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

Ubuntu Mounting Linux Partitions

Mounting Linux Partitions in Ubuntu
If you plug in an external hard drive with a Linux filesystem, it will automount and show up on your desktop, just like any external media. But what if you have an internal hard drive or partition with a Linux filesystem? Well, that's what this tutorial is about.

Warning: The tutorial on this page is for an internal drive that will serve as an extra data partition. If you would like to mount a separate drive or partition as /home instead, you want a different tutorial.

First you have to determine what the partition is called and what filesystem it is. One quick way to do it if you know what filesystem you formatted the drive as (Ext3, for example) is to just type the terminal command
sudo fdisk -l

Here's how it could come out:

Disk /dev/sda: 8589 MB, 8589934592 bytes
255 heads, 63 sectors/track, 1044 cylinders
Units = cylinders of 16065 * 512 = 8225280 bytes
Sector size (logical/physical): 512 bytes / 512 bytes
I/O size (minimum/optimal): 512 bytes / 512 bytes
Disk identifier: 0x000eb4ba

Device Boot Start End Blocks Id System
/dev/sda1 * 1 524 4208006 83 Linux
/dev/sda2 525 1044 4176900 83 Linux

As you can see, I'm able to locate that /dev/sda2 is my Linux partition, but in System, I don't find out if it's Ext3, Ext4, Reiserfs, or what it is. If I happen to know it's Ext4, cool.

But let's say I didn't know. Well, one way to find out for sure is to install GParted and find out:

sudo apt-get update
sudo apt-get install gparted gksu
gksudo gparted


You can go to System > Administration > GParted and enter your password to get it started.


Ah, now I can definitely see it's Ext4 for sure. Under Partition I see it's /dev/sda2, and under Filesystem, I see it's Ext4.

If you have a second physical hard drive (not just another partition), you might have to click on the top-right corner to focus on the second hard drive. (Click on the down-pointing arrow to get the drop-down menu.)

So now I'll create a mount point for that partition:
sudo mkdir /storage

Next, I want to determine the UUID of my partition.***
ls -l /dev/disk/by-uuid
and I get back this output:
total 0
lrwxrwxrwx 1 root root 10 2010-04-26 12:00 20bfd80a-a96b-461c-a63d-c96ff8e95872 -> ../../sda1
lrwxrwxrwx 1 root root 10 2010-04-26 19:19 d1d0cf46-958f-4a12-a604-0ac66040648b -> ../../sda2
Then I'll edit my /etc/fstab file:
sudo cp /etc/fstab /etc/fstab_backup
sudo nano /etc/fstab

Once in there, I should add in this line:
UUID=d1d0cf46-958f-4a12-a604-0ac66040648b /storage ext4 defaults 0 0

Then I can save (Control-X), confirm (Y), and exit (Enter).

Since we've made changes to the /etc/fstab file, we need to have Ubuntu acknowledge those changes:
sudo mount -a

Now I need to give it the proper permissions. Let's just assume, for this example, that my username is jessica.
sudo chown -R jessica:jessica /storage
sudo chmod -R 755 /storage

RMAN list backup Generar Archivo Backups

ORACLE_HOME=/u01/app/oracle/product/10.2.0;
export ORACLE_HOME
PATH=.:/bin:$ORACLE_HOME/bin:/usr/bin:/usr/local/bin:/etc:/usr/ccs/bin:/usr/openwin/bin:/usr/dt/bin;
export PATH

ORACLE_SID=letodb
export ORACLE_SID
sid=$ORACLE_SID

/u01/app/oracle/product/10.2.0/bin/rman target / LOG=/u02/backup/list_backup.log CMDFILE=/u02/backup/list_backup.rcv

list_backup.rcv

show all;
list backup summary;

Recovery

SQL> select segment_name, status from dba_rollback_segs;

