Oracle DBA Tips and Techniques

By Arjun Raja

UNPLUG AND PLUG A PLUGGABLE DATABASE

Posted by Arjun Raja on June 11, 2014

POINT IN TIME RECOVERY OF TABLESPACE IN PLUGGABLE DATABASE

TABLESPACE TEST12C

TABLE – TEST

SQL> @loop
16 /

PL/SQL procedure successfully completed.

SQL> select count (*) from test;

COUNT(*)
———-
10000

SQL>

SQL> select dbms_flashback.get_system_change_number from dual;

GET_SYSTEM_CHANGE_NUMBER
————————
2936965

Now add another 10,000 rows…

@loop.sql

SQL> select count (*) from test;

COUNT(*)
———-
20000

SQL>

Now remove datafile of tablespace test to simulate failure

rm /u02/oradata/cdbd1/pdb1d1/test12c01.dbf

Try updating table :

cdbd1:/u01/oracle/scripts> sqlplus sys/Oracle12c@pdb1d1 as sysdba

SQL*Plus: Release 12.1.0.1.0 xroduction on Fri May 9 13:41:40 2014

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

Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 – 64bit xroduction
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

SQL> @loop
16 /
declare
*
ERROR at line 1:
ORA-01116: error in opening database file 12
ORA-01110: data file 12: ‘/u02/oradata/cdbd1/pdb1d1/test12c01.dbf’
ORA-27041: unable to open file
IBM AIX RISC System/6000 Error: 2: No such file or directory
Additional information: 3
Additional information: 4
Additional information: 4194304
ORA-06512: at line 7

SQL>

Now restore and recover tablespace to SCN 2936965 – Total rows must be 10,000 once recovery is complete.

Login to Pluggable Database as sysdba

sqlplus sys/Oracle12c@pdb1d1 as sysdba

SQL> alter tablespace test12c offline immediate;

Tablespace altered.

SQL>

Next Login to CDB ROOT.

cdbd1:/u01/oracle/scripts> rman target /

Recovery Manager: Release 12.1.0.1.0 – xroduction on Fri May 9 14:09:09 2014

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

connected to target database: CDBD1 (DBID=2264962255)

RMAN> RUN {
RECOVER TABLESPACE PDB1D1:TEST12C
UNTIL SCN 2936965
AUXILIARY DESTINATION ‘/u01/oracle/scripts’;
}2> 3> 4> 5>

Starting recover at 09-05-2014 14:09:13
using target database control file instead of recovery catalog
current log archived
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=21 device type=DISK
allocated channel: ORA_DISK_2
channel ORA_DISK_2: SID=132 device type=DISK
RMAN-05026: WARNING: presuming following set of tablespaces applies to specified Point-in-Time

List of tablespaces expected to have UNDO segments
Tablespace SYSTEM
Tablespace UNDOTBS1

Creating automatic instance, with SID=’aDbn’

initialization parameters used for automatic instance:
db_name=CDBD1
db_unique_name=aDbn_pitr_PDB1D1_CDBD1
compatible=12.1.0.0.0
db_block_size=8192
db_files=200
sga_target=1G
processes=80
diagnostic_dest=/u01/oracle
db_create_file_dest=/u01/oracle/scripts
log_archive_dest_1=’location=/u01/oracle/scripts’
enable_pluggable_database=true
_clone_one_pdb_recovery=true
#No auxiliary parameter file used

starting up automatic instance CDBD1

Oracle instance started

Total System Global Area 1068937216 bytes

Fixed Size 2368400 bytes
Variable Size 293604464 bytes
Database Buffers 767557632 bytes
Redo Buffers 5406720 bytes
Automatic instance created
Running TRANSPORT_SET_CHECK on recovery set tablespaces
TRANSPORT_SET_CHECK completed successfully

contents of Memory Script:
{
# set requested point in time
set until scn 2936965;
# restore the controlfile
restore clone controlfile;
# mount the controlfile
sql clone ‘alter database mount clone database’;
# archive current online log
sql ‘alter system archive log current’;
# avoid unnecessary autobackups for structural changes during TSPITR
sql ‘begin dbms_backup_restore.AutoBackupFlag(FALSE); end;’;
}
executing Memory Script

executing command: SET until clause

Starting restore at 09-05-2014 14:09:45
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: SID=79 device type=DISK
allocated channel: ORA_AUX_DISK_2
channel ORA_AUX_DISK_2: SID=109 device type=DISK

channel ORA_AUX_DISK_1: starting datafile backup set restore
channel ORA_AUX_DISK_1: restoring control file
channel ORA_AUX_DISK_1: reading from backup piece /u03/oradata/fast_recovery_area/CDBD1/autobackup/2014_05_09/o1_mf_s_847115717_9prvg5m6_.bkp
channel ORA_AUX_DISK_1: piece handle=/u03/oradata/fast_recovery_area/CDBD1/autobackup/2014_05_09/o1_mf_s_847115717_9prvg5m6_.bkp tag=TAG20140509T135517
channel ORA_AUX_DISK_1: restored backup piece 1
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:01
output file name=/u01/oracle/scripts/CDBD1/controlfile/o1_mf_9prw9brw_.ctl
Finished restore at 09-05-2014 14:09:47

sql statement: alter database mount clone database

sql statement: alter system archive log current

sql statement: begin dbms_backup_restore.AutoBackupFlag(FALSE); end;

contents of Memory Script:
{
# set requested point in time
set until scn 2936965;
# set destinations for recovery set and auxiliary set datafiles
set newname for clone datafile 1 to new;
set newname for clone datafile 5 to new;
set newname for clone datafile 3 to new;
set newname for clone datafile 10 to new;
set newname for clone datafile 11 to new;
set newname for clone tempfile 1 to new;
set newname for clone tempfile 3 to new;
set newname for datafile 12 to
“/u02/oradata/cdbd1/pdb1d1/test12c01.dbf”;
# switch all tempfiles
switch clone tempfile all;
# restore the tablespaces in the recovery set and the auxiliary set
restore clone datafile 1, 5, 3, 10, 11, 12;
switch clone datafile all;
}
executing Memory Script

executing command: SET until clause

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

renamed tempfile 1 to /u01/oracle/scripts/CDBD1/datafile/o1_mf_temp_%u_.tmp in control file
renamed tempfile 3 to /u01/oracle/scripts/CDBD1/datafile/o1_mf_temp_%u_.tmp in control file

Starting restore at 09-05-2014 14:09:53
using channel ORA_AUX_DISK_1
using channel ORA_AUX_DISK_2

channel ORA_AUX_DISK_1: starting datafile backup set restore
channel ORA_AUX_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_AUX_DISK_1: restoring datafile 00005 to /u01/oracle/scripts/CDBD1/datafile/o1_mf_undotbs1_%u_.dbf
channel ORA_AUX_DISK_1: reading from backup piece /u03/oradata/fast_recovery_area/CDBD1/backupset/2014_05_09/o1_mf_nnndf_TAG20140509T110516_9prkhh55_.bkp
channel ORA_AUX_DISK_2: starting datafile backup set restore
channel ORA_AUX_DISK_2: specifying datafile(s) to restore from backup set
channel ORA_AUX_DISK_2: restoring datafile 00001 to /u01/oracle/scripts/CDBD1/datafile/o1_mf_system_%u_.dbf
channel ORA_AUX_DISK_2: reading from backup piece /u03/oradata/fast_recovery_area/CDBD1/backupset/2014_05_09/o1_mf_nnndf_TAG20140509T110516_9prkkw9b_.bkp
channel ORA_AUX_DISK_1: piece handle=/u03/oradata/fast_recovery_area/CDBD1/backupset/2014_05_09/o1_mf_nnndf_TAG20140509T110516_9prkhh55_.bkp tag=TAG20140509T110516
channel ORA_AUX_DISK_1: restored backup piece 1
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:07
channel ORA_AUX_DISK_1: starting datafile backup set restore
channel ORA_AUX_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_AUX_DISK_1: restoring datafile 00010 to /u01/oracle/scripts/CDBD1/datafile/o1_mf_system_%u_.dbf
channel ORA_AUX_DISK_1: restoring datafile 00011 to /u01/oracle/scripts/CDBD1/datafile/o1_mf_sysaux_%u_.dbf
channel ORA_AUX_DISK_1: reading from backup piece /u03/oradata/fast_recovery_area/CDBD1/F8EEE24DA36C006AE0430AFECB4C494F/backupset/2014_05_09/o1_mf_nnndf_TAG20140509T111533_9prl2p47_.bkp
channel ORA_AUX_DISK_1: piece handle=/u03/oradata/fast_recovery_area/CDBD1/F8EEE24DA36C006AE0430AFECB4C494F/backupset/2014_05_09/o1_mf_nnndf_TAG20140509T111533_9prl2p47_.bkp tag=TAG20140509T111533
channel ORA_AUX_DISK_1: restored backup piece 1
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:55
channel ORA_AUX_DISK_1: starting datafile backup set restore
channel ORA_AUX_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_AUX_DISK_1: restoring datafile 00012 to /u02/oradata/cdbd1/pdb1d1/test12c01.dbf
channel ORA_AUX_DISK_1: reading from backup piece /u03/oradata/fast_recovery_area/CDBD1/F8EEE24DA36C006AE0430AFECB4C494F/backupset/2014_05_09/o1_mf_nnndf_TAG20140509T125811_9prr33yd_.bkp
channel ORA_AUX_DISK_2: piece handle=/u03/oradata/fast_recovery_area/CDBD1/backupset/2014_05_09/o1_mf_nnndf_TAG20140509T110516_9prkkw9b_.bkp tag=TAG20140509T110516
channel ORA_AUX_DISK_2: restored backup piece 1
channel ORA_AUX_DISK_2: restore complete, elapsed time: 00:01:02
channel ORA_AUX_DISK_2: starting datafile backup set restore
channel ORA_AUX_DISK_2: specifying datafile(s) to restore from backup set
channel ORA_AUX_DISK_2: restoring datafile 00003 to /u01/oracle/scripts/CDBD1/datafile/o1_mf_sysaux_%u_.dbf
channel ORA_AUX_DISK_2: reading from backup piece /u03/oradata/fast_recovery_area/CDBD1/backupset/2014_05_09/o1_mf_nnndf_TAG20140509T125811_9prr33xx_.bkp
channel ORA_AUX_DISK_1: piece handle=/u03/oradata/fast_recovery_area/CDBD1/F8EEE24DA36C006AE0430AFECB4C494F/backupset/2014_05_09/o1_mf_nnndf_TAG20140509T125811_9prr33yd_.bkp tag=TAG20140509T125811
channel ORA_AUX_DISK_1: restored backup piece 1
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:07
channel ORA_AUX_DISK_2: piece handle=/u03/oradata/fast_recovery_area/CDBD1/backupset/2014_05_09/o1_mf_nnndf_TAG20140509T125811_9prr33xx_.bkp tag=TAG20140509T125811
channel ORA_AUX_DISK_2: restored backup piece 1
channel ORA_AUX_DISK_2: restore complete, elapsed time: 00:01:05
Finished restore at 09-05-2014 14:12:01

