How to put DB in Archive/NoArchive log Mode/ORA-01126 in oracle database

Log mode is always important when it comes to backup. this is probably the important question with respective for fresher oracle dba.

Following video shows the how to enable archivelog/noarchive log mode oracle database

Check the Current archive log mode and archive destination. here the database is in no-archive log mode and archive destination point to USE_DB_RECOVERY_FILE_DEST.

SQL> archive log list;

Database log mode No Archive Mode

Automatic archival Disabled

Archive destination USE_DB_RECOVERY_FILE_DEST

Oldest online log sequence 16

Current log sequence 18

First change the archive destination which does not need to bounce the database.

SQL> alter system set log_archive_dest_1=’LOCATION=/backup/archive’ scope=both;

System altered.

Check the archive destination which point to new location now.

SQL> archive log list;

Database log mode No Archive Mode

Automatic archival Disabled

Archive destination /backup/archive

Oldest online log sequence 16

Current log sequence 18

We need to bounce the database to change the archive log mode

SQL> shutdown immediate;

Database closed.

Database dismounted.

ORACLE instance shut down.

Start the database in mount stage to change archive log mode.

SQL> startup mount

ORACLE instance started.

Total System Global Area 830930944 bytes

Fixed Size 2217912 bytes

Variable Size 624953416 bytes

Database Buffers 201326592 bytes

Redo Buffers 2433024 bytes

Database mounted.

SQL>

Change the archive log mode to archivelog/noarchive depend upon situation. Here I am putting the database in archive log mode.

SQL> alter database archivelog;

Database altered.

Crosscheck the archive log destination and archive log mode.

SQL> archive log list;

Database log mode Archive Mode

Automatic archival Enabled

Archive destination /backup/archive

Oldest online log sequence 16

Next log sequence to archive 18

Current log sequence 18

SQL>

Open the database for user access.

SQL> alter database open;

Database altered.

SQL> SQL> archive log list;

Database log mode No Archive Mode

Automatic archival Disabled

Archive destination USE_DB_RECOVERY_FILE_DEST

Oldest online log sequence 16

Current log sequence 18

Leave a Reply

Your email address will not be published. Required fields are marked *