I am an IT professional with 10 years of professional experience,I have good proficiency on Oracle technologies, and at last 2 years of my career to study Real Application Clusters,data guard and participate actively on Oracle community ,If you want to hire me on Contract or to quote on project basis contact me at khurrampc@hotmail.com.

Wednesday, April 9, 2008

disaster recovery

SQL*Plus: Release 10.1.0.2.0 - Production on Wed Apr 9 16:23:47 2008

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


Connected to:
Oracle Database 10g Enterprise Edition Release 10.1.0.2.0 - Production
With the Partitioning, OLAP and Data Mining options

SQL> select name,dbid from v$database
  2  /

NAME            DBID
--------- ----------
ORCL      1179381888

SQL> show parameter db_recovery_file_dest

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_recovery_file_dest                string      C:\oracle\product\10.1.0\flash_recovery_area
db_recovery_file_dest_size           big integer 2G
SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount
ORACLE instance started.

Total System Global Area  171966464 bytes
Fixed Size                   787988 bytes
Variable Size             145488364 bytes
Database Buffers           25165824 bytes
Redo Buffers                 524288 bytes
Database mounted.
SQL> alter database archivelog
  2  /

Database altered.

SQL> alter database open
  2  /

Database altered.

SQL> archive log list
Database log mode              Archive Mode
Automatic archival             Enabled
Archive destination            USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence     3
Next log sequence to archive   5
Current log sequence           5
SQL> conn scott/tiger
Connected.
SQL> create table t as select * from all_objects
  2  /

Table created.

C:\>rman

Recovery Manager: Release 10.1.0.2.0 - Production

Copyright (c) 1995, 2004, Oracle.  All rights reserved.

RMAN> connect target sys/sys

connected to target database: ORCL (DBID=1179381888)

RMAN> configure controlfile autobackup on
2> ;

new RMAN configuration parameters:
CONFIGURE CONTROLFILE AUTOBACKUP ON;
new RMAN configuration parameters are successfully stored

RMAN>

RMAN> run
2> {
3> allocate channel t1 type disk;
4> backup database;
5> release channel t1;
6> allocate channel t2 type disk;
7> backup archivelog all;
8> release channel t2;
9> }

using target database controlfile instead of recovery catalog
allocated channel: t1
channel t1: sid=136 devtype=DISK
.
.
.

RMAN>

-take alls FRA backupsets to somewhere else safe i.e tape cartridge.
-Delete alls database files including spfile,pfile and password file
-Delete also cdump,udump,bdump,create.

Now you have been in disaster scenario.Now what you have to do is install the same version of oralce with same
directory structure and instance.

You may create the old instance by OS laterz after installing the oracle.


C:\>oradim -new -sid orcl
Instance created.

after instance creation ,create password file by using orapwd utility

C:\>orapwd file=C:\oracle\product\10.1.0\Db_2\database\PWDorcl.ora password=sys

put alls backup files from FRA which is in tape cartridge to its respected location.

Connect to RMAN

C:\>rman

Recovery Manager: Release 10.1.0.2.0 - Production

Copyright (c) 1995, 2004, Oracle.  All rights reserved.

RMAN> connect target sys/sys

connected to target database (not started)

RMAN>

RMAN> startup force nomount

startup failed: ORA-01078: failure in processing system parameters
LRM-00109: could not open parameter file 'C:\ORACLE\PRODUCT\10.1.0\DB_2\DATABASE
\INITORCL.ORA'

trying to start the Oracle instance without parameter files ...
Oracle instance started

Total System Global Area     113246208 bytes

Fixed Size                      787708 bytes
Variable Size                 61602564 bytes
Database Buffers              50331648 bytes
Redo Buffers                    524288 bytes

RMAN>
RMAN> restore spfile from 'C:\oracle\product\10.1.0\flash_recovery_area\ORCL\AUT
OBACKUP\2008_04_09\O1_MF_S_651605789_3ZSG2GHX_.BKP'
2> ;

Starting restore at 09-APR-08
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=29 devtype=DISK

channel ORA_DISK_1: autobackup found: C:\oracle\product\10.1.0\flash_recovery_ar
ea\ORCL\AUTOBACKUP\2008_04_09\O1_MF_S_651605789_3ZSG2GHX_.BKP
channel ORA_DISK_1: SPFILE restore from autobackup complete
Finished restore at 09-APR-08