datafile 1 switched to datafile copy
input datafile copy RECID=6 STAMP=847116721 file name=/u01/oracle/scripts/CDBD1/datafile/o1_mf_system_9prw9l5t_.dbf
datafile 5 switched to datafile copy
input datafile copy RECID=7 STAMP=847116721 file name=/u01/oracle/scripts/CDBD1/datafile/o1_mf_undotbs1_9prw9l8m_.dbf
datafile 3 switched to datafile copy
input datafile copy RECID=8 STAMP=847116721 file name=/u01/oracle/scripts/CDBD1/datafile/o1_mf_sysaux_9prwcjs1_.dbf
datafile 10 switched to datafile copy
input datafile copy RECID=9 STAMP=847116721 file name=/u01/oracle/scripts/CDBD1/datafile/o1_mf_system_9prw9so4_.dbf
datafile 11 switched to datafile copy
input datafile copy RECID=10 STAMP=847116721 file name=/u01/oracle/scripts/CDBD1/datafile/o1_mf_sysaux_9prw9sgr_.dbf

contents of Memory Script:
{
# set requested point in time
set until scn 2936965;
# online the datafiles restored or switched
sql clone “alter database datafile 1 online”;
sql clone “alter database datafile 5 online”;
sql clone “alter database datafile 3 online”;
sql clone ‘PDB1D1’ “alter database datafile
10 online”;
sql clone ‘PDB1D1’ “alter database datafile
11 online”;
sql clone ‘PDB1D1’ “alter database datafile
12 online”;
# recover and open resetlogs
recover clone database tablespace “PDB1D1″:”TEST12C”, “SYSTEM”, “UNDOTBS1”, “SYSAUX”, “PDB1D1″:”SYSTEM”, “PDB1D1″:”SYSAUX” delete archivelog;
alter clone database open resetlogs;
}
executing Memory Script

executing command: SET until clause

sql statement: alter database datafile 1 online

sql statement: alter database datafile 5 online

sql statement: alter database datafile 3 online

sql statement: alter database datafile 10 online

sql statement: alter database datafile 11 online

sql statement: alter database datafile 12 online

Starting recover at 09-05-2014 14:12:02
using channel ORA_AUX_DISK_1
using channel ORA_AUX_DISK_2

starting media recovery

archived log for thread 1 with sequence 202 is already on disk as file /u03/oradata/fast_recovery_area/CDBD1/archivelog/2014_05_09/o1_mf_1_202_9prkt4dm_.arc
archived log for thread 1 with sequence 203 is already on disk as file /u03/oradata/fast_recovery_area/CDBD1/archivelog/2014_05_09/o1_mf_1_203_9prky8qq_.arc
archived log for thread 1 with sequence 204 is already on disk as file /u03/oradata/fast_recovery_area/CDBD1/archivelog/2014_05_09/o1_mf_1_204_9prt0qxj_.arc
archived log for thread 1 with sequence 205 is already on disk as file /u03/oradata/fast_recovery_area/CDBD1/archivelog/2014_05_09/o1_mf_1_205_9prw8c8k_.arc
archived log file name=/u03/oradata/fast_recovery_area/CDBD1/archivelog/2014_05_09/o1_mf_1_202_9prkt4dm_.arc thread=1 sequence=202
archived log file name=/u03/oradata/fast_recovery_area/CDBD1/archivelog/2014_05_09/o1_mf_1_203_9prky8qq_.arc thread=1 sequence=203
archived log file name=/u03/oradata/fast_recovery_area/CDBD1/archivelog/2014_05_09/o1_mf_1_204_9prt0qxj_.arc thread=1 sequence=204
archived log file name=/u03/oradata/fast_recovery_area/CDBD1/archivelog/2014_05_09/o1_mf_1_205_9prw8c8k_.arc thread=1 sequence=205
media recovery complete, elapsed time: 00:00:03
Finished recover at 09-05-2014 14:12:07

database opened

contents of Memory Script:
{
sql clone ‘alter pluggable database PDB1D1 open’;
}
executing Memory Script

sql statement: alter pluggable database PDB1D1 open

contents of Memory Script:
{
# make read only the tablespace that will be exported
sql clone ‘PDB1D1’ ‘alter tablespace
TEST12C read only’;
# create directory for datapump import
sql ‘PDB1D1’ “create or replace directory
TSPITR_DIROBJ_DPDIR as ”
/u01/oracle/scripts””;
# create directory for datapump export
sql clone ‘PDB1D1’ “create or replace directory
TSPITR_DIROBJ_DPDIR as ”
/u01/oracle/scripts””;
}
executing Memory Script

sql statement: alter tablespace TEST12C read only

sql statement: create or replace directory TSPITR_DIROBJ_DPDIR as ”/u01/oracle/scripts”

sql statement: create or replace directory TSPITR_DIROBJ_DPDIR as ”/u01/oracle/scripts”

Performing export of metadata…
EXPDP> Starting “SYS”.”TSPITR_EXP_aDbn_atdu”:
EXPDP> Processing object type TRANSPORTABLE_EXPORT/PLUGTS_BLK
EXPDP> Processing object type TRANSPORTABLE_EXPORT/TABLE
EXPDP> Processing object type TRANSPORTABLE_EXPORT/TABLE_STATISTICS
EXPDP> Processing object type TRANSPORTABLE_EXPORT/STATISTICS/MARKER
EXPDP> Processing object type TRANSPORTABLE_EXPORT/POST_INSTANCE/PLUGTS_BLK
EXPDP> Master table “SYS”.”TSPITR_EXP_aDbn_atdu” successfully loaded/unloaded
EXPDP> ******************************************************************************
EXPDP> Dump file set for SYS.TSPITR_EXP_aDbn_atdu is:
EXPDP> /u01/oracle/scripts/tspitr_aDbn_27853.dmp
EXPDP> ******************************************************************************
EXPDP> Datafiles required for transportable tablespace TEST12C:
EXPDP> /u02/oradata/cdbd1/pdb1d1/test12c01.dbf
EXPDP> Job “SYS”.”TSPITR_EXP_aDbn_atdu” successfully completed at Fri May 9 14:13:49 2014 elapsed 0 00:01:05
Export completed

contents of Memory Script:
{
# shutdown clone before import
shutdown clone abort
# drop target tablespaces before importing them back
sql ‘PDB1D1’ ‘drop tablespace
TEST12C including contents keep datafiles cascade constraints’;
}
executing Memory Script

Oracle instance shut down

sql statement: drop tablespace TEST12C including contents keep datafiles cascade constraints

Performing import of metadata…
IMPDP> Master table “SYS”.”TSPITR_IMP_aDbn_anil” successfully loaded/unloaded
IMPDP> Starting “SYS”.”TSPITR_IMP_aDbn_anil”:
IMPDP> Processing object type TRANSPORTABLE_EXPORT/PLUGTS_BLK
IMPDP> Processing object type TRANSPORTABLE_EXPORT/TABLE
IMPDP> Processing object type TRANSPORTABLE_EXPORT/TABLE_STATISTICS
IMPDP> Processing object type TRANSPORTABLE_EXPORT/STATISTICS/MARKER
IMPDP> Processing object type TRANSPORTABLE_EXPORT/POST_INSTANCE/PLUGTS_BLK
IMPDP> Job “SYS”.”TSPITR_IMP_aDbn_anil” successfully completed at Fri May 9 14:14:52 2014 elapsed 0 00:00:35
Import completed

contents of Memory Script:
{
# make read write and offline the imported tablespaces
sql ‘PDB1D1’ ‘alter tablespace
TEST12C read write’;
sql ‘PDB1D1’ ‘alter tablespace
TEST12C offline’;
# enable autobackups after TSPITR is finished
sql ‘begin dbms_backup_restore.AutoBackupFlag(TRUE); end;’;
}
executing Memory Script

sql statement: alter tablespace TEST12C read write

sql statement: alter tablespace TEST12C offline

sql statement: begin dbms_backup_restore.AutoBackupFlag(TRUE); end;

Removing automatic instance
Automatic instance removed
auxiliary instance file /u01/oracle/scripts/CDBD1/datafile/o1_mf_temp_9prwg2o4_.tmp deleted
auxiliary instance file /u01/oracle/scripts/CDBD1/datafile/o1_mf_temp_9prwft77_.tmp deleted
auxiliary instance file /u01/oracle/scripts/CDBD1/onlinelog/o1_mf_3_9prwfrg6_.log deleted
auxiliary instance file /u01/oracle/scripts/CDBD1/onlinelog/o1_mf_2_9prwfr42_.log deleted
auxiliary instance file /u01/oracle/scripts/CDBD1/onlinelog/o1_mf_1_9prwfqs5_.log deleted
auxiliary instance file /u01/oracle/scripts/CDBD1/datafile/o1_mf_sysaux_9prw9sgr_.dbf deleted
auxiliary instance file /u01/oracle/scripts/CDBD1/datafile/o1_mf_system_9prw9so4_.dbf deleted
auxiliary instance file /u01/oracle/scripts/CDBD1/datafile/o1_mf_sysaux_9prwcjs1_.dbf deleted
auxiliary instance file /u01/oracle/scripts/CDBD1/datafile/o1_mf_undotbs1_9prw9l8m_.dbf deleted
auxiliary instance file /u01/oracle/scripts/CDBD1/datafile/o1_mf_system_9prw9l5t_.dbf deleted
auxiliary instance file /u01/oracle/scripts/CDBD1/controlfile/o1_mf_9prw9brw_.ctl deleted
auxiliary instance file tspitr_aDbn_27853.dmp deleted
Finished recover at 09-05-2014 14:14:56

RMAN> exit

Recovery Manager complete.

Now bring the tablespace online by logging into the Pluggable database as sysdba

cdbd1:/u01/oracle/scripts> sqlplus sys/xxxxxx@pdb1d1 as sysdba

SQL> ALTER TABLESPACE TEST12C ONLINE;

Tablespace altered.

SQL> select count (*) from test;

COUNT(*)
———-
10000

SQL>

Posted in ORACLE 12C CDB-PDB | Leave a Comment »

