This forum is currently locked. You can't register or post questions at this time. (read more)

DB PITR in 11.2.0.1

All posts relating to Oracle database administration.

Moderator: Tim...

kytemaniac
Senior Member
Posts: 234
Joined: Tue May 19, 2009 12:59 am

DB PITR in 11.2.0.1

Postby kytemaniac » Sun Sep 22, 2013 10:48 am

Hi I was doing a hands on on DB PITR in 11.2.0.1

here is what I did

Code: Select all

CREATE TABLE dba_list(
dba_list_id INT,
dba_name VARCHAR(30),
dba_certification VARCHAR(20),
CONSTRAINT dba_list PRIMARY KEY (dba_list_id)
);

INSERT INTO dba_list (dba_list_id, dba_name, dba_certification) VALUES (1, 'Yunis Valeikanov', 'OCM');

INSERT INTO dba_list (dba_list_id, dba_name, dba_certification) VALUES (2, 'Gary Wang', 'OCM');

COMMIT:


immediately after the commit I did the following to find the current SCN after the commit;

Code: Select all

SYS@ORCL>SELECT log_mode, name, current_scn, DB_UNIQUE_NAME from v$database;

LOG_MODE     NAME      CURRENT_SCN DB_UNIQUE_NAME
------------ --------- ----------- ------------------------------
ARCHIVELOG   ORCL      1973690 orcl


next I do a rman backup

Code: Select all

BACKUP INCREMENTAL LEVEL 0 DATABASE plus archivelog tag 'db_inc0_20130922_1812';




next I truncate the table

Code: Select all


SCOTT@ORCL>truncate table dba_list;




next I do the following as sysdba

SHUTDOWN IMMEDIATE;
STARTUP MOUNT;


in rman I did the following

RUN
{
SET UNTIL SCN 1973690;
RESTORE DATABASE;
RECOVER DATABASE;
}

RMAN> sql 'ALTER DATABASE OPEN READ ONLY'

sql statement: ALTER DATABASE OPEN READ ONLY
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03009: failure of sql command on default channel at 09/22/2013 18:32:38
RMAN-11003: failure during parse/execution of SQL statement: ALTER DATABASE OPEN READ ONLY
ORA-16005: database requires recovery

I'm referring to http://docs.oracle.com/cd/E11882_01/bac ... m#i1011846

Performing Database Point-in-Time Recovery


details are as follow

Code: Select all

SQL> select * from v$version;

BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
PL/SQL Release 11.2.0.1.0 - Production
CORE   11.2.0.1.0   Production
TNS for Linux: Version 11.2.0.1.0 - Production
NLSRTL Version 11.2.0.1.0 - Production



so how do I resolve this issue?

is there anything wrong with the way I did the recovery?


thanks a lot!

Tim...
Site Admin
Posts: 18437
Joined: Mon Nov 01, 2004 5:56 pm
Location: England, UK
Contact:

Re: DB PITR in 11.2.0.1

Postby Tim... » Sun Sep 22, 2013 5:24 pm

Hi.

When you do a PITR you have to open the database using the RESETLOGS option, which recreates the redo logs.

http://www.oracle-base.com/articles/9i/ ... teRecovery

You are getting this error because you have not actually completed the PITR. :) Opening the DB with the RESETLOGS option will complete the recovery.

Cheers

Tim...
Tim...
Oracle ACE Director
Oracle ACE of the Year 2006 - Oracle Magazine Editors Choice Awards
OakTable Member
OCP DBA 7.3, 8, 8i, 9i, 10g, 11g
OCP Advanced PL/SQL Developer
Oracle Database: SQL Certified Expert
My website: http://www.oracle-base.com
My blog: http://www.oracle-base.com/blog

kytemaniac
Senior Member
Posts: 234
Joined: Tue May 19, 2009 12:59 am

Re: DB PITR in 11.2.0.1

Postby kytemaniac » Mon Sep 23, 2013 12:18 am

with reference to http://docs.oracle.com/cd/E11882_01/bac ... m#i1011846



If satisfied with the results, then perform either of the following mutually exclusive actions:

Open your database for read/write, abandoning all changes after the target SCN. In this case, you must shut down the database, mount it, and then execute the following command:

ALTER DATABASE OPEN RESETLOGS;

The OPEN RESETLOGS operation fails if a data file is offline unless the data file went offline normally or is read-only. You can bring files in read-only or offline normal tablespaces online after the RESETLOGS because they do not need any redo.

Export one or more objects from your database with Data Pump Export. You can then recover the database to the current point in time and re-import the exported objects, thus returning these objects to their state before the unwanted change without abandoning all other changes.





once I open reset logs isn't that means I will abandon the changes and creates a new incarnation? I means in reall life there can be other changes to other objects which will not be logical errors. If I abandon the changes I will also abandoining the correct changes to other objects.

with reference to http://newsgroups.derkeiler.com/Archive ... 00069.html

Note:399276.1

"Solution
It is possible to open a database READ ONLY in the middle of recovery
to check the data content
before deciding to proceed with recovery but with certain
restrictions:

1. Minimal recovery must be done before the database can be opened
READ ONLY ie you must at least
apply the log that was current at the time the backup ended and the
database must be consistent.
2. Recovery must be done using the 'backup controlfile' option, even
if the controlfile in use is
actually the CURRENT controlfile otherwise the database will fail to
open with :"-



could anyone explain expliciy what does the above means?

thanks!

Tim...
Site Admin
Posts: 18437
Joined: Mon Nov 01, 2004 5:56 pm
Location: England, UK
Contact:

Re: DB PITR in 11.2.0.1

Postby Tim... » Mon Sep 23, 2013 8:54 pm

Hi.

Opening with RESETLOGS is like saying, this is definitely the point in time i want. The redo logs are recreated, so any changes beyond the current point in time are lots. That's fine, because it is what you want in a PITR. The database will be consistent to the SCN/Time.

I'm not sure what you expect me to comment on about the second part of the post.

Cheers

Tim...
Tim...
Oracle ACE Director
Oracle ACE of the Year 2006 - Oracle Magazine Editors Choice Awards
OakTable Member
OCP DBA 7.3, 8, 8i, 9i, 10g, 11g
OCP Advanced PL/SQL Developer
Oracle Database: SQL Certified Expert
My website: http://www.oracle-base.com
My blog: http://www.oracle-base.com/blog


Return to “Oracle Database Administration”

Who is online

Users browsing this forum: No registered users and 8 guests