One is All You Need

Only available in 32bit windows and not 64bit!! Review below on why.

Step 1: Create an ODBC connection for SQL Server on the Oracle box . • Start -> programs ->administrative tools -> data sources (ODBC) Click add and create the data source.

–> Click next – (Put the SQL Server login id and password)

–> Click next – (Select the database you would like to connect to)

–> Click next – (Options you can use in your ODBC connection)

–> Click finish – (make sure you test the data source. Click this button at the bottom)

–> Hopefully you see this below.

Step 2: Create a Heterogeneous Services Initialization File.

Oracle provides a sample in the ‘E:\oracle\product\db_1\hs\admin’ edit that and rename it. We named ours inithsEndurACC53.ora. Below is the lines we inserted in.

# This is a sample agent init file that contains the HS parameters that are

# needed for an ODBC Agent.

#

# HS init parameters

#

HS_FDS_CONNECT_INFO = EndurACC53

HS_FDS_TRACE_LEVEL = off

#

# Environment variables required for the non-Oracle system

#

#set <envvar>=<value>

Step 3: Alter the listener.ora to represent this new HS service. (Here is how ours was configured)

# listener.ora Network Configuration File: #E:\oracle\product\db_1\NETWORK\ADMIN\listener.ora

# Generated by Oracle configuration tools.

SID_LIST_LISTENER =

(SID_LIST =

(SID_DESC =

(SID_NAME = PLSExtProc)

(ORACLE_HOME = E:\oracle\product\db_1)

(PROGRAM = extproc)

)

(SID_DESC =

(GLOBAL_DBNAME = ENDURDEV)

(ORACLE_HOME = E:\oracle\product)

(SID_NAME = ENDURDEV)

)

)

LISTENER =

(DESCRIPTION_LIST =

(DESCRIPTION =

(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1))

)

(DESCRIPTION =

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

)

)

LISTENERENDURACC53 =

(ADDRESS_LIST=

(ADDRESS=(PROTOCOL=tcp)(HOST= BOSORADEV60)(PORT=1526))

(ADDRESS=(PROTOCOL=ipc)(KEY=PNPKEY)))

SID_LIST_LISTENEREndurACC53=

(SID_LIST=

(SID_DESC=

(SID_NAME=EndurACC53)

(ORACLE_HOME = d:\oracle\product\10.1.0\db_1)

(PROGRAM=hsodbc)

)

)

Open a command window and issue the commands below. This will start both listeners. Remember the HS service has to be listening on a separate port.

P:\>lsnrctl reload

LSNRCTL for 64-bit Windows: Version 10.2.0.3.0 - Production on 12-JUN-2008 10:52

:48

Copyright (c) 1991, 2006, Oracle. All rights reserved.

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC1)))

The command completed successfully

P:\>lsnrctl status

LSNRCTL for 64-bit Windows: Version 10.2.0.3.0 - Production on 12-JUN-2008 10:52

:51

Copyright (c) 1991, 2006, Oracle. All rights reserved.

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC1)))

STATUS of the LISTENER

————————

Alias LISTENER

Version TNSLSNR for 64-bit Windows: Version 10.2.0.3.0 - Produ

ction

Start Date 06-JUN-2008 09:48:57

Uptime 6 days 1 hr. 3 min. 54 sec

Trace Level off

Security ON: Local OS Authentication

SNMP OFF

Listener Parameter File E:\oracle\product\db_1\network\admin\listener.ora

Listener Log File E:\oracle\product\db_1\network\log\listener.log

Listening Endpoints Summary…

(DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(PIPENAME=\\.\pipe\EXTPROC1ipc)))

(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=BOSORADEV60)(PORT=1521)))

Services Summary…

Service “ENDURDEV” has 2 instance(s).

Instance “ENDURDEV”, status UNKNOWN, has 1 handler(s) for this service…

Instance “endurdev”, status READY, has 1 handler(s) for this service…

Service “ENDURDEVXDB” has 1 instance(s).

Instance “endurdev”, status READY, has 1 handler(s) for this service…

Service “ENDURDEV_XPT” has 1 instance(s).

Instance “endurdev”, status READY, has 1 handler(s) for this service…

Service “PLSExtProc” has 1 instance(s).

Instance “PLSExtProc”, status UNKNOWN, has 1 handler(s) for this service…

The command completed successfully

P:\>lsnrctl start listenerenduracc53

LSNRCTL for 64-bit Windows: Version 10.2.0.3.0 - Production on 12-JUN-2008 10:53

:07

Copyright (c) 1991, 2006, Oracle. All rights reserved.

Starting tnslsnr: please wait…

TNSLSNR for 64-bit Windows: Version 10.2.0.3.0 - Production

System parameter file is E:\oracle\product\db_1\network\admin\listener.ora

Log messages written to E:\oracle\product\db_1\network\log\listenerenduracc53.lo

g

Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=127.0.0.1)(PORT=1526)))

Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(PIPENAME=\\.\pipe\PNPKEYipc))

)

Connecting to (ADDRESS=(PROTOCOL=tcp)(HOST=localhost)(PORT=1526))

STATUS of the LISTENER

————————

Alias listenerenduracc53

