How to change Archiving Mode in Oracle Database 12c

This process is same for Oracle database  11g  and Oracle database 12C, . Means you can follow the below steps for 11g and 12C as well.

As we know to change the archiving mode in the 11g we use ALTER DATABASE statement. Same statement we use to change archive mode of your 12C database .

We also know that to do the same, we need to connect to the database with SYSDBA privileges.

Ok, let's change the archive mode. Please follow below steps:-

I have assumed that your DB is in NOARCHIVELOG mode. You can check the status of your database with below command. Please make sure you are connected with SYSDBA privileges.

SQL> conn sys@XXXXXX as sysdba
Enter password:XXXXXX
Connected.

Now you can run "archive log list;" to check your database. Below is output :-

SQL>  archive log list;
XXXXX            No Archive Mode
XXXXX          Disabled
XXXXX            USE_DB_RECOVERY_FILE_DEST
XXXXX     18001
XXXXX          18003

Database log mode showing that database is in No Archive Mode. :)

Please follow the below steps to put your 12C Database in archive log:-

1. Complete Shutdown your database instance. 


We cannot change mode of Database when it is up and running. So we need to stop the database 12c.

SQL> SHUTDOWN IMMEDIATE

The main point is you cannot change ARCHIVELOG to NOARCHIVELOG if any data files need media recovery. This is recomannded by Oracle.


2. Take Backup the database.


Although this step is recomended by Oracle, It is not nessesry. Taking backup before doing any changes to database is good practise. If you are new to Oracle
Please take backup of your database. Take cold backup of your DB i.e. copy everything to diffenet location. Easy but time taking process, again it depends on the size of your database.



3. Startup instance with mount option 

You need to startup the database again in mount mount. No need to open the database yet. BEcause we are going to change somoe parameters in the database.

SQL> STARTUP MOUNT


4. Change archiving mode

Now we can set the archiving mode for our 12C database. After that we need to open the database.

SQL> ALTER DATABASE ARCHIVELOG;

SQL> ALTER DATABASE OPEN;


5. Shut down the database. 

After changing the ARCHIVELOG in oracle database 12c you need to restart the database after taking the cold backup again.

SQL>SHUTDOWN IMMEDIATE


6. Back up the database

Take Cold backup of your oracle database 12c as control file information is also changed fro previous backup. Now you need to use this backup as your BASE backup.



7. Start the database 

After backup, just start the backup
SQL>STARTUP



Now your oracle database 12c is in ARCHIVELOG.

Keep ARCHIVING, Keep Posting :)