Thinking Out Loud

December 13, 2013

Cloning Database without ASM

Filed under: 11g,ASM — mdinh @ 2:09 am

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
$
About these ads

Leave a Comment »

No comments yet.

RSS feed for comments on this post. TrackBack URI

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

The Rubric Theme Blog at WordPress.com.

Follow

Get every new post delivered to your Inbox.

Join 273 other followers

%d bloggers like this: