Friday, June 24, 2016

Database Clone Using RMAN on same server with connection to Target and Catalog DB.


My Environment
Operating System : RHEL 6.4 64 Bit
DB Version : 12CR1 [12.1.0.1.0]
Primary DB Name : ORCL
Primary DB Mount Point : /PRIM
Clone DB Name : ORATEST
Clone DB Mount Point : /TEST

Creating Directory Structures:
First need to create directory structure for the clone db. Easy way to create the directory structure is copy the source filesystem and change the path according to the clone db requirement and delete the content of the source at clone db side.

  • Copy ORACLE_BASE and ORACLE_HOME from source to clone db.
  • Create directory structure for control files, fast_recovery_area.
  • Create directory structure for audit files.

Establishing Connection:
Edit listener.ora, tnsnames.ora and pfile copied from source db and change according to clone db. Below is the content of my clone db listener, tnsnames file and pfile. Once everything is changed, copy the clone DB’s pfile to source/target[ORCL] DB since we need to start our auxiliary DB[ORATEST] from there.

listener.ora
# listener.ora Network Configuration File: /TEST/u02/app/oratest/product/12.1.0/dbhome_1/network/admin/listener.ora
# Generated by Oracle configuration tools.

ORATEST =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1523))
(ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1523))
)
)

SID_LIST_oratest =
(SID_LIST =
(SID_DESC =
(ORACLE_HOME=/TEST/u02/app/oratest/product/12.1.0/dbhome_1)
(SID_NAME=oratest)
)
)

SID_LIST_orcl =
(SID_LIST =
(SID_DESC =
(ORACLE_HOME=/PRIM/u02/app/oraprim/product/12.1.0/dbhome_1)
(SID_NAME=orcl)
)
)

SID_LIST_oracat =
(SID_LIST =
(SID_DESC =
(ORACLE_HOME=/CATDB/u01/app/oracat/product/12.1.0/dbhome_1)
(SID_NAME=oracat)
)
)

NOTE: Copy the listener entry of oratest to source db listener file. Then only the connection is possible between these two databases. I’ve also entered the catalog db details here since I need to register this DB later.

tnsnames.ora
# tnsnames.ora Network Configuration File: /TEST/u02/app/oratest/product/12.1.0/dbhome_1/network/admin/tnsnames.ora
# Generated by Oracle configuration tools.

ORATEST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1523))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = oratest.localdomain)
)
)

ORACAT =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1522))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = oracat.localdomain)
)
)

ORCL =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = orcl.localdomain)
)
)

NOTE: Copy the tnsnames entry of oratest to source db listener file. Then only the connection is possible between these two databases. I’ve also entered the catalog db details here since I need to register this DB later.

pfile:
orcl.__data_transfer_cache_size=0
orcl.__db_cache_size=822083584
orcl.__java_pool_size=16777216
orcl.__large_pool_size=150994944
orcl.__oracle_base='/TEST/u02/app/oratest'#ORACLE_BASE set from environment
orcl.__pga_aggregate_target=436207616
orcl.__sga_target=1291845632
orcl.__shared_io_pool_size=0
orcl.__shared_pool_size=285212672
orcl.__streams_pool_size=0
*.audit_file_dest='/TEST/u02/app/oratest/admin/oratest/adump'
*.audit_trail='db'
*.compatible='12.1.0.0.0'
*.control_files='/TEST/u02/oradata/oratest/control01.ctl','/TEST/u02/app/oratest/fast_recovery_area/oratest/control02.ctl'
*.db_block_size=8192
*.db_domain='localdomain'
*.db_name='oratest'
*.diagnostic_dest='/TEST/u02/app/oratest'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=oratestXDB)'
*.log_archive_dest='/TEST/u02/app/oratest/product/12.1.0/dbhome_1/database/archive'
*.log_archive_format='Log_%d_%s_%t_%r.arc'
*.open_cursors=300
*.pga_aggregate_target=410m
*.processes=300
*.remote_login_passwordfile='EXCLUSIVE'
db_file_name_convert='/PRIM/u02/oradata/orcl','/TEST/u02/oradata/oratest'
log_file_name_convert='/PRIM/u02/oradata/orcl','/TEST/u02/oradata/oratest'
*.sga_target=1231m
*.undo_tablespace='UNDOTBS1'

