Experiment notes on using RMAN for backup and recovery in Oracle 10 GB

 

**************************************** **************************************** ***

Experiment 1: Using RMAN backup for non-archive databases in open state, system error
 
If you use RMAN for backup of a database in non-archive mode, will the system go wrong? No. Backup can be performed.
**************************************** **************************************** ***

SQL> archive log list;
Database Log mode non-archive Mode
Disable automatic archiving
Archiving end point use_db_recovery_file_dest
Oldest online log sequence 216
Current Log sequence 218
SQL>

RMAN> backup database;

Start backup from September 11 to September 09
Allocated channel: ora_disk_1
Channel ora_disk_1: SID = 144 devtype = Disk
Channel ora_disk_1: Start all data file backup Sets
Channel ora_disk_1: Specifies the data file in the backup set.
ORA-19602: unable to back up or copy active file channel ora_disk_1 in noarchivelog mode: Start all data file backup Sets
Channel ora_disk_1: Specifies the data file in the backup set.
The backup set includes the current control file
Include the current spfile in the backup set
Channel ora_disk_1: Starting segment 1 from February 5 to February 09
Channel ora_disk_1: Completed segment 1 from February 5 to February 09
Segment handle = c: \ oracle \ product \ 10.2.0 \ flash_recovery_area \ keymen \ backupset \ 2009_05_11 \ o1_mf_ncsnf_tag200901_t144853_50hllshn _
. Bkp flag = tag20090rjt144853 annotation = none
Channel ora_disk_1: Backup set completed. elapsed time: 00: 00: 07
RMAN-00571: ========================================================== ==============================
RMAN-00569: ==================== error message stack follows ==========================
RMAN-00571: ========================================================== ==============================

**************************************** **************************************** ***

Experiment 2: A full database backup is generated by rman, and some data files in the target database are lost, including system and undo lost recovery.
Another users tablespace data file has been taken offline.

**************************************** **************************************** ***

Use RMAN to generate full-Database Backup

RMAN> connect target/

Connect to the target database: mygod (dbid = 85383244)

RMAN> backup database;

Start backup from September 11 to September 09
Use the target database control file to replace the recovery directory
Allocated channel: ora_disk_1
Channel ora_disk_1: SID = 134 devtype = Disk
Channel ora_disk_1: Start all data file backup Sets
Channel ora_disk_1: Specifies the data file in the backup set.
Input data file fno = 00001 name = c: \ oracle \ product \ 10.2.0 \ oradata \ mygod \ system01.dbf
Input data file fno = 00003 name = c: \ oracle \ product \ 10.2.0 \ oradata \ mygod \ sysaux01.dbf
Input data file fno = 00005 name = c: \ oracle \ product \ 10.2.0 \ oradata \ mygod \ example01.dbf
Input data file fno = 00002 name = c: \ oracle \ product \ 10.2.0 \ oradata \ mygod \ undotbs01.dbf
Input data file fno = 00004 name = c: \ oracle \ product \ 10.2.0 \ oradata \ mygod \ users01.dbf
Channel ora_disk_1: Starting segment 1 from February 5 to February 09
Channel ora_disk_1: Completed segment 1 from February 5 to February 09
Segment handle = c: \ oracle \ product \ 10.2.0 \ flash_recovery_area \ mygod \ backupset \ 2009_05_11 \ o1_mf_nnndf_tag200901_t151129_50hmx55v _.
Bkp flag = tag200901_t151129 comment = none
Channel ora_disk_1: Backup set completed. elapsed time: 00: 00: 55
Channel ora_disk_1: Start all data file backup Sets
Channel ora_disk_1: Specifies the data file in the backup set.
The backup set includes the current control file
Include the current spfile in the backup set
Channel ora_disk_1: Starting segment 1 from February 5 to February 09
Channel ora_disk_1: Completed segment 1 from February 5 to February 09
Segment handle = c: \ oracle \ product \ 10.2.0 \ flash_recovery_area \ mygod \ backupset \ 2009_05_11 \ o1_mf_ncsnf_tag200901_t151129_50hmywdp _.
Bkp flag = tag200901_t151129 comment = none
Channel ora_disk_1: Backup set completed. elapsed time: 00: 00: 03
Backup is completed from September 11 to September 09.

RMAN>

---------------------------------------------------

C: \> sqlplus/nolog

