-
Title
How to turn ON or OFF Archive Log Mode in Oracle 10g via SQL Plus -
Description
How can I turn ON and OFF Archive Log Mode in Oracle 10g via SQL Plus,
-
Resolution
In Oracle 10g, the database will need to be shutdown, restarted, and mounted before you can turn ON or OFF the Archive Log mode.
Here's the Oracle link for more information on the instructions as well:
http://download.oracle.com/docs/cd/B25329_01/doc/admin.102/b25107/backrest.htm#BABDFAHJNote: The steps below were pulled from SQL Plus, which includes the results of each line executed.
First, start SQL Plus and LogIn as SYSDBA:
SQL> connect <user>/<password> as sysdba;
Connected.
To Turn ON Archive Log:
1)
SQL> SHUTDOWN IMMEDIATE;
Database closed.
Database dismounted.
ORACLE instance shut down.
2)
SQL> STARTUP MOUNT;
ORACLE instance started.
Total System Global Area 612368384 bytes
Fixed Size 1250428 bytes
Variable Size 201329540 bytes
Database Buffers 402653184 bytes
Redo Buffers 7135232 bytes
Database mounted.
3)
SQL> ALTER DATABASE ARCHIVELOG;
Database altered.
4)
SQL> ALTER DATABASE OPEN;
Database altered.
5) To verify if it's ON
SQL> archive log list
Database log mode Archive Mode
Automatic archival Enabled
Archive destination USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence 589
Next log sequence to archive 591
Current log sequence 591
To Turn OFF Archive Log:
1)
SQL> SHUTDOWN IMMEDIATE;
Database closed.
Database dismounted.
ORACLE instance shut down.
2)
SQL> STARTUP MOUNT;
ORACLE instance started.
Total System Global Area 612368384 bytes
Fixed Size 1250428 bytes
Variable Size 205523844 bytes
Database Buffers 398458880 bytes
Redo Buffers 7135232 bytes
Database mounted.
3)
SQL> ALTER DATABASE NOARCHIVELOG;
Database altered.
4)
SQL> ALTER DATABASE OPEN;
Database altered.
5) To verify if it's OFF
SQL> archive log list
Database log mode No Archive Mode
Automatic archival Disabled
Archive destination USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence 589
Current log sequence 591