Version TNSLSNR for 64-bit Windows: Version 10.2.0.3.0 - Produ

ction

Start Date 12-JUN-2008 10:53:13

Uptime 0 days 0 hr. 0 min. 5 sec

Trace Level off

Security ON: Local OS Authentication

SNMP OFF

Listener Parameter File E:\oracle\product\db_1\network\admin\listener.ora

Listener Log File E:\oracle\product\db_1\network\log\listenerenduracc53.

log

Listening Endpoints Summary…

(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=127.0.0.1)(PORT=1526)))

(DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(PIPENAME=\\.\pipe\PNPKEYipc)))

Services Summary…

Service “EndurACC53″ has 1 instance(s).

Instance “EndurACC53″, status UNKNOWN, has 1 handler(s) for this service…

The command completed successfully

Step 4: Edit TNSNAMES.ora to represent the new SQL Server database. Below is the entry we created.

EndurACC53 =

(DESCRIPTION=

(ADDRESS=(PROTOCOL=tcp)(HOST=BOSORADEV60)(PORT=1526))

(CONNECT_DATA=(SID=ENDURACC53))

(HS=OK)

)

Step 5: Do a quick TNSPING to verify you are reaching the SQL Server database.

P:\>tnsping enduracc53

TNS Ping Utility for 64-bit Windows: Version 10.2.0.3.0 - Production on 12-JUN-2

008 11:03:56

Copyright (c) 1997, 2006, Oracle. All rights reserved.

Used parameter files:

E:\oracle\product\db_1\network\admin\sqlnet.ora

Used TNSNAMES adapter to resolve the alias

Attempting to contact (DESCRIPTION= (ADDRESS=(PROTOCOL=tcp)(HOST=BOSORADEV60)(PORT=1526)) (CONNECT_DATA=(SID=ENDURACC53)) (HS=OK))

OK (20 msec)

Step 6: Create the database link to the SQL Server database.

sql> create public database link enduracc53 connect to enduracc53 identified by ******;

Database link created

Step 7: Validate the SQL Server db link by desc or selecting from a table from that location.

SQL> desc <tablename>@enduracc53.

NOTE: This only works in 32-bit windows and not 64-bit windows. Review the note below stating ODBC does not work on 64-bit Oracle.

If you look at the BIN directory in 64-bit windows you will notice the HSODBC.exe is not there. So when you go to a command line and type HSODBC you get:

P:\>hsodbc

‘hsodbc’ is not recognized as an internal or external command,

operable program or batch file.

What you should see is:

C:\ hsodbc

Oracle Corporation — THURSDAY  JUN 12 2008 13:33:34.658

Heterogeneous Agent Release 10.2.0.1.0 - Production  Built with

   Driver for ODBC
No HSODBC in the bin directory in 64-bit. Review below:




Please review note below. This will not

be available until a future release
http://forums.oracle.com/forums/thread.jspa?messageID=2585221&tstart=0
 

Purpose
The purpose of this document is to give an explanation of how to get the module/program information to link with the dba_fga_audit_table. When you create an FGA policy the audit table does not list program or module in the table. The DBA will not be able to see or audit what program the user is executing under. If our environment wants to make sure all the users are only using approved applications to interact with the database, we will have to do the example below to get this information.

Sql> desc dba_fga_audit_table;

Name Type

SESSION_ID NUMBER
TIMESTAMP
DB_USER VARCHAR2(30)
OS_USER VARCHAR2(255)
USERHOST VARCHAR2(128)
CLIENT_ID VARCHAR2(64)
ECONTEXT_ID VARCHAR2(64)
EXT_NAME VARCHAR2(4000)
OBJECT_SCHEMA VARCHAR2(30)
OBJECT_NAME VARCHAR2(128)
POLICY_NAME VARCHAR2(30)
NUMBER
SQL_TEXT NVARCHAR2(2000)
SQL_BIND NVARCHAR2(2000)
COMMENT$TEXT VARCHAR2(4000)
STATEMENT_TYPE VARCHAR2(7)
EXTENDED_TIMESTAMP TIMESTAMP(6)
PROXY_SESSIONID NUMBER
GLOBAL_UID VARCHAR2(32)
INSTANCE_NUMBER NUMBER
OS_PROCESS VARCHAR2(16)
STATEMENTID NUMBER
ENTRYID NUMBER

dba_fga_audit_table does not have module/program in the table. So we have to add the code below to get that information into a separate table to link back to the FGA table.

Code:
– Verify that module is the information that we are looking for.