SQL * Plus: Release 10.2.0.1.0-production on Monday May 11 15:18:01 2009

Copyright (c) 1982,200 5, Oracle. All rights reserved.

SQL> Conn/As sysdba
Connected.
SQL> select * from Scott. Dept;
Select * from Scott. Dept
*
Row 3 has an error:
ORA-01219: Database not open: Allow queries only in fixed tables/Views

SQL> alter database open;
Alter database open
*
Row 3 has an error:
ORA-01157: unable to identify/lock data file 1-see dbwr trace file
ORA-01110: Data File 1: 'C: \ oracle \ product \ 10.2.0 \ oradata \ mygod \ system01.dbf'

SQL>

The database needs to be restored.

-------------------------------------------------------

Start recovery:

In the RMAN window, write the recovery script

RMAN> connect target/

Connected to the target database: mygod (dbid = 85383244, not enabled)

RMAN> run {
2> Restore database;
3> recover database;
4> SQL 'alter database open ';
5>}

Start restore from September 5 to September 09
Use the target database control file to replace the recovery directory
Allocated channel: ora_disk_1
Channel ora_disk_1: SID = 155 devtype = Disk

Data File 4 is not processed because the file is offline
Channel ora_disk_1: Recovering data file backup set
Channel ora_disk_1: Specifies the data file to be recovered from the backup set.
Restoring data file 00001 to c: \ oracle \ product \ 10.2.0 \ oradata \ mygod \ system01.dbf
Restoring data file 00002 to c: \ oracle \ product \ 10.2.0 \ oradata \ mygod \ undotbs01.dbf
Restoring data file 00003 to c: \ oracle \ product \ 10.2.0 \ oradata \ mygod \ sysaux01.dbf
Restoring data file 00005 to c: \ oracle \ product \ 10.2.0 \ oradata \ mygod \ example01.dbf
Channel ora_disk_1: Reading the backup section C: \ oracle \ product \ 10.2.0 \ flash_recovery_area \ mygod \ backupset \ 2009_05_11 \ o1_mf_nnndf_tag2
0090417t151129_50hmx55v_.bkp
Channel ora_disk_1: recovered backup segment 1
Segment handle = c: \ oracle \ product \ 10.2.0 \ flash_recovery_area \ mygod \ backupset \ 2009_05_11 \ o1_mf_nnndf_tag200901_t151129_50hmx55v
_. Bkp mark = tag20090417t151129
Channel ora_disk_1: Recovery completed, time: 00:00:55
The restore is completed from February 5 to February 09.

Start recover from September 5 to September 09
Use channel ora_disk_1
Data File 4 is not processed because the file is offline

Restoring media...
Media Recovery completed. Time: 00:00:04

The recover is completed from February 5 to February 09.

SQL statement: Alter database open

RMAN>

After the restoration is completed, you can find that the data has been opened in the sqlplus window.

SQL> select open_mode from V $ database;

Open_mode
----------
Read Write

SQL>

However, the users tablespace is still not restored.

SQL> select * from Scott. Dept;
Select * from Scott. Dept
*
Row 3 has an error:
ORA-00376: file cannot be read at this time 4
ORA-01110: data file 4: 'C: \ oracle \ product \ 10.2.0 \ oradata \ mygod \ users01.dbf'

SQL>

Recovery for a single tablespace

RMAN> run {
2> restore tablespace users;
3> recover tablespace users;
4>}

Start restore from September 5 to September 09
Allocated channel: ora_disk_1
Channel ora_disk_1: SID = 130 devtype = Disk

Channel ora_disk_1: Recovering data file backup set
Channel ora_disk_1: Specifies the data file to be recovered from the backup set.
Restoring data file 00004 to c: \ oracle \ product \ 10.2.0 \ oradata \ mygod \ users01.dbf
Channel ora_disk_1: Reading the backup section C: \ oracle \ product \ 10.2.0 \ flash_recovery_area \ mygod \ backupset \ 2009_05_11 \ o1_mf_nnndf_tag2
0090417t151129_50hmx55v_.bkp
Channel ora_disk_1: recovered backup segment 1
Segment handle = c: \ oracle \ product \ 10.2.0 \ flash_recovery_area \ mygod \ backupset \ 2009_05_11 \ o1_mf_nnndf_tag200901_t151129_50hmx55v
_. Bkp mark = tag20090417t151129
Channel ora_disk_1: Recovery completed, time: 00:00:04
The restore is completed from February 5 to February 09.

