Recovery scenarios involving loss of SPFILE

In this session we will see how to recover spfile from rman backup.

Considering we have RMAN backup available that has a copy of the spfile

Case 1) Autobackup in Flash (or now called Fast) Recovery Area

The SPFILE has accidently been deleted and now the database is not starting up after a shutdown has been 

executed.

FRA has been configured.

If FRA has been configured, the backup of the SPFILE is located in the autobackup sub-directory.

For example:

amit/app/oracle/fast_recovery_area/TEST12C/autobackup/2015_11_25/o1_mf_s_896703524_c5bpgjs5_.bkp'

Note that it is stored in OMF format in this example. The ‘s’ in the string identifies the OMF as a backup 

related to the SPFILE

To recover from loss of SPFILE if you are NOT using an RMAN Catalog, we need to do two things first :

1) Set the DBID

2) Issue the STARTUP NOMOUNT FORCE command from an RMAN prompt (note – not SQL*PLUS)

RMAN> SET DBID=1500410030;

executing command: SET DBID

RMAN> startup nomount force;

startup failed: ORA-01078: failure in processing system parameters
LRM-00109: could not open parameter file '/data/amit/app/oracle/product/12.1.0/db_1/dbs/inittest12c.ora'

starting Oracle instance without parameter file for retrieval of spfile

Oracle instance started

Total System Global Area    1073741824 bytes

Fixed Size                     2932632 bytes
Variable Size                293601384 bytes
Database Buffers             771751936 bytes
Redo Buffers                   5455872 bytes

This is a typical error we will face when either restoring the SPFILE or control file from an autobackup.

RMAN> restore spfile from autobackup;

Starting restore at 25-NOV-15
using channel ORA_DISK_1

channel ORA_DISK_1: looking for AUTOBACKUP on day: 20151125
channel ORA_DISK_1: looking for AUTOBACKUP on day: 20151124
channel ORA_DISK_1: looking for AUTOBACKUP on day: 20151123
channel ORA_DISK_1: looking for AUTOBACKUP on day: 20151122
channel ORA_DISK_1: looking for AUTOBACKUP on day: 20151121
channel ORA_DISK_1: looking for AUTOBACKUP on day: 20151120
channel ORA_DISK_1: looking for AUTOBACKUP on day: 20151119
channel ORA_DISK_1: no AUTOBACKUP in 7 days found
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of restore command at 11/25/2015 16:01:20
RMAN-06172: no AUTOBACKUP found or specified handle is not a valid copy or piece

The reason in this case is that since the spfile is missing and we have mounted the instance using a dummy 

spfile, the database needs to know where to look to find the autobackup of the spfile .

So now we include the db_file_recovery_dest and db_name parameters in the RESTORE SPFILE command.

RMAN> restore spfile from '/data/amit/app/oracle/fast_recovery_area/TEST12C/autobackup/2015_11_25/o1_mf_s_896703524_c5bpgjs5_.bkp';

Starting restore at 25-NOV-15
using channel ORA_DISK_1

channel ORA_DISK_1: restoring spfile from AUTOBACKUP /data/amit/app/oracle/fast_recovery_area/TEST12C/autobackup/2015_11_25/o1_mf_s_896703524_c5bpgjs5_.bkp
channel ORA_DISK_1: SPFILE restore from AUTOBACKUP complete
Finished restore at 25-NOV-15

Case 2) Autobackup in non-FRA location – non OMF

RMAN> startup nomount force

startup failed: ORA-01078: failure in processing system parameters
LRM-00109: could not open parameter file '/u01/app/oracle/product/11.2.0/dbhome_1/dbs/inittest12c.ora'

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                104859368 bytes
Database Buffers              46137344 bytes
Redo Buffers                   5439488 bytes

RMAN> set DBID=2968723077

executing command: SET DBID

RMAN>  run {
2> set CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '/u01/backup/%F';
3> restore spfile from autobackup;
4> }

executing command: SET CONTROLFILE AUTOBACKUP FORMAT

Starting restore at 13-JUN-13
using channel ORA_DISK_1

