PROD-SID:- IDEA
TEST-SID:-AIRTEL
TEST database (AIRTEL) Refresh from Production(IDEA) Step by Steps
we are refreshing the AIRTEL
database from a backup taken of the IDEA database.
The assumption here is that the required
RMAN Production backup has already been either copied from the Production
database via scp or has been restored from tape or has been placed in an NFS
shared location which is accessible from both machines.
On the target machine the backups have been
restored in the location u02/backup/AIRTEL
Step:1
- Shutdown the AIRTEL database
if it is already running
[oracle@AIRTEL
AIRTEL]$ ps -ef |grep pmon
oracle 12701 29275 0 15:36:00 pts/3 0:00 grep pmon
oracle 7377
2235 0 May 19 ? 84:59 ora_pmon_AIRTEL
[oracle@AIRTEL
AIRTEL]$ echo $ORACLE_SID
AIRTEL
[oracle@AIRTEL
AIRTEL]$ sqlplus sys as sysdba
SQL*Plus:
Release 10.2.0.4.0 - IDEAuction on Thu Jun 17 15:36:22 2010
Copyright
(c) 1982, 2007, Oracle. All Rights
Reserved.
Enter
password:
Connected
to:
Oracle
Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit IDEAuction
With
the Partitioning, OLAP, Data Mining and Real Application AIRTELing options
SQL>
shutdown immediate
Database
closed.
Database
dismounted.
ORACLE
instance shut down.
Step:2
- Delete the database
data files, redo log files and control files
Note:
**Ensure
we are connected to the right server and are in the appropriate directory
location**
[oracle@AIRTEL
AIRTEL]$ hostname
AIRTEL
[oracle@AIRTEL
AIRTEL]$ pwd
/u03/oradata/AIRTEL
[oracle@AIRTEL
AIRTEL]$ rm *.dbf
[oracle@AIRTEL
AIRTEL]$ cd /u04/oradata/AIRTEL
[oracle@AIRTEL
AIRTEL]$ ls
control2.ctl redo01a.log
redo02a.log redo03a.log
[oracle@AIRTEL
AIRTEL]$ rm *.ctl
[oracle@AIRTEL
AIRTEL]$ rm *.log
[oracle@AIRTEL
AIRTEL]$ cd /u05/oradata/AIRTEL
[oracle@AIRTEL
AIRTEL]$ ls
control3.ctl redo01b.log
redo02b.log redo03b.log
Step:3
- Copy the current
init.ora parameter file of the AIRTEL database and create a parameter file with the name
of the source Production database (IDEA)
[oracle@AIRTEL
~]$ cd $ORACLE_HOME/dbs
[oracle@AIRTEL
dbs]$ pwd
/u01/app/oracle/IDEAuct/10.2.0/db_1/dbs
[oracle@AIRTEL
dbs]$ cp initAIRTEL.ora initIDEA.ora
Step:4
- Make the following
changes to the initIDEA.ora
*.db_name='IDEA'
Step:5
- Set the environment
to reflect the source Production database and start the instance in
NOMOUNT mode
[oracle@AIRTEL
dbs]$ export ORACLE_SID=IDEA
[oracle@AIRTEL
dbs]$ sqlplus sys as sysdba
SQL*Plus:
Release 10.2.0.4.0 - IDEAuction on Mon Jun 21 12:58:32 2010
Copyright
(c) 1982, 2007, Oracle. All Rights
Reserved.
Enter
password:
Connected
to an idle instance.
SQL>
startup nomount
pfile=$ORACLE_HOME/dbs/initIDEA.ora
ORACLE
instance started.
Total
System Global Area 3154116608 bytes
Fixed
Size 2043904 bytes
Variable
Size 637538304 bytes
Database
Buffers 2499805184 bytes
Redo
Buffers 14729216 bytes
SQL>
quit
Step:6
- Restore the control
file from the backup location
The control file backup exists in the
format
“c-<DBID>-<DATE>-<BACKUP SEQUENCE NUMBER>
Select the controlfile appropriate to the
period of time that we wish to restore the database from
[oracle@AIRTEL
AIRTEL]$ rman target /
Recovery
Manager: Release 10.2.0.4.0 - IDEAuction on Fri Jun 18 11:05:20 2010
Copyright
(c) 1982, 2007, Oracle. All rights
reserved.
connected
to target database: IDEA (not mounted)
RMAN>
restore controlfile from '/u02/backup/AIRTEL/c-4031762323-20100616-00';
Starting
restore at 18-JUN-10
using
target database control file instead of recovery catalog
allocated
channel: ORA_DISK_1
channel
ORA_DISK_1: sid=538 devtype=DISK
channel
ORA_DISK_1: restoring control file
channel
ORA_DISK_1: restore complete, elapsed time: 00:00:06
output
filename=/u03/oradata/AIRTEL/control1.ctl
output
filename=/u04/oradata/AIRTEL/control2.ctl
output
filename=/u05/oradata/AIRTEL/control3.ctl
Finished
restore at 18-JUN-10
Step:7
- Mount the database
RMAN>
alter database mount
2>
;
database
mounted
released
channel: ORA_DISK_1
Step:8
- Catalog the RMAN
backup sets which have been copied from the source Production database
RMAN>
catalog start with '/u02/backup/AIRTEL';
searching
for all files that match the pattern /u02/backup/AIRTEL
List
of Files Unknown to the Database
=====================================
File
Name: /u02/backup/AIRTEL/c-4031762323-20100616-00
File
Name: /u02/backup/AIRTEL/21lgatsk_1_1
File
Name: /u02/backup/AIRTEL/c-2263349373-20100419-00
...
...
Do
you really want to catalog the above files (enter YES or NO)? YES
cataloging
files...
cataloging
done
List
of Cataloged Files
=======================
File
Name: /u02/backup/AIRTEL/c-4031762323-20100616-00
File
Name: /u02/backup/AIRTEL/21lgatsk_1_1
File
Name: /u02/backup/AIRTEL/2flgdi89_1_1
...
...
List
of Files Which Where Not Cataloged
=======================================
File
Name: /u02/backup/AIRTEL/c-2263349373-20100419-00
RMAN-07518: Reason: Foreign database file
DBID: 2263349373 Database Name: AIRTEL
Note – ignore any errors reported for files
that are not cataloged
Step:9
- Determine the last archive-log
sequence included in the backup. We will be recovering the database until
this particular sequence number.
Look
for the string "List of archived logs"
RMAN
> list backup of archivelog all
List of Archived Logs in backup set 69
Thrd Seq
Low SCN Low Time Next SCN
Next Time
---- ------- ---------- --------- ----------
---------
1
79 7970987 16-JUN-10 7973402 16-JUN-10
In this case, the last archive-log backed
up belongs to sequence number 79. If we wish to recover the database until the
last archived log which has been backed up, we need to increment the last
sequence number by 1. So in this case it will be 79+1 or 80
Step:10
- Create the following
files in the location “/u02/backup/{DB_NAME}”
vi rman_head
RUN
{
#
allocate a channel to the tape device
ALLOCATE CHANNEL d1 DEVICE TYPE disk;
#
rename the datafiles and online redo logs
vi rman_tail
#
Do a SET UNTIL to prevent recovery of the online logs
SET UNTIL SEQUENCE 80;
#
restore the database and switch the datafile names
RESTORE DATABASE;
SWITCH DATAFILE ALL;
#
recover the database
RECOVER DATABASE;
}
vi generate_datafiles.sql
set
head off pages 0 feed off echo off verify off
set
lines 200
spool
rename_datafiles.lst
select
'SET NEWNAME FOR DATAFILE ' || FILE# || ' TO ''' || '/u03/oradata/&1/' ||
substr(name,instr(name,'/',-1)+1) || ''';' from v$datafile;
spool
off
exit;
vi generate_logfiles.sql
set head off
pages 0 feed off echo off
spool
rename_logfiles.lst
SELECT 'SQL
"ALTER DATABASE RENAME FILE '''''||
MEMBER ||'''''' ||chr(10)||'to ''''' || member || '''''" ;'
FROM V$LOGFILE;
exit
Step:11
- Generate data file
rename script
While in directory /u02/backup/{DB_NAME}, connect
as sys as sysdba via SQL*PLUS session and run the generate_datafiles.sql script.
The generate_datafiles.sql script accepts a
parameter which is the target database name.
SQL> @generate_datafiles AIRTEL
It will create a file rename_datafiles.lst .
The contents of this file will be like this:
SET
NEWNAME FOR DATAFILE 1 TO '/u03/oradata/AIRTEL/system01.dbf';
SET
NEWNAME FOR DATAFILE 2 TO '/u03/oradata/AIRTEL/undotbs01.dbf';
SET
NEWNAME FOR DATAFILE 3 TO '/u03/oradata/AIRTEL/sysaux01.dbf';
….
….
- Generate redo log
file rename script
While in directory /u02/backup/{DB_NAME},
connect as sys as sysdba via SQL*PLUS session
and run the generate_logfiles.sql script.
SQL> @generate_logfiles.sql
It will create a file called rename_logfiles.lst
Edit the rename_logfiles.lst file and change
values of IDEA to AIRTEL
SQL
"ALTER DATABASE RENAME FILE ''/u04/oradata/IDEA/redo01a.log'' to
''/u04/oradata/AIRTEL/redo01a.log''" ;
SQL
"ALTER DATABASE RENAME FILE ''/u05/oradata/IDEA/redo01b.log'' to
''/u05/oradata/AIRTEL/redo01b.log''" ;
SQL
"ALTER DATABASE RENAME FILE ''/u04/oradata/IDEA/redo02a.log'' to
''/u04/oradata/AIRTEL/redo02a.log''" ;
SQL
"ALTER DATABASE RENAME FILE ''/u05/oradata/IDEA/redo02b.log'' to
''/u05/oradata/AIRTEL/redo02b.log''" ;
SQL
"ALTER DATABASE RENAME FILE ''/u04/oradata/IDEA/redo03a.log'' to
''/u04/oradata/AIRTEL/redo03a.log''" ;
SQL
"ALTER DATABASE RENAME FILE ''/u05/oradata/IDEA/redo03b.log'' to
''/u05/oradata/AIRTEL/redo03b.log''" ;
Step:12
- Specify the archive
log sequence until which recovery will be performed
Edit the rman_tail file and change the line
with the words “>> SET UNTIL SEQUENCE 80” to include the appropriate
archive log sequence which was noted in an earlier step.
Step:13
- Prepare the RMAN
restore and recover database script
[oracle@AIRTEL
AIRTEL]$ cat rman_head
rename_datafiles.lst rename_logfiles.lst rman_tail > rman_recovery.rcv
Step:14
- Connect to the target
database via RMAN and execute the rman_recovery.rcv
script
[oracle@AIRTEL
dbs]$ rman target /
Recovery
Manager: Release 10.2.0.4.0 - Production on Mon Jun 21 13:04:04 2010
Copyright
(c) 1982, 2007, Oracle. All rights
reserved.
connected
to target database (not started)
RMAN>
@rman_recovery.rcv
Note:
At
this stage, we can continue to recover the database and keep it in sync with
the source Production database by manually applying the archive log files which
are copied from the Production server to the log archive destination of the AIRTEL
database on the target server.
We can do this via SQL*PLUS connected as
SYS by issuing the command
Step:15
RECOVER
DATABASE UNTIL CANCEL USING BACKUP CONTROLFILE
When there are no more archive log files to
apply, we enter CANCEL
- Open the database
with RESETLOGS
After the RMAN script has successfully run
and recovered the database until the last archive log sequence, we will now
open the database using the ALTER DATABASE OPEN RESETLOGS command executed
either via RMAN or from SQL*PLUS connected as SYS.
SQL>
alter database open resetlogs;
Database
altered.
Step:16
- Temporary Tablespace
Reconfiguration
After the restore, we will note that the
temporary tablespace files are still pointing to the source Production database
as these tempfiles have not been renamed when we renamed all the database data
files in an earlier step.
Obtain the name of the current tempfile -
SQL>
select name from v$tempfile;
NAME
--------------------------------------------------------------------------------
/u03/oradata/IDEA/temp01.dbf
Drop the tempfile-
SQL>
ALTER DATABASE TEMPFILE '/u03/oradata/IDEA/temp01.dbf' drop including
datafiles;
Database
altered.
Add a new tempfile for the refreshed
database in the appropriate location-
SQL>
ALTER TABLESPACE temp ADD TEMPFILE '/u03/oradata/AIRTEL/temp01.dbf' size 2G;
Tablespace
altered.
Step:17
- At this stage we will
change the passwords if required for the SYS and SYSTEM or any other database accounts.
Step:18
- Change the database
name using nid
We will now shutdown the database and then
mount it.
We will then run the nid utility to change the database name – we need to provide the
appropriate password for the user SYS and the new value we want for the
database name.
[oracle@AIRTEL
dbs]$ nid target=sys dbname=AIRTEL
DBNEWID:
Release 10.2.0.4.0 - IDEAuction on Fri Jun 18 13:55:14 2010
Copyright
(c) 1982, 2007, Oracle. All rights
reserved.
Password:
Connected
to database IDEA (DBID=4031762323)
Connected
to server version 10.2.0
Control
Files in database:
/u03/oradata/AIRTEL/control1.ctl
/u04/oradata/AIRTEL/control2.ctl
/u05/oradata/AIRTEL/control3.ctl
Change
database ID and database name IDEA to AIRTEL? (Y/[N]) => Y
Proceeding
with operation
Changing
database ID from 4031762323 to 2271553224
Changing
database name from IDEA to AIRTEL
Control File /u03/oradata/AIRTEL/control1.ctl
- modified
Control File /u04/oradata/AIRTEL/control2.ctl
- modified
Control File /u05/oradata/AIRTEL/control3.ctl
- modified
Datafile /u03/oradata/AIRTEL/system01.dbf -
dbid changed, wrote new name
Datafile /u03/oradata/AIRTEL/undotbs01.dbf
- dbid changed, wrote new name
Datafile /u03/oradata/AIRTEL/sysaux01.dbf -
dbid changed, wrote new name
Datafile /u03/oradata/AIRTEL/users01.dbf -
dbid changed, wrote new name
...
...
...
Datafile /u03/oradata/AIRTEL/COGNOSPAD_CLOB01.dbf
- dbid changed, wrote new name
Datafile /u03/oradata/AIRTEL/temp01.dbf -
dbid changed, wrote new name
Control File /u03/oradata/AIRTEL/control1.ctl
- dbid changed, wrote new name
Control File /u04/oradata/AIRTEL/control2.ctl
- dbid changed, wrote new name
Control File /u05/oradata/AIRTEL/control3.ctl
- dbid changed, wrote new name
Instance shut down
Database
name changed to AIRTEL.
Modify
parameter file and generate a new password file before restarting.
Database
ID for database AIRTEL changed to 2271553224.
All
previous backups and archived redo logs for this database are unusable.
Database
has been shutdown, open database with RESETLOGS option.
Succesfully
changed database name and ID.
DBNEWID
- Completed succesfully.
Step:19
At this stage the database has been shutdown
and now we need to mount it and issue the RESETLOGS command after the database
change.
Note:
We
will now set the environment to the target database
(until this stage, for example, ORACLE_SID had been set to the Production
database value)
[oracle@AIRTEL
backup] export ORACLE_SID=AIRTEL
[oracle@AIRTEL
backup]$ sqlplus sys as sysdba
SQL*Plus:
Release 10.2.0.4.0 - IDEAuction on Mon Jun 21 14:12:00 2010
Copyright
(c) 1982, 2007, Oracle. All Rights
Reserved.
Enter
password:
Connected
to an idle instance.
SQL>
startup mount
ORACLE
instance started.
Total
System Global Area 3154116608 bytes
Fixed
Size 2043904 bytes
Variable
Size 637538304 bytes
Database
Buffers 2499805184 bytes
Redo
Buffers 14729216 bytes
SQL>
alter database open resetlogs;
Database altered.
Step:20
- Post Database Refresh
Tasks
Create import and export directories
required for Data Pump
/u02/export/{DB_NAME} - export_dir
/u02/import/{DB_NAME} - import_dir
************************************END****************************************
No comments:
Post a Comment