Oracle database instance recovery is taking too much time after shutdown immediate

1.We were going to open DB after shutdown immediate but it went hang.In alert log , we could only notice following message for 40 minutes.

 

Completed: ALTER DATABASE MOUNT
Tue Oct 31 01:08:39 2017
ALTER DATABASE OPEN
Beginning crash recovery of 1 threadsparallel recovery started with 9 processes
Started redo scanTue Oct 31 01:08:58 2017
Completed redo scan
read 3733362 KB redo, 2824480 data blocks need recovery
Started redo application at
Thread 1: logseq 39596, block 3434354
Recovery of Online Redo Log: Thread 1 Group 10 Seq 39596 Reading mem 0
Mem# 0: /oracle/data/san_oradata01/eocmdb/redofiles/log10b.rdo
Recovery of Online Redo Log: Thread 1 Group 11 Seq 39597 Reading mem 0
Mem# 0: /oracle/data/san_oradata01/eocmdb/redofiles/log11b.rdo
Tue Oct 31 01:09:18 2017
Recovery of Online Redo Log: Thread 1 Group 13 Seq 39598 Reading mem 0
Mem# 0: /oracle/data/san_oradata01/eocmdb/redofiles/log13b.log
Tue Oct 31 01:09:28 2017
Completed redo application of 628.90MB

 

2.Troubleshooting using following queries:-

export NLS_DATE_FORMAT=’dd-mon-yyyy hh24:mi:ss’

set feedback on

set heading on

set echo on

set pagesize 100

set linesize 150

col checkpoint_change# format 999999999999999999999

set echo on

set linesize 400

set pagesize 20000

col name format a75

col MEMBER format a100

col file_name format a100

col PLATFORM_NAME format a30

col TABLESPACE_NAME format a30

set numformat 999999999999999

col hxfil format 9999

col fhsta format 9999

col fhscn format 999999999999999999999999

col fhtmn format a30

col fhrba_Seq format 9999999999999

col FHBCP_THR format 9999

col fhthr format 999

col fhrba_seq format 99999999

col fhscn format 999999999999999999

set linesize 175

set pagesize 50

alter session set nls_date_format = ‘DD-MON-RRRR HH24:MI:SS’;

Spool recover.lst

select name,platform_name,open_mode,controlfile_type,log_mode,flashback_on,RESETLOGS_TIME,RESETLOGS_CHANGE# from v$database;

select name, status from v$datafile;

select name, recover, fuzzy, checkpoint_change#,RESETLOGS_TIME,RESETLOGS_CHANGE# from v$datafile_header;

select * from v$backup;

select name, open_mode, checkpoint_change#, ARCHIVE_CHANGE# from v$database;

select GROUP#,THREAD#,SEQUENCE#,MEMBERS,ARCHIVED,STATUS,FIRST_CHANGE# from v$log;

select GROUP#,member from v$logfile;

select * from v$recover_file;

select * from v$recovery_log;

select hxfil FILE#, fhsta STATUS, fhscn SCN, fhrba_Seq SEQUENCE, fhtnm TABLESPACE, FHBCP_THR Thread, fhafs AFUZZY from x$kcvfh order by hxfil;

— Min PITR ABSSCN is minimun SCN needed to open

select min(FHSCN) “LOW FILEHDR SCN”, max(FHSCN) “MAX FILEHDR SCN”, max(FHAFS) “Min PITR ABSSCN” from X$KCVFH;

— Min max SEQUENCE on file headers

SELECT fhthr thread, fhrba_seq sequence, fhscn scn, fhsta status, count(*) FROM x$kcvfh group by fhthr,fhrba_seq,fhscn,fhsta;

select min(FHRBA_SEQ) “LOW FILEHDR SEQUENCE”, max(FHRBA_SEQ) “MAX FILEHDR SEQUENCE” from X$KCVFH;

select HXFIL File_num,substr(HXFNM,1,140) File_name,FHTYP Type,HXERR Validity, FHSCN SCN, FHTNM TABLESPACE_NAME,FHSTA status ,FHRBA_SEQ Sequence, FHTHR Thread from X$KCVFH;

SELECT fhthr thread, fhrba_seq sequence, fhscn scn, fhsta status, count(*) FROM x$kcvfh group by fhthr,fhrba_seq,fhscn,fhsta;

 

3. Please execute following command to check recovery.

RMAN>backup validate check logical database;

SQL>alter system dump logfile ‘/oracle/data/san_oradata01/eocmdb/redofiles/log10b.rdo’ validate;

RMAN>recover database test;

 

4.Re-creating control file and run recover from RMAN

connect target /;
alter session set tracefile_identifier = “10046_trace_file”;
alter session set events ‘10046 trace name context forever, level 3’;
set echo on;
debug on;
run {
allocate channel d1 type disk;
recover database;
}
debug off;
exit

But this was stuck with following:-

RMAN> recover database;

Starting recover at 31-OCT-17
using channel ORA_DISK_1

starting media recovery

unable to find archived log
archived log thread=1 sequence=39598
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of recover command at 10/31/2017 06:29:09
RMAN-06054: media recovery requesting unknown archived log for thread 1 with sequence 39598 and starting SCN of 12444621699598

 

5.Now we need to validate archive log 39597 for consistency.

RMAN> validate archivelog sequence 39597 thread 1;

6.We need to do following if recover stuck

a)Open one more session and shutdown abort the instance

b)Startup mount

c)Try with no parallel recovery

Now, you are applying the redolog named /oracle/data/san_oradata01/eocmdb/redofiles/log13b.log

with sequence 39598

Which is the current redo log:

SQL> select GROUP#,THREAD#,SEQUENCE#,MEMBERS,ARCHIVED,STATUS,FIRST_CHANGE# from v$log;

GROUP# THREAD# SEQUENCE# MEMBERS ARC STATUS FIRST_CHANGE#
—————- —————- —————- —————- — —————- —————-
8 1 39593 1 YES INACTIVE 12444097046518
9 1 39595 1 YES INACTIVE 12444097257724
10 1 39596 1 YES ACTIVE 12444097354077
15 1 39592 1 YES INACTIVE 12444096949452
12 1 39594 1 YES INACTIVE 12444097140637
13 1 39598 1 NO CURRENT 12444621699598 >>>>>>>>>>>>>>>>>>
14 1 39591 1 YES INACTIVE 12444095658402
11 1 39597 1 YES ACTIVE 12444621392892

SQL> recover database noparallel using backup controlfile;

If it ask for log sequence 39598, then provide the full path as below and then press <Enter>
/oracle/data/san_oradata01/eocmdb/redofiles/log13b.log  (This is current redolog file)

If it says… “Media Recovery complete” then open the db with resetlogs:

SQL> alter database open resetlogs;

Note:-Following views are very useful to monitor instance recovery progress.

Reference:-Monitoring Restore/Recovery Progress (Doc ID 1335910.1)

 Session Waits:-

Are there any sessions in wait and what is it waiting for?

Run the following queries at least 3 times, at 5 minute intervals to see progress/change.

set linesize 200 trimspool oncol event form a25
col p1text form a15
col p1 form 999999
col p2text form a15
col p2 form 999999
col p3text form a10
col p3 form 9999
col waited form 9999
col waiting form 9999select sid, event, p1text, p1, p2text, p2, p3text, p3,
wait_time waited, seconds_in_wait waiting
from gv$session_wait
where event not like ‘SQL*Net%’
and event not like ‘%timer%’
and event not like ‘rdbms%’
and event not like ‘pipe%’
and event not like ‘DIAG%’
and event not like ‘Streams AQ%’
and event not like ‘VKTM%’
and state = ‘WAITING’
order by seconds_in_wait
/

Recovery Progress:-

What is the recovery progress?  V$RECOVERY_PROGRESS is only populated when RECOVERY is in progress. A restore operation will not populate this view. So if you think a recovery process is slow – is it really at the recovery phase, or still restoring from RMAN backuppieces?

This is an example of a recovery progress:

 

START_TIME ITEM UNITS SOFAR TOTAL TIMESTAMP
——————– ——————————– ——————————– ———- ———- ——————–
31-OCT-2017 15:52:22 Active Apply Rate KB/sec 164 0
31-OCT-2017 15:52:22 Average Apply Rate KB/sec 167 0
31-OCT-2017 15:52:22 Maximum Apply Rate KB/sec 5460 0
31-OCT-2017 15:52:22 Redo Applied Megabytes 330 0
31-OCT-2017 15:52:22 Last Applied Redo SCN+Time 0 0 30-OCT-2017 13:45:57
31-OCT-2017 15:52:22 Active Time Seconds 1963 0
31-OCT-2017 15:52:22 Elapsed Time Seconds 2018 0
31-OCT-2017 15:14:17 Log Files Files 1 1
31-OCT-2017 15:14:17 Active Apply Rate KB/sec 40 40
31-OCT-2017 15:14:17 Average Apply Rate KB/sec 45 45
31-OCT-2017 15:14:17 Maximum Apply Rate KB/sec 341 341START_TIME ITEM UNITS SOFAR TOTAL TIMESTAMP
——————– ——————————– ——————————– ———- ———- ——————–
31-OCT-2017 15:14:17 Redo Applied Megabytes 89 89
31-OCT-2017 15:14:17 Last Applied Redo SCN+Time 0 0 30-OCT-2017 13:39:19
31-OCT-2017 15:14:17 Active Time Seconds 1903 1903
31-OCT-2017 15:14:17 Apply Time per Log Seconds 1903 1903
31-OCT-2017 15:14:17 Elapsed Time Seconds 2005 2005

 

6.Root Cause of slowness:-

  • Hang analyzer log says:-

