DBAWORKSHOP: 04/23/13

Creating Physical Standby Database on Oracle 10g

Some key points before proceeding with the physical standby setup.

§ Primary database should be in archivelog mode.

§ Forced Logging is on in Primary database (sql>alter database force logging;)

§ Initialization parameter “db_name” should be same on both primary and standby database.

§ Initialization parameter “db_unique_name” should be different on primary and standby databases.

Primary Database : cwgdb

Standby Database : dupdb

1. Check if the primary database is using the password file or not. If not, then create one as below.

orapwd file=$ORACLE_HOME/dbs/orapwcwgdb password=rac123 force=y

2. Add the following parameters in the initialization parameter file of the primary database.

initprim.ora

cwgdb.__db_cache_size=150994944

cwgdb.__java_pool_size=4194304

cwgdb.__large_pool_size=4194304

cwgdb.__oracle_base='/u01/app/oracle'#ORACLE_BASE set from environment

cwgdb.__pga_aggregate_target=171966464

cwgdb.__sga_target=251658240

cwgdb.__shared_io_pool_size=0

cwgdb.__shared_pool_size=83886080

cwgdb.__streams_pool_size=0

*.audit_file_dest='/u01/app/oracle/admin/cwgdb/adump'

*.audit_trail='db'

*.compatible='11.2.0.0.0'

*.control_files='/u01/app/oracle/oradata/cwgdb/control01.ctl' ,'/u01/app/oracle/archivelog/cwgdb/control02.ctl'

*.db_block_size=8192

*.db_domain=''

*.db_name='cwgdb'

*.db_recovery_file_dest='/u01/app/oracle/archivelog'

*.db_recovery_file_dest_size=4039114752

*.diagnostic_dest='/u01/app/oracle'

#*.dispatchers='(PROTOCOL=TCP) (SERVICE=cwgdbXDB)'

#*.local_listener='CWGDB'

*.log_archive_format='%t_%s_%r.dbf'

*.memory_target=422576128

*.open_cursors=300

*.processes=150

*.remote_login_passwordfile='EXCLUSIVE'

*.undo_tablespace='UNDOTBS1'

#------------------------------------------------------------------------------

*.log_archive_config='DG_CONFIG=(cwgdb,dupdb)'

*.log_archive_dest_1='LOCATION=/u01/archivelog VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=cwgdb'

*.log_archive_dest_2='SERVICE=dupdb VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=dupdb LGWR ASYNC'

#*.standby_archive_dest='/u01/app/oracle/archivelog/CWGDB/archivelog'

*.standby_file_management=auto

*.db_file_name_convert=('/oracle/app/oracle/datafiles','/u01/app/oracle/oradata/cwgdb','/oracle/app/oracle/datafiles','/u02/datafile')

*.log_file_name_convert=('/oracle/app/oracle/datafiles','/u01/app/oracle/oradata/cwgdb')

#*.remote_archive_enable=true

*.log_archive_dest_state_1=enable

*.log_archive_dest_state_2=enable

*.fal_server=dupdb

*.fal_client=cwgdb

*.db_unique_name=cwgdb

3. Setup the connectivity (listener.ora and tnsnames.ora) for the primary and standby databases.

Primary:

listener.ora file

# listener.ora Network Configuration File: /u01/app/oracle/product/11.2.0/dbhome_1/network/admin/listener.ora
# Generated by Oracle configuration tools.
cwgdb=

