Dev's Weblog

Reading time: 4 - 7 minutes

Source Database        : london.sysdbaonline.com

Destination Database  : Californ.sysdbaonline.com

• create streams admin user on source database and destination database.

 

SQL> CREATE TABLESPACE streams_tbs DATAFILE '/u01/database/london/streams_tbs.dbf' SIZE 25M REUSE AUTOEXTEND ON MAXSIZE UNLIMITED;

Tablespace created.

 

SQL> CREATE USER strmadmin IDENTIFIED BY "oracle" DEFAULT TABLESPACE streams_tbs QUOTA UNLIMITED ON streams_tbs;

User created.

 

SQL> GRANT DBA TO strmadmin;

Grant succeeded.

 

SQL> BEGIN
DBMS_STREAMS_AUTH.GRANT_ADMIN_PRIVILEGE(
grantee          => 'strmadmin',   
grant_privileges => true);
END;
/  2    3    4    5    6 

PL/SQL procedure successfully completed.

 

SQL> CREATE TABLESPACE streams_tbs DATAFILE '/u01/database/californ/streams_tbs.dbf' SIZE 25M REUSE AUTOEXTEND ON MAXSIZE UNLIMITED;

Tablespace created.

 

SQL> CREATE USER strmadmin IDENTIFIED BY "oracle" DEFAULT TABLESPACE streams_tbs QUOTA UNLIMITED ON streams_tbs;

User created.

 

SQL> GRANT DBA TO strmadmin;

Grant succeeded.

 

SQL> BEGIN
DBMS_STREAMS_AUTH.GRANT_ADMIN_PRIVILEGE(
grantee          => 'strmadmin',   
grant_privileges => true);
END;
/  2    3    4    5    6 

PL/SQL procedure successfully completed.

 

• Setup Initialization Parameters on source database and destination database. 

 

SQL> alter system set global_names=TRUE;

System altered.

 

SQL> alter system set open_links=20 scope=spfile;

System altered.

 

SQL> alter system set streams_pool_size=50M;

System altered.

 

• Restart source and destination database so that parameter change comes into effect

SQL> startup force
ORACLE instance started.

Total System Global Area  167772160 bytes
Fixed Size                        1218316 bytes
Variable Size                    117442804 bytes
Database Buffers             46137344 bytes
Redo Buffers                   2973696 bytes
Database mounted.
Database opened.

• Create database link on source and destination database ,so that it can connect to destination database

$sqlplus strmadmin/oracle@london.sysdbaonline.com

SQL> CREATE DATABASE LINK californ.sysdbaonline.com CONNECT TO strmadmin IDENTIFIED BY "oracle" USING 'californ.sysdbaonline.com';

Database link created.

 

$sqlplus strmadmin/oracle@californ.sysdbaonline.com

SQL> CREATE DATABASE LINK london.sysdbaonline.com CONNECT TO strmadmin IDENTIFIED BY "oracle" USING 'london.sysdbaonline.com';

Database link created.

 

• Make sure that source and destination database is in archivelog mode

SQL> select log_mode from v$database;

LOG_MODE
------------
ARCHIVELOG

 

• Create directory object in source and destination database becaue we are going to use DATAPUMP for instantiation.

$sqlplus strmadmin/oracle@london.sysdbaonline.com

SQL>create directory source_dir as '/u01/database/source';

$mkdir -p /u01/database/source

 

$sqlplus strmadmin/oracle@californ.sysdbaonline.com

SQL>create directory dest_dir as '/u01/database/dest';

$mkdir -p /u01/database/dest

• Configure instantiation using following procedures of DBMS_STREAMS_ADM package

$ sqlplus strmadmin/oracle@london.sysdbaonline.com

BEGIN
  DBMS_STREAMS_ADM.MAINTAIN_SCHEMAS(
    schema_names                 => 'RABI',
    source_directory_object      => 'EXP_DP_DIR',
    destination_directory_object => 'EXP_DP_VOL',
    source_database              => 'london.sysdbaonline.com',
    destination_database         => 'californ.sysdbaonline.com',
    perform_actions              => true,
    dump_file_name               => 'export_rabi.dmp',
    capture_queue_table          => 'rep_capture_queue_table',
    capture_queue_name           => 'rep_capture_queue',
    capture_queue_user           => NULL,
    apply_queue_table            => 'rep_dest_queue_table',
    apply_queue_name             => 'rep_dest_queue',
    apply_queue_user             => NULL,
    capture_name                 => 'capture_rabi',
    propagation_name             => 'prop_rabi',
    apply_name                   => 'apply_rabi',
    log_file                     => 'export_rabi.clg',
    bi_directional               => true,
    include_ddl                  => true,
    instantiation                => DBMS_STREAMS_ADM.INSTANTIATION_SCHEMA);
END;
/

 

• You are all done.RABI schema is configured for bi-directional replication now.

If replication is not working that try troubleshooting using following post.

http://sysdbaonline.com/?p=888

Reading time: 8 - 13 minutes

Task 1: Gather Files and Perform BackUp..........................

Task 2: Configure Oracle Net SERVICES on the Standby.............

Task 3: Create the Standby Instances and Database................

Task 4: Configure The Primary Database For Data Guard............

Task 5: Verify Data Guard Configuration..........................

1. On the primary node, create a staging directory.

For example:

[oracle@primary oracle]$ mkdir -p /u01/stage

