Laboratorios Recovery RMAN

CONDICIONES INICIALES

RMAN> backup database;

SQL> select * from v$instance;

INSTANCE_NUMBER INSTANCE_NAME
--------------- ----------------
HOST_NAME
----------------------------------------------------------------
VERSION STARTUP_ STATUS PAR THREAD# ARCHIVE LOG_SWITCH_WAIT
----------------- -------- ------------ --- ---------- ------- ---------------
LOGINS SHU DATABASE_STATUS INSTANCE_ROLE ACTIVE_ST BLO
---------- --- ----------------- ------------------ --------- ---
1 acme
oracle11g.localdomain
10.2.0.4.0 06/07/10 OPEN NO 1 STOPPED
ALLOWED NO ACTIVE PRIMARY_INSTANCE NORMAL NO


SQL> select name from v$datafile;

NAME
--------------------------------------------------------------------------------
/u01/app/oracle/oradata/acme/system01.dbf
/u01/app/oracle/oradata/acme/undotbs01.dbf
/u01/app/oracle/oradata/acme/sysaux01.dbf
/u01/app/oracle/oradata/acme/users01.dbf
/u01/app/oracle/oradata/acme/example01.dbf


SQL> select name from v$controlfile;

NAME
--------------------------------------------------------------------------------
/u01/app/oracle/oradata/acme/control01.ctl
/u01/app/oracle/oradata/acme/control02.ctl
/u01/app/oracle/oradata/acme/control03.ctl

SQL> select member from v$logfile;

MEMBER
--------------------------------------------------------------------------------
/u01/app/oracle/oradata/acme/redo03.log
/u01/app/oracle/oradata/acme/redo02.log
/u01/app/oracle/oradata/acme/redo01.log


COLOCAR INSTANCIA EN MODE ARCHIVELOG

SQL> shutdown immediate;
Base de datos cerrada.
Base de datos desmontada.
Instancia ORACLE cerrada.
SQL>

SQL> startup mount;
Instancia ORACLE iniciada.

Total System Global Area 285212672 bytes
Fixed Size 1267068 bytes
Variable Size 92277380 bytes
Database Buffers 188743680 bytes
Redo Buffers 2924544 bytes
Base de datos montada.
SQL>
SQL> alter database archivelog;

Base de datos modificada.

SQL> alter database open;

Base de datos modificada.

SQL> archive log list;
Modo log de la base de datos Modo de Archivado
Archivado automatico Activado
Destino del archivo USE_DB_RECOVERY_FILE_DEST
Secuencia de log en linea mas antigua 1
Siguiente secuencia de log para archivar 2
Secuencia de log actual 2

SQL> show parameter db_recovery;

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_recovery_file_dest string /u01/app/oracle/flash_recovery
_area
db_recovery_file_dest_size big integer 5G
SQL>


SQL> alter system switch logfile;

Sistema modificado.

SQL> archive log list;
Modo log de la base de datos Modo de Archivado
Archivado automatico Activado
Destino del archivo USE_DB_RECOVERY_FILE_DEST
Secuencia de log en linea mas antigua 1
Siguiente secuencia de log para archivar 3
Secuencia de log actual 3
SQL>

CREAR ARCHIVO DE PARAMETROS

SQL> create pfile from spfile;

Archivo creado.

SQL> exit
Desconectado de Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
[oracle@oracle11g ~]$ cd $ORACLE_HOME/dbs
[oracle@oracle11g dbs]$ pwd
/u01/app/oracle/product/10.2.0/db/dbs
[oracle@oracle11g dbs]$ ls
hc_acme.dat initdw.ora lkACME spfileacme.ora
initacme.ora init.ora orapwacme
[oracle@oracle11g dbs]$ ls -ltr
total 76
-rw-r----- 1 oracle oinstall 8385 sep 11 1998 init.ora
-rw-r--r-- 1 oracle oinstall 12920 may 3 2001 initdw.ora
-rw-rw---- 1 oracle oinstall 1544 jul 6 09:48 hc_acme.dat
-rw-rw---- 1 oracle oinstall 24 jul 6 09:49 lkACME
-rw-r----- 1 oracle oinstall 1536 jul 6 09:53 orapwacme
-rw-r----- 1 oracle oinstall 3584 jul 6 10:04 spfileacme.ora
-rw-r--r-- 1 oracle oinstall 1058 jul 6 10:15 initacme.ora
[oracle@oracle11g dbs]$

SCRIPT PARA REALIZAR COPIA DE SEGURIDAD FISICA DATAFILES

set head off
set feed off echo off head off trimspool on line 500
spool copia.sh
select 'cp ' || name || ' /u01/copia' from v$datafile
union all
select 'cp ' || name || ' /u01/copia' from v$controlfile
union all
select 'cp ' || member || ' /u01/copia' from v$logfile
/
spool off
set feed on


Conectado a:
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> start copia_todo.sql

cp /u01/app/oracle/oradata/acme/system01.dbf /u01/copia
cp /u01/app/oracle/oradata/acme/undotbs01.dbf /u01/copia
cp /u01/app/oracle/oradata/acme/sysaux01.dbf /u01/copia
cp /u01/app/oracle/oradata/acme/users01.dbf /u01/copia
cp /u01/app/oracle/oradata/acme/example01.dbf /u01/copia
cp /u01/app/oracle/oradata/acme/control01.ctl /u01/copia
cp /u01/app/oracle/oradata/acme/control02.ctl /u01/copia
cp /u01/app/oracle/oradata/acme/control03.ctl /u01/copia
cp /u01/app/oracle/oradata/acme/redo03.log /u01/copia
cp /u01/app/oracle/oradata/acme/redo02.log /u01/copia
cp /u01/app/oracle/oradata/acme/redo01.log /u01/copia

SQL*Plus: Release 10.2.0.4.0 - Production on Tue Jul 6 10:30:44 2010

Copyright (c) 1982, 2007, Oracle. All Rights Reserved.


Conectado a:
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> shutdown immediate;

[oracle@oracle11g copia]$ ls -ltr
total 1045864
-rw-r----- 1 oracle oinstall 503324672 jul 6 10:32 system01.dbf
-rw-r----- 1 oracle oinstall 26222592 jul 6 10:33 undotbs01.dbf
-rw-r----- 1 oracle oinstall 251666432 jul 6 10:33 sysaux01.dbf
-rw-r----- 1 oracle oinstall 5251072 jul 6 10:33 users01.dbf
-rw-r----- 1 oracle oinstall 104865792 jul 6 10:33 example01.dbf
-rw-r----- 1 oracle oinstall 7061504 jul 6 10:33 control02.ctl
-rw-r----- 1 oracle oinstall 7061504 jul 6 10:33 control01.ctl
-rw-r----- 1 oracle oinstall 7061504 jul 6 10:33 control03.ctl
-rw-r----- 1 oracle oinstall 52429312 jul 6 10:34 redo03.log
-rw-r----- 1 oracle oinstall 52429312 jul 6 10:34 redo02.log
-rw-r----- 1 oracle oinstall 52429312 jul 6 10:34 redo01.log
[oracle@oracle11g copia]$
[oracle@oracle11g copia]$ sqlplus '/as sysdba'

SQL*Plus: Release 10.2.0.4.0 - Production on Tue Jul 6 11:14:01 2010

Copyright (c) 1982, 2007, Oracle. All Rights Reserved.

Connected to an idle instance.

SQL> startup
ORACLE instance started.

Total System Global Area 285212672 bytes
Fixed Size 1267068 bytes
Variable Size 92277380 bytes
Database Buffers 188743680 bytes
Redo Buffers 2924544 bytes
Base de datos montada.
Base de datos abierta.
SQL>
SQL> exit
Desconectado de Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
[oracle@oracle11g copia]$
[oracle@oracle11g copia]$
[oracle@oracle11g copia]$ rman target /

Recovery Manager: Release 10.2.0.4.0 - Production on Tue Jul 6 11:14:39 2010

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

connected to target database: ACME (DBID=1908099333)

RMAN> list backup summary;

using target database control file instead of recovery catalog


[oracle@oracle11g copia]$ sqlplus '/as sysdba'

SQL*Plus: Release 10.2.0.4.0 - Production on Tue Jul 6 11:18:20 2010

Copyright (c) 1982, 2007, Oracle. All Rights Reserved.


Conectado a:
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> alter user hr account unlock;

Usuario modificado.

SQL> connect hr/hr;
Conectado.
SQL>
SQL> select * from tab;

TNAME TABTYPE CLUSTERID
------------------------------ ------- ----------
REGIONS TABLE
COUNTRIES TABLE
LOCATIONS TABLE
DEPARTMENTS TABLE
JOBS TABLE
EMPLOYEES TABLE
JOB_HISTORY TABLE
EMP_DETAILS_VIEW VIEW

8 filas seleccionadas.

SQL> desc regions;
Nombre ?Nulo? Tipo
----------------------------------------- -------- ----------------------------
REGION_ID NOT NULL NUMBER
REGION_NAME VARCHAR2(25)

SQL> select count(*) from regions;

COUNT(*)
----------
4

SQL> insert into regions values(5,'CUNDINAMARCA');

1 fila creada.

SQL> commit;

Confirmacion terminada.

SQL> select * from regions;

REGION_ID REGION_NAME
---------- -------------------------
5 CUNDINAMARCA
1 Europe
2 Americas
3 Asia
4 Middle East and Africa