Start recover from September 5 to September 09
Use channel ora_disk_1

Restoring media...
Media Recovery completed. Time: 00:00:01

The recover is completed from February 5 to February 09.

SQL> alter tablespace users online;

The tablespace has been changed.

SQL> select * from Scott. Dept;

Deptno dname Loc
-------------------------------------
10 Accounting New York
20 research Dallas
30 sales Chicago
40 operations Boston

SQL>

**************************************** **************************************** ***

Experiment 3: part of the data files in the target database are lost due to the full database backup generated by RMAN.
When the database is opened, it is simulated that the users tablespace is damaged or lost. Recovery is also performed when the database is opened.

**************************************** **************************************** ***

When the database is opened, the users tablespace is taken offline and its corresponding data files are deleted using the OS command. Fault simulation!

SQL> alter tablespace users offline immediate;

The tablespace has been changed.

SQL> select count (*) from Scott. myemp;
Select count (*) from Scott. myemp
*
Row 3 has an error:
ORA-00376: file cannot be read at this time 4
ORA-01110: data file 4: 'C: \ oracle \ product \ 10.2.0 \ oradata \ mygod \ users01.dbf'

SQL>

SQL> alter tablespace users online;
Alter tablespace users online
*
Row 3 has an error:
ORA-01157: unable to identify/lock data file 4-see dbwr trace file
ORA-01110: data file 4: 'C: \ oracle \ product \ 10.2.0 \ oradata \ mygod \ users01.dbf'

SQL>

Database recovery is required!

------------------------------ Use RMAN to restore a single tablespace.
C: \ Documents ents and Settings \ Administrator> RMAN

Recovery MANAGER: Release 10.2.0.1.0-production on Monday May 11 16:42:27 2009

Copyright (c) 1982,200 5, Oracle. All rights reserved.

RMAN> connect target sys/SysAdmin @ mygod;

Connect to the target database: mygod (dbid = 85383244)

RMAN> run {
2> restore tablespace users;
3> recover tablespace users;
4>}

Start restore from September 5 to September 09
Use the target database control file to replace the recovery directory
Allocated channel: ora_disk_1
Channel ora_disk_1: SID = 147 devtype = Disk

Channel ora_disk_1: Recovering data file backup set
Channel ora_disk_1: Specifies the data file to be recovered from the backup set.
Restoring data file 00004 to c: \ oracle \ product \ 10.2.0 \ oradata \ mygod \ users01.dbf
Channel ora_disk_1: Reading backup segment E: \ orabak \ 200901__8_1_08kep62q.rmn
Channel ora_disk_1: recovered backup segment 1
Segment handle = E: \ orabak \ 200901__8_1_08kep62q.rmn mark = tag200901_t163538
Channel ora_disk_1: Recovery completed, time: 00:00:04
The restore is completed from February 5 to February 09.

Start recover from September 5 to September 09
Use channel ora_disk_1

Restoring media...
Media Recovery completed. Time: 00:00:03

The recover is completed from February 5 to February 09.

RMAN>

Recovery completed. In the SQL plus window, perform online tablespace operations.

 

SQL> alter tablespace users online;

The tablespace has been changed.

SQL> select count (*) from Scott. myemp;

Count (*)
----------
18432

SQL>

**************************************** **************************************** ***

Tutorial 4: how to restore a backup without a database? The damaged file cannot be a system or undo file.

**************************************** **************************************** ***

Restore a data file without backup. The damaged file cannot be a system or undo file.
Create tablespace testtbs datafile 'C: \ oracle \ product \ 10.2.0 \ oradata \ mygod \ testtbs. dbf' size 10 m;
Alter system switch logfile;
Create Table Scott. com4 (N1 number) tablespace testtbs;
Alter system switch logfile;
Insert into Scott. com4 values (1 );
Commit;
Alter system switch logfile;

SQL> Create tablespace testtbs datafile 'C: \ oracle \ product \ 10.2.0 \ oradata \ mygod \ testtbs. dbf' size 10 m;

The tablespace has been created.

SQL> alter system switch logfile;

The system has been changed.

SQL> Create Table Scott. com4 (N1 number) tablespace testtbs;

The table has been created.

SQL> alter system switch logfile;

The system has been changed.

