Go back to OCM 12c Preparation Project
Hands On Lab – Index
[1] Use Recovery Manager to perform database backups
1.1 Backup the whole database
1.2 BACKING UP TABLESPACE AND DATAFILES
1.3 BACKING UP CONTROLFILE AND SPFILE
1.4 BACKING UP ARCHIVELOG
1.5 Taking INCREMENTAL BACKUPS
1.6 INCREMENTALLY UPDATED BACKUP
1.7 Tacking Multisectional BACKUPS
[2] Create different types of RMAN backups to cater for different peformance and retention requirements
[3] Complete database restore and recovery operations
3.1 Restore Parameter File
3.2 Restore from loss of controlfile without RMAN
3.3 Restore controlfile without backup.
3.4 Recover the control-file by manually recreating controlfile.
3.5 Restore Controlfile with RMAN
3.6 Restore datafile using RMAN
3.7 Recovering datafile without having any backup
3.8 Using SET NEWNAME command to change location of the restored datafile
3.9 Restore/Recover a tablespace
3.10 Restoring archived redo log files.
3.11 Perform Block media recovery
3.12 Performing disaster recovery
3.13 Perform incomplete recovery (Time,SCN and Change based)
3.14 Recover database to the previous incarnation.
3.15 Performing tablespace point in time recovery (TSPITR)
[4] Verify the rman backups
4.1 Checking for Block Corruption with the VALIDATE Command
4.2 Validating Database Files with BACKUP VALIDATE
4.3 Validating Backups Before Restoring Them
4.4 Validating CDBs and PDBs
[1] Use Recovery Manager to perform database backups
Back up the whole system or single tablespaces / datafiles RMAN> BACKUP DATABASE; Note: If you specify only the minimum required options for an RMAN command such as BACKUP DATABASE, then RMAN automatically determines the destination device, locations for backup output, and a backup tag based on your configured environment and built-in RMAN defaults. You can also provide arguments to BACKUP to override these defaults. The most typical options are described in the following sections: -> Specifying the Device Type for an RMAN Backup RMAN> BACKUP DEVICE TYPE DISK DATABASE; -> Specifying Backup Set or Copy for an RMAN Backup to Disk Example 9-2 Making Image Copies RMAN > BACKUP AS COPY DEVICE TYPE DISK DATABASE; Example 9-3 Making Backup Sets RMAN > BACKUP AS BACKUPSET DATABASE; ->Specifying a Format for RMAN Backups BACKUP DATABASE FORMAT "/disk1/backup_%U"; # specifies a location on the file system BACKUP DATABASE FORMAT '+dgroup1'; # specifies an ASM disk group -> Specifying Multiple Formats for Disk Backups RUN { ALLOCATE CHANNEL disk1 DEVICE TYPE DISK FORMAT '/disk1/%d_backups/%U'; ALLOCATE CHANNEL disk2 DEVICE TYPE DISK FORMAT '/disk2/%d_backups/%U'; ALLOCATE CHANNEL disk3 DEVICE TYPE DISK FORMAT '/disk3/%d_backups/%U'; BACKUP AS COPY DATABASE; } You can distribute backups in this manner by default in the future, by configuring channels as follows: CONFIGURE DEVICE TYPE DISK PARALLELISM 3; CONFIGURE DEFAULT DEVICE TYPE TO DISK; CONFIGURE CHANNEL 1 DEVICE TYPE DISK FORMAT '/disk1/%d_backups/%U'; CONFIGURE CHANNEL 2 DEVICE TYPE DISK FORMAT '/disk2/%d_backups/%U'; CONFIGURE CHANNEL 3 DEVICE TYPE DISK FORMAT '/disk3/%d_backups/%U'; BACKUP AS COPY DATABASE; -> Specifying Tags for an RMAN Backup Example 9-4 Applying a Tag to a Backup Set RMAN> BACKUP AS BACKUPSET COPIES 1 DATAFILE 7 TAG mondaybkp; Example 9-5 Applying Tags to Image Copies RMAN> BACKUP AS COPY TABLESPACE users, tools TAG mondaycpy; -> Making Compressed Backups Example 9-7 Making Compressed Backups RMAN> BACKUP AS COMPRESSED BACKUPSET DATABASE PLUS ARCHIVELOG; -> Specifying Multisection Incremental Backups ------------------------- Backup the whole database ------------------------- -> BACKUP DATABASE -bash-4.1$ rman target / Recovery Manager: Release 12.1.0.2.0 - Production on Tue Aug 13 09:31:07 2019 Copyright (c) 1982, 2014, Oracle and/or its affiliates. All rights reserved. connected to target database: CDB1 (DBID=989477122) RMAN> CONFIGURE CONTROLFILE AUTOBACKUP ON; RMAN> CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '/u01/app/oracle/backup1/ctl_autobackup_%F.bkp'; RMAN> BACKUP DATABASE PLUS ARCHIVELOG DELETE INPUT FORMAT '/u01/app/oracle/backup2/full_backup_%U.bkp'; RUN { ALLOCATE CHANNEL ch1 DEVICE TYPE DISK FORMAT '/u01/app/oracle/backup1/%U.bkp'; ALLOCATE CHANNEL ch2 DEVICE TYPE DISK FORMAT '/u01/app/oracle/backup2/%U.bkp'; ALLOCATE CHANNEL ch3 DEVICE TYPE DISK FORMAT '/u01/app/oracle/backup3/%U.bkp'; BACKUP DATABASE PLUS ARCHIVELOG DELETE INPUT; } RMAN> CONFIGURE DEVICE TYPE DISK PARALLELISM 2; RMAN> CONFIGURE DEFAULT DEVICE TYPE TO DISK; RMAN> CONFIGURE CHANNEL 1 DEVICE TYPE DISK FORMAT '/u01/app/oracle/backup1/%U.bk'; RMAN> CONFIGURE CHANNEL 2 DEVICE TYPE DISK FORMAT '/u01/app/oracle/backup2/%U.bkp'; RMAN> BACKUP DATABASE PLUS ARCHIVELOG DELETE INPUT; -> IMAGE COPY RMAN> BACKUP AS COPY DATABASE PLUS ARCHIVELOG DELETE INPUT; -> BACKUPSET RMAN> BACKUP AS BACKUPSET DATABASE; RMAN> backup as backupset database delete input ; -> COMPRESSED BACKUPSET BACKUP AS COMPRESSED BACKUPSET DATABASE PLUS ARCHIVELOG; RMAN> BACKUP AS COMPRESSED BACKUPSET DATABASE PLUS ARCHIVELOG DELETE ALL INPUT; ----------------------------------- BACKING UP TABLESPACE AND DATAFILES ----------------------------------- RMAN> BACKUP TABLESPACE USERS; RMAN> BACKUP DATAFILE 3,6; --------------------------------- BACKING UP CONTROLFILE AND SPFILE --------------------------------- RMAN> BACKUP DATAFILE 5 INCLUDE CURRENT CONTROLFILE; RMAN> BACKUP AS COPY CURRENT CONTROLFILE FORMAT '/u01/app/oracle/backup1/controlfile_backup%F.bkp'; RMAN> BACKUP SPFILE; --------------------- BACKING UP ARCHIVELOG --------------------- RMAN> BACKUP DATAFILE 5 PLUS ARCHIVELOG; RMAN> BACKUP DATAFILE 6 PLUS ARCHIVELOG DELETE INPUT; RMAN> BACKUP DATAFILE 7 PLUS ARCHIVELOG DELETE ALL INPUT; --------------------------- Taking INCREMENTAL BACKUPS --------------------------- SQL> ALTER DATABASE ENABLE BLOCK CHANGE TRACKING USING FILE '/u01/app/oracle/backup1/bct.trc'; RMAN> BACKUP INCREMENTAL LEVEL 0 DATABASE; RMAN> BACKUP INCREMENTAL LEVEL 1 DATABASE; SQL> ALTER DATABSE DISABLE BLCOK CHANGE TRACKING; ---------------------------- INCREMENTALLY UPDATED BACKUP ---------------------------- SQL> ALTER DATABASE ENABLE BLOCK CHANGE TRACKING USING FILE '/u01/app/oracle/backup2/bct.trc'; RMAN> RUN{ BACKUP INCREMENTAL LEVEL 1 FOR RECOVER OF COPY WITH TAG 'mydb_incr_bkp' DATABASE; RECOVER COPY OF DATABASE WITH TAG 'mydb_incr_bkp'; } CREATE TABLESPACE mytbs01 datafile '/u01/app/oracle/oradata/cdb1/pdb1/mytbs01.dbf' size 10M; SQL> CREATE TABLE tbl_test01 (ID NUMBER,NAME VARCHAR2(10)) tablespace mytbs01; SQL> INSERT INTO tbl_test01 VALUES (1,'oracle'); SQL> COMMIT; SQL> SELETE * FROM tbl_test01; RMAN> RUN { BACKUP INCREMENTAL LEVEL 1 FOR RECOVER OF COPY WITH TAG 'mydb_incr_bkp' DATABASE; RECOVER COPY OF DATABASE WITH TAG 'mydb_incr_bkp'; } -bash-4.1$ mv mytbs01.dbf mytbs01.dbf.bkp SQL> ALTER SYSTEM FLUSH BUFFER_CACHE; System altered. SQL> select * from tbl_mytest01; select * from tbl_mytest01 * ERROR at line 1: ORA-01116: error in opening database file 26 ORA-01110: data file 26: '/u01/app/oracle/oradata/cdb1/mytbs01.dbf' ORA-27041: unable to open file Linux-x86_64 Error: 2: No such file or directory Additional information: 3 RMAN> LIST COPY OF DATAFILE 26; List of Datafile Copies ======================= Key File S Completion Time Ckp SCN Ckp Time ------- ---- - -------------------- ---------- -------------------- 35 26 A Aug 13 2019 11:03:42 2629489 Aug 13 2019 11:03:41 Name: /u01/app/oracle/backup2/data_D-CDB1_I-989477122_TS-MYTBS01_FNO-26_96u93l8d.bkp Tag: MY_INCR_BKP RMAN> SWITCH DATAFILE 26 TO COPY; RMAN-00571: =========================================================== RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS =============== RMAN-00571: =========================================================== RMAN-03002: failure of switch to copy command at 08/13/2019 11:05:48 RMAN-06572: database is open and datafile 26 is not offline SQL> alter database datafile 26 offline; Database altered. RMAN> SWITCH DATAFILE 26 TO COPY; datafile 26 switched to datafile copy "/u01/app/oracle/backup2/data_D-CDB1_I-989477122_TS-MYTBS01_FNO-26_96u93l8d.bkp RMAN> RECOVER DATAFILE 26; Starting recover at Aug 13 2019 11:07:05 using channel ORA_DISK_1 using channel ORA_DISK_2 starting media recovery media recovery complete, elapsed time: 00:00:00 Finished recover at Aug 13 2019 11:07:06 SQL> alter database datafile 26 online; Database altered. SQL> SELECT * FROM tbl_mytest01; ID NAME ---------- ---------- 1 ORACLE ------------------------------ Tacking Multisectional BACKUPS ------------------------------ RMAN> RUN { ALLOCATE CHANNEL ch1 DEVICE TYPE DISK ; ALLOCATE CHANNEL ch2 DEVICE TYPE DISK ; BACKUP SECTION SIZE 200M DATAFILE 3; }
[2] Create different types of RMAN backups to cater for different peformance and retention requirements
Monitor RMAN Backup/Restore Progress #-> Identify the RMAN process. col clinet_info FORMAT a30 SET LINESIZE 150 SELECT b.sid,b.serial#,a.spid,b.client_info FROM v$process a,v$session b WHERE a.addr = b.paddr AND b.client_info LIKE '%rman%'; #-> To get more statistical information about backups. COL input_bytes_per_sec_display FORMAT a10 COL output_bytes_per_sec_display FORMAT a10 COL time_taken_display FORMAT a20 SELECT session_recid,input_bytes_per_sec_display,output_bytes_per_sec_display, time_taken_display,end_time FROM v$rman_backup_job_details ORDER BY end_time; SQL> desc V$rman_backup_job_details Name Null? Type ---------------------------------------------- SESSION_KEY NUMBER SESSION_RECID NUMBER SESSION_STAMP NUMBER COMMAND_ID VARCHAR2(33) START_TIME DATE END_TIME DATE INPUT_BYTES NUMBER OUTPUT_BYTES NUMBER STATUS_WEIGHT NUMBER OPTIMIZED_WEIGHT NUMBER OBJECT_TYPE_WEIGHT NUMBER OUTPUT_DEVICE_TYPE VARCHAR2(17) AUTOBACKUP_COUNT NUMBER BACKED_BY_OSB VARCHAR2(3) AUTOBACKUP_DONE VARCHAR2(3) STATUS VARCHAR2(23) INPUT_TYPE VARCHAR2(13) OPTIMIZED VARCHAR2(3) ELAPSED_SECONDS NUMBER COMPRESSION_RATIO NUMBER INPUT_BYTES_PER_SEC NUMBER OUTPUT_BYTES_PER_SEC NUMBER INPUT_BYTES_DISPLAY VARCHAR2(4000) OUTPUT_BYTES_DISPLAY VARCHAR2(4000) INPUT_BYTES_PER_SEC_DISPLAY VARCHAR2(4000) OUTPUT_BYTES_PER_SEC_DISPLAY VARCHAR2(4000) TIME_TAKEN_DISPLAY VARCHAR2(4000) CON_ID NUMBER #-> To monitor the ongoing job process of RMAN backup COL opname FORMAT a30 SELECT sid,serial#,sofar,totalwork,opname,sofar/totalwork * 100 "% Complete FROM v$session_longops WHERE opname LIKE 'RMAN%' AND opname NOT LIKE '%aggregate%' AND totalwork <> 0 AND sofar != totalwork; #-> Identify source of backup or restore bottlenecks. SELECT filename,sid,serial,close_time,long_waits / io_count as ratio FROM v$backup_async_io where type != ' AGGREGATE' AND SID = &SID AND SERIAL = &SERIAL ORDER BY ratio desc; Insufficient Memory If you realized from the alert.log file that RMAN can't start IO slaves due to insuficent memory,increase LARGE_POOL_SIZE parameter in the database level. LARGE_POOL_SIZE = number of allocated channels * (16 MB + (4 * size of tape buffer)) Specify the backup duration #-> if you run this command at 6PM then backup wil be either run till 9PM or terminate and report an error. BACKUP DURATION 3:00 DATABASE; Example-1: -bash-4.1$ rman target / RMAN> backup duration 00:02 database; Starting backup at Aug 15 2019 10:01:29 using target database control file instead of recovery catalog allocated channel: ORA_DISK_1 channel ORA_DISK_1: SID=36 device type=DISK allocated channel: ORA_DISK_2 channel ORA_DISK_2: SID=18 device type=DISK channel ORA_DISK_1: starting full datafile backup set channel ORA_DISK_1: specifying datafile(s) in backup set input datafile file number=00003 name=/u02/app/oracle/oradata/cdb1/data_D-CDB1_TS-SYSAUX_FNO-3 input datafile file number=00026 name=/u02/app/oracle/oradata/cdb1/data_D-CDB1_TS-MYTBS01_FNO-26 input datafile file number=00033 name=/u02/app/oracle/oradata/cdb1/data_D-CDB1_TS-USER2_FNO-33 input datafile file number=00031 name=/u02/app/oracle/oradata/cdb1/data_D-CDB1_TS-T5_FNO-31 input datafile file number=00028 name=/u02/app/oracle/oradata/cdb1/data_D-CDB1_TS-T2_FNO-28 channel ORA_DISK_1: starting piece 1 at Aug 15 2019 10:01:32 channel ORA_DISK_2: starting full datafile backup set channel ORA_DISK_2: specifying datafile(s) in backup set input datafile file number=00001 name=/u02/app/oracle/oradata/cdb1/data_D-CDB1_TS-SYSTEM_FNO-1 input datafile file number=00029 name=/u02/app/oracle/oradata/cdb1/data_D-CDB1_TS-T3_FNO-29 input datafile file number=00030 name=/u02/app/oracle/oradata/cdb1/data_D-CDB1_TS-T4_FNO-30 input datafile file number=00004 name=/u02/app/oracle/oradata/cdb1/data_D-CDB1_TS-UNDOTBS1_FNO-4 channel ORA_DISK_2: starting piece 1 at Aug 15 2019 10:01:32 channel ORA_DISK_1: finished piece 1 at Aug 15 2019 10:02:39 piece handle=/u01/app/oracle/fast_recovery_area/CDB1/backupset/2019_08_15/o1_mf_nnndf_TAG20190815T100131_go9r14hz_.bkp tag=TAG20190815T100131 comment=NONE channel ORA_DISK_1: backup set complete, elapsed time: 00:01:07 channel ORA_DISK_1: starting full datafile backup set channel ORA_DISK_1: specifying datafile(s) in backup set input datafile file number=00009 name=/u02/app/oracle/oradata/cdb1/data_D-CDB1_TS-SYSAUX_FNO-9 input datafile file number=00035 name=/u01/app/oracle/oradata/cdb1/pdb1/tc.dbf input datafile file number=00010 name=/u02/app/oracle/oradata/cdb1/data_D-CDB1_TS-USERS_FNO-10 input datafile file number=00032 name=/u02/app/oracle/oradata/cdb1/data_D-CDB1_TS-T1_FNO-32 input datafile file number=00034 name=/u02/app/oracle/oradata/cdb1/data_D-CDB1_TS-T2_FNO-34 channel ORA_DISK_1: starting piece 1 at Aug 15 2019 10:02:40 channel ORA_DISK_2: finished piece 1 at Aug 15 2019 10:02:40 piece handle=/u01/app/oracle/fast_recovery_area/CDB1/backupset/2019_08_15/o1_mf_nnndf_TAG20190815T100131_go9r155h_.bkp tag=TAG20190815T100131 comment=NONE channel ORA_DISK_2: backup set complete, elapsed time: 00:01:08 channel ORA_DISK_2: starting full datafile backup set channel ORA_DISK_2: specifying datafile(s) in backup set input datafile file number=00007 name=/u02/app/oracle/oradata/cdb1/data_D-CDB1_TS-SYSAUX_FNO-7 channel ORA_DISK_2: starting piece 1 at Aug 15 2019 10:02:40 channel ORA_DISK_1: finished piece 1 at Aug 15 2019 10:03:35 piece handle=/u01/app/oracle/fast_recovery_area/CDB1/8F95F488F27F26B1E0536638A8C085A3/backupset/2019_08_15/o1_mf_nnndf_TAG20190815T100131_go9r38bg_.bkp tag=TAG20190815T100131 comment=NONE channel ORA_DISK_1: backup set complete, elapsed time: 00:00:55 RMAN-03009: failure of backup command on ORA_DISK_1 channel at 08/15/2019 10:03:35 ORA-19591: backup aborted because job time exceeded duration time continuing other job steps, job failed will not be re-run channel ORA_DISK_2: finished piece 1 at Aug 15 2019 10:03:36 piece handle=/u01/app/oracle/fast_recovery_area/CDB1/8F95C4062AA822B8E0536638A8C060CB/backupset/2019_08_15/o1_mf_nnndf_TAG20190815T100131_go9r38os_.bkp tag=TAG20190815T100131 comment=NONE channel ORA_DISK_2: backup set complete, elapsed time: 00:00:56 RMAN-00571: =========================================================== RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS =============== RMAN-00571: =========================================================== RMAN-03009: failure of backup command on ORA_DISK_1 channel at 08/15/2019 10:03:35 ORA-19591: backup aborted because job time exceeded duration time #-> If partial parameter is specified, instead of giving an error message, RMAN display a list of files that are not backed up; RMAN > BACKUP DURATION 3:00 PARTIAL DATABASE; Example-2: RMAN> backup duration 00:02 partial database; Starting backup at Aug 15 2019 10:04:17 using channel ORA_DISK_1 using channel ORA_DISK_2 channel ORA_DISK_1: starting full datafile backup set channel ORA_DISK_1: specifying datafile(s) in backup set input datafile file number=00005 name=/u02/app/oracle/oradata/cdb1/data_D-CDB1_TS-SYSTEM_FNO-5 channel ORA_DISK_1: starting piece 1 at Aug 15 2019 10:04:17 channel ORA_DISK_2: starting full datafile backup set channel ORA_DISK_2: specifying datafile(s) in backup set input datafile file number=00008 name=/u02/app/oracle/oradata/cdb1/data_D-CDB1_TS-SYSTEM_FNO-8 input datafile file number=00025 name=/u02/app/oracle/oradata/cdb1/data_D-CDB1_TS-TEST_ENCRYPTION_BKP_FNO-25 input datafile file number=00039 name=/u01/app/oracle/oradata/cdb1/pdb1/tbs_test_pitr.dbf input datafile file number=00012 name=/u02/app/oracle/oradata/cdb1/data_D-CDB1_TS-RCATALOG1_FNO-12 input datafile file number=00017 name=/u02/app/oracle/oradata/cdb1/data_D-CDB1_TS-USERS_FNO-17 input datafile file number=00036 name=/u01/app/oracle/oradata/cdb1/pdb1/tbs_pitr.dbf channel ORA_DISK_2: starting piece 1 at Aug 15 2019 10:04:18 channel ORA_DISK_1: finished piece 1 at Aug 15 2019 10:04:53 piece handle=/u01/app/oracle/fast_recovery_area/CDB1/8F95C4062AA822B8E0536638A8C060CB/backupset/2019_08_15/o1_mf_nnndf_TAG20190815T100417_go9r69yr_.bkp tag=TAG20190815T100417 comment=NONE channel ORA_DISK_1: backup set complete, elapsed time: 00:00:36 channel ORA_DISK_1: starting full datafile backup set channel ORA_DISK_1: specifying datafile(s) in backup set input datafile file number=00003 name=/u02/app/oracle/oradata/cdb1/data_D-CDB1_TS-SYSAUX_FNO-3 input datafile file number=00026 name=/u02/app/oracle/oradata/cdb1/data_D-CDB1_TS-MYTBS01_FNO-26 input datafile file number=00033 name=/u02/app/oracle/oradata/cdb1/data_D-CDB1_TS-USER2_FNO-33 input datafile file number=00031 name=/u02/app/oracle/oradata/cdb1/data_D-CDB1_TS-T5_FNO-31 input datafile file number=00028 name=/u02/app/oracle/oradata/cdb1/data_D-CDB1_TS-T2_FNO-28 channel ORA_DISK_1: starting piece 1 at Aug 15 2019 10:04:53 channel ORA_DISK_2: finished piece 1 at Aug 15 2019 10:04:53 piece handle=/u01/app/oracle/fast_recovery_area/CDB1/8F95F488F27F26B1E0536638A8C085A3/backupset/2019_08_15/o1_mf_nnndf_TAG20190815T100417_go9r6cqc_.bkp tag=TAG20190815T100417 comment=NONE channel ORA_DISK_2: backup set complete, elapsed time: 00:00:35 channel ORA_DISK_2: starting full datafile backup set channel ORA_DISK_2: specifying datafile(s) in backup set input datafile file number=00001 name=/u02/app/oracle/oradata/cdb1/data_D-CDB1_TS-SYSTEM_FNO-1 input datafile file number=00029 name=/u02/app/oracle/oradata/cdb1/data_D-CDB1_TS-T3_FNO-29 input datafile file number=00030 name=/u02/app/oracle/oradata/cdb1/data_D-CDB1_TS-T4_FNO-30 input datafile file number=00004 name=/u02/app/oracle/oradata/cdb1/data_D-CDB1_TS-UNDOTBS1_FNO-4 channel ORA_DISK_2: starting piece 1 at Aug 15 2019 10:04:53 channel ORA_DISK_1: finished piece 1 at Aug 15 2019 10:05:58 piece handle=/u01/app/oracle/fast_recovery_area/CDB1/backupset/2019_08_15/o1_mf_nnndf_TAG20190815T100417_go9r7fl8_.bkp tag=TAG20190815T100417 comment=NONE channel ORA_DISK_1: backup set complete, elapsed time: 00:01:05 channel ORA_DISK_1: starting full datafile backup set channel ORA_DISK_1: specifying datafile(s) in backup set input datafile file number=00009 name=/u02/app/oracle/oradata/cdb1/data_D-CDB1_TS-SYSAUX_FNO-9 input datafile file number=00035 name=/u01/app/oracle/oradata/cdb1/pdb1/tc.dbf input datafile file number=00010 name=/u02/app/oracle/oradata/cdb1/data_D-CDB1_TS-USERS_FNO-10 input datafile file number=00032 name=/u02/app/oracle/oradata/cdb1/data_D-CDB1_TS-T1_FNO-32 input datafile file number=00034 name=/u02/app/oracle/oradata/cdb1/data_D-CDB1_TS-T2_FNO-34 channel ORA_DISK_1: starting piece 1 at Aug 15 2019 10:05:59 channel ORA_DISK_2: finished piece 1 at Aug 15 2019 10:05:59 piece handle=/u01/app/oracle/fast_recovery_area/CDB1/backupset/2019_08_15/o1_mf_nnndf_TAG20190815T100417_go9r7fy8_.bkp tag=TAG20190815T100417 comment=NONE channel ORA_DISK_2: backup set complete, elapsed time: 00:01:06 channel ORA_DISK_2: starting full datafile backup set channel ORA_DISK_2: specifying datafile(s) in backup set input datafile file number=00007 name=/u02/app/oracle/oradata/cdb1/data_D-CDB1_TS-SYSAUX_FNO-7 channel ORA_DISK_2: starting piece 1 at Aug 15 2019 10:05:59 RMAN-03009: failure of backup command on ORA_DISK_1 channel at 08/15/2019 10:06:24 ORA-19591: backup aborted because job time exceeded duration time backup of datafile 9 was cancelled backup of datafile 35 was cancelled backup of datafile 10 was cancelled backup of datafile 32 was cancelled backup of datafile 34 was cancelled RMAN-03009: failure of backup command on ORA_DISK_2 channel at 08/15/2019 10:06:24 ORA-19591: backup aborted because job time exceeded duration time backup of datafile 7 was cancelled Finished backup at Aug 15 2019 10:06:24 Starting Control File and SPFILE Autobackup at Aug 15 2019 10:06:24 piece handle=/u01/app/oracle/fast_recovery_area/CDB1/autobackup/2019_08_15/o1_mf_s_1016359584_go9rb9r4_.bkp comment=NONE Finished Control File and SPFILE Autobackup at Aug 15 2019 10:06:27 Minimize duration time and Load -> When using DURATION parameter, you can run the backup with the maximum possible performance. RMAN> BACKUP DURATION 3:00 PARTIAL MINIMIZE TIME DATABASE FILEPERSET 1; -> To extend the backup to use full available time, use MINIMIZE LOAD option. RMAN monitors the running backup, estimate the time of expected backup completion and minimize the load by slowing down the rate if it identifies that the backup will finish before the end of the backup window. RMAN> BACKUP DURATION 4:00 PARTIAL MINIMIZE LOAD DATABASE; Using RATE parameter To specify the amount of bytes that are read over a channel per second, use a RATE parameter of ALLOCATE and CONFIGURE CHANNEL commands. By using this parameter, you set a limit for IO to minimize disk bandwidth. RMAN> run 2> { 3> ALLOCATE CHANNEL ch1 TYPE DISK RATE 40M; 4> BACKUP PLUGGABLE DATABASE pdb1; 5> } allocated channel: ch1 channel ch1: SID=262 device type=DISK Starting backup at Aug 15 2019 10:25:08 channel ch1: starting full datafile backup set channel ch1: specifying datafile(s) in backup set input datafile file number=00009 name=/u02/app/oracle/oradata/cdb1/data_D-CDB1_TS-SYSAUX_FNO-9 input datafile file number=00008 name=/u02/app/oracle/oradata/cdb1/data_D-CDB1_TS-SYSTEM_FNO-8 input datafile file number=00025 name=/u02/app/oracle/oradata/cdb1/data_D-CDB1_TS-TEST_ENCRYPTION_BKP_FNO-25 input datafile file number=00039 name=/u01/app/oracle/oradata/cdb1/pdb1/tbs_test_pitr.dbf input datafile file number=00012 name=/u02/app/oracle/oradata/cdb1/data_D-CDB1_TS-RCATALOG1_FNO-12 input datafile file number=00017 name=/u02/app/oracle/oradata/cdb1/data_D-CDB1_TS-USERS_FNO-17 input datafile file number=00036 name=/u01/app/oracle/oradata/cdb1/pdb1/tbs_pitr.dbf input datafile file number=00035 name=/u01/app/oracle/oradata/cdb1/pdb1/tc.dbf input datafile file number=00010 name=/u02/app/oracle/oradata/cdb1/data_D-CDB1_TS-USERS_FNO-10 input datafile file number=00032 name=/u02/app/oracle/oradata/cdb1/data_D-CDB1_TS-T1_FNO-32 input datafile file number=00034 name=/u02/app/oracle/oradata/cdb1/data_D-CDB1_TS-T2_FNO-34 channel ch1: starting piece 1 at Aug 15 2019 10:25:08 channel ch1: finished piece 1 at Aug 15 2019 10:27:33 piece handle=/u01/app/oracle/fast_recovery_area/CDB1/8F95F488F27F26B1E0536638A8C085A3/backupset/2019_08_15/o1_mf_nnndf_TAG20190815T102508_go9sfds9_.bkp tag=TAG20190815T102508 comment=NONE channel ch1: backup set complete, elapsed time: 00:02:25 Finished backup at Aug 15 2019 10:27:33 Starting Control File and SPFILE Autobackup at Aug 15 2019 10:27:33 piece handle=/u01/app/oracle/fast_recovery_area/CDB1/autobackup/2019_08_15/o1_mf_s_1016360853_go9skz1z_.bkp comment=NONE Finished Control File and SPFILE Autobackup at Aug 15 2019 10:27:36 released channel: ch1
[3] Complete database restore and recovery operations
How to protect is the easier part, you must ensure your environment will be prepared for a catastrophic event:
Enable Archivelog
Enable Flashback
Add Supplemental Log
Then perform a full backup of your CDB (including the PDBs)
What you need to do here is do all kinds of complete restore/recovery operations.
Use Recover Manager to perform complete database restore and recovery operations
Practice the following:
Losing one or multiple logfiles (members or entire groups, active or inactive)
Losing one or all controlfiles
Losing one or all datafiles from CDB
Losing one or all datafiles from PDB (with or without the PDB backup after its creation)
Losing spfile
Losing everything (including spfile)
Database, PDB and Tablespace point in time recovery
Drop a PDB and do a PDB PITR just before it was removed
Using Restore Points to flashback your database
New Table Point In Time Recovery (using also the REMAP or dump file features)
I also recommend studying the situation where you have a data guard environment and
you need to restore missing controlfile/datafiles on your primary using the standby (and vice versa).
==================================================================USE RECOVERY MANAGER TO PERFORM COMPLETE DATABASE RESTORE AND RECOVERY OPERATIONS
==================================================================
Restore Parameter File
[1] Restore the spfile using rman spfile backup. -bash-4.1$ cd $ORACLE_HOME/dbs -bash-4.1$ rm spfilecdb1.ora SQL> shutdown immediate Database closed. Database dismounted. -bash-4.1$ sql SQL> startup ORA-01078: failure in processing system parameters LRM-00109: could not open parameter file '/u01/app/oracle/product/12.1.0.2/db_1/dbs/initcdb1.ora' SQL> quit -bash-4.1$ rman target / RMAN> startup nomount startup failed: ORA-01078: failure in processing system parameters LRM-00109: could not open parameter file '/u01/app/oracle/product/12.1.0.2/db_1/dbs/initcdb1.ora' starting Oracle instance without parameter file for retrieval of spfile Oracle instance started Total System Global Area 1073741824 bytes Fixed Size 2932632 bytes Variable Size 281018472 bytes Database Buffers 784334848 bytes Redo Buffers 5455872 bytes RMAN> set DBID=989477122 executing command: SET DBID database name is CDB1 and DBID is 989477122 RMAN> RESTORE SPFILE FROM AUTOBACKUP; Starting restore at Aug 13 2019 12:09:30 allocated channel: ORA_DISK_1 channel ORA_DISK_1: SID=11 device type=DISK channel ORA_DISK_1: looking for AUTOBACKUP on day: 20190813 channel ORA_DISK_1: looking for AUTOBACKUP on day: 20190812 channel ORA_DISK_1: looking for AUTOBACKUP on day: 20190811 channel ORA_DISK_1: looking for AUTOBACKUP on day: 20190810 channel ORA_DISK_1: looking for AUTOBACKUP on day: 20190809 channel ORA_DISK_1: looking for AUTOBACKUP on day: 20190808 channel ORA_DISK_1: looking for AUTOBACKUP on day: 20190807 channel ORA_DISK_1: no AUTOBACKUP in 7 days found RMAN-00571: =========================================================== RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS =============== RMAN-00571: =========================================================== RMAN-03002: failure of restore command at 08/13/2019 12:09:33 RMAN-06172: no AUTOBACKUP found or specified handle is not a valid copy or piece RMAN> restore spfile from '/u01/app/oracle/backup1/ctl_autobackup_c-989477122-20190813-15.bkp'; Starting restore at Aug 13 2019 12:10:09 using channel ORA_DISK_1 channel ORA_DISK_1: restoring spfile from AUTOBACKUP /u01/app/oracle/backup1/ctl_autobackup_c-989477122-20190813-15.bkp channel ORA_DISK_1: SPFILE restore from AUTOBACKUP complete Finished restore at Aug 13 2019 12:10:10 RMAN> quit -bash-4.1$ sql SQL> STARTUP ORACLE instance started. Total System Global Area 1073741824 bytes Fixed Size 2932632 bytes Variable Size 666894440 bytes Database Buffers 398458880 bytes Redo Buffers 5455872 bytes Database mounted. Database opened. SQL> SQL> show parameter spfile NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ spfile string /u01/app/oracle/product/12.1.0 .2/db_1/dbs/spfilecdb1.ora [2] -bash-4.1$ rm /u01/app/oracle/product/12.1.0.2/db_1/dbs/spfilecdb1.ora -bash-4.1$ -bash-4.1$ sql SQL> alter system processes=200 scope=spfile; alter system processes=200 scope=spfile * ERROR at line 1: ORA-02065: illegal option for ALTER SYSTEM SQL> alter system set processes=200 scope=spfile; alter system set processes=200 scope=spfile * ERROR at line 1: ORA-01565: error in identifying file '/u01/app/oracle/product/12.1.0.2/db_1/dbs/spfilecdb1.ora' ORA-27037: unable to obtain file status Linux-x86_64 Error: 2: No such file or directory Additional information: 3 RMAN> restore spfile to '/tmp/spfile_cdb1.ora'; Starting restore at Aug 13 2019 15:09:15 using channel ORA_DISK_1 using channel ORA_DISK_2 channel ORA_DISK_1: starting datafile backup set restore channel ORA_DISK_1: restoring SPFILE output file name=/tmp/spfile_cdb1.ora channel ORA_DISK_1: reading from backup piece /u01/app/oracle/fast_recovery_area/CDB1/autobackup/2019_08_13/o1_mf_s_1016195699_go4r8w7n_.bkp channel ORA_DISK_1: piece handle=/u01/app/oracle/fast_recovery_area/CDB1/autobackup/2019_08_13/o1_mf_s_1016195699_go4r8w7n_.bkp tag=TAG20190813T123459 channel ORA_DISK_1: restored backup piece 1 channel ORA_DISK_1: restore complete, elapsed time: 00:00:05 Finished restore at Aug 13 2019 15:09:24 -bash-4.1$ cp /tmp/spfile_cdb1.ora /u01/app/oracle/product/12.1.0.2/db_1/dbs/spfilecdb1.ora -bash-4.1$ sql SQL> show parameter spfile NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ spfile string /u01/app/oracle/product/12.1.0 .2/db_1/dbs/spfilecdb1.ora SQL> alter system set processes=200 scope=spfile; [3] Create spfile from alert log file. -bash-4.1$ rm /u01/app/oracle/product/12.1.0.2/db_1/dbs/spfilecdb1.ora -bash-4.1$ sql SQL*Plus: Release 12.1.0.2.0 Production on Tue Aug 13 15:10:43 2019 Copyright (c) 1982, 2014, Oracle. All rights reserved. Connected to an idle instance. SQL> startup ORA-01078: failure in processing system parameters LRM-00109: could not open parameter file '/u01/app/oracle/product/12.1.0.2/db_1/dbs/initcdb1.ora' SQL> quit Disconnected -bash-4.1$ cd /u01/app/oracle/diag/rdbms/cdb1/cdb1/trace/ -bash-4.1$ tail -500 alert_cdb1.log | grep non-default -A23 System parameters with non-default values: processes = 300 memory_target = 1G control_files = "/u01/app/oracle/oradata/cdb1/control01.ctl control_files = "/u01/app/oracle/fast_recovery_area/cdb1/control02.ctl db_block_size = 8192 compatible = 12.1.0.2.0 db_recovery_file_dest = "/u01/app/oracle/fast_recovery_area db_recovery_file_dest_size= 4560M undo_tablespace = UNDOTBS1 remote_login_passwordfile= EXCLUSIVE db_domain = "" dispatchers = "(PROTOCOL=TCP) (SERVICE=cdb1XDB)" audit_file_dest = "/u01/app/oracle/admin/cdb1/adump audit_trail = DB db_name = cdb1 open_cursors = 300 diagnostic_dest = "/u01/app/oracle enable_pluggable_database= TRUE NOTE: remote asm mode is local (mode 0x1; from cluster type) Starting background process PMON Starting background process PSP0 Tue Aug 13 15:04:00 2019 PMON started with pid=2, OS id=3432 -bash-4.1$ dbs -bash-4.1$ vi initcdb1.ora -bash-4.1$ sql SQL*Plus: Release 12.1.0.2.0 Production on Tue Aug 13 15:13:08 2019 Copyright (c) 1982, 2014, Oracle. All rights reserved. Connected to an idle instance. SQL> startup pfile=initcdb1.ora ORACLE instance started. Total System Global Area 1073741824 bytes Fixed Size 2932632 bytes Variable Size 713031784 bytes Database Buffers 352321536 bytes Redo Buffers 5455872 bytes Database mounted. Database opened. SQL> show parameter spfile NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ spfile string SQL> create spfile from pfile; File created. SQL> startup force ORACLE instance started. Total System Global Area 1073741824 bytes Fixed Size 2932632 bytes Variable Size 713031784 bytes Database Buffers 352321536 bytes Redo Buffers 5455872 bytes Database mounted. Database opened. SQL> SQL> show parameter spfile NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ spfile string /u01/app/oracle/product/12.1.0 .2/db_1/dbs/spfilecdb1.ora SQL> quit [4] Restore spfile to local from rman autobackup. -bash-4.1$ rm spfilecdb1.ora -bash-4.1$ rm initcdb1.ora -bash-4.1$ -bash-4.1$ sql SQL> shutdown immediate Database closed. Database dismounted. ORACLE instance shut down. -bash-4.1$ rman target / Recovery Manager: Release 12.1.0.2.0 - Production on Tue Aug 13 16:57:32 2019 Copyright (c) 1982, 2014, Oracle and/or its affiliates. All rights reserved. connected to target database (not started) RMAN> startup nomount startup failed: ORA-01078: failure in processing system parameters LRM-00109: could not open parameter file '/u01/app/oracle/product/12.1.0.2/db_1/dbs/initcdb1.ora' starting Oracle instance without parameter file for retrieval of spfile Oracle instance started Total System Global Area 1073741824 bytes Fixed Size 2932632 bytes Variable Size 281018472 bytes Database Buffers 784334848 bytes Redo Buffers 5455872 bytes RMAN> restore spfile to '/tmp/spfile-cdb1.ora' from '/u01/app/oracle/fast_recovery_area/CDB1/autobackup/2019_08_13/o1_mf_s_1016195699_go4r8w7n_.bkp'; Starting restore at Aug 13 2019 17:00:21 using target database control file instead of recovery catalog allocated channel: ORA_DISK_1 channel ORA_DISK_1: SID=12 device type=DISK channel ORA_DISK_1: restoring spfile from AUTOBACKUP /u01/app/oracle/fast_recovery_area/CDB1/autobackup/2019_08_13/o1_mf_s_1016195699_go4r8w7n_.bkp channel ORA_DISK_1: SPFILE restore from AUTOBACKUP complete Finished restore at Aug 13 2019 17:00:24 RMAN> quit Recovery Manager complete. -bash-4.1$ -bash-4.1$ sql SQL*Plus: Release 12.1.0.2.0 Production on Tue Aug 13 17:00:35 2019 Copyright (c) 1982, 2014, Oracle. All rights reserved. SQL> create pfile from spfile='/tmp/spfile-cdb1.ora'; File created. SQL> create spfile from pfile; File created. SQL> startup force ORACLE instance started. Total System Global Area 1073741824 bytes Fixed Size 2932632 bytes Variable Size 666894440 bytes Database Buffers 398458880 bytes Redo Buffers 5455872 bytes Database mounted. Database opened. SQL> show parameter spfile NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ spfile string /u01/app/oracle/product/12.1.0 .2/db_1/dbs/spfilecdb1.ora
Restore from loss of controlfile without RMAN
[1] Restore controlfile without backup. -bash-4.1$ sql SQL> select name from v$controlfile; NAME -------------------------------------------------------------------------------- /u01/app/oracle/oradata/cdb1/control01.ctl /u01/app/oracle/fast_recovery_area/cdb1/control02.ctl SQL> !rm /u01/app/oracle/oradata/cdb1/control01.ctl SQL> create tablespace t2 datafile '/u01/app/oracle/oradata/cdb1/t2.dbf' size 1M; create tablespace t2 datafile '/u01/app/oracle/oradata/cdb1/t2.dbf' size 1M * ERROR at line 1: ORA-00210: cannot open the specified control file ORA-00202: control file: '/u01/app/oracle/oradata/cdb1/control01.ctl' ORA-27041: unable to open file Linux-x86_64 Error: 2: No such file or directory Additional information: 3 SQL> quit -bash-4.1$ cd /u01/app/oracle/fast_recovery_area/cdb1/ -bash-4.1$ cp control02.ctl /u01/app/oracle/oradata/cdb1/control01.ctl -bash-4.1$ -bash-4.1$ sql SQL> startup force ORACLE instance started. Total System Global Area 1073741824 bytes Fixed Size 2932632 bytes Variable Size 666894440 bytes Database Buffers 398458880 bytes Redo Buffers 5455872 bytes Database mounted. Database opened. SQL> SQL> show parameter control_files; NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ control_files string /u01/app/oracle/oradata/cdb1/c ontrol01.ctl, /u01/app/oracle/ fast_recovery_area/cdb1/contro l02.ctl SQL> create tablespace t2 datafile '/u01/app/oracle/oradata/cdb1/t2.dbf' size 1M; Tablespace created. SQL> !rm /u01/app/oracle/fast_recovery_area/cdb1/control02.ctl SQL> create tablespace t3 datafile '/u01/app/oracle/oradata/cdb1/t3.dbf' size 1M; Tablespace created. SQL> !ls /u01/app/oracle/fast_recovery_area/cdb1/control02.ctl ls: cannot access /u01/app/oracle/fast_recovery_area/cdb1/control02.ctl: No such file or directory SQL> create tablespace t4 datafile '/u01/app/oracle/oradata/cdb1/t4.dbf' size 1M; Tablespace created. SQL> alter system switch logfile; System altered. SQL> !ls /u01/app/oracle/fast_recovery_area/cdb1/control02.ctl ls: cannot access /u01/app/oracle/fast_recovery_area/cdb1/control02.ctl: No such file or directory SQL> !ls /u01/app/oracle/oradata/cdb1/control01.ctl /u01/app/oracle/oradata/cdb1/control01.ctl SQL> alter system switch logfile; System altered. SQL> shutdown immediate Database closed. ORA-00210: cannot open the specified control file ORA-00202: control file: '/u01/app/oracle/fast_recovery_area/cdb1/control02.ctl' ORA-27041: unable to open file Linux-x86_64 Error: 2: No such file or directory Additional information: 3 SQL> quit -bash-4.1$ sql SQL> SQL> shut abort ORACLE instance shut down. SQL> SQL> exit -bash-4.1$ pwd /u01/app/oracle/oradata/cdb1 -bash-4.1$ cp control01.ctl control02.ctl -bash-4.1$ mv control02.ctl /u01/app/oracle/fast_recovery_area/cdb1/ -bash-4.1$ ls /u01/app/oracle/fast_recovery_area/cdb1/control02.ctl /u01/app/oracle/fast_recovery_area/cdb1/control02.ctl -bash-4.1$ -bash-4.1$ sql SQL> startup ORACLE instance started. Total System Global Area 1073741824 bytes Fixed Size 2932632 bytes Variable Size 666894440 bytes Database Buffers 398458880 bytes Redo Buffers 5455872 bytes Database mounted. Database opened. SQL> select name from v$controlfile; NAME -------------------------------------------------------------------------------- /u01/app/oracle/oradata/cdb1/control01.ctl /u01/app/oracle/fast_recovery_area/cdb1/control02.ctl [2] Recover the control-file by manually recreating controlfile. SQL> alter database backup controlfile to trace as '/tmp/controlfile_cdb1.txt'; Database altered. SQL> exit -bash-4.1$ rm /u01/app/oracle/oradata/cdb1/control01.ctl -bash-4.1$ rm /u01/app/oracle/fast_recovery_area/cdb1/control02.ctl -bash-4.1$ sql SQL*Plus: Release 12.1.0.2.0 Production on Tue Aug 13 17:23:30 2019 Copyright (c) 1982, 2014, Oracle. All rights reserved. Connected to: Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options SQL> create tablespace t1 datafile '/u01/app/oracle/oradata/cdb1/t1.dbf' size 1M; create tablespace t1 datafile '/u01/app/oracle/oradata/cdb1/t1.dbf' size 1M * ERROR at line 1: ORA-00210: cannot open the specified control file ORA-00202: control file: '/u01/app/oracle/oradata/cdb1/control01.ctl' ORA-27041: unable to open file Linux-x86_64 Error: 2: No such file or directory Additional information: 3 SQL> quit -bash-4.1$ rman target / Recovery Manager: Release 12.1.0.2.0 - Production on Tue Aug 13 17:24:09 2019 Copyright (c) 1982, 2014, Oracle and/or its affiliates. All rights reserved. RMAN-00571: =========================================================== RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS =============== RMAN-00571: =========================================================== RMAN-00554: initialization of internal recovery manager package failed RMAN-06003: ORACLE error from target database: ORA-00210: cannot open the specified control file ORA-00202: control file: '/u01/app/oracle/oradata/cdb1/control01.ctl' ORA-27041: unable to open file Linux-x86_64 Error: 2: No such file or directory Additional information: 3 -bash-4.1$ -bash-4.1$ sql SQL> shut abort ORACLE instance shut down. SQL> startup nomount; ORACLE instance started. Total System Global Area 1073741824 bytes Fixed Size 2932632 bytes Variable Size 666894440 bytes Database Buffers 398458880 bytes Redo Buffers 5455872 bytes SQL> quit Disconnected from Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options -bash-4.1$ -bash-4.1$ vi /tmp/controlfile_cdb1.txt -bash-4.1$ sql SQL> STARTUP NOMOUNT CREATE CONTROLFILE REUSE DATABASE CDB1 NORESETLOGS ARCHIVELOG MAXLOGFILES 16 MAXLOGMEMBERS 3 MAXDATAFILES 1024 MAXINSTANCES 8 MAXLOGHISTORY 292 LOGFILE GROUP 1 '/u01/app/oracle/oradata/cdb1/redo01.log' SIZE 50M BLOCKSIZE 512, GROUP 2 '/u01/app/oracle/oradata/cdb1/redo02.log' SIZE 50M BLOCKSIZE 512, GROUP 3 '/u01/app/oracle/oradata/cdb1/redo03.log' SIZE 50M BLOCKSIZE 512 -- STANDBY LOGFILE DATAFILE '/u01/app/oracle/oradata/cdb1/system01.dbf', '/u01/app/oracle/oradata/cdb1/sysaux01.dbf', '/u01/app/oracle/oradata/cdb1/undotbs01.dbf', '/u01/app/oracle/oradata/cdb1/pdbseed/system01.dbf', '/u01/app/oracle/oradata/cdb1/pdbseed/sysaux01.dbf', '/u01/app/oracle/oradata/cdb1/pdb1/system01.dbf', '/u01/app/oracle/oradata/cdb1/pdb1/sysaux01.dbf', '/u01/app/oracle/oradata/cdb1/pdb1/pdb1_users01.dbf', '/u01/app/oracle/oradata/cdb1/pdb1/rcatalog1.dbf', '/u01/app/oracle/oradata/cdb1/pdb1/user02.dbf', '/u01/app/oracle/oradata/cdb1/pdb1/test01.dbf', '/u01/app/oracle/backup2/data_D-CDB1_I-989477122_TS-MYTBS01_FNO-26_96u93l8d.bkp' CHARACTER SET AL32UTF8 ; 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 Control file created. SQL> SQL> -- Configure RMAN configuration record 1 VARIABLE RECNO NUMBER; EXECUTE :RECNO := SYS.DBMS_BACKUP_RESTORE.SETCONFIG('CONTROLFILE AUTOBACKUP','ON'); -- Configure RMAN configuration record 3 VARIABLE RECNO NUMBER; EXECUTE :RECNO := SYS.DBMS_BACKUP_RESTORE.SETCONFIG('ARCHIVELOG DELETION POLICY','TO NONE'); -- Configure RMAN configuration record 4 VARIABLE RECNO NUMBER; EXECUTE :RECNO := SYS.DBMS_BACKUP_RESTORE.SETCONFIG('BACKUP OPTIMIZATION','ON'); -- Configure RMAN configuration record 5 VARIABLE RECNO NUMBER; EXECUTE :RECNO := SYS.DBMS_BACKUP_RESTORE.SETCONFIG('DEVICE TYPE','DISK PARALLELISM 2 BACKUP TYPE TO BACKUPSET'); -- Configure RMAN configuration record 6 VARIABLE RECNO NUMBER; EXECUTE :RECNO := SYS.DBMS_BACKUP_RESTORE.SETCONFIG('DEFAULT DEVICE TYPE TO','DISK'); -- Configure RMAN configuration record 7 VARIABLE RECNO NUMBER; EXECUTE :RECNO := SYS.DBMS_BACKUP_RESTORE.SETCONFIG('CHANNEL','1 DEVICE TYPE DISK FORMAT ''/u01/app/oracle/backup1/%U.bk'''); -- Configure RMAN configuration record 8 VARIABLE RECNO NUMBER; EXECUTE :RECNO := SYS.DBMS_BACKUP_RESTORE.SETCONFIG('CHANNEL','2 DEVICE TYPE DISK FORMAT ''/u01/app/oracle/backup2/%U.bkp'''); -- Configure RMAN configuration record 9 VARIABLE RECNO NUMBER; EXECUTE :RECNO := SYS.DBMS_BACKUP_RESTORE.SETCONFIG('CHANNEL','3 DEVICE TYPE DISK FORMAT ''/u01/app/oracle/backup3/%U.bkp'''); SQL> SQL> PL/SQL procedure successfully completed. -bash-4.1$ rman target / RMAN> recover database; Starting recover at Aug 13 2019 17:31:03 using target database control file instead of recovery catalog configuration for DISK channel 3 is ignored allocated channel: ORA_DISK_1 channel ORA_DISK_1: SID=12 device type=DISK allocated channel: ORA_DISK_2 channel ORA_DISK_2: SID=249 device type=DISK starting media recovery media recovery complete, elapsed time: 00:00:01 Finished recover at Aug 13 2019 17:31:05 RMAN> alter database open; Statement processed RMAN> quit Recovery Manager complete.
Restore Controlfile with RMAN
[3] Restore the controlfile using backup SQL> select name from v$controlfile; NAME -------------------------------------------------------------------------------- /u01/app/oracle/oradata/cdb1/control01.ctl /u01/app/oracle/fast_recovery_area/cdb1/control02.ctl SQL> !ls /u01/app/oracle/oradata/cdb1/control01.ctl ls: cannot access /u01/app/oracle/oradata/cdb1/control01.ctl: No such file or directory SQL> !ls /u01/app/oracle/fast_recovery_area/cdb1/control02.ctl ls: cannot access /u01/app/oracle/fast_recovery_area/cdb1/control02.ctl: No such file or directory SQL> create tablespace test datafile '/u01/app/oracle/oradata/cdb1/test01.dbf' size 1M; create tablespace test datafile '/u01/app/oracle/oradata/cdb1/test01.dbf' size 1M * ERROR at line 1: ORA-00210: cannot open the specified control file ORA-00202: control file: '/u01/app/oracle/oradata/cdb1/control01.ctl' ORA-27041: unable to open file Linux-x86_64 Error: 2: No such file or directory Additional information: 3 SQL> alter database backup controlfile to trace; alter database backup controlfile to trace * ERROR at line 1: ORA-00210: cannot open the specified control file ORA-00202: control file: '/u01/app/oracle/oradata/cdb1/control01.ctl' ORA-27041: unable to open file Linux-x86_64 Error: 2: No such file or directory Additional information: 3 -bash-4.1$ rman target / Recovery Manager: Release 12.1.0.2.0 - Production on Tue Aug 13 17:14:05 2019 Copyright (c) 1982, 2014, Oracle and/or its affiliates. All rights reserved. RMAN-00571: =========================================================== RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS =============== RMAN-00571: =========================================================== RMAN-00554: initialization of internal recovery manager package failed RMAN-06003: ORACLE error from target database: ORA-00210: cannot open the specified control file ORA-00202: control file: '/u01/app/oracle/oradata/cdb1/control01.ctl' ORA-27041: unable to open file Linux-x86_64 Error: 2: No such file or directory Additional information: 3 -bash-4.1$ -bash-4.1$ -bash-4.1$ sql SQL> shut abort ORACLE instance shut down. SQL> SQL> exit Disconnected from Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options -bash-4.1$ -bash-4.1$ export ORACLE_SID=cdb1 -bash-4.1$ rman target / Recovery Manager: Release 12.1.0.2.0 - Production on Tue Aug 13 17:14:25 2019 Copyright (c) 1982, 2014, Oracle and/or its affiliates. All rights reserved. connected to target database (not started) RMAN> startup nomount; Oracle instance started Total System Global Area 1073741824 bytes Fixed Size 2932632 bytes Variable Size 666894440 bytes Database Buffers 398458880 bytes Redo Buffers 5455872 bytes RMAN> restore controlfile from autobackup; Starting restore at Aug 13 2019 17:14:53 using target database control file instead of recovery catalog allocated channel: ORA_DISK_1 channel ORA_DISK_1: SID=12 device type=DISK recovery area destination: /u01/app/oracle/fast_recovery_area database name (or database unique name) used for search: CDB1 channel ORA_DISK_1: AUTOBACKUP /u01/app/oracle/fast_recovery_area/CDB1/autobackup/2019_08_13/o1_mf_s_1016195699_go4r8w7n_.bkp found in the recovery area AUTOBACKUP search with format "%F not attempted because DBID was not set channel ORA_DISK_1: restoring control file from AUTOBACKUP /u01/app/oracle/fast_recovery_area/CDB1/autobackup/2019_08_13/o1_mf_s_1016195699_go4r8w7n_.bkp channel ORA_DISK_1: control file restore from AUTOBACKUP complete output file name=/u01/app/oracle/oradata/cdb1/control01.ctl output file name=/u01/app/oracle/fast_recovery_area/cdb1/control02.ctl Finished restore at Aug 13 2019 17:14:58 RMAN> alter database mount; Statement processed released channel: ORA_DISK_1 RMAN> alter database open; RMAN-00571: =========================================================== RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS =============== RMAN-00571: =========================================================== RMAN-03002: failure of sql statement command at 08/13/2019 17:16:04 ORA-01589: must use RESETLOGS or NORESETLOGS option for database open RMAN> alter database open resetlogs; RMAN-00571: =========================================================== RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS =============== RMAN-00571: =========================================================== RMAN-03002: failure of sql statement command at 08/13/2019 17:16:22 ORA-01194: file 1 needs more recovery to be consistent ORA-01110: data file 1: '/u01/app/oracle/oradata/cdb1/system01.dbf' RMAN> recover database; Starting recover at Aug 13 2019 17:16:30 Starting implicit crosscheck backup at Aug 13 2019 17:16:30 allocated channel: ORA_DISK_1 channel ORA_DISK_1: SID=12 device type=DISK allocated channel: ORA_DISK_2 channel ORA_DISK_2: SID=249 device type=DISK Crosschecked 4 objects Crosschecked 53 objects Finished implicit crosscheck backup at Aug 13 2019 17:16:33 Starting implicit crosscheck copy at Aug 13 2019 17:16:33 using channel ORA_DISK_1 using channel ORA_DISK_2 Crosschecked 13 objects Crosschecked 9 objects Finished implicit crosscheck copy at Aug 13 2019 17:16:35 searching for all files in the recovery area cataloging files... cataloging done List of Cataloged Files ======================= File Name: /u01/app/oracle/fast_recovery_area/CDB1/autobackup/2019_08_13/o1_mf_s_1016195699_go4r8w7n_.bkp File Name: /u01/app/oracle/fast_recovery_area/CDB1/archivelog/2019_08_13/o1_mf_1_62_go510sz1_.arc File Name: /u01/app/oracle/fast_recovery_area/CDB1/archivelog/2019_08_13/o1_mf_1_63_go51mf2h_.arc File Name: /u01/app/oracle/fast_recovery_area/CDB1/archivelog/2019_08_13/o1_mf_1_64_go57j7y3_.arc using channel ORA_DISK_1 using channel ORA_DISK_2 datafile 6 not processed because file is read-only datafile 13 not processed because file is read-only datafile 14 not processed because file is read-only datafile 15 not processed because file is read-only datafile 16 not processed because file is read-only datafile 18 not processed because file is read-only datafile 19 not processed because file is read-only datafile 20 not processed because file is read-only datafile 21 not processed because file is read-only datafile 22 not processed because file is read-only datafile 23 not processed because file is read-only starting media recovery archived log for thread 1 with sequence 62 is already on disk as file /u01/app/oracle/fast_recovery_area/CDB1/archivelog/2019_08_13/o1_mf_1_62_go510sz1_.arc archived log for thread 1 with sequence 63 is already on disk as file /u01/app/oracle/fast_recovery_area/CDB1/archivelog/2019_08_13/o1_mf_1_63_go51mf2h_.arc archived log for thread 1 with sequence 64 is already on disk as file /u01/app/oracle/fast_recovery_area/CDB1/archivelog/2019_08_13/o1_mf_1_64_go57j7y3_.arc archived log for thread 1 with sequence 65 is already on disk as file /u01/app/oracle/oradata/cdb1/redo02.log archived log file name=/u01/app/oracle/fast_recovery_area/CDB1/archivelog/2019_08_13/o1_mf_1_62_go510sz1_.arc thread=1 sequence=62 archived log file name=/u01/app/oracle/fast_recovery_area/CDB1/archivelog/2019_08_13/o1_mf_1_63_go51mf2h_.arc thread=1 sequence=63 archived log file name=/u01/app/oracle/fast_recovery_area/CDB1/archivelog/2019_08_13/o1_mf_1_64_go57j7y3_.arc thread=1 sequence=64 archived log file name=/u01/app/oracle/oradata/cdb1/redo02.log thread=1 sequence=65 media recovery complete, elapsed time: 00:00:02 Finished recover at Aug 13 2019 17:16:40 RMAN> alter database open resetlogs; Statement processed -bash-4.1$ -bash-4.1$ sql SQL> select name from v$controlfile; NAME -------------------------------------------------------------------------------- /u01/app/oracle/oradata/cdb1/control01.ctl /u01/app/oracle/fast_recovery_area/cdb1/control02.ctl
Restore datafile using RMAN
SQL> show pdbs CON_ID CON_NAME OPEN MODE RESTRICTED ---------- ------------------------------ ---------- ---------- 2 PDB$SEED READ ONLY NO 3 PDB1 READ WRITE NO SQL> SQL> alter session set container=pdb1; Session altered. SQL> create tablespace t1 datafile '/u01/app/oracle/oradata/cdb1/pdb1/t1.dbf' size 5m; Tablespace created. SQL> create user t1 identified by t1 default tablespace t1 quota unlimited on t1 temporary tablespace temp; User created. SQL> grant connect,resource to t1; Grant succeeded. SQL> connect t1/t1@//racnode2/pdb1 Connected. SQL> create table t1 (ID NUMBER) ; Table created. SQL> INSERT INTO T1 VALUES(100); -bash-4.1$ rman target / Recovery Manager: Release 12.1.0.2.0 - Production on Tue Aug 13 18:43:01 2019 Copyright (c) 1982, 2014, Oracle and/or its affiliates. All rights reserved. connected to target database: CDB1 (DBID=989477122) RMAN> backup database plus archivelog delete input; Starting backup at Aug 13 2019 18:43:17 current log archived using target database control file instead of recovery catalog configuration for DISK channel 3 is ignored allocated channel: ORA_DISK_1 channel ORA_DISK_1: SID=32 device type=DISK allocated channel: ORA_DISK_2 channel ORA_DISK_2: SID=248 device type=DISK channel ORA_DISK_1: starting archived log backup set channel ORA_DISK_1: specifying archived log(s) in backup set input archived log thread=1 sequence=6 RECID=6 STAMP=1016217797 channel ORA_DISK_1: starting piece 1 at Aug 13 2019 18:43:18 channel ORA_DISK_1: finished piece 1 at Aug 13 2019 18:43:19 piece handle=/u01/app/oracle/backup1/05u94g66_1_1.bk tag=TAG20190813T184318 comment=NONE channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01 channel ORA_DISK_1: deleting archived log(s) archived log file name=/u01/app/oracle/fast_recovery_area/CDB1/archivelog/2019_08_13/o1_mf_1_6_go5fvfpk_.arc RECID=6 STAMP=1016217797 Finished backup at Aug 13 2019 18:43:19 Starting backup at Aug 13 2019 18:43:19 using channel ORA_DISK_1 using channel ORA_DISK_2 channel ORA_DISK_1: starting full datafile backup set channel ORA_DISK_1: specifying datafile(s) in backup set input datafile file number=00001 name=/u01/app/oracle/oradata/cdb1/system01.dbf input datafile file number=00029 name=/u01/app/oracle/oradata/cdb1/t3.dbf input datafile file number=00030 name=/u01/app/oracle/oradata/cdb1/t4.dbf input datafile file number=00004 name=/u01/app/oracle/oradata/cdb1/undotbs01.dbf channel ORA_DISK_1: starting piece 1 at Aug 13 2019 18:43:20 channel ORA_DISK_2: starting full datafile backup set channel ORA_DISK_2: specifying datafile(s) in backup set input datafile file number=00003 name=/u01/app/oracle/oradata/cdb1/sysaux01.dbf input datafile file number=00026 name=/u01/app/oracle/backup2/data_D-CDB1_I-989477122_TS-MYTBS01_FNO-26_96u93l8d.bkp input datafile file number=00033 name=/u01/app/oracle/oradata/cdb1/USER2.dbf input datafile file number=00031 name=/u01/app/oracle/oradata/cdb1/t5.dbf input datafile file number=00028 name=/u01/app/oracle/oradata/cdb1/t2.dbf channel ORA_DISK_2: starting piece 1 at Aug 13 2019 18:43:20 channel ORA_DISK_2: finished piece 1 at Aug 13 2019 18:47:27 piece handle=/u01/app/oracle/backup2/07u94g68_1_1.bkp tag=TAG20190813T184320 comment=NONE channel ORA_DISK_2: backup set complete, elapsed time: 00:04:09 channel ORA_DISK_2: starting full datafile backup set channel ORA_DISK_2: specifying datafile(s) in backup set input datafile file number=00009 name=/u01/app/oracle/oradata/cdb1/pdb1/sysaux01.dbf input datafile file number=00010 name=/u01/app/oracle/oradata/cdb1/pdb1/pdb1_users01.dbf input datafile file number=00032 name=/u01/app/oracle/oradata/cdb1/pdb1/t1.dbf channel ORA_DISK_2: starting piece 1 at Aug 13 2019 18:47:47 channel ORA_DISK_1: finished piece 1 at Aug 13 2019 18:47:47 piece handle=/u01/app/oracle/backup1/06u94g68_1_1.bk tag=TAG20190813T184320 comment=NONE channel ORA_DISK_1: backup set complete, elapsed time: 00:04:27 channel ORA_DISK_1: starting full datafile backup set channel ORA_DISK_1: specifying datafile(s) in backup set input datafile file number=00007 name=/u01/app/oracle/oradata/cdb1/pdbseed/sysaux01.dbf channel ORA_DISK_1: starting piece 1 at Aug 13 2019 18:47:48 channel ORA_DISK_1: finished piece 1 at Aug 13 2019 18:50:13 piece handle=/u01/app/oracle/backup1/09u94gej_1_1.bk tag=TAG20190813T184320 comment=NONE channel ORA_DISK_1: backup set complete, elapsed time: 00:02:25 channel ORA_DISK_1: starting full datafile backup set channel ORA_DISK_1: specifying datafile(s) in backup set input datafile file number=00008 name=/u01/app/oracle/oradata/cdb1/pdb1/system01.dbf input datafile file number=00025 name=/u01/app/oracle/oradata/cdb1/pdb1/test01.dbf input datafile file number=00012 name=/u01/app/oracle/oradata/cdb1/pdb1/rcatalog1.dbf input datafile file number=00017 name=/u01/app/oracle/oradata/cdb1/pdb1/user02.dbf channel ORA_DISK_1: starting piece 1 at Aug 13 2019 18:50:15 channel ORA_DISK_2: finished piece 1 at Aug 13 2019 18:50:15 piece handle=/u01/app/oracle/backup2/08u94gei_1_1.bkp tag=TAG20190813T184320 comment=NONE channel ORA_DISK_2: backup set complete, elapsed time: 00:02:28 channel ORA_DISK_2: starting full datafile backup set channel ORA_DISK_2: specifying datafile(s) in backup set input datafile file number=00005 name=/u01/app/oracle/oradata/cdb1/pdbseed/system01.dbf channel ORA_DISK_2: starting piece 1 at Aug 13 2019 18:50:16 channel ORA_DISK_1: finished piece 1 at Aug 13 2019 18:51:11 piece handle=/u01/app/oracle/backup1/0bu94gj7_1_1.bk tag=TAG20190813T184320 comment=NONE channel ORA_DISK_1: backup set complete, elapsed time: 00:00:56 channel ORA_DISK_2: finished piece 1 at Aug 13 2019 18:51:11 piece handle=/u01/app/oracle/backup2/0cu94gj8_1_1.bkp tag=TAG20190813T184320 comment=NONE channel ORA_DISK_2: backup set complete, elapsed time: 00:00:55 Finished backup at Aug 13 2019 18:51:11 Starting backup at Aug 13 2019 18:51:11 current log archived using channel ORA_DISK_1 using channel ORA_DISK_2 channel ORA_DISK_1: starting archived log backup set channel ORA_DISK_1: specifying archived log(s) in backup set input archived log thread=1 sequence=7 RECID=7 STAMP=1016218271 channel ORA_DISK_1: starting piece 1 at Aug 13 2019 18:51:11 channel ORA_DISK_1: finished piece 1 at Aug 13 2019 18:51:12 piece handle=/u01/app/oracle/backup1/0du94gkv_1_1.bk tag=TAG20190813T185111 comment=NONE channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01 channel ORA_DISK_1: deleting archived log(s) archived log file name=/u01/app/oracle/fast_recovery_area/CDB1/archivelog/2019_08_13/o1_mf_1_7_go5gb7ff_.arc RECID=7 STAMP=1016218271 Finished backup at Aug 13 2019 18:51:12 Starting Control File and SPFILE Autobackup at Aug 13 2019 18:51:12 piece handle=/u01/app/oracle/fast_recovery_area/CDB1/autobackup/2019_08_13/o1_mf_s_1016218272_go5gb9fg_.bkp comment=NONE Finished Control File and SPFILE Autobackup at Aug 13 2019 18:51:15 RMAN> quit SQL> insert into t1 values(200); SQL> commit; SQL> select * from t1; ID ---------- 200 100 -bash-4.1$ rm /u01/app/oracle/oradata/cdb1/pdb1/t1.dbf -bash-4.1$ -bash-4.1$ sqlplus t1/t1@//racnode2/pdb1 SQL> insert into t1 values(300); 1 row created. SQL> insert into t1 values(400); 1 row created. SQL> select * from t1; ID ---------- 200 300 400 100 SQL> commit; Commit complete. SQL> quit -bash-4.1$ sql SQL> alter session set container=pdb1; Session altered. SQL> select * from t1.t1; ID ---------- 200 300 400 100 SQL> insert into t1.t1 values(500); 1 row created. SQL> commit; Commit complete. SQL> exit -bash-4.1$ sql SQL> alter system flush buffer_cache; System altered. SQL> alter session set container=pdb1; Session altered. SQL> insert into t1.t1 values(500); 1 row created. SQL> connect t1/t1@//racnode2/pdb1 Connected. SQL> select table_name,tablespace_name from user_tables; TABLE_NAME -------------------------------------------------------------------------------- TABLESPACE_NAME ------------------------------ T1 T1 SQL> !ls /u01/app/oracle/oradata/cdb1/pdb1/t1.dbf ls: cannot access /u01/app/oracle/oradata/cdb1/pdb1/t1.dbf: No such file or directory SQL> commit; Commit complete. SQL> select table_name,tablespace_name from user_tables; TABLE_NAME -------------------------------------------------------------------------------- TABLESPACE_NAME ------------------------------ T1 T1 SQL> connect sys/oracle_4U@//racnode2/pdb1 as sysdba Connected. SQL> alter system flush buffer_cache; System altered. SQL> insert into t1.t1 values(500); insert into t1.t1 values(500) * ERROR at line 1: ORA-01116: error in opening database file 32 ORA-01110: data file 32: '/u01/app/oracle/oradata/cdb1/pdb1/t1.dbf' ORA-27041: unable to open file Linux-x86_64 Error: 2: No such file or directory Additional information: 3 SQL> quit Disconnected from Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options -bash-4.1$ -bash-4.1$ rman target / Recovery Manager: Release 12.1.0.2.0 - Production on Tue Aug 13 18:58:11 2019 Copyright (c) 1982, 2014, Oracle and/or its affiliates. All rights reserved. connected to target database: CDB1 (DBID=989477122) RMAN> restore datafile 32; Starting restore at Aug 13 2019 18:58:24 using target database control file instead of recovery catalog configuration for DISK channel 3 is ignored allocated channel: ORA_DISK_1 channel ORA_DISK_1: SID=23 device type=DISK allocated channel: ORA_DISK_2 channel ORA_DISK_2: SID=248 device type=DISK channel ORA_DISK_1: starting datafile backup set restore channel ORA_DISK_1: specifying datafile(s) to restore from backup set channel ORA_DISK_1: restoring datafile 00032 to /u01/app/oracle/oradata/cdb1/pdb1/t1.dbf channel ORA_DISK_1: reading from backup piece /u01/app/oracle/backup2/08u94gei_1_1.bkp RMAN-00571: =========================================================== RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS =============== RMAN-00571: =========================================================== RMAN-03002: failure of restore command at 08/13/2019 18:58:26 ORA-19870: error while restoring backup piece /u01/app/oracle/backup2/08u94gei_1_1.bkp ORA-19573: cannot obtain exclusive enqueue for datafile 32 -bash-4.1$ sqlplus sys/oracle_4U@//racnode2/pdb1 as sysdba SQL> alter tablespace t1 offline immediate; alter tablespace t1 offline * ERROR at line 1: ORA-01116: error in opening database file 32 ORA-01110: data file 32: '/u01/app/oracle/oradata/cdb1/pdb1/t1.dbf' ORA-27041: unable to open file Linux-x86_64 Error: 2: No such file or directory Additional information: 3 SQL> quit -bash-4.1$ rman target / Recovery Manager: Release 12.1.0.2.0 - Production on Tue Aug 13 19:00:55 2019 Copyright (c) 1982, 2014, Oracle and/or its affiliates. All rights reserved. connected to target database: CDB1 (DBID=989477122) RMAN> restore datafile 32; Starting restore at Aug 13 2019 19:01:01 using target database control file instead of recovery catalog configuration for DISK channel 3 is ignored allocated channel: ORA_DISK_1 channel ORA_DISK_1: SID=23 device type=DISK allocated channel: ORA_DISK_2 channel ORA_DISK_2: SID=269 device type=DISK channel ORA_DISK_1: starting datafile backup set restore channel ORA_DISK_1: specifying datafile(s) to restore from backup set channel ORA_DISK_1: restoring datafile 00032 to /u01/app/oracle/oradata/cdb1/pdb1/t1.dbf channel ORA_DISK_1: reading from backup piece /u01/app/oracle/backup2/08u94gei_1_1.bkp channel ORA_DISK_1: piece handle=/u01/app/oracle/backup2/08u94gei_1_1.bkp tag=TAG20190813T184320 channel ORA_DISK_1: restored backup piece 1 channel ORA_DISK_1: restore complete, elapsed time: 00:00:01 Finished restore at Aug 13 2019 19:01:03 RMAN> recover datafile 32; Starting recover at Aug 13 2019 19:01:12 using channel ORA_DISK_1 using channel ORA_DISK_2 starting media recovery media recovery complete, elapsed time: 00:00:00 Finished recover at Aug 13 2019 19:01:12 -bash-4.1$ sqlplus sys/oracle_4U@//racnode2/pdb1 as sysdba SQL> alter tablespace t1 online; Tablespace altered. SQL> select * from t1.t1; ID ---------- 200 300 400 500 500 100 6 rows selected.
Recovering datafile without having any backup
SQL> create tablespace t5 datafile '/u01/app/oracle/oradata/cdb1/t5.dbf' size 5M SQL> create table t1 (ID NUMBER) TABLESPACE t5; SQL> insert into t1 2 select 100 from dual; SQL> COMMIT; SQL> SELECT * FROM T1; ID ---------- 100 SQL> !rm /u01/app/oracle/oradata/cdb1/t5.dbf SQL> SELECT * FROM T1; ID ---------- 100 SQL> alter system flush buffer_cachel; alter system flush buffer_cachel * ERROR at line 1: ORA-02000: missing SHARED_POOL/BUFFER_POOL/BUFFER_CACHE/GLOBAL CONTEXT keyword SQL> alter system flush buffer_cache; System altered. SQL> SELECT * FROM T1; ID ---------- 100 SQL> alter system flush buffer_cache; System altered. SQL> SELECT * FROM T1; ID ---------- 100 SQL> select count(*) from t1; COUNT(*) ---------- 1 SQL> desc t1 Name Null? Type ----------------------------------------- -------- ---------------------------- ID NUMBER SQL> insert into t1 values(2); insert into t1 values(2) * ERROR at line 1: ORA-01116: error in opening database file 31 ORA-01110: data file 31: '/u01/app/oracle/oradata/cdb1/t5.dbf' ORA-27041: unable to open file Linux-x86_64 Error: 2: No such file or directory Additional information: 3 -bash-4.1$ rman target / Recovery Manager: Release 12.1.0.2.0 - Production on Tue Aug 13 18:21:08 2019 Copyright (c) 1982, 2014, Oracle and/or its affiliates. All rights reserved. connected to target database: CDB1 (DBID=989477122) RMAN> report schema; using target database control file instead of recovery catalog Report of database schema for database with db_unique_name CDB1 List of Permanent Datafiles =========================== File Size(MB) Tablespace RB segs Datafile Name ---- -------- -------------------- ------- ------------------------ 1 800 SYSTEM YES /u01/app/oracle/oradata/cdb1/system01.dbf 3 710 SYSAUX NO /u01/app/oracle/oradata/cdb1/sysaux01.dbf 4 220 UNDOTBS1 YES /u01/app/oracle/oradata/cdb1/undotbs01.dbf 5 250 PDB$SEED:SYSTEM NO /u01/app/oracle/oradata/cdb1/pdbseed/system01.dbf 6 0 USERS NO /u01/app/oracle/product/12.1.0.2/db_1/dbs/MISSING00006 7 550 PDB$SEED:SYSAUX NO /u01/app/oracle/oradata/cdb1/pdbseed/sysaux01.dbf 8 270 PDB1:SYSTEM NO /u01/app/oracle/oradata/cdb1/pdb1/system01.dbf 9 590 PDB1:SYSAUX NO /u01/app/oracle/oradata/cdb1/pdb1/sysaux01.dbf 10 5 PDB1:USERS NO /u01/app/oracle/oradata/cdb1/pdb1/pdb1_users01.dbf 12 10 PDB1:RCATALOG1 NO /u01/app/oracle/oradata/cdb1/pdb1/rcatalog1.dbf 13 0 USERS NO /u01/app/oracle/product/12.1.0.2/db_1/dbs/MISSING00013 14 0 USERS NO /u01/app/oracle/product/12.1.0.2/db_1/dbs/MISSING00014 15 0 USERS NO /u01/app/oracle/product/12.1.0.2/db_1/dbs/MISSING00015 16 0 USERS NO /u01/app/oracle/product/12.1.0.2/db_1/dbs/MISSING00016 17 10 PDB1:USERS NO /u01/app/oracle/oradata/cdb1/pdb1/user02.dbf 18 0 USERS NO /u01/app/oracle/product/12.1.0.2/db_1/dbs/MISSING00018 19 0 USERS NO /u01/app/oracle/product/12.1.0.2/db_1/dbs/MISSING00019 20 0 USERS NO /u01/app/oracle/product/12.1.0.2/db_1/dbs/MISSING00020 21 0 USERS NO /u01/app/oracle/product/12.1.0.2/db_1/dbs/MISSING00021 22 0 USERS NO /u01/app/oracle/product/12.1.0.2/db_1/dbs/MISSING00022 23 0 USERS NO /u01/app/oracle/product/12.1.0.2/db_1/dbs/MISSING00023 25 15 PDB1:TEST_ENCRYPTION_BKP NO /u01/app/oracle/oradata/cdb1/pdb1/test01.dbf 26 10 MYTBS01 NO /u01/app/oracle/backup2/data_D-CDB1_I-989477122_TS-MYTBS01_FNO-26_96u93l8d.bkp 28 1 T2 NO /u01/app/oracle/oradata/cdb1/t2.dbf 29 1 T3 NO /u01/app/oracle/oradata/cdb1/t3.dbf 30 1 T4 NO /u01/app/oracle/oradata/cdb1/t4.dbf 31 0 T5 NO /u01/app/oracle/oradata/cdb1/t5.dbf RMAN> recover datafile 31; Starting recover at Aug 13 2019 18:21:39 configuration for DISK channel 3 is ignored allocated channel: ORA_DISK_1 channel ORA_DISK_1: SID=25 device type=DISK allocated channel: ORA_DISK_2 channel ORA_DISK_2: SID=237 device type=DISK RMAN-00571: =========================================================== RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS =============== RMAN-00571: =========================================================== RMAN-03002: failure of recover command at 08/13/2019 18:21:40 RMAN-06094: datafile 31 must be restored RMAN> restore datafile 31; Starting restore at Aug 13 2019 18:21:50 using channel ORA_DISK_1 using channel ORA_DISK_2 creating datafile file number=31 name=/u01/app/oracle/oradata/cdb1/t5.dbf RMAN-00571: =========================================================== RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS =============== RMAN-00571: =========================================================== RMAN-03002: failure of restore command at 08/13/2019 18:21:51 ORA-01182: cannot create database file 31 - file is in use or recovery ORA-01110: data file 31: '/u01/app/oracle/oradata/cdb1/t5.dbf' RMAN> sql 'alter tablespace t5 offline'; sql statement: alter tablespace t5 offline RMAN-00571: =========================================================== RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS =============== RMAN-00571: =========================================================== RMAN-03009: failure of sql command on default channel at 08/13/2019 18:22:29 RMAN-11003: failure during parse/execution of SQL statement: alter tablespace t5 offline ORA-01116: error in opening database file 31 ORA-01110: data file 31: '/u01/app/oracle/oradata/cdb1/t5.dbf' ORA-27041: unable to open file Linux-x86_64 Error: 2: No such file or directory Additional information: 3 RMAN> sql 'alter tablespace t5 offline immediate'; sql statement: alter tablespace t5 offline immediate RMAN> restore datafile 31; Starting restore at Aug 13 2019 18:22:51 using channel ORA_DISK_1 using channel ORA_DISK_2 creating datafile file number=31 name=/u01/app/oracle/oradata/cdb1/t5.dbf restore not done; all files read only, offline, or already restored Finished restore at Aug 13 2019 18:22:51 RMAN> recover datafile 31; Starting recover at Aug 13 2019 18:23:05 using channel ORA_DISK_1 using channel ORA_DISK_2 starting media recovery media recovery complete, elapsed time: 00:00:00 Finished recover at Aug 13 2019 18:23:05 RMAN> sql 'alter tablespace t5 online'; sql statement: alter tablespace t5 online RMAN> quit -bash-4.1$ sql SQL> insert into t1 values(2); 1 row created. SQL> insert into t1 values(200); 1 row created. SQL> commit; Commit complete. SQL> select * from t1; ID ---------- 100 2 200 SQL>
Using SET NEWNAME command to change location of the restored datafile
-bash-4.1$ rman target / Recovery Manager: Release 12.1.0.2.0 - Production on Tue Aug 13 21:44:30 2019 Copyright (c) 1982, 2014, Oracle and/or its affiliates. All rights reserved. connected to target database: CDB1 (DBID=989477122) RMAN> backup as copy tablespace t5; Starting backup at Aug 13 2019 21:44:56 using target database control file instead of recovery catalog configuration for DISK channel 3 is ignored allocated channel: ORA_DISK_1 channel ORA_DISK_1: SID=31 device type=DISK allocated channel: ORA_DISK_2 channel ORA_DISK_2: SID=279 device type=DISK channel ORA_DISK_1: starting datafile copy input datafile file number=00031 name=/u01/app/oracle/oradata/cdb1/t5.dbf output file name=/u01/app/oracle/backup1/data_D-CDB1_I-989477122_TS-T5_FNO-31_0hu94qqs.bk tag=TAG20190813T214459 RECID=2 STAMP=1016228701 channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:03 Finished backup at Aug 13 2019 21:45:03 Starting Control File and SPFILE Autobackup at Aug 13 2019 21:45:04 piece handle=/u01/app/oracle/fast_recovery_area/CDB1/autobackup/2019_08_13/o1_mf_s_1016228704_go5rjbdn_.bkp comment=NONE Finished Control File and SPFILE Autobackup at Aug 13 2019 21:45:11 RMAN> quit Recovery Manager complete. -bash-4.1$ rm /u01/app/oracle/oradata/cdb1/t5.dbf -bash-4.1$ sql SQL> startup force ORACLE instance started. Total System Global Area 1073741824 bytes Fixed Size 2932632 bytes Variable Size 666894440 bytes Database Buffers 398458880 bytes Redo Buffers 5455872 bytes Database mounted. ORA-01157: cannot identify/lock data file 31 - see DBWR trace file ORA-01110: data file 31: '/u01/app/oracle/oradata/cdb1/t5.dbf' SQL> quit -bash-4.1$ sql SQL> select open_mode from v$database; OPEN_MODE -------------------- MOUNTED SQL> quit -bash-4.1$ rman target / Recovery Manager: Release 12.1.0.2.0 - Production on Tue Aug 13 21:48:17 2019 Copyright (c) 1982, 2014, Oracle and/or its affiliates. All rights reserved. connected to target database: CDB1 (DBID=989477122, not open) RMAN> RMAN> switch datafile 31 to copy; using target database control file instead of recovery catalog datafile 31 switched to datafile copy "/u01/app/oracle/backup1/data_D-CDB1_I-989477122_TS-T5_FNO-31_0hu94qqs.bk RMAN> quit Recovery Manager complete. -bash-4.1$ sql SQL*Plus: Release 12.1.0.2.0 Production on Tue Aug 13 21:48:50 2019 Copyright (c) 1982, 2014, Oracle. All rights reserved. SQL> startup force ORACLE instance started. Total System Global Area 1073741824 bytes Fixed Size 2932632 bytes Variable Size 666894440 bytes Database Buffers 398458880 bytes Redo Buffers 5455872 bytes Database mounted. Database opened. SQL> select name from v$datafile; NAME -------------------------------------------------------------------------------- /u01/app/oracle/oradata/cdb1/system01.dbf /u01/app/oracle/oradata/cdb1/sysaux01.dbf /u01/app/oracle/oradata/cdb1/undotbs01.dbf /u01/app/oracle/oradata/cdb1/pdbseed/system01.dbf /u01/app/oracle/oradata/cdb1/pdbseed/sysaux01.dbf /u01/app/oracle/oradata/cdb1/pdb1/system01.dbf /u01/app/oracle/oradata/cdb1/pdb1/sysaux01.dbf /u01/app/oracle/oradata/cdb1/pdb1/pdb1_users01.dbf /u01/app/oracle/oradata/cdb1/pdb1/rcatalog1.dbf /u01/app/oracle/oradata/cdb1/pdb1/user02.dbf /u01/app/oracle/oradata/cdb1/pdb1/test01.dbf /u01/app/oracle/backup2/data_D-CDB1_I-989477122_TS-MYTBS01_FNO-26_96u93l8d.bkp /u01/app/oracle/oradata/cdb1/t2.dbf /u01/app/oracle/oradata/cdb1/t3.dbf /u01/app/oracle/oradata/cdb1/t4.dbf /u01/app/oracle/backup1/data_D-CDB1_I-989477122_TS-T5_FNO-31_0hu94qqs.bk /u01/app/oracle/oradata/cdb1/pdb1/t1.dbf /u01/app/oracle/backup1/data_D-CDB1_TS-USER2_FNO-33 /u01/app/oracle/oradata/cdb1/pdb1/t2.dbf 19 rows selected. SQL> quit -bash-4.1$ rman target / Recovery Manager: Release 12.1.0.2.0 - Production on Tue Aug 13 21:50:28 2019 Copyright (c) 1982, 2014, Oracle and/or its affiliates. All rights reserved. connected to target database: CDB1 (DBID=989477122) RMAN> run 2> { 3> set newname for tablespace t5 to '/u01/app/oracle/oradata/cdb1/%U'; 4> restore tablespace t5; 5> } executing command: SET NEWNAME Starting restore at Aug 13 2019 21:53:32 using target database control file instead of recovery catalog configuration for DISK channel 3 is ignored allocated channel: ORA_DISK_1 channel ORA_DISK_1: SID=21 device type=DISK allocated channel: ORA_DISK_2 channel ORA_DISK_2: SID=261 device type=DISK channel ORA_DISK_1: starting datafile backup set restore channel ORA_DISK_1: specifying datafile(s) to restore from backup set channel ORA_DISK_1: restoring datafile 00031 to /u01/app/oracle/oradata/cdb1/data_D-CDB1_TS-T5_FNO-31 channel ORA_DISK_1: reading from backup piece /u01/app/oracle/backup2/07u94g68_1_1.bkp channel ORA_DISK_1: piece handle=/u01/app/oracle/backup2/07u94g68_1_1.bkp tag=TAG20190813T184320 channel ORA_DISK_1: restored backup piece 1 channel ORA_DISK_1: restore complete, elapsed time: 00:00:01 Finished restore at Aug 13 2019 21:53:36 #-> Switch datafiles to copy to avoid the time need to restore RMAN> backup as copy database; -bash-4.1$ sql SQL> select name from v$datafile; NAME -------------------------------------------------------------------------------- /u01/app/oracle/oradata/cdb1/system01.dbf /u01/app/oracle/oradata/cdb1/sysaux01.dbf /u01/app/oracle/oradata/cdb1/undotbs01.dbf /u01/app/oracle/oradata/cdb1/pdbseed/system01.dbf /u01/app/oracle/oradata/cdb1/pdbseed/sysaux01.dbf /u01/app/oracle/oradata/cdb1/pdb1/system01.dbf /u01/app/oracle/oradata/cdb1/pdb1/sysaux01.dbf /u01/app/oracle/oradata/cdb1/pdb1/pdb1_users01.dbf /u01/app/oracle/oradata/cdb1/pdb1/rcatalog1.dbf /u01/app/oracle/oradata/cdb1/pdb1/user02.dbf /u01/app/oracle/oradata/cdb1/pdb1/test01.dbf /u01/app/oracle/backup2/data_D-CDB1_I-989477122_TS-MYTBS01_FNO-26_96u93l8d.bkp /u01/app/oracle/oradata/cdb1/t2.dbf /u01/app/oracle/oradata/cdb1/t3.dbf /u01/app/oracle/oradata/cdb1/t4.dbf /u01/app/oracle/backup1/data_D-CDB1_I-989477122_TS-T5_FNO-31_0hu94qqs.bk /u01/app/oracle/oradata/cdb1/pdb1/t1.dbf /u01/app/oracle/backup1/data_D-CDB1_TS-USER2_FNO-33 /u01/app/oracle/oradata/cdb1/pdb1/t2.dbf 19 rows selected. SQL> quit -bash-4.1$ rman target / Recovery Manager: Release 12.1.0.2.0 - Production on Tue Aug 13 22:05:05 2019 Copyright (c) 1982, 2014, Oracle and/or its affiliates. All rights reserved. connected to target database: CDB1 (DBID=989477122) RMAN> run 2> { 3> set newname for database to '/u02/app/oracle/oradata/cdb1/%U'; 4> restore database; 5> } -bash-4.1$ rman target / Recovery Manager: Release 12.1.0.2.0 - Production on Tue Aug 13 22:15:56 2019 Copyright (c) 1982, 2014, Oracle and/or its affiliates. All rights reserved. connected to target database: CDB1 (DBID=989477122) RMAN> switch database to copy; using target database control file instead of recovery catalog RMAN-00571: =========================================================== RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS =============== RMAN-00571: =========================================================== RMAN-03002: failure of switch to copy command at 08/13/2019 22:16:05 RMAN-06572: database is open and datafile 1 is not offline RMAN> quit Recovery Manager complete. -bash-4.1$ -bash-4.1$ sql SQL*Plus: Release 12.1.0.2.0 Production on Tue Aug 13 22:16:11 2019 Copyright (c) 1982, 2014, Oracle. All rights reserved. Connected to: Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options SQL> shut immediate Database closed. Database dismounted. ORACLE instance shut down. -bash-4.1$ rman target / Recovery Manager: Release 12.1.0.2.0 - Production on Tue Aug 13 22:17:20 2019 Copyright (c) 1982, 2014, Oracle and/or its affiliates. All rights reserved. connected to target database (not started) RMAN> startup mount; Oracle instance started database mounted Total System Global Area 1073741824 bytes Fixed Size 2932632 bytes Variable Size 666894440 bytes Database Buffers 398458880 bytes Redo Buffers 5455872 bytes RMAN> switch database to copy; using target database control file instead of recovery catalog datafile 1 switched to datafile copy "/u02/app/oracle/oradata/cdb1/data_D-CDB1_TS-SYSTEM_FNO-1 datafile 3 switched to datafile copy "/u02/app/oracle/oradata/cdb1/data_D-CDB1_TS-SYSAUX_FNO-3 datafile 4 switched to datafile copy "/u02/app/oracle/oradata/cdb1/data_D-CDB1_TS-UNDOTBS1_FNO-4 datafile 5 switched to datafile copy "/u02/app/oracle/oradata/cdb1/data_D-CDB1_TS-SYSTEM_FNO-5 datafile 7 switched to datafile copy "/u02/app/oracle/oradata/cdb1/data_D-CDB1_TS-SYSAUX_FNO-7 datafile 8 switched to datafile copy "/u02/app/oracle/oradata/cdb1/data_D-CDB1_TS-SYSTEM_FNO-8 datafile 9 switched to datafile copy "/u02/app/oracle/oradata/cdb1/data_D-CDB1_TS-SYSAUX_FNO-9 datafile 10 switched to datafile copy "/u02/app/oracle/oradata/cdb1/data_D-CDB1_TS-USERS_FNO-10 datafile 12 switched to datafile copy "/u02/app/oracle/oradata/cdb1/data_D-CDB1_TS-RCATALOG1_FNO-12 datafile 17 switched to datafile copy "/u02/app/oracle/oradata/cdb1/data_D-CDB1_TS-USERS_FNO-17 datafile 25 switched to datafile copy "/u02/app/oracle/oradata/cdb1/data_D-CDB1_TS-TEST_ENCRYPTION_BKP_FNO-25 datafile 26 switched to datafile copy "/u02/app/oracle/oradata/cdb1/data_D-CDB1_TS-MYTBS01_FNO-26 datafile 28 switched to datafile copy "/u02/app/oracle/oradata/cdb1/data_D-CDB1_TS-T2_FNO-28 datafile 29 switched to datafile copy "/u02/app/oracle/oradata/cdb1/data_D-CDB1_TS-T3_FNO-29 datafile 30 switched to datafile copy "/u02/app/oracle/oradata/cdb1/data_D-CDB1_TS-T4_FNO-30 datafile 31 switched to datafile copy "/u02/app/oracle/oradata/cdb1/data_D-CDB1_TS-T5_FNO-31 datafile 32 switched to datafile copy "/u02/app/oracle/oradata/cdb1/data_D-CDB1_TS-T1_FNO-32 datafile 33 switched to datafile copy "/u02/app/oracle/oradata/cdb1/data_D-CDB1_TS-USER2_FNO-33 datafile 34 switched to datafile copy "/u02/app/oracle/oradata/cdb1/data_D-CDB1_TS-T2_FNO-34 RMAN> quit Recovery Manager complete. -bash-4.1$ -bash-4.1$ sql SQL*Plus: Release 12.1.0.2.0 Production on Tue Aug 13 22:17:54 2019 Copyright (c) 1982, 2014, Oracle. All rights reserved. Connected to: Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options SQL> select name from v$datafile; NAME -------------------------------------------------------------------------------- /u02/app/oracle/oradata/cdb1/data_D-CDB1_TS-SYSTEM_FNO-1 /u02/app/oracle/oradata/cdb1/data_D-CDB1_TS-SYSAUX_FNO-3 /u02/app/oracle/oradata/cdb1/data_D-CDB1_TS-UNDOTBS1_FNO-4 /u02/app/oracle/oradata/cdb1/data_D-CDB1_TS-SYSTEM_FNO-5 /u02/app/oracle/oradata/cdb1/data_D-CDB1_TS-SYSAUX_FNO-7 /u02/app/oracle/oradata/cdb1/data_D-CDB1_TS-SYSTEM_FNO-8 /u02/app/oracle/oradata/cdb1/data_D-CDB1_TS-SYSAUX_FNO-9 /u02/app/oracle/oradata/cdb1/data_D-CDB1_TS-USERS_FNO-10 /u02/app/oracle/oradata/cdb1/data_D-CDB1_TS-RCATALOG1_FNO-12 /u02/app/oracle/oradata/cdb1/data_D-CDB1_TS-USERS_FNO-17 /u02/app/oracle/oradata/cdb1/data_D-CDB1_TS-TEST_ENCRYPTION_BKP_FNO-25 NAME -------------------------------------------------------------------------------- /u02/app/oracle/oradata/cdb1/data_D-CDB1_TS-MYTBS01_FNO-26 /u02/app/oracle/oradata/cdb1/data_D-CDB1_TS-T2_FNO-28 /u02/app/oracle/oradata/cdb1/data_D-CDB1_TS-T3_FNO-29 /u02/app/oracle/oradata/cdb1/data_D-CDB1_TS-T4_FNO-30 /u02/app/oracle/oradata/cdb1/data_D-CDB1_TS-T5_FNO-31 /u02/app/oracle/oradata/cdb1/data_D-CDB1_TS-T1_FNO-32 /u02/app/oracle/oradata/cdb1/data_D-CDB1_TS-USER2_FNO-33 /u02/app/oracle/oradata/cdb1/data_D-CDB1_TS-T2_FNO-34 19 rows selected.
Restore/Recover a tablespace
RMAN> restore tablespace PDB1:RCATALOG1; Starting restore at Aug 14 2019 09:42:35 using channel ORA_DISK_1 using channel ORA_DISK_2 channel ORA_DISK_1: restoring datafile 00012 input datafile copy RECID=65 STAMP=1016230938 file name=/u01/app/oracle/oradata/cdb1/data_D-CDB1_TS-RCATALOG1_FNO-12 destination for restore of datafile 00012: /u02/app/oracle/oradata/cdb1/data_D-CDB1_TS-RCATALOG1_FNO-12 RMAN-00571: =========================================================== RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS =============== RMAN-00571: =========================================================== RMAN-03002: failure of restore command at 08/14/2019 09:42:36 ORA-19573: cannot obtain exclusive enqueue for datafile 12 ORA-19600: input file is datafile-copy 65 (/u01/app/oracle/oradata/cdb1/data_D-CDB1_TS-RCATALOG1_FNO-12) ORA-19601: output file is datafile 12 (/u02/app/oracle/oradata/cdb1/data_D-CDB1_TS-RCATALOG1_FNO-12) RMAN> sql 'PDB1' 'alter database datafile 12 offline'; sql statement: alter database datafile 12 offline RMAN> restore tablespace PDB1:RCATALOG1; Starting restore at Aug 14 2019 09:43:19 using channel ORA_DISK_1 using channel ORA_DISK_2 channel ORA_DISK_1: restoring datafile 00012 input datafile copy RECID=65 STAMP=1016230938 file name=/u01/app/oracle/oradata/cdb1/data_D-CDB1_TS-RCATALOG1_FNO-12 destination for restore of datafile 00012: /u02/app/oracle/oradata/cdb1/data_D-CDB1_TS-RCATALOG1_FNO-12 channel ORA_DISK_1: copied datafile copy of datafile 00012 output file name=/u02/app/oracle/oradata/cdb1/data_D-CDB1_TS-RCATALOG1_FNO-12 RECID=0 STAMP=0 Finished restore at Aug 14 2019 09:43:20 RMAN> recover tablespace PDB1:RCATALOG1; Starting recover at Aug 14 2019 09:43:32 using channel ORA_DISK_1 using channel ORA_DISK_2 starting media recovery media recovery complete, elapsed time: 00:00:00 Finished recover at Aug 14 2019 09:43:32 RMAN> sql 'PDB1' 'alter database datafile 12 online'; sql statement: alter database datafile 12 online
Restoring archived redo log files.
RESTORE ARCHIVELOG FROM SCN 459723; RESTORE ARCHIVELOG FROM SCN BETWEEN 234324 AND 54545; RESTORE ARCHIVELOG FROM SEQUENCE 774; RESTORE ARCHIVELOG FROM SEQUENCE 23 UNTIL SEQUENCE 25; RESTORE ARCHIVELOG FROM LOGSEQ 774 UNTIL 800 [THREAD 1]; -- FOR RAC. RESTORE ARCHIVELOG UNTIL TIME TO_DATE('14/08/19 12:00:00','DD/MM/YY HH24:MI:SS)" [1] Restore archivelog in different location. RMAN> backup archivelog all delete input; Starting backup at Aug 14 2019 10:12:08 current log archived using channel ORA_DISK_1 using channel ORA_DISK_2 channel ORA_DISK_1: starting archived log backup set channel ORA_DISK_1: specifying archived log(s) in backup set input archived log thread=1 sequence=24 RECID=32 STAMP=1016273261 input archived log thread=1 sequence=25 RECID=33 STAMP=1016273372 channel ORA_DISK_1: starting piece 1 at Aug 14 2019 10:12:09 channel ORA_DISK_2: starting archived log backup set channel ORA_DISK_2: specifying archived log(s) in backup set input archived log thread=1 sequence=26 RECID=34 STAMP=1016273380 input archived log thread=1 sequence=27 RECID=35 STAMP=1016273387 input archived log thread=1 sequence=28 RECID=36 STAMP=1016273399 input archived log thread=1 sequence=29 RECID=37 STAMP=1016273455 channel ORA_DISK_2: starting piece 1 at Aug 14 2019 10:12:09 channel ORA_DISK_1: finished piece 1 at Aug 14 2019 10:12:10 piece handle=/u01/app/oracle/fast_recovery_area/CDB1/backupset/2019_08_14/o1_mf_annnn_TAG20190814T101208_go74912b_.bkp tag=TAG20190814T101208 comment=NONE channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01 channel ORA_DISK_1: deleting archived log(s) archived log file name=/u01/app/oracle/fast_recovery_area/CDB1/archivelog/2019_08_14/o1_mf_1_24_go740o3n_.arc RECID=32 STAMP=1016273261 archived log file name=/u01/app/oracle/fast_recovery_area/CDB1/archivelog/2019_08_14/o1_mf_1_25_go7444kx_.arc RECID=33 STAMP=1016273372 channel ORA_DISK_1: starting archived log backup set channel ORA_DISK_1: specifying archived log(s) in backup set input archived log thread=1 sequence=30 RECID=38 STAMP=1016273528 channel ORA_DISK_1: starting piece 1 at Aug 14 2019 10:12:10 channel ORA_DISK_2: finished piece 1 at Aug 14 2019 10:12:10 piece handle=/u01/app/oracle/fast_recovery_area/CDB1/backupset/2019_08_14/o1_mf_annnn_TAG20190814T101208_go74914n_.bkp tag=TAG20190814T101208 comment=NONE channel ORA_DISK_2: backup set complete, elapsed time: 00:00:01 channel ORA_DISK_2: deleting archived log(s) archived log file name=/u01/app/oracle/fast_recovery_area/CDB1/archivelog/2019_08_14/o1_mf_1_26_go744d4m_.arc RECID=34 STAMP=1016273380 archived log file name=/u01/app/oracle/fast_recovery_area/CDB1/archivelog/2019_08_14/o1_mf_1_27_go744mmo_.arc RECID=35 STAMP=1016273387 archived log file name=/u01/app/oracle/fast_recovery_area/CDB1/archivelog/2019_08_14/o1_mf_1_28_go744znl_.arc RECID=36 STAMP=1016273399 archived log file name=/u01/app/oracle/fast_recovery_area/CDB1/archivelog/2019_08_14/o1_mf_1_29_go746qtj_.arc RECID=37 STAMP=1016273455 channel ORA_DISK_1: finished piece 1 at Aug 14 2019 10:12:10 piece handle=/u01/app/oracle/fast_recovery_area/CDB1/backupset/2019_08_14/o1_mf_annnn_TAG20190814T101208_go7492gx_.bkp tag=TAG20190814T101208 comment=NONE channel ORA_DISK_1: backup set complete, elapsed time: 00:00:00 channel ORA_DISK_1: deleting archived log(s) archived log file name=/u01/app/oracle/fast_recovery_area/CDB1/archivelog/2019_08_14/o1_mf_1_30_go7490fn_.arc RECID=38 STAMP=1016273528 Finished backup at Aug 14 2019 10:12:10 Starting Control File and SPFILE Autobackup at Aug 14 2019 10:12:10 piece handle=/u01/app/oracle/fast_recovery_area/CDB1/autobackup/2019_08_14/o1_mf_s_1016273530_go74944n_.bkp comment=NONE Finished Control File and SPFILE Autobackup at Aug 14 2019 10:12:14 RMAN> run 2> { 3> set archivelog destination to '/u01/app/oracle/backup1'; 4> restore archivelog from sequence 25; 5> } executing command: SET ARCHIVELOG DESTINATION Starting restore at Aug 14 2019 10:12:41 using channel ORA_DISK_1 using channel ORA_DISK_2 channel ORA_DISK_1: starting archived log restore to user-specified destination archived log destination=/u01/app/oracle/backup1 channel ORA_DISK_1: restoring archived log archived log thread=1 sequence=25 channel ORA_DISK_1: reading from backup piece /u01/app/oracle/fast_recovery_area/CDB1/backupset/2019_08_14/o1_mf_annnn_TAG20190814T101208_go74912b_.bkp channel ORA_DISK_2: starting archived log restore to user-specified destination archived log destination=/u01/app/oracle/backup1 channel ORA_DISK_2: restoring archived log archived log thread=1 sequence=26 channel ORA_DISK_2: restoring archived log archived log thread=1 sequence=27 channel ORA_DISK_2: restoring archived log archived log thread=1 sequence=28 channel ORA_DISK_2: restoring archived log archived log thread=1 sequence=29 channel ORA_DISK_2: reading from backup piece /u01/app/oracle/fast_recovery_area/CDB1/backupset/2019_08_14/o1_mf_annnn_TAG20190814T101208_go74914n_.bkp channel ORA_DISK_1: piece handle=/u01/app/oracle/fast_recovery_area/CDB1/backupset/2019_08_14/o1_mf_annnn_TAG20190814T101208_go74912b_.bkp tag=TAG20190814T101208 channel ORA_DISK_1: restored backup piece 1 channel ORA_DISK_1: restore complete, elapsed time: 00:00:01 channel ORA_DISK_1: starting archived log restore to user-specified destination archived log destination=/u01/app/oracle/backup1 channel ORA_DISK_1: restoring archived log archived log thread=1 sequence=30 channel ORA_DISK_1: reading from backup piece /u01/app/oracle/fast_recovery_area/CDB1/backupset/2019_08_14/o1_mf_annnn_TAG20190814T101208_go7492gx_.bkp channel ORA_DISK_2: piece handle=/u01/app/oracle/fast_recovery_area/CDB1/backupset/2019_08_14/o1_mf_annnn_TAG20190814T101208_go74914n_.bkp tag=TAG20190814T101208 channel ORA_DISK_2: restored backup piece 1 channel ORA_DISK_2: restore complete, elapsed time: 00:00:01 channel ORA_DISK_1: piece handle=/u01/app/oracle/fast_recovery_area/CDB1/backupset/2019_08_14/o1_mf_annnn_TAG20190814T101208_go7492gx_.bkp tag=TAG20190814T101208 channel ORA_DISK_1: restored backup piece 1 channel ORA_DISK_1: restore complete, elapsed time: 00:00:01 Finished restore at Aug 14 2019 10:12:43 RMAN> quit Recovery Manager complete. -bash-4.1$ ll /u01/app/oracle/backup1 total 1177692 -rw-r----- 1 oracle oinstall 28672 Aug 14 10:12 1_25_1016212613.dbf -rw-r----- 1 oracle oinstall 5632 Aug 14 10:12 1_26_1016212613.dbf -rw-r----- 1 oracle oinstall 6144 Aug 14 10:12 1_27_1016212613.dbf -rw-r----- 1 oracle oinstall 1536 Aug 14 10:12 1_28_1016212613.dbf -rw-r----- 1 oracle oinstall 15872 Aug 14 10:12 1_29_1016212613.dbf -rw-r----- 1 oracle oinstall 1536 Aug 14 10:12 1_30_1016212613.dbf [2] Restore archivelog from scn. RMAN> restore archivelog from scn 3468866; Starting restore at Aug 14 2019 10:18:08 using target database control file instead of recovery catalog allocated channel: ORA_DISK_1 channel ORA_DISK_1: SID=248 device type=DISK allocated channel: ORA_DISK_2 channel ORA_DISK_2: SID=23 device type=DISK archived log for thread 1 with sequence 25 is already on disk as file /u01/app/oracle/backup1/1_25_1016212613.dbf archived log for thread 1 with sequence 26 is already on disk as file /u01/app/oracle/backup1/1_26_1016212613.dbf archived log for thread 1 with sequence 27 is already on disk as file /u01/app/oracle/backup1/1_27_1016212613.dbf archived log for thread 1 with sequence 28 is already on disk as file /u01/app/oracle/backup1/1_28_1016212613.dbf archived log for thread 1 with sequence 29 is already on disk as file /u01/app/oracle/backup1/1_29_1016212613.dbf archived log for thread 1 with sequence 30 is already on disk as file /u01/app/oracle/backup1/1_30_1016212613.dbf channel ORA_DISK_1: starting archived log restore to default destination channel ORA_DISK_1: restoring archived log archived log thread=1 sequence=23 channel ORA_DISK_1: reading from backup piece /u01/app/oracle/fast_recovery_area/CDB1/backupset/2019_08_14/o1_mf_annnn_TAG20190814T100520_go73w8hk_.bkp channel ORA_DISK_2: starting archived log restore to default destination channel ORA_DISK_2: restoring archived log archived log thread=1 sequence=24 channel ORA_DISK_2: reading from backup piece /u01/app/oracle/fast_recovery_area/CDB1/backupset/2019_08_14/o1_mf_annnn_TAG20190814T101208_go74912b_.bkp channel ORA_DISK_1: piece handle=/u01/app/oracle/fast_recovery_area/CDB1/backupset/2019_08_14/o1_mf_annnn_TAG20190814T100520_go73w8hk_.bkp tag=TAG20190814T100520 channel ORA_DISK_1: restored backup piece 1 channel ORA_DISK_1: restore complete, elapsed time: 00:00:01 channel ORA_DISK_2: piece handle=/u01/app/oracle/fast_recovery_area/CDB1/backupset/2019_08_14/o1_mf_annnn_TAG20190814T101208_go74912b_.bkp tag=TAG20190814T101208 channel ORA_DISK_2: restored backup piece 1 channel ORA_DISK_2: restore complete, elapsed time: 00:00:01 Finished restore at Aug 14 2019 10:18:11 [3] Restore archive log from sequence 23 to 25. RMAN> RESTORE ARCHIVELOG FROM SEQUENCE 23 UNTIL SEQUENCE 25; Starting restore at Aug 14 2019 10:24:36 using channel ORA_DISK_1 using channel ORA_DISK_2 archived log for thread 1 with sequence 23 is already on disk as file /u01/app/oracle/fast_recovery_area/CDB1/archivelog/2019_08_14/o1_mf_1_23_go74nb96_.arc archived log for thread 1 with sequence 24 is already on disk as file /u01/app/oracle/fast_recovery_area/CDB1/archivelog/2019_08_14/o1_mf_1_24_go74nb9k_.arc archived log for thread 1 with sequence 25 is already on disk as file /u01/app/oracle/backup1/1_25_1016212613.dbf restore not done; all files read only, offline, or already restored Finished restore at Aug 14 2019 10:24:36
Perform Block media recovery
-bash-4.1$ sql SQL*Plus: Release 12.1.0.2.0 Production on Wed Aug 14 10:29:30 2019 Copyright (c) 1982, 2014, Oracle. All rights reserved. Connected to: Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options SQL> alter session set container=pdb1; Session altered. SQL> create tablespace tc datafile '/u01/app/oracle/oradata/cdb1/pdb1/tc.dbf' size 9M; Tablespace created. SQL> create user tc identified by tc default tablespace tc quota unlimited on tc temporary tablespace temp 2 ; User created. SQL> grant connect,resource to tc; Grant succeeded. SQL> connect tc/tc@//racnode2/pdb1 Connected. SQL> create table tbl_tc (id number); Table created. SQL> insert into tbl_tc values (1); 1 row created. SQL> commit; Commit complete. SQL> select * from tbl_tc; ID ---------- 1 SQL> connect sys/oracle_4U@//racnode2/pdb1 as sysdba Connected. SQL> select header_block from dba_segments where segment_name ='TBL_TC'; HEADER_BLOCK ------------ 130 -bash-4.1$ rman target / Recovery Manager: Release 12.1.0.2.0 - Production on Wed Aug 14 10:33:17 2019 Copyright (c) 1982, 2014, Oracle and/or its affiliates. All rights reserved. connected to target database: CDB1 (DBID=989477122) RMAN> report schema; using target database control file instead of recovery catalog Report of database schema for database with db_unique_name CDB1 List of Permanent Datafiles =========================== File Size(MB) Tablespace RB segs Datafile Name ---- -------- -------------------- ------- ------------------------ 1 810 SYSTEM YES /u02/app/oracle/oradata/cdb1/data_D-CDB1_TS-SYSTEM_FNO-1 3 710 SYSAUX NO /u02/app/oracle/oradata/cdb1/data_D-CDB1_TS-SYSAUX_FNO-3 4 220 UNDOTBS1 YES /u02/app/oracle/oradata/cdb1/data_D-CDB1_TS-UNDOTBS1_FNO-4 5 250 PDB$SEED:SYSTEM NO /u02/app/oracle/oradata/cdb1/data_D-CDB1_TS-SYSTEM_FNO-5 7 550 PDB$SEED:SYSAUX NO /u02/app/oracle/oradata/cdb1/data_D-CDB1_TS-SYSAUX_FNO-7 8 270 PDB1:SYSTEM NO /u02/app/oracle/oradata/cdb1/data_D-CDB1_TS-SYSTEM_FNO-8 9 590 PDB1:SYSAUX NO /u02/app/oracle/oradata/cdb1/data_D-CDB1_TS-SYSAUX_FNO-9 10 5 PDB1:USERS NO /u02/app/oracle/oradata/cdb1/data_D-CDB1_TS-USERS_FNO-10 12 10 PDB1:RCATALOG1 NO /u02/app/oracle/oradata/cdb1/data_D-CDB1_TS-RCATALOG1_FNO-12 17 10 PDB1:USERS NO /u02/app/oracle/oradata/cdb1/data_D-CDB1_TS-USERS_FNO-17 25 15 PDB1:TEST_ENCRYPTION_BKP NO /u02/app/oracle/oradata/cdb1/data_D-CDB1_TS-TEST_ENCRYPTION_BKP_FNO-25 26 10 MYTBS01 NO /u02/app/oracle/oradata/cdb1/data_D-CDB1_TS-MYTBS01_FNO-26 28 1 T2 NO /u02/app/oracle/oradata/cdb1/data_D-CDB1_TS-T2_FNO-28 29 1 T3 NO /u02/app/oracle/oradata/cdb1/data_D-CDB1_TS-T3_FNO-29 30 1 T4 NO /u02/app/oracle/oradata/cdb1/data_D-CDB1_TS-T4_FNO-30 31 5 T5 NO /u02/app/oracle/oradata/cdb1/data_D-CDB1_TS-T5_FNO-31 32 5 PDB1:T1 NO /u02/app/oracle/oradata/cdb1/data_D-CDB1_TS-T1_FNO-32 33 10 USER2 NO /u02/app/oracle/oradata/cdb1/data_D-CDB1_TS-USER2_FNO-33 34 5 PDB1:T2 NO /u02/app/oracle/oradata/cdb1/data_D-CDB1_TS-T2_FNO-34 35 9 PDB1:TC NO /u01/app/oracle/oradata/cdb1/pdb1/tc.dbf RMAN> backup datafile 35; Starting backup at Aug 14 2019 10:33:35 allocated channel: ORA_DISK_1 channel ORA_DISK_1: SID=279 device type=DISK allocated channel: ORA_DISK_2 channel ORA_DISK_2: SID=23 device type=DISK channel ORA_DISK_1: starting full datafile backup set channel ORA_DISK_1: specifying datafile(s) in backup set input datafile file number=00035 name=/u01/app/oracle/oradata/cdb1/pdb1/tc.dbf channel ORA_DISK_1: starting piece 1 at Aug 14 2019 10:33:36 channel ORA_DISK_1: finished piece 1 at Aug 14 2019 10:33:37 piece handle=/u01/app/oracle/fast_recovery_area/CDB1/8F95F488F27F26B1E0536638A8C085A3/backupset/2019_08_14/o1_mf_nnndf_TAG20190814T103336_go75k8vm_.bkp tag=TAG20190814T103336 comment=NONE channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01 Finished backup at Aug 14 2019 10:33:37 Starting Control File and SPFILE Autobackup at Aug 14 2019 10:33:37 piece handle=/u01/app/oracle/fast_recovery_area/CDB1/autobackup/2019_08_14/o1_mf_s_1016274818_go75kbkj_.bkp comment=NONE Finished Control File and SPFILE Autobackup at Aug 14 2019 10:33:39 RMAN> quit Recovery Manager complete. -bash-4.1$ -bash-4.1$ dd of=/u01/app/oracle/oradata/cdb1/pdb1/tc.dbf bs=8192 conv=notrunc seek=130 << EOF > This is corruption > EOF 0+1 records in 0+1 records out 19 bytes (19 B) copied, 0.000893291 s, 21.3 kB/s -bash-4.1$ -bash-4.1$ sql SQL> connect sys/oracle_4U@//racnode2/pdb1 as sysdba Connected. SQL> SQL> alter system flush buffer_cache; System altered. SQL> select * from tc.tbl_tc; select * from tc.tbl_tc * ERROR at line 1: ORA-01578: ORACLE data block corrupted (file # 35, block # 130) ORA-01110: data file 35: '/u01/app/oracle/oradata/cdb1/pdb1/tc.dbf' -bash-4.1$ sql SQL> select * from v$database_block_corruption; FILE# BLOCK# BLOCKS CORRUPTION_CHANGE# CORRUPTIO CON_ID ---------- ---------- ---------- ------------------ --------- ---------- 35 130 1 0 CORRUPT 0 -bash-4.1$ rman target / Recovery Manager: Release 12.1.0.2.0 - Production on Wed Aug 14 10:36:25 2019 Copyright (c) 1982, 2014, Oracle and/or its affiliates. All rights reserved. connected to target database: CDB1 (DBID=989477122) RMAN> recover datafile 35 block 130; Starting recover at Aug 14 2019 10:36:55 using target database control file instead of recovery catalog allocated channel: ORA_DISK_1 channel ORA_DISK_1: SID=269 device type=DISK allocated channel: ORA_DISK_2 channel ORA_DISK_2: SID=23 device type=DISK channel ORA_DISK_1: restoring block(s) channel ORA_DISK_1: specifying block(s) to restore from backup set restoring blocks of datafile 00035 channel ORA_DISK_1: reading from backup piece /u01/app/oracle/fast_recovery_area/CDB1/8F95F488F27F26B1E0536638A8C085A3/backupset/2019_08_14/o1_mf_nnndf_TAG20190814T103336_go75k8vm_.bkp channel ORA_DISK_1: piece handle=/u01/app/oracle/fast_recovery_area/CDB1/8F95F488F27F26B1E0536638A8C085A3/backupset/2019_08_14/o1_mf_nnndf_TAG20190814T103336_go75k8vm_.bkp tag=TAG20190814T103336 channel ORA_DISK_1: restored block(s) from backup piece 1 channel ORA_DISK_1: block restore complete, elapsed time: 00:00:01 starting media recovery media recovery complete, elapsed time: 00:00:03 Finished recover at Aug 14 2019 10:37:01 RMAN> quit Recovery Manager complete. -bash-4.1$ sqlplus sys/oracle_4U@//racnode2/pdb1 as sysdba SQL> select * from tc.tbl_tc; ID ---------- 1 -bash-4.1$ rman target / Recovery Manager: Release 12.1.0.2.0 - Production on Wed Aug 14 10:37:37 2019 Copyright (c) 1982, 2014, Oracle and/or its affiliates. All rights reserved. connected to target database: CDB1 (DBID=989477122) RMAN> blockrecover corruption list; Starting recover at Aug 14 2019 10:37:47 using target database control file instead of recovery catalog allocated channel: ORA_DISK_1 channel ORA_DISK_1: SID=279 device type=DISK allocated channel: ORA_DISK_2 channel ORA_DISK_2: SID=23 device type=DISK starting media recovery media recovery complete, elapsed time: 00:00:00 Finished recover at Aug 14 2019 10:37:49 RMAN> quit Recovery Manager complete. -bash-4.1$ sql SQL> select * from v$database_block_corruption; no rows selected ----------------------------- Performing disaster recovery ----------------------------- SQL> select name from v$datafile 2 union all 3 select member from v$logfile 4 union all 5 select name from v$controlfile; NAME -------------------------------------------------------------------------------- /u02/app/oracle/oradata/cdb1/data_D-CDB1_TS-SYSTEM_FNO-1 /u02/app/oracle/oradata/cdb1/data_D-CDB1_TS-SYSAUX_FNO-3 /u02/app/oracle/oradata/cdb1/data_D-CDB1_TS-UNDOTBS1_FNO-4 /u02/app/oracle/oradata/cdb1/data_D-CDB1_TS-SYSTEM_FNO-5 /u02/app/oracle/oradata/cdb1/data_D-CDB1_TS-SYSAUX_FNO-7 /u02/app/oracle/oradata/cdb1/data_D-CDB1_TS-SYSTEM_FNO-8 /u02/app/oracle/oradata/cdb1/data_D-CDB1_TS-SYSAUX_FNO-9 /u02/app/oracle/oradata/cdb1/data_D-CDB1_TS-USERS_FNO-10 /u02/app/oracle/oradata/cdb1/data_D-CDB1_TS-RCATALOG1_FNO-12 /u02/app/oracle/oradata/cdb1/data_D-CDB1_TS-USERS_FNO-17 /u02/app/oracle/oradata/cdb1/data_D-CDB1_TS-TEST_ENCRYPTION_BKP_FNO-25 /u02/app/oracle/oradata/cdb1/data_D-CDB1_TS-MYTBS01_FNO-26 /u02/app/oracle/oradata/cdb1/data_D-CDB1_TS-T2_FNO-28 /u02/app/oracle/oradata/cdb1/data_D-CDB1_TS-T3_FNO-29 /u02/app/oracle/oradata/cdb1/data_D-CDB1_TS-T4_FNO-30 /u02/app/oracle/oradata/cdb1/data_D-CDB1_TS-T5_FNO-31 /u02/app/oracle/oradata/cdb1/data_D-CDB1_TS-T1_FNO-32 /u02/app/oracle/oradata/cdb1/data_D-CDB1_TS-USER2_FNO-33 /u02/app/oracle/oradata/cdb1/data_D-CDB1_TS-T2_FNO-34 /u01/app/oracle/oradata/cdb1/pdb1/tc.dbf /u01/app/oracle/oradata/cdb1/redo01.log /u01/app/oracle/oradata/cdb1/redo03.log /u01/app/oracle/oradata/cdb1/redo02.log /u01/app/oracle/oradata/cdb1/control01.ctl /u01/app/oracle/fast_recovery_area/cdb1/control02.ctl RMAN> backup database plus archivelog delete input format '/u01/app/oracle/backup2/full_backup_cdb1_%U.bkp'; -bash-4.1$ cd /u02/app/oracle/oradata/cdb1 -bash-4.1$ ls data_D-CDB1_TS-MYTBS01_FNO-26 data_D-CDB1_TS-SYSAUX_FNO-9 data_D-CDB1_TS-T1_FNO-32 data_D-CDB1_TS-T4_FNO-30 data_D-CDB1_TS-USER2_FNO-33 data_D-CDB1_TS-RCATALOG1_FNO-12 data_D-CDB1_TS-SYSTEM_FNO-1 data_D-CDB1_TS-T2_FNO-28 data_D-CDB1_TS-T5_FNO-31 data_D-CDB1_TS-USERS_FNO-10 data_D-CDB1_TS-SYSAUX_FNO-3 data_D-CDB1_TS-SYSTEM_FNO-5 data_D-CDB1_TS-T2_FNO-34 data_D-CDB1_TS-TEST_ENCRYPTION_BKP_FNO-25 data_D-CDB1_TS-USERS_FNO-17 data_D-CDB1_TS-SYSAUX_FNO-7 data_D-CDB1_TS-SYSTEM_FNO-8 data_D-CDB1_TS-T3_FNO-29 data_D-CDB1_TS-UNDOTBS1_FNO-4 -bash-4.1$ rm -rf * -bash-4.1$ ls -bash-4.1$ -bash-4.1$ cd /u01/app/oracle/oradata/cdb1/ -bash-4.1$ ls afiedt.buf data_D-CDB1_TS-SYSAUX_FNO-7 mytbs01.dbf.bkp pdbseed redo02.log redo4.log redo6.log control01.ctl data_D-CDB1_TS-SYSTEM_FNO-5 pdb1 redo01.log redo03.log redo5.log -bash-4.1$ pwd /u01/app/oracle/oradata/cdb1 -bash-4.1$ rm -rf * -bash-4.1$ ls -bash-4.1$ cd /u01/app/oracle/oradata/cdb1/ -bash-4.1$ ll total 0 -bash-4.1$ cd /u01/app/oracle/fast_recovery_area/cdb1/ -bash-4.1$ ll total 18160 -rw-r----- 1 oracle oinstall 18595840 Aug 14 10:50 control02.ctl -bash-4.1$ -bash-4.1$ rm control02.ctl -bash-4.1$ -bash-4.1$ dbs -bash-4.1$ ls hc_cdb1.dat hc_orcl.dat initcdb1.ora init.ora initorcl.ora lkCDB1 lkDUMMY lkORCL orapwcdb1 orapworcl snapcf_cdb1.f spfilecdb1.ora spfileorcl.ora -bash-4.1$ -bash-4.1$ rm spfilecdb1.ora -bash-4.1$ rm initcdb1.ora -bash-4.1$ -bash-4.1$ sql SQL> startup force ORA-01078: failure in processing system parameters LRM-00109: could not open parameter file '/u01/app/oracle/product/12.1.0.2/db_1/dbs/initcdb1.ora' -bash-4.1$ -bash-4.1$ rman target / Recovery Manager: Release 12.1.0.2.0 - Production on Wed Aug 14 10:51:25 2019 Copyright (c) 1982, 2014, Oracle and/or its affiliates. All rights reserved. connected to target database (not started) RMAN> startup nomount; startup failed: ORA-01078: failure in processing system parameters LRM-00109: could not open parameter file '/u01/app/oracle/product/12.1.0.2/db_1/dbs/initcdb1.ora' starting Oracle instance without parameter file for retrieval of spfile Oracle instance started Total System Global Area 1073741824 bytes Fixed Size 2932632 bytes Variable Size 281018472 bytes Database Buffers 784334848 bytes Redo Buffers 5455872 bytes RMAN> restore spfile from '/u01/app/oracle/fast_recovery_area/CDB1/autobackup/2019_08_14/o1_mf_s_1016275497_go766kwo_.bkp'; Starting restore at Aug 14 2019 10:53:21 using target database control file instead of recovery catalog allocated channel: ORA_DISK_1 channel ORA_DISK_1: SID=12 device type=DISK channel ORA_DISK_1: restoring spfile from AUTOBACKUP /u01/app/oracle/fast_recovery_area/CDB1/autobackup/2019_08_14/o1_mf_s_1016275497_go766kwo_.bkp channel ORA_DISK_1: SPFILE restore from AUTOBACKUP complete Finished restore at Aug 14 2019 10:53:23 RMAN> startup force nomount Oracle instance started Total System Global Area 1073741824 bytes Fixed Size 2932632 bytes Variable Size 666894440 bytes Database Buffers 398458880 bytes Redo Buffers 5455872 bytes RMAN> restore controlfile from '/u01/app/oracle/fast_recovery_area/CDB1/autobackup/2019_08_14/o1_mf_s_1016275497_go766kwo_.bkp'; Starting restore at Aug 14 2019 10:54:10 allocated channel: ORA_DISK_1 channel ORA_DISK_1: SID=12 device type=DISK channel ORA_DISK_1: restoring control file channel ORA_DISK_1: restore complete, elapsed time: 00:00:03 output file name=/u01/app/oracle/oradata/cdb1/control01.ctl output file name=/u01/app/oracle/fast_recovery_area/cdb1/control02.ctl Finished restore at Aug 14 2019 10:54:14 RMAN> alter database mount; Statement processed released channel: ORA_DISK_1 RMAN> restore database; Starting restore at Aug 14 2019 10:54:44 Starting implicit crosscheck backup at Aug 14 2019 10:54:44 allocated channel: ORA_DISK_1 channel ORA_DISK_1: SID=12 device type=DISK allocated channel: ORA_DISK_2 channel ORA_DISK_2: SID=249 device type=DISK Crosschecked 24 objects Finished implicit crosscheck backup at Aug 14 2019 10:54:46 Starting implicit crosscheck copy at Aug 14 2019 10:54:46 using channel ORA_DISK_1 using channel ORA_DISK_2 Crosschecked 2 objects Finished implicit crosscheck copy at Aug 14 2019 10:54:47 searching for all files in the recovery area cataloging files... cataloging done List of Cataloged Files ======================= File Name: /u01/app/oracle/fast_recovery_area/CDB1/autobackup/2019_08_13/o1_mf_s_1016195699_go4r8w7n_.bkp File Name: /u01/app/oracle/fast_recovery_area/CDB1/autobackup/2019_08_13/o1_mf_s_1016212626_go58sv6f_.bkp File Name: /u01/app/oracle/fast_recovery_area/CDB1/autobackup/2019_08_14/o1_mf_s_1016275497_go766kwo_.bkp using channel ORA_DISK_1 using channel ORA_DISK_2 channel ORA_DISK_1: starting datafile backup set restore channel ORA_DISK_1: specifying datafile(s) to restore from backup set channel ORA_DISK_1: restoring datafile 00003 to /u02/app/oracle/oradata/cdb1/data_D-CDB1_TS-SYSAUX_FNO-3 channel ORA_DISK_1: restoring datafile 00026 to /u02/app/oracle/oradata/cdb1/data_D-CDB1_TS-MYTBS01_FNO-26 channel ORA_DISK_1: restoring datafile 00028 to /u02/app/oracle/oradata/cdb1/data_D-CDB1_TS-T2_FNO-28 channel ORA_DISK_1: restoring datafile 00031 to /u02/app/oracle/oradata/cdb1/data_D-CDB1_TS-T5_FNO-31 channel ORA_DISK_1: restoring datafile 00033 to /u02/app/oracle/oradata/cdb1/data_D-CDB1_TS-USER2_FNO-33 channel ORA_DISK_1: reading from backup piece /u01/app/oracle/fast_recovery_area/CDB1/backupset/2019_08_14/o1_mf_nnndf_TAG20190814T104215_go761j05_.bkp channel ORA_DISK_2: starting datafile backup set restore channel ORA_DISK_2: specifying datafile(s) to restore from backup set channel ORA_DISK_2: restoring datafile 00001 to /u02/app/oracle/oradata/cdb1/data_D-CDB1_TS-SYSTEM_FNO-1 channel ORA_DISK_2: restoring datafile 00004 to /u02/app/oracle/oradata/cdb1/data_D-CDB1_TS-UNDOTBS1_FNO-4 channel ORA_DISK_2: restoring datafile 00029 to /u02/app/oracle/oradata/cdb1/data_D-CDB1_TS-T3_FNO-29 channel ORA_DISK_2: restoring datafile 00030 to /u02/app/oracle/oradata/cdb1/data_D-CDB1_TS-T4_FNO-30 channel ORA_DISK_2: reading from backup piece /u01/app/oracle/fast_recovery_area/CDB1/backupset/2019_08_14/o1_mf_nnndf_TAG20190814T104215_go761hrr_.bkp channel ORA_DISK_1: piece handle=/u01/app/oracle/fast_recovery_area/CDB1/backupset/2019_08_14/o1_mf_nnndf_TAG20190814T104215_go761j05_.bkp tag=TAG20190814T104215 channel ORA_DISK_1: restored backup piece 1 channel ORA_DISK_1: restore complete, elapsed time: 00:01:16 channel ORA_DISK_1: starting datafile backup set restore channel ORA_DISK_1: specifying datafile(s) to restore from backup set channel ORA_DISK_1: restoring datafile 00007 to /u02/app/oracle/oradata/cdb1/data_D-CDB1_TS-SYSAUX_FNO-7 channel ORA_DISK_1: reading from backup piece /u01/app/oracle/fast_recovery_area/CDB1/8F95C4062AA822B8E0536638A8C060CB/backupset/2019_08_14/o1_mf_nnndf_TAG20190814T104215_go763lp2_.bkp channel ORA_DISK_2: piece handle=/u01/app/oracle/fast_recovery_area/CDB1/backupset/2019_08_14/o1_mf_nnndf_TAG20190814T104215_go761hrr_.bkp tag=TAG20190814T104215 channel ORA_DISK_2: restored backup piece 1 channel ORA_DISK_2: restore complete, elapsed time: 00:01:20 channel ORA_DISK_2: starting datafile backup set restore channel ORA_DISK_2: specifying datafile(s) to restore from backup set channel ORA_DISK_2: restoring datafile 00009 to /u02/app/oracle/oradata/cdb1/data_D-CDB1_TS-SYSAUX_FNO-9 channel ORA_DISK_2: restoring datafile 00010 to /u02/app/oracle/oradata/cdb1/data_D-CDB1_TS-USERS_FNO-10 channel ORA_DISK_2: restoring datafile 00032 to /u02/app/oracle/oradata/cdb1/data_D-CDB1_TS-T1_FNO-32 channel ORA_DISK_2: restoring datafile 00034 to /u02/app/oracle/oradata/cdb1/data_D-CDB1_TS-T2_FNO-34 channel ORA_DISK_2: reading from backup piece /u01/app/oracle/fast_recovery_area/CDB1/8F95F488F27F26B1E0536638A8C085A3/backupset/2019_08_14/o1_mf_nnndf_TAG20190814T104215_go763ldl_.bkp channel ORA_DISK_1: piece handle=/u01/app/oracle/fast_recovery_area/CDB1/8F95C4062AA822B8E0536638A8C060CB/backupset/2019_08_14/o1_mf_nnndf_TAG20190814T104215_go763lp2_.bkp tag=TAG20190814T104215 channel ORA_DISK_1: restored backup piece 1 channel ORA_DISK_1: restore complete, elapsed time: 00:00:48 channel ORA_DISK_1: starting datafile backup set restore channel ORA_DISK_1: specifying datafile(s) to restore from backup set channel ORA_DISK_1: restoring datafile 00005 to /u02/app/oracle/oradata/cdb1/data_D-CDB1_TS-SYSTEM_FNO-5 channel ORA_DISK_1: reading from backup piece /u01/app/oracle/fast_recovery_area/CDB1/8F95C4062AA822B8E0536638A8C060CB/backupset/2019_08_14/o1_mf_nnndf_TAG20190814T104215_go765dqv_.bkp channel ORA_DISK_2: piece handle=/u01/app/oracle/fast_recovery_area/CDB1/8F95F488F27F26B1E0536638A8C085A3/backupset/2019_08_14/o1_mf_nnndf_TAG20190814T104215_go763ldl_.bkp tag=TAG20190814T104215 channel ORA_DISK_2: restored backup piece 1 channel ORA_DISK_2: restore complete, elapsed time: 00:01:00 channel ORA_DISK_2: starting datafile backup set restore channel ORA_DISK_2: specifying datafile(s) to restore from backup set channel ORA_DISK_2: restoring datafile 00008 to /u02/app/oracle/oradata/cdb1/data_D-CDB1_TS-SYSTEM_FNO-8 channel ORA_DISK_2: restoring datafile 00012 to /u02/app/oracle/oradata/cdb1/data_D-CDB1_TS-RCATALOG1_FNO-12 channel ORA_DISK_2: restoring datafile 00017 to /u02/app/oracle/oradata/cdb1/data_D-CDB1_TS-USERS_FNO-17 channel ORA_DISK_2: restoring datafile 00025 to /u02/app/oracle/oradata/cdb1/data_D-CDB1_TS-TEST_ENCRYPTION_BKP_FNO-25 channel ORA_DISK_2: restoring datafile 00035 to /u01/app/oracle/oradata/cdb1/pdb1/tc.dbf channel ORA_DISK_2: reading from backup piece /u01/app/oracle/fast_recovery_area/CDB1/8F95F488F27F26B1E0536638A8C085A3/backupset/2019_08_14/o1_mf_nnndf_TAG20190814T104215_go765d5k_.bkp channel ORA_DISK_2: ORA-19870: error while restoring backup piece /u01/app/oracle/fast_recovery_area/CDB1/8F95F488F27F26B1E0536638A8C085A3/backupset/2019_08_14/o1_mf_nnndf_TAG20190814T104215_go765d5k_.bkp ORA-19504: failed to create file "/u01/app/oracle/oradata/cdb1/pdb1/tc.dbf ORA-27040: file create error, unable to create file Linux-x86_64 Error: 2: No such file or directory Additional information: 1 channel ORA_DISK_1: piece handle=/u01/app/oracle/fast_recovery_area/CDB1/8F95C4062AA822B8E0536638A8C060CB/backupset/2019_08_14/o1_mf_nnndf_TAG20190814T104215_go765dqv_.bkp tag=TAG20190814T104215 channel ORA_DISK_1: restored backup piece 1 channel ORA_DISK_1: restore complete, elapsed time: 00:00:22 failover to previous backup channel ORA_DISK_1: starting datafile backup set restore channel ORA_DISK_1: specifying datafile(s) to restore from backup set channel ORA_DISK_1: restoring datafile 00008 to /u02/app/oracle/oradata/cdb1/data_D-CDB1_TS-SYSTEM_FNO-8 channel ORA_DISK_1: restoring datafile 00012 to /u02/app/oracle/oradata/cdb1/data_D-CDB1_TS-RCATALOG1_FNO-12 channel ORA_DISK_1: restoring datafile 00017 to /u02/app/oracle/oradata/cdb1/data_D-CDB1_TS-USERS_FNO-17 channel ORA_DISK_1: restoring datafile 00025 to /u02/app/oracle/oradata/cdb1/data_D-CDB1_TS-TEST_ENCRYPTION_BKP_FNO-25 channel ORA_DISK_1: reading from backup piece /u01/app/oracle/fast_recovery_area/CDB1/8F95F488F27F26B1E0536638A8C085A3/backupset/2019_08_14/o1_mf_nnndf_TAG20190814T095611_go73gylk_.bkp channel ORA_DISK_2: starting datafile backup set restore channel ORA_DISK_2: specifying datafile(s) to restore from backup set channel ORA_DISK_2: restoring datafile 00035 to /u01/app/oracle/oradata/cdb1/pdb1/tc.dbf channel ORA_DISK_2: reading from backup piece /u01/app/oracle/fast_recovery_area/CDB1/8F95F488F27F26B1E0536638A8C085A3/backupset/2019_08_14/o1_mf_nnndf_TAG20190814T103336_go75k8vm_.bkp channel ORA_DISK_2: ORA-19870: error while restoring backup piece /u01/app/oracle/fast_recovery_area/CDB1/8F95F488F27F26B1E0536638A8C085A3/backupset/2019_08_14/o1_mf_nnndf_TAG20190814T103336_go75k8vm_.bkp ORA-19504: failed to create file "/u01/app/oracle/oradata/cdb1/pdb1/tc.dbf ORA-27040: file create error, unable to create file Linux-x86_64 Error: 2: No such file or directory Additional information: 1 channel ORA_DISK_1: piece handle=/u01/app/oracle/fast_recovery_area/CDB1/8F95F488F27F26B1E0536638A8C085A3/backupset/2019_08_14/o1_mf_nnndf_TAG20190814T095611_go73gylk_.bkp tag=TAG20190814T095611 channel ORA_DISK_1: restored backup piece 1 channel ORA_DISK_1: restore complete, elapsed time: 00:00:15 failover to previous backup creating datafile file number=35 name=/u01/app/oracle/oradata/cdb1/pdb1/tc.dbf RMAN-00571: =========================================================== RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS =============== RMAN-00571: =========================================================== RMAN-03002: failure of restore command at 08/14/2019 10:57:31 ORA-01119: error in creating database file '/u01/app/oracle/oradata/cdb1/pdb1/tc.dbf' ORA-27040: file create error, unable to create file Linux-x86_64 Error: 2: No such file or directory Additional information: 1 RMAN-06956: create datafile failed; retry after removing /u01/app/oracle/oradata/cdb1/pdb1/tc.dbf from OS RMAN> quit Recovery Manager complete. -bash-4.1$ cd /u01/app/oracle/oradata/cdb1/ -bash-4.1$ mkdir pdb1 -bash-4.1$ ls control01.ctl pdb1 -bash-4.1$ rman target / Recovery Manager: Release 12.1.0.2.0 - Production on Wed Aug 14 10:58:20 2019 Copyright (c) 1982, 2014, Oracle and/or its affiliates. All rights reserved. connected to target database: CDB1 (DBID=989477122, not open) RMAN> restore database; Starting restore at Aug 14 2019 10:58:32 using target database control file instead of recovery catalog allocated channel: ORA_DISK_1 channel ORA_DISK_1: SID=12 device type=DISK allocated channel: ORA_DISK_2 channel ORA_DISK_2: SID=240 device type=DISK skipping datafile 1; already restored to file /u02/app/oracle/oradata/cdb1/data_D-CDB1_TS-SYSTEM_FNO-1 skipping datafile 4; already restored to file /u02/app/oracle/oradata/cdb1/data_D-CDB1_TS-UNDOTBS1_FNO-4 skipping datafile 29; already restored to file /u02/app/oracle/oradata/cdb1/data_D-CDB1_TS-T3_FNO-29 skipping datafile 30; already restored to file /u02/app/oracle/oradata/cdb1/data_D-CDB1_TS-T4_FNO-30 skipping datafile 3; already restored to file /u02/app/oracle/oradata/cdb1/data_D-CDB1_TS-SYSAUX_FNO-3 skipping datafile 26; already restored to file /u02/app/oracle/oradata/cdb1/data_D-CDB1_TS-MYTBS01_FNO-26 skipping datafile 28; already restored to file /u02/app/oracle/oradata/cdb1/data_D-CDB1_TS-T2_FNO-28 skipping datafile 31; already restored to file /u02/app/oracle/oradata/cdb1/data_D-CDB1_TS-T5_FNO-31 skipping datafile 33; already restored to file /u02/app/oracle/oradata/cdb1/data_D-CDB1_TS-USER2_FNO-33 skipping datafile 5; already restored to file /u02/app/oracle/oradata/cdb1/data_D-CDB1_TS-SYSTEM_FNO-5 skipping datafile 7; already restored to file /u02/app/oracle/oradata/cdb1/data_D-CDB1_TS-SYSAUX_FNO-7 skipping datafile 9; already restored to file /u02/app/oracle/oradata/cdb1/data_D-CDB1_TS-SYSAUX_FNO-9 skipping datafile 10; already restored to file /u02/app/oracle/oradata/cdb1/data_D-CDB1_TS-USERS_FNO-10 skipping datafile 32; already restored to file /u02/app/oracle/oradata/cdb1/data_D-CDB1_TS-T1_FNO-32 skipping datafile 34; already restored to file /u02/app/oracle/oradata/cdb1/data_D-CDB1_TS-T2_FNO-34 channel ORA_DISK_1: starting datafile backup set restore channel ORA_DISK_1: specifying datafile(s) to restore from backup set channel ORA_DISK_1: restoring datafile 00008 to /u02/app/oracle/oradata/cdb1/data_D-CDB1_TS-SYSTEM_FNO-8 channel ORA_DISK_1: restoring datafile 00012 to /u02/app/oracle/oradata/cdb1/data_D-CDB1_TS-RCATALOG1_FNO-12 channel ORA_DISK_1: restoring datafile 00017 to /u02/app/oracle/oradata/cdb1/data_D-CDB1_TS-USERS_FNO-17 channel ORA_DISK_1: restoring datafile 00025 to /u02/app/oracle/oradata/cdb1/data_D-CDB1_TS-TEST_ENCRYPTION_BKP_FNO-25 channel ORA_DISK_1: restoring datafile 00035 to /u01/app/oracle/oradata/cdb1/pdb1/tc.dbf channel ORA_DISK_1: reading from backup piece /u01/app/oracle/fast_recovery_area/CDB1/8F95F488F27F26B1E0536638A8C085A3/backupset/2019_08_14/o1_mf_nnndf_TAG20190814T104215_go765d5k_.bkp channel ORA_DISK_1: piece handle=/u01/app/oracle/fast_recovery_area/CDB1/8F95F488F27F26B1E0536638A8C085A3/backupset/2019_08_14/o1_mf_nnndf_TAG20190814T104215_go765d5k_.bkp tag=TAG20190814T104215 channel ORA_DISK_1: restored backup piece 1 channel ORA_DISK_1: restore complete, elapsed time: 00:00:15 Finished restore at Aug 14 2019 10:58:49 RMAN> recover database; Starting recover at Aug 14 2019 10:58:58 using channel ORA_DISK_1 using channel ORA_DISK_2 starting media recovery channel ORA_DISK_1: starting archived log restore to default destination channel ORA_DISK_1: restoring archived log archived log thread=1 sequence=32 channel ORA_DISK_1: reading from backup piece /u01/app/oracle/backup2/full_backup_cdb1_2hu968h8_1_1.bkp channel ORA_DISK_1: piece handle=/u01/app/oracle/backup2/full_backup_cdb1_2hu968h8_1_1.bkp tag=TAG20190814T104455 channel ORA_DISK_1: restored backup piece 1 channel ORA_DISK_1: restore complete, elapsed time: 00:00:01 archived log file name=/u01/app/oracle/fast_recovery_area/CDB1/archivelog/2019_08_14/o1_mf_1_32_go770wlr_.arc thread=1 sequence=32 channel default: deleting archived log(s) archived log file name=/u01/app/oracle/fast_recovery_area/CDB1/archivelog/2019_08_14/o1_mf_1_32_go770wlr_.arc RECID=49 STAMP=1016276340 unable to find archived log archived log thread=1 sequence=33 RMAN-00571: =========================================================== RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS =============== RMAN-00571: =========================================================== RMAN-03002: failure of recover command at 08/14/2019 10:59:02 RMAN-06054: media recovery requesting unknown archived log for thread 1 with sequence 33 and starting SCN of 3471829 RMAN> restore archivelog from sequence 31; Starting restore at Aug 14 2019 11:07:13 using channel ORA_DISK_1 using channel ORA_DISK_2 channel ORA_DISK_1: starting archived log restore to default destination channel ORA_DISK_1: restoring archived log archived log thread=1 sequence=31 channel ORA_DISK_1: reading from backup piece /u01/app/oracle/backup2/full_backup_cdb1_29u968c5_1_1.bkp channel ORA_DISK_2: starting archived log restore to default destination channel ORA_DISK_2: restoring archived log archived log thread=1 sequence=32 channel ORA_DISK_2: reading from backup piece /u01/app/oracle/backup2/full_backup_cdb1_2hu968h8_1_1.bkp channel ORA_DISK_1: piece handle=/u01/app/oracle/backup2/full_backup_cdb1_29u968c5_1_1.bkp tag=TAG20190814T104212 channel ORA_DISK_1: restored backup piece 1 channel ORA_DISK_1: restore complete, elapsed time: 00:00:01 channel ORA_DISK_2: piece handle=/u01/app/oracle/backup2/full_backup_cdb1_2hu968h8_1_1.bkp tag=TAG20190814T104455 channel ORA_DISK_2: restored backup piece 1 channel ORA_DISK_2: restore complete, elapsed time: 00:00:01 Finished restore at Aug 14 2019 11:07:14 RMAN> recover database; Starting recover at Aug 14 2019 11:09:21 using channel ORA_DISK_1 using channel ORA_DISK_2 starting media recovery unable to find archived log archived log thread=1 sequence=33 RMAN-00571: =========================================================== RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS =============== RMAN-00571: =========================================================== RMAN-03002: failure of recover command at 08/14/2019 11:09:22 RMAN-06054: media recovery requesting unknown archived log for thread 1 with sequence 33 and starting SCN of 3471829 RMAN> recover database until scn 3471829; Starting recover at Aug 14 2019 11:09:52 using channel ORA_DISK_1 using channel ORA_DISK_2 starting media recovery media recovery complete, elapsed time: 00:00:01 Finished recover at Aug 14 2019 11:09:53 RMAN> alter database open; RMAN-00571: =========================================================== RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS =============== RMAN-00571: =========================================================== RMAN-03002: failure of sql statement command at 08/14/2019 11:10:09 ORA-01589: must use RESETLOGS or NORESETLOGS option for database open RMAN> alter database open resetlogs; Statement processed RMAN> quit Recovery Manager complete. -bash-4.1$ sql SQL> select open_mode from v$database; OPEN_MODE -------------------- READ WRITE SQL> set pagesize 10000 SQL> select name from v$datafile 2 union all 3 select member from v$logfile 4 union all 5 select name from v$controlfile; NAME -------------------------------------------------------------------------------- /u02/app/oracle/oradata/cdb1/data_D-CDB1_TS-SYSTEM_FNO-1 /u02/app/oracle/oradata/cdb1/data_D-CDB1_TS-SYSAUX_FNO-3 /u02/app/oracle/oradata/cdb1/data_D-CDB1_TS-UNDOTBS1_FNO-4 /u02/app/oracle/oradata/cdb1/data_D-CDB1_TS-SYSTEM_FNO-5 /u02/app/oracle/oradata/cdb1/data_D-CDB1_TS-SYSAUX_FNO-7 /u02/app/oracle/oradata/cdb1/data_D-CDB1_TS-SYSTEM_FNO-8 /u02/app/oracle/oradata/cdb1/data_D-CDB1_TS-SYSAUX_FNO-9 /u02/app/oracle/oradata/cdb1/data_D-CDB1_TS-USERS_FNO-10 /u02/app/oracle/oradata/cdb1/data_D-CDB1_TS-RCATALOG1_FNO-12 /u02/app/oracle/oradata/cdb1/data_D-CDB1_TS-USERS_FNO-17 /u02/app/oracle/oradata/cdb1/data_D-CDB1_TS-TEST_ENCRYPTION_BKP_FNO-25 /u02/app/oracle/oradata/cdb1/data_D-CDB1_TS-MYTBS01_FNO-26 /u02/app/oracle/oradata/cdb1/data_D-CDB1_TS-T2_FNO-28 /u02/app/oracle/oradata/cdb1/data_D-CDB1_TS-T3_FNO-29 /u02/app/oracle/oradata/cdb1/data_D-CDB1_TS-T4_FNO-30 /u02/app/oracle/oradata/cdb1/data_D-CDB1_TS-T5_FNO-31 /u02/app/oracle/oradata/cdb1/data_D-CDB1_TS-T1_FNO-32 /u02/app/oracle/oradata/cdb1/data_D-CDB1_TS-USER2_FNO-33 /u02/app/oracle/oradata/cdb1/data_D-CDB1_TS-T2_FNO-34 /u01/app/oracle/oradata/cdb1/pdb1/tc.dbf /u01/app/oracle/oradata/cdb1/redo01.log /u01/app/oracle/oradata/cdb1/redo03.log /u01/app/oracle/oradata/cdb1/redo02.log /u01/app/oracle/oradata/cdb1/control01.ctl /u01/app/oracle/fast_recovery_area/cdb1/control02.ctl 25 rows selected. SQL> select current_scn from v$database; CURRENT_SCN ----------- 3472851 SQL> quit
Perform incomplete recovery (Time,SCN and Change based)
RMAN> backup database plus archivelog delete input format '/u01/app/oracle/backup2/my_full_bkp_%U.bkp'; SQL> alter session set container=pdb1; Session altered. SQL> create tablespace tbs_pitr datafile '/u01/app/oracle/oradata/cdb1/pdb1/tbs_pitr.dbf' size 10M; Tablespace created. SQL> create user pitr identified by pitr default tablespace tbs_pitr quota unlimited on tbs_pitr temporary tablespace temp; User created. SQL> grant connect,resource to pitr; Grant succeeded. SQL> connect pitr/pitr@//racnode2/pdb1 Connected. SQL> create table mytable as select * from all_objects where rownum < 100; Table created. SQL> select count(*) from mytable; COUNT(*) ---------- 99 SQL> connect sys/oracle_4U@//racnode2/pdb1 as sysdba Connected. SQL> select current_scn from V$database; CURRENT_SCN ----------- 3574483 SQL> select scn_to_timestamp(3574483) from dual; SCN_TO_TIMESTAMP(3574483) --------------------------------------------------------------------------- 14-AUG-19 11.26.31.000000000 AM SQL> set timing on SQL> set time on 11:27:04 SQL> truncate table pitr.mytable; Table truncated. Elapsed: 00:00:00.40 11:27:19 SQL> select count(*) from pitr.mytable; COUNT(*) ---------- 0 Elapsed: 00:00:00.00 11:27:27 SQL> 11:27:28 SQL> shut immediate Pluggable Database closed. 11:27:32 SQL> connect / as sysdba Connected. 11:27:38 SQL> shut immediate Database closed. Database dismounted. ORACLE instance shut down. 11:28:00 SQL> 11:28:01 SQL> startup mount; ORACLE instance started. Total System Global Area 1073741824 bytes Fixed Size 2932632 bytes Variable Size 666894440 bytes Database Buffers 398458880 bytes Redo Buffers 5455872 bytes Database mounted. 11:28:19 SQL> quit -bash-4.1$ rman target / Recovery Manager: Release 12.1.0.2.0 - Production on Wed Aug 14 11:28:24 2019 Copyright (c) 1982, 2014, Oracle and/or its affiliates. All rights reserved. connected to target database: CDB1 (DBID=989477122, not open) RMAN> run 2> { 3> set until scn 3574483; 4> restore database; 5> recover database; 6> } RMAN> alter database open; RMAN-00571: =========================================================== RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS =============== RMAN-00571: =========================================================== RMAN-03002: failure of sql statement command at 08/14/2019 11:32:26 ORA-01589: must use RESETLOGS or NORESETLOGS option for database open RMAN> alter database open resetlogs; Statement processed RMAN> quit Recovery Manager complete. -bash-4.1$ sqlplus sys/oracle_4U@//racnode2/pdb1 as sysdba SQL> select count(*) from pitr.mytable; COUNT(*) ---------- 99 SQL> quit
Recover database to the previous incarnation.
SELECT incarnation#,resetlogs_change#,status,con_id from v$database_incarnation; -bash-4.1$ sqlplus sys/oracle_4U@//racnode2/pdb1 as sysdba select count(*) from pitr.mytable; truncate table pitr.mytable; shutdown immediate startup mount RMAN> run 2> { 3> set until scn 3574483; 4> restore database; 5> recover database; 6> } RMAN-20208: UNTIL CHANGE is before RESETLOGS change -bash-4.1$ oerr RMAN 20208 20208, 1, UNTIL CHANGE is before RESETLOGS change // *Cause: UNTIL CHANGE cannot be less than the database RESETLOGS change. // *Action: Check the UNTIL CHANGE. If the database needs to be restored to // an old incarnation, use the RESET DATABASE TO INCARNATION command. // RMAN> RESET DATABASE TO INCARNATION 3; RMAN> RMAN> run 2> { 3> set until scn 3574483; 4> restore database; 5> recover database; 6> } ALTER DATABASE OPEN READ ONLY; ALTER PLUGGABLE DATABASE PDB1 OPEN READ ONLY; select count(*) from pitr.mytable;
Performing tablespace point in time recovery (TSPITR)
SQL> create tablespace my_tbs_pitr datafile '/u01/app/oracle/oradata/cdb1/pdb1/mypitr.dbf' size 10M autoextend on; Tablespace created. SQL> create user mypitr identified by mypitr default tablespace my_tbs_pitr quota unlimited on my_tbs_pitr temporary tablespace temp; User created. SQL> grant connect,resource,dba to mypitr; Grant succeeded. SQL> connect mypitr/mypitr@//racnode2/pdb1 Connected. SQL> SQL> create table mytable as select * from dba_objects; Table created. SQL> select count(*) from mytable; COUNT(*) ---------- 91295 SQL> quit Disconnected from Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options -bash-4.1$ rman target / Recovery Manager: Release 12.1.0.2.0 - Production on Wed Aug 14 12:16:01 2019 Copyright (c) 1982, 2014, Oracle and/or its affiliates. All rights reserved. connected to target database: CDB1 (DBID=989477122) RMAN> backup database plus archivelog delete all input; $sql SQL> ALTER SESSION SET CONTAINER=PDB1; Session altered. SQL> SET SERVEROUTPUT ON SQL> BEGIN 2 DBMS_TTS.TRANSPORT_SET_CHECK('MY_TBS_PITR',TRUE,TRUE); 3 END; 4 / PL/SQL procedure successfully completed. SQL> SELECT * FROM TRANSPORT_SET_VIOLATIONS; no rows selected SQL> SELECT TABLESPACE_NAME,OWNER,NAME FROM TS_PITR_OBJECTS_TO_BE_DROPPED 2 WHERE CREATION_TIME > TO_DATE('14-AUG-2019','DD-MON-RRRR') 3 AND TABLESPACE_NAME='MY_TBS_PITR'; TABLESPACE_NAME ------------------------------ OWNER -------------------------------------------------------------------------------- NAME -------------------------------------------------------------------------------- MY_TBS_PITR MYPITR MYTABLE SQL> SELECT CURRENT_SCN FROM V$DATABASE; CURRENT_SCN ----------- 3677123 SQL> SQL> TRUNCATE TABLE mypitr.mytable; Table truncated. SQL> select count(*) from mytable; select count(*) from mytable * ERROR at line 1: ORA-00942: table or view does not exist SQL> select count(*) from mypitr.mytable; COUNT(*) ---------- 0 SQL> quit -bash-4.1$ pwd /home/oracle/aux -bash-4.1$ rman target / Recovery Manager: Release 12.1.0.2.0 - Production on Wed Aug 14 12:24:18 2019 Copyright (c) 1982, 2014, Oracle and/or its affiliates. All rights reserved. connected to target database: CDB1 (DBID=989477122) RMAN> report schema; Report of database schema for database with db_unique_name CDB1 List of Permanent Datafiles =========================== File Size(MB) Tablespace RB segs Datafile Name ---- -------- -------------------- ------- ------------------------ 1 810 SYSTEM YES /u02/app/oracle/oradata/cdb1/data_D-CDB1_TS-SYSTEM_FNO-1 3 710 SYSAUX NO /u02/app/oracle/oradata/cdb1/data_D-CDB1_TS-SYSAUX_FNO-3 4 220 UNDOTBS1 YES /u02/app/oracle/oradata/cdb1/data_D-CDB1_TS-UNDOTBS1_FNO-4 5 250 PDB$SEED:SYSTEM NO /u02/app/oracle/oradata/cdb1/data_D-CDB1_TS-SYSTEM_FNO-5 7 550 PDB$SEED:SYSAUX NO /u02/app/oracle/oradata/cdb1/data_D-CDB1_TS-SYSAUX_FNO-7 8 270 PDB1:SYSTEM NO /u02/app/oracle/oradata/cdb1/data_D-CDB1_TS-SYSTEM_FNO-8 9 590 PDB1:SYSAUX NO /u02/app/oracle/oradata/cdb1/data_D-CDB1_TS-SYSAUX_FNO-9 10 5 PDB1:USERS NO /u02/app/oracle/oradata/cdb1/data_D-CDB1_TS-USERS_FNO-10 12 10 PDB1:RCATALOG1 NO /u02/app/oracle/oradata/cdb1/data_D-CDB1_TS-RCATALOG1_FNO-12 17 10 PDB1:USERS NO /u02/app/oracle/oradata/cdb1/data_D-CDB1_TS-USERS_FNO-17 25 15 PDB1:TEST_ENCRYPTION_BKP NO /u02/app/oracle/oradata/cdb1/data_D-CDB1_TS-TEST_ENCRYPTION_BKP_FNO-25 26 10 MYTBS01 NO /u02/app/oracle/oradata/cdb1/data_D-CDB1_TS-MYTBS01_FNO-26 28 1 T2 NO /u02/app/oracle/oradata/cdb1/data_D-CDB1_TS-T2_FNO-28 29 1 T3 NO /u02/app/oracle/oradata/cdb1/data_D-CDB1_TS-T3_FNO-29 30 1 T4 NO /u02/app/oracle/oradata/cdb1/data_D-CDB1_TS-T4_FNO-30 31 5 T5 NO /u02/app/oracle/oradata/cdb1/data_D-CDB1_TS-T5_FNO-31 32 5 PDB1:T1 NO /u02/app/oracle/oradata/cdb1/data_D-CDB1_TS-T1_FNO-32 33 10 USER2 NO /u02/app/oracle/oradata/cdb1/data_D-CDB1_TS-USER2_FNO-33 34 5 PDB1:T2 NO /u02/app/oracle/oradata/cdb1/data_D-CDB1_TS-T2_FNO-34 35 9 PDB1:TC NO /u01/app/oracle/oradata/cdb1/pdb1/tc.dbf 36 10 PDB1:TBS_PITR NO /u01/app/oracle/oradata/cdb1/pdb1/tbs_pitr.dbf 37 14 PDB1:MY_TBS_PITR NO /u01/app/oracle/oradata/cdb1/pdb1/mypitr.dbf RMAN> recover tablespace PDB1:MY_TBS_PITR until scn 3677123 auxiliary destination '/home/oracle/aux'; recover tablespace PDB1:TEST UNTIL SCN 2958620 auxiliary destination '/refresh/home/aux'; Removing automatic instance shutting down automatic instance Oracle instance shut down Automatic instance removed auxiliary instance file /home/oracle/aux/CDB1/onlinelog/o1_mf_3_go7fn50g_.log deleted auxiliary instance file /home/oracle/aux/CDB1/onlinelog/o1_mf_2_go7fn3b8_.log deleted auxiliary instance file /home/oracle/aux/CDB1/onlinelog/o1_mf_1_go7fn1wd_.log deleted auxiliary instance file /home/oracle/aux/CDB1/datafile/o1_mf_sysaux_go7flhd1_.dbf deleted auxiliary instance file /home/oracle/aux/CDB1/datafile/o1_mf_system_go7fljvy_.dbf deleted auxiliary instance file /home/oracle/aux/CDB1/datafile/o1_mf_sysaux_go7fhyj7_.dbf deleted auxiliary instance file /home/oracle/aux/CDB1/datafile/o1_mf_undotbs1_go7fhylx_.dbf deleted auxiliary instance file /home/oracle/aux/CDB1/datafile/o1_mf_system_go7fhyho_.dbf deleted auxiliary instance file /home/oracle/aux/CDB1/controlfile/o1_mf_go7fhr1h_.ctl deleted auxiliary instance file tspitr_Ckua_42706.dmp deleted RMAN-00571: =========================================================== RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS =============== RMAN-00571: =========================================================== RMAN-03002: failure of recover command at 08/14/2019 12:52:34 RMAN-06962: Error received during export of metadata RMAN-06960: EXPDP> ORA-39006: internal error ORA-39065: unexpected master process exception in DISPATCH ORA-25153: Temporary Tablespace is Empty ORA-39097: Data Pump job encountered unexpected error -25153 -bash-4.1$ rman target / Recovery Manager: Release 12.1.0.2.0 - Production on Wed Aug 14 16:40:45 2019 Copyright (c) 1982, 2014, Oracle and/or its affiliates. All rights reserved. connected to target database: CDB1 (DBID=989477122) RMAN> sql 'PDB1' 'alter tablespace MY_TBS_PITR online'; using target database control file instead of recovery catalog sql statement: alter tablespace MY_TBS_PITR online RMAN> quit Recovery Manager complete. -bash-4.1$ sqlplus sys/oracle_4U@//racnode2/pdb1 as sysdba SQL*Plus: Release 12.1.0.2.0 Production on Wed Aug 14 16:41:59 2019 Copyright (c) 1982, 2014, Oracle. All rights reserved. Connected to: Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options SQL> select count(*) from mypitr.mytable; COUNT(*) ---------- 91296
The main purpose of RMAN validation is to check for corrupt blocks and missing files. You can also use RMAN to determine whether backups can be restored. You can use the following RMAN commands to perform validation: VALIDATE BACKUP ... VALIDATE RESTORE ... VALIDATE [1] Checking for Block Corruption with the VALIDATE Command #How to validate all data files and control files (and the server parameter file if one is in use) RMAN> VALIDATE DATABASE; #How you can validate a particular backup set RMAN> VALIDATE BACKUPSET 22; #How you can check individual data blocks within a data file for corruption. RMAN> VALIDATE DATAFILE 1 BLOCK 10; #To make parallel the validation of a data file: RUN { ALLOCATE CHANNEL c1 DEVICE TYPE DISK; ALLOCATE CHANNEL c2 DEVICE TYPE DISK; VALIDATE DATAFILE 1 SECTION SIZE 1200M; } [2] Validating Database Files with BACKUP VALIDATE #To validate files with the BACKUP VALIDATE command, This command checks for physical corruptions only. BACKUP VALIDATE DATABASE ARCHIVELOG ALL; #To check for logical corruptions in addition to physical corruptions BACKUP VALIDATE CHECK LOGICAL DATABASE ARCHIVELOG ALL; [3] Validating Backups Before Restoring Them #How to validating the restore of the database and all archived redo logs: RESTORE DATABASE VALIDATE; RESTORE ARCHIVELOG ALL VALIDATE; [4] Validating CDBs and PDBs #Validating a Whole CDB VALIDATE DATABASE; VALIDATE DATABASE ROOT; #Validating PDBs VALIDATE PLUGGABLE DATABASE hr_pdb, sales_pdb; RESTORE DATABASE VALIDATE;
===================== Path to Documentation: ===================== Oracle Database Backup and Recovery User's Guide -> 9 Backing Up the Database -> 10 Backing Up the Database: Advanced Topics -> 16 Validating Database Files and Backups Oracle Database Backup and Recovery User's Guide -> Performing RMAN Recovery: Advanced Scenarios Oracle Data Guard Concepts and Administration -> 11 Using RMAN to Back Up and Restore Files Reference Database Administration -> Database Backup and Recovery Guide -> 24 Troubleshooting RMAN Operations Database Backup and Recovery User's Guide 17 Performing Complete Database Recovery 19 Performing Block Media Recovery 20 Performing RMAN Recovery: Advanced Scenarios 21 Performing RMAN Tablespace Point-in-Time Recovery (TSPITR)
Thank you for visiting this blog…