Hiya Buddies!!!
We would like share a very simple concept but it is
very important i.e. How to enable or disable Archive
log mode.
Below are the steps which are described step by
step which we have done on test server.
ARCHIVELOG MODE
It will backup of all transactions. So, helpful in recovery to any
point in time.
We can perform Hot/online backup.
NO-ARCHIVELOG MODE
We can't recover to any point in time.
We can't take hot/online database backup.
1. Database is in open mode.
SQL> select name,open_mode from v$database;
NAME
OPEN_MODE
--------- --------------------
ORCL
READ WRITE
SQL>
2. Bring down the database.
SQL> shut immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL>
3. Start database in Mount stage.
SQL> startup mount
ORACLE instance started.
Total System Global Area 456146944 bytes
Fixed Size 1344840 bytes
Variable Size 356518584 bytes
Database Buffers 92274688 bytes
Redo Buffers
6008832 bytes
Database mounted.
4. Check for database is in Archive mode or not. As below we can
Archive mode is disabled.
SQL>
archive log list
Database log mode No Archive Mode
Automatic archival Disabled
Archive destination USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence 559
Current log sequence 561
SQL>
5. Enable the Archive log from below command.
SQL> alter database archivelog;
Database altered.
SQL>
6. Now cross verify the Archive log mode status:
SQL>
archive log list
Database log mode Archive Mode
Automatic archival Enabled
Archive destination USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence 559
Next log sequence to archive 561
Current log sequence 561
SQL>
SQL>
alter system switch logfile;
System altered.
SQL>
archive log list;
Database log mode Archive Mode
Automatic archival Enabled
Archive destination USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence 560
Next log sequence to archive 562
Current log sequence 562
SQL>
Disable the Archive Log:
We can see below here, what if the database is
Opened and try to change Archive log mode:
SQL> alter database noarchivelog;
alter database noarchivelog
*
ERROR at line 1:
ORA-01126: database must be mounted in
this instance and not open in any
instance
SQL> shut immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL>
SQL> startup mount
ORACLE instance started.
Total System Global Area 456146944 bytes
Fixed Size 1344840 bytes
Variable Size 360712888 bytes
Database Buffers 88080384 bytes
Redo Buffers 6008832 bytes
Database mounted.
SQL>
archive log list;
Database log mode Archive Mode
Automatic archival Enabled
Archive destination USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence 560
Next log sequence to archive 562
Current log sequence 562
SQL> alter database noarchivelog;
Database altered.
SQL>
archive log list;
Database log mode No Archive Mode
Automatic archival Disabled
Archive destination USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence 560
Current log sequence 562
SQL>
alter database open;
Database altered.
SQL> alter system switch logfile;
System altered.
SQL> archive log list;
Database log mode No Archive Mode
Automatic archival Disabled
Archive destination USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence 561
Current log sequence 563
SQL>
Some more useful links:
Regards,
No comments:
Post a Comment