Vitor Eduardo

Bom estou fazendo este procedimento de restore do banco pois hoje no trabalhou ocorreu um problema semelhante, ande que foi deletado os controlfiles por alguém e o banco estava em no archive mode, sendo assim teria que ser feito restore do ambiente com perda de dados, segue procedimento semelhante que fiz na minha Vmware:

[oracle@hodb001vtr dbs]$ . oraenv
ORACLE_SID = [orcl] ? orcl
[oracle@hodb001vtr dbs]$ sqlplus “/as sysdba”

SQL*Plus: Release 10.2.0.1.0 – Production on Mon Jan 9 17:28:40 2012

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

Connected to an idle instance.

SQL> startup
ORACLE instance started.

Total System Global Area 369098752 bytes
Fixed Size 1219496 bytes
Variable Size 67109976 bytes
Database Buffers 293601280 bytes
Redo Buffers 7168000 bytes
Database mounted.
Database opened.
SQL> show parameter control

NAME TYPE VALUE
———————————— ———– ——————————
control_file_record_keep_time integer 7
control_files string /u01/app/oracle/oradata/orcl/c
ontrol01.ctl, /u01/app/oracle/
oradata/orcl/control02.ctl, /u
01/app/oracle/oradata/orcl/con
trol03.ctl
SQL> archive log list
Database log mode No Archive Mode
Automatic archival Disabled
Archive destination USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence 9
Current log sequence 11
SQL> shut immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> exit
Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 – Production
With the Partitioning, OLAP and Data Mining options
[oracle@hodb001vtr dbs]$ rman
c
Recovery Manager: Release 10.2.0.1.0 – Production on Mon Jan 9 17:31:56 2012

Copyright (c) 1982, 2005, Oracle. All rights reserved.
on
RMAN> nect target /

connected to target database (not started)

RMAN> startup mount

Oracle instance started
database mounted

Total System Global Area 369098752 bytes

Fixed Size 1219496 bytes
Variable Size 67109976 bytes
Database Buffers 293601280 bytes
Redo Buffers 7168000 bytes

RMAN> list backup of controlfile
2> ;

using target database control file instead of recovery catalog

List of Backup Sets
===================

BS Key Type LV Size Device Type Elapsed Time Completion Time
——- —- — ———- ———– ———— —————
2 Full 6.80M DISK 00:00:03 12-NOV-11
BP Key: 2 Status: AVAILABLE Compressed: NO Tag: TAG20111112T103636
Piece Name: /u01/app/oracle/flash_recovery_area/ORCL/autobackup/2011_11_12/o1_mf_s_767010591_7cwt2pr3_.bkp
Control File Included: Ckp SCN: 653651 Ckp time: 12-NOV-11

BS Key Type LV Size Device Type Elapsed Time Completion Time
——- —- — ———- ———– ———— —————
4 Full 6.80M DISK 00:00:01 12-NOV-11
BP Key: 4 Status: AVAILABLE Compressed: NO Tag: TAG20111112T104521
Piece Name: /u01/app/oracle/flash_recovery_area/ORCL/autobackup/2011_11_12/o1_mf_s_767011343_7cwtm1ws_.bkp
Control File Included: Ckp SCN: 654147 Ckp time: 12-NOV-11
Removido controfiles, como só havia 1 backup do dia 12 de Novembro, como a instancia estava em no archive mode então não teve como aplicar os archives, segue restore realizado:
[oracle@hodb001vtr dbs]$ rman

Recovery Manager: Release 10.2.0.1.0 – Production on Mon Jan 9 17:37:09 2012

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

RMAN> connect target /

connected to target database (not started)

RMAN> startup nomount

Oracle instance started

Total System Global Area 369098752 bytes

Fixed Size 1219496 bytes
Variable Size 67109976 bytes
Database Buffers 293601280 bytes
Redo Buffers 7168000 bytes

RMAN> restore controlfile;

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

RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of restore command at 01/09/2012 17:37:31
RMAN-06563: control file or SPFILE must be restored using FROM AUTOBACKUP

RMAN> restore controlfile from ‘/u01/app/oracle/flash_recovery_area/ORCL/autobackup/2011_11_12/o1_mf_s_767010591_7cwt2pr3_.bkp’;

Starting restore at 09-JAN-12
using channel ORA_DISK_1

channel ORA_DISK_1: restoring control file
channel ORA_DISK_1: restore complete, elapsed time: 00:00:05
output filename=/u01/app/oracle/oradata/orcl/control01.ctl
output filename=/u01/app/oracle/oradata/orcl/control02.ctl
output filename=/u01/app/oracle/oradata/orcl/control03.ctl
Finished restore at 09-JAN-12

Com o controlfile restaurado montei o banco
RMAN> alter database mount;
database mounted

RMAN> alter database open
2> ;

RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of alter db command at 01/09/2012 17:39:13
ORA-01589: must use RESETLOGS or NORESETLOGS option for database open

RMAN> alter database open resetlogs
2> ;

RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of alter db command at 01/09/2012 17:39:27
ORA-01152: file 1 was not restored from a sufficiently old backup
ORA-01110: data file 1: ‘/u01/app/oracle/oradata/orcl/system01.dbf’

Neste caso como o banco estava em no archive mode ou seja, os datafiles também precisam ser restaurados por causa do sincronismo do ambiente, segue:

RMAN> restore database;

Starting restore at 09-JAN-12
Starting implicit crosscheck backup at 09-JAN-12
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=156 devtype=DISK
Crosschecked 1 objects
Finished implicit crosscheck backup at 09-JAN-12

Starting implicit crosscheck copy at 09-JAN-12
using channel ORA_DISK_1
Finished implicit crosscheck copy at 09-JAN-12

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

List of Cataloged Files
=======================
File Name: /u01/app/oracle/flash_recovery_area/ORCL/autobackup/2011_11_12/o1_mf_s_767011343_7cwtm1ws_.bkp
File Name: /u01/app/oracle/flash_recovery_area/ORCL/autobackup/2011_11_12/o1_mf_s_767010591_7cwt2pr3_.bkp

using channel ORA_DISK_1

channel ORA_DISK_1: starting datafile backupset restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
restoring datafile 00001 to /u01/app/oracle/oradata/orcl/system01.dbf
restoring datafile 00002 to /u01/app/oracle/oradata/orcl/undotbs01.dbf
restoring datafile 00003 to /u01/app/oracle/oradata/orcl/sysaux01.dbf
restoring datafile 00004 to /u01/app/oracle/oradata/orcl/users01.dbf
restoring datafile 00005 to /u01/app/oracle/oradata/orcl/example01.dbf
channel ORA_DISK_1: reading from backup piece /u01/app/oracle/flash_recovery_area/ORCL/backupset/2011_11_12/o1_mf_nnndf_TAG20111112T103355_7cwsxmwz_.bkp
channel ORA_DISK_1: restored backup piece 1
piece handle=/u01/app/oracle/flash_recovery_area/ORCL/backupset/2011_11_12/o1_mf_nnndf_TAG20111112T103355_7cwsxmwz_.bkp tag=TAG20111112T103355
channel ORA_DISK_1: restore complete, elapsed time: 00:01:55
Finished restore at 09-JAN-12

Não é necessário fazer o recover devido o banco não estar em archive mode, após o restore apenas abrir o banco

RMAN> alter database open resetlogs;
Database opened.

Abraço!