NOTE: db_file_name_convert and log_file_name_convert are the two important parameters needs to be changed. If is not mentioned in pfile or not set properly, then RMAN will throw error as follows

RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of Duplicate Db command at 05/17/2006 07:54:10
RMAN-05501: aborting duplication of target database
RMAN-05001: auxiliary filename /PRIM/u02/oradata/orcl/system01.dbf conflicts with a file used by the target database

Cause: RMAN is attempting to use the specified file name as a restore destination in the auxiliary database, but this name is already used by the target database.

Action: Recommended solution for the RMAN-00501 error is to use the configure auxname command.

Eg: configure auxname for datafile ‘/PRIM/u02/oradata/orcl/system01.dbf’ to ‘/TEST/u02/oradata/oratest/system01.dbf’

RMAN will replace the target DB’s files if the below given duplicate command is used

duplicate target database for standby nofilenamecheck;

Now you can check the connection as follows

[oraprim@localhost ~]$ export ORACLE_SID=oratest
[oraprim@localhost ~]$ sqlplus '/as sysdba'

SQL*Plus: Release 12.1.0.1.0 Production on Thu Jun 23 16:14:08 2016

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

Connected to an idle instance.


Now start the DB Duplication process

  • Start the Source Database [ORCL]
  • Start the Catalog Database [ORACAT]
  • Start the Clone Database in nomount state [ORATEST]
  • Connect RMAN and start duplicating the DB

[oraprim@localhost ~]$ export ORACLE_SID=orcl
[oraprim@localhost ~]$ lsnrctl start orcl

LSNRCTL for Linux: Version 12.1.0.1.0 - Production on 23-JUN-2016 16:13:46

Copyright (c) 1991, 2013, Oracle. All rights reserved.

Starting /PRIM/u02/app/oraprim/product/12.1.0/dbhome_1/bin/tnslsnr: please wait...

TNSLSNR for Linux: Version 12.1.0.1.0 - Production
System parameter file is /PRIM/u02/app/oraprim/product/12.1.0/dbhome_1/network/admin/listener.ora
Log messages written to /PRIM/u02/app/oraprim/diag/tnslsnr/localhost/orcl/alert/log.xml
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=localhost)(PORT=1521)))

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC1521)))
STATUS of the LISTENER
------------------------
Alias orcl
Version TNSLSNR for Linux: Version 12.1.0.1.0 - Production
Start Date 23-JUN-2016 16:13:46
Uptime 0 days 0 hr. 0 min. 0 sec
Trace Level off
Security ON: Local OS Authentication
SNMP OFF
Listener Parameter File /PRIM/u02/app/oraprim/product/12.1.0/dbhome_1/network/admin/listener.ora
Listener Log File /PRIM/u02/app/oraprim/diag/tnslsnr/localhost/orcl/alert/log.xml
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=localhost)(PORT=1521)))
Services Summary...
Service "orcl" has 1 instance(s).
Instance "orcl", status UNKNOWN, has 1 handler(s) for this service...
The command completed successfully

[oraprim@localhost ~]$ sqlplus '/as sysdba'

SQL*Plus: Release 12.1.0.1.0 Production on Thu Jun 23 17:18:46 2016

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

Connected to an idle instance.

SQL> startup
ORACLE instance started.

Total System Global Area 1286066176 bytes
Fixed Size 2287960 bytes
Variable Size 452986536 bytes
Database Buffers 822083584 bytes
Redo Buffers 8708096 bytes
Database mounted.
Database opened.

[oraprim@localhost ~]$ export ORACLE_SID=oratest
[oraprim@localhost ~]$ lsnrctl start oratest

LSNRCTL for Linux: Version 12.1.0.1.0 - Production on 23-JUN-2016 16:14:01

Copyright (c) 1991, 2013, Oracle. All rights reserved.

TNS-01106: Listener using listener name orcl has already been started
[oraprim@localhost ~]$ sqlplus '/as sysdba'

SQL*Plus: Release 12.1.0.1.0 Production on Thu Jun 23 16:14:08 2016

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

Connected to an idle instance.

SQL> startup nomount pfile=/PRIM/u02/app/oraprim/product/12.1.0/dbhome_1/dbs/pfile_clone_db.ora
ORACLE instance started.