is waiting for ‘parallel recovery slave idle wait’ with wait info:
{
p1: ”=0x1001ffff
p2: ”=0x1
p3: ”=0x182f945d00
time in wait: 1.004650 sec
timeout after: never
wait id: 57508
blocking: 0 sessions
current sql: <none>

  • IO stat report:-

The all local disk (not in SAN) was showing await.

AWR report :-

it looks like local disk issue not SAN issue. If you look into Av rd(ms) ,it is 1 -2 ms for SAN and for local it is 20 ms to 60 ms. Oracle suggestion is it should be less than 10 ms. So all datafiles should immediately be moved to SAN from Local mount point (Local does not have good striping capacity compare to SAN which will raise bottleneck) .

Schedule Start & Stop RDS Instances and Save Money

Introduction

If RDS Instances is AWS keep running without 24×7 usage, it can cost you very high in AWS billing.

Take following example how idle RDS instances can cost you:-

Lets take a medium sized RDS instance db.m4.xlarge (4CPU and 16GB RAM) in Sydney region for MySQL engine costs $0.492 per hour.

Now if we use the calculator, usage running 24×7, costs on average $360.15 per month.

Now if we only had that running business hours (9 hrs a day MON-FRI) , talking on average $92.99.

Thats a cost saving of $267.16. That is just for one instance per month/ OR $3,205.92 per annum.

What if you were working in a large enterprise with 10s or 100s of RDS instances.

Hence it is very important to stop the RDS instance when Idle to save significant cost in AWS Billing.

Let’s use aws-sdk, nodeJs Lambda function and CloudWatch to start/stop RDS instances in schedule interval.

Following are step by step guide with screenshot.

Download

Download the source code in zip format from following link and keep the zip file in local machine for further upload in Lambda function.

https://s3.ap-south-1.amazonaws.com/rdsstopstartcode/rds-lambda-stop-start.zip

Step 1: Create IAM Policy RDSManagement

First, let’s create policy, which is in AWS IAM console.

Go to Services -> Policies -> Create Policy -> Create Your Own Policy.
Let’s call it “RDSManagement”. Put the code given below to the ‘Policy Document’ field:

{
    "Version": "2012-10-17",
    "Statement": [        
        {
            "Action": [
                "rds:StopDBInstance",
                "rds:StartDBInstance"
            ],
            "Effect": "Allow",
            "Resource": "*"
        }
    ]
}

Press ‘Create policy’, Create Your Own Policy put name, description and JSON to the appropriate fields and press save. Eventually, you have to get something like on the screenshot below:

Screen1:-

1

Screen2:-

2

Screen3:-

3

Screen4:-

4

Step 2: Create Role LambdaRDSManagement

The second step is to create a role which will be associated with lambda function and allow it to manage RDS instances.

Navigate to Services -> Roles -> Create new Role.

Select ‘AWS Lambda’ in ‘AWS Service Role’ section. Search for the policy we created previously, select it, and press ‘Next’.

Screen5:-

5.png

Put “LambdaRDSManagement” as a Role Name, set some description and press ‘Create Role’.

Screen6:-

6.png

Screen7:-

7.png

Screen8:-

8.png

Step 3: Create Lambda Function ManageRDSInstances

Now we are ready to go and create lambda function which will manage our instances. Navigate to Services -> Lambda -> Create a Lambda function -> Blank function. Let’s call it ‘ManageRDSInstances’, select latest Node js 6.x as a runtime. Ignore lambda function code for now, and select ‘Choose an existing role’ in ‘Role’ field. You have to be able to find the previously created role in ‘Existing role’ field. Press ‘Next’ -> ‘Create function’.

Screen9:-

9.png

Now let’s upload our archive zip file previously downloaded in local machine to newly created lambda function.

Services -> Lambda -> ManageRDSInstances, and change Code entry type to ‘Upload a .ZIP file’. Press ‘Upload’, select your zip file and press ‘Save’.

Screen10:-

10

Now we need to configure test event: Actions -> Configure test event.

Screen11:-

11.png

Where “tomcatdb01” and “mysqlbiz” are testing RDS instances. After pressing Press ‘Save’ and ‘Test’, you will see that your RDS instances changed state to ‘Stopping’ and soon to ‘Stopped’.

After they are stopped, you can run the same test with action ‘start’, which will run change state of RDS instances to running.

Step 4: Create CloudWatch rule to schedule the event

The last thing is to set up CloudWatch rules to trigger these function on schedule.
Services -> CloudWatch -> Rules -> Create Rule.

Screen12:-

12.png

Select Schedule instead of default Event Pattern. Now you need to set up cron time. Keep in mind that time must be set in GMT timezone. For instance, to start instances every day at 8 am in cron time will look like this: ‘* 8 * * *’.
After you set cron time for waking up your instances, select Lambda function as a Target and pick your newly created lambda function. Then in Configure Input section put your JSON to Constant(JSON text) field:

{ "instances": ["some-test-instance-1",
"some-test-instance-2"], "action":"start" }

Screen13:-

13.png

Screen14:-

14.png

Now your instances will be woken on every morning at 8AM. Create a similar rule with correct cron time for stopping them, do not forget to change action from start to stop in the json:

{ "instances": ["some-test-instance-1",
"some-test-instance-2"], "action":"stop" }

So now we have schedule the Lambda functions to trigger at schedule interval to start and stop RDS instances and save significant cost in AWS billing.

Troubleshoot connection issue using standalone jdbc driver to oracle

Hi All

Please download following file from Google drive and keep in unix directory say /home/oracle

https://drive.google.com/open?id=0B8-3R318F39hQUJOcnVPMUdrNzg

The files to be downloaded:-

Please use ojdbc7.jar downloaded for 12c.For 11g,you can use ojdbc6.jar.

 

Please provide execute permissions

Now you can test connection using below:-

[oracle@SBJ1ITESBDBV01 ~]$ sh sqlScript_new.sh
2017-10-26 09:45:53.0

Please note you may get following error if you use ojdbc6.jar to connect 12c database.

[oracle@SBJ1ITESBDBV01 ~]$ sh sqlScript_new.sh
Exception in thread “main” java.lang.ClassNotFoundException: oracle.jdbc.driver.OracleDriver
at java.net.URLClassLoader.findClass(URLClassLoader.java:381)
at java.lang.ClassLoader.loadClass(ClassLoader.java:424)
at sun.misc.Launcher$AppClassLoader.loadClass(Launcher.java:331)
at java.lang.ClassLoader.loadClass(ClassLoader.java:357)
at java.lang.Class.forName0(Native Method)
at java.lang.Class.forName(Class.java:264)
at sqlconnect.main(sqlconnect.java:20)

Different restore and recovery scenario for PDB and CDB in Oracle 12c

Introduction

The next document will describe the steps to restore scenario for CDB and PDB using Data protector and RMAN

1.New SYSBACKUP privilege in RMAN 12c

1.1 create common user in CDB with sysbackup privilege.

[oracle@rac1 ~]$ export ORACLE_SID=orcl1
 [oracle@rac1 ~]$ sqlplus / as sysdba

SQL*Plus: Release 12.1.0.2.0 Production on Sat Dec 9 19:56:36 2017

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, Real Application Clusters, Automatic Storage Management, OLAP,
Advanced Analytics and Real Application Testing options

SQL> create user c##dba_backup identified by test123;

User created.

SQL> grant sysbackup to c##dba_backup;

Grant succeeded.

 

1.2 Backup whole database using sysbackup privilege

[oracle@rac1 ~]$ rman target '"c##dba_backup@orcl as sysbackup"'

Recovery Manager: Release 12.1.0.2.0 – Production on Sat Dec 9 19:58:38 2017

Copyright (c) 1982, 2014, Oracle and/or its affiliates. All rights reserved.

target database Password:
connected to target database: ORCL (DBID=1489144156)

RMAN> backup database;

Starting backup at 09-DEC-17
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=105 instance=orcl1 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=00001 name=+DATA/ORCL/DATAFILE/system.278.961363313
input datafile file number=00003 name=+DATA/ORCL/DATAFILE/sysaux.277.961363267
input datafile file number=00004 name=+DATA/ORCL/DATAFILE/undotbs1.280.961363369
input datafile file number=00008 name=+DATA/ORCL/DATAFILE/undotbs2.291.961364193
input datafile file number=00006 name=+DATA/ORCL/DATAFILE/users.279.961363369
channel ORA_DISK_1: starting piece 1 at 09-DEC-17

 

2.Backup of CDB and PDB

—CDB backup–
export ORACLE_SID=ORCL1

[oracle@rac1 ~]$ rman target /

RMAN> backup database plus archivelog;
—CDB root backup—
RMAN> backup pluggable database "CDB$ROOT";
–Backup pluggable database—
RMAN>backup pluggable database oem;

RMAN>backup pluggable database oem plus archivelog;

3.Active database duplicate database and other new RMAN 12c feature

Active RMAN duplicate clone 12c using section size and compress backupset

4.Lost of temp file scenario for CDB and PDB

When you create a user, you can specify a temporary tablespace to be used by the user. If a temporary tablespace is not specified, the default tablespace for the PDB is used. If a default tablespace is not specified for the PDB, the temporary tablespace for the CDB is used.
If a temp file belonging to the CDB temporary tablespace is lost or damaged, and the user issuing the statement uses it, an error during the execution of SQL statements that require this temporary space occurs.
SQL> select * from dba_source order by 1;

274137 rows selected.

Execution Plan ———————————————————-

ERROR: ORA-00604: error occurred at recursive SQL level 1
ORA-01116: error in opening database file 201 ORA-01110: data file 201: ‘/u01/app/oracle/oradata/ORA12CP/datafile/o1_mf_temp_do2ljro0_.tmp’
ORA-27041: unable to open file Linux-x86_64 Error: 2: No such file or directory Additional information: 3

4.1 Create a temp file in CDB

SQL> alter tablespace temp add tempfile;

Tablespace altered.

NB: Temporary datafile will be automatically re-created once DB is started.

4.2 Temporary tablespace lost for PDB

[oracle@XXX ~]$ sqlplus system/oracle@ORA12CPD1

 

SQL> set autotrace traceonly;
SQL> select * from dba_source order by 1;

285357 rows selected.

Execution Plan
———————————————————-
ERROR:
ORA-00604: error occurred at recursive SQL level 1
ORA-01116: error in opening database file 203
ORA-01110: data file 203:
‘/u01/app/oracle/oradata/ORA12CP/52E1B5675376582DE0537904330A562D/datafile/o1_mf
_temp_do2lvkg7_.dbf’
ORA-27041: unable to open file
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3

4.3 Add tempfile to temp tablespace in PDB and then drop corrupted temp file

SQL> alter tablespace temp add tempfile;

Tablespace altered.

SQL> alter tablespace temp drop tempfile ‘/u01/app/oracle/oradata/ORA12CP/52E1B5675376582DE0537904330A562D/datafile/o1_mf_temp_do2lvkg7_.dbf’;

Tablespace altered.

SQL> select * from dba_source order by 1;

285357 rows selected.

NB: The PDB can open with missing temp files. If any of the temporary files do not exist when the PDB is opened , they are not created automatically. They are automatically recreated at CDB startup. 

 5. Lost of Control file in CDB (PDB does not contain control file)

Failure Scenario

[oracle@xxx ~]$ rm /u01/app/oracle/oradata/ORA12CP/controlfile/o1_mf_do2ljh6z_.ctl
[oracle@xxx ~]$ sqlplus / as sysdba

 

SQL> select count(1) from dba_objects;

COUNT(1)
———-
72648

SQL> create table t1(c1 char(100));

Table created.
SQL> insert into t1 select 'A' from dual connect by rownum<100000;
 insert into t1 select 'A' from dual connect by rownum<100000
 *
 ERROR at line 1:
 ORA-00210: cannot open the specified control file
 ORA-00202: control file:
 '/u01/app/oracle/oradata/ORA12CP/controlfile/o1_mf_do2ljh6z_.ctl'
 ORA-27041: unable to open file
 Linux-x86_64 Error: 2: No such file or directory
 Additional information: 3
SQL> select count(1) from dba_objects;

COUNT(1)
———-
72649

5.1 Recover the CONTROLFILE now (The database was previously restored to old incarnation)

[oracle@XXX ~]$ rman target /

Recovery Manager: Release 12.2.0.1.0 – Production on Tue Jun 27 15:02:13 2017

Copyright (c) 1982, 2017, Oracle and/or its affiliates. All rights reserved.

connected to target database: ORA12CP (DBID=1429060559, not open)

RMAN> list incarnation;

using target database control file instead of recovery catalog

List of Database Incarnations
DB Key Inc Key DB Name DB ID STATUS Reset SCN Reset Time
——- ——- ——– —————- — ———- ———-
1 1 ORA12CP 1429060559 PARENT 1 26-JAN-17
2 2 ORA12CP 1429060559 PARENT 1408558 26-JUN-17
3 3 ORA12CP 1429060559 CURRENT 1499533 26-JUN-17

RMAN> reset database to incarnation 2;

database reset to incarnation 2

[oracle@XXX ~]$ rman target / catalog rco/rco@DBADB

Recovery Manager: Release 12.2.0.1.0 – Production on Tue Jun 27 14:58:14 2017

Copyright (c) 1982, 2017, Oracle and/or its affiliates. All rights reserved.

connected to target database: ORA12CP (DBID=1429060559, not open)
connected to recovery catalog database

RMAN> run {
 2> allocate channel 'dev_0' type 'sbt_tape'
 3> parms 'SBT_LIBRARY=/opt/omni/lib/libob2oracle8_64bit.so,ENV=(OB2BARTYPE=Oracle8,OB2APPNAME=ORA12cP,OB2BARLIST=1498520660,OB2BARHOSTNAME=cdv1pradmdbv01.tdeprdcl.internal)';
 4> recover database;
 5> alter database open resetlogs;
 6> }

allocated channel: dev_0
channel dev_0: SID=34 device type=SBT_TAPE
channel dev_0: Data Protector A.09.00/110

Starting recover at 27-JUN-17

starting media recovery
media recovery failed
released channel: dev_0
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of recover command at 06/27/2017 15:04:16
ORA-00283: recovery session canceled due to errors
RMAN-11003: failure during parse/execution of SQL statement: alter database recover
if needed start until cancel using backup controlfile
ORA-00283: recovery session canceled due to errors
ORA-19909: datafile 1 belongs to an orphan incarnation
ORA-01110: data file 1: ‘/u01/app/oracle/oradata/ORA12CP/datafile/o1_mf_system_do2lf6n8_.dbf’

You need to restore and recover as you reset incarnation

RMAN> run {
 2> allocate channel 'dev_0' type 'sbt_tape'
 3> parms 'SBT_LIBRARY=/opt/omni/lib/libob2oracle8_64bit.so,ENV=(OB2BARTYPE=Oracle8,OB2APPNAME=ORA12cP,OB2BARLIST=1498520660,OB2BARHOSTNAME=cdv1pradmdbv01.tdeprdcl.internal)';
 4> restore database;
 5> recover database;
 6> alter database open resetlogs;
 7> }

allocated channel: dev_0
channel dev_0: SID=42 device type=SBT_TAPE
channel dev_0: Data Protector A.09.00/110

Starting restore at 27-JUN-17

skipping datafile 5; already restored to file /u01/app/oracle/oradata/ORA12CP/datafile/o1_mf_system_do2ljv4y_.dbf
skipping datafile 6; already restored to file /u01/app/oracle/oradata/ORA12CP/datafile/o1_mf_sysaux_do2ljv4q_.dbf
skipping datafile 8; already restored to file /u01/app/oracle/oradata/ORA12CP/datafile/o1_mf_undotbs1_do2ljv4z_.dbf
channel dev_0: starting datafile backup set restore
channel dev_0: specifying datafile(s) to restore from backup set
channel dev_0: restoring datafile 00001 to /u01/app/oracle/oradata/ORA12CP/datafile/o1_mf_system_do2lf6n8_.dbf
channel dev_0: reading from backup piece ora12cpt<ORA12cP_2:947689345:1>.dbf
channel dev_0: piece handle=ora12cpt<ORA12cP_2:947689345:1>.dbf tag=TAG20170626T150225
channel dev_0: restored backup piece 1
channel dev_0: restore complete, elapsed time: 00:00:45
channel dev_0: starting datafile backup set restore
channel dev_0: specifying datafile(s) to restore from backup set
channel dev_0: restoring datafile 00003 to /u01/app/oracle/oradata/ORA12CP/datafile/o1_mf_sysaux_do2lgmrx_.dbf
channel dev_0: reading from backup piece ora12cpt<ORA12cP_3:947689360:1>.dbf
channel dev_0: piece handle=ora12cpt<ORA12cP_3:947689360:1>.dbf tag=TAG20170626T150225
channel dev_0: restored backup piece 1
channel dev_0: restore complete, elapsed time: 00:00:45
channel dev_0: starting datafile backup set restore
channel dev_0: specifying datafile(s) to restore from backup set
channel dev_0: restoring datafile 00010 to /u01/app/oracle/oradata/ORA12CP/52E1B5675376582DE0537904330A562D/datafile/o1_mf_sysaux_do2lvkg6_.dbf
channel dev_0: reading from backup piece ora12cpt<ORA12cP_4:947689367:1>.dbf
channel dev_0: piece handle=ora12cpt<ORA12cP_4:947689367:1>.dbf tag=TAG20170626T150225
channel dev_0: restored backup piece 1
channel dev_0: restore complete, elapsed time: 00:00:45
channel dev_0: starting datafile backup set restore
channel dev_0: specifying datafile(s) to restore from backup set
channel dev_0: restoring datafile 00014 to /u01/app/oracle/oradata/ORA12CP/52E1B5C5F134583AE0537904330A679A/datafile/o1_mf_sysaux_do2lvqd8_.dbf
channel dev_0: reading from backup piece ora12cpt<ORA12cP_5:947689374:1>.dbf
channel dev_0: piece handle=ora12cpt<ORA12cP_5:947689374:1>.dbf tag=TAG20170626T150225
channel dev_0: restored backup piece 1
channel dev_0: restore complete, elapsed time: 00:00:45
channel dev_0: starting datafile backup set restore
channel dev_0: specifying datafile(s) to restore from backup set
channel dev_0: restoring datafile 00009 to /u01/app/oracle/oradata/ORA12CP/52E1B5675376582DE0537904330A562D/datafile/o1_mf_system_do2lvkfl_.dbf
channel dev_0: reading from backup piece ora12cpt<ORA12cP_8:947689396:1>.dbf
channel dev_0: piece handle=ora12cpt<ORA12cP_8:947689396:1>.dbf tag=TAG20170626T150225
channel dev_0: restored backup piece 1
channel dev_0: restore complete, elapsed time: 00:00:45
channel dev_0: starting datafile backup set restore
channel dev_0: specifying datafile(s) to restore from backup set
channel dev_0: restoring datafile 00013 to /u01/app/oracle/oradata/ORA12CP/52E1B5C5F134583AE0537904330A679A/datafile/o1_mf_system_do2lvqd7_.dbf
channel dev_0: reading from backup piece ora12cpt<ORA12cP_9:947689403:1>.dbf
channel dev_0: piece handle=ora12cpt<ORA12cP_9:947689403:1>.dbf tag=TAG20170626T150225
channel dev_0: restored backup piece 1
channel dev_0: restore complete, elapsed time: 00:00:45
channel dev_0: starting datafile backup set restore
channel dev_0: specifying datafile(s) to restore from backup set
channel dev_0: restoring datafile 00011 to /u01/app/oracle/oradata/ORA12CP/52E1B5675376582DE0537904330A562D/datafile/o1_mf_undotbs1_do2lvkg7_.dbf
channel dev_0: reading from backup piece ora12cpt<ORA12cP_11:947689413:1>.dbf
channel dev_0: piece handle=ora12cpt<ORA12cP_11:947689413:1>.dbf tag=TAG20170626T150225
channel dev_0: restored backup piece 1
channel dev_0: restore complete, elapsed time: 00:00:35
channel dev_0: starting datafile backup set restore
channel dev_0: specifying datafile(s) to restore from backup set
channel dev_0: restoring datafile 00015 to /u01/app/oracle/oradata/ORA12CP/52E1B5C5F134583AE0537904330A679A/datafile/o1_mf_undotbs1_do2lvqd8_.dbf
channel dev_0: reading from backup piece ora12cpt<ORA12cP_12:947689416:1>.dbf
channel dev_0: piece handle=ora12cpt<ORA12cP_12:947689416:1>.dbf tag=TAG20170626T150225
channel dev_0: restored backup piece 1
channel dev_0: restore complete, elapsed time: 00:00:35
channel dev_0: starting datafile backup set restore
channel dev_0: specifying datafile(s) to restore from backup set
channel dev_0: restoring datafile 00004 to /u01/app/oracle/oradata/ORA12CP/datafile/o1_mf_undotbs1_do2lhdxt_.dbf
channel dev_0: reading from backup piece ora12cpt<ORA12cP_13:947689419:1>.dbf
channel dev_0: piece handle=ora12cpt<ORA12cP_13:947689419:1>.dbf tag=TAG20170626T150225
channel dev_0: restored backup piece 1
channel dev_0: restore complete, elapsed time: 00:00:35
channel dev_0: starting datafile backup set restore
channel dev_0: specifying datafile(s) to restore from backup set
channel dev_0: restoring datafile 00007 to /u01/app/oracle/oradata/ORA12CP/datafile/o1_mf_users_do2lhg0j_.dbf
channel dev_0: reading from backup piece ora12cpt<ORA12cP_14:947689422:1>.dbf
channel dev_0: piece handle=ora12cpt<ORA12cP_14:947689422:1>.dbf tag=TAG20170626T150225
channel dev_0: restored backup piece 1
channel dev_0: restore complete, elapsed time: 00:00:35
channel dev_0: starting datafile backup set restore
channel dev_0: specifying datafile(s) to restore from backup set
channel dev_0: restoring datafile 00012 to /u01/app/oracle/oradata/ORA12CP/52E1B5675376582DE0537904330A562D/datafile/o1_mf_users_do2lvpy8_.dbf
channel dev_0: reading from backup piece ora12cpt<ORA12cP_15:947689429:1>.dbf
channel dev_0: piece handle=ora12cpt<ORA12cP_15:947689429:1>.dbf tag=TAG20170626T150225
channel dev_0: restored backup piece 1
channel dev_0: restore complete, elapsed time: 00:00:35
channel dev_0: starting datafile backup set restore
channel dev_0: specifying datafile(s) to restore from backup set
channel dev_0: restoring datafile 00016 to /u01/app/oracle/oradata/ORA12CP/52E1B5C5F134583AE0537904330A679A/datafile/o1_mf_users_do2lvxsx_.dbf
channel dev_0: reading from backup piece ora12cpt<ORA12cP_16:947689433:1>.dbf
channel dev_0: piece handle=ora12cpt<ORA12cP_16:947689433:1>.dbf tag=TAG20170626T150225
channel dev_0: restored backup piece 1
channel dev_0: restore complete, elapsed time: 00:00:45
Finished restore at 27-JUN-17

Starting recover at 27-JUN-17

starting media recovery
media recovery failed
released channel: dev_0
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of recover command at 06/27/2017 15:13:38
ORA-00283: recovery session canceled due to errors
RMAN-11003: failure during parse/execution of SQL statement: alter database recover
if needed start until cancel using backup controlfile
ORA-00283: recovery session canceled due to errors
ORA-19912: cannot recover to target incarnation 2

[oracle@xxx ~]$ rman target /

Recovery Manager: Release 12.2.0.1.0 – Production on Tue Jun 27 15:16:04 2017

Copyright (c) 1982, 2017, Oracle and/or its affiliates. All rights reserved.

connected to target database: ORA12CP (DBID=1429060559, not open)

RMAN> list incarnation;

using target database control file instead of recovery catalog

List of Database Incarnations
DB Key Inc Key DB Name DB ID STATUS Reset SCN Reset Time
——- ——- ——– —————- — ———- ———-
1 1 ORA12CP 1429060559 PARENT 1 26-JAN-17
2 2 ORA12CP 1429060559 CURRENT 1408558 26-JUN-17
3 3 ORA12CP 1429060559 ORPHAN 1499533 26-JUN-17

RMAN> reset database to incarnation 3;
RMAN> run {
 2> allocate channel 'dev_0' type 'sbt_tape'
 3> parms 'SBT_LIBRARY=/opt/omni/lib/libob2oracle8_64bit.so,ENV=(OB2BARTYPE=Oracle8,OB2APPNAME=ORA12cP,OB2BARLIST=1498520660,OB2BARHOSTNAME=cdv1pradmdbv01.tdeprdcl.internal)';
 4> recover database;
 5> alter database open resetlogs;
 6> }

allocated channel: dev_0
channel dev_0: SID=34 device type=SBT_TAPE
channel dev_0: Data Protector A.09.00/110

Starting recover at 27-JUN-17

starting media recovery

archived log for thread 1 with sequence 4 is already on disk as file /u01/app/oracle/fast_recovery_area/ORA12cP/ORA12CP/archivelog/2017_06_26/o1_mf_1_4_do314vlc_.arc
archived log for thread 1 with sequence 5 is already on disk as file /u01/app/oracle/fast_recovery_area/ORA12cP/ORA12CP/archivelog/2017_06_26/o1_mf_1_5_do3fzn9p_.arc
archived log for thread 1 with sequence 1 is already on disk as file /u01/app/oracle/fast_recovery_area/ORA12cP/ORA12CP/archivelog/2017_06_27/o1_mf_1_1_do3p7ltn_.arc
archived log for thread 1 with sequence 2 is already on disk as file /u01/app/oracle/fast_recovery_area/ORA12cP/ORA12CP/onlinelog/o1_mf_2_do3fzo1m_.log
channel dev_0: starting archived log restore to default destination
channel dev_0: restoring archived log
archived log thread=1 sequence=2
channel dev_0: reading from backup piece ora12cpt<ORA12cP_18:947689444:1>.dbf
channel dev_0: piece handle=ora12cpt<ORA12cP_18:947689444:1>.dbf tag=TAG20170626T150404
channel dev_0: restored backup piece 1
channel dev_0: restore complete, elapsed time: 00:00:45
archived log file name=/u01/app/oracle/fast_recovery_area/ORA12cP/ORA12CP/archivelog/2017_06_27/o1_mf_1_2_do5cv24b_.arc thread=1 sequence=2
channel default: deleting archived log(s)
archived log file name=/u01/app/oracle/fast_recovery_area/ORA12cP/ORA12CP/archivelog/2017_06_27/o1_mf_1_2_do5cv24b_.arc RECID=26 STAMP=947776675
channel dev_0: starting archived log restore to default destination
channel dev_0: restoring archived log
archived log thread=1 sequence=3
channel dev_0: reading from backup piece ora12cpt<ORA12cP_19:947689447:1>.dbf
channel dev_0: piece handle=ora12cpt<ORA12cP_19:947689447:1>.dbf tag=TAG20170626T150404
channel dev_0: restored backup piece 1
channel dev_0: restore complete, elapsed time: 00:00:35
archived log file name=/u01/app/oracle/fast_recovery_area/ORA12cP/ORA12CP/archivelog/2017_06_27/o1_mf_1_3_do5cwg82_.arc thread=1 sequence=3
channel default: deleting archived log(s)
archived log file name=/u01/app/oracle/fast_recovery_area/ORA12cP/ORA12CP/archivelog/2017_06_27/o1_mf_1_3_do5cwg82_.arc RECID=27 STAMP=947776718
archived log file name=/u01/app/oracle/fast_recovery_area/ORA12cP/ORA12CP/archivelog/2017_06_26/o1_mf_1_4_do314vlc_.arc thread=1 sequence=4
archived log file name=/u01/app/oracle/fast_recovery_area/ORA12cP/ORA12CP/archivelog/2017_06_26/o1_mf_1_5_do3fzn9p_.arc thread=1 sequence=5
archived log file name=/u01/app/oracle/fast_recovery_area/ORA12cP/ORA12CP/archivelog/2017_06_27/o1_mf_1_1_do3p7ltn_.arc thread=1 sequence=1
archived log file name=/u01/app/oracle/fast_recovery_area/ORA12cP/ORA12CP/onlinelog/o1_mf_2_do3fzo1m_.log thread=1 sequence=2
media recovery complete, elapsed time: 00:00:25
Finished recover at 27-JUN-17

Statement processed
released channel: dev_0

5.2 Restore database control file current incarnation

[oracle@cdv1pradmdbv01 ~]$ rm /u01/app/oracle/oradata/ORA12CP/controlfile/o1_mf_do2ljh6z_.ctl
[oracle@cdv1pradmdbv01 ~]$ sqlplus / as sysdba

SQL*Plus: Release 12.2.0.1.0 Production on Tue Jun 27 15:26:11 2017

Copyright (c) 1982, 2016, Oracle. All rights reserved.

Connected to:
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 – 64bit Production

SQL> shutdown abort;
 ORACLE instance shut down.

SQL> startup nomount;
 ORACLE instance started.

Total System Global Area 1828716544 bytes
Fixed Size 8793928 bytes
Variable Size 553648312 bytes
Database Buffers 1258291200 bytes
Redo Buffers 7983104 bytes

[oracle@cdv1pradmdbv01 ~]$ rman target /

connected to target database: ORA12CP (not mounted)

RMAN> restore controlfile from autobackup;

Starting restore at 27-JUN-17
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=35 device type=DISK

recovery area destination: /u01/app/oracle/fast_recovery_area/ORA12cP
database name (or database unique name) used for search: ORA12CP
channel ORA_DISK_1: AUTOBACKUP /u01/app/oracle/fast_recovery_area/ORA12cP/ORA12CP/autobackup/2017_06_27/o1_mf_s_947776764_do5cxx44_.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/ORA12cP/ORA12CP/autobackup/2017_06_27/o1_mf_s_947776764_do5cxx44_.bkp
channel ORA_DISK_1: control file restore from AUTOBACKUP complete
output file name=/u01/app/oracle/oradata/ORA12CP/controlfile/o1_mf_do2ljh6z_.ctl
output file name=/u01/app/oracle/fast_recovery_area/ORA12cP/ORA12CP/controlfile/o1_mf_do2ljh8f_.ctl
Finished restore at 27-JUN-17

 

RMAN> alter database mount;

Statement processed
released channel: ORA_DISK_1

RMAN> run {
 allocate channel 'dev_0' type 'sbt_tape'
 parms 'SBT_LIBRARY=/opt/omni/lib/libob2oracle8_64bit.so,ENV=(OB2BARTYPE=Oracle8,OB2APPNAME=ORA12cP,OB2BARLIST=1498520660,OB2BARHOSTNAME=cdv1pradmdbv01.tdeprdcl.internal)';
 recover database;
 }2> 3> 4> 5>

allocated channel: dev_0
channel dev_0: SID=35 device type=SBT_TAPE
channel dev_0: Data Protector A.09.00/110

Starting recover at 27-JUN-17

starting media recovery

archived log for thread 1 with sequence 1 is already on disk as file /u01/app/oracle/fast_recovery_area/ORA12cP/ORA12CP/onlinelog/o1_mf_1_do3fznnn_.log
archived log file name=/u01/app/oracle/fast_recovery_area/ORA12cP/ORA12CP/onlinelog/o1_mf_1_do3fznnn_.log thread=1 sequence=1
media recovery complete, elapsed time: 00:00:00
Finished recover at 27-JUN-17
released channel: dev_0

RMAN> alter database open RESETLOGS;

Statement processed

RMAN> list incarnation;

List of Database Incarnations
DB Key Inc Key DB Name DB ID STATUS Reset SCN Reset Time
——- ——- ——– —————- — ———- ———-
1 1 ORA12CP 1429060559 PARENT 1 26-JAN-17
2 2 ORA12CP 1429060559 PARENT 1408558 26-JUN-17
3 3 ORA12CP 1429060559 PARENT 1499533 26-JUN-17
4 4 ORA12CP 1429060559 PARENT 1589073 27-JUN-17
5 5 ORA12CP 1429060559 CURRENT 1591011 27-JUN-17

RMAN> alter pluggable database all open;

Statement processed

6.RESTORE TABLESPACE SCENERIO (CDB)

If the missing or corrupted data file belongs to the root container SYSTEM or UNDO tablespace, then the CDB instance will require shutdown, and a media recovery is required. In a RAC environment, you would shut down all instances of the CDB.
This means that all PDBs will be closed.
The CDB must be mounted before restoring and recovering the missing root data file.
After the root data file is recovered, open the CDB and all PDBs.

 6.1 RESTORE SYSTEM AND UNDO TABLESPACE FOR ROOT(CDB)

[oracle@cdv1pradmdbv01 ~]$ sqlplus / as sysdba

SQL> shutdown immediate;

ORACLE instance shut down.
 SQL> startup mount;
ORACLE instance started.

Total System Global Area 1828716544 bytes
Fixed Size 8793928 bytes
Variable Size 553648312 bytes
Database Buffers 1258291200 bytes
Redo Buffers 7983104 bytes
Database mounted.

[oracle@cdv1pradmdbv01 ~]$ rman target / catalog rco/rco@DBADB

Recovery Manager: Release 12.2.0.1.0 – Production on Tue Jun 27 15:37:49 2017

Copyright (c) 1982, 2017, Oracle and/or its affiliates. All rights reserved.

connected to target database: ORA12CP (DBID=1429060559, not open)
connected to recovery catalog database

RMAN> run {
 allocate channel 'dev_0' type 'sbt_tape'
 2> 3> parms 'SBT_LIBRARY=/opt/omni/lib/libob2oracle8_64bit.so,ENV=(OB2BARTYPE=Oracle8,OB2APPNAME=ORA12cP,OB2BARLIST=1498520660,OB2BARHOSTNAME=cdv1pradmdbv01.tdeprdcl.internal)';
 4> restore tablespace 'UNDOTBS1';
 5> recover tablespace 'UNDOTBS1';
 6> }

allocated channel: dev_0
channel dev_0: SID=41 device type=SBT_TAPE
channel dev_0: Data Protector A.09.00/110

Starting restore at 27-JUN-17

channel dev_0: starting datafile backup set restore
channel dev_0: specifying datafile(s) to restore from backup set
channel dev_0: restoring datafile 00004 to /u01/app/oracle/oradata/ORA12CP/datafile/o1_mf_undotbs1_do2lhdxt_.dbf
channel dev_0: reading from backup piece ora12cpt<ORA12cP_13:947689419:1>.dbf
channel dev_0: piece handle=ora12cpt<ORA12cP_13:947689419:1>.dbf tag=TAG20170626T150225
channel dev_0: restored backup piece 1
channel dev_0: restore complete, elapsed time: 00:00:45
Finished restore at 27-JUN-17

Starting recover at 27-JUN-17

starting media recovery

archived log for thread 1 with sequence 4 is already on disk as file /u01/app/oracle/fast_recovery_area/ORA12cP/ORA12CP/archivelog/2017_06_26/o1_mf_1_4_do314vlc_.arc
archived log for thread 1 with sequence 1 is already on disk as file /u01/app/oracle/fast_recovery_area/ORA12cP/ORA12CP/archivelog/2017_06_27/o1_mf_1_1_do5dh20l_.arc
channel dev_0: starting archived log restore to default destination
channel dev_0: restoring archived log
archived log thread=1 sequence=2
channel dev_0: reading from backup piece ora12cpt<ORA12cP_18:947689444:1>.dbf
channel dev_0: piece handle=ora12cpt<ORA12cP_18:947689444:1>.dbf tag=TAG20170626T150404
channel dev_0: restored backup piece 1
channel dev_0: restore complete, elapsed time: 00:00:45
archived log file name=/u01/app/oracle/fast_recovery_area/ORA12cP/ORA12CP/archivelog/2017_06_27/o1_mf_1_2_do5f37t5_.arc thread=1 sequence=2
channel default: deleting archived log(s)
archived log file name=/u01/app/oracle/fast_recovery_area/ORA12cP/ORA12CP/archivelog/2017_06_27/o1_mf_1_2_do5f37t5_.arc RECID=37 STAMP=947777960
channel dev_0: starting archived log restore to default destination
channel dev_0: restoring archived log
archived log thread=1 sequence=3
channel dev_0: reading from backup piece ora12cpt<ORA12cP_19:947689447:1>.dbf
channel dev_0: piece handle=ora12cpt<ORA12cP_19:947689447:1>.dbf tag=TAG20170626T150404
channel dev_0: restored backup piece 1
channel dev_0: restore complete, elapsed time: 00:00:35
archived log file name=/u01/app/oracle/fast_recovery_area/ORA12cP/ORA12CP/archivelog/2017_06_27/o1_mf_1_3_do5f4nw9_.arc thread=1 sequence=3
channel default: deleting archived log(s)
archived log file name=/u01/app/oracle/fast_recovery_area/ORA12cP/ORA12CP/archivelog/2017_06_27/o1_mf_1_3_do5f4nw9_.arc RECID=38 STAMP=947778004
archived log file name=/u01/app/oracle/fast_recovery_area/ORA12cP/ORA12CP/archivelog/2017_06_26/o1_mf_1_4_do314vlc_.arc thread=1 sequence=4
archived log file name=/u01/app/oracle/fast_recovery_area/ORA12cP/ORA12CP/archivelog/2017_06_26/o1_mf_1_5_do3fzn9p_.arc thread=1 sequence=5
archived log file name=/u01/app/oracle/fast_recovery_area/ORA12cP/ORA12CP/archivelog/2017_06_27/o1_mf_1_1_do5cx8k2_.arc thread=1 sequence=1
archived log file name=/u01/app/oracle/fast_recovery_area/ORA12cP/ORA12CP/archivelog/2017_06_27/o1_mf_1_2_do5cx8x2_.arc thread=1 sequence=2
archived log file name=/u01/app/oracle/fast_recovery_area/ORA12cP/ORA12CP/archivelog/2017_06_27/o1_mf_1_1_do5dh20l_.arc thread=1 sequence=1
media recovery complete, elapsed time: 00:00:03
Finished recover at 27-JUN-17
released channel: dev_0

RMAN> alter database open;

Statement processed
starting full resync of recovery catalog
full resync complete

RMAN> alter pluggable database all open;

Statement processed

NB: If the missing or corrupted file belongs to root container SYSTEM or UNDO tablespace ,then CDB instance needs to shutdown and media recover required. All PDBs will be shutdown also.
After root datafile recovered, CDB and PDB could be opened.

 

6.2 RESTORE SYSAUX TABLESPACE FOR ROOT IN CDB

There is no need to stop CDB and PDB.This recovery can be done when database is online
[oracle@cdv1pradmdbv01 ~]$ rman target / catalog rco/rco@DBADB

Recovery Manager: Release 12.2.0.1.0 – Production on Tue Jun 27 16:10:13 2017

Copyright (c) 1982, 2017, Oracle and/or its affiliates. All rights reserved.

connected to target database: ORA12CP (DBID=1429060559)
connected to recovery catalog database

RMAN> run {
 allocate channel 'dev_0' type 'sbt_tape'
 2> 3> parms 'SBT_LIBRARY=/opt/omni/lib/libob2oracle8_64bit.so,ENV=(OB2BARTYPE=Oracle8,OB2APPNAME=ORA12cP,OB2BARLIST=1498520660,OB2BARHOSTNAME=cdv1pradmdbv01.tdeprdcl.internal)';
 4> restore tablespace 'SYSAUX';
 5> recover tablespace 'SYSAUX';
 6> alter tablespace SYSAUX online;
 7> }

allocated channel: dev_0
channel dev_0: SID=58 device type=SBT_TAPE
channel dev_0: Data Protector A.09.00/110

Starting restore at 27-JUN-17

channel dev_0: starting datafile backup set restore
channel dev_0: specifying datafile(s) to restore from backup set
channel dev_0: restoring datafile 00003 to /u01/app/oracle/oradata/ORA12CP/datafile/o1_mf_sysaux_do2lgmrx_.dbf
channel dev_0: reading from backup piece ora12cpt<ORA12cP_3:947689360:1>.dbf
channel dev_0: piece handle=ora12cpt<ORA12cP_3:947689360:1>.dbf tag=TAG20170626T150225
channel dev_0: restored backup piece 1
channel dev_0: restore complete, elapsed time: 00:00:45
Finished restore at 27-JUN-17

Starting recover at 27-JUN-17

starting media recovery

archived log for thread 1 with sequence 4 is already on disk as file /u01/app/oracle/fast_recovery_area/ORA12cP/ORA12CP/archivelog/2017_06_26/o1_mf_1_4_do314vlc_.arc
archived log for thread 1 with sequence 5 is already on disk as file /u01/app/oracle/fast_recovery_area/ORA12cP/ORA12CP/archivelog/2017_06_26/o1_mf_1_5_do3fzn9p_.arc
archived log for thread 1 with sequence 1 is already on disk as file /u01/app/oracle/fast_recovery_area/ORA12cP/ORA12CP/archivelog/2017_06_27/o1_mf_1_1_do5cx8k2_.arc
archived log for thread 1 with sequence 2 is already on disk as file /u01/app/oracle/fast_recovery_area/ORA12cP/ORA12CP/archivelog/2017_06_27/o1_mf_1_2_do5cx8x2_.arc
archived log for thread 1 with sequence 1 is already on disk as file /u01/app/oracle/fast_recovery_area/ORA12cP/ORA12CP/archivelog/2017_06_27/o1_mf_1_1_do5dh20l_.arc
channel dev_0: starting archived log restore to default destination
channel dev_0: restoring archived log
archived log thread=1 sequence=2
channel dev_0: reading from backup piece ora12cpt<ORA12cP_18:947689444:1>.dbf
channel dev_0: piece handle=ora12cpt<ORA12cP_18:947689444:1>.dbf tag=TAG20170626T150404
channel dev_0: restored backup piece 1
channel dev_0: restore complete, elapsed time: 00:00:46
archived log file name=/u01/app/oracle/fast_recovery_area/ORA12cP/ORA12CP/archivelog/2017_06_27/o1_mf_1_2_do5h134y_.arc thread=1 sequence=2
channel default: deleting archived log(s)
archived log file name=/u01/app/oracle/fast_recovery_area/ORA12cP/ORA12CP/archivelog/2017_06_27/o1_mf_1_2_do5h134y_.arc RECID=42 STAMP=947779940
channel dev_0: starting archived log restore to default destination
channel dev_0: restoring archived log
archived log thread=1 sequence=3
channel dev_0: reading from backup piece ora12cpt<ORA12cP_19:947689447:1>.dbf
channel dev_0: piece handle=ora12cpt<ORA12cP_19:947689447:1>.dbf tag=TAG20170626T150404
channel dev_0: restored backup piece 1
channel dev_0: restore complete, elapsed time: 00:00:35
archived log file name=/u01/app/oracle/fast_recovery_area/ORA12cP/ORA12CP/archivelog/2017_06_27/o1_mf_1_3_do5h2hq4_.arc thread=1 sequence=3
channel default: deleting archived log(s)
archived log file name=/u01/app/oracle/fast_recovery_area/ORA12cP/ORA12CP/archivelog/2017_06_27/o1_mf_1_3_do5h2hq4_.arc RECID=43 STAMP=947779983
archived log file name=/u01/app/oracle/fast_recovery_area/ORA12cP/ORA12CP/archivelog/2017_06_26/o1_mf_1_4_do314vlc_.arc thread=1 sequence=4
archived log file name=/u01/app/oracle/fast_recovery_area/ORA12cP/ORA12CP/archivelog/2017_06_26/o1_mf_1_5_do3fzn9p_.arc thread=1 sequence=5
archived log file name=/u01/app/oracle/fast_recovery_area/ORA12cP/ORA12CP/archivelog/2017_06_27/o1_mf_1_1_do5cx8k2_.arc thread=1 sequence=1
archived log file name=/u01/app/oracle/fast_recovery_area/ORA12cP/ORA12CP/archivelog/2017_06_27/o1_mf_1_2_do5cx8x2_.arc thread=1 sequence=2
archived log file name=/u01/app/oracle/fast_recovery_area/ORA12cP/ORA12CP/archivelog/2017_06_27/o1_mf_1_1_do5dh20l_.arc thread=1 sequence=1
media recovery complete, elapsed time: 00:00:03
Finished recover at 27-JUN-17
starting full resync of recovery catalog
full resync complete

Statement processed
released channel: dev_0

7.RESTORE TABLESPACE SCENERIO (PDB)

7.1 Restore missing system tablespace in PDB

If the data file missing or corrupted belongs to a PDB and more specifically to the SYSTEM tablespace, the CDB must be closed unless the PDB is already closed.
A pluggable database or tablespace or data file media recovery is required before the PDB can be reopened.
If the PDB was closed at the time issue, the users can still work in other PDBs during the PDB recovery.
If the PDB was still opened at the time issue, users cannot work at all in any other PDB because the CDB needs to be shut down and mounted only.
The recovery must be issued from root.
Option 1:- Recover whole PDB database .CDB needs to be in Mount state.
[oracle@cdv1pradmdbv01 ~]$ sqlplus

 

Enter user-name: / as sysdba

Connected to:
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 – 64bit Production

SQL> shutdown immediate;
 Database closed.
 Database dismounted.
 ORACLE instance shut down.

SQL> startup mount;
 ORACLE instance started.

Total System Global Area 1828716544 bytes
Fixed Size 8793928 bytes
Variable Size 553648312 bytes
Database Buffers 1258291200 bytes
Redo Buffers 7983104 bytes
Database mounted.
SQL> exit
Disconnected from Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 – 64bit Production

[oracle@cdv1pradmdbv01 ~]$ rman target /

 

connected to target database: ORA12CP (DBID=1429060559, not open)

RMAN> run {
 allocate channel 'dev_0' type 'sbt_tape'
 2> 3> parms 'SBT_LIBRARY=/opt/omni/lib/libob2oracle8_64bit.so,ENV=(OB2BARTYPE=Oracle8,OB2APPNAME=ORA12cP,OB2BARLIST=1498520660,OB2BARHOSTNAME=cdv1pradmdbv01.tdeprdcl.internal)';
 4> restore pluggable database ORA12CPD1;
 5> recover pluggable database ORA12CPD1;
 6> alter database open;
 7> alter pluggable database ORA12CPD1 open;
 8> }

using target database control file instead of recovery catalog
allocated channel: dev_0
channel dev_0: SID=41 device type=SBT_TAPE
channel dev_0: Data Protector A.09.00/110

Starting restore at 27-JUN-17

channel dev_0: starting datafile backup set restore
channel dev_0: specifying datafile(s) to restore from backup set
channel dev_0: restoring datafile 00010 to /u01/app/oracle/oradata/ORA12CP/52E1B5675376582DE0537904330A562D/datafile/o1_mf_sysaux_do2lvkg6_.dbf
channel dev_0: reading from backup piece ora12cpt<ORA12cP_4:947689367:1>.dbf
channel dev_0: piece handle=ora12cpt<ORA12cP_4:947689367:1>.dbf tag=TAG20170626T150225
channel dev_0: restored backup piece 1
channel dev_0: restore complete, elapsed time: 00:00:45
channel dev_0: starting datafile backup set restore
channel dev_0: specifying datafile(s) to restore from backup set
channel dev_0: restoring datafile 00009 to /u01/app/oracle/oradata/ORA12CP/52E1B5675376582DE0537904330A562D/datafile/o1_mf_system_do2lvkfl_.dbf
channel dev_0: reading from backup piece ora12cpt<ORA12cP_8:947689396:1>.dbf
channel dev_0: piece handle=ora12cpt<ORA12cP_8:947689396:1>.dbf tag=TAG20170626T150225
channel dev_0: restored backup piece 1
channel dev_0: restore complete, elapsed time: 00:00:45
channel dev_0: starting datafile backup set restore
channel dev_0: specifying datafile(s) to restore from backup set
channel dev_0: restoring datafile 00011 to /u01/app/oracle/oradata/ORA12CP/52E1B5675376582DE0537904330A562D/datafile/o1_mf_undotbs1_do2lvkg7_.dbf
channel dev_0: reading from backup piece ora12cpt<ORA12cP_11:947689413:1>.dbf
channel dev_0: piece handle=ora12cpt<ORA12cP_11:947689413:1>.dbf tag=TAG20170626T150225
channel dev_0: restored backup piece 1
channel dev_0: restore complete, elapsed time: 00:00:35
channel dev_0: starting datafile backup set restore
channel dev_0: specifying datafile(s) to restore from backup set
channel dev_0: restoring datafile 00012 to /u01/app/oracle/oradata/ORA12CP/52E1B5675376582DE0537904330A562D/datafile/o1_mf_users_do2lvpy8_.dbf
channel dev_0: reading from backup piece ora12cpt<ORA12cP_15:947689429:1>.dbf
channel dev_0: piece handle=ora12cpt<ORA12cP_15:947689429:1>.dbf tag=TAG20170626T150225
channel dev_0: restored backup piece 1
channel dev_0: restore complete, elapsed time: 00:00:35
Finished restore at 27-JUN-17

Starting recover at 27-JUN-17

starting media recovery

archived log for thread 1 with sequence 4 is already on disk as file /u01/app/oracle/fast_recovery_area/ORA12cP/ORA12CP/archivelog/2017_06_26/o1_mf_1_4_do314vlc_.arc
archived log for thread 1 with sequence 5 is already on disk as file /u01/app/oracle/fast_recovery_area/ORA12cP/ORA12CP/archivelog/2017_06_26/o1_mf_1_5_do3fzn9p_.arc
archived log for thread 1 with sequence 1 is already on disk as file /u01/app/oracle/fast_recovery_area/ORA12cP/ORA12CP/archivelog/2017_06_27/o1_mf_1_1_do5cx8k2_.arc
archived log for thread 1 with sequence 2 is already on disk as file /u01/app/oracle/fast_recovery_area/ORA12cP/ORA12CP/archivelog/2017_06_27/o1_mf_1_2_do5cx8x2_.arc
archived log for thread 1 with sequence 1 is already on disk as file /u01/app/oracle/fast_recovery_area/ORA12cP/ORA12CP/archivelog/2017_06_27/o1_mf_1_1_do5dh20l_.arc
channel dev_0: starting archived log restore to default destination
channel dev_0: restoring archived log
archived log thread=1 sequence=2
channel dev_0: reading from backup piece ora12cpt<ORA12cP_18:947689444:1>.dbf
channel dev_0: piece handle=ora12cpt<ORA12cP_18:947689444:1>.dbf tag=TAG20170626T150404
channel dev_0: restored backup piece 1
channel dev_0: restore complete, elapsed time: 00:00:45
archived log file name=/u01/app/oracle/fast_recovery_area/ORA12cP/ORA12CP/archivelog/2017_06_27/o1_mf_1_2_do5l1gjw_.arc thread=1 sequence=2
channel default: deleting archived log(s)
archived log file name=/u01/app/oracle/fast_recovery_area/ORA12cP/ORA12CP/archivelog/2017_06_27/o1_mf_1_2_do5l1gjw_.arc RECID=44 STAMP=947783023
channel dev_0: starting archived log restore to default destination
channel dev_0: restoring archived log
archived log thread=1 sequence=3
channel dev_0: reading from backup piece ora12cpt<ORA12cP_19:947689447:1>.dbf
channel dev_0: piece handle=ora12cpt<ORA12cP_19:947689447:1>.dbf tag=TAG20170626T150404
channel dev_0: restored backup piece 1
channel dev_0: restore complete, elapsed time: 00:00:35
archived log file name=/u01/app/oracle/fast_recovery_area/ORA12cP/ORA12CP/archivelog/2017_06_27/o1_mf_1_3_do5l2t4x_.arc thread=1 sequence=3
channel default: deleting archived log(s)
archived log file name=/u01/app/oracle/fast_recovery_area/ORA12cP/ORA12CP/archivelog/2017_06_27/o1_mf_1_3_do5l2t4x_.arc RECID=45 STAMP=947783066
archived log file name=/u01/app/oracle/fast_recovery_area/ORA12cP/ORA12CP/archivelog/2017_06_26/o1_mf_1_4_do314vlc_.arc thread=1 sequence=4
archived log file name=/u01/app/oracle/fast_recovery_area/ORA12cP/ORA12CP/archivelog/2017_06_26/o1_mf_1_5_do3fzn9p_.arc thread=1 sequence=5
archived log file name=/u01/app/oracle/fast_recovery_area/ORA12cP/ORA12CP/archivelog/2017_06_27/o1_mf_1_1_do5cx8k2_.arc thread=1 sequence=1
archived log file name=/u01/app/oracle/fast_recovery_area/ORA12cP/ORA12CP/archivelog/2017_06_27/o1_mf_1_2_do5cx8x2_.arc thread=1 sequence=2
archived log file name=/u01/app/oracle/fast_recovery_area/ORA12cP/ORA12CP/archivelog/2017_06_27/o1_mf_1_1_do5dh20l_.arc thread=1 sequence=1
media recovery complete, elapsed time: 00:00:06
Finished recover at 27-JUN-17

RMAN> alter database open;

Statement processed

RMAN> alter pluggable database ORA12CPD1 open;

RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of sql statement command at 06/27/2017 17:05:18
ORA-65019: pluggable database ORA12CPD1 already open

 

Option2: Only recover the corrupted system tablespace.  CDB needs to be in mount state.
[oracle@cdv1pradmdbv01 ~]$ rm /u01/app/oracle/oradata/ORA12CP/52E1B5675376582DE0537904330A562D/datafile/o1_mf_system_do2lvkfl_.dbf
[oracle@cdv1pradmdbv01 ~]$ sqlplus

SQL*Plus: Release 12.2.0.1.0 Production on Tue Jun 27 17:09:48 2017

Copyright (c) 1982, 2016, Oracle. All rights reserved.

Enter user-name: / as sysdba

Connected to:
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 – 64bit Production

SQL> shutdown immediate;
 ORA-01116: error in opening database file 9
 ORA-01110: data file 9: '/u01/app/oracle/oradata/ORA12CP/52E1B5675376582DE0537904330A562D/datafile/o1_mf_system_do2lvkfl_.dbf'
 ORA-27041: unable to open file
 Linux-x86_64 Error: 2: No such file or directory
 Additional information: 3

SQL> shutdown abort;

ORACLE instance shut down.

SQL> startup mount;

ORACLE instance started.

Total System Global Area 1828716544 bytes
Fixed Size 8793928 bytes
Variable Size 553648312 bytes
Database Buffers 1258291200 bytes
Redo Buffers 7983104 bytes
Database mounted.

[oracle@cdv1pradmdbv01 ~]$ rman target /

Recovery Manager: Release 12.2.0.1.0 – Production on Tue Jun 27 17:11:18 2017

Copyright (c) 1982, 2017, Oracle and/or its affiliates. All rights reserved.

connected to target database: ORA12CP (DBID=1429060559, not open)

RMAN> run {
 2> allocate channel 'dev_0' type 'sbt_tape'
 3> parms 'SBT_LIBRARY=/opt/omni/lib/libob2oracle8_64bit.so,ENV=(OB2BARTYPE=Oracle8,OB2APPNAME=ORA12cP,OB2BARLIST=1498520660,OB2BARHOSTNAME=cdv1pradmdbv01.tdeprdcl.internal)';
 4> restore tablespace ORA12CPD1:system;
 5> recover tablespace ORA12CPD1:system;
 6> }

using target database control file instead of recovery catalog
allocated channel: dev_0
channel dev_0: SID=41 device type=SBT_TAPE
channel dev_0: Data Protector A.09.00/110

Starting restore at 27-JUN-17

channel dev_0: starting datafile backup set restore
channel dev_0: specifying datafile(s) to restore from backup set
channel dev_0: restoring datafile 00009 to /u01/app/oracle/oradata/ORA12CP/52E1B5675376582DE0537904330A562D/datafile/o1_mf_system_do2lvkfl_.dbf
channel dev_0: reading from backup piece ora12cpt<ORA12cP_8:947689396:1>.dbf
channel dev_0: piece handle=ora12cpt<ORA12cP_8:947689396:1>.dbf tag=TAG20170626T150225
channel dev_0: restored backup piece 1
channel dev_0: restore complete, elapsed time: 00:00:45
Finished restore at 27-JUN-17

Starting recover at 27-JUN-17

starting media recovery

archived log for thread 1 with sequence 4 is already on disk as file /u01/app/oracle/fast_recovery_area/ORA12cP/ORA12CP/archivelog/2017_06_26/o1_mf_1_4_do314vlc_.arc
archived log for thread 1 with sequence 5 is already on disk as file /u01/app/oracle/fast_recovery_area/ORA12cP/ORA12CP/archivelog/2017_06_26/o1_mf_1_5_do3fzn9p_.arc
archived log for thread 1 with sequence 1 is already on disk as file /u01/app/oracle/fast_recovery_area/ORA12cP/ORA12CP/archivelog/2017_06_27/o1_mf_1_1_do5cx8k2_.arc
archived log for thread 1 with sequence 2 is already on disk as file /u01/app/oracle/fast_recovery_area/ORA12cP/ORA12CP/archivelog/2017_06_27/o1_mf_1_2_do5cx8x2_.arc
archived log for thread 1 with sequence 1 is already on disk as file /u01/app/oracle/fast_recovery_area/ORA12cP/ORA12CP/archivelog/2017_06_27/o1_mf_1_1_do5dh20l_.arc
channel dev_0: starting archived log restore to default destination
channel dev_0: restoring archived log
archived log thread=1 sequence=2
channel dev_0: reading from backup piece ora12cpt<ORA12cP_18:947689444:1>.dbf
channel dev_0: piece handle=ora12cpt<ORA12cP_18:947689444:1>.dbf tag=TAG20170626T150404
channel dev_0: restored backup piece 1
channel dev_0: restore complete, elapsed time: 00:00:45
archived log file name=/u01/app/oracle/fast_recovery_area/ORA12cP/ORA12CP/archivelog/2017_06_27/o1_mf_1_2_do5llvyw_.arc thread=1 sequence=2
channel default: deleting archived log(s)
archived log file name=/u01/app/oracle/fast_recovery_area/ORA12cP/ORA12CP/archivelog/2017_06_27/o1_mf_1_2_do5llvyw_.arc RECID=46 STAMP=947783580
channel dev_0: starting archived log restore to default destination
channel dev_0: restoring archived log
archived log thread=1 sequence=3
channel dev_0: reading from backup piece ora12cpt<ORA12cP_19:947689447:1>.dbf
channel dev_0: piece handle=ora12cpt<ORA12cP_19:947689447:1>.dbf tag=TAG20170626T150404
channel dev_0: restored backup piece 1
channel dev_0: restore complete, elapsed time: 00:00:45
archived log file name=/u01/app/oracle/fast_recovery_area/ORA12cP/ORA12CP/archivelog/2017_06_27/o1_mf_1_3_do5lnb83_.arc thread=1 sequence=3
channel default: deleting archived log(s)
archived log file name=/u01/app/oracle/fast_recovery_area/ORA12cP/ORA12CP/archivelog/2017_06_27/o1_mf_1_3_do5lnb83_.arc RECID=47 STAMP=947783626
archived log file name=/u01/app/oracle/fast_recovery_area/ORA12cP/ORA12CP/archivelog/2017_06_26/o1_mf_1_4_do314vlc_.arc thread=1 sequence=4
archived log file name=/u01/app/oracle/fast_recovery_area/ORA12cP/ORA12CP/archivelog/2017_06_26/o1_mf_1_5_do3fzn9p_.arc thread=1 sequence=5
archived log file name=/u01/app/oracle/fast_recovery_area/ORA12cP/ORA12CP/archivelog/2017_06_27/o1_mf_1_1_do5cx8k2_.arc thread=1 sequence=1
archived log file name=/u01/app/oracle/fast_recovery_area/ORA12cP/ORA12CP/archivelog/2017_06_27/o1_mf_1_2_do5cx8x2_.arc thread=1 sequence=2
archived log file name=/u01/app/oracle/fast_recovery_area/ORA12cP/ORA12CP/archivelog/2017_06_27/o1_mf_1_1_do5dh20l_.arc thread=1 sequence=1
media recovery complete, elapsed time: 00:00:04
Finished recover at 27-JUN-17
released channel: dev_0

database dismounted
Oracle instance shut down

RMAN> shutdown immediate;
RMAN> startup;

connected to target database (not started)
Oracle instance started
database mounted
database opened

 

 

8.Point in time recovery of pluggable database (PDB)

If you need to recover a PDB database to a point in time in the past beyond flashback retention, then in this case, flashback is not possible, therefore a point-in-time recovery is necessary.
Recovering a PDB to a point-in-time does not affect all parts of the CDB: the whole CDB is still opened and therefore all other PDBs are opened. After recovering a PDB to a specified point-in-time, when you open the PDB using the RESETLOGS option, a new incarnation of the PDB is created. The PDB RESETLOGS does not perform a RESETLOGS for the CDB.
• A PDB record in the control file is updated.
• Each redo log record carries PDB id in the redo header. This is how recovery knows
which redo applies to which PDB. Redo logs are shared by all PDBs; redo from each PDB is written to a single set of redo logs.
Conceptually a PDB resetlogs is similar to a database resetlogs.
After recovery, the old backup of the PDB remains valid and can be used if a media failure occurs. After restoring/recovering a PDB to a past point in time, one cannot open the PDB read only. PDB read-write open through resetlogs is required.
A PDB incarnation is a subincarnation of the CDB. For example, if the CDB is incarnation 5,and a PDB is incarnation 3, then the fully specified incarnation number of the PDB is (5, 3). The nitial incarnation of a PDB is 0. To view the incarnation of a PDB, query the $PDB_INCARNATION view.

CDB and other PDB remain un affected.

RMAN> run {
 set until time = "TO_DATE('06/27/2017 17:34:00','MM/DD/YYYY HH24:MI:SS')" ;
 2> 3> allocate channel 'dev_0' type 'sbt_tape'
 4> parms 'SBT_LIBRARY=/opt/omni/lib/libob2oracle8_64bit.so,ENV=(OB2BARTYPE=Oracle8,OB2APPNAME=ORA12cP,OB2BARLIST=1498520660,OB2BARHOSTNAME=cdv1pradmdbv01.tdeprdcl.internal)';
 5> restore pluggable database ORA12CPD1;
 6> recover pluggable database ORA12CPD1 auxiliary destination '/u01/app/oracle/oradata';
 7> alter pluggable database ORA12CPD1 open resetlogs;
 8> }

executing command: SET until clause

allocated channel: dev_0
channel dev_0: SID=8 device type=SBT_TAPE
channel dev_0: Data Protector A.09.00/110

Starting restore at 27-JUN-17

skipping datafile 9; already restored to file /u01/app/oracle/oradata/ORA12CP/52E1B5675376582DE0537904330A562D/datafile/o1_mf_system_do5lkhfq_.dbf
skipping datafile 10; already restored to file /u01/app/oracle/oradata/ORA12CP/52E1B5675376582DE0537904330A562D/datafile/o1_mf_sysaux_do2lvkg6_.dbf
skipping datafile 11; already restored to file /u01/app/oracle/oradata/ORA12CP/52E1B5675376582DE0537904330A562D/datafile/o1_mf_undotbs1_do2lvkg7_.dbf
skipping datafile 12; already restored to file /u01/app/oracle/oradata/ORA12CP/52E1B5675376582DE0537904330A562D/datafile/o1_mf_users_do2lvpy8_.dbf
Finished restore at 27-JUN-17

Starting recover at 27-JUN-17

starting media recovery

archived log for thread 1 with sequence 4 is already on disk as file /u01/app/oracle/fast_recovery_area/ORA12cP/ORA12CP/archivelog/2017_06_26/o1_mf_1_4_do314vlc_.arc
archived log for thread 1 with sequence 5 is already on disk as file /u01/app/oracle/fast_recovery_area/ORA12cP/ORA12CP/archivelog/2017_06_26/o1_mf_1_5_do3fzn9p_.arc
archived log for thread 1 with sequence 1 is already on disk as file /u01/app/oracle/fast_recovery_area/ORA12cP/ORA12CP/archivelog/2017_06_27/o1_mf_1_1_do5cx8k2_.arc
archived log for thread 1 with sequence 2 is already on disk as file /u01/app/oracle/fast_recovery_area/ORA12cP/ORA12CP/archivelog/2017_06_27/o1_mf_1_2_do5cx8x2_.arc
archived log for thread 1 with sequence 1 is already on disk as file /u01/app/oracle/fast_recovery_area/ORA12cP/ORA12CP/archivelog/2017_06_27/o1_mf_1_1_do5dh20l_.arc
archived log for thread 1 with sequence 2 is already on disk as file /u01/app/oracle/fast_recovery_area/ORA12cP/ORA12CP/archivelog/2017_06_27/o1_mf_1_2_do5ndy7b_.arc
channel dev_0: starting archived log restore to default destination
channel dev_0: restoring archived log
archived log thread=1 sequence=2
channel dev_0: reading from backup piece ora12cpt<ORA12cP_18:947689444:1>.dbf
channel dev_0: piece handle=ora12cpt<ORA12cP_18:947689444:1>.dbf tag=TAG20170626T150404
channel dev_0: restored backup piece 1
channel dev_0: restore complete, elapsed time: 00:00:45
channel default: deleting archived log(s)
archived log file name=/u01/app/oracle/fast_recovery_area/ORA12cP/ORA12CP/archivelog/2017_06_27/o1_mf_1_2_do5nns56_.arc RECID=50 STAMP=947785690
channel dev_0: starting archived log restore to default destination
channel dev_0: restoring archived log
archived log thread=1 sequence=3
channel dev_0: reading from backup piece ora12cpt<ORA12cP_19:947689447:1>.dbf
channel dev_0: piece handle=ora12cpt<ORA12cP_19:947689447:1>.dbf tag=TAG20170626T150404
channel dev_0: restored backup piece 1
channel dev_0: restore complete, elapsed time: 00:00:35
channel default: deleting archived log(s)
archived log file name=/u01/app/oracle/fast_recovery_area/ORA12cP/ORA12CP/archivelog/2017_06_27/o1_mf_1_3_do5np4dk_.arc RECID=51 STAMP=947785732
media recovery complete, elapsed time: 00:00:06
Finished recover at 27-JUN-17

Statement processed
released channel: dev_0

Restore Database with PDB PITR Oracle 12c

1 Introduction

The next document will describe the steps to restore database with CDB and PDB point in time using Data protector and RMAN.

 

2.  Restore Database with PDB PITR using command prompt (Best/Preferred way)

 

[oracle@orclbkp admin]$ rman target /

Recovery Manager: Release 12.2.0.1.0 – Production on Mon Jun 26 21:32:29 2017

Copyright (c) 1982, 2017, Oracle and/or its affiliates. All rights reserved.

connected to target database: ORA12CP (DBID=1429060559, not open)

RMAN> run {
 allocate channel 'dev_0' type 'sbt_tape'
 2> 3> parms 'SBT_LIBRARY=/opt/omni/lib/libob2oracle8_64bit.so,ENV=(OB2BARTYPE=Oracle8,OB2APPNAME=ORA12cP,OB2BARLIST=1498520660,OB2BARHOSTNAME=orclbkp.tdeprdcl.internal)';
 4> set until time "to_date('06/26/17 21:07:41','MM/DD/YY HH24:MI:SS')";
 restore database;
 5> 6> recover database;
 alter database open resetlogs;
 7> 8> }

using target database control file instead of recovery catalog
allocated channel: dev_0
channel dev_0: SID=40 device type=SBT_TAPE
channel dev_0: Data Protector A.09.00/110

executing command: SET until clause

Starting restore at 26-JUN-17

skipping datafile 5; already restored to file /u01/app/oracle/oradata/ORA12CP/datafile/o1_mf_system_do2ljv4y_.dbf
skipping datafile 6; already restored to file /u01/app/oracle/oradata/ORA12CP/datafile/o1_mf_sysaux_do2ljv4q_.dbf
skipping datafile 8; already restored to file /u01/app/oracle/oradata/ORA12CP/datafile/o1_mf_undotbs1_do2ljv4z_.dbf
channel dev_0: starting datafile backup set restore
channel dev_0: specifying datafile(s) to restore from backup set
channel dev_0: restoring datafile 00001 to /u01/app/oracle/oradata/ORA12CP/datafile/o1_mf_system_do2lf6n8_.dbf
channel dev_0: reading from backup piece ora12cpt<ORA12cP_2:947689345:1>.dbf
channel dev_0: piece handle=ora12cpt<ORA12cP_2:947689345:1>.dbf tag=TAG20170626T150225
channel dev_0: restored backup piece 1
channel dev_0: restore complete, elapsed time: 00:00:46
channel dev_0: starting datafile backup set restore
channel dev_0: specifying datafile(s) to restore from backup set
channel dev_0: restoring datafile 00003 to /u01/app/oracle/oradata/ORA12CP/datafile/o1_mf_sysaux_do2lgmrx_.dbf
channel dev_0: reading from backup piece ora12cpt<ORA12cP_3:947689360:1>.dbf
channel dev_0: piece handle=ora12cpt<ORA12cP_3:947689360:1>.dbf tag=TAG20170626T150225
channel dev_0: restored backup piece 1
channel dev_0: restore complete, elapsed time: 00:00:45
channel dev_0: starting datafile backup set restore
channel dev_0: specifying datafile(s) to restore from backup set
channel dev_0: restoring datafile 00010 to /u01/app/oracle/oradata/ORA12CP/52E1B5675376582DE0537904330A562D/datafile/o1_mf_sysaux_do2lvkg6_.dbf
channel dev_0: reading from backup piece ora12cpt<ORA12cP_4:947689367:1>.dbf
channel dev_0: piece handle=ora12cpt<ORA12cP_4:947689367:1>.dbf tag=TAG20170626T150225
channel dev_0: restored backup piece 1
channel dev_0: restore complete, elapsed time: 00:00:45
channel dev_0: starting datafile backup set restore
channel dev_0: specifying datafile(s) to restore from backup set
channel dev_0: restoring datafile 00014 to /u01/app/oracle/oradata/ORA12CP/52E1B5C5F134583AE0537904330A679A/datafile/o1_mf_sysaux_do2lvqd8_.dbf
channel dev_0: reading from backup piece ora12cpt<ORA12cP_5:947689374:1>.dbf
channel dev_0: piece handle=ora12cpt<ORA12cP_5:947689374:1>.dbf tag=TAG20170626T150225
channel dev_0: restored backup piece 1
channel dev_0: restore complete, elapsed time: 00:00:45
channel dev_0: starting datafile backup set restore
channel dev_0: specifying datafile(s) to restore from backup set
channel dev_0: restoring datafile 00009 to /u01/app/oracle/oradata/ORA12CP/52E1B5675376582DE0537904330A562D/datafile/o1_mf_system_do2lvkfl_.dbf
channel dev_0: reading from backup piece ora12cpt<ORA12cP_8:947689396:1>.dbf
channel dev_0: piece handle=ora12cpt<ORA12cP_8:947689396:1>.dbf tag=TAG20170626T150225
channel dev_0: restored backup piece 1
channel dev_0: restore complete, elapsed time: 00:00:45
channel dev_0: starting datafile backup set restore
channel dev_0: specifying datafile(s) to restore from backup set
channel dev_0: restoring datafile 00013 to /u01/app/oracle/oradata/ORA12CP/52E1B5C5F134583AE0537904330A679A/datafile/o1_mf_system_do2lvqd7_.dbf
channel dev_0: reading from backup piece ora12cpt<ORA12cP_9:947689403:1>.dbf
channel dev_0: piece handle=ora12cpt<ORA12cP_9:947689403:1>.dbf tag=TAG20170626T150225
channel dev_0: restored backup piece 1
channel dev_0: restore complete, elapsed time: 00:00:45
channel dev_0: starting datafile backup set restore
channel dev_0: specifying datafile(s) to restore from backup set
channel dev_0: restoring datafile 00011 to /u01/app/oracle/oradata/ORA12CP/52E1B5675376582DE0537904330A562D/datafile/o1_mf_undotbs1_do2lvkg7_.dbf
channel dev_0: reading from backup piece ora12cpt<ORA12cP_11:947689413:1>.dbf
channel dev_0: piece handle=ora12cpt<ORA12cP_11:947689413:1>.dbf tag=TAG20170626T150225
channel dev_0: restored backup piece 1
channel dev_0: restore complete, elapsed time: 00:00:35
channel dev_0: starting datafile backup set restore
channel dev_0: specifying datafile(s) to restore from backup set
channel dev_0: restoring datafile 00015 to /u01/app/oracle/oradata/ORA12CP/52E1B5C5F134583AE0537904330A679A/datafile/o1_mf_undotbs1_do2lvqd8_.dbf
channel dev_0: reading from backup piece ora12cpt<ORA12cP_12:947689416:1>.dbf
channel dev_0: piece handle=ora12cpt<ORA12cP_12:947689416:1>.dbf tag=TAG20170626T150225
channel dev_0: restored backup piece 1
channel dev_0: restore complete, elapsed time: 00:00:35
channel dev_0: starting datafile backup set restore
channel dev_0: specifying datafile(s) to restore from backup set
channel dev_0: restoring datafile 00004 to /u01/app/oracle/oradata/ORA12CP/datafile/o1_mf_undotbs1_do2lhdxt_.dbf
channel dev_0: reading from backup piece ora12cpt<ORA12cP_13:947689419:1>.dbf
channel dev_0: piece handle=ora12cpt<ORA12cP_13:947689419:1>.dbf tag=TAG20170626T150225
channel dev_0: restored backup piece 1
channel dev_0: restore complete, elapsed time: 00:00:35
channel dev_0: starting datafile backup set restore
channel dev_0: specifying datafile(s) to restore from backup set
channel dev_0: restoring datafile 00007 to /u01/app/oracle/oradata/ORA12CP/datafile/o1_mf_users_do2lhg0j_.dbf
channel dev_0: reading from backup piece ora12cpt<ORA12cP_14:947689422:1>.dbf
channel dev_0: piece handle=ora12cpt<ORA12cP_14:947689422:1>.dbf tag=TAG20170626T150225
channel dev_0: restored backup piece 1
channel dev_0: restore complete, elapsed time: 00:00:35
channel dev_0: starting datafile backup set restore
channel dev_0: specifying datafile(s) to restore from backup set
channel dev_0: restoring datafile 00012 to /u01/app/oracle/oradata/ORA12CP/52E1B5675376582DE0537904330A562D/datafile/o1_mf_users_do2lvpy8_.dbf
channel dev_0: reading from backup piece ora12cpt<ORA12cP_15:947689429:1>.dbf
channel dev_0: piece handle=ora12cpt<ORA12cP_15:947689429:1>.dbf tag=TAG20170626T150225
channel dev_0: restored backup piece 1
channel dev_0: restore complete, elapsed time: 00:00:35
channel dev_0: starting datafile backup set restore
channel dev_0: specifying datafile(s) to restore from backup set
channel dev_0: restoring datafile 00016 to /u01/app/oracle/oradata/ORA12CP/52E1B5C5F134583AE0537904330A679A/datafile/o1_mf_users_do2lvxsx_.dbf
channel dev_0: reading from backup piece ora12cpt<ORA12cP_16:947689433:1>.dbf
channel dev_0: piece handle=ora12cpt<ORA12cP_16:947689433:1>.dbf tag=TAG20170626T150225
channel dev_0: restored backup piece 1
channel dev_0: restore complete, elapsed time: 00:00:35
Finished restore at 26-JUN-17

Starting recover at 26-JUN-17

starting media recovery

archived log for thread 1 with sequence 4 is already on disk as file /u01/app/oracle/fast_recovery_area/ORA12cP/ORA12CP/archivelog/2017_06_26/o1_mf_1_4_do314vlc_.arc
channel dev_0: starting archived log restore to default destination
channel dev_0: restoring archived log
archived log thread=1 sequence=2
channel dev_0: reading from backup piece ora12cpt<ORA12cP_18:947689444:1>.dbf
channel dev_0: piece handle=ora12cpt<ORA12cP_18:947689444:1>.dbf tag=TAG20170626T150404
channel dev_0: restored backup piece 1
channel dev_0: restore complete, elapsed time: 00:00:45
archived log file name=/u01/app/oracle/fast_recovery_area/ORA12cP/ORA12CP/archivelog/2017_06_26/o1_mf_1_2_do3fxw63_.arc thread=1 sequence=2
channel default: deleting archived log(s)
archived log file name=/u01/app/oracle/fast_recovery_area/ORA12cP/ORA12CP/archivelog/2017_06_26/o1_mf_1_2_do3fxw63_.arc RECID=4 STAMP=947713277
media recovery complete, elapsed time: 00:00:10
channel dev_0: starting archived log restore to default destination
channel dev_0: restoring archived log
archived log thread=1 sequence=3
channel dev_0: reading from backup piece ora12cpt<ORA12cP_19:947689447:1>.dbf
channel dev_0: piece handle=ora12cpt<ORA12cP_19:947689447:1>.dbf tag=TAG20170626T150404
channel dev_0: restored backup piece 1
channel dev_0: restore complete, elapsed time: 00:00:35
channel default: deleting archived log(s)
archived log file name=/u01/app/oracle/fast_recovery_area/ORA12cP/ORA12CP/archivelog/2017_06_26/o1_mf_1_3_do3fzm9t_.arc RECID=5 STAMP=947713331
Finished recover at 26-JUN-17

Statement processed
released channel: dev_0

Database point in time recovery using Data protector and RMAN

1 Introduction

The next document will describe the steps to restore database point in time using Data protector and RMAN

 

2. Restore using data protector console

 

2.1. Restore screen shot

 

 

 

3.  Restore Database PITR using command prompt (Best/Preferred way)

 

3.1  Restore database 1st incarnation (No need to reset incarnation)

 

[oracle@cdv1pradmdbv01 ~]$ rman target /

Recovery Manager: Release 12.2.0.1.0 – Production on Fri Jun 23 17:38:28 2017

Copyright (c) 1982, 2017, Oracle and/or its affiliates. All rights reserved.

connected to target database: BKPTST (not mounted)

RMAN> run {
2> allocate channel ‘dev_0’ type ‘sbt_tape’
3> parms ‘SBT_LIBRARY=/opt/omni/lib/libob2oracle8_64bit.so,ENV=(OB2BARTYPE=Oracle8,OB2APPNAME=BKPTST,OB2BARLIST=1498246968,OB2BARHOSTNAME=cdv1pradmdbv01.tdeprdcl.internal)’;
4> set until time “to_date(’06/23/17 16:41:54′,’MM/DD/YY HH24:MI:SS’)”;
5> restore database;
6> recover database;
7> alter database open resetlogs;
8> }

using target database control file instead of recovery catalog
allocated channel: dev_0
channel dev_0: SID=1 device type=SBT_TAPE
channel dev_0: Data Protector A.09.00/110

executing command: SET until clause

Starting restore at 23-JUN-17
released channel: dev_0
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of restore command at 06/23/2017 17:38:32
ORA-01507: database not mounted

RMAN> alter database mount;

Statement processed

RMAN> run {
2> allocate channel ‘dev_0’ type ‘sbt_tape’
3> parms ‘SBT_LIBRARY=/opt/omni/lib/libob2oracle8_64bit.so,ENV=(OB2BARTYPE=Oracle8,OB2APPNAME=BKPTST,OB2BARLIST=1498246968,OB2BARHOSTNAME=cdv1pradmdbv01.tdeprdcl.internal)’;
4> set until time “to_date(’06/23/17 16:41:54′,’MM/DD/YY HH24:MI:SS’)”;
5> restore database;
6> recover database;
7> alter database open resetlogs;
8> }

allocated channel: dev_0
channel dev_0: SID=36 device type=SBT_TAPE
channel dev_0: Data Protector A.09.00/110

executing command: SET until clause

Starting restore at 23-JUN-17

creating datafile file number=5 name=/u01/app/oracle/oradata/BKPTST/datafile/o1_mf_test1_dntyvk81_.dbf
channel dev_0: starting datafile backup set restore
channel dev_0: specifying datafile(s) to restore from backup set
channel dev_0: restoring datafile 00001 to /u01/app/oracle/oradata/BKPTST/system01.dbf
channel dev_0: reading from backup piece bkptst_new<BKPTST_15:947418586:1>.dbf
channel dev_0: piece handle=bkptst_new<BKPTST_15:947418586:1>.dbf tag=TAG20170623T114946
channel dev_0: restored backup piece 1
channel dev_0: restore complete, elapsed time: 00:00:45
channel dev_0: starting datafile backup set restore
channel dev_0: specifying datafile(s) to restore from backup set
channel dev_0: restoring datafile 00003 to /u01/app/oracle/oradata/BKPTST/sysaux01.dbf
channel dev_0: reading from backup piece bkptst_new<BKPTST_16:947418601:1>.dbf
channel dev_0: piece handle=bkptst_new<BKPTST_16:947418601:1>.dbf tag=TAG20170623T114946
channel dev_0: restored backup piece 1
channel dev_0: restore complete, elapsed time: 00:00:45
channel dev_0: starting datafile backup set restore
channel dev_0: specifying datafile(s) to restore from backup set
channel dev_0: restoring datafile 00004 to /u01/app/oracle/oradata/BKPTST/undotbs01.dbf
channel dev_0: reading from backup piece bkptst_new<BKPTST_17:947418608:1>.dbf
channel dev_0: piece handle=bkptst_new<BKPTST_17:947418608:1>.dbf tag=TAG20170623T114946
channel dev_0: restored backup piece 1
channel dev_0: restore complete, elapsed time: 00:00:45
channel dev_0: starting datafile backup set restore
channel dev_0: specifying datafile(s) to restore from backup set
channel dev_0: restoring datafile 00007 to /u01/app/oracle/oradata/BKPTST/users01.dbf
channel dev_0: reading from backup piece bkptst_new<BKPTST_18:947418611:1>.dbf
channel dev_0: piece handle=bkptst_new<BKPTST_18:947418611:1>.dbf tag=TAG20170623T114946
channel dev_0: restored backup piece 1
channel dev_0: restore complete, elapsed time: 00:00:35
Finished restore at 23-JUN-17

Starting recover at 23-JUN-17

starting media recovery
media recovery complete, elapsed time: 00:00:01

Finished recover at 23-JUN-17

Statement processed
released channel: dev_0

 

3.2  Now we need to restore database older incarnation than above (For example above restoration happened till 16.41.54 PM now this restoration will happen till 16.41.04)

 

RMAN> run {
2> allocate channel ‘dev_0’ type ‘sbt_tape’
3> parms ‘SBT_LIBRARY=/opt/omni/lib/libob2oracle8_64bit.so,ENV=(OB2BARTYPE=Oracle8,OB2APPNAME=BKPTST,OB2BARLIST=1498246968,OB2BARHOSTNAME=cdv1pradmdbv01.tdeprdcl.internal)’;
4> set until time “to_date(’06/23/17 16:41:04′,’MM/DD/YY HH24:MI:SS’)”;
restore database;
5> 6> recover database;
7> alter database open resetlogs;
8> }

using target database control file instead of recovery catalog
allocated channel: dev_0
channel dev_0: SID=41 device type=SBT_TAPE
channel dev_0: Data Protector A.09.00/110

executing command: SET until clause

Starting restore at 23-JUN-17
released channel: dev_0
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of restore command at 06/23/2017 17:47:14
RMAN-20207: UNTIL TIME or RECOVERY WINDOW is before RESETLOGS time

 

You will get the error as we have already have new incarnation in database. So we need to reset the incarnation.

 

[oracle@cdv1pradmdbv01 ~]$ rman target /

Recovery Manager: Release 12.2.0.1.0 – Production on Fri Jun 23 17:48:01 2017

Copyright (c) 1982, 2017, Oracle and/or its affiliates. All rights reserved.

connected to target database: BKPTST (DBID=2013020542, not open)

RMAN> list incarnation;

using target database control file instead of recovery catalog

List of Database Incarnations
DB Key Inc Key DB Name DB ID STATUS Reset SCN Reset Time
——- ——- ——– —————- — ———- ———-
1 1 BKPTST 2013020542 PARENT 1 26-JAN-17
2 2 BKPTST 2013020542 PARENT 1408558 22-JUN-17
3 3 BKPTST 2013020542 CURRENT 1511521 23-JUN-17

RMAN> reset database to incarnation 2;

database reset to incarnation 2

RMAN> exit

Restoration will now work fine

[oracle@cdv1pradmdbv01 ~]$ rman target /

Recovery Manager: Release 12.2.0.1.0 – Production on Fri Jun 23 17:49:33 2017

Copyright (c) 1982, 2017, Oracle and/or its affiliates. All rights reserved.

connected to target database: BKPTST (DBID=2013020542, not open)

RMAN> run {
2> allocate channel ‘dev_0’ type ‘sbt_tape’
3> parms ‘SBT_LIBRARY=/opt/omni/lib/libob2oracle8_64bit.so,ENV=(OB2BARTYPE=Oracle8,OB2APPNAME=BKPTST,OB2BARLIST=1498246968,OB2BARHOSTNAME=cdv1pradmdbv01.tdeprdcl.internal)’;
4> set until time “to_date(’06/23/17 16:41:04′,’MM/DD/YY HH24:MI:SS’)”;
5> restore database;
6> recover database;
7> alter database open resetlogs;
}8>

using target database control file instead of recovery catalog
allocated channel: dev_0
channel dev_0: SID=1 device type=SBT_TAPE
channel dev_0: Data Protector A.09.00/110

executing command: SET until clause

Starting restore at 23-JUN-17

creating datafile file number=5 name=/u01/app/oracle/oradata/BKPTST/datafile/o1_mf_test1_dntyvk81_.dbf
channel dev_0: starting datafile backup set restore
channel dev_0: specifying datafile(s) to restore from backup set
channel dev_0: restoring datafile 00001 to /u01/app/oracle/oradata/BKPTST/system01.dbf
channel dev_0: reading from backup piece bkptst_new<BKPTST_15:947418586:1>.dbf
channel dev_0: piece handle=bkptst_new<BKPTST_15:947418586:1>.dbf tag=TAG20170623T114946
channel dev_0: restored backup piece 1
channel dev_0: restore complete, elapsed time: 00:00:45
channel dev_0: starting datafile backup set restore
channel dev_0: specifying datafile(s) to restore from backup set
channel dev_0: restoring datafile 00003 to /u01/app/oracle/oradata/BKPTST/sysaux01.dbf
channel dev_0: reading from backup piece bkptst_new<BKPTST_16:947418601:1>.dbf
channel dev_0: piece handle=bkptst_new<BKPTST_16:947418601:1>.dbf tag=TAG20170623T114946
channel dev_0: restored backup piece 1
channel dev_0: restore complete, elapsed time: 00:00:45
channel dev_0: starting datafile backup set restore
channel dev_0: specifying datafile(s) to restore from backup set
channel dev_0: restoring datafile 00004 to /u01/app/oracle/oradata/BKPTST/undotbs01.dbf
channel dev_0: reading from backup piece bkptst_new<BKPTST_17:947418608:1>.dbf
channel dev_0: piece handle=bkptst_new<BKPTST_17:947418608:1>.dbf tag=TAG20170623T114946
channel dev_0: restored backup piece 1
channel dev_0: restore complete, elapsed time: 00:00:45
channel dev_0: starting datafile backup set restore
channel dev_0: specifying datafile(s) to restore from backup set
channel dev_0: restoring datafile 00007 to /u01/app/oracle/oradata/BKPTST/users01.dbf
channel dev_0: reading from backup piece bkptst_new<BKPTST_18:947418611:1>.dbf
channel dev_0: piece handle=bkptst_new<BKPTST_18:947418611:1>.dbf tag=TAG20170623T114946
channel dev_0: restored backup piece 1
channel dev_0: restore complete, elapsed time: 00:00:35
Finished restore at 23-JUN-17

Starting recover at 23-JUN-17

starting media recovery

archived log for thread 1 with sequence 8 is already on disk as file /u01/app/oracle/product/12.2.0/db_1/dbs/arch1_8_947344385.dbf
channel dev_0: starting archived log restore to default destination
channel dev_0: restoring archived log
archived log thread=1 sequence=6
channel dev_0: reading from backup piece bkptst_new<BKPTST_22:947418639:1>.dbf
channel dev_0: piece handle=bkptst_new<BKPTST_22:947418639:1>.dbf tag=TAG20170623T115024
channel dev_0: restored backup piece 1
channel dev_0: restore complete, elapsed time: 00:00:45
archived log file name=/u01/app/oracle/product/12.2.0/db_1/dbs/arch1_6_947344385.dbf thread=1 sequence=6
channel dev_0: starting archived log restore to default destination
channel dev_0: restoring archived log
archived log thread=1 sequence=7
channel dev_0: reading from backup piece bkptst_new<BKPTST_26:947418651:1>.dbf
channel dev_0: piece handle=bkptst_new<BKPTST_26:947418651:1>.dbf tag=TAG20170623T115024
channel dev_0: restored backup piece 1
channel dev_0: restore complete, elapsed time: 00:00:35
archived log file name=/u01/app/oracle/product/12.2.0/db_1/dbs/arch1_7_947344385.dbf thread=1 sequence=7
archived log file name=/u01/app/oracle/product/12.2.0/db_1/dbs/arch1_8_947344385.dbf thread=1 sequence=8
media recovery complete, elapsed time: 00:00:01
Finished recover at 23-JUN-17

Statement processed
released channel: dev_0

3.3  Now we need to restore database again to 3.1 incarnation (For example above restoration happened till 16.41.04 PM now this restoration will happen till 16.41.54 again)

 

[oracle@cdv1pradmdbv01 ~]$ rman target /

Recovery Manager: Release 12.2.0.1.0 – Production on Fri Jun 23 18:56:47 2017

Copyright (c) 1982, 2017, Oracle and/or its affiliates. All rights reserved.

connected to target database: BKPTST (DBID=2013020542, not open)

RMAN> run {
2> allocate channel ‘dev_0’ type ‘sbt_tape’
3> parms ‘SBT_LIBRARY=/opt/omni/lib/libob2oracle8_64bit.so,ENV=(OB2BARTYPE=Oracle8,OB2APPNAME=BKPTST,OB2BARLIST=1498246968,OB2BARHOSTNAME=cdv1pradmdbv01.tdeprdcl.internal)’;
4> set until time “to_date(’06/23/17 16:41:54′,’MM/DD/YY HH24:MI:SS’)”;
5> restore database;
6> recover database;
7> alter database open resetlogs;
8> }

using target database control file instead of recovery catalog
allocated channel: dev_0
channel dev_0: SID=41 device type=SBT_TAPE
channel dev_0: Data Protector A.09.00/110

executing command: SET until clause

Starting restore at 23-JUN-17
released channel: dev_0
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of restore command at 06/23/2017 18:57:50
RMAN-20207: UNTIL TIME or RECOVERY WINDOW is before RESETLOGS time

You will get the error as we have already have new incarnation in database. So we need to reset the incarnation.

RMAN> list incarnation;

List of Database Incarnations
DB Key Inc Key DB Name DB ID STATUS Reset SCN Reset Time
——- ——- ——– —————- — ———- ———-
1 1 BKPTST 2013020542 PARENT 1 26-JAN-17
2 2 BKPTST 2013020542 PARENT 1408558 22-JUN-17
4 4 BKPTST 2013020542 CURRENT 1511448 23-JUN-17
3 3 BKPTST 2013020542 ORPHAN 1511521 23-JUN-17

RMAN> reset database to incarnation 2;

database reset to incarnation 2

Now restore again

RMAN> run {
2> allocate channel ‘dev_0’ type ‘sbt_tape’
3> parms ‘SBT_LIBRARY=/opt/omni/lib/libob2oracle8_64bit.so,ENV=(OB2BARTYPE=Oracle8,OB2APPNAME=BKPTST,OB2BARLIST=1498246968,OB2BARHOSTNAME=cdv1pradmdbv01.tdeprdcl.internal)’;
4> set until time “to_date(’06/23/17 16:41:54′,’MM/DD/YY HH24:MI:SS’)”;
5> restore database;
6> recover database;
7> alter database open resetlogs;
8> }

allocated channel: dev_0
channel dev_0: SID=41 device type=SBT_TAPE
channel dev_0: Data Protector A.09.00/110

executing command: SET until clause

Starting restore at 23-JUN-17

creating datafile file number=5 name=/u01/app/oracle/oradata/BKPTST/datafile/o1_mf_test1_dntyvk81_.dbf
channel dev_0: starting datafile backup set restore
channel dev_0: specifying datafile(s) to restore from backup set
channel dev_0: restoring datafile 00001 to /u01/app/oracle/oradata/BKPTST/system01.dbf
channel dev_0: reading from backup piece bkptst_new<BKPTST_15:947418586:1>.dbf
channel dev_0: piece handle=bkptst_new<BKPTST_15:947418586:1>.dbf tag=TAG20170623T114946
channel dev_0: restored backup piece 1
channel dev_0: restore complete, elapsed time: 00:00:45
channel dev_0: starting datafile backup set restore
channel dev_0: specifying datafile(s) to restore from backup set
channel dev_0: restoring datafile 00003 to /u01/app/oracle/oradata/BKPTST/sysaux01.dbf
channel dev_0: reading from backup piece bkptst_new<BKPTST_16:947418601:1>.dbf
channel dev_0: piece handle=bkptst_new<BKPTST_16:947418601:1>.dbf tag=TAG20170623T114946
channel dev_0: restored backup piece 1
channel dev_0: restore complete, elapsed time: 00:00:45
channel dev_0: starting datafile backup set restore
channel dev_0: specifying datafile(s) to restore from backup set
channel dev_0: restoring datafile 00004 to /u01/app/oracle/oradata/BKPTST/undotbs01.dbf
channel dev_0: reading from backup piece bkptst_new<BKPTST_17:947418608:1>.dbf
channel dev_0: piece handle=bkptst_new<BKPTST_17:947418608:1>.dbf tag=TAG20170623T114946
channel dev_0: restored backup piece 1
channel dev_0: restore complete, elapsed time: 00:00:45
channel dev_0: starting datafile backup set restore
channel dev_0: specifying datafile(s) to restore from backup set
channel dev_0: restoring datafile 00007 to /u01/app/oracle/oradata/BKPTST/users01.dbf
channel dev_0: reading from backup piece bkptst_new<BKPTST_18:947418611:1>.dbf
channel dev_0: piece handle=bkptst_new<BKPTST_18:947418611:1>.dbf tag=TAG20170623T114946
channel dev_0: restored backup piece 1
channel dev_0: restore complete, elapsed time: 00:00:35
Finished restore at 23-JUN-17

Starting recover at 23-JUN-17

starting media recovery

archived log for thread 1 with sequence 6 is already on disk as file /u01/app/oracle/product/12.2.0/db_1/dbs/arch1_6_947344385.dbf
archived log for thread 1 with sequence 7 is already on disk as file /u01/app/oracle/product/12.2.0/db_1/dbs/arch1_7_947344385.dbf
archived log for thread 1 with sequence 8 is already on disk as file /u01/app/oracle/product/12.2.0/db_1/dbs/arch1_8_947344385.dbf
archived log file name=/u01/app/oracle/product/12.2.0/db_1/dbs/arch1_6_947344385.dbf thread=1 sequence=6
archived log file name=/u01/app/oracle/product/12.2.0/db_1/dbs/arch1_7_947344385.dbf thread=1 sequence=7
archived log file name=/u01/app/oracle/product/12.2.0/db_1/dbs/arch1_8_947344385.dbf thread=1 sequence=8
media recovery complete, elapsed time: 00:00:01
Finished recover at 23-JUN-17

Statement processed
released channel: dev_0

 

NB: If you need to restore backup older than (15 days or control file keep record days) ,then you need to connect to recovery catalog as well.

 

4     .  Restore Database with full, incremental and archive log using command prompt (Best/Preferred way)

 

  • Restore Database

All full backup will be restored

  • Recover Database

All incremental backup will be restored

All archive log backup will be restored

 

[oracle@cdv1pradmdbv01 ~]$ rman target /

Recovery Manager: Release 12.2.0.1.0 – Production on Sun Jun 25 19:21:28 2017

Copyright (c) 1982, 2017, Oracle and/or its affiliates. All rights reserved.

connected to target database: BKPTST (DBID=2013020542, not open)

RMAN> run {
allocate channel ‘dev_0’ type ‘sbt_tape’
2> 3> parms ‘SBT_LIBRARY=/opt/omni/lib/libob2oracle8_64bit.so,ENV=(OB2BARTYPE=Oracle8,OB2APPNAME=BKPTST,OB2BARLIST=1498246968,OB2BARHOSTNAME=cdv1pradmdbv01.tdeprdcl.internal)’;
4> set until time “to_date(’06/25/17 10:41:54′,’MM/DD/YY HH24:MI:SS’)”;
5> restore database;
recover database;
6> 7> alter database open resetlogs;
8> }

using target database control file instead of recovery catalog
allocated channel: dev_0
channel dev_0: SID=41 device type=SBT_TAPE
channel dev_0: Data Protector A.09.00/110

executing command: SET until clause

Starting restore at 25-JUN-17

channel dev_0: starting datafile backup set restore
channel dev_0: specifying datafile(s) to restore from backup set
channel dev_0: restoring datafile 00001 to /u01/app/oracle/oradata/BKPTST/system01.dbf
channel dev_0: reading from backup piece bkptst_new<BKPTST_15:947418586:1>.dbf
channel dev_0: piece handle=bkptst_new<BKPTST_15:947418586:1>.dbf tag=TAG20170623T114946
channel dev_0: restored backup piece 1
channel dev_0: restore complete, elapsed time: 00:00:45
channel dev_0: starting datafile backup set restore
channel dev_0: specifying datafile(s) to restore from backup set
channel dev_0: restoring datafile 00003 to /u01/app/oracle/oradata/BKPTST/sysaux01.dbf
channel dev_0: reading from backup piece bkptst_new<BKPTST_16:947418601:1>.dbf
channel dev_0: piece handle=bkptst_new<BKPTST_16:947418601:1>.dbf tag=TAG20170623T114946
channel dev_0: restored backup piece 1
channel dev_0: restore complete, elapsed time: 00:00:45
channel dev_0: starting datafile backup set restore
channel dev_0: specifying datafile(s) to restore from backup set
channel dev_0: restoring datafile 00004 to /u01/app/oracle/oradata/BKPTST/undotbs01.dbf
channel dev_0: reading from backup piece bkptst_new<BKPTST_17:947418608:1>.dbf
channel dev_0: piece handle=bkptst_new<BKPTST_17:947418608:1>.dbf tag=TAG20170623T114946
channel dev_0: restored backup piece 1
channel dev_0: restore complete, elapsed time: 00:00:45
channel dev_0: starting datafile backup set restore
channel dev_0: specifying datafile(s) to restore from backup set
channel dev_0: restoring datafile 00005 to /u01/app/oracle/oradata/BKPTST/datafile/o1_mf_test1_dntyvk81_.dbf
channel dev_0: reading from backup piece bkptst_new<BKPTST_44:947503937:1>.dbf
channel dev_0: piece handle=bkptst_new<BKPTST_44:947503937:1>.dbf tag=TAG20170624T113155
channel dev_0: restored backup piece 1
channel dev_0: restore complete, elapsed time: 00:00:45
channel dev_0: starting datafile backup set restore
channel dev_0: specifying datafile(s) to restore from backup set
channel dev_0: restoring datafile 00007 to /u01/app/oracle/oradata/BKPTST/users01.dbf
channel dev_0: reading from backup piece bkptst_new<BKPTST_18:947418611:1>.dbf
channel dev_0: piece handle=bkptst_new<BKPTST_18:947418611:1>.dbf tag=TAG20170623T114946
channel dev_0: restored backup piece 1
channel dev_0: restore complete, elapsed time: 00:00:45
Finished restore at 25-JUN-17

Starting recover at 25-JUN-17
channel dev_0: starting incremental datafile backup set restore
channel dev_0: specifying datafile(s) to restore from backup set
destination for restore of datafile 00001: /u01/app/oracle/oradata/BKPTST/system01.dbf
channel dev_0: reading from backup piece bkptst_new<BKPTST_42:947503915:1>.dbf
channel dev_0: piece handle=bkptst_new<BKPTST_42:947503915:1>.dbf tag=TAG20170624T113155
channel dev_0: restored backup piece 1
channel dev_0: restore complete, elapsed time: 00:00:45
channel dev_0: starting incremental datafile backup set restore
channel dev_0: specifying datafile(s) to restore from backup set
destination for restore of datafile 00003: /u01/app/oracle/oradata/BKPTST/sysaux01.dbf
channel dev_0: reading from backup piece bkptst_new<BKPTST_43:947503930:1>.dbf
channel dev_0: piece handle=bkptst_new<BKPTST_43:947503930:1>.dbf tag=TAG20170624T113155
channel dev_0: restored backup piece 1
channel dev_0: restore complete, elapsed time: 00:00:46
channel dev_0: starting incremental datafile backup set restore
channel dev_0: specifying datafile(s) to restore from backup set
destination for restore of datafile 00004: /u01/app/oracle/oradata/BKPTST/undotbs01.dbf
channel dev_0: reading from backup piece bkptst_new<BKPTST_45:947503941:1>.dbf
channel dev_0: piece handle=bkptst_new<BKPTST_45:947503941:1>.dbf tag=TAG20170624T113155
channel dev_0: restored backup piece 1
channel dev_0: restore complete, elapsed time: 00:00:35
channel dev_0: starting incremental datafile backup set restore
channel dev_0: specifying datafile(s) to restore from backup set
destination for restore of datafile 00007: /u01/app/oracle/oradata/BKPTST/users01.dbf
channel dev_0: reading from backup piece bkptst_new<BKPTST_46:947503948:1>.dbf
channel dev_0: piece handle=bkptst_new<BKPTST_46:947503948:1>.dbf tag=TAG20170624T113155
channel dev_0: restored backup piece 1
channel dev_0: restore complete, elapsed time: 00:00:03

starting media recovery

channel dev_0: starting archived log restore to default destination
channel dev_0: restoring archived log
archived log thread=1 sequence=4
channel dev_0: reading from backup piece bkptst_new<BKPTST_49:947503970:1>.dbf
channel dev_0: piece handle=bkptst_new<BKPTST_49:947503970:1>.dbf tag=TAG20170624T113242
channel dev_0: restored backup piece 1
channel dev_0: restore complete, elapsed time: 00:00:45
archived log file name=/u01/app/oracle/product/12.2.0/db_1/dbs/arch1_4_947444893.dbf thread=1 sequence=4
channel dev_0: starting archived log restore to default destination
channel dev_0: restoring archived log
archived log thread=1 sequence=5
channel dev_0: reading from backup piece bkptst_new<BKPTST_50:947503977:1>.dbf
channel dev_0: piece handle=bkptst_new<BKPTST_50:947503977:1>.dbf tag=TAG20170624T113242
channel dev_0: restored backup piece 1
channel dev_0: restore complete, elapsed time: 00:00:35
archived log file name=/u01/app/oracle/product/12.2.0/db_1/dbs/arch1_5_947444893.dbf thread=1 sequence=5
channel dev_0: starting archived log restore to default destination
channel dev_0: restoring archived log
archived log thread=1 sequence=6
channel dev_0: restoring archived log
archived log thread=1 sequence=7
channel dev_0: reading from backup piece New3<BKPTST_53:947531995:1>.dbf
channel dev_0: piece handle=New3<BKPTST_53:947531995:1>.dbf tag=TAG20170624T191955
channel dev_0: restored backup piece 1
channel dev_0: restore complete, elapsed time: 00:00:45
archived log file name=/u01/app/oracle/product/12.2.0/db_1/dbs/arch1_6_947444893.dbf thread=1 sequence=6
archived log file name=/u01/app/oracle/product/12.2.0/db_1/dbs/arch1_7_947444893.dbf thread=1 sequence=7
channel dev_0: starting archived log restore to default destination
channel dev_0: restoring archived log
archived log thread=1 sequence=8
channel dev_0: reading from backup piece bkptst_new<BKPTST_61:947590300:1>.dbf
channel dev_0: piece handle=bkptst_new<BKPTST_61:947590300:1>.dbf tag=TAG20170625T113140
channel dev_0: restored backup piece 1
channel dev_0: restore complete, elapsed time: 00:00:45
archived log file name=/u01/app/oracle/product/12.2.0/db_1/dbs/arch1_8_947444893.dbf thread=1 sequence=8
channel dev_0: starting archived log restore to default destination
channel dev_0: restoring archived log
archived log thread=1 sequence=9
channel dev_0: reading from backup piece bkptst_new<BKPTST_62:947590308:1>.dbf
channel dev_0: piece handle=bkptst_new<BKPTST_62:947590308:1>.dbf tag=TAG20170625T113140
channel dev_0: restored backup piece 1
channel dev_0: restore complete, elapsed time: 00:00:45
archived log file name=/u01/app/oracle/product/12.2.0/db_1/dbs/arch1_9_947444893.dbf thread=1 sequence=9
media recovery complete, elapsed time: 00:00:06
Finished recover at 25-JUN-17

Statement processed
released channel: dev_0

Database corrupt block recovery using RMAN

1 Introduction

The next document will describe the steps to recover corrupt block using RMAN

 

2. Recover block if datafile header is corrupted

 

2.1. Please check the physical corruption

 

[oracle@cdv1pradmdbv01 datafile]$ rman target /

Recovery Manager: Release 12.2.0.1.0 – Production on Mon Jun 26 12:55:06 2017

Copyright (c) 1982, 2017, Oracle and/or its affiliates. All rights reserved.

connected to target database: BKPTST (DBID=2013020542)

RMAN> backup validate check logical datafile 5;

Starting backup at 26-JUN-17
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=28 device type=DISK
RMAN-06169: could not read file header for datafile 5 error reason 7
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of backup command at 06/26/2017 12:55:10
RMAN-06056: could not access datafile 5

 

2.2. Restore and recover the particular tablespace (For example test1)

 

RMAN> run {
2> allocate channel ‘dev_0’ type ‘sbt_tape’
3> parms ‘SBT_LIBRARY=/opt/omni/lib/libob2oracle8_64bit.so,ENV=(OB2BARTYPE=Oracle8,OB2APPNAME=BKPTST,OB2BARLIST=1498246968,OB2BARHOSTNAME=cdv1pradmdbv01.tdeprdcl.internal)’;
4> restore tablespace test1;
5> recover tablespace test1;
6> alter tablespace test1 online;
7> }

released channel: ORA_DISK_1
allocated channel: dev_0
channel dev_0: SID=28 device type=SBT_TAPE
channel dev_0: Data Protector A.09.00/110

Starting restore at 26-JUN-17

channel dev_0: starting datafile backup set restore
channel dev_0: specifying datafile(s) to restore from backup set
channel dev_0: restoring datafile 00005 to /u01/app/oracle/oradata/BKPTST/datafile/o1_mf_test1_dntyvk81_.dbf
channel dev_0: reading from backup piece bkptst_new<BKPTST_44:947503937:1>.dbf
channel dev_0: piece handle=bkptst_new<BKPTST_44:947503937:1>.dbf tag=TAG20170624T113155
channel dev_0: restored backup piece 1
channel dev_0: restore complete, elapsed time: 00:00:45
Finished restore at 26-JUN-17

Starting recover at 26-JUN-17

starting media recovery

archived log for thread 1 with sequence 4 is already on disk as file /u01/app/oracle/product/12.2.0/db_1/dbs/arch1_4_947444893.dbf
archived log for thread 1 with sequence 5 is already on disk as file /u01/app/oracle/product/12.2.0/db_1/dbs/arch1_5_947444893.dbf
archived log for thread 1 with sequence 6 is already on disk as file /u01/app/oracle/product/12.2.0/db_1/dbs/arch1_6_947444893.dbf
archived log for thread 1 with sequence 7 is already on disk as file /u01/app/oracle/product/12.2.0/db_1/dbs/arch1_7_947444893.dbf
archived log for thread 1 with sequence 8 is already on disk as file /u01/app/oracle/product/12.2.0/db_1/dbs/arch1_8_947444893.dbf
archived log for thread 1 with sequence 9 is already on disk as file /u01/app/oracle/product/12.2.0/db_1/dbs/arch1_9_947444893.dbf
archived log for thread 1 with sequence 1 is already on disk as file /u01/app/oracle/product/12.2.0/db_1/dbs/arch1_1_947619127.dbf
archived log for thread 1 with sequence 2 is already on disk as file /u01/app/oracle/product/12.2.0/db_1/dbs/arch1_2_947619127.dbf
archived log file name=/u01/app/oracle/product/12.2.0/db_1/dbs/arch1_4_947444893.dbf thread=1 sequence=4
archived log file name=/u01/app/oracle/product/12.2.0/db_1/dbs/arch1_5_947444893.dbf thread=1 sequence=5
archived log file name=/u01/app/oracle/product/12.2.0/db_1/dbs/arch1_6_947444893.dbf thread=1 sequence=6
archived log file name=/u01/app/oracle/product/12.2.0/db_1/dbs/arch1_7_947444893.dbf thread=1 sequence=7
archived log file name=/u01/app/oracle/product/12.2.0/db_1/dbs/arch1_8_947444893.dbf thread=1 sequence=8
archived log file name=/u01/app/oracle/product/12.2.0/db_1/dbs/arch1_9_947444893.dbf thread=1 sequence=9
media recovery complete, elapsed time: 00:00:01
Finished recover at 26-JUN-17

Statement processed
released channel: dev_0

 

 

2.3. Now please validate after restore and recover (For example test1)

 

RMAN> backup validate check logical datafile 5;

Starting backup at 26-JUN-17
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=28 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=00005 name=/u01/app/oracle/oradata/BKPTST/datafile/o1_mf_test1_do2hg0cw_.dbf
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
List of Datafiles
=================
File Status Marked Corrupt Empty Blocks Blocks Examined High SCN
—- —— ————– ———— ————— ———-
5 OK 0 12673 12800 1510201
File Name: /u01/app/oracle/oradata/BKPTST/datafile/o1_mf_test1_do2hg0cw_.dbf
Block Type Blocks Failing Blocks Processed
———- ————– —————-
Data 0 0
Index 0 0
Other 0 127

Finished backup at 26-JUN-17

 

 

3.  Recover corrupt block (Logical corruption)

 

3.1  Detect the logical corruption

 

 RMAN> backup database;

RMAN-03009: failure of backup command on ORA_DISK_1 channel at 06/23/2017 11:27:36
ORA-19566: exceeded limit of 0 corrupt blocks for file +DATA/EAMDB01/DATAFILE/system.266.926328081

$ dbv file=+DATA/EAMDB01/DATAFILE/system.266.926328081

DBVERIFY: Release 12.1.0.2.0 – Production on Fri Jun 23 11:33:22 2017

Copyright (c) 1982, 2014, Oracle and/or its affiliates. All rights reserved.

DBVERIFY – Verification starting : FILE = +DATA/EAMDB01/DATAFILE/system.266.926328081

DBVERIFY – Verification complete

Total Pages Examined : 262144
Total Pages Processed (Data) : 70470
Total Pages Failing (Data) : 0
Total Pages Processed (Index): 15803
Total Pages Failing (Index): 0
Total Pages Processed (Other): 6573
Total Pages Processed (Seg) : 1
Total Pages Failing (Seg) : 0
Total Pages Empty : 169298
Total Pages Marked Corrupt : 0
Total Pages Influx : 0
Total Pages Encrypted : 0
Highest block SCN : 0 (0.0)

 

3.2 Validate using RMAN

 

 

  • Validate the datafile using RMAN:RMAN> backup validate check logical datafile 1;2) After completion of RMAN command, provide output from Sqlplus on connection AS SYSDBA:select * from v$database_block_corruption ;set pagesize 2000
    set linesize 280
    SELECT e.owner, e.segment_type, e.segment_name, e.partition_name, c.file#
    , greatest(e.block_id, c.block#) corr_start_block#
    , least(e.block_id+e.blocks-1, c.block#+c.blocks-1) corr_end_block#
    , least(e.block_id+e.blocks-1, c.block#+c.blocks-1)
    – greatest(e.block_id, c.block#) + 1 blocks_corrupted
    , corruption_type description
    FROM dba_extents e, v$database_block_corruption c
    WHERE e.file_id = c.file#
    AND e.block_id <= c.block# + c.blocks – 1
    AND e.block_id + e.blocks – 1 >= c.block#
    UNION
    SELECT s.owner, s.segment_type, s.segment_name, s.partition_name, c.file#
    , header_block corr_start_block#
    , header_block corr_end_block#
    , 1 blocks_corrupted
    , corruption_type||’ Segment Header’ description
    FROM dba_segments s, v$database_block_corruption c
    WHERE s.header_file = c.file#
    AND s.header_block between c.block# and c.block# + c.blocks – 1
    UNION
    SELECT null owner, null segment_type, null segment_name, null partition_name, c.file#
    , greatest(f.block_id, c.block#) corr_start_block#
    , least(f.block_id+f.blocks-1, c.block#+c.blocks-1) corr_end_block#
    , least(f.block_id+f.blocks-1, c.block#+c.blocks-1)
    – greatest(f.block_id, c.block#) + 1 blocks_corrupted
    , ‘Free Block’ description
    FROM dba_free_space f, v$database_block_corruption c
    WHERE f.file_id = c.file#
    AND f.block_id <= c.block# + c.blocks – 1
    AND f.block_id + f.blocks – 1 >= c.block#
    order by file#, corr_start_block#;

 

 

SQL> select * from v$database_block_corruption ;

FILE# BLOCK# BLOCKS CORRUPTION_CHANGE# CORRUPTIO CON_ID
———- ———- ———- —————— ——— ———-
5 341508 1 0 CORRUPT 0
3 117889 1 0 FRACTURED 0
4 456056 1 0 FRACTURED 0

OWNER SEGMENT_TYPE SEGMENT_NAME
——————————————————————————————————————————– —————— ——————————————————————————————————————————–
PARTITION_NAME FILE# CORR_START_BLOCK# CORR_END_BLOCK# BLOCKS_CORRUPTED DESCRIPTION
——————————————————————————————————————————– ———- —————– ————— —————- ————————
SYS INDEX PARTITION WRH$_SYSSTAT_PK
WRH$_SYSSTA_2631080415_9515 3 117889 117889 1 FRACTURED
4 456056 456056 1 Free Block
5 341508 341508 1 Free Block

 

 

3.3 Repair corrupt block using RMAN

 

ACTION PLAN
============

1) Repair the affected blocks using RMAN:

RMAN> blockrecover corruption list;

2) Validate again to repopulate view v$database_block_corruption

RMAN> backup validate check logical datafile1;

3) Verify the output again from sqlplus to make sure all is clear:

select * from v$database_block_corruption ;

set pagesize 2000
set linesize 280
SELECT e.owner, e.segment_type, e.segment_name, e.partition_name, c.file#
, greatest(e.block_id, c.block#) corr_start_block#
, least(e.block_id+e.blocks-1, c.block#+c.blocks-1) corr_end_block#
, least(e.block_id+e.blocks-1, c.block#+c.blocks-1)
– greatest(e.block_id, c.block#) + 1 blocks_corrupted
, corruption_type description
FROM dba_extents e, v$database_block_corruption c
WHERE e.file_id = c.file#
AND e.block_id <= c.block# + c.blocks – 1
AND e.block_id + e.blocks – 1 >= c.block#
UNION
SELECT s.owner, s.segment_type, s.segment_name, s.partition_name, c.file#
, header_block corr_start_block#
, header_block corr_end_block#
, 1 blocks_corrupted
, corruption_type||’ Segment Header’ description
FROM dba_segments s, v$database_block_corruption c
WHERE s.header_file = c.file#
AND s.header_block between c.block# and c.block# + c.blocks – 1
UNION
SELECT null owner, null segment_type, null segment_name, null partition_name, c.file#
, greatest(f.block_id, c.block#) corr_start_block#
, least(f.block_id+f.blocks-1, c.block#+c.blocks-1) corr_end_block#
, least(f.block_id+f.blocks-1, c.block#+c.blocks-1)
– greatest(f.block_id, c.block#) + 1 blocks_corrupted
, ‘Free Block’ description
FROM dba_free_space f, v$database_block_corruption c
WHERE f.file_id = c.file#
AND f.block_id <= c.block# + c.blocks – 1
AND f.block_id + f.blocks – 1 >= c.block#
order by file#, corr_start_block#;

Clone point in time Recovery using Dataprotector and RMAN for single instance to single instance

1.Login to source database

2. Create temporary pfile from spfile of source database

 

2.1. create temporary pfile from spfile

sqlplus / as sysdba

SQL>create pfile=’/tmp/inittest.ora’ from spfile;

 

 

2.2. Change parameters in init parameter to fit target database

 

Please change all source database name to target database name and create audit_file_dest and control_files loction

 

BKPTSTC.__data_transfer_cache_size=0

BKPTSTC.__db_cache_size=939524096

BKPTSTC.__inmemory_ext_roarea=0

BKPTSTC.__inmemory_ext_rwarea=0

BKPTSTC.__java_pool_size=16777216

BKPTSTC.__large_pool_size=33554432

BKPTSTC.__oracle_base=’/u01/app/oracle’#ORACLE_BASE set from environment

BKPTSTC.__pga_aggregate_target=469762048

BKPTSTC.__sga_target=1375731712

BKPTSTC.__shared_io_pool_size=67108864

BKPTSTC.__shared_pool_size=301989888

BKPTSTC.__streams_pool_size=0

*.audit_file_dest=’/u01/app/oracle/admin/BKPTSTC/adump’

*.audit_trail=’db’

*.compatible=’12.2.0′

*.control_files=’/u01/app/oracle/oradata/BKPTSTC/control01.ctl’,’/u01/app/oracle/oradata/BKPTSTC/control02.ctl’

*.db_block_size=8192

*.db_create_file_dest=’/u01/app/oracle/oradata’

*.db_name=’BKPTSTC’

*.diagnostic_dest=’/u01/app/oracle’

*.dispatchers='(PROTOCOL=TCP) (SERVICE=BKPTSTCXDB)’

*.local_listener=’LISTENER_BKPTSTC’

*.nls_language=’AMERICAN’

*.nls_territory=’AMERICA’

*.open_cursors=300

*.pga_aggregate_target=436m

*.processes=300

*.remote_login_passwordfile=’EXCLUSIVE’

*.sga_target=1305m

*.undo_tablespace=’UNDOTBS1′

3.  Startup no mount the database using temporary parameter file.

 

[oracle@xxx tmp]$ sqlplus / as sysdba

SQL*Plus: Release 12.2.0.1.0 Production on Mon Jun 26 20:07:11 2017

Copyright (c) 1982, 2016, Oracle. All rights reserved.

Connected to:
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 – 64bit Production

SQL> shutdown immediate;
ORA-01109: database not open

Database dismounted.
ORACLE instance shut down.
SQL> startup nomount pfile=’/tmp/inittest.ora’;
ORACLE instance started.

Total System Global Area 1375731712 bytes
Fixed Size 8620560 bytes
Variable Size 469763568 bytes
Database Buffers 889192448 bytes
Redo Buffers 8155136 bytes
SQL> exit

 

4.Clone the database after connecting to source, catalog and auxiliary

 

[oracle@xxx tmp]$ rman target sys/oracle@bkptst catalog rco/rco@botdbadb auxiliary /

Recovery Manager: Release 12.2.0.1.0 – Production on Mon Jun 26 20:09:06 2017

Copyright (c) 1982, 2017, Oracle and/or its affiliates. All rights reserved.

connected to target database: BKPTST (DBID=2013020542)
connected to recovery catalog database
connected to auxiliary database: BKPTSTC (not mounted)

RMAN> run {
set until time = “TO_DATE(’06/26/2017 11:59:00′,’MM/DD/YYYY HH24:MI:SS’)” ;
2> 3> configure auxiliary channel 1 device type sbt parms ‘SBT_LIBRARY=/opt/omni/lib/libob2oracle8_64bit.so,ENV=(OB2BARTYPE=Oracle8,OB2APPNAME=BKPTST,OB2BARLIST=1498246968,OB2BARHOSTNAME=cdv1pradmdbv01.tdeprdcl.internal)’;
4> DUPLICATE TARGET DATABASE TO BKPTSTC
5> logfile
6> group 1 (‘/u01/app/oracle/oradata/BKPTSTC/redolog1a.log’) size 512M,
7> group 2 (‘/u01/app/oracle/oradata/BKPTSTC/redolog2a.log’) size 512M,
8> group 3 (‘/u01/app/oracle/oradata/BKPTSTC/redolog3a.log’) size 512M;
9> }

executing command: SET until clause

new RMAN configuration parameters:
CONFIGURE AUXILIARY CHANNEL 1 DEVICE TYPE ‘SBT_TAPE’ PARMS ‘SBT_LIBRARY=/opt/omni/lib/libob2oracle8_64bit.so,ENV=(OB2BARTYPE=Oracle8,OB2APPNAME=BKPTST,OB2BARLIST=1498246968,OB2BARHOSTNAME=cdv1pradmdbv01.tdeprdcl.internal)’;
new RMAN configuration parameters are successfully stored
starting full resync of recovery catalog
full resync complete

Starting Duplicate Db at 26-JUN-17
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: SID=35 device type=DISK
allocated channel: ORA_AUX_SBT_TAPE_1
channel ORA_AUX_SBT_TAPE_1: SID=36 device type=SBT_TAPE
channel ORA_AUX_SBT_TAPE_1: Data Protector A.09.00/110

contents of Memory Script:
{
sql clone “create spfile from memory”;
}
executing Memory Script

sql statement: create spfile from memory

contents of Memory Script:
{
shutdown clone immediate;
startup clone nomount;
}
executing Memory Script

Oracle instance shut down

connected to auxiliary database (not started)
Oracle instance started

Total System Global Area 1375731712 bytes

Fixed Size 8620560 bytes
Variable Size 469763568 bytes
Database Buffers 889192448 bytes
Redo Buffers 8155136 bytes

contents of Memory Script:
{
set until scn 1732640;
sql clone “alter system set db_name =
”BKPTST” comment=
”Modified by RMAN duplicate” scope=spfile”;
sql clone “alter system set db_unique_name =
”BKPTSTC” comment=
”Modified by RMAN duplicate” scope=spfile”;
shutdown clone immediate;
startup clone force nomount
restore clone primary controlfile;
alter clone database mount;
}
executing Memory Script

executing command: SET until clause

sql statement: alter system set db_name = ”BKPTST” comment= ”Modified by RMAN duplicate” scope=spfile

sql statement: alter system set db_unique_name = ”BKPTSTC” comment= ”Modified by RMAN duplicate” scope=spfile

Oracle instance shut down

Oracle instance started

Total System Global Area 1375731712 bytes

Fixed Size 8620560 bytes
Variable Size 469763568 bytes
Database Buffers 889192448 bytes
Redo Buffers 8155136 bytes

Starting restore at 26-JUN-17
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: SID=34 device type=DISK
allocated channel: ORA_AUX_SBT_TAPE_1
channel ORA_AUX_SBT_TAPE_1: SID=35 device type=SBT_TAPE
channel ORA_AUX_SBT_TAPE_1: Data Protector A.09.00/110

channel ORA_AUX_SBT_TAPE_1: starting datafile backup set restore
channel ORA_AUX_SBT_TAPE_1: restoring control file
channel ORA_AUX_SBT_TAPE_1: reading from backup piece c-2013020542-20170626-00
channel ORA_AUX_SBT_TAPE_1: piece handle=c-2013020542-20170626-00 tag=TAG20170626T113217
channel ORA_AUX_SBT_TAPE_1: restored backup piece 1
channel ORA_AUX_SBT_TAPE_1: restore complete, elapsed time: 00:00:45
output file name=/u01/app/oracle/oradata/BKPTSTC/control01.ctl
output file name=/u01/app/oracle/oradata/BKPTSTC/control02.ctl
Finished restore at 26-JUN-17

database mounted
datafile 7 not processed because file is offline

contents of Memory Script:
{
set until scn 1732640;
set newname for clone datafile 1 to new;
set newname for clone datafile 3 to new;
set newname for clone datafile 4 to new;
set newname for clone datafile 5 to new;
restore
clone database
skip forever tablespace “USERS” ;
}
executing Memory Script

executing command: SET until clause

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

Starting restore at 26-JUN-17
using channel ORA_AUX_DISK_1
using channel ORA_AUX_SBT_TAPE_1

channel ORA_AUX_DISK_1: restoring datafile 00001
input datafile copy RECID=21 STAMP=947624215 file name=/u01/app/oracle/product/12.2.0/db_1/dbs/data_D-BKPTST_I-2013020542_TS-SYSTEM_FNO-1_23s7n67k
destination for restore of datafile 00001: /u01/app/oracle/oradata/BKPTSTC/datafile/o1_mf_system_%u_.dbf
ORA-19505: failed to identify file “/u01/app/oracle/product/12.2.0/db_1/dbs/data_D-BKPTST_I-2013020542_TS-SYSTEM_FNO-1_23s7n67k”
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory
Additional information: 7
ORA-19600: input file is datafile-copy 0 (/u01/app/oracle/product/12.2.0/db_1/dbs/data_D-BKPTST_I-2013020542_TS-SYSTEM_FNO-1_23s7n67k)
channel ORA_AUX_DISK_1: restoring datafile 00003
input datafile copy RECID=20 STAMP=947624215 file name=/u01/app/oracle/product/12.2.0/db_1/dbs/data_D-BKPTST_I-2013020542_TS-SYSAUX_FNO-3_24s7n67n
destination for restore of datafile 00003: /u01/app/oracle/oradata/BKPTSTC/datafile/o1_mf_sysaux_%u_.dbf
ORA-19505: failed to identify file “/u01/app/oracle/product/12.2.0/db_1/dbs/data_D-BKPTST_I-2013020542_TS-SYSAUX_FNO-3_24s7n67n”
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory
Additional information: 7
ORA-19600: input file is datafile-copy 0 (/u01/app/oracle/product/12.2.0/db_1/dbs/data_D-BKPTST_I-2013020542_TS-SYSAUX_FNO-3_24s7n67n)
channel ORA_AUX_DISK_1: restoring datafile 00004
input datafile copy RECID=19 STAMP=947624215 file name=/u01/app/oracle/product/12.2.0/db_1/dbs/data_D-BKPTST_I-2013020542_TS-UNDOTBS1_FNO-4_26s7n67r
destination for restore of datafile 00004: /u01/app/oracle/oradata/BKPTSTC/datafile/o1_mf_undotbs1_%u_.dbf
ORA-19505: failed to identify file “/u01/app/oracle/product/12.2.0/db_1/dbs/data_D-BKPTST_I-2013020542_TS-UNDOTBS1_FNO-4_26s7n67r”
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory
Additional information: 7
ORA-19600: input file is datafile-copy 0 (/u01/app/oracle/product/12.2.0/db_1/dbs/data_D-BKPTST_I-2013020542_TS-UNDOTBS1_FNO-4_26s7n67r)
channel ORA_AUX_DISK_1: restoring datafile 00005
input datafile copy RECID=17 STAMP=947624215 file name=/u01/app/oracle/product/12.2.0/db_1/dbs/data_D-BKPTST_I-2013020542_TS-TEST1_FNO-5_25s7n67q
destination for restore of datafile 00005: /u01/app/oracle/oradata/BKPTSTC/datafile/o1_mf_test1_%u_.dbf
ORA-19505: failed to identify file “/u01/app/oracle/product/12.2.0/db_1/dbs/data_D-BKPTST_I-2013020542_TS-TEST1_FNO-5_25s7n67q”
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory
Additional information: 7
ORA-19600: input file is datafile-copy 0 (/u01/app/oracle/product/12.2.0/db_1/dbs/data_D-BKPTST_I-2013020542_TS-TEST1_FNO-5_25s7n67q)
failover to previous backup

channel ORA_AUX_SBT_TAPE_1: starting datafile backup set restore
channel ORA_AUX_SBT_TAPE_1: specifying datafile(s) to restore from backup set
channel ORA_AUX_SBT_TAPE_1: restoring datafile 00001 to /u01/app/oracle/oradata/BKPTSTC/datafile/o1_mf_system_%u_.dbf
channel ORA_AUX_SBT_TAPE_1: reading from backup piece bkptst_new<BKPTST_15:947418586:1>.dbf
channel ORA_AUX_SBT_TAPE_1: piece handle=bkptst_new<BKPTST_15:947418586:1>.dbf tag=TAG20170623T114946
channel ORA_AUX_SBT_TAPE_1: restored backup piece 1
channel ORA_AUX_SBT_TAPE_1: restore complete, elapsed time: 00:00:45
channel ORA_AUX_SBT_TAPE_1: starting datafile backup set restore
channel ORA_AUX_SBT_TAPE_1: specifying datafile(s) to restore from backup set
channel ORA_AUX_SBT_TAPE_1: restoring datafile 00003 to /u01/app/oracle/oradata/BKPTSTC/datafile/o1_mf_sysaux_%u_.dbf
channel ORA_AUX_SBT_TAPE_1: reading from backup piece bkptst_new<BKPTST_16:947418601:1>.dbf
channel ORA_AUX_SBT_TAPE_1: piece handle=bkptst_new<BKPTST_16:947418601:1>.dbf tag=TAG20170623T114946
channel ORA_AUX_SBT_TAPE_1: restored backup piece 1
channel ORA_AUX_SBT_TAPE_1: restore complete, elapsed time: 00:00:45
channel ORA_AUX_SBT_TAPE_1: starting datafile backup set restore
channel ORA_AUX_SBT_TAPE_1: specifying datafile(s) to restore from backup set
channel ORA_AUX_SBT_TAPE_1: restoring datafile 00004 to /u01/app/oracle/oradata/BKPTSTC/datafile/o1_mf_undotbs1_%u_.dbf
channel ORA_AUX_SBT_TAPE_1: reading from backup piece bkptst_new<BKPTST_17:947418608:1>.dbf
channel ORA_AUX_SBT_TAPE_1: piece handle=bkptst_new<BKPTST_17:947418608:1>.dbf tag=TAG20170623T114946
channel ORA_AUX_SBT_TAPE_1: restored backup piece 1
channel ORA_AUX_SBT_TAPE_1: restore complete, elapsed time: 00:00:45
channel ORA_AUX_SBT_TAPE_1: starting datafile backup set restore
channel ORA_AUX_SBT_TAPE_1: specifying datafile(s) to restore from backup set
channel ORA_AUX_SBT_TAPE_1: restoring datafile 00005 to /u01/app/oracle/oradata/BKPTSTC/datafile/o1_mf_test1_%u_.dbf
channel ORA_AUX_SBT_TAPE_1: reading from backup piece bkptst_new<BKPTST_44:947503937:1>.dbf
channel ORA_AUX_SBT_TAPE_1: piece handle=bkptst_new<BKPTST_44:947503937:1>.dbf tag=TAG20170624T113155
channel ORA_AUX_SBT_TAPE_1: restored backup piece 1
channel ORA_AUX_SBT_TAPE_1: restore complete, elapsed time: 00:00:45
Finished restore at 26-JUN-17

contents of Memory Script:
{
switch clone datafile all;
}
executing Memory Script

datafile 1 switched to datafile copy
input datafile copy RECID=37 STAMP=947708162 file name=/u01/app/oracle/oradata/BKPTSTC/datafile/o1_mf_system_do38slvl_.dbf
datafile 3 switched to datafile copy
input datafile copy RECID=38 STAMP=947708162 file name=/u01/app/oracle/oradata/BKPTSTC/datafile/o1_mf_sysaux_do38tx8r_.dbf
datafile 4 switched to datafile copy
input datafile copy RECID=39 STAMP=947708162 file name=/u01/app/oracle/oradata/BKPTSTC/datafile/o1_mf_undotbs1_do38wbg8_.dbf
datafile 5 switched to datafile copy
input datafile copy RECID=40 STAMP=947708162 file name=/u01/app/oracle/oradata/BKPTSTC/datafile/o1_mf_test1_do38xrxy_.dbf

contents of Memory Script:
{
set until time “to_date(‘JUN 26 2017 11:59:00’, ‘MON DD YYYY HH24:MI:SS’)”;
recover
clone database
;
}
executing Memory Script

executing command: SET until clause

Starting recover at 26-JUN-17
using channel ORA_AUX_DISK_1
using channel ORA_AUX_SBT_TAPE_1
channel ORA_AUX_SBT_TAPE_1: starting incremental datafile backup set restore
channel ORA_AUX_SBT_TAPE_1: specifying datafile(s) to restore from backup set
destination for restore of datafile 00001: /u01/app/oracle/oradata/BKPTSTC/datafile/o1_mf_system_do38slvl_.dbf
channel ORA_AUX_SBT_TAPE_1: reading from backup piece bkptst_new<BKPTST_42:947503915:1>.dbf
channel ORA_AUX_SBT_TAPE_1: piece handle=bkptst_new<BKPTST_42:947503915:1>.dbf tag=TAG20170624T113155
channel ORA_AUX_SBT_TAPE_1: restored backup piece 1
channel ORA_AUX_SBT_TAPE_1: restore complete, elapsed time: 00:00:45
channel ORA_AUX_SBT_TAPE_1: starting incremental datafile backup set restore
channel ORA_AUX_SBT_TAPE_1: specifying datafile(s) to restore from backup set
destination for restore of datafile 00003: /u01/app/oracle/oradata/BKPTSTC/datafile/o1_mf_sysaux_do38tx8r_.dbf
channel ORA_AUX_SBT_TAPE_1: reading from backup piece bkptst_new<BKPTST_43:947503930:1>.dbf
channel ORA_AUX_SBT_TAPE_1: piece handle=bkptst_new<BKPTST_43:947503930:1>.dbf tag=TAG20170624T113155
channel ORA_AUX_SBT_TAPE_1: restored backup piece 1
channel ORA_AUX_SBT_TAPE_1: restore complete, elapsed time: 00:00:45
channel ORA_AUX_SBT_TAPE_1: starting incremental datafile backup set restore
channel ORA_AUX_SBT_TAPE_1: specifying datafile(s) to restore from backup set
destination for restore of datafile 00004: /u01/app/oracle/oradata/BKPTSTC/datafile/o1_mf_undotbs1_do38wbg8_.dbf
channel ORA_AUX_SBT_TAPE_1: reading from backup piece bkptst_new<BKPTST_45:947503941:1>.dbf
channel ORA_AUX_SBT_TAPE_1: piece handle=bkptst_new<BKPTST_45:947503941:1>.dbf tag=TAG20170624T113155
channel ORA_AUX_SBT_TAPE_1: restored backup piece 1
channel ORA_AUX_SBT_TAPE_1: restore complete, elapsed time: 00:00:35

Executing: alter database datafile 7 offline drop
starting media recovery

archived log for thread 1 with sequence 4 is already on disk as file /u01/app/oracle/product/12.2.0/db_1/dbs/arch1_4_947444893.dbf
archived log for thread 1 with sequence 5 is already on disk as file /u01/app/oracle/product/12.2.0/db_1/dbs/arch1_5_947444893.dbf
archived log for thread 1 with sequence 6 is already on disk as file /u01/app/oracle/product/12.2.0/db_1/dbs/arch1_6_947444893.dbf
archived log for thread 1 with sequence 7 is already on disk as file /u01/app/oracle/product/12.2.0/db_1/dbs/arch1_7_947444893.dbf
archived log for thread 1 with sequence 8 is already on disk as file /u01/app/oracle/product/12.2.0/db_1/dbs/arch1_8_947444893.dbf
archived log for thread 1 with sequence 9 is already on disk as file /u01/app/oracle/product/12.2.0/db_1/dbs/arch1_9_947444893.dbf
archived log for thread 1 with sequence 1 is already on disk as file /u01/app/oracle/product/12.2.0/db_1/dbs/arch1_1_947619127.dbf
archived log for thread 1 with sequence 2 is already on disk as file /u01/app/oracle/product/12.2.0/db_1/dbs/arch1_2_947619127.dbf
archived log for thread 1 with sequence 3 is already on disk as file /u01/app/oracle/product/12.2.0/db_1/dbs/arch1_3_947619127.dbf
archived log file name=/u01/app/oracle/product/12.2.0/db_1/dbs/arch1_4_947444893.dbf thread=1 sequence=4
archived log file name=/u01/app/oracle/product/12.2.0/db_1/dbs/arch1_5_947444893.dbf thread=1 sequence=5
archived log file name=/u01/app/oracle/product/12.2.0/db_1/dbs/arch1_6_947444893.dbf thread=1 sequence=6
archived log file name=/u01/app/oracle/product/12.2.0/db_1/dbs/arch1_7_947444893.dbf thread=1 sequence=7
archived log file name=/u01/app/oracle/product/12.2.0/db_1/dbs/arch1_8_947444893.dbf thread=1 sequence=8
archived log file name=/u01/app/oracle/product/12.2.0/db_1/dbs/arch1_9_947444893.dbf thread=1 sequence=9
archived log file name=/u01/app/oracle/product/12.2.0/db_1/dbs/arch1_1_947619127.dbf thread=1 sequence=1
archived log file name=/u01/app/oracle/product/12.2.0/db_1/dbs/arch1_2_947619127.dbf thread=1 sequence=2
archived log file name=/u01/app/oracle/product/12.2.0/db_1/dbs/arch1_3_947619127.dbf thread=1 sequence=3
media recovery complete, elapsed time: 00:00:28
Finished recover at 26-JUN-17
Oracle instance started

Total System Global Area 1375731712 bytes

Fixed Size 8620560 bytes
Variable Size 469763568 bytes
Database Buffers 889192448 bytes
Redo Buffers 8155136 bytes

contents of Memory Script:
{
sql clone “alter system set db_name =
”BKPTSTC” comment=
”Reset to original value by RMAN” scope=spfile”;
sql clone “alter system reset db_unique_name scope=spfile”;
}
executing Memory Script

sql statement: alter system set db_name = ”BKPTSTC” comment= ”Reset to original value by RMAN” scope=spfile

sql statement: alter system reset db_unique_name scope=spfile
Oracle instance started

Total System Global Area 1375731712 bytes

Fixed Size 8620560 bytes
Variable Size 469763568 bytes
Database Buffers 889192448 bytes
Redo Buffers 8155136 bytes
sql statement: CREATE CONTROLFILE REUSE SET DATABASE “BKPTSTC” RESETLOGS ARCHIVELOG
MAXLOGFILES 16
MAXLOGMEMBERS 3
MAXDATAFILES 100
MAXINSTANCES 8
MAXLOGHISTORY 292
LOGFILE
GROUP 1 ( ‘/u01/app/oracle/oradata/BKPTSTC/redolog1a.log’ ) SIZE 512 M ,
GROUP 2 ( ‘/u01/app/oracle/oradata/BKPTSTC/redolog2a.log’ ) SIZE 512 M ,
GROUP 3 ( ‘/u01/app/oracle/oradata/BKPTSTC/redolog3a.log’ ) SIZE 512 M
DATAFILE
‘/u01/app/oracle/oradata/BKPTSTC/datafile/o1_mf_system_do38slvl_.dbf’
CHARACTER SET AL32UTF8

contents of Memory Script:
{
set newname for clone tempfile 1 to new;
switch clone tempfile all;
catalog clone datafilecopy “/u01/app/oracle/oradata/BKPTSTC/datafile/o1_mf_sysaux_do38tx8r_.dbf”,
“/u01/app/oracle/oradata/BKPTSTC/datafile/o1_mf_undotbs1_do38wbg8_.dbf”,
“/u01/app/oracle/oradata/BKPTSTC/datafile/o1_mf_test1_do38xrxy_.dbf”;
switch clone datafile all;
}
executing Memory Script

executing command: SET NEWNAME

renamed tempfile 1 to /u01/app/oracle/oradata/BKPTSTC/datafile/o1_mf_temp_%u_.tmp in control file

cataloged datafile copy
datafile copy file name=/u01/app/oracle/oradata/BKPTSTC/datafile/o1_mf_sysaux_do38tx8r_.dbf RECID=1 STAMP=947708374
cataloged datafile copy
datafile copy file name=/u01/app/oracle/oradata/BKPTSTC/datafile/o1_mf_undotbs1_do38wbg8_.dbf RECID=2 STAMP=947708374
cataloged datafile copy
datafile copy file name=/u01/app/oracle/oradata/BKPTSTC/datafile/o1_mf_test1_do38xrxy_.dbf RECID=3 STAMP=947708374

datafile 3 switched to datafile copy
input datafile copy RECID=1 STAMP=947708374 file name=/u01/app/oracle/oradata/BKPTSTC/datafile/o1_mf_sysaux_do38tx8r_.dbf
datafile 4 switched to datafile copy
input datafile copy RECID=2 STAMP=947708374 file name=/u01/app/oracle/oradata/BKPTSTC/datafile/o1_mf_undotbs1_do38wbg8_.dbf
datafile 5 switched to datafile copy
input datafile copy RECID=3 STAMP=947708374 file name=/u01/app/oracle/oradata/BKPTSTC/datafile/o1_mf_test1_do38xrxy_.dbf

contents of Memory Script:
{
Alter clone database open resetlogs;
}
executing Memory Script

database opened