POINT IN TIME RECOVERY OF TABLESPACE IN PLUGGABLE DATABASE

Posted by Arjun Raja on June 11, 2014

POINT IN TIME RECOVERY OF TABLESPACE IN PLUGGABLE DATABASE

TABLESPACE TEST12C

TABLE – TEST

SQL> @loop
16 /

PL/SQL procedure successfully completed.

SQL> select count (*) from test;

COUNT(*)
———-
10000

SQL>

SQL> select dbms_flashback.get_system_change_number from dual;

GET_SYSTEM_CHANGE_NUMBER
————————
2936965

Now add another 10,000 rows…

@loop.sql

SQL> select count (*) from test;

COUNT(*)
———-
20000

SQL>

Now remove datafile of tablespace test to simulate failure

rm /u02/oradata/cdbd1/pdb1d1/test12c01.dbf

Try updating table :

cdbd1:/u01/oracle/scripts> sqlplus sys/Oracle12c@pdb1d1 as sysdba

SQL*Plus: Release 12.1.0.1.0 xroduction on Fri May 9 13:41:40 2014

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

Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 – 64bit xroduction
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

SQL> @loop
16 /
declare
*
ERROR at line 1:
ORA-01116: error in opening database file 12
ORA-01110: data file 12: ‘/u02/oradata/cdbd1/pdb1d1/test12c01.dbf’
ORA-27041: unable to open file
IBM AIX RISC System/6000 Error: 2: No such file or directory
Additional information: 3
Additional information: 4
Additional information: 4194304
ORA-06512: at line 7

SQL>

Now restore and recover tablespace to SCN 2936965 – Total rows must be 10,000 once recovery is complete.

Login to Pluggable Database as sysdba

sqlplus sys/Oracle12c@pdb1d1 as sysdba

SQL> alter tablespace test12c offline immediate;

Tablespace altered.

SQL>

Next Login to CDB ROOT.

cdbd1:/u01/oracle/scripts> rman target /

Recovery Manager: Release 12.1.0.1.0 – xroduction on Fri May 9 14:09:09 2014

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

connected to target database: CDBD1 (DBID=2264962255)

RMAN> RUN {
RECOVER TABLESPACE PDB1D1:TEST12C
UNTIL SCN 2936965
AUXILIARY DESTINATION ‘/u01/oracle/scripts’;
}2> 3> 4> 5>

Starting recover at 09-05-2014 14:09:13
using target database control file instead of recovery catalog
current log archived
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=21 device type=DISK
allocated channel: ORA_DISK_2
channel ORA_DISK_2: SID=132 device type=DISK
RMAN-05026: WARNING: presuming following set of tablespaces applies to specified Point-in-Time

List of tablespaces expected to have UNDO segments
Tablespace SYSTEM
Tablespace UNDOTBS1

Creating automatic instance, with SID=’aDbn’

initialization parameters used for automatic instance:
db_name=CDBD1
db_unique_name=aDbn_pitr_PDB1D1_CDBD1
compatible=12.1.0.0.0
db_block_size=8192
db_files=200
sga_target=1G
processes=80
diagnostic_dest=/u01/oracle
db_create_file_dest=/u01/oracle/scripts
log_archive_dest_1=’location=/u01/oracle/scripts’
enable_pluggable_database=true
_clone_one_pdb_recovery=true
#No auxiliary parameter file used

starting up automatic instance CDBD1

Oracle instance started

Total System Global Area 1068937216 bytes

Fixed Size 2368400 bytes
Variable Size 293604464 bytes
Database Buffers 767557632 bytes
Redo Buffers 5406720 bytes
Automatic instance created
Running TRANSPORT_SET_CHECK on recovery set tablespaces
TRANSPORT_SET_CHECK completed successfully

contents of Memory Script:
{
# set requested point in time
set until scn 2936965;
# restore the controlfile
restore clone controlfile;
# mount the controlfile
sql clone ‘alter database mount clone database’;
# archive current online log
sql ‘alter system archive log current’;
# avoid unnecessary autobackups for structural changes during TSPITR
sql ‘begin dbms_backup_restore.AutoBackupFlag(FALSE); end;’;
}
executing Memory Script

executing command: SET until clause

Starting restore at 09-05-2014 14:09:45
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: SID=79 device type=DISK
allocated channel: ORA_AUX_DISK_2
channel ORA_AUX_DISK_2: SID=109 device type=DISK

channel ORA_AUX_DISK_1: starting datafile backup set restore
channel ORA_AUX_DISK_1: restoring control file
channel ORA_AUX_DISK_1: reading from backup piece /u03/oradata/fast_recovery_area/CDBD1/autobackup/2014_05_09/o1_mf_s_847115717_9prvg5m6_.bkp
channel ORA_AUX_DISK_1: piece handle=/u03/oradata/fast_recovery_area/CDBD1/autobackup/2014_05_09/o1_mf_s_847115717_9prvg5m6_.bkp tag=TAG20140509T135517
channel ORA_AUX_DISK_1: restored backup piece 1
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:01
output file name=/u01/oracle/scripts/CDBD1/controlfile/o1_mf_9prw9brw_.ctl
Finished restore at 09-05-2014 14:09:47

sql statement: alter database mount clone database

sql statement: alter system archive log current

sql statement: begin dbms_backup_restore.AutoBackupFlag(FALSE); end;

contents of Memory Script:
{
# set requested point in time
set until scn 2936965;
# set destinations for recovery set and auxiliary set datafiles
set newname for clone datafile 1 to new;
set newname for clone datafile 5 to new;
set newname for clone datafile 3 to new;
set newname for clone datafile 10 to new;
set newname for clone datafile 11 to new;
set newname for clone tempfile 1 to new;
set newname for clone tempfile 3 to new;
set newname for datafile 12 to
“/u02/oradata/cdbd1/pdb1d1/test12c01.dbf”;
# switch all tempfiles
switch clone tempfile all;
# restore the tablespaces in the recovery set and the auxiliary set
restore clone datafile 1, 5, 3, 10, 11, 12;
switch clone datafile all;
}
executing Memory Script

executing command: SET until clause

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

renamed tempfile 1 to /u01/oracle/scripts/CDBD1/datafile/o1_mf_temp_%u_.tmp in control file
renamed tempfile 3 to /u01/oracle/scripts/CDBD1/datafile/o1_mf_temp_%u_.tmp in control file

Starting restore at 09-05-2014 14:09:53
using channel ORA_AUX_DISK_1
using channel ORA_AUX_DISK_2

channel ORA_AUX_DISK_1: starting datafile backup set restore
channel ORA_AUX_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_AUX_DISK_1: restoring datafile 00005 to /u01/oracle/scripts/CDBD1/datafile/o1_mf_undotbs1_%u_.dbf
channel ORA_AUX_DISK_1: reading from backup piece /u03/oradata/fast_recovery_area/CDBD1/backupset/2014_05_09/o1_mf_nnndf_TAG20140509T110516_9prkhh55_.bkp
channel ORA_AUX_DISK_2: starting datafile backup set restore
channel ORA_AUX_DISK_2: specifying datafile(s) to restore from backup set
channel ORA_AUX_DISK_2: restoring datafile 00001 to /u01/oracle/scripts/CDBD1/datafile/o1_mf_system_%u_.dbf
channel ORA_AUX_DISK_2: reading from backup piece /u03/oradata/fast_recovery_area/CDBD1/backupset/2014_05_09/o1_mf_nnndf_TAG20140509T110516_9prkkw9b_.bkp
channel ORA_AUX_DISK_1: piece handle=/u03/oradata/fast_recovery_area/CDBD1/backupset/2014_05_09/o1_mf_nnndf_TAG20140509T110516_9prkhh55_.bkp tag=TAG20140509T110516
channel ORA_AUX_DISK_1: restored backup piece 1
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:07
channel ORA_AUX_DISK_1: starting datafile backup set restore
channel ORA_AUX_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_AUX_DISK_1: restoring datafile 00010 to /u01/oracle/scripts/CDBD1/datafile/o1_mf_system_%u_.dbf
channel ORA_AUX_DISK_1: restoring datafile 00011 to /u01/oracle/scripts/CDBD1/datafile/o1_mf_sysaux_%u_.dbf
channel ORA_AUX_DISK_1: reading from backup piece /u03/oradata/fast_recovery_area/CDBD1/F8EEE24DA36C006AE0430AFECB4C494F/backupset/2014_05_09/o1_mf_nnndf_TAG20140509T111533_9prl2p47_.bkp
channel ORA_AUX_DISK_1: piece handle=/u03/oradata/fast_recovery_area/CDBD1/F8EEE24DA36C006AE0430AFECB4C494F/backupset/2014_05_09/o1_mf_nnndf_TAG20140509T111533_9prl2p47_.bkp tag=TAG20140509T111533
channel ORA_AUX_DISK_1: restored backup piece 1
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:55
channel ORA_AUX_DISK_1: starting datafile backup set restore
channel ORA_AUX_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_AUX_DISK_1: restoring datafile 00012 to /u02/oradata/cdbd1/pdb1d1/test12c01.dbf
channel ORA_AUX_DISK_1: reading from backup piece /u03/oradata/fast_recovery_area/CDBD1/F8EEE24DA36C006AE0430AFECB4C494F/backupset/2014_05_09/o1_mf_nnndf_TAG20140509T125811_9prr33yd_.bkp
channel ORA_AUX_DISK_2: piece handle=/u03/oradata/fast_recovery_area/CDBD1/backupset/2014_05_09/o1_mf_nnndf_TAG20140509T110516_9prkkw9b_.bkp tag=TAG20140509T110516
channel ORA_AUX_DISK_2: restored backup piece 1
channel ORA_AUX_DISK_2: restore complete, elapsed time: 00:01:02
channel ORA_AUX_DISK_2: starting datafile backup set restore
channel ORA_AUX_DISK_2: specifying datafile(s) to restore from backup set
channel ORA_AUX_DISK_2: restoring datafile 00003 to /u01/oracle/scripts/CDBD1/datafile/o1_mf_sysaux_%u_.dbf
channel ORA_AUX_DISK_2: reading from backup piece /u03/oradata/fast_recovery_area/CDBD1/backupset/2014_05_09/o1_mf_nnndf_TAG20140509T125811_9prr33xx_.bkp
channel ORA_AUX_DISK_1: piece handle=/u03/oradata/fast_recovery_area/CDBD1/F8EEE24DA36C006AE0430AFECB4C494F/backupset/2014_05_09/o1_mf_nnndf_TAG20140509T125811_9prr33yd_.bkp tag=TAG20140509T125811
channel ORA_AUX_DISK_1: restored backup piece 1
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:07
channel ORA_AUX_DISK_2: piece handle=/u03/oradata/fast_recovery_area/CDBD1/backupset/2014_05_09/o1_mf_nnndf_TAG20140509T125811_9prr33xx_.bkp tag=TAG20140509T125811
channel ORA_AUX_DISK_2: restored backup piece 1
channel ORA_AUX_DISK_2: restore complete, elapsed time: 00:01:05
Finished restore at 09-05-2014 14:12:01

