Parm Patram

column
select inst_id,opname, totalwork, sofar, time_remaining from gv$session_longops where totalwork > sofar order by time_remaining;

1. Trying to start the database using ’startup’ will fail trying to open the datafile. It will give the datafile number let’s say D.

2. Make sure we have backups of all the datafiles. In RMAN type:
REPORT NEED BACKUP REDUNDANCY 1;

If there are datafiles that were not backed up as yet, there are 2 options:
a) delete the datafiles. (RMAN will create them and apply their data from redo logs).
b) issue RMAN command:
BACKUP DATAFILE N;

3. Use RMAN to restore and recover the datafile found missing in step #1. The recovery operation will fail because the online redo is missing.
RESTORE DATAFILE D;
RECOVER DATAFILE D;

4. Find the last log sequence:
SELECT SEQUENCE# FROM V$ARCHIVED_LOG WHERE RECID = (SELECT MAX(RECID) FROM V$ARC
HIVED_LOG);

5. Run the following RMAN script. The nunber X will be 1 more than the number you got from step 4. This is because the UNTIL SEQUENCE clause of RMAN goes to one less than the number specified.

RUN {
SET UNTIL SEQUENCE X THREAD 1;
RESTORE DATABASE;
RECOVER DATABASE;
ALTER DATABASE OPEN RESETLOGS;
}

