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

Archive for the ‘Clone or Duplicate’ Category

ORA-01113: file 1 needs media recovery during open reset logs

Posted by Hendry chinnapparaj on May 16, 2011

Problem

———–

When you’re cloning a database, get the ora error below during open resetlogs

SQL> alter database open resetlogs;

alter database open resetlogs

*

ERROR at line 1:

ORA-01113: file 1 needs media recovery

ORA-01110: data file 1: ‘E:\ORADATA\LN3D\SYSTEM01.DBF’

Solution

———

Copy the redolog from the source database where the cold backup was done to the target database redo directory

And then

SQL> recover database using backup controlfile until cancel;

ORA-00279: change 23735596903 generated at 05/16/2011 14:35:45 needed for

thread 1

ORA-00289: suggestion : D:\ORACLE\ORA92\RDBMS\ARC52958.001

ORA-00280: change 23735596903 for thread 1 is in sequence #52958

Specify log: {<RET>=suggested | filename | AUTO | CANCEL}

d:\oracle\oradata\ln3d\redo01.log

Log applied.

Media recovery complete.

SQL> alter database open resetlogs;

Database altered.

Advertisements

Posted in Clone or Duplicate | Leave a Comment »

Clone Oracle9i Database on same Windows Server

Posted by Hendry chinnapparaj on May 10, 2011

Summary :-

                      
The following steps must be on Source -Production instance/Server

Step 1. Shutdown database in normal mode and start it up in restricted mode.

                        Step 2. Take the backup of control file

                        Step 3. Shutdown database again  in normal mode.                    

Step 4. Copy init parameter file ,control file script  and all the database file on the destination server/location, once all the files are successfully copied, you may startup the database normally.

                        The following step must be on destination – Clone instance/server

                        Step 5. Edit init parameter file and control file script.

                        Step 6.  New Environment setup .

                        Step 7. Connect with sqlplus and recreate control file

                        Step 8. Open the database in resetlogs.

                        Step 9. Shutdown the database in normal mode

                        Step 10. Take the cold backup and start the database in archive/non archive mode.

Details :-


Step 1 – 3 on the Source Server ( Server A)

SVRMGR> connect internal/oracle

Connected.

SVRMGR> shutdown ;

Database closed.

Database dismounted.

ORACLE instance shut down.

SVRMGR> startup restrict

ORACLE instance started.

Total System Global Area                         57124108 bytes

Fixed Size                                          70924 bytes

Variable Size                                    40198144 bytes

Database Buffers                                 16777216 bytes

Redo Buffers                                        77824 bytes

Database mounted.

Database opened.

SVRMGR> alter database backup controlfile to trace;

Statement processed.

SVRMGR>

SVRMGR> show parameter user_dump_dest

NAME                                TYPE    VALUE

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

user_dump_dest                      string E:\ora816\admin\ora816\udump

In Windows

D:\oracle\admin\ora816\udump

Note :- Backup control file will generate in user dump destination as above. Check for the latest Ora<xxxxx>.trc .  Rename this file to Ctrl.sql

 

SVRMGR> shutdown immediate;

Database closed.

Database dismounted.

ORACLE instance shut down.

Step 4: Copy all the parameter files (initsid.ora, configsid.ora ),  Control files,  Ctrlprod.sql ( created as above) and all data files  to clone server . It is good to follow the OFA. If you are  cloning a  database on the same machine then  create a different directory structure ( as OFA) for cloning instance and copied all the required files of primary instance here.

 

    Step 4a : On Unix  ( Let us say your cloning instance name is clone)

If you had followed OFA , your initsid.ora  parameters files would be in  $ORACLE_BASE/admin/clone/pfile/initclone.ora

and configsid.ora would be in  $ORACLE_BASE/admin/clone/pfile/configclone.ora.

Create a symbolic link

Change the working directory to $ORACLE_HOME/dbs and create a symbolic link

$ ln –s $ORACLE_BASE/admin/clone/pfile/initclone.ora  initclone.ora

 

Step 5 : On destination server

Change the following parameter in the initsid.ora of the cloning instance/server

 

InitSid.ora (initora816.ora) of the Source/production server.
InitSid.ora (initclone.ora) of the

destination/clone server.

db_name = “ora816”instance_name = ora816

service_names = ora816

control_files = (“e:\ora816\oradata\ora816\control01.ctl”, “f:\ora816\oradata\ora816\control02.ctl”, “g:\ora816\oradata\ora816\control03.ctl”)

Db_name= “clone”Instance_name= clone

Service_name = clone

Control_file = (“C:\clone\control01.ctl”,”D:\clone\control02.ctl”)

The other parameter which is required to change is user_dump_dest, background_dump_dest,log_archive_dest

 

 

Oraxxxx.trc is copied from source/production instance to destination/clone instance and renamed it as ctrlclone.sql .

 

 Edit ctrlclone.sql as follows.

 

Oraxxxx.trc in user dump dest of Source/Production instance Ctrlclone.sql
STARTUP NOMOUNT

CREATE CONTROLFILE REUSE DATABASE “ORA816” NORESETLOGS ARCHIVELOG