datafile 1 switched to datafile copy
input datafile copy RECID=6 STAMP=847116721 file name=/u01/oracle/scripts/CDBD1/datafile/o1_mf_system_9prw9l5t_.dbf
datafile 5 switched to datafile copy
input datafile copy RECID=7 STAMP=847116721 file name=/u01/oracle/scripts/CDBD1/datafile/o1_mf_undotbs1_9prw9l8m_.dbf
datafile 3 switched to datafile copy
input datafile copy RECID=8 STAMP=847116721 file name=/u01/oracle/scripts/CDBD1/datafile/o1_mf_sysaux_9prwcjs1_.dbf
datafile 10 switched to datafile copy
input datafile copy RECID=9 STAMP=847116721 file name=/u01/oracle/scripts/CDBD1/datafile/o1_mf_system_9prw9so4_.dbf
datafile 11 switched to datafile copy
input datafile copy RECID=10 STAMP=847116721 file name=/u01/oracle/scripts/CDBD1/datafile/o1_mf_sysaux_9prw9sgr_.dbf

contents of Memory Script:
{
# set requested point in time
set until scn 2936965;
# online the datafiles restored or switched
sql clone “alter database datafile 1 online”;
sql clone “alter database datafile 5 online”;
sql clone “alter database datafile 3 online”;
sql clone ‘PDB1D1’ “alter database datafile
10 online”;
sql clone ‘PDB1D1’ “alter database datafile
11 online”;
sql clone ‘PDB1D1’ “alter database datafile
12 online”;
# recover and open resetlogs
recover clone database tablespace “PDB1D1″:”TEST12C”, “SYSTEM”, “UNDOTBS1”, “SYSAUX”, “PDB1D1″:”SYSTEM”, “PDB1D1″:”SYSAUX” delete archivelog;
alter clone database open resetlogs;
}
executing Memory Script

executing command: SET until clause

sql statement: alter database datafile 1 online

sql statement: alter database datafile 5 online

sql statement: alter database datafile 3 online

sql statement: alter database datafile 10 online

sql statement: alter database datafile 11 online

sql statement: alter database datafile 12 online

Starting recover at 09-05-2014 14:12:02
using channel ORA_AUX_DISK_1
using channel ORA_AUX_DISK_2

starting media recovery

archived log for thread 1 with sequence 202 is already on disk as file /u03/oradata/fast_recovery_area/CDBD1/archivelog/2014_05_09/o1_mf_1_202_9prkt4dm_.arc
archived log for thread 1 with sequence 203 is already on disk as file /u03/oradata/fast_recovery_area/CDBD1/archivelog/2014_05_09/o1_mf_1_203_9prky8qq_.arc
archived log for thread 1 with sequence 204 is already on disk as file /u03/oradata/fast_recovery_area/CDBD1/archivelog/2014_05_09/o1_mf_1_204_9prt0qxj_.arc
archived log for thread 1 with sequence 205 is already on disk as file /u03/oradata/fast_recovery_area/CDBD1/archivelog/2014_05_09/o1_mf_1_205_9prw8c8k_.arc
archived log file name=/u03/oradata/fast_recovery_area/CDBD1/archivelog/2014_05_09/o1_mf_1_202_9prkt4dm_.arc thread=1 sequence=202
archived log file name=/u03/oradata/fast_recovery_area/CDBD1/archivelog/2014_05_09/o1_mf_1_203_9prky8qq_.arc thread=1 sequence=203
archived log file name=/u03/oradata/fast_recovery_area/CDBD1/archivelog/2014_05_09/o1_mf_1_204_9prt0qxj_.arc thread=1 sequence=204
archived log file name=/u03/oradata/fast_recovery_area/CDBD1/archivelog/2014_05_09/o1_mf_1_205_9prw8c8k_.arc thread=1 sequence=205
media recovery complete, elapsed time: 00:00:03
Finished recover at 09-05-2014 14:12:07

database opened

contents of Memory Script:
{
sql clone ‘alter pluggable database PDB1D1 open’;
}
executing Memory Script

sql statement: alter pluggable database PDB1D1 open

contents of Memory Script:
{
# make read only the tablespace that will be exported
sql clone ‘PDB1D1’ ‘alter tablespace
TEST12C read only’;
# create directory for datapump import
sql ‘PDB1D1’ “create or replace directory
TSPITR_DIROBJ_DPDIR as ”
/u01/oracle/scripts””;
# create directory for datapump export
sql clone ‘PDB1D1’ “create or replace directory
TSPITR_DIROBJ_DPDIR as ”
/u01/oracle/scripts””;
}
executing Memory Script

sql statement: alter tablespace TEST12C read only

sql statement: create or replace directory TSPITR_DIROBJ_DPDIR as ”/u01/oracle/scripts”

sql statement: create or replace directory TSPITR_DIROBJ_DPDIR as ”/u01/oracle/scripts”

Performing export of metadata…
EXPDP> Starting “SYS”.”TSPITR_EXP_aDbn_atdu”:
EXPDP> Processing object type TRANSPORTABLE_EXPORT/PLUGTS_BLK
EXPDP> Processing object type TRANSPORTABLE_EXPORT/TABLE
EXPDP> Processing object type TRANSPORTABLE_EXPORT/TABLE_STATISTICS
EXPDP> Processing object type TRANSPORTABLE_EXPORT/STATISTICS/MARKER
EXPDP> Processing object type TRANSPORTABLE_EXPORT/POST_INSTANCE/PLUGTS_BLK
EXPDP> Master table “SYS”.”TSPITR_EXP_aDbn_atdu” successfully loaded/unloaded
EXPDP> ******************************************************************************
EXPDP> Dump file set for SYS.TSPITR_EXP_aDbn_atdu is:
EXPDP> /u01/oracle/scripts/tspitr_aDbn_27853.dmp
EXPDP> ******************************************************************************
EXPDP> Datafiles required for transportable tablespace TEST12C:
EXPDP> /u02/oradata/cdbd1/pdb1d1/test12c01.dbf
EXPDP> Job “SYS”.”TSPITR_EXP_aDbn_atdu” successfully completed at Fri May 9 14:13:49 2014 elapsed 0 00:01:05
Export completed

contents of Memory Script:
{
# shutdown clone before import
shutdown clone abort
# drop target tablespaces before importing them back
sql ‘PDB1D1’ ‘drop tablespace
TEST12C including contents keep datafiles cascade constraints’;
}
executing Memory Script

Oracle instance shut down

sql statement: drop tablespace TEST12C including contents keep datafiles cascade constraints

Performing import of metadata…
IMPDP> Master table “SYS”.”TSPITR_IMP_aDbn_anil” successfully loaded/unloaded
IMPDP> Starting “SYS”.”TSPITR_IMP_aDbn_anil”:
IMPDP> Processing object type TRANSPORTABLE_EXPORT/PLUGTS_BLK
IMPDP> Processing object type TRANSPORTABLE_EXPORT/TABLE
IMPDP> Processing object type TRANSPORTABLE_EXPORT/TABLE_STATISTICS
IMPDP> Processing object type TRANSPORTABLE_EXPORT/STATISTICS/MARKER
IMPDP> Processing object type TRANSPORTABLE_EXPORT/POST_INSTANCE/PLUGTS_BLK
IMPDP> Job “SYS”.”TSPITR_IMP_aDbn_anil” successfully completed at Fri May 9 14:14:52 2014 elapsed 0 00:00:35
Import completed

contents of Memory Script:
{
# make read write and offline the imported tablespaces
sql ‘PDB1D1’ ‘alter tablespace
TEST12C read write’;
sql ‘PDB1D1’ ‘alter tablespace
TEST12C offline’;
# enable autobackups after TSPITR is finished
sql ‘begin dbms_backup_restore.AutoBackupFlag(TRUE); end;’;
}
executing Memory Script

sql statement: alter tablespace TEST12C read write

sql statement: alter tablespace TEST12C offline

sql statement: begin dbms_backup_restore.AutoBackupFlag(TRUE); end;

Removing automatic instance
Automatic instance removed
auxiliary instance file /u01/oracle/scripts/CDBD1/datafile/o1_mf_temp_9prwg2o4_.tmp deleted
auxiliary instance file /u01/oracle/scripts/CDBD1/datafile/o1_mf_temp_9prwft77_.tmp deleted
auxiliary instance file /u01/oracle/scripts/CDBD1/onlinelog/o1_mf_3_9prwfrg6_.log deleted
auxiliary instance file /u01/oracle/scripts/CDBD1/onlinelog/o1_mf_2_9prwfr42_.log deleted
auxiliary instance file /u01/oracle/scripts/CDBD1/onlinelog/o1_mf_1_9prwfqs5_.log deleted
auxiliary instance file /u01/oracle/scripts/CDBD1/datafile/o1_mf_sysaux_9prw9sgr_.dbf deleted
auxiliary instance file /u01/oracle/scripts/CDBD1/datafile/o1_mf_system_9prw9so4_.dbf deleted
auxiliary instance file /u01/oracle/scripts/CDBD1/datafile/o1_mf_sysaux_9prwcjs1_.dbf deleted
auxiliary instance file /u01/oracle/scripts/CDBD1/datafile/o1_mf_undotbs1_9prw9l8m_.dbf deleted
auxiliary instance file /u01/oracle/scripts/CDBD1/datafile/o1_mf_system_9prw9l5t_.dbf deleted
auxiliary instance file /u01/oracle/scripts/CDBD1/controlfile/o1_mf_9prw9brw_.ctl deleted
auxiliary instance file tspitr_aDbn_27853.dmp deleted
Finished recover at 09-05-2014 14:14:56

RMAN> exit

Recovery Manager complete.

Now bring the tablespace online by logging into the Pluggable database as sysdba

cdbd1:/u01/oracle/scripts> sqlplus sys/Oracle12c@pdb1d1 as sysdba

SQL> ALTER TABLESPACE TEST12C ONLINE;

Tablespace altered.

SQL> select count (*) from test;

COUNT(*)
———-
10000

SQL>

Posted in ORACLE 12C CDB-PDB | Leave a Comment »