Total System Global Area 1286066176 bytes
Fixed Size 2287960 bytes
Variable Size 452986536 bytes
Database Buffers 822083584 bytes
Redo Buffers 8708096 bytes
SQL> exit

[oracat@localhost ~]$ lsnrctl start oracat

LSNRCTL for Linux: Version 12.1.0.1.0 - Production on 23-JUN-2016 17:18:43

Copyright (c) 1991, 2013, Oracle. All rights reserved.

Starting /CATDB/u01/app/oracat/product/12.1.0/dbhome_1/bin/tnslsnr: please wait...

TNSLSNR for Linux: Version 12.1.0.1.0 - Production
System parameter file is /CATDB/u01/app/oracat/product/12.1.0/dbhome_1/network/admin/listener.ora
Log messages written to /CATDB/u01/app/oracat/diag/tnslsnr/localhost/oracat/alert/log.xml
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1522)))
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=localhost)(PORT=1522)))

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC1522)))
STATUS of the LISTENER
------------------------
Alias oracat
Version TNSLSNR for Linux: Version 12.1.0.1.0 - Production
Start Date 23-JUN-2016 17:18:43
Uptime 0 days 0 hr. 0 min. 0 sec
Trace Level off
Security ON: Local OS Authentication
SNMP OFF
Listener Parameter File /CATDB/u01/app/oracat/product/12.1.0/dbhome_1/network/admin/listener.ora
Listener Log File /CATDB/u01/app/oracat/diag/tnslsnr/localhost/oracat/alert/log.xml
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1522)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=localhost)(PORT=1522)))
Services Summary...
Service "oracat" has 1 instance(s).
Instance "oracat", status UNKNOWN, has 1 handler(s) for this service...
The command completed successfully
[oracat@localhost ~]$ sqlplus '/as sysdba'

SQL*Plus: Release 12.1.0.1.0 Production on Thu Jun 23 17:18:46 2016

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

Connected to an idle instance.

SQL> startup
ORACLE instance started.

Total System Global Area 1286066176 bytes
Fixed Size 2287960 bytes
Variable Size 452986536 bytes
Database Buffers 822083584 bytes
Redo Buffers 8708096 bytes
Database mounted.
Database opened.
SQL> exit


[oraprim@localhost ~]$ ps -ef | grep pmon
oracat 2833 1 0 10:27 ? 00:00:03 ora_pmon_oracat
oraprim 5073 1 0 11:37 ? 00:00:03 ora_pmon_orcl
oraprim 11859 1 0 16:14 ? 00:00:00 ora_pmon_oratest
oraprim 11910 2587 0 16:14 pts/0 00:00:00 grep pmon


Now connect RMAN

[oraprim@localhost ~]$ export ORACLE_SID=oratest
[oraprim@localhost ~]$ rman

Recovery Manager: Release 12.1.0.1.0 - Production on Thu Jun 23 16:15:02 2016

Copyright (c) 1982, 2013, Oracle and/or its affiliates. All rights reserved.

RMAN> connect target sys/sys@orcl

connected to target database: ORCL (DBID=1434527233)

RMAN> connect catalog rman/rman@oracat

connected to recovery catalog database

RMAN> connect auxiliary /

connected to auxiliary database: ORATEST (not mounted)

RMAN> duplicate target database to oratest

Starting Duplicate Db at 23-JUN-16
starting full resync of recovery catalog
full resync complete
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: SID=20 device type=DISK
current log archived

contents of Memory Script:
{
sql clone "create spfile from memory";
}
executing Memory Script

sql statement: create spfile from memory

contents of Memory Script:
{
shutdown clone immediate;
startup clone nomount;
}
executing Memory Script

Oracle instance shut down

connected to auxiliary database (not started)
Oracle instance started

Total System Global Area 1286066176 bytes

Fixed Size 2287960 bytes
Variable Size 469763752 bytes
Database Buffers 805306368 bytes
Redo Buffers 8708096 bytes

contents of Memory Script:
{
sql clone "alter system set db_name =
''ORCL'' comment=
''Modified by RMAN duplicate'' scope=spfile";
sql clone "alter system set db_unique_name =
''ORATEST'' comment=
''Modified by RMAN duplicate'' scope=spfile";
shutdown clone immediate;
startup clone force nomount
restore clone primary controlfile;
alter clone database mount;
}
executing Memory Script

