BASIC STEPS (USING RMAN)
=========================
We need to use RMAN as we are dealing with an ASM target instance.
Non RAC is different from RAC just in redo log threads. and some instance parameters.
In RAC redo log threads have the same number as in RAC instances and in non RAC redo log threads is one at time.
Note : RMAN uses a terminology of target as being the source database and auxiliary as the duplicated database.
1) ASSUMPTIONS
==============
Existing two node RAC(on ASM) DATABASE name : BRIJ
NON -RAC database name : BRIJESH
we are moving from ASM to NON-ASM and RAC to NON-RAC
TARGET ORACLE_HOME > /u01/app/oracle/product/10.2.0/db_1 (same as source)
2) INSTALL ORACLE DATABASE SOFTWARE on target node (if not already present)
===========================================================================
You need to isntall oracle database software to the target node and you can use dbca for it.
3) Make initialization parameter file from current database
============================================================
SQL> create pfile='/u01/app/oracle/product/10.2.0/db_1/dbs/initbrijesh.ora' from spfile; File created.
Copy over this file to the target node
4) Modify instance parameters concerning Cluster, ASM and database name
=======================================================================
NOTE : Value of SGA_TARGET and SHARED_POOL was increased later on as RMAN duplicate command was giving below error :
----------------------------------------------------------------------
RMAN-00571: =========================================================== RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS =============== RMAN-00571: =========================================================== RMAN-03002: failure of Duplicate Db command at 11/21/2011 23:31:34 RMAN-03015: error occurred in stored script Memory Script RMAN-06136: ORACLE error from auxiliary database: ORA-04031: unable to allocate 560 bytes of shared memory ("shared pool","select col#,intcol#,toid,ver...","sga heap(1,1)","KQR M PO")
-----------------------------------------------------------------------
5) Create required directories
==============================
Create below directories which are also referenced in init file:
/u01/app/oracle/oradata/brijesh
/u01/app/oracle/arch
/u01/app/oracle/backup/brijesh
/u01/app/oracle/admin/brijesh/adump
/u01/app/oracle/admin/brijesh/bdump
/u01/app/oracle/admin/brijesh/cdump
/u01/app/oracle/admin/brijesh/udump
6) Modify listener.ora and tnsnames.ora
=======================================
Listener.ora
---------------
SID_LIST_LISTENER = (SID_LIST = (SID_DESC = (SID_NAME = PLSExtProc) (ORACLE_HOME = /u01/app/oracle/product/10.2.0) (PROGRAM = extproc) ) ) listener_brijesh = (DESCRIPTION_LIST = (DESCRIPTION = (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1)) (ADDRESS = (PROTOCOL = TCP)(HOST = rac3.mycorpdomain.com)(PORT = 1521)) ) ) LISTENER = (DESCRIPTION_LIST = (DESCRIPTION = (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1)) (ADDRESS = (PROTOCOL = TCP)(HOST = rac3.mycorpdomain.com)(PORT = 1521)) ) )
Tnsnames.ora
--------------
BRIJESH = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = rac3.mycorpdomain.com)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = brijesh) ) )
Below entry was added for RMAN catalog database :
------------------------------------------------
CATDB = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = rac3.mycorpdomain.com)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = catdb) ) )
7) CREATE A PASSWORD FILE
=========================
$ orapwd file=/home/oracle/product/10.1.0/db_1/dbs/orapwbrijesh password=<password>
$ls -ltr orapw*
-rw-r----- 1 oracle oinstall 1536 Nov 21 17:51 orapwbrijesh
8) Startup new instance to check if all parameters are correct
===============================================================
$ sqlplus sys/<password>@brijesh as sysdba SQL*Plus: Release 10.2.0.1.0 - Production on Mon Nov 21 23:57:48 2011 Copyright (c) 1982, 2005, Oracle. All rights reserved. Connected to an idle instance. SQL> startup nomount;
ORACLE instance started. Total System Global Area 331350016 bytes Fixed Size 2020576 bytes Variable Size 209718048 bytes Database Buffers 117440512 bytes Redo Buffers 2170880 bytes SQL> show parameter cluster_database NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ cluster_database boolean FALSE cluster_database_instances integer 1 SQL> show parameter thread NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ thread integer 0
9) BACKUP RAC DATABASE AND COPY THE BACKUP PIECES TO OUR TARGET SERVER
======================================================================
A) Create identical directory on both source instance and our target node
/u01/app/oracle/backup/brijesh ( better to work out with /u01/app/oracle/backup but here we are working with
/u01/app/oracle/backup/brijesh)
B) Connect to RMAN
$rman
RMAN> connect catalog rman/rman@catdb
connected to recovery catalog database
RMAN> connect target rman/rman@brij1
connected to target database: BRIJ (DBID=662038515)
C) Run below RMAN COMMAND to set location for backup pieces to be created
configure channel device type disk format '/u01/app/oracle/backup/brijesh/brij_bkp_%U';
D) START BACKUP
+++++++++++++++++++++++++++++++++++++++++++++
RMAN> BACKUP DATABASE PLUS ARCHIVELOG;
RMAN> SQL 'ALTER SYSTEM ARCHIVE LOG CURRENT'; # switches logs and archives all logs
++++++++++++++++++++++++++++++++++++++++++++++
E) CHECK BACKUP PIECES
$ pwd /u01/app/oracle/backup/brijesh
$ ls -ltr total 315052
-rw-r----- 1 oracle oinstall 196904448 Nov 21 23:18 brij_bkp_11ms8e4k_1_1 -rw-r----- 1 oracle oinstall 124174336 Nov 21 23:20 brij_bkp_12ms8e73_1_1 -rw-r----- 1 oracle oinstall 1130496 Nov 21 23:20 brij_bkp_13ms8e9f_1_1 -rw-r----- 1 oracle oinstall 53248 Nov 21 23:20 brij_bkp_14ms8e9p_1_1
F) COPY BACKUP PIECES TO TARGET SERVER
You can either copy it over all the backup pieces to the shared directory or use scp command to copy them.
You need to copy it over to directory >> /u01/app/oracle/backup/brijesh on target node
10) Duplicate database files by RMAN
====================================
Below command will be used
duplicate target database to 'brijesh' ;
Here "brijesh" is our Auxiliary database. ( IMPORTANT: CHECK IF "BRIJESH" IS IN NO-MOUNT STAGE OR NOT BEFORE RUNNING RMAN COMMAND)
RMAN LOG FILE >>>>
####################################################################################################################################
$ rman
connect catalog rman/rman@catdb
connect target rman/rman@brij1
connect auxiliary sys/<PASSWORD> connected to auxiliary database: BRIJESH (not mounted) RMAN> duplicate target database to 'brijesh'; Starting Duplicate Db at 21-NOV-11 allocated channel: ORA_AUX_DISK_1 channel ORA_AUX_DISK_1: sid=155 devtype=DISK contents of Memory Script: { set until scn 2747940; 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; restore check readonly 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 21-NOV-11 using channel ORA_AUX_DISK_1 channel ORA_AUX_DISK_1: starting datafile backupset restore channel ORA_AUX_DISK_1: specifying datafile(s) to restore from backup set restoring datafile 00001 to /u01/app/oracle/oradata/brijesh/BRIJESH/datafile/o1_mf_system_%u_.dbf restoring datafile 00002 to /u01/app/oracle/oradata/brijesh/BRIJESH/datafile/o1_mf_undotbs1_%u_.dbf restoring datafile 00003 to /u01/app/oracle/oradata/brijesh/BRIJESH/datafile/o1_mf_sysaux_%u_.dbf restoring datafile 00004 to /u01/app/oracle/oradata/brijesh/BRIJESH/datafile/o1_mf_users_%u_.dbf restoring datafile 00005 to /u01/app/oracle/oradata/brijesh/BRIJESH/datafile/o1_mf_undotbs2_%u_.dbf channel ORA_AUX_DISK_1: reading from backup piece /u01/app/oracle/backup/brijesh/brij_bkp_12ms8e73_1_1 channel ORA_AUX_DISK_1: restored backup piece 1 piece handle=/u01/app/oracle/backup/brijesh/brij_bkp_12ms8e73_1_1 tag=TAG20111121T231858 channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:01:05 Finished restore at 21-NOV-11 sql statement: CREATE CONTROLFILE REUSE SET DATABASE "BRIJESH" RESETLOGS ARCHIVELOG MAXLOGFILES 192 MAXLOGMEMBERS 3 MAXDATAFILES 1024 MAXINSTANCES 32 MAXLOGHISTORY 292 LOGFILE GROUP 1 SIZE 50 M , GROUP 2 SIZE 50 M DATAFILE '/u01/app/oracle/oradata/brijesh/BRIJESH/datafile/o1_mf_system_7do51otw_.dbf' CHARACTER SET WE8ISO8859P1 contents of Memory Script: { switch clone datafile all; } executing Memory Script released channel: ORA_AUX_DISK_1 datafile 2 switched to datafile copy input datafile copy recid=1 stamp=767835730 filename=/u01/app/oracle/oradata/brijesh/BRIJESH/datafile/o1_mf_undotbs1_7do51oyl_.dbf datafile 3 switched to datafile copy input datafile copy recid=2 stamp=767835730 filename=/u01/app/oracle/oradata/brijesh/BRIJESH/datafile/o1_mf_sysaux_7do51owq_.dbf datafile 4 switched to datafile copy input datafile copy recid=3 stamp=767835730 filename=/u01/app/oracle/oradata/brijesh/BRIJESH/datafile/o1_mf_users_7do51p6d_.dbf datafile 5 switched to datafile copy input datafile copy recid=4 stamp=767835730 filename=/u01/app/oracle/oradata/brijesh/BRIJESH/datafile/o1_mf_undotbs2_7do51p57_.dbf contents of Memory Script: { set until scn 2747940; recover clone database delete archivelog ; } executing Memory Script executing command: SET until clause Starting recover at 21-NOV-11 allocated channel: ORA_AUX_DISK_1 channel ORA_AUX_DISK_1: sid=157 devtype=DISK starting media recovery channel ORA_AUX_DISK_1: starting archive log restore to default destination channel ORA_AUX_DISK_1: restoring archive log archive log thread=1 sequence=42 channel ORA_AUX_DISK_1: restoring archive log archive log thread=2 sequence=28 channel ORA_AUX_DISK_1: reading from backup piece /u01/app/oracle/backup/brijesh/brij_bkp_14ms8e9p_1_1 channel ORA_AUX_DISK_1: restored backup piece 1 piece handle=/u01/app/oracle/backup/brijesh/brij_bkp_14ms8e9p_1_1 tag=TAG20111121T232025 channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:01 archive log filename=/u01/app/oracle/arch/1_42_761792516.dbf thread=1 sequence=42 archive log filename=/u01/app/oracle/arch/2_28_761792516.dbf thread=2 sequence=28 channel clone_default: deleting archive log(s) archive log filename=/u01/app/oracle/arch/1_42_761792516.dbf recid=1 stamp=767835731 channel clone_default: deleting archive log(s) archive log filename=/u01/app/oracle/arch/2_28_761792516.dbf recid=2 stamp=767835731 media recovery complete, elapsed time: 00:00:03 Finished recover at 21-NOV-11 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 331350016 bytes Fixed Size 2020576 bytes Variable Size 209718048 bytes Database Buffers 117440512 bytes Redo Buffers 2170880 bytes sql statement: CREATE CONTROLFILE REUSE SET DATABASE "BRIJESH" RESETLOGS ARCHIVELOG MAXLOGFILES 192 MAXLOGMEMBERS 3 MAXDATAFILES 1024 MAXINSTANCES 32 MAXLOGHISTORY 292 LOGFILE GROUP 1 SIZE 50 M , GROUP 2 SIZE 50 M DATAFILE '/u01/app/oracle/oradata/brijesh/BRIJESH/datafile/o1_mf_system_7do51otw_.dbf' CHARACTER SET WE8ISO8859P1 contents of Memory Script: { set newname for clone tempfile 1 to new; switch clone tempfile all; catalog clone datafilecopy "/u01/app/oracle/oradata/brijesh/BRIJESH/datafile/o1_mf_undotbs1_7do51oyl_.dbf"; catalog clone datafilecopy "/u01/app/oracle/oradata/brijesh/BRIJESH/datafile/o1_mf_sysaux_7do51owq_.dbf"; catalog clone datafilecopy "/u01/app/oracle/oradata/brijesh/BRIJESH/datafile/o1_mf_users_7do51p6d_.dbf"; catalog clone datafilecopy "/u01/app/oracle/oradata/brijesh/BRIJESH/datafile/o1_mf_undotbs2_7do51p57_.dbf"; switch clone datafile all; } executing Memory Script executing command: SET NEWNAME renamed temporary file 1 to /u01/app/oracle/oradata/brijesh/BRIJESH/datafile/o1_mf_temp_%u_.tmp in control file cataloged datafile copy datafile copy filename=/u01/app/oracle/oradata/brijesh/BRIJESH/datafile/o1_mf_undotbs1_7do51oyl_.dbf recid=1 stamp=767835741 cataloged datafile copy datafile copy filename=/u01/app/oracle/oradata/brijesh/BRIJESH/datafile/o1_mf_sysaux_7do51owq_.dbf recid=2 stamp=767835741 cataloged datafile copy datafile copy filename=/u01/app/oracle/oradata/brijesh/BRIJESH/datafile/o1_mf_users_7do51p6d_.dbf recid=3 stamp=767835742 cataloged datafile copy datafile copy filename=/u01/app/oracle/oradata/brijesh/BRIJESH/datafile/o1_mf_undotbs2_7do51p57_.dbf recid=4 stamp=767835742 datafile 2 switched to datafile copy input datafile copy recid=1 stamp=767835741 filename=/u01/app/oracle/oradata/brijesh/BRIJESH/datafile/o1_mf_undotbs1_7do51oyl_.dbf datafile 3 switched to datafile copy input datafile copy recid=2 stamp=767835741 filename=/u01/app/oracle/oradata/brijesh/BRIJESH/datafile/o1_mf_sysaux_7do51owq_.dbf datafile 4 switched to datafile copy input datafile copy recid=3 stamp=767835742 filename=/u01/app/oracle/oradata/brijesh/BRIJESH/datafile/o1_mf_users_7do51p6d_.dbf datafile 5 switched to datafile copy input datafile copy recid=4 stamp=767835742 filename=/u01/app/oracle/oradata/brijesh/BRIJESH/datafile/o1_mf_undotbs2_7do51p57_.dbf contents of Memory Script: { Alter clone database open resetlogs; } executing Memory Script database opened Finished Duplicate Db at 21-NOV-11 RMAN>
#######################################################################################################################
11) ADD CONTROL FILE ENTRIES IN THE initbrijesh.ora file
========================================================
Issue "show parameter control_files" in target BRIJESH database.
SQL> show parameter control_files
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------ -----------------------------------------------
control_files string /u01/app/oracle/oradata/brijes h/BRIJESH/controlfile/o1_mf_7d o541vk_.ctl, /u01/app/oracle/backup/brijesh/BRIJESH/controlf ile/o1_mf_7do542kz_.ctl
It shows the location of control files. Add the location to the init parameter file as shown below.
-----------
control_files='/u01/app/oracle/oradata/brijesh/BRIJESH/controlfile/o1_mf_7do541vk_.ctl','/u01/app/oracle/backup/brijesh/BRIJESH/controlfile/o1_mf_7do542kz_.ctl'
-----------
12) POST CLONE WORKS
====================
A) Job Queue Processes
Any jobs that were scheduled to run in production will start running in the cloned database if the job is not dropped or broken by
the concerned schema owners explicitly. To mitigate such an eventuality, you can turn off the job queue processes.
SQL> show parameter job
NAME TYPE VALUE ------------------------------------ ----------- ------------------- job_queue_processes integer 10
SQL> Alter system set parameter job_queue_processes = 0;
SQL> show parameter job
NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ job_queue_processes integer 0
B) Drop database links
Drop the database links that are not required and create the ones that are required to point to production. You should be able to automate these using scripts and there is a script of mine on RevealNet to do the same.
C) Change Passwords
Change any production schema passwords and the password of sys and system. Apart from the core oracle
database DBA related schema password, you could spool all the users and change their password to
something that they know will be the one they should expect whenever a clone/refresh of the dev and test
instance happens. Let’s say we reset the entire user’s password to ‘oracle’. They could then change this at
their convenience.
We can write a small script to spool the output and execute the output to achieve the same.
============================================================================================
Set linesize 200
Set pagesize 200
Set echo off
Spool change_passwords.sql
select 'alter user '||username||' identified by oracle;' from dba_users order by username
/
spool off
=============================================================================================
@change_passwords.sql
We could then change the core schema passwords of different modules to some standard password that the
leads of different modules are in agreement with. You could maintain these passwords in hidden password
file (this is something a lot of developers want to know on how to avoid the use of hard coded passwords in
their batch jobs) and call it using a shell script and change the core schema passwords in the database. A simple script will look like:
#!/bin/ksh
#--------------------------------------------------------------------------------------------------------
set -x
PASSFILE='/dba/etc/.dba_dba2.pwd'
cat $PASSFILE | awk -F"/" '{print $1}' | while read NAME
do
PASSWORD=`cat $PASSFILE | grep ^"$NAME/" | awk -F"/" '{print $2}`
echo 'alter user '$NAME 'identified by '$PASSWORD';' >> change_password.sql
done
#------------------------------------------------------------------------------------
#######################################################################################################################