Solved

DB file transfer onto another machine and Data Guard sequence number issue

Posted on 2011-09-22
3
265 Views
Last Modified: 2012-05-12
I have a problem with DB file transfer and Data Guard sequence numbers.

History:
I have a live system (LS) with its data being mirrored onto another system (BS) via Data Guard.  The live system has a disk problem and one of the tablespace files can't be copied.

What is almost working except for the Data Guard:
(i) I do a synchronization between the two systems with the following:
      SQL> alter system switch logfile
      SQL> shutdown immediate
(ii) Shutdown the oracle data Guard on the 'BS':
SQL> alter database recover managed standby database cancel;
SQL> shutdown immediate
(iii) Copy the control files; logXDB.dbf files; init*.ora; orapwASYDB from the 'LS' to a vmWare Live system (vmwLS).
(iv) Copy the tablespace files;control files; logXDB.dbf files; init*.ora; orapwASYDB from the 'BS' to a vmWare backup system (vmwBS).
(v) Copy the tablesapce files from the 'BS' to the 'vmwLS'
(vi) I have to run 'recover datafile X' on 6 datafiles in order to be able to start the database on the vmwLS.

The sequence number info is as follows:
With just the Data Guard running on the vmwBS, we have the last sequence #: 197555; Applied = YES
When the vmwLS is started, we get on the vmwBS:
197555; Applied = YES
197557; Applied = NO

Yes '197556' is missing, all together.

What is my problem?





0
Comment
Question by:Hope4U
  • 2
3 Comments
 
LVL 6

Expert Comment

by:Greg Clough
Comment Utility
If I understand that, your LS database suffered a disk failure which has damaged some datafiles belonging to a tablespace.  The only way to recover these is to either:

1) Restore those datafiles from a backup (rman, hot), and then apply ALL logs needed to bring it up to date.

If you can't restore the datafile, or are missing even 1 log... then the best way would be:

2) Activate your BS database, as it should have all of your transactions.

If you want to try this on a Virtual System to avoid damage to your LS and/or BS system (great idea by the way), then copy the BS database to vwmBS, and force it online as a primary.

This link may be helpful:

http://download.oracle.com/docs/cd/B12037_01/server.101/b10726/recover.htm#1006522
0
 

Accepted Solution

by:
Hope4U earned 0 total points
Comment Utility
I with the help of a Guru (F.J.) found a solution:

Requirement: Must have an older working image of the 'LS' installed on the 'vmwLS'.


[1] Stopped the LS oracle DB using :
SQL>  alter system switch logfile;
SQL> shutdown immediate.

[2] Stopped the BS oracle DB with these commands:
SQL> alter database recover managed standby database cancel;
SQL> shutdown immediate;

[3] Copy all the following files from the BS to the new vmwLS: Datafiles; Control and Log files; init*.ora files; orapwSID file.
[4] Copy the 'last archive log file' from the LS to the vmwLS - Location '/u02/oradata/SID/arch/'
[5] Set all the permissions and correct owners on the above files.

[6] Repeat step [3 - 5] on the vmwBS except you only add the 'last archive log file' that is present on the BS.

[7] Start up the vmwBS with:
SQL> connect / as sysdba
SQL> Startup nomount;
SQL> ALTER DATABASE MOUNT STANDBY DATABASE;
SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION;

[8] Startup the vmwLS with:
SQL> connect / as sysdba
SQL> Startup;

Should get several datafile errors until all are fixed.  Use 'SQL> recover datafile X', where X is the number of the datafile. I had six to fix.

Then restart again.

[9] Can check that the vmwLS and vmwBS are in sync with:
SQL> SELECT SEQUENCE#,FIRST_TIME, NEXT_TIME, APPLIED FROM V$ARCHIVED_LOG WHERE SEQUENCE# > 10256 ORDER BY SEQUENCE#;

And can run some 'select count(*) ...' commands on tables in the vmwLS and the LS and see how they match back before full switch over.


[10] That's all Folks - Bye.
0
 

Author Closing Comment

by:Hope4U
Comment Utility
Was the easiest solution.
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Join & Write a Comment

Suggested Solutions

Truncate is a DDL Command where as Delete is a DML Command. Both will delete data from table, but what is the difference between these below statements truncate table <table_name> ?? delete from <table_name> ?? The first command cannot be …
From implementing a password expiration date, to datatype conversions and file export options, these are some useful settings I've found in Jasper Server.
Via a live example show how to connect to RMAN, make basic configuration settings changes and then take a backup of a demo database
This video shows information on the Oracle Data Dictionary, starting with the Oracle documentation, explaining the different types of Data Dictionary views available by group and permissions as well as giving examples on how to retrieve data from th…

772 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now