Table restore from complete RMAN backup

Scenario:
Nisha works at premier bank headquarters in Virginia.They have databases primarily running with versions 8i(8.1.7.4) and 9i (9.2.0.6).Full backups are scheduled to run every Sunday using rman. Archivelog backups are taken every two hours. On Tuesday night at 8pm Nisha receives call from management team that, one of application person had dropped a SALES table from "SALE" database.

Nisha is engaged on a conference call to gather more details. Soon she realizes that, she may be out of options other than doing full restore/recovery of database and that will take long time. At this point of time she plans to consult with other DBA team members from branch office. Brian came up with alternate plan which will allow to perform table restore up to point in time in past using RMAN backups.


Plan of action:

Brian lays out complete Process and scripts and explain details to Nisha,

1] Find out last full successful backup.
2] Pickup date and time of restore and calculate duration between last full backup and restore time.
3] Find archivelogs required to roll forward from last full backup till restore time.
4] Now find out tablespace in which table was created.
5] Identify datafiles in that tablespace.
6] Calculate space required for datafiles and other required files. Lets assume SALES_DATA tablespace had 4 datafiles each of 2G, so total of 8G then we have system tablespace of 300M and 2 redo log groups with 2 members each of 100M that will be total of 400M. Finally total archivelogs required are 24 and hence space for archivelog required for recovery will be 2.4G.
Total space will be 8G (datafiles )+ 2.4G (archive) + 400M( redo) + 300M(system) + 200M (control files and other scripts)
is 11.3G.
7] Find out oracle home which have binaries installed and running with same version
8] If you don't want to perform restore/recovery on production server and you have available test server with similar configuration, you can add space on TEST server and NFS mount to prod server to avoid redirected restore and recovery effort.
In this case, test server has similar version and patchset available and hence we will use TEST server to run restore/recovery.

We had temp space /u_stage of 15G which we mounted on TEST server and then NFS mounted to PROD server which will allow us to perform restore operation. Once restore is complete, we will remove nfs mount and perform recovery.

9] run restore of datafiles with system and 4 datafiles

run{

allocate channel t1 type 'SBT_TAPE' parms
'ENV=;

set until time "to_date(''Time of restore before drop",'MON-DD-YYYY HH24:MI:SS')";


set newname for datafile '/u01/system_01.dbf' to '/u_stage/system_01.dbf';
restore datafile '/u01/system_01.dbf';

set newname for datafile '/u02/sales_01.dbf' to '/u_stage/sales_01.dbf';
restore datafile '/u02/sales_01.dbf';

set newname for datafile '/u02/sales_02.dbf' to '/u_stage/sales_02.dbf';
restore datafile '/u02/sales_02.dbf';

set newname for datafile '/u04/sales_03.dbf' to '/u_stage/sales_03.dbf';
restore datafile '/u04/sales_03.dbf';

set newname for datafile '/u04/sales_04.dbf' to '/u_stage/sales_04.dbf';
restore datafile '/u04/sales_04.dbf';

release channel t1;
}

Set until time is time of exact restore just before table drop
set newname for datafile will allow us to restore at alternate location other than production and hence we can perform

restore and recovery without touching production instance.

10] Run restore of archivelogs
run{

allocate channel t1 type 'SBT_TAPE' parms
'ENV=;

set archivelog destination "/u_stage/archive

restore archivelog from logseq "X" until logseq "Y";

release channel t1;
}


This will restore archivelogs from sequence # X to sequence # Y to destination /u_stage/archive which will be different than production location and hence it can be done on separate node without affecting production. Where X and Y are lowest and highest archivelog sequnece number required to recover database until time specified.

11] Now dump controlfile from current production to trace
SQL> alter database backup controlfile to trace;

12] Edit controlfile and keep only datafiles which are being restored along with SYSTEM datafile and redolog members.

Edit path of redolog files to point to /u_stageWe will need to edit database name from "SALE" to "SALEDUP" to avoid any issues.
No archivelog mode is set in order to avoid any archive issues and also resetlogs is necessary since we are doing partial restore/recovery effort.

STARTUP NOMOUNT
CREATE CONTROLFILE REUSE DATABASE "SALEDUP" RESETLOGS NOARCHIVELOG
MAXLOGFILES 32
MAXLOGMEMBERS 3
MAXDATAFILES 1000
MAXINSTANCES 1
MAXLOGHISTORY 900
LOGFILE
GROUP 1 (
'/u_stage/redo1_a.dbf',
'/u_stage/redo1_b.dbf'
) SIZE 100M,
GROUP 2 (
'/u_stage/redo2_a.dbf',
'/u_stage/redo2_b.dbf'
) SIZE 100M

DATAFILE
'/u_stage/system_01.dbf',
'/u_stage/sales_01.dbf',
'/u_stage/sales_02.dbf',
'/u_stage/sales_03.dbf',
'/u_stage/sales_04.dbf'
CHARACTER SET US7ASCII
;


13] Edit initSALEDUP.ora file and set db_name to SALEDUP also remove remote_login_password file change path of bdump,udump,adump,cdump and any directories to /u_stage. Also change controlfiles to new location /u_stage. We have created /u_stage/bdump, /u_stage/adump respectively and point dump directories to these one. Also we created /u_stage/controlfiles and put all controlfiles in here.