select sys_context(’userenv’,'MODULE’) from dual;

– Module returns the program as expected.

– Create table to store SESSSION ID and MODULE to link to the dba_fga_audit_log

drop table “LOGONUSER”.”LOGON_FGA”;

CREATE TABLE “LOGONUSER”.”LOGON_FGA”
( “SESSIONID”NUMBER NOT NULL,
“MODULE” VARCHAR2(150)
)
TABLESPACE “LOGON_TBS”;

— Only do below if you are going to run reporting against the FGA table.

GRANT SELECT ON “LOGONUSER”.”LOGON_FGA” TO PUBLIC;

CREATE OR REPLACE PUBLIC SYNONYM “LOGON_FGA” FOR “LOGONUSER”.”LOGON_FGA”;

– Create trigger to store LOGON information from sys_context (SessionID and MODULE) In query you will link sessionid with session_id in FGA table

drop trigger logon_audit_trigger;
create or replace trigger
logon_audit_trigger
AFTER LOGON ON DATABASE
BEGIN
insert into LOGON_FGA values(
sys_context(’USERENV’,'SESSIONID’),
sys_context(’USERENV’,'MODULE’)
);
END;
/
show errors

End of Code!

— Run query to verify that the information is what you are looking for.

select a.sessionid, a.module, b.timestamp, b.db_user, b.os_user, b.object_name, b.policy_name, b.sql_text, b.sql_bind
from LOGON_FGA a, dba_fga_audit_trail b where b.session_id = a.sessionid;

Output:

2921579 plsqldev.exe 3/20/2008 9:46:34 AM TEST Roger?Lenihan PRICE_HOURLY_FACT PRICE_AUDIT “select * from price_hourly_fact where rownum < 1000

”
2921641 toad.exe 3/20/2008 9:57:26 AM TEST
Roger?Lenihan PRICE_HOURLY_FACT PRICE_AUDIT “select * from price_hourly_fact where rownum < 10 ”

As you can see I made connections via Toad and PL/SQL developer to verify that the trigger works and is logging the correct information. The application is working as planned.

Conclusion
The dba_fga_audit_table does not give information about what application the user is coming in under. With a LOGON trigger using the sys_context function we are able to get the needed information. In most organization having the application along with the SQL proves to be very useful in getting the full picture of the user as they interact with the database.

 Overview

In this example, we will demonstrate how Ajax can be use to efficiently populate a select list item in an Apex page on the fly “at run time”. Of course, this can be also done by using some simple JavaScript code BUT Ajax will be more efficient because it does not require reloading the whole Apex page every time the JavaScript event is fired. On the other hand, using just JavaScript will require the entire Apex page to reload, which is not a good practice in today’s dynamic world.

Imagine an Apex form that contains 100 items (Text fields, Select lists, Radio buttons, Check boxes), and that there is a JavaScript event defined for one item. The event dynamically sets the value of another item in that form. Every time this event is fired, Apex will need to reload the entire form and re-populate all fields with their old values (if any); this can be time consuming and may also cause some issues “side effects”.

 Example:

Consider the following sample table that basically contains data about movies, and categorize movies into different Genres.

	MOVIE_ID	GENRE_ID	GENRE	MOVIE_NAME
	1	1	Action	Romeo Must Die
	2	2	Comedy	Meet Dave
	3	2	Comedy	Kit Kittredge an American Girl
	4	1	Action	Wanted
	5	3	Drama	Hancock
	6	1	Action	Dark Knight

     row(s) 1 - 6 of 6

 Requirements:

 We need to create a simple Apex page with the following items:
- A select list that contains all the movies (Movie_name)
- A select list that contains the movies’ genres (Genre)
- A text field that represents the genre ID ( example: 1,2 or 3) .

The behavior of these items should be as follows:
- When the value of the Genre select list is changed, the Movies’ select list is auto populated with the corresponding movie(s) that belong to the selected genre.
-When the value of the Genre ID Text field is changed (1,2 or 3), the Movies’ select list is auto populated with the corresponding movie(s) that belong to the selected genre ID.

Implementation: 

1) Create the Genre ID text field (P1_GENRE_ID) and add the following code to the HTML Form Element
    Attributes:

     onKeyUp=“get_List_XML (this,’ P1_MOVIE_LIST ‘)”  

2) Create the Genre select list (P1_GENRE_LIST)  and add the following code to the HTML Form Element   
       Attributes:    
    
     onchange=“get_List_XML (this,’P1_MOVIE_LIST‘)”   

    And this code to the list of values definition:

    select distinct  genre display_val,genre_id return_val
    from movies

 3) Create the Movies select list (P1_MOVIE_LIST) and add the following code to the list
    of values definition:

    select movie_name, movie_id
   from movies
   where genre_id= :P1_GENRE_ID

4) Create an On Demand Process (POPULATE_LIST_XML) as follows:

declare 
l_counter number;  
l_o_name  varchar2(2000);  
begin  
    owa_util.mime_header(’text/xml’, FALSE );   
    htp.p(’Cache-Control: no-cache’);   
    htp.p(’Pragma: no-cache’);   
    owa_util.http_header_close;  
    htp.prn(’<select>’);  
     for rec in (select “MOVIES”.”GENRE” as “GENRE”,   
      “MOVIES”.”MOVIE_NAME” as “MOVIE_NAME”,   
      “MOVIES”.”MOVIE_ID” as “MOVIE_ID”   
       from “MOVIES” “MOVIES” 
       where “MOVIES”.”GENRE_ID” = :TEMP_ITEM)  
      loop  
       htp.prn(’<option value=”‘ || rec.movie_id || ‘”>’ || rec.movie_name || ‘</option>’);  
     end loop;   
      htp.prn(’</select>’);  
end;

5) Add the following JavaScript code to the source of any exiting HTML region in the page:

 <center><b>This example demonstrates the use of Ajax to dynamically populate a select list on the fly.</center><br>