channel ORA_DISK_1: looking for AUTOBACKUP on day: 20151119
channel ORA_DISK_1: AUTOBACKUP found: '/u01/backup/c-2968723077-20151119-01
channel ORA_DISK_1: restoring spfile from AUTOBACKUP '/u01/backup/c-2968723077-20130613-01
channel ORA_DISK_1: SPFILE restore from AUTOBACKUP complete
Finished restore at 13-JUN-13

I hope this article helped you. Your suggestions/feedback are most welcome.

Keep learning... Have a great day!!!

Installing Oracle Multimedia Component on 11g

Installing Oracle Multimedia, which is required for Spatial and/or Locator is quite simple.

All of the following must be carried out while logged in as a SYSDBA user.

1.Pre-Requisite to install Oracle Multimedia component.

Decide which tablespace to use for the Oracle Multimedia users, and which tablespace to use for the Oracle

Spatial/Oracle Multimedia Location Services user. Oracle recommends using the SYSAUX tablespace for all

of these users.

2.Create the users and grant the appropriate privileges, as follows:

Call the script ordinst.sql, with two parameters for the Oracle Multimedia tablespace and the Oracle

Spatial/Oracle Multimedia Location Services tablespace:

On Linux and UNIX:

SQL> @<ORACLE_HOME>/ord/admin/ordinst.sql SYSAUX SYSAUX

On Windows:

SQL> @<ORACLE_HOME>\ord\admin\ordinst.sql SYSAUX SYSAUX

3.Install the Oracle Multimedia types and packages, as follows:

Call the script catim.sql, while you are connected as SYSDBA:

On Linux and UNIX:

SQL> @<ORACLE_HOME>/ord/im/admin/catim.sql

On Windows:

SQL> @<ORACLE_HOME>\ord\im\admin\catim.sql

Now Oracle Multimedia is ready for use.

4.Verifying an Installed Version of Oracle Multimedia

set lines 140

col COMP_NAME format a40

select comp_name, version, status from dba_registry;

COMP_NAME VERSION STATUS

---------------------------------------- ------------------------------ -----------

Oracle Database Vault 12.1.0.2.0 VALID

Oracle Application Express 4.2.5.00.08 VALID

Oracle Label Security 12.1.0.2.0 VALID

Spatial 12.1.0.2.0 VALID

Oracle Multimedia 12.1.0.2.0 VALID

Oracle Text 12.1.0.2.0 VALID

Oracle Workspace Manager 12.1.0.2.0 VALID

Oracle XML Database 12.1.0.2.0 VALID

Oracle Database Catalog Views 12.1.0.2.0 VALID

Oracle Database Packages and Types 12.1.0.2.0 VALID

JServer JAVA Virtual Machine 12.1.0.2.0 VALID

Oracle XDK 12.1.0.2.0 VALID

Oracle Database Java Packages 12.1.0.2.0 VALID

OLAP Analytic Workspace 12.1.0.2.0 VALID

Oracle OLAP API 12.1.0.2.0 VALID

Oracle Real Application Clusters 12.1.0.2.0 OPTION OFF

16 rows selected.

I hope this article helped you. Your suggestions/feedback are most welcome.

Keep learning... Have a great day!!!

How to find the last RMAN backup’s log?

set pagesize 2000

set linesize 2000

select

output

from

GV$RMAN_OUTPUT

where

session_recid =

(

select

session_recid

from

V$RMAN_BACKUP_JOB_DETAILS

where

session_key=(select max(session_key) from v$RMAN_BACKUP_JOB_DETAILS)

);

OUTPUT

----------------------------------------------------------------------------------------------------------------------------------

connected to target database: TEST (DBID=3221280546)

connected to recovery catalog database

allocated channel: ORA_DISK_1

channel ORA_DISK_1: sid=2817 devtype=DISK

validation succeeded for archived log

archive log filename=/data/TEST/arch/TEST1_1243_891780004.arc recid=2465 stamp=893720321

validation succeeded for archived log

archive log filename=/backup/data/TEST/arch/TEST1_1243_891780004.arc recid=2466 stamp=893720321

validation succeeded for archived log

archive log filename=/data/TEST/arch/TEST1_1244_891780004.arc recid=2467 stamp=893720321

validation succeeded for archived log

archive log filename=/backup/data/TEST/arch/TEST1_1244_891780004.arc recid=2468 stamp=893720321

validation succeeded for archived log