SQL> insert into Scott. com4 values (1 );

One row has been created.

SQL> commit;

Submitted.

SQL> alter system switch logfile;

The system has been changed.

SQL>

Damage testtbs. DBF
My method: Direct the data file to another directory
Alter tablespace testtbs offline immediate;
Alter database create datafile 'C: \ oracle \ product \ 10.2.0 \ oradata \ mygod \ testtbs. dbf'

As 'C: \ oracle \ product \ 10.2.0 \ oradata \ mygod \ testtbs1.dbf ';

SQL> select * from Scott. com4;
Select * from Scott. com4
*
Row 3 has an error:
ORA-00376: file 6 cannot be read at this time
ORA-01110: data file 6: 'C: \ oracle \ product \ 10.2.0 \ oradata \ mygod \ testtbs1.dbf'

SQL>

You need to use RMAN to restore the database:
Connect to RMAN and run:

 

Run {
Set newname for datafile 6 to 'C: \ oracle \ product \ 10.2.0 \ oradata \ mygod \ testtbs1.dbf ';
}

Recover tablespace testtbs;

RMAN> run {
2> set newname for datafile 6 to 'C: \ oracle \ product \ 10.2.0 \ oradata \ mygod \ testtbs1.dbf ';
3>}

Executing command: Set newname

RMAN> recover tablespace testtbs;

Start recover from September 5 to September 09
Use channel ora_disk_1

Restoring media...

The archive log thread 1 sequence 6 is used as the file c: \ oracle \ product \ 10.2.0 \ flash_recovery_area \ mygod \ archivelog \ 2009_05_11 \ o1_mf_1_6_50hs
Ghmk_.arc exists on disk
Archive log thread 1 sequence 7 is used as the file c: \ oracle \ product \ 10.2.0 \ flash_recovery_area \ mygod \ archivelog \ 2009_05_11 \ o1_mf_20177_50hs
Gtcq_.arc exists on disk
Archive log thread 1 sequence 8 is used as the file c: \ oracle \ product \ 10.2.0 \ flash_recovery_area \ mygod \ archivelog \ 2009_05_11 \ o1_mf_1_8_50hs
Hbws_.arc exists on disk
Archive log file name = c: \ oracle \ product \ 10.2.0 \ flash_recovery_area \ mygod \ archivelog \ 2009_05_11 \ o1_mf_00006_50hsghmk_.arc thread = 1
Sequence = 6
Media Recovery completed. Time: 00:00:02
The recover is completed from February 5 to February 09.

RMAN>

There is a media restoration process in the RMAN restoration process.

In the sqlplus window, bring the users tablespace online, query and confirm that the data has been restored.

Alter tablespace testtbs online;
Select * from Scott. com4;

 

Incomplete recovery of three experiments
**************************************** **************************************** *******************************
Incomplete recovery experiment 1:
**************************************** **************************************** *******************************
Note: Before starting the experiment, you must back up the data.

Set the environment:
Alter session set nls_date_format = 'yyyy-mm-DD: hh24: MI: ss ';

SQL> alter session set nls_date_format = 'yyyy-mm-DD: hh24: MI: ss ';

The session has been changed.

 

 

SQL> select sysdate from dual;

Sysdate
-------------------
2009-05-11: 17: 06: 43

SQL> drop table Scott. myemp;

The table has been deleted.

SQL>

SQL> Create Table Scott. com4 (ID number );

The table has been created.

SQL> insert into Scott. com4 values (1000 );

One row has been created.

SQL> commit;

Submitted.

SQL> alter system switch logfile;

The system has been changed.

You need to retrieve the deleted data table and restore the database to the time point: 17: 06: 43. Perform Incomplete recovery based on time points.

SQL> shutdown immediate;
SQL> startup Mount;

The Oracle routine has been started.

Total system global area 612368384 bytes
Fixed size 1250428 bytes
Variable Size 188746628 bytes
Database buffers 415236096 bytes
Redo buffers 7135232 bytes
The database has been loaded.
SQL>

In RMAN
Run {
Set until time = "to_date ('2017-05-11: 17: 06: 43 ', 'yyyy-mm-DD: hh24: MI: ss ')";
Restore database;
Recover database;
}

C: \ Documents ents and Settings \ Administrator> RMAN

Recovery MANAGER: Release 10.2.0.1.0-production on Monday May 11 17:16:39 2009