Conectado a:
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> alter system switch logfile;

Sistema modificado.

SQL>

Recovery Manager: Release 10.2.0.4.0 - Production on Tue Jul 6 11:21:24 2010

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

connected to target database: ACME (DBID=1908099333)

RMAN> backup archivelog all;

Starting backup at 06-JUL-10
current log archived
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=205 devtype=DISK
channel ORA_DISK_1: starting archive log backupset
channel ORA_DISK_1: specifying archive log(s) in backup set
input archive log thread=1 sequence=2 recid=1 stamp=723636781
input archive log thread=1 sequence=3 recid=2 stamp=723640844
input archive log thread=1 sequence=4 recid=3 stamp=723640892
channel ORA_DISK_1: starting piece 1 at 06-JUL-10
channel ORA_DISK_1: finished piece 1 at 06-JUL-10
piece handle=/u01/app/oracle/flash_recovery_area/ACME/backupset/2010_07_06/o1_mf_annnn_TAG20100706T112133_636p0h6k_.bkp tag=TAG20100706T112133 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:04
Finished backup at 06-JUL-10

RMAN> list backup summary;


List of Backups
===============
Key TY LV S Device Type Completion Time #Pieces #Copies Compressed Tag
------- -- -- - ----------- --------------- ------- ------- ---------- ---
1 B F A DISK 06-JUL-10 1 1 NO TAG20100706T111529
2 B F A DISK 06-JUL-10 1 1 NO TAG20100706T111529
3 B A A DISK 06-JUL-10 1 1 NO TAG20100706T112133

RMAN> list backupset 1;


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

BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
1 Full 581.77M DISK 00:01:25 06-JUL-10
BP Key: 1 Status: AVAILABLE Compressed: NO Tag: TAG20100706T111529
Piece Name: /u01/app/oracle/flash_recovery_area/ACME/backupset/2010_07_06/o1_mf_nnndf_TAG20100706T111529_636oo2gs_.bkp
List of Datafiles in backup set 1
File LV Type Ckp SCN Ckp Time Name
---- -- ---- ---------- --------- ----
1 Full 499458 06-JUL-10 /u01/app/oracle/oradata/acme/system01.dbf
2 Full 499458 06-JUL-10 /u01/app/oracle/oradata/acme/undotbs01.dbf
3 Full 499458 06-JUL-10 /u01/app/oracle/oradata/acme/sysaux01.dbf
4 Full 499458 06-JUL-10 /u01/app/oracle/oradata/acme/users01.dbf
5 Full 499458 06-JUL-10 /u01/app/oracle/oradata/acme/example01.dbf

RMAN> list backupset 2;


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

BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
2 Full 6.80M DISK 00:00:02 06-JUL-10
BP Key: 2 Status: AVAILABLE Compressed: NO Tag: TAG20100706T111529
Piece Name: /u01/app/oracle/flash_recovery_area/ACME/backupset/2010_07_06/o1_mf_ncsnf_TAG20100706T111529_636or3gl_.bkp
Control File Included: Ckp SCN: 499497 Ckp time: 06-JUL-10
SPFILE Included: Modification time: 06-JUL-10

RMAN>

RMAN> list backupset 3;


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

BS Key Size Device Type Elapsed Time Completion Time
------- ---------- ----------- ------------ ---------------
3 7.45M DISK 00:00:02 06-JUL-10
BP Key: 3 Status: AVAILABLE Compressed: NO Tag: TAG20100706T112133
Piece Name: /u01/app/oracle/flash_recovery_area/ACME/backupset/2010_07_06/o1_mf_annnn_TAG20100706T112133_636p0h6k_.bkp

List of Archived Logs in backup set 3
Thrd Seq Low SCN Low Time Next SCN Next Time
---- ------- ---------- --------- ---------- ---------
1 2 494642 06-JUL-10 498441 06-JUL-10
1 3 498441 06-JUL-10 499609 06-JUL-10
1 4 499609 06-JUL-10 499632 06-JUL-10

RMAN>

NOTA: CON LO ANTERIOR YA NOS PODEMOS TIRAR LA BD

1. LABORATORIO BORRAR datafile temp01.dbf

NOTA: NO PASA NADA

[oracle@oracle11g acme]$ ls -ltr
total 1063364
-rw-r----- 1 oracle oinstall 20979712 jul 6 09:51 temp01.dbf
-rw-r----- 1 oracle oinstall 5251072 jul 6 11:15 users01.dbf
-rw-r----- 1 oracle oinstall 104865792 jul 6 11:15 example01.dbf
-rw-r----- 1 oracle oinstall 52429312 jul 6 11:20 redo03.log
-rw-r----- 1 oracle oinstall 251666432 jul 6 11:21 sysaux01.dbf
-rw-r----- 1 oracle oinstall 52429312 jul 6 11:21 redo01.log
-rw-r----- 1 oracle oinstall 26222592 jul 6 11:23 undotbs01.dbf
-rw-r----- 1 oracle oinstall 503324672 jul 6 11:23 system01.dbf
-rw-r----- 1 oracle oinstall 52429312 jul 6 11:23 redo02.log
-rw-r----- 1 oracle oinstall 7061504 jul 6 11:23 control03.ctl
-rw-r----- 1 oracle oinstall 7061504 jul 6 11:23 control02.ctl
-rw-r----- 1 oracle oinstall 7061504 jul 6 11:23 control01.ctl
[oracle@oracle11g acme]$ rm -r temp01.dbf
[oracle@oracle11g acme]$

SQL> select * from v$instance;

INSTANCE_NUMBER INSTANCE_NAME
--------------- ----------------
HOST_NAME
----------------------------------------------------------------
VERSION STARTUP_ STATUS PAR THREAD# ARCHIVE LOG_SWITCH_WAIT
----------------- -------- ------------ --- ---------- ------- ---------------
LOGINS SHU DATABASE_STATUS INSTANCE_ROLE ACTIVE_ST BLO
---------- --- ----------------- ------------------ --------- ---
1 acme
oracle11g.localdomain
10.2.0.4.0 06/07/10 OPEN NO 1 STARTED
ALLOWED NO ACTIVE PRIMARY_INSTANCE NORMAL NO


SQL> select name from v$tempfile;

NAME
--------------------------------------------------------------------------------
/u01/app/oracle/oradata/acme/temp01.dbf

SQL>

SQL> select name from v$tempfile;

NAME
--------------------------------------------------------------------------------
/u01/app/oracle/oradata/acme/temp01.dbf

SQL> shutdown immediate;
Base de datos cerrada.
Base de datos desmontada.
Instancia ORACLE cerrada.
SQL>

SQL> ! ls /u01/app/oracle/oradata/acme/
control01.ctl control02.ctl control03.ctl example01.dbf redo01.log redo02.log redo03.log sysaux01.dbf system01.dbf undotbs01.dbf users01.dbf

SQL>

SQL> ! ls /u01/app/oracle/oradata/acme/
control01.ctl control02.ctl control03.ctl example01.dbf redo01.log redo02.log redo03.log sysaux01.dbf system01.dbf undotbs01.dbf users01.dbf

SQL> startup
Instancia ORACLE iniciada.

Total System Global Area 285212672 bytes
Fixed Size 1267068 bytes
Variable Size 92277380 bytes
Database Buffers 188743680 bytes
Redo Buffers 2924544 bytes
Base de datos montada.
Base de datos abierta.
SQL> ! ls /u01/app/oracle/oradata/acme/
control01.ctl control03.ctl redo01.log redo03.log system01.dbf undotbs01.dbf
control02.ctl example01.dbf redo02.log sysaux01.dbf temp01.dbf users01.dbf

SQL>

2. LABORATORIO BORRAR datafile /u01/app/oracle/oradata/acme/users01.dbf

SQL> select name from v$datafile;

NAME
--------------------------------------------------------------------------------
/u01/app/oracle/oradata/acme/system01.dbf
/u01/app/oracle/oradata/acme/undotbs01.dbf
/u01/app/oracle/oradata/acme/sysaux01.dbf
/u01/app/oracle/oradata/acme/users01.dbf
/u01/app/oracle/oradata/acme/example01.dbf

SQL> !
[oracle@oracle11g copia]$ cd /u01/app/oracle/oradata/acme/
[oracle@oracle11g acme]$ rm users01.dbf
[oracle@oracle11g acme]$ exit
exit

SQL> select name from v$datafile;

NAME
--------------------------------------------------------------------------------
/u01/app/oracle/oradata/acme/system01.dbf
/u01/app/oracle/oradata/acme/undotbs01.dbf
/u01/app/oracle/oradata/acme/sysaux01.dbf
/u01/app/oracle/oradata/acme/users01.dbf
/u01/app/oracle/oradata/acme/example01.dbf

SQL>

SQL> desc dba_users;
Nombre ?Nulo? Tipo
----------------------------------------- -------- ----------------------------
USERNAME NOT NULL VARCHAR2(30)
USER_ID NOT NULL NUMBER
PASSWORD VARCHAR2(30)
ACCOUNT_STATUS NOT NULL VARCHAR2(32)
LOCK_DATE DATE
EXPIRY_DATE DATE
DEFAULT_TABLESPACE NOT NULL VARCHAR2(30)
TEMPORARY_TABLESPACE NOT NULL VARCHAR2(30)
CREATED NOT NULL DATE
PROFILE NOT NULL VARCHAR2(30)
INITIAL_RSRC_CONSUMER_GROUP VARCHAR2(30)
EXTERNAL_NAME VARCHAR2(4000)

