Tag: RMAN restore

Restore RMAN backup to same host with same database name | Windows

On By windows7bugsIn NOT a DBA, Oracle Database 19cLeave a comment

I think I have mentioned few times already that I am not a certified (Certifitted as we call it sarcastically due to the pure ignorance of much of them) DBA, yet I love executing experiments with Oracle database technology and come up with some hacks, tricks etc. As usual, whatever you read ahead is NOT recommended for PRODUCTION environment and if you end up setting up something that works finally for your PRODUCTION instance, well you are sole responsible for it.

For this experiment, we will use one 19c database with multitenant structure installed on Windows 10/11. Before you attempt the below, please make sure that you have taken the backup of control file trace, spfile copy as pfile and a full backup of database using RMAN

Control file backup trace.

SQL>alter database backup controlfile to trace as 'D:\folder_name\control.txt';

SPfile to Pfile (This file should be available at $ORACLE_HOME\database\ folder)

SQL>create pfile from spfile;

Creating pfile is not mandatory incase if you are restoring the backup to same host and the paths are intact. Pfile becomes useful when you are restoring the database to another host with same directory structure.

Shutdown the database & delete the datafile from paths. For example, my datafiles were in the following paths.

There were three paths from which I needed to delete the data files. From CDB root, PDB seed and PDB itself. You should get a clear idea of the exact locations for your data files from the control file trace export.

Once after deleting the files, start the database at nomount state. The latest version of databases allow you to execute many SQL commands from RMAN itself, however, I always prefer SQL prompt for SQL activities. Suite yourself.

SQL>startup nomount

From another command/terminal window connect to RMAN

D:\RMAN>rman target /

Recovery Manager: Release 19.0.0.0.0 - Production on Sat Mar 12 11:47:15 2022
Version 19.11.0.0.0

Copyright (c) 1982, 2021, Oracle and/or its affiliates.  All rights reserved.

connected to target database: SCTCDB (not mounted)

Restore the control file from latest backup

RMAN> restore controlfile from 'D:\RMAN\BKPCONTROL_FILE.CTL_SCTCDB_20220312';

Starting restore at 12-MAR-22
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=498 device type=DISK

channel ORA_DISK_1: restoring control file
channel ORA_DISK_1: restore complete, elapsed time: 00:00:01
output file name=D:\ORACLE19C\DB\ORADATA\SCTCDB\CONTROL01.CTL
output file name=D:\ORACLE19C\DB\ORADATA\SCTCDB\CONTROL02.CTL
Finished restore at 12-MAR-22

Now try to mount the database.

RMAN> alter database mount;

released channel: ORA_DISK_1
Statement processed

Once the database mounted, let us see what the schema reprorts

RMAN> report schema;

Populate catalog from the backup.

RMAN> catalog start with 'D:\RMAN';

searching for all files that match the pattern D:\RMAN

List of Files Unknown to the Database
=====================================
File Name: D:\RMAN\BKPCONTROL_FILE.CTL_SCTCDB_20220312

Do you really want to catalog the above files (enter YES or NO)? YES
cataloging files...
cataloging done

List of Cataloged Files
=======================
File Name: D:\RMAN\BKPCONTROL_FILE.CTL_SCTCDB_20220312

Once the above completes successfully, we can proceed with restoring the backup.

RMAN> run
2> {allocate channel ch1 device type disk;
3> restore database;
4> switch datafile all;
5> switch tempfile all;
6> release channel ch1;
7> }

allocated channel: ch1
channel ch1: SID=252 device type=DISK

Starting restore at 12-MAR-22

