- Leverage Flash Recovery Area for archive log management
- Rename Data File on the primary database server
- Rename Data File on the standby database server
- Manage TEMP files on the standby database server
1. Leverage Flash Recovery Area for archive log management
Primary Database in Oracle Data Guard continuously ships redo vectors to the physical standby database to keep it in synch with the primary. During archive log switch operation, Oracle creates a new archive log file on the standby server. Use of Flash recovery area is definitely advantageous as you don't need to manage archive logs on the physical standby. We use flash recovery area to store and manage archive logs on physical standby. With Oracle flash recovery area, Oracle delete archive logs after applying them to standby database. Works pretty well. And simple setup as shown below:
SQL> connect / as sysdba
Connected.
SQL> alter system set DB_RECOVERY_FILE_DEST='e:\oraflash' ;
alter system set DB_RECOVERY_FILE_DEST='e:\oraflash'
*
ERROR at line 1:
ORA-02097: parameter cannot be modified because specified value is invalid
ORA-19802: cannot use DB_RECOVERY_FILE_DEST without DB_RECOVERY_FILE_DEST_SIZE
SQL> alter system set DB_RECOVERY_FILE_DEST_SIZE=5g ;
System altered.
SQL> alter system set DB_RECOVERY_FILE_DEST='e:\oraflash' ;
System altered.
SQL> alter system set LOG_ARCHIVE_DEST_1='LOCATION=USE_DB_RECOVERY_FILE_DEST';
System altered.
Now configure archive log deletion policy.
RMAN> CONFIGURE ARCHIVELOG DELETION POLICY TO APPLIED ON STANDBY;
2. Rename Data files on Primary DB
Oracle documentation explains the procedure to rename a data file on the primary database participating in a data guard managed physical standby configuration. Per Oracle documentation, you rename the data file on primary server and then rename it on the standby database server as well.
A test, described below, shows that you don't need to repeat the rename step on physical standby server.
Step - 1
Check the name of the data file on physical standby database:
SQL> select name, bytes from v$datafile where file# = 4 ;
NAME BYTES
-------------------------------------------------- ----------
E:\ORADATA\DGS\USERS11.ORA 52428800
Step 2
Rename the data file on the primary database server
SQL> select name, bytes from v$datafile where file# = 4 ;
NAME BYTES
-------------------------------------------------- ----------
E:\ORADATA\DGP\USERS11.ORA 52428800
1 rows selected.
SQL> ALTER TABLESPACE USERS OFFLINE ;
Tablespace altered.
REM Rename USERS11.ORA to USERS21.ora on the primary database server at the OS level.
SQL> ALTER TABLESPACE USERS
2 RENAME DATAFILE 'E:\ORADATA\DGP\USERS11.ORA'
3 TO 'E:\ORADATA\DGP\USERS21.ORA' ;
Tablespace altered.
SQL> ALTER TABLESPACE USERS ONLINE ;
Tablespace altered.
REM Resize the file on primary to ensure propagation of resize operation
SQL> ALTER DATABASE DATAFILE 'E:\ORADATA\DGP\USERS21.ORA' RESIZE 250M ;
Database altered.
SQL> select name, bytes from v$datafile where file# = 4 ;
NAME BYTES
-------------------------------------------------- ----------
E:\ORADATA\DGP\USERS21.ORA 262144000
1 rows selected.
Step - 3
Verify the name of the data file on physical standby database after renaming it on the primary
SQL> select name, bytes from v$datafile where file# = 4 ;
NAME BYTES
-------------------------------------------------- ----------
E:\ORADATA\DGS\USERS11.ORA 262144000
1 rows selected.
3. Rename Data File on Physical Standby
We recently ran out of disk space on the physical standby database server. In ideal world, you would like to have your physical standby database server mirror the primary database server in terms of processors, memory and disk space, but that wasn't the case in our environment. On primary database server, Oracle data files were on E drive which was 500GB in size. We also had E drive on the physical standby database server, but it was smaller in size.
E drive on the physical standby database server was completely full, so we wanted to move few files from E drive to F drive on the physical standby database server to make some space on the drive.
Like a good "DBA", before renaming a data file on standby, we performed a test in a test environment as shown below:
Step 1
Rename Data File on Physical Standby Database Server
SQL> alter database recover managed standby database cancel ;
Database altered.
After cancelling managed recovery on the standby server, we attempted to rename the database at the OS level, but it failed with "File locked" error message. So let's shutdown the database to release locks on data files.
SQL> shutdown immediate ;
ORA-01109: database not open
Database dismounted.
ORACLE instance shut down.
After shutting down the database, we successfully renamed the file at the OS level. A USERS01.ORA file was renamed to USERS31.ORA at the OS level.
SQL> STARTUP MOUNT ;
ORACLE instance started.
Total System Global Area 402653184 bytes
Fixed Size 1296988 bytes
Variable Size 117441956 bytes
Database Buffers 281018368 bytes
Redo Buffers 2895872 bytes
Database mounted.
SQL> ALTER DATABASE
2 RENAME FILE 'E:\ORADATA\DGS\USERS01.ORA'
3* TO 'E:\ORADATA\DGS\USERS31.ORA' ;
ALTER DATABASE
*
ERROR at line 1:
ORA-01511: error in renaming log/data files
ORA-01275: Operation RENAME is not allowed if standby file management is automatic.
SQL> ALTER SYSTEM SET STANDBY_FILE_MANAGEMENT=MANUAL;
System altered.
SQL>
SQL> ALTER DATABASE
2 RENAME FILE 'E:\ORADATA\DGS\USERS01.ORA'
3 TO 'E:\ORADATA\DGS\USERS31.ORA' ;
Database altered.
SQL> ALTER SYSTEM SET STANDBY_FILE_MANAGEMENT=AUTO;
System altered.
Step 2
Resize the corresponding file on the primary database server. The purpose of this step is to verify that file resize operation on this file is replicated to the standby database server.
SQL> ALTER DATABASE DATAFILE 'E:\ORADATA\DGP\USERS01.ORA' RESIZE 300M ;
Database altered.
SQL> ALTER SYSTEM SWITCH LOGFILE ;
System altered.
Step 3
Verify the file size on the physical standby database server. In addition, open the physical standby database in read only mode to verify.
SQL> select name, bytes from v$datafile where file# = 4 ;
NAME BYTES
-------------------------------------------------- ----------
E:\ORADATA\DGS\USERS31.ORA 314572800
1 rows selected.
SQL> alter database recover managed standby database cancel ;
Database altered.
SQL> alter database open ;
Database altered.
To summarize, renaming the data file on physical standby works.
4. Rename TEMP files on Standby
In data guard managed physical standby environment, Oracle manages TEMP files differently. During the initial standby database creation, RMAN creates TEMP files on the standby database server. From this point onwards, TEMP file management is on standby independent of the primary database. You should be able to perform the following on standby server independently:
- Resize TEMP files on standby
- Move TEMP files on standby
- Create additional TEMP files on standby
If you are using the physical standby database for reporting purposes and if you need additional TEMP space on standby, you should be able to perform these tasks independent of the primary database
No comments:
Post a Comment