Skip to main content

1) Login to your oracle using SqlPlus with user: sys as sysdba and password : root 

 

 

2) Now run the following sql queries step by step in SqlPlus: 

i) select name,log_mode from v$database; 

The output you will get is NOARCHIVELOG if logminer is not enabled. 

 

 

ii) archive log list; 

 

 

When you execute the ii) query you will get "Archive Destination" as shown in the above screenshot. Copy that value carefully. 

 

iii) alter system set log_archive_dest_1='LOCATION=D:\oracledatabase\db\rdbms' scope=spfile; 

 

Before executing the iii) query please check the Archive Destination and LOCATION is exactly same. 

 

 

iv) shutdown immediate; 

 

 

v) startup mount; 

 

 

vi) alter database archivelog; 

 

 

vii) alter database open; 

 

 

viii) select name,log_mode from v$database

 

 

ix) archive log list 

 

 

3) Ensure that the database retains backups and archive logs for at least twenty-four hours. Note that CData recommends retaining backups and logs for seven days or you can also increase time according to your preference as shown in the below example: 

RMAN> CONFIGURE RETENTION POLICY TO RECOVERY WINDOW OF 7 DAYS; 

 

 

Run this command in oracle home folder and in that folder you will find a database folder like this - C:\Users\Downloads\WINDOWS.X64_193000_db_home from there open command prompt and then run – RMAN TARGET / 

 

 

Now in RMAN run the following command - CONFIGURE RETENTION POLICY TO RECOVERY WINDOW OF 7 DAYS; 

 

 

This step is optional as if you want to retain logs only for 24 hours but if you want increase retention period you can proceed with this step. 

 

4) Now run the following queries in your sqlDeveloper: 

i) ALTER DATABASE ADD SUPPLEMENTAL LOG DATA (ALL) COLUMNS 

ii) ALTER TABLE SchemaName.TableName ADD SUPPLEMENTAL LOG DATA (ALL)    COLUMNS 

 

5) Restart your database.