Hendry's "Oracle and SQL Server DBA Stuff"

Oracle and SQL Server Database Solutions for DBA's please visit http://hendrydba.com for the latest posts. Thanks

Duplicate database from non ASM to ASM Env.

Posted by Hendry chinnapparaj on May 17, 2010

Source database – cpstest – 10gr2

Duplicate target database – cpstest – 10gr2

Target asm – +ASM – 11Gr2

Rman target /

Advantages of using this Strategy for read only datafiles database

——————————————————————————-

1)     The Methodology is simple compare to Data Guard complicated approach

2)     There is no configuration operations or overhead on production unlike the Data Guard Approach

3)     The readonly datafiles can be restored a week before the cutover date during the first restoration to new server. The second restoration during cut-over date skips the (1.5 Terrabytes readonly data) saving time / minimizing downtime for the change. Note:- Nearly 80% of database size constitutes readonly data. The Total db size is about 1.9 Terra Bytes.

Steps

1. Backup the primary database. 

devu009 $ nohup rman target / cmdfile=backup1.rcv log=backup1.log &

[1]     1601790

devu009 $ Sending output to nohup.out

devu009 $ cat backup1.rcv

run

{

backup check logical incremental level 0 database filesperset 8 format ‘/u01/oracle/backup/cpstest_dbbackup1_%U’ force;

sql ‘alter system archive log current’;

backup format ‘/u01/oracle/backup/cpstest_logbackup1_%U’ archivelog all not backed up 1 times;

}

Exit

RMAN> show all

2> ;

using target database control file instead of recovery catalog

RMAN configuration parameters are:

CONFIGURE RETENTION POLICY TO RECOVERY WINDOW OF 30 DAYS;

CONFIGURE BACKUP OPTIMIZATION ON;

CONFIGURE DEFAULT DEVICE TYPE TO DISK;

CONFIGURE CONTROLFILE AUTOBACKUP ON;

CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO ‘CF_%F’;

CONFIGURE DEVICE TYPE DISK PARALLELISM 1 BACKUP TYPE TO COMPRESSED BACKUPSET;

CONFIGURE DATAFILE BACKUP COPIES FOR DEVICE TYPE DISK TO 1;

CONFIGURE ARCHIVELOG BACKUP COPIES FOR DEVICE TYPE DISK TO 1;

CONFIGURE MAXSETSIZE TO UNLIMITED;

CONFIGURE ENCRYPTION FOR DATABASE OFF;

CONFIGURE ENCRYPTION ALGORITHM ‘AES128’;

CONFIGURE ARCHIVELOG DELETION POLICY TO NONE;

CONFIGURE SNAPSHOT CONTROLFILE NAME TO ‘/u02/oradata/cpstest_bkp/snapcf_cpstest.f’;

2 Determine how much disk space will be required.

select DF.TOTAL/1048576 “DataFile Size Mb”, LOG.TOTAL/1048576 “Redo Log Size Mb”, CONTROL.TOTAL/1048576 “Control File Size Mb”, (DF.TOTAL + LOG.TOTAL + CONTROL.TOTAL)/1048576 “Total Size Mb” from dual, (select sum(a.bytes) TOTAL from dba_data_files a) DF, (select sum(b.bytes) TOTAL from v$log b) LOG, (select sum((cffsz+1)*cfbsz) TOTAL from x$kcccf c) CONTROL; 

DataFile Size Mb Redo Log Size Mb Control File Size Mb Total Size Mb

—————- —————- ——————– ————-

            5683              400             21.09375    6104.09375

1 row selected.

select count(*) from v$datafile;

COUNT(*)

———-

       183

1 row selected.

3 Ensuring you have enough space within your ASM instance.

select NAME, STATE, TOTAL_MB, FREE_MB from v$asm_diskgroup;

SQL> select NAME, STATE, TOTAL_MB, FREE_MB from v$asm_diskgroup;

NAME                           STATE         TOTAL_MB    FREE_MB

—————————— ———– ———- ———-

DATA                           MOUNTED         102400      69487

4 Making the backup available for the duplicate process.

At source database

Rman target /

List backup;