SEGMENT_NAME STATUS
------------------------------ ----------------
SYSTEM ONLINE
_SYSSMU4$ ONLINE
_SYSSMU5$ ONLINE
_SYSSMU6$ ONLINE
_SYSSMU7$ ONLINE
_SYSSMU8$ ONLINE
_SYSSMU9$ ONLINE
_SYSSMU10$ ONLINE
_SYSSMU11$ ONLINE
_SYSSMU12$ ONLINE
_SYSSMU13$ ONLINE
_SYSSMU14$ ONLINE
_SYSSMU15$ ONLINE
_SYSSMU16$ ONLINE
_SYSSMU17$ ONLINE
_SYSSMU18$ ONLINE
_SYSSMU19$ ONLINE
_SYSSMU20$ ONLINE
_SYSSMU21$ OFFLINE
_SYSSMU22$ NEEDS RECOVERY
_SYSSMU23$ OFFLINE
_SYSSMU24$ OFFLINE
_SYSSMU25$ OFFLINE
_SYSSMU26$ OFFLINE
_SYSSMU27$ OFFLINE
_SYSSMU28$ OFFLINE
_SYSSMU29$ OFFLINE
_SYSSMU30$ OFFLINE
_SYSSMU31$ OFFLINE
_SYSSMU32$ OFFLINE
_SYSSMU33$ OFFLINE
_SYSSMU34$ OFFLINE
_SYSSMU35$ OFFLINE
_SYSSMU36$ OFFLINE
_SYSSMU37$ OFFLINE
_SYSSMU38$ OFFLINE
_SYSSMU39$ OFFLINE
_SYSSMU40$ OFFLINE
_SYSSMU41$ ONLINE
_SYSSMU42$ ONLINE
_SYSSMU43$ ONLINE
_SYSSMU44$ OFFLINE
_SYSSMU45$ OFFLINE
_SYSSMU46$ OFFLINE
_SYSSMU47$ OFFLINE
_SYSSMU48$ OFFLINE
_SYSSMU49$ OFFLINE
_SYSSMU50$ OFFLINE
_SYSSMU51$ OFFLINE
_SYSSMU52$ OFFLINE
_SYSSMU53$ OFFLINE
_SYSSMU54$ OFFLINE
_SYSSMU55$ OFFLINE
_SYSSMU56$ OFFLINE
_SYSSMU57$ OFFLINE
_SYSSMU58$ OFFLINE
_SYSSMU59$ OFFLINE
_SYSSMU60$ OFFLINE
_SYSSMU61$ OFFLINE
_SYSSMU62$ OFFLINE
_SYSSMU63$ OFFLINE
_SYSSMU64$ OFFLINE
_SYSSMU65$ OFFLINE
_SYSSMU66$ OFFLINE
_SYSSMU67$ OFFLINE
_SYSSMU68$ OFFLINE
_SYSSMU69$ OFFLINE
_SYSSMU70$ OFFLINE
_SYSSMU71$ OFFLINE
_SYSSMU72$ OFFLINE
_SYSSMU73$ OFFLINE
_SYSSMU74$ OFFLINE
_SYSSMU75$ OFFLINE
_SYSSMU76$ OFFLINE
_SYSSMU77$ OFFLINE
_SYSSMU78$ OFFLINE
_SYSSMU79$ OFFLINE
_SYSSMU80$ OFFLINE
_SYSSMU81$ OFFLINE
_SYSSMU82$ OFFLINE
_SYSSMU83$ OFFLINE
_SYSSMU84$ OFFLINE
_SYSSMU85$ OFFLINE
_SYSSMU86$ OFFLINE
_SYSSMU87$ OFFLINE
_SYSSMU88$ OFFLINE
_SYSSMU89$ OFFLINE
_SYSSMU90$ OFFLINE
_SYSSMU91$ OFFLINE
_SYSSMU92$ OFFLINE
_SYSSMU93$ OFFLINE
_SYSSMU94$ OFFLINE
_SYSSMU95$ OFFLINE
_SYSSMU96$ OFFLINE
_SYSSMU97$ OFFLINE
_SYSSMU98$ OFFLINE
_SYSSMU99$ OFFLINE
_SYSSMU100$ OFFLINE
_SYSSMU101$ OFFLINE
_SYSSMU102$ OFFLINE
_SYSSMU103$ ONLINE
_SYSSMU104$ OFFLINE
_SYSSMU105$ OFFLINE
_SYSSMU106$ OFFLINE
_SYSSMU107$ OFFLINE
_SYSSMU108$ ONLINE
_SYSSMU109$ ONLINE
_SYSSMU110$ OFFLINE
_SYSSMU111$ OFFLINE
_SYSSMU112$ OFFLINE
_SYSSMU113$ OFFLINE
_SYSSMU114$ OFFLINE
_SYSSMU115$ OFFLINE
_SYSSMU116$ OFFLINE
_SYSSMU117$ OFFLINE
_SYSSMU118$ OFFLINE
_SYSSMU119$ OFFLINE
_SYSSMU120$ OFFLINE
_SYSSMU121$ OFFLINE
_SYSSMU122$ OFFLINE
_SYSSMU123$ OFFLINE
_SYSSMU124$ OFFLINE
_SYSSMU125$ OFFLINE
_SYSSMU126$ OFFLINE
_SYSSMU127$ OFFLINE
_SYSSMU128$ OFFLINE
_SYSSMU129$ OFFLINE
_SYSSMU130$ OFFLINE
_SYSSMU131$ OFFLINE
_SYSSMU132$ OFFLINE
_SYSSMU133$ OFFLINE
_SYSSMU134$ OFFLINE
_SYSSMU135$ OFFLINE
_SYSSMU136$ OFFLINE
_SYSSMU137$ OFFLINE

135 rows selected.

SQL>


drop the UNDO tablespace que estan corruptos

SQL> drop tablespace UNDO_name including contents ;

Tablespace dropped.

Remover datafiles physicall

$ rm /path/undo_name.dbf

Recreate the tablespace.

Examples

SQL> create undo tablespace “UNDO_name″ datafile ‘/path/undo_name.dbf’ size 5000M AUTOEXTEND ON;

Tablespace created.

l. shutdown db and restart the db with original init file.


SQL> alter tablespace UNDOTBS1 offline immediate;
Tablespace altered.


Para Recuperar un Bloque Corrupto si los hay

