{"id":1137,"date":"2016-03-15T13:53:16","date_gmt":"2016-03-15T11:53:16","guid":{"rendered":"http:\/\/www.ludovicocaldara.net\/dba\/?p=1137"},"modified":"2020-08-18T16:26:26","modified_gmt":"2020-08-18T14:26:26","slug":"how-cold-incremental-recovery-saved-me-once","status":"publish","type":"post","link":"https:\/\/www.ludovicocaldara.net\/dba\/how-cold-incremental-recovery-saved-me-once\/","title":{"rendered":"How cold incremental recovery saved me once"},"content":{"rendered":"

UPDATE: <\/strong>In the original version I was missing a few keywords: “incremental level 0” for the base backup and “resetlogs” at the database open. Thanks Gregorz for your comments.<\/p>\n

Sorry for this “memories” post, but the technical solution at the end is worth the read, I hope \ud83d\ude09<\/p>\n

Back in 2010, I was in charge of a quite complex project and faced some difficulties that led me to recover a database in a different manner. A few years have passed, but I used again the same procedure many times with full satisfaction… I think it’s worth to publish it now.<\/p>\n

But first, let me introduce the project details and the problem.<\/p>\n

 <\/p>\n

Scope of the project<\/strong><\/p>\n

Transport a >1TB RAC database from AIX 5 on P6 to AIX 6 on P7, from a third-party datacenter in southern Italy to our main datacenter in northern Italy.
\nThe Database featured >1000 datafiles and a huge table (800GB) partitioned by range and sub-partitioned by list (or the opposite, can’t remember).<\/p>\n

 <\/p>\n

Challenges<\/strong><\/p>\n

For budget containement, the project owner asked to avoid the use of HACMP (and thus, avoid the use of shared JFS2). I decided\u00a0 then to take the risk and migrate from JFS2\u00a0 to ASM.<\/p>\n

In order to avoid a few platform-related ASM bugs, I also had to upgrade from Oracle 10.2.0.3 to Oracle 10.2.0.4.<\/p>\n

 <\/p>\n

Constraints<\/strong><\/p>\n

I had no access to the source database that was 800km far from our datacenter, and I was granted only to ask for RMAN backups.<\/p>\n

The total time of service disruption accepted was quite short (<30 minutes) considering the size and the distance of the database, and there was no direct connectivity between the sites (for political reasons).<\/p>\n

Globally, the network throughput for sharing files over ftp was very poor.<\/p>\n

 <\/p>\n

First solution<\/strong><\/p>\n

This kind of move was very common to me, and because I was not grated to ask for a temporary Data Guard configuration, the easy solution for me was to ask:<\/p>\n

1 – one RMAN ONLINE full backup physically sent on disk<\/p>\n

2 – many RMAN archive backups sent over network (via ftp)<\/p>\n

Then, on my side, restore the full backup, recover the archives sent over time and, at the date X, ask a final archive backup, ask to close the db and send the online redo logs to do a complete recovery on my side, then startup open upgrade.<\/p>\n

 <\/p>\n

Problem<\/strong><\/p>\n

I did a first “dry run” open resetlogs in order to test the procedure and make it faster, and also asked to test the application pointing to the destination database.<\/p>\n

The very bad surprise was that the source database was doing a huge amount of nologging inserts leading to monster index corruptions after the recovery on the destination database.<\/p>\n

ORA-26040: Data block was loaded using the NOLOGGING option\r\n\r\n<\/pre>\n
According to the current database maintainer, setting the force logging on the source database was NOT an option because the SAN was not able to cope with the high redo rates.<\/p>\n
 <\/p>\n
Solution<\/strong><\/p>\n
By knowing the Oracle recovery mechanisms, I have proposed to the remote maintainer to change the recovery strategy, despite this solution was not clearly stated in the Oracle documentation:<\/p>\n
1 – Take a first online incremental backup from the begin scn of\u00a0 the base full backup (thank God block change tracking was in place) and send it physically over disk<\/p>\n
2 – Take other smaller online\u00a0 incremental backups, send them over ftp\u00a0 and apply them on the destination with “noredo”<\/p>\n
3 – At the date X, shutdown the source, mount it and take a last incremental in mount state<\/p>\n
4 – recover noredo the last incremental and open resetlogs the database.<\/p>\n
According to the documentation, the “cold incremental strategy” applies if you take “cold full backups”. But from a technical point of view, taking a cold incremental and recovering it on top of a fuzzy online backup this is 100% equivalent of taking a full consistent backup in mount state.
\nBecause all the blocks are consistent to a specific SCN, there are no fuzzy datafiles:\u00a0 they are recovered from incremental taken from a mounted database! This allows to do incremental recovery and open the databases without applying any single archived log and by shutting down the database only once.<\/p>\n
 <\/p>\n
Technical steps<\/strong><\/p>\n
First, take a\u00a0 full ONLINE backup on the source:<\/p>\n
-- SOURCE\r\nSQL> alter database backup controlfile to '\/tmp\/source\/ludo.cf' reuse;\r\n\r\nDatabase altered.\r\n\r\nSQL> exit\r\n$ rman target \/\r\nRMAN> backup incremental level 0 database as compressed backupset format '\/tmp\/source\/%U';<\/pre>\n
# SOURCE\r\nscp -rp \/tmp\/source\/ destsrv:\/tmp\/dest\/\r\nludo.cf              100% |*************************************| 40944 KB    00:00\r\n...<\/pre>\n
Then restore it on the destination (with no recovery):<\/p>\n
# DEST\r\nRMAN> restore controlfile from '\/tmp\/ludo.cf';\r\n\r\nStarting restore at 11-AUG-15\r\nusing target database control file instead of recovery catalog\r\nallocated channel: ORA_DISK_1\r\nchannel ORA_DISK_1: SID=1058 device type=DISK\r\n\r\nchannel ORA_DISK_1: copied control file copy\r\noutput file name=\/...\/control01.ctl\r\noutput file name=\/...\/control02.ctl\r\nFinished restore at 11-AUG-15\r\n\r\nRMAN> alter database mount;\r\n\r\nStatement processed\r\nreleased channel: ORA_DISK_1\r\n\r\nRMAN> catalog start with '\/tmp\/dest\/';\r\n...\r\nRMAN> run\r\n2> {\r\n3> set newname for database to '+DATA';\r\n4>\r\n5> restore database;\r\n6> }\r\n...\r\nFinished restore at 11-AUG-15\r\nRMAN>\r\n<\/pre>\n
Then, run a COLD incremental backup on the source:<\/p>\n
-- SOURCE\r\nSQL> shutdown immediate;\r\n...\r\nORACLE instance shut down.\r\n\r\nSQL> startup mount\r\nORACLE instance started.\r\n...\r\nDatabase mounted.\r\nSQL> exit\r\n$ rman target \/\r\nRMAN>  BACKUP AS COMPRESSED BACKUPSET INCREMENTAL LEVEL 1 \r\n2> CUMULATIVE DATABASE format '\/tmp\/source\/incr%U';\r\n...\r\nFinished backup at 11-AUG-15\r\nRMAN> exit\r\n$ scp -rp \/tmp\/source\/incr* destsrv:\/tmp\/dest\/<\/pre>\n
And run the incremental recovery on the source (without redo):<\/p>\n
# DEST\r\nRMAN> catalog start with '\/tmp\/dest\/incr';\r\n...\r\nRMAN> run {\r\n2> recover database noredo;\r\n3> }\r\n...\r\nchannel ORA_DISK_1: starting incremental datafile backup set restore\r\n...\r\nFinished recover at 11-AUG-15\r\nRMAN> exit\r\n$ sqlplus \/ as sysdba\r\n...\r\nSQL> alter database disable block change tracking;\r\nDatabase altered.\r\nSQL> alter database flashback off;\r\nDatabase altered.\r\nSQL> alter database flashback on;\r\nDatabase altered.\r\nSQL> create restore point PREUPG guarantee flashback database;\r\nRestore point created.\r\nSQL> -- open resetlogs can be avoided if I copy the online redo logs\r\nSQL> alter database open resetlogs upgrade;\r\nDatabase altered.\r\n...\r\n-- run catupgrd here<\/pre>\n
That’s all!<\/p>\n
This solution gave me the opportunity to move physically the whole >1TB nologging database from one region to another one with a minimal service disruption and without touching at all the source database.<\/p>\n
I used it many times later on, even for bigger databases and on several platforms (yes, also Windows, sigh), it works like a charm.<\/p>\n
HTH<\/p>\n
—<\/p>\n
Ludovico<\/p>\n","protected":false},"excerpt":{"rendered":"
UPDATE: In the original version I was missing a few keywords: “incremental level 0” for the base backup and “resetlogs” at the database open. Thanks Gregorz for your comments. Sorry for this “memories” post, but the technical solution at the … Continue reading →<\/span><\/a><\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[326,3,330],"tags":[263,260,259,262,265,261,264],"class_list":["post-1137","post","type-post","status-publish","format-standard","hentry","category-oracle","category-oracledb","category-oracle-inst-upg","tag-block-corruptions","tag-incremental-backup","tag-incremental-recovery","tag-nologging","tag-ora-26040","tag-recover-strategy","tag-vdatabase_block_corruption"],"aioseo_notices":[],"_links":{"self":[{"href":"https:\/\/www.ludovicocaldara.net\/dba\/wp-json\/wp\/v2\/posts\/1137","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.ludovicocaldara.net\/dba\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.ludovicocaldara.net\/dba\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.ludovicocaldara.net\/dba\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/www.ludovicocaldara.net\/dba\/wp-json\/wp\/v2\/comments?post=1137"}],"version-history":[{"count":7,"href":"https:\/\/www.ludovicocaldara.net\/dba\/wp-json\/wp\/v2\/posts\/1137\/revisions"}],"predecessor-version":[{"id":1323,"href":"https:\/\/www.ludovicocaldara.net\/dba\/wp-json\/wp\/v2\/posts\/1137\/revisions\/1323"}],"wp:attachment":[{"href":"https:\/\/www.ludovicocaldara.net\/dba\/wp-json\/wp\/v2\/media?parent=1137"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.ludovicocaldara.net\/dba\/wp-json\/wp\/v2\/categories?post=1137"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.ludovicocaldara.net\/dba\/wp-json\/wp\/v2\/tags?post=1137"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}