Copyright (c) 1982,200 5, Oracle. All rights reserved.

RMAN> connect target sys/SysAdmin @ mygod;

Connected to the target database: mygod (dbid = 85383244, not enabled)

RMAN> run {
2> set until time = "to_date ('2017-05-11: 17: 06: 43 ', 'yyyy-mm-DD: hh24: MI: ss ')";
3> Restore database;
4> recover database;
5>}

Executing command: set until clause
Use the target database control file to replace the recovery directory

Start restore from September 5 to September 09
Allocated channel: ora_disk_1
Channel ora_disk_1: SID = 155 devtype = Disk

Channel ora_disk_1: Recovering data file backup set
Channel ora_disk_1: Specifies the data file to be recovered from the backup set.
Restoring data file 00001 to c: \ oracle \ product \ 10.2.0 \ oradata \ mygod \ system01.dbf
Restoring data file 00002 to c: \ oracle \ product \ 10.2.0 \ oradata \ mygod \ undotbs01.dbf
Restoring data file 00003 to c: \ oracle \ product \ 10.2.0 \ oradata \ mygod \ sysaux01.dbf
Restoring data file 00004 to c: \ oracle \ product \ 10.2.0 \ oradata \ mygod \ users01.dbf
Restoring data file 00005 to c: \ oracle \ product \ 10.2.0 \ oradata \ mygod \ example01.dbf
Restoring data file 00006 to c: \ oracle \ product \ 10.2.0 \ oradata \ mygod \ testtbs1.dbf
Channel ora_disk_1: Reading backup segment E: \ orabak \ 200901__17_1_0hkep7ns.rmn
Channel ora_disk_1: recovered backup segment 1
Segment handle = E: \ orabak \ 200901__17_1_0hkep7ns.rmn mark = tag200901_t170355
Channel ora_disk_1: Recovery completed, time: 00:01:16
The restore is completed from February 5 to February 09.

Start recover from September 5 to September 09
Use channel ora_disk_1

Restoring media...
Media Recovery completed. Time: 00:00:02

The recover is completed from February 5 to February 09.

RMAN>

After the restoration is completed, in the sqlplus window:

SQL> alter database open resetlogs;

The database has been changed.

 

SQL> select count (*) from Scott. myemp;

Count (*)
----------
18432

SQL> select * from Scott. com4;
Select * from Scott. com4
*
Row 3 has an error:
ORA-00942: Table or view does not exist

SQL>

The database has been recovered based on time points ..

Additional information: Database Backup Method

RMAN> backup database format = 'e: \ orabak \ 20090511 _ % S _ % P _ % u. rmn ';

Start backup from September 11 to September 09
Use channel ora_disk_1
Channel ora_disk_1: Start all data file backup Sets
Channel ora_disk_1: Specifies the data file in the backup set.
Input data file fno = 00001 name = c: \ oracle \ product \ 10.2.0 \ oradata \ mygod \ system01.dbf
Input data file fno = 00003 name = c: \ oracle \ product \ 10.2.0 \ oradata \ mygod \ sysaux01.dbf
Input data file fno = 00005 name = c: \ oracle \ product \ 10.2.0 \ oradata \ mygod \ example01.dbf
Input data file fno = 00002 name = c: \ oracle \ product \ 10.2.0 \ oradata \ mygod \ undotbs01.dbf
Input data file fno = 00006 name = c: \ oracle \ product \ 10.2.0 \ oradata \ mygod \ testtbs1.dbf
Input data file fno = 00004 name = c: \ oracle \ product \ 10.2.0 \ oradata \ mygod \ users01.dbf
Channel ora_disk_1: Starting segment 1 from February 5 to February 09
Channel ora_disk_1: Completed segment 1 from February 5 to February 09
Segment handle = E: \ orabak \ 200901__17_1_0hkep7ns.rmn mark = tag200901_t170355 comment = none
Channel ora_disk_1: Backup set completed. elapsed time: 00: 01: 05
Backup is completed from September 11 to September 09.

Start Control File and spfile autobackup from February 5 to February 09
Segment handle = c: \ oracle \ product \ 10.2.0 \ flash_recovery_area \ mygod \ autobackup \ 2009_05_11 \ o1_mf_s_686595902_50htkznj_.bkp comme
Nt = none
The control file and spfile autobackup tasks are completed from February 5 to February 09.