RMAN> shutdown immediate

Oracle instance shut down

RMAN> startup

connected to target database (not started)
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of startup command at 04/09/2008 18:13:56
RMAN-04014: startup failed: ORA-02778: Name given for the log directory is invalid

RMAN> ^C


create bdump,udump,cdump and create folder at C:\oracle\product\10.1.0\admin\orcl

C:\>rman

Recovery Manager: Release 10.1.0.2.0 - Production

Copyright (c) 1995, 2004, Oracle.  All rights reserved.

RMAN> connect target sys/sys

connected to target database (not started)

RMAN> startup

Oracle instance started
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of startup command at 04/09/2008 18:17:41
ORA-00205: error in identifying controlfile, check alert log for more info

RMAN> restore controlfile from autobackup
2> ;

Starting restore at 09-APR-08
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=160 devtype=DISK

recovery area destination: C:\oracle\product\10.1.0\flash_recovery_area
database name (or lock name space) used for search: ORCL
channel ORA_DISK_1: autobackup found in the recovery area
channel ORA_DISK_1: autobackup found: C:\ORACLE\PRODUCT\10.1.0\FLASH_RECOVERY_AR
EA\ORCL\AUTOBACKUP\2008_04_09\O1_MF_S_651605800_3ZSG2SLS_.BKP
channel ORA_DISK_1: controlfile restore from autobackup complete
output filename=C:\ORACLE\PRODUCT\10.1.0\ORADATA\ORCL\CONTROL01.CTL
output filename=C:\ORACLE\PRODUCT\10.1.0\ORADATA\ORCL\CONTROL02.CTL
output filename=C:\ORACLE\PRODUCT\10.1.0\ORADATA\ORCL\CONTROL03.CTL
Finished restore at 09-APR-08

RMAN> startup mount

database is already started
database mounted

RMAN> restore database
2> ;

ORACLE error from target database:
ORA-19922: there is no parent row with id 0 and level 0
ORA-06512: at "SYS.X$DBMS_BACKUP_RESTORE", line 5038
ORA-06512: at line 1

RMAN-06900: WARNING: unable to generate V$RMAN_STATUS or V$RMAN_OUTPUT row
RMAN-06901: WARNING: disabling update of the V$RMAN_STATUS and V$RMAN_OUTPUT row
s
Starting restore at 09-APR-08
Starting implicit crosscheck backup at 09-APR-08
released channel: ORA_DISK_1
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=160 devtype=DISK
Crosschecked 3 objects
Finished implicit crosscheck backup at 09-APR-08

RMAN> recover database
2> ;

Starting recover at 09-APR-08
using channel ORA_DISK_1

starting media recovery

archive log thread 1 sequence 8 is already on disk as file C:\ORACLE\PRODUCT\10.
1.0\FLASH_RECOVERY_AREA\ORCL\ARCHIVELOG\2008_04_09\O1_MF_1_8_3ZSG2M7M_.ARC
archive log filename=C:\ORACLE\PRODUCT\10.1.0\FLASH_RECOVERY_AREA\ORCL\ARCHIVELO
G\2008_04_09\O1_MF_1_8_3ZSG2M7M_.ARC thread=1 sequence=8
unable to find archive log
archive log thread=1 sequence=9
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of recover command at 04/09/2008 18:31:10
RMAN-06054: media recovery requesting unknown log: thread 1 seq 9 lowscn 332376

RMAN> alter database open resetlogs
2> ;

database opened

RMAN> exit


Recovery Manager complete.

C:\>

4 comments:

mohsinsaqib said...

That works for a table, with an assumption that we are ok to overwrite on other data using PITR.
But if we want few tables out of hundreds then it is more interesting.
That is, you find tablespaces for those tables and indexes, and then separately restore them or do PITR of whole database (auxiliary database), then export import your desired data.

Khurram Siddiqui said...

mohsin i am not getting yours comments , why you are relating PITR to disaster recovery?

daspeac said...

I have heard about another way of recover a database from mssql. Besides, you can visit my blogs at: http://daspeac.livejournal.com/ or http://daspeac.blogspot.com/ where I’m trying to share my experience with regard to data corruption issues.

Kim said...

Hi,

good article and thanks for this info.

Regards
Kim Roddy

Video conferencing uk

Followers

About Me

My photo
Melbourne, Victoria, Australia