channel ch1: starting datafile backup set restore
channel ch1: specifying datafile(s) to restore from backup set
channel ch1: restoring datafile 00004 to D:\ORACLE19C\DB\ORADATA\SCTCDB\UNDOTBS01.DBF
channel ch1: reading from backup piece D:\RMAN\SCT_SCTCDB_ LVL0_040O6TG6_1_1
channel ch1: piece handle=D:\RMAN\SCT_SCTCDB_ LVL0_040O6TG6_1_1 tag=DAILYFULL_DB_LVL0_BKP
channel ch1: restored backup piece 1
channel ch1: restore complete, elapsed time: 00:00:03
channel ch1: starting datafile backup set restore
channel ch1: specifying datafile(s) to restore from backup set
channel ch1: restoring datafile 00003 to D:\ORACLE19C\DB\ORADATA\SCTCDB\SYSAUX01.DBF
channel ch1: reading from backup piece D:\RMAN\SCT_SCTCDB_ LVL0_020O6TG5_1_1
channel ch1: piece handle=D:\RMAN\SCT_SCTCDB_ LVL0_020O6TG5_1_1 tag=DAILYFULL_DB_LVL0_BKP
channel ch1: restored backup piece 1
channel ch1: restore complete, elapsed time: 00:00:15
channel ch1: starting datafile backup set restore
channel ch1: specifying datafile(s) to restore from backup set
channel ch1: restoring datafile 00005 to D:\ORACLE19C\DB\ORADATA\SCTCDB\PDBSEED\SYSTEM01.DBF
channel ch1: reading from backup piece D:\RMAN\SCT_SCTCDB_ LVL0_050O6TG9_1_1
channel ch1: piece handle=D:\RMAN\SCT_SCTCDB_ LVL0_050O6TG9_1_1 tag=DAILYFULL_DB_LVL0_BKP
channel ch1: restored backup piece 1
channel ch1: restore complete, elapsed time: 00:00:15
channel ch1: starting datafile backup set restore
channel ch1: specifying datafile(s) to restore from backup set
channel ch1: restoring datafile 00009 to D:\ORACLE19C\DB\ORADATA\SCTCDB\SCT\SYSTEM01.DBF
channel ch1: reading from backup piece D:\RMAN\SCT_SCTCDB_ LVL0_030O6TG5_1_1
channel ch1: piece handle=D:\RMAN\SCT_SCTCDB_ LVL0_030O6TG5_1_1 tag=DAILYFULL_DB_LVL0_BKP
channel ch1: restored backup piece 1
channel ch1: restore complete, elapsed time: 00:00:15
channel ch1: starting datafile backup set restore
channel ch1: specifying datafile(s) to restore from backup set
channel ch1: restoring datafile 00011 to D:\ORACLE19C\DB\ORADATA\SCTCDB\SCT\UNDOTBS01.DBF
channel ch1: reading from backup piece D:\RMAN\SCT_SCTCDB_ LVL0_080O6TH9_1_1
channel ch1: piece handle=D:\RMAN\SCT_SCTCDB_ LVL0_080O6TH9_1_1 tag=DAILYFULL_DB_LVL0_BKP
channel ch1: restored backup piece 1
channel ch1: restore complete, elapsed time: 00:00:01
channel ch1: starting datafile backup set restore
channel ch1: specifying datafile(s) to restore from backup set
channel ch1: restoring datafile 00010 to D:\ORACLE19C\DB\ORADATA\SCTCDB\SCT\SYSAUX01.DBF
channel ch1: reading from backup piece D:\RMAN\SCT_SCTCDB_ LVL0_060O6TH2_1_1
channel ch1: piece handle=D:\RMAN\SCT_SCTCDB_ LVL0_060O6TH2_1_1 tag=DAILYFULL_DB_LVL0_BKP
channel ch1: restored backup piece 1
channel ch1: restore complete, elapsed time: 00:00:07
channel ch1: starting datafile backup set restore
channel ch1: specifying datafile(s) to restore from backup set
channel ch1: restoring datafile 00007 to D:\ORACLE19C\DB\ORADATA\SCTCDB\USERS01.DBF
channel ch1: reading from backup piece D:\RMAN\SCT_SCTCDB_ LVL0_0B0O6THG_1_1
channel ch1: piece handle=D:\RMAN\SCT_SCTCDB_ LVL0_0B0O6THG_1_1 tag=DAILYFULL_DB_LVL0_BKP
channel ch1: restored backup piece 1
channel ch1: restore complete, elapsed time: 00:00:01
channel ch1: starting datafile backup set restore
channel ch1: specifying datafile(s) to restore from backup set
channel ch1: restoring datafile 00008 to D:\ORACLE19C\DB\ORADATA\SCTCDB\PDBSEED\UNDOTBS01.DBF
channel ch1: reading from backup piece D:\RMAN\SCT_SCTCDB_ LVL0_090O6THA_1_1
channel ch1: piece handle=D:\RMAN\SCT_SCTCDB_ LVL0_090O6THA_1_1 tag=DAILYFULL_DB_LVL0_BKP
channel ch1: restored backup piece 1
channel ch1: restore complete, elapsed time: 00:00:07
channel ch1: starting datafile backup set restore
channel ch1: specifying datafile(s) to restore from backup set
channel ch1: restoring datafile 00012 to D:\ORACLE19C\DB\ORADATA\SCTCDB\SCT\USERS01.DBF
channel ch1: reading from backup piece D:\RMAN\SCT_SCTCDB_ LVL0_0C0O6THH_1_1
channel ch1: piece handle=D:\RMAN\SCT_SCTCDB_ LVL0_0C0O6THH_1_1 tag=DAILYFULL_DB_LVL0_BKP
channel ch1: restored backup piece 1
channel ch1: restore complete, elapsed time: 00:00:02
channel ch1: starting datafile backup set restore
channel ch1: specifying datafile(s) to restore from backup set
channel ch1: restoring datafile 00006 to D:\ORACLE19C\DB\ORADATA\SCTCDB\PDBSEED\SYSAUX01.DBF
channel ch1: reading from backup piece D:\RMAN\SCT_SCTCDB_ LVL0_070O6TH9_1_1
channel ch1: piece handle=D:\RMAN\SCT_SCTCDB_ LVL0_070O6TH9_1_1 tag=DAILYFULL_DB_LVL0_BKP
channel ch1: restored backup piece 1
channel ch1: restore complete, elapsed time: 00:00:07
channel ch1: starting datafile backup set restore
channel ch1: specifying datafile(s) to restore from backup set
channel ch1: restoring datafile 00001 to D:\ORACLE19C\DB\ORADATA\SCTCDB\SYSTEM01.DBF
channel ch1: reading from backup piece D:\RMAN\SCT_SCTCDB_ LVL0_010O6TG5_1_1
channel ch1: piece handle=D:\RMAN\SCT_SCTCDB_ LVL0_010O6TG5_1_1 tag=DAILYFULL_DB_LVL0_BKP
channel ch1: restored backup piece 1
channel ch1: restore complete, elapsed time: 00:00:35
Finished restore at 12-MAR-22