Type in the Genre ID in the Genre ID text field or select a Genre from the Genre select list <br> to automatically populate the Movies list (( without re-loading this page! ))

<br><br>
<script language=”JavaScript1.1″ type=”text/javascript”>   
 function get_List_XML (source_item,target_item){    
    var v_Target = html_GetElement(target_item);
    var v_Source = html_GetElement(source_item);
    var ajaxResult = new htmldb_Get
    (null,&APP_ID.,’APPLICATION_PROCESS=POPULATE_LIST_XML’,0);  
    ajaxResult.add(’TEMP_ITEM’,source_item.value);  

    var v_result_xml = ajaxResult.get(’XML’);  
    if(v_Source == document.getElementById(’P1_GENRE_ID’) )
    document.getElementById(’P1_GENRE_LIST’).selectedIndex=0;
    else
    document.getElementById(’P1_GENRE_ID’).value=”;

    if(v_result_xml && v_Target){
    var options_Contents = v_result_xml.getElementsByTagName(”option”);
    v_Count = options_Contents.length;
    v_Target.length = 0;     for(var i=0;i<v_Count;i++) {
      var v_opt_xml = v_result_xml.getElementsByTagName(”option”)[i];   
      writeToSelectList (v_Target, v_opt_xml.getAttribute
      (’value’),v_opt_xml.firstChild.nodeValue)   }   
    }   
 }  

   function writeToSelectList(target_item, option_val, option_content) {   
    var v_Opt = document.createElement(”option”);   
    v_Opt.option_value = option_val;  

    if(document.all){ 
        target_item.options.add(v_Opt);   
        v_Opt.innerText = option_content;   
     } else {   
       v_Opt.appendChild(document.createTextNode(option_content));   
       target_item.appendChild(v_Opt);   
    }   
  }  
</script>

You can run the Apex page for this example by visiting my online Apex account. 

I got a little press in the July/August 2008 edition of Oracle magazine about my presentation at Collaborate and I thought it would be prudent to shamelessly refer you to my presentation. Additionally, I will be presenting this same topic at the Northern California Oracle Users Group Summer Conference on August 21 in San Ramon.

Aces in the Hole

Greg

Greg Pike

Step by Step Guide for Installing a Data Guard/ Data Guard Broker Environment

This focuses on setting up a test environment on a single node. The DBA can adapt this to set up a production environment.

By Roger Lenihan

Step 1:

Install an Oracle database and validate it is in Archive log Mode.

If Archive log is disabled enable it before continuing.

SQL> archive log list

Database log mode             Archive Mode

Automatic archival            Enabled

Archive destination           C:\oracle\product\10.2.0\archive

Oldest online log sequence    66

Next log sequence to archive  68

Current log sequence          68

Step 2:

Enable force logging mode.

SQL> alter database force logging;

Database altered.

Step 3:

Identify all the datafiles to copy for a cold back up or do an RMAN backup.

Since this is test environment I will shutdown the database and do a cold backup.

SQL> select name from v$datafile;

NAME

———————————————————————-

C:\ORACLE\PRODUCT\10.2.0\ORADATA\TEST\SYSTEM01.DBF

C:\ORACLE\PRODUCT\10.2.0\ORADATA\TEST\UNDOTBS01.DBF

C:\ORACLE\PRODUCT\10.2.0\ORADATA\TEST\SYSAUX01.DBF

C:\ORACLE\PRODUCT\10.2.0\ORADATA\TEST\USERS01.DBF

C:\ORACLE\PRODUCT\10.2.0\ORADATA\TEST\EXAMPLE01.DBF

SQL>

Step 4:

In a windows environment create the service for the standby database. You will also have to create the folders and environment for the standby database.

C:\>oradim -new -sid stby -intpwd teststby -startmode manual

C:\oracle\product\10.2.0\oradata\teststby – Create this folder

C:\oracle\product\10.2.0\admin\teststby – Create this folder and all the folders underneath. Like bdump, udump, etc. Or just copy the production one.

Note: Since this is a dataguard environment on a single test node I do not have to install all the Oracle software again.

Step 5:

Shutdown Primary database and move the datafiles

SQL> shutdown immediate;

Database closed.

Database dismounted.

ORACLE instance shut down.

Move the datafiles from step 3 into the oradata standby environment.

C:\oracle\product\10.2.0\oradata\test

C:\oracle\product\10.2.0\oradata\teststby

Step 6:

Create a standby control file and start the database

SQL> startup mount;

SQL> ALTER DATABASE CREATE STANDBY CONTROLFILE AS

    ‘ C:\oracle\product\10.2.0\oradata\teststby\teststby.ctl’;

Database altered.

SQL> alter database open;

Step 7:

Create the Primary and Standby init.ora/spfile to facilitate Dataguard.

SQL> CREATE PFILE= ‘C:\oracle\product\10.2.0\testinit.ora’

from spfile;

File created.

Primary Database:

Make the corresponding changes: 

*.aq_tm_processes=1

*.background_dump_dest='C:\oracle\product\10.2.0/admin/test/bdump'

*.compatible='10.2.0.1.0'

