RMAN RESTORE TABLE TPITR

 # SCN

RECOVER TABLE TEST.T1
  UNTIL SCN 1853267
  AUXILIARY DESTINATION '/u01/backup'  
  REMAP TABLE 'TEST'.'T1':'T1_PREV';

RECOVER TABLE TEST.T1
  UNTIL SEQUENCE 200
  AUXILIARY DESTINATION '/u01/backup'  
  REMAP TABLE 'TEST'.'T1':'T1_PREV';

# TIME
RECOVER TABLE TEST.T1
  UNTIL TIME "TO_DATE('2020-12-14 19:00', 'YYYY-MM-DD:HH24:MI:SS')"
  AUXILIARY DESTINATION '/u01/backup'  
  REMAP TABLE 'TEST'.'T1':'T1_PREV';

TECNOLOGIA TPITR

LABORATORIO RESTORE TABLE UNTIL TIME

SQL> !date

Mon Dec 14 19:13:47 -05 2020

SQL> select current_scn from v$database;

CURRENT_SCN

-----------

    6003465

SQL> connect hr/hr;

Connected.

SQL> 

SQL> update hr.lolito set salary=0, email='NELSONJAVIER63@GMAIL.COM';

107 rows updated.

SQL> commit;

Commit complete.

RMAN> RECOVER TABLE HR.LOLITO UNTIL TIME "TO_DATE('2020-12-14 19:13:47', 'YYYY-MM-DD:HH24:MI:SS')" AUXILIARY DESTINATION '/u01/backup/';

RMAN> RECOVER TABLE HR.LOLITO UNTIL TIME "TO_DATE('2020-12-14 19:13:47', 'YYYY-MM-DD:HH24:MI:SS')" AUXILIARY DESTINATION '/u01/backup/';

Starting recover at 2020-12-14:19:23:07

current log archived

RMAN-00571: ===========================================================

RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============

RMAN-00571: ===========================================================

RMAN-03002: failure of recover command at 12/14/2020 19:23:09

RMAN-05063: Cannot recover specified tables

RMAN-05112: table "HR"."LOLITO" already exists

RMAN> RECOVER TABLE HR.LOLITO UNTIL TIME "TO_DATE('2020-12-14 19:13:47', 'YYYY-MM-DD:HH24:MI:SS')" AUXILIARY DESTINATION '/u01/backup/';

Starting recover at 2020-12-14:19:23:49

allocated channel: ORA_DISK_1

channel ORA_DISK_1: SID=72 device type=DISK

RMAN-05026: WARNING: presuming following set of tablespaces applies to specified Point-in-Time

List of tablespaces expected to have UNDO segments

Tablespace SYSTEM

Tablespace UNDOTBS1

Creating automatic instance, with SID='pfuh'

initialization parameters used for automatic instance:

db_name=ACME

db_unique_name=pfuh_pitr_ACME

compatible=12.1.0.0.0

db_block_size=8192

db_files=200

sga_target=1G

processes=80

diagnostic_dest=/u01/app/oracle

db_create_file_dest=/u01/backup/

log_archive_dest_1='location=/u01/backup/'

#No auxiliary parameter file used

starting up automatic instance ACME

Oracle instance started

Total System Global Area    1068937216 bytes

Fixed Size                     2296576 bytes

Variable Size                281019648 bytes

Database Buffers             780140544 bytes

Redo Buffers                   5480448 bytes

Automatic instance created

contents of Memory Script:

{

# set requested point in time

set until  time "TO_DATE('2020-12-14 19:13:47', 'YYYY-MM-DD:HH24:MI:SS')";

# restore the controlfile

restore clone controlfile;

# mount the controlfile

sql clone 'alter database mount clone database';

# archive current online log 

sql 'alter system archive log current';

}

executing Memory Script

executing command: SET until clause

Starting restore at 2020-12-14:19:24:01

allocated channel: ORA_AUX_DISK_1

channel ORA_AUX_DISK_1: SID=7 device type=DISK

channel ORA_AUX_DISK_1: starting datafile backup set restore

channel ORA_AUX_DISK_1: restoring control file

channel ORA_AUX_DISK_1: reading from backup piece +FRA/ACME/AUTOBACKUP/2020_12_14/s_1059158709.366.1059158711

channel ORA_AUX_DISK_1: piece handle=+FRA/ACME/AUTOBACKUP/2020_12_14/s_1059158709.366.1059158711 tag=TAG20201214T184509

channel ORA_AUX_DISK_1: restored backup piece 1

channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:06

output file name=/u01/backup/ACME/controlfile/o1_mf_hxj0k64p_.ctl

Finished restore at 2020-12-14:19:24:07

sql statement: alter database mount clone database

sql statement: alter system archive log current

contents of Memory Script:

