Activate archivelog mode on a database

As you can see, database is in "No Archive Mode" and Archive destination is "/u01/app/oracle/flash_recovery_area"
SQL> archive log list
Database log mode              No Archive Mode
Automatic archival             Disabled
Archive destination            USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence     308
Current log sequence           309

SQL> show parameter db_recovery_file_dest

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_recovery_file_dest                string      /u01/app/oracle/flash_recovery_area
db_recovery_file_dest_size           big integer 2G

We can change archive destination by doing:
SQL> alter system set DB_RECOVERY_FILE_DEST='' scope=both SID='*';

System altered.

SQL> alter system set log_archive_dest='/u01/app/oracle/prueba' scope=both SID='*';

System altered.
We also should to check destination filesystem size
[oracle@ODIGettingStarted ~]$ df -h /u01/app/oracle/flash_recovery_area
Filesystem            Size  Used Avail Use% Mounted on
/dev/sdb               15G   1.2G  13G  8% /u01/app/oracle/flash_recovery_area
And make and estimation of how many days of archivelog can storage without make a archivelog backup. You can estimate this with the below query that gives you number of archivelogs generation by day(database not needed to be on archivelog mode to launch that query):
select count(*), to_char(first_time,'YYYY-MM-DD') from v$log_history group by to_char(first_time,'YYYY-MM-DD') order by 2;
Then, to activate archivelog mode is needed to shutdown database:
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount;
ORACLE instance started.

Total System Global Area 1068937216 bytes
Fixed Size                  2260088 bytes
Variable Size             822084488 bytes
Database Buffers          239075328 bytes
Redo Buffers                5517312 bytes
Database mounted.
SQL> alter database archivelog;

Database altered.

SQL> alter database open;

Database altered.
now, we can see archivelog mode is set:
SQL> archive log list;
Database log mode              Archive Mode
Automatic archival             Enabled
Archive destination            /u01/app/oracle/prueba
Oldest online log sequence     308
Next log sequence to archive   309
Current log sequence           309
Finally, you can probe it by generating some archive files:
SQL> alter system switch logfile;

System altered.

SQL> r
  1* alter system switch logfile

System altered.

SQL> r
  1* alter system switch logfile

System altered.

SQL> r
  1* alter system switch logfile

System altered.

SQL> !ls -ltr /u01/app/oracle/prueba
total 10332
-rw-rw----. 1 oracle oracle 10566144 Sep 15 05:40 1_309_834681255.dbf
-rw-rw----. 1 oracle oracle     2560 Sep 15 05:40 1_310_834681255.dbf
-rw-rw----. 1 oracle oracle     1024 Sep 15 05:40 1_311_834681255.dbf
-rw-rw----. 1 oracle oracle     1024 Sep 15 05:40 1_312_834681255.dbf

Comments

Popular posts from this blog

Oracle Historical Session Information with ASH >10g

Purging and archiving Oracle alert.log and listener.log

Check sessions and processes limits in Oracle