*.control_files='C:\oracle\product\10.2.0/oradata/test/\control01.ctl'

*.core_dump_dest='C:\oracle\product\10.2.0/admin/test/cdump'

*.db_domain=''

*.db_file_multiblock_read_count=16

*.db_name='test'

*.dispatchers='(PROTOCOL=TCP) (SERVICE=testXDB)'

*.fast_start_mttr_target=300

*.instance_name='test'

*.db_unique_name=’test’

*.job_queue_processes=10

*.LOG_ARCHIVE_CONFIG=’DG_CONFIG=(test,teststb)’

*.LOG_ARCHIVE_DEST_1=’location=C:\oracle\archive arch mandatory
valid_for=(ONLINE_LOGFILES,ALL_ROLES) db_unique_name=test’

*.LOG_ARCHIVE_DEST_2=’service=teststby lgwr sync affirm net_timeout=30
valid_for=(ONLINE_LOGFILES,ALL_ROLES) db_unique_name=teststby’

*.log_archive_dest_state_1=’ENABLE’

*.log_archive_dest_state_2=’ENABLE’

*.open_cursors=300

*.pga_aggregate_target=25165824

*.processes=150

*.query_rewrite_enabled='FALSE'

*.remote_login_passwordfile='EXCLUSIVE'

*.shared_pool_size=50331648

*.sort_area_size=524288

*.star_transformation_enabled='FALSE'

*.timed_statistics=TRUE

*.undo_management='AUTO'

*.undo_retention=10800

*.undo_tablespace='UNDOTBS1'

*.user_dump_dest='C:\oracle\product\10.2.0/admin/test/udump'

*.sga_target=611319808

*.fal_server=teststby

*.fal_client=test

*.db_file_name_convert=’C:\oracle\product\10.2.0\oradata\teststby’,
‘C:\oracle\product\10.2.0\oradata\test’

*.log_file_name_convert=’C:\oracle\product\10.2.0\oradata\teststby’,
‘C:\oracle\product\10.2.0\oradata\test’

Standby Database:

Create a standby init.ora from a copy of the primary one and make the necessary changes below.

*.aq_tm_processes=1

*.background_dump_dest=’C:\oracle\product\10.2.0/admin/teststby/bdump’

*.compatible=’10.2.0.1.0′

*.control_files=’C:\oracle\product\10.2.0/oradata/teststby/\control01.ctl’

*.core_dump_dest=’C:\oracle\product\10.2.0/admin/teststby/cdump’

*.db_domain=”

*.db_file_multiblock_read_count=16

*.db_name=’test’

*.DB_UNIQUE_NAME=’teststby’

*.dispatchers=’(PROTOCOL=TCP) (SERVICE=testXDB)’

*.fast_start_mttr_target=300

*.instance_name=’teststby’

*.job_queue_processes=10

*.LOG_ARCHIVE_DEST_1=’location=C:\oracle\archive2 arch mandatory valid_for=(ONLINE_LOGFILES,ALL_ROLES) db_unique_name=teststby’

*.LOG_ARCHIVE_DEST_2=’service=test lgwr sync affirm net_timeout=30 valid_for=(ONLINE_LOGFILES,ALL_ROLES) db_unique_name=test’

*.log_archive_dest_state_1=’ENABLE’

*.log_archive_dest_state_2=’ENABLE’

*.open_cursors=300

*.pga_aggregate_target=25165824

*.remote_login_passwordfile=’EXCLUSIVE’

*.processes=150

*.query_rewrite_enabled=’FALSE’

*.shared_pool_size=50331648

*.sort_area_size=524288

*.star_transformation_enabled=’FALSE’

*.timed_statistics=TRUE

*.undo_management=’AUTO’

*.undo_retention=10800

*.undo_tablespace=’UNDOTBS1′

*.user_dump_dest=’C:\oracle\product\10.2.0/admin/teststby/udump’

*.sga_target=611319808

*.standby_archive_dest=’location=c:\oracle\product\10.2.0\archive’

*.db_file_name_convert=’C:\oracle\product\10.2.0\oradata\test’,

‘C:\oracle\product\10.2.0\oradata\teststby’

*.log_file_name_convert=’C:\oracle\product\10.2.0\oradata\test’,

‘C:\oracle\product\10.2.0\oradata\teststby’

*.standby_file_management=AUTO

*.log_archive_config=’DG_CONFIG=(test,teststby)’

*.service_names=teststby

*.fal_server=test

*.fal_client=teststby

Step 8:

Convert the control file created from step 6 to the name and location of control file parameter in the new standby init.ora.

C:\oracle\product\10.2.0\oradata\teststby\teststby.ctl

to

C:\oracle\product\10.2.0\oradata\teststby\control01.ctl

Step 9:

Configure the Listener.ora file. Since the example is on the same node I have both the standby and primary in the listener.ora. If this was a production environment you would create the listener for primary and standby separate.

LISTENER =

(DESCRIPTION_LIST =

(DESCRIPTION =

(ADDRESS_LIST =

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

)

(ADDRESS_LIST =

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

)

)

)

SID_LIST_LISTENER =

