Duplicate Oracle 9i database - same server

posted Mar 16, 2011, 4:01 PM by Sachchida Ojha   [ updated Mar 17, 2011, 4:39 AM ]
1. Create password file

va-ibill01:SVPRD1B:cd /u01/SVPRD1B/app/oracle/product/92/dbs


va-ibill01:SVPRD1B:/u01/SVPRD1B/app/oracle/product/92/dbs

$orapwd file=/u01/SVPRD1B/app/oracle/product/92/dbs/orapwDUP password=password  entries=10

$ls orapwDUP
orapwDUP

1. Update listener. ora file and restart the listener

$cd $ORACLE_HOME/network/admin

va-ibill01:SVPRD1B:/u01/SVPRD1B/app/oracle/product/92/network/admin
$ls
libnk59.exp   libnk59.imp   libnrad9.exp  libnrad9.imp  listener.ora  samples       shrept.lst    snmp_ro.ora   snmp_rw.ora   sqlnet.ora    tnsnames.ora

$vi listener.ora
"listener.ora" 16 lines, 430 characters
# LISTENER.ORA Network Configuration File: /u01/SVPRD1B/app/oracle/product/92/network/admin/listener.ora
# Generated by Oracle configuration tools.

LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS_LIST =
        (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC))
      )
      (ADDRESS_LIST =
        (ADDRESS = (PROTOCOL = TCP)(HOST = va-ibill01)(PORT = 1521))
      )
    )
  )
DUP =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST =  va-ibill01)(PORT = 1521))
    )
    (CONNECT_DATA =
      (ORACLE_SID = DUP)
    )
  )


INBOUND_CONNECT_TIMEOUT_LISTENER = 0

Save file.

va-ibill01:SVPRD1B:/u01/SVPRD1B/app/oracle/product/92/network/admin

$lsnrctl reload

LSNRCTL for IBM/AIX RISC System/6000: Version 9.2.0.8.0 - Production on 16-MAR-2011 21:08:16

Copyright (c) 1991, 2006, Oracle Corporation.  All rights reserved.

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC)))
The command completed successfully

va-ibill01:SVPRD1B:/u01/SVPRD1B/app/oracle/product/92/network/admin

$cd /u01/SVPRD1B/app/oracle/product/92/dbs

va-ibill01:SVPRD1B:/u01/SVPRD1B/app/oracle/product/92/dbs
$ls
autocf_SVPRD1B_c-3443740097-20100902-0d.f  initSVPRF1B.ora                            orapwSVPRD1B                               snapcf_SVPRD1B.f
autocf_SVPRD1B_c-3443740097-20110228-02.f  initdw.ora                                 reorg418.sql                               spfileSVPRD1B.ora
init.ora                                   lkSVPRD1B                                  reorg418.sql.orginal
initSVPRD1B.ora                            orapwDUP                                   reorg421.sql

3. Create new init.ora file for DUP database.

va-ibill01:SVPRD1B:/u01/SVPRD1B/app/oracle/product/92/dbs

$cp initSVPRD1B.ora initDUP.ora

va-ibill01:SVPRD1B:/u01/SVPRD1B/app/oracle/product/92/dbs

Update the initDUP.ora file.

a) Updatelocation of new control files.
b) get location of the data files  from prod and use DB_FILE_NAME_CONVERT to point new location
c)Get location of all log files from prod and use  LOG_FILE_NAME_CONVERT to point new location

SQL> select name from v$datafile;

NAME
--------------------------------------------------------------------------------
/mnt/u02/SVPRD1B/oradata/system01.dbf
/mnt/u04/SVPRD1B/oradata/undotbs01.dbf
/mnt/u02/SVPRD1B/oradata/drsys01.dbf
/mnt/u08/SVPRD1B/oradata/tools01.dbf
/mnt/u08/SVPRD1B/oradata/users01.dbf
/mnt/u03/SVPRD1B/oradata/xdb01.dbf
/mnt/u09/SVPRD1B/oradata/archived_ne_idx01.dbf
/mnt/u08/SVPRD1B/oradata/archived_ne_data02.dbf
/mnt/u08/SVPRD1B/oradata/archived_ne_data03.dbf
/mnt/u08/SVPRD1B/oradata/ata_data01.dbf
/mnt/u08/SVPRD1B/oradata/ata_data_charge01.dbf

NAME
--------------------------------------------------------------------------------
/mnt/u08/SVPRD1B/oradata/ata_data_ne01.dbf
/mnt/u08/SVPRD1B/oradata/ata_ic_lob01.dbf
/mnt/u09/SVPRD1B/oradata/ata_index01.dbf
/mnt/u09/SVPRD1B/oradata/ata_index_charge01.dbf
/mnt/u09/SVPRD1B/oradata/ata_index_ne01.dbf
/mnt/u04/SVPRD1B/oradata/perfstat01.dbf
/mnt/u08/SVPRD1B/oradata/tpsysadm_data01.dbf
/mnt/u09/SVPRD1B/oradata/tpsysadm_index01.dbf
/mnt/u08/SVPRD1B/oradata/forecasting.dbf
/mnt/u08/SVPRD1B/oradata/archived_charge_data.dbf
/mnt/u09/SVPRD1B/oradata/archived_charge_idx01.dbf

