Performing a Point-in-Time Recovery use SCN


In this exercise, you will do a point-in-time recovery by restoring the database to a previous SCN.

1. Back up the database. Details on how to do a full online database backup are found in online backup. In summary, follow these steps:

  • First put the database in hot backup mode.
  • Copy all database datafiles to a backup location.
  • Take the database out of hot backup mode.
  • Force a log switch. Back up the archived redo logs.

Here is an example of a backup:

[oracle@localhost orcl]$ sqlplus “/ as sysdba”

SQL*Plus: Release 11.1.0.6.0 – Production on Sun Aug 17 15:35:48 2008

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

Connected to:

Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 – Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> alter database begin backup;

Database altered.

SQL> host cp /oracle01/oradata/orcl/*.dbf /oracle01/backup/orcl

SQL> alter database end backup;

Database altered.

SQL> alter system switch logfile;

System altered.

SQL> host cp /oracle01/backup/arch/* /oracle01/backup/orcl/*

SQL> alter database backup controlfile to trace;

Database altered.

SQL> alter database backup controlfile to ‘/oracle01/oradata/orcl/control1.bak’;

Database altered.

2. Next, log into the database as scott/tiger and create a new table. Insert two records

into the new table and commit the insert.

SQL> connect scott/tiger

Connected.

SQL> create table test_table (id number);

Table created.

SQL> insert into test_table values (1);

1 row created.

SQL> insert into test_table values (2);

1 row created.

SQL> commit;

Commit complete.

3. Now log in as SYS and determine the current SCN by using the CURRENT_SCN column

of the V$DATABASE table. Your SCN will be different from that in the example.

SQL> connect sys as sysdba

Enter password:

Connected.

SQL> select current_scn from v$database;

CURRENT_SCN

———–

5135413

4. Log back in as scott/tiger and add two more records. Commit the inserts.

SQL> connect scott/tiger

Connected.

SQL> insert into test_table values (3);

1 row created.

SQL> insert into test_table values (4);

1 row created.

SQL> commit;

Commit complete.

5. Log in as SYS again and query the current SCN by using the CURRENT_SCN column of the V$DATABASE table. Notice that the SCN has changed.

SQL> connect sys as sysdba

Enter password:

Connected.

SQL> select current_scn from v$database;

CURRENT_SCN

———–

5135522

6. Shut down the database.

SQL> shutdown immediate

Database closed.

Database dismounted.

ORACLE instance shut down.

7. Once you are sure the database is down, restore the database datafiles from their backup location to the location where the database files belong.

[oracle@localhost orcl]$ pwd

/oracle01/backup/orcl

[oracle@localhost orcl]$ cp *.dbf /oracle01/oradata/orcl/*

Exercise 3.3 (continued)

8. Mount the database.

[oracle@localhost orcl]$ sqlplus “/ as sysdba”

SQL*Plus: Release 11.1.0.6.0 – Production on Sun Aug 17 17:53:14 2008

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

Connected to an idle instance.

SQL> startup mount

ORACLE instance started.

Total System Global Area 167395328 bytes

Fixed Size 1298612 bytes

Variable Size 142610252 bytes

Database Buffers 20971520 bytes

Redo Buffers 2514944 bytes

Database mounted.

9. Recover the database using the recover database until change command. You will use the SCN you queried in step 3 as the SCN to recover to. Enter AUTO when prompted for an archived redo log to apply.

SQL> Recover database until change 5135413;

ORA-00279: change 5071334 generated at 08/17/2008 15:35:51 needed for thread 1

ORA-00289: suggestion :

/oracle01/flash_recovery_area/ORCL/archivelog/2008_08_17

/o1_mf_1_5_4bk6onh8_.arc

ORA-00280: change 5071334 for thread 1 is in sequence #5

Specify log: {<RET>=suggested | filename | AUTO | CANCEL}

auto

ORA-00279: change 5071583 generated at 08/17/2008 15:40:04 needed for thread 1

ORA-00289: suggestion :

/oracle01/flash_recovery_area/ORCL/archivelog/2008_08_17

/o1_mf_1_6_4bk76kwk_.arc

ORA-00280: change 5071583 for thread 1 is in sequence #6

ORA-00279: change 5091960 generated at 08/17/2008 15:49:05 needed for thread 1

ORA-00289: suggestion :

/oracle01/flash_recovery_area/ORCL/archivelog/2008_08_17

/o1_mf_1_7_4bk9ksb4_.arc

ORA-00280: change 5091960 for thread 1 is in sequence #7

ORA-00279: change 5112317 generated at 08/17/2008 16:29:13 needed for thread 1

ORA-00289: suggestion :

/oracle01/flash_recovery_area/ORCL/archivelog/2008_08_17

/o1_mf_1_8_4bk9p236_.arc

Exercise 3.3 (continued)

ORA-00280: change 5112317 for thread 1 is in sequence #8

ORA-00279: change 5112647 generated at 08/17/2008 16:31:29 needed for thread 1

ORA-00289: suggestion :

/oracle01/flash_recovery_area/ORCL/archivelog/2008_08_17

/o1_mf_1_9_4bk9p2mz_.arc

ORA-00280: change 5112647 for thread 1 is in sequence #9

ORA-00279: change 5112649 generated at 08/17/2008 16:31:30 needed for thread 1

ORA-00289: suggestion :

/oracle01/flash_recovery_area/ORCL/archivelog/2008_08_17

/o1_mf_1_10_4bk9p3gz_.arc

ORA-00280: change 5112649 for thread 1 is in sequence #10

Log applied.

Media recovery complete.

10. Open the database with the alter database open resetlogs command. Note that

once you have done this you will not be able to recover any data that was entered

after the point of the recovery.

SQL> alter database open resetlogs;

Database altered.

11. Log into the scott schema. Do a select * from test_table. You should have only

two records in the table.

SQL> Connect scott/tiger

Connected.

SQL> Select * from test_table;

ID

———-

1

2

Advertisements

2 thoughts on “Performing a Point-in-Time Recovery use SCN

  1. Luar biasa komandan….

  2. mau donk komandan…..

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

w

Connecting to %s