archive log filename=/data/TEST/arch/TEST1_1245_891780004.arc recid=2469 stamp=893720326

validation succeeded for archived log

archive log filename=/backup/data/TEST/arch/TEST1_1245_891780004.arc recid=2470 stamp=893720326

validation succeeded for archived log

archive log filename=/data/TEST/arch/TEST1_1246_891780004.arc recid=2471 stamp=893720326

validation succeeded for archived log

archive log filename=/backup/data/TEST/arch/TEST1_1246_891780004.arc recid=2472 stamp=893720326

validation succeeded for archived log

archive log filename=/data/TEST/arch/TEST1_1247_891780004.arc recid=2475 stamp=893720329

validation succeeded for archived log

archive log filename=/backup/data/TEST/arch/TEST1_1247_891780004.arc recid=2476 stamp=893720329

validation succeeded for archived log

archive log filename=/data/TEST/arch/TEST1_1248_891780004.arc recid=2473 stamp=893720329

validation succeeded for archived log

archive log filename=/backup/data/TEST/arch/TEST1_1248_891780004.arc recid=2474 stamp=893720329

archive log filename=/backup/data/TEST/arch/TEST1_1251_891780004.arc recid=2482 stamp=893722157

validation succeeded for archived log

archive log filename=/data/TEST/arch/TEST1_1252_891780004.arc recid=2483 stamp=893723957

validation succeeded for archived log

archive log filename=/backup/data/TEST/arch/TEST1_1252_891780004.arc recid=2484 stamp=893723957

validation succeeded for archived log

archive log filename=/data/TEST/arch/TEST1_1253_891780004.arc recid=2485 stamp=893725758

validation succeeded for archived log

archive log filename=/backup/data/TEST/arch/TEST1_1253_891780004.arc recid=2486 stamp=893725758

validation succeeded for archived log

archive log filename=/data/TEST/arch/TEST1_1254_891780004.arc recid=2487 stamp=893727558

validation succeeded for archived log

archive log filename=/backup/data/TEST/arch/TEST1_1254_891780004.arc recid=2488 stamp=893727558

.

.

.

input archive log thread=1 sequence=1305 recid=2589 stamp=893806765

channel CH1: starting piece 1 at 22-OCT-15

channel CH1: finished piece 1 at 22-OCT-15

piece handle=/backup/data/TEST/rman_backup/arch/FULL_TEST_1_5nqkcq6k_1_1 tag=TAG20151022T234001 comment=NONE

channel CH1: backup set complete, elapsed time: 00:00:01

Finished backup at 22-OCT-15

released channel: CH1

released channel: CH2

released channel: CH3

967 rows selected.

I hope this article helped you. Your suggestions/feedback are most welcome.

Keep learning... Have a great day!!!

oracle script to check the database growth

SET LINESIZE 200

SET PAGESIZE 200

COL "Database Size" FORMAT a13

COL "Used Space" FORMAT a11

COL "Used in %" FORMAT a11

COL "Free in %" FORMAT a11

COL "Database Name" FORMAT a13

COL "Free Space" FORMAT a12

COL "Growth DAY" FORMAT a11

COL "Growth WEEK" FORMAT a12

COL "Growth DAY in %" FORMAT a16

COL "Growth WEEK in %" FORMAT a16

SELECT

(select min(creation_time) from v$datafile) "Create Time",

(select name from v$database) "Database Name",

ROUND((SUM(USED.BYTES) / 1024 / 1024 ),2) || ' MB' "Database Size",

ROUND((SUM(USED.BYTES) / 1024 / 1024 ) - ROUND(FREE.P / 1024 / 1024 ),2) || ' MB' "Used Space",

ROUND(((SUM(USED.BYTES) / 1024 / 1024 ) - (FREE.P / 1024 / 1024 )) / ROUND(SUM(USED.BYTES) / 1024 / 1024 ,2)*100,2) || '% MB' "Used in %",

ROUND((FREE.P / 1024 / 1024 ),2) || ' MB' "Free Space",

ROUND(((SUM(USED.BYTES) / 1024 / 1024 ) - ((SUM(USED.BYTES) / 1024 / 1024 ) - ROUND(FREE.P / 1024 / 1024 )))/ROUND(SUM(USED.BYTES) / 1024 / 1024,2 )*100,2) || '% MB' "Free in %",