{

# set requested point in time

set until  time "TO_DATE('2020-12-14 19:13:47', 'YYYY-MM-DD:HH24:MI:SS')";

# set destinations for recovery set and auxiliary set datafiles

set newname for clone datafile  1 to new;

set newname for clone datafile  4 to new;

set newname for clone datafile  3 to new;

set newname for clone tempfile  1 to new;

# switch all tempfiles

switch clone tempfile all;

# restore the tablespaces in the recovery set and the auxiliary set

restore clone datafile  1, 4, 3;

switch clone datafile all;

}

executing Memory Script

executing command: SET until clause

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

renamed tempfile 1 to /u01/backup/ACME/datafile/o1_mf_temp_%u_.tmp in control file

Starting restore at 2020-12-14:19:24:13

using channel ORA_AUX_DISK_1

channel ORA_AUX_DISK_1: starting datafile backup set restore

channel ORA_AUX_DISK_1: specifying datafile(s) to restore from backup set

channel ORA_AUX_DISK_1: restoring datafile 00001 to /u01/backup/ACME/datafile/o1_mf_system_%u_.dbf

channel ORA_AUX_DISK_1: restoring datafile 00004 to /u01/backup/ACME/datafile/o1_mf_undotbs1_%u_.dbf

channel ORA_AUX_DISK_1: restoring datafile 00003 to /u01/backup/ACME/datafile/o1_mf_sysaux_%u_.dbf

channel ORA_AUX_DISK_1: reading from backup piece +FRA/ACME/BACKUPSET/2020_12_14/nnndf0_tag20201214t184339_0.362.1059158619

channel ORA_AUX_DISK_1: piece handle=+FRA/ACME/BACKUPSET/2020_12_14/nnndf0_tag20201214t184339_0.362.1059158619 tag=TAG20201214T184339

channel ORA_AUX_DISK_1: restored backup piece 1

channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:45

Finished restore at 2020-12-14:19:24:59

datafile 1 switched to datafile copy

input datafile copy RECID=11 STAMP=1059161099 file name=/u01/backup/ACME/datafile/o1_mf_system_hxj0kg5n_.dbf

datafile 4 switched to datafile copy

input datafile copy RECID=12 STAMP=1059161099 file name=/u01/backup/ACME/datafile/o1_mf_undotbs1_hxj0kg5t_.dbf

datafile 3 switched to datafile copy

input datafile copy RECID=13 STAMP=1059161099 file name=/u01/backup/ACME/datafile/o1_mf_sysaux_hxj0kg5h_.dbf

contents of Memory Script:

{

# set requested point in time

set until  time "TO_DATE('2020-12-14 19:13:47', 'YYYY-MM-DD:HH24:MI:SS')";

# online the datafiles restored or switched

sql clone "alter database datafile  1 online";

sql clone "alter database datafile  4 online";

sql clone "alter database datafile  3 online";

# recover and open database read only

recover clone database tablespace  "SYSTEM", "UNDOTBS1", "SYSAUX";

sql clone 'alter database open read only';

}

executing Memory Script

executing command: SET until clause

sql statement: alter database datafile  1 online

sql statement: alter database datafile  4 online

sql statement: alter database datafile  3 online

Starting recover at 2020-12-14:19:24:59

using channel ORA_AUX_DISK_1

starting media recovery

archived log for thread 1 with sequence 14 is already on disk as file +FRA/ACME/ARCHIVELOG/2020_12_14/thread_1_seq_14.367.1059158705

archived log for thread 1 with sequence 15 is already on disk as file +FRA/ACME/ARCHIVELOG/2020_12_14/thread_1_seq_15.266.1059160987

archived log file name=+FRA/ACME/ARCHIVELOG/2020_12_14/thread_1_seq_14.367.1059158705 thread=1 sequence=14

archived log file name=+FRA/ACME/ARCHIVELOG/2020_12_14/thread_1_seq_15.266.1059160987 thread=1 sequence=15

media recovery complete, elapsed time: 00:00:09

Finished recover at 2020-12-14:19:25:11

sql statement: alter database open read only

contents of Memory Script:

{

   sql clone "create spfile from memory";

   shutdown clone immediate;

   startup clone nomount;

   sql clone "alter system set  control_files = 

  ''/u01/backup/ACME/controlfile/o1_mf_hxj0k64p_.ctl'' comment=

 ''RMAN set'' scope=spfile";

   shutdown clone immediate;

   startup clone nomount;

# mount database

sql clone 'alter database mount clone database';

}

executing Memory Script

sql statement: create spfile from memory

database closed

database dismounted

Oracle instance shut down

connected to auxiliary database (not started)

Oracle instance started

Total System Global Area    1068937216 bytes

Fixed Size                     2296576 bytes

Variable Size                285213952 bytes

Database Buffers             775946240 bytes

Redo Buffers                   5480448 bytes

sql statement: alter system set  control_files =   ''/u01/backup/ACME/controlfile/o1_mf_hxj0k64p_.ctl'' comment= ''RMAN set'' scope=spfile

Oracle instance shut down

connected to auxiliary database (not started)

Oracle instance started

Total System Global Area    1068937216 bytes

Fixed Size                     2296576 bytes

