ARCHIVELOG mode is a mode that you can put the database in for creating a backup of all transactions that have occurred in the database so that you can recover to any point in time.
NOARCHIVELOG mode is basically the absence of ARCHIVELOG mode and has the disadvantage of not being able to recover to any point in time.
Converting database from noarchivelog mode to archivelog mode:
---> Please note that converition of noarchivelog to archivelog mode is only posible in database mount state:
1. Login to database and verify the database state and log mode:
sqlplus / as sysdba
SQL> select name, open_mode, database_role, log_mode from v$database;
NAME OPEN_MODE DATABASE_ROLE LOG_MODE
--------- -------------------- ---------------- ------------
DEVDB READ WRITE PRIMARY NOARCHIVELOG
SQL> archive log list
Database log mode No Archive Mode
Automatic archival Disabled
Archive destination USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence 3
Current log sequence 5
SQL>
2. Shutdown the database
SQL> shut immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
3. Start the database in mount mode.
SQL> startup mount;
ORACLE instance started.
Total System Global Area 1570009088 bytes
Fixed Size 2253584 bytes
Variable Size 973081840 bytes
Database Buffers 587202560 bytes
Redo Buffers 7471104 bytes
Database mounted.
SQL> archive log list
Database log mode No Archive Mode
Automatic archival Disabled
Archive destination USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence 3
Current log sequence 5
SQL>
4. Convert the database from noarchivelog to archivelog mode
SQL> alter database archivelog;
Database altered.
5. Open the database
SQL> alter database open;
Database altered.
6. Verify the database state and log mode:
SQL> select name, open_mode, database_role, log_mode from v$database;
NAME OPEN_MODE DATABASE_ROLE LOG_MODE
--------- -------------------- ---------------- ------------
DEVDB READ WRITE PRIMARY ARCHIVELOG
SQL> archive log list
Database log mode Archive Mode
Automatic archival Enabled
Archive destination USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence 3
Next log sequence to archive 5
Current log sequence 5
SQL>
Converting database from archivelog mode to noarchivelog mode:
---> Please note that converition of noarchivelog to archivelog mode is only posible in database mount state:
1. Login to database and verify the database state and log mode:
SQL> select name, open_mode, database_role, log_mode from v$database;
NAME OPEN_MODE DATABASE_ROLE LOG_MODE
--------- -------------------- ---------------- ------------
DEVDB READ WRITE PRIMARY ARCHIVELOG
SQL> archive log list
Database log mode Archive Mode
Automatic archival Enabled
Archive destination USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence 3
Next log sequence to archive 5
Current log sequence 5
SQL>
2. Shutdown the database
SQL> shut immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
3. Start the database in mount mode.
SQL> startup mount;
ORACLE instance started.
Total System Global Area 1570009088 bytes
Fixed Size 2253584 bytes
Variable Size 973081840 bytes
Database Buffers 587202560 bytes
Redo Buffers 7471104 bytes
Database mounted.
4. Convert the database from archivelog to noarchivelog mode
SQL> alter database noarchivelog;
Database altered.
5. Open the database
SQL> alter database open;
Database altered.
6. Verify the database state and log mode:
SQL> select name, open_mode, database_role, log_mode from v$database;
NAME OPEN_MODE DATABASE_ROLE LOG_MODE
--------- -------------------- ---------------- ------------
DEVDB READ WRITE PRIMARY NOARCHIVELOG
SQL> archive log list
Database log mode No Archive Mode
Automatic archival Disabled
Archive destination USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence 3
Current log sequence 5
SQL>
Please follow more on my YouTube
https://youtu.be/PUYuv0aU5S0
Regards,
Mallik
No comments:
Post a Comment