(DESCRIPTION_LIST =

(DESCRIPTION=

(ADDRESS_LIST=

(ADDRESS=(PROTOCOL=TCP)(HOST=172.16.1.220)(PORT=1521))

)

SID_LIST_cwgdb=

(SID_LIST=

(SID_DESC=

# (SID_NAME = PLSExtProc)

(ORACLE_HOME = /u01/app/oracle/product/11.2.0)

#(PROGRAM = extproc)

# (SID_DESC=

# (ORACLE_HOME = /u01/app/oracle/product/11.2.0)

(SID_NAME=cwgdb)

)

tnsnames.ora file

# tnsnames.ora Network Configuration File: /u01/app/oracle/product/11.2.0/dbhome_1/network/admin/tnsnames.ora
# Generated by Oracle configuration tools.

dupdb =

(DESCRIPTION =

(ADDRESS_LIST =

(ADDRESS = (PROTOCOL = TCP)(HOST = 172.16.1.221)(PORT = 1521))

)

(CONNECT_DATA =

(SERVER = DEDICATED)

(SERVICE_NAME = dupdb)

)

cwgdb =

(DESCRIPTION =

(ADDRESS_LIST =

(ADDRESS = (PROTOCOL = TCP)(HOST = 172.16.1.220)(PORT = 1521))

)

(CONNECT_DATA =

(SERVER = DEDICATED)

(SERVICE_NAME = cwgdb)

)

Standby:

Listener.ora file

# listener.ora Network Configuration File: /u01/app/oracle/product/11.2.0/dbhome_1/network/admin/listener.ora
# Generated by Oracle configuration tools.

dupdb=

(DESCRIPTION_LIST =

(DESCRIPTION=

(ADDRESS_LIST=

(ADDRESS=(PROTOCOL=TCP)(HOST=172.16.1.221)(PORT=1521))

)

SID_LIST_dupdb=

(SID_LIST=

(SID_DESC=

# (SID_NAME = PLSExtProc)

(ORACLE_HOME = /u01/app/oracle/product/11.2.0)

#(PROGRAM = extproc)

# (SID_DESC=

# (ORACLE_HOME = /u01/app/oracle/product/11.2.0)

(SID_NAME=dupdb)

)

tnsnames.ora file

# tnsnames.ora Network Configuration File: /u01/app/oracle/product/11.2.0/dbhome_1/network/admin/tnsnames.ora
# Generated by Oracle configuration tools.

cwgdb =

(DESCRIPTION =

(ADDRESS_LIST =

(ADDRESS = (PROTOCOL = TCP)(HOST = 172.16.1.220)(PORT = 1521))

)

(CONNECT_DATA =

(SERVER = DEDICATED)

(SERVICE_NAME = cwgdb)

)

dupdb =

(DESCRIPTION =

(ADDRESS_LIST =

(ADDRESS = (PROTOCOL = TCP)(HOST = 172.16.1.221)(PORT = 1521))

)

(CONNECT_DATA =

(SERVER = DEDICATED)

(SERVICE_NAME = dupdb)

)

4. Add the following parameters in the initialization parameter file of the standby database

initstnd.ora

dupdb.__db_cache_size=150994944

dupdb.__java_pool_size=4194304

dupdb.__large_pool_size=4194304

dupdb.__oracle_base='/u01/app/oracle'#ORACLE_BASE set from environment

dupdb.__pga_aggregate_target=171966464

dupdb.__sga_target=251658240

dupdb.__shared_io_pool_size=0

dupdb.__shared_pool_size=83886080

dupdb.__streams_pool_size=0

*.audit_file_dest='/oracle/app/oracle/admin/dupdb/adump'

*.audit_trail='db'

*.compatible='11.2.0.0.0'

*.control_files='/oracle/app/oracle/oradata/dupdb/stdby.ctl'

*.db_block_size=8192

*.db_domain=''

*.db_name='cwgdb'

#*.db_recovery_file_dest='/oracle/app/oracle/archivelog'

*.db_recovery_file_dest_size=4039114752

*.diagnostic_dest='/oracle/app/oracle'

#*.dispatchers='(PROTOCOL=TCP) (SERVICE=cwgdbXDB)'

#*.local_listener='CWGDB'

*.log_archive_format='%t_%s_%r.dbf'

*.memory_target=422576128

*.open_cursors=300

*.processes=150

*.remote_login_passwordfile='EXCLUSIVE'

*.undo_tablespace='UNDOTBS1'

#------------------------------------------------------------------------------

*.log_archive_config='DG_CONFIG=(cwgdb,dupdb)'

*.log_archive_dest_1='LOCATION=/oracle/app/oracle/archivelog VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=dupdb'

*.log_archive_dest_2='SERVICE=cwgdb VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLES) DB_UNIQUE_NAME=cwgdb LGWR ASYNC'

#*.standby_archive_dest='/u01/app/oracle/archivelog/CWGDB/archivelog'

*.standby_file_management=auto

*.db_file_name_convert=( '/u01/app/oracle/oradata/cwgdb','/oracle/app/oracle/datafiles','/u02/datafile','/oracle/app/oracle/datafiles')

*.log_file_name_convert=( '/u01/app/oracle/oradata/cwgdb','/oracle/app/oracle/datafiles')

#*.remote_archive_enable=true

*.log_archive_dest_state_1=enable

*.log_archive_dest_state_2=enable

*.fal_server=cwgdb

*.fal_client=dupdb

*.db_unique_name=dupdb

5. Now copy the password file of the primary database “orapwcwgdb” located at $ORACLE_HOME/dbs to the standby server location $ORACLE_HOME/dbs and rename the file as “orapwdupdb”.

Note that the format of the password file in Windows is as pwd<SID>.ora (Example: pwdPRIM.ora)

6. assign a location to take the backup in primary database:

Rman >show all

Rman > CONFIGURE CHANNEL DEVICE TYPE DISK FORMAT '/u01/app/oracle/backup/sbi_%U';

7. Take the full backup from primary database:

Rman> backup database plus archivelog ;

8.now go to mount stage and take the backup of standby controlfile :

Sql>startup mount

Sql> alter database create standby controlfile as '/u01/app/oracle/backup/stdby.ctl;

9. Now connect to the Primary database as target database and standby database as auxiliary instance through RMAN. Make sure that the Primary database is open and the standby database is in nomount stage (STARTED).

rman target sys/<password>@prim auxiliary sys/<password>@stnd

Now perform the duplicate operation to create the standby database.

rman>duplicate target database for standby from active database nofilenamecheck;

10. Once the duplication is completed, close the RMAN prompt and connect to the standby database through SQL.

sqlplus sys/<password>@stnd as sysdba

Check the status of the standby database by making sure it is in mount stage.

sql>select status,instance_name,database_role from v$instance,v$database;

11. Now start the managed recovery process on the standby database.

sql>alter database recover managed standby database disconnect from session;

12. Now check if the managed recovery process (MRP) has been started on the standby database or not.

sql>select process,status,sequence# from v$managed_standby;

Example:

SQL> select process,status,sequence# from v$managed_standby;

PROCESS   STATUS        SEQUENCE#
——— ———— ———-
ARCH      CONNECTED             0
ARCH      CONNECTED             0
ARCH      CONNECTED             0
ARCH      CONNECTED             0
MRP0      WAIT_FOR_LOG         39

Here, the MRP has been started and is waiting for the log sequence# 39. If MRP is not started, then the above query would not show up the MRP0 under the process column.

13. On the primary database, perform a few log switches and check if the logs are applied to the standby database.

sqlplus sys/<password>@prim as sysdba

sql>alter system switch logfile;

sql>select max(sequence#) from v$archived_log;

Example:

SQL> select max(sequence#) from v$archived_log;

MAX(SEQUENCE#)
————–
38

sqlplus sys/<password>@stnd as sysdba

sql>select thread#,max(sequence#) from v$archived_log where applied=’YES’ group by thread#;

Example:

SQL> select thread#,max(sequence#) from v$archived_log where applied=’YES’ group by thread#;

THREAD# MAX(SEQUENCE#)
———- ————–
1 38

Here, the maximum sequence# generated on the Primary database is 38 and the maximum sequence# applied on the standby database is also 38 which means that the standby database is in sync with the primary database.

Here you go !!!

NOTE:

ORA-10456: cannot open standby database; media recovery session may be in progress

è SQL> select * from hr.aa;
select * from hr.aa
*
ERROR at line 1:
ORA-01219: database not open: queries allowed on fixed tables/views only

SQL> select name,open_mode from v$database;
NAME OPEN_MODE
--------- --------------------
NOIDA MOUNTED

SQL> alter database open;
alter database open
*
ERROR at line 1:
ORA-10456: cannot open standby database; media recovery session may be in progress

SQL> alter database recover managed standby database cancel;
Database altered.

SQL> alter database open;
Database altered.

SQL> alter database recover managed standby database using current logfile disconnect ;
Database altered.

PREPARED BY ANAND.J

Moving data from one tablespace to new tablespace:

1) Take a schema level export of particular user

è Expdp sarathi/sarathi123 dumpfile=<file.dmp> logfile=<file.log> schemas=sbbjprod directory=data

2) Create a new tablespace

è Create tablespace <tablespace name> datafile= ‘<location whare you want’> size 31G

3) Create a new user and assign a new tablespace as a default tablespace

è Create user <username> default tablespace <new tablespace name>

4) Import the data

Impdp sbjprod/sbjprod dumpfile=<filename.dmp> logfile=<file.log> remap_tablespace=users:sbbj remap_schema=sbbjprod:sbjprod directory=data

Example:

I am taking scott user with DATA as default tablespace and all the objects in the scott schema is in DATAtablespace.

SQL> SELECT DEFAULT_TABLESPACE FROM DBA_USERS WHERE USERNAME ='SCOTT';

DEFAULT_TABLESPACE

-------------------

DATA

SQL> select table_name, tablespace_name from dba_tables where owner='SCOTT';

TABLE_NAME TABLESPACE_NAME

------------------------------ ------------------------------

S_APLT_WTI_INTL DATA

S_APPL_WTMPL_IT DATA

CANDIDATE DATA

CX_KPN_FLITS DATA

DEPTREE_TEMPTAB DATA

EMP DATA

TEMP_EMP

DEPT DATA

EXAMPLE_SHRINK DATA

9 rows selected.

Exporting the entire schema

$ expdp schemas=scott directory=exp_dir dumpfile=remap.dmp logfile=remap.log compression=all

Export: Release 11.2.0.2.0 - Production on Fri May 11 03:05:31 2012

Username: / as sysdba

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production

With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,

Data Mining and Real Application Testing options

Starting "SYS"."SYS_EXPORT_SCHEMA_01": /******** AS SYSDBA schemas=scott directory=exp_dir dumpfile=remap.dmp logfile=remap.log compressi on=all

Estimate in progress using BLOCKS method...

Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA

Total estimation using BLOCKS method: 860.2 MB

. . .

Master table "SYS"."SYS_EXPORT_SCHEMA_01" successfully loaded/unloaded

******************************************************************************

Dump file set for SYS.SYS_EXPORT_SCHEMA_01 is:

/home/oracle/shony/remap.dmp

Job "SYS"."SYS_EXPORT_SCHEMA_01" successfully completed at 03:06:45

I am going to drop the user and recreate it without any object.

SQL> connect / as sysdba

Connected.

SQL> drop user scott cascade;

User dropped.

SQL> create user scott identified by voyage123 default tablespace data temporary tablespace temp;

User created.

SQL> alter user scott quota unlimited on data;

User altered.

I have created a new tablespace called remap_test and I am going grant quota to the new scott user.

SQL> alter user scott quota unlimited on remap_test;

User altered.

SQL> grant connect, resource to scott;

Grant succeeded.

Now I am going to import the previous dump with remap_tablespace option

$ impdp anand/anand dumpfile=scott.dmp logfile=scott1.log remap_tablespace=users:sbh remap_schema=scott:anand

$ impdp directory=exp_dir dumpfile=remap.dmp logfile=imp_remap.logremap_tablespace=data:remap_test

Import: Release 11.2.0.2.0 - Production on Fri May 11 05:22:44 2012

Username: / as sysdba

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production

With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,

Data Mining and Real Application Testing options

Master table "SYS"."SYS_IMPORT_FULL_01" successfully loaded/unloaded

Starting "SYS"."SYS_IMPORT_FULL_01": /******** AS SYSDBA directory=exp_dir dumpfile=remap.dmp logfile=imp_remap.log remap_tablespace=data:remap_test

. . .

Job "SYS"."SYS_IMPORT_FULL_01" completed successfully at 05:23:40

After the import if you check the objects tablespace as REMAP_TEST. Even though it is export from DATA tablespace.

SQL> select table_name, tablespace_name from dba_tables where owner='SCOTT';

TABLE_NAME TABLESPACE_NAME

------------------------------ ------------------------------

S_APLT_WTI_INTL REMAP_TEST

S_APPL_WTMPL_IT REMAP_TEST

CANDIDATE REMAP_TEST

CX_KPN_FLITS REMAP_TEST

DEPTREE_TEMPTAB REMAP_TEST

EMP REMAP_TEST

TEMP_EMP

DEPT REMAP_TEST

EXAMPLE_SHRINK REMAP_TEST

9 rows selected.

DBAWORKSHOP

Tuesday, April 23, 2013

PHYSICAL DATAGUARD

IMPDP