Oracle DBA's : Point in time recovery

Point in time recovery

Recover a table with RMAN 12c new Feature testing:

RMAN Table Point In Time Recovery (PITR) in Oracle Database 12c Release 2 (12.2)

PURPOSE: All documents are provided on this Blog just for educational purposes only. Please make sure that you run it in your test environment before to move on to production environment.

You can use different keywords in your RMAN commands for exp.

Example:

- until scn xxxxx

- until sequence xxxxx

- until time "TO_DATE('28-JAN-2020 15:00', 'DD-MON-YYYY HH24:MI')"

Let us go throw the steps.

Step 1.

DB need to be in archive log mode, if not do the following.

SQL> archive log list;

Database log mode No Archive Mode

Automatic archival Disabled

...

SQL> shutdown immediate;

SQL> startup mount;

SQL> alter database archivelog;

SQL> alter database open;

SQL> archive log list;

Database log mode Archive Mode

Automatic archival Enabled

You can disable archive log mode as follow:

alter database noarchivelog;

Step 2.

Create user:

$ sqlplus / as sysdba

SQL*Plus: Release 12.2.0.1.0 Production on Mon Jan 27 10:56:18 2020

Connected to:

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

SQL>

SQL> select name from v$database;

NAME

---------

KARDBPRD

SQL>

SQL> create user kar identified by kartest;

User created.

SQL> alter user kar quota unlimited on users;

User altered.

SQL> create table kar.kartbl(id number, text varchar2(30));

Table created.

SQL> begin

for i in 1..3000

loop

insert into kar.kartbl values (i,'row Inserted');

end loop;

commit;

end;

PL/SQL procedure successfully completed.

SQL> select count(*) from kar.kartbl;

COUNT(*)

----------

3000

SQL> select current_scn from v$database;

CURRENT_SCN

-----------

6047801

OR:

SELECT DBMS_FLASHBACK.get_system_change_number FROM dual;

Let's alter few rows and simulate corruption:

SQL> update kar.kartbl set text='data is corrupted' where id in (10,110,210,510);

4 rows updated.

commit;

SQL> select id, text from kar.kartbl where id in(10,110,210,510);

ID TEXT

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

10 data is corrupted

110 data is corrupted

210 data is corrupted

510 data is corrupted

Backup the DB:

rman target /

backup database include current controlfile plus archivelog delete input;

Follwing is the log output:

Starting backup at 28-JAN-20

current log archived

using target database control file instead of recovery catalog

allocated channel: ORA_DISK_1

channel ORA_DISK_1: SID=381 device type=DISK

channel ORA_DISK_1: starting archived log backup set

channel ORA_DISK_1: specifying archived log(s) in backup set

input archived log thread=1 sequence=607 RECID=3 STAMP=1030820431

input archived log thread=1 sequence=608 RECID=4 STAMP=1030831210

input archived log thread=1 sequence=609 RECID=5 STAMP=1030831225

input archived log thread=1 sequence=610 RECID=6 STAMP=1030838442

input archived log thread=1 sequence=611 RECID=7 STAMP=1030841625

input archived log thread=1 sequence=612 RECID=8 STAMP=1030852851

input archived log thread=1 sequence=613 RECID=9 STAMP=1030874721

input archived log thread=1 sequence=614 RECID=10 STAMP=1030876288

input archived log thread=1 sequence=615 RECID=11 STAMP=1030876812

input archived log thread=1 sequence=616 RECID=12 STAMP=1030877064

input archived log thread=1 sequence=617 RECID=13 STAMP=1030877638

input archived log thread=1 sequence=618 RECID=14 STAMP=1030892416

input archived log thread=1 sequence=619 RECID=15 STAMP=1030899237

channel ORA_DISK_1: starting piece 1 at 28-JAN-20

channel ORA_DISK_1: finished piece 1 at 28-JAN-20

piece handle=/oracle/product/12.2.0.1/db12c/dbs/05un4hh5_1_1 tag=TAG20200128T165357 comment=NONE

channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01

channel ORA_DISK_1: deleting archived log(s)

archived log file name=/oracle/product/12.2.0.1/db12c/dbs/arch1_607_1024388656.dbf RECID=3 STAMP=1030820431

archived log file name=/oracle/product/12.2.0.1/db12c/dbs/arch1_608_1024388656.dbf RECID=4 STAMP=1030831210