released channel: ch1

With the above restore, I used only one channel, please adjust based on available hardware resources. Once the backup completes, we can recover the database using the available archive logs. For that you need to know what is the latest available sequence number.

RMAN> list backup of archivelog all;

From the image above, you could see that the latest Sequence number for the SCN is 20, however the listing is kind of confusing (remember I am not a DBA, however eventually I will figure out why the listing is messed up). Once the latest sequence number identified, we can try to recover until last sequence + 1

RMAN> run {
2> allocate channel ch1 device type disk;
3> allocate channel ch2 device type disk;
4> allocate channel ch3 device type disk;
5> allocate channel ch4 device type disk;
6> recover database until sequence 21;
7> release channel ch1;
8> release channel ch2;
9> release channel ch3;
10> release channel ch4;
11> }

allocated channel: ch1
channel ch1: SID=252 device type=DISK

allocated channel: ch2
channel ch2: SID=128 device type=DISK

allocated channel: ch3
channel ch3: SID=375 device type=DISK

allocated channel: ch4
channel ch4: SID=499 device type=DISK

Starting recover at 12-MAR-22

starting media recovery

archived log for thread 1 with sequence 19 is already on disk as file D:\ORA\ARCH\SCT0000000019_1096219042_0001.ARC
archived log for thread 1 with sequence 20 is already on disk as file D:\ORA\ARCH\SCT0000000020_1096219042_0001.ARC
archived log file name=D:\ORA\ARCH\SCT0000000019_1096219042_0001.ARC thread=1 sequence=19
archived log file name=D:\ORA\ARCH\SCT0000000020_1096219042_0001.ARC thread=1 sequence=20
media recovery complete, elapsed time: 00:00:01
Finished recover at 12-MAR-22

released channel: ch1

released channel: ch2

