Monday, November 4, 2013

SCENARIO – LOSS OF ALL CONTROLFILES (NO CATALOG)




 
SQL> insert into testobj select * from myobjects;

919664 rows created.

SQL> commit;

Commit complete.

SQL> select count(*) from testobj;

  COUNT(*)
----------
   1432318   >>>> need to check this record count after recovery

SQL> archive log list
Database log mode              Archive Mode
Automatic archival             Enabled
Archive destination            USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence     5
Next log sequence to archive   7
Current log sequence           7

Note - current log sequence is 7 - not archived but contains the last committed changes that we made
Note - archive logs will not be found in $ARCV area, but in the flashback location

Simulate a failure

SQL> select name from v$controlfile;

NAME
--------------------------------------------------------------------------------
/u01/ORACLE/mydb/control01.ctl
/u01/ORACLE/mydb/control02.ctl
/u01/ORACLE/mydb/control03.ctl

SQL> !rm /u01/ORACLE/mydb/*.ctl


SQL> alter tablespace users online;
alter tablespace users online
*
ERROR at line 1:
ORA-00603: ORACLE server session terminated by fatal error


SQL> shutdown abort
ORACLE instance shut down.
SQL> startup nomount;
ORACLE instance started.

Total System Global Area  893386752 bytes
Fixed Size                  2076816 bytes
Variable Size             432017264 bytes
Database Buffers          452984832 bytes
Redo Buffers                6307840 bytes

Since we are not using a RMAN catalog we need to set the DBID

RMAN> set dbid=2424550413;

executing command: SET DBID


Restore the controlfile

RMAN> run {
2> restore controlfile from autobackup;
3> }

Starting restore at 18-JAN-13
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=156 devtype=DISK

recovery area destination: /u01/ORACLE/flash_recovery_arema
database name (or database unique name) used for search: MYDB
channel ORA_DISK_1: autobackup found in the recovery area
channel ORA_DISK_1: autobackup found: /u01/ORACLE/flash_recovery_area/MYDB/autobackup/2013_01_18/o1_mf_s_637601034_3gynd74g_.bkp
channel ORA_DISK_1: control file restore from autobackup complete
output filename=/u01/ORACLE/mydb/control01.ctl
output filename=/u01/ORACLE/mydb/control02.ctl
output filename=/u01/ORACLE/mydb/control03.ctl
Finished restore at 18-SEP-07

Mount and recover the database

RMAN> alter database mount;

database mounted
released channel: ORA_DISK_1

RMAN> recover database;

Starting recover at 18-JAN-13
Starting implicit crosscheck backup at 18-JAN-13
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=156 devtype=DISK
Crosschecked 1 objects
Finished implicit crosscheck backup at 18-JAN-13

Starting implicit crosscheck copy at 18-JAN-13
using channel ORA_DISK_1
Finished implicit crosscheck copy at 18-JAN-13

searching for all files in the recovery area
cataloging files...
cataloging done

List of Cataloged Files
=======================
File Name: /u01/ORACLE/flash_recovery_area/MYDB/autobackup/2013_01_18/o1_mf_s_632641095_3gynd74g_.bkp

using channel ORA_DISK_1
allocated channel: ORA_SBT_TAPE_1
channel ORA_SBT_TAPE_1: sid=155 devtype=SBT_TAPE
channel ORA_SBT_TAPE_1: Data Protection for Oracle: version 5.2.4.0
datafile 4 not processed because file is offline

starting media recovery

archive log thread 1 sequence 6 is already on disk as file /u01/ORACLE/flash_recovery_area/MYDB/archivelog/2013_01_18/o1_mf_1_6_3gyn7vnk_.arc
archive log thread 1 sequence 7 is already on disk as file /u01/ORACLE/mydb/redo03.log
archive log filename=/u01/ORACLE/flash_recovery_area/MYDB/archivelog/2013_01_18/o1_mf_1_6_3gyn7vnk_.arc thread=1 sequence=6
archive log filename=/u01/ORACLE/mydb/redo03.log thread=1 sequence=7  >>>> current redo log with committed but unarchived changes applied
media recovery complete, elapsed time: 00:00:02
Finished recover at 18-JAN-13


SQL> alter database open resetlogs;

Database altered.

 conn scott/tiger
Connected.
SQL> select count(*) from myobjects;

  COUNT(*)
----------
   1432318  



Network_DBlink




Step:-1 Create the following tns entry in tnsnames.ora file in 1st server. (give the ip of 2nd server)
==================================================
try=
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 0.0.0.0)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (sid = orcl)
    )
  )

step2:- On 1st server where you have created tns entry,now create the followig DBlink in Sys schema.
====================================================================================================

create public database link remoteloc connect to scott identified by tiger using 'try';


step3:- Create directory on 1st server
=======================================================

SQL> Create or replace directory mydir as 'd:\datapump';
SQL> Grant read,write on direcotry mydir to scott;

(Also create a physical folder on drive d:\ with the name of 'datapump'


step4:- Run the expdp command on 1st server
=============================================
C:\Users\>expdp scott/tiger directory=mydir  network_link=rloc2 sche
mas=scott dumpfile=newscot1.dmp


Enjoy.........................






--