The Arup Nanda Blog - Oraclepoint

2y ago
11 Views
2 Downloads
386.62 KB
9 Pages
Last View : 24d ago
Last Download : 2m ago
Upload by : Emanuel Batten
Transcription

The Arup Nanda Blog: Resolving Gaps in Data Guard Apply Using Incremental RMAN .Page 1 of 9The Arup Nanda BlogConfessions of an Oracle Database Junkie - Arup Nanda The opinions expressed here are mine and mine alone. They may notnecessarily reflect that of my employers and customers - both past or present. The comments left by the reviewers are theirs aloneand may not reflect my opinion whether implied or not. None of the advice is warranted to be free of errors and ommision. Please useat your own risk and after thorough testing in your environment.HomeCollection of Some of My Very Popular Published Web ArticlesManaged Services HelpdeskOne Help Desk for all your Clients with Asset mgmt. Free Trial!Friday,December18,2009Resolving Gaps in Data Guard Apply Using Incremental RMAN BAckupRecently, we had a glitch on a Data Guard (physical standby database) on infrastructure. This is not a criticaldatabase; so the monitoring was relatively lax. And that being done by an outsourcer does not help it either. In anycase, the laxness resulted in a failure remaining undetected for quite some time and it was eventually discoveredonly when the customer complained. This standby database is usually opened for read only access from time totime.This time, however, the customer saw that the data was significantly out of sync with primary and raised ared flag. Unfortunately, at this time it had become a rather political issue.Since the DBA in charge couldn’t resolve the problem, I was called in. In this post, I will describe the issue and howit was resolved. In summary, there are two parts of the problem:(1) What happened(2) How to fix itWhat HappenedLet’s look at the first question – what caused the standby to lag behind. First, I looked for the current SCN numbersof the primary and standby databases. On the primary:SQL select current scn from v database;CURRENT SCN----------1447102On the standby:SQL select current scn from v database;CURRENT SCN----------1301571Clearly there is a difference. But this by itself does not indicate a problem; since the standby is expected to lagbehind the primary (this is an asynchronous non-real time apply setup). The real question is how much it is laggingin the terms of wall clock. To know that I used the scn to timestamp function to translate the SCN to a timestamp:SQL select scn to timestamp(1447102) from dual;SCN TO -DEC-09 08.54.28.000000000 AMSearch This BlogSearchI ran the same query to know the timestamp associated with the SCN of the standby database as well (note, I ran iton the primary database, though; since it will fail in the standby in a mounted mode):SQL select scn to timestamp(1301571) from dual;About MeArup NandaSCN TO -DEC-09 07.19.27.000000000 PMThis shows that the standby is two and half days lagging! The data at this point is not just stale; it must be rotten.819Oracle technologist, architect,author, speaker and mentor.View my complete profileThe next question is why it would be lagging so far back in the past. This is a 10.2 database where FAL ps-in-data-guard-apply.html1/26/2015