released channel: ch3

released channel: ch4

Finally, we can open the database resetting online redo log files

RMAN> alter database open resetlogs;

Database MUST open without creating any issues at this stage. Continue checking for the database vitals.

RMAN on Windows | Backup & Restore 11g R2 database

On By windows7bugsIn NOT a DBA, Oracle database 11gLeave a comment

11g R2? too late to post anything for 11g? Wait, there are thousands of Businesses still using Oracle database 10g. So let us see how RMAN could be simplified on Windows. I hardly see many articles explaining such for Windows platform in general.

Considering you have 11g database already installed & setup for Archive logging, we’ll start by setting up RMAN backup first, then try a restore routine on a different sever.

Like cron jobs on Linux environment, for Windows we will use Task Scheduler for RMAN backups. Here are couple of batch files we will be using for making RMAN backup on Windows.

run
{
ALLOCATE CHANNEL c1 DEVICE TYPE disk;
ALLOCATE CHANNEL c2 DEVICE TYPE disk;
ALLOCATE CHANNEL c3 DEVICE TYPE disk;
ALLOCATE CHANNEL c4 DEVICE TYPE disk;
crosscheck archivelog all;
crosscheck backup;
BACKUP AS COMPRESSED BACKUPSET DATABASE FORMAT 'D:\RMAN_BACKUP\PROD_comp_%d_ lvl0_%U' TAG "dailyfull_db_lvl0_bkp" INCLUDE CURRENT CONTROLFILE;
sql 'ALTER SYSTEM ARCHIVE LOG CURRENT';
BACKUP AS COMPRESSED BACKUPSET ARCHIVELOG ALL FORMAT 'D:\RMAN_BACKUP\archive_%d_lvl0_%U';
DELETE NOPROMPT archivelog all completed before 'sysdate-7';
backup current controlfile format 'D:\RMAN_BACKUP\bkpcontrol_file.ctl_%d_%T'               ;
DELETE NOPROMPT OBSOLETE RECOVERY WINDOW OF 7 DAYS;
RELEASE CHANNEL c1;
RELEASE CHANNEL c2;
RELEASE CHANNEL c3;
RELEASE CHANNEL c4;
}

Save this script as “rman_backup.cmd”. You can save it with any other name ending with .cmd or .bat extension. Make sure you change the name in the next script incase if you choose a name other than “rman_backup.cmd”. Please note, I am taking backup on “D:\RMAN_BACKUP” folder, you should change “D:\RMAN_BACKUP” with the exact location that is used for the backup!

Now, we will create another cmd file that will call the above script.

@echo off
For /f "tokens=2-4 delims=/ " %%a in ('date /t') do (set mydate=%%c-%%a-%%b)
For /f "tokens=1-2 delims=/:" %%a in ('time /t') do (set mytime=%%a%%b)
:: echo %mydate%_%mytime%

set filename="D:\RMAN_BACKUP\logs\%mydate%_%mytime%_rman_backup.log"
:: echo %filename%

rman target / nocatalog cmdfile='D:\scripts\rman_backup.cmd' log='%filename%'

Once again you can save this file with any name, provided the extension is either .cmd or .bat. I saved this cmd file as “callrman.cmd”

Make sure the path ” D:\RMAN_BACKUP\logs” or equivalent exists prior running the scripts.

Now fire up Windows Task scheduler and create a basic job

That’s all. Everyday, at a said time, RMAN backups will be created and kept in the destination folder. We are using 7 days retention policy with the script file, so on the 8th day obsolete backups will be purged. Depending upon the disk space available, you should adjust the retention policy for your backups. Don’t forget to copy those backup pieces to an external medium to insure maximum availability during a crisis.

That completes the simplest form of Oracle RMAN backups on Windows platform.

RMAN Restore

Currently we are discussing about restoring the RMAN backup to same version of Oracle database. If you want to restore RMAN backup to a higher version of Oracle database, it will be an entirely different exercise.

RMAN backups are the fastest, easiest incase if you are doing it right. No hassles like creating tablespaces, schemas and then waiting hours for the import process to complete! The biggest size of the database I ever dealt with is 550GB and a server with 2×4 core processors, 16GB total memory restores the 140GB backup in less than 1.5 hours. I suggest you to start using RMAN backups the soonest possible regardless whether your database is just few megabytes in size.

