How to Backup and Restore Database which is being Configured in ASM

Here in this Post – i have installed and configured – 11gR2 GRID and Database Software (11.2.0.3) and have running TESTDB1 instance.

We have 1 Database TESTDB1 which is there in ASM, we will find out the status and information about – Data Files, Control Files and Redo Log Files before we start our process of backup and restore.

It shows Datafile and Redo Log files location (see we have redo log files in DATA and LOG both disk groups for redundancy):

4

Check for Control File location too and Database Name ( We have 2 Control Files, 1 is in DATA and 1 is in LOG):

5

Check the Database Size, just the sake of it:

6

As we are planning to take Cold Backup, so we will shutdown if its up and start in mount state.

This command will shutdown the database (srvctl stop database -d testdb1). Check the status of the Grid and Database ( crs_stat -t -v ). Database is down now.

1

We will start the database in mount state ( srvctl start database -d testdb1 -o mount ), because we have to take the backup from RMAN.

Check the status again (crs_stat -t -v):

2

Just to verify, connect from SQLPLUS and find out the status of database, it shows MOUNTED:

3

Now connect to ORACLE User and initiate the Backup using RMAN, make sure ORACLE_SID points to TESTDB1.

So we don’t have any prior backup (list backup; and list archivelog all; shows that blank).

Now make sure we have this directory created – /home/oracle/backup.

Now take the backup in the directory /home/oracle/backup using backup as compressed backupset database format ‘/home/oracle/backup/%d_%Y%M%D_%s_%p’;

7

So now we have our backup ready at – /home/oracle/backup:

7

Now remove files from ASM Disk Groups – DATA and LOG:

Now connect to ASM using GRID user :

[grid@ora-linux ~]$ . oraenv
ORACLE_SID = [+ASM] ?
The Oracle base for ORACLE_HOME=/u01/app/11.2.0/grid_11.2.0.3 is /u01/app/grid
[grid@ora-linux ~]$

Remove Datafiles from +DATA DiskGroup, because all datafiles belong to +DATA, we have to remove it and restore it back using backups which we took:

[grid@ora-linux ~]$ asmcmd
ASMCMD>
ASMCMD> ls
CRS/
DATA/
LOG/
ASMCMD>
ASMCMD> cd data
ASMCMD> ls
TESTDB1/
ASMCMD>
ASMCMD> cd testdb1
ASMCMD> ls
CONTROLFILE/
DATAFILE/
ONLINELOG/
PARAMETERFILE/
TEMPFILE/
spfileTESTDB1.ora
ASMCMD>
ASMCMD> cd datafile
ASMCMD> ls
SYSAUX.257.897332263
SYSTEM.256.897332263
UNDOTBS1.258.897332263
USERS.259.897332263
ASMCMD>
ASMCMD> rm *
You may delete multiple files and/or directories.
Are you sure? (y/n) y
ASMCMD> exit
[grid@ora-linux ~]$

Datafiles removed now from +DATA DiskGroup.

Remove control file from +DATA DiskGroup:

[grid@ora-linux ~]$ asmcmd -p
ASMCMD [+] >
ASMCMD [+] > ls
CRS/
DATA/
LOG/
ASMCMD [+] > cd data
ASMCMD [+data] > ls
TESTDB1/
ASMCMD [+data] > cd testdb1
ASMCMD [+data/testdb1] >
ASMCMD [+data/testdb1] > ls
CONTROLFILE/
ONLINELOG/
PARAMETERFILE/
TEMPFILE/
spfileTESTDB1.ora
ASMCMD [+data/testdb1] >
ASMCMD [+data/testdb1] > cd CONTROLFILE/
ASMCMD [+data/testdb1/CONTROLFILE] > ls
Current.260.897332375
ASMCMD [+data/testdb1/CONTROLFILE] >
ASMCMD [+data/testdb1/CONTROLFILE] > rm *
You may delete multiple files and/or directories.
Are you sure? (y/n) y
ASMCMD [+data/testdb1/CONTROLFILE] >
ASMCMD [+data/testdb1/CONTROLFILE] > exit
[grid@ora-linux ~]$

Control files has been removed from +DATA.

Remove Parameter File from +DATA DiskGroup:

