So there I was, working on a project to duplicate a database from a volume copy at the storage level with the database being shutdown.
Sounds pretty simple right? Wrong! Put the database on ASM and it becomes complicated and convoluted.
Volume1 has ASM disk group in the fashion +DG1/db01/datafile, +DG1/db01/onlinelog +DG1/db01/tempfile
Volume1 will be copied to Volume2 at the storage level. What happens to all the ASM disk group?
The ASM disk group will need to be renamed using renamdg. Great! (with a little sarcasm)
So now the ASM disk groups will be +DG2/db01/datafile, +DG2/db01/onlinelog +DG2/db01/tempfile
What’s wrong with that picture?
Given I am an ASM noob, I did reach out to other and still waiting for a respond I can be confident with.
Now, if the database was not on ASM, I would be done with the project already.
1 hour to create a test case without using ASM, 8 hours too create action plan using ASM and still not completed.
Create and Edit Control File SQL
SQL> alter database backup controlfile to trace as '/tmp/cf.sql';
Parameter Copied from Source (DB01)
[oracle@arrow:db01]/u01/app/oracle/product/11.2.0.3/dbhome_1/dbs
$ cat initdb02.ora
*.audit_file_dest='/u01/app/oracle/admin/adump'
*.audit_trail='none'
*.compatible='11.2.0.3.0'
*.db_block_size=8192
*.db_create_file_dest='/oradata'
*.control_files='/oradata/DB02/controlfile/o1_mf_9bb5brjc_.ctl','/oradata/fra/DB02/controlfile/o1_mf_9bb5brxx_.ctl'
*.db_domain=''
*.db_name='db02'
*.db_recovery_file_dest='/oradata/fra'
*.db_recovery_file_dest_size=4g
*.diagnostic_dest='/u01/app/oracle'
*.event='10795 trace name context forever, level 2'
*.fast_start_mttr_target=300
*.java_pool_size=0
*.local_listener='(ADDRESS=(PROTOCOL=tcp)(HOST=)(PORT=1531))'
*.pga_aggregate_target=268435456
*.processes=100
*.remote_login_passwordfile='EXCLUSIVE'
*.sga_target=805306368
*.undo_tablespace='UNDOTBS'
[oracle@arrow:db01]/u01/app/oracle/product/11.2.0.3/dbhome_1/dbs
Shutdown database from Source (DB01) and perform copy
$ cd /oradata/
[oracle@arrow:db01]/oradata
$ ls *
DB01:
controlfile datafile onlinelog
fra:
DB01
[oracle@arrow:db01]/oradata
$ cp -rp DB01/ DB02/
[oracle@arrow:db01]/oradata
$ cd fra/
[oracle@arrow:db01]/oradata/fra
$ cp -rp DB01/ DB02/
Clone database from Source (DB01)
[oracle@arrow:db01]/oradata/fra
$ db02
The Oracle base remains unchanged with value /u01/app/oracle
IPC Resources for ORACLE_SID "db02" :
Shared Memory
ID KEY
No shared memory segments used
Semaphores:
ID KEY
No semaphore resources used
Oracle Instance not alive for sid "db02"
[oracle@arrow:db02]/oradata/fra
$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.3.0 Production on Thu Dec 12 17:04:23 2013
Copyright (c) 1982, 2011, Oracle. All rights reserved.
Connected to an idle instance.
SQL> set echo on
SQL> @/tmp/cf.sql
SQL> STARTUP NOMOUNT
ORACLE instance started.
Total System Global Area 801701888 bytes
Fixed Size 2232640 bytes
Variable Size 222301888 bytes
Database Buffers 570425344 bytes
Redo Buffers 6742016 bytes
SQL> CREATE CONTROLFILE REUSE SET DATABASE "DB02" RESETLOGS ARCHIVELOG
2 MAXLOGFILES 16
3 MAXLOGMEMBERS 2
4 MAXDATAFILES 30
5 MAXINSTANCES 1
6 MAXLOGHISTORY 292
7 LOGFILE
8 GROUP 1 (
9 '/oradata/DB02/onlinelog/o1_mf_1_9bb5bt5g_.log',
10 '/oradata/fra/DB02/onlinelog/o1_mf_1_9bb5btk6_.log'
11 ) SIZE 100M BLOCKSIZE 512,
12 GROUP 2 (
13 '/oradata/DB02/onlinelog/o1_mf_2_9bb5btq3_.log',
14 '/oradata/fra/DB02/onlinelog/o1_mf_2_9bb5bv4g_.log'
15 ) SIZE 100M BLOCKSIZE 512,
16 GROUP 3 (
17 '/oradata/DB02/onlinelog/o1_mf_3_9bb5bvc1_.log',
18 '/oradata/fra/DB02/onlinelog/o1_mf_3_9bb5cny5_.log'
19 ) SIZE 100M BLOCKSIZE 512
20 -- STANDBY LOGFILE
21 DATAFILE
22 '/oradata/DB02/datafile/o1_mf_system_9bb5cx2c_.dbf',
23 '/oradata/DB02/datafile/o1_mf_sysaux_9bb5ff55_.dbf',
24 '/oradata/DB02/datafile/o1_mf_undotbs_9bb5gsjs_.dbf',
25 '/oradata/DB02/datafile/o1_mf_users_9bb5j5wx_.dbf'
26 CHARACTER SET AL32UTF8
27 ;
Control file created.
SQL> ALTER DATABASE OPEN RESETLOGS;
Database altered.
SQL> ALTER TABLESPACE TEMP ADD TEMPFILE '/oradata/DB02/datafile/o1_mf_temp_9bb5j4rf_.tmp'
2 SIZE 268435456 REUSE AUTOEXTEND ON NEXT 268435456 MAXSIZE 8193M;
Tablespace altered.
SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning option
Check DBID for Cloned DB (Same as Source)
[oracle@arrow:db01]/oradata
$ rman target /
Recovery Manager: Release 11.2.0.3.0 - Production on Thu Dec 12 17:34:06 2013
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
connected to target database: DB01 (DBID=1464916248)
RMAN>
[oracle@arrow:db02]/oradata/fra
$ rman target /
Recovery Manager: Release 11.2.0.3.0 - Production on Thu Dec 12 17:05:44 2013
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
connected to target database: DB02 (DBID=1464916248)
RMAN>
Change DBID for cloned DB
[oracle@arrow:db02]/oradata/fra
$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.3.0 Production on Thu Dec 12 17:05:54 2013
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 option
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount;
ORACLE instance started.
Total System Global Area 801701888 bytes
Fixed Size 2232640 bytes
Variable Size 222301888 bytes
Database Buffers 570425344 bytes
Redo Buffers 6742016 bytes
Database mounted.
SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning option
[oracle@arrow:db02]/oradata/fra
$ nid target=sys dbname=DB02 logfile=/tmp/nid.log
Password:
[oracle@arrow:db02]/oradata/fra
$ rman target /
Recovery Manager: Release 11.2.0.3.0 - Production on Thu Dec 12 17:08:14 2013
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
connected to target database: DB02 (DBID=1464916248, not open)
RMAN> exit
Recovery Manager complete.
[oracle@arrow:db02]/oradata/fra
$ cat /tmp/nid.log
DBNEWID: Release 11.2.0.3.0 - Production on Thu Dec 12 17:08:06 2013
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
Connected to database DB02 (DBID=1464916248)
Connected to server version 11.2.0
Control Files in database:
/oradata/DB02/controlfile/o1_mf_9bb5brjc_.ctl
/oradata/fra/DB02/controlfile/o1_mf_9bb5brxx_.ctl
NID-00144: New name for database DB02 is the same as current name DB02
Change of database name and ID failed during validation - database is intact.
DBNEWID - Completed with validation errors.
Change DBID omitting DBNAME (changed from CF create)
[oracle@arrow:db02]/oradata/fra
$ nid target=sys logfile=/tmp/nid.log
Password:
[oracle@arrow:db02]/oradata/fra
$ cat /tmp/nid.log
DBNEWID: Release 11.2.0.3.0 - Production on Thu Dec 12 17:10:58 2013
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
Connected to database DB02 (DBID=1464916248)
Connected to server version 11.2.0
Control Files in database:
/oradata/DB02/controlfile/o1_mf_9bb5brjc_.ctl
/oradata/fra/DB02/controlfile/o1_mf_9bb5brxx_.ctl
Changing database ID from 1464916248 to 1581437733
Control File /oradata/DB02/controlfile/o1_mf_9bb5brjc_.ctl - modified
Control File /oradata/fra/DB02/controlfile/o1_mf_9bb5brxx_.ctl - modified
Datafile /oradata/DB02/datafile/o1_mf_system_9bb5cx2c_.db - dbid changed
Datafile /oradata/DB02/datafile/o1_mf_sysaux_9bb5ff55_.db - dbid changed
Datafile /oradata/DB02/datafile/o1_mf_undotbs_9bb5gsjs_.db - dbid changed
Datafile /oradata/DB02/datafile/o1_mf_users_9bb5j5wx_.db - dbid changed
Datafile /oradata/DB02/datafile/o1_mf_temp_9bb5j4rf_.tm - dbid changed
Control File /oradata/DB02/controlfile/o1_mf_9bb5brjc_.ctl - dbid changed
Control File /oradata/fra/DB02/controlfile/o1_mf_9bb5brxx_.ctl - dbid changed
Instance shut down
Database ID for database DB02 changed to 1581437733.
All previous backups and archived redo logs for this database are unusable.
Database is not aware of previous backups and archived logs in Recovery Area.
Database has been shutdown, open database with RESETLOGS option.
Succesfully changed database ID.
DBNEWID - Completed succesfully.
Check DBID for Cloned DB
[oracle@arrow:db02]/oradata/fra
$ rman target /
Recovery Manager: Release 11.2.0.3.0 - Production on Thu Dec 12 17:11:16 2013
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
connected to target database (not started)
RMAN> startup mount;
Oracle instance started
database mounted
Total System Global Area 801701888 bytes
Fixed Size 2232640 bytes
Variable Size 222301888 bytes
Database Buffers 570425344 bytes
Redo Buffers 6742016 bytes
RMAN> list incarnation;
using target database control file instead of recovery catalog
List of Database Incarnations
DB Key Inc Key DB Name DB ID STATUS Reset SCN Reset Time
------- ------- -------- ---------------- --- ---------- ----------
1 1 DB02 1581437733 CURRENT 360639 12-DEC-2013 17:04:50
RMAN> alter database open;
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of alter db command at 12/12/2013 17:12:14
ORA-01589: must use RESETLOGS or NORESETLOGS option for database open
Open database resetlogs
RMAN> alter database open resetlogs;
database opened
RMAN> exit
Recovery Manager complete.
[oracle@arrow:db02]/oradata/fra
$
Thinking Out Loud 