SQL>
SQL> select username,default_tablespace from dba_users where username='HR';

USERNAME DEFAULT_TABLESPACE
------------------------------ ------------------------------
HR USERS

SQL>

Copyright (c) 1982, 2007, Oracle. All Rights Reserved.


Conectado a:
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> select * from tab;

TNAME TABTYPE CLUSTERID
------------------------------ ------- ----------
REGIONS TABLE
COUNTRIES TABLE
LOCATIONS TABLE
DEPARTMENTS TABLE
JOBS TABLE
EMPLOYEES TABLE
JOB_HISTORY TABLE
EMP_DETAILS_VIEW VIEW

8 filas seleccionadas.

SQL> create table SUPPLIERS ( id number(10), description varchar2(40));
create table SUPPLIERS ( id number(10), description varchar2(40))
*
ERROR en linea 1:
ORA-01116: error al abrir el archivo de base de datos 4
ORA-01110: archivo de datos 4: '/u01/app/oracle/oradata/acme/users01.dbf'
ORA-27041: no se ha podido abrir el archivo
Linux Error: 2: No such file or directory
Additional information: 3

PROBAR LO SIGUIENTE CON LA INSTANCIA ARRIBA (ESTO ES UN ERROR A PROPOSITO)

NOTA; NO VA DEJAR RESTAURAR EL DATAFILE

Recovery Manager: Release 10.2.0.4.0 - Production on Tue Jul 6 11:41:07 2010

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

connected to target database: ACME (DBID=1908099333)

RMAN> report schema;

using target database control file instead of recovery catalog
Report of database schema

List of Permanent Datafiles
===========================
File Size(MB) Tablespace RB segs Datafile Name
---- -------- -------------------- ------- ------------------------
1 480 SYSTEM *** /u01/app/oracle/oradata/acme/system01.dbf
2 25 UNDOTBS1 *** /u01/app/oracle/oradata/acme/undotbs01.dbf
3 240 SYSAUX *** /u01/app/oracle/oradata/acme/sysaux01.dbf
4 0 USERS *** /u01/app/oracle/oradata/acme/users01.dbf
5 100 EXAMPLE *** /u01/app/oracle/oradata/acme/example01.dbf

List of Temporary Files
=======================
File Size(MB) Tablespace Maxsize(MB) Tempfile Name
---- -------- -------------------- ----------- --------------------
1 20 TEMP 32767 /u01/app/oracle/oradata/acme/temp01.dbf

RMAN> restore datafile users;

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: "double-quoted-string, integer, single-quoted-string"
RMAN-01008: the bad identifier was: users
RMAN-01007: at line 1 column 18 file: standard input

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

RMAN> restore datafile 4;

Starting restore at 06-JUL-10
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=199 devtype=DISK

channel ORA_DISK_1: starting datafile backupset restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
restoring datafile 00004 to /u01/app/oracle/oradata/acme/users01.dbf
channel ORA_DISK_1: reading from backup piece /u01/app/oracle/flash_recovery_area/ACME/backupset/2010_07_06/o1_mf_nnndf_TAG20100706T111529_636oo2gs_.bkp
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of restore command at 07/06/2010 11:42:33
ORA-19870: error al leer parte de la copia de seguridad /u01/app/oracle/flash_recovery_area/ACME/backupset/2010_07_06/o1_mf_nnndf_TAG20100706T111529_636oo2gs_.bkp
ORA-19573: no se puede obtener la cola exclusive para el archivo de datos 4

RMAN>

AHORA SI PROBAMOS LA RESTAURACION BAJANDO LA INSTANCIA


SQL> select * from v$instance;

INSTANCE_NUMBER INSTANCE_NAME
--------------- ----------------
HOST_NAME
----------------------------------------------------------------
VERSION STARTUP_ STATUS PAR THREAD# ARCHIVE LOG_SWITCH_WAIT
----------------- -------- ------------ --- ---------- ------- ---------------
LOGINS SHU DATABASE_STATUS INSTANCE_ROLE ACTIVE_ST BLO
---------- --- ----------------- ------------------ --------- ---
1 acme
oracle11g.localdomain
10.2.0.4.0 06/07/10 OPEN NO 1 STARTED
ALLOWED NO ACTIVE PRIMARY_INSTANCE NORMAL NO


SQL> shutdown immediate;
ORA-01116: error al abrir el archivo de base de datos 4
ORA-01110: archivo de datos 4: '/u01/app/oracle/oradata/acme/users01.dbf'
ORA-27041: no se ha podido abrir el archivo
Linux Error: 2: No such file or directory
Additional information: 3
SQL>
SQL> shutdown abort;
Instancia ORACLE cerrada.
SQL>
SQL> startup mount;
Instancia ORACLE iniciada.

Total System Global Area 285212672 bytes
Fixed Size 1267068 bytes
Variable Size 92277380 bytes
Database Buffers 188743680 bytes
Redo Buffers 2924544 bytes
Base de datos montada.
SQL>

[oracle@oracle11g copia]$ rman target /

Recovery Manager: Release 10.2.0.4.0 - Production on Tue Jul 6 11:45:26 2010

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

connected to target database: ACME (DBID=1908099333, not open)

RMAN> report schema;

using target database control file instead of recovery catalog
Report of database schema

List of Permanent Datafiles
===========================
File Size(MB) Tablespace RB segs Datafile Name
---- -------- -------------------- ------- ------------------------
1 480 SYSTEM *** /u01/app/oracle/oradata/acme/system01.dbf
2 25 UNDOTBS1 *** /u01/app/oracle/oradata/acme/undotbs01.dbf
3 240 SYSAUX *** /u01/app/oracle/oradata/acme/sysaux01.dbf
4 0 USERS *** /u01/app/oracle/oradata/acme/users01.dbf
5 100 EXAMPLE *** /u01/app/oracle/oradata/acme/example01.dbf

List of Temporary Files
=======================
File Size(MB) Tablespace Maxsize(MB) Tempfile Name
---- -------- -------------------- ----------- --------------------
1 20 TEMP 32767 /u01/app/oracle/oradata/acme/temp01.dbf

RMAN> restore datafile 4;

Starting restore at 06-JUL-10
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=211 devtype=DISK

channel ORA_DISK_1: starting datafile backupset restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
restoring datafile 00004 to /u01/app/oracle/oradata/acme/users01.dbf
channel ORA_DISK_1: reading from backup piece /u01/app/oracle/flash_recovery_area/ACME/backupset/2010_07_06/o1_mf_nnndf_TAG20100706T111529_636oo2gs_.bkp
channel ORA_DISK_1: restored backup piece 1
piece handle=/u01/app/oracle/flash_recovery_area/ACME/backupset/2010_07_06/o1_mf_nnndf_TAG20100706T111529_636oo2gs_.bkp tag=TAG20100706T111529
channel ORA_DISK_1: restore complete, elapsed time: 00:00:01
Finished restore at 06-JUL-10

RMAN>

[oracle@oracle11g acme]$ ls -ltr
total 1045944
-rw-r----- 1 oracle oinstall 52429312 jul 6 11:29 redo03.log
-rw-r----- 1 oracle oinstall 52429312 jul 6 11:29 redo01.log
-rw-r----- 1 oracle oinstall 104865792 jul 6 11:29 example01.dbf
-rw-r----- 1 oracle oinstall 20979712 jul 6 11:29 temp01.dbf
-rw-r----- 1 oracle oinstall 26222592 jul 6 11:44 undotbs01.dbf
-rw-r----- 1 oracle oinstall 503324672 jul 6 11:44 system01.dbf
-rw-r----- 1 oracle oinstall 251666432 jul 6 11:44 sysaux01.dbf
-rw-r----- 1 oracle oinstall 52429312 jul 6 11:44 redo02.log
-rw-r----- 1 oracle oinstall 5251072 jul 6 11:45 users01.dbf
-rw-r----- 1 oracle oinstall 7061504 jul 6 11:46 control03.ctl
-rw-r----- 1 oracle oinstall 7061504 jul 6 11:46 control02.ctl
-rw-r----- 1 oracle oinstall 7061504 jul 6 11:46 control01.ctl
[oracle@oracle11g acme]$

RMAN> report schema;

using target database control file instead of recovery catalog
Report of database schema

List of Permanent Datafiles
===========================
File Size(MB) Tablespace RB segs Datafile Name
---- -------- -------------------- ------- ------------------------
1 480 SYSTEM *** /u01/app/oracle/oradata/acme/system01.dbf
2 25 UNDOTBS1 *** /u01/app/oracle/oradata/acme/undotbs01.dbf
3 240 SYSAUX *** /u01/app/oracle/oradata/acme/sysaux01.dbf
4 0 USERS *** /u01/app/oracle/oradata/acme/users01.dbf
5 100 EXAMPLE *** /u01/app/oracle/oradata/acme/example01.dbf

List of Temporary Files
=======================
File Size(MB) Tablespace Maxsize(MB) Tempfile Name
---- -------- -------------------- ----------- --------------------
1 20 TEMP 32767 /u01/app/oracle/oradata/acme/temp01.dbf

RMAN> restore datafile 4;

Starting restore at 06-JUL-10
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=211 devtype=DISK

