Common rman backup and recovery commands

Common rman backup and recovery commands SQL code Oracle official documentation contains a complete description of the rman command, we can refer. The following are examples of commonly used commands: www.2cto.com common Rman commands: 1. RMAN settings 1. display the default RMAN configuration. The show command can be used to display the current configuration parameters. For example, use show all to display ALL current configurations. RMAN> show all; RMAN configuration parameters: configure retention policy to recovery window of 3 DAYS; configure backup optimization off; # default configure default device type to disk; # default configure controlfile autobackup on; configure controlfile autobackup format for device type disk to 'd:/backup/% F'; configure device type disk parallelism 1; # default CONFIGURE DATAFILE BACKUP COPIES FOR DEVICE TYPE DIS K to 1; # default configure archivelog backup copies for device type disk to 1; # default configure maxsetsize to unlimited; # default configure snapshot controlfile name to 'f: \ ORAHOME1 \ DATABASE \ SNCFJSSWEB. ORA '; # default RMAN> note: the configuration item is followed by # default, indicating that the item is still the initial configuration and has not been modified. Www.2cto.com this command is also quite flexible to use, and later with different types of configuration parameters, you can display different types of configuration, such as: show channel; show device type; show default device type; 2. create a recovery Directory: Step 1: create a tablespace used to restore the directory in the directory database: SQL> create tablespace rman_ts datafile 'd: \ Oracle \ oradata \ rman \ rman_ts.dbf' size 20 M; step 2: create an RMAN user in the directory database and authorize: SQL> create user rman identified by rman default tablespace rman_ts temporary tablespace temp quota unlimited on rman_ts; the user has created. SQL> grant recovery_catalog_owner connect, resource to rman; authorization successful. Step 3, create recovery directory C: \> rman catalog rman/rman recovery manager in the directory database: Version 8.1.6.0.0-Production RMAN-06008: connect to recovery directory database RMAN-06428: recovery directory RMAN> create catalog tablespace rman_ts; RMAN-06431: Recovery directory created 2. start RMAN 2. 1. use RMAN without the recovery directory to set the Oracle_SID of the target database. Execute: oracle @ myserve :~ $ Rman target/2.2.use RMAN oracle with recovery directory @ myserve :~ $ Rman target/catalog rman/rman 2. 3. register the database in the recovery Directory: RMAN> register database ;. to log out of the target database, you must obtain the database ID code (DB_ID) and database key value (DB_KEY ). When connecting to the target database, DB_ID will be obtained and connected to the target database to query the db table: SQL> select * from db; DB_KEY DB_ID CURR_DBINC_KEY ---------- ---------------- 1 3021445076 2 SQL> execute dbms_rcvcat.unregisterdatabase (); PL/SQL has been completed successfully. 3. use rman to back up the database: when specifying the backup file name, you can use the following match character: % c: When multiple backups are generated, used to specify the backup file ID % d: used to specify database name % e: used to specify the serial number of the archive log % p: used to specify the serial number of the backup part in the backup set % s: used to specify the serial number of the Backup set % n: used to specify the tablespace name % f: used to specify the absolute file number. To prevent the establishment of a backup set, the error matching character % s is required. If you want to create multiple backup file, the matching character % p is required. If you want to create multiple backup copy copies, the matching character % c is required. full database backup set backup database 1 to sexual backup: RMAN> shutdown immediate RMAN> startup mount RMAN> backup database format = '/opt/oracle/rmanbak/% d _ % s. dbf'; RMAN> alter database open; RMAN> SQL 'alter sys Tem archive log current 'Non-inductive backup (in open state): RMAN> backup database format ='/opt/oracle/rmanbak/% d _ % s. dbf'; RMAN> SQL 'alter system archive log current '; exempt tablespace: RMAN> configure exclude for tablespace ts_name; // You can exclude ts_name when backing up the database; RMAN> backup database format = '/opt/oracle/rmanbak/% d _ % s. dbf'; RMAN> SQL 'alter system archive log current '; view database backup: RMAN> list backup of database; tablespace backup set (only applicable to archivelog): RMAN> Backup tablespace ts_name format = '/opt/oracle/rmanbak/mongon_mongos.pdf'; view tablespace backup: RMAN> list backup of tablespace taplespace_name; query the data file corresponding to the table space and its serial number Select file_name, file_id, tablespace_name from dba_data_file; backup Data File RMAN> backup datafile 5 format = '/opt/oracle/rmanbak/% n _ % f _ % s. dbf'; view the backup rman of the specified data file> list backup of datafile n; RMAN> list backup of datafile '/opt/oracle/rmanbak/JWEB. ORA '; controls the file backup set to back up the current Control File RMAN> backup current controlfile format = '/opt/oracle/rmanbak/% d _ % s. when backing up other data files, ctl also backs up the control file RMAN> backup datafile 5 format = '/opt/oracle/rmanbak/% n _ % f _ % s. dbf '2> include current controlfile; automatic backup control file RMAN> configure controlfile autobackup on during backup; if you want to view the backup control file, you can use: RMAN> list backup of controlfile; backup spfile RMAN> backup spfile format = '/opt/oracle/rmanbak/% d _ % s. par'; backup gauge log: Back up all the gauge logs: RMAN> backup Rchivelog all 2. Use the plus archivelog parameter during BACKUP. For example, RMAN> backup database plus archivelog automatically backs up all archive files while backing up the database. What is the difference between this method and the above? The difference is too obvious. BACKUP ..... the plus archivelog Command performs the following steps in sequence during the backup process: 1>. run the alter system archive log current statement to ARCHIVE the CURRENT redolog. 2> run the backup archivelog all command to back up ALL archived logs. 3>. Execute the BACKUP command to back up the specified item. 4>. Run alter system archive log current again to ARCHIVE the CURRENT redolog. 5>. Back up the newly generated archive files that have not yet been backed up. View the log backup: RMAN> list backup of archivelog all; displays the backup information: View: V $ ARCHIVED_LOG: displays information about all archived log image copies SQL> col name format a45; SQL> select sequence #, first_change # from v $ archived_log 2 where status = 'a'; V $ BACKUP_DATAFILE: used to display backup information of control files and data files V $ BACKUP_PIECE: SQL> select. file #, B. handle,. blocks * block_size byte 2 from v $ backup_datafile a, v $ backup_piece B 3 where. set_stamp = B. set_stamp and. status = 'A' V $ BACKUP_RED OLOG displays information about the archive log backup set. Each gauge log backup set can contain one or more archive logs. SQL> select distinct. handle, B. sequence #, B. first_change #, B. blocks 2 from v $ backup_piece a, v $ backup_redolog B 3 where. set_stamp = B. set_stamp and. status = 'A' V $ BACKUP_CORRUPTION uption: displays the corrupted data block information detected when the BACKUP command is executed. SQL> select file #, block #, blocks, marked_0000upt 2 from v $ backup_0000uption; Delete backup: 1. delete old backup when RMAN is used for backup, RMAN determines the old backup Based on the Backup Redundancy policy. RMAN> delete obsolete; 2. delete the EXPIRED backup and run the crosscheck command to check the backup set. The backup set is marked as EXPIRED. To delete the corresponding backup records, run the delete expired backup command. RMAN> delete expired backup; 3. delete the EXPIRED copy RMAN> delete expired copy; 4. delete the specific backup set RMAN> delete backupset 19; 5. delete the specific backup file RMAN> delete backuppiece 'd: \ backup \ DEMO_19.bak '; 6. delete All backup sets RMAN> delete backup; 7. delete the specific image copy RMAN> delete datafilecopy 'd: \ backup \ DEMO_19.bak '; 8. delete all image copies RMAN> delete copy; 9. delete the input object RMAN> delete archivelog all delete input after backup; RMAN> delete backupset 22 format = 'd: \ backup \ % u. bak ''delete input; RMAN recovery RMAN full recovery refers to the use of the RESTORE command to dump data file backup when a media failure occurs in the data file, use the RECOVER command to restore the data file to the failed State. RMAN Incomplete recovery: When a media failure occurs in the data file, use the RESTORE command to dump the data file backup and use the RECOVER command to RESTORE the database to the backup point and the time of failure. recover database V $ RECOVER_FILE: Determine the data file to be recovered SQL> SELECT file #, error from v $ recover_file; all data files are accidentally deleted $ rman target/catalog rman/rman> startup force mount rman> run {2> restore database; 3> recover database; 4> SQL 'alter database open'; 5 >}when the disk where the data file is located encounters a hardware fault, the data file cannot be dumped to the original location, run {2> startup force mount; 2> set newname for datafile 1 to '/opt/datafile/system01.pdf'; 3>. 4> // specify the new location of the Data File 5> set newname for datafile 6 to '/opt/datafile/sdlfile' 6> restore database 7> switch datafile all; // change the location and name of the data file recorded in the control file 8> recover database; 9> SQL 'alter database open' 10>} RMAN> report schema; // view the data file after recovery. Example of restoring the tablespace data file: the data file in the SYSTEM tablespace is deleted. RMAN> run {2> startup force mount; 3> restore datafile 1; 4> recover datafile 1; 5> SQL 'alter database open';} example: the disk where the SYSTEM tablespace data file is located is faulty. RMAN> run {2> startup force mount 3> set name for datafile 1 to '/opt/datafile/system01.dbf' // you can specify a new tablespace file. location 4> restore datafile 1; // dump Data File 5>. switech datafile 1; 6> recover datafile 1; 7> SQL 'alter database open';} example of corrupted data files in the open state: the data file is accidentally deleted by RMAN> run {2> startup force mount; 3> SQL 'alter database datafile 4 offline '; // offline corrupted data file 4> SQL 'alter database open'; // open database 5> restore datafile 4; // dump data file 6> recover datafile 4; // restore data file 7> SQL 'alter database datafile 4 online'; // online recovered data file} Example 2: the disk where the data file is located is damaged. RMAN> run {2> startup force mount; 3> SQL 'alter database datafile 4 offline '; // offline corrupted data file 4> SQL 'alter database open'; // open the database 5> set newname for datafile 4 to '/opt/datafile/user01.pdf '; // specify the recovery Location 5> restore datafile 4; // dump data file 6> recover datafile 4; // restore data file 7> SQL 'alter database datafile 4 online '; // data file after online recovery} restore tablespace: Example: The tablespace data file is accidentally deleted by RMAN> run {2> SQL 'alter tablespace users offline for recover '; // offline tablespace 3> restore tablespace user; 4> recover tablespace user; 5> SQL 'alter tablespace users';} example: disk fault occurs on the disk where the tablespace is located. RMAN> run {2> SQL 'alter tablespace users offline for recover '; // offline tablespace 3> set newname for datafile 4 to '/opt/datafile/user01.dbf'; 4> restore tablespace user; 5> switch datafile all; 6> recover tablespace user; 5> SQL 'alter tablespace users online';} the following error occurs when the block media is restored: SQL> SELECT * FROM SCOTT. CUSTOMERS; ORA-01578: oracle data block upted (FILE #5, BLOCK #21) ORA-01110: data file 5: '/opt/oracle/oradata/mydb/sdl. dbf 'rman> blockrecover device type disk 2> DATAFILE 5 BLOCK 128,; Incomplete recovery based on time: $ export nls_date_format = 'yyyy-mm-dd hh24: mi: ss' // specify the date format RMAN> run {2> startup force mount; // mount the database 3> set until time = '2017-7-26 20:49:00 '; // recovery time point 4> restore database; // dump Data File 5> recover database; // restore the database based on the time point 6> SQL 'alter database open resetlogs '; // open the DATABASE and recreate the redolog file 7 >}determine the recovered scn SQL based on SCN> SELECT CURRENT_SCN FROM V $ DATABASE; RMAN> run {2> startup force mount; 3> set until scn = n; // set the restore source SCN 4> restore database; 5> recover database; 6> SQL 'alter database open resetlogs '; 7 >}log-based: the following error occurs when archiving logs with log numbers cannot be located: Rman-06025: no backup of log thread 1 seq 6 lowscn 531976 found to restore RMAN> run {2> startup force mount; 3> set until sequence = n; // The log number is usually a log number that cannot be located 4> restore database; 5> recover database; 6> SQL 'alter database open resetlogs'; 7 >} Based on the backup control file: this method can be used when the accidentally deleted tablespace or some control files in the database are corrupted $ set nls_date_format = 'yyyy-mm-dd hh24: mi: ss '; $ rman target/catalog rman/rman> startup force nomount RMAN> set dbid = 3286265633; RMAN> restore controlfile from autobackup maxseq 6; RMAN> alter database mount; RMAN> run {2> set until time = '2017-7-26 21:07:00 '3> restore database; 4> recover database; 5> SQL 'alter database open resetlogs '; 6 >} after Incomplete recovery, we recommend that you delete all the earlier backups and back up RMAN again> run {2> delete noprompt backup; 3> delete noprompt copy; 4> backup database format = '/opt/ora_bak/mongod_mongos.pdf'; 5> SQL 'alter system archive log current ';}