[grid@ora-linux ~]$ asmcmd -p
ASMCMD [+] >
ASMCMD [+] > ls
CRS/
DATA/
LOG/
ASMCMD [+] > cd data
ASMCMD [+data] > ls
TESTDB1/
ASMCMD [+data] > cd testdb1
ASMCMD [+data/testdb1] > ls
ONLINELOG/
PARAMETERFILE/
TEMPFILE/
spfileTESTDB1.ora
ASMCMD [+data/testdb1] > cd PARAMETERFILE/
ASMCMD [+data/testdb1/PARAMETERFILE] > ls
spfile.265.897332583
ASMCMD [+data/testdb1/PARAMETERFILE] >
ASMCMD [+data/testdb1/PARAMETERFILE] > rm *
You may delete multiple files and/or directories.
Are you sure? (y/n) y
ASMCMD [+data/testdb1/PARAMETERFILE] > exit
[grid@ora-linux ~]$

Parameter file has been removed from +DATA DiskGroup.

Remove Redo Log Files from +DATA Disk Group:

[grid@ora-linux ~]$ asmcmd -p
ASMCMD [+] > cd +data/tetsdb1
ASMCMD [+data/testdb1] > cd onlinelog
ASMCMD [+data/testdb1/onlinelog] > ls
group_1.261.897332379
group_2.262.897332383
group_3.263.897332385
ASMCMD [+data/testdb1/onlinelog] >
ASMCMD [+data/testdb1/onlinelog] >
ASMCMD [+data/testdb1/onlinelog] > rm *
You may delete multiple files and/or directories.
Are you sure? (y/n) y
ASMCMD [+data/testdb1/onlinelog] >
ASMCMD [+data/testdb1/onlinelog] > exit
[grid@ora-linux ~]$

Redo Log file has been removed.

Remove temp file from +DATA DiskGroup:

[grid@ora-linux ~]$ asmcmd -p
ASMCMD [+] >
ASMCMD [+] > cd +DATA/testdb1/tempfile
ASMCMD [+DATA/testdb1/tempfile] >
ASMCMD [+DATA/testdb1/tempfile] > rm *
You may delete multiple files and/or directories.
Are you sure? (y/n) y
ASMCMD [+DATA/testdb1/tempfile] > cd +DATA
ASMCMD [+DATA] > ls
ASMCMD [+DATA] >
ASMCMD [+DATA] > exit
[grid@ora-linux ~]$

Temp file has been removed form +DATA.

Remove Control Files and Redo Log Files from +LOG DiskGroup too ( Why do we have in +LOG – Because Oracle Creates redundant copy in different Disk Groups – so in our case it went to +DATA and +LOG):

[grid@ora-linux ~]$ asmcmd -p
ASMCMD [+] >
ASMCMD [+] > cd +LOG
ASMCMD [+LOG] > ls
TESTDB1/
ASMCMD [+LOG] >
ASMCMD [+LOG] >
ASMCMD [+LOG] > cd testdb1
ASMCMD [+LOG/testdb1] > ls
CONTROLFILE/
ONLINELOG/
ASMCMD [+LOG/testdb1] > cd controlfile
ASMCMD [+LOG/testdb1/controlfile] > ls
Current.260.897332375
ASMCMD [+LOG/testdb1/controlfile] >
ASMCMD [+LOG/testdb1/controlfile] > rm *
You may delete multiple files and/or directories.
Are you sure? (y/n) y
ASMCMD [+LOG/testdb1/controlfile] >
ASMCMD [+LOG/testdb1/controlfile] > cd +LOG/testdb1
ASMCMD [+LOG/testdb1] > ls
ONLINELOG/
ASMCMD [+LOG/testdb1] > cd ONLINELOG/
ASMCMD [+LOG/testdb1/ONLINELOG] > ls
group_1.261.897332381
group_2.269.897332385
group_3.268.897332387
ASMCMD [+LOG/testdb1/ONLINELOG] > rm *
You may delete multiple files and/or directories.
Are you sure? (y/n) y
ASMCMD [+LOG/testdb1/ONLINELOG] >
ASMCMD [+LOG/testdb1/ONLINELOG] > cd +LOG
ASMCMD [+LOG] > ls
ASMCMD [+LOG] >
ASMCMD [+LOG] > cd +DATA
ASMCMD [+DATA] > ls
ASMCMD [+DATA] >
ASMCMD [+DATA] > exit
[grid@ora-linux ~]$

Control Files and Redo Log Files has been removed from +LOG.

Now we have removed everything from ASM Disk Groups, means there is nothing in our Disk Groups which can make our database up. Let just try starting up the database:

8

