Creating 10g Data Guard

Environment

Primary host : erlach
Standby host : erpsbx
ORACLE_SID=MTD
Kernel :Solaris 10 (Sparc) 64 Bit
Service names : Primary MTD / Standby MTDDG

Data Guard Creation Steps:
1. Enable Force Logging at Primary
SQL>Alter database force logging;
2. Initialization Parameters

Primary Initialization parameters

##COMMON TO BOTH PRIMARY AND STANDBY ROLES
LOG_ARCHIVE_CONFIG='DG_CONFIG=(MTD,MTDDG)'
LOG_ARCHIVE_DEST_1='LOCATION=/oracle/MTD/arch VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=MTD'
LOG_ARCHIVE_DEST_2='SERVICE=MTDDG VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=MTDDG'
LOG_ARCHIVE_DEST_STATE_1=ENABLE
LOG_ARCHIVE_DEST_STATE_2=ENABLE
LOG_ARCHIVE_MAX_PROCESSES=2
#log_archive_format='%t_%s_%r.dbf'
#SPECIFIC TO STANDBY ROLE
STANDBY_FILE_MANAGEMENT=AUTO
STANDBY_ARCHIVE_DEST='/oracle/MTD/arch'
FAL_SERVER=MTDDG
FAL_CLIENT=MTD

Standby Initialization parameters
db_name='MTD'
db_unique_name=MTDDG
#COMMON TO BOTH PRIMARY AND STANDBY ROLES
LOG_ARCHIVE_CONFIG='DG_CONFIG=(MTD,MTDDG)'
LOG_ARCHIVE_DEST_1='LOCATION=/oracle/MTD/arch
VALID_FOR=(ALL_LOGFILES,ALL_ROLES)
DB_UNIQUE_NAME=MTD'
LOG_ARCHIVE_DEST_2='SERVICE=MTDDG ARCH
VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE)
DB_UNIQUE_NAME=MTDDG'
LOG_ARCHIVE_DEST_STATE_1=ENABLE
LOG_ARCHIVE_DEST_STATE_2=ENABLE
LOG_ARCHIVE_MAX_PROCESSES=2
log_archive_format='%t_%s_%r.dbf'
#SPECIFIC TO STANDBY ROLE
STANDBY_FILE_MANAGEMENT=AUTO
STANDBY_ARCHIVE_DEST='/oracle/MTD/arch'
FAL_SERVER=MTDDG
FAL_CLIENT=MTD

Steps for init.ora:
1) Use the commands below on the primary database:
alter system set LOG_ARCHIVE_CONFIG='DG_CONFIG=(MTD,MTDDG)' scope=both;
alter system reset log_archive_dest scope=both sid='MTD';
ALTER SYSTEM SET LOG_ARCHIVE_DEST_STATE_1 = 'enable' scope=both;
alter system set LOG_ARCHIVE_DEST_1='LOCATION=/oracle/MTD/arch VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=MTD MANDATORY' scope=both;
alter system set LOG_ARCHIVE_DEST_2='SERVICE=MTDDG ARCH REOPEN=60 NOAFFIRM VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=MTD' scope=both;
alter system set LOG_ARCHIVE_DEST_STATE_2=enable scope=both;
alter system set LOG_ARCHIVE_MAX_PROCESSES=15 scope=both;
alter system set FAL_SERVER=MTDDG scope=both;
alter system set FAL_CLIENT=MTD scope=both;
alter system set STANDBY_FILE_MANAGEMENT=AUTO scope=both;
alter system set STANDBY_ARCHIVE_DEST='/oracle/MTD/arch' scope=both;
2) Create a pfile for transferring to the standby site:
Sql> create pfile=’initMTD.ora’ from spfile;
3) Go to the $ORACLE_HOME/dbs directory and copy the initMTD.ora in the backup directory which you will be transferring to the standby site along with the password file and standby control file

3. Net Services :

TNSNAMES.ORA sample
- On primary
MTD =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = erpsbx)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = MTD)
)

MTDDG =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 10.175.244.188)(PORT = 1527))
)
(CONNECT_DATA =
(SERVICE_NAME = MTD)
)
)