ON STARTUP TRIGGER TO OPEN ALL PLUGGABLE DATABASES

Posted by Arjun Raja on June 11, 2014

ON STARTUP TRIGGER TO OPEN ALL PLUGGABLE DATABASES

Create trigger to open all PDB’s on restart of CDB.

Container database called CDB1D1

Pluggable database called PDB1D1

Shutdown CDB

cdbd1:/u01/oracle> sqlas

SQL*Plus: Release 12.1.0.1.0 Production on Tue May 13 13:39:17 2014

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

Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 – 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

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

Try connecting to PDB

cdbd1:/u01/oracle> sqlplus sys/Oracle12c@pdb1d1 as sysdba

SQL*Plus: Release 12.1.0.1.0 Production on Tue May 13 13:40:07 2014

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

ERROR:
ORA-12514: TNS:listener does not currently know of service requested in connect
descriptor

Enter user-name:

Now startup CDB

cdbd1:/u01/oracle> sqlas

SQL*Plus: Release 12.1.0.1.0 Production on Tue May 13 13:40:31 2014

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

Connected to an idle instance.

SQL> startup;
ORACLE instance started.

Total System Global Area 1603411968 bytes
Fixed Size 2360696 bytes
Variable Size 956304008 bytes
Database Buffers 637534208 bytes
Redo Buffers 7213056 bytes
Database mounted.
Database opened.
SQL>

Check status of PDB.

cdbd1:/u01/oracle> sqlplus sys/Oracle12c@pdb1d1 as sysdba

SQL*Plus: Release 12.1.0.1.0 Production on Tue May 13 13:41:20 2014

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

Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 – 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

SQL> SELECT NAME, OPEN_MODE FROM V$PDBS;

NAME OPEN_MODE
—————————— ———-
PDB1D1 MOUNTED

SQL>

SQL> select file_name from dba_data_files;
select file_name from dba_data_files
*
ERROR at line 1:
ORA-01219: database or pluggable database not open: queries allowed on fixed
tables or views only

SQL>

Therefore if PDB is shutdown when a CDB is shutdown, on restart of CDB, the PDB has to be opened manually.

cdbd1:/u01/oracle> sqlas

SQL> alter pluggable database pdb1d1 open read write;

Pluggable database altered.

SQL> SELECT NAME, OPEN_MODE FROM V$PDBS;

NAME OPEN_MODE
—————————— ———-
PDB1D1 READ WRITE

To avoid this – a trigger can be enabled which starts all PDB’s once a CDB is restarted.

CREATE TRIGGER open_all_PDBs
AFTER STARTUP ON DATABASE
begin
execute immediate ‘alter pluggable database all open’;
end open_all_PDBs;
/

Trigger created.

SQL>

Now shutdown and restart CDB – the PDB should also be opened by the trigger.

SQL> SHUT IMMEDIATE;
Database closed.
Database dismounted.
ORACLE instance shut down.

SQL> STARTUP;
ORACLE instance started.

Total System Global Area 1603411968 bytes
Fixed Size 2360696 bytes
Variable Size 956304008 bytes
Database Buffers 637534208 bytes
Redo Buffers 7213056 bytes
Database mounted.
Database opened.
SQL>

Check if PDB is opened.

cdbd1:/u01/oracle> sqlplus sys/Oracle12c@pdb1d1 as sysdba

SQL> SELECT NAME, OPEN_MODE FROM V$PDBS;

NAME OPEN_MODE
—————————— ———-
PDB1D1 READ WRITE

SQL>

OPENED !

Posted in ORACLE 12C CDB-PDB | Leave a Comment »

RESTORE AND RECOVER NON SYSTEM TABLESPACE IN PLUGGABLE DATABASE

Posted by Arjun Raja on June 11, 2014

RESTORE AND RECOVER NON SYSTEM TABLESPACE IN PLUGGABLE DATABASE.

cdbd1:/u02/oradata/cdbd1/pdb1d1> l
total 4116376
-rw-r—– 1 oracle dba 20979712 May 11 06:02 temp01.dbf
-rw-r—– 1 oracle dba 1073750016 May 12 09:29 test12c01.dbf
-rw-r—– 1 oracle dba 283123712 May 12 10:15 system01.dbf
-rw-r—– 1 oracle dba 749740032 May 12 10:15 sysaux01.dbf

cdbd1:/u02/oradata/cdbd1/pdb1d1> rm test12c01.dbf

cdbd1:/u02/oradata/cdbd1/pdb1d1> sqlplus sys/Oracle12c@pdb1d1 as sysdba

SQL*Plus: Release 12.1.0.1.0 Production on Mon May 12 10:23:49 2014

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

Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 – 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

SQL> select count (*) from test;
select count (*) from test
*
ERROR at line 1:
ORA-01116: error in opening database file 14
ORA-01110: data file 14: ‘/u02/oradata/cdbd1/pdb1d1/test12c01.dbf’
ORA-27041: unable to open file
IBM AIX RISC System/6000 Error: 2: No such file or directory
Additional information: 3
Additional information: 4
Additional information: 4194304

SQL>

Now restore and recover tablespace TEST12C in PDB pdb1d1

First take tablespace offline –

sqlplus sys/xxxxx@pdb1d1 as sysdba

SQL> alter tablespace test12c offline immediate;

Tablespace altered.

SQL>

From CDB root.

RMAN> RESTORE TABLESPACE PDB1D1:TEST12C;

Starting restore at 12-05-2014 10:26:51
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=140 device type=DISK
allocated channel: ORA_DISK_2
channel ORA_DISK_2: SID=14 device type=DISK

channel ORA_DISK_1: starting datafile backup set restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_DISK_1: restoring datafile 00014 to /u02/oradata/cdbd1/pdb1d1/test12c01.dbf
channel ORA_DISK_1: reading from backup piece /u03/oradata/fast_recovery_area/CDBD1/F8EEE24DA36C006AE0430AFECB4C494F/backupset/2014_05_12/o1_mf_nnndf_TAG20140512T085552_9q070rny_.bkp
channel ORA_DISK_1: piece handle=/u03/oradata/fast_recovery_area/CDBD1/F8EEE24DA36C006AE0430AFECB4C494F/backupset/2014_05_12/o1_mf_nnndf_TAG20140512T085552_9q070rny_.bkp tag=TAG20140512T085552
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:07
Finished restore at 12-05-2014 10:27:01

RMAN>

RMAN> RECOVER TABLESPACE PDB1D1:TEST12C;

Starting recover at 12-05-2014 10:27:39
using channel ORA_DISK_1
using channel ORA_DISK_2

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

Finished recover at 12-05-2014 10:27:40
starting full resync of recovery catalog
full resync complete

RMAN>

cdbd1:/u02/oradata/cdbd1/pdb1d1> sqlplus sys/Oracle12c@pdb1d1 as sysdba

SQL*Plus: Release 12.1.0.1.0 Production on Mon May 12 10:28:05 2014

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

Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 – 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

SQL> alter tablespace test12c online;

Tablespace altered.

SQL>

All good.

Posted in Uncategorized | Leave a Comment »

RESTORE AND RECOVER SYSTEM TABLESPACE IN PLUGGABLE DATABASE

Posted by Arjun Raja on June 11, 2014

SYSTEM TABLESPACE RESTORE AND RECOVER IN PLUGGABLE DATABASE

From CDB root backup all databases – CDB and PDB’s.

cdbd1:/u01/oracle> rcat

Recovery Manager: Release 12.1.0.1.0 – Production on Mon May 12 08:45:15 2014

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

connected to target database: CDBD1 (DBID=2264962255)
connected to recovery catalog database

RMAN> backup database plus archivelog;

Next backup only pluggable database.

RMAN> backup pluggable database pdb1d1;

Starting backup at 12-05-2014 08:55:51
using channel ORA_DISK_1
using channel ORA_DISK_2
channel ORA_DISK_1: starting compressed full datafile backup set

channel ORA_DISK_2: backup set complete, elapsed time: 00:00:55
Finished backup at 12-05-2014 08:56:47

Starting Control File and SPFILE Autobackup at 12-05-2014 08:56:47
piece handle=/u03/oradata/fast_recovery_area/CDBD1/autobackup/2014_05_12/o1_mf_s_847357009_9q072kv9_.bkp comment=NONE
Finished Control File and SPFILE Autobackup at 12-05-2014 08:56:50

RMAN>

Now remove system datafile from PDB.

cd /u02/oradata/cdbd1/pdb1d1

cdbd1:/u02/oradata/cdbd1/pdb1d1> rm system01.dbf

cdbd1:/u02/oradata/cdbd1/pdb1d1> sqlplus sys/Oracle12c@pdb1d1 as sysdba

SQL*Plus: Release 12.1.0.1.0 Production on Mon May 12 09:21:02 2014

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

Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 – 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

SQL> select name from v$database;
select name from v$database
*
ERROR at line 1:
ORA-00604: error occurred at recursive SQL level 3
ORA-01116: error in opening database file 10
ORA-01110: data file 10: ‘/u02/oradata/cdbd1/pdb1d1/system01.dbf’
ORA-27041: unable to open file
IBM AIX RISC System/6000 Error: 2: No such file or directory
Additional information: 3
Additional information: 4
Additional information: 4194304

Now try to restore and recover SYSTEM tablespace of PDB with CDB still up and running….

RMAN> run {
2> RESTORE TABLESPACE PDB1D1:SYSTEM;
3> }

Starting restore at 12-05-2014 09:22:25
starting full resync of recovery catalog
full resync complete
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=14 device type=DISK
allocated channel: ORA_DISK_2
channel ORA_DISK_2: SID=138 device type=DISK

channel ORA_DISK_1: starting datafile backup set restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_DISK_1: restoring datafile 00010 to /u02/oradata/cdbd1/pdb1d1/system01.dbf
channel ORA_DISK_1: reading from backup piece /u03/oradata/fast_recovery_area/CDBD1/F8EEE24DA36C006AE0430AFECB4C494F/backupset/2014_05_12/o1_mf_nnndf_TAG20140512T085552_9q070rmy_.bkp
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of restore command at 05/12/2014 09:22:31
ORA-19870: error while restoring backup piece /u03/oradata/fast_recovery_area/CDBD1/F8EEE24DA36C006AE0430AFECB4C494F/backupset/2014_05_12/o1_mf_nnndf_TAG20140512T085552_9q070rmy_.bkp
ORA-19573: cannot obtain exclusive enqueue for datafile 10

RMAN>

So you can’t restore the system tablespace datafile of a PDB if the CDB is still up and running.

In CDB.

Shutdown abort .

If you try shutdown immediate – the following error will appear :