channel ORA_DISK_1: starting datafile backupset restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
restoring datafile 00004 to /u01/app/oracle/oradata/acme/users01.dbf
channel ORA_DISK_1: reading from backup piece /u01/app/oracle/flash_recovery_area/ACME/backupset/2010_07_06/o1_mf_nnndf_TAG20100706T111529_636oo2gs_.bkp
channel ORA_DISK_1: restored backup piece 1
piece handle=/u01/app/oracle/flash_recovery_area/ACME/backupset/2010_07_06/o1_mf_nnndf_TAG20100706T111529_636oo2gs_.bkp tag=TAG20100706T111529
channel ORA_DISK_1: restore complete, elapsed time: 00:00:01
Finished restore at 06-JUL-10

RMAN> alter database open;

RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of alter db command at 07/06/2010 11:47:04
ORA-01113: el archivo 4 necesita recuperacion del medio fisico
ORA-01110: archivo de datos 4: '/u01/app/oracle/oradata/acme/users01.dbf'

RMAN> recover database;

Starting recover at 06-JUL-10
using channel ORA_DISK_1

starting media recovery
media recovery complete, elapsed time: 00:00:02

Finished recover at 06-JUL-10

RMAN> alter database open;

database opened

RMAN>

Recovery Manager complete.
[oracle@oracle11g copia]$ sqlplus '/as sysdba'

SQL*Plus: Release 10.2.0.4.0 - Production on Tue Jul 6 11:50:32 2010

Copyright (c) 1982, 2007, Oracle. All Rights Reserved.


Conectado a:
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> select * from v$instance;

INSTANCE_NUMBER INSTANCE_NAME
--------------- ----------------
HOST_NAME
----------------------------------------------------------------
VERSION STARTUP_ STATUS PAR THREAD# ARCHIVE LOG_SWITCH_WAIT
----------------- -------- ------------ --- ---------- ------- ---------------
LOGINS SHU DATABASE_STATUS INSTANCE_ROLE ACTIVE_ST BLO
---------- --- ----------------- ------------------ --------- ---
1 acme
oracle11g.localdomain
10.2.0.4.0 06/07/10 OPEN NO 1 STARTED
ALLOWED NO ACTIVE PRIMARY_INSTANCE NORMAL NO


SQL> select name from v$datafile;

NAME
--------------------------------------------------------------------------------
/u01/app/oracle/oradata/acme/system01.dbf
/u01/app/oracle/oradata/acme/undotbs01.dbf
/u01/app/oracle/oradata/acme/sysaux01.dbf
/u01/app/oracle/oradata/acme/users01.dbf
/u01/app/oracle/oradata/acme/example01.dbf

SQL> connect hr/hr;
Conectado.
SQL> create table SUPPLIERS ( id number(10), description varchar2(40));

Tabla creada.

SQL>

3. LABORATORIO TABLESPACES


SQL> select tablespace_name from dba_tablespaces;

TABLESPACE_NAME
------------------------------
SYSTEM
UNDOTBS1
SYSAUX
TEMP
USERS
EXAMPLE

6 filas seleccionadas.

SQL>

[oracle@oracle11g acme]$ sqlplus '/as sysdba'

SQL*Plus: Release 10.2.0.4.0 - Production on Tue Jul 6 15:13:16 2010

Copyright (c) 1982, 2007, Oracle. All Rights Reserved.


Conectado a:
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> select username,default_tablespace from dba_users where username='HR';

USERNAME DEFAULT_TABLESPACE
------------------------------ ------------------------------
HR USERS

SQL> connect hr/hr;
Conectado.
SQL> create table SUPPLIERS ( id number(10), description varchar2(40));
create table SUPPLIERS ( id number(10), description varchar2(40))
*
ERROR en linea 1:
ORA-01116: error al abrir el archivo de base de datos 4
ORA-01110: archivo de datos 4: '/u01/app/oracle/oradata/acme/users01.dbf'
ORA-27041: no se ha podido abrir el archivo
Linux Error: 2: No such file or directory
Additional information: 3


[oracle@oracle11g acme]$ rman target /

Recovery Manager: Release 10.2.0.4.0 - Production on Tue Jul 6 15:15:28 2010

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

connected to target database: ACME (DBID=1908099333)

RMAN> report schema;

using target database control file instead of recovery catalog
Report of database schema

List of Permanent Datafiles
===========================
File Size(MB) Tablespace RB segs Datafile Name
---- -------- -------------------- ------- ------------------------
1 480 SYSTEM *** /u01/app/oracle/oradata/acme/system01.dbf
2 25 UNDOTBS1 *** /u01/app/oracle/oradata/acme/undotbs01.dbf
3 240 SYSAUX *** /u01/app/oracle/oradata/acme/sysaux01.dbf
4 0 USERS *** /u01/app/oracle/oradata/acme/users01.dbf
5 100 EXAMPLE *** /u01/app/oracle/oradata/acme/example01.dbf

List of Temporary Files
=======================
File Size(MB) Tablespace Maxsize(MB) Tempfile Name
---- -------- -------------------- ----------- --------------------
1 20 TEMP 32767 /u01/app/oracle/oradata/acme/temp01.dbf


VER LOS ERRORES POR NO ESTAR EN MODE MOUNT

RMAN> restore tablespace users;

Starting restore at 06-JUL-10
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=201 devtype=DISK

channel ORA_DISK_1: starting datafile backupset restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
restoring datafile 00004 to /u01/app/oracle/oradata/acme/users01.dbf
channel ORA_DISK_1: reading from backup piece /u01/app/oracle/flash_recovery_area/ACME/backupset/2010_07_06/o1_mf_nnndf_TAG20100706T111529_636oo2gs_.bkp
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of restore command at 07/06/2010 15:16:11
ORA-19870: error al leer parte de la copia de seguridad /u01/app/oracle/flash_recovery_area/ACME/backupset/2010_07_06/o1_mf_nnndf_TAG20100706T111529_636oo2gs_.bkp
ORA-19573: no se puede obtener la cola exclusive para el archivo de datos 4

RMAN>


SQL> shutdown immediate;
ORA-01116: error al abrir el archivo de base de datos 4
ORA-01110: archivo de datos 4: '/u01/app/oracle/oradata/acme/users01.dbf'
ORA-27041: no se ha podido abrir el archivo
Linux Error: 2: No such file or directory
Additional information: 3
SQL>
SQL> shutdown abort;
Instancia ORACLE cerrada.
SQL>
SQL>
SQL> startup mount;
Instancia ORACLE iniciada.

Total System Global Area 285212672 bytes
Fixed Size 1267068 bytes
Variable Size 92277380 bytes
Database Buffers 188743680 bytes
Redo Buffers 2924544 bytes
Base de datos montada.
SQL> exit
Desconectado de Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
[oracle@oracle11g acme]$
[oracle@oracle11g acme]$ rman target /

[oracle@oracle11g acme]$ rman target /

Recovery Manager: Release 10.2.0.4.0 - Production on Tue Jul 6 15:18:44 2010

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

connected to target database: ACME (DBID=1908099333, not open)

RMAN> report schema;

using target database control file instead of recovery catalog
Report of database schema

List of Permanent Datafiles
===========================
File Size(MB) Tablespace RB segs Datafile Name
---- -------- -------------------- ------- ------------------------
1 480 SYSTEM *** /u01/app/oracle/oradata/acme/system01.dbf
2 25 UNDOTBS1 *** /u01/app/oracle/oradata/acme/undotbs01.dbf
3 240 SYSAUX *** /u01/app/oracle/oradata/acme/sysaux01.dbf
4 0 USERS *** /u01/app/oracle/oradata/acme/users01.dbf
5 100 EXAMPLE *** /u01/app/oracle/oradata/acme/example01.dbf

List of Temporary Files
=======================
File Size(MB) Tablespace Maxsize(MB) Tempfile Name
---- -------- -------------------- ----------- --------------------
1 20 TEMP 32767 /u01/app/oracle/oradata/acme/temp01.dbf

RMAN> restore tablespace users;

Starting restore at 06-JUL-10
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=211 devtype=DISK

channel ORA_DISK_1: starting datafile backupset restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
restoring datafile 00004 to /u01/app/oracle/oradata/acme/users01.dbf
channel ORA_DISK_1: reading from backup piece /u01/app/oracle/flash_recovery_area/ACME/backupset/2010_07_06/o1_mf_nnndf_TAG20100706T111529_636oo2gs_.bkp
channel ORA_DISK_1: restored backup piece 1
piece handle=/u01/app/oracle/flash_recovery_area/ACME/backupset/2010_07_06/o1_mf_nnndf_TAG20100706T111529_636oo2gs_.bkp tag=TAG20100706T111529
channel ORA_DISK_1: restore complete, elapsed time: 00:00:01
Finished restore at 06-JUL-10

RMAN> alter database open;

RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of alter db command at 07/06/2010 15:19:38
ORA-01113: el archivo 4 necesita recuperacion del medio fisico
ORA-01110: archivo de datos 4: '/u01/app/oracle/oradata/acme/users01.dbf'

RMAN> recover database;

Starting recover at 06-JUL-10
using channel ORA_DISK_1

starting media recovery