(SID_LIST =

(SID_DESC =

(SID_NAME = PLSExtProc)

(ORACLE_HOME = C:\oracle\product\10.2.0)

(PROGRAM = extproc)

)

(SID_DESC =

(GLOBAL_DBNAME = test)

(ORACLE_HOME = C:\oracle\product\10.2.0)

(SID_NAME = test)

)

(SID_DESC =

(GLOBAL_DBNAME = teststby)

(ORACLE_HOME = C:\oracle\product\10.2.0)

(SID_NAME = teststby)

)

(SID_DESC =

(GLOBAL_DBNAME = teststby_DGMGRL)

(ORACLE_HOME = C:\oracle\product\10.2.0)

(SID_NAME = teststby)

)

(SID_DESC =

(GLOBAL_DBNAME = test_DGMGRL)

(ORACLE_HOME = C:\oracle\product\10.2.0)

(SID_NAME = test)

)

)

Note: The DGMGRL entries are for the Data Guard Broker to be able to start and stop the databases on switchovers.

Step 10:

Stop and restart the listener

C:\>lsnrctl stop

LSNRCTL for 32-bit Windows: Version 10.2.0.1.0 - Production on 30-MAR-2008 10:40

:55

Copyright (c) 1991, 2005, Oracle. All rights reserved.

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC0)))

The command completed successfully

C:\>lsnrctl start

LSNRCTL for 32-bit Windows: Version 10.2.0.1.0 - Production on 30-MAR-2008 10:41

:32

Copyright (c) 1991, 2005, Oracle. All rights reserved.

Starting tnslsnr: please wait…

TNSLSNR for 32-bit Windows: Version 10.2.0.1.0 - Production

System parameter file is C:\oracle\product\10.2.0\db_1\network\admin\listener.or

a

Log messages written to C:\oracle\product\10.2.0\db_1\network\log\listener.log

Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(PIPENAME=\\.\pipe\EXTPROC0ipc

)))

Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=rllaptop01)(PORT=1521)))

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC0)))

STATUS of the LISTENER

————————

Alias LISTENER

Version TNSLSNR for 32-bit Windows: Version 10.2.0.1.0 - Produ

ction

Start Date 30-MAR-2008 10:41:34

Uptime 0 days 0 hr. 0 min. 1 sec

Trace Level off

Security ON: Local OS Authentication

SNMP OFF

Listener Parameter File C:\oracle\product\10.2.0\db_1\network\admin\listener.o

ra

Listener Log File C:\oracle\product\10.2.0\db_1\network\log\listener.log

Listening Endpoints Summary…

(DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(PIPENAME=\\.\pipe\EXTPROC0ipc)))

(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=rllaptop01)(PORT=1521)))

Services Summary…

Service “PLSExtProc” has 1 instance(s).

Instance “PLSExtProc”, status UNKNOWN, has 1 handler(s) for this service…

Service “test” has 1 instance(s).

Instance “test”, status UNKNOWN, has 1 handler(s) for this service…

Service “test_DGMGRL” has 1 instance(s).

Instance “test”, status UNKNOWN, has 1 handler(s) for this service…

Service “teststby” has 1 instance(s).

Instance “teststby”, status UNKNOWN, has 1 handler(s) for this service…

Service “teststby_DGMGRL” has 1 instance(s).

Instance “teststby”, status UNKNOWN, has 1 handler(s) for this service…

The command completed successfully

Step 11:

Add the standby database to the tnsnames.ora file.

teststby =

(DESCRIPTION =

(ADDRESS_LIST =

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

)

(CONNECT_DATA =

(SERVER = DEDICATED)

(SERVICE_NAME = teststby)

)

)

Step 12:

Shutdown the primary database and start again with the new init.ora.

Convert it back to a spfile.

C:\> set oracle_sid=test

C:\> sqlplus

SQL> shutdown immediate;

Database closed.

Database dismounted.

ORACLE instance shut down.

SQL> startup pfile=C:\oracle\product\10.2.0\testinit.ora;

ORACLE instance started.

Total System Global Area 612368384 bytes

Fixed Size 1250428 bytes

Variable Size 83889028 bytes

Database Buffers 520093696 bytes

Redo Buffers 7135232 bytes

Database mounted.

Database opened.

SQL> create spfile from pfile=c:\oracle\product\10.2.0\testinit.ora;

Database altered.

SQL> shutdown immediate;

Database closed.

Database dismounted.

ORACLE instance shut down.

SQL> Startup;

ORACLE instance started.

Total System Global Area 612368384 bytes

Fixed Size 1250428 bytes

Variable Size 83889028 bytes

Database Buffers 520093696 bytes

Redo Buffers 7135232 bytes

Database mounted.

Database opened.

Note: You have to use a spfile to use Data Guard Broker.

Step 13:

Create standby redo logs if you want to run the configuration in MaxAvailibility or MaxProtection. Basically you have to create them if you want to run in SYNC mode.

SQL> Select * from v$log;

GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARCHIVED STATUS FIRST_CHANGE# FIRST_TIME

———- ———- ———- ———- ———- ——– —————- ————- ———–

1 1 108 52428800 1 NO CURRENT 658611 4/9/2008 10

2 1 106 52428800 1 YES ACTIVE 658530 4/9/2008 10

