Resize standby datafile if disk runs out of space on standby site.

PURPOSE: TO AVOID RECREATION OF STANDBY DATABASE IN CASE FILE IS NOT RESIZED ON STANDBY :

ITLINUXDEVBLADE07-PRIMARY

Database is DGTEST9i

[oracle@itlinuxdevblade07 dgtest9i]$ df -h .

Filesystem Size Used Avail Use% Mounted on
/dev/cciss/c0d0p2 25G 21G 2.0G 92% /opt

[oracle@itlinuxdevblade07 dgtest9i]$

2 gb freespace on disk on PRIMARY.

ITLINUXDEVBLADE08

[oracle@itlinuxdevblade08 oradata]$ df -h .

Filesystem Size Used Avail Use% Mounted on
/dev/cciss/c0d0p2 25G 23G 550M 98% /opt

[oracle@itlinuxdevblade08 oradata]$

The corresponding disk on the standby site as in db_file_name_convert has only 550 mb free.

Now create a new tablespace for the testing purpose..

SQL> create tablespace dropme datafile ‘/opt/oracle/oradata/dgtest9i/dropme.dbf’ size 200m;

Tablespace created.

SQL> alter system switch logfile;

System altered.

SQL> /

System altered.

[oracle@itlinuxdevblade07 dgtest9i]$ df -h .
Filesystem Size Used Avail Use% Mounted on
/dev/cciss/c0d0p2 25G 22G 1.8G 93% /opt
[oracle@itlinuxdevblade07 dgtest9i]$

On STANDBY

[oracle@itlinuxdevblade08 dgtest9i]$ df -h .
Filesystem Size Used Avail Use% Mounted on
/dev/cciss/c0d0p2 25G 23G 350M 99% /opt
[oracle@itlinuxdevblade08 dgtest9i]$

Space available reduced from 550 mb to 350 mb

The logs were succesfully applied on STANDBY disk as there was enough space.

Now add a sum of more than the amount available on the standby disk by RESIZING the file on PRIMARY to the tablespace.

PRIMARY site..

SQL> Alter database datafile ‘/opt/oracle/oradata/dgtest9i/dropme.dbf’ RESIZE 1024M;
Database altered.

SQL> SQL>
SQL> alter system switch logfile;
System altered.
Go to STANDBY…

[oracle@itlinuxdevblade08 dgtest9i]$ df -h .
Filesystem Size Used Avail Use% Mounted on
/dev/cciss/c0d0p2 25G 23G 350M 99% /opt
[oracle@itlinuxdevblade08 dgtest9i]$ ls -lrt
total 801848
-rw-r—– 1 oracle dba 209723392 May 10 14:23 dropme.dbf
-rw-r—– 1 oracle dba 7544832 May 10 14:23 control01.ctl
[oracle@itlinuxdevblade08 dgtest9i]$

The file has not been resized….as there was not enough space…

Now check the logs for errors..

On standby …alert log shows media recovery failed as the datafile could not be resized and media recovery stops

The standby database cannot be opened now as the files need recovery..

Media Recovery Log /opt/oracle/dgtest9i/arch/arch59.log
MRP0: Background Media Recovery terminated with error 1237
Wed May 10 14:23:04 2006
Errors in file /opt/oracle/admin/dgtest9i/bdump/dgtest9i_mrp0_10198.trc:
ORA-01237: cannot extend datafile 3
ORA-01110: data file 3: ‘/opt/oracle/dgtest9i/dropme.dbf’
ORA-19502: write error on file “/opt/oracle/dgtest9i/dropme.dbf”, blockno 71297 (blocksize=8192)
ORA-27072: skgfdisp: I/O error
Linux-x86_64 Error: 2: No such file or directory
Additional information: 71296
Some recovered datafiles maybe left media fuzzy
Media recovery may continue but open resetlogs may fail
MRP0: Background Media Recovery process shutdown

STEPS to solve this problem.

1. Shutdown both databases.

2. Create a new db_file_name_convert path as the file in question has to be moved to different disks where space exists.

*.db_file_name_convert=’/opt/oracle/dgtest9i/’,’/opt/oracle/oradata/dgtest9i/’,’/tmp/dgtest9i/’,’/tmp/dgtest9i/’…both machines.

3. Copy dropme.dbf to /tmp/dgtest9i on both machines from original area.

4. Startup mount;..PRIMARY DATABASE

5. alter database rename file ‘/opt/oracle/oradata/dgtest9i/dropmedbf’ to ‘/tmp/dgtest9i/dropme.dbf’;

6. alter database open;

7. alter database create standby controlfile as ‘/tmp/control01.ctl’ ….primary site.

8. ftp the standby controlfile to /opt/oracle/dgtest9i on standby….controlfile area in standby spfile.

9.startup nomount;…….STANDBY site.

SQL> show parameter convert;

NAME TYPE VALUE
———————————— ———– ——————————
db_file_name_convert string /opt/oracle/oradata/dgtest9i/, /opt/oracle/dgtest9i/, /tmp/dgtest9i/, /tmp/dgtest9i/
log_file_name_convert string /opt/oracle/oradata/dgtest9i/, /opt/oracle/dgtest9i/

SQL> alter database mount standby database;

Database altered.

SQL> select name from v$datafile;

/opt/oracle/dgtest9i/system01.dbf
/opt/oracle/dgtest9i/undotbs01.dbf
/tmp/dgtest9i/dropme.dbf

Primary site : Switch a few logfiles….

Now start managed recovery on standby.

SQL>recover managed standby database disconnect;

Alert log below…

Completed: ALTER DATABASE RECOVER managed standby database d
Wed May 10 14:54:30 2006
RFS: Possible network disconnect with primary database
Wed May 10 14:54:38 2006
Fetching gap sequence for thread 1, gap sequence 59-63
Trying FAL server: DGTEST9I_BLADE07
Wed May 10 14:54:41 2006
RFS: Successfully opened standby logfile 4: ‘/opt/oracle/dgtest9i/st01.log’
Wed May 10 14:54:56 2006
Media Recovery Log /opt/oracle/dgtest9i/arch/arch59.log
Media Recovery Log /opt/oracle/dgtest9i/arch/arch60.log
Media Recovery Log /opt/oracle/dgtest9i/arch/arch61.log
Media Recovery Log /opt/oracle/dgtest9i/arch/arch62.log
Media Recovery Log /opt/oracle/dgtest9i/arch/arch63.log
Media Recovery Log /opt/oracle/dgtest9i/arch/arch64.log
Media Recovery Log /opt/oracle/dgtest9i/arch/arch65.log
Media Recovery Waiting for thread 1 seq# 66 (in transit)

Go to /tmp/dgtest9i on STANDBY and make sure file is resized to 1 gb..

[oracle@itlinuxdevblade08 dgtest9i]$ ls -lrt
total 1049612
-rw-r—– 1 oracle dba 1073750016 May 10 14:55 dropme.dbf
[oracle@itlinuxdevblade08 dgtest9i]$ pwd

SUCCESS !