archive log thread 1 sequence 3 is already on disk as file /u01/app/oracle/flash_recovery_area/ACME/archivelog/2010_07_06/o1_mf_1_3_636oyw60_.arc
archive log thread 1 sequence 4 is already on disk as file /u01/app/oracle/flash_recovery_area/ACME/archivelog/2010_07_06/o1_mf_1_4_636p0d5o_.arc
archive log thread 1 sequence 5 is already on disk as file /u01/app/oracle/flash_recovery_area/ACME/archivelog/2010_07_06/o1_mf_1_5_636qno97_.arc
archive log thread 1 sequence 6 is already on disk as file /u01/app/oracle/flash_recovery_area/ACME/archivelog/2010_07_06/o1_mf_1_6_6372gws3_.arc
archive log filename=/u01/app/oracle/flash_recovery_area/ACME/archivelog/2010_07_06/o1_mf_1_3_636oyw60_.arc thread=1 sequence=3
archive log filename=/u01/app/oracle/flash_recovery_area/ACME/archivelog/2010_07_06/o1_mf_1_4_636p0d5o_.arc thread=1 sequence=4
media recovery complete, elapsed time: 00:00:04
Finished recover at 06-JUL-10

RMAN> alter database open;

database opened

RMAN>

4 LABORATORIO BORRAR TABLESPACE SYSTEM


[oracle@oracle11g acme]$ rm system01.dbf
[oracle@oracle11g acme]$ sqlplus '/as sysdba'

SQL*Plus: Release 10.2.0.4.0 - Production on Tue Jul 6 15:27:32 2010

Copyright (c) 1982, 2007, Oracle. All Rights Reserved.


Conectado a:
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL>
SQL> select * from v$instance;

INSTANCE_NUMBER INSTANCE_NAME
--------------- ----------------
HOST_NAME
----------------------------------------------------------------
VERSION STARTUP_ STATUS PAR THREAD# ARCHIVE LOG_SWITCH_WAIT
----------------- -------- ------------ --- ---------- ------- ---------------
LOGINS SHU DATABASE_STATUS INSTANCE_ROLE ACTIVE_ST BLO
---------- --- ----------------- ------------------ --------- ---
1 acme
oracle11g.localdomain
10.2.0.4.0 06/07/10 OPEN NO 1 STARTED
ALLOWED NO ACTIVE PRIMARY_INSTANCE NORMAL NO


SQL> select name from v$datafile;

NAME
--------------------------------------------------------------------------------
/u01/app/oracle/oradata/acme/system01.dbf
/u01/app/oracle/oradata/acme/undotbs01.dbf
/u01/app/oracle/oradata/acme/sysaux01.dbf
/u01/app/oracle/oradata/acme/users01.dbf
/u01/app/oracle/oradata/acme/example01.dbf

SQL> archive log list;
Modo log de la base de datos Modo de Archivado
Archivado automatico Activado
Destino del archivo USE_DB_RECOVERY_FILE_DEST
Secuencia de log en linea mas antigua 6
Siguiente secuencia de log para archivar 8
Secuencia de log actual 8
SQL>
SQL> alter system switch logfile;

Sistema modificado.

SQL> archive log list;
Modo log de la base de datos Modo de Archivado
Archivado automatico Activado
Destino del archivo USE_DB_RECOVERY_FILE_DEST
Secuencia de log en linea mas antigua 7
Siguiente secuencia de log para archivar 9
Secuencia de log actual 9
SQL>
SQL>
SQL> select * from dba_tables;
select * from dba_tables
*
ERROR en linea 1:
ORA-00604: se ha producido un error a nivel 1 de SQL recursivo
ORA-01116: error al abrir el archivo de base de datos 1
ORA-01110: archivo de datos 1: '/u01/app/oracle/oradata/acme/system01.dbf'
ORA-27041: no se ha podido abrir el archivo
Linux Error: 2: No such file or directory
Additional information: 3


SQL> shutdown abort;
Instancia ORACLE cerrada.
SQL>
SQL> startup mount;
Instancia ORACLE iniciada.

Total System Global Area 285212672 bytes
Fixed Size 1267068 bytes
Variable Size 92277380 bytes
Database Buffers 188743680 bytes
Redo Buffers 2924544 bytes
Base de datos montada.
SQL>
SQL> exit

[oracle@oracle11g acme]$ rman target /

Recovery Manager: Release 10.2.0.4.0 - Production on Tue Jul 6 15:30:36 2010

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

connected to target database: ACME (DBID=1908099333, not open)

RMAN> report schema;

using target database control file instead of recovery catalog
Report of database schema

List of Permanent Datafiles
===========================
File Size(MB) Tablespace RB segs Datafile Name
---- -------- -------------------- ------- ------------------------
1 0 SYSTEM *** /u01/app/oracle/oradata/acme/system01.dbf
2 25 UNDOTBS1 *** /u01/app/oracle/oradata/acme/undotbs01.dbf
3 240 SYSAUX *** /u01/app/oracle/oradata/acme/sysaux01.dbf
4 5 USERS *** /u01/app/oracle/oradata/acme/users01.dbf
5 100 EXAMPLE *** /u01/app/oracle/oradata/acme/example01.dbf

List of Temporary Files
=======================
File Size(MB) Tablespace Maxsize(MB) Tempfile Name
---- -------- -------------------- ----------- --------------------
1 20 TEMP 32767 /u01/app/oracle/oradata/acme/temp01.dbf

RMAN> restore datafile 1;

Starting restore at 06-JUL-10
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=211 devtype=DISK

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/acme/system01.dbf
channel ORA_DISK_1: reading from backup piece /u01/app/oracle/flash_recovery_area/ACME/backupset/2010_07_06/o1_mf_nnndf_TAG20100706T111529_636oo2gs_.bkp
channel ORA_DISK_1: restored backup piece 1
piece handle=/u01/app/oracle/flash_recovery_area/ACME/backupset/2010_07_06/o1_mf_nnndf_TAG20100706T111529_636oo2gs_.bkp tag=TAG20100706T111529
channel ORA_DISK_1: restore complete, elapsed time: 00:01:06
Finished restore at 06-JUL-10

RMAN> alter database open;

RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of alter db command at 07/06/2010 15:32:30
ORA-01113: el archivo 1 necesita recuperacion del medio fisico
ORA-01110: archivo de datos 1: '/u01/app/oracle/oradata/acme/system01.dbf'

RMAN> recover database;

Starting recover at 06-JUL-10
using channel ORA_DISK_1

starting media recovery

archive log thread 1 sequence 3 is already on disk as file /u01/app/oracle/flash_recovery_area/ACME/archivelog/2010_07_06/o1_mf_1_3_636oyw60_.arc
archive log thread 1 sequence 4 is already on disk as file /u01/app/oracle/flash_recovery_area/ACME/archivelog/2010_07_06/o1_mf_1_4_636p0d5o_.arc
archive log thread 1 sequence 5 is already on disk as file /u01/app/oracle/flash_recovery_area/ACME/archivelog/2010_07_06/o1_mf_1_5_636qno97_.arc
archive log thread 1 sequence 6 is already on disk as file /u01/app/oracle/flash_recovery_area/ACME/archivelog/2010_07_06/o1_mf_1_6_6372gws3_.arc
archive log thread 1 sequence 7 is already on disk as file /u01/app/oracle/flash_recovery_area/ACME/archivelog/2010_07_06/o1_mf_1_7_63748vtz_.arc
archive log thread 1 sequence 8 is already on disk as file /u01/app/oracle/flash_recovery_area/ACME/archivelog/2010_07_06/o1_mf_1_8_6374gv6n_.arc
archive log filename=/u01/app/oracle/flash_recovery_area/ACME/archivelog/2010_07_06/o1_mf_1_3_636oyw60_.arc thread=1 sequence=3
archive log filename=/u01/app/oracle/flash_recovery_area/ACME/archivelog/2010_07_06/o1_mf_1_4_636p0d5o_.arc thread=1 sequence=4
archive log filename=/u01/app/oracle/flash_recovery_area/ACME/archivelog/2010_07_06/o1_mf_1_5_636qno97_.arc thread=1 sequence=5
archive log filename=/u01/app/oracle/flash_recovery_area/ACME/archivelog/2010_07_06/o1_mf_1_6_6372gws3_.arc thread=1 sequence=6
media recovery complete, elapsed time: 00:00:02
Finished recover at 06-JUL-10

RMAN> alter database open;

database opened

RMAN> exit


Recovery Manager complete.
[oracle@oracle11g acme]$ sqlplus '/as sysdba'

SQL*Plus: Release 10.2.0.4.0 - Production on Tue Jul 6 15:33:08 2010

Copyright (c) 1982, 2007, Oracle. All Rights Reserved.


Conectado a:
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> select * from v$instance;

INSTANCE_NUMBER INSTANCE_NAME
--------------- ----------------
HOST_NAME
----------------------------------------------------------------
VERSION STARTUP_ STATUS PAR THREAD# ARCHIVE LOG_SWITCH_WAIT
----------------- -------- ------------ --- ---------- ------- ---------------
LOGINS SHU DATABASE_STATUS INSTANCE_ROLE ACTIVE_ST BLO
---------- --- ----------------- ------------------ --------- ---
1 acme
oracle11g.localdomain
10.2.0.4.0 06/07/10 OPEN NO 1 STARTED
ALLOWED NO ACTIVE PRIMARY_INSTANCE NORMAL NO


SQL>

5 LABORATORIO ELIMINAR CONTROLFILE

SQL> select * from v$instance;