3 1 107 52428800 1 YES ACTIVE 658552 4/9/2008 10

SQL> Select * from v$logfile;

GROUP# STATUS TYPE MEMBER IS_RECOVERY_DEST_FILE

———- ——- ——- ——————————————————————————– ———————

3 ONLINE C:\ORACLE\PRODUCT\10.2.0\ORADATA\TEST\REDO03.LOG NO

2 ONLINE C:\ORACLE\PRODUCT\10.2.0\ORADATA\TEST\REDO02.LOG NO

1 ONLINE C:\ORACLE\PRODUCT\10.2.0\ORADATA\TEST\REDO01.LOG NO

Now we create the standby redologs

SQL> alter database add standby logfile ‘C:\ORACLE\PRODUCT\10.2.0\ORADATA\TEST\REDOSTB01.LOG’
size 52428800;

SQL> alter database add standby logfile ‘C:\ORACLE\PRODUCT\10.2.0\ORADATA\TEST\REDOSTB02.LOG’
size 52428800;

SQL> alter database add standby logfile ‘C:\ORACLE\PRODUCT\10.2.0\ORADATA\TEST\REDOSTB03.LOG’
size 52428800;

Once you mount standby you will have to open it in read only and issue the above commands
in the standby database location. 

i.e –

SQL> alter database add standby logfile ‘C:\ORACLE\PRODUCT\10.2.0\ORADATA\TESTSTBY\REDOSTB01.LOG’ size 52428800;

--- Do this for all 3

Step 14:

Start up the standby database using its init.ora file that you created in step 7.

Convert the init.ora file to a spfile.

Shut down the database and restart it using the spfile.

C:\> set oracle_sid=teststby

C:\> sqlplus

SQL> startup nomount pfile=C:\oracle\product\10.2.0\teststbyinit.ora;

ORACLE instance started.

Total System Global Area 612368384 bytes

Fixed Size 1250428 bytes

Variable Size 67111812 bytes

Database Buffers 536870912 bytes

Redo Buffers 7135232 bytes

SQL> alter database mount standby database;

Database altered.

SQL> create spfile from pfile=c:\oracle\product\10.2.0\teststbyinit.ora;

Database altered.

SQL> Shutdown immediate;

Database closed.

Database dismounted.

ORACLE instance shut down.

SQL> Startup nomount;

ORACLE instance started.

Total System Global Area 612368384 bytes

Fixed Size 1250428 bytes

Variable Size 67111812 bytes

Database Buffers 536870912 bytes

Redo Buffers 7135232 bytes

Now the standby database is started under the spfile.

Step 15:

Mount the standby database and recover it.

This will bring the standby database into standby mode.

SQL> alter database mount standby database;

Database altered.

SQL> recover managed standby database disconnect from session;

Media recovery complete.

SQL>

—- Go back to step 13 and add standby redo logs on standby database.

Step 16:

Now that Data Guard is all configured, we set up the Broker component.

First, we create the configuration.

C:\>dgmgrl

DGMGRL for 32-bit Windows: Version 10.2.0.1.0 - Production

Copyright (c) 2000, 2005, Oracle. All rights reserved.

Welcome to DGMGRL, type “help” for information.

DGMGRL> connect sys@test

Password:

Connected.

DGMGRL> CREATE CONFIGURATION ‘DGBTEST’ AS

PRIMARY DATABASE IS ‘TEST’

CONNECT IDENTIFIER IS TEST;

Configuration “DGBTEST’ created with primary database “TEST”

Step 17:

We add the standby database to the broker configuration.

DGMGRL> ADD DATABASE ‘TESTSTBY’ AS

CONNECT IDENTIFIER IS TESTSTBY

MAINTAINED AS PHYSICAL;

Database “TESTSTBY” added.

Step 18:

Enable the configuration.

DGMGRL> enable configuration;

Enabled.

DGMGRL>

Step 19:

Verify the broker configuration is set up correctly and running.

DGMGRL> show configuration

Configuration

Name: dgbtest

Enabled: YES

Protection Mode: MaxAvailability

Fast-Start Failover: DISABLED

Databases:

test - Primary database

teststby - Physical standby database

Current status for “dgbtest”:

SUCCESS

DGMGRL>

Now we have a fully functional Data Guard and Broker configuration. If you have any questions or any ideas to make this document better please contact me at rlenihan@piocon.com

I just recently authored a PL/SQL Server Page designed to funnel large amounts of data via the htp.p function to a web application. Because the volume of data could be very large, I wanted to minimize the individual calls to the htp.p function (which can only take a maximum of 32767 characters per call).

The answer was obviously CLOBS! I proceeded to accumulate the full multi-megabyte response in a CLOB by concatenating the individual VARCHAR2 results line by line as they came out of the database.

Now I’ll be the first to admit that my experience with LOBs in Oracle has been very limited. And with the onset of SecureFiles in 11g, maybe I will never really become very proficient with LOBs. But in my LOB naivety, I assumed that if it COULD be done, it would probably work fine. You would think that after 15 years of Oracle development, I would know better…nope.

DECLARE
    bigLob CLOB;
    CURSOR getVarchars2 IS
         SELECT singleColumn FROM table;