ROUND(((SUM(USED.BYTES) / 1024 / 1024 ) - (FREE.P / 1024 / 1024 ))/(select sysdate-min(creation_time) from v$datafile),2) || ' MB' "Growth DAY",

ROUND(((SUM(USED.BYTES) / 1024 / 1024 ) - (FREE.P / 1024 / 1024 ))/(select sysdate-min(creation_time) from v$datafile)/ROUND((SUM(USED.BYTES) / 1024 / 1024 ),2)*100,3) || '% MB' "Growth DAY in %",

ROUND(((SUM(USED.BYTES) / 1024 / 1024 ) - (FREE.P / 1024 / 1024 ))/(select sysdate-min(creation_time) from v$datafile)*7,2) || ' MB' "Growth WEEK",

ROUND((((SUM(USED.BYTES) / 1024 / 1024 ) - (FREE.P / 1024 / 1024 ))/(select sysdate-min(creation_time) from v$datafile)/ROUND((SUM(USED.BYTES) / 1024 / 1024 ),2)*100)*7,3) || '% MB' "Growth WEEK in %"

FROM    (SELECT BYTES FROM V$DATAFILE

UNION ALL

SELECT BYTES FROM V$TEMPFILE

UNION ALL

SELECT BYTES FROM V$LOG) USED,

(SELECT SUM(BYTES) AS P FROM DBA_FREE_SPACE) FREE

GROUP BY FREE.P;

Output:

SHUTDOWN: waiting for active calls to complete.

Issue:

Active call for process 1993 user ‘oraprod’ program ‘oracleprod@prod’

Active call for process 18672 user ‘oraprod’ program ‘oracleprod@prod’

Active call for process 19304 user ‘oraprod’ program ‘oracleprod@prod’

SHUTDOWN: waiting for active calls to complete.

Problem: When SHUTDOWN IMMEDIATE HUNG with above message means PMON process is unable to clear any uncommitted transaction and oracle hung for long time.

Solution:

1. Connect to SYS user with new terminal window

SQL> conn / sysdba
Connected to idle instance.

2. Shutdown database with ABORT option

SQL> shutdown abort;
ORACLE instance shut down.

3. Open database for normal shutdown with RESTRICT option

SQL> startup restrict
ORACLE instance started.

Total System Global Area 167772160 bytes
Fixed Size 788496 bytes
Variable Size 149682160 bytes
Database Buffers 16777216 bytes
Redo Buffers 524288 bytes
Database mounted.
Database opened.

4. Now shutdown database with NORMAL option

SQL> shutdown normal;
Database closed.
Database dismounted.
ORACLE instance shut down.

I hope this article helped you. Your suggestions/feedback are most welcome.

Keep learning... Have a great day!!!

Move a datafile with RMAN

Using RMAN is the best way before 12c to relocate a datafile with a minimum downtime. The only downtime is for switching from the old datafile to the new one and recover it. That means that the datafile size as no impact on the downtime.

Check schmea report before moving datafile.

Here is the situation before moving the datafile

RMAN> report schema
2> ;

using target database control file instead of recovery catalog
Report of database schema for database with db_unique_name XE

List of Permanent Datafiles
===========================
File Size(MB) Tablespace RB segs Datafile Name
---- -------- -------------------- ------- ------------------------
1 360 SYSTEM *** C:\ORACLEXE\APP\ORACLE\ORADATA\XE\SYSTEM.DBF
2 650 SYSAUX *** C:\ORACLEXE\APP\ORACLE\ORADATA\XE\UNDOTBS1.DBF
3 25 UNDOTBS1 *** C:\ORACLEXE\APP\ORACLE\ORADATA\XE\SYSAUX.DBF
4 100 USERS *** C:\ORACLEXE\APP\ORACLE\ORADATA\XE\USERS.DBF


List of Temporary Files
=======================
File Size(MB) Tablespace Maxsize(MB) Tempfile Name
---- -------- -------------------- ----------- --------------------
1 20 TEMP 32767 C:\ORACLEXE\APP\ORACLE\ORADATA\XE\TEMP.DBF


1.Create a copy of datafile to new destination.