INSTANCE_NUMBER INSTANCE_NAME
--------------- ----------------
HOST_NAME
----------------------------------------------------------------
VERSION STARTUP_ STATUS PAR THREAD# ARCHIVE LOG_SWITCH_WAIT
----------------- -------- ------------ --- ---------- ------- ---------------
LOGINS SHU DATABASE_STATUS INSTANCE_ROLE ACTIVE_ST BLO
---------- --- ----------------- ------------------ --------- ---
1 acme
oracle11g.localdomain
10.2.0.4.0 06/07/10 STARTED NO 0 STOPPED
ALLOWED NO ACTIVE UNKNOWN NORMAL NO


SQL>

SQL> shutdown abort;
Instancia ORACLE cerrada.
SQL>
SQL>
SQL> exit
Desconectado de Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
[oracle@oracle11g acme]$ sqlplus '/as sysdba'

SQL*Plus: Release 10.2.0.4.0 - Production on Tue Jul 6 15:40:15 2010

Copyright (c) 1982, 2007, Oracle. All Rights Reserved.

Connected to an idle instance.

SQL> startup
ORACLE instance started.

Total System Global Area 285212672 bytes
Fixed Size 1267068 bytes
Variable Size 92277380 bytes
Database Buffers 188743680 bytes
Redo Buffers 2924544 bytes
ORA-00205: error al identificar el archivo de control, compruebe el log de
alertas para obtener mas informacion


SQL> select name from v$controlfile;

ninguna fila seleccionada

SQL> exit
Desconectado de Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
[oracle@oracle11g acme]$
[oracle@oracle11g acme]$ cd $ORACLE_HOME/dbs
[oracle@oracle11g dbs]$ cat initacme.ora
acme.__db_cache_size=188743680
acme.__java_pool_size=4194304
acme.__large_pool_size=4194304
acme.__shared_pool_size=83886080
acme.__streams_pool_size=0
*.audit_file_dest='/u01/app/oracle/admin/acme/adump'
*.background_dump_dest='/u01/app/oracle/admin/acme/bdump'
*.compatible='10.2.0.3.0'
*.control_files='/u01/app/oracle/oradata/acme/control01.ctl','/u01/app/oracle/oradata/acme/control02.ctl','/u01/app/oracle/oradata/acme/control03.ctl'*.core_dump_dest='/u01/app/oracle/admin/acme/cdump'
*.db_block_size=8192
*.db_domain=''
*.db_file_multiblock_read_count=8
*.db_name='acme'
*.db_recovery_file_dest='/u01/app/oracle/flash_recovery_area'
*.db_recovery_file_dest_size=5368709120
*.dispatchers='(PROTOCOL=TCP) (SERVICE=acmeXDB)'
*.job_queue_processes=10
*.nls_language='SPANISH'
*.nls_territory='COLOMBIA'
*.open_cursors=300
*.pga_aggregate_target=94371840
*.processes=200
*.remote_login_passwordfile='EXCLUSIVE'
*.sessions=225
*.sga_target=285212672
*.undo_management='AUTO'
*.undo_tablespace='UNDOTBS1'
*.user_dump_dest='/u01/app/oracle/admin/acme/udump'
[oracle@oracle11g dbs]$


[oracle@oracle11g acme]$ ls
control02.ctl control03.ctl example01.dbf redo01.log redo02.log redo03.log sysaux01.dbf system01.dbf temp01.dbf undotbs01.dbf users01.dbf
[oracle@oracle11g acme]$ ls -ltr
total 1039032
-rw-r----- 1 oracle oinstall 20979712 jul 6 11:29 temp01.dbf
-rw-r----- 1 oracle oinstall 52429312 jul 6 15:35 redo03.log
-rw-r----- 1 oracle oinstall 52429312 jul 6 15:35 redo02.log
-rw-r----- 1 oracle oinstall 5251072 jul 6 15:35 users01.dbf
-rw-r----- 1 oracle oinstall 26222592 jul 6 15:35 undotbs01.dbf
-rw-r----- 1 oracle oinstall 503324672 jul 6 15:35 system01.dbf
-rw-r----- 1 oracle oinstall 251666432 jul 6 15:35 sysaux01.dbf
-rw-r----- 1 oracle oinstall 52429312 jul 6 15:35 redo01.log
-rw-r----- 1 oracle oinstall 104865792 jul 6 15:35 example01.dbf
-rw-r----- 1 oracle oinstall 7061504 jul 6 15:36 control03.ctl
-rw-r----- 1 oracle oinstall 7061504 jul 6 15:36 control02.ctl

SI PODEMOS COPIAR EL ARCHIVO DE CONTROLFILE DE UNO EXISTEN LO HACEMOS

[oracle@oracle11g acme]$ cp control02.ctl control01.ctl
[oracle@oracle11g acme]$ sqlplus '/as sysdba'

SQL*Plus: Release 10.2.0.4.0 - Production on Tue Jul 6 15:42:15 2010

Copyright (c) 1982, 2007, Oracle. All Rights Reserved.


Conectado a:
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> exit
Desconectado de Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
[oracle@oracle11g acme]$ sqlplus '/as sysdba'

SQL*Plus: Release 10.2.0.4.0 - Production on Tue Jul 6 15:42:21 2010

Copyright (c) 1982, 2007, Oracle. All Rights Reserved.


Conectado a:
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> shutdown immediate;
ORA-01507: base de datos sin montar


Instancia ORACLE cerrada.
SQL>
SQL> startup
Instancia ORACLE iniciada.

Total System Global Area 285212672 bytes
Fixed Size 1267068 bytes
Variable Size 92277380 bytes
Database Buffers 188743680 bytes
Redo Buffers 2924544 bytes
Base de datos montada.
Base de datos abierta.
SQL>

6 LABORATORIO ELIMINAR TODOS LOS CONTROLFILES

[oracle@oracle11g acme]$ rm *.ctl
[oracle@oracle11g acme]$ ls -ltr
total 1045944
-rw-r----- 1 oracle oinstall 20979712 jul 6 11:29 temp01.dbf
-rw-r----- 1 oracle oinstall 52429312 jul 6 15:42 redo03.log
-rw-r----- 1 oracle oinstall 52429312 jul 6 15:42 redo02.log
-rw-r----- 1 oracle oinstall 5251072 jul 6 15:44 users01.dbf
-rw-r----- 1 oracle oinstall 26222592 jul 6 15:44 undotbs01.dbf
-rw-r----- 1 oracle oinstall 503324672 jul 6 15:44 system01.dbf
-rw-r----- 1 oracle oinstall 251666432 jul 6 15:44 sysaux01.dbf
-rw-r----- 1 oracle oinstall 52429312 jul 6 15:44 redo01.log

[oracle@oracle11g acme]$ sqlplus '/as sysdba'

SQL*Plus: Release 10.2.0.4.0 - Production on Tue Jul 6 15:55:20 2010

Copyright (c) 1982, 2007, Oracle. All Rights Reserved.

Connected to an idle instance.

SQL> startup
ORACLE instance started.

Total System Global Area 285212672 bytes
Fixed Size 1267068 bytes
Variable Size 92277380 bytes
Database Buffers 188743680 bytes
Redo Buffers 2924544 bytes
ORA-00205: error al identificar el archivo de control, compruebe el log de
alertas para obtener mas informacion


SQL> exit
Desconectado de Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
[oracle@oracle11g acme]$

SQL> select * from v$instance;

INSTANCE_NUMBER INSTANCE_NAME
--------------- ----------------
HOST_NAME
----------------------------------------------------------------
VERSION STARTUP_ STATUS PAR THREAD# ARCHIVE LOG_SWITCH_WAIT
----------------- -------- ------------ --- ---------- ------- ---------------
LOGINS SHU DATABASE_STATUS INSTANCE_ROLE ACTIVE_ST BLO
---------- --- ----------------- ------------------ --------- ---
1 acme
oracle11g.localdomain
10.2.0.4.0 06/07/10 MOUNTED NO 1 STARTED
ALLOWED NO ACTIVE PRIMARY_INSTANCE NORMAL NO


SQL> alter database open;
alter database open
*
ERROR en linea 1:
ORA-01589: debe utilizar la opcion RESETLOGS o NORESETLOGS para abrir la base
de datos

ABRIMOS LA BASE DE DATOS CON MODE OPEN RESETLOOGS LO QUE QUIERE DECIR ES QUE GENERA UNA INCARNATION NUEVA

SQL> alter database open resetlogs;

Base de datos modificada.

SQL> select * from v$instance;

INSTANCE_NUMBER INSTANCE_NAME
--------------- ----------------
HOST_NAME
----------------------------------------------------------------
VERSION STARTUP_ STATUS PAR THREAD# ARCHIVE LOG_SWITCH_WAIT
----------------- -------- ------------ --- ---------- ------- ---------------
LOGINS SHU DATABASE_STATUS INSTANCE_ROLE ACTIVE_ST BLO
---------- --- ----------------- ------------------ --------- ---
1 acme
oracle11g.localdomain
10.2.0.4.0 06/07/10 OPEN NO 1 STARTED
ALLOWED NO ACTIVE PRIMARY_INSTANCE NORMAL NO


RMAN> list incarnation;

List of Database Incarnations
DB Key Inc Key DB Name DB ID STATUS Reset SCN Reset Time
------- ------- -------- ---------------- --- ---------- ----------
1 1 ACME 1908099333 PARENT 1 17-FEB-08
2 2 ACME 1908099333 CURRENT 464631 06-JUL-10

RMAN> list incarnation;