The Arup Nanda Blog: Resolving Gaps in Data Guard Apply Using Incremental RMAN .should automatically resolved any gaps in archived logs. Something must have happened that caused the FAL (fetcharchived log) process to fail. To get that answer, first, I checked the alert log of the standby instance. I foundthese lines that showed the issue clearly: Fri Dec 18 06:12:26 2009Waiting for all non-current ORLs to be archived.Media Recovery Waiting for thread 1 sequence 70046Follow by EmailSubmitFetching gap sequence in thread 1, gap sequence 700-700 Fri Dec 18 06:13:27 2009FAL[client]: Failed to request gap sequenceGAP - thread 1 sequence 700-700DBID 846390698 branch 697108460FAL[client]: All defined FAL servers have been attempted. Going back in the alert log, I found these lines:Tue Dec 15 17:16:15 2009Fetching gap sequence in thread 1, gap sequence 700-700Error 12514 received logging on to the standbyFAL[client, MRP0]: Error 12514 connecting to DEL1 for fetching gap sequenceTue Dec 15 17:16:15 2009Popular PostsResolving Gaps in Data Guard Apply Using IncrementalRMAN BAckupRecently, we had a glitch on a Data Guard (physicalstandby database) on infrastructure. This is not acritical database; so the monitoring .Difference between Select Any Dictionary andSelect Catalog RoleWhen you want to give a user the privilege to selectfrom data dictionary and dynamic performance viewssuch as V DATAFILE, you have two opt.How Oracle Locking WorksMyth: Oracle has a Central LockingSystem A week or so ago I started anew series – 100 Things YouProbably didn’t Know about OracleDatab.Errors in file /opt/oracle/admin/DEL2/bdump/del2 mrp0 18308.trc:ORA-12514: TNS:listener does not currently know of service requested in connectdescriptorTue Dec 15 17:16:45 2009Error 12514 received logging on to the standbyFAL[client, MRP0]: Error 12514 connecting to DEL1 for fetching gap sequenceTue Dec 15 17:16:45 2009Errors in file /opt/oracle/admin/DEL2/bdump/del2 mrp0 18308.trc:ORA-12514: TNS:listener does not currently know of service requested in connectdescriptor100 Things You Probably Didn't Know About OracleDatabaseRecently, while delivering a presentation on CacheFusion at New York Oracle Users Group( www.nyoug.org ), the regional user group where I .Application Design is the onlyReason for Deadlocks? Think Again[Updated on 4/20/2013 afterfeedback from Charles Hooper ,Jonathan Lewis , Lau rentSchneider and Mohamed Houri andwith some minor cosm.This clearly showed the issue. On December 15th at 17:16:15, the Managed Recovery Process encountered an errorwhile receiving the log information from the primary. The error was ORA-12514 “TNS:listener does not currentlyknow of service requested in connect descriptor”. This is usually the case when the TNS connect string isincorrectly specified. The primary is called DEL1 and there is a connect string called DEL1 in the standby server.The connect string works well. Actually, right now there is no issue with the standby getting the archived logs; sothere connect string is fine - now. The standby is receiving log information from the primary. There must have beensome temporary hiccups causing that specific archived log not to travel to the standby. If that log was somehowskipped (could be an intermittent problem), then it should have been picked by the FAL process later on; but thatnever happened. Since the sequence# 700 was not applied, none of the logs received later – 701, 702 and so on –were applied either. This has caused the standby to lag behind since that time.So, the fundamental question was why FAL did not fetch the archived log sequence# 700 from the primary. To getto that, I looked into the alert log of the primary instance. The following lines were of interest: Tue Dec 15 19:19:58 2009Thread 1 advanced to log sequence 701 (LGWR switch)Current log# 2 seq# 701 mem# 0: /u01/oradata/DEL1/onlinelog/o1 mf 2 5bhbkg92 .logTue Dec 15 19:20:29 2009Errors inStandby Database Using RMANI learn something about Oracle everyday and in thelast few days the learnings have been rather painful. Ihave been trying to create a phys.Resource Manager I/O Calibration in 11gYou are considering a new platform for the Oracledatabase; or perhaps worried about the I/Ocapabilities of the existing platform. How do y.Restoring Controlfile When AUTOBACKUP FailAllow me to present the snapshot of a day from thelife of John--the DBA at Acme Bank. On this particularday a database John manages crashe.Demystifying Big Data for OracleProfessionalsEver wonder about Big Data andwhat exactly it means, especially ifyou are already an Oracle Databaseprofessional? Or, do you get lostin .file 1 arc1 14469.trc:ORA-00308: cannot open archived log '/u01/oraback/1 700 697108460.dbf'ORA-27037: unable to obtain file statusLinux Error: 2: No such file or directoryAdditional information: 3Tue Dec 15 19:20:29 2009FAL[server, ARC1]: FAL archive failed, see trace file.Tue Dec 15 19:20:29 2009Errors in file 1 arc1 14469.trc:ORA-16055: FAL request rejectedARCH: FAL archive failed.Archiver continuingTue Dec 15 19:20:29 2009ORACLE Instance DEL1 - Archival Error. Archiver continuing. These lines showed everything clearly. The issue was:Page 2 of 9Who Manages the Exadata Machine?For organizations that just procured an Exadatamachine, one of the big questions is bound to be aboutthe group supporting it. Who should i.Blog Archive 2014(19) 2013(15) 2012(8) 2011(31) 2010(25) 2009(19)ORA-00308: cannot open archived log '/u01/oraback/1 700 697108460.dbf'ORA-27037: unable to obtain file statusLinux Error: 2: No such file or directoryThe archived log simply was not available. The process could not see the file and couldn’t get it across to thestandby site.Upon further investigation I found that the DBA actually removed the archived logs to make some room in thefilesystem without realizing that his action has removed the most current one which was yet to be transmitted tothe remote site. The mystery surrounding why the FAL did not get that log was finally cleared. December(3)Instance Number is busy Message during StandbyIns.Resolving Gaps in Data Guard Apply UsingIncrement.Sepaking at New York Oracle Users Group November(1) October(7) September(3) ps-in-data-guard-apply.html1/26/2015

