Rman database recovery; key/non-key files, image copies, control files, restore points

Source: Internet
Author: User
Perform full recovery: the key data file of the SYSTEM is lost in ARCHIVELOG mode. If a data file is lost or damaged and the file belongs to the SYSTEM or UNDO tablespace, perform the following steps: 1. the instance may or may not be automatically closed. 2. If the instance is not automatically closed, use SHUTDOWNABORT to close the instance. 3. Load the database

Perform full recovery: the key data file of the SYSTEM is lost in ARCHIVELOG mode. If a data file is lost or damaged and the file belongs to the SYSTEM or UNDO tablespace, perform the following steps: 1. the instance may or may not be automatically closed. 2. If the instance is not automatically closed, use shutdown abort to close the instance. 3. Load the database

Perform full recovery: the system's key data files are lost in ARCHIVELOG mode:

If a data file is lost or corrupted and belongs to the SYSTEM or UNDO tablespace, perform the following steps:

1.
The instance may or may not be automatically closed.
2. If the instance is not automatically closed, use shutdown abort to close the instance.
3.
Load the database.
4.
Restore and restore the missing data files.

5. Open the database.

1. Full recovery of non-critical data files lost in archive Mode

Method 1:

Shutdown abort;

Startup mount;

Alter database create datafile '/home/oracle/test. dbf' as '/home/oracle/test. dbf ';

Recover datafile;

Method 2:

Create tablespace test datafile '/home/oracle/test. dbf' size 10 m;

Rman target/

Backup datafile 5;

Rm test. dbf

Sq> alter database datafile 5 offline;

Restore datafile 5;

Recover datafile 5;

Sq> alter database datafile 5 online;

Select FILE #, STATUS from v $ datafile;

2. full recovery of key data files lost in archive Mode

The backup is the same as the second method.

Bytes --------------------------------------------------------------------------------------------------------------
Restore the image copy:
RMAN> recover copy of database with tag 'daily _ inc ';

RMAN> backup incremental level 1 for recover of copy
With tag 'daily _ inc' database;

RECOVER BACKUP
No image copy created in 1st days
No 1-level Incremental backup is created in 2nd days.
3rd days and later, level 1 Incremental Backup will be created based on the Incremental Backup Recovery copy


Restore image copy: Example
If you run the preceding commands every day, you can obtain continuous update image copies of all database data files at any time.
The chart shows the operations that occur during each operation. Note that this algorithm takes a while to prepare; The policy takes effect after 3rd days.

1st days
The RECOVER command does not perform any operations. The image copy to be restored does not exist. The BACKUP command can be used to create an image copy.

2nd days
The RECOVER command still does not perform any operations. Because Incremental backup does not exist. The BACKUP command creates an incremental BACKUP because a copy of the baseline image has been created in 1st days.
3rd days
The RECOVER command applies the changes in the Incremental backup to the image copy. The BACKUP command will execute another Incremental BACKUP, which will be used to restore the image copy on the seventh day, so that the cycle is made accordingly.

Fast switch to image copy
Perform the following steps:

You can use the image copy of the data file for quick recovery:

1. Take the data file offline.
2. Use the switch to... COPY command TO point TO the image copies of these files.

3. Restore the data file.
4. Bring data files online.

In this case, the database is available and the data file has been recovered. However, if you want to place the data file back to its original location, continue with the following steps:

5. Use the backup as copy command to create an image COPY of the data file in the original location.

6. Take the data file offline.

7. Use the switch to copy command to switch to the COPY created in step 5.

8. Restore the data file.

9. Bring data files online.
You can use this command to restore data files, tablespaces, temporary files, or the entire database. The target file to be switched to must be an image copy.

3. Fast Switching of image copies

Backup as copy datafile 6;

Rm a. dbf

SQL: alter database datafile 6 offline;

Rman> switch datafile '/home/oracle/test. dbf' to copy;

Recover datafile 6;

-- The path and name of the data file after the reply are '/home/oracle/test. dbf ';

Alter database datafile 4 online;

Select FILE #, STATUS from v $ datafile;

Select file_name from dba_data_files;

Backup as copy datafile 4 format'/u01/app/oracle/oradata/orcln/users01.dbf ';

Alter database datafile 4 offline;

Switch datafile '/home/oracle/backup/db_data_D-ORCL_I-4115975543_TS-TEST_FNO-6_38otbf5q.bak' to copy;

Switch datafile 4 to copy;

Recover datafile 4;

Alter database datafile 4 online;

Select file_name from dba_data_files;

Bytes -----------------------------------------------------------------------------------------------------------------

4. Change the name of set newname

Backup as copy datafile 6;

List copy of datafile 6;

Select FILE #, STATUS from v $ datafile;

