8. User-managed backup and recovery in noarchivelog Mode

8.1 Introduction to noarchive Mode

1. noarchivelog Mode

Noarchivelog is a log operation mode that does not retain redo history. This operation mode can only be used to protect the failure of the routine (such as system power failure), but cannot protect the media failure. The noarchivelog mode has the following features:

• After the checkpoint is completed, the background process lgwr can overwrite the original redo log Content.
• If the redo log content after the database backup has been overwritten, when the data file fails, it can only be restored to the previous full backup point.
• When the database is open, you cannot back up the database.
• When backing up a database, you must use shutdown normal or shutdown immediate to shut down the database.
• When backing up a database, all data files and control files must be backed up.

2. Change to noarchivelog Mode

Shut down the database: shutdown immediate

Load Database: startup Mount

Change log operation mode: Alter database noarchivelog;

Open Database: Alter database open;

SQL> Conn/As sysdba
Connected.
SQL> shutdown immediate
The database has been closed.
The database has been detached.
The Oracle routine has been disabled.
SQL> startup Mount
The Oracle routine has been started.

Total system global area 167772160 bytes
Fixed size 1247900 bytes
Variable Size 75498852 bytes
Database buffers 88080384 bytes
Redo buffers 2945024 bytes
The database has been loaded.
SQL> alter database noarchivelog;

The database has been changed.

SQL> alter database open;

The database has been changed.

8.2 backup in noarchivelog Mode

(1) list the data files and control files to be backed up.

(2) shut down the database.

(3) Copy all data files and control files to the backup directory.

(4) Start the routine and open the database.

SQL> select name from V $ datafile
2 Union
3 select name from V $ controlfile;

Name
----------------------------------------------------------------------------------
C: \ demo \ control02.ctl
D: \ demo \ control01.ctl
D: \ demo \ sysaux01.dbf
D: \ demo \ system01.dbf
D: \ demo \ undotbs01.dbf
D: \ demo \ users01.dbf

You have selected 6 rows.

SQL> shutdown immediate
The database has been closed.
The database has been detached.
The Oracle routine has been disabled.
SQL> host copy c: \ demo \ control02.ctl D: \ 0927

SQL> host Copy D: \ demo \ control01.ctl D: \ 0927

SQL> host Copy D: \ demo \ sysaux01.dbf D :\ 0927

SQL> host Copy D: \ demo \ system01.dbf D: \ 0927

SQL> host Copy D: \ demo \ undotbs01.dbf D :\ 0927

SQL> host Copy D: \ demo \ users01.dbf D :\ 0927

SQL> startup
The Oracle routine has been started.

Total system global area 167772160 bytes
Fixed size 1247900 bytes
Variable Size 75498852 bytes
Database buffers 88080384 bytes
Redo buffers 2945024 bytes
The database has been loaded.
The database has been opened.

8.3 restoration in noarchivelog Mode

8.3.1 a media failure occurs in the data file, but the redo log is not overwritten.

(1) load the database.

(2) determine the data file to be restored.

(3) copy data file backup.

(4) restore data files.

(5) Open the database.

SQL> shutdown immediate
The database has been closed.
The database has been detached.
The Oracle routine has been disabled.
SQL> host del D: \ demo \ system01.dbf

SQL> startup
The Oracle routine has been started.

Total system global area 167772160 bytes
Fixed size 1247900 bytes
Variable Size 75498852 bytes
Database buffers 88080384 bytes
Redo buffers 2945024 bytes
The database has been loaded.
ORA-01157: unable to identify/lock data file 1-see dbwr trace file
ORA-01110: Data File 1: 'd: \ demo \ system01.dbf'

SQL> select status from V $ instance;

Status
------------
Mounted

SQL> SELECT FILE #, error from V $ recover_file;

File # error
---------------------------------------------------------------------------
1 file not found

SQL> host Copy d :\0927 \ system01.dbf D: \ demo

SQL> recover datafile 1
Media recovery is completed.
SQL> alter database open;

The database has been changed.

8.3.2 media failure in secondary data files

A secondary data file is a data file that contains only secondary data. If the media of the secondary data file fails and the redo log Content since the backup has been overwritten, you can delete the secondary data file.

SQL> select name from V $ datafile;

Name
Bytes ----------------------------------------------------------------------------------------------------
D: \ demo \ system01.dbf
D: \ demo \ undotbs01.dbf
D: \ demo \ sysaux01.dbf
D: \ demo \ users01.dbf
D: \ demo \ user01.dbf
D: \ demo \ user02.dbf

You have selected 6 rows.
SQL> shutdown immediate
The database has been closed.
The database has been detached.
The Oracle routine has been disabled.
SQL> host del D: \ demo \ user02.dbf

SQL> startup
The Oracle routine has been started.

Total system global area 167772160 bytes
Fixed size 1247900 bytes
Variable Size 75498852 bytes
Database buffers 88080384 bytes
Redo buffers 2945024 bytes
The database has been loaded.
ORA-01157: unable to identify/lock data file 6-see dbwr trace file
ORA-01110: data file 6: 'd: \ demo \ user02.dbf'

SQL> select status from V $ instance;

Status
------------
Mounted

SQL> alter database datafile 6 offline drop;

The database has been changed.

SQL> alter database open;

The database has been changed.

 

8.3.3 media failure in important data files

(1) shut down the database.

(2) Copy all data files and control file backups.

(3) load the database.

(4) use the resetlogs option to open the database.

SQL> startup
The Oracle routine has been started.

Total system global area 167772160 bytes
Fixed size 1247900 bytes
Variable Size 62915940 bytes
Database buffers 100663296 bytes
Redo buffers 2945024 bytes
The database has been loaded.
ORA-01157: unable to identify/lock data file 4-see dbwr trace file
ORA-01110: data file 4: 'd: \ demo \ users01.dbf'

SQL> select status from V $ instance;

Status
------------
Mounted

SQL> shutdown immediate
ORA-01109: the database is not open

The database has been detached.
The Oracle routine has been disabled.
SQL> host Copy d :\0927 \ system01.dbf D: \ demo

SQL> host Copy d :\ 0927 \ undotbs01.dbf D: \ demo

SQL> host Copy d :\0927 \ sysaux01.dbf D: \ demo

SQL> host Copy d :\0927 \ users01.dbf D: \ demo

SQL> host Copy d :\0927 \ control01.ctl D: \ demo

SQL> host Copy d :\ 0927 \ control02.ctl c: \ demo

SQL> startup Mount
The Oracle routine has been started.

Total system global area 167772160 bytes
Fixed size 1247900 bytes
Variable Size 62915940 bytes
Database buffers 100663296 bytes
Redo buffers 2945024 bytes
The database has been loaded.
SQL> recover database until cancel;
Media recovery is completed.

In noarchivelog mode, the recover database until cancel command does not perform any restoration operations, but prompts that the control file no longer uses the original redo log.
SQL> alter database open resetlogs;

The database has been changed.