NAME
--------------------------------------------------------------------------------
/mnt/u08/SVPRD1B/oradata/archived_charge_data02.dbf
/mnt/u08/SVPRD1B/oradata/archived_ne_data01.dbf

24 rows selected.

SQL> select name from v$controlfile;

NAME
--------------------------------------------------------------------------------
/mnt/u02/SVPRD1B/oradata/control01.ctl
/mnt/u03/SVPRD1B/oradata/control02.ctl
/mnt/u04/SVPRD1B/oradata/control03.ctl

SQL> select member from v$logfile;

MEMBER
--------------------------------------------------------------------------------
/mnt/u05/SVPRD1B/oradata/redo05a.log
/mnt/u06/SVPRD1B/oradata/redo05b.log
/mnt/u05/SVPRD1B/oradata/redo04a.log
/mnt/u06/SVPRD1B/oradata/redo04b.log
/mnt/u05/SVPRD1B/oradata/redo03a.log
/mnt/u06/SVPRD1B/oradata/redo03b.log
/mnt/u05/SVPRD1B/oradata/redo02a.log
/mnt/u06/SVPRD1B/oradata/redo02b.log
/mnt/u05/SVPRD1B/oradata/redo01a.log
/mnt/u06/SVPRD1B/oradata/redo01b.log

10 rows selected.

SQL>

Minimum required parameter in init.ora file.

*.compatible='9.2.0.8.0'
*.control_files='/mnt/u02/SVPRD1B/oradata/control01.ctl','/mnt/u03/SVPRD1B/oradata/control02.ctl','/mnt/u04/SVPRD1B/oradata/control03.ctl'
# Convert file names to allow for different directory structure.

DB_FILE_NAME_CONVERT=
('/u01/SVPRD1B/','/mnt/u01/SVPRD1B/','
/u02/SVPRD1B/','/mnt/u02/SVPRD1B/',
'/u03/SVPRD1B/','/mnt/u03/SVPRD1B/',
'/u04/SVPRD1B/','/mnt/u04/SVPRD1B/',
'/u05/SVPRD1B/','/mnt/u05/SVPRD1B/',
 '/u06/SVPRD1B/','/mnt/u06/SVPRD1B/',
'/u07/SVPRD1B/','/mnt/u07/SVPRD1B/',
'/u08/SVPRD1B/','/mnt/u08/SVPRD1B/',
'/u09/SVPRD1B/','/mnt/u09/SVPRD1B/')

LOG_FILE_NAME_CONVERT=('/u05/SVPRD1B/oradata/','/mnt/u05/SVPRD1B/oradata/','/u06/SVPRD1B/oradata/','/mnt/u06/SVPRD1B/oradata/')

*.db_block_size=8192
*.db_name='DUP'

Save the file

va-ibill01:DUP:/u01/SVPRD1B/app/oracle/product/92/dbs
$ORACLE_SID=DUP; export ORACLE_SID

va-ibill01:DUP:/u01/SVPRD1B/app/oracle/product/92/dbs
$sqlplus /nolog

SQL*Plus: Release 9.2.0.8.0 - Production on Wed Mar 16 21:42:01 2011

Copyright (c) 1982, 2002, Oracle Corporation.  All rights reserved.

SQL> conn / as sysdba
Connected to an idle instance.
SQL> create spfile from pfile='/u01/SVPRD1B/app/oracle/product/92/dbs/initDUP.ora';

File created.

SQL>STARTUP FORCE NOMOUNT;


SQL>EXIT

$ORACLE_SID=DUP; export ORACLE_SID

$rman TARGET sys/duck_p6nts@SVPRD1B CATALOG rman/rman@rman AUXILIARY /

RUN
{
  ALLOCATE AUXILIARY CHANNEL c1 DEVICE TYPE sbt;
  ALLOCATE AUXILIARY CHANNEL c2 DEVICE TYPE sbt;
  DUPLICATE TARGET DATABASE TO DUP;
}


Open another window and monitor the progress.

va-ibill01:SVPRD1B:/mnt/u02/SVPRD1B/oradata
$export ORACLE_SID=DUP;

va-ibill01:DUP:/mnt/u02/SVPRD1B/oradata
$sqlplus "/ as sysdba"

SQL*Plus: Release 9.2.0.8.0 - Production on Wed Mar 16 23:53:25 2011

Copyright (c) 1982, 2002, Oracle Corporation.  All rights reserved.


Connected to:
Oracle9i Enterprise Edition Release 9.2.0.8.0 - 64bit Production
With the Partitioning option
JServer Release 9.2.0.8.0 - Production

SQL> SELECT SID, SERIAL#, CONTEXT, SOFAR, TOTALWORK,
ROUND(SOFAR/TOTALWORK*100,2) "%_COMPLETE"
FROM V$SESSION_LONGOPS
WHERE OPNAME LIKE 'RMAN%'
AND OPNAME NOT LIKE '%aggregate%'
AND TOTALWORK != 0
AND SOFAR <> TOTALWORK   2    3    4    5    6    7  ;

       SID    SERIAL#    CONTEXT      SOFAR  TOTALWORK %_COMPLETE
---------- ---------- ---------- ---------- ---------- ----------
        11          1                      1                    6196962    8038400      77.09

SQL>

Comments