execute dbms_repair.skip_corrupt_blocks(’OWNER’,'TABLE_NAME’) ;

Add the following line to pfile:

_corrupted_rollback_segments =(‘_SYSSMU11$’,'_SYSSMU12$’,'_SYSSMU13$’,'_SYSSMU14$’,'_SYSSMU15$’,'_SYSSMU16$’,
‘_SYSSMU17$’,'_SYSSMU18$’,'_SYSSMU19$’,'_SYSSMU20$’)


SQL> drop rollback segment "_SYSSMU22$";
Rollback segment dropped.



SQL > drop tablespace undotbs including contents and datafiles;
Tablespace dropped.

Centos Vbox Guest Aditions

Centos

yum install gcc gcc-c++ make automake kernel-devel

Fedora

yum install gcc -y

yum install kernel sources -y

yum install kernel-devel -y

Multiple Temporary Tablespaces

Multiple Temporary Tablespaces

CAMBIAR DEFAULT TABLESPACE SE DEBE CREAR OTRO PRIMERO

SQL> ALTER DATABASE DEFAULT TEMPORARY TABLESPACE temp1;

Database altered.


SQL> SELECT * FROM DATABASE_PROPERTIES where PROPERTY_NAME='DEFAULT_TEMP_TABLESPACE';


ALTER DATABASE DEFAULT TABLESPACE users;The current settings for the default tablespaces can be viewed using the following query.

COLUMN property_name FORMAT A30
COLUMN property_value FORMAT A30
COLUMN description FORMAT A50
SET LINESIZE 200

SELECT *
FROM database_properties
WHERE property_name like '%TABLESPACE';

PROPERTY_NAME PROPERTY_VALUE DESCRIPTION
------------------------------ ------------------------------ --------------------------------------
DEFAULT_TEMP_TABLESPACE TEMP Name of default temporary tablespace
DEFAULT_PERMANENT_TABLESPACE USERS Name of default permanent tablespaceRename Tablespace
Renaming permanent and temporary tablespaces is now allowed in Oracle 10g (except for SYSTEM and SYSAUX) using the following command.

Multiple Temporary Tablespaces

Tablespace groups allow users to use more than one tablespace to store temporary segments. The tablespace group is created implicitly when the first tablespace is assigned to it:

-- Create group by adding existing tablespace.
ALTER TABLESPACE temp TABLESPACE GROUP temp_ts_group;

-- Add a new tablespace to the group.
CREATE TEMPORARY TABLESPACE temp2
TEMPFILE '/u01/app/oracle/oradata/DB10G/temp201.dbf' SIZE 20M
TABLESPACE GROUP temp_ts_group;The tablespaces assigned to a group can be viewed using:

SELECT * FROM dba_tablespace_groups;

GROUP_NAME TABLESPACE_NAME
------------------------------ ------------------------------
TEMP_TS_GROUP TEMP
TEMP_TS_GROUP TEMP2

2 rows selected.Once the group is created it can be assigned just like a tablespace to a user or as the default temporary tablespace:

-- Assign group as the temporary tablespace for a user.
ALTER USER scott TEMPORARY TABLESPACE temp_ts_group;

-- Assign group as the default temporary tablespace.
ALTER DATABASE DEFAULT TEMPORARY TABLESPACE temp_ts_group;A tablespace can be removed from a group using:

ALTER TABLESPACE temp2 TABLESPACE GROUP '';

SELECT * FROM dba_tablespace_groups;

GROUP_NAME TABLESPACE_NAME
------------------------------ ------------------------------
TEMP_TS_GROUP TEMP

1 row selected.There is no theoretical maximum limit to the number of tablespaces in a tablespace group, but it must contain at least one. The group is implicitly dropped when the last member is removed. The last member of a group cannot be removed if the group is still assigned as the default temporary tablespace. In this example the following must be done to remove the last member from the group.
-- Switch from the group to a specific tablespace.
ALTER DATABASE DEFAULT TEMPORARY TABLESPACE temp;

-- Remove the tablespace from the group.
ALTER TABLESPACE temp TABLESPACE GROUP '';

-- Check the group has gone.
SELECT * FROM dba_tablespace_groups;

no rows selected

Solaris 10

Ver si la Maquina esta Corriendo en 32 Bits o 64 Bits

# isainfo –v


Trabajar con el área de swap

Una de las primeras cosas que vamos a ver, es la forma en la que podemos conocer el estado actual de nuestra área de swap, utilizaremos el comando swap con la opción -s. El comando nos devolverá un resumen del uso actual del área de swap.

(root@huelva)# swap -s
total: 552696k bytes allocated + 580744k reserved = 1133440k used, 2310312k available
(root@huelva)#

Ubuntu 10.04

http://linuxcaceres.blogspot.com/2010/04/cosas-que-hacer-despues-de-instalar.html

http://conecti.ca/2010/04/29/cosas-que-hacer-despues-de-instalar-ubuntu-10-04-lucid-lynx/

http://sliceoflinux.com/2010/05/02/que-hacer-despues-de-instalar-ubuntu-10-04-lts-paso-a-paso/

http://www.argentinawarez.com/programas-gratis/736989-foro-ubuntu-cosas-que-hacer-despues-de-instalar-ubuntu-10-04-a.html

Podemos Optimizar Ubuntu 10.04 tras su instalación, desde consola, así:

sudo apt-get install zenity
wget http://dl.dropbox.com/u/1113424/webupd8/script_ubuntu_10.04_lucid_lynx.sh
chmod +x script_ubuntu_10.04_lucid_lynx.sh
sudo ./script_ubuntu_10.04_lucid_lynx.sh

http://www.subinet.es/contenido-i/guias-y-tips/guias-tips-linux/10-cosas-que-hacer-despues-de-instalar-ubuntu-10-04-lucid/

Limpieza Ubuntu:

Esto es bueno hacerlo cada “X” tiempo para quitar lastre y mantener tu sistema operativo “limpio”, pero sobre todo tras una instalación limpia. Abrimos Terminal y escribimos:

sudo apt-get autoremove

[fuera los paquetes innecesarios]

sudo apt-get autoclean

[fuera las referencias innecesarias]

sudo apt-get clean

[cada vez que descargamos un paquete para ser instalado se guarda en /var/cache/apt/archives/. Con esto limpiamos estos paquetes sin afectar alsistema]

Oracle 1OG Performance and Tuning

http://husnusensoy.wordpress.com/2009/10/30/how-to-install-oracle-11g-release-2-on-oel-5-4-on-virtualbox-installing-grid-infrastructure/

alter system flush buffer_cache;

alter system flush shared_pool;

http://www.red-partner.com/pdf/UsoDeBitmapJoinIndexes.pdf

http://www.oracle-base.com/articles/10g/TablespaceManagement10g.php

v$session_wait

Tablas IOT
http://plsqlhowtos.blogspot.com/2008/10/notes-on-index-organized-tables.html
Vistas Materializadas
Vista Materializadas Log

ALTER SESSION ENABLE RESUMABLE

AFTER SUSPEND TRIGGER

http://jjmora.es/Rendimiento_Tuning_Solaris_Memoria_Swap/

Oracle Violacion de Politicas

POLITICAS DE VIOLACIONES ORACLE 10G

SQL> revoke execute on utl_file from public;
SQL> revoke execute on DBMS_RANDOM from public;
SQL> revoke execute on UTL_HTTP from public;
SQL> revoke execute on UTL_SMTP from public;
SQL> revoke execute on UTL_TCP from public;
SQL> ALTER PROFILE DEFAULT LIMIT FAILED_LOGIN_ATTEMPTS 5;
SQL> revoke sysdba from rman;

Examples Oracle Online Table Redefinition

Online Table Redefinition Examples

For the following examples, see Oracle Database PL/SQL Packages and Types Reference for descriptions of all DBMS_REDEFINITION subprograms.

Example Description
Example 1 Redefines a table by adding new columns and adding partitioning.
Example 2 Demonstrates redefinition with object datatypes.
Example 3 Demonstrates redefinition with manually registered dependent objects.
Example 4 Redefines a single table partition, moving it to a different tablespace.


Example 1

This example illustrates online redefinition of the previously created table hr.admin_emp, which at this point only contains columns: empno, ename, job, deptno. The table is redefined as follows:

•New columns mgr, hiredate, sal, and bonus are added. (These existed in the original table but were dropped in previous examples.)

•The new column bonus is initialized to 0

•The column deptno has its value increased by 10.

•The redefined table is partitioned by range on empno.

The steps in this redefinition are illustrated below.

1.Verify that the table is a candidate for online redefinition. In this case you specify that the redefinition is to be done using primary keys or pseudo-primary keys.

BEGIN
DBMS_REDEFINITION.CAN_REDEF_TABLE('hr','admin_emp',
DBMS_REDEFINITION.CONS_USE_PK);
END;
/

2.Create an interim table hr.int_admin_emp.

CREATE TABLE hr.int_admin_emp
(empno NUMBER(5) PRIMARY KEY,
ename VARCHAR2(15) NOT NULL,
job VARCHAR2(10),
mgr NUMBER(5),
hiredate DATE DEFAULT (sysdate),
sal NUMBER(7,2),
deptno NUMBER(3) NOT NULL,
bonus NUMBER (7,2) DEFAULT(1000))
PARTITION BY RANGE(empno)
(PARTITION emp1000 VALUES LESS THAN (1000) TABLESPACE admin_tbs,
PARTITION emp2000 VALUES LESS THAN (2000) TABLESPACE admin_tbs2);

3.Start the redefinition process.

BEGIN
DBMS_REDEFINITION.START_REDEF_TABLE('hr', 'admin_emp','int_admin_emp',
'empno empno, ename ename, job job, deptno+10 deptno, 0 bonus',
dbms_redefinition.cons_use_pk);
END;
/

4.Copy dependent objects. (Automatically create any triggers, indexes, grants, and constraints on hr.int_admin_emp.)

DECLARE
num_errors PLS_INTEGER;
BEGIN
DBMS_REDEFINITION.COPY_TABLE_DEPENDENTS('hr', 'admin_emp','int_admin_emp',
DBMS_REDEFINITION.CONS_ORIG_PARAMS, TRUE, TRUE, TRUE, TRUE, num_errors);
END;

Note that the ignore_errors argument is set to TRUE for this call. The reason is that the interim table was created with a primary key constraint, and when COPY_TABLE_DEPENDENTS attempts to copy the primary key constraint and index from the original table, errors occurs. You can ignore these errors, but you must run the query shown in the next step to see if there are other errors.

5.Query the DBA_REDEFINITION_ERRORS view to check for errors.

SQL> select object_name, base_table_name, ddl_txt from
DBA_REDEFINITION_ERRORS;

OBJECT_NAME BASE_TABLE_NAME DDL_TXT
------------- ---------------- ------------------------------
SYS_C005836 ADMIN_EMP CREATE UNIQUE INDEX "HR"."TMP$
$_SYS_C0058360" ON "HR"."INT_A
DMIN_EMP" ("EMPNO")

SYS_C005836 ADMIN_EMP ALTER TABLE "HR"."INT_ADMIN_EM
P" ADD CONSTRAINT "TMP$$_SYS_C
0058360" PRIMARY KEY

These errors are caused by the existing primary key constraint on the interim table and can be ignored. Note that with this approach, the names of the primary key constraint and index on the post-redefined table are changed. An alternate approach, one that avoids errors and name changes, would be to define the interim table without a primary key constraint. In this case, the primary key constraint and index are copied from the original table.

Note:

The best approach is to define the interim table with a primary key constraint, use REGISTER_DEPENDENT_OBJECT to register the primary key constraint and index, and then copy the remaining dependent objects with COPY_TABLE_DEPENDENTS. This approach avoids errors and ensures that the redefined table always has a primary key and that the dependent object names do not change.
6.Optionally, synchronize the interim table hr.int_admin_emp.

BEGIN
DBMS_REDEFINITION.SYNC_INTERIM_TABLE('hr', 'admin_emp', 'int_admin_emp');
END;
/

7.Complete the redefinition.

BEGIN
DBMS_REDEFINITION.FINISH_REDEF_TABLE('hr', 'admin_emp', 'int_admin_emp');
END;
/

The table hr.admin_emp is locked in the exclusive mode only for a small window toward the end of this step. After this call the table hr.admin_emp is redefined such that it has all the attributes of the hr.int_admin_emp table.

8.Drop the interim table.

Example 2

This example redefines a table to change columns into object attributes. The redefined table gets a new column that is an object type.

The original table, named CUSTOMER, is defined as follows:

Name Type
------------ -------------
CID NUMBER <- Primary key
NAME VARCHAR2(30)
STREET VARCHAR2(100)
CITY VARCHAR2(30)
STATE VARCHAR2(2)
ZIP NUMBER(5)

The type definition for the new object is:

CREATE TYPE ADDR_T AS OBJECT (
street VARCHAR2(100),
city VARCHAR2(30),
state VARCHAR2(2),
zip NUMBER(5, 0) );

Here are the steps for this redefinition:

1.Verify that the table is a candidate for online redefinition. Specify that the redefinition is to be done using primary keys or pseudo-primary keys.

BEGIN
DBMS_REDEFINITION.CAN_REDEF_TABLE('STEVE','CUSTOMER',
DBMS_REDEFINITION.CONS_USE_PK);
END;
/

2.Create the interim table int_customer.

CREATE TABLE INT_CUSTOMER(
CID NUMBER,
NAME VARCHAR2(30),
ADDR ADDR_T);

Note that no primary key is defined on the interim table. When dependent objects are copied in step 5, the primary key constraint and index are copied.

3.Because CUSTOMER is a very large table, specify parallel operations for the next step.

alter session force parallel dml parallel 4;
alter session force parallel query parallel 4;

4.Start the redefinition process using primary keys.

BEGIN
DBMS_REDEFINITION.START_REDEF_TABLE(
uname => 'STEVE',
orig_table => 'CUSTOMER',
int_table => 'INT_CUSTOMER',
col_mapping => 'cid cid, name name,
addr_t(street, city, state, zip) addr');
END;
/

Note that addr_t(street, city, state, zip) is a call to the object constructor.

5.Copy dependent objects.

DECLARE
num_errors PLS_INTEGER;
BEGIN
DBMS_REDEFINITION.COPY_TABLE_DEPENDENTS(
'STEVE','CUSTOMER','INT_CUSTOMER',DBMS_REDEFINITION.CONS_ORIG_PARAMS,
TRUE, TRUE, TRUE, FALSE, num_errors, TRUE);
END;
/

Note that for this call, the final argument indicates that table statistics are to be copied to the interim table.

6.Optionally synchronize the interim table.

BEGIN
DBMS_REDEFINITION.SYNC_INTERIM_TABLE('STEVE', 'CUSTOMER', 'INT_CUSTOMER');
END;
/

7.Complete the redefinition.

BEGIN
DBMS_REDEFINITION.FINISH_REDEF_TABLE('STEVE', 'CUSTOMER', 'INT_CUSTOMER');
END;
/

8.Drop the interim table.

Example 3

This example addresses the situation where a dependent object must be manually created and registered.

Consider the case where a table T1 has a column named C1, and where this column becomes C2 after the redefinition. Assume that there is an index Index1 on C1. In this case, COPY_TABLE_DEPENDENTS tries to create an index on the interim table corresponding to Index1, and tries to create it on a column C1, which does not exist on the interim table. This results in an error. You must therefore manually create the index on column C2 and register it. Here are the steps:

1.Create the interim table INT_T1 and create an index Int_Index1 on column C2.

2.Ensure that T1 is a candidate for online redefinition with CAN_REDEF_TABLE, and then begin the redefinition process with START_REDEF_TABLE.

3.Register the original (Index1) and interim (Int_Index1) dependent objects.

BEGIN
DBMS_REDEFINITION.REGISTER_DEPENDENT_OBJECT(
uname => 'STEVE',
orig_table => 'T1',
int_table => 'INT_T1',
dep_type => DBMS_REDEFINITION.CONS_INDEX,
dep_owner => 'STEVE',
dep_orig_name => 'Index1',
dep_int_name => 'Int_Index1');
END;
/

4.Use COPY_TABLE_DEPENDENTS to copy the remaining dependent objects.

5.Optionally synchronize the interim table.

6.Complete the redefinition and drop the interim table.

Example 4

This example demonstrates redefining a single partition. It moves the oldest partition of a range-partitioned sales table to a tablespace named TBS_LOW_FREQ. The table containing the partition to be redefined is defined as follows:

CREATE TABLE salestable
(s_productid NUMBER,
s_saledate DATE,
s_custid NUMBER,
s_totalprice NUMBER)
TABLESPACE users
PARTITION BY RANGE(s_saledate)
(PARTITION sal03q1 VALUES LESS THAN (TO_DATE('01-APR-2003', 'DD-MON-YYYY')),
PARTITION sal03q2 VALUES LESS THAN (TO_DATE('01-JUL-2003', 'DD-MON-YYYY')),
PARTITION sal03q3 VALUES LESS THAN (TO_DATE('01-OCT-2003', 'DD-MON-YYYY')),
PARTITION sal03q4 VALUES LESS THAN (TO_DATE('01-JAN-2004', 'DD-MON-YYYY')));

The table has a local partitioned index that is defined as follows:

CREATE INDEX sales_index ON salestable
(s_saledate, s_productid, s_custid) LOCAL;

Here are the steps. In the following procedure calls, note the extra argument: partition name (part_name).

1.Ensure that salestable is a candidate for redefinition.

BEGIN
DBMS_REDEFINITION.CAN_REDEF_TABLE(
uname => 'STEVE',
tname => 'SALESTABLE',
options_flag => DBMS_REDEFINITION.CONS_USE_ROWID,
part_name => 'sal03q1');
END;
/

2.Create the interim table in the TBS_LOW_FREQ tablespace. Because this is a redefinition of a range partition, the interim table is non-partitioned.

CREATE TABLE int_salestable
(s_productid NUMBER,
s_saledate DATE,
s_custid NUMBER,
s_totalprice NUMBER)
TABLESPACE tbs_low_freq;

3.Start the redefinition process using rowid.

BEGIN
DBMS_REDEFINITION.START_REDEF_TABLE(
uname => 'STEVE',
orig_table => 'salestable',
int_table => 'int_salestable',
col_mapping => NULL,
options_flag => DBMS_REDEFINITION.CONS_USE_ROWID,
part_name => 'sal03q1');
END;
/

4.Manually create any local indexes on the interim table.

CREATE INDEX int_sales_index ON int_salestable
(s_saledate, s_productid, s_custid)
TABLESPACE tbs_low_freq;

5.Optionally synchronize the interim table.

BEGIN
DBMS_REDEFINITION.SYNC_INTERIM_TABLE(
uname => 'STEVE',
orig_table => 'salestable',
int_table => 'int_salestable',
part_name => 'sal03q1');
END;
/

6.Complete the redefinition.

BEGIN
DBMS_REDEFINITION.FINISH_REDEF_TABLE(
uname => 'STEVE',
orig_table => 'salestable',
int_table => 'int_salestable',
part_name => 'sal03q1');
END;
/

7.Drop the interim table.

The following query shows that the oldest partition has been moved to the new tablespace:

select partition_name, tablespace_name from user_tab_partitions
where table_name = 'SALESTABLE';

PARTITION_NAME TABLESPACE_NAME
------------------------------ ------------------------------
SAL03Q1 TBS_LOW_FREQ
SAL03Q2 USERS
SAL03Q3 USERS
SAL03Q4 USERS

4 rows selected.
Privileges Required for the DBMS_REDEFINITION Package
Execute privileges on the DBMS_REDEFINITION package are granted to EXECUTE_CATALOG_ROLE. In addition to having execute privileges on this package, you must be granted the following privileges:

•CREATE ANY TABLE

•ALTER ANY TABLE

•DROP ANY TABLE

•LOCK ANY TABLE

•SELECT ANY TABLE

The following additional privileges are required to execute COPY_TABLE_DEPENDENTS:

•CREATE ANY TRIGGER

•CREATE ANY INDEX


77 DBMS_REDEFINITIONThe DBMS_REDEFINITION package provides an interface to perform an online redefinition of tables.

See Also:

Oracle Database Administrator's Guide for more information about online redefinition of tables
This chapter contains the following topics:

•Using DBMS_REDEFINITION

◦Overview

◦Constants

◦Operational Notes

•Summary of DBMS_REDEFINITION Subprograms


--------------------------------------------------------------------------------

Using DBMS_REDEFINITION
•Overview

•Constants

•Operational Notes


--------------------------------------------------------------------------------

Overview
To achieve online redefinition, incrementally maintainable local materialized views are used. These logs keep track of the changes to the master tables and are used by the materialized views during refresh synchronization.


--------------------------------------------------------------------------------

Constants
The DBMS_REDEFINITION package uses the constants shown in Table 77-1, "DBMS_REDEFINITION Constants":

Table 77-1 DBMS_REDEFINITION Constants

Constant Type Value Description
CONS_CONSTRAINT
PLS_INTEGER
3
Used to specify that dependent object type is a constraint

CONS_INDEX
PLS_INTEGER
2
Used to specify that dependent object type is a index

CONS_ORIG_PARAMS
PLS_INTEGER
1
Used to specify that indexes should be cloned with their original storage parameters

CONS_TRIGGER
PLS_INTEGER
4
Used to specify that dependent object type is a trigger

CONS_USE_PK
BINARY_INTEGER
1
Used to indicate that the redefinition should be done using primary keys or pseudo-primary keys (unique keys with all component columns having not-NULL constraints)

CONS_USE_ROWID
BINARY_INTEGER
2
Used to indicate that the redefinition should be done using rowids




--------------------------------------------------------------------------------

Operational Notes
•CONS_USE_PK and CONS_USE_ROWID are constants used as input to the "options_flag" parameter in both the START_REDEF_TABLE Procedure and CAN_REDEF_TABLE Procedure. CONS_USE_ROWID is used to indicate that the redefinition should be done using rowids while CONS_USE_PK implies that the redefinition should be done using primary keys or pseudo-primary keys (which are unique keys with all component columns having NOT NULL constraints).

•CONS_INDEX, CONS_TRIGGER and CONS_CONSTRAINT are used to specify the type of the dependent object being (un)registered in REGISTER_DEPENDENT_OBJECT Procedure and UNREGISTER_DEPENDENT_OBJECT Procedure (parameter "dep_type").

CONS_INDEX ==> dependent object is of type INDEX

CONS_TRIGGER ==> dependent object is of type TRIGGER

CONS_CONSTRAINT==> dependent object type is of type CONSTRAINT

•CONS_ORIG_PARAMS as used as input to the "copy_indexes" parameter in COPY_TABLE_DEPENDENTS Procedure. Using this parameter implies that the indexes on the original table be copied onto the interim table using the same storage parameters as that of the original index.


--------------------------------------------------------------------------------
Rules and Limits
For information about various rules and limits that apply to implementation of this package, see the Oracle Database Administrator's Guide.


--------------------------------------------------------------------------------

Summary of DBMS_REDEFINITION Subprograms
Table 77-2 DBMS_REDEFINITION Package Subprograms

Subprogram Description
ABORT_REDEF_TABLE Procedure
Cleans up errors that occur during the redefinition process and removes all temporary objects created by the reorganization process

CAN_REDEF_TABLE Procedure
Determines if a given table can be redefined online

COPY_TABLE_DEPENDENTS Procedure
Copies the dependent objects of the original table onto the interim table

FINISH_REDEF_TABLE Procedure
Completes the redefinition process.

REGISTER_DEPENDENT_OBJECT Procedure
Registers a dependent object (index, trigger or constraint) on the table being redefined and the corresponding dependent object on the interim table

START_REDEF_TABLE Procedure
Initiates the redefinition process

SYNC_INTERIM_TABLE Procedure
Keeps the interim table synchronized with the original table

UNREGISTER_DEPENDENT_OBJECT Procedure
Unregisters a dependent object (index, trigger or constraint) on the table being redefined and the corresponding dependent object on the interim table




--------------------------------------------------------------------------------

ABORT_REDEF_TABLE Procedure
This procedure cleans up errors that occur during the redefinition process. This procedure can also be used to terminate the redefinition process any time after the START_REDEF_TABLE Procedure has been called and before the FINISH_REDEF_TABLE Procedure is called. This process will remove the temporary objects that are created by the redefinition process such as materialized view logs.

Syntax

DBMS_REDEFINITION.ABORT_REDEF_TABLE (
uname IN VARCHAR2,
orig_table IN VARCHAR2,
int_table IN VARCHAR2,
part_name IN VARCHAR2 := NULL);
Parameters

Table 77-3 ABORT_REDEF_TABLE Procedure Parameters

Parameter Description
uname
The schema name of the tables.

orig_table
The name of the table to be redefined.

int_table
The name of the interim table.

part_name
The name of the partition being redefined. If redefining only a single partition of a table, specify the partition name in this parameter. NULL implies the entire table is being redefined.




--------------------------------------------------------------------------------

CAN_REDEF_TABLE Procedure
This procedure determines if a given table can be redefined online. This is the first step of the online redefinition process. If the table is not a candidate for online redefinition, an error message is raised.

Syntax

DBMS_REDEFINITION.CAN_REDEF_TABLE (
uname IN VARCHAR2,
tname IN VARCHAR2,
options_flag IN PLS_INTEGER := 1,
part_name IN VARCHAR2 := NULL);
Parameters

Table 77-4 CAN_REDEF_TABLE Procedure Parameters

Parameter Description
uname
The schema name of the table

tname
The name of the table to be re-organized

options_flag
Indicates the type of redefinition method to use.

•If dbms_redefinition.cons_use_pk, the redefinition is done using primary keys or pseudo-primary keys (unique keys with all component columns having NOT NULL constraints). The default method of redefinition is using primary keys.

•If dbms_redefinition.cons_use_rowid, the redefinition is done using rowids.

part_name
The name of the partition being redefined. If redefining only a single partition of a table, specify the partition name in this parameter. NULL implies the entire table is being redefined.



Exceptions

If the table is not a candidate for online redefinition, an error message is raised.


--------------------------------------------------------------------------------

COPY_TABLE_DEPENDENTS Procedure
This procedure clones the dependent objects of the table being redefined onto the interim table and registers the dependent objects. This procedure does not clone the already registered dependent objects.

This subprogram is used to clone the dependent objects like grants, triggers, constraints and privileges from the table being redefined to the interim table (which represents the post-redefinition table).

Syntax

DBMS_REDEFINITION.COPY_TABLE_DEPENDENTS(
uname IN VARCHAR2,
orig_table IN VARCHAR2,
int_table IN VARCHAR2,
copy_indexes IN PLS_INTEGER := 1,
copy_triggers IN BOOLEAN := TRUE,
copy_constraints IN BOOLEAN := TRUE,
copy_privileges IN BOOLEAN := TRUE,
ignore_errors IN BOOLEAN := FALSE,
num_errors OUT PLS_INTEGER,
copy_statistics IN BOOLEAN := FALSE);
Parameters

Table 77-5 COPY_TABLE_DEPENDENTS Procedure Parameters

Parameter Description
uname
The schema name of the tables.

orig_table
The name of the table being redefined.

int_table
The name of the interim table.

copy_indexes
A flag indicating whether to copy the indexes

•0 - do not copy any index

•dbms_redefinition.cons_orig_params – copy the indexes using the physical parameters of the source indexes

copy_triggers
TRUE = clone triggers, FALSE = do nothing

copy_constraints
TRUE = clone constraints, FALSE = do nothing. If compatibility setting is 10.2 or higher, then clone CHECK and NOT NULL constraints

copy_privileges
TRUE = clone privileges, FALSE = do nothing

ignore_errors
TRUE = if an error occurs while cloning a particular dependent object, then skip that object and continue cloning other dependent objects. FALSE = that the cloning process should stop upon encountering an error.

num_errors
The number of errors that occurred while cloning dependent objects

copy_statistics
TRUE = copy statistics, FALSE = do nothing



Usage Notes

•The user must check the column num_errors before proceeding to ensure that no errors occurred during the cloning of the objects.

•In case of an error, the user should fix the cause of the error and call the COPY_TABLE_DEPENDENTS Procedure again to clone the dependent object. Alternatively the user can manually clone the dependent object and then register the manually cloned dependent object using the REGISTER_DEPENDENT_OBJECT Procedure.

•All cloned referential constraints involving the interim tables will be created disabled (they will be automatically enabled after the redefinition) and all triggers on interim tables will not fire till the redefinition is completed. After the redefinition is complete, the cloned objects will be renamed to the corresponding pre-redefinition names of the objects (from which they were cloned from).

•It is the user's responsibility that the cloned dependent objects are unaffected by the redefinition. All the triggers will be cloned and it is the user's responsibility that the cloned triggers are unaffected by the redefinition.


--------------------------------------------------------------------------------

FINISH_REDEF_TABLE Procedure
This procedure completes the redefinition process. Before this step, you can create new indexes, triggers, grants, and constraints on the interim table. The referential constraints involving the interim table must be disabled. After completing this step, the original table is redefined with the attributes and data of the interim table. The original table is locked briefly during this procedure.

Syntax

DBMS_REDEFINITION.FINISH_REDEF_TABLE (
uname IN VARCHAR2,
orig_table IN VARCHAR2,
int_table IN VARCHAR2,
part_name IN VARCHAR2 := NULL);
Parameters

Table 77-6 FINISH_REDEF_TABLE Procedure Parameters

Parameters Description
uname
The schema name of the tables.

orig_table
The name of the table to be redefined.

int_table
The name of the interim table.

part_name
The name of the partition being redefined. If redefining only a single partition of a table, specify the partition name in this parameter. NULL implies the entire table is being redefined.




--------------------------------------------------------------------------------

REGISTER_DEPENDENT_OBJECT Procedure
This procedure registers a dependent object (index, trigger or constraint) on the table being redefined and the corresponding dependent object on the interim table.

This can be used to have the same object on each table but with different attributes. For example: for an index, the storage and tablespace attributes could be different but the columns indexed remain the same

Syntax

DBMS_REDEFINITION.REGISTER_DEPEPENDENT_OBJECT(
uname IN VARCHAR2,
orig_table IN VARCHAR2,
int_table IN VARCHAR2,
dep_type IN PLS_INTEGER,
dep_owner IN VARCHAR2,
dep_orig_name IN VARCHAR2,
dep_int_name IN VARCHAR2);
Parameters

Table 77-7 REGISTER_DEPENDENT_OBJECT Procedure Parameters

Parameters Description
uname
The schema name of the tables.

orig_table
The name of the table to be redefined.

int_table
The name of the interim table.

dep_type
The type of the dependent object.

dep_owner
The owner of the dependent object.

dep_orig_name
The name of the original dependent object.

dep_int_name
The name of the interim dependent object.



Usage Notes

•Attempting to register an already registered object will raise an error.

•Registering a dependent object will automatically remove that object from DBA_REDEFINITION_ERRORS if an entry exists for that object.


--------------------------------------------------------------------------------

START_REDEF_TABLE Procedure
Prior to calling this procedure, you must manually create an empty interim table (in the same schema as the table to be redefined) with the desired attributes of the post-redefinition table, and then call this procedure to initiate the redefinition.

Syntax

DBMS_REDEFINITION.START_REDEF_TABLE (
uname IN VARCHAR2,
orig_table IN VARCHAR2,
int_table IN VARCHAR2,
col_mapping IN VARCHAR2 := NULL,
options_flag IN BINARY_INTEGER := 1,
orderby_cols IN VARCHAR2 := NULL,
part_name IN VARCHAR2 := NULL);
Parameters

Table 77-8 START_REDEF_TABLE Procedure Parameters

Parameter Description
uname
The schema name of the tables.

orig_table
The name of the table to be redefined.

int_table
The name of the interim table.

col_mapping
The mapping information from the columns in the original table to the columns in the interim table. (This is similar to the column list on the SELECT clause of a query.) If NULL, all the columns in the original table are selected and have the same name after redefinition.

options_flag
Indicates the type of redefinition method to use.

•If dbms_redefinition.cons_use_pk, the redefinition is done using primary keys or pseudo-primary keys (unique keys with all component columns having NOT NULL constraints). The default method of redefinition is using primary keys.

•If dbms_redefinition.cons_use_rowid, the redefinition is done using rowids.

orderby_cols
This optional parameter accepts the list of columns (along with the optional keyword(s) ascending/descending) with which to order by the rows during the initial instantiation of the interim table (the order by is only done for the initial instantiation and not for subsequent synchronizations)

part_name
The name of the partition being redefined. If redefining only a single partition of a table, specify the partition name in this parameter. NULL implies the entire table is being redefined.




--------------------------------------------------------------------------------

SYNC_INTERIM_TABLE Procedure
This procedure keeps the interim table synchronized with the original table.

Syntax

DBMS_REDEFINITION.SYNC_INTERIM_TABLE (
uname IN VARCHAR2,
orig_table IN VARCHAR2,
int_table IN VARCHAR2,
part_name IN VARCHAR2 := NULL);
Parameters

Table 77-9 SYNC_INTERIM_TABLE Procedure Parameters

Parameter Description
uname
The schema name of the table.

orig_table
The name of the table to be redefined.

int_table
The name of the interim table.

part_name
The name of the partition being redefined. If redefining only a single partition of a table, specify the partition name in this parameter. NULL implies the entire table is being redefined.



Usage Notes

•This step is useful in minimizing the amount of synchronization needed to be done by the FINISH_REDEF_TABLE Procedure before completing the online redefinition.

•This procedure can be called between long running operations (such as CREATE INDEX) on the interim table to sync it up with the data in the original table and speed up subsequent operations.


--------------------------------------------------------------------------------

UNREGISTER_DEPENDENT_OBJECT Procedure
This procedure unregisters a dependent object (index, trigger or constraint) on the table being redefined and the corresponding dependent object on the interim table.

Syntax

DBMS_REDEFINITION.UNREGISTER_DEPEPENDENT_OBJECT(
uname IN VARCHAR2,
orig_table IN VARCHAR2,
int_table IN VARCHAR2,
dep_type IN PLS_INTEGER,
dep_owner IN VARCHAR2,
dep_orig_name IN VARCHAR2,
dep_int_name IN VARCHAR2);
Parameters

Table 77-10 UNREGISTER_DEPENDENT_OBJECT Procedure Parameters

Parameters Description
uname
The schema name of the tables.

orig_table
The name of the table to be redefined.

int_table
The name of the interim table.

dep_type
The type of the dependent object.

dep_owner
The owner of the dependent object.

dep_orig_name
The name of the original dependent object.

dep_int_name
The name of the interim dependent object.