The first step is to create a copy of your datafile in the new destination. Here, we will copy the USERS datafile to
C:\ORACLEXE\APP\ORACLE\ORADATA2\XE:


RMAN> copy datafile 4 to 'C:\ORACLEXE\APP\ORACLE\ORADATA2\XE\USERS.DBF';

Starting backup at 14-01-27
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=50 device type=DISK
channel ORA_DISK_1: starting datafile copy
input datafile file number=00004 name=C:\ORACLEXE\APP\ORACLE\ORADATA\XE\USERS.DBF
output file name=C:\ORACLEXE\APP\ORACLE\ORADATA2\XE\USERS.DBF tag=TAG20140127T132133 RECID=1 STAMP=837955296
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:07
Finished backup at 14-01-27

2.Take tablespace offline.

Now I hae a copy of my datafile in the new destination, I have to switch from the old datafile to the new one. Of course this operation cannot be done online, I’ll have to put my tablespace offline, this is the begining of the downtime.

RMAN> SQL 'ALTER TABLESPACE USERS OFFLINE';

sql statement: ALTER TABLESPACE USERS OFFLINE

3.Switch old datafile to new datafile.

Now I can switch from the old datafile to the new one:

RMAN> SWITCH DATAFILE 4 TO COPY;

datafile 4 switched to datafile copy "C:\ORACLEXE\APP\ORACLE\ORADATA2\XE\USERS.DBF"

4.Recover the datafile.

Now we have to recover the datafile:

RMAN> RECOVER DATAFILE 4;

Starting recover at 14-01-27
using channel ORA_DISK_1

starting media recovery
media recovery complete, elapsed time: 00:00:01

Finished recover at 14-01-27

5.Put tablespace back to online.

And we put back the tablespace USERS online:

RMAN> SQL 'ALTER TABLESPACE USERS ONLINE';

sql statement: ALTER TABLESPACE USERS ONLINE

6. Check status now.

Here is the new situation:

Report of database schema for database with db_unique_name XE

RMAN> report schema

List of Permanent Datafiles
===========================
File Size(MB) Tablespace RB segs Datafile Name
---- -------- -------------------- ------- ------------------------
1 360 SYSTEM *** C:\ORACLEXE\APP\ORACLE\ORADATA\XE\SYSTEM.DBF
2 650 SYSAUX *** C:\ORACLEXE\APP\ORACLE\ORADATA\XE\UNDOTBS1.DBF
3 25 UNDOTBS1 *** C:\ORACLEXE\APP\ORACLE\ORADATA\XE\SYSAUX.DBF
4 100 USERS *** C:\ORACLEXE\APP\ORACLE\ORADATA2\XE\USERS.DBF

List of Temporary Files
=======================
File Size(MB) Tablespace Maxsize(MB) Tempfile Name
---- -------- -------------------- ----------- --------------------
1 20 TEMP 32767 C:\ORACLEXE\APP\ORACLE\ORADATA\XE\TEMP.DBF

The old datafile is kept as copy:

7.Check old datafile copy.

RMAN> list copy of datafile 4;

List of Datafile Copies
=======================

Key File S Completion Time Ckp SCN Ckp Time
------- ---- - --------------- ---------- ---------------
2 4 A 14-01-27 379024 14-01-27
Name: C:\ORACLEXE\APP\ORACLE\ORADATA\XE\USERS.DBF

Check 8.Remove old copy if do not need

We can remove it as we no longer need it:

RMAN> DELETE DATAFILECOPY 2;

released channel: ORA_DISK_1
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=50 device type=DISK
List of Datafile Copies
=======================

Key File S Completion Time Ckp SCN Ckp Time
------- ---- - --------------- ---------- ---------------
2 4 A 14-01-27 379024 14-01-27
Name: C:\ORACLEXE\APP\ORACLE\ORADATA\XE\USERS.DBF

Do you really want to delete the above objects (enter YES or NO)? yes
deleted datafile copy
datafile copy file name=C:\ORACLEXE\APP\ORACLE\ORADATA\XE\USERS.DBF RECID=2 STAMP=837955601

Deleted 1 objects


And its done :)




I hope this article helped you. Your suggestions/feedback are most welcome.

Keep learning... Have a great day!!!