In this article, we will discuss how to Enable Archivelog Mode in Oracle 19c multitenant container database at CDB and PDB level. In my previous post, we already discussed about enabling / disabling archiving in 11g/12c databases for single instance.
To backup database online while it is open or to perform complete or point-in-time media recovery, you must enable archiving of redo log file.
For Multitenant container database CDB and PDB, all the PDBs share same redo log files so it is not possible to enable archiving for a specific PDB. All the PDBs share same archivelog mode of CDB. So configuration of archiving should be implemented at CDB level only.
Steps to Enable / Disable Archivelog Mode in 19c
STEP 1: Check archivelog mode
SQL> select log_mode,name from v$database; LOG_MODE NAME -------------- ---------- NOARCHIVELOG ORAHOW2C SQL> archive log list Database log mode No Archive Mode Automatic archival Disabled Archive destination USE_DB_RECOVERY_FILE_DEST Oldest online log sequence 27888 Current log sequence 27889 SQL> show parameter DB_RECOVERY_FILE_DEST NAME TYPE VALUE --------- --------- ---------- db_recovery_file_dest string +RE_QTR4 db_recovery_file_dest_size big integer 80G
STEP 2: Configure archive destination for archiving redo logs.
From the above output we can see that database is using Flash recovery area – FRA (DB_RECOVERY_FILE_DEST) for archive destination which is the default Oracle managed location for centralized backup and recovery files. So first we need to configure FRA as archiving destination if it is not set.
db_recovery_file_dest and db_recovery_file_dest_size parameter need to be set for FRA. db_recovery_file_dest is the location and db_recovery_file_dest_size is the quota/size for FRA
SQL> show parameter db_recovery_file
NAME TYPE VALUE
------- ------- -------------
db_recovery_file_dest string +RE_QTR4
db_recovery_file_dest_size big integer 80G
Here we can see it is already set, if not then set it using below command.
SQL> alter system set db_recovery_file_dest_size=80G scope=both sid='*';
SQL> alter system set db_recovery_file_dest='+RE_QTR4' scope=both sid='*';
NOTE: You can also set db_recovery_file_dest path to local file system or Linux directory.
Once set verify it.
SQL> select * from V$RECOVERY_FILE_DEST;
To make archivelog destination same as FRA execute below command.
alter system set log_archive_dest_1='LOCATION=USE_DB_RECOVERY_FILE_DEST' scope=both sid='*';
STEP 3: Stop database and start in mount state.
[oracle@orahow07 ~]$ srvctl status database -d orahow2c [oracle@orahow07 ~]$ srvctl stop database -d orahow2c [oracle@orahow07 ~]$ srvctl start database -d orahow2c -o mount [oracle@orahow07 ~]$ srvctl status database -d orahow2c
STEP 4: Enable Archiving of RAC Database.
SQL> alter database archivelog; Database altered. SQL> archive log list Database log mode Archive Mode Automatic archival Enabled Archive destination USE_DB_RECOVERY_FILE_DEST Oldest online log sequence 27891 Next log sequence to archive 27892 Current log sequence 27892 SQL> select log_mode,name from v$database; LOG_MODE NAME ---------------- ---------- ARCHIVELOG orahow2c
STEP 5: STOP and Start the database.
[oracle@orahow07 ~]$ srvctl stop database -d orahow2c [oracle@orahow07 ~]$ srvctl start database -d orahow2c [oracle@orahow07 ~]$ srvctl status database -d orahow2c
Done !!!!
SUMMARY:
- Use alter database archivelog to enable archiving.
- For container database CDB and PDB archiving need to be configure at CDB level.
- Either you can use FRA DB_RECOVERY_FILE_DEST or log_archive_dest_1 parameter to set archive destination.
- In above all command Use sid=’*’ only for RAC database.
- As per my recommendation you can set archive destination as 20% of the total database size. Again it depends on your available space and retention policy. Once archive destination is full, you need to release some space for incoming archive logs.