BEGIN
FOR v_rec IN getVarchars2 LOOP
    bigClob := bigClob||v_rec.singleRow||linebreak;
END LOOP;
    doStuffWithTheCLOB(bigCLOB);
END;

It looks so innocent. Append the VARCHAR2 to the end of the CLOB. Let the database do the data type conversion from VARCHAR2 to CLOB. No problema! 10 rows, 100 rows, 1000 rows, all working just fine. Then came the request for 44K rows of data. The database became very angry and would not cooperate (“Stop asking for 44k rows!”). The user community became very angry and would not cooperate (“We need our 44k rows!”).

The solution is of course found in an obscure part of the Oracle documentation.

Here are the relevant entries:
• When Possible, Read/Write Large Data Chunks at a Time: Because LOBs are big, you can obtain the best performance by reading and writing large pieces of a LOB value at a time. This helps in several respects:
    o Writing to the LOB creates a new version of the LOB chunk. Therefore, writing small amounts at a time will incur the cost of a new version for each small write. If logging is on, then the chunk is also stored in the redo log.

• If you use the newly provided enhanced SQL semantics functionality in your applications, then there will be many more temporary LOBs created silently in SQL and PL/SQL than before. Ensure that temporary tablespace for storing these temporary LOBs is large enough for your applications. In particular, these temporary LOBs are silently created when you use the following:
     o SQL functions on LOBs
     o PL/SQL built-in character functions on LOBs
     o Variable assignments from VARCHAR2/RAW to CLOBs/BLOBs, respectively.
     o Perform a LONG-to-LOB migration

I highly recommend looking at this chapter in the LOB documentation prior to working with LOBs in SQL and PL/SQL.

Greg Pike

I just returned from Collaborate 2008 in beautiful Denver, Colorado where I gave a presentation extolling the virtues of the OTN SQL and PL/SQL Forum as a learning tool for beginners and experts alike. The presentation was designed to both inform the audience about the incredible wealth of knowledge found on the Forums as well as to highlight the most experienced and prodigious contributors along with their unique solutions.

I focused a little on the Oracle Ace program (and invited Ace Director Dan Norris to give an overview) in an attempt to demonstrate the extraordinary talent and experience of the moderators. Finally, I ended with a case study of a single post and the myriad of solutions that the Forum Gurus brought to bear to solve the problem. The presentation itself can be downloaded here, but as with most PowerPoints, it’s just not that informative without the commentary. If you do view it, please play it in slide show mode as it contains significant animations.

The presentation was well attended (especially for a last-minute Collaborate addition) and the attendees were attentive and full of questions. Several people found me later and reported returning to the Forums for the first time in years to both read and contribute. Mission accomplished!

I want to thank all of the contributors to the SQL and PL/SQL Forum and especially those that provided the content for my case study.

Greg Pike

Greg and I will be speaking at IOUG Collaborate 08! Come hear us or the rest of the Piocon crew and stop by Piocon’s Booth # 1619.

Monday, April 14
Session 501	9:15 - 11:30 AM    Dan Norris and Matt Topper SSL, Load Balancers, Rewrite, Redirect and More Advanced Configuration
Tuesday, April 15
Session 2035	9:45 - 10:45 PM    Greg Pike Aces in the Hole: Learning Advanced SQL Techniques from the OTN Forum Pros
Session 711	12:15 - 1:15 PM    Dan Norris RAC SIG Experts Panel
Session 110	1:45 - 2:45 PM    Roger Lenihan  Remove Spreadsheets, Checklists and Stress Out Of A Switchover, Use Data Guard Broker
Session 433	1:45 - 2:45 PM    Rohit Badiyani, Karen Smudde, Winslow Troy Fantastic Four:  ADF Faces, WebCenter, AJAX and SOA
Session 341	3:30 - 4:30 PM    Dan Norris Building A RAC Test Environment On VMWare For Free
Session 513	3:30 - 4:30 PM    Greg Pike Leveraging Oracle Portal as an Enterprise Identity Management Repository
Wednesday, April 16
Session 517	8:30 - 9:30 AM    Dan Norris Oracle Adaptive Access Manager: What, Why, How
Session 131	9:45 - 10:45 AM    Jeremy Simmons Oracle Application Server High-Availability Options
Session 712	1:30 - 2:30 PM    Dan Norris Oracle RAC SIG Birds-of-a-Feather
Session 232	4:30 - 5:30 PM    Matthew Vranicar 2008:  A BI Odyssey
Thursday, April 17
Session 379	9:45 - 10:45 AM    Dan Norris To RAC or Not To RAC: What's Best For HA?

SingleQuery was out of commission for a short (actually, long!) while but we’ve fixed the issues and upgraded to Wordpress 2.5!

Look for more posts soon.

Jeremy

CREATE TABLE emp
( empno    NUMBER(4,0),
  ename    VARCHAR2(10 BYTE),
  job      VARCHAR2(9 BYTE),
  mgr      NUMBER(4,0),
  hiredate DATE,
  sal      NUMBER(7,2),
  comm     NUMBER(7,2),
  deptno   NUMBER(2,0)
); 

2.Insert Script

The script below can