Post date: 01-Feb-2011 12:29:15
Tips
1. Make initialization parameter file from current database
2. Modify instance parameters concerning cluster and database name
3. Modify listener.ora and tnsnames.ora and make password file to connect to instance by NET80 connect string
4. Startup new instance to check if all parameters are correct and for further RMAN operations
5. Dublicate database files by RMAN from nearest backup
1. Make initialization parameter file from current database
it is basic task. It could do even newbie DBA's. For instance lets take clone database name is TEST1
and I use 2 node RAC 10g .
SQL> create pfile='?/dbs/inittest1.ora' from spfile;
File created.
SQL>
2. Modify instance parameters concerning cluster and database name
Now I'll modify instance parameters .
first of all I'll comment all parameters concerning cluster database.
Then I modify parameters concerning database name and directories for BDUMP,CDUMP,UDUMP and controlfile name
Let's see difference. This parameters I've changed.
Before I modified
*.cluster_database_instances=2
*.cluster_database=TRUE
grid1.instance_number=1
grid2.instance_number=2
grid2.thread=2
grid1.thread=1
grid2.undo_tablespace='UNDOTBS2'
grid1.undo_tablespace='UNDOTBS1'
*.user_dump_dest='/home/oracle/admin/grid/udump'
*.background_dump_dest='/home/oracle/admin/grid/bdump'
*.core_dump_dest='/home/oracle/admin/grid/cdump'
undo_tablespace='UNDOTBS1'
*.db_name='grid'
Then I've modified
#*.cluster_database_instances=2
#*.cluster_database=TRUE
#grid1.instance_number=1
#grid2.instance_number=2
#grid2.thread=2
#grid1.thread=1
#grid2.undo_tablespace='UNDOTBS2'
#grid1.undo_tablespace='UNDOTBS1'
*.user_dump_dest='/home/oracle/admin/test1/udump'
*.background_dump_dest='/home/oracle/admin/test1/bdump'
*.core_dump_dest='/home/oracle/admin/test1/cdump'
*.control_files='/var/oracle/oradata/test1/control01.ctl','/var/oracle/oradata/test1/control02.ctl'
undo_tablespace='UNDOTBS1'
*.db_name='test1'
3. Modify listener.ora and tnsnames.ora and make password file to connect to instance by NET80 connect string
Then I'll add next string to listener.ora and tnsnames.ora
It is important then you using RMAN or to connect to instance which is not started
In fact it was described in Oracle Administrator's Guide at Using Operating System (OS) Authentication topic
Listener.ora
(SID_DESC =
(GLOBAL_DBNAME = test1.fors.ru)
(ORACLE_HOME = /home/oracle/product/10.1.0/db_1)
(SID_NAME = test1)
)
Tnsnames.ora
test1 =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = hydra1vip.fors.ru)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = test1.fors.ru)
(INSTANCE_NAME =test1)
)
)
Next thing we need to create password file
[oracle@hydra1 oracle]$ orapwd file=/home/oracle/product/10.1.0/db_1/dbs/orapwtest1 password=manager
4. Startup new instance to check if all parameters are correct and for further RMAN operations
We will check our new connection that we will be able to connect using OS autontethication,
and also we will check initialization parameters I modified
[oracle@hydra1 oracle]$ sqlplus sys/manager@test1 as sysdba
SQL*Plus: Release 10.1.0.4.0 - Production on Fri Jun 10 14:11:34 2005
Copyright (c) 1982, 2005, Oracle. All rights reserved.
Connected to an idle instance.
SQL> startup nomount
ORACLE instance started.
Total System Global Area 176160768 bytes
Fixed Size 778332 bytes
Variable Size 149954468 bytes
Database Buffers 25165824 bytes
Redo Buffers 262144 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
SQL>
5. Dublicate database files by RMAN from nearest backup
The last step
At this point we'll be able to connect to instance, and have modified initializing parameters
Lets make directories for our future databases . Theay are for datafiles,logfiles and controlfiles.
Lets say it will be /var/oracle/oradata/test1/
oracle@hydra1 oracle]$mkdir -p /var/oracle/oradata/test1
oracle@hydra1 oracle]$mkdir -p /home/oracle/admin/test1/bdump
oracle@hydra1 oracle]$mkdir -p /home/oracle/admin/test1/udump
oracle@hydra1 oracle]$mkdir -p /home/oracle/admin/test1/cdump
The next thing will have to do is toc know there datafiles in current RAC database placed
This is essential enough becouse we will use it in when construe our RMAN script for duplicate database;
As we use RMAN duplicate command we need to know the old datafile name and set up newname
for RMAN which will copy datafiles into new destination .
So we need to know old datafiles names.
SQL> select name from v$datafile;
NAME
--------------------------------------------------------------------------------
+DBAREA/grid/datafile/system.256.559670091
+DBAREA/grid/datafile/undotbs1.257.559670205
+DBAREA/grid/datafile/sysaux.258.559670311
+DBAREA/grid/datafile/undotbs2.259.559670355
+DBAREA/grid/datafile/users.260.559670431
+DBAREA/grid/datafile/users2.261.559670505
+DBAREA/grid/datafile/users3.262.559670531
7 rows selected.
SQL>
Here, how RMAN duplicate command works?
Well it just use the database from backup, and restore this backup to destination we have indicated.
Which backup datafiles RMAN will use?
The datafiles have been made before SCN we said to RMAN .
So we need to know the SCN to point it out to RMAN.
Well it very simple enough. I just select current SCN from RAC database,
and switch redo logs a couple of time to make sure this SCN will get into archived logs
RMAN will use for duplicate command.
So...
SQL> select current_scn from v$database;
CURRENT_SCN
-----------
2081811
SQL> alter system switch logfile;
System altered.
SQL> /
System altered.
So, we now can recover database from last backup and recover them using archive log files until SCN=2081811
From this point we'll be able to construit RMAN duplicate command
duplicate target database to 'test1' db_file_name_convert=('+DBAREA/grid/datafile/','/var/oracle/oradata/test1/')
logfile
group 1('/var/oracle/oradata/test1/redo01.log') size 100M,
group 2 ('/var/oracle/oradata/test1/redo02.log') size 100M,
group 3 ('/var/oracle/oradata/test1/redo03.log') size 100M,
group 4 ('/var/oracle/oradata/test1/redo04.log') size 100M,
group 5 ('/var/oracle/oradata/test1/redo05.log') size 100M
until scn=2081811 ;
RMAN output.
[oracle@hydra1 oracle]$ rman target=/ auxiliary=sys@test1
Recovery Manager: Release 10.1.0.4.0 - Production
Copyright (c) 1995, 2004, Oracle. All rights reserved.
connected to target database: GRID (DBID=1857477647)
auxiliary database Password:
connected to auxiliary database (not started)
RMAN> startup auxiliary nomount;
Oracle instance started
Total System Global Area 176160768 bytes
Fixed Size 778332 bytes
Variable Size 149954468 bytes
Database Buffers 25165824 bytes
Redo Buffers 262144 bytes
RMAN> duplicate target database to 'test1' db_file_name_convert=('+DBAREA/grid/datafile/','/var/oracle/oradata/test1/')
2> logfile
3> group 1('/var/oracle/oradata/test1/redo01.log') size 100M,
4> group 2 ('/var/oracle/oradata/test1/redo02.log') size 100M,
5> group 3 ('/var/oracle/oradata/test1/redo03.log') size 100M,
group 4 ('/var/oracle/oradata/test1/redo04.log') size 100M,
6> 7> group 5 ('/var/oracle/oradata/test1/redo05.log') size 100M
8> until scn=2081811 ;
Starting Duplicate Db at 10-JUN-05
using target database controlfile instead of recovery catalog
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: sid=267 devtype=DISK
contents of Memory Script:
{
set until scn 2081811;
set newname for datafile 1 to
"/var/oracle/oradata/test1/system.256.559670091";
set newname for datafile 2 to
"/var/oracle/oradata/test1/undotbs1.257.559670205";
set newname for datafile 3 to
"/var/oracle/oradata/test1/sysaux.258.559670311";
set newname for datafile 4 to
"/var/oracle/oradata/test1/undotbs2.259.559670355";
set newname for datafile 5 to
"/var/oracle/oradata/test1/users.260.559670431";
set newname for datafile 6 to
"/var/oracle/oradata/test1/users2.261.559670505";
set newname for datafile 7 to
"/var/oracle/oradata/test1/users3.262.559670531";
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
executing command: SET NEWNAME
executing command: SET NEWNAME
Starting restore at 10-JUN-05
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 /var/oracle/oradata/test1/system.256.559670091
restoring datafile 00002 to /var/oracle/oradata/test1/undotbs1.257.559670205
restoring datafile 00003 to /var/oracle/oradata/test1/sysaux.258.559670311
restoring datafile 00004 to /var/oracle/oradata/test1/undotbs2.259.559670355
restoring datafile 00005 to /var/oracle/oradata/test1/users.260.559670431
restoring datafile 00006 to /var/oracle/oradata/test1/users2.261.559670505
restoring datafile 00007 to /var/oracle/oradata/test1/users3.262.559670531
channel ORA_AUX_DISK_1: restored backup piece 1
piece handle=+FLASHAREA/grid/backupset/2005_06_10/nnndf0_tag20050610t153027_0.266.560619029 tag=TAG20050610T153027
channel ORA_AUX_DISK_1: restore complete
Finished restore at 10-JUN-05
sql statement: CREATE CONTROLFILE REUSE SET DATABASE "test1" RESETLOGS ARCHIVELOG
MAXLOGFILES 192
MAXLOGMEMBERS 3
MAXDATAFILES 1024
MAXINSTANCES 32
MAXLOGHISTORY 908
LOGFILE
GROUP 1 ( '/var/oracle/oradata/test1/redo01.log' ) SIZE 100 M ,
GROUP 2 ( '/var/oracle/oradata/test1/redo02.log' ) SIZE 100 M ,
GROUP 3 ( '/var/oracle/oradata/test1/redo03.log' ) SIZE 100 M ,
GROUP 4 ( '/var/oracle/oradata/test1/redo04.log' ) SIZE 100 M ,
GROUP 5 ( '/var/oracle/oradata/test1/redo05.log' ) SIZE 100 M
DATAFILE
'/var/oracle/oradata/test1/system.256.559670091'
CHARACTER SET AL32UTF8
contents of Memory Script:
{
switch clone datafile all;
}
executing Memory Script
datafile 2 switched to datafile copy
input datafilecopy recid=1 stamp=560648218 filename=/var/oracle/oradata/test1/undotbs1.257.559670205
datafile 3 switched to datafile copy
input datafilecopy recid=2 stamp=560648218 filename=/var/oracle/oradata/test1/sysaux.258.559670311
datafile 4 switched to datafile copy
input datafilecopy recid=3 stamp=560648218 filename=/var/oracle/oradata/test1/undotbs2.259.559670355
datafile 5 switched to datafile copy
input datafilecopy recid=4 stamp=560648218 filename=/var/oracle/oradata/test1/users.260.559670431
datafile 6 switched to datafile copy
input datafilecopy recid=5 stamp=560648218 filename=/var/oracle/oradata/test1/users2.261.559670505
datafile 7 switched to datafile copy
input datafilecopy recid=6 stamp=560648218 filename=/var/oracle/oradata/test1/users3.262.559670531
contents of Memory Script:
{
set until scn 2081811;
recover
clone database
delete archivelog
;
}
executing Memory Script
executing command: SET until clause
Starting recover at 10-JUN-05
using channel ORA_AUX_DISK_1
starting media recovery
archive log thread 1 sequence 16 is already on disk as file +FLASHAREA/grid/archivelog/2005_06_10/thread_1_seq_16.279.560620315
archive log thread 2 sequence 23 is already on disk as file +FLASHAREA/grid/archivelog/2005_06_10/thread_2_seq_23.289.560621221
archive log filename=+FLASHAREA/grid/archivelog/2005_06_10/thread_1_seq_16.279.560620315 thread=1 sequence=16
archive log filename=+FLASHAREA/grid/archivelog/2005_06_10/thread_2_seq_23.289.560621221 thread=2 sequence=0
media recovery complete
Finished recover at 10-JUN-05
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 176160768 bytes
Fixed Size 778332 bytes
Variable Size 149954468 bytes
Database Buffers 25165824 bytes
Redo Buffers 262144 bytes
sql statement: CREATE CONTROLFILE REUSE SET DATABASE "test1" RESETLOGS ARCHIVELOG
MAXLOGFILES 192
MAXLOGMEMBERS 3
MAXDATAFILES 1024
MAXINSTANCES 32
MAXLOGHISTORY 908
LOGFILE
GROUP 1 ( '/var/oracle/oradata/test1/redo01.log' ) SIZE 100 M ,
GROUP 2 ( '/var/oracle/oradata/test1/redo02.log' ) SIZE 100 M ,
GROUP 3 ( '/var/oracle/oradata/test1/redo03.log' ) SIZE 100 M ,
GROUP 4 ( '/var/oracle/oradata/test1/redo04.log' ) SIZE 100 M ,
GROUP 5 ( '/var/oracle/oradata/test1/redo05.log' ) SIZE 100 M
DATAFILE
'/var/oracle/oradata/test1/system.256.559670091'
CHARACTER SET AL32UTF8
contents of Memory Script:
{
catalog clone datafilecopy "/var/oracle/oradata/test1/undotbs1.257.559670205";
catalog clone datafilecopy "/var/oracle/oradata/test1/sysaux.258.559670311";
catalog clone datafilecopy "/var/oracle/oradata/test1/undotbs2.259.559670355";
catalog clone datafilecopy "/var/oracle/oradata/test1/users.260.559670431";
catalog clone datafilecopy "/var/oracle/oradata/test1/users2.261.559670505";
catalog clone datafilecopy "/var/oracle/oradata/test1/users3.262.559670531";
switch clone datafile all;
}
executing Memory Script
cataloged datafile copy
datafile copy filename=/var/oracle/oradata/test1/undotbs1.257.559670205 recid=1 stamp=560648248
cataloged datafile copy
datafile copy filename=/var/oracle/oradata/test1/sysaux.258.559670311 recid=2 stamp=560648287
cataloged datafile copy
datafile copy filename=/var/oracle/oradata/test1/undotbs2.259.559670355 recid=3 stamp=560648287
cataloged datafile copy
datafile copy filename=/var/oracle/oradata/test1/users.260.559670431 recid=4 stamp=560648289
cataloged datafile copy
datafile copy filename=/var/oracle/oradata/test1/users2.261.559670505 recid=5 stamp=560648290
cataloged datafile copy
datafile copy filename=/var/oracle/oradata/test1/users3.262.559670531 recid=6 stamp=560648291
datafile 2 switched to datafile copy
input datafilecopy recid=1 stamp=560648248 filename=/var/oracle/oradata/test1/undotbs1.257.559670205
datafile 3 switched to datafile copy
input datafilecopy recid=2 stamp=560648287 filename=/var/oracle/oradata/test1/sysaux.258.559670311
datafile 4 switched to datafile copy
input datafilecopy recid=3 stamp=560648287 filename=/var/oracle/oradata/test1/undotbs2.259.559670355
datafile 5 switched to datafile copy
input datafilecopy recid=4 stamp=560648289 filename=/var/oracle/oradata/test1/users.260.559670431
datafile 6 switched to datafile copy
input datafilecopy recid=5 stamp=560648290 filename=/var/oracle/oradata/test1/users2.261.559670505
datafile 7 switched to datafile copy
input datafilecopy recid=6 stamp=560648291 filename=/var/oracle/oradata/test1/users3.262.559670531
contents of Memory Script:
{
Alter clone database open resetlogs;
}
executing Memory Script
database opened
Finished Duplicate Db at 10-JUN-05
RMAN>