Drop Down MenusCSS Drop Down MenuPure CSS Dropdown Menu

USING RMAN RESTORE AND RECOVERY PROCEDURE OF PRODUCTION DATABASE BACKUP ON TEST SERVERS

-



As part of disaster recovery exercise or to test the validity of a RMAN backup,  a full restore and recovery of databases can be performed on scratch or test servers utilising the production RMAN backups which have been restored from the tape backups on these test or scratch servers.

This note will illustrate the above procedure by detailing the steps required to restore the backup of a production database (prod1) on a test server linux01 .

The following assumptions are made in this note:

·        The RMAN backups have been restored from tape backups to the same backup location on the test server as the production server where the backup was originally taken
·        The identical directory structure as is present on production has been created on the test server. This will apply to not only the location of the database files (data, control files, redo log files), but also to the bdump,cdump, udump and adump locations.

·        Controlfile autobackup has been enabled. This is important.


Overview

·        Restore the spfile from the autobackup
·        Restore the controlfile from the autobackup
·        Restore the data files
·        Recover by applying archived redo log files
·        Open the database with resetlogs

Restore the SPFILE

[oracle@linux01 prod1]$ rman target /

Recovery Manager: Release 10.2.0.4.0 - Production on Thu Jun 24 13:23:58 2010

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

connected to target database (not started)

RMAN> set dbid=4266928631

executing command: SET DBID

RMAN> startup force nomount;

startup failed: ORA-01078: failure in processing system parameters
LRM-00109: could not open parameter file '/u01/app/oracle/product/10.2.0/dbs/initprod1.ora'

starting Oracle instance without parameter file for retrival of spfile
Oracle instance started

Total System Global Area     159383552 bytes

Fixed Size                     2039056 bytes
Variable Size                 67109616 bytes
Database Buffers              83886080 bytes
Redo Buffers                   6348800 bytes



RMAN> run
2>  {SET CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '/u02/backup/prod1/%F';
3> restore spfile from autobackup;
4> }

executing command: SET CONTROLFILE AUTOBACKUP FORMAT

Starting restore at 24-JUN-10
using channel ORA_DISK_1

channel ORA_DISK_1: looking for autobackup on day: 20100624
channel ORA_DISK_1: looking for autobackup on day: 20100623
channel ORA_DISK_1: looking for autobackup on day: 20100622
channel ORA_DISK_1: autobackup found: /u02/backup/prod1/c-4266928631-20100622-00
channel ORA_DISK_1: SPFILE restore from autobackup complete
Finished restore at 24-JUN-10


RMAN> shutdown immediate;

database closed
database dismounted
Oracle instance shut down


Restore the Control File

In this case, the instance is now being started with the restored spfile. We can create a pfile as well from this spfile is so required.


RMAN> startup nomount

connected to target database (not started)
Oracle instance started

Total System Global Area    3154116608 bytes

Fixed Size                     2043904 bytes
Variable Size               1879052288 bytes
Database Buffers            1258291200 bytes
Redo Buffers                  14729216 bytes


RMAN> set dbid=4266928631

executing command: SET DBID

RMAN> run
2> {SET CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '/u02/backup/prod1/%F';
3> restore controlfile from autobackup;
4> }

executing command: SET CONTROLFILE AUTOBACKUP FORMAT
using target database control file instead of recovery catalog

Starting restore at 24-JUN-10
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=541 devtype=DISK

recovery area destination: /u02/flash_recovery_area
database name (or database unique name) used for search: PROD1
channel ORA_DISK_1: no autobackups found in the recovery area
channel ORA_DISK_1: looking for autobackup on day: 20100624
channel ORA_DISK_1: looking for autobackup on day: 20100623
channel ORA_DISK_1: looking for autobackup on day: 20100622
channel ORA_DISK_1: autobackup found: /u02/backup/prod1/c-4266928631-20100622-00
channel ORA_DISK_1: control file restore from autobackup complete
output filename=/u03/oradata/prod1/control1.ctl
output filename=/u04/oradata/prod1/control2.ctl
output filename=/u05/oradata/prod1/control3.ctl
Finished restore at 24-JUN-10


RMAN> alter database mount;

database mounted
released channel: ORA_DISK_1



Restore the database




RMAN> restore database;

Starting restore at 24-JUN-10
Starting implicit crosscheck backup at 24-JUN-10
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=541 devtype=DISK
allocated channel: ORA_DISK_2
channel ORA_DISK_2: sid=540 devtype=DISK
allocated channel: ORA_DISK_3
channel ORA_DISK_3: sid=538 devtype=DISK
allocated channel: ORA_DISK_4
channel ORA_DISK_4: sid=537 devtype=DISK
allocated channel: ORA_DISK_5
channel ORA_DISK_5: sid=536 devtype=DISK
allocated channel: ORA_DISK_6
channel ORA_DISK_6: sid=535 devtype=DISK
Crosschecked 44 objects
Finished implicit crosscheck backup at 24-JUN-10