1. Create the same exact path on one of the standby hosts:

[oracle@quartz oracle]$ mkdir -p /u01/stage

2. On the primary node, connect to the primary database and create a PFILE from the SPFILE in the staging directory.

For example:

SQL> CREATE PFILE='/u01/stage/initPRIMARY.ora' FROM SPFILE;

3. On the primary node, perform an RMAN backup of the primary database that places the backup pieces into the staging directory.

For example:

[oracle@primary stage]$ rman target /

RMAN> BACKUP DEVICE TYPE DISK FORMAT '/u01/stage/%U' DATABASE PLUS ARCHIVELOG;

RMAN> BACKUP DEVICE TYPE DISK FORMAT '/u01/stage/%U' CURRENT CONTROLFILE FOR STANDBY;

5. Place a copy of the listener.ora, tnsnames.ora, and sqlnet.ora files into the staging directory. For example:

[oracle@primary oracle]$ cp $ORACLE_HOME/network/admin/*.ora /u01/stage

6. Copy the contents of the staging directory on primary node to the standby node on which the staging directory was created on in step 2. For example:

[oracle@primary oracle]$ scp /u01/stage/* oracle@quartz:/u01/stage

1. Copy the listener.ora, tnsnames.ora, and sqlnet.ora files from the staging directory on the standby host to the $ORACLE_HOME/network/admin directory on all standby hosts.

2. Modify the listener.ora files of primary and standby host.

Listner.ora of primary host must look like following.

SID_LIST_LISTENER =

(SID_LIST =

(SID_DESC =

(SID_NAME = PLSExtProc)

(ORACLE_HOME = /u01/app/oracle/product/10.2.0/db_1/)

(PROGRAM = extproc)

)

(SID_DESC =

(SID_NAME = primary)

(GLOBAL_DBNAME = primary_DGMGRL)

(ORACLE_HOME = /u01/app/oracle/product/10.2.0/db_1/)

)

)

LISTENER =

(DESCRIPTION_LIST =

(DESCRIPTION =

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

(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC0))

)

)

Listner.ora of standby host must look like following.

SID_LIST_LISTENER =

(SID_LIST =

(SID_DESC =

(SID_NAME = PLSExtProc)

(ORACLE_HOME = /u01/app/oracle/product/10.2.0/db_1/)

(PROGRAM = extproc)

)

(SID_DESC =

(SID_NAME = standby)

(GLOBAL_DBNAME = standby_DGMGRL)

(ORACLE_HOME = /u01/app/oracle/product/10.2.0/db_1/)

)

)

LISTENER =

(DESCRIPTION_LIST =

(DESCRIPTION =

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

(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC0))

)

)

1. Start the standby listeners on all standby hosts.

1. To enable secure transmission of redo data, make sure the primary and standby databases use a password file, and make sure the password for the SYS user is identical on every system.

For example:

$ cd $ORACLE_HOME/dbs

$ orapwd file=orapwSTANDBY password=oracle

2. Copy and rename the primary database PFILE from the staging area on all standby hosts to the $ORACLE_HOME/dbs directory on all standby hosts. For example:

[oracle@standby_host1 stage]$ cp initPRIMARY.ora $ORACLE_HOME/dbs/initSTANDBY.ora

2. Modify the standby initialization parameter file copied from the primary node to include Data Guard parameters as illustrated in the following table:

Data Guard Parameter

*.log_archive_config=

'dg_config=(primary,standby)'

*.log_archive_dest_2=

'service=standby valid_for=(online_logfiles,primary_role)

db_unique_name=standby'

*.db_file_name_convert='/u01/database/primary',

'/u01/database/standby'

*.log_file_name_convert='/u01/database/primary',

'/u01/database/standby'

*.standby_file_management=auto

*.fal_server='standby'

*.fal_client='primary'

*.service_names='primary'

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

*.background_dump_dest=

/u01/app/oracle/admin/standby/bdump

*.core_dump_dest=

/u01/app/oracle/admin/standby/cdump

*.user_dump_dest=

/u01/app/oracle/admin/standby/udump

*.audit_file_dest=

/u01/app/oracle/admin/standby/adump

*.db_recovery_dest='+RECOVERY'

1. Create the dump directories on all standby hosts as referenced in the standby initialization parameter file.

For example:

[oracle@standby_host1 oracle]$ mkdir -p $ORACLE_BASE/admin/STANDBY/bdump

[oracle@standby_host1 oracle]$ mkdir -p

$ORACLE_BASE/admin/STANDBY/cdump

[oracle@standby_host1 oracle]$ mkdir -p $ORACLE_BASE/admin/STANDBY/udump

[oracle@standby_host1 oracle]$ mkdir -p $ORACLE_BASE/admin/STANDBY/adump

1. After setting up the appropriate environment variables on each standby host, such as ORACLE_SID, ORACLE_HOME, and PATH, start the standby database instance on the standby host that has the staging directory, without mounting the control file.

SQL> STARTUP NOMOUNT

1. From the standby host where the standby instance was just started, duplicate the primary database as a standby.

For example:

$ rman target sys/oracle@primary auxiliary /

RMAN> DUPLICATE TARGET DATABASE FOR STANDBY;

1. Connect to the standby database, and create the standby redo logs to support the standby role. The standby redo logs must be the same size as the primary database online logs.

The recommended number of standby redo logs is:

(maximum # of logfiles +1) * maximum # of threads

This example uses two online log files for each thread. Thus, the number of standby redo logs should be

(2 + 1) * 2 = 6.

That is, one more standby redo log file for each thread.

SQL> ALTER DATABASE ADD STANDBY LOGFILE THREAD 1

GROUP 5 SIZE 10M,

GROUP 6 SIZE 10M,

GROUP 7 SIZE 10M;

SQL> ALTER DATABASE ADD STANDBY LOGFILE THREAD 2

GROUP 8 SIZE 10M,

GROUP 9 SIZE 10M,

GROUP 10 SIZE 10M;

These statements create two standby log members for each group, and each member is 10MB in size. One member is created in the directory specified by the DB_CREATE_FILE_DEST initialization parameter, and the other member is created in the directory specified by DB_RECOVERY_FILE_DEST initialization parameter.

Because this example assumes that there are two redo log groups in two threads, the next group is group five.

You can check the number and group numbers of the redo logs by querying the V$LOG view:

SQL> SELECT * FROM V$LOG;

You can check the results of the previous statements by querying the V$STANDBY_LOG view:

SQL> SELECT * FROM V$STANDBY_LOG;

You can also see the members created by querying the V$LOGFILE view:

SQL> SELECT * FROM V$LOGFILE;

On only one standby host (and this is your designated Redo Apply instance), start managed recovery and real-time apply on the standby database:

SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE DISCONNECT;

1. Configure the primary database initialization parameters to support both the primary and standby roles.

*.log_archive_config='dg_config=(STANDBY,PRIMARY)'

*.log_archive_dest_2='service=STANDBY

valid_for=(online_logfiles,primary_role)

db_unique_name=STANDBY'

*.db_file_name_convert='/u01/database/standby','/u01/database/primary' *.log_file_name_convert='/u01/database/standby','/u01/database/primary'

*.standby_file_management=auto

*.fal_server='STANDBY'

*.fal_client='PRIMARY'

*.service_names=PRIMARY

1. Create standby redo logs on the primary database to support the standby role. The standby redo logs are the same size as the primary database online logs. The recommended number of standby redo logs is one more than the number of online redo logs for each thread. Because this example has two online redo logs for each thread, three standby redo logs are required for each thread .

SQL> ALTER DATABASE ADD STANDBY LOGFILE THREAD 1

GROUP 5 SIZE 10M,

GROUP 6 SIZE 10M,

GROUP 7 SIZE 10M;

SQL> ALTER DATABASE ADD STANDBY LOGFILE THREAD 2

GROUP 8 SIZE 10M,

GROUP 9 SIZE 10M,

GROUP 10 SIZE 10M;

These statements create two standby log members for each group, and each member is 10MB in size. One member is created in the directory specified by the DB_CREATE_FILE_DEST initialization parameter, and the other member is created in the directory specified by DB_RECOVERY_FILE_DEST initialization parameter. Because this example assumes that there are two redo log groups in two threads, the next group is group five.

You can check the number and group numbers of the redo logs by querying the V$LOG view:

SQL> SELECT * FROM V$LOG;

You can check the results of the previous statements by querying the V$STANDBY_LOG view:

SQL> SELECT * FROM V$STANDBY_LOG;

You can also see the members created by querying V$LOGFILE view:

SQL> SELECT * FROM V$LOGFILE;

1. On the standby database, query the V$ARCHIVED_LOG view to identify existing files in the archived redo log. For example:

SQL> SELECT SEQUENCE#, FIRST_TIME, NEXT_TIME FROM V$ARCHIVED_LOG ORDER BY SEQUENCE#;

2. On the primary database, issue the following SQL statement to force a log switch and archive the current online redo log file group:

SQL> ALTER SYSTEM ARCHIVE LOG CURRENT;

3. On the standby database, query the V$ARCHIVED_LOG view to verify that the redo data was received and archived on the standby database:

SQL> SELECT SEQUENCE#, FIRST_TIME, NEXT_TIME  FROM V$ARCHIVED_LOG ORDER BY SEQUENCE#;

Reading time: 6 - 9 minutes

Pre-Switchover / Failover Check

Please note that Dataguard Broker / Observer running on a-proddb2 ,so all the following steps must be run from a-proddb2.

You can connect to BOSTON ( Primary instance ) or CHICAGO ( Standby Instance ) using tns strings , like system/password@BOSTON

Verify Data Guard Environment Health.

1.Check the DGMGRL configuration status using following command. It must return SUCCESS.

$dgmgrl sys/password@CHICAGO
DGMGRL> show configuration verbose;
Configuration
Name:                DG
Enabled:             YES
Protection Mode:     Maxavailability
Fast-Start Failover: ENABLED
Databases:
   CHICAGO - Physical standby database
          - Fast-Start Failover target
   BOSTON - Primary database
Fast-Start Failover
  Threshold:           60 seconds
Observer:            eul0001311
Shutdown Primary:    TRUE
Current status for "DG":
SUCCESS

2. Validates each database for the current status.

$dgmgrl sys/password@CHICAGO
DGMGRL> show database "BOSTON";
Database
Name:            BOSTON
Role:            PRIMARY
Enabled:         YES
Intended State:  ONLINE
Instance(s):
BOSTON

Current status for "BOSTON":
SUCCESS

DGMGRL> show database "CHICAGO";
Database
Name:            CHICAGO
Role:            PHYSICAL STANDBY
Enabled:         YES
Intended State:  ONLINE
Instance(s):
CHICAGO

Current status for "CHICAGO":
SUCCESS

3.Performs a current log archive on the primary database and then verifies that the log file was applied on standby database.

On primary database ,

$sqlplus sys/password@BOSTON as sysdba

SQL> alter system archive log current;
System altered.

Immediately on standby database check the alert log and you should see output similar like following.

tail -f /apps1/oracle01/u11/app/oracle/admin/CHICAGO/bdump/alert_CHICAGO.log

Tue May 19 11:45:53 2009
Primary database is in MAXIMUM AVAILABILITY mode
Standby controlfile consistent with primary
RFS[7]: Successfully opened standby log 4: '/oradata02/u14/oradata/CHICAGO/stdby_redo01a.log'
Tue May 19 11:45:53 2009
Media Recovery Waiting for thread 1 sequence 15 (in transit)
Tue May 19 11:45:53 2009
Recovery of Online Redo Log: Thread 1 Group 4 Seq 15 Reading mem 0
Mem# 0: /oradata02/u14/oradata/CHICAGO/stdby_redo01a.log

4.Verify there are no large GAPS.GAP should not be more than 3.

Identify the current sequence number for each thread

$sqlplus sys/password@BOSTON as sysdba

SQL> SELECT THREAD#, SEQUENCE# FROM V$THREAD;

|THREAD#|  SEQUENCE#|
---------------------
|1      |  15       |

Verify the standby has applied up to, but not including the logs from the primary query. On the standby the following query should be no more than 1-2 less than the primary query result.

$sqlplus sys/password@CHICAGO as sysdba

SQL>SELECT THREAD#, MAX(SEQUENCE#) FROM V$ARCHIVED_LOG val, V$DATABASE vdb WHERE APPLIED = 'YES' AND val.RESETLOGS_CHANGE#=vdb.RESETLOGS_CHANGE# GROUP BY THREAD#;

|THREAD#| MAX(SEQUENCE#)|
-------------------------
| 1     |     14        |

5.Verify Primary and Standby TEMP File size Match

On primary ,

$sqlplus sys/password@BOSTON as sysdba

0:sys@BOSTON> select tmp.name Filename, bytes, ts.name Tablespace from v$tempfile tmp, v$tablespace ts where tmp.ts#=ts.ts#;

|Filename                                |Bytes|tablespace|
-----------------------------------------------------------
|/oradata02/u13/oradata/BOSTON/temp01.dbf|20971520|TEMP   |

On Standby ,

$sqlplus sys/password@CHICAGO as sysdba

0:sys@CHICAGO> select tmp.name Filename, bytes, ts.name Tablespace from v$tempfile tmp, v$tablespace ts where tmp.ts#=ts.ts#;

|Filename                                |Bytes   |tablespace|
--------------------------------------------------------------
|/oradata02/u13/oradata/CHICAGO/temp01.dbf|20971520|TEMP      |

6.Check the database_role and FS_FAILOVER_STATUS columns's value in both instance

FS_FAILOVER_STATUS must return SYNCHRONIZED for successful switchover.

DATABASE_ROLE would tell you whether its a primary instance or standby instance

$sqlplus sys/password@BOSTON as sysdba
SQL> select database_role,FS_FAILOVER_STATUS from v$database;

DATABASE_ROLE    FS_FAILOVER_STATUS
---------------- ---------------------
PRIMARY          SYNCHRONIZED

On Standby ,

$sqlplus sys/password@CHICAGO as sysdba

SQL> select database_role,FS_FAILOVER_STATUS from v$database;

DATABASE_ROLE    FS_FAILOVER_STATUS
---------------- ---------------------
PHYSICAL STANDBY SYNCHRONIZED

Switchover

Clear Potential Blocking Parameters & Jobs

Capture current job state on the primary

$sqlplus sys/password@BOSTON as sysdba

SQL> select * from dba_jobs_running; 

*[ depending on what the running job is, be ready to terminate ]*

SQL> select owner,job_name, start_date, end_date, enabled from dba_scheduler_jobs where enabled='TRUE' and owner <> 'SYS';

|Owner     |job_Name             |start_Date|        

60
;            |end_Date|enabled|
------------------------------------------------------------------------------------
|ANALYTICS |NORMALIZEJOB         |16-APR-09 15.59.21.113399 GMT   |        |TRUE   |
|ANALYTICS |NIGHTLYJOB           |16-APR-09 15.59.21.144124 GMT   |        |TRUE   |
|ANALYTICS |UPDATEIP2COUNTRY_JOB |15-MAR-06 13.34.32.447815 +01:00|        |TRUE   |

SQL> show parameter job_queue_processes

|NAME                 |TYPE   | VALUE|
|job_queue_processes  |integer|    10|

Block further job submission

SQL> alter system set job_queue_processes=0 scope=both sid='*';

SQL> execute dbms_scheduler.disable(job_name);

Disable any cron jobs that may interfere.

Perform Switchover

Log into the dgmgrl command line utility as sys using the same password as the sys user on the primary and standby databases Issue the switchover to command:

dgmgrl sys/password@CHICAGO
DGMGRL> switchover to "CHICAGO";

If switchover fails then check the log files /apps1/oracle01/u11/app/oracle/admin/BOSTON/bdump/drcBOSTON.log ( On Primary ) and /apps1/oracle01/u11/app/oracle/admin/CHICAGO/bdump/drcCHICAGO.log ( On Standby )

Failover

Please perform all the checks before performing the failover if its a manual failover.

Log into the dgmgrl command line utility as sys using the same password as the sys user on the primary.

Issue the failover to command:

dgmgrl sys/password@CHICAGO
DGMGRL> switchover to "CHICAGO";

If switchover fails then check the log files /apps1/oracle01/u11/app/oracle/admin/BOSTON/bdump/drcBOSTON.log ( On Primary ) and /apps1/oracle01/u11/app/oracle/admin/CHICAGO/bdump/drcCHICAGO.log ( On Standby )

Dataguard Observer

Starting the observer.

Observer should be started in background ,so it wont get killed even if you close the session from where it is started.

[oracle01@eul0001311/a-db2 ~]$ cat observer.sh
#!/bin/bash
# startobserver
export ORACLE_BASE=/apps1/oracle01/u11/app/oracle/
export ORACLE_HOME=/apps1/oracle01/u11/app/oracle/product/10.2.0/db_1
export PATH=$ORACLE_HOME/bin:$PATH
dgmgrl -logfile /tmp/observer.log << eof
connect sys/password@CHICAGO
START OBSERVER;
eof

The above script is located in /home/oracle01 on a-proddb2.

Run it as following,

nohup  /home/oracle01/observer.sh &

Stopping Observer

For stopping the observer , you need to login to dgmgrl.

$dgmgrl sys/password@CHICAGO
DGMGRL>stop observer;

Observer log

You can find observer's log into /tmp/observer.log

[oracle01@eul0001311/a-db2 ~]$ tail -f /tmp/observer.log
Observer started
[W000 05/05 13:49:11.40] Observer started.

Reading time: 4 - 6 minutes

Prerequisite

1 : Before setting up a logical standby database, ensure the logical standby database can maintain the data types and tables in your primary database. See Appendix C of the dataguard documentation for a complete list of data type and storage type considerations.

2 : Ensure Table Rows in the Primary Database Can Be Uniquely Identified.

2.1 : Find Tables Without Unique Logical Identifier in the Primary Database.

Use following query to display a list of tables that SQL Apply may not be able to uniquely identify:

SQL> SELECT OWNER, TABLE_NAME FROM DBA_LOGSTDBY_NOT_UNIQUE WHERE (OWNER, TABLE_NAME) NOT IN (SELECT DISTINCT OWNER, TABLE_NAME FROM DBA_LOGSTDBY_UNSUPPORTED) AND BAD_COLUMN = 'Y';

2.2 : If your application ensures the rows in a table are unique, you can create a disabled primary key RELY constraint on the table. Use ALTER TABLE command to add a disabled primary-key RELY constraint.

The following example creates a disabled RELY constraint on a table named mytab, for which rows can be uniquely identified using the id and name columns:

SQL> ALTER TABLE mytab ADD PRIMARY KEY (id, name) RELY DISABLE;

Creating a Logical Standby Database:

Step 1 Create a Physical Standby Database

Create a Physical Standby Database and make sure that there is no error in remote archiving to Standby from Primary Database.

Step 2 Make Sure that Physical Standby is in Sync with Primary Database

Use following query on Standby to check:

SQL>SELECT MAX(AL.SEQUENCE#) "LAST SEQ RECEIVED", MAX(LH.SEQUENCE#) "LAST SEQ APPLIED" FROM V$ARCHIVED_LOG AL, V$LOG_HISTORY LH;

There should not be any difference in Last Seq Received and Last Seq Applied on Physical Standby.

Step 3 Stop Redo Apply on the Physical Standby Database

SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;

Step 4 Set Parameters for Logical Standby in Primary

4.1. Change VALID_FOR in LOG_ARCHIVE_DEST_1 on Primary to (ONLINE_LOGFILES,ALL_ROLES)

LOG_ARCHIVE_DEST_1= 'LOCATION=/u01/arch/online/ VALID_FOR=(ONLINE_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=prim1'

4.2. Set LOG_ARCHIVE_DEST_3 for logs which will received on Standby from Primary

LOG_ARCHIVE_DEST_3= 'LOCATION=/u01/arch/standby/ VALID_FOR=(STANDBY_LOGFILES,STANDBY_ROLE) DB_UNIQUE_NAME=prim1'

LOG_ARCHIVE_DEST_STATE_3=ENABLE

Note: LOG_ARCHIVE_DEST_3 only takes effect when the primary database is transitioned to the logical standby role.

Step 5 Build a Dictionary in the Redo Data on Primary Database

SQL> EXECUTE DBMS_LOGSTDBY.BUILD;

The DBMS_LOGSTDBY.BUILD procedure waits for all existing transactions to complete. Long-running transactions executed on the primary database will affect the timeliness of this command.

Step 6 Convert to a Logical Standby Database

SQL> ALTER DATABASE RECOVER TO LOGICAL STANDBY <db_name>;

For db_name, specify a database name to identify the new logical standby database. If you are using a spfile for standby, then command will update the db_name parameter otherwise it will issues a message reminding you to set the name of the DB_NAME parameter after shutting down the database.

Step 7 Create a New Password File for Logical Standby Database

$ORAPWD FILE=<filename> PASSWORD=<password> ENTRIES=<max_users>

This step is required in 10.2 only and should not be performed in 11g.

Step 8 Shutdown and Startup Logical Standby Database in Mount Stage

SQL> SHUTDOWN;
SQL> STARTUP MOUNT;

Step 9 Adjust Initialization Parameter on Logical Standby Database

LOG_ARCHIVE_DEST_1= 'LOCATION=/u01/arch/online/ VALID_FOR=(ONLINE_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=logstd1'
LOG_ARCHIVE_DEST_2= 'SERVICE=prim1 LGWR ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=prim1'
LOG_ARCHIVE_DEST_3= 'LOCATION=/u01/arch/standby/ VALID_FOR=(STANDBY_LOGFILES,STANDBY_ROLE) DB_UNIQUE_NAME=logstd1'
LOG_ARCHIVE_DEST_STATE_1=ENABLE
LOG_ARCHIVE_DEST_STATE_2=ENABLE
LOG_ARCHIVE_DEST_STATE_3=ENABLE

Step 10 Open the Logical Standby Database

SQL> ALTER DATABASE OPEN RESETLOGS;

Step 11 Start Logical Apply on Standby

SQL> ALTER DATABASE START LOGICAL STANDBY APPLY IMMEDIATE;

Reading time: 3 - 5 minutes

Often DBAs are needed to import the data of one table of first database to another table of another database.

With 10G DBAs had to export the table and import it to destination DB then rename the table.

But with 11G you can simply REMAP the table to new table name of destination Database.

As for Example,

Source DB : CHICAGO

$sqlplus dev/oracle@CHICAGO

SQL>create table dev as select rownum num from dual connect by rownum <= 1000000;

Check the size of table after its creating using following SQL.

col segment_name format a20
select segment_name,bytes "SIZE_BYTES",ceil(bytes / 1024 / 1024) "SIZE_MB" from   dba_segments where  segment_name = 'DEV';

SEGMENT_NAME         ?SIZE_BYTES?  ?SIZE_MB?
--------------------         ------------         ----------
DEV                           13631488         13

1. Export DEV table from DEV schema.

expdp userid=dev/oracle dumpfile=DATA_PUMP_DIR:exp.dmp tables=dev reuse_dumpfiles=y

Reuse_dumpfiles will re-create the dumpfile if the same file exists.This is mostly helpful in case of ASM where you need remove the dumpfile from SQLPLUS if you don't have access to ASMCMD.

Export: Release 11.2.0.1.0 - Production on Tue Sep 8 12:01:23 2009

Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Starting "DEV"."SYS_EXPORT_TABLE_01":  userid=dev/******** dumpfile=DATA_PUMP_DIR:exp.dmp tables=dev reuse_dumpfiles=y
Estimate in progress using BLOCKS method...
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 13 MB
Processing object type TABLE_EXPORT/TABLE/TABLE
. . exported "DEV"."DEV"                                 8.568 MB 1000000 rows
Master table "DEV"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded
******************************************************************************
Dump file set for DEV.SYS_EXPORT_TABLE_01 is:
  /u01/app/dev/admin/CHICAGO/dpdump/exp.dmp
Job "DEV"."SYS_EXPORT_TABLE_01" successfully completed at 12:01:33

Destination DB : DUP

1. Import the Dev table to Het table.Please note that Het table must not exists before you import it.

impdp userid=dev/oracle dumpfile=DATA_PUMP_DIR:exp.dmp remap_table=dev.dev:het

Import: Release 11.2.0.1.0 - Production on Tue Sep 8 12:07:33 2009

Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Master table "DEV"."SYS_IMPORT_FULL_01" successfully loaded/unloaded
Starting "DEV"."SYS_IMPORT_FULL_01":  userid=dev/******** dumpfile=DATA_PUMP_DIR:exp.dmp remap_table=dev.dev:het
Processing object type TABLE_EXPORT/TABLE/TABLE
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
. . imported "DEV"."HET"                                 8.568 MB 1000000 rows
Job "DEV"."SYS_IMPORT_FULL_01" successfully completed at 12:07:38

Notice that the REMAP_TABLE option is designated as

[schema.]old_tablename[.partition]:new_tablename.

• Check Het table created or not.

dev@shetal:~$ sqlplus  dev/oracle@DUP

SQL*Plus: Release 11.2.0.1.0 Production on Tue Sep 8 12:08:10 2009

Copyright (c) 1982, 2009, Oracle.  All rights reserved.

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> desc het
Name                       Null?    Type
----------------------------------------- -------- ----------------------------
NUM                            NUMBER

SQL> select count(*) from het;

  COUNT(*)
----------
   1000000

Enjoy Madi…

Reading time: 2 - 4 minutes

1.Install Oracle RDBMS Server Software on destination host.

2.Patch it to same release which is installed on source host.

3.Copy Datafile Backups , Archivelog Backups , Controlfile backup and spfile backup on to destination host ( test2 ) to same location as source host ( test1 )

4.Restore spfile from autobackup using following command.

$export ORACLE_SID=DEV
$export ORACLE_HOME=/apps1/oracle01/u11/app/oracle/product/10.2.0/db_1
$rman target / nocatalog
RMAN>set DBID=00000000 ( You can get DB ID for source db by querying v$database view )
RMAN>startup nomount;
RMAN>restore spfile from '/var/backups1/DEV/autobackup/2009_10_04/o1_mf_s_699336061_5dj3zg47_.bkp';

Spfile will be restored in $ORACLE_HOME/dbs folder.

RMAN>shutdown immediate;
$sqlplus / as sysdba
SQL>create pfile from spfile;

pfile will be restored in $ORACLE_HOME/dbs folder.

Open pfile ( initDEV.ora ) and modify adump,bdump,cdump,udump and db_recovery_file_dest parameter.Also modify control_files parameter according to new path.

$sqlplus / as sysdba
SQL>startup nomount pfile='$ORACLE_HOME/dbs/initDEV.ora'
SQL>create spfile from pfile;
SQL>startup nomount force;

5.Restore controlfile from autobackup using following command.

$rman target / nocatalog
RMAN>set DBID=00000000
RMAN>restore controlfile from '/var/backups1/DEV/autobackup/2009_10_04/o1_mf_s_699336061_5dj3zg47_.bkp';
RMAN>alter database mount;

6.Restore datafiles.
If the directory structure is different than you need to use "set newname" command to change file name location else its not required.

Create RMAN script for restoring DB.

RUN
{
SET NEWNAME FOR DATAFILE 1 TO '/apps1/DEV/system01.dbf';
SET NEWNAME FOR DATAFILE 2 TO '/apps1/DEV/undotbs01.dbf';
SET NEWNAME FOR DATAFILE 3 TO '/apps1/DEV/sysaux01.dbf';
SET NEWNAME FOR DATAFILE 4 TO '/apps1/DEV/users01.dbf';
SET NEWNAME FOR DATAFILE 5 TO '/apps1/DEV/cs_dev_owner_01.dbf';
SET NEWNAME FOR DATAFILE 6 TO '/apps1/DEV/admin_01.dbf';
SET UNTIL TIMEA = "to_date('2019-11-03:41:54:00','YYYY-MM-DD:HH24:MI:SS')";
RESTORE DATABASE;
SWITCH DATAFILE ALL;
}
EXIT

7.Recover database to point in time.

$rman target / nocatalog
RMAN>recover database until time "to_date(''2019-11-03:41:54:00'','YYYY-MM-DD:HH24:MI:SS')";

8.Rename redo logfile name before opening DB with resetlogs option.

$sqlplus / as sysdba
SQL>alter database rename file '/apps1/u14/oradata/DEV/redo01a.log' to '/apps1/DEV/redo01a.log';

Do same for all the redo files.( run select member from v$logfile to get list of redo logs )

9.Open DB with resetlogs option.

$sqlplus / as sysdba
SQL>alter database open resetlogs;

10.Create TEMP tablespace.

$sqlplus / as sysdba
SQL>create temporary tablespace temp1 tempfile '/apps1/DEV/temp.dbf' size 10M;
SQL>alter database default temporary tablespace temp1;

Reading time: 3 - 4 minutes

Today happed had a situation where i were called by my client and informed about slow generation of report.

 

Client was using some SQL which was selecting some data from few tables to generate reports.

 

I used the following query to find out how much it used to take in past.

 

set lines 155
col execs for 999,999,999
col avg_etime for 999,999.999
col avg_lio for 999,999,999.9
col begin_interval_time for a30
col node for 99999
break on plan_hash_value on startup_time skip 1
select ss.snap_id, ss.instance_number node, begin_interval_time, sql_id, plan_hash_value,
nvl(executions_delta,0) execs,
(elapsed_time_delta/decode(nvl(executions_delta,0),0,1,executions_delta))/1000000 avg_etime,
(buffer_gets_delta/decode(nvl(buffer_gets_delta,0),0,1,executions_delta)) avg_lio
from DBA_HIST_SQLSTAT S, DBA_HIST_SNAPSHOT SS
where sql_id = nvl('&sql_id','4dqs2k5tynk61')
and ss.snap_id = S.snap_id
and ss.instance_number = S.instance_number
and executions_delta > 0
order by 1, 2, 3
/

 

As per the output i can see that SQL has changed the execution plan from 2PM on 29th JUL 10.

 

As per the Oracle documentation , i decided to use SQL Tuning Advisor

 

SQL Tuning Advisor has three steps.

1. Create Tuning Task    ( DBMS_SQLTUNE.CREATE_TUNING_TASK )
2. Execute Tuning Task  ( DBMS_SQLTUNE.EXECUTE_TUNING_TASK )
3. Report Tuning Task    ( DBMS_SQLTUNE.REPORT_TUNING_TASK )

I created SQL Tuning task using following script.

SET LONG 10000;
SET PAGESIZE 9999
SET LINESIZE 155
set verify off
col recommendations for a150
accept task_name -
       prompt 'Task_Name: '
DECLARE
ret_val VARCHAR2(4000);

BEGIN

ret_val := dbms_sqltune.create_tuning_task(task_name=>'&&Task_name', sql_id=>'&sql_id', time_limit=>&time_limit);

dbms_sqltune.execute_tuning_task('&&Task_name');

END;
/
SELECT DBMS_SQLTUNE.report_tuning_task('&&task_name') AS recommendations FROM dual;
undef task_name

Tuning Advisor's Primary recommendations are often to accept a SQL Profile but there may be other recommendations such as creating indexes, etc.

As per my past experience never ever accept recommendation blindly else you may be in problem.

 

SQL tuning advisor suggested SQL profile and i accepted it and it improved performance dramatically.

 

 

Script Source:

http://kerryosborne.oracle-guy.com/2008/09/sql-tuning-advisor/

Reading time: < 1 minute

[oracle@db1 ~]$ export ORACLE_HOME=/u01/app/oracle/product/11.2.0/db

[oracle@db1 ~]$ export PATH=$ORACLE_HOME/bin:$PATH

[oracle@db1 ~]$ dbca -silent -createDatabase -templateName General_Purpose.dbc -gdbName orcl.sysdbaonline.com -sysPassword oracle -systemPassword oracle -emConfiguration NONE -datafileDestination /u01/app/oracle/oradata -storageType FS

Reading time: 3 - 5 minutes

Setup Apply process to continue in the event of error.

Begin

DBMS_APPLY_ADM.SET_PARAMETER(apply_name => 'APPLY_LONDON',parameter => 'disable_on_error', value => 'n');
end;
/

You need to start Apply process manually if it was stopped due to error.

BEGIN
DBMS_APPLY_ADM.START_APPLY(apply_name => 'apply_london');
END;

/

Apply troubleshooting

Apply Parameters


select * from DBA_APPLY_PARAMETERS';

Apply rules

select DBA_RULES.* from dba_apply, DBA_RULE_SET_RULES, DBA_RULES
where DBA_RULE_SET_RULES.rule_set_name (+)= dba_apply.rule_set_name
and DBA_RULES.rule_name (+)= DBA_RULE_SET_RULES.rule_name;

Apply status

SELECT dba_apply.*, sysdate FROM DBA_apply where apply_name = 'APPLY_LONDON';
select * from dba_rules where upper(rule_condition) like upper('%LONDON%');

How to delete it?

execute DBMS_APPLY_ADM.STOP_APPLY(apply_name => 'apply_london');
execute DBMS_APPLY_ADM.DROP_APPLY(apply_name => 'apply_london');

Errors in apply?

Is the apply process running?

Remember to re-execute pending errors before restarting the apply

--First check sequence

select * from dba_apply;
select * from dba_apply_error;

execute DBMS_APPLY_ADM.EXECUTE_All_ERRORS;

select * from dba_apply_error;

execute DBMS_APPLY_ADM.START_APPLY(apply_name => 'APPLY_london');

select * from DBA_APPLY_PROGRESS;

#Check the handler exists

SELECT * FROM DBA_APPLY_DML_HANDLERS WHERE APPLY_DATABASE_LINK IS NULL ORDER BY OBJECT_OWNER, OBJECT_NAME;

#What is on alert log if the apply stop due to an exception?

Tue Jul 20 15:11:16 BST 2010
Streams APPLY A001 with pid=26, OS id=24429 stopped

Errors in file /u01/app/dev/admin/californ/bdump/californ_p001_26782.trc:
ORA-00942: table or view does not exist

#You also find a trace in bdump in the form californ_p001_26782.trc

#You may resubmit the failed apply using:

begin
DBMS_APPLY_ADM.EXECUTE_ERROR(local_transaction_id => '34.11.1437');
end;

or

execute DBMS_APPLY_ADM.EXECUTE_All_ERRORS;

#The apply process stopped for a full tbs, now the tbs is ok how many messages are now to be dequeued?

SELECT s.SUBSCRIBER_NAME, q.QUEUE_SCHEMA, q.QUEUE_NAME, s.LAST_DEQUEUED_SEQ,
s.NUM_MSGS, s.TOTAL_SPILLED_MSG
FROM V$BUFFERED_QUEUES q, V$BUFFERED_SUBSCRIBERS s, DBA_APPLY a
WHERE q.QUEUE_ID = s.QUEUE_ID AND s.SUBSCRIBER_ADDRESS IS NULL AND s.SUBSCRIBER_NAME = a.APPLY_NAME;

#If the apply process as stopped due to an exception
#YOU MUST EXECUTE ERRORS BEFORE STARTING IT AGAIN
#the error queue must be be empty oterwhise new changes are applied and lead to incinsistency
#Data on destination has been manually repaired, how to clear to error queue?

BEGIN
--CAUTION!!! Any pending LCR will be deleted
delete from SYS.apply$_error;
commit;
execute immediate('alter system flush shared_pool');
DBMS_APPLY_ADM.START_APPLY(apply_name => 'APPLY_london');
END;

#Is there an apply history(10g)?

select * from DBA_HIST_STREAMS_APPLY_SUM;

Reading time: 1 - 2 minutes

Sometimes it is necessary to automatically trigger SQL trace. Automatically here means that code must be added somewhere.

The simplest approach is to create a logon trigger at the database level. To avoid enabling SQL trace for all users, I usually suggest creating a role (named sqltrace in the following example) and temporarily granting it only to the user utilized for the test.

CREATE ROLE sqltrace;

CREATE OR REPLACE TRIGGER enable_sqltrace AFTER LOGON ON DATABASE
BEGIN
IF (dbms_session.is_role_enabled('SQLTRACE'))
THEN
EXECUTE IMMEDIATE 'ALTER SESSION SET timed_statistics = TRUE';
EXECUTE IMMEDIATE 'ALTER SESSION SET max_dump_file_size = unlimited';
dbms_monitor.session_trace_enable;
END IF;
END;
/

So whenever user with sqltrace role logs in sql tracing will be started by above trigger.