sql statement: alter system set db_name = ''ORCL'' comment= ''Modified by RMAN duplicate'' scope=spfile

sql statement: alter system set db_unique_name = ''ORATEST'' comment= ''Modified by RMAN duplicate'' scope=spfile

Oracle instance shut down

Oracle instance started

Total System Global Area 1286066176 bytes

Fixed Size 2287960 bytes
Variable Size 469763752 bytes
Database Buffers 805306368 bytes
Redo Buffers 8708096 bytes

Starting restore at 23-JUN-16
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: SID=20 device type=DISK

channel ORA_AUX_DISK_1: starting datafile backup set restore
channel ORA_AUX_DISK_1: restoring control file
channel ORA_AUX_DISK_1: reading from backup piece /CTRL_AUTOBKP/ctrl_file_c-1434527233-20160621-01_.bkp
channel ORA_AUX_DISK_1: piece handle=/CTRL_AUTOBKP/ctrl_file_c-1434527233-20160621-01_.bkp tag=TAG20160621T130917
channel ORA_AUX_DISK_1: restored backup piece 1
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:01
output file name=/TEST/u02/oradata/oratest/control01.ctl
output file name=/TEST/u02/app/oratest/fast_recovery_area/oratest/control02.ctl
Finished restore at 23-JUN-16

database mounted