archived log file name=/oracle/product/12.2.0.1/db12c/dbs/arch1_609_1024388656.dbf RECID=5 STAMP=1030831225

archived log file name=/oracle/product/12.2.0.1/db12c/dbs/arch1_610_1024388656.dbf RECID=6 STAMP=1030838442

archived log file name=/oracle/product/12.2.0.1/db12c/dbs/arch1_611_1024388656.dbf RECID=7 STAMP=1030841625

archived log file name=/oracle/product/12.2.0.1/db12c/dbs/arch1_612_1024388656.dbf RECID=8 STAMP=1030852851

archived log file name=/oracle/product/12.2.0.1/db12c/dbs/arch1_613_1024388656.dbf RECID=9 STAMP=1030874721

archived log file name=/oracle/product/12.2.0.1/db12c/dbs/arch1_614_1024388656.dbf RECID=10 STAMP=1030876288

archived log file name=/oracle/product/12.2.0.1/db12c/dbs/arch1_615_1024388656.dbf RECID=11 STAMP=1030876812

archived log file name=/oracle/product/12.2.0.1/db12c/dbs/arch1_616_1024388656.dbf RECID=12 STAMP=1030877064

archived log file name=/oracle/product/12.2.0.1/db12c/dbs/arch1_617_1024388656.dbf RECID=13 STAMP=1030877638

archived log file name=/oracle/product/12.2.0.1/db12c/dbs/arch1_618_1024388656.dbf RECID=14 STAMP=1030892416

archived log file name=/oracle/product/12.2.0.1/db12c/dbs/arch1_619_1024388656.dbf RECID=15 STAMP=1030899237

Finished backup at 28-JAN-20

Starting backup at 28-JAN-20

using channel ORA_DISK_1

channel ORA_DISK_1: starting full datafile backup set

channel ORA_DISK_1: specifying datafile(s) in backup set

input datafile file number=00002 name=+DATA/kardbprd/datafile/sysaux.261.1024388661

input datafile file number=00003 name=+DATA/kardbprd/datafile/undotbs1.262.1024388663

input datafile file number=00001 name=+DATA/kardbprd/datafile/system.260.1024388659

input datafile file number=00004 name=+DATA/kardbprd/datafile/users.264.1024388665

channel ORA_DISK_1: starting piece 1 at 28-JAN-20

channel ORA_DISK_1: finished piece 1 at 28-JAN-20

piece handle=/oracle/product/12.2.0.1/db12c/dbs/06un4hh6_1_1 tag=TAG20200128T165358 comment=NONE

channel ORA_DISK_1: backup set complete, elapsed time: 00:00:15

channel ORA_DISK_1: starting full datafile backup set

channel ORA_DISK_1: specifying datafile(s) in backup set

including current control file in backup set

including current SPFILE in backup set

channel ORA_DISK_1: starting piece 1 at 28-JAN-20

channel ORA_DISK_1: finished piece 1 at 28-JAN-20

piece handle=/oracle/product/12.2.0.1/db12c/dbs/07un4hhl_1_1 tag=TAG20200128T165358 comment=NONE

channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01

Finished backup at 28-JAN-20

Starting backup at 28-JAN-20

current log archived

using channel ORA_DISK_1

channel ORA_DISK_1: starting archived log backup set

channel ORA_DISK_1: specifying archived log(s) in backup set

input archived log thread=1 sequence=620 RECID=16 STAMP=1030899255

channel ORA_DISK_1: starting piece 1 at 28-JAN-20

channel ORA_DISK_1: finished piece 1 at 28-JAN-20

piece handle=/oracle/product/12.2.0.1/db12c/dbs/08un4hho_1_1 tag=TAG20200128T165416 comment=NONE

channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01

channel ORA_DISK_1: deleting archived log(s)

archived log file name=/oracle/product/12.2.0.1/db12c/dbs/arch1_620_1024388656.dbf RECID=16 STAMP=1030899255

Finished backup at 28-JAN-20

RMAN> list backup of controlfile;

List of Backup Sets

===================

BS Key Type LV Size Device Type Elapsed Time Completion Time

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

7 Full 9.80M DISK 00:00:01 28-JAN-20

BP Key: 12 Status: AVAILABLE Compressed: NO Tag: TAG20200128T165358

Piece Name: /oracle/product/12.2.0.1/db12c/dbs/07un4hhl_1_1

Control File Included: Ckp SCN: 6047934 Ckp time: 28-JAN-20

