jueves, 8 de julio de 2010

Laboratorios Flashback

SQL> shutdown immediate;

SQL> startup mount;

ORACLE instance started.





SQL> alter system set DB_FLASHBACK_RETENTION_TARGET=4320;

System altered.



SQL> alter system set DB_RECOVERY_FILE_DEST_SIZE=536870912;

System altered.



SQL> alter system set DB_RECOVERY_FILE_DEST='/u02/fra';

System altered.



SQL> alter database flashback on;

Database altered.



SQL> alter database open;

Database altered.



Alter system set recyclebin=on scope=both;















1 LABORATORIO





SQL*Plus: Release 10.2.0.4.0 - Production on Thu Jul 8 10:54:13 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> connect hr/hr;

Conectado.

SQL>

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> create table COPIA_REGIONS as (select * from REGIONS);



Tabla creada.



SQL> select count(*) from COPIA_REGIONS;



COUNT(*)

----------

4



SQL> drop table COPIA_REGIONS;



Tabla borrada.



SQL> show recyclebin;

ORIGINAL NAME RECYCLEBIN NAME OBJECT TYPE DROP TIME

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

COPIA_REGIONS BIN$iuNRnAWxvlDgQAB/AQAL8Q==$0 TABLE 2010-07-08:11:00:42

SQL>



SQL> SQL> flashback table COPIA_REGIONS to before drop;



Flashback terminado.



VALIDAMOS LOS DATOS DE LA TABLA



SQL> select * from COPIA_REGIONS;



REGION_ID REGION_NAME

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

1 Europe

2 Americas

3 Asia

4 Middle East and Africa



VER EL TAMAÑO DE FLASHBACK



SQL> select * from v$flash_recovery_area_usage;



FILE_TYPE PERCENT_SPACE_USED PERCENT_SPACE_RECLAIMABLE NUMBER_OF_FILES

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

CONTROLFILE 0 0 0

ONLINELOG 0 0 0

ARCHIVELOG ,06 ,06 5

BACKUPPIECE 11,66 0 4

IMAGECOPY 0 0 0

FLASHBACKLOG 0 0 0





OTRO METODO.



SQL> show recyclebin;

ORIGINAL NAME RECYCLEBIN NAME OBJECT TYPE DROP TIME

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

COPIA_REGIONS BIN$iuNRnAWxvlDgQAB/AQAL8Q==$0 TABLE 2010-07-08:11:00:42

SQL>

SQL>

SQL> flashback table "BIN$iuNRnAWxvlDgQAB/AQAL8Q==$0" to before drop;



Flashback terminado.





2 LABORATORIO PUNTOS DE RESTAURACION



SQL> CREATE RESTORE POINT good_data;



Punto de restauracion creado.



SQL> select * from hr.regions where region_id=1;



REGION_ID REGION_NAME

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

1 Europe



SQL>



SQL> update hr.regions set region_name='Colombia' where region_id=1;



1 fila actualizada.



SQL> commit;



Confirmacion terminada.



SQL> select * from hr.regions where region_id=1;



REGION_ID REGION_NAME

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

1 Colombia





SQL> flashback table hr.regions to restore point good_data;



ERROR en linea 1:

ORA-08189: no se puede realizar flashback en la tabla porque el movimiento de

filas no esta activado



SQL> alter table hr.regions enable row movement;



Tabla modificada.





SQL> flashback table hr.regions to restore point good_data;



Flashback terminado.



SQL> select * from hr.regions where region_id=1;



REGION_ID REGION_NAME

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

1 Europe



SQL>





PARA VER PUNTOS DE RESTAURACION CREADOS EN LA INSTANCIA



SQL> select name, storage_size from v$restore_point;



BORRAR PUNTO DE RESTAURACION



drop restore point good_data;





3. LABORATORIO FLASHBACK DATABASE



NOTA: SI NO ESTA ACTIVADA LA OPCION DE LA BD NO FUNCIONA



EJEMPLO CUANDO NO ESTA ACTIVADA



VER CURRENT_SCN



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



SQL*Plus: Release 10.2.0.4.0 - Production on Fri Jul 9 12:04:28 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> 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

-----------

708133



SQL> flashback database to scn 708132;

flashback database to scn 708132

*

ERROR en linea 1:

ORA-38757: La base de datos debe estar montada y no estar abierta para

FLASHBACK.





SQL> shutdown immediate;

Base de datos cerrada.

Base de datos desmontada.

Instancia ORACLE cerrada.

SQL>



SQL> start mount;

SP2-0310: no se ha podido abrir el archivo "mount.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> flashback database to scn 70812;

flashback database to scn 70812

*

ERROR en linea 1:

ORA-38726: El registro de flashback de la base de datos no esta activado.



SQL>



4 LABORATORIO FLASHBACK DATABASE

ACTIVACION FLASHBACK




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



SQL*Plus: Release 10.2.0.4.0 - Production on Fri Jul 9 14:39:51 2010



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



Connected to an idle instance.



SQL> startup mount;

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.

SQL>

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 2

