Friday, December 23, 2011

CREATE DB FROM RMAN BACKUPS WITH SAME DBNAME ON SAME HOST OR REMOTE HOST

CREATE DB FROM RMAN BACKUPS WITH SAME DBNAME ON SAME HOST OR REMOTE HOST IN ORACLE 11G:

Please do check with oracle online documentation before we do any backup and recovery steps, I think it is good.. having complete failed backup of db/dbfiles storage would help us if something goes wrong in our recovery.. have a copy of it...


One should ensure that Database has valid RMAN full backup all the available......at least three places...

Problem: All the files are lost, including spfile, controlfile, dbf files, redo logs.. only we have good rman backup..

C:\Users\HOME>rman target /

Recovery Manager: Release 11.2.0.1.0 - Production on Wed Dec 21 20:53:09 2011

Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.

connected to target database: TSTDEV01 (DBID=874354934)

RMAN> SHOW ALL;

using target database control file instead of recovery catalog
RMAN configuration parameters for database with db_unique_name TSTDEV01 are:
CONFIGURE RETENTION POLICY TO REDUNDANCY 1; # default
CONFIGURE BACKUP OPTIMIZATION ON;
CONFIGURE DEFAULT DEVICE TYPE TO DISK; # default
CONFIGURE CONTROLFILE AUTOBACKUP ON;
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '%F'; # default
CONFIGURE DEVICE TYPE DISK PARALLELISM 1 BACKUP TYPE TO BACKUPSET; # default
CONFIGURE DATAFILE BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
CONFIGURE ARCHIVELOG BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
CONFIGURE MAXSETSIZE TO UNLIMITED; # default
CONFIGURE ENCRYPTION FOR DATABASE OFF; # default
CONFIGURE ENCRYPTION ALGORITHM 'AES128'; # default
CONFIGURE COMPRESSION ALGORITHM 'BASIC' AS OF RELEASE 'DEFAULT' OPTIMIZE FOR LOA
D TRUE ; # default
CONFIGURE ARCHIVELOG DELETION POLICY TO NONE; # default
CONFIGURE SNAPSHOT CONTROLFILE NAME TO 'F:\APP\HOME\PRODUCT\11.2.0\DBHOME_1\DATA
BASE\SNCFTSTDEV01.ORA'; # default

set oracle_sid
C:\Users\HOME>set ORACLE_SID=TSTDEV01


Connect rman to take database backup
C:\Users\HOME>rman target /
connected to target database: TSTDEV01 (DBID=874354934)

3. take the database backup using one of the way shown below

run
{
ALLOCATE CHANNEL disk1 DEVICE TYPE DISK FORMAT 'd:\testdelete\dbf\%U';
backup database plus archivelog delete input;
}

or

run
{
ALLOCATE CHANNEL disk1 DEVICE TYPE DISK FORMAT 'd:\testdelete\dbf\%U';
backup database plus archivelog delete input;
backup current controlfile format 'd:\testdelete\dbf\control.back';
BACKUP SPFILE TO DESTINATION 'd:\testdelete\dbf';
BACKUP AS COPY CURRENT CONTROLFILE FORMAT 'd:\testdelete\dbf\control01.ctl';
}

Ensure that where control file, spfile backup is being made. it is important. I do like to run second run block no matter how many control files are created... good to safe...
Second run would help us when recovery disk location is lost.. otherwise first run block is okay...



---------------------- Restore database from RMAN backup starts from here....------------------

LOST DATABASE FILES (SPFILE, CONTROL FILES, DBF AND REDO LOGS ).. ONLY HAVE RMAN FULL BACKUP

1. Create instance
C:\Users\HOME>oradim -new -sid TSTDEV01 -intpwd oracle -startmode m
Instance created.

After creation of instance, one can use netmanager and netca to configure listener.ora and tnsnames.ora etc files... or one can go for manuall edit (ensure entries are made in right format)..
2. Add the following entry in listener.ora file

(SID_DESC =
(GLOBAL_DBNAME=TSTDEV01)
(SID_NAME = TSTDEV01)
(ORACLE_HOME = F:\app\HOME\product\11.2.0\dbhome_1)
)

3. Add the following entry into tnsnames.ora file

 TSTDEV01 =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = TSTDEV01)
)
)



C:\Users\HOME>lsnrctl reload
C:\Users\HOME>lsnrclt status

Ensure instance is ready and accessible.

C:\Users\HOME>tnsping TSTDEV01

TNS Ping Utility for 32-bit Windows: Version 11.2.0.1.0 - Production on 21-DEC-2
011 21:27:06

Copyright (c) 1997, 2010, Oracle. All rights reserved.

Used parameter files:
F:\app\HOME\product\11.2.0\dbhome_1\network\admin\sqlnet.ora


Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = localhos
t)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = TSTDEV01))
)
OK (20 msec)