Copy all the files from /u01/oracle/backup/* and the last controlfile /u01/oracle/product/10.2/db_1/dbs/CF_c-248426826-20100428-07 to target server

5 Creating the init.ora & administration directories for the duplicate database.

devu009n3 $ cat initcpstest.ora

*.db_cache_size=159383552

*.java_pool_size=4194304

*.large_pool_size=4194304

*.shared_pool_size=243269632

*.streams_pool_size=0

*.aq_tm_processes=2

*.audit_trail=’DB’

*.background_dump_dest=’/u01/oracle/product/10.2.0/db_1/admin/cpstest/bdump’

*.cluster_database=FALSE

*.compatible=’10.2.0.4.0′

*.control_files=’+DATA/control01.ctl’

*.core_dump_dest=’/u01/oracle/product/10.2.0/db_1/admin/cpstest/cdump’

*.db_block_size=8192

*.db_domain=”

*.db_file_multiblock_read_count=16

*.db_create_file_dest=’+DATA’

*.db_files=1024

*.db_name=’cpstest’

*.db_recovery_file_dest=’+DATA’

*.db_recovery_file_dest_size=16106127360

*.global_names=FALSE

*.instance_name=’cpstest’

*.job_queue_processes=2

*.local_listener='(address=(protocol=TCP) (host=devu009n3) (port=1521))’

*.log_archive_format=’ARC%S_%R.%T’

*.log_buffer=2044928

*.log_checkpoint_interval=10000

*.log_checkpoint_timeout=1800

*.max_dump_file_size=’10240′

*.nls_length_semantics=’BYTE’

*.O7_DICTIONARY_ACCESSIBILITY=TRUE

*.open_cursors=300

*.os_authent_prefix=”

*.parallel_max_servers=5

*.pga_aggregate_target=209715200

*.processes=500

*.remote_login_passwordfile=’EXCLUSIVE’

*.resource_manager_plan=”

*.sessions=555

*.sga_max_size=419430400

*.sga_target=419430400

*.sort_area_retained_size=65536

*.sort_area_size=65536

*.standby_file_management=’AUTO’

*.star_transformation_enabled=’true’

*.statistics_level=’typical’

*.undo_management=’AUTO’

*.undo_retention=900

*.undo_tablespace=’undotbs’

*.user_dump_dest=’/u01/oracle/product/10.2.0/db_1/admin/cpstest/udump’

*.utl_file_dir=’*’

*.workarea_size_policy=’auto’

export ORACLE_SID=cpstest

export ORACLE_HOME= /u01/oracle/product/10.2.0/db_1

cd $ORACLE_HOME/dbs

orapw file=orapwdcpstest password=*** entries=10

sqlplus / as sysdba

startup nomount

6. Ensuring SQL*NET connections to primary database and RMAN catalog are working.

devu009n3 $ pwd

/u01/oracle/product/10.2.0/db_1/network/admin

devu009n3 $ cat tnsnames.ora

# tnsnames.ora Network Configuration File: /u01/oracle/product/10.2.0/db_1/network/admin/tnsnames.ora

# Generated by Oracle configuration tools.

CPSTEST_SOURCE =

  (DESCRIPTION =

    (ADDRESS_LIST =

      (ADDRESS = (PROTOCOL = TCP)(HOST = devu009)(PORT = 1522))

    )

    (CONNECT_DATA =

      (SERVICE_NAME = cpstest)

    )

  )

devu009n3 $ tnsping cpstest_source

TNS Ping Utility for IBM/AIX RISC System/6000: Version 10.2.0.4.0 – Production on 28-APR-2010 18:24:07

Copyright (c) 1997,  2007, Oracle.  All rights reserved.

Used parameter files:

Used TNSNAMES adapter to resolve the alias

Attempting to contact (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = devu009)(PORT = 1522))) (CONNECT_DATA = (SERVICE_NAME = cpstest)))

OK (400 msec)

7. Prepare RMAN duplicate script.

 

devu009n3 $ cat res1.rcv

run

{

allocate auxiliary channel c1 device type disk;

duplicate target database to cpstest;

}

8. Execute the RMAN script.

SQL> startup nomount

ORACLE instance started.

Total System Global Area  419430400 bytes

Fixed Size                  2084560 bytes

Variable Size             255852848 bytes

Database Buffers          159383552 bytes

Redo Buffers                2109440 bytes

SQL> exit

Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 – 64bit Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

devu009n3 $

devu009n3 $

devu009n3 $

devu009n3 $ ps -ef|grep pmon

  oracle 487614      1   0   Apr 28      –  0:41 asm_pmon_+ASM

  oracle 598166      1   0 17:03:40      –  0:00 ora_pmon_cpstest

  oracle 790728 602150   0 17:03:47  pts/3  0:00 grep pmon

devu009n3 $

nohup rman target sys/***@cpstest_source auxiliary / cmdfile=res1.rcv log=res1.log &

FIRST RESTORE LOG HIGHLIGHTS

———————————————–

Recovery Manager: Release 10.2.0.4.0 – Production on Fri Apr 30 17:04:51 2010

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

connected to target database: CPSTEST (DBID=248426826)

connected to auxiliary database: CPSTEST (not mounted)

RMAN> run

2> {

3> allocate auxiliary channel c1 device type disk;

4> duplicate target database to cpstest;

5> }

6>

using target database control file instead of recovery catalog

allocated channel: c1

channel c1: sid=541 devtype=DISK

Starting Duplicate Db at 30-APR-10

contents of Memory Script:

{

   set until scn  23651686575;

   set newname for clone datafile  1 to new;

   set newname for clone datafile  2 to new;

   set newname for clone datafile  3 to new;

channel c1: starting datafile backupset restore

channel c1: specifying datafile(s) to restore from backup set

restoring datafile 00032 to +DATA

restoring datafile 00040 to +DATA

restoring datafile 00049 to +DATA

restoring datafile 00102 to +DATA

restoring datafile 00170 to +DATA

restoring datafile 00174 to +DATA

restoring datafile 00178 to +DATA

restoring datafile 00182 to +DATA

channel c1: reading from backup piece /u01/oracle/backup/cpstest_dbbackup1_7klcedhf_1_1

channel c1: restore complete, elapsed time: 00:00:07

Finished restore at 30-APR-10

sql statement: CREATE CONTROLFILE REUSE SET DATABASE “CPSTEST” RESETLOGS ARCHIVELOG

  MAXLOGFILES     32

  MAXLOGMEMBERS      4

  MAXDATAFILES     1024

  MAXINSTANCES     8

  MAXLOGHISTORY      292

 LOGFILE

  GROUP  1  SIZE 100 M ,

  GROUP  2  SIZE 100 M ,

  GROUP  3  SIZE 100 M ,

  GROUP  4  SIZE 100 M

 DATAFILE

  ‘+DATA/cpstest/datafile/system.975.717700029’

 CHARACTER SET US7ASCII

contents of Memory Script:

{

   switch clone datafile all;

}

executing Memory Script

datafile 2 switched to datafile copy

input datafile copy recid=1 stamp=717700359 filename=+DATA/cpstest/datafile/undotbs.1007.717700195

datafile 3 switched to datafile copy

input datafile copy recid=2 stamp=717700359 filename=+DATA/cpstest/datafile/sysaux.991.717700121

datafile 4 switched to datafile copy

contents of Memory Script:

{

   set until scn  23651686575;

   recover

   clone database

    delete archivelog

   ;

}

executing Memory Script

executing command: SET until clause

Starting recover at 30-APR-10

datafile 46 not processed because file is read-only

datafile 47 not processed because file is read-only

datafile 48 not processed because file is read-only

datafile 49 not processed because file is read-only

datafile 50 not processed because file is read-only

datafile 51 not processed because file is read-only

starting media recovery

hannel c1: starting archive log restore to default destination

channel c1: restoring archive log

archive log thread=1 sequence=109

channel c1: restoring archive log

archive log thread=1 sequence=110

channel c1: reading from backup piece /u01/oracle/backup/cpstest_logbackup1_8blcedue_1_1

channel c1: restored backup piece 1

piece handle=/u01/oracle/backup/cpstest_logbackup1_8blcedue_1_1 tag=TAG20100430T165022

channel c1: restore complete, elapsed time: 00:00:03

archive log filename=+DATA/cpstest/archivelog/2010_04_30/thread_1_seq_109.1143.717700405 thread=1 sequence=109

channel clone_default: deleting archive log(s)

archive log filename=+DATA/cpstest/archivelog/2010_04_30/thread_1_seq_109.1143.717700405 recid=2 stamp=717700406

media recovery complete, elapsed time: 00:00:03

Finished recover at 30-APR-10

contents of Memory Script:

{

   shutdown clone;

   startup clone nomount ;

}

executing Memory Script

database dismounted

Oracle instance shut down

connected to auxiliary database (not started)

Oracle instance started

Total System Global Area     419430400 bytes

Fixed Size                     2084560 bytes

Variable Size                255852848 bytes

Database Buffers             159383552 bytes

Redo Buffers                   2109440 bytes

sql statement: CREATE CONTROLFILE REUSE SET DATABASE “CPSTEST” RESETLOGS ARCHIVELOG

  MAXLOGFILES     32

  MAXLOGMEMBERS      4

  MAXDATAFILES     1024

  MAXINSTANCES     8

  MAXLOGHISTORY      292

 LOGFILE

  GROUP  1  SIZE 100 M ,

  GROUP  2  SIZE 100 M ,

  GROUP  3  SIZE 100 M ,

  GROUP  4  SIZE 100 M

 DATAFILE

  ‘+DATA/cpstest/datafile/system.975.717700029’

 CHARACTER SET US7ASCII

contents of Memory Script:

{

   set newname for clone tempfile  1 to new;

   switch clone tempfile all;

   catalog clone datafilecopy  “+DATA/cpstest/datafile/undotbs.1007.717700195”;

   catalog clone datafilecopy  “+DATA/cpstest/datafile/sysaux.991.717700121”;

   catalog clone datafilecopy  “+DATA/cpstest/datafile/tools.1138.717700349”;

   catalog clone datafilecopy  “+DATA/cpstest/datafile/user_data.1121.717700335”;

   catalog clone datafilecopy  “+DATA/cpstest/datafile/user_index.1139.717700351”;

   catalog clone datafilecopy  “+DATA/cpstest/datafile/cps_data.981.717700031”;

   switch clone datafile all;

}

executing Memory Script

executing command: SET NEWNAME

renamed temporary file 1 to +DATA in control file

cataloged datafile copy

datafile copy filename=+DATA/cpstest/datafile/undotbs.1007.717700195 recid=1 stamp=717700438

cataloged datafile copy

datafile copy filename=+DATA/cpstest/datafile/sysaux.991.717700121 recid=2 stamp=717700438

cataloged datafile copy

datafile copy filename=+DATA/cpstest/datafile/tools.1138.717700349 recid=3 stamp=717700438

cataloged datafile copy

datafile 2 switched to datafile copy

input datafile copy recid=1 stamp=717700438 filename=+DATA/cpstest/datafile/undotbs.1007.717700195

datafile 3 switched to datafile copy

input datafile copy recid=2 stamp=717700438 filename=+DATA/cpstest/datafile/sysaux.991.717700121

datafile 4 switched to datafile copy

input datafile copy recid=3 stamp=717700438 filename=+DATA/cpstest/datafile/tools.1138.717700349

datafile 5 switched to datafile copy

input datafile copy recid=4 stamp=717700439 filename=+DATA/cpstest/datafile/user_data.1121.717700335

datafile 6 switched to datafile copy

input datafile copy recid=5 stamp=717700439 filename=+DATA/cpstest/datafile/user_index.1139.717700351

contents of Memory Script:

{

   Alter clone database open resetlogs;

}

executing Memory Script

database opened

contents of Memory Script:

{

   catalog clone datafilecopy  “+DATA/cpstest/datafile/cb001.1072.717700305”;

   catalog clone datafilecopy  “+DATA/cpstest/datafile/cb001.1080.717700309”;

   catalog clone datafilecopy  “+DATA/cpstest/datafile/cb002.983.717700065”;

   catalog clone datafilecopy  “+DATA/cpstest/datafile/cb002.967.717699973”;

   catalog clone datafilecopy  “+DATA/cpstest/datafile/cb003.1088.717700315”;

   catalog clone datafilecopy  “+DATA/cpstest/datafile/cb003.1096.717700319”;

   switch clone datafile  46 to datafilecopy

 “+DATA/cpstest/datafile/cb001.1072.717700305”;

   switch clone datafile  47 to datafilecopy

 “+DATA/cpstest/datafile/cb001.1080.717700309”;

   switch clone datafile  48 to datafilecopy

 “+DATA/cpstest/datafile/cb002.983.717700065”;

   switch clone datafile  49 to datafilecopy

 “+DATA/cpstest/datafile/cb002.967.717699973”;

   switch clone datafile  50 to datafilecopy

 “+DATA/cpstest/datafile/cb003.1088.717700315”;

   switch clone datafile  51 to datafilecopy

 “+DATA/cpstest/datafile/cb003.1096.717700319”;

#online the readonly tablespace

sql clone “alter tablespace  CB003 online”;

#online the readonly tablespace

sql clone “alter tablespace  CB002 online”;

#online the readonly tablespace

sql clone “alter tablespace  CB001 online”;

}

executing Memory Script

cataloged datafile copy

datafile copy filename=+DATA/cpstest/datafile/cb001.1072.717700305 recid=177 stamp=717700552

cataloged datafile copy

datafile copy filename=+DATA/cpstest/datafile/cb001.1080.717700309 recid=178 stamp=717700552

cataloged datafile copy

datafile copy filename=+DATA/cpstest/datafile/cb002.983.717700065 recid=179 stamp=717700552

cataloged datafile copy

datafile copy filename=+DATA/cpstest/datafile/cb002.967.717699973 recid=180 stamp=717700553

cataloged datafile copy

datafile copy filename=+DATA/cpstest/datafile/cb003.1088.717700315 recid=181 stamp=717700553

cataloged datafile copy

datafile copy filename=+DATA/cpstest/datafile/cb003.1096.717700319 recid=182 stamp=717700553

datafile 46 switched to datafile copy

input datafile copy recid=177 stamp=717700552 filename=+DATA/cpstest/datafile/cb001.1072.717700305

datafile 47 switched to datafile copy

input datafile copy recid=178 stamp=717700552 filename=+DATA/cpstest/datafile/cb001.1080.717700309

datafile 48 switched to datafile copy

input datafile copy recid=179 stamp=717700552 filename=+DATA/cpstest/datafile/cb002.983.717700065

datafile 49 switched to datafile copy

input datafile copy recid=180 stamp=717700553 filename=+DATA/cpstest/datafile/cb002.967.717699973

datafile 50 switched to datafile copy

input datafile copy recid=181 stamp=717700553 filename=+DATA/cpstest/datafile/cb003.1088.717700315

datafile 51 switched to datafile copy

input datafile copy recid=182 stamp=717700553 filename=+DATA/cpstest/datafile/cb003.1096.717700319

sql statement: alter tablespace  CB003 online

sql statement: alter tablespace  CB002 online

sql statement: alter tablespace  CB001 online

Finished Duplicate Db at 30-APR-10

Recovery Manager complete.

SQL> select open_mode from v$database;

OPEN_MODE

———-

READ WRITE

SQL> select count(*) from v$datafile;

  COUNT(*)

———-

       183

SQL> select name,status,checkpoint_change# from v$datafile where status not in (‘ONLINE’,’SYSTEM’);

no rows selected

SQL> select DF.TOTAL/1048576 “DataFile Size Mb”, LOG.TOTAL/1048576 “Redo Log Size Mb”, CONTROL.TOTAL/1048576 “Control File Size Mb”, (DF.TOTAL + LOG.TOTAL + CONTROL.TOTAL)/1048576 “Total Size Mb” from dual, (select sum(a.bytes) TOTAL from dba_data_files a) DF, (select sum(b.bytes) TOTAL from v$log b) LOG, (select sum((cffsz+1)*cfbsz) TOTAL from x$kcccf c) CONTROL;

DataFile Size Mb Redo Log Size Mb Control File Size Mb Total Size Mb

—————- —————- ——————– ————-

            5683              400            10.328125    6093.32813

SQL> show parameter db_create_file

NAME                                 TYPE        VALUE

———————————— ———– ——————————

db_create_file_dest                  string      +DATA

SQL> show parameter control

NAME                                 TYPE        VALUE

———————————— ———– ——————————

control_file_record_keep_time        integer     7

control_files                        string      +DATA/control01.ctl

sqlplus / as sysdba

create spfile from pfile;

startup force

SECOND FULL BACKUP

Source database

alter system switch logfile;

create table system.BACKUP2_TEST as select * from dba_tables;

Table created.

 alter system switch logfile;

devu009 $ nohup rman target / cmdfile=backup2.rcv log=backup2.log &

[1]     1601790

devu009 $ Sending output to nohup.out

devu009 $ cat b2.rcv

run

{

backup check logical incremental level 0 database filesperset 8 format ‘/u01/oracle/backup/cpstest_dbbackup2_%U’ force;

sql ‘alter system archive log current’;

backup format ‘/u01/oracle/backup/cpstest_logbackup2_%U’ archivelog all not backed up 1 times;

}

Exit

Copy all the files to the target server

SECOND RESTORE

SQL> show parameter db_name

NAME                                 TYPE        VALUE

———————————— ———– ——————————

db_name                              string      cpstest

SQL>

SQL>

SQL> shutdown immediate

Database closed.

Database dismounted.

ORACLE instance shut down.

devu009n3 $ pwd

/u01/oracle/product/10.2.0/db_1/dbs

Startup nomount

nohup rman target sys/***@cpstest_source auxiliary / cmdfile=res_skip_ro.rcv log= res_skip_ro.log &

SECOND RESTORE LOG HIGHLIGHTS, ISSUES AND RESOULTIONS

———————————————————————————–

devu009n3 $ view res_skip_ro.log

“res_skip_ro.log” [Read only] 582 lines, 15392 characters

Recovery Manager: Release 10.2.0.4.0 – Production on Fri Apr 30 18:17:45 2010

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

connected to target database: CPSTEST (DBID=248426826)

connected to auxiliary database: CPSTEST (not mounted)

RMAN> run

2> {

3> allocate auxiliary channel c1 device type disk;

4> duplicate target database to cpstest skip readonly;

5> }

6>

using target database control file instead of recovery catalog

allocated channel: c1

channel c1: sid=541 devtype=DISK

Starting Duplicate Db at 30-APR-10

datafile 46 not processed because file is read-only

datafile 47 not processed because file is read-only

datafile 48 not processed because file is read-only

datafile 49 not processed because file is read-only

datafile 50 not processed because file is read-only

datafile 51 not processed because file is read-only

contents of Memory Script:

{

{

   set until scn  23651699996;

   set newname for clone datafile  1 to new;

   set newname for clone datafile  2 to new;

   set newname for clone datafile  3 to new;

   set newname for clone datafile  4 to new;

   set newname for clone datafile  5 to new;

   set newname for clone datafile  6 to new;

input datafile copy recid=176 stamp=717704618 filename=+DATA/cpstest/datafile/doctrap.1362.717704343

contents of Memory Script:

{

   set until scn  23651699996;

   recover

   clone database

    delete archivelog

   ;

}

executing Memory Script

executing command: SET until clause

Starting recover at 30-APR-10

datafile 46 not processed because file is read-only

datafile 47 not processed because file is read-only

datafile 48 not processed because file is read-only

datafile 49 not processed because file is read-only

datafile 50 not processed because file is read-only

datafile 51 not processed because file is read-only

starting media recovery

channel c1: starting archive log restore to default destination

channel c1: restoring archive log

archive log thread=1 sequence=117

channel c1: restoring archive log

channel c1: restoring archive log

archive log thread=1 sequence=118

channel c1: reading from backup piece /u01/oracle/backup/cpstest_logbackup2_9vlceist_1_1

channel c1: restored backup piece 1

piece handle=/u01/oracle/backup/cpstest_logbackup2_9vlceist_1_1 tag=TAG20100430T181453

channel c1: restore complete, elapsed time: 00:00:01

archive log filename=+DATA/cpstest/archivelog/2010_04_30/thread_1_seq_117.1521.717704691 thread=1 sequence=117

channel clone_default: deleting archive log(s)

archive log filename=+DATA/cpstest/archivelog/2010_04_30/thread_1_seq_117.1521.717704691 recid=1 stamp=717704690

archive log filename=+DATA/cpstest/archivelog/2010_04_30/thread_1_seq_118.1522.717704691 thread=1 sequence=118

channel clone_default: deleting archive log(s)

archive log filename=+DATA/cpstest/archivelog/2010_04_30/thread_1_seq_118.1522.717704691 recid=2 stamp=717704691

media recovery complete, elapsed time: 00:00:03

Finished recover at 30-APR-10

contents of Memory Script:

{

   shutdown clone;

   startup clone nomount ;

}

executing Memory Script

database dismounted

Oracle instance shut down

connected to auxiliary database (not started)

Oracle instance started

Oracle instance started

Total System Global Area     419430400 bytes

Fixed Size                     2084560 bytes

Variable Size                255852848 bytes

Database Buffers             159383552 bytes

Redo Buffers                   2109440 bytes

sql statement: CREATE CONTROLFILE REUSE SET DATABASE “CPSTEST” RESETLOGS ARCHIVELOG

  MAXLOGFILES     32

  MAXLOGMEMBERS      4

  MAXDATAFILES     1024

  MAXINSTANCES     8

  MAXLOGHISTORY      292

 LOGFILE

  GROUP  1  SIZE 100 M ,

  GROUP  2  SIZE 100 M ,

  GROUP  3  SIZE 100 M ,

  GROUP  4  SIZE 100 M

 DATAFILE

  ‘+DATA/cpstest/datafile/system.1358.717704343’

 CHARACTER SET US7ASCII

contents of Memory Script:

contents of Memory Script:

{

   set newname for clone tempfile  1 to new;

   switch clone tempfile all;

   catalog clone datafilecopy  “+DATA/cpstest/datafile/undotbs.1389.717704443”;

   catalog clone datafilecopy  “+DATA/cpstest/datafile/sysaux.1373.717704381”;

   catalog clone datafilecopy  “+DATA/cpstest/datafile/tools.1516.717704591”;

   catalog clone datafilecopy  “+DATA/cpstest/datafile/user_data.1499.717704575”;

  switch clone datafile all;

}

executing Memory Script

executing command: SET NEWNAME

renamed temporary file 1 to +DATA in control file

cataloged datafile copy

datafile copy filename=+DATA/cpstest/datafile/undotbs.1389.717704443 recid=1 stamp=717704724

cataloged datafile copy

datafile copy filename=+DATA/cpstest/datafile/sysaux.1373.717704381 recid=2 stamp=717704724

cataloged datafile copy

datafile copy filename=+DATA/cpstest/datafile/tools.1516.717704591 recid=3 stamp=717704724

datafile 2 switched to datafile copy

input datafile copy recid=1 stamp=717704724 filename=+DATA/cpstest/datafile/undotbs.1389.717704443

datafile 3 switched to datafile copy

input datafile copy recid=2 stamp=717704724 filename=+DATA/cpstest/datafile/sysaux.1373.717704381

datafile 4 switched to datafile copy

input datafile copy recid=3 stamp=717704724 filename=+DATA/cpstest/datafile/tools.1516.717704591

datafile 5 switched to datafile copy

input datafile copy recid=4 stamp=717704724 filename=+DATA/cpstest/datafile/user_data.1499.717704575

contents of Memory Script:

{

   Alter clone database open resetlogs;

}

executing Memory Script

database opened

Finished Duplicate Db at 30-APR-10

Recovery Manager complete.

select open_mode from v$database;

OPEN_MODE

———-

READ WRITE

1 row selected.

FIXING READONLY DATAFILE Missing file issue

set pages 1000 lines 150

column name format a50

select file#,name,status,checkpoint_change# from v$datafile where status not in (‘ONLINE’,’SYSTEM’);

     FILE# NAME                                               STATUS  CHECKPOINT_CHANGE#

———- ————————————————– ——- ——————

        46 /u01/oracle/product/10.2.0/db_1/dbs/MISSING00046   OFFLINE         2.3648E+10

        47 /u01/oracle/product/10.2.0/db_1/dbs/MISSING00047   OFFLINE         2.3648E+10

        48 /u01/oracle/product/10.2.0/db_1/dbs/MISSING00048   OFFLINE         2.3648E+10

        49 /u01/oracle/product/10.2.0/db_1/dbs/MISSING00049   OFFLINE         2.3648E+10

        50 /u01/oracle/product/10.2.0/db_1/dbs/MISSING00050   OFFLINE         2.3648E+10

        51 /u01/oracle/product/10.2.0/db_1/dbs/MISSING00051   OFFLINE         2.3648E+10

6 rows selected.

set pages 1000 lines 150 num 777777777777

column name format a50

select file#,name,status,checkpoint_change# from v$datafile where file# between 46 and 51;

FILE# NAME                                               STATUS  CHECKPOINT_CHANGE#

—– ————————————————– ——- ——————

   46 /u01/oracle/product/10.2.0/db_1/dbs/MISSING00046   OFFLINE        23648149286

   47 /u01/oracle/product/10.2.0/db_1/dbs/MISSING00047   OFFLINE        23648149286

   48 /u01/oracle/product/10.2.0/db_1/dbs/MISSING00048   OFFLINE        23648153263

   49 /u01/oracle/product/10.2.0/db_1/dbs/MISSING00049   OFFLINE        23648153263

   50 /u01/oracle/product/10.2.0/db_1/dbs/MISSING00050   OFFLINE        23648171745

   51 /u01/oracle/product/10.2.0/db_1/dbs/MISSING00051   OFFLINE        23648171745

6 rows selected.

Query from source system for these offline files

set pages 1000 lines 150 num 777777777777

column name format a50

select file#,name,status,checkpoint_change# from v$datafile where file# between 46 and 51;

FILE# NAME                                               STATUS  CHECKPOINT_CHANGE#

—– ————————————————– ——- ——————

   46 /u02/oradata/cpstest/CB001_02.DBF                  ONLINE         23648149286

   47 /u02/oradata/cpstest/CB001_01.DBF                  ONLINE         23648149286

   48 /u02/oradata/cpstest/CB002_02.DBF                  ONLINE         23648153263

   49 /u02/oradata/cpstest/CB002_01.DBF                  ONLINE         23648153263

   50 /u02/oradata/cpstest/CB003_02.DBF                  ONLINE         23648171745

   51 /u02/oradata/cpstest/CB003_01.DBF                  ONLINE         23648171745

6 rows selected.

ASMCMD> ls

ASM/

CPSTEST/

ASMCMD> pwd

+data

ASMCMD> ls -l CB001*

Type      Redund  Striped  Time             Sys  Name

                                            N    cb001_01.dbf => +DATA/CPSTEST/DATAFILE/CB001.332.717610107

                                            N    cb001_02.dbf => +DATA/CPSTEST/DATAFILE/CB001.340.717610101

ASMCMD> ls -l CB002*

Type      Redund  Striped  Time             Sys  Name

                                            N    cb002_01.dbf => +DATA/CPSTEST/DATAFILE/CB002.444.717609787

                                            N    cb002_02.dbf => +DATA/CPSTEST/DATAFILE/CB002.429.717609847

ASMCMD> ls -l CB003*

Type      Redund  Striped  Time             Sys  Name

                                            N    cb003_01.dbf => +DATA/CPSTEST/DATAFILE/CB003.316.717610125

                                            N    cb003_02.dbf => +DATA/CPSTEST/DATAFILE/CB003.324.717610117

Now you can map the file#, name for the OMF in ASM Storage as below

46 à +DATA/CPSTEST/DATAFILE/CB001.340.717610101

47 à +DATA/CPSTEST/DATAFILE/CB001.332.717610107

48 à +DATA/CPSTEST/DATAFILE/CB002.429.717609847

49 à +DATA/CPSTEST/DATAFILE/CB002.444.717609787

50 à +DATA/CPSTEST/DATAFILE/CB003.324.717610117

51 à +DATA/CPSTEST/DATAFILE/CB003.316.717610125

And accordingly prepare the SQL for the renaming

Shutdown immediate

Startup mount

alter database rename file ‘/u01/oracle/product/10.2.0/db_1/dbs/MISSING00046’ to ‘+DATA/CPSTEST/DATAFILE/CB001.340.717610101’;

alter database rename file ‘/u01/oracle/product/10.2.0/db_1/dbs/MISSING00047’ to ‘+DATA/CPSTEST/DATAFILE/CB001.332.717610107’;

alter database rename file ‘/u01/oracle/product/10.2.0/db_1/dbs/MISSING00048’ to ‘+DATA/CPSTEST/DATAFILE/CB002.429.717609847’;

alter database rename file ‘/u01/oracle/product/10.2.0/db_1/dbs/MISSING00049’ to ‘+DATA/CPSTEST/DATAFILE/CB002.444.717609787’;

alter database rename file ‘/u01/oracle/product/10.2.0/db_1/dbs/MISSING00050’ to ‘+DATA/CPSTEST/DATAFILE/CB003.324.717610117’;

alter database rename file ‘/u01/oracle/product/10.2.0/db_1/dbs/MISSING00051’ to ‘+DATA/CPSTEST/DATAFILE/CB003.316.717610125’;

alter database open;

alter tablespace CB001 online;

alter tablespace CB002 online;

alter tablespace CB003 online;

————————————————————————————————————————————-

NOTE – ALWAYS RESTORE READONLY DATAFILE IN MOUNT MODE AND BEFORE OPEN RESETLOGS IF NEEDED

SYNTAX – replace the OMF files from asmcmd

Startup mount

alter database rename file ‘/u01/oracle/product/10.2.0/db_1/dbs/MISSING00046’ to ‘+DATA/CPSTEST/DATAFILE/CB001.348.717528709’;

alter database rename file ‘/u01/oracle/product/10.2.0/db_1/dbs/MISSING00047’ to ‘+DATA/CPSTEST/DATAFILE/CB001.340.717528717’;

alter database rename file ‘/u01/oracle/product/10.2.0/db_1/dbs/MISSING00048’ to ‘

alter database rename file ‘/u01/oracle/product/10.2.0/db_1/dbs/MISSING00049’ to ‘

alter database rename file ‘/u01/oracle/product/10.2.0/db_1/dbs/MISSING00050’ to ‘

alter database rename file ‘/u01/oracle/product/10.2.0/db_1/dbs/MISSING00051’ to ‘

SQL> select name,status,checkpoint_change# from v$datafile where status not in (‘ONLINE’,’SYSTEM’);

no rows selected

SQL> select * from v$tempfile;

     FILE# CREATION_CHANGE# CREATION_        TS#     RFILE# STATUS  ENABLED

———- —————- ——— ———- ———- ——- ———-

     BYTES     BLOCKS CREATE_BYTES BLOCK_SIZE

———- ———- ———— ———-

NAME

——————————————————————————–

         1       2.3645E+10 12-APR-10          3          1 ONLINE  READ WRITE

 603979776      73728    603979776       8192

+DATA/cpstest/tempfile/temp.1529.717704823

SQL> select * from v$controlfile;

STATUS

——-

NAME

——————————————————————————–

IS_ BLOCK_SIZE FILE_SIZE_BLKS

— ———- ————–

+DATA/control01.ctl

NO       16384            660

SQL> select * from v$logfile;

    GROUP# STATUS  TYPE

———- ——- ——-

MEMBER

——————————————————————————–

IS_

         4         ONLINE

+DATA/cpstest/onlinelog/group_4.1527.717704813

NO

         4         ONLINE

+DATA/cpstest/onlinelog/group_4.1528.717704815

YES

    GROUP# STATUS  TYPE

———- ——- ——-

MEMBER

——————————————————————————–

IS_

         3         ONLINE

+DATA/cpstest/onlinelog/group_3.1525.717704811

NO

         3         ONLINE

+DATA/cpstest/onlinelog/group_3.1526.717704813

    GROUP# STATUS  TYPE

———- ——- ——-

MEMBER

——————————————————————————–

IS_

YES

         2         ONLINE

+DATA/cpstest/onlinelog/group_2.1523.717704811

NO

         2         ONLINE

    GROUP# STATUS  TYPE

———- ——- ——-

MEMBER

——————————————————————————–

IS_

+DATA/cpstest/onlinelog/group_2.1524.717704811

YES

         1         ONLINE

+DATA/cpstest/onlinelog/group_1.1522.717704809

NO

    GROUP# STATUS  TYPE

———- ——- ——-

MEMBER

——————————————————————————–

IS_

         1         ONLINE

+DATA/cpstest/onlinelog/group_1.1521.717704809

YES

8 rows selected.

SQL>

SQL> select count(*) from v$datafile;

  COUNT(*)

———-

       183

SQL> select count(*) from cpstest_before_backup3;

  COUNT(*)

———-

        19

SQL> select count(*) from before_inc_backup;

  COUNT(*)

———-

      1120

TARGET IS SUCCESSFULLY IN SYNC WITH SOURCE NOW UPTO THE LAST ARCHIVED LOGFILE  – SUCCESS STORY

Advertisements

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

 
%d bloggers like this: