Wednesday, 8 August 2012

Adding,Deleting or renaming tablespace or database on primary what affect on standby database

8.1 Adding a Datafile or Creating a Tablespace
The initialization parameter, STANDBY_FILE_MANAGEMENT, allows you to control whether or not adding a datafile to the primary database is automatically propagated to the standby database, as follows:
  • If you set the STANDBY_FILE_MANAGEMENT initialization parameter in the standby database server parameter file to AUTO, any new datafiles created on the primary database are automatically created on the standby database as well.
  • If you do not specify the STANDBY_FILE_MANAGEMENT initialization parameter or if you set it to MANUAL, then you must manually copy the new datafile to the standby database when you add a datafile to the primary database.
Note that if you copy an existing datafile from another database to the primary database, then you must also copy the new datafile to the standby database and re-create the standby control file, regardless of the setting of STANDBY_FILE_MANAGEMENT initialization parameter.
The following sections provide examples of adding a datafile to the primary and standby databases when the STANDBY_FILE_MANAGEMENT initialization parameter is set to AUTO and MANUAL, respectively.
8.2 Adding a Tablespace and a Datafile When STANDBY_FILE_MANAGEMENT Is Set to AUTO
The following example shows the steps required to add a new datafile to the primary and standby databases when the STANDBY_FILE_MANAGEMENT initialization parameter is set to AUTO.
  1. Add a new tablespace to the primary database:
SQL> CREATE TABLESPACE new_ts DATAFILE 't_db2.dbf'
  2> SIZE 1m AUTOEXTEND ON MAXSIZE UNLIMITED;
  1. Archive the current redo log so it will get copied to the standby database:
SQL> ALTER SYSTEM ARCHIVE LOG CURRENT;
  1. Verify that the new datafile was added to the primary database:
SQL> SELECT NAME FROM V$DATAFILE;
----------------------------------------------------------------------
/disk1/oracle/dbs/t_db1.dbf
/disk1/oracle/dbs/t_db2.dbf
  1. Verify that the new datafile was added to the standby database:
SQL> SELECT NAME FROM V$DATAFILE;
----------------------------------------------------------------------
/disk1/oracle/dbs/s2t_db1.dbf
/disk1/oracle/dbs/s2t_db2.dbf
8.3 Adding a Tablespace and a Datafile When STANDBY_FILE_MANAGEMENT Is Set to MANUAL
The following example shows the steps required to add a new datafile to the primary and standby database when the STANDBY_FILE_MANAGEMENT initialization parameter is set to MANUAL. You must set the STANDBY_FILE_MANAGEMENT initialization parameter to MANUAL when the standby datafiles reside on raw devices.
1.      Add a new tablespace to the primary database:
SQL> CREATE TABLESPACE new_ts DATAFILE 't_db2.dbf'
       2> SIZE 1m AUTOEXTEND ON MAXSIZE UNLIMITED;
2.      Verify that the new datafile was added to the primary database:
SQL> SELECT NAME FROM V$DATAFILE;

----------------------------------------------------------------------
/disk1/oracle/dbs/t_db1.dbf
/disk1/oracle/dbs/t_db2.dbf
3.      Perform the following steps to copy the tablespace to a remote standby location:
a.       Place the new tablespace offline:
SQL> ALTER TABLESPACE new_ts OFFLINE;
b.      Copy the new tablespace to a local temporary location using an operating system utility copy command. Copying the files to a temporary location will reduce the amount of time that the tablespace must remain offline. The following example copies the tablespace using the UNIX cp command:
% cp t_db2.dbf s2t_db2.dbf
c.       Place the new tablespace back online:
SQL> ALTER TABLESPACE new_ts ONLINE;
d.      Copy the local copy of the tablespace to a remote standby location using an operating system utility command. The following example uses the UNIX rcp command:
%rcp s2t_db2.dbf standby_location
4.      Archive the current redo log on the primary database so it will get copied to the standby database:
SQL> ALTER SYSTEM ARCHIVE LOG CURRENT;
5.      Use the following query to make sure that managed recovery is running. If the MRP or MRP0 process is returned, managed recovery is being performed.
SQL> SELECT PROCESS, STATUS FROM V$MANAGED_STANDBY;
6.      Verify that the datafile was added to the standby database after the redo log was applied to the standby database.
 SQL> SELECT NAME FROM V$DATAFILE;

----------------------------------------------------------------------
/disk1/oracle/dbs/s2t_db1.dbf
/disk1/oracle/dbs/s2t_db2.dbf
8.4 Dropping a Tablespace in the Primary Database
When you delete one or more datafiles or drop one or more tablespaces in the primary database, you also need to delete the corresponding datafiles in the standby database, as follows:
  1. Drop the tablespace at the primary site:
SQL> DROP TABLESPACE tbs_4;
SQL> ALTER SYSTEM SWITCH LOGFILE;
% rm tbs_4.dbf
  1. Make sure that managed recovery is on (so that the change is applied to the standby database). If the following query returns the MRP or MRP0 process, managed recovery is on.
SQL> SELECT PROCESS, STATUS FROM V$MANAGED_STANDBY;
  1. Delete the corresponding datafile on the standby site after the archived redo log was applied to the standby database. For example:
% rm tbs_4.dbf
  1. On the primary database, after ensuring that the standby database has applied the redo information for the dropped tablespace, you can remove the datafile for the tablespace. For example:
% rm tbs_4.dbf
8.5 Renaming a Datafile in the Primary Database
When you rename one or more datafiles in the primary database, the change is not propagated to the standby database. Therefore, if you want to rename the same datafiles on the standby database, you must manually make the equivalent modifications on the standby database because the modifications are not performed automatically, even if the STANDBY_FILE_MANAGEMENT initialization parameter is set to AUTO.
The following steps describe how to rename a datafile in the primary database and manually propagate the changes to the standby database. If you do not want the standby database to have the same physical structure as the primary database, then these steps are not required.
  1. To rename the datafile in the primary database, take the tablespace offline:
SQL> ALTER TABLESPACE tbs_4 OFFLINE;
  1. Exit from the SQL prompt and issue an operating system command, such as the following UNIX mv command, to rename the datafile on the primary system:
% mv tbs_4.dbf tbs_x.dbf
  1. Rename the datafile in the primary database and bring the tablespace back online:
SQL> ALTER TABLESPACE tbs_4 RENAME DATAFILE 'tbs_4.dbf'
  2> TO 'tbs_x.dbf';
SQL> ALTER TABLESPACE tbs_4 ONLINE;
  1. Connect to the standby database and make sure that all the logs are applied; then stop managed recovery operations:
SQL> SELECT NAME, SEQUENCE#, ARCHIVED, APPLIED
  2> FROM V$ARCHIVED_LOG;
SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;
  1. Shut down the standby database:
SQL> SHUTDOWN;
  1. Rename the datafile at the standby site using an operating system command, such as the UNIX mv command:
% mv tbs_4.dbf tbs_x.dbf
  1. Start and mount the standby database with the new control file:
  SQL> STARTUP NOMOUNT;
     SQL> ALTER DATABASE MOUNT STANDBY DATABASE;
  1. Rename the datafile in the standby controlfile. Note that the STANDBY_FILE_MANAGEMENT initialization parameter must be set to MANUAL.
SQL> ALTER DATABASE RENAME FILE 'tbs_4.dbf'
  2> TO 'tbs_x.dbf';
  1. On the standby database, restart managed recovery operations:
SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE
  2> DISCONNECT FROM SESSION;
If you do not rename the corresponding datafile at the standby site, and then try to refresh the standby database control file, the standby database will attempt to use the renamed datafile, but it will not find it. Consequently, you will see error messages similar to the following in the alert log:
ORA-00283: recovery session canceled due to errors
ORA-01157: cannot identify/lock data file 4 - see DBWR trace file
ORA-01110: data file 4: '/disk1/oracle/dbs/tbs_x.dbf'
Posted by at No comments:

Data Guard – Adding and Resizing a datafile on the Primary database 

In a Data Guard environment, care should be taken while performing tasks like adding or
resizing datafiles on the primary database. Ideally, the primary and standby sites should
be identical in terms of file system structure. Also disk space allocated should be identical,
or we could be faced with a case when resizing a datafile on the the primary site fails on the
standby site as the disk space available is not adequate or maybe a file was added on a file
system on the primary and the corresponding file system was not available on the standby site
based on the parameter db_file_name_convert.

In such cases the MRP0 process will stop on the standby site and redo apply operations will cease.

Test case:

Primary machine:  itlinuxdevblade07
Standby machine: itlinuxdevblade08

We will use the USERS tablespace for the test

• Add a datafile
• Resize a datafile
• Drop tablespace

Database used for testing is dgtest9i.

Firstly the following gives an explanation of init.ora parameters used in a dataguard set-up
and whether they pertain to the primary or standby database.

log_archive_dest_1 : used both when Primary and when Standby

standby_archive_dest : used when Standby, ignored when Primary

log_archive_dest_2 :  used when Primary, ignored when Standby

db_file_name_convert : used when Standby, ignored when Primary

log_file_name_convert :  used when Standby, ignored when Primary

fal_server :   used when Standby, ignored when Primary

fal_client :  used when Standby, ignored when Primary

standby_file_management :  used when Standby, ignored when Primary

log_archive_format :  used both when Primary and when Standby

log_archive_start :  used both when Primary and when Standby

remote_archive_enable : must be TRUE on both Primary and Standby

drs_start :  must be TRUE to use Data Guard Broker

compatible :   must be identical on both Primary and Standby

db_name, db_domain :  must be identical on both Primary and Standby

Primary SITE:

. setdgtest9i

SQL> select name from v$datafile;

NAME
--------------------------------------------------------------------------------
/opt/oracle/oradata/dgtest9i/system01.dbf
/opt/oracle/oradata/dgtest9i/undotbs01.dbf
/opt/oracle/oradata/dgtest9i/users01.dbf

cd /opt/oracle/oradata/dgtest9i> ls –l

[oracle@itlinuxdevblade07 dgtest9i]$ ls -l
total 987948
drwxr-xr-x    2 oracle   dba          4096 Feb 12 11:28 bkp
-rw-r-----    1 oracle   dba      41951232 Jan 25 12:20 temp01.dbf
-rw-r-----    1 oracle   dba      78651392 Mar  4 14:33 undotbs01.dbf
-rw-r-----    1 oracle   dba      22028288 Mar  3 14:41 users01.dbf

In STANDBY database init.ora…

*.standby_file_management='AUTO'
.db_file_name_convert='/opt/oracle/','/opt/oracle/'
*.log_file_name_convert='/opt/oracle/','/opt/oracle/'

STANDBY SITE:

cd / opt/oracle/oradata/dgtest9i>

[oracle@itlinuxdevblade08 dgtest9i]$ /bin/ls –l…. only few files are shown here to save space.
total 1035568
-rwxrwxr-x    1 oracle   dba      41951232 Feb 23 22:38 temp01.dbf
-rwxrwxr-x    1 oracle   dba      78651392 Mar  4 14:37 undotbs01.dbf
-rwxrwxr-x    1 oracle   dba      22028288 Mar  4 09:08 users01.dbf

1.Add a datafile.

In primary database :

. setdgtest9i

SQL> alter tablespace  users add datafile '/opt/oracle/oradata/dgtest9i/users02.dbf'  size 10m;

Tablespace altered.

SQL> alter system switch logfile;

(To make sure the a log is shipped to the standby site and changes applied.)

System altered.

If you check in the standby site you will see the new file ….

STANDBY SITE

[oracle@itlinuxdevblade08 dgtest9i]$ ls -lrt users*
total 1045832
-rw-r-----    1 oracle   dba      10493952 Mar  4 14:46 users02.dbf
-rw-r-----    1 oracle   dba      22028288 Mar  4 14:46 users01.dbf

2. Resize datafile.

PRIMARY SITE:

SQL> Alter database datafile  ‘/opt/oracle/oradata/dgtest9i/users02.dbf' RESIZE 5m;

Database altered.

SQL> alter system switch logfile;

System altered.

SQL> /

System altered.

Now in  STANDBY database machine…

[oracle@itlinuxdevblade08 dgtest9i]$ ls –lrt users*
total 1040708
-rw-r-----    1 oracle   dba       5251072 Mar  4 18:00 users02.dbf
-rwxrwxr-x    1 oracle   dba      22028288 Mar  4 18:00 users01.dbf

Notice the size of the users02.dbf file is 5 m on the standby site as well…

PROBLEMS THAT MAY ARISE :

Now we will look at the scenario where the db_file_convert parameter PATH was wrong..

SHUTDOWN both standby and primary databases…..

Change the db_file_name_convert parameter from standby database init.ora file and recreate
 the spfile from pfile.

EX: db_file_name_convert =/opt/oracle/, /opt/arjun

arjun directory must not exist IN STANDBY machine...

Now startup  the standby db…

SQL> Startup nomount;

SQL> Alter database mount standby database;

Restart PRIMARY DATABASE.

Go back to standby database and

SQL> recover managed standby database disconnect;

SQL> show parameter convert

NAME                                  TYPE      VALUE
------------------------------------ ----------- ---------------------------------------
db_file_name_convert             string /opt/oracle/, /opt/arjun

Now go to PRIMARY database and add a datafile…..

SQL> alter tablespace users add datafile
'/opt/oracle/oradata/dgtest9i/users04.dbf' size 5m;

Tablespace altered.

SQL> alter system switch logfile;

System altered.

SQL> SQL> /

System altered.

SQL> select name from v$datafile;

NAME
--------------------------------------------------------------------------------
/opt/oracle/oradata/dgtest9i/system01.dbf
/opt/oracle/oradata/dgtest9i/undotbs01.dbf
/opt/oracle/oradata/dgtest9i/users01.dbf
/opt/oracle/oradata/dgtest9i/users02.dbf
/opt/oracle/oradata/dgtest9i/users03.dbf
/opt/oracle/oradata/dgtest9i/users04.dbf

In standby site…

SQL> select name from v$datafile;

NAME
--------------------------------------------------------------------------------
/opt/arjun/oradata/dgtest9i/system01.dbf
/opt/arjun/oradata/dgtest9i/undotbs01.dbf
/opt/arjun/oradata/dgtest9i/users01.dbf
/opt/oracle/oradata/dgtest9i/users02.dbf
/opt/oracle/oradata/dgtest9i/users03.dbf

Notice file users04 is missing…

In this case shutdown the standby database….and recreate the spfile making sure the path is right..

Then restart the standby database and start recovery….

SQL>  select message from v$dataguard_status;

MESSAGE
--------------------------------------------------------------------------------
ARC0: Archival started
ARC1: Archival started
Media Recovery Log /opt/oracle/dgtest9i/arch/arch196.log
Media Recovery Waiting for thread 1 seq# 197

10 rows selected.

SQL> select name from v$datafile;

NAME
--------------------------------------------------------------------------------
/opt/oracle/oradata/dgtest9i/users01.dbf
/opt/oracle/oradata/dgtest9i/users02.dbf
/opt/oracle/oradata/dgtest9i/users03.dbf
/opt/oracle/oradata/dgtest9i/users04.dbf

NEXT SCENARIO….ADDING DATAFILE IN ANOTHER AREA ON PRIMARY MACHINE…

SQL> create tablespace arjun datafile '/u01/ORACLE/dgtest9i/arjun1.dbf' size 5m;..The directory
/u01/ORACLE/dgtest9i does not exist on the STANDBY machine.

Tablespace created.

SQL> alter system switch logfile;

SQL> SELECT NAME FROM V$DATAFILE;

NAME
--------------------------------------------------------------------------------
/opt/oracle/oradata/dgtest9i/system01.dbf
/opt/oracle/oradata/dgtest9i/undotbs01.dbf
/opt/oracle/oradata/dgtest9i/users01.dbf
/opt/oracle/oradata/dgtest9i/users02.dbf
/opt/oracle/oradata/dgtest9i/users03.dbf
/opt/oracle/oradata/dgtest9i/users04.dbf
/u01/ORACLE/dgtest9i/arjun1.dbf

On STANDBY site…..

SQL>  select name from v$datafile;

NAME
--------------------------------------------------------------------------------
/opt/oracle/oradata/dgtest9i/system01.dbf
/opt/oracle/oradata/dgtest9i/undotbs01.dbf
/opt/oracle/oradata/dgtest9i/users01.dbf
/opt/oracle/oradata/dgtest9i/users02.dbf
/opt/oracle/oradata/dgtest9i/users03.dbf
/opt/oracle/oradata/dgtest9i/users04.dbf
/opt/oracle/product9204/dbs/UNNAMED00007….

7 rows selected.

STANDBY SITE

SQL>  SHOW PARAMETER STANDBY;

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
standby_archive_dest                 string      ?/dbs/arch
standby_file_management              string      AUTO

SQL> alter system set standby_file_management=manual ;
System altered.

SQL> alter database create datafile '/opt/oracle/product9204/dbs/UNNAMED00007'
  2  as '/opt/oracle/oradata/dgtest9i/arjun1.dbf';

Database altered.

SQL> select name from v$datafile;

NAME
--------------------------------------------------------------------------------
/opt/oracle/oradata/dgtest9i/system01.dbf
/opt/oracle/oradata/dgtest9i/undotbs01.dbf
/opt/oracle/oradata/dgtest9i/users01.dbf
/opt/oracle/oradata/dgtest9i/users02.dbf
/opt/oracle/oradata/dgtest9i/users03.dbf
/opt/oracle/oradata/dgtest9i/users04.dbf
/opt/oracle/oradata/dgtest9i/arjun1.dbf

7 rows selected.

SQL> alter system set standby_file_management=auto ;

3. DROPPING TABLESPACE.

In PRIMARY…

SQL> DROP TABLESPACE ARJUN INCLUDING CONTENTS AND DATAFILES;

SQL> Alter system switch logfile;

SQL> SELECT NAME FROM V$DATAFILE;

NAME
--------------------------------------------------------------------------------
/opt/oracle/oradata/dgtest9i/system01.dbf
/opt/oracle/oradata/dgtest9i/undotbs01.dbf
/opt/oracle/oradata/dgtest9i/users01.dbf
/opt/oracle/oradata/dgtest9i/users02.dbf
/opt/oracle/oradata/dgtest9i/users03.dbf
/opt/oracle/oradata/dgtest9i/users04.dbf

6 rows selected.

In STANDBY Database….

SQL> select name from v$datafile;

NAME
--------------------------------------------------------------------------------
/opt/oracle/oradata/dgtest9i/system01.dbf
/opt/oracle/oradata/dgtest9i/undotbs01.dbf
/opt/oracle/oradata/dgtest9i/users01.dbf
/opt/oracle/oradata/dgtest9i/users02.dbf
/opt/oracle/oradata/dgtest9i/users03.dbf
/opt/oracle/oradata/dgtest9i/users04.dbf

6 rows selected.
Posted by at No comments:
Subscribe to: Posts (Atom)