SQL> shut immediate;
startuORA-01116: error in opening database file 10
ORA-01110: data file 10: ‘/u02/oradata/cdbd1/pdb1d1/system01.dbf’
ORA-27041: unable to open file
IBM AIX RISC System/6000 Error: 2: No such file or directory
Additional information: 3
Additional information: 4
Additional information: 4194304

SQL> shutdown abort;
ORACLE instance shut down.

SQL> startup mount;
ORACLE instance started.

Total System Global Area 1603411968 bytes
Fixed Size 2360696 bytes
Variable Size 973081224 bytes
Database Buffers 620756992 bytes
Redo Buffers 7213056 bytes
Database mounted.
SQL>

Now run RMAN recovery of SYSTEM tablespace in Pluggable database.

run
{ RESTORE TABLESPACE PDB1D1:SYSTEM ;
}

Starting restore at 12-05-2014 09:10:15
using channel ORA_DISK_1
using channel ORA_DISK_2

channel ORA_DISK_1: starting datafile backup set restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_DISK_1: restoring datafile 00010 to /u02/oradata/cdbd1/pdb1d1/system01.dbf
channel ORA_DISK_1: reading from backup piece /u03/oradata/fast_recovery_area/CDBD1/F8EEE24DA36C006AE0430AFECB4C494F/backupset/2014_05_12/o1_mf_nnndf_TAG20140512T085552_9q070rmy_.bkp
channel ORA_DISK_1: piece handle=/u03/oradata/fast_recovery_area/CDBD1/F8EEE24DA36C006AE0430AFECB4C494F/backupset/2014_05_12/o1_mf_nnndf_TAG20140512T085552_9q070rmy_.bkp tag=TAG20140512T085552
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:25
Finished restore at 12-05-2014 09:10:41

RUN {
RECOVER TABLESPACE PDB1D1:SYSTEM;
}

Starting recover at 12-05-2014 09:11:13
using channel ORA_DISK_1
using channel ORA_DISK_2

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

Finished recover at 12-05-2014 09:11:14

RMAN>

Now to continue first open the CDB and then open the PDB.

If you try to open the PDB with the CDB in mount state –

RMAN> ALTER PLUGGABLE DATABASE PDB1D1 OPEN READ WRITE;

RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of sql statement command at 05/12/2014 09:25:23
ORA-01109: database not open

RMAN> ALTER DATABASE OPEN;

Statement processed
starting full resync of recovery catalog
full resync complete

RMAN>

Finally open PDB

RMAN> ALTER PLUGGABLE DATABASE PDB1D1 OPEN READ WRITE;

Statement processed
starting full resync of recovery catalog
full resync complete

RMAN>

Posted in ORACLE 12C CDB-PDB | Leave a Comment »

DROP AND RECREATE PLUGGABLE DATABASE

Posted by Arjun Raja on June 11, 2014

DROP AND RECREATE PLUGGABLE DATABASE

I created a pluggable database using this syntax.

CREATE PLUGGABLE DATABASE pdb1d1 ADMIN USER pdb1d1admin IDENTIFIED BY oracle12c
FILE_NAME_CONVERT=(‘/u02/oradata/cdbd1/pdbseed/’,’/u02/oradata/cdbd1/pdb1d1′);

Forgot to put a slash after pdb1d1 – hence the datafiles were created in the same directory as container db.

SQL> select file_name from dba_data_files;

FILE_NAME
——————————————————————————–
/u02/oradata/cdbd1/pdb1d1system01.dbf
/u02/oradata/cdbd1/pdb1d1sysaux01.dbf

To rectify this :

First drop the pluggable database and remove all datafiles .

Connect to CBD

sqlplus / as sysdba

SQL> drop pluggable database pdb1d1 including datafiles;
drop pluggable database pdb1d1 including datafiles
*
ERROR at line 1:
ORA-65025: Pluggable database PDB1D1 is not closed on all instances.

Therefore close the pluggable database first –

SQL> alter pluggable database pdb1d1 close;

Pluggable database altered.

SQL>drop pluggable database pdb1d1 including datafiles;

Pluggable database dropped.

SQL>

Now try logging in to the pluggable database.

cdbd1:/u01/oracle/scripts> sqlplus sys/Oracle12c@pdb1d1 as sysdba

SQL*Plus: Release 12.1.0.1.0 Production on Fri May 9 09:51:23 2014

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

ERROR:
ORA-12514: TNS:listener does not currently know of service requested in connect
descriptor

Enter user-name:
Create the pluggable database again.

CREATE PLUGGABLE DATABASE pdb1d1 ADMIN USER pdb1d1admin IDENTIFIED BY oracle12c
FILE_NAME_CONVERT=(‘/u02/oradata/cdbd1/pdbseed/’,’/u02/oradata/cdbd1/pdb1d1/’);

sqlplus sys/Oracle12c@pdb1d1 as sysdba

SQL> select file_name from dba_Data_files;
select file_name from dba_Data_files
*
ERROR at line 1:
ORA-01219: database or pluggable database not open: queries allowed on fixed
tables or views only

OPEN THE PDB

Login as sysdba into the CDB.

SQL> ALTER PLUGGABLE DATABASE PDB1D1 OPEN READ WRITE;

Pluggable database altered.

SQL>

Now login to the PDB

SQL> select file_name from dba_Data_files;

FILE_NAME
——————————————————————————–
/u02/oradata/cdbd1/pdb1d1/system01.dbf
/u02/oradata/cdbd1/pdb1d1/sysaux01.dbf

SQL>
SQL> select file_name from dba_temp_files;

FILE_NAME
——————————————————————————–
/u02/oradata/cdbd1/pdb1d1/temp01.dbf

SQL>

The alert.log file as a CDB shows new DDL statements such as:

• CREATE PLUGGABLE DATABASE
• ALTER PLUGGABLE DATABASE
• DROP PLUGGABLE DATABASE

This alert log

drop pluggable database pdb1d1 including datafiles
ORA-65025 signalled during: drop pluggable database pdb1d1 including datafiles…
alter pluggable database pdb1d1 close
Fri May 09 09:50:08 2014
ALTER SYSTEM: Flushing buffer cache inst=0 container=3 local
Pluggable database PDB1D1 closed
Completed: alter pluggable database pdb1d1 close
drop pluggable database pdb1d1 including datafiles
Fri May 09 09:50:43 2014
Deleted file /u02/oradata/cdbd1/pdb1d1temp01.dbf
Deleted file /u02/oradata/cdbd1/pdb1d1sysaux01.dbf
Deleted file /u02/oradata/cdbd1/pdb1d1system01.dbf
Completed: drop pluggable database pdb1d1 including datafiles
Fri May 09 09:54:54 2014
CREATE PLUGGABLE DATABASE pdb1d1 ADMIN USER pdb1d1_admin IDENTIFIED BY *FILE_NAME_CONVERT=(‘/u02/oradata/cdbd1/pdbseed/’,’/u02/oradata/cdbd1/pdb1d1/’)
Fri May 09 09:55:07 2014
****************************************************************
Pluggable Database PDB1D1 with pdb id – 3 is created as UNUSABLE.
If any errors are encountered before the pdb is marked as NEW,
then the pdb must be dropped
****************************************************************
Deleting old file#2 from file$
Deleting old file#4 from file$
Adding new file#10 to file$(old file#2)
Adding new file#11 to file$(old file#4)
Successfully created internal service pdb1d1 at open
ALTER SYSTEM: Flushing buffer cache inst=0 container=3 local
****************************************************************
Post plug operations are now complete.
Pluggable database PDB1D1 with pdb id – 3 is now marked as NEW.

Posted in Uncategorized | Leave a Comment »

CREATE PLUGGABLE DATABASE

Posted by Arjun Raja on June 11, 2014

CREATE PLUGGABLE DATABASE

Currently we have a CDB_ROOT and PDB_SEED.

CDB_ROOT is the container database.

PDB_SEED is the template to be used to create new pluggable databases.

Current PDB

— ******************************************
REM Check how many PDBs are in the current container

select
v.name,
v.open_mode,
nvl(v.restricted, ‘n/a’) “RESTRICTED”,
d.status
from
v$pdbs v
inner join
dba_pdbs d
using (GUID)
order by v.create_scn;

NAME OPEN_MODE RESTRICTED STATUS
—————————— ———- ———- ————-
PDB$SEED READ ONLY NO NORMAL

Now create a new Pluggable database using the copy method – copying the pdb_seed.

CREATE PLUGGABLE DATABASE pdb1d1 ADMIN USER pdb1d1admin IDENTIFIED BY oracle12c
FILE_NAME_CONVERT=(‘/u02/oradata/cdbd1/pdbseed/’,’/u02/oradata/cdbd1/pdb1d1′);

Pluggable database created.

select
v.name,
v.open_mode,
nvl(v.restricted, ‘n/a’) “RESTRICTED”,
d.status
from
v$pdbs v
inner join
dba_pdbs d
using (GUID)
order by v.create_scn;

NAME OPEN_MODE RESTRICTED STATUS
—————————— ———- ———- ———————————————-
PDB$SEED READ ONLY NO NORMAL
PDB1D1 MOUNTED n/a NEW

The new pluggable database is still in NEW state.

They must be opened in READ WRITE mode atleast once for the integration of the PDB into CDB to be complete.

ALTER PLUGGABLE DATABASE PDB1D1 OPEN READ WRITE;

Pluggable database altered.

SQL> select
v.name,
v.open_mode,
nvl(v.restricted, ‘n/a’) “RESTRICTED”,
d.status
from
v$pdbs v
inner join
dba_pdbs d
using (GUID)
order by v.create_scn; 2 3 4 5 6 7 8 9 10 11

NAME OPEN_MODE RES STATUS
—————————— ———- — ————-
PDB$SEED READ ONLY NO NORMAL
PDB1D1 READ WRITE NO NORMAL

Posted in ORACLE 12C CDB-PDB | Leave a Comment »

Upgrade Cloud Control OMS and agent to 12.1.0.4.0

Posted by Arjun Raja on June 11, 2014

UPGRADE CLOUD CONTROL TO VERSION 12.1.0.4.0

Posted in Oracle 12c Cloud Control | Tagged: | Leave a Comment »

RECOVERY- POINT IN TIME – PITR – PDB NON SYSTEM TABLESPACE.

Posted by Arjun Raja on May 9, 2014

TABLESPACE TEST12C

TABLE – TEST

SQL> @loop
16 /

PL/SQL procedure successfully completed.

SQL> select count (*) from test;

COUNT(*)
———-
10000

SQL>

SQL> select dbms_flashback.get_system_change_number from dual;

GET_SYSTEM_CHANGE_NUMBER
————————
2936965

Now add another 10,000 rows…

@loop.sql

SQL> select count (*) from test;

COUNT(*)
———-
20000

SQL>

Now remove datafile of tablespace test to simulate failure

/u02/oradata/cdbd1/pdb1d1/test12c01.dbf

mv /u02/oradata/cdbd1/pdb1d1/test12c01.dbf /u02/oradata/cdbd1/pdb1d1/test12c01.dbf.old

Try updating table :

cdbd1:/u01/oracle/scripts> sqlplus sys/Oracle12c@pdb1d1 as sysdba

SQL*Plus: Release 12.1.0.1.0 Production on Fri May 9 13:41:40 2014

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

Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 – 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

SQL> @loop
16 /
declare
*
ERROR at line 1:
ORA-01116: error in opening database file 12
ORA-01110: data file 12: ‘/u02/oradata/cdbd1/pdb1d1/test12c01.dbf’
ORA-27041: unable to open file
IBM AIX RISC System/6000 Error: 2: No such file or directory
Additional information: 3
Additional information: 4
Additional information: 4194304
ORA-06512: at line 7

SQL>

Now restore and recover tablespace to SCN 2936965 – Total rows must be 10,000 once recovery is complete.

sqlplus sys/Oracle12c@pdb1d1 as sysdba – FROM PDB.

SQL> alter tablespace test12c offline immediate;

Tablespace altered.

SQL>

cdbd1:/u01/oracle/scripts> rman target / —- FROM CDB ROOT.

Recovery Manager: Release 12.1.0.1.0 – Production on Fri May 9 13:46:20 2014

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

connected to target database: CDBD1 (DBID=2264962255)

cdbd1:/u01/oracle/scripts> rman target /

Recovery Manager: Release 12.1.0.1.0 – Production on Fri May 9 14:09:09 2014

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

connected to target database: CDBD1 (DBID=2264962255)

RMAN> RUN {
RECOVER TABLESPACE PDB1D1:TEST12C
UNTIL SCN 2936965
AUXILIARY DESTINATION ‘/u01/oracle/scripts’;
}2> 3> 4> 5>

Starting recover at 09-05-2014 14:09:13
using target database control file instead of recovery catalog
current log archived
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=21 device type=DISK
allocated channel: ORA_DISK_2
channel ORA_DISK_2: SID=132 device type=DISK
RMAN-05026: WARNING: presuming following set of tablespaces applies to specified Point-in-Time

List of tablespaces expected to have UNDO segments
Tablespace SYSTEM
Tablespace UNDOTBS1

Creating automatic instance, with SID=’aDbn’

initialization parameters used for automatic instance:
db_name=CDBD1
db_unique_name=aDbn_pitr_PDB1D1_CDBD1
compatible=12.1.0.0.0
db_block_size=8192
db_files=200
sga_target=1G
processes=80
diagnostic_dest=/u01/oracle
db_create_file_dest=/u01/oracle/scripts
log_archive_dest_1=’location=/u01/oracle/scripts’
enable_pluggable_database=true
_clone_one_pdb_recovery=true
#No auxiliary parameter file used

starting up automatic instance CDBD1

Oracle instance started

Total System Global Area 1068937216 bytes

Fixed Size 2368400 bytes
Variable Size 293604464 bytes
Database Buffers 767557632 bytes
Redo Buffers 5406720 bytes
Automatic instance created
Running TRANSPORT_SET_CHECK on recovery set tablespaces
TRANSPORT_SET_CHECK completed successfully

contents of Memory Script:
{
# set requested point in time
set until scn 2936965;
# restore the controlfile
restore clone controlfile;
# mount the controlfile
sql clone ‘alter database mount clone database’;
# archive current online log
sql ‘alter system archive log current’;
# avoid unnecessary autobackups for structural changes during TSPITR
sql ‘begin dbms_backup_restore.AutoBackupFlag(FALSE); end;’;
}
executing Memory Script

executing command: SET until clause

Starting restore at 09-05-2014 14:09:45
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: SID=79 device type=DISK
allocated channel: ORA_AUX_DISK_2
channel ORA_AUX_DISK_2: SID=109 device type=DISK

channel ORA_AUX_DISK_1: starting datafile backup set restore
channel ORA_AUX_DISK_1: restoring control file
channel ORA_AUX_DISK_1: reading from backup piece /u03/oradata/fast_recovery_area/CDBD1/autobackup/2014_05_09/o1_mf_s_847115717_9prvg5m6_.bkp
channel ORA_AUX_DISK_1: piece handle=/u03/oradata/fast_recovery_area/CDBD1/autobackup/2014_05_09/o1_mf_s_847115717_9prvg5m6_.bkp tag=TAG20140509T135517
channel ORA_AUX_DISK_1: restored backup piece 1
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:01
output file name=/u01/oracle/scripts/CDBD1/controlfile/o1_mf_9prw9brw_.ctl
Finished restore at 09-05-2014 14:09:47

sql statement: alter database mount clone database

sql statement: alter system archive log current

sql statement: begin dbms_backup_restore.AutoBackupFlag(FALSE); end;

contents of Memory Script:
{
# set requested point in time
set until scn 2936965;
# set destinations for recovery set and auxiliary set datafiles
set newname for clone datafile 1 to new;
set newname for clone datafile 5 to new;
set newname for clone datafile 3 to new;
set newname for clone datafile 10 to new;
set newname for clone datafile 11 to new;
set newname for clone tempfile 1 to new;
set newname for clone tempfile 3 to new;
set newname for datafile 12 to
“/u02/oradata/cdbd1/pdb1d1/test12c01.dbf”;
# switch all tempfiles
switch clone tempfile all;
# restore the tablespaces in the recovery set and the auxiliary set
restore clone datafile 1, 5, 3, 10, 11, 12;
switch clone datafile all;
}
executing Memory Script

executing command: SET until clause

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

renamed tempfile 1 to /u01/oracle/scripts/CDBD1/datafile/o1_mf_temp_%u_.tmp in control file
renamed tempfile 3 to /u01/oracle/scripts/CDBD1/datafile/o1_mf_temp_%u_.tmp in control file

Starting restore at 09-05-2014 14:09:53
using channel ORA_AUX_DISK_1
using channel ORA_AUX_DISK_2

channel ORA_AUX_DISK_1: starting datafile backup set restore
channel ORA_AUX_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_AUX_DISK_1: restoring datafile 00005 to /u01/oracle/scripts/CDBD1/datafile/o1_mf_undotbs1_%u_.dbf
channel ORA_AUX_DISK_1: reading from backup piece /u03/oradata/fast_recovery_area/CDBD1/backupset/2014_05_09/o1_mf_nnndf_TAG20140509T110516_9prkhh55_.bkp
channel ORA_AUX_DISK_2: starting datafile backup set restore
channel ORA_AUX_DISK_2: specifying datafile(s) to restore from backup set
channel ORA_AUX_DISK_2: restoring datafile 00001 to /u01/oracle/scripts/CDBD1/datafile/o1_mf_system_%u_.dbf
channel ORA_AUX_DISK_2: reading from backup piece /u03/oradata/fast_recovery_area/CDBD1/backupset/2014_05_09/o1_mf_nnndf_TAG20140509T110516_9prkkw9b_.bkp
channel ORA_AUX_DISK_1: piece handle=/u03/oradata/fast_recovery_area/CDBD1/backupset/2014_05_09/o1_mf_nnndf_TAG20140509T110516_9prkhh55_.bkp tag=TAG20140509T110516
channel ORA_AUX_DISK_1: restored backup piece 1
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:07
channel ORA_AUX_DISK_1: starting datafile backup set restore
channel ORA_AUX_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_AUX_DISK_1: restoring datafile 00010 to /u01/oracle/scripts/CDBD1/datafile/o1_mf_system_%u_.dbf
channel ORA_AUX_DISK_1: restoring datafile 00011 to /u01/oracle/scripts/CDBD1/datafile/o1_mf_sysaux_%u_.dbf
channel ORA_AUX_DISK_1: reading from backup piece /u03/oradata/fast_recovery_area/CDBD1/F8EEE24DA36C006AE0430AFECB4C494F/backupset/2014_05_09/o1_mf_nnndf_TAG20140509T111533_9prl2p47_.bkp
channel ORA_AUX_DISK_1: piece handle=/u03/oradata/fast_recovery_area/CDBD1/F8EEE24DA36C006AE0430AFECB4C494F/backupset/2014_05_09/o1_mf_nnndf_TAG20140509T111533_9prl2p47_.bkp tag=TAG20140509T111533
channel ORA_AUX_DISK_1: restored backup piece 1
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:55
channel ORA_AUX_DISK_1: starting datafile backup set restore
channel ORA_AUX_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_AUX_DISK_1: restoring datafile 00012 to /u02/oradata/cdbd1/pdb1d1/test12c01.dbf
channel ORA_AUX_DISK_1: reading from backup piece /u03/oradata/fast_recovery_area/CDBD1/F8EEE24DA36C006AE0430AFECB4C494F/backupset/2014_05_09/o1_mf_nnndf_TAG20140509T125811_9prr33yd_.bkp
channel ORA_AUX_DISK_2: piece handle=/u03/oradata/fast_recovery_area/CDBD1/backupset/2014_05_09/o1_mf_nnndf_TAG20140509T110516_9prkkw9b_.bkp tag=TAG20140509T110516
channel ORA_AUX_DISK_2: restored backup piece 1
channel ORA_AUX_DISK_2: restore complete, elapsed time: 00:01:02
channel ORA_AUX_DISK_2: starting datafile backup set restore
channel ORA_AUX_DISK_2: specifying datafile(s) to restore from backup set
channel ORA_AUX_DISK_2: restoring datafile 00003 to /u01/oracle/scripts/CDBD1/datafile/o1_mf_sysaux_%u_.dbf
channel ORA_AUX_DISK_2: reading from backup piece /u03/oradata/fast_recovery_area/CDBD1/backupset/2014_05_09/o1_mf_nnndf_TAG20140509T125811_9prr33xx_.bkp
channel ORA_AUX_DISK_1: piece handle=/u03/oradata/fast_recovery_area/CDBD1/F8EEE24DA36C006AE0430AFECB4C494F/backupset/2014_05_09/o1_mf_nnndf_TAG20140509T125811_9prr33yd_.bkp tag=TAG20140509T125811
channel ORA_AUX_DISK_1: restored backup piece 1
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:07
channel ORA_AUX_DISK_2: piece handle=/u03/oradata/fast_recovery_area/CDBD1/backupset/2014_05_09/o1_mf_nnndf_TAG20140509T125811_9prr33xx_.bkp tag=TAG20140509T125811
channel ORA_AUX_DISK_2: restored backup piece 1
channel ORA_AUX_DISK_2: restore complete, elapsed time: 00:01:05
Finished restore at 09-05-2014 14:12:01