List of Database Incarnations
DB Key Inc Key DB Name DB ID STATUS Reset SCN Reset Time
------- ------- -------- ---------------- --- ---------- ----------
1 1 ACME 1908099333 PARENT 1 17-FEB-08
2 2 ACME 1908099333 PARENT 464631 06-JUL-10
3 3 ACME 1908099333 CURRENT 584972 06-JUL-10

RMAN>

NOTA: COMO SE GENERO UNA NUEVA INCARNATION ES OBLIGAORIO O SE SUGIERE REALIZAR UN BACKUP FULL DE LA BD Y DE LOS ARCHIVES.

SQL> select dbid from v$database;

DBID
----------
1908099333

SQL> desc v$database;
Nombre ?Nulo? Tipo
----------------------------------------- -------- ----------------------------
DBID NUMBER
NAME VARCHAR2(9)
CREATED DATE
RESETLOGS_CHANGE# NUMBER
RESETLOGS_TIME DATE
PRIOR_RESETLOGS_CHANGE# NUMBER
PRIOR_RESETLOGS_TIME DATE
LOG_MODE VARCHAR2(12)
CHECKPOINT_CHANGE# NUMBER
ARCHIVE_CHANGE# NUMBER
CONTROLFILE_TYPE VARCHAR2(7)
CONTROLFILE_CREATED DATE
CONTROLFILE_SEQUENCE# NUMBER
CONTROLFILE_CHANGE# NUMBER
CONTROLFILE_TIME DATE
OPEN_RESETLOGS VARCHAR2(11)
VERSION_TIME DATE
OPEN_MODE VARCHAR2(10)
PROTECTION_MODE VARCHAR2(20)
PROTECTION_LEVEL VARCHAR2(20)
REMOTE_ARCHIVE VARCHAR2(8)
ACTIVATION# NUMBER
SWITCHOVER# NUMBER
DATABASE_ROLE VARCHAR2(16)
ARCHIVELOG_CHANGE# NUMBER
ARCHIVELOG_COMPRESSION VARCHAR2(8)
SWITCHOVER_STATUS VARCHAR2(20)
DATAGUARD_BROKER VARCHAR2(8)
GUARD_STATUS VARCHAR2(7)
SUPPLEMENTAL_LOG_DATA_MIN VARCHAR2(8)
SUPPLEMENTAL_LOG_DATA_PK VARCHAR2(3)
SUPPLEMENTAL_LOG_DATA_UI VARCHAR2(3)
FORCE_LOGGING VARCHAR2(3)
PLATFORM_ID NUMBER
PLATFORM_NAME VARCHAR2(101)
RECOVERY_TARGET_INCARNATION# NUMBER
LAST_OPEN_INCARNATION# NUMBER
CURRENT_SCN NUMBER
FLASHBACK_ON VARCHAR2(18)
SUPPLEMENTAL_LOG_DATA_FK VARCHAR2(3)
SUPPLEMENTAL_LOG_DATA_ALL VARCHAR2(3)
DB_UNIQUE_NAME VARCHAR2(30)
STANDBY_BECAME_PRIMARY_SCN NUMBER
FS_FAILOVER_STATUS VARCHAR2(21)
FS_FAILOVER_CURRENT_TARGET VARCHAR2(30)
FS_FAILOVER_THRESHOLD NUMBER
FS_FAILOVER_OBSERVER_PRESENT VARCHAR2(7)
FS_FAILOVER_OBSERVER_HOST VARCHAR2(512)

SQL> select CURRENT_SCN from v$database;

CURRENT_SCN
-----------
586120

ELIMINAR BACKUPS DE LA INCARNATION ANTERIOR

RMAN> list backup summary;


List of Backups
===============
Key TY LV S Device Type Completion Time #Pieces #Copies Compressed Tag
------- -- -- - ----------- --------------- ------- ------- ---------- ---
1 B F X DISK 06-JUL-10 1 1 NO TAG20100706T111529
2 B F X DISK 06-JUL-10 1 1 NO TAG20100706T111529
3 B A X DISK 06-JUL-10 1 1 NO TAG20100706T112133
4 B F X DISK 06-JUL-10 1 1 NO TAG20100706T154914
5 B F A DISK 06-JUL-10 1 1 NO TAG20100706T155041
6 B F A DISK 06-JUL-10 1 1 NO TAG20100706T160049

RMAN> crosscheck backup;

using channel ORA_DISK_1
crosschecked backup piece: found to be 'EXPIRED'
backup piece handle=/u01/app/oracle/flash_recovery_area/ACME/backupset/2010_07_06/o1_mf_nnndf_TAG20100706T111529_636oo2gs_.bkp recid=1 stamp=723640530
crosschecked backup piece: found to be 'EXPIRED'
backup piece handle=/u01/app/oracle/flash_recovery_area/ACME/backupset/2010_07_06/o1_mf_ncsnf_TAG20100706T111529_636or3gl_.bkp recid=2 stamp=723640627
crosschecked backup piece: found to be 'EXPIRED'
backup piece handle=/u01/app/oracle/flash_recovery_area/ACME/backupset/2010_07_06/o1_mf_annnn_TAG20100706T112133_636p0h6k_.bkp recid=3 stamp=723640895
crosschecked backup piece: found to be 'EXPIRED'
backup piece handle=/u01/app/oracle/flash_recovery_area/ACME/backupset/2010_07_06/o1_mf_nnndf_TAG20100706T154914_6375pcdd_.bkp recid=4 stamp=723656955
crosschecked backup piece: found to be 'AVAILABLE'
backup piece handle=/u01/app/oracle/flash_recovery_area/ACME/autobackup/2010_07_06/o1_mf_s_723656691_6375s233_.bkp recid=5 stamp=723657446
crosschecked backup piece: found to be 'AVAILABLE'
backup piece handle=/u01/app/oracle/flash_recovery_area/ACME/autobackup/2010_07_06/o1_mf_s_723657649_6376d28p_.bkp recid=6 stamp=723657650
Crosschecked 6 objects


RMAN> delete backup expired;

RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-00558: error encountered while parsing input commands
RMAN-01009: syntax error: found "expired": expecting one of: "backed, completed, controlfile, device, like, of, ;, tag"
RMAN-01007: at line 1 column 15 file: standard input

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

RMAN> delete expired backup;

using channel ORA_DISK_1

List of Backup Pieces
BP Key BS Key Pc# Cp# Status Device Type Piece Name
------- ------- --- --- ----------- ----------- ----------
1 1 1 1 EXPIRED DISK /u01/app/oracle/flash_recovery_area/ACME/backupset/2010_07_06/o1_mf_nnndf_TAG20100706T111529_636oo2gs_.bkp
2 2 1 1 EXPIRED DISK /u01/app/oracle/flash_recovery_area/ACME/backupset/2010_07_06/o1_mf_ncsnf_TAG20100706T111529_636or3gl_.bkp
3 3 1 1 EXPIRED DISK /u01/app/oracle/flash_recovery_area/ACME/backupset/2010_07_06/o1_mf_annnn_TAG20100706T112133_636p0h6k_.bkp
4 4 1 1 EXPIRED DISK /u01/app/oracle/flash_recovery_area/ACME/backupset/2010_07_06/o1_mf_nnndf_TAG20100706T154914_6375pcdd_.bkp

Do you really want to delete the above objects (enter YES or NO)? yes
deleted backup piece
backup piece handle=/u01/app/oracle/flash_recovery_area/ACME/backupset/2010_07_06/o1_mf_nnndf_TAG20100706T111529_636oo2gs_.bkp recid=1 stamp=723640530
deleted backup piece
backup piece handle=/u01/app/oracle/flash_recovery_area/ACME/backupset/2010_07_06/o1_mf_ncsnf_TAG20100706T111529_636or3gl_.bkp recid=2 stamp=723640627
deleted backup piece
backup piece handle=/u01/app/oracle/flash_recovery_area/ACME/backupset/2010_07_06/o1_mf_annnn_TAG20100706T112133_636p0h6k_.bkp recid=3 stamp=723640895
deleted backup piece
backup piece handle=/u01/app/oracle/flash_recovery_area/ACME/backupset/2010_07_06/o1_mf_nnndf_TAG20100706T154914_6375pcdd_.bkp recid=4 stamp=723656955
Deleted 4 EXPIRED objects


RMAN> list backup summary;


List of Backups
===============
Key TY LV S Device Type Completion Time #Pieces #Copies Compressed Tag
------- -- -- - ----------- --------------- ------- ------- ---------- ---
5 B F A DISK 06-JUL-10 1 1 NO TAG20100706T155041
6 B F A DISK 06-JUL-10 1 1 NO TAG20100706T160049

RMAN> delete backupset 5;

using channel ORA_DISK_1

List of Backup Pieces
BP Key BS Key Pc# Cp# Status Device Type Piece Name
------- ------- --- --- ----------- ----------- ----------
5 5 1 1 AVAILABLE DISK /u01/app/oracle/flash_recovery_area/ACME/autobackup/2010_07_06/o1_mf_s_723656691_6375s233_.bkp

Do you really want to delete the above objects (enter YES or NO)? YES
deleted backup piece
backup piece handle=/u01/app/oracle/flash_recovery_area/ACME/autobackup/2010_07_06/o1_mf_s_723656691_6375s233_.bkp recid=5 stamp=723657446
Deleted 1 objects


RMAN> delete backupset 6;

using channel ORA_DISK_1