select max(sequence#) from v$archived_logs;

this can then be used in rman as follows:

run{
set until sequence 15 thread 1;
restore database;
recover database;
alter database open resetlogs;
}

http://oracle.3dub.com/RMAN/

[oracle@odbs ~]$ sqlplus / as sysdba

SQL*Plus: Release 11.1.0.6.0 – Production on Mon Sep 29 20:05:49 2008

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

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

SQL> create user rman_user identified by rabid default tablespace RMAN_CAT temporary tablespace TEMP;

User created.

SQL> grant resource,connect,recovery_catalog_owner to rman_user;

Grant succeeded.

SQL> quit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 – Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
[oracle@odbs ~]$ rman

Recovery Manager: Release 11.1.0.6.0 – Production on Mon Sep 29 20:07:59 2008

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

RMAN> connect catalog rman_user/rabid

connected to recovery catalog database

RMAN> create catalog tablespace RMAN_CAT;

recovery catalog created

RMAN> connect target

connected to target database: ORCL (DBID=1191592594)

RMAN> register database;

database registered in recovery catalog
starting full resync of recovery catalog
full resync complete

RMAN> run
{
allocate channel c1 type disk;
backup database format ‘db_%u_%d_%s’;
backup format ‘log_t%t_s%s_p%p’
(archivelog all);
}

allocated channel: c1
channel c1: SID=115 device type=DISK

Starting backup at 29-SEP-08
channel c1: starting full datafile backup set
channel c1: specifying datafile(s) in backup set
input datafile file number=00002 name=/u01/app/oracle/oradata/orcl/sysaux01.dbf
input datafile file number=00001 name=/u01/app/oracle/oradata/orcl/system01.dbf
input datafile file number=00003 name=/u01/app/oracle/oradata/orcl/undotbs01.dbf
input datafile file number=00006 name=/u01/app/oracle/oradata/orcl/rman_catalog
input datafile file number=00005 name=/u01/app/oracle/oradata/orcl/example01.dbf
input datafile file number=00004 name=/u01/app/oracle/oradata/orcl/users01.dbf
channel c1: starting piece 1 at 29-SEP-08
channel c1: finished piece 1 at 29-SEP-08
piece handle=/u01/app/oracle/product/11.1.0/db_1/dbs/db_01jrr4j4_ORCL_1 tag=TAG2 0080929T201323 comment=NONE
channel c1: backup set complete, elapsed time: 00:05:21
channel c1: starting full datafile backup set
channel c1: specifying datafile(s) in backup set
including current control file in backup set
including current SPFILE in backup set
channel c1: starting piece 1 at 29-SEP-08
channel c1: finished piece 1 at 29-SEP-08
piece handle=/u01/app/oracle/product/11.1.0/db_1/dbs/db_02jrr4u7_ORCL_2 tag=TAG2 0080929T201323 comment=NONE
channel c1: backup set complete, elapsed time: 00:00:01
Finished backup at 29-SEP-08

Starting backup at 29-SEP-08
current log archived
channel c1: starting archived log backup set
channel c1: specifying archived log(s) in backup set
input archived log thread=1 sequence=188 RECID=2 STAMP=666126255
input archived log thread=1 sequence=189 RECID=4 STAMP=666136994
input archived log thread=1 sequence=190 RECID=6 STAMP=666137372
input archived log thread=1 sequence=191 RECID=8 STAMP=666155715
input archived log thread=1 sequence=192 RECID=10 STAMP=666181071
input archived log thread=1 sequence=193 RECID=12 STAMP=666202337
input archived log thread=1 sequence=194 RECID=14 STAMP=666223313
input archived log thread=1 sequence=195 RECID=16 STAMP=666223791
input archived log thread=1 sequence=196 RECID=18 STAMP=666232938
input archived log thread=1 sequence=197 RECID=20 STAMP=666258139
input archived log thread=1 sequence=198 RECID=22 STAMP=666283234
input archived log thread=1 sequence=199 RECID=24 STAMP=666306043
input archived log thread=1 sequence=200 RECID=26 STAMP=666309784
input archived log thread=1 sequence=201 RECID=28 STAMP=666316859
input archived log thread=1 sequence=202 RECID=30 STAMP=666339853
input archived log thread=1 sequence=203 RECID=32 STAMP=666363436
input archived log thread=1 sequence=204 RECID=34 STAMP=666384833
input archived log thread=1 sequence=205 RECID=36 STAMP=666396177
input archived log thread=1 sequence=206 RECID=38 STAMP=666396660
input archived log thread=1 sequence=207 RECID=40 STAMP=666414900
input archived log thread=1 sequence=208 RECID=42 STAMP=666439937
input archived log thread=1 sequence=209 RECID=44 STAMP=666463456
input archived log thread=1 sequence=210 RECID=46 STAMP=666482535
input archived log thread=1 sequence=211 RECID=48 STAMP=666483086
input archived log thread=1 sequence=212 RECID=50 STAMP=666496858
input archived log thread=1 sequence=213 RECID=52 STAMP=666511346
input archived log thread=1 sequence=214 RECID=54 STAMP=666523956
input archived log thread=1 sequence=215 RECID=56 STAMP=666540114
input archived log thread=1 sequence=216 RECID=58 STAMP=666554550
input archived log thread=1 sequence=217 RECID=60 STAMP=666569511
input archived log thread=1 sequence=218 RECID=62 STAMP=666576197
input archived log thread=1 sequence=219 RECID=64 STAMP=666597710
input archived log thread=1 sequence=220 RECID=66 STAMP=666600496
input archived log thread=1 sequence=221 RECID=68 STAMP=666618400
input archived log thread=1 sequence=222 RECID=70 STAMP=666637130
input archived log thread=1 sequence=223 RECID=72 STAMP=666648749
input archived log thread=1 sequence=224 RECID=74 STAMP=666655412
input archived log thread=1 sequence=225 RECID=76 STAMP=666675364
input archived log thread=1 sequence=226 RECID=78 STAMP=666699443
input archived log thread=1 sequence=227 RECID=80 STAMP=666722128
input archived log thread=1 sequence=228 RECID=82 STAMP=666735075
input archived log thread=1 sequence=229 RECID=84 STAMP=666735595
channel c1: starting piece 1 at 29-SEP-08
channel c1: finished piece 1 at 29-SEP-08
piece handle=/u01/app/oracle/product/11.1.0/db_1/dbs/log_t666735600_s3_p1 tag=TA G20080929T201958 comment=NONE
channel c1: backup set complete, elapsed time: 00:04:06
Finished backup at 29-SEP-08
released channel: c1

RMAN> print script b_whole;

RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-06004: ORACLE error from recovery catalog database: RMAN-20400: stored scri pt not found
RMAN-06083: error when loading stored script b_whole

RMAN> help

RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-00558: error encountered while parsing input commands
RMAN-01009: syntax error: found “identifier”: expecting one of: “allocate, amper , alter, advise, backup, beginline, blockrecover, catalog, change, connect, copy , convert, create, crosscheck, configure, duplicate, debug, delete, drop, exit, endinline, flashback, grant, host, import, {, library, list, mount, open, print, quit, recover, register, release, replace, report, renormalize, reset, restore, resync, rman, run, rpctest, repair, revoke, set, setlimit, sql, switch, spool, startup, shutdown, send, show, test, transport, upgrade, unregister, validate”
RMAN-01008: the bad identifier was: help
RMAN-01007: at line 1 column 1 file: standard input

RMAN> ?

RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-00558: error encountered while parsing input commands
RMAN-01006: error signaled during parse
RMAN-02001: unrecognized punctuation symbol “?”

RMAN>