Database Refresh using RMAN

 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:

ORA-01552: cannot use system rollback segment for non-system tablespace 'TEMP'

 ORA-01552: cannot use system rollback segment for non-system tablespace "string" Cause: Used the system rollback segment for non...