List of Backup Pieces
BP Key BS Key Pc# Cp# Status Device Type Piece Name
------- ------- --- --- ----------- ----------- ----------
6 6 1 1 AVAILABLE DISK /u01/app/oracle/flash_recovery_area/ACME/autobackup/2010_07_06/o1_mf_s_723657649_6376d28p_.bkp

Do you really want to delete the above objects (enter YES or NO)? YES
deleted backup piece
backup piece handle=/u01/app/oracle/flash_recovery_area/ACME/autobackup/2010_07_06/o1_mf_s_723657649_6376d28p_.bkp recid=6 stamp=723657650
Deleted 1 objects


RMAN>

REALIZAR BACKUP CON LA NUEVA INCARNATION

7 LABORATORIO BORRAR REDOLOGS

[oracle@oracle11g acme]$ ls -ltr
total 1045944
-rw-r----- 1 oracle oinstall 52429312 jul 6 16:18 redo03.log
-rw-r----- 1 oracle oinstall 52429312 jul 6 16:18 redo02.log
-rw-r----- 1 oracle oinstall 5251072 jul 6 16:32 users01.dbf
-rw-r----- 1 oracle oinstall 104865792 jul 6 16:32 example01.dbf
-rw-r----- 1 oracle oinstall 20979712 jul 6 17:17 temp01.dbf
-rw-r----- 1 oracle oinstall 503324672 jul 6 17:38 system01.dbf
-rw-r----- 1 oracle oinstall 251666432 jul 6 17:38 sysaux01.dbf
-rw-r----- 1 oracle oinstall 26222592 jul 6 17:39 undotbs01.dbf
-rw-r----- 1 oracle oinstall 52429312 jul 6 17:40 redo01.log
-rw-r----- 1 oracle oinstall 7061504 jul 6 17:40 control03.ctl
-rw-r----- 1 oracle oinstall 7061504 jul 6 17:40 control02.ctl
-rw-r----- 1 oracle oinstall 7061504 jul 6 17:40 control01.ctl
[oracle@oracle11g acme]$
[oracle@oracle11g acme]$ sqlplus '/as sysdba'

SQL*Plus: Release 10.2.0.4.0 - Production on Tue Jul 6 17:41:11 2010

Copyright (c) 1982, 2007, Oracle. All Rights Reserved.


Conectado a:
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> select member from v$logfile;

MEMBER
--------------------------------------------------------------------------------
/u01/app/oracle/oradata/acme/redo03.log
/u01/app/oracle/oradata/acme/redo02.log
/u01/app/oracle/oradata/acme/redo01.log

[oracle@oracle11g acme]$ pwd
/u01/app/oracle/oradata/acme
[oracle@oracle11g acme]$ ls
control01.ctl control03.ctl redo01.log redo03.log system01.dbf undotbs01.dbf
control02.ctl example01.dbf redo02.log sysaux01.dbf temp01.dbf users01.dbf
[oracle@oracle11g acme]$
[oracle@oracle11g acme]$
[oracle@oracle11g acme]$ ls -ltr
total 1045944
-rw-r----- 1 oracle oinstall 20979712 jul 6 17:17 temp01.dbf
-rw-r----- 1 oracle oinstall 52429312 jul 6 17:47 redo03.log
-rw-r----- 1 oracle oinstall 52429312 jul 6 17:47 redo01.log
-rw-r----- 1 oracle oinstall 5251072 jul 6 17:47 users01.dbf
-rw-r----- 1 oracle oinstall 26222592 jul 6 17:47 undotbs01.dbf
-rw-r----- 1 oracle oinstall 503324672 jul 6 17:47 system01.dbf
-rw-r----- 1 oracle oinstall 251666432 jul 6 17:47 sysaux01.dbf
-rw-r----- 1 oracle oinstall 52429312 jul 6 17:47 redo02.log
-rw-r----- 1 oracle oinstall 104865792 jul 6 17:47 example01.dbf
-rw-r----- 1 oracle oinstall 7061504 jul 6 17:47 control03.ctl
-rw-r----- 1 oracle oinstall 7061504 jul 6 17:47 control02.ctl
-rw-r----- 1 oracle oinstall 7061504 jul 6 17:47 control01.ctl
[oracle@oracle11g acme]$
[oracle@oracle11g acme]$
[oracle@oracle11g acme]$ cp redo01.log redo01.cop
[oracle@oracle11g acme]$ rm redo01.log
[oracle@oracle11g acme]$


[oracle@oracle11g acme]$ sqlplus '/as sysdba'

SQL*Plus: Release 10.2.0.4.0 - Production on Tue Jul 6 17:59:48 2010

Copyright (c) 1982, 2007, Oracle. All Rights Reserved.


Conectado a:
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> alter database clear unarchived logfile group 1;

Base de datos modificada.

SQL> alter database open;

Base de datos modificada.

1* select group#,members,status from v$logfile
SQL> ed
Escrito file afiedt.buf

1* select group#,members,status from v$log
SQL> /

GROUP# MEMBERS STATUS
---------- ---------- ----------------
1 1 CURRENT
2 1 INACTIVE
3 1 UNUSED

SQL> alter system switch logfile;

Sistema modificado.

SQL> select group#,members,status from v$log;

GROUP# MEMBERS STATUS
---------- ---------- ----------------
1 1 ACTIVE
2 1 INACTIVE
3 1 CURRENT

SQL> alter system switch logfile;

Sistema modificado.

SQL> select group#,members,status from v$log;

GROUP# MEMBERS STATUS
---------- ---------- ----------------
1 1 ACTIVE
2 1 CURRENT
3 1 ACTIVE

SQL>


8 LABORATORIO BORRAR CONTROLFILES Y RECUPERARLOS SIN BACKUP
BORRAMOS ARCHIVOS CTL CONTROLFILES

[oracle@oracle11g app]$ cd oracle/oradata/acme/
[oracle@oracle11g acme]$ pwd
/u01/app/oracle/oradata/acme
[oracle@oracle11g acme]$ ls
afiedt.buf control02.ctl example01.dbf redo01.log redo03.log system01.dbf undotbs01.dbf
control01.ctl control03.ctl redo01.cop redo02.log sysaux01.dbf temp01.dbf users01.dbf
[oracle@oracle11g acme]$ rm *.ctl

AL ABRIR LA INSTANCIA SE PRODUCE EL ERROR

SQL> startup
ORACLE instance started.

Total System Global Area 285212672 bytes
Fixed Size 1267068 bytes
Variable Size 92277380 bytes
Database Buffers 188743680 bytes
Redo Buffers 2924544 bytes
ORA-00205: error al identificar el archivo de control, compruebe el log de
alertas para obtener mas informacion


SE VALIDA Y PARA EL EJEMPLO NO SE CONSIDERA QUE NO SE TIENE BACKUP

NOS TOCA CREAR EL ARCHIVO DE CONTROLFILE MANUALMENTE

creamos un archivo control.sql con lo siguiente

STARTUP NOMOUNT
CREATE CONTROLFILE SET DATABASE "acme" RESETLOGS ARCHIVELOG REUSE
MAXLOGFILES 16
MAXLOGMEMBERS 3
MAXDATAFILES 100
MAXINSTANCES 8
MAXLOGHISTORY 292
LOGFILE
GROUP 1 '/u01/app/oracle/oradata/acme/redo01.log' SIZE 50M,
GROUP 2 '/u01/app/oracle/oradata/acme/redo02.log' SIZE 50M,
GROUP 3 '/u01/app/oracle/oradata/acme/redo03.log' SIZE 50M
DATAFILE
'/u01/app/oracle/oradata/acme/system01.dbf',
'/u01/app/oracle/oradata/acme/undotbs01.dbf',
'/u01/app/oracle/oradata/acme/sysaux01.dbf',
'/u01/app/oracle/oradata/acme/users01.dbf',
'/u01/app/oracle/oradata/acme/example01.dbf'
CHARACTER SET WE8ISO8859P1;


[oracle@oracle11g scripts]$ sqlplus '/as sysdba'

SQL*Plus: Release 10.2.0.4.0 - Production on Wed Jul 7 10:46:18 2010

Copyright (c) 1982, 2007, Oracle. All Rights Reserved.

Connected to an idle instance.

SQL> start control.sql
ORACLE instance started.

Total System Global Area 285212672 bytes
Fixed Size 1267068 bytes
Variable Size 92277380 bytes
Database Buffers 188743680 bytes
Redo Buffers 2924544 bytes

Archivo de control creado.

SQL> alter database open;
alter database open
*
ERROR en linea 1:
ORA-01589: debe utilizar la opcion RESETLOGS o NORESETLOGS para abrir la base
de datos


SQL> alter database open resetlogs;

Base de datos modificada.

SQL> select * from v$instance;

INSTANCE_NUMBER INSTANCE_NAME
--------------- ----------------
HOST_NAME
----------------------------------------------------------------
VERSION STARTUP_ STATUS PAR THREAD# ARCHIVE LOG_SWITCH_WAIT
----------------- -------- ------------ --- ---------- ------- ---------------
LOGINS SHU DATABASE_STATUS INSTANCE_ROLE ACTIVE_ST BLO
---------- --- ----------------- ------------------ --------- ---
1 acme
oracle11g.localdomain
10.2.0.4.0 07/07/10 OPEN NO 1 STARTED
ALLOWED NO ACTIVE PRIMARY_INSTANCE NORMAL NO