Resizing standby datafile if disk runs out of space on standby server:-
Primary db: Prim
Standby db: Stand
On primary database server:-
[root@server1 ~]# df -h
Filesystem Size Used Avail Use% Mounted on
/dev/sda2 8.7G 6.4G 1.9G 78% /
Here on primary server i have 1.9G as freespace.
On standby database server:-
[oracle@server2 ~]$ df -h
Filesystem Size Used Avail Use% Mounted on
/dev/sda2 8.7G 7.8G 428M 95% /
On standby server we have 428 mb left in /
Now we will create a tablespace on primary server.
[oracle@server1 ~]$ sqlplus / as sysdba
SQL> create tablespace somtbs datafile '/home/oracle/dropme.dbf' size 200M;
Tablespace created.
SQL> alter system switch logfile;
System altered.
At primary :-
[root@server1 ~]# df -h
Filesystem Size Used Avail Use% Mounted on
/dev/sda2 8.7G 7.0G 1.3G 85% /
At standby:-
Filesystem Size Used Avail Use% Mounted on
/dev/sda2 8.7G 8.0G 228M 98% /
Now i'm gonna resize the datafile on primary database to 1024mb which is not actually available in standby database and check the result.
At primary server:-
QL> alter database datafile '/home/oracle/dropme.dbf' resize 1024M;
Database altered.
SQL> alter system switch logfile;
System altered.
After resizing the the datafile
At primary :-
[root@server1 ~]# df -h
Filesystem Size Used Avail Use% Mounted on
/dev/sda2 8.7G 8.0G 700M 92% /
At standby server:-
Filesystem Size Used Avail Use% Mounted on
/dev/sda2 8.7G 8.0G 228M 98% /
[oracle@server2 ~]$ ls -ltrh
total 301M
-rw-r----- 1 oracle oinstall 201M Dec 30 15:26 dropme.dbf
We see the datafile has not been resized at standby database due to no required space available.
Let's check the alert log for reference.
On standby database:-
in alertlog on standby server:-
Tue Dec 30 15:26:38 2014
MRP0: Background Media Recovery terminated with error 1237
Errors in file /u01/app/oracle/diag/rdbms/stand/stand/trace/stand_mrp0_2720.trc:
ORA-01237: cannot extend datafile 6
ORA-01110: data file 6: '/home/oracle/dropme.dbf'
ORA-19502: write error on file "/home/oracle/dropme.dbf", block number 54656 (block size=8192)
ORA-27072: File I/O error
Linux-x86_64 Error: 25: Inappropriate ioctl for device
Additional information: 4
Additional information: 54656
Additional information: 155648
Managed Standby Recovery not using Real Time Apply
Recovery interrupted!
Recovered data files to a consistent state at change 1043748
Errors in file /u01/app/oracle/diag/rdbms/stand/stand/trace/stand_mrp0_2720.trc:
ORA-01237: cannot extend datafile 6
ORA-01110: data file 6: '/home/oracle/dropme.dbf'
ORA-19502: write error on file "/home/oracle/dropme.dbf", block number 54656 (block size=8192)
ORA-27072: File I/O error
Linux-x86_64 Error: 25: Inappropriate ioctl for device
The 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.
Solution of this problem as follows:-
Step 1. Shutdown both databases.
SQL> shut immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
Step 2. Add a new db_file_name_convert path in pfile where we have enough space to relocate the datafile.
[oracle@server1 ~]$ cd $ORACLE_HOME/dbs
[oracle@server1 dbs]$ ll
total 32
-rw-r--r--. 1 oracle oinstall 1423 Dec 27 23:44 initprim.ora
-rw-r--r--. 1 oracle oinstall 1427 Dec 27 23:59 initstand.ora
-rw-r-----. 1 oracle oinstall 3584 Dec 28 00:49 spfileprim.ora
[oracle@server1 dbs]$ vi initprim.ora
locate the following line in pfile and make the following changes
*.log_file_name_convert='/u01/app/oracle/oradata/stand/','/u01/app/oracle/oradata/prim/','/home/oracle/','/u01/'
:wq (save & exit)
Do the same changes in initstand.ora pfile which is in standby database server.
Step 3. Copy dropme.dbf to /u01/ on both server from original area.As this will be the location where i will keep the the new resized datafile .
[oracle@server1 ~]$ cp dropme.dbf /u01/
Now transfer the datafile into standby database
[oracle@server1 u01]$ scp dropme.dbf oracle@server2:/u01/
oracle@server2's password:
dropme.dbf 100% 1024MB 20.5MB/s 00:50
Step 4. Mount the primary database from newly created pfile
[oracle@server1 u01]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.1.0 Production on Sun Dec 28 01:28:51 2014
Copyright (c) 1982, 2009, Oracle. All rights reserved.
Connected to an idle instance.
SQL> startup mount pfile='/u01/app/oracle/product/11.2.0/db_1/dbs/initprim.ora';
ORACLE instance started.
Total System Global Area 413372416 bytes
Fixed Size 2213896 bytes
Variable Size 310380536 bytes
Database Buffers 96468992 bytes
Redo Buffers 4308992 bytes
Database mounted.
Step 5. Move the datafile to new location
SQL> alter database rename file '/home/oracle/dropme.dbf' to '/u01/dropme.dbf';
Database altered.
Step 6.Create spfile from the pfile.
SQL> create spfile from pfile='/u01/app/oracle/product/11.2.0/db_1/dbs/initprim.ora';
SQL> shut immediate;
ORA-01109: database not open
Database dismounted.
ORACLE instance shut down.
SQL> startup mount
ORACLE instance started.
Total System Global Area 413372416 bytes
Fixed Size 2213896 bytes
Variable Size 310380536 bytes
Database Buffers 96468992 bytes
Redo Buffers 4308992 bytes
Database mounted.
Step 7.Create standby controlfile
SQL> alter database create standby controlfile as '/u01/app/oracle/oradata/prim/stand.ctl';
SQL> alter database open;
Step 8.Now copy the standby controlfile into standby server
[oracle@server1 u01]$ cd /u01/app/oracle/oradata/prim/
[oracle@server1 prim]$ scp stand.ctl oracle@server2:/u01/app/oracle/oradata/stand/
oracle@server2's password:
stand.ctl 100% 9808KB 9.6MB/s 00:00
Step 9. Startup standby database into mount stage using the new pfile.
SQL> startup mount pfile ='/u01/app/oracle/product/11.2.0/db_1/dbs/initstand.ora';
SQL> create spfile from pfile='/u01/app/oracle/product/11.2.0/db_1/dbs/initstand.ora';
SQL> shut immediate;
SQL> startup nomount;
SQL> alter database mount standby database;
SQL> select name from v$datafile;
NAME
--------------------------------------------------------------------------------
/u01/app/oracle/oradata/stand/system01.dbf
/u01/app/oracle/oradata/stand/sysaux01.dbf
/u01/app/oracle/oradata/stand/undotbs01.dbf
/u01/app/oracle/oradata/stand/users01.dbf
/u01/app/oracle/oradata/stand/example01.dbf
/u01/dropme.dbf
Primary server : Switch a few logfiles….
Now start managed recovery on standby.
SQL>recover managed standby database disconnect;
Alert log below…
Media Recovery Waiting for thread 1 sequence 32
Fetching gap sequence in thread 1, gap sequence 32-34
Tue Dec 30 19:59:44 2014
RFS[4]: Assigned to RFS process 4848
RFS[5]: Assigned to RFS process 4850
RFS[5]: Identified database type as 'physical standby': Client is ARCH pid 3781
RFS[5]: Opened log for thread 1 sequence 32 dbid -164957407 branch 837708259
Archived Log entry 23 added for thread 1 sequence 33 rlc 837708259 ID 0xf62acd21 dest 2:
Archived Log entry 24 added for thread 1 sequence 32 rlc 837708259 ID 0xf62acd21 dest 2:
Completed: ALTER DATABASE RECOVER managed standby database disconnect
Tue Dec 30 19:59:54 2014
Media Recovery Log /u01/app/oracle/flash_recovery_area/1_32_837708259.dbf
Media Recovery Log /u01/app/oracle/flash_recovery_area/1_33_837708259.dbf
Media Recovery Log /u01/app/oracle/flash_recovery_area/1_34_837708259.dbf
Media Recovery Log /u01/app/oracle/flash_recovery_area/1_35_837708259.dbf
Media Recovery Log /u01/app/oracle/flash_recovery_area/1_36_837708259.dbf
Media Recovery Log /u01/app/oracle/flash_recovery_area/1_37_837708259.dbf
Media Recovery Log /u01/app/oracle/flash_recovery_area/1_38_837708259.dbf
Media Recovery Log /u01/app/oracle/flash_recovery_area/1_39_837708259.dbf
Media Recovery Log /u01/app/oracle/flash_recovery_area/1_40_837708259.dbf
Done....
Primary db: Prim
Standby db: Stand
On primary database server:-
[root@server1 ~]# df -h
Filesystem Size Used Avail Use% Mounted on
/dev/sda2 8.7G 6.4G 1.9G 78% /
Here on primary server i have 1.9G as freespace.
On standby database server:-
[oracle@server2 ~]$ df -h
Filesystem Size Used Avail Use% Mounted on
/dev/sda2 8.7G 7.8G 428M 95% /
On standby server we have 428 mb left in /
Now we will create a tablespace on primary server.
[oracle@server1 ~]$ sqlplus / as sysdba
SQL> create tablespace somtbs datafile '/home/oracle/dropme.dbf' size 200M;
Tablespace created.
SQL> alter system switch logfile;
System altered.
At primary :-
[root@server1 ~]# df -h
Filesystem Size Used Avail Use% Mounted on
/dev/sda2 8.7G 7.0G 1.3G 85% /
At standby:-
Filesystem Size Used Avail Use% Mounted on
/dev/sda2 8.7G 8.0G 228M 98% /
Now i'm gonna resize the datafile on primary database to 1024mb which is not actually available in standby database and check the result.
At primary server:-
QL> alter database datafile '/home/oracle/dropme.dbf' resize 1024M;
Database altered.
SQL> alter system switch logfile;
System altered.
After resizing the the datafile
At primary :-
[root@server1 ~]# df -h
Filesystem Size Used Avail Use% Mounted on
/dev/sda2 8.7G 8.0G 700M 92% /
At standby server:-
Filesystem Size Used Avail Use% Mounted on
/dev/sda2 8.7G 8.0G 228M 98% /
[oracle@server2 ~]$ ls -ltrh
total 301M
-rw-r----- 1 oracle oinstall 201M Dec 30 15:26 dropme.dbf
We see the datafile has not been resized at standby database due to no required space available.
Let's check the alert log for reference.
On standby database:-
in alertlog on standby server:-
Tue Dec 30 15:26:38 2014
MRP0: Background Media Recovery terminated with error 1237
Errors in file /u01/app/oracle/diag/rdbms/stand/stand/trace/stand_mrp0_2720.trc:
ORA-01237: cannot extend datafile 6
ORA-01110: data file 6: '/home/oracle/dropme.dbf'
ORA-19502: write error on file "/home/oracle/dropme.dbf", block number 54656 (block size=8192)
ORA-27072: File I/O error
Linux-x86_64 Error: 25: Inappropriate ioctl for device
Additional information: 4
Additional information: 54656
Additional information: 155648
Managed Standby Recovery not using Real Time Apply
Recovery interrupted!
Recovered data files to a consistent state at change 1043748
Errors in file /u01/app/oracle/diag/rdbms/stand/stand/trace/stand_mrp0_2720.trc:
ORA-01237: cannot extend datafile 6
ORA-01110: data file 6: '/home/oracle/dropme.dbf'
ORA-19502: write error on file "/home/oracle/dropme.dbf", block number 54656 (block size=8192)
ORA-27072: File I/O error
Linux-x86_64 Error: 25: Inappropriate ioctl for device
The 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.
Solution of this problem as follows:-
Step 1. Shutdown both databases.
SQL> shut immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
Step 2. Add a new db_file_name_convert path in pfile where we have enough space to relocate the datafile.
[oracle@server1 ~]$ cd $ORACLE_HOME/dbs
[oracle@server1 dbs]$ ll
total 32
-rw-r--r--. 1 oracle oinstall 1423 Dec 27 23:44 initprim.ora
-rw-r--r--. 1 oracle oinstall 1427 Dec 27 23:59 initstand.ora
-rw-r-----. 1 oracle oinstall 3584 Dec 28 00:49 spfileprim.ora
[oracle@server1 dbs]$ vi initprim.ora
locate the following line in pfile and make the following changes
*.log_file_name_convert='/u01/app/oracle/oradata/stand/','/u01/app/oracle/oradata/prim/','/home/oracle/','/u01/'
:wq (save & exit)
Do the same changes in initstand.ora pfile which is in standby database server.
Step 3. Copy dropme.dbf to /u01/ on both server from original area.As this will be the location where i will keep the the new resized datafile .
[oracle@server1 ~]$ cp dropme.dbf /u01/
Now transfer the datafile into standby database
[oracle@server1 u01]$ scp dropme.dbf oracle@server2:/u01/
oracle@server2's password:
dropme.dbf 100% 1024MB 20.5MB/s 00:50
Step 4. Mount the primary database from newly created pfile
[oracle@server1 u01]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.1.0 Production on Sun Dec 28 01:28:51 2014
Copyright (c) 1982, 2009, Oracle. All rights reserved.
Connected to an idle instance.
SQL> startup mount pfile='/u01/app/oracle/product/11.2.0/db_1/dbs/initprim.ora';
ORACLE instance started.
Total System Global Area 413372416 bytes
Fixed Size 2213896 bytes
Variable Size 310380536 bytes
Database Buffers 96468992 bytes
Redo Buffers 4308992 bytes
Database mounted.
Step 5. Move the datafile to new location
SQL> alter database rename file '/home/oracle/dropme.dbf' to '/u01/dropme.dbf';
Database altered.
Step 6.Create spfile from the pfile.
SQL> create spfile from pfile='/u01/app/oracle/product/11.2.0/db_1/dbs/initprim.ora';
SQL> shut immediate;
ORA-01109: database not open
Database dismounted.
ORACLE instance shut down.
SQL> startup mount
ORACLE instance started.
Total System Global Area 413372416 bytes
Fixed Size 2213896 bytes
Variable Size 310380536 bytes
Database Buffers 96468992 bytes
Redo Buffers 4308992 bytes
Database mounted.
Step 7.Create standby controlfile
SQL> alter database create standby controlfile as '/u01/app/oracle/oradata/prim/stand.ctl';
SQL> alter database open;
Step 8.Now copy the standby controlfile into standby server
[oracle@server1 u01]$ cd /u01/app/oracle/oradata/prim/
[oracle@server1 prim]$ scp stand.ctl oracle@server2:/u01/app/oracle/oradata/stand/
oracle@server2's password:
stand.ctl 100% 9808KB 9.6MB/s 00:00
Step 9. Startup standby database into mount stage using the new pfile.
SQL> startup mount pfile ='/u01/app/oracle/product/11.2.0/db_1/dbs/initstand.ora';
SQL> create spfile from pfile='/u01/app/oracle/product/11.2.0/db_1/dbs/initstand.ora';
SQL> shut immediate;
SQL> startup nomount;
SQL> alter database mount standby database;
SQL> select name from v$datafile;
NAME
--------------------------------------------------------------------------------
/u01/app/oracle/oradata/stand/system01.dbf
/u01/app/oracle/oradata/stand/sysaux01.dbf
/u01/app/oracle/oradata/stand/undotbs01.dbf
/u01/app/oracle/oradata/stand/users01.dbf
/u01/app/oracle/oradata/stand/example01.dbf
/u01/dropme.dbf
Primary server : Switch a few logfiles….
Now start managed recovery on standby.
SQL>recover managed standby database disconnect;
Alert log below…
Media Recovery Waiting for thread 1 sequence 32
Fetching gap sequence in thread 1, gap sequence 32-34
Tue Dec 30 19:59:44 2014
RFS[4]: Assigned to RFS process 4848
RFS[5]: Assigned to RFS process 4850
RFS[5]: Identified database type as 'physical standby': Client is ARCH pid 3781
RFS[5]: Opened log for thread 1 sequence 32 dbid -164957407 branch 837708259
Archived Log entry 23 added for thread 1 sequence 33 rlc 837708259 ID 0xf62acd21 dest 2:
Archived Log entry 24 added for thread 1 sequence 32 rlc 837708259 ID 0xf62acd21 dest 2:
Completed: ALTER DATABASE RECOVER managed standby database disconnect
Tue Dec 30 19:59:54 2014
Media Recovery Log /u01/app/oracle/flash_recovery_area/1_32_837708259.dbf
Media Recovery Log /u01/app/oracle/flash_recovery_area/1_33_837708259.dbf
Media Recovery Log /u01/app/oracle/flash_recovery_area/1_34_837708259.dbf
Media Recovery Log /u01/app/oracle/flash_recovery_area/1_35_837708259.dbf
Media Recovery Log /u01/app/oracle/flash_recovery_area/1_36_837708259.dbf
Media Recovery Log /u01/app/oracle/flash_recovery_area/1_37_837708259.dbf
Media Recovery Log /u01/app/oracle/flash_recovery_area/1_38_837708259.dbf
Media Recovery Log /u01/app/oracle/flash_recovery_area/1_39_837708259.dbf
Media Recovery Log /u01/app/oracle/flash_recovery_area/1_40_837708259.dbf
Done....
No comments:
Post a Comment