MAXLOGFILES 32

MAXLOGMEMBERS 2

MAXDATAFILES 32

MAXINSTANCES 16

MAXLOGHISTORY 1815

LOGFILE

GROUP 1 ‘E:\ORA816\ORADATA\ORA816\REDO03.LOG’  SIZE 1M,

GROUP 2 ‘E:\ORA816\ORADATA\ORA816\REDO02.LOG’  SIZE 1M,

GROUP 3 ‘E:\ORA816\ORADATA\ORA816\REDO01.LOG’  SIZE 1M

DATAFILE

‘E:\ORA816\ORADATA\ORA816\SYSTEM01.DBF’,

‘E:\ORA816\ORADATA\ORA816\RBS01.DBF’,

‘E:\ORA816\ORADATA\ORA816\TEMP01.DBF’,

‘E:\ORA816\ORADATA\ORA816\TOOLS01.DBF’,

‘E:\ORA816\ORADATA\ORA816\INDX01.DBF’,

‘E:\ORA816\ORADATA\ORA816\DR01.DBF’,

‘E:\ORA816\DATABASE\NGAR5ORA81601.DBF’,

‘E:\ORA816\DATABASE\RBSTEST01.DBF’

CHARACTER SET WE8ISO8859P1

;

# Recovery is required if any of the datafiles are restored backups,

# or if the last shutdown was not normal or immediate.

RECOVER DATABASE

# All logs need archiving and a log switch is needed.

ALTER SYSTEM ARCHIVE LOG ALL;

# Database can now be opened normally.

ALTER DATABASE OPEN;

/* Delete everything upto startup nomount */STARTUP NOMOUNT pfile=f:\clone\initclone.ora

CREATE CONTROLFILE set DATABASE “CLONE” resetlogs noarchivelog

MAXLOGFILES 32

MAXLOGMEMBERS 2

MAXDATAFILES 32

MAXINSTANCES 16

MAXLOGHISTORY 1815

LOGFILE

GROUP 1 ‘F:\CLONE\ORA816\REDO03.LOG’

SIZE 1M,

GROUP 2 ‘F:\CLONE\ORA816\REDO02.LOG’

SIZE 1M,

GROUP 3 ‘F:\CLONE\ORA816\REDO01.LOG’

SIZE 1M

DATAFILE

‘F:\CLONE\ORA816\SYSTEM01.DBF’,

‘F:\CLONE\ORA816\RBS01.DBF’,

‘F:\CLONE\ORA816\TEMP01.DBF’,

‘F:\CLONE\ORA816\TOOLS01.DBF’,

‘F:\CLONE\ORA816\INDX01.DBF’,

‘F:\CLONE\ORA816\DR01.DBF’,    ‘F:\CLONE\DATABASE\NGAR5ORA81601.DBF’,

‘F:\CLONE\DATABASE\RBSTEST01.DBF’

CHARACTER SET WE8ISO8859P1

;

# Recovery is required if any of the datafiles are restored backups,

# or if the last shutdown was not normal or immediate.

#RECOVER DATABASE

# All logs need archiving and a log switch is needed.

#ALTER SYSTEM ARCHIVE LOG ALL;

# Database can now be opened normally.

#ALTER DATABASE OPEN;

STEP 6 :-

 

ON NT :- You have to create a windows NT services as follows.

 

C:\>oradim -new -sid clone -srvc oracleserviceClone -intpwd oracle -startmode auto -pfile f:\clone\initclone.ora

 

ON Unix :-

         

(A)   (A)    Change the working directory to /etc and edit the oratab file  to put the entry for CLOBE instance.

 (B)   Setup login profile for the Oracle user having dba group.

                        ORACLE_SID=CLONE

                         Export ORACLE_SID

         Or  .oraenv

               

           

 

 

STEP 7 :- Create controlfile as follows :-
 
Check in the initclone.ora REMOTE_LOGIN_PASSWORDFILE is set to
   exclusive or shared.  If this is set, then a valid passwordfile

   should exist in ORACLE_HOME/dbs or created using orapwd  as

 

orapwd file=d:\oracle\ora92\database\pwdclone.ora password=oracle entries=1 
 

  sqlplus /nolog

connect sys/** as sysdba

or

SVRMGR> @f:\clone\ctrlclone.sql

ORACLE instance started.

Total System Global Area                         57123804 bytes

Fixed Size                                          70620 bytes

Variable Size                                    40198144 bytes

Database Buffers                                 16777216 bytes

Redo Buffers                                        77824 bytes

Statement processed.

D:\ORACLE\ORA92\DATABASE

STEP 8 :- Open the database in resetlog mode as follows.

SVRMGR> alter database open resetlogs;

Statement processed.

STEP 9 :- Shutdown the database in Normal mode

SVRMGR> connect internal/oracle

Connected.

SVRMGR> shutdown ;

Database closed.

Database dismounted.

ORACLE instance shut down.

STEP 10 :- Take the cold backup and start the database in archive/non archive mode.

Posted in Clone or Duplicate | Leave a Comment »

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

Posted in Clone or Duplicate | Leave a Comment »