Saturday, May 24, 2008

How to recover Database by SCN number (incomplete recovery) -RMAN

• Log sequence recovery

1. Mount the database.
2. Restore all data files.
3. Recover the database by using UNTIL SCN
4. Open the database by using RESETLOGS.

Know last SCN no.
SQL> select first_change#,status,group# from v$log;

FIRST_CHANGE# STATUS GROUP#
------------- ---------------- ----------
8793770 CURRENT 1
8785734 ACTIVE 2
8793669 ACTIVE 3

The FIRST_CHANGE# is the first SCN stamped in the missing log. This implies that the last
SCN stamped in the previous log is 8793769 (FIRST_CHANGE#-1). This is the highest SCN
that we can recover to. In order to do the recovery we must first restore ALL datafiles to this
SCN, followed by recovery (also up to this SCN).

RMAN> shutdown immediate;

using target database control file instead of re
database closed
database dismounted
Oracle instance shut down

RMAN> startup mount;

connected to target database (not started)
Oracle instance started
database mounted

Total System Global Area 683671552 bytes

Fixed Size 1250836 bytes
Variable Size 125831660 bytes
Database Buffers 549453824 bytes
Redo Buffers 7135232 bytes

RMAN> restore database until scn 8793769;
……
channel ORA_DISK_1: restore complete, elapsed time: 00:02:17
Finished restore at 23-MAY-08

RMAN> recover database until scn 8793769;
……
media recovery complete, elapsed time: 00:00:16
Finished recover at 23-MAY-08

RMAN> alter database open resetlogs;

database opened

Saturday, May 17, 2008

Limit exceeded for recovery files.

SQL> startup;
ORACLE instance started.

Total System Global Area 364904448 bytes
Fixed Size 778880 bytes
Variable Size 116137344 bytes
Database Buffers 247463936 bytes
Redo Buffers 524288 bytes
Database mounted.
ORA-16038: log 3 sequence# 693 cannot be archived
ORA-19809: limit exceeded for recovery files
ORA-00312: online log 3 thread 1: '/u01/oradata/erprod/erprod/redo03.log'

SQL> alter system set DB_RECOVERY_FILE_DEST_SIZE = 10g;
...
SQL> shutdown immediate;
...
SQL> startup;

...no error message this time...

How to change Archive log Mode

SQL> startup mount;
ORACLE instance started.

Total System Global Area 612368384 bytes
Fixed Size 1250428 bytes
Variable Size 226495364 bytes
Database Buffers 377487360 bytes
Redo Buffers 7135232 bytes
Database mounted.
SQL> alter database noarchivelog;

Database altered.

SQL> alter database open;

Database altered.

SQL> select log_mode from v$database;

LOG_MODE
------------
NOARCHIVELOG

How to recover a dropped table from Recycle bin

SQL> show recyclebin

ORIGINAL NAME RECYCLEBIN NAME OBJECT TYPE DROP TIME
---------------- ------------------------------ ------------ ------------------
RECYCLETEST BIN$04LhcpndanfgMAAAAAANPw==$0 TABLE 2004-02-16:21:13:31
This shows the original name of the table, RECYCLETEST, as well as the new name in the recycle bin, which has the same name as the new table we saw created after the drop. (Note: the exact name may differ by platform.) To reinstate the table, all you have to do is use the FLASHBACK TABLE command:
SQL> FLASHBACK TABLE RECYCLETEST TO BEFORE DROP;

FLASHBACK COMPLETE.