C:\Users\HOME>

I see lsnrctl stop, lsnrctl start would help here .. if u can not able to login using sqlplus sys/pwd@dbname as sysdba..
C:\Users\HOME>SQLPLUS SYS/ORACLE@TSTDEV01 AS SYSDBA

SQL*Plus: Release 11.2.0.1.0 Production on Wed Dec 21 21:34:10 2011

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

Connected to an idle instance.

SQL>

4. Create the spfile from the rman backup

C:\Users\HOME>RMAN TARGET SYS/ORACLE@TSTDEV01

Recovery Manager: Release 11.2.0.1.0 - Production on Wed Dec 21 21:35:03 2011

Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.

connected to target database (not started)

RMAN> set DBID=9945147239

executing command: SET DBID

RMAN> startup force nomount

startup failed: ORA-01078: failure in processing system parameters
LRM-00109: could not open parameter file 'F:\APP\HOME\PRODUCT\11.2.0\DBHOME_1\DA
TABASE\INITTSTDEV01.ORA'

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

Total System Global Area 159019008 bytes

Fixed Size 1373264 bytes
Variable Size 75500464 bytes
Database Buffers 75497472 bytes
Redo Buffers 6647808 bytes

RMAN> restore spfile from 'F:\app\HOME\flash_recovery_area\TSTDEV01\AUTOBACKUP\2011_12_21\O1_MF_S_770504345_7H3YT5T9_.BKP';

Starting restore at 21-DEC-11
using channel ORA_DISK_1

channel ORA_DISK_1: restoring spfile from AUTOBACKUP F:\app\HOME\flash_recovery_
area\TSTDEV01\AUTOBACKUP\2011_12_21\O1_MF_S_770504345_7H3YT5T9_.BKP
channel ORA_DISK_1: SPFILE restore from AUTOBACKUP complete
Finished restore at 21-DEC-11

RMAN>

Run the following command at command prompt to create pfile from spfile that is just created from rman backup
sql "create PFILE = ''D:\TESTDELETE\PFILE'' from SPFILE = ''F:\app\HOME\product\11.2.0\dbhome_1\database\SPFILETSTDEV01.ORA''";

RMAN> sql "create PFILE = ''D:\TESTDELETE\PFILE'' from SPFILE = ''F:\app\HOME\pr
oduct\11.2.0\dbhome_1\database\SPFILETSTDEV01.ORA''";

sql statement: create PFILE = ''D:\TESTDELETE\PFILE'' from SPFILE = ''F:\app\HOM
E\product\11.2.0\dbhome_1\database\SPFILETSTDEV01.ORA''

RMAN>exit

5. Create control file from RMAN backups

Shutdown the database and start with spfile (create in step 4)

C:\Users\HOME>SQLPLUS SYS/ORACLE@TSTDEV01 AS SYSDBA

SQL*Plus: Release 11.2.0.1.0 Production on Wed Dec 21 21:46:49 2011

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


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options


ORACLE instance shut down.
SQL> startup nomount;
ORA-00119: invalid specification for system parameter LOCAL_LISTENER
ORA-00132: syntax error or unresolved network name 'LISTENER_TSTDEV01'

remove the following line from pfile the one just created
*.local_listener='LISTENER_TSTDEV01'


SQL> startup nomount pfile='d:\testdelete\PFILE';
ORACLE instance started.

Total System Global Area 640286720 bytes
Fixed Size 1376492 bytes
Variable Size 197136148 bytes
Database Buffers 436207616 bytes
Redo Buffers 5566464 bytes
SQL>

6. Get the controle file from backup

Here are many ways to get the controlfile from RMAN backup.... shown three ways ( I dont know but some reason I backup control file many times..see the rman backup run script above).

 RMAN> restore controlfile from autobackup;

Starting restore at 23-DEC-11
using channel ORA_DISK_1

recovery area destination: F:\app\HOME\flash_recovery_area
database name (or database unique name) used for search: TSTDEV01
channel ORA_DISK_1: AUTOBACKUP F:\APP\HOME\FLASH_RECOVERY_AREA\TSTDEV01\AUTOBACK
UP\2011_12_22\O1_MF_S_770591689_7H6N3ML1_.BKP found in the recovery area
AUTOBACKUP search with format "%F" not attempted because DBID was not set
channel ORA_DISK_1: restoring control file from AUTOBACKUP F:\APP\HOME\FLASH_REC
OVERY_AREA\TSTDEV01\AUTOBACKUP\2011_12_22\O1_MF_S_770591689_7H6N3ML1_.BKP
channel ORA_DISK_1: control file restore from AUTOBACKUP complete
output file name=F:\APP\HOME\ORADATA\TSTDEV01\CONTROL01.CTL
output file name=F:\APP\HOME\FLASH_RECOVERY_AREA\TSTDEV01\CONTROL02.CTL
Finished restore at 23-DEC-11