contents of Memory Script:
{
set until scn 1861898;
set newname for datafile 1 to
"/TEST/u02/oradata/oratest/system01.dbf";
set newname for datafile 2 to
"/TEST/u02/oradata/oratest/example01.dbf";
set newname for datafile 3 to
"/TEST/u02/oradata/oratest/sysaux01.dbf";
set newname for datafile 4 to
"/TEST/u02/oradata/oratest/undotbs01.dbf";
set newname for datafile 6 to
"/TEST/u02/oradata/oratest/users01.dbf";
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

Starting restore at 23-JUN-16
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 /TEST/u02/oradata/oratest/system01.dbf
channel ORA_AUX_DISK_1: restoring datafile 00002 to /TEST/u02/oradata/oratest/example01.dbf
channel ORA_AUX_DISK_1: restoring datafile 00003 to /TEST/u02/oradata/oratest/sysaux01.dbf
channel ORA_AUX_DISK_1: restoring datafile 00004 to /TEST/u02/oradata/oratest/undotbs01.dbf
channel ORA_AUX_DISK_1: restoring datafile 00006 to /TEST/u02/oradata/oratest/users01.dbf
channel ORA_AUX_DISK_1: reading from backup piece /RMAN_Backup/Backup_ORCL_0er8mtp1_1_1_20160621
channel ORA_AUX_DISK_1: piece handle=/RMAN_Backup/Backup_ORCL_0er8mtp1_1_1_20160621 tag=TAG20160621T130742
channel ORA_AUX_DISK_1: restored backup piece 1
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:01:15
Finished restore at 23-JUN-16

contents of Memory Script:
{
switch clone datafile all;
}
executing Memory Script

datafile 1 switched to datafile copy
input datafile copy RECID=6 STAMP=915294018 file name=/TEST/u02/oradata/oratest/system01.dbf
datafile 2 switched to datafile copy
input datafile copy RECID=7 STAMP=915294018 file name=/TEST/u02/oradata/oratest/example01.dbf
datafile 3 switched to datafile copy
input datafile copy RECID=8 STAMP=915294018 file name=/TEST/u02/oradata/oratest/sysaux01.dbf
datafile 4 switched to datafile copy
input datafile copy RECID=9 STAMP=915294018 file name=/TEST/u02/oradata/oratest/undotbs01.dbf
datafile 6 switched to datafile copy
input datafile copy RECID=10 STAMP=915294018 file name=/TEST/u02/oradata/oratest/users01.dbf

contents of Memory Script:
{
set until scn 1861898;
recover
clone database
delete archivelog
;
}
executing Memory Script

executing command: SET until clause

Starting recover at 23-JUN-16
using channel ORA_AUX_DISK_1

starting media recovery

archived log for thread 1 with sequence 2 is already on disk as file /PRIM/u02/app/oraprim/fast_recovery_area/ORCL/archivelog/2016_06_21/o1_mf_1_2_cpkvryw9_.arc
archived log for thread 1 with sequence 3 is already on disk as file /PRIM/u02/app/oraprim/fast_recovery_area/ORCL/archivelog/2016_06_22/o1_mf_1_3_cpohgwkc_.arc
archived log for thread 1 with sequence 4 is already on disk as file /PRIM/u02/app/oraprim/fast_recovery_area/ORCL/archivelog/2016_06_23/o1_mf_1_4_cppyfxfj_.arc
archived log for thread 1 with sequence 5 is already on disk as file /PRIM/u02/app/oraprim/fast_recovery_area/ORCL/archivelog/2016_06_23/o1_mf_1_5_cppysc5w_.arc
archived log for thread 1 with sequence 6 is already on disk as file /PRIM/u02/app/oraprim/fast_recovery_area/ORCL/archivelog/2016_06_23/o1_mf_1_6_cppzbrsy_.arc
archived log for thread 1 with sequence 7 is already on disk as file /PRIM/u02/app/oraprim/fast_recovery_area/ORCL/archivelog/2016_06_23/o1_mf_1_7_cpq18qq1_.arc
archived log for thread 1 with sequence 8 is already on disk as file /PRIM/u02/app/oraprim/fast_recovery_area/ORCL/archivelog/2016_06_23/o1_mf_1_8_cpq29kfz_.arc
archived log for thread 1 with sequence 9 is already on disk as file /PRIM/u02/app/oraprim/fast_recovery_area/ORCL/archivelog/2016_06_23/o1_mf_1_9_cpq4n4hx_.arc
archived log for thread 1 with sequence 10 is already on disk as file /PRIM/u02/app/oraprim/fast_recovery_area/ORCL/archivelog/2016_06_23/o1_mf_1_10_cpq651h7_.arc
archived log for thread 1 with sequence 11 is already on disk as file /PRIM/u02/app/oraprim/fast_recovery_area/ORCL/archivelog/2016_06_23/o1_mf_1_11_cpq6qtfs_.arc
archived log for thread 1 with sequence 12 is already on disk as file /PRIM/u02/app/oraprim/fast_recovery_area/ORCL/archivelog/2016_06_23/o1_mf_1_12_cpqhk1rg_.arc
archived log file name=/PRIM/u02/app/oraprim/fast_recovery_area/ORCL/archivelog/2016_06_21/o1_mf_1_2_cpkvryw9_.arc thread=1 sequence=2
archived log file name=/PRIM/u02/app/oraprim/fast_recovery_area/ORCL/archivelog/2016_06_22/o1_mf_1_3_cpohgwkc_.arc thread=1 sequence=3
archived log file name=/PRIM/u02/app/oraprim/fast_recovery_area/ORCL/archivelog/2016_06_23/o1_mf_1_4_cppyfxfj_.arc thread=1 sequence=4
archived log file name=/PRIM/u02/app/oraprim/fast_recovery_area/ORCL/archivelog/2016_06_23/o1_mf_1_5_cppysc5w_.arc thread=1 sequence=5
archived log file name=/PRIM/u02/app/oraprim/fast_recovery_area/ORCL/archivelog/2016_06_23/o1_mf_1_6_cppzbrsy_.arc thread=1 sequence=6
archived log file name=/PRIM/u02/app/oraprim/fast_recovery_area/ORCL/archivelog/2016_06_23/o1_mf_1_7_cpq18qq1_.arc thread=1 sequence=7
archived log file name=/PRIM/u02/app/oraprim/fast_recovery_area/ORCL/archivelog/2016_06_23/o1_mf_1_8_cpq29kfz_.arc thread=1 sequence=8
archived log file name=/PRIM/u02/app/oraprim/fast_recovery_area/ORCL/archivelog/2016_06_23/o1_mf_1_9_cpq4n4hx_.arc thread=1 sequence=9
archived log file name=/PRIM/u02/app/oraprim/fast_recovery_area/ORCL/archivelog/2016_06_23/o1_mf_1_10_cpq651h7_.arc thread=1 sequence=10
archived log file name=/PRIM/u02/app/oraprim/fast_recovery_area/ORCL/archivelog/2016_06_23/o1_mf_1_11_cpq6qtfs_.arc thread=1 sequence=11
archived log file name=/PRIM/u02/app/oraprim/fast_recovery_area/ORCL/archivelog/2016_06_23/o1_mf_1_12_cpqhk1rg_.arc thread=1 sequence=12
media recovery complete, elapsed time: 00:00:23
Finished recover at 23-JUN-16
Oracle instance started

Total System Global Area 1286066176 bytes

Fixed Size 2287960 bytes
Variable Size 469763752 bytes
Database Buffers 805306368 bytes
Redo Buffers 8708096 bytes

contents of Memory Script:
{
sql clone "alter system set db_name =
''ORATEST'' comment=
''Reset to original value by RMAN'' scope=spfile";
sql clone "alter system reset db_unique_name scope=spfile";
}
executing Memory Script

sql statement: alter system set db_name = ''ORATEST'' comment= ''Reset to original value by RMAN'' scope=spfile

sql statement: alter system reset db_unique_name scope=spfile
Oracle instance started

Total System Global Area 1286066176 bytes

Fixed Size 2287960 bytes
Variable Size 469763752 bytes
Database Buffers 805306368 bytes
Redo Buffers 8708096 bytes
sql statement: CREATE CONTROLFILE REUSE SET DATABASE "ORATEST" RESETLOGS ARCHIVELOG
MAXLOGFILES 16
MAXLOGMEMBERS 3
MAXDATAFILES 100
MAXINSTANCES 8
MAXLOGHISTORY 292
LOGFILE
GROUP 1 '/TEST/u02/oradata/oratest/redo01.log' SIZE 50 M ,
GROUP 2 '/TEST/u02/oradata/oratest/redo02.log' SIZE 50 M ,
GROUP 3 '/TEST/u02/oradata/oratest/redo03.log' SIZE 50 M
DATAFILE
'/TEST/u02/oradata/oratest/system01.dbf'
CHARACTER SET WE8MSWIN1252


contents of Memory Script:
{
set newname for tempfile 1 to
"/TEST/u02/oradata/orcl/temp01.dbf";
switch clone tempfile all;
catalog clone datafilecopy "/TEST/u02/oradata/oratest/example01.dbf",
"/TEST/u02/oradata/oratest/sysaux01.dbf",
"/TEST/u02/oradata/oratest/undotbs01.dbf",
"/TEST/u02/oradata/oratest/users01.dbf";
switch clone datafile all;
}
executing Memory Script

executing command: SET NEWNAME

renamed tempfile 1 to /TEST/u02/oradata/orcl/temp01.dbf in control file

cataloged datafile copy
datafile copy file name=/TEST/u02/oradata/oratest/example01.dbf RECID=1 STAMP=915294112
cataloged datafile copy
datafile copy file name=/TEST/u02/oradata/oratest/sysaux01.dbf RECID=2 STAMP=915294112
cataloged datafile copy
datafile copy file name=/TEST/u02/oradata/oratest/undotbs01.dbf RECID=3 STAMP=915294112
cataloged datafile copy
datafile copy file name=/TEST/u02/oradata/oratest/users01.dbf RECID=4 STAMP=915294112

datafile 2 switched to datafile copy
input datafile copy RECID=1 STAMP=915294112 file name=/TEST/u02/oradata/oratest/example01.dbf
datafile 3 switched to datafile copy
input datafile copy RECID=2 STAMP=915294112 file name=/TEST/u02/oradata/oratest/sysaux01.dbf
datafile 4 switched to datafile copy
input datafile copy RECID=3 STAMP=915294112 file name=/TEST/u02/oradata/oratest/undotbs01.dbf
datafile 6 switched to datafile copy
input datafile copy RECID=4 STAMP=915294112 file name=/TEST/u02/oradata/oratest/users01.dbf

contents of Memory Script:
{
Alter clone database open resetlogs;
}
executing Memory Script

database opened
Cannot remove created server parameter file
Finished Duplicate Db at 23-JUN-16

RMAN> exit


Recovery Manager complete.

If you face error RMAN-05001, refer the NOTE given below pfile mentioned under the section Establishing Connection.

[oraprim@localhost ~]$ ps -ef | grep pmon
oracat 2833 1 0 10:27 ? 00:00:03 ora_pmon_oracat
oraprim 5073 1 0 11:37 ? 00:00:03 ora_pmon_orcl
oraprim 12224 1 0 16:21 ? 00:00:00 ora_pmon_oratest
oraprim 12593 2587 0 16:30 pts/0 00:00:00 grep pmon

[oraprim@localhost ~]$ sqlplus '/as sysdba'

SQL*Plus: Release 12.1.0.1.0 Production on Thu Jun 23 16:31:13 2016

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


Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

SQL> set pagesize 999
SQL> select username from dba_users order by created;

USERNAME
--------------------------------------------------------------------------------
SYS
SYSTEM
AUDSYS
SYSKM
SYSBACKUP
SYSDG
OUTLN
GSMUSER
GSMADMIN_INTERNAL
DIP
XS$NULL
ORACLE_OCM
DBSNMP
APPQOSSYS
ANONYMOUS
XDB
GSMCATUSER
WMSYS
OJVMSYS
CTXSYS
ORDSYS
ORDPLUGINS
MDSYS
ORDDATA
SI_INFORMTN_SCHEMA
OLAPSYS
MDDATA
SPATIAL_WFS_ADMIN_USR
SPATIAL_CSW_ADMIN_USR
LBACSYS
APEX_040200
APEX_PUBLIC_USER
FLOWS_FILES
DVSYS
DVF
HR
IX
SH
PM
BI
OE
SCOTT

42 rows selected.

SQL> exit

[oraprim@localhost ~]$ export ORACLE_SID=orcl
[oraprim@localhost ~]$ sqlplus '/as sysdba'

SQL*Plus: Release 12.1.0.1.0 Production on Thu Jun 23 16:32:45 2016

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


Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

SQL> set pagesize 999
SQL> select username from dba_users order by created;

USERNAME
--------------------------------------------------------------------------------
SYS
SYSTEM
AUDSYS
SYSKM
SYSBACKUP
SYSDG
OUTLN
GSMUSER
GSMADMIN_INTERNAL
DIP
XS$NULL
ORACLE_OCM
DBSNMP
APPQOSSYS
ANONYMOUS
XDB
GSMCATUSER
WMSYS
OJVMSYS
CTXSYS
ORDSYS
ORDPLUGINS
MDSYS
ORDDATA
SI_INFORMTN_SCHEMA
OLAPSYS
MDDATA
SPATIAL_WFS_ADMIN_USR
SPATIAL_CSW_ADMIN_USR
LBACSYS
APEX_040200
APEX_PUBLIC_USER
FLOWS_FILES
DVSYS
DVF
HR
IX
SH
PM
BI
OE
SCOTT

42 rows selected.

SQL>
SQL> select dbid,name,open_mode,created,log_mode from v$database;

DBID NAME OPEN_MODE CREATED LOG_MODE
-------- --------- -------------------- ------------- -----------------
1434527233 ORCL READ WRITE 17-MAR-16 ARCHIVELOG

SQL> exit

[oraprim@localhost oratest]$ export ORACLE_SID=oratest
[oraprim@localhost oratest]$ sqlplus '/as sysdba'

SQL*Plus: Release 12.1.0.1.0 Production on Thu Jun 23 17:27:41 2016

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


Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

SQL> select dbid,name,open_mode,created,log_mode from v$database;

DBID NAME OPEN_MODE CREATED LOG_MODE
---------- --------- ------------------- -------------- -----------------
3450673184 ORATEST READ WRITE 23-JUN-16 ARCHIVELOG

Now you can provide the proper ownership and permissions to the Clone DB mount point(/TEST) and continue without need to export SID every time.

OK. That’s All !!!

Reference Documents and blogs


Reference MOS Documents for error ORA-01031
UNIX: Checklist for Resolving Connect AS SYSDBA Issues (Doc ID 69642.1)
SYSDBA and SYSOPER Privileges in Oracle (Doc ID 50507.1)

In my case section 3 of Doc 50507.1 solved the issue ORA-01031

To effect any changes to the groups and to be sure you are using the groups defined in this file relink the Oracle executable. Be sure to shutdown all databases before relinking:

cd $ORACLE_HOME/rdbms/lib
mv config.o config.o.orig
make -f ins_rdbms.mk ioracle

(Note config.o will be re-created by make because of dependencies automatically)
-

No comments:

Post a Comment

Subscribe to: Post Comments (Atom)

IAS Cache initialization failed

 Today I faced an Issue in R12.2 instance. The solution I followed to overcome the issue is very simple, but they are more than one solution...