Tuesday, June 22, 2010

Manually Add Datafile

I have a database that runs on RAW devices. Whenever I add a datafile to a tablespace, out UNIX Sys Admins must first create the RAW device on the Primary & Standby Server. The other day they created the RAW device on the standby server but forgot to change the ownership of the RAW device to oracle:dba.
The datafile was successfully added to the primary database, but I found the following error in the alert.log of the standby:

File #323 added to control file as 'UNNAMED00323'.
Originally created as:
'/dev/vx/rdsk/oradg1/casprd_SA_I_CMD10'
Recovery was unable to create the file as:
'/dev/vx/rdsk/oradg1_dg/casprd_SA_I_CMD10'
MRP0: Background Media Recovery terminated with error 1119
Mon Mar 9 23:41:44 2010
Errors in file /opt/apps/oracle/admin/caspp/bdump/caspp_mrp0_15261.trc:
ORA-01119: error in creating database file '/dev/vx/rdsk/oradg1_dg/casprd_SA_I_CMD10'
ORA-27040: file create error, unable to create file
HPUX-ia64 Error: 13: Permission denied
Managed Standby Recovery not using Real Time Apply
Some recovered datafiles maybe left media fuzzy
Media recovery may continue but open resetlogs may fail
Mon Mar 9 23:41:45 2010
Errors in file /opt/apps/oracle/admin/caspp/bdump/caspp_mrp0_15261.trc:
ORA-01119: error in creating database file '/dev/vx/rdsk/oradg1_dg/casprd_SA_I_CMD10'
ORA-27040: file create error, unable to create file
HPUX-ia64 Error: 13: Permission denied

I can only qquery some v$ views when a standby database is mounted. So to fix the problem I did the following:
On production get the relevant details

SQL>select ts# from v$tablespace where name = 'SA_I_CMD';
TS#
----------
17
SQL> col name for a80;
SQL> select FILE#, NAME, bytes/1024/1024 MB from v$datafile where ts#=17;
FILE# NAME MB
---------- ----------------------------------------------------- ----------
34 /dev/vx/rdsk/oradg/casprd_SA_I_CMD03 32500
35 /dev/vx/rdsk/oradg/casprd_SA_I_CMD 31144
36 /dev/vx/rdsk/oradg/casprd_SA_I_CMD02 30712
37 /dev/vx/rdsk/oradg/casprd_SA_I_CMD01 32360
38 /dev/vx/rdsk/oradg/casprd_SA_I_CMD04 32500
39 /dev/vx/rdsk/oradg/casprd_SA_I_CMD05 30000
201 /dev/vx/rdsk/oradg/casprd_SA_I_CMD06 20000
266 /dev/vx/rdsk/oradg1/casprd_SA_I_CMD07 32500
307 /dev/vx/rdsk/oradg1/casprd_SA_I_CMD08 32500
311 /dev/vx/rdsk/oradg1/casprd_SA_I_CMD09 32500
323 /dev/vx/rdsk/oradg1/casprd_SA_I_CMD10 32500

Lets look at the same query on the standby side

SQL> col name for a80
SQL> select FILE#, NAME, bytes/1024/1024 MB from v$datafile where ts#=17;
FILE# NAME MB
---------- ----------------------------------------------- ----------
34 /dev/vx/rdsk/oradg_dg/casprd_SA_I_CMD03 32500
35 /dev/vx/rdsk/oradg_dg/casprd_SA_I_CMD 31144
36 /dev/vx/rdsk/oradg_dg/casprd_SA_I_CMD02 30712
37 /dev/vx/rdsk/oradg_dg/casprd_SA_I_CMD01 32360
38 /dev/vx/rdsk/oradg_dg/casprd_SA_I_CMD04 32500
39 /dev/vx/rdsk/oradg_dg/casprd_SA_I_CMD05 30000
201 /dev/vx/rdsk/oradg_dg/casprd_SA_I_CMD06 20000
266 /dev/vx/rdsk/oradg1_dg/casprd_SA_I_CMD07 32500
307 /dev/vx/rdsk/oradg1_dg/casprd_SA_I_CMD08 32500
311 /dev/vx/rdsk/oradg1_dg/casprd_SA_I_CMD09 32500
323 /opt/apps/oracle/product/10.2.0.4.0/dbs/UNNAMED00323 0

After the UNIX admin corrected the file permissions of the RAW device I had to manually add it as a datafile to the standby database.

SQL>alter system set STANDBY_FILE_MANAGEMENT=MANUAL;
System altered.
SQL>ALTER DATABASE CREATE DATAFILE ‘/opt/apps/oracle/product/10.2.0.4.0/dbs/UNNAMED00323' as '/dev/vx/rdsk/oradg1_dg/casprd_SA_I_CMD10';
Database altered.
SQL>select FILE#, NAME, bytes/1024/1024 MB from v$datafile where ts#=17;
FILE# NAME MB
---------- ----------------------------------------------------- ----------
34 /dev/vx/rdsk/oradg_dg/casprd_SA_I_CMD03 32500
35 /dev/vx/rdsk/oradg_dg/casprd_SA_I_CMD 31144
36 /dev/vx/rdsk/oradg_dg/casprd_SA_I_CMD02 30712
37 /dev/vx/rdsk/oradg_dg/casprd_SA_I_CMD01 32360
38 /dev/vx/rdsk/oradg_dg/casprd_SA_I_CMD04 32500
39 /dev/vx/rdsk/oradg_dg/casprd_SA_I_CMD05 30000
201 /dev/vx/rdsk/oradg_dg/casprd_SA_I_CMD06 20000
266 /dev/vx/rdsk/oradg1_dg/casprd_SA_I_CMD07 32500
307 /dev/vx/rdsk/oradg1_dg/casprd_SA_I_CMD08 32500
311 /dev/vx/rdsk/oradg1_dg/casprd_SA_I_CMD09 32500
323 /dev/vx/rdsk/oradg1_dg/casprd_SA_I_CMD10 32500
11 rows selected.
SQL>alter system set STANDBY_FILE_MANAGEMENT=AUTO;
System altered.
SQL>ALTER DATABASE RECOVER MANAGED STANDBY DATABASE THROUGH ALL SWITCHOVER DISCONNECT USING CURRENT LOGFILE;
Database altered.