Starting implicit crosscheck copy at 24-JUN-10
using channel ORA_DISK_1
using channel ORA_DISK_2
using channel ORA_DISK_3
using channel ORA_DISK_4
using channel ORA_DISK_5
using channel ORA_DISK_6
Finished implicit crosscheck copy at 24-JUN-10

searching for all files in the recovery area
cataloging files...
no files cataloged

using channel ORA_DISK_1
using channel ORA_DISK_2
using channel ORA_DISK_3
using channel ORA_DISK_4
using channel ORA_DISK_5
using channel ORA_DISK_6

channel ORA_DISK_1: starting datafile backupset restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
restoring datafile 00007 to /u03/oradata/prod1/system02.dbf
restoring datafile 00009 to /u03/oradata/prod1/glassfishjms_data01.dbf
restoring datafile 00010 to /u03/oradata/prod1/mciw_data01.dbf
channel ORA_DISK_1: reading from backup piece /u02/backup/prod1/hqlgt5hp_1_1
channel ORA_DISK_2: starting datafile backupset restore
channel ORA_DISK_2: specifying datafile(s) to restore from backup set
restoring datafile 00004 to /u03/oradata/prod1/jira.dbf
restoring datafile 00005 to /u03/oradata/prod1/users01.dbf
restoring datafile 00018 to /u03/oradata/prod1/mcollage_lob01.dbf
channel ORA_DISK_2: reading from backup piece /u02/backup/prod1/holgt5ho_1_1
channel ORA_DISK_3: starting datafile backupset restore
channel ORA_DISK_3: specifying datafile(s) to restore from backup set
restoring datafile 00003 to /u03/oradata/prod1/sysaux01.dbf

...
...

channel ORA_DISK_2: restored backup piece 1
piece handle=/u02/backup/prod1/hplgt5ho_1_1 tag=TAG20100622T200404
channel ORA_DISK_2: restore complete, elapsed time: 00:27:35
channel ORA_DISK_1: restored backup piece 1
piece handle=/u02/backup/prod1/holgt5ho_1_1 tag=TAG20100622T200404
channel ORA_DISK_1: restore complete, elapsed time: 00:49:12
Finished restore at 24-JUN-10


Recover the database

The recovery will fail at a point where it cannot restore any more archived redo log files.

In this case the last archived log file which has been backed up is sequence 613.

This information  can be obtained by issuing a LIST BACKUP OF ARCHIVELOG ALL command.

So RMAN will fail when it tries to apply sequence 614 ….

RMAN> recover database

Starting recover at 24-JUN-10
using channel ORA_DISK_1
using channel ORA_DISK_2
using channel ORA_DISK_3
using channel ORA_DISK_4
using channel ORA_DISK_5
using channel ORA_DISK_6

starting media recovery

channel ORA_DISK_1: starting archive log restore to default destination
channel ORA_DISK_1: restoring archive log
archive log thread=1 sequence=613
channel ORA_DISK_1: reading from backup piece /u02/backup/prod1/hulgt6bo_1_1
channel ORA_DISK_1: restored backup piece 1
piece handle=/u02/backup/prod1/hulgt6bo_1_1 tag=TAG20100622T201759
channel ORA_DISK_1: restore complete, elapsed time: 00:00:02
archive log filename=/u02/flash_recovery_area/PROD1/archivelog/2010_06_24/o1_mf_1_613_6260ob01_.arc thread=1 sequence=613
channel default: deleting archive log(s)
archive log filename=/u02/flash_recovery_area/PROD1/archivelog/2010_06_24/o1_mf_1_613_6260ob01_.arc recid=1224 stamp=722530858
unable to find archive log
archive log thread=1 sequence=614
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of recover command at 06/24/2010 15:01:03
RMAN-06054: media recovery requesting unknown log: thread 1 seq 614 lowscn 39895194


Open the database with RESETLOGS


[oracle@linux01 PROD1]$ rman target /

Recovery Manager: Release 10.2.0.4.0 - Production on Thu Jun 24 15:04:48 2010

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

connected to target database: PROD1 (DBID=4266928631, not open)

RMAN> sql 'alter database open resetlogs';

using target database control file instead of recovery catalog
sql statement: alter database open resetlogs


RMAN>

Comments

Post a Comment

Popular posts from this blog

How to find the server is whether standby (slave) or primary(master) in Postgresql replication ?

