[oracle@subhen-db01 ~]$ ps -fe|grep pmon|grep -i dev
oracle 24422 1 0 Nov27 ? 00:03:05 ora_pmon_ORCL_1
[oracle@subhen-db01 ~]$ . oraenv
[oracle@subhen-db01 ~]$ hostname
subhen-db01.espdev.aurdev.national.com.au
[oracle@subhen-db01 ~]$ . oraenv
ORACLE_SID = [oracle] ? ORCL_1
The Oracle base for ORACLE_HOME=/oracle/app/database/11.2.0.3/dbhome_1 is /oracle/app
[oracle@subhen-db01 ~]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.3.0 Production on Wed Dec 5 15:02:34 2012
Copyright (c) 1982, 2011, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Data Mining and Real Application Testing options
SQL> select name,open_mode,database_role from v$database;
NAME OPEN_MODE DATABASE_ROLE
--------- -------------------- ----------------
ORCL READ WRITE PRIMARY
SQL> !hostname
subhen-db01.espdev.aurdev.national.com.au
SQL> select sum(bytes)/1024/1024/1024 Gb from dba_segments;
GB
----------
1.70672607
SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Data Mining and Real Application Testing options
[oracle@subhen-db01 ~]$ rman target /
Recovery Manager: Release 11.2.0.3.0 - Production on Wed Dec 5 15:03:58 2012
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
connected to target database: ORCL (DBID=3812261009)
RMAN> show all;
using target database control file instead of recovery catalog
RMAN configuration parameters for database with db_unique_name ORCL are:
CONFIGURE RETENTION POLICY TO REDUNDANCY 1; # default
CONFIGURE BACKUP OPTIMIZATION OFF; # default
CONFIGURE DEFAULT DEVICE TYPE TO DISK; # default
CONFIGURE CONTROLFILE AUTOBACKUP OFF; # default
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '%F'; # default
CONFIGURE DEVICE TYPE DISK PARALLELISM 1 BACKUP TYPE TO BACKUPSET; # default
CONFIGURE DATAFILE BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
CONFIGURE ARCHIVELOG BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
CONFIGURE MAXSETSIZE TO UNLIMITED; # default
CONFIGURE ENCRYPTION FOR DATABASE OFF; # default
CONFIGURE ENCRYPTION ALGORITHM 'AES128'; # default
CONFIGURE COMPRESSION ALGORITHM 'BASIC' AS OF RELEASE 'DEFAULT' OPTIMIZE FOR LOAD TRUE ; # default
CONFIGURE ARCHIVELOG DELETION POLICY TO NONE; # default
CONFIGURE SNAPSHOT CONTROLFILE NAME TO '/oracle/app/database/11.2.0.3/dbhome_1/dbs/snapcf_ORCL_1.f'; # default
************************************
Configured controlfile autobackup on
************************************
RMAN> configure CONTROLFILE AUTOBACKUP on;
new RMAN configuration parameters:
CONFIGURE CONTROLFILE AUTOBACKUP ON;
new RMAN configuration parameters are successfully stored
RMAN> show all;
RMAN configuration parameters for database with db_unique_name ORCL are:
CONFIGURE RETENTION POLICY TO REDUNDANCY 1; # default
CONFIGURE BACKUP OPTIMIZATION OFF; # default
CONFIGURE DEFAULT DEVICE TYPE TO DISK; # default
CONFIGURE CONTROLFILE AUTOBACKUP ON;
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '%F'; # default
CONFIGURE DEVICE TYPE DISK PARALLELISM 1 BACKUP TYPE TO BACKUPSET; # default
CONFIGURE DATAFILE BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
CONFIGURE ARCHIVELOG BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
CONFIGURE MAXSETSIZE TO UNLIMITED; # default
CONFIGURE ENCRYPTION FOR DATABASE OFF; # default
CONFIGURE ENCRYPTION ALGORITHM 'AES128'; # default
CONFIGURE COMPRESSION ALGORITHM 'BASIC' AS OF RELEASE 'DEFAULT' OPTIMIZE FOR LOAD TRUE ; # default
CONFIGURE ARCHIVELOG DELETION POLICY TO NONE; # default
CONFIGURE SNAPSHOT CONTROLFILE NAME TO '/oracle/app/database/11.2.0.3/dbhome_1/dbs/snapcf_ORCL_1.f'; # default
*****************************************************************************
Here i deleted the archivelogs to keep backup small size & to finish quickly
*****************************************************************************
RMAN> list archivelog all;
List of Archived Log Copies for database with db_unique_name ORCL
=====================================================================
Key Thrd Seq S Low Time
------- ---- ------- - ---------
7 1 1 A 14-NOV-12
Name: +FRA/ORCL/archivelog/2012_11_14/thread_1_seq_1.6650.799335123
6 1 2 A 14-NOV-12
Name: +FRA/ORCL/archivelog/2012_11_14/thread_1_seq_2.6653.799335131
8 1 3 A 14-NOV-12
Name: +FRA/ORCL/archivelog/2012_11_14/thread_1_seq_3.6654.799335133
13 1 4 A 14-NOV-12
Name: +FRA/ORCL/archivelog/2012_11_16/thread_1_seq_4.7525.799489749
15 1 5 A 16-NOV-12
Name: +FRA/ORCL/archivelog/2012_11_16/thread_1_seq_5.7563.799491437
16 1 6 A 16-NOV-12
Name: +FRA/ORCL/archivelog/2012_11_16/thread_1_seq_6.7571.799491445
19 1 7 A 16-NOV-12
Name: +FRA/ORCL/archivelog/2012_11_20/thread_1_seq_7.9500.799884067
20 1 8 A 20-NOV-12
Name: +FRA/ORCL/archivelog/2012_11_20/thread_1_seq_8.9380.799884071
43 1 9 A 05-DEC-12
Name: +FRA/ORCL/archivelog/2012_12_05/thread_1_seq_9.15553.801239171
45 1 10 A 05-DEC-12
Name: +FRA/ORCL/archivelog/2012_12_05/thread_1_seq_10.15679.801241527
2 2 1 A 14-NOV-12
Name: +FRA/ORCL/archivelog/2012_11_14/thread_2_seq_1.6456.799329883
5 2 2 A 14-NOV-12
Name: +FRA/ORCL/archivelog/2012_11_14/thread_2_seq_2.6651.799335125
9 2 3 A 14-NOV-12
Name: +FRA/ORCL/archivelog/2012_11_16/thread_2_seq_3.7411.799489257
12 2 4 A 16-NOV-12
Name: +FRA/ORCL/archivelog/2012_11_16/thread_2_seq_4.7512.799489739
14 2 5 A 16-NOV-12
Name: +FRA/ORCL/archivelog/2012_11_16/thread_2_seq_5.7555.799489795
18 2 6 A 16-NOV-12
Name: +FRA/ORCL/archivelog/2012_11_19/thread_2_seq_6.9057.799797639
22 2 7 A 19-NOV-12
Name: +FRA/ORCL/archivelog/2012_11_23/thread_2_seq_7.10328.800082011
25 2 8 A 23-NOV-12
Name: +FRA/ORCL/archivelog/2012_11_26/thread_2_seq_8.7483.800402453
27 2 9 A 26-NOV-12
Name: +FRA/ORCL/archivelog/2012_11_27/thread_2_seq_9.12320.800456761
31 2 10 A 27-NOV-12
Name: +FRA/ORCL/archivelog/2012_11_28/thread_2_seq_10.12875.800550599
32 2 11 A 28-NOV-12
Name: +FRA/ORCL/archivelog/2012_11_30/thread_2_seq_11.11969.800733609
34 2 12 A 30-NOV-12
Name: +FRA/ORCL/archivelog/2012_12_02/thread_2_seq_12.14317.800967941
35 2 13 A 02-DEC-12
Name: +FRA/ORCL/archivelog/2012_12_04/thread_2_seq_13.4122.801111615
37 2 14 A 04-DEC-12
Name: +FRA/ORCL/archivelog/2012_12_05/thread_2_seq_14.15422.801239127
39 2 15 A 05-DEC-12
Name: +FRA/ORCL/archivelog/2012_12_05/thread_2_seq_15.15509.801239165
1 3 1 A 14-NOV-12
Name: +FRA/ORCL/archivelog/2012_11_14/thread_3_seq_1.5958.799329881
3 3 2 A 14-NOV-12
Name: +FRA/ORCL/archivelog/2012_11_14/thread_3_seq_2.6507.799334905
4 3 3 A 14-NOV-12
Name: +FRA/ORCL/archivelog/2012_11_14/thread_3_seq_3.6570.799334991
10 3 4 A 14-NOV-12
Name: +FRA/ORCL/archivelog/2012_11_16/thread_3_seq_4.7414.799489263
11 3 5 A 16-NOV-12
Name: +FRA/ORCL/archivelog/2012_11_16/thread_3_seq_5.7418.799489267
17 3 6 A 16-NOV-12
Name: +FRA/ORCL/archivelog/2012_11_18/thread_3_seq_6.8602.799711435
21 3 7 A 18-NOV-12
Name: +FRA/ORCL/archivelog/2012_11_23/thread_3_seq_7.10261.800067617
23 3 8 A 23-NOV-12
Name: +FRA/ORCL/archivelog/2012_11_24/thread_3_seq_8.11015.800190607
24 3 9 A 24-NOV-12
Name: +FRA/ORCL/archivelog/2012_11_25/thread_3_seq_9.11556.800287359
26 3 10 A 25-NOV-12
Name: +FRA/ORCL/archivelog/2012_11_27/thread_3_seq_10.12239.800450041
28 3 11 A 27-NOV-12
Name: +FRA/ORCL/archivelog/2012_11_27/thread_3_seq_11.12314.800456759
29 3 12 A 27-NOV-12
Name: +FRA/ORCL/archivelog/2012_11_27/thread_3_seq_12.12370.800456811
30 3 13 A 27-NOV-12
Name: +FRA/ORCL/archivelog/2012_11_28/thread_3_seq_13.12874.800550597
33 3 14 A 28-NOV-12
Name: +FRA/ORCL/archivelog/2012_12_01/thread_3_seq_14.13653.800859623
36 3 15 A 01-DEC-12
Name: +FRA/ORCL/archivelog/2012_12_05/thread_3_seq_15.15297.801187247
38 3 16 A 05-DEC-12
Name: +FRA/ORCL/archivelog/2012_12_05/thread_3_seq_16.15495.801239163
40 3 17 A 05-DEC-12
Name: +FRA/ORCL/archivelog/2012_12_05/thread_3_seq_17.15511.801239165
41 3 18 A 05-DEC-12
Name: +FRA/ORCL/archivelog/2012_12_05/thread_3_seq_18.15525.801239167
42 3 19 A 05-DEC-12
Name: +FRA/ORCL/archivelog/2012_12_05/thread_3_seq_19.15533.801239169
44 3 20 A 05-DEC-12
Name: +FRA/ORCL/archivelog/2012_12_05/thread_3_seq_20.15678.801241525
RMAN> delete archivelog until sequence 8 thread 1;
released channel: ORA_DISK_1
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=515 instance=ORCL_1 device type=DISK
List of Archived Log Copies for database with db_unique_name ORCL
=====================================================================
Key Thrd Seq S Low Time
------- ---- ------- - ---------
7 1 1 A 14-NOV-12
Name: +FRA/ORCL/archivelog/2012_11_14/thread_1_seq_1.6650.799335123
6 1 2 A 14-NOV-12
Name: +FRA/ORCL/archivelog/2012_11_14/thread_1_seq_2.6653.799335131
8 1 3 A 14-NOV-12
Name: +FRA/ORCL/archivelog/2012_11_14/thread_1_seq_3.6654.799335133
13 1 4 A 14-NOV-12
Name: +FRA/ORCL/archivelog/2012_11_16/thread_1_seq_4.7525.799489749
15 1 5 A 16-NOV-12
Name: +FRA/ORCL/archivelog/2012_11_16/thread_1_seq_5.7563.799491437
16 1 6 A 16-NOV-12
Name: +FRA/ORCL/archivelog/2012_11_16/thread_1_seq_6.7571.799491445
19 1 7 A 16-NOV-12
Name: +FRA/ORCL/archivelog/2012_11_20/thread_1_seq_7.9500.799884067
20 1 8 A 20-NOV-12
Name: +FRA/ORCL/archivelog/2012_11_20/thread_1_seq_8.9380.799884071
Do you really want to delete the above objects (enter YES or NO)? yes
deleted archived log
archived log file name=+FRA/ORCL/archivelog/2012_11_14/thread_1_seq_1.6650.799335123 RECID=7 STAMP=799335132
deleted archived log
archived log file name=+FRA/ORCL/archivelog/2012_11_14/thread_1_seq_2.6653.799335131 RECID=6 STAMP=799335131
deleted archived log
archived log file name=+FRA/ORCL/archivelog/2012_11_14/thread_1_seq_3.6654.799335133 RECID=8 STAMP=799335133
deleted archived log
archived log file name=+FRA/ORCL/archivelog/2012_11_16/thread_1_seq_4.7525.799489749 RECID=13 STAMP=799489777
deleted archived log
archived log file name=+FRA/ORCL/archivelog/2012_11_16/thread_1_seq_5.7563.799491437 RECID=15 STAMP=799491436
deleted archived log
archived log file name=+FRA/ORCL/archivelog/2012_11_16/thread_1_seq_6.7571.799491445 RECID=16 STAMP=799491445
deleted archived log
archived log file name=+FRA/ORCL/archivelog/2012_11_20/thread_1_seq_7.9500.799884067 RECID=19 STAMP=799884070
deleted archived log
archived log file name=+FRA/ORCL/archivelog/2012_11_20/thread_1_seq_8.9380.799884071 RECID=20 STAMP=799884070
Deleted 8 objects
RMAN> delete archivelog until sequence 12 thread 2;
released channel: ORA_DISK_1
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=515 instance=ORCL_1 device type=DISK
List of Archived Log Copies for database with db_unique_name ORCL
=====================================================================
Key Thrd Seq S Low Time
------- ---- ------- - ---------
2 2 1 A 14-NOV-12
Name: +FRA/ORCL/archivelog/2012_11_14/thread_2_seq_1.6456.799329883
5 2 2 A 14-NOV-12
Name: +FRA/ORCL/archivelog/2012_11_14/thread_2_seq_2.6651.799335125
9 2 3 A 14-NOV-12
Name: +FRA/ORCL/archivelog/2012_11_16/thread_2_seq_3.7411.799489257
12 2 4 A 16-NOV-12
Name: +FRA/ORCL/archivelog/2012_11_16/thread_2_seq_4.7512.799489739
14 2 5 A 16-NOV-12
Name: +FRA/ORCL/archivelog/2012_11_16/thread_2_seq_5.7555.799489795
18 2 6 A 16-NOV-12
Name: +FRA/ORCL/archivelog/2012_11_19/thread_2_seq_6.9057.799797639
22 2 7 A 19-NOV-12
Name: +FRA/ORCL/archivelog/2012_11_23/thread_2_seq_7.10328.800082011
25 2 8 A 23-NOV-12
Name: +FRA/ORCL/archivelog/2012_11_26/thread_2_seq_8.7483.800402453
27 2 9 A 26-NOV-12
Name: +FRA/ORCL/archivelog/2012_11_27/thread_2_seq_9.12320.800456761
31 2 10 A 27-NOV-12
Name: +FRA/ORCL/archivelog/2012_11_28/thread_2_seq_10.12875.800550599
32 2 11 A 28-NOV-12
Name: +FRA/ORCL/archivelog/2012_11_30/thread_2_seq_11.11969.800733609
34 2 12 A 30-NOV-12
Name: +FRA/ORCL/archivelog/2012_12_02/thread_2_seq_12.14317.800967941
Do you really want to delete the above objects (enter YES or NO)? yes
deleted archived log
archived log file name=+FRA/ORCL/archivelog/2012_11_14/thread_2_seq_1.6456.799329883 RECID=2 STAMP=799329882
deleted archived log
archived log file name=+FRA/ORCL/archivelog/2012_11_14/thread_2_seq_2.6651.799335125 RECID=5 STAMP=799335126
deleted archived log
archived log file name=+FRA/ORCL/archivelog/2012_11_16/thread_2_seq_3.7411.799489257 RECID=9 STAMP=799489263
deleted archived log
archived log file name=+FRA/ORCL/archivelog/2012_11_16/thread_2_seq_4.7512.799489739 RECID=12 STAMP=799489776
deleted archived log
archived log file name=+FRA/ORCL/archivelog/2012_11_16/thread_2_seq_5.7555.799489795 RECID=14 STAMP=799489794
deleted archived log
archived log file name=+FRA/ORCL/archivelog/2012_11_19/thread_2_seq_6.9057.799797639 RECID=18 STAMP=799797641
deleted archived log
archived log file name=+FRA/ORCL/archivelog/2012_11_23/thread_2_seq_7.10328.800082011 RECID=22 STAMP=800082014
deleted archived log
archived log file name=+FRA/ORCL/archivelog/2012_11_26/thread_2_seq_8.7483.800402453 RECID=25 STAMP=800402456
deleted archived log
archived log file name=+FRA/ORCL/archivelog/2012_11_27/thread_2_seq_9.12320.800456761 RECID=27 STAMP=800456809
deleted archived log
archived log file name=+FRA/ORCL/archivelog/2012_11_28/thread_2_seq_10.12875.800550599 RECID=31 STAMP=800550598
deleted archived log
archived log file name=+FRA/ORCL/archivelog/2012_11_30/thread_2_seq_11.11969.800733609 RECID=32 STAMP=800733614
deleted archived log
archived log file name=+FRA/ORCL/archivelog/2012_12_02/thread_2_seq_12.14317.800967941 RECID=34 STAMP=800967942
Deleted 12 objects
RMAN> delete archivelog until sequence 15 thread 3;
released channel: ORA_DISK_1
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=515 instance=ORCL_1 device type=DISK
List of Archived Log Copies for database with db_unique_name ORCL
=====================================================================
Key Thrd Seq S Low Time
------- ---- ------- - ---------
1 3 1 A 14-NOV-12
Name: +FRA/ORCL/archivelog/2012_11_14/thread_3_seq_1.5958.799329881
3 3 2 A 14-NOV-12
Name: +FRA/ORCL/archivelog/2012_11_14/thread_3_seq_2.6507.799334905
4 3 3 A 14-NOV-12
Name: +FRA/ORCL/archivelog/2012_11_14/thread_3_seq_3.6570.799334991
10 3 4 A 14-NOV-12
Name: +FRA/ORCL/archivelog/2012_11_16/thread_3_seq_4.7414.799489263
11 3 5 A 16-NOV-12
Name: +FRA/ORCL/archivelog/2012_11_16/thread_3_seq_5.7418.799489267
17 3 6 A 16-NOV-12
Name: +FRA/ORCL/archivelog/2012_11_18/thread_3_seq_6.8602.799711435
21 3 7 A 18-NOV-12
Name: +FRA/ORCL/archivelog/2012_11_23/thread_3_seq_7.10261.800067617
23 3 8 A 23-NOV-12
Name: +FRA/ORCL/archivelog/2012_11_24/thread_3_seq_8.11015.800190607
24 3 9 A 24-NOV-12
Name: +FRA/ORCL/archivelog/2012_11_25/thread_3_seq_9.11556.800287359
26 3 10 A 25-NOV-12
Name: +FRA/ORCL/archivelog/2012_11_27/thread_3_seq_10.12239.800450041
28 3 11 A 27-NOV-12
Name: +FRA/ORCL/archivelog/2012_11_27/thread_3_seq_11.12314.800456759
29 3 12 A 27-NOV-12
Name: +FRA/ORCL/archivelog/2012_11_27/thread_3_seq_12.12370.800456811
30 3 13 A 27-NOV-12
Name: +FRA/ORCL/archivelog/2012_11_28/thread_3_seq_13.12874.800550597
33 3 14 A 28-NOV-12
Name: +FRA/ORCL/archivelog/2012_12_01/thread_3_seq_14.13653.800859623
36 3 15 A 01-DEC-12
Name: +FRA/ORCL/archivelog/2012_12_05/thread_3_seq_15.15297.801187247
Do you really want to delete the above objects (enter YES or NO)? yes
deleted archived log
archived log file name=+FRA/ORCL/archivelog/2012_11_14/thread_3_seq_1.5958.799329881 RECID=1 STAMP=799329880
deleted archived log
archived log file name=+FRA/ORCL/archivelog/2012_11_14/thread_3_seq_2.6507.799334905 RECID=3 STAMP=799334906
deleted archived log
archived log file name=+FRA/ORCL/archivelog/2012_11_14/thread_3_seq_3.6570.799334991 RECID=4 STAMP=799335123
deleted archived log
archived log file name=+FRA/ORCL/archivelog/2012_11_16/thread_3_seq_4.7414.799489263 RECID=10 STAMP=799489266
deleted archived log
archived log file name=+FRA/ORCL/archivelog/2012_11_16/thread_3_seq_5.7418.799489267 RECID=11 STAMP=799489268
deleted archived log
archived log file name=+FRA/ORCL/archivelog/2012_11_18/thread_3_seq_6.8602.799711435 RECID=17 STAMP=799711436
deleted archived log
archived log file name=+FRA/ORCL/archivelog/2012_11_23/thread_3_seq_7.10261.800067617 RECID=21 STAMP=800067622
deleted archived log
archived log file name=+FRA/ORCL/archivelog/2012_11_24/thread_3_seq_8.11015.800190607 RECID=23 STAMP=800190609
deleted archived log
archived log file name=+FRA/ORCL/archivelog/2012_11_25/thread_3_seq_9.11556.800287359 RECID=24 STAMP=800287361
deleted archived log
archived log file name=+FRA/ORCL/archivelog/2012_11_27/thread_3_seq_10.12239.800450041 RECID=26 STAMP=800450043
deleted archived log
archived log file name=+FRA/ORCL/archivelog/2012_11_27/thread_3_seq_11.12314.800456759 RECID=28 STAMP=800456811
deleted archived log
archived log file name=+FRA/ORCL/archivelog/2012_11_27/thread_3_seq_12.12370.800456811 RECID=29 STAMP=800456811
deleted archived log
archived log file name=+FRA/ORCL/archivelog/2012_11_28/thread_3_seq_13.12874.800550597 RECID=30 STAMP=800550598
deleted archived log
archived log file name=+FRA/ORCL/archivelog/2012_12_01/thread_3_seq_14.13653.800859623 RECID=33 STAMP=800859626
deleted archived log
archived log file name=+FRA/ORCL/archivelog/2012_12_05/thread_3_seq_15.15297.801187247 RECID=36 STAMP=801187251
Deleted 15 objects
RMAN> list archivelog all
2> ;
List of Archived Log Copies for database with db_unique_name ORCL
=====================================================================
Key Thrd Seq S Low Time
------- ---- ------- - ---------
43 1 9 A 05-DEC-12
Name: +FRA/ORCL/archivelog/2012_12_05/thread_1_seq_9.15553.801239171
45 1 10 A 05-DEC-12
Name: +FRA/ORCL/archivelog/2012_12_05/thread_1_seq_10.15679.801241527
35 2 13 A 02-DEC-12
Name: +FRA/ORCL/archivelog/2012_12_04/thread_2_seq_13.4122.801111615
37 2 14 A 04-DEC-12
Name: +FRA/ORCL/archivelog/2012_12_05/thread_2_seq_14.15422.801239127
39 2 15 A 05-DEC-12
Name: +FRA/ORCL/archivelog/2012_12_05/thread_2_seq_15.15509.801239165
38 3 16 A 05-DEC-12
Name: +FRA/ORCL/archivelog/2012_12_05/thread_3_seq_16.15495.801239163
40 3 17 A 05-DEC-12
Name: +FRA/ORCL/archivelog/2012_12_05/thread_3_seq_17.15511.801239165
41 3 18 A 05-DEC-12
Name: +FRA/ORCL/archivelog/2012_12_05/thread_3_seq_18.15525.801239167
42 3 19 A 05-DEC-12
Name: +FRA/ORCL/archivelog/2012_12_05/thread_3_seq_19.15533.801239169
44 3 20 A 05-DEC-12
Name: +FRA/ORCL/archivelog/2012_12_05/thread_3_seq_20.15678.801241525
RMAN> list backup;
specification does not match any backup in the repository
===================================================
RMAN> backup database plus archivelog;
Starting backup at 05-DEC-12
current log archived
using channel ORA_DISK_1
channel ORA_DISK_1: starting archived log backup set
channel ORA_DISK_1: specifying archived log(s) in backup set
input archived log thread=2 sequence=13 RECID=35 STAMP=801111618
input archived log thread=2 sequence=14 RECID=37 STAMP=801239130
input archived log thread=3 sequence=16 RECID=38 STAMP=801239165
input archived log thread=2 sequence=15 RECID=39 STAMP=801239165
input archived log thread=3 sequence=17 RECID=40 STAMP=801239165
input archived log thread=1 sequence=9 RECID=43 STAMP=801239170
input archived log thread=3 sequence=18 RECID=41 STAMP=801239167
input archived log thread=3 sequence=19 RECID=42 STAMP=801239168
input archived log thread=1 sequence=10 RECID=45 STAMP=801241526
input archived log thread=3 sequence=20 RECID=44 STAMP=801241524
input archived log thread=3 sequence=21 RECID=47 STAMP=801241757
input archived log thread=1 sequence=11 RECID=46 STAMP=801241754
channel ORA_DISK_1: starting piece 1 at 05-DEC-12
channel ORA_DISK_1: finished piece 1 at 05-DEC-12
piece handle=+FRA/ORCL/backupset/2012_12_05/annnf0_tag20121205t150917_0.12874.801241759 tag=TAG20121205T150917 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:07
Finished backup at 05-DEC-12
Starting backup at 05-DEC-12
using channel ORA_DISK_1
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00002 name=+DATA/ORCL/datafile/sysaux.3472.799329609
input datafile file number=00001 name=+DATA/ORCL/datafile/system.3471.799329607
input datafile file number=00007 name=+DATA/ORCL/datafile/ggate_data.3487.799330815
input datafile file number=00003 name=+DATA/ORCL/datafile/undotbs1.3473.799329609
input datafile file number=00006 name=+DATA/ORCL/datafile/undotbs3.3482.799329791
input datafile file number=00005 name=+DATA/ORCL/datafile/undotbs2.3481.799329791
input datafile file number=00004 name=+DATA/ORCL/datafile/users.3474.799329609
channel ORA_DISK_1: starting piece 1 at 05-DEC-12
channel ORA_DISK_1: finished piece 1 at 05-DEC-12
piece handle=+FRA/ORCL/backupset/2012_12_05/nnndf0_tag20121205t150925_0.12370.801241767 tag=TAG20121205T150925 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:15
Finished backup at 05-DEC-12
Starting backup at 05-DEC-12
current log archived
using channel ORA_DISK_1
channel ORA_DISK_1: starting archived log backup set
channel ORA_DISK_1: specifying archived log(s) in backup set
input archived log thread=1 sequence=12 RECID=49 STAMP=801241784
input archived log thread=3 sequence=22 RECID=48 STAMP=801241781
channel ORA_DISK_1: starting piece 1 at 05-DEC-12
channel ORA_DISK_1: finished piece 1 at 05-DEC-12
piece handle=+FRA/ORCL/backupset/2012_12_05/annnf0_tag20121205t150947_0.11556.801241789 tag=TAG20121205T150947 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
Finished backup at 05-DEC-12
Starting Control File and SPFILE Autobackup at 05-DEC-12
piece handle=+FRA/ORCL/autobackup/2012_12_05/s_801241789.11015.801241789 comment=NONE
Finished Control File and SPFILE Autobackup at 05-DEC-12
RMAN> exit
Recovery Manager complete.
====================================================================
[oracle@subhen-db01 ~]$ echo $ORACLE_SID
ORCL_1
[oracle@subhen-db01 ~]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.3.0 Production on Wed Dec 5 15:10:36 2012
Copyright (c) 1982, 2011, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Data Mining and Real Application Testing options
SQL> show parameter pfile
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
spfile string +DATA/ORCL/spfileORCL
.ora
SQL> create pfile='/tmp/initDUPDB.ora' from spfile;
File created.
SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Data Mining and Real Application Testing options
**************************************************************
Preparing the init file for duplicate database from 3 node RAC to single instance DB
**************************************************************
[oracle@subhen-db01 ~]$ cp /tmp/initDUPDB.ora /tmp/initDUPDB.ora.bkp
[oracle@subhen-db01 ~]$ vi /tmp/initDUPDB.ora
[oracle@subhen-db01 ~]$ cat /tmp/initDUPDB.ora
*.audit_file_dest='/oracle/app/admin/DUPDB/adump'
*.audit_trail='db'
*.cluster_database=false
*.compatible='11.2.0.0.0'
*.control_files='+DATA/DUPDB/controlfile/current.3475.799329681','+FRA/DUPDB/controlfile/current.6090.799329683'
*.db_block_size=8192
*.db_create_file_dest='+DATA'
*.db_domain=''
*.db_name='DUPDB'
*.db_recovery_file_dest='+FRA'
*.db_recovery_file_dest_size=21474836480
*.diagnostic_dest='/oracle/app'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=DUPDBXDB)'
*.log_archive_format='%t_%s_%r.dbf'
*.memory_max_target=7516192768
*.open_cursors=500
*.pga_aggregate_target=2539651072
*.processes=1000
*.remote_listener='subhen-dbscan.xxxx.xxxxx.xxxx.com.au:1621'
*.remote_login_passwordfile='exclusive'
*.sga_target=7621050368
DB_FILE_NAME_CONVERT='ORCL','DUPDB'
LOG_FILE_NAME_CONVERT=''ORCL','DUPDB'
[oracle@subhen-db01 ~]$ mkdir -p /oracle/app/admin/DUPDB/adump
[oracle@subhen-db01 admin]$ tnsping ORCL
TNS Ping Utility for Linux: Version 11.2.0.3.0 - Production on 05-DEC-2012 15:37:19
Copyright (c) 1997, 2011, Oracle. All rights reserved.
Used parameter files:
Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = subhen-dbscan.xxxx.xxxxx.xxxx.com.au)(PORT = 1621)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = ORCL)))
OK (0 msec)
[oracle@subhen-db01 admin]$ sqlplus sys/sw2tesb@ORCL as sysdba
SQL*Plus: Release 11.2.0.3.0 Production on Wed Dec 5 15:38:05 2012
Copyright (c) 1982, 2011, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Data Mining and Real Application Testing options
SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Data Mining and Real Application Testing options
[oracle@subhen-db01 admin]$ export ORACLE_SID=DUPDB
[oracle@subhen-db01 admin]$ echo $ORACLE_SID
DUPDB
[oracle@subhen-db01 admin]$ echo $ORACLE_HOME
/oracle/app/database/11.2.0.3/dbhome_1
[oracle@subhen-db01 admin]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.3.0 Production on Wed Dec 5 15:41:50 2012
Copyright (c) 1982, 2011, Oracle. All rights reserved.
Connected to an idle instance.
SQL> startup nomount pfile='/tmp/initDUPDB.ora'
ORACLE instance started.
Total System Global Area 1.0138E+10 bytes
Fixed Size 2249288 bytes
Variable Size 3791654328 bytes
Database Buffers 6325010432 bytes
Redo Buffers 19382272 bytes
SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, Real Application Clusters, OLAP, Data Mining
and Real Application Testing options
[oracle@subhen-db01 admin]$ echo $ORACLE_SID
DUPDB
[oracle@subhen-db01 admin]$ . oraenv
ORACLE_SID = [DUPDB] ? ORCL_1
The Oracle base for ORACLE_HOME=/oracle/app/database/11.2.0.3/dbhome_1 is /oracle/app
[oracle@subhen-db01 admin]$ rman target /
Recovery Manager: Release 11.2.0.3.0 - Production on Wed Dec 5 15:54:05 2012
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
connected to target database: ORCL (DBID=3812261009)
RMAN> backup database include current controlfile;
Starting backup at 05-DEC-12
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=322 instance=ORCL_1 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=00002 name=+DATA/ORCL/datafile/sysaux.3472.799329609
input datafile file number=00001 name=+DATA/ORCL/datafile/system.3471.799329607
input datafile file number=00007 name=+DATA/ORCL/datafile/ggate_data.3487.799330815
input datafile file number=00003 name=+DATA/ORCL/datafile/undotbs1.3473.799329609
input datafile file number=00006 name=+DATA/ORCL/datafile/undotbs3.3482.799329791
input datafile file number=00005 name=+DATA/ORCL/datafile/undotbs2.3481.799329791
input datafile file number=00004 name=+DATA/ORCL/datafile/users.3474.799329609
channel ORA_DISK_1: starting piece 1 at 05-DEC-12
channel ORA_DISK_1: finished piece 1 at 05-DEC-12
piece handle=+FRA/ORCL/backupset/2012_12_05/nnndf0_tag20121205t155423_0.10261.801244465 tag=TAG20121205T155423 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:15
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
including current control file in backup set
channel ORA_DISK_1: starting piece 1 at 05-DEC-12
channel ORA_DISK_1: finished piece 1 at 05-DEC-12
piece handle=+FRA/ORCL/backupset/2012_12_05/ncnnf0_tag20121205t155423_0.8602.801244481 tag=TAG20121205T155423 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
Finished backup at 05-DEC-12
Starting Control File and SPFILE Autobackup at 05-DEC-12
piece handle=+FRA/ORCL/autobackup/2012_12_05/s_801244481.15610.801244481 comment=NONE
Finished Control File and SPFILE Autobackup at 05-DEC-12
RMAN> exit
Recovery Manager complete.
Recovery Manager complete.
[oracle@subhen-db01 admin]$ export ORACLE_SID=ORCL_1
[oracle@subhen-db01 admin]$ rman target /
Recovery Manager: Release 11.2.0.3.0 - Production on Wed Dec 5 16:31:42 2012
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
connected to target database: ORCL (DBID=3812261009)
**************************************************
check for the SCN of the controlfile included
**************************************************
RMAN> list backup
2> ;
using target database control file instead of recovery catalog
List of Backup Sets
===================
BS Key Size Device Type Elapsed Time Completion Time
------- ---------- ----------- ------------ ---------------
1 429.56M DISK 00:00:03 05-DEC-12
BP Key: 1 Status: AVAILABLE Compressed: NO Tag: TAG20121205T150917
Piece Name: +FRA/ORCL/backupset/2012_12_05/annnf0_tag20121205t150917_0.12874.801241759
List of Archived Logs in backup set 1
Thrd Seq Low SCN Low Time Next SCN Next Time
---- ------- ---------- --------- ---------- ---------
1 9 4847883 05-DEC-12 4848141 05-DEC-12
1 10 4848141 05-DEC-12 4857468 05-DEC-12
1 11 4857468 05-DEC-12 4857723 05-DEC-12
2 13 4325472 02-DEC-12 4577922 04-DEC-12
2 14 4577922 04-DEC-12 4827552 05-DEC-12
2 15 4827552 05-DEC-12 4827554 05-DEC-12
3 16 4723419 05-DEC-12 4847882 05-DEC-12
3 17 4847882 05-DEC-12 4847886 05-DEC-12
3 18 4847886 05-DEC-12 4847888 05-DEC-12
3 19 4847888 05-DEC-12 4847894 05-DEC-12
3 20 4848806 05-DEC-12 4857459 05-DEC-12
3 21 4857459 05-DEC-12 4857735 05-DEC-12
BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
2 Full 1.49G DISK 00:00:09 05-DEC-12
BP Key: 2 Status: AVAILABLE Compressed: NO Tag: TAG20121205T150925
Piece Name: +FRA/ORCL/backupset/2012_12_05/nnndf0_tag20121205t150925_0.12370.801241767
List of Datafiles in backup set 2
File LV Type Ckp SCN Ckp Time Name
---- -- ---- ---------- --------- ----
1 Full 4857761 05-DEC-12 +DATA/ORCL/datafile/system.3471.799329607
2 Full 4857761 05-DEC-12 +DATA/ORCL/datafile/sysaux.3472.799329609
3 Full 4857761 05-DEC-12 +DATA/ORCL/datafile/undotbs1.3473.799329609
4 Full 4857761 05-DEC-12 +DATA/ORCL/datafile/users.3474.799329609
5 Full 4857761 05-DEC-12 +DATA/ORCL/datafile/undotbs2.3481.799329791
6 Full 4857761 05-DEC-12 +DATA/ORCL/datafile/undotbs3.3482.799329791
7 Full 4857761 05-DEC-12 +DATA/ORCL/datafile/ggate_data.3487.799330815
BS Key Size Device Type Elapsed Time Completion Time
------- ---------- ----------- ------------ ---------------
3 4.50K DISK 00:00:00 05-DEC-12
BP Key: 3 Status: AVAILABLE Compressed: NO Tag: TAG20121205T150947
Piece Name: +FRA/ORCL/backupset/2012_12_05/annnf0_tag20121205t150947_0.11556.801241789
List of Archived Logs in backup set 3
Thrd Seq Low SCN Low Time Next SCN Next Time
---- ------- ---------- --------- ---------- ---------
1 12 4857723 05-DEC-12 4857788 05-DEC-12
3 22 4857735 05-DEC-12 4857784 05-DEC-12
BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
4 Full 22.39M DISK 00:00:00 05-DEC-12
BP Key: 4 Status: AVAILABLE Compressed: NO Tag: TAG20121205T150949
Piece Name: +FRA/ORCL/autobackup/2012_12_05/s_801241789.11015.801241789
SPFILE Included: Modification time: 05-DEC-12
SPFILE db_unique_name: ORCL
Control File Included: Ckp SCN: 4857798 Ckp time: 05-DEC-12
BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
5 Full 1.49G DISK 00:00:10 05-DEC-12
BP Key: 5 Status: AVAILABLE Compressed: NO Tag: TAG20121205T155423
Piece Name: +FRA/ORCL/backupset/2012_12_05/nnndf0_tag20121205t155423_0.10261.801244465
List of Datafiles in backup set 5
File LV Type Ckp SCN Ckp Time Name
---- -- ---- ---------- --------- ----
1 Full 4865188 05-DEC-12 +DATA/ORCL/datafile/system.3471.799329607
2 Full 4865188 05-DEC-12 +DATA/ORCL/datafile/sysaux.3472.799329609
3 Full 4865188 05-DEC-12 +DATA/ORCL/datafile/undotbs1.3473.799329609
4 Full 4865188 05-DEC-12 +DATA/ORCL/datafile/users.3474.799329609
5 Full 4865188 05-DEC-12 +DATA/ORCL/datafile/undotbs2.3481.799329791
6 Full 4865188 05-DEC-12 +DATA/ORCL/datafile/undotbs3.3482.799329791
7 Full 4865188 05-DEC-12 +DATA/ORCL/datafile/ggate_data.3487.799330815
BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
6 Full 22.36M DISK 00:00:01 05-DEC-12
BP Key: 6 Status: AVAILABLE Compressed: NO Tag: TAG20121205T155423
Piece Name: +FRA/ORCL/backupset/2012_12_05/ncnnf0_tag20121205t155423_0.8602.801244481
Control File Included: Ckp SCN: 4865237 Ckp time: 05-DEC-12
BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
7 Full 22.39M DISK 00:00:01 05-DEC-12
BP Key: 7 Status: AVAILABLE Compressed: NO Tag: TAG20121205T155441
Piece Name: +FRA/ORCL/autobackup/2012_12_05/s_801244481.15610.801244481
SPFILE Included: Modification time: 05-DEC-12
SPFILE db_unique_name: ORCL
Control File Included: Ckp SCN: 4865243 Ckp time: 05-DEC-12
RMAN> exit
Recovery Manager complete.
[oracle@subhen-db01 admin]$ echo $ORACLE_SID
ORCL_1
[oracle@subhen-db01 admin]$ export ORACLE_SID=DUPDB
[oracle@subhen-db01 admin]$ rman target sys/sw2tesb@ORCL auxiliary /
Recovery Manager: Release 11.2.0.3.0 - Production on Wed Dec 5 16:35:38 2012
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
connected to target database: ORCL (DBID=3812261009)
connected to auxiliary database: DUPDB (not mounted)
RMAN> ^C
user interrupt received
RMAN>
RMAN> exit
Recovery Manager complete.
[oracle@subhen-db01 admin]$ rman target sys/sw2tesb@ORCL auxiliary /
Recovery Manager: Release 11.2.0.3.0 - Production on Wed Dec 5 16:35:54 2012
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
connected to target database: ORCL (DBID=3812261009)
connected to auxiliary database: DUPDB (not mounted)
RMAN> run {
set until SCN 4865237;
DUPLICATE TARGET DATABASE TO DUPDB;
}
2> 3> 4>
executing command: SET until clause
Starting Duplicate Db at 05-DEC-12
using target database control file instead of recovery catalog
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: SID=578 device type=DISK
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 10154999808 bytes
Fixed Size 2237128 bytes
Variable Size 3825208632 bytes
Database Buffers 6274678784 bytes
Redo Buffers 52875264 bytes
contents of Memory Script:
{
set until scn 4865237;
sql clone "alter system set control_files =
''+DATA/DUPDB/controlfile/current.3922.801246979'', ''+FRA/DUPDB/controlfile/current.15606.801246979'' comment=
''Set by RMAN'' scope=spfile";
sql clone "alter system set db_name =
''ORCL'' comment=
''Modified by RMAN duplicate'' scope=spfile";
sql clone "alter system set db_unique_name =
''DUPDB'' 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 control_files = ''+DATA/DUPDB/controlfile/current.3922.801246979'', ''+FRA/DUPDB/controlfile/current.15606.801246979'' comment= ''Set by RMAN'' scope=spfile
sql statement: alter system set db_name = ''ORCL'' comment= ''Modified by RMAN duplicate'' scope=spfile
sql statement: alter system set db_unique_name = ''DUPDB'' comment= ''Modified by RMAN duplicate'' scope=spfile
Oracle instance shut down
Oracle instance started
Total System Global Area 10154999808 bytes
Fixed Size 2237128 bytes
Variable Size 3825208632 bytes
Database Buffers 6274678784 bytes
Redo Buffers 52875264 bytes
Starting restore at 05-DEC-12
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: SID=578 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 +FRA/ORCL/backupset/2012_12_05/ncnnf0_tag20121205t155423_0.8602.801244481
channel ORA_AUX_DISK_1: piece handle=+FRA/ORCL/backupset/2012_12_05/ncnnf0_tag20121205t155423_0.8602.801244481 tag=TAG20121205T155423
channel ORA_AUX_DISK_1: restored backup piece 1
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:03
output file name=+DATA/DUPDB/controlfile/current.3922.801246979
output file name=+FRA/DUPDB/controlfile/current.15606.801246979
Finished restore at 05-DEC-12
database mounted
RMAN-05529: WARNING: DB_FILE_NAME_CONVERT resulted in invalid ASM names; names changed to disk group only.
contents of Memory Script:
{
set until scn 4865237;
set newname for datafile 1 to
"+DATA";
set newname for datafile 2 to
"+DATA";
set newname for datafile 3 to
"+DATA";
set newname for datafile 4 to
"+DATA";
set newname for datafile 5 to
"+DATA";
set newname for datafile 6 to
"+DATA";
set newname for datafile 7 to
"+DATA";
restore
clone database
;
}
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
Starting restore at 05-DEC-12
using channel ORA_AUX_DISK_1
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 00001 to +DATA
channel ORA_AUX_DISK_1: restoring datafile 00002 to +DATA
channel ORA_AUX_DISK_1: restoring datafile 00003 to +DATA
channel ORA_AUX_DISK_1: restoring datafile 00004 to +DATA
channel ORA_AUX_DISK_1: restoring datafile 00005 to +DATA
channel ORA_AUX_DISK_1: restoring datafile 00006 to +DATA
channel ORA_AUX_DISK_1: restoring datafile 00007 to +DATA
channel ORA_AUX_DISK_1: reading from backup piece +FRA/ORCL/backupset/2012_12_05/nnndf0_tag20121205t155423_0.10261.801244465
channel ORA_AUX_DISK_1: piece handle=+FRA/ORCL/backupset/2012_12_05/nnndf0_tag20121205t155423_0.10261.801244465 tag=TAG20121205T155423
channel ORA_AUX_DISK_1: restored backup piece 1
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:15
Finished restore at 05-DEC-12
contents of Memory Script:
{
switch clone datafile all;
}
executing Memory Script
datafile 1 switched to datafile copy
input datafile copy RECID=8 STAMP=801247018 file name=+DATA/DUPDB/datafile/system.3924.801247003
datafile 2 switched to datafile copy
input datafile copy RECID=9 STAMP=801247018 file name=+DATA/DUPDB/datafile/sysaux.3923.801247003
datafile 3 switched to datafile copy
input datafile copy RECID=10 STAMP=801247018 file name=+DATA/DUPDB/datafile/undotbs1.3926.801247003
datafile 4 switched to datafile copy
input datafile copy RECID=11 STAMP=801247018 file name=+DATA/DUPDB/datafile/users.3929.801247003
datafile 5 switched to datafile copy
input datafile copy RECID=12 STAMP=801247018 file name=+DATA/DUPDB/datafile/undotbs2.3928.801247003
datafile 6 switched to datafile copy
input datafile copy RECID=13 STAMP=801247018 file name=+DATA/DUPDB/datafile/undotbs3.3927.801247003
datafile 7 switched to datafile copy
input datafile copy RECID=14 STAMP=801247018 file name=+DATA/DUPDB/datafile/ggate_data.3925.801247003
contents of Memory Script:
{
set until scn 4865237;
recover
clone database
delete archivelog
;
}
executing Memory Script
executing command: SET until clause
Starting recover at 05-DEC-12
using channel ORA_AUX_DISK_1
starting media recovery
archived log for thread 1 with sequence 13 is already on disk as file +FRA/ORCL/archivelog/2012_12_05/thread_1_seq_13.7571.801245801
archived log for thread 2 with sequence 16 is already on disk as file +FRA/ORCL/archivelog/2012_12_05/thread_2_seq_16.9500.801245799
archived log for thread 3 with sequence 23 is already on disk as file +FRA/ORCL/archivelog/2012_12_05/thread_3_seq_23.9380.801245797
archived log file name=+FRA/ORCL/archivelog/2012_12_05/thread_3_seq_23.9380.801245797 thread=3 sequence=23
archived log file name=+FRA/ORCL/archivelog/2012_12_05/thread_1_seq_13.7571.801245801 thread=1 sequence=13
archived log file name=+FRA/ORCL/archivelog/2012_12_05/thread_2_seq_16.9500.801245799 thread=2 sequence=0
media recovery complete, elapsed time: 00:00:01
Finished recover at 05-DEC-12
Oracle instance started
Total System Global Area 10154999808 bytes
Fixed Size 2237128 bytes
Variable Size 3825208632 bytes
Database Buffers 6274678784 bytes
Redo Buffers 52875264 bytes
contents of Memory Script:
{
sql clone "alter system set db_name =
''DUPDB'' comment=
''Reset to original value by RMAN'' scope=spfile";
sql clone "alter system reset db_unique_name scope=spfile";
shutdown clone immediate;
startup clone nomount;
}
executing Memory Script
sql statement: alter system set db_name = ''DUPDB'' comment= ''Reset to original value by RMAN'' scope=spfile
sql statement: alter system reset db_unique_name scope=spfile
Oracle instance shut down
connected to auxiliary database (not started)
Oracle instance started
Total System Global Area 10154999808 bytes
Fixed Size 2237128 bytes
Variable Size 3825208632 bytes
Database Buffers 6274678784 bytes
Redo Buffers 52875264 bytes
sql statement: CREATE CONTROLFILE REUSE SET DATABASE "DUPDB" RESETLOGS ARCHIVELOG
MAXLOGFILES 1000
MAXLOGMEMBERS 5
MAXDATAFILES 1024
MAXINSTANCES 32
MAXLOGHISTORY 292
LOGFILE
GROUP 1 ( '+DATA', '+FRA' ) SIZE 256 M REUSE,
GROUP 2 ( '+DATA', '+FRA' ) SIZE 256 M REUSE,
GROUP 7 ( '+DATA', '+FRA' ) SIZE 256 M REUSE,
GROUP 8 ( '+DATA', '+FRA' ) SIZE 256 M REUSE
DATAFILE
'+DATA/DUPDB/datafile/system.3924.801247003'
CHARACTER SET AL32UTF8
sql statement: ALTER DATABASE ADD LOGFILE
INSTANCE 'i2'
GROUP 3 ( '+DATA', '+FRA' ) SIZE 256 M REUSE,
GROUP 4 ( '+DATA', '+FRA' ) SIZE 256 M REUSE
sql statement: ALTER DATABASE ADD LOGFILE
INSTANCE 'i3'
GROUP 5 ( '+DATA', '+FRA' ) SIZE 256 M REUSE,
GROUP 6 ( '+DATA', '+FRA' ) SIZE 256 M REUSE
contents of Memory Script:
{
set newname for tempfile 1 to
"+DATA";
switch clone tempfile all;
catalog clone datafilecopy "+DATA/DUPDB/datafile/sysaux.3923.801247003",
"+DATA/DUPDB/datafile/undotbs1.3926.801247003",
"+DATA/DUPDB/datafile/users.3929.801247003",
"+DATA/DUPDB/datafile/undotbs2.3928.801247003",
"+DATA/DUPDB/datafile/undotbs3.3927.801247003",
"+DATA/DUPDB/datafile/ggate_data.3925.801247003";
switch clone datafile all;
}
executing Memory Script
executing command: SET NEWNAME
renamed tempfile 1 to +DATA in control file
cataloged datafile copy
datafile copy file name=+DATA/DUPDB/datafile/sysaux.3923.801247003 RECID=1 STAMP=801247056
cataloged datafile copy
datafile copy file name=+DATA/DUPDB/datafile/undotbs1.3926.801247003 RECID=2 STAMP=801247056
cataloged datafile copy
datafile copy file name=+DATA/DUPDB/datafile/users.3929.801247003 RECID=3 STAMP=801247056
cataloged datafile copy
datafile copy file name=+DATA/DUPDB/datafile/undotbs2.3928.801247003 RECID=4 STAMP=801247056
cataloged datafile copy
datafile copy file name=+DATA/DUPDB/datafile/undotbs3.3927.801247003 RECID=5 STAMP=801247056
cataloged datafile copy
datafile copy file name=+DATA/DUPDB/datafile/ggate_data.3925.801247003 RECID=6 STAMP=801247056
datafile 2 switched to datafile copy
input datafile copy RECID=1 STAMP=801247056 file name=+DATA/DUPDB/datafile/sysaux.3923.801247003
datafile 3 switched to datafile copy
input datafile copy RECID=2 STAMP=801247056 file name=+DATA/DUPDB/datafile/undotbs1.3926.801247003
datafile 4 switched to datafile copy
input datafile copy RECID=3 STAMP=801247056 file name=+DATA/DUPDB/datafile/users.3929.801247003
datafile 5 switched to datafile copy
input datafile copy RECID=4 STAMP=801247056 file name=+DATA/DUPDB/datafile/undotbs2.3928.801247003
datafile 6 switched to datafile copy
input datafile copy RECID=5 STAMP=801247056 file name=+DATA/DUPDB/datafile/undotbs3.3927.801247003
datafile 7 switched to datafile copy
input datafile copy RECID=6 STAMP=801247056 file name=+DATA/DUPDB/datafile/ggate_data.3925.801247003
contents of Memory Script:
{
Alter clone database open resetlogs;
}
executing Memory Script
database opened
Finished Duplicate Db at 05-DEC-12
RMAN> exit
Recovery Manager complete.
[oracle@subhen-db01 admin]$ ps -fe|grep pmon|grep -i sys
oracle 1591 1 0 16:37 ? 00:00:00 ora_pmon_DUPDB
[oracle@subhen-db01 admin]$ . oraenv
ORACLE_SID = [DUPDB] ?
ORACLE_HOME = [/home/oracle] ? ^C
[oracle@subhen-db01 admin]$ export ORACLE_SID=DUPDB
[oracle@subhen-db01 admin]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.3.0 Production on Wed Dec 5 16:38:51 2012
Copyright (c) 1982, 2011, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Data Mining and Real Application Testing options
SQL> select name,open_mode,database_role from v$database;
NAME OPEN_MODE DATABASE_ROLE
--------- -------------------- ----------------
DUPDB READ WRITE PRIMARY
SQL> desc dba_data_files;
Name Null? Type
----------------------------------------- -------- ----------------------------
FILE_NAME VARCHAR2(513)
FILE_ID NUMBER
TABLESPACE_NAME VARCHAR2(30)
BYTES NUMBER
BLOCKS NUMBER
STATUS VARCHAR2(9)
RELATIVE_FNO NUMBER
AUTOEXTENSIBLE VARCHAR2(3)
MAXBYTES NUMBER
MAXBLOCKS NUMBER
INCREMENT_BY NUMBER
USER_BYTES NUMBER
USER_BLOCKS NUMBER
ONLINE_STATUS VARCHAR2(7)
SQL> select FILE_NAME from dba_data_files;
FILE_NAME
--------------------------------------------------------------------------------
+DATA/DUPDB/datafile/system.3924.801247003
+DATA/DUPDB/datafile/sysaux.3923.801247003
+DATA/DUPDB/datafile/undotbs1.3926.801247003
+DATA/DUPDB/datafile/users.3929.801247003
+DATA/DUPDB/datafile/undotbs2.3928.801247003
+DATA/DUPDB/datafile/undotbs3.3927.801247003
+DATA/DUPDB/datafile/ggate_data.3925.801247003
7 rows selected.
SQL> show parameter pfile
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
spfile string /oracle/app/database/11.2.0.3/
dbhome_1/dbs/spfileDUPDB.or
a
SQL> host
*************************************
Now try to create a pfile from spfile
*************************************
[oracle@subhen-db01 tmp]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.3.0 Production on Wed Dec 5 16:46:25 2012
Copyright (c) 1982, 2011, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Data Mining and Real Application Testing options
SQL> show parameter db_name
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_name string DUPDB
SQL> create pfile='/tmp/initDUPDB.ora' from spfile;
create pfile='/tmp/initDUPDB.ora' from spfile
*
ERROR at line 1:
ORA-01565: error in identifying file
'/oracle/app/database/11.2.0.3/dbhome_1/dbs/spfileDUPDB.ora'
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3
SQL> create pfile='/tmp/initDUPDB.ora' from memory;
File created.
SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Data Mining and Real Application Testing options
************************************************************
Modify the pfile to include the cluster related parameters
************************************************************
[oracle@subhen-db01 tmp]$ cat /tmp/initDUPDB.ora
# Oracle init.ora parameter file generated by instance DUPDB on 12/05/2012 16:47:16
audit_file_dest='/oracle/app/admin/DUPDB/adump'
audit_trail='DB'
background_dump_dest='/oracle/app/diag/rdbms/DUPDB/DUPDB/trace' #Deprecate parameter
*.cluster_database=TRUE
*.cluster_database_instances = 3
*.undo_management=AUTO
compatible='11.2.0.0.0'
control_files='+DATA/DUPDB/controlfile/current.3922.801246979'
control_files='+FRA/DUPDB/controlfile/current.15606.801246979' # Restore Controlfile
core_dump_dest='/oracle/app/diag/rdbms/DUPDB/DUPDB/cdump'
db_block_size=8192
db_create_file_dest='+DATA'
db_domain=''
db_name='DUPDB' # Reset to original value by RMAN
db_recovery_file_dest='+FRA'
db_recovery_file_dest_size=20G
diagnostic_dest='/oracle/app'
dispatchers='(PROTOCOL=TCP) (SERVICE=DUPDBXDB)'
log_archive_format='%t_%s_%r.dbf'
log_buffer=18694144 # log buffer update
memory_max_target=9728M
open_cursors=500
optimizer_dynamic_sampling=2
optimizer_mode='ALL_ROWS'
pga_aggregate_target=2422M
plsql_warnings='DISABLE:ALL' # PL/SQL warnings at init.ora
processes=1000
query_rewrite_enabled='TRUE'
remote_listener='subhen-dbscan.xxxx.xxxxx.xxxx.com.au:1621'
remote_login_passwordfile='EXCLUSIVE'
result_cache_max_size=37280K
sga_target=7296M
skip_unusable_indexes=TRUE
#undo_tablespace='UNDOTBS1'
user_dump_dest='/oracle/app/diag/rdbms/DUPDB/DUPDB/trace' #Deprecate parameter
DUPDB_1.undo_tablespace=UNDOTBS1
DUPDB_1.instance_name=DUPDB_1
DUPDB_1.instance_number=1
DUPDB_1.thread=1
DUPDB_2.instance_name=DUPDB_2
DUPDB_2.instance_number=2
DUPDB_2.thread=2
DUPDB_2.undo_tablespace=UNDOTBS2
DUPDB_3.instance_name=DUPDB_3
DUPDB_3.instance_number=3
DUPDB_3.thread=3
DUPDB_3.undo_tablespace=UNDOTBS3
*********************************************
create a spfile from pfile
**********************************************
[oracle@subhen-db01 tmp]$ export ORACLE_SID=DUPDB_1
[oracle@subhen-db01 tmp]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.3.0 Production on Wed Dec 5 17:02:31 2012
Copyright (c) 1982, 2011, Oracle. All rights reserved.
Connected to an idle instance.
SQL> create spfile='+DATA/DUPDB/spfileDUPDB.ora' from pfile='/tmp/initDUPDB.ora';
File created.
SQL> exit
Disconnected
[oracle@subhen-db01 tmp]$ echo $ORACLE_SID
DUPDB_1
*************************************
create passwd file
*************************************
[oracle@subhen-db01 tmp]$ cd /oracle/app/database/11.2.0.3/dbhome_1/dbs/
[oracle@subhen-db01 dbs]$ ls -ltr|grep -i pw
-rw-rwx---+ 1 oracle oinstall 1536 Nov 14 12:03 orapwORCL
[oracle@subhen-db01 dbs]$ orapwd file=orapwDUPDB entries=10 ignorecase=y
Enter password for SYS:
[oracle@subhen-db01 dbs]$ ls -ltr|grep -i pw
-rw-rwx---+ 1 oracle oinstall 1536 Nov 14 12:03 orapwORCL
-rw-r-----+ 1 oracle oinstall 2560 Dec 5 17:07 orapwDUPDB
[oracle@subhen-db01 dbs]$ echo $ORACLE_SID
DUPDB_1
[oracle@subhen-db01 dbs]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.3.0 Production on Wed Dec 5 17:08:19 2012
Copyright (c) 1982, 2011, Oracle. All rights reserved.
Connected to an idle instance.
SQL> exit
Disconnected
******************************************************
init file should point to the spfile in ASM location
******************************************************
[oracle@subhen-db01 dbs]$ ls -ltr|grep init
[oracle@subhen-db01 dbs]$ vi initDUPDB_1.ora
[oracle@subhen-db01 dbs]$ cat initDUPDB_1.ora
spfile='+DATA/DUPDB/spfileDUPDB.ora'
[oracle@subhen-db01 dbs]$ echo $ORACLE_SID
DUPDB_1
[oracle@subhen-db01 dbs]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.3.0 Production on Wed Dec 5 17:10:48 2012
Copyright (c) 1982, 2011, Oracle. All rights reserved.
Connected to an idle instance.
SQL> startup mount
ORA-32004: obsolete or deprecated parameter(s) specified for RDBMS instance
ORACLE instance started.
Total System Global Area 1.0155E+10 bytes
Fixed Size 2237128 bytes
Variable Size 3892317496 bytes
Database Buffers 6207569920 bytes
Redo Buffers 52875264 bytes
Database mounted.
SQL> alter database open;
Database altered.
SQL> select INST_ID,INSTANCE_NAME,STATUS from gv$instance;
INST_ID INSTANCE_NAME STATUS
---------- ---------------- ------------
1 DUPDB_1 OPEN
SQL> show parameter pfile
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
spfile string +DATA/DUPDB/spfilesysnwh
ds.ora
SQL> show parameter undo
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
undo_management string AUTO
undo_retention integer 900
undo_tablespace string UNDOTBS1
SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Data Mining and Real Application Testing options
No comments:
Post a Comment