Run {

Allocate channel c1 device type disk;

SQL "alter database datafile 6 offline ";

Set newname for datafile '/home/oracle/test. dbf' to'/home/oracle/test_new.dbf ';

Restore datafile 6;

Switch datafile all;

Recover datafile 6;

SQL "alter database datafile 6 online ";

}
Bytes --------------------------------------------------------------------------------------------------------------
Restore control files from automatic backup:

RMAN> startup nomount;

RMAN> restore controlfile from autobackup;

RMAN> alter database mount;

RMAN> recover database;

RMAN> alter database open resetlogs;

7. Lost control file, parameter file-The database must be in nomount state

View File Information:

SQL> select name from v $ controlfile;

SQL> show parameter spfile;

SQL> select name from v $ datafile;

Configuration control file Automatic Backup Recovery Control File

Rman target/

Connected to target database: ORCL (DBID = 1353286769)

RMAN> show all;

RMAN> configure controlfile autobackup on;

Full database backup:

RMAN> backup as compressed backupset full database;

Delete, parameter file, control file, Data File

Rm/opt/oracle/oradata/orcl/control01.ctl

Rm/opt/oracle/flash_recovery_area/orcl/control02.ctl

Rm/opt/oracle/oradata/orcl/control03.ctl

Rm/opt/oracle/product/11.2.2/dbs/spfileorcl. ora

Rm/opt/oracle/oradata/orcl/system01.dbf

Rm/opt/oracle/oradata/orcl/sysaux01.dbf

Rm/opt/oracle/oradata/orcl/undotbs01.dbf

Rm/opt/oracle/oradata/orcl/users01.dbf

Rm/home/oracle/tbs_ctl01.dbf

Rman target/

RMAN> set dbid 1353286769 -- if the backup is in

Startup nomount -- the instance can be started even if there is no parameter file

Restore the parameter file:

Restore spfile from '/data/backup/ctl_c-1353286769-20131102-04.bak ';

/* Restore the parameter file from the quick recovery area:

Run {

Restore spfile from autobackup

Recovery area = 'Flash recovery area destination'

Db_name = 'db _ name ';

}

*/

Check whether there are parameters to be modified: mainly depends on the file location

SQL> create pfile from spfile;

Recovery control file:

Restore controlfile to '/opt/oracle/oradata/orcl/control01.ctl' from '/data/backup/ctl_c-1353286769-20131102-04.bak ';

Restore data files:

RMAN> shutdown immediate

RMAN> startup mount

RMAN> restore database;

RMAN> recover database;

RMAN> alter database open;

RMAN-00571: ========================================================== ==============================

RMAN-00569: ==================== error message stack follows ==========================

RMAN-00571: ========================================================== ==============================

RMAN-03002: failure of alter db command at 12:10:46

The ORA-01589: must use RESETLOGS or NORESETLOGS option for database open

RMAN> alter database open resetlogs;
Bytes --------------------------------------------------------------------------------------------------------------
Use Incremental backup to restore a database in NOARCHIVELOG mode:

Startup force nomount;

Restore controlfile;

Alter database mount;

Restore database;

Recover database noredo;

Alter database open resetlogs;

8. Use Incremental backup to restore a database in NOARCHIVELOG Mode

1) revoking the database archivelog Mode

SQL> shutdown immediate;

SQL> startup mount;

SQL> alter database noarchivelog;

SQL> alter database open;

SQL> archive log list;

2) create a test table and Data

Conn zhuxy/zhuxy

Create table c (id number );

Insert into c values (1 );

Commit;

3) level 0 backup in noarchivelog Mode

RMAN> run {

Shutdown immediate;

Startup mount;

Backup as backupset incremental level 0 database;

Alter database open;

}

4) Level 1 backup in noarchivelog Mode

Insert into c values (2 );

Commit;

RMAN> run {

Shutdown immediate;

Startup mount;

Backup as backupset incremental level 1 database;

Alter database open;

}

6) Delete all log files and some data files

7) execute recovery

Rman target/

RMAN> startup mount;

-- Restore the entire database

RMAN> restore database;

-- Restore with the noredo clause

RMAN> recover database noredo;

-- The restoration is complete, and the database is opened in resetlogs mode to restore the loss of all files.

RMAN> alter database open resetlogs;

8) verify the data inserted before the test (before Incremental Backup)

Select * from c;

6. Non-archive lost files

Incomplete recovery: SCN, time, restore origin, or log serial number

1. Determine the target restore point: SCN, time, restore point, or log serial number.

2. Set NLS environment variables.

3. Load the database.

4. RUN the set until, RESTORE, and RECOVER commands to prepare and RUN the RUN block.

5. Use RESETLOGS to open the database.

----- Incomplete recovery-time-based recovery

1) Prepare the environment: RMAN full-Database Backup

RMAN> backup as compressed backupset full database;

2) Prepare the environment: test tables and data

Alter session set nls_date_format = 'yyyy-mm-dd hh24: mi: ss ';

Create table d (c date );

Insert into d values (sysdate );

