NOARCHIVELOG mode protects a database from instance failure but not from media failure. Only the most recent changes made to the database, which are stored in the online redo log groups, are available for instance recovery. If a media failure occurs while the database is in NOARCHIVELOG mode, you can only restore the database to the point of the most recent full database backup. You cannot recover transactions subsequent to that backup.
In NOARCHIVELOG mode you cannot perform online tablespace backups, nor can you use online tablespace backups taken earlier while the database was in ARCHIVELOG mode. To restore a database operating in NOARCHIVELOG mode, you can use only whole database backups taken while the database is closed. Therefore, if you decide to operate a database in NOARCHIVELOG mode, take whole database backups at regular, frequent intervals.
You can download The Study Case & solution Lab Oracle & SQL Expert
1. Login sqlplus using sys as sysdba
Microsoft Windows [Version 6.1.7601]
Copyright (c) 2009 Microsoft Corporation. All rights reserved.
C:\Users\teguh.triharto>sqlplus
SQL*Plus: Release 11.2.0.1.0 Production on Fri Mar 15 15:09:40 2013
Copyright (c) 1982, 2010, Oracle. All rights reserved.
Enter user-name: sys as sysdba
Enter password:
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL>
2. Check current archivelog mode in database
SQL> select log_mode from v$database;
LOG_MODE
------------
ARCHIVELOG
SQL> archive log list
Database log mode Archive Mode
Automatic archival Enabled
Archive destination USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence 8
Next log sequence to archive 10
Current log sequence 10
SQL>
3. Force a checkpoint to help and speed the database shutdown
SQL> alter system checkpoint;
System altered.
SQL>
4. Shutdown the database
SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL>
5. Start the database in mount mode
SQL> startup mount
ORACLE instance started.
Total System Global Area 744910848 bytes
Fixed Size 1374696 bytes
Variable Size 251659800 bytes
Database Buffers 486539264 bytes
Redo Buffers 5337088 bytes
Database mounted.
SQL>
6. Disable archivelog in the database
SQL> alter database noarchivelog;
Database altered.
SQL>
7. Open the database
SQL> alter database open;
Database altered.
SQL>
8. Check the current archivelog mode in the database
SQL> archive log list
Database log mode No Archive Mode
Automatic archival Disabled
Archive destination USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence 8
Current log sequence 10
SQL> select log_mode from v$database;
LOG_MODE
------------
NOARCHIVELOG
SQL>
9. Check parameter log_archive_dest
SQL> show parameter log_archive_dest_1
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
log_archive_dest_1 string
log_archive_dest_10 string
log_archive_dest_11 string
log_archive_dest_12 string
log_archive_dest_13 string
log_archive_dest_14 string
log_archive_dest_15 string
log_archive_dest_16 string
log_archive_dest_17 string
log_archive_dest_18 string
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
log_archive_dest_19 string
SQL>
10. Check parameter db_recovery_file_dest on C:\app\teguh.triharto\flash_recovery_area\tgh\ONLINELOG
SQL> show parameter db_recovery_file_dest
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_recovery_file_dest string C:\app\teguh.triharto\flash_re
covery_area
db_recovery_file_dest_size big integer 3852M
SQL> archive log list
Database log mode No Archive Mode
Automatic archival Disabled
Archive destination USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence 9
Current log sequence 11
SQL>
11. force generation of some archivelogs
SQL> alter system switch logfile;
System altered.
SQL>