RMAN> restore controlfile from 'D:\TESTDELETE\dbf\control.back';

Starting restore at 23-DEC-11
using channel ORA_DISK_1

channel ORA_DISK_1: restoring control file
channel ORA_DISK_1: restore complete, elapsed time: 00:00:03
output file name=F:\APP\HOME\ORADATA\TSTDEV01\CONTROL01.CTL
output file name=F:\APP\HOME\FLASH_RECOVERY_AREA\TSTDEV01\CONTROL02.CTL
Finished restore at 23-DEC-11

RMAN> restore controlfile from 'D:\TESTDELETE\dbf\control01.ctl';

Starting restore at 23-DEC-11
using channel ORA_DISK_1

channel ORA_DISK_1: copied control file copy
output file name=F:\APP\HOME\ORADATA\TSTDEV01\CONTROL01.CTL
output file name=F:\APP\HOME\FLASH_RECOVERY_AREA\TSTDEV01\CONTROL02.CTL
Finished restore at 23-DEC-11

RMAN>
RMAN> alter database mount;

database mounted
released channel: ORA_DISK_1

RMAN> restore database;

Starting restore at 23-DEC-11
using channel ORA_DISK_1

channel ORA_DISK_1: starting datafile backup set restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_DISK_1: restoring datafile 00001 to F:\APP\HOME\ORADATA\TSTDEV01\SYS
TEM01.DBF
channel ORA_DISK_1: restoring datafile 00002 to F:\APP\HOME\ORADATA\TSTDEV01\SYS
AUX01.DBF
channel ORA_DISK_1: restoring datafile 00003 to F:\APP\HOME\ORADATA\TSTDEV01\UND
OTBS01.DBF
channel ORA_DISK_1: restoring datafile 00004 to F:\APP\HOME\ORADATA\TSTDEV01\USE
RS01.DBF
channel ORA_DISK_1: reading from backup piece D:\TESTDELETE\DBF\0KMUSIQ7_1_1
channel ORA_DISK_1: piece handle=D:\TESTDELETE\DBF\0KMUSIQ7_1_1 tag=TAG20111222T
211239
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:02:25
Finished restore at 23-DEC-11

RMAN> recover database;

Starting recover at 23-DEC-11
using channel ORA_DISK_1

starting media recovery

channel ORA_DISK_1: starting archived log restore to default destination
channel ORA_DISK_1: restoring archived log
archived log thread=1 sequence=16
channel ORA_DISK_1: reading from backup piece D:\TESTDELETE\DBF\0LMUSITS_1_1
channel ORA_DISK_1: piece handle=D:\TESTDELETE\DBF\0LMUSITS_1_1 tag=TAG20111222T
211436
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:01
archived log file name=F:\APP\HOME\FLASH_RECOVERY_AREA\TSTDEV01\ARCHIVELOG\2011_
12_23\O1_MF_1_16_7H94R2H4_.ARC thread=1 sequence=16
channel default: deleting archived log(s)
archived log file name=F:\APP\HOME\FLASH_RECOVERY_AREA\TSTDEV01\ARCHIVELOG\2011_
12_23\O1_MF_1_16_7H94R2H4_.ARC RECID=16 STAMP=770674266
unable to find archived log
archived log thread=1 sequence=17
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of recover command at 12/23/2011 20:11:10
RMAN-06054: media recovery requesting unknown archived log for thread 1 with seq
uence 17 and starting SCN of 958969

RMAN> recover database until logseq 17;

Starting recover at 23-DEC-11
using channel ORA_DISK_1

starting media recovery
media recovery complete, elapsed time: 00:00:01

Finished recover at 23-DEC-11

RMAN> alter database open;

RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of alter db command at 12/23/2011 20:12:30
ORA-01589: must use RESETLOGS or NORESETLOGS option for database open


RMAN> alter database open resetlogs;

database opened

RMAN>

Note: Usually resetlogs used when incomplete recovery took place otherwise we should go for noresetlogs option.
RESETLOGS would initialize all the logs, reset the log sequence number from start and start a new incarnation of the database.


RMAN> exit


Recovery Manager complete.

C:\Users\HOME>sqlplus "/as sysdba"

SQL*Plus: Release 11.2.0.1.0 Production on Fri Dec 23 20:22:23 2011

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


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> archive log list
Database log mode Archive Mode
Automatic archival Enabled
Archive destination USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence 1
Next log sequence to archive 1
Current log sequence 1
SQL> select name, open_mode from v$database;

NAME OPEN_MODE
--------- --------------------
TSTDEV01 READ WRITE

SQL>


Check everything and have a good backup once everything looks good.. Good to have backup at this moment.

.. Have a great fun in working in oracle technologies... Cheers....

No comments:

Post a Comment