Today we will see how to restore RMAN backup from Server A to Server B, ie, to a different physical server with a different database name.

It might sound bit unethical to ask you to create a new database using Oracle database creation wizard for this purpose, however this is what we will do for this exercise. This will save us some time figuring out setting up many other parameters for the new database at later stages.

So let us start.

Start DBCA and create a database with a database name that you prefer for the purpose, for eg: “ORCL”

Once the database created, shut it down & delete all the datafiles from the data store. For example, I had all my data files in the folder C:\oracle\oradata\orcl, where orcl is the database name!

Now copy the RMAN backups from Server A to Server B. I’ve copied only one day backups from Server A to Server B and make a note of the exact location where you copied the backup files. In my case, I copied the files to “C:\RMAN”

From an elevated command prompt start SQLPLUS as sysdba & start the database in non mounted mode.

Now, start another elevated command prompt and connect to RMAN in Auxiliary mode, for duplication of the database.

We are all set to restore the RMAN backup to Server B now. However, there are few things we have to insure to make a successful restoration. For example renaming the datafiles. Based on the new datastore location where you are restoring the database files, each data file should be renamed. Please refer the below sample script for the same.

run
{
ALLOCATE AUXILIARY CHANNEL c1 DEVICE TYPE disk;
ALLOCATE AUXILIARY CHANNEL c2 DEVICE TYPE disk;
ALLOCATE AUXILIARY CHANNEL c3 DEVICE TYPE disk;
ALLOCATE AUXILIARY CHANNEL c4 DEVICE TYPE disk;
duplicate database to "ORCL" backup location 'C:\RMAN' nofilenamecheck
db_file_name_convert=('D:\Oracle\oradata\PROD','C:\Oracle\oradata\orcl')
LOGFILE
GROUP 1 (
'C:\Oracle\oradata\orcl\redo01a.log',
'C:\Oracle\oradata\orcl\redo01b.log'
) SIZE 1000M ,
GROUP 2 (
'C:\Oracle\oradata\orcl\redo02a.log',
'C:\Oracle\oradata\orcl\redo02b.log'
) SIZE 1000M ,
GROUP 3 (
'C:\Oracle\oradata\orcl\redo03a.log',
'C:\Oracle\oradata\orcl\redo03b.log'
) SIZE 1000M ,
GROUP 4 (
'C:\Oracle\oradata\orcl\redo04a.log',
'C:\Oracle\oradata\orcl\redo04b.log'
) SIZE 1000M ;
RELEASE CHANNEL c1;
RELEASE CHANNEL c2;
RELEASE CHANNEL c3;
RELEASE CHANNEL c4;
}

As I am duplicating the database to existing one, I don’t have to go through the tedious process of changing the dbid or database name etc. Once the restore is done, all I need is to disable the archive logging and go online! We will see those in next few steps.

Please give attention to “db_file_name_convert“, Server A had the datafiles in the path “D:\oracle\oradata\PROD” and the Server B, we will be restoring the datafile to C:\oracle\ordata\orcl folder. So we must mention those changes here in the script. The same way we need to rename the logfiles and locations also. I hope the above script is self explanatory on that regard.

You may not need all those channel allocation for a small database, please adjust the number of cannels allocated as per your requirements. Give a try with less or more number of channels incase if you want to learn what happens :)

Execute the above script from the RMAN prompt.

If there are no errors, based on the size of the database & hardware resources RMAN will return to the prompt once the activities are finished.

Before doing anything else, we need to insure that the Archive logging is disabled for the newly restored database. Rush to the previous SQLPlus session that you started with nomount. This session is already disconnected, hence reconnect and shutdown immediate.

Now start the database in mount state and disable the archive logging.

Shutdown the database and restart normally.

That’s all, your Server B “orcl” has all the data from Server A “PROD” database now. If this is a production recovery, you can change the database name using “NID” utility, that is supported from Oracle database 9i onwards. Hope this helps few Oracle database beginners out there.

As we have restored full database with archive logs, there is no need to restore logs. Cross verify the database details

Cheers guys!