- On standby
MTD =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 10.175.244.188)(PORT = 1527))
)
(CONNECT_DATA =
(SERVICE_NAME = MTD)
)
)
MTDDG =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST =erpsbx)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = MTD)
)
)

Listener.ora on standby:
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(SID_NAME = MTD)
(ORACLE_HOME = /oracle/SMD/102_64)
)
)

LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1))
(ADDRESS = (PROTOCOL = TCP)(HOST = 10.175.244.188)(PORT = 1527))
)
)

4. Standby Redo Log (SRL) creation :
Create the SRL's :
Use the query below to find out all the standby redo logs that are required to be created:

select 'alter database add standby logfile '''||
regexp_substr(MEMBER,'/.+/')||'stdby_'||
regexp_replace(member,regexp_substr(MEMBER,'/.+/'),'')||
''' size '||bytes||';' "Create Standby redo"
from v$logfile lf , v$log l
where l.group# = lf.group#
union all
select 'alter database add standby logfile '''||
regexp_substr(MEMBER,'/.+/')||'stdby_redo0'||(select max(group#)+1 from v$log)||'.rdo'||
''' size '||bytes||';' "Create Standby redo"
from v$logfile lf , v$log l
where l.group# = lf.group#
and rownum <=2
/
alter database add standby logfile '/oracle/MTD/oralogs/stdby_redo_g13m1.dbf' size 52428800;
alter database add standby logfile '/oracle/MTD/oralogs/stdby_redo_g13m2.dbf' size 52428800;
alter database add standby logfile '/oracle/MTD/oralogs/stdby_redo_g12m1.dbf' size 52428800;
alter database add standby logfile '/oracle/MTD/oralogs/stdby_redo_g12m2.dbf' size 52428800;
alter database add standby logfile '/oracle/MTD/oralogs/stdby_redo_g11m1.dbf' size 52428800;
alter database add standby logfile '/oracle/MTD/oralogs/stdby_redo_g11m2.dbf' size 52428800;
alter database add standby logfile '/oracle/MTD/oralogs/stdby_redo04.rdo' size 52428800;
alter database add standby logfile '/oracle/MTD/oralogs/stdby_redo04.rdo' size 52428800;

5.Backup the Primary
Take a backup of the primary. ( You can take a hot backup or oracle recommends the usage of RMAN.)
- Backup the datafiles, online redologs and the standby logs if created and ftp / restore it on the standby site;

6.Create the standby controlfile :

Primary :

SQL>ALTER DATABASE CREATE STANDBY CONTROLFILE AS '/tmp/stdby.ctl';

- ftp / scp the stdby.ctl file to the standby site. we can multiplex this by making copies of this stdby.ctl on different mountpoints on the standby and editing the control_files parameter in the init.ora to reflect this.
- Copy the orapw.ora file from the OH/dbs directory of the primary to the same location on the standby.
- Copy all the archives generated since the start of the backup.
- Create all the directory structures as required for the standby database.
- Also copy the password file from the primary
- Create pfile from spfile and also add to the list of files to be ftped.
SQL> create pfile='initMTD.ora' from spfile;

Verify Connectivity both ways:
We need to verify connectivity from primary to standby and standby to primary. This can be done by making a remote connection as sys:

On primary:
Sqlplus sys/@mtddg as sysdba (this can be done by opening the standby database in read only mode. See below for instructions).
Select host_name from v$instance; (should return the standby host name)
On standby:
Sqlplus sys/@mtd as sysdba
Select host_name from v$instance; (should return the primary host name)
This should result in connecting to the remote site as sys.

7. Starting the standby :
SQL>STARTUP MOUNT

SQL>ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT;
Remember: If you need to open the standby for any reason it should be done in read only mode:
SQL>ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;

Using Plan Stability - Stored Outlines

Using Plan Stability (Outlines)


Background:

Customer has been using a custom program which runs a complex select statement and then creates a CSV file. This program has been running for the last 2 years with no issues. It used to run within 5-6 minutes

Problem Statement:

On a weekend the database was bounced for some 3Par parameter changes while the program was running. After bringing the database up, the program ran for longer time without completing. Primary DBA had to cancel the job, run GSS with 30% and try it again. It ran for almost 12 hours without completing. On Dev and Preprod databases the same process took the normal 5 minutes time. Explain plan showed that the plan on Dev and Prod were different. During the week the issue got escalated and came to me.

Analysis:

I did the initial analysis and confirmed that the plan was different in Dev and Prod. Getting level 12 trace was not feasible on Prod as the program was not finishing within reasonable time.
It was decided to test and implement “Plan Stability” via Stored Outlines.

Creating Stored Outlines:

1) Grant necessary privileges to the user where stored outlines will be generated. In our case it is the user APPS.

GRANT CREATE ANY OUTLINE TO APPS;
GRANT EXECUTE_CATALOG_ROLE TO APPS;

2) Now connect to APPS and set the session level parameter which will help generate outlines:

ALTER SESSION SET create_stored_outlines=TRUE;

3) The actual creation of the outlines can be done in two ways
i) Using Create Outline command
ii) Using DBMS_OUTLN package

i) Using Create Outline Command:
CREATE OUTLINE tt_prof FOR CATEGORY apps_outlines
ON ;

Check if outline has been created successfully:
SELECT name, category, sql_text FROM user_outlines WHERE category = 'APPS_OUTLINES';

Check the hints associated with the outline:
SELECT node, stage, join_pos, hint FROM user_outline_hints WHERE name = 'TT_PROF';

ii) Using DBMS_OUTLN Package:
Run the SQL statement once to get the statement in the shared pool. After running check the shared pool for the following information:

SELECT hash_value, child_number, sql_text FROM v$sql WHERE sql_text LIKE '%'

Create the stored outline:

BEGIN
DBMS_OUTLN.create_outline(
hash_value => 3909283366,
child_number => 0,
category => 'APPS_OUTLINES');
END;
/

Check if outline has been created successfully:
SELECT name, category, sql_text FROM user_outlines WHERE category = 'APPS_OUTLINES';

Check the hints associated with the outline:
SELECT node, stage, join_pos, hint FROM user_outline_hints WHERE name = '’;

4) Turn off the outline creation for the session using:
ALTER SESSION SET create_stored_outlines=FALSE;


Exporting Stored Outlines:

Stored outlines needed to be exported to be used in the prod database. We need to export two tables (OL$ and OL$HINTS) for successfully using stored outlines in PROD. The command for export is:


exp userid=outln/outln file=outln.dmp tables=ol\$,ol\$hints query=\”where ol_name= \’\’ \”

Once the export file is available, FTP it to the PROD database server and then import it to the PROD database using:

***NOTE: PLEASE BE CAREFUL IN USING THIS STEP. MAKE SURE THAT IF DATA ALREADY EXISTS IN THESE TWO TABLES THEN IT SHOULD BE BACKED UP AND THEN RESEEDED AFTER IMPORT. PERFORMANCE TEAM IS NOT RESPONSIBLE IF THE DATA IS NOT BACKED UP PROPERLY

imp userid=outln/outln file=outln.dmp full=y ignore=y



Using Stored Outlines:

In order to use stored outlines the following parameter must be enabled at an instance or session level:

ALTER SESSION SET query_rewrite_enabled=TRUE;
ALTER SESSION SET use_stored_outlines=APPS_OUTLINES;

Now run your query in PROD. We can check if the query used the stored outline or not:

SELECT name, category, used FROM user_outlines;

Outcome:

Using Stored Outline brought back the response time to the original 5-6 minutes.

Conclusion:

When resolving issue quickly is the key and when initial analysis shows a discrepancy in the explain plans between different environments for the same query, stored outline can be used to force the query to use the more efficient plan. 11g uses sql profiles and I will discuss that in a later post

Hello World!

SET SERVEROUTPUT ON
DECLARE
lv_text varchar2(100);
BEGIN
lv_text:='Hello World! I am finally here in Blogosphere. More on Oracle, my Career and my passion to follow');
dbms_output.put_line(lv_text);
END;
/