datafile 1 switched to datafile copy
input datafile copy RECID=6 STAMP=847116721 file name=/u01/oracle/scripts/CDBD1/datafile/o1_mf_system_9prw9l5t_.dbf
datafile 5 switched to datafile copy
input datafile copy RECID=7 STAMP=847116721 file name=/u01/oracle/scripts/CDBD1/datafile/o1_mf_undotbs1_9prw9l8m_.dbf
datafile 3 switched to datafile copy
input datafile copy RECID=8 STAMP=847116721 file name=/u01/oracle/scripts/CDBD1/datafile/o1_mf_sysaux_9prwcjs1_.dbf
datafile 10 switched to datafile copy
input datafile copy RECID=9 STAMP=847116721 file name=/u01/oracle/scripts/CDBD1/datafile/o1_mf_system_9prw9so4_.dbf
datafile 11 switched to datafile copy
input datafile copy RECID=10 STAMP=847116721 file name=/u01/oracle/scripts/CDBD1/datafile/o1_mf_sysaux_9prw9sgr_.dbf

contents of Memory Script:
{
# set requested point in time
set until scn 2936965;
# online the datafiles restored or switched
sql clone “alter database datafile 1 online”;
sql clone “alter database datafile 5 online”;
sql clone “alter database datafile 3 online”;
sql clone ‘PDB1D1’ “alter database datafile
10 online”;
sql clone ‘PDB1D1’ “alter database datafile
11 online”;
sql clone ‘PDB1D1’ “alter database datafile
12 online”;
# recover and open resetlogs
recover clone database tablespace “PDB1D1″:”TEST12C”, “SYSTEM”, “UNDOTBS1”, “SYSAUX”, “PDB1D1″:”SYSTEM”, “PDB1D1″:”SYSAUX” delete archivelog;
alter clone database open resetlogs;
}
executing Memory Script

executing command: SET until clause

sql statement: alter database datafile 1 online

sql statement: alter database datafile 5 online

sql statement: alter database datafile 3 online

sql statement: alter database datafile 10 online

sql statement: alter database datafile 11 online

sql statement: alter database datafile 12 online

Starting recover at 09-05-2014 14:12:02
using channel ORA_AUX_DISK_1
using channel ORA_AUX_DISK_2

starting media recovery

archived log for thread 1 with sequence 202 is already on disk as file /u03/oradata/fast_recovery_area/CDBD1/archivelog/2014_05_09/o1_mf_1_202_9prkt4dm_.arc
archived log for thread 1 with sequence 203 is already on disk as file /u03/oradata/fast_recovery_area/CDBD1/archivelog/2014_05_09/o1_mf_1_203_9prky8qq_.arc
archived log for thread 1 with sequence 204 is already on disk as file /u03/oradata/fast_recovery_area/CDBD1/archivelog/2014_05_09/o1_mf_1_204_9prt0qxj_.arc
archived log for thread 1 with sequence 205 is already on disk as file /u03/oradata/fast_recovery_area/CDBD1/archivelog/2014_05_09/o1_mf_1_205_9prw8c8k_.arc
archived log file name=/u03/oradata/fast_recovery_area/CDBD1/archivelog/2014_05_09/o1_mf_1_202_9prkt4dm_.arc thread=1 sequence=202
archived log file name=/u03/oradata/fast_recovery_area/CDBD1/archivelog/2014_05_09/o1_mf_1_203_9prky8qq_.arc thread=1 sequence=203
archived log file name=/u03/oradata/fast_recovery_area/CDBD1/archivelog/2014_05_09/o1_mf_1_204_9prt0qxj_.arc thread=1 sequence=204
archived log file name=/u03/oradata/fast_recovery_area/CDBD1/archivelog/2014_05_09/o1_mf_1_205_9prw8c8k_.arc thread=1 sequence=205
media recovery complete, elapsed time: 00:00:03
Finished recover at 09-05-2014 14:12:07

database opened

contents of Memory Script:
{
sql clone ‘alter pluggable database PDB1D1 open’;
}
executing Memory Script

sql statement: alter pluggable database PDB1D1 open

contents of Memory Script:
{
# make read only the tablespace that will be exported
sql clone ‘PDB1D1’ ‘alter tablespace
TEST12C read only’;
# create directory for datapump import
sql ‘PDB1D1’ “create or replace directory
TSPITR_DIROBJ_DPDIR as ”
/u01/oracle/scripts””;
# create directory for datapump export
sql clone ‘PDB1D1’ “create or replace directory
TSPITR_DIROBJ_DPDIR as ”
/u01/oracle/scripts””;
}
executing Memory Script

sql statement: alter tablespace TEST12C read only

sql statement: create or replace directory TSPITR_DIROBJ_DPDIR as ”/u01/oracle/scripts”

sql statement: create or replace directory TSPITR_DIROBJ_DPDIR as ”/u01/oracle/scripts”

Performing export of metadata…
EXPDP> Starting “SYS”.”TSPITR_EXP_aDbn_atdu”:
EXPDP> Processing object type TRANSPORTABLE_EXPORT/PLUGTS_BLK
EXPDP> Processing object type TRANSPORTABLE_EXPORT/TABLE
EXPDP> Processing object type TRANSPORTABLE_EXPORT/TABLE_STATISTICS
EXPDP> Processing object type TRANSPORTABLE_EXPORT/STATISTICS/MARKER
EXPDP> Processing object type TRANSPORTABLE_EXPORT/POST_INSTANCE/PLUGTS_BLK
EXPDP> Master table “SYS”.”TSPITR_EXP_aDbn_atdu” successfully loaded/unloaded
EXPDP> ******************************************************************************
EXPDP> Dump file set for SYS.TSPITR_EXP_aDbn_atdu is:
EXPDP> /u01/oracle/scripts/tspitr_aDbn_27853.dmp
EXPDP> ******************************************************************************
EXPDP> Datafiles required for transportable tablespace TEST12C:
EXPDP> /u02/oradata/cdbd1/pdb1d1/test12c01.dbf
EXPDP> Job “SYS”.”TSPITR_EXP_aDbn_atdu” successfully completed at Fri May 9 14:13:49 2014 elapsed 0 00:01:05
Export completed

contents of Memory Script:
{
# shutdown clone before import
shutdown clone abort
# drop target tablespaces before importing them back
sql ‘PDB1D1’ ‘drop tablespace
TEST12C including contents keep datafiles cascade constraints’;
}
executing Memory Script

Oracle instance shut down

sql statement: drop tablespace TEST12C including contents keep datafiles cascade constraints

Performing import of metadata…
IMPDP> Master table “SYS”.”TSPITR_IMP_aDbn_anil” successfully loaded/unloaded
IMPDP> Starting “SYS”.”TSPITR_IMP_aDbn_anil”:
IMPDP> Processing object type TRANSPORTABLE_EXPORT/PLUGTS_BLK
IMPDP> Processing object type TRANSPORTABLE_EXPORT/TABLE
IMPDP> Processing object type TRANSPORTABLE_EXPORT/TABLE_STATISTICS
IMPDP> Processing object type TRANSPORTABLE_EXPORT/STATISTICS/MARKER
IMPDP> Processing object type TRANSPORTABLE_EXPORT/POST_INSTANCE/PLUGTS_BLK
IMPDP> Job “SYS”.”TSPITR_IMP_aDbn_anil” successfully completed at Fri May 9 14:14:52 2014 elapsed 0 00:00:35
Import completed

contents of Memory Script:
{
# make read write and offline the imported tablespaces
sql ‘PDB1D1’ ‘alter tablespace
TEST12C read write’;
sql ‘PDB1D1’ ‘alter tablespace
TEST12C offline’;
# enable autobackups after TSPITR is finished
sql ‘begin dbms_backup_restore.AutoBackupFlag(TRUE); end;’;
}
executing Memory Script

sql statement: alter tablespace TEST12C read write

sql statement: alter tablespace TEST12C offline

sql statement: begin dbms_backup_restore.AutoBackupFlag(TRUE); end;

Removing automatic instance
Automatic instance removed
auxiliary instance file /u01/oracle/scripts/CDBD1/datafile/o1_mf_temp_9prwg2o4_.tmp deleted
auxiliary instance file /u01/oracle/scripts/CDBD1/datafile/o1_mf_temp_9prwft77_.tmp deleted
auxiliary instance file /u01/oracle/scripts/CDBD1/onlinelog/o1_mf_3_9prwfrg6_.log deleted
auxiliary instance file /u01/oracle/scripts/CDBD1/onlinelog/o1_mf_2_9prwfr42_.log deleted
auxiliary instance file /u01/oracle/scripts/CDBD1/onlinelog/o1_mf_1_9prwfqs5_.log deleted
auxiliary instance file /u01/oracle/scripts/CDBD1/datafile/o1_mf_sysaux_9prw9sgr_.dbf deleted
auxiliary instance file /u01/oracle/scripts/CDBD1/datafile/o1_mf_system_9prw9so4_.dbf deleted
auxiliary instance file /u01/oracle/scripts/CDBD1/datafile/o1_mf_sysaux_9prwcjs1_.dbf deleted
auxiliary instance file /u01/oracle/scripts/CDBD1/datafile/o1_mf_undotbs1_9prw9l8m_.dbf deleted
auxiliary instance file /u01/oracle/scripts/CDBD1/datafile/o1_mf_system_9prw9l5t_.dbf deleted
auxiliary instance file /u01/oracle/scripts/CDBD1/controlfile/o1_mf_9prw9brw_.ctl deleted
auxiliary instance file tspitr_aDbn_27853.dmp deleted
Finished recover at 09-05-2014 14:14:56

RMAN> exit

Recovery Manager complete.

cdbd1:/u01/oracle/scripts> sqlplus sys/Oracle12c@pdb1d1 as sysdba —– IN PDB.

SQL> ALTER TABLESPACE TEST12C ONLINE;

Tablespace altered.

SQL> select count (*) from test;

COUNT(*)
———-
10000

SQL>

Posted in ORACLE 12C CDB-PDB | Leave a Comment »

UPGRADE FROM 11.2.0.4 TO 12C

Posted by Arjun Raja on March 6, 2014

11.2.0.4 to 12c upgrade using DBUA

Posted in Uncategorized | Leave a Comment »

 
Follow

Get every new post delivered to your Inbox.