-
Method 1 You can check the mode of the server using "pg_controldata". [pgsql@test~]$ pg_controldata /usr/local/pgsql/data84/ Database cluster state: in archive recovery --> This is Standby Database Database cluster state: in production --> This is Production Database [Master] Method 2 You can use pg_is_in_recovery() which returns True if recovery is still in progress(so the server is running in standby mode or slave) postgres=# select pg_is_in_recovery(); pg_is_in_recovery ------------------- t (1 row) If Return    false   so the server is running in primary mode or master postgres=# select pg_is_in_recovery(); pg_is_in_recovery ------------------- f (1 row)
Read more

7 Steps to configure BDR replication in postgresql

-
Image
 The  BDR  (Bi-Directional Replication) project adds multi-master replication to PostgreSQL 9.4.  Postgres-BDR has a lower impact on the masters(s) than trigger-based replication solutions. There is no write-amplification, as it does not require triggers to write to queue tables in order to replicate writes.   Here We are using  postgres version 9.4.12 and bdr version 1.0.2. for configuring  multi master replication . Simply Following 7 steps you can configure the multi master replication in postgresql. To download the bdr in below link. .   https://github.com/2ndQuadrant/bdr/archive/bdr-pg/REL9_4_12-1.tar.gz $ tar -xzvf REL9_4_12-1.tar.gz $ wget https://github.com/2ndQuadrant/bdr/archive/bdr-plugin/1.0.2.tar.gz $ tar -xzvf 1.0.2.tar.gz 1. To install BDR. $ cd ~/bdr-bdr-pg-REL9_4_12-1 $ ./configure --prefix=/usr/lib/postgresql/9.4 --enable-debug --with-openssl $ make -j4 -s install-world $ cd ~/bdr-bdr-plugin-1.0.2 $ PATH=/usr/lib/postgresql/9.4/bin:"$PATH" ./c
Read more

How to Get Table Size, Database Size, Indexes Size, schema Size, Tablespace Size, column Size in PostgreSQL Database

-
In this post, I am sharing few important function for finding the size of database, table and index in PostgreSQL. Finding object size in postgresql database is very important and common. Is it very useful to know the exact size occupied by the object at the tablespace. The object size in the following scripts is in GB. The scripts have been formatted to work very easily with PUTTY SQL Editor. 1. Checking table size excluding table dependency: SELECT pg_size_pretty(pg_relation_size('mhrordhu_shk.mut_kharedi_audit')); pg_size_pretty ---------------- 238 MB (1 row) 2. Checking table size including table dependency: SELECT pg_size_pretty(pg_total_relation_size('mhrordhu_shk.mut_kharedi_audit')); pg_size_pretty ---------------- 268 MB (1 row) 3. Finding individual postgresql database size SELECT pg_size_pretty(pg_database_size('db_name')); 4. Finding individual table size for postgresql database -including dependency index: SELECT pg_size_pretty(pg_total_rel
Read more

Ora2PG - Oracle/MySQL to Postgres DB migration Version 20.0

-
Ora2Pg is a free tool used to  migrate  an  Oracle  or MySQL database to a  PostgreSQL  compatible schema. It connects your  Oracle  database, scan it automatically and extracts its structure or data, it then generates SQL scripts that you can load into your  PostgreSQL  database. ora2pg version 20.0 software : ora2pg-20.0.tar ora2pg-20.0   https://github.com/darold/ora2pg/releases/tag/v20.0 Some Blogs about DB Migration : https://2ndquadrant.in/postgresmigration/oracle-to-postgresql-migration/ https://2ndquadrant.in/database-migration-from-oracle-to-postgres/ INSTALLATION All Perl modules can always be found at CPAN ( http://search.cpan.org/ ). Just type the full name of the module (ex: DBD::Oracle) into the search input box, it will brings you the page for download. Releases of Ora2Pg stay at SF.net ( https://sourceforge.net/projects/ora2pg/ ). Under Windows you should install Strawberry Perl ( http://strawberryperl.com/ ) and the OSes corresponding Oracle clients. I
Read more

PostgreSQL Introduction

-
PostgreSQL tutorial provides basic and advanced concepts of SQL. Our PostgreSQL tutorial is designed for beginners and professionals. PostgreSQL is an opens-source object-relational database management system (ORDBMS). Our PostgreSQL tutorial includes all topics of PostgreSQL language such as create database, create table, drop database, drop table, select database, select table, insert record, update record, delete record, triggers, functions, procedures, cursors etc. There are also given PostgreSQL interview questions and quizzes to help you better understand the PostgreSQL language. Prerequisite: Before you start doing practice with various types of examples given in this reference, I'm making an assumption that you are already aware about what is database, especially RDBMS and what is a computer programming language. you must have the basic knowledge of SQL Problem: We assure that you will not find any problem in this PostgreSQL tutorial. But if there is any mistake
Read more