Siguiente secuencia de log para archivar 4

Secuencia de log actual 4

SQL>

SQL> alter database flashback on;



Base de datos modificada.



SQL> show parameter db_flashback;



NAME TYPE VALUE

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

db_flashback_retention_target integer 1440

SQL> select current_scn,flashback_on from v$database;



CURRENT_SCN FLASHBACK_ON

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

0 YES



SQL> alter database flashback off;



Base de datos modificada.



SQL> select current_scn,flashback_on from v$database;



CURRENT_SCN FLASHBACK_ON

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

0 NO



SQL> alter database flashback on;



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 09/07/10 MOUNTED NO 1 STARTED

ALLOWED NO ACTIVE PRIMARY_INSTANCE NORMAL NO





SQL> alter database open;



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 09/07/10 OPEN NO 1 STARTED

ALLOWED NO ACTIVE PRIMARY_INS





EMPEZAMOS EL LABORATORIO FLASHBACK DATABASE



SQL> connect hr/hr;

Conectado.

SQL>

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

COPIA_REGIONS TABLE





SQL> drop table COPIA_REGIONS;



Tabla borrada.



SQL>



SQL> create table COPIA_EMPLOYEES as (select * from employees);



Tabla creada.



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

BIN$ivm+7nDFpazgQAB/AQALUg==$0 TABLE

COPIA_EMPLOYEES TABLE



SQL> delete from COPIA_EMPLOYEES where 1=1;



107 filas suprimidas.



SQL> commit;



Confirmacion terminada.



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 ~]$ sqlplus '/as sysdba'



SQL*Plus: Release 10.2.0.4.0 - Production on Fri Jul 9 14:55:59 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



CURRENT ACTUAL



SQL> select current_scn from v$database;



CURRENT_SCN

-----------

708510





SQL> desc hr.regions;

Nombre ?Nulo? Tipo

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

REGION_ID NOT NULL NUMBER

REGION_NAME VARCHAR2(25)



SQL> insert into hr.regions values(6,'COLOMBIA');



1 fila creada.



SQL> insert into hr.regions values(7,'PERU');



1 fila creada.



SQL> COMMIT;



Confirmacion terminada.



SQL> select * from hr.regions;



REGION_ID REGION_NAME

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

1 Europe

2 Americas

3 Asia

4 Middle East and Africa

6 COLOMBIA

7 PERU



6 filas seleccionadas.



COMO HAGO PARA DEVOLVERME A UN ESTADO ANTERIOR DE LA BASE DE DATOS ANTES DEL DELETE



SQL> shutdown immediate;

Base de datos cerrada.

Base de datos desmontada.

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> 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 09/07/10 MOUNTED NO 1 STARTED

ALLOWED NO ACTIVE PRIMARY_INSTANCE NORMAL NO





SQL> select current_scn from v$database;



CURRENT_SCN

-----------

0



VALIDAR EL RESULTADO DE ESTE QUERY



SQL> SELECT OLDEST_FLASHBACK_SCN, OLDEST_FLASHBACK_TIME FROM V$FLASHBACK_DATABASE_LOG;





SQL> flashback database to scn 708358;



Flashback terminado.



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>



VER NUEVA INCARNATION GENERADA EN LA INSTANCIA



[oracle@oracle11g ~]$ rman target /



Recovery Manager: Release 10.2.0.4.0 - Production on Fri Jul 9 15:02:27 2010



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



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



RMAN> list incarnation;



using target database control file instead of recovery catalog



List of Database Incarnations

DB Key Inc Key DB Name DB ID STATUS Reset SCN Reset Time

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

1 1 ACME 1908099333 PARENT 584972 06-JUL-10

2 2 ACME 1908099333 CURRENT 654164 07-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 584972 06-JUL-10

2 2 ACME 1908099333 PARENT 654164 07-JUL-10

3 3 ACME 1908099333 CURRENT 708361 09-JUL-10



RMAN>



VALIDAR LA DATA A VER SI QUEDO BIEN





SQL> connect hr/hr;

Conectado.

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

COPIA_REGIONS TABLE



9 filas seleccionadas.



SQL> select * from regions;



REGION_ID REGION_NAME

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

1 Europe

2 Americas

3 Asia

4 Middle East and Africa



SQL>



NOTA: REALIZAR BACKUP VIA RMAN FULL DATABASE ARCHIVELOG





5 LABORATORIO FLASHBACK QUERY



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



SQL*Plus: Release 10.2.0.4.0 - Production on Fri Jul 9 15:23:03 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> connect hr/hr;

Conectado.

SQL> CREATE TABLE emp

(empno NUMBER PRIMARY KEY,

empname VARCHAR2(16),

salary NUMBER);

2 3 4

Tabla creada.



SQL> INSERT INTO emp VALUES (111, 'Mike', 555);



1 fila creada.



SQL> commit;



Confirmacion terminada.



SQL> CREATE TABLE dept

(deptno NUMBER,

deptname VARCHAR2(32));

2 3

Tabla creada.



SQL> INSERT INTO dept VALUES (10, 'Accounting');