Now we will try to restore the table.

NOTE: Use the capital later. Otherwise you will see the error : RMAN-05057: Table kar.kartbl not found

RMAN> recover table 'KAR'.'KARTBL' until scn 6047801 AUXILIARY DESTINATION '+DATA' REMAP TABLE 'KAR'.'KARTBL':'KAR'.'PREV';

Follwing is the log output:

Starting recover at 28-JAN-20

using channel ORA_DISK_1

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='odvd'

initialization parameters used for automatic instance:

db_name=kardbprd

db_unique_name=odvd_pitr_kardbprd

compatible=11.2.0.0.0

db_block_size=8192

db_files=200

diagnostic_dest=/oracle

_system_trig_enabled=FALSE

sga_target=28864M

processes=200

db_create_file_dest=+DATA

log_archive_dest_1='location=+DATA'

#No auxiliary parameter file used

starting up automatic instance kardbprd

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

….

Finished recover at 28-JAN-20

database opened

contents of Memory Script:

{

# create directory for datapump import

sql "create or replace directory TSPITR_DIROBJ_DPDIR as ''

+DATA''";

# create directory for datapump export

sql clone "create or replace directory TSPITR_DIROBJ_DPDIR as ''

+DATA''";

}

executing Memory Script

sql statement: create or replace directory TSPITR_DIROBJ_DPDIR as ''+DATA''

Performing export of tables...

EXPDP> Starting "SYS"."TSPITR_EXP_odvd_Dzzr":

EXPDP> Processing object type TABLE_EXPORT/TABLE/TABLE_DATA

EXPDP> Processing object type TABLE_EXPORT/TABLE/TABLE

EXPDP> . . exported "KAR"."KARTBL" 66.86 KB 3000 rows

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

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

EXPDP> Dump file set for SYS.TSPITR_EXP_odvd_Dzzr is:

EXPDP> +DATA/tspitr_odvd_31743.dmp

EXPDP> Job "SYS"."TSPITR_EXP_odvd_Dzzr" successfully completed at Tue Jan 28 16:58:09 2020 elapsed 0 00:00:11

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_odvd_pdym" successfully loaded/unloaded

IMPDP> Starting "SYS"."TSPITR_IMP_odvd_pdym":

IMPDP> Processing object type TABLE_EXPORT/TABLE/TABLE

IMPDP> Processing object type TABLE_EXPORT/TABLE/TABLE_DATA

IMPDP> . . imported "KAR"."PREV" 66.86 KB 3000 rows

IMPDP> Job "SYS"."TSPITR_IMP_odvd_pdym" successfully completed at Tue Jan 28 16:58:15 2020 elapsed 0 00:00:01

Import completed

Removing automatic instance

Automatic instance removed

auxiliary instance file +DATA/kardbprd/TEMPFILE/temp.293.1030899403 deleted

auxiliary instance file +DATA/ODVD_PITR_kardbprd/ONLINELOG/group_3.304.1030899473 deleted

auxiliary instance file +DATA/ODVD_PITR_kardbprd/ONLINELOG/group_2.298.1030899473 deleted

auxiliary instance file +DATA/ODVD_PITR_kardbprd/ONLINELOG/group_1.303.1030899473 deleted

auxiliary instance file +DATA/ODVD_PITR_kardbprd/DATAFILE/users.294.1030899467 deleted

auxiliary instance file +DATA/kardbprd/DATAFILE/sysaux.266.1030899389 deleted

auxiliary instance file +DATA/kardbprd/DATAFILE/undotbs1.269.1030899389 deleted

auxiliary instance file +DATA/kardbprd/DATAFILE/system.268.1030899389 deleted

auxiliary instance file +DATA/kardbprd/CONTROLFILE/current.284.1030899383 deleted

auxiliary instance file tspitr_odvd_31743.dmp deleted

Finished recover at 28-JAN-20

select id, text from kar.prev where id in(10,110,210,510);

ID TEXT

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

10 row Inserted

110 row Inserted

210 row Inserted

510 row Inserted

drop table kar.kartbl;

create table kar.kartbl as select * from kar.prev;

Completed.

Oracle DBA's

Tags

Saturday, February 8, 2020

Point In Time Recovery 12c

Point in time recovery

Recover a table with RMAN 12c new Feature testing:

RMAN Table Point In Time Recovery (PITR) in Oracle Database 12c Release 2 (12.2)

physical standby without duplicate command

Search This Blog