So its failed, it did not even get the SPFILE to start up the database.

Now we will restore our Database using RMAN from Backup:

Step 1: We have to start our instance using (startup force nomount) – Why, because we don’t have either pfile or spfile to start our database, so this command will just start the database for the sake of restoring the spfile.

[oracle@ora-linux ~]$ rman target /

Recovery Manager: Release 11.2.0.3.0 – Production on Thu Dec 3 12:38:20 2015

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

connected to target database (not started)

RMAN> startup force nomount;

startup failed: ORA-01078: failure in processing system parameters
ORA-01565: error in identifying file ‘+DATA/TESTDB1/spfileTESTDB1.ora’
ORA-17503: ksfdopn:2 Failed to open file +DATA/TESTDB1/spfileTESTDB1.ora
ORA-15056: additional error message
ORA-17503: ksfdopn:2 Failed to open file +DATA/testdb1/spfiletestdb1.ora
ORA-15173: entry ‘testdb1’ does not exist in directory ‘/’
ORA-06512: at line 4

starting Oracle instance without parameter file for retrieval of spfile
Oracle instance started

Total System Global Area 158662656 bytes

Fixed Size 2226456 bytes
Variable Size 92276456 bytes
Database Buffers 58720256 bytes
Redo Buffers 5439488 bytes

RMAN>

Now restore the spfile:

RMAN> restore spfile from ‘/home/oracle/backup/TESTDB1_20151203_2_1’;

Starting restore at 03-DEC-2015 12:42:59
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=20 device type=DISK

channel ORA_DISK_1: restoring spfile from AUTOBACKUP /home/oracle/backup/TESTDB1_20151203_2_1
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of restore command at 12/03/2015 12:43:00
ORA-19870: error while restoring backup piece /home/oracle/backup/TESTDB1_20151203_2_1
ORA-32015: unable to restore SPFILE
ORA-17502: ksfdcre:4 Failed to create file +DATA/testdb1/spfiletestdb1.ora
ORA-15056: additional error message
ORA-17502: ksfdcre:4 Failed to create file +DATA/testdb1/spfiletestdb1.ora
ORA-15173: entry ‘testdb1’ does not exist in directory ‘/’

RMAN>

It says, testdb1 does not exist in +DATA Disk Group. So now we have to create it before we restore it back.

[grid@ora-linux ~]$ asmcmd -p
ASMCMD [+] >
ASMCMD [+] > ls -lt
State Type Rebal Name
MOUNTED EXTERN N CRS/
MOUNTED EXTERN N DATA/
MOUNTED EXTERN N LOG/
ASMCMD [+] > cd data
ASMCMD [+data] >
ASMCMD [+data] > ls -lt
ASMCMD [+data] >
ASMCMD [+data] > mkdir testdb1
ASMCMD [+data] >
ASMCMD [+data] > ls -lt
Type Redund Striped Time Sys Name
N testdb1/
ASMCMD [+data] > exit
[grid@ora-linux ~]$

So now we have testdb1 directory created under TESTDB1. Try again restoring spfile from the previous command:

RMAN> restore spfile from ‘/home/oracle/backup/TESTDB1_20151203_2_1’;

Starting restore at 03-DEC-2015 12:44:42
using channel ORA_DISK_1

channel ORA_DISK_1: restoring spfile from AUTOBACKUP /home/oracle/backup/TESTDB1_20151203_2_1
channel ORA_DISK_1: SPFILE restore from AUTOBACKUP complete
Finished restore at 03-DEC-2015 12:44:43

RMAN>

Spfile has been restored, now start the database in nomount state:

RMAN> startup force nomount;

Oracle instance started

Total System Global Area 2505338880 bytes

Fixed Size 2230952 bytes
Variable Size 553649496 bytes
Database Buffers 1946157056 bytes
Redo Buffers 3301376 bytes

RMAN>

Now we have our database started from SPFILE, we can restore our Control File Now:

RMAN> RESTORE CONTROLFILE FROM ‘/home/oracle/backup/TESTDB1_20151203_2_1’;

Starting restore at 03-DEC-2015 12:47:55
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=22 device type=DISK

channel ORA_DISK_1: restoring control file
channel ORA_DISK_1: restore complete, elapsed time: 00:00:03
output file name=+DATA/testdb1/controlfile/current.263.897482877
output file name=+LOG/testdb1/controlfile/current.268.897482877
Finished restore at 03-DEC-2015 12:47:59

RMAN>