Variable Size                285213952 bytes

Database Buffers             775946240 bytes

Redo Buffers                   5480448 bytes

sql statement: alter database mount clone database

contents of Memory Script:

{

# set requested point in time

set until  time "TO_DATE('2020-12-14 19:13:47', 'YYYY-MM-DD:HH24:MI:SS')";

# set destinations for recovery set and auxiliary set datafiles

set newname for datafile  6 to new;

# restore the tablespaces in the recovery set and the auxiliary set

restore clone datafile  6;

switch clone datafile all;

}

executing Memory Script

executing command: SET until clause

executing command: SET NEWNAME

Starting restore at 2020-12-14:19:25:46

allocated channel: ORA_AUX_DISK_1

channel ORA_AUX_DISK_1: SID=27 device type=DISK

channel ORA_AUX_DISK_1: starting datafile backup set restore

channel ORA_AUX_DISK_1: specifying datafile(s) to restore from backup set

channel ORA_AUX_DISK_1: restoring datafile 00006 to /u01/backup/PFUH_PITR_ACME/datafile/o1_mf_users_%u_.dbf

channel ORA_AUX_DISK_1: reading from backup piece +FRA/ACME/BACKUPSET/2020_12_14/nnndf0_tag20201214t184339_0.362.1059158619

channel ORA_AUX_DISK_1: piece handle=+FRA/ACME/BACKUPSET/2020_12_14/nnndf0_tag20201214t184339_0.362.1059158619 tag=TAG20201214T184339

channel ORA_AUX_DISK_1: restored backup piece 1

channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:01

Finished restore at 2020-12-14:19:25:49

datafile 6 switched to datafile copy

input datafile copy RECID=15 STAMP=1059161149 file name=/u01/backup/PFUH_PITR_ACME/datafile/o1_mf_users_hxj0nd73_.dbf

contents of Memory Script:

{

# set requested point in time

set until  time "TO_DATE('2020-12-14 19:13:47', 'YYYY-MM-DD:HH24:MI:SS')";

# online the datafiles restored or switched

sql clone "alter database datafile  6 online";

# recover and open resetlogs

recover clone database tablespace  "USERS", "SYSTEM", "UNDOTBS1", "SYSAUX" delete archivelog;

alter clone database open resetlogs;

}

executing Memory Script

executing command: SET until clause

sql statement: alter database datafile  6 online

Starting recover at 2020-12-14:19:25:49

using channel ORA_AUX_DISK_1

starting media recovery

archived log for thread 1 with sequence 14 is already on disk as file +FRA/ACME/ARCHIVELOG/2020_12_14/thread_1_seq_14.367.1059158705

archived log for thread 1 with sequence 15 is already on disk as file +FRA/ACME/ARCHIVELOG/2020_12_14/thread_1_seq_15.266.1059160987

archived log file name=+FRA/ACME/ARCHIVELOG/2020_12_14/thread_1_seq_14.367.1059158705 thread=1 sequence=14

archived log file name=+FRA/ACME/ARCHIVELOG/2020_12_14/thread_1_seq_15.266.1059160987 thread=1 sequence=15

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

Finished recover at 2020-12-14:19:25:51

database opened

contents of Memory Script:

{

# create directory for datapump import

sql "create or replace directory TSPITR_DIROBJ_DPDIR as ''

/u01/backup/''";

# create directory for datapump export

sql clone "create or replace directory TSPITR_DIROBJ_DPDIR as ''

/u01/backup/''";

}

executing Memory Script

sql statement: create or replace directory TSPITR_DIROBJ_DPDIR as ''/u01/backup/''

sql statement: create or replace directory TSPITR_DIROBJ_DPDIR as ''/u01/backup/''

Performing export of tables...

   EXPDP> Starting "SYS"."TSPITR_EXP_pfuh_nDAC":  

   EXPDP> Estimate in progress using BLOCKS method...

   EXPDP> Processing object type TABLE_EXPORT/TABLE/TABLE_DATA

   EXPDP> Total estimation using BLOCKS method: 64 KB

   EXPDP> Processing object type TABLE_EXPORT/TABLE/TABLE

   EXPDP> Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS

   EXPDP> Processing object type TABLE_EXPORT/TABLE/STATISTICS/MARKER

   EXPDP> ORA-39127: unexpected error from call to export_string :=SYS.DBMS_TRANSFORM_EXIMP.INSTANCE_INFO_EXP('AQ$_ORDERS_QUEUETABLE_S','IX',1,1,'12.01.00.00.00',newblock) 

ORA-00376: file 2 cannot be read at this time

ORA-01110: data file 2: '+DATA/ACME/DATAFILE/example.259.1058124367'

ORA-06512: at "SYS.DBMS_TRANSFORM_EXIMP", line 197

ORA-06512: at line 1

ORA-06512: at "SYS.DBMS_METADATA", line 9901

ORA-39127: unexpected error from call to export_string :=SYS.DBMS_TRANSFORM_EXIMP.INSTANCE_INFO_EXP('AQ$_STREAMS_QUEUE_TABLE_S','IX',1,1,'12.01.00.00.00',newblock) 

ORA-00376: file 2 cannot be read at this time

ORA-01110: data file 2: '+DATA/ACME/DATAFILE/example.259.1058124367'

ORA-06512: at "SYS.DBMS_TRANSFORM_EXIMP", line 197

ORA-06512: at line 1

ORA-06512: at "SYS.DBMS_METADATA", line 9901

   EXPDP> . . exported "HR"."LOLITO"                               17.07 KB     107 rows

   EXPDP> Master table "SYS"."TSPITR_EXP_pfuh_nDAC" successfully loaded/unloaded

   EXPDP> ******************************************************************************

   EXPDP> Dump file set for SYS.TSPITR_EXP_pfuh_nDAC is:

   EXPDP>   /u01/backup/tspitr_pfuh_82509.dmp

   EXPDP> Job "SYS"."TSPITR_EXP_pfuh_nDAC" completed with 2 error(s) at Mon Dec 14 19:27:12 2020 elapsed 0 00:00:52

Export completed

contents of Memory Script:

{

# shutdown clone before import

shutdown clone abort

}

executing Memory Script

Oracle instance shut down

Performing import of tables...

   IMPDP> Master table "SYS"."TSPITR_IMP_pfuh_pkrj" successfully loaded/unloaded

   IMPDP> Starting "SYS"."TSPITR_IMP_pfuh_pkrj":  

   IMPDP> Processing object type TABLE_EXPORT/TABLE/TABLE

   IMPDP> Processing object type TABLE_EXPORT/TABLE/TABLE_DATA

   IMPDP> . . imported "HR"."LOLITO"                               17.07 KB     107 rows

   IMPDP> Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS

   IMPDP> Processing object type TABLE_EXPORT/TABLE/STATISTICS/MARKER

   IMPDP> Job "SYS"."TSPITR_IMP_pfuh_pkrj" successfully completed at Mon Dec 14 19:28:00 2020 elapsed 0 00:00:30

Import completed

Removing automatic instance

Automatic instance removed

auxiliary instance file /u01/backup/ACME/datafile/o1_mf_temp_hxj0m83n_.tmp deleted

auxiliary instance file /u01/backup/PFUH_PITR_ACME/onlinelog/o1_mf_3_hxj0nj8c_.log deleted

auxiliary instance file /u01/backup/PFUH_PITR_ACME/onlinelog/o1_mf_2_hxj0nhnq_.log deleted

auxiliary instance file /u01/backup/PFUH_PITR_ACME/onlinelog/o1_mf_1_hxj0nh58_.log deleted

auxiliary instance file /u01/backup/PFUH_PITR_ACME/datafile/o1_mf_users_hxj0nd73_.dbf deleted

auxiliary instance file /u01/backup/ACME/datafile/o1_mf_sysaux_hxj0kg5h_.dbf deleted

auxiliary instance file /u01/backup/ACME/datafile/o1_mf_undotbs1_hxj0kg5t_.dbf deleted

auxiliary instance file /u01/backup/ACME/datafile/o1_mf_system_hxj0kg5n_.dbf deleted

auxiliary instance file /u01/backup/ACME/controlfile/o1_mf_hxj0k64p_.ctl deleted

auxiliary instance file tspitr_pfuh_82509.dmp deleted

Finished recover at 2020-12-14:19:28:06

RMAN> 

TECNOLOGIA TPITR

LABORATORIO RESTORE TABLE SCN 

SQL> create table hr.lolito as (select * from hr.employees); 

RMAN> RECOVER TABLE HR.LOLITO UNTIL SCN 6223707 AUXILIARY DESTINATION '/u01/backup/';

Starting recover at 14-DEC-20

using target database control file instead of recovery catalog

current log archived

RMAN-00571: ===========================================================

RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============

RMAN-00571: ===========================================================

RMAN-03002: failure of recover command at 12/14/2020 18:58:42

RMAN-05063: Cannot recover specified tables

RMAN-05112: table "HR"."LOLITO" already exists

RMAN> RECOVER TABLE HR.LOLITO UNTIL SCN 6223707 AUXILIARY DESTINATION '/u01/backup/';

Starting recover at 14-DEC-20

allocated channel: ORA_DISK_1

channel ORA_DISK_1: SID=50 device type=DISK

RMAN-05026: WARNING: presuming following set of tablespaces applies to specified Point-in-Time

List of tablespaces expected to have UNDO segments

Tablespace SYSTEM

Tablespace UNDOTBS1

Creating automatic instance, with SID='haer'

initialization parameters used for automatic instance:

db_name=ORCL

db_unique_name=haer_pitr_ORCL

compatible=12.1.0.0.0

db_block_size=8192

db_files=200

sga_target=1G

processes=80

diagnostic_dest=/u01/app/oracle

db_create_file_dest=/u01/backup/

log_archive_dest_1='location=/u01/backup/'