The Arup Nanda Blog: Resolving Gaps in Data Guard Apply Using Incremental RMAN .SolutionNow that I know the cause, the focus was now on the resolution. If the archived log sequence# 700 was available onthe primary, I could have easily copied it over to the standby, registered the log file and let the managed recoveryprocess pick it up. But unfortunately, the file was gone and I couldn’t just recreate the file. Until that logfile wasapplied, the recovery will not move forward. So, what are my options?Page 3 of 9 March(1) January(3) 2008(18) 2007(5) 2005(1)One option is of course to recreate the standby - possible one but not technically feasible considering the timerequired. The other option is to apply the incremental backup of primary from that SCN number. That’s the key –the backup must be from a specific SCN number. I have described the process since it is not very obvious. Thefollowing shows the step by step approach for resolving this problem. I have shown where the actions must beperformed – [Standby] or [Primary]. 2003(1)FollowersJoin this site1. [Standby] Stop the managed standby apply process:SQL alter database recover managed standby database cancel;with Google Friend ConnectMembers (313) More »Database altered.2. [Standby] Shutdown the standby database3. [Primary] On the primary, take an incremental backup from the SCN number where the standby has been stuck:RMAN run {2 allocate channel c1 type disk format '/u01/oraback/%U.rmb';3 backup incremental from scn 1301571 database;4 }Already a member?Sign inusing target database control file instead of recovery catalogallocated channel: c1channel c1: sid 139 devtype DISKSubscribe ToPostsStarting backup at 18-DEC-09channel c1: starting full datafile backupsetchannel c1: specifying datafile(s) in backupsetinput datafile fno 00001 name /u01/oradata/DEL1/datafile/o1 mf system 5bhbh59c .dbf piece handle /u01/oraback/06l16u1q 1 1.rmb tag TAG20091218T083619 comment NONEchannel c1: backup set complete, elapsed time: 00:00:06Finished backup at 18-DEC-09released channel: c1Comments4. [Primary] On the primary, create a new standby controlfile:SQL alter database create standby controlfile as '/u01/oraback/DEL1 standby.ctl';Database altered.5. [Primary] Copy these files to standby host:oracle@oradba1 /u01/oraback# scp *.rmb *.ctl oracle@oradba2:/u01/orabackoracle@oradba2's password:06l16u1q 1 1.rmb 100% 43MB 10.7MB/s 00:04DEL1 standby.ctl 100% 43MB 10.7MB/s 00:046. [Standby] Bring up the instance in nomount mode:SQL startup nomount7. [Standby] Check the location of the controlfile:SQL show parameter control filesNAME TYPE VALUE------------------------------------ ----------- -----------------------------control files string /u01/oradata/standby cntfile.ctl8. [Standby] Replace the controlfile with the one you just created in primary.9. cp /u01/oraback/DEL1 standby.ctl /u01/oradata/standby cntfile.ctl10.[Standby] Mount the standby database:SQL alter database mount standby database;11.[Standby] RMAN does not know about these files yet; so you must let it know – by a process called cataloging.Catalog these files: rman target /Recovery Manager: Release 10.2.0.4.0 - Production on Fri Dec 18 06:44:25 2009Copyright (c) 1982, 2007, Oracle. All rights reserved.connected to target database: DEL1 (DBID 846390698, not s-in-data-guard-apply.html1/26/2015

The Arup Nanda Blog: Resolving Gaps in Data Guard Apply Using Incremental RMAN .Page 4 of 9RMAN catalog start with '/u01/oraback';using target database control file instead of recovery catalogsearching for all files that match the pattern /u01/orabackList of Files Unknown to the Database File Name: /u01/oraback/DEL1 standby.ctlFile Name: /u01/oraback/06l16u1q 1 1.rmbDo you really want to catalog the above files (enter YES or NO)? yescataloging files.cataloging doneList of Cataloged Files File Name: /u01/oraback/DEL1 standby.ctlFile Name: /u01/oraback/06l16u1q 1 1.rmb12.Recover these files:RMAN recover database;Starting recover at 18-DEC-09using channel ORA DISK 1channel ORA DISK 1: starting incremental datafile backupset restorechannel ORA DISK 1: specifying datafile(s) to restore from backup setdestination for restore of datafile00001: /u01/oradata/DEL2/datafile/o1 mf system 5lptww3f .dbf. channel ORA DISK 1: reading from backup piece /u01/oraback/05l16u03 1 1.rmbchannel ORA DISK 1: restored backup piece 1piece handle /u01/oraback/05l16u03 1 1.rmb tag TAG20091218T083619channel ORA DISK 1: restore complete, elapsed time: 00:00:07starting media recoveryarchive log thread 1 sequence 8012 is already on disk asfile /u01/oradata/1 8012 697108460.dbfarchive log thread 1 sequence 8013 is already on disk asfile /u01/oradata/1 8013 697108460.dbf 13. After some time, the recovery fails with the message:archive log filename /u01/oradata/1 8008 697108460.dbf thread 1 sequence 8009RMAN-00571: RMAN-00569: ERROR MESSAGE STACK FOLLOWS RMAN-00571: RMAN-03002: failure of recover command at 12/18/2009 06:53:02RMAN-11003: failure during parse/execution of SQL statement: alter database recoverlogfile '/u01/oradata/1 8008 697108460.dbf'ORA-00310: archived log contains sequence 8008; sequence 8009 requiredORA-00334: archived log: '/u01/oradata/1 8008 697108460.dbf'This happens because we have come to the last of the archived logs. The expected archived log with sequence#8008 has not been generated yet.14.At this point exit RMAN and start managed recovery process:SQL alter database recover managed standby database disconnect from session;Database altered.15.Check the SCN’s in primary and standby:[Standby] SQL select current scn from v database;CURRENT SCN----------1447474[Primary] SQL select current scn from v database;CURRENT SCN----------1447478Now they are very close to each other. The standby has now caught up.at3:27 PM 10 Recommend this on GoogleLabels:Data Guard,FAL Server,Gap Resolution,RMAN Incremental,Standby resolving-gaps-in-data-guard-apply.html1/26/2015

The Arup Nanda Blog: Resolving Gaps in Data Guard Apply Using Incremental RMAN -data-guard-apply.htmlPage 5 of 91/26/2015

The Arup Nanda Blog: Resolving Gaps in Data Guard Apply Using Incremental RMAN .Page 6 of 964 commentsAdd a commentTop commentsRajanikant Kumar 1 year ago - Shared publiclyThis notes is awesome.· ReplyArup Nanda 10 months agoThanks, Rajanikant.SUDHAKARA RAO Vallabhapurapu 1 year ago - Shared publiclyHi ArupGreat blog with useful information to apply incremental backup to standby. Have onequestion, standby recovery information shows sequence 8012 and 8013 are already ondisk, whereas it failed at sequence 8008 when it reached last file. Why this difference?something missing here.Read more· ReplyArup Nanda 1 year agoThat's because the archived log has been deleted as not required, or wasn'tsomehow generated. This is the cause of the gap.Arup Nanda 10 months agoSudhakara - any updates since your posted it? Did you finally figure out the cause ofthe gaps?Douglas Paiva 1 month ago - Shared publiclyReally nice! too much useful for me.Thanks a lot.· Replyrajkishore patro 1 year ago - Shared publiclyI was searching for this article since a very long time. Thanks a lot Arup. Keep posting thistype o case studies.· ReplyAndrea Colombo 1 year ago - Shared publiclyGreat! Very useful and accurate.· ReplyTapak K. Sahoo 1 year ago - Shared publiclyReally useful topic.· ReplyNariman Narimanov 2 months ago - Shared publiclyHello Arup,Thanks for article. Just would like to add some correction. Getting current scn fromv database is not always true. You should take lowest value from currenct scn and queryresult below:select min(fhscn) from x ps-in-data-guard-apply.html1/26/2015

The Arup Nanda Blog: Resolving Gaps in Data Guard Apply Using Incremental RMAN -data-guard-apply.htmlPage 7 of 91/26/2015

The Arup Nanda Blog: Resolving Gaps in Data Guard Apply Using Incremental RMAN .Page 8 of 9Post a aps-in-data-guard-apply.html1/26/2015

The Arup Nanda Blog: Resolving Gaps in Data Guard Apply Using Incremental RMAN .Newer PostHomePage 9 of 9Older PostSubscribe to:Post Comments (Atom)TranslateSelect LanguagePowered by TranslateTotal PageviewsShare it817,253Share this on FacebookTweet thisView stats(NEW) Appointment gadget (c) Arup Nanda. Simple template. Powered by gaps-in-data-guard-apply.html1/26/2015

Confessions of an Oracle Database Junkie -Arup Nanda The opinions expressed here are mine and mine alone. They may not . Since the DBA in charge couldn’t resolve the problem, I was called in. In this post, I will describe the issue and how . speaker and

Related Documents:

May 02, 2018 · D. Program Evaluation ͟The organization has provided a description of the framework for how each program will be evaluated. The framework should include all the elements below: ͟The evaluation methods are cost-effective for the organization ͟Quantitative and qualitative data is being collected (at Basics tier, data collection must have begun)

Silat is a combative art of self-defense and survival rooted from Matay archipelago. It was traced at thé early of Langkasuka Kingdom (2nd century CE) till thé reign of Melaka (Malaysia) Sultanate era (13th century). Silat has now evolved to become part of social culture and tradition with thé appearance of a fine physical and spiritual .

On an exceptional basis, Member States may request UNESCO to provide thé candidates with access to thé platform so they can complète thé form by themselves. Thèse requests must be addressed to esd rize unesco. or by 15 A ril 2021 UNESCO will provide thé nomineewith accessto thé platform via their émail address.

̶The leading indicator of employee engagement is based on the quality of the relationship between employee and supervisor Empower your managers! ̶Help them understand the impact on the organization ̶Share important changes, plan options, tasks, and deadlines ̶Provide key messages and talking points ̶Prepare them to answer employee questions

Dr. Sunita Bharatwal** Dr. Pawan Garga*** Abstract Customer satisfaction is derived from thè functionalities and values, a product or Service can provide. The current study aims to segregate thè dimensions of ordine Service quality and gather insights on its impact on web shopping. The trends of purchases have

Chính Văn.- Còn đức Thế tôn thì tuệ giác cực kỳ trong sạch 8: hiện hành bất nhị 9, đạt đến vô tướng 10, đứng vào chỗ đứng của các đức Thế tôn 11, thể hiện tính bình đẳng của các Ngài, đến chỗ không còn chướng ngại 12, giáo pháp không thể khuynh đảo, tâm thức không bị cản trở, cái được

North American Nursing Diagnosis Association (NANDA) NANDA didirikan sebagai badan formal untuk meningkatkan, mengkaji kembali dengan mengesahkan daftar terbaru dari diagnosis keperawatan yang digunakan oleh perawat praktisi. NANDA mengidentifikasi masalah atau k

ASTM E 989: 1989Classification for determination of impact VM1 2.0.1 insulation class (IIC) International Standards Organisation ISO 140/VII: 1978 Field measurements of impact sound VM1 2.0.1 insulation of floors Amend 2 Dec 1995 Amend 2 Dec 1995 Amend 2 Dec 1995. 9 AIRBORNE AND IMPACT SOUND DEPARTMENT OF BUILDING AND HOUSING July 1992 Definitions G6/VM1 & AS1 Adequate Adequateto achieve the .