Commit;

Create table ff (c date );

Insert into ff values (sysdate );

Commit;

Start to mount mode for time-based recovery

Shutdown immediate;

Startup mount;

Run

{SQL 'alter session set nls_date_format = "yyyy-mm-dd hh24: mi: ss "';

Set until time = '00:39:34 ';

Restore database;

Recover database;

Alter database open resetlogs;

}

The time can also be written as follows:

Set until time = "to_date ('2017-11-01 15:29:34 ', 'yyyy-mm-dd hh24: mi: ss ')";

----- Restore origin:

Select count (*) from copy_emp;

Restore origin:

Create restore point before_delete;

Delete from copy_emp where ID> = 10;

Commit;

Alter table copy_emp enable row movement;

Flashback table copy_emp to restore point B;

Bytes --------------------------------------------------------------------------------------------------------------
Restore the database to the new host:

To restore the database, perform the following steps on the restore Host:

1. Configure the ORACLE_SID environment variable.

2. Start RMAN and connect to the target instance in NOCATALOG mode.

3. Set the database identifier (DBID ).

4. Start the instance in NOMOUNT mode.
5.
Restore the server parameter file from the backup set.

6. Close the instance.
7.
Edit the restored initialization parameter file.
8.
Start an instance in NOMOUNT Mode
9. Create a RUN block to execute the following tasks:

-- Restore control files
--
Load Database

10. Create an RMAN recovery script to restore and restore the database.

11. Execute the RMAN script.

12. Use the RESETLOGS option to open the database.

9 restore the database to the new host using backup: oracle software is required for the target database

1) Preparations

Full backup of source database and automatic backup of control files, including parameter files

RMAN> backup as backupset database;

2) copy the RMAN backup file to the target database.

Scp can be used

3) configure the target database

Set environment variables for the target database

Export ORACLE_SID = new_orcl

Start rman to nomount and set dbid (that is, dbid of the source database)

$ Rman target/

RMAN> set dbid 1357901988

RMAN> startup nomount; -- if there is no parameter file, an error is returned, but the instance can be started.

4) restore the parameter file

RMAN> restore spfile to pfile ''from '';

Edit the pfile parameter file: file location, database instance name..., and create a directory

5) Start the database to nomount mode.

SQL> create spfile from pfile;

SQL> startup nomount;

6) restore the control file

$ Rman target/

RMAN> restore controlfile to ''from '';

RMAN> alter database mount;

7) restore data files

Run {

Set newname for datafile 1 TO '/u01/app/oracle/oradata/orcl/system01.dbf ';

Set newname for datafile 2 TO '/u01/app/oracle/oradata/orcl/sysaux01.dbf ';

Set newname for datafile 3 TO '/u01/app/oracle/oradata/orcl/undotbs01.dbf ';

Set newname for datafile 4 TO '/u01/app/oracle/oradata/orcl/users01.dbf ';

Set newname for datafile 5 TO '/u01/app/oracle/oradata/orcl/example01.dbf ';

Set newname for datafile 6 TO '/u01/app/oracle/oradata/orcl/test. dbf ';

Restore database;

Switch datafile all;

Recover database ;}

-- Rename Log File

Run {

SQL "ALTER DATABASE RENAME FILE''/u01/app/oracle/oradata/ENMOEDU/redo01.log''

''/U01/app/oracle/oradata/orcl/redo01.log ''";

SQL "ALTER DATABASE RENAME FILE''/u01/app/oracle/oradata/ENMOEDU/redo02.log''

''/U01/app/oracle/oradata/orcl/redo02.log ''";

SQL "ALTER DATABASE RENAME FILE''/u01/app/oracle/oradata/ENMOEDU/redo03.log''

''/U01/app/oracle/oradata/orcl/redo03.log ''";

}

RMAN> alter database open resetlogs;

8) the location of the temporary file is incorrect.

SQL> select file_name from dba_temp_files;

SQL> create temporary tablespace tempa tempfile

'/U01/app/oracle/oradata/orcl/tempa01.dbf' size 10 m reuse autoextend on maxsize 200 m;

SQL> alter database default temporary tablespace tempa;

SQL> drop tablespace temp;

SQL> select file_name from dba_temp_files;

Note: This test changes the Instance name.
Bytes --------------------------------------------------------------------------------------------------------------
Perform disaster recovery:

Disaster means that the entire target database, directory database, all current control files, all online redo log files, and all parameter files are lost.

Disaster recovery includes restoring and restoring the target database.

Minimum requirements for backup sets:

1. data file backup
2.
Corresponding archived and redone log files
3.
Automatic Backup of at least one control file

Basic Process:
1.
Restore automatic backup of server parameter files.

2. Start the target database instance.

3. Restore the control file from an automatic backup.

4. Load the database.

5. Restore the data file.

6. Restore the data file.

7. Use the RESETLOGS option to open the database.

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.