#No auxiliary parameter file used

starting up automatic instance ORCL

Oracle instance started

Total System Global Area    1068937216 bytes

Fixed Size                     2296576 bytes

Variable Size                281019648 bytes

Database Buffers             780140544 bytes

Redo Buffers                   5480448 bytes

Automatic instance created

contents of Memory Script:

{

# set requested point in time

set until  scn 6223707;

# restore the controlfile

restore clone controlfile;

# mount the controlfile

sql clone 'alter database mount clone database';

# archive current online log 

sql 'alter system archive log current';

}

executing Memory Script

executing command: SET until clause

Starting restore at 14-DEC-20

allocated channel: ORA_AUX_DISK_1

channel ORA_AUX_DISK_1: SID=7 device type=DISK

channel ORA_AUX_DISK_1: starting datafile backup set restore

channel ORA_AUX_DISK_1: restoring control file

channel ORA_AUX_DISK_1: reading from backup piece /u01/app/oracle/fast_recovery_area/ORCL/autobackup/2020_12_14/o1_mf_s_1059158574_hxhy3zj0_.bkp

channel ORA_AUX_DISK_1: piece handle=/u01/app/oracle/fast_recovery_area/ORCL/autobackup/2020_12_14/o1_mf_s_1059158574_hxhy3zj0_.bkp tag=TAG20201214T184254

channel ORA_AUX_DISK_1: restored backup piece 1

channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:01

output file name=/u01/backup/ORCL/controlfile/o1_mf_hxhz3two_.ctl

Finished restore at 14-DEC-20

sql statement: alter database mount clone database

sql statement: alter system archive log current

contents of Memory Script:

{

# set requested point in time

set until  scn 6223707;

# set destinations for recovery set and auxiliary set datafiles

set newname for clone datafile  1 to new;

set newname for clone datafile  5 to new;

set newname for clone datafile  4 to new;

set newname for clone datafile  3 to new;

set newname for clone tempfile  1 to new;

# switch all tempfiles

switch clone tempfile all;

# restore the tablespaces in the recovery set and the auxiliary set

restore clone datafile  1, 5, 4, 3;

switch clone datafile all;

}

executing Memory Script

executing command: SET until clause

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

renamed tempfile 1 to /u01/backup/ORCL/datafile/o1_mf_temp_%u_.tmp in control file

Starting restore at 14-DEC-20

using channel ORA_AUX_DISK_1

channel ORA_AUX_DISK_1: starting datafile backup set restore

channel ORA_AUX_DISK_1: specifying datafile(s) to restore from backup set

channel ORA_AUX_DISK_1: restoring datafile 00001 to /u01/backup/ORCL/datafile/o1_mf_system_%u_.dbf

channel ORA_AUX_DISK_1: restoring datafile 00005 to /u01/backup/ORCL/datafile/o1_mf_system_%u_.dbf

channel ORA_AUX_DISK_1: restoring datafile 00004 to /u01/backup/ORCL/datafile/o1_mf_undotbs1_%u_.dbf

channel ORA_AUX_DISK_1: restoring datafile 00003 to /u01/backup/ORCL/datafile/o1_mf_sysaux_%u_.dbf

channel ORA_AUX_DISK_1: reading from backup piece /u01/app/oracle/fast_recovery_area/ORCL/backupset/2020_12_14/o1_mf_nnnd0_TAG20201214T184159_hxhy27q3_.bkp

channel ORA_AUX_DISK_1: piece handle=/u01/app/oracle/fast_recovery_area/ORCL/backupset/2020_12_14/o1_mf_nnnd0_TAG20201214T184159_hxhy27q3_.bkp tag=TAG20201214T184159

channel ORA_AUX_DISK_1: restored backup piece 1

channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:01:05

Finished restore at 14-DEC-20

datafile 1 switched to datafile copy

input datafile copy RECID=7 STAMP=1059159666 file name=/u01/backup/ORCL/datafile/o1_mf_system_hxhz41lk_.dbf

datafile 5 switched to datafile copy

input datafile copy RECID=8 STAMP=1059159666 file name=/u01/backup/ORCL/datafile/o1_mf_system_hxhz41lw_.dbf

datafile 4 switched to datafile copy

input datafile copy RECID=9 STAMP=1059159666 file name=/u01/backup/ORCL/datafile/o1_mf_undotbs1_hxhz41lo_.dbf

datafile 3 switched to datafile copy

input datafile copy RECID=10 STAMP=1059159666 file name=/u01/backup/ORCL/datafile/o1_mf_sysaux_hxhz41l5_.dbf

contents of Memory Script:

{

# set requested point in time

set until  scn 6223707;

# online the datafiles restored or switched

sql clone "alter database datafile  1 online";

sql clone "alter database datafile  5 online";

sql clone "alter database datafile  4 online";

sql clone "alter database datafile  3 online";

# recover and open database read only

recover clone database tablespace  "SYSTEM", "UNDOTBS1", "SYSAUX";

sql clone 'alter database open read only';

}

executing Memory Script

executing command: SET until clause

sql statement: alter database datafile  1 online

sql statement: alter database datafile  5 online

sql statement: alter database datafile  4 online

sql statement: alter database datafile  3 online

Starting recover at 14-DEC-20

using channel ORA_AUX_DISK_1

starting media recovery

archived log for thread 1 with sequence 12 is already on disk as file /u01/app/oracle/fast_recovery_area/ORCL/archivelog/2020_12_14/o1_mf_1_12_hxhz1klk_.arc

archived log file name=/u01/app/oracle/fast_recovery_area/ORCL/archivelog/2020_12_14/o1_mf_1_12_hxhz1klk_.arc thread=1 sequence=12

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

Finished recover at 14-DEC-20

sql statement: alter database open read only

contents of Memory Script:

{

   sql clone "create spfile from memory";

   shutdown clone immediate;

   startup clone nomount;

   sql clone "alter system set  control_files = 

  ''/u01/backup/ORCL/controlfile/o1_mf_hxhz3two_.ctl'' comment=

 ''RMAN set'' scope=spfile";

   shutdown clone immediate;

   startup clone nomount;

# mount database

sql clone 'alter database mount clone database';

}

executing Memory Script

sql statement: create spfile from memory

database closed

database dismounted

Oracle instance shut down

connected to auxiliary database (not started)

Oracle instance started

Total System Global Area    1068937216 bytes

Fixed Size                     2296576 bytes

Variable Size                285213952 bytes

Database Buffers             775946240 bytes

Redo Buffers                   5480448 bytes

sql statement: alter system set  control_files =   ''/u01/backup/ORCL/controlfile/o1_mf_hxhz3two_.ctl'' comment= ''RMAN set'' scope=spfile

Oracle instance shut down

connected to auxiliary database (not started)

Oracle instance started

Total System Global Area    1068937216 bytes

Fixed Size                     2296576 bytes

Variable Size                285213952 bytes

Database Buffers             775946240 bytes

Redo Buffers                   5480448 bytes

sql statement: alter database mount clone database

contents of Memory Script:

{

# set requested point in time

set until  scn 6223707;

# set destinations for recovery set and auxiliary set datafiles

set newname for datafile  6 to new;

# restore the tablespaces in the recovery set and the auxiliary set

restore clone datafile  6;

switch clone datafile all;

}

executing Memory Script

executing command: SET until clause

executing command: SET NEWNAME

Starting restore at 14-DEC-20

allocated channel: ORA_AUX_DISK_1

channel ORA_AUX_DISK_1: SID=20 device type=DISK

channel ORA_AUX_DISK_1: starting datafile backup set restore

channel ORA_AUX_DISK_1: specifying datafile(s) to restore from backup set

channel ORA_AUX_DISK_1: restoring datafile 00006 to /u01/backup/HAER_PITR_ORCL/datafile/o1_mf_users_%u_.dbf

channel ORA_AUX_DISK_1: reading from backup piece /u01/app/oracle/fast_recovery_area/ORCL/backupset/2020_12_14/o1_mf_nnnd0_TAG20201214T184159_hxhy27q3_.bkp

channel ORA_AUX_DISK_1: piece handle=/u01/app/oracle/fast_recovery_area/ORCL/backupset/2020_12_14/o1_mf_nnnd0_TAG20201214T184159_hxhy27q3_.bkp tag=TAG20201214T184159

channel ORA_AUX_DISK_1: restored backup piece 1

channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:01

Finished restore at 14-DEC-20

datafile 6 switched to datafile copy

input datafile copy RECID=12 STAMP=1059159701 file name=/u01/backup/HAER_PITR_ORCL/datafile/o1_mf_users_hxhz743t_.dbf

contents of Memory Script:

{

# set requested point in time

set until  scn 6223707;

# online the datafiles restored or switched

sql clone "alter database datafile  6 online";

# recover and open resetlogs

recover clone database tablespace  "USERS", "SYSTEM", "UNDOTBS1", "SYSAUX" delete archivelog;

alter clone database open resetlogs;

}

executing Memory Script

executing command: SET until clause

sql statement: alter database datafile  6 online

Starting recover at 14-DEC-20

using channel ORA_AUX_DISK_1

starting media recovery

archived log for thread 1 with sequence 12 is already on disk as file /u01/app/oracle/fast_recovery_area/ORCL/archivelog/2020_12_14/o1_mf_1_12_hxhz1klk_.arc

archived log file name=/u01/app/oracle/fast_recovery_area/ORCL/archivelog/2020_12_14/o1_mf_1_12_hxhz1klk_.arc thread=1 sequence=12

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

Finished recover at 14-DEC-20

database opened

contents of Memory Script:

{

# create directory for datapump import

sql "create or replace directory TSPITR_DIROBJ_DPDIR as ''

/u01/backup/''";

# create directory for datapump export

sql clone "create or replace directory TSPITR_DIROBJ_DPDIR as ''

/u01/backup/''";

}

executing Memory Script

sql statement: create or replace directory TSPITR_DIROBJ_DPDIR as ''/u01/backup/''

sql statement: create or replace directory TSPITR_DIROBJ_DPDIR as ''/u01/backup/''

Performing export of tables...

   EXPDP> Starting "SYS"."TSPITR_EXP_haer_peEB":  

   EXPDP> Estimate in progress using BLOCKS method...

   EXPDP> Processing object type TABLE_EXPORT/TABLE/TABLE_DATA

   EXPDP> Total estimation using BLOCKS method: 64 KB

   EXPDP> Processing object type TABLE_EXPORT/TABLE/TABLE

   EXPDP> Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS

   EXPDP> Processing object type TABLE_EXPORT/TABLE/STATISTICS/MARKER

   EXPDP> ORA-39127: unexpected error from call to export_string :=SYS.DBMS_TRANSFORM_EXIMP.INSTANCE_INFO_EXP('AQ$_ORDERS_QUEUETABLE_S','IX',1,1,'12.01.00.00.00',newblock) 

ORA-00376: file 2 cannot be read at this time

ORA-01110: data file 2: '/u01/app/oracle/oradata/ORCL/datafile/o1_mf_example_hwjdb5y7_.dbf'

ORA-06512: at "SYS.DBMS_TRANSFORM_EXIMP", line 197

ORA-06512: at line 1

ORA-06512: at "SYS.DBMS_METADATA", line 9901

ORA-39127: unexpected error from call to export_string :=SYS.DBMS_TRANSFORM_EXIMP.INSTANCE_INFO_EXP('AQ$_STREAMS_QUEUE_TABLE_S','IX',1,1,'12.01.00.00.00',newblock) 

ORA-00376: file 2 cannot be read at this time

ORA-01110: data file 2: '/u01/app/oracle/oradata/ORCL/datafile/o1_mf_example_hwjdb5y7_.dbf'

ORA-06512: at "SYS.DBMS_TRANSFORM_EXIMP", line 197

ORA-06512: at line 1

ORA-06512: at "SYS.DBMS_METADATA", line 9901

   EXPDP> . . exported "HR"."LOLITO"                               17.07 KB     107 rows

   EXPDP> Master table "SYS"."TSPITR_EXP_haer_peEB" successfully loaded/unloaded

   EXPDP> ******************************************************************************

   EXPDP> Dump file set for SYS.TSPITR_EXP_haer_peEB is:

   EXPDP>   /u01/backup/tspitr_haer_59331.dmp

   EXPDP> Job "SYS"."TSPITR_EXP_haer_peEB" completed with 2 error(s) at Mon Dec 14 19:03:12 2020 elapsed 0 00:00:50

Export completed

contents of Memory Script:

{

# shutdown clone before import

shutdown clone abort

}

executing Memory Script

Oracle instance shut down

Performing import of tables...

   IMPDP> Master table "SYS"."TSPITR_IMP_haer_pAbb" successfully loaded/unloaded

   IMPDP> Starting "SYS"."TSPITR_IMP_haer_pAbb":  

   IMPDP> Processing object type TABLE_EXPORT/TABLE/TABLE

   IMPDP> Processing object type TABLE_EXPORT/TABLE/TABLE_DATA

   IMPDP> . . imported "HR"."LOLITO"                               17.07 KB     107 rows

   IMPDP> Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS

   IMPDP> Processing object type TABLE_EXPORT/TABLE/STATISTICS/MARKER

   IMPDP> Job "SYS"."TSPITR_IMP_haer_pAbb" successfully completed at Mon Dec 14 19:03:56 2020 elapsed 0 00:00:30

Import completed

Removing automatic instance

Automatic instance removed

auxiliary instance file /u01/backup/ORCL/datafile/o1_mf_temp_hxhz666l_.tmp deleted

auxiliary instance file /u01/backup/HAER_PITR_ORCL/onlinelog/o1_mf_3_hxhz774g_.log deleted

auxiliary instance file /u01/backup/HAER_PITR_ORCL/onlinelog/o1_mf_2_hxhz76pt_.log deleted

auxiliary instance file /u01/backup/HAER_PITR_ORCL/onlinelog/o1_mf_1_hxhz76cr_.log deleted

auxiliary instance file /u01/backup/HAER_PITR_ORCL/datafile/o1_mf_users_hxhz743t_.dbf deleted

auxiliary instance file /u01/backup/ORCL/datafile/o1_mf_sysaux_hxhz41l5_.dbf deleted

auxiliary instance file /u01/backup/ORCL/datafile/o1_mf_undotbs1_hxhz41lo_.dbf deleted

auxiliary instance file /u01/backup/ORCL/datafile/o1_mf_system_hxhz41lw_.dbf deleted

auxiliary instance file /u01/backup/ORCL/datafile/o1_mf_system_hxhz41lk_.dbf deleted

auxiliary instance file /u01/backup/ORCL/controlfile/o1_mf_hxhz3two_.ctl deleted

auxiliary instance file tspitr_haer_59331.dmp deleted

Finished recover at 14-DEC-20

RMAN> 

BACKUP INCREMENTAL UNTIL TIME

 RMAN> backup incremental level 1 format '/u01/backup/dev_%t_%s_level1.dbk' database plus archivelog delete input until time "to_date(to_char(trunc(sysdate) || ':13:00:00'), 'DD-MON-YY:hh24:mi:ss')" ; 

FLASHBACK DATABASE TO RESTORE POINT GUARANTEE.

LABORATORIO.

ACTIVAR FLASHBACK DATABASE

CREAR PUNTO DE RESTAURACION

SQL> create restore point before_upgrade guarantee flashback database

SQL> /

Restore point created.

SQL> update hr.prueba set salary=0;

107 rows updated.

SQL> commit;

Commit complete.

SQL> shutdown immediate;

Database closed.

Database dismounted.

ORACLE instance shut down.

SQL> startup mount;

ORACLE instance started.

Total System Global Area  835104768 bytes

Fixed Size     2293880 bytes

Variable Size   658509704 bytes

Database Buffers   171966464 bytes

Redo Buffers     2334720 bytes

Database mounted.

SQL> exit

Disconnected from Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production

With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

[oracle@localhost ~]$ 

RMAN> FLASHBACK DATABASE TO RESTORE POINT 'BEFORE_UPGRADE';

Starting flashback at 11-DEC-20

using channel ORA_DISK_1

starting media recovery

media recovery complete, elapsed time: 00:00:01

Finished flashback at 11-DEC-20

Finished flashback at 11-DEC-20

RMAN> alter database open resetlogs;

Statement processed

RMAN> 

RMAN> list restore point all;

using target database control file instead of recovery catalog

SCN              RSP Time  Type       Time      Name

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

6032442                    GUARANTEED 11-DEC-20 BEFORE_UPGRADE

RMAN> drop restore point before_upgrade;

Statement processed

RMAN> 

FLASHBACK DATABASE

LABORATORIO.

SQL*Plus: Release 12.1.0.1.0 Production on Fri May 14 21:14:29 2021

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

Connected to:

Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production

With the Partitioning, Automatic Storage Management, OLAP, Advanced Analytics

and Real Application Testing options

SQL> shutdown immediate;

Database closed.

Database dismounted.

ORACLE instance shut down.

SQL> 

SQL> startup mount;

ORACLE instance started.

Total System Global Area  835104768 bytes

Fixed Size     2293880 bytes

Variable Size   658509704 bytes

Database Buffers   171966464 bytes

Redo Buffers     2334720 bytes

Database mounted.

SQL> flashback database to timestamp TO_TIMESTAMP('2021-05-14 21:11:00','YYYY-MM-DD HH24:MI:SS');

Flashback complete.

SQL>

SQL> alter system set undo_retention=1800 scope=both;

System altered.

SQL> show parameter undo;

NAME      TYPE  VALUE

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

temp_undo_enabled      boolean  FALSE

undo_management       string  AUTO

undo_retention      integer  1800

undo_tablespace       string  UNDOTBS1

ESTO SE DEBE REALIZAR CON LAS INSTANCIA EN MODO OPEN

SQL> select tablespace_name,retention from dba_tablespaces;

TABLESPACE_NAME         RETENTION

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

SYSTEM        NOT APPLY

SYSAUX        NOT APPLY

UNDOTBS1        NOGUARANTEE

TEMP        NOT APPLY

USERS        NOT APPLY

EXAMPLE         NOT APPLY

TBS_1        NOT APPLY

7 rows selected.

SQL> 

SQL> alter tablespace undotbs1 retention guarantee;

SQL> shutdown immediate; 
SQL> startup mount; 
ORACLE instance started. 

CONFIGURACION EN MINUTOS


SQL> alter system set DB_FLASHBACK_RETENTION_TARGET=1440;
System altered. 

SQL> alter system set DB_RECOVERY_FILE_DEST_SIZE=10G; 
System altered. 

SQL> alter database flashback on; 
Database altered. 

SQL> alter database open; 
Database altered. 

SQL>alter system set recyclebin=on scope=both; 

SQL> select open_mode,log_mode,flashback_on ,current_scn from v$database; 


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> 

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. 

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> 

SI TOCA DEVOLVER AL ULTIMO RESETLOGS.

RMAN> FLASHBACK DATASBE TO BEFORE RESETLOGS;

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;

CONSULTAS FLASHBACK

SQL> select estimated_flashback_size,flashback_size from v$flashback_database_log;

ESTIMATED_FLASHBACK_SIZE FLASHBACK_SIZE

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

11039744      104857600

SQL> select oldest_flashback_scn, oldest_flashback_time from v$flashback_database_log;

OLDEST_FLASHBACK_SCN OLDEST_F

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

     5799514 11/12/20

SQL>select * from v$flashback_database_stat;