So our control file has been restored in +LOG and +DATA, locations are being picked up from SPFILE.

Now we have our control files restored, we have to start the database in mount state:

RMAN> startup force mount;

Oracle instance started
database mounted

Total System Global Area 2505338880 bytes

Fixed Size 2230952 bytes
Variable Size 553649496 bytes
Database Buffers 1946157056 bytes
Redo Buffers 3301376 bytes

RMAN>

Now we have our database being mounted, we can restore our database from the backup (There is a catch, we will not be mentioning from where we have to restore because location is already there in control file, so restore database will be sufficient instead of restore database from ‘/home/oracle/backup/TESTDB1_20151203_1_1’):

RMAN> RESTORE DATABASE;

Starting restore at 03-DEC-2015 12:48:46
Starting implicit crosscheck backup at 03-DEC-2015 12:48:46
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=22 device type=DISK
Crosschecked 1 objects
Finished implicit crosscheck backup at 03-DEC-2015 12:48:47

Starting implicit crosscheck copy at 03-DEC-2015 12:48:47
using channel ORA_DISK_1
Finished implicit crosscheck copy at 03-DEC-2015 12:48:47

searching for all files in the recovery area
cataloging files…
no files cataloged

using channel ORA_DISK_1

channel ORA_DISK_1: starting datafile backup set restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_DISK_1: restoring datafile 00001 to +DATA/testdb1/datafile/system.256.897332263
channel ORA_DISK_1: restoring datafile 00002 to +DATA/testdb1/datafile/sysaux.257.897332263
channel ORA_DISK_1: restoring datafile 00003 to +DATA/testdb1/datafile/undotbs1.258.897332263
channel ORA_DISK_1: restoring datafile 00004 to +DATA/testdb1/datafile/users.259.897332263
channel ORA_DISK_1: reading from backup piece /home/oracle/backup/TESTDB1_20151203_1_1
channel ORA_DISK_1: piece handle=/home/oracle/backup/TESTDB1_20151203_1_1 tag=TAG20151203T120046
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:55
Finished restore at 03-DEC-2015 12:49:42

RMAN>

Now start the database with open resetlogs to start the logs from scratch, previous redo log files are of no use, anyways we have removed them already from ASM.

RMAN> ALTER DATABASE OPEN RESETLOGS;

database opened

RMAN>

So database is open now. Check the status and Data Files, Control Files and Redo Log files location etc..

[oracle@ora-linux backup]$ . oraenv
ORACLE_SID = [TESTDB1] ?
The Oracle base for ORACLE_HOME=/u01/app/oracle/product/11.2.0.3/db_1 is /u01/app/oracle
[oracle@ora-linux backup]$
[oracle@ora-linux backup]$
[oracle@ora-linux backup]$
[oracle@ora-linux backup]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.3.0 Production on Thu Dec 3 16:24:19 2015

Copyright (c) 1982, 2011, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 – 64bit Production
With the Partitioning, Automatic Storage Management, OLAP, Data Mining
and Real Application Testing options

SQL>
SQL> select file_name from dba_data_files;

FILE_NAME
————————————————————-
+DATA/testdb1/datafile/users.260.897482929
+DATA/testdb1/datafile/undotbs1.265.897482927
+DATA/testdb1/datafile/sysaux.261.897482927
+DATA/testdb1/datafile/system.262.897482927

SQL>

SQL> select name from v$tempfile;

NAME
————————————————————-
+DATA/testdb1/tempfile/temp.256.897483027

SQL>

SQL> select file_name from dba_temp_files;

FILE_NAME
————————————————————-
+DATA/testdb1/tempfile/temp.256.897483027

SQL>

SQL> select member from v$logfile;

MEMBER
————————————————————-
+DATA/testdb1/onlinelog/group_3.257.897483023
+LOG/testdb1/onlinelog/group_3.260.897483023
+DATA/testdb1/onlinelog/group_2.258.897483019
+LOG/testdb1/onlinelog/group_2.261.897483021
+DATA/testdb1/onlinelog/group_1.259.897483017
+LOG/testdb1/onlinelog/group_1.269.897483017

6 rows selected.

SQL>

SQL> show parameter spfile;

NAME TYPE VALUE
——————- ———– ——————————
spfile                                    string               +DATA/testdb1/spfiletestdb1.ora
SQL>

So we are done with backup and restore of Database being configured in ASM.

Let me know if there is any question.

Hope it helps..

 

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s