1 fila creada.



SQL> commit;



Confirmacion terminada.



SQL> UPDATE emp SET salary = salary + 100 WHERE empno = 111;



1 fila actualizada.



SQL> COMMIT;



Confirmacion terminada.



SQL> select * from emp;



EMPNO EMPNAME SALARY

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

111 Mike 655



SQL>

SQL> INSERT INTO dept VALUES (20, 'Finance');



1 fila creada.



SQL> DELETE FROM emp WHERE empno = 111;



1 fila suprimida.



SQL> commit;



Confirmacion terminada.



SQL>





REALIZAN UNA NUEVA TRANSACCION PARA EL CODIGO 111



a new transaction reinserts employee id 111 with a new employee name into the emp table.



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



SQL*Plus: Release 10.2.0.4.0 - Production on Fri Jul 9 15:30:54 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 versions_xid XID, versions_startscn START_SCN,

versions_endscn END_SCN, versions_operation OPERATION,

empname, salary FROM hr.emp

VERSIONS BETWEEN SCN MINVALUE AND MAXVALUE

where empno = 111;

2 3 4 5

XID START_SCN END_SCN O EMPNAME SALARY

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

0800230006010000 710161 I Tom 927

0600220049010000 710077 D Mike 655

0500290027010000 710057 710077 U Mike 655

04000800DB000000 709971 710057 I Mike 555



SQL>



VALIDAMOS EL undo_sql



SELECT xid, start_scn, commit_scn,

operation, logon_user,

undo_sql FROM flashback_transaction_query

WHERE xid = HEXTORAW('0600220049010000')

/



XID START_SCN COMMIT_SCN OPERATION LOGON_USER

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

UNDO_SQL

0600220049010000 710065 710077 DELETE HR

insert into "HR"."EMP"("EMPNO","EMPNAME","SALARY") values ('111','Mike','655');



0600220049010000 710065 710077 INSERT HR

delete from "HR"."DEPT" where ROWID = 'AAAM7pAAEAAAAGcAAB';



0600220049010000 710065 710077 BEGIN HR



•To obtain an SCN to use later with a flashback feature, use



DBMS_FLASHBACK.GET_SYSTEM_CHANGE_NUMBER.





6 MANEJO PAPELERA RECICLAJE

SELECT * FROM RECYCLEBIN;



ALTER SESSION SET recyclebin = OFF;

ALTER SYSTEM SET recyclebin = OFF;



VISTAS



USER_RECYCLEBIN

This view can be used by users to see their own dropped objects in the recycle bin. It has a synonym RECYCLEBIN, for ease of use.



DBA_RECYCLEBIN

This view gives administrators visibility to all dropped objects in the recycle bin



PURGE TABLE nombre_tabla;

Elimina la tabla indicada de la papelera. El nombre de la tabla puede ser el nombre original o el renombrado.



PURGE INDEX nombre_índice;

Elimina el índice indicado de la papelera. El nombre del índice es el nombre original y no el renombrado.



PURGE RECYCLEBIN;

Elimina todos los objetos (del usuario que lanza la orden) de la papelera.



PURGE DBA_RECYCLEBIN;

Elimina todos los objetos (de todos los usuarios) de la papelera. Solo un SYSDBA puede lanzar este comando.



PURGE TABLESPACE nombre_tablespace;

Elimina todos los objetos (del usuario) de la papelera que residan en el tablespace indicado.



PURGE TABLESPACE nombre_tablespace USERS nombre_usuario;

Elimina todos los objetos de la papelera que residan en el tablespace indicado y pertenezcan el usuario indicado.



SQL> show recyclebin;ORIGINAL NAME RECYCLEBIN NAME OBJECT TYPE DROP TIME---------------- ------------------------------ ------------ -------------------PENDIENTES BIN$WJ4MAU6RU7XgRAAUT2gJUg==$0 TABLE 2008-10-06:17:17:21PRUEBA BIN$WMDKHy9/HiHgRAAUT2gJUg==$0 TABLE 2008-10-08:10:44:19PRUEBA BIN$WL+UR7qDG8/gRAAUT2gJUg==$0 TABLE 2008-10-08:09:17:41PRUEBA BIN$WL83vpCuGmTgRAAUT2gJUg==$0 TABLE 2008-10-08:08:51:49



SQL> flashback table pendientes to before drop;

Flashback complete.

SQL>





SQL> show recyclebin

ORIGINAL NAME RECYCLEBIN NAME OBJECT TYPE DROP TIME

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

EMPLOYEES BIN$yrMKlZaVMhfgNAgAIMenRA==$0 TABLE 2003-10-27:14:00:19



Tmabien se puede realizar un query con el nombre de la tabla de esta forma.



example: SELECT * FROM "BIN$yrMKlZaVMhfgNAgAIMenRA==$0";



PURGE TABLE BIN$jsleilx392mk2=293$0;



PURGE TABLESPACE example;



PURGE TABLESPACE example USER oe;



Limpia toda la papelera reciclaje



PURGE RECYCLEBIN;