14] Startup database in nomount mode using binaries from test server(which was selected earlier) which should be of same version and patchset as of databse which we are planning to restore

#sqlplus /nolog
SQL>connect / as sysdba;
SQL>startup nomount;

15] Once database is up in nomount mode, run create controlfile script and recreate new controlfiles

SQL>@create_controlfile.lst
SQL>

Controfile created.

SQL>

16] Mount database and set autorecovery on with time specified before drop.

SQL> set autorecovery on;
SQL> alter session set nls_date_format='mm-dd-yyyy:hh24:mi:ss';
SQL> recover database using backup controlfile until time ''time of restore before drop";

..
... LOGS applied
....

Media recovery complete.

17] Open database in resetlog mode

SQL>Alter database open resetlogs;

18] Now you have restored and successfully recovered objects in SALES_DATA tablespaces upto point in time (just before table drop) specified in recovery script. Make sure there are no index/es on table, if there are any drop them otherwise when queried it will try to access indexes and since we havent restored any indexes tablespaces query will fail.

19] Check and run export of object which was recovred.
SQL> select count(1) from PROJECT.SALES;
count(1)
--------
3900356

SQL>

#exp project/tmp123 file=sales_restore.dmp log=sales_restore.log tables=sales
..
...
....
Exporting table SALES 3900356 rows exported.
Export terminated successfully without warnings.

20] We can alternatively add SALEDUP sid in listener configuration and reload listener file on TEST server and then create dblink to prod and queries from prod via dblink to restored table. SQLPLUS copy is also possible option once SALEDUP is added in listener configuration.


Conclusion:
Nisha was successfully able to restore one table point in time to get required data back from full rman backup within 4 hrs vis-a-vis a total database recovery which may have taken more than 30hours. Within short time, required data was pulled over and recreated table and business resumed as normal with a minimal downtime of functionality.

Sending email from Unix with attachment and your name as "reply to" person

Challenges/Requirements:
One afternoon received phone call from customer.They wanted to run a report on daily basis to look for obsolete records in database and have them mail out report for those obsolete records. This report was also going to be viewed by higher management and hence required better arrangement in email.

Thinking:
1 We all know mailx commmand by now, which will help us to send emails from unix server to get information/reports on regular basis when put in a script and scheduled via CRON.
2 Since this email report was going to be viewed by higher management, proper text layout and viewing arrangement was necessary. So this time I decided to add attachment to email along with the body Which is different than sending regular mail.
3 One major drawback I have seen since so many years is that, we always send email out via mailx command where recepient receives that email from "unix user" who is running mailx command. In most of database accounts user name is oracle, so we get sender as "oracle@'hostname.domain.com' ". If /etc/passwd file has owner as physical person, then we may see alias to above email sender as " 'account owner name' oracle@'hostname.domain.com' "
4 Also, when recepient do "reply to all", they will need editing in sender list, else it will bring "mailer daemon" to replyer of that email since unix host will not receive any emails.
5 Finally, If I am not around to reply in repsonse to any issues in that report, we needed my manager to be CC'ed so that, he can allocate resource to take care of any work.
6 Creating report was easy part of all. So finally decided to create a quick script before I leave home for the day.


Implementation/scripts/outputs :
1 created a regular shell script and scheduled via CRON
2 "-r" in mailx command is "reply to" option. If we provide "my_name" along with -r option then, it will be replied back to me.
3 Attachment to mailx command is simple. We need to pipe "body of email" to file then pipe that to uuencode command to make an attachment and than pipe it again to mailx

--Script details
LOGFILE=/home/oracle/1.log
DATA=/home/oracle/1.data
.
..
... {rest of script to spool data via sqlplus in to file named LOGFILE }
....
echo "Please find attached file.\n Thanks,\n Sandeep " >${DATA}|uuencode ${LOGFILE} Obsolete_data.txt|cat ${DATA} -|mailx -s "Obsolete data from ${ORACLE_SID} database" -c " 'my_manager'@domain.com, 'my_name'@domain.com" -r " 'my_name'@domain.com"
" 'client_1'@customer.domain.com,'client_2'@Customer.domain.com"
.....


4 In mailx command
mailx -s "Obsolete data from ${ORACLE_SID} database" -c " 'my_manager'@domain.com, 'my_name'@domain.com" -r "@domain.com" " 'client_1'@customer.domain.com, 'client_2'@Customer.domain.com"

-c will perform Carbon Copy to my manager
-r will "reply to" person described after that option
-s is subject of email
5 In uunecode command
uuencode ${LOGFILE} Obsolete_data.txt|cat ${DATA}

${LOGFILE} is file spooled by sqlplus which has all report of obsolete record. Remember to view this file in "wordpad" when received in email as an attachment to see proper format.
Followed by attachment is name you want to provide to attached file
${DATA} contains body of email.

Conclusion:
We have successfully created email from unix host with an attachment which not only provides security to sensitive data,but also improves readability of emails by keeping required information in attached files. Customer was very pleased to receive daily report with proper format along with management contacts from my side.

Introduction...

Hello folks,

I have just created this blog for posting out rman/backup and recovery techniques and discuss new/unpublished features along with with tips which will help us supporting Oracle databases.

Stay tuned for upcoming post.

Sandeep.