Cloning Database from sgptqa to vtbqa
Cloning Database from sgptqa to vstbqa
1. Create initial parameter file
SQL> create pfile='/tmp/initvstbqa1.ora' from spfile;
File created.
2. Edit the initial parameter file
$ cat initvstbqa1.ora
vstbqa2.__db_cache_size=71303168
vstbqa1.__db_cache_size=62914560
vstbqa2.__java_pool_size=4194304
vstbqa1.__java_pool_size=12582912
vstbqa2.__large_pool_size=4194304
vstbqa1.__large_pool_size=4194304
vstbqa1.__oracle_base='/opt/oracle'#ORACLE_BASE set from environment
vstbqa2.__oracle_base='/opt/oracle'#ORACLE_BASE set from environment
vstbqa2.__pga_aggregate_target=314572800
vstbqa1.__pga_aggregate_target=314572800
vstbqa2.__sga_target=314572800
vstbqa1.__sga_target=314572800
vstbqa2.__shared_io_pool_size=0
vstbqa1.__shared_io_pool_size=0
vstbqa2.__shared_pool_size=226492416
vstbqa1.__shared_pool_size=222298112
vstbqa2.__streams_pool_size=0
vstbqa1.__streams_pool_size=4194304
*.audit_file_dest='/opt/oracle/admin/vstbqa/adump'
*.audit_trail='db'
*.cluster_database_instances=2
*.cluster_database=true
*.compatible='11.1.0.0.0'
*.control_files='+SG_DG/vstbqa/controlfile/current.261.686073359','+SG_DG/vstbqa/controlfile/current.260.686073359'
*.control_management_pack_access='DIAGNOSTIC+TUNING'
*.db_block_size=8192
*.db_create_file_dest='+VB_DG' ( files destination
*.db_domain=''
*.db_name='vstbqa' ( destination db name
*.db_recovery_file_dest='+VB_DG'
*.db_recovery_file_dest_size=21474836480
*.diagnostic_dest='/opt/oracle'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=vstbqaXDB)'
vstbqa2.instance_number=2
vstbqa1.instance_number=1
vstbqa1.local_listener='LISTENER_VSTBQA1'
vstbqa2.local_listener='LISTENER_VSTBQA2'
*.log_archive_dest_1='LOCATION=+SG_DG/'
*.log_archive_format='%t_%s_%r.arc'
*.nls_territory='HONG KONG'
*.open_cursors=300
*.pga_aggregate_target=314572800
*.processes=300
*.remote_listener='LISTENERS_VSTBQA'
*.remote_login_passwordfile='exclusive'
*.sessions=335
*.sga_target=314572800
vstbqa2.thread=2
vstbqa1.thread=1
vstbqa1.undo_tablespace='UNDOTBS1'
vstbqa2.undo_tablespace='UNDOTBS2'
3. Create administration directory on node1 (qapdb01)
$ cd /opt/oracle/admin
$ ls
+ASM cviqa sgcmsqa sgdmqa sgptqa
$ cp -r sgptqa vstbqa
4. Edit $ORACLE_HOME/network/admin/tnsnames.ora
VSTBQA =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = qapdb01-vip)(PORT = 1521))
(ADDRESS = (PROTOCOL = TCP)(HOST = qapdb03-vip)(PORT = 1521))
(LOAD_BALANCE = yes)
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = VSTBQA)
)
)
VSTBQA2 =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = qapdb03-vip)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = VSTBQA)
(INSTANCE_NAME = VSTBQA2)
)
)
VSTBQA1 =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = qapdb01-vip)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = VSTBQA)
(INSTANCE_NAME = VSTBQA1)
)
)
LISTENER_VSTBQA2 =
(ADDRESS = (PROTOCOL = TCP)(HOST = qapdb03-vip)(PORT = 1521))
LISTENER_VSTBQA1 =
(ADDRESS = (PROTOCOL = TCP)(HOST = qapdb01-vip)(PORT = 1521))
LISTENERS_VSTBQA =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = qapdb01-vip)(PORT = 1521))
(ADDRESS = (PROTOCOL = TCP)(HOST = qapdb03-vip)(PORT = 1521))
)
5. Create orapwvstbqa1 password file under $ORACLE_HOME/dbs
$ cd $ORACLE_HOME/dbs
$ orapwd file=orapwvstbqa1 password=12345
6. start target database vstbqa in nomount
$ ORACLE_SID=vstbqa1
$ export ORACLE_SID
$ sqlplus '/as sysdba'
SQL*Plus: Release 11.1.0.7.0 - Production on Sat Nov 14 13:38:49 2009
Copyright (c) 1982, 2008, Oracle. All rights reserved.
Connected to an idle instance.
SQL> startup nomount pfile='/tmp/initvstbqa1.ora';
ORACLE instance started.
Total System Global Area 313245696 bytes
Fixed Size 2102088 bytes
Variable Size 243272888 bytes
Database Buffers 62914560 bytes
Redo Buffers 4956160 bytes
SQL>
7. Start Rman and allocate auxiliary channels to the new database
$ ORACLE_SID=sgptqa1;export ORACLE_SID
$ rman target / nocatalog auxiliary sys/12345@vstbqa1
Recovery Manager: Release 11.1.0.7.0 - Production on Mon Nov 16 09:42:12 2009
Copyright (c) 1982, 2007, Oracle. All rights reserved.
connected to target database: SGPTQA (DBID=3621525582)
using target database control file instead of recovery catalog
connected to auxiliary database: VSTBQA (not mounted)
8. Duplicate the database
Recovery Manager: Release 11.1.0.7.0 - Production on Mon Nov 16 10:51:46 2009
Copyright (c) 1982, 2007, Oracle. All rights reserved.
connected to target database: SGPTQA (DBID=3621525582)
using target database control file instead of recovery catalog
connected to auxiliary database: VSTBQA (not mounted)
RMAN> duplicate target database to vstbqa;
Starting Duplicate Db at 16-NOV-09
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: SID=310 device type=DISK
contents of Memory Script:
{
set until scn 5436419800;
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;
set newname for clone datafile 7 to new;
restore
clone database
;
}
executing Memory Script
executing command: SET until clause
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
Starting restore at 16-NOV-09
using channel ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: starting datafile backup set restore
channel ORA_AUX_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_AUX_DISK_1: restoring datafile 00001 to +VB_DG
channel ORA_AUX_DISK_1: restoring datafile 00002 to +VB_DG
channel ORA_AUX_DISK_1: restoring datafile 00003 to +VB_DG
channel ORA_AUX_DISK_1: restoring datafile 00004 to +VB_DG
channel ORA_AUX_DISK_1: restoring datafile 00005 to +VB_DG
channel ORA_AUX_DISK_1: restoring datafile 00006 to +VB_DG
channel ORA_AUX_DISK_1: restoring datafile 00007 to +VB_DG
channel ORA_AUX_DISK_1: reading from backup piece /export/home/oracle/bkup/sgptqa/bkup_SGPTQA_377_1_703054874.dat
channel ORA_AUX_DISK_1: piece handle=/export/home/oracle/bkup/sgptqa/bkup_SGPTQA_377_1_703054874.dat tag=TAG20091116T050113
channel ORA_AUX_DISK_1: restored backup piece 1
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:04:15
Finished restore at 16-NOV-09
sql statement: CREATE CONTROLFILE REUSE SET DATABASE "VSTBQA" RESETLOGS ARCHIVELOG
MAXLOGFILES 192
MAXLOGMEMBERS 3
MAXDATAFILES 1024
MAXINSTANCES 32
MAXLOGHISTORY 584
LOGFILE
GROUP 1 SIZE 10 M ,
GROUP 2 SIZE 10 M
DATAFILE
'+VB_DG/vstbqa/datafile/system.278.703075939'
CHARACTER SET AL32UTF8
contents of Memory Script:
{
switch clone datafile all;
}
executing Memory Script
datafile 2 switched to datafile copy
input datafile copy RECID=1 STAMP=703076193 file name=+VB_DG/vstbqa/datafile/sysaux.277.703075937
datafile 3 switched to datafile copy
input datafile copy RECID=2 STAMP=703076193 file name=+VB_DG/vstbqa/datafile/undotbs1.282.703075939
datafile 4 switched to datafile copy
input datafile copy RECID=3 STAMP=703076193 file name=+VB_DG/vstbqa/datafile/users.283.703075939
datafile 5 switched to datafile copy
input datafile copy RECID=4 STAMP=703076193 file name=+VB_DG/vstbqa/datafile/example.281.703075939
datafile 6 switched to datafile copy
input datafile copy RECID=5 STAMP=703076193 file name=+VB_DG/vstbqa/datafile/undotbs2.279.703075939
datafile 7 switched to datafile copy
input datafile copy RECID=6 STAMP=703076193 file name=+VB_DG/vstbqa/datafile/sgpt_tbs.280.703075939
contents of Memory Script:
{
set until scn 5436419800;
recover
clone database
delete archivelog
;
}
executing Memory Script
executing command: SET until clause
Starting recover at 16-NOV-09
using channel ORA_AUX_DISK_1
starting media recovery
archived log for thread 1 with sequence 1711 is already on disk as file +SG_DG/sgptqa/archivelog/2009_11_16/thread_1_seq_1711.840.703 055061
archived log for thread 1 with sequence 1712 is already on disk as file +SG_DG/sgptqa/archivelog/2009_11_16/thread_1_seq_1712.1106.70 3065623
archived log for thread 1 with sequence 1713 is already on disk as file +SG_DG/sgptqa/archivelog/2009_11_16/thread_1_seq_1713.347.703 066409
archived log for thread 1 with sequence 1714 is already on disk as file +SG_DG/sgptqa/archivelog/2009_11_16/thread_1_seq_1714.571.703 066423
archived log for thread 2 with sequence 2053 is already on disk as file +SG_DG/sgptqa/archivelog/2009_11_16/thread_2_seq_2053.302.703 055059
archived log for thread 2 with sequence 2054 is already on disk as file +SG_DG/sgptqa/archivelog/2009_11_16/thread_2_seq_2054.914.703 066387
archived log for thread 2 with sequence 2055 is already on disk as file +SG_DG/sgptqa/archivelog/2009_11_16/thread_2_seq_2055.558.703 066399
archived log for thread 2 with sequence 2056 is already on disk as file +SG_DG/sgptqa/archivelog/2009_11_16/thread_2_seq_2056.514.703 066403
archived log for thread 2 with sequence 2057 is already on disk as file +SG_DG/sgptqa/archivelog/2009_11_16/thread_2_seq_2057.603.703 066409
archived log for thread 2 with sequence 2058 is already on disk as file +SG_DG/sgptqa/archivelog/2009_11_16/thread_2_seq_2058.838.703 066411
archived log for thread 2 with sequence 2059 is already on disk as file +SG_DG/sgptqa/archivelog/2009_11_16/thread_2_seq_2059.727.703 066417
archived log for thread 2 with sequence 2060 is already on disk as file +SG_DG/sgptqa/archivelog/2009_11_16/thread_2_seq_2060.1090.70 3069531
archived log file name=+SG_DG/sgptqa/archivelog/2009_11_16/thread_2_seq_2053.302.703055059 thread=2 sequence=2053
archived log file name=+SG_DG/sgptqa/archivelog/2009_11_16/thread_1_seq_1711.840.703055061 thread=1 sequence=0
archived log file name=+SG_DG/sgptqa/archivelog/2009_11_16/thread_2_seq_2054.914.703066387 thread=2 sequence=2054
archived log file name=+SG_DG/sgptqa/archivelog/2009_11_16/thread_1_seq_1712.1106.703065623 thread=1 sequence=1712
archived log file name=+SG_DG/sgptqa/archivelog/2009_11_16/thread_1_seq_1713.347.703066409 thread=1 sequence=1713
archived log file name=+SG_DG/sgptqa/archivelog/2009_11_16/thread_2_seq_2055.558.703066399 thread=2 sequence=2055
archived log file name=+SG_DG/sgptqa/archivelog/2009_11_16/thread_2_seq_2056.514.703066403 thread=2 sequence=2056
archived log file name=+SG_DG/sgptqa/archivelog/2009_11_16/thread_2_seq_2057.603.703066409 thread=2 sequence=2057
archived log file name=+SG_DG/sgptqa/archivelog/2009_11_16/thread_1_seq_1714.571.703066423 thread=1 sequence=1714
archived log file name=+SG_DG/sgptqa/archivelog/2009_11_16/thread_2_seq_2058.838.703066411 thread=2 sequence=2058
archived log file name=+SG_DG/sgptqa/archivelog/2009_11_16/thread_2_seq_2059.727.703066417 thread=2 sequence=2059
archived log file name=+SG_DG/sgptqa/archivelog/2009_11_16/thread_2_seq_2060.1090.703069531 thread=2 sequence=2060
media recovery complete, elapsed time: 00:00:06
Finished recover at 16-NOV-09
contents of Memory Script:
{
shutdown clone immediate;
startup clone nomount ;
}
executing Memory Script
database dismounted
Oracle instance shut down
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of Duplicate Db command at 11/16/2009 10:56:52
RMAN-03015: error occurred in stored script Memory Script
RMAN-04006: error from auxiliary database: ORA-12523: TNS:listener could not find instance appropriate for the client connection
9. Update the control files entries on initvstbqa1.ora
10. Manually start the database
$ ORACLE_SID=vstbqa1;export ORACLE_SID
$ sqlplus '/as sysdba'
SQL*Plus: Release 11.1.0.7.0 - Production on Mon Nov 16 11:45:28 2009
Copyright (c) 1982, 2008, Oracle. All rights reserved.
Connected to an idle instance.
SQL> startup mount;
ORACLE instance started.
Total System Global Area 313245696 bytes
Fixed Size 2102088 bytes
Variable Size 243272888 bytes
Database Buffers 62914560 bytes
Redo Buffers 4956160 bytes
Database mounted.
SQL> alter database open resetlogs;
Database altered.
11. Add tempfile for temp tablespace
SQL> select name from v$tempfile;
no rows selected
SQL> alter tablespace temp add tempfile size 1024M autoextend on next 256M maxsize 8192M;
Tablespace altered.
SQL> vi
NAME
--------------------------------------------------------------------------------
+VB_DG/vstbqa/tempfile/temp.309.703079631
12. Migrate initial parameter initvstbqa1 to ‘VB_DG’ ASM Diskgroup
a. Create spfile from pfile
SQL> create spfile='+VB_DG' from pfile='/tmp/initvstbqa1.ora';
File created.
b. Create spfilevstbqa.ora alias by ASMCMD
$ ORACLE_SID=+ASM1;export ORACLE_SID
$ asmcmd
ASMCMD> ls
SG_DG/
VB_DG/
VI_DG/
ASMCMD> cd VB_DG
ASMCMD> cd VS*
ASMCMD> ls
CONTROLFILE/
DATAFILE/
ONLINELOG/
PARAMETERFILE/
TEMPFILE/
ASMCMD> ls -l
Type Redund Striped Time Sys Name
Y CONTROLFILE/
Y DATAFILE/
Y ONLINELOG/
Y PARAMETERFILE/
Y TEMPFILE/
ASMCMD> pwd
+VB_DG/VSTBQA
ASMCMD> ls -l P*
Type Redund Striped Time Sys Name
PARAMETERFILE UNPROT COARSE NOV 16 12:00:00 Y spfile.310.703080253
ASMCMD> mkalias '+VB_DG/VSTBQA/PARAMETERFILE/spfile.310.703080253' spfilevstbqa.ora
ASMCMD> ls
CONTROLFILE/
DATAFILE/
ONLINELOG/
PARAMETERFILE/
TEMPFILE/
spfilevstbqa.ora
ASMCMD> exit
c. Create initial parameter initvstbqq1.ora on $ORACLE_HOME/dbs
$ echo "SPFILE='+VB_DG/VSTBQA/spfilevstbqa.ora'" > initvstbqa1.ora
$ cat initvstbqa1.ora
SPFILE='+VB_DG/VSTBQA/spfilevstbqa.ora'
d. Restart database
SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup
ORACLE instance started.
Total System Global Area 313245696 bytes
Fixed Size 2102088 bytes
Variable Size 243272888 bytes
Database Buffers 62914560 bytes
Redo Buffers 4956160 bytes
Database mounted.
Database opened.
SQL>
SQL> show parameter spfile
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
spfile string +VB_DG/vstbqa/spfilevstbqa.ora
SQL>
13. Start database instance vstbqa2 on the second node (qapdb03)
a. Login qapdb03
b. Create the initial parameter and password files
$ cd $ORACLE_HOME/dbs
$ scp qapdb01:$ORACLE_HOME/dbs/initvstbqa1.ora initvstbqa2.ora
initvstbqa1.ora 100% |*****************************| 40 00:00
$ scp qapdb01:$ORACLE_HOME/dbs/orapwvstbqa1 orapwvstbqa2
orapwvstbqa1 100% |*****************************| 1536 00:00
c. Create administration directory
$ cd /opt/oracle/admin
$ scp -rp qapdb01:/opt/oracle/admin/vstbqa .
d. Update tnsnames.ora
$ cd $ORACLE_HOME/network/admin
$ scp -p qapdb01:$ORACLE_HOME/network/admin/tnsnames.ora .
tnsnames.ora 100% |*****************************| 5518 00:00
e. Change database to cluster mode at qapdb01 (Login qapdb01)
SQL> alter system set cluster_database=TRUE scope=spfile;
System altered.
SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup
ORACLE instance started.
Total System Global Area 313245696 bytes
Fixed Size 2102088 bytes
Variable Size 243272888 bytes
Database Buffers 62914560 bytes
Redo Buffers 4956160 bytes
Database mounted.
Database opened.
SQL> show parameter cluster
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
cluster_database boolean TRUE
cluster_database_instances integer 2
cluster_interconnects string
SQL>
f. Start the database instance on the second node
$ ORACLE_SID=vstbqa2;export ORACLE_SID
$ sqlplus '/as sysdba'
SQL*Plus: Release 11.1.0.7.0 - Production on Mon Nov 16 12:30:56 2009
Copyright (c) 1982, 2008, Oracle. All rights reserved.
Connected to an idle instance.
SQL> startup
ORACLE instance started.
Total System Global Area 313245696 bytes
Fixed Size 2102088 bytes
Variable Size 234884280 bytes
Database Buffers 71303168 bytes
Redo Buffers 4956160 bytes
Database mounted.
Database opened.
SQL>
14. Register databases
$ srvctl add database -d vstbqa -o $ORACLE_HOME
$ srvctl add instance -d vstbqa -i vstbqa1 -n qapdb01
$ srvctl add instance -d vstbqa -i vstbqa2 -n qapdb03
15. Modify instances resource dependencies
$ srvctl modify instance -d sgptpro -i sgptpro1 -s +ASM1
$ srvctl modify instance -d sgptpro -i sgptpro2 -s +ASM2
$ crs_stat -p | grep REQ
REQUIRED_RESOURCES=
REQUIRED_RESOURCES=ora.wvpdb09.ASM1.asm
REQUIRED_RESOURCES=ora.wvpdb08.ASM2.asm
REQUIRED_RESOURCES=
REQUIRED_RESOURCES=ora.wvpdb09.ASM1.asm
REQUIRED_RESOURCES=ora.wvpdb08.ASM2.asm
REQUIRED_RESOURCES=
REQUIRED_RESOURCES=ora.wvpdb09.ASM1.asm
REQUIRED_RESOURCES=ora.wvpdb08.ASM2.asm
REQUIRED_RESOURCES=
REQUIRED_RESOURCES=ora.wvpdb09.ASM1.asm
REQUIRED_RESOURCES=ora.wvpdb08.ASM2.asm
REQUIRED_RESOURCES=
REQUIRED_RESOURCES=ora.
REQUIRED_RESOURCES=
REQUIRED_RESOURCES=
REQUIRED_RESOURCES=
REQUIRED_RESOURCES=
REQUIRED_RESOURCES=ora.
REQUIRED_RESOURCES=
REQUIRED_RESOURCES=
REQUIRED_RESOURCES=
16. Start the database by srvctl
$ crs_stat -t
Name Type Target State Host
------------------------------------------------------------
ora....a1.inst application ONLINE ONLINE qapdb01
ora....a2.inst application ONLINE ONLINE qapdb03
ora.cviqa.db application ONLINE ONLINE qapdb03
ora....SM1.asm application ONLINE ONLINE qapdb01
ora....01.lsnr application ONLINE ONLINE qapdb01
ora....b01.gsd application ONLINE ONLINE qapdb01
ora....b01.ons application ONLINE ONLINE qapdb01
ora.... application ONLINE ONLINE qapdb01
ora....SM2.asm application ONLINE ONLINE qapdb03
ora....03.lsnr application ONLINE ONLINE qapdb03
ora....b03.gsd application ONLINE ONLINE qapdb03
ora....b03.ons application ONLINE ONLINE qapdb03
ora.... application ONLINE ONLINE qapdb03
ora.sgcmsqa.db application ONLINE ONLINE qapdb01
ora....a1.inst application ONLINE ONLINE qapdb01
ora....a2.inst application ONLINE ONLINE qapdb03
ora.sgdmqa.db application ONLINE ONLINE qapdb01
ora....a1.inst application ONLINE ONLINE qapdb01
ora....a2.inst application ONLINE ONLINE qapdb03
ora.sgptqa.db application ONLINE ONLINE qapdb01
ora....a1.inst application ONLINE ONLINE qapdb01
ora....a2.inst application ONLINE ONLINE qapdb03
ora.vstbqa.db application OFFLINE OFFLINE
ora....a1.inst application OFFLINE OFFLINE
ora....a2.inst application OFFLINE OFFLINE
$ srvctl start database -d vstbqa
$ crs_stat -t
Name Type Target State Host
------------------------------------------------------------
ora....a1.inst application ONLINE ONLINE qapdb01
ora....a2.inst application ONLINE ONLINE qapdb03
ora.cviqa.db application ONLINE ONLINE qapdb03
ora....SM1.asm application ONLINE ONLINE qapdb01
ora....01.lsnr application ONLINE ONLINE qapdb01
ora....b01.gsd application ONLINE ONLINE qapdb01
ora....b01.ons application ONLINE ONLINE qapdb01
ora.... application ONLINE ONLINE qapdb01
ora....SM2.asm application ONLINE ONLINE qapdb03
ora....03.lsnr application ONLINE ONLINE qapdb03
ora....b03.gsd application ONLINE ONLINE qapdb03
ora....b03.ons application ONLINE ONLINE qapdb03
ora.... application ONLINE ONLINE qapdb03
ora.sgcmsqa.db application ONLINE ONLINE qapdb01
ora....a1.inst application ONLINE ONLINE qapdb01
ora....a2.inst application ONLINE ONLINE qapdb03
ora.sgdmqa.db application ONLINE ONLINE qapdb01
ora....a1.inst application ONLINE ONLINE qapdb01
ora....a2.inst application ONLINE ONLINE qapdb03
ora.sgptqa.db application ONLINE ONLINE qapdb01
ora....a1.inst application ONLINE ONLINE qapdb01
ora....a2.inst application ONLINE ONLINE qapdb03
ora.vstbqa.db application ONLINE ONLINE qapdb03
ora....a1.inst application ONLINE ONLINE qapdb01
ora....a2.inst application ONLINE ONLINE qapdb03
17. Remove the (UR=A) entry from tnsnames.
If you try to duplicate database using rman and get following error:
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-00554: initialization of internal recovery manager package failed
RMAN-04006: error from auxiliary database: ORA-12528: TNS:listener: all appropriate instances are blocking new connections
Most probably your auxiliary database (in nomount state) is the only instance works on current ORACLE_HOME. Each instance is registered in listener by PMON process, which starts only when database is at least mounted. If there is nothing registered in listener before, instance in blocked.
You can do 2 things:
1. Modify listener.ora like this:
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(GLOBAL_DBNAME = AUXDB)
(ORACLE_HOME = /app/oracle/product/10.2.0/)
(SID_NAME = AUXDB)
)
)
OR
2. Modify tnsnames.ora (10g only!) :
AUXDB =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = AUXDB)(UR=A)
)
)
It was caused by the *.cluster_database was set to false. Set cluster_database=fales, the problem can be fixed.
sql statement: CREATE CONTROLFILE REUSE SET DATABASE "VSTBQA" RESETLOGS ARCHIVELOG
MAXLOGFILES 192
MAXLOGMEMBERS 3
MAXDATAFILES 1024
MAXINSTANCES 32
MAXLOGHISTORY 584
LOGFILE
GROUP 1 SIZE 10 M ,
GROUP 2 SIZE 10 M
DATAFILE
'+VB_DG/vstbqa/datafile/system.271.703075305'
CHARACTER SET AL32UTF8
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of Duplicate Db command at 11/16/2009 10:45:56
RMAN-06136: ORACLE error from auxiliary database: ORA-01503: CREATE CONTROLFILE failed
ORA-12720: operation requires database is in EXCLUSIVE mode exit
Issue:
2009-11-26 09:52:13.988: [ CRSAPP][54] StartResource error for ora.sgcmspro.sgcmspro1.inst error code = 1
2009-11-26 09:52:13.988: [ CRSAPP][55] StartResource error for ora.sgdmpro.sgdmpro1.inst error code = 1
2009-11-26 09:52:13.988: [ CRSAPP][56] StartResource error for ora.sgptpro.sgptpro1.inst error code = 1
2009-11-26 09:52:14.083: [ CRSAPP][57] StartResource error for ora.vstbpro.vstbpro1.inst error code = 1
2009-11-26 09:52:15.225: [ CRSRES][53] Start of `ora.wvpdb09.LISTENER_WVPDB09.lsnr` on member `wvpdb09` succeeded.
2009-11-26 09:52:15.693: [ CRSRES][121] CRS-1002: Resource 'ora.wvpdb09.LISTENER_WVPDB09.lsnr' is already running on member 'wvpdb09'
2009-11-26 09:52:15.933: [ CRSRES][56] Start of `ora.sgptpro.sgptpro1.inst` on member `wvpdb09` failed.
2009-11-26 09:52:15.936: [ CRSRES][54] Start of `ora.sgcmspro.sgcmspro1.inst` on member `wvpdb09` failed.
2009-11-26 09:52:15.939: [ CRSRES][55] Start of `ora.sgdmpro.sgdmpro1.inst` on member `wvpdb09` failed.
2009-11-26 09:52:15.944: [ CRSRES][57] Start of `ora.vstbpro.vstbpro1.inst` on member `wvpdb09` failed.
2009-11-26 09:52:19.019: [ CRSRES][139] startRunnable: setting CLI values
2009-11-26 09:52:19.030: [ CRSRES][139] Attempting to start `ora.wvpdb09.ons` on member `wvpdb09`
2009-11-26 09:52:20.843: [ CRSRES][139] Start of `ora.wvpdb09.ons` on member `wvpdb09` succeeded.
2009-11-26 09:52:20.870: [ CRSRES][58] Start of `ora.wvpdb09.ASM1.asm` on member `wvpdb09` succeeded.
2009-11-26 09:52:20.872: [ CRSRES][141] Skip online resource: ora.wvpdb09.ons
Issue
--- alertqarac1.log shows ---
2008-07-15 19:08:16.303
[crsd(18464)]CRS-1205:Auto-start failed for the CRS resource . Details in qarac1.
RAC Instance is not coming up after the node is rebooted.
RESEARCH
cd /u01/app/oracle/product/10.2.0/crs/log/qarac1
find . -ls | grep -v "4 drw" | grep -v client
4686403 588 -rw-r--r-- 1 root root 596559 Jul 15 19:13 ./crsd/crsd.log
4686401 4 -rw-r--r-- 1 oracle oinstall 794 Jul 15 19:07 ./evmd/evmdOUT.log
4686402 188 -rw-r--r-- 1 oracle oinstall 187483 Jul 15 19:08 ./evmd/evmd.log
4686404 4 -rw-r--r-- 1 oracle oinstall 193 Jul 15 19:07 ./cssd/cssdOUT.log
4686405 4 -rw-r--r-- 1 oracle oinstall 5 Jul 10 20:00 ./cssd/qarac1.pid
4686406 316 -rw-r--r-- 1 oracle oinstall 315799 Jul 15 19:08 ./cssd/ocssd.log
4917004 0 -rw-r--r-- 1 oracle oinstall 0 Jul 15 19:07 ./cssd/oclsomon/oclsomon.log
4917003 0 -rw-r--r-- 1 oracle oinstall 0 Jul 11 19:09 ./cssd/oclsomon/oclsomon.ba1
4915672 0 -rw-r--r-- 1 oracle oinstall 0 Jul 11 14:32 ./cssd/oclsomon/oclsomon.ba2
4686418 4 -rw-r--r-- 1 oracle oinstall 3356 Jul 15 19:08 ./racg/ora.qarac1.ons.log
4686396 12 -rw-rw-r-- 1 root oinstall 10289 Jul 15 19:08 ./alertqarac1.log
qarac1..ORASID1->
qarac1..ORASID1-> grep -i xgrid ./crsd/crsd.log
2008-07-15 15:42:41.747: [ CRSRES][1495452000]0Resource Registered: ora.ORASID.db
2008-07-15 15:44:27.609: [ CRSRES][1495452000]0Resource Registered: ora.ORASID.ORASID1.inst
2008-07-15 15:44:33.143: [ CRSRES][1495452000]0Resource Registered: ora.ORASID.ORASID2.inst
2008-07-15 15:54:35.000: [ CRSRES][1497553248]0Attempting to start `ora.ORASID.ORASID1.inst` on member `qarac1`
2008-07-15 15:54:35.115: [ CRSRES][1510144352]0Attempting to start `ora.ORASID.ORASID2.inst` on member `qarac2`
2008-07-15 15:55:01.283: [ CRSRES][1497553248]0Start of `ora.ORASID.ORASID1.inst` on member `qarac1` succeeded.
2008-07-15 15:55:01.420: [ CRSRES][1497553248]0Attempting to start `ora.ORASID.db` on member `qarac2`
2008-07-15 15:55:01.542: [ CRSRES][1497553248]0Start of `ora.ORASID.db` on member `qarac2` succeeded.
2008-07-15 15:55:04.931: [ CRSRES][1510144352]0Start of `ora.ORASID.ORASID2.inst` on member `qarac2` succeeded.
2008-07-15 16:50:22.377: [ CRSRES][1524836704]0Attempting to start `ora.ORASID.db` on member `qarac1`
2008-07-15 16:50:22.566: [ CRSRES][1524836704]0Start of `ora.ORASID.db` on member `qarac1` succeeded.
2008-07-15 16:57:40.637: [ CRSRES][1524836704]0Attempting to start `ora.ORASID.ORASID2.inst` on member `qarac2`
2008-07-15 16:58:05.271: [ CRSRES][1524836704]0Start of `ora.ORASID.ORASID2.inst` on member `qarac2` succeeded.
2008-07-15 17:56:08.083: [ CRSRES][1522735456]0Attempting to start `ora.ORASID.ORASID2.inst` on member `qarac2`
2008-07-15 17:56:32.719: [ CRSRES][1522735456]0Start of `ora.ORASID.ORASID2.inst` on member `qarac2` succeeded.
2008-07-15 19:08:03.990: [ CRSRES][1484962144]0Attempting to start `ora.ORASID.ORASID1.inst` on member `qarac1`
2008-07-15 19:08:14.731: [ CRSAPP][1484962144]0StartResource error for ora.ORASID.ORASID1.inst error code = 1
2008-07-15 19:08:16.283: [ CRSRES][1484962144]0Start of `ora.ORASID.ORASID1.inst` on member `qarac1` failed.
qarac1..ORASID1->
vi ./crsd/crsd.log
2008-07-15 19:08:14.731: [ CRSAPP][1484962144]0StartResource error for ora.ORASID.ORASID1.inst error code = 1
2008-07-15 19:08:16.283: [ CRSRES][1484962144]0Start of `ora.ORASID.ORASID1.inst` on member `qarac1` failed.
2008-07-15 19:08:24.519: [ CRSRES][1482860896]0startRunnable: setting CLI values
2008-07-15 19:08:24.542: [ CRSRES][1482860896]0Attempting to start `ora.qarac1.ons` on member `qarac1`
2008-07-15 19:08:25.993: [ CRSRES][1482860896]0Start of `ora.qarac1.ons` on member `qarac1` succeeded.
2008-07-15 19:08:26.081: [ CRSRES][1487063392]0Start of `ora.qarac1.ASM1.asm` on member `qarac1` succeeded.
2008-07-15 19:08:26.082: [ CRSRES][1484962144]0Skip online resource: ora.qarac1.ons
2008-07-15 19:08:26.117: [ CRSRES][1487063392]0startRunnable: setting CLI values
2008-07-15 19:08:26.125: [ CRSRES][1487063392]0Attempting to start `ora.qarac1.gsd` on member `qarac1`
2008-07-15 19:08:26.459: [ CRSRES][1487063392]0Start of `ora.qarac1.gsd` on member `qarac1` succeeded.
2008-07-15 19:13:16.581: [ CRSRES][1487063392]0CRS-1002: Resource 'ora.qarac1.LISTENER_QARAC1.lsnr' is already running on member 'qarac1'
## BINGO -- CRS tried to bring up ora.ORASID.ORASID1.inst BEFORE ora.qarac1.ASM1.asm!
## So there's no dependency on ASM -- here's hot to verify this:
export ORACLE_HOME=$CRS_HOME
export PATH=$ORACLE_HOME/bin:$PATH
crs_stat -p | grep REQ
## qarac1..ORASID1-> crs_stat -p | grep REQ
## REQUIRED_RESOURCES=
## REQUIRED_RESOURCES=
## REQUIRED_RESOURCES=
## REQUIRED_RESOURCES=
## REQUIRED_RESOURCES=ora.
## REQUIRED_RESOURCES=
## REQUIRED_RESOURCES=
## REQUIRED_RESOURCES=
## REQUIRED_RESOURCES=
## REQUIRED_RESOURCES=ora.
## REQUIRED_RESOURCES=
## REQUIRED_RESOURCES=
## REQUIRED_RESOURCES=
## qarac1..ORASID1->
## NOTE the only REQUIRED_RESOURCES are vip's it should also include ASM ...
SOLUTION
## run this on the first node
##
export ORACLE_HOME=$CRS_HOME
export PATH=$ORACLE_HOME/bin:$PATH
srvctl modify instance -d ORASID -i ORASID1 -s +ASM1
srvctl modify instance -d ORASID -i ORASID2 -s +ASM2
## qarac1..ORASID1-> crs_stat -p | grep REQ
## REQUIRED_RESOURCES=ora.qarac1.ASM1.asm
## REQUIRED_RESOURCES=ora.qarac2.ASM2.asm
## REQUIRED_RESOURCES=
## REQUIRED_RESOURCES=
## REQUIRED_RESOURCES=ora.
## REQUIRED_RESOURCES=
## REQUIRED_RESOURCES=
## REQUIRED_RESOURCES=
## REQUIRED_RESOURCES=
## REQUIRED_RESOURCES=ora.
## REQUIRED_RESOURCES=
## REQUIRED_RESOURCES=
## REQUIRED_RESOURCES=
## qarac1..ORASID1->
## REFERENCE:
##
Note:276208.1 CRS is not starting first the ASM instance
I used the following search terms to find it:
"Start of" inst "on member" failed
................
................
In order to avoid copyright disputes, this page is only a partial summary.
To fulfill the demand for quickly locating and searching documents.
It is intelligent file search solution for home and business.
Related download
- why take this class bmc software
- 1 nelielas java klases programmas definēšana kompilēšana
- introduction to oracle
- cloning database from sgptqa to vtbqa
- dbms lab manual for iv sem b
- displaying the sap directories
- prakash s dba blog where learning begins
- sql server 2005 and oracle 10g security comparison
- lecture notes for