I normally do not gripe on my blog but this one has to be stated. When installing Oracle Enterprise Manager Grid Control 10gR2 you are required to finish the installation with several "recommended" packages. One in particular is the OMS Configuration package. Without this package, you mine as well kiss your hopes for Grid Control goodbye. To me, it is not "recommended" but essential. Getting back to my issue, I have had the OMS Configuration package fail om me about 7 out of every 10 installation. I have no idea what causes the package to fail but Oracle tries to leave you a hint. It informs you to go to the $ORACLE_AGENT_HOME/cfgtoollogs and look for a file called "configToolFailedCommands". Now you, beign the DBA, are supposed to execute any commands found in this file manually before continuing on.
Now for my gripe: Do you think Oracle could be any more vague with a command called "oracle.sysman.emcp.agent.AgentPlugIn"?
If you have encountered this one, I feel your pain. Now for a solution to this ugly, non-descriptive command in the configToolFailedCommands file.
1. Log onto your Grid Control Repository that you are using with this installation.
2. Unlock the user SYSMAN and DBSNMP accounts.
3. Change the password and when you do make CERTAIN you use double-quotes around the password. (That is what causes this ugly command to appear)
4. Commit your changes and exit.
5. Last, check your /etc/hosts file to ensure you have the long, short, and full hostname in the description. For example:
IP Host's Long Name Host's Short Name Host's Even Shorter Name
127.0.0.1 localhost.localdomain localhost lhost
10.10.10.1 myserver.mydomain.com myserver mserv
The Host's Even Shorter Name doesn't have to exist. I tend to use it to make my life easier but again, it isn't essential.
Best of luck with your Grid Control Installation and Setup.
Monday, October 26, 2009
Oracle Database 10gR2 Streams Bidirectional Setup
ORACLE STREAMS CONFIGURATION BEST PRACTICES
This section recommends best practice for configuring Streams for Oracle Database 10g Release 2. The discussions are divided into the following sections:
· Guidelines for Preparing All Streams Configurations
· Recommendations for Downstream Capture Configurations
· Recommendations for Upstream (Local) Capture Configurations
All users should perform the instructions in the Guidelines for Preparing All Streams Configurations section of this white paper before branching off to either the downstream or the upstream configuration recommendations.
The examples in this article are based on a configuration that uses:
· Two databases (each on a 2-node RAC Cluster)
· Stream database administrator privileges
· Automatic Storage Management (ASM).
Guidelines for Preparing All Streams Configurations
The following list summarizes the tasks you must perform to properly prepare each Streams database:
Use Oracle Database 10g release 2 (10.2.0.4)
Ensure that all Streams databases are running Oracle Database 10g Release 2 (release 10.2.0.4 is recommended) and apply any critical patch sets. Although the source database must be running Oracle Database 10g Release 2, the downstream capture database can run Release 10.2.0.4 or a later release.
See the Oracle Database Upgrade Guide [7] for database upgrade information and the “Patches and Updates” tab on Oracle Metalink for information about critical Streams patches for release 10.2 and higher releases.
Verify platform requirements for downstream capture
Downstream capture requires that the source and Destination database are running on the same platform.
Prepare the source and target databases redo logs for Streams
1. Configure the source and target databases in ARCHIVELOG mode. For one-way Streams local capture, you must configure the source database to run in ARCHIVELOG mode, but the best practice is for both the source and target databases to be in ARCHIVELOG mode in case media recovery is required for either of the databases. If replication is bi-directional, then you must configure both source and target databases in ARCHIVELOG mode.
2. Configure the local archive Destination, LOG_ARCHIVE_TARGET_1, parameter and do not use a flash recovery area. Streams local capture and downstream capture require online redo logs and archived redo logs. Do not place archived redo log files in the Flash Recovery area, because this is a fixed-size storage area and the files may be deleted if the amount of space becomes low.
Create a tablespace dedicated to Streams
Create a dedicated Streams tablespace to contain the Streams AQ tables and other objects related to Streams, and to allow for better space management for the Streams objects:
For downstream capture, create the Streams tablespace on the downstream capture database only.
For upstream capture(and bidirectional), create the Streams tablespace on both the source and target databases.
For example, to create a Streams tablespace of 50 MB, which is the minimum recommended size, issue the following statements:
CREATE TABLESPACE streams_data
LOGGING
EXTENT MANAGEMENT LOCAL AUTOALLOCATE
SEGMENT SPACE MANAGEMENT AUTO
DATAFILE '+DATA' SIZE 50M
AUTOEXTEND ON NEXT 50M MAXSIZE UNLIMITED ;
Create the Streams administrator database user
Create the Streams administrator account that you will use to create and modify all Streams configurations and perform administrator functions with the DBMS_STREAMS_ADM PL/SQL package. Do this both on the Source and Destination databases.
For example, to create a new user named streamsadmin with the default streams_data tablespace, enter the following SQL*Plus statement as SYSDBA:
CREATE USER strmadmin IDENTIFIED BY strmadmin DEFAULT TABLESPACE STREAMS_DATA QUOTA UNLIMITED ON STREAMS_DATA;
GRANT DBA TO strmadmin;
Grant Streams authorization and DBA privileges
Use the DBMS_STREAMS_AUTH PL/SQL procedure to grant all of the privileges necessary for the Streams package procedures to function properly. Do this both on the Source and Destination databases:
BEGIN DBMS_STREAMS_AUTH.GRANT_ADMIN_PRIVILEGE( grantee => 'strmadmin', grant_privileges => true); END; /
Also, set Logminer to use the Streams tablespace. Do this both on the Source and Destination databases.
exec DBMS_LOGMNR_D.SET_TABLESPACE ('STREAMS_DATA');
Check that the streams administartor account is created:
SELECT * FROM dba_streams_administrator;
Set key initialization parameters
On each Streams database in the configuration, specify the initialization parameters shown below:
Parameter Name
Description
AQ_TM_PROCESSES
alter system set aq_tm_processes=1 scope=spfile sid='*';
DB_NAME
Select name from v$database. Each database should be the same. Ex. ORCL
DB_UNIQUE_NAME
Each database name will be different. In this example, on servers cluster1node1/2 the database’s name is ORCL_FORTWORTH. On servers cluster2node1/2 the databases’s name is ORCL_DALLAS.
DB_DOMAIN
On each Streams database, specify the network domain where the database resides. In this example, the db_domain is aadev. Therefore the source database is named:
ORCL_FORTWORTH.mydomain
GLOBAL_NAME=TRUE
On each Streams database specify GLOBAL_NAME=TRUE to ensure the database links work correctly. For example, the source database is named ORCL_FORTWORTH.mydomain when you select * from global_name;
JOB_QUEUE_PROCESSES=4
Specify the JOB_QUEUE_PROCESSES has at least 4 job queues for Streams. If the parameter is already set, increment the parameter by 4.
COMPATIBLE=10.2
Make certain the COMPATIBLE parameter is at least 10.2.
_JOB_QUEUE_INTERVAL=1
On each Streams database, specify how many seconds the job queue is scanned for work. It is recommended that you set this parameter to 1 (seconds) to improve the propagation job performance and minimize the delay for how often propagation jobs will execute. Since this is a hidden parameter, here is an example of how to set it:
alter system set "_job_queue_interval"=1 scope=spfile sid='*';
TIMED_STATISTICS=TRUE
On each Streams database, set the TIMED_STATISTICS=TRUE parameter to allow performance statistics to be gathered for analysis of potential performance bottlenecks.
STATISTICS_LEVEL=TYPICAL
or ALL
On each Streams database, set this parameter to TYPICAL to collect the necessary statistics. Set to ALL to gather more than the bare minimum.
SHARED_POOL_SIZE=256M
On each Streams database, set this parameter to a minimum value of 256 MB because Streams uses a significant amount of PL/SQL and library cache components. Future performance analysis may providerecommendations for how much to increase the size ofSHARED_POOL_SIZE, especially for the database running Streamapply processes.
STREAMS_POOL_SIZE=256M
On each database where there is either a capture or apply process running, set the STREAMS_POOL_SIZE parameter to a minimum of 256M. Memory from the Streams pool is used by both the Streamsprocesses and the buffered queue, with the LCRs in the buffered queuethe largest component. Use the V$STREAMS_POOL_ADVICE view to help you size the buffered queue.
Create database links between the source and target databases
Before creating database links, ensure that all Streams databases are reachable using Oracle Net aliases. Perform the following steps to create database links:
Log into the target database as the Streams administrator user and determine the global database name by issuing the following query:
select * from global_name;
You will use the name returned by this query to create the database link in the next step.
Create a TNSNAMES.ORA entry for each database using Streams. Below is an example of the TNSNAMES.ORA file entry:
ORCL_DALLAS.mydomain =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = aadfwcluster2node1-vip)(PORT = 1521))
(ADDRESS = (PROTOCOL = TCP)(HOST = aadfwcluster2node2-vip)(PORT = 1521))
(LOAD_BALANCE = yes)
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = ORCL_DALLAS.mydomain)
(FAILOVER_MODE =
(TYPE = SELECT)
(METHOD = BASIC)
(RETRIES = 180)
(DELAY = 5)
)
)
)
Log into the source database as the Streams administrator.
conn strmadmin/strmadmin
Create a database link first from the source database to the target database. For example, on the source database, enter the following statement to create a database link from the source database to a target database named ORCL_DALLAS.mydomain using the TNSNAMES.ORA alias entry ORCL_DALLAS.mydomain.
create database link ORCL_DALLAS.mydomain connect to strmadmin identified by strmadmin using 'ORCL_DALLAS.mydomain';
Validate the database link is working by issuing the following select * from dual@ query on the source database:
select * from dual@ORCL_DALLAS.mydomain;
DUM
---
X
If an error is returned, it is probably due to an incorrect TNSNAMES.ORA descriptor or to an incorrect database name. Do not proceed to the next step until the database link is working properly.
Log into the Streams Administrator account on the Destination database.
conn strmadmin/strmadmin
Create a database link from the Destination database to the source database.
create database link ORCL_FORTWORTH.mydomain connect to strmadmin identified by strmadmin using 'ORCL_FORTWORTH.mydomain';
Validate the database link is working by issuing the following select * from dual@ query on the target database:
select * from dual@ORCL_FORTWORTH.mydomain;
If an error occurs, it is probably because of an incorrect TNSNAMES.ORA descriptor or an incorrect database name. Do not proceed to the next section until the database link is working properly.
Set up Directory Objects
In many cases, Oracle Streams instantiates the objects for which changes will be captured and uses Data Pump to initially populate the replica tables at the targetdatabase. As a part of this instantiation process, Data Pump needs a file system directory location on both the source and target database server for staging and moving all Streams objects. Streams uses Oracle Directory objects that point to these file system directory locations.
Directory objects are needed by Streams for instantiation purposes. Ensure thatthe locations to which the directory objects point have sufficient storage space tosupport all objects that Streams instantiates. On both the source and downstream databases servers, determine an appropriate location for the file system directory and create Oracle directory objects on each of the databases. For example, as the Streams administrator user, create the Oracle Directory objects as follows:
create directory streams_dir as '/HR/oracle/admin/ORCL1/dpdump';
Account for object or tablespace name differences when replicating DDLs
If you choose to replicate DDLs, then you need to account for object or tablespace name differences between the source and target databases. Here are some best practices:
Avoid system-generated naming for constraints or indexes
Keep the same tablespace names between databases or use a DDL handler to explicitly address the naming differences.
Recommendations for Bidirectional Configurations
Downstream capture is typically used to replicate a full database, to offload processing from the source database to the target database, or to reduce data loss with ASYNC or SYNC redo transport.
Specify initialization parameters on the source and target databases.
Note the example below. Be sure that the parameters you specify match the locations and parameter values in your Streams configuration.
Source Database
LOG_ARCHIVE_TARGET_1=
'LOCATION=+DATA/ORCL_FORTWORTH/
VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) MANDATORY'
LOG_ARCHIVE_TARGET_2=
'SERVICE=ORCL_DALLAS.mydomain
LGWR ASYNC NOREGISTER
VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE)
DB_UNIQUE_NAME=ORCL_DALLAS'
LOG_ARCHIVE_TARGET_STATE_1=ENABLE
LOG_ARCHIVE_TARGET_STATE_2=DEFER
LOG_ARCHIVE_CONFIG='SEND, DG_CONFIG=(ORCL_FORTWORTH,ORCL_DALLAS)'
LOG_ARCHIVE_MAX_PROCESSES=4
Target Database
LOG_ARCHIVE_TARGET_1=
'LOCATION=use_db_recovery_file_dest
VALID_FOR=(ONLINE_LOGFILE,PRIMARY_ROLE)'
LOG_ARCHIVE_TARGET_2=
'LOCATION=+DATA/ORCL_DALLAS/ARCH_FORTWORTH_STREAMS
VALID_FOR=(STANDBY_LOGFILE,PRIMARY_ROLE)'
LOG_ARCHIVE_TARGET_STATE_1=ENABLE
LOG_ARCHIVE_TARGET_STATE_2=ENABLE
LOG_ARCHIVE_CONFIG='RECEIVE, DG_CONFIG=(ORCL_FORTWORTH,ORCL_DALLAS)'
LOG_ARCHIVE_MAX_PROCESSES=4
Configure standby redo logs.
To enable real-time mining for capture at the downstream database, you must configure standby redo logs (SRLs) on the downstream database to accept redo from the source database. The Streams capture process mines the standby redo logs and any archived redo logs created from the standby redo logs.
We should have minimum of (No. Of threads)*(groups per Threads + 1). We have total 2 Threads and each has 4 groups. So in this example, we need 5 standby Redo Logs per Thread at Minimum. Get the Max Group# from v$log.
select thread#, group# from v$log order by 1,2;
THREAD# GROUP#
---------- ----------
1 1
1 2
1 3
1 4
2 5
2 6
2 7
2 8
8 rows selected.
I will start with the Group No. of 9 to add the Standby Redo Logs because the Last Online Redo Group# is 18.
alter system set standby_file_management=manual sid='*';
ALTER DATABASE ADD STANDBY LOGFILE
THREAD 1
GROUP 9 SIZE 50M,
GROUP 10 SIZE 50M,
GROUP 11 SIZE 50M,
GROUP 12 SIZE 50M,
GROUP 13 SIZE 50M;
ALTER DATABASE ADD STANDBY LOGFILE
THREAD 2
GROUP 14 SIZE 50M,
GROUP 15 SIZE 50M,
GROUP 16 SIZE 50M,
GROUP 17 SIZE 50M,
GROUP 18 SIZE 50M;
select thread#, group# from v$standby_log order by 1,2;
THREAD# GROUP#
---------- ----------
1 9
1 10
1 11
1 12
1 13
2 14
2 15
2 16
2 17
2 18
10 rows selected.alter system set standby_file_management=auto sid='*';
Bidirectional Streams Setup You must have at least one schema (Ex. "HR") in two different databases Source and Target. Now set up two queues for Capture and apply in Source Database as shown below :
conn strmadmin/strmadmin@ORCL_FORTWORTH
begin dbms_streams_adm.set_up_queue( queue_table => 'apply_FORTWORTHtab', queue_name => 'apply_FORTWORTH', queue_user => 'strmadmin');
end;
/
begin dbms_streams_adm.set_up_queue( queue_table => 'capture_FORTWORTHtab', queue_name => 'capture_FORTWORTH', queue_user => 'strmadmin');
end;
/
Set up two queues for Capture and apply in Target Database as shown below :
conn strmadmin/strmadmin@ORCL_DALLAS
begin dbms_streams_adm.set_up_queue( queue_table => 'apply_DALLAStab', queue_name => 'apply_DALLAS', queue_user => 'strmadmin');
end;
/
begin dbms_streams_adm.set_up_queue( queue_table => 'capture_DALLAStab', queue_name => 'capture_DALLAS', queue_user => 'strmadmin');
end;
/
Configure Capture process on Source database.
conn strmadmin/strmadmin@ORCL_FORTWORTH begin dbms_streams_adm.add_schema_rules ( schema_name => 'HR', streams_type => 'capture', streams_name => 'captures_FORTWORTH', queue_name => 'capture_FORTWORTH', include_dml => true, include_ddl => true, inclusion_rule => true); end; /
Configure Apply process on Source database
conn strmadmin/strmadmin@ORCL_FORTWORTH begin dbms_streams_adm.add_schema_rules ( schema_name => 'HR', streams_type => 'apply', streams_name => 'applies_FORTWORTH', queue_name => 'apply_FORTWORTH', include_dml => true, include_ddl => true, source_database => 'ORCL_DALLAS.mydomain'); end; /
Configure Propagation process on Source Database:
conn strmadmin/strmadmin@ORCL_FORTWORTH begin dbms_streams_adm.add_schema_propagation_rules ( schema_name => 'HR', streams_name => 'prop_FORTWORTH_to_DALLAS', source_queue_name => 'capture_FORTWORTH', destination_queue_name => 'apply_DALLAS@ORCL_DALLAS.mydomain', include_dml => true, include_ddl => true, source_database => 'ORCL_FORTWORTH.mydomain'); end; /
Configure capture process on Target Database :
conn strmadmin/strmadmin@ORCL_DALLAS begin dbms_streams_adm.add_schema_rules ( schema_name => 'HR', streams_type => 'capture', streams_name => 'captures_DALLAS', queue_name => 'capture_DALLAS', include_dml => true, include_ddl => true); end; /
Set the Schema Instantiation SCN on Source using the SCN of Target database :
connect to strmadmin/strmadmin@ORCL_DALLAS declare v_scn number; begin v_scn := dbms_flashback.get_system_change_number(); dbms_apply_adm.set_schema_instantiation_scn@ORCL_FORTWORTH.mydomain( source_schema_name => 'HR', source_database_name => 'ORCL_DALLAS.mydomain', instantiation_scn => v_scn, recursive => true); end; /
If you experiences any TNS issues, reload the listener on the Source and retry.
Configure Apply process on Target :
connect strmadmin/strmadmin@ORCL_DALLAS
begin dbms_streams_adm.add_schema_rules ( schema_name => 'HR', streams_type => 'apply', streams_name => 'applies_DALLAS', queue_name => 'apply_DALLAS', include_dml => true, include_ddl => true, source_database => 'ORCL_FORTWORTH.mydomain'); end; /
Configure Propagation process on Target .
connect strmadmin/strmadmin@ORCL_DALLAS begin dbms_streams_adm.add_schema_propagation_rules ( schema_name => 'HR', streams_name => 'prop_dest_to_FORTWORTH', source_queue_name => 'capture_DALLAS', destination_queue_name => 'apply_FORTWORTH@ORCL_FORTWORTH.mydomain', include_dml => true, include_ddl => true, source_database => 'ORCL_DALLAS'); end; /
At this point, the databases should now be replicating to one another. The next step is in case you need to restart the replication process.
You can confirm the environments are in sync by executing the following SQL on each:
select current_scn from v$database;
If the SCN numbers are identical, the Streams configuration is ready to start the replication.
Set Schema Instantiation on Target Database : There are many ways to instantiate HR@Target Database. If object is not already exists in the Target database, instantiation can be done using export/import. If object already exists , Instantiation can be done using dbms_apply_adm.set_schema_instantiation_scn Say object already exists in HR@Target then,
conn strmadmin/strmadmin@ORCL_FORTWORTH declare v_scn number; begin v_scn := dbms_flashback.get_system_change_number(); dbms_apply_adm.set_schema_instantiation_scn@ORCL_DALLAS.mydomain( source_schema_name => 'HR', source_database_name => 'ORCL_FORTWORTH.mydomain', instantiation_scn => v_scn, recursive => true); end; /
Ensure that supplemental logging is present for objects present in both Source and Target databases. Start Apply processes on Target :
Start Apply. Set the parameter disable_on_error to 'N' to continue processing row LCR even it encounters errors.
conn strmadmin/strmadmin@ORCL_DALLASbegin dbms_apply_adm.set_parameter ( apply_name => 'applies_DALLAS', parameter => 'disable_on_error', value => 'N'); end; /
exec dbms_apply_adm.start_apply (apply_name=> 'applies_DALLAS');
Start Capture process in Target :
exec dbms_capture_adm.start_capture (capture_name=>'captures_DALLAS');
Start Apply processes on Source :
conn strmadmin/strmadmin@ORCL_FORTWORTH
begin dbms_apply_adm.set_parameter ( apply_name => 'applies_FORTWORTH', parameter => 'disable_on_error', value => 'N'); end; / exec dbms_apply_adm.start_apply (apply_name=> 'applies_FORTWORTH'); Start Capture process in Source : exec dbms_capture_adm.start_capture (capture_name=>'captures_FORTWORTH');
Testing of the Bidirectional Steams setup can be done with DML & DDL Statements between HR@Source and HR@Target Schemas
This section recommends best practice for configuring Streams for Oracle Database 10g Release 2. The discussions are divided into the following sections:
· Guidelines for Preparing All Streams Configurations
· Recommendations for Downstream Capture Configurations
· Recommendations for Upstream (Local) Capture Configurations
All users should perform the instructions in the Guidelines for Preparing All Streams Configurations section of this white paper before branching off to either the downstream or the upstream configuration recommendations.
The examples in this article are based on a configuration that uses:
· Two databases (each on a 2-node RAC Cluster)
· Stream database administrator privileges
· Automatic Storage Management (ASM).
Guidelines for Preparing All Streams Configurations
The following list summarizes the tasks you must perform to properly prepare each Streams database:
Use Oracle Database 10g release 2 (10.2.0.4)
Ensure that all Streams databases are running Oracle Database 10g Release 2 (release 10.2.0.4 is recommended) and apply any critical patch sets. Although the source database must be running Oracle Database 10g Release 2, the downstream capture database can run Release 10.2.0.4 or a later release.
See the Oracle Database Upgrade Guide [7] for database upgrade information and the “Patches and Updates” tab on Oracle Metalink for information about critical Streams patches for release 10.2 and higher releases.
Verify platform requirements for downstream capture
Downstream capture requires that the source and Destination database are running on the same platform.
Prepare the source and target databases redo logs for Streams
1. Configure the source and target databases in ARCHIVELOG mode. For one-way Streams local capture, you must configure the source database to run in ARCHIVELOG mode, but the best practice is for both the source and target databases to be in ARCHIVELOG mode in case media recovery is required for either of the databases. If replication is bi-directional, then you must configure both source and target databases in ARCHIVELOG mode.
2. Configure the local archive Destination, LOG_ARCHIVE_TARGET_1, parameter and do not use a flash recovery area. Streams local capture and downstream capture require online redo logs and archived redo logs. Do not place archived redo log files in the Flash Recovery area, because this is a fixed-size storage area and the files may be deleted if the amount of space becomes low.
Create a tablespace dedicated to Streams
Create a dedicated Streams tablespace to contain the Streams AQ tables and other objects related to Streams, and to allow for better space management for the Streams objects:
For downstream capture, create the Streams tablespace on the downstream capture database only.
For upstream capture(and bidirectional), create the Streams tablespace on both the source and target databases.
For example, to create a Streams tablespace of 50 MB, which is the minimum recommended size, issue the following statements:
CREATE TABLESPACE streams_data
LOGGING
EXTENT MANAGEMENT LOCAL AUTOALLOCATE
SEGMENT SPACE MANAGEMENT AUTO
DATAFILE '+DATA' SIZE 50M
AUTOEXTEND ON NEXT 50M MAXSIZE UNLIMITED ;
Create the Streams administrator database user
Create the Streams administrator account that you will use to create and modify all Streams configurations and perform administrator functions with the DBMS_STREAMS_ADM PL/SQL package. Do this both on the Source and Destination databases.
For example, to create a new user named streamsadmin with the default streams_data tablespace, enter the following SQL*Plus statement as SYSDBA:
CREATE USER strmadmin IDENTIFIED BY strmadmin DEFAULT TABLESPACE STREAMS_DATA QUOTA UNLIMITED ON STREAMS_DATA;
GRANT DBA TO strmadmin;
Grant Streams authorization and DBA privileges
Use the DBMS_STREAMS_AUTH PL/SQL procedure to grant all of the privileges necessary for the Streams package procedures to function properly. Do this both on the Source and Destination databases:
BEGIN DBMS_STREAMS_AUTH.GRANT_ADMIN_PRIVILEGE( grantee => 'strmadmin', grant_privileges => true); END; /
Also, set Logminer to use the Streams tablespace. Do this both on the Source and Destination databases.
exec DBMS_LOGMNR_D.SET_TABLESPACE ('STREAMS_DATA');
Check that the streams administartor account is created:
SELECT * FROM dba_streams_administrator;
Set key initialization parameters
On each Streams database in the configuration, specify the initialization parameters shown below:
Parameter Name
Description
AQ_TM_PROCESSES
alter system set aq_tm_processes=1 scope=spfile sid='*';
DB_NAME
Select name from v$database. Each database should be the same. Ex. ORCL
DB_UNIQUE_NAME
Each database name will be different. In this example, on servers cluster1node1/2 the database’s name is ORCL_FORTWORTH. On servers cluster2node1/2 the databases’s name is ORCL_DALLAS.
DB_DOMAIN
On each Streams database, specify the network domain where the database resides. In this example, the db_domain is aadev. Therefore the source database is named:
ORCL_FORTWORTH.mydomain
GLOBAL_NAME=TRUE
On each Streams database specify GLOBAL_NAME=TRUE to ensure the database links work correctly. For example, the source database is named ORCL_FORTWORTH.mydomain when you select * from global_name;
JOB_QUEUE_PROCESSES=4
Specify the JOB_QUEUE_PROCESSES has at least 4 job queues for Streams. If the parameter is already set, increment the parameter by 4.
COMPATIBLE=10.2
Make certain the COMPATIBLE parameter is at least 10.2.
_JOB_QUEUE_INTERVAL=1
On each Streams database, specify how many seconds the job queue is scanned for work. It is recommended that you set this parameter to 1 (seconds) to improve the propagation job performance and minimize the delay for how often propagation jobs will execute. Since this is a hidden parameter, here is an example of how to set it:
alter system set "_job_queue_interval"=1 scope=spfile sid='*';
TIMED_STATISTICS=TRUE
On each Streams database, set the TIMED_STATISTICS=TRUE parameter to allow performance statistics to be gathered for analysis of potential performance bottlenecks.
STATISTICS_LEVEL=TYPICAL
or ALL
On each Streams database, set this parameter to TYPICAL to collect the necessary statistics. Set to ALL to gather more than the bare minimum.
SHARED_POOL_SIZE=256M
On each Streams database, set this parameter to a minimum value of 256 MB because Streams uses a significant amount of PL/SQL and library cache components. Future performance analysis may providerecommendations for how much to increase the size ofSHARED_POOL_SIZE, especially for the database running Streamapply processes.
STREAMS_POOL_SIZE=256M
On each database where there is either a capture or apply process running, set the STREAMS_POOL_SIZE parameter to a minimum of 256M. Memory from the Streams pool is used by both the Streamsprocesses and the buffered queue, with the LCRs in the buffered queuethe largest component. Use the V$STREAMS_POOL_ADVICE view to help you size the buffered queue.
Create database links between the source and target databases
Before creating database links, ensure that all Streams databases are reachable using Oracle Net aliases. Perform the following steps to create database links:
Log into the target database as the Streams administrator user and determine the global database name by issuing the following query:
select * from global_name;
You will use the name returned by this query to create the database link in the next step.
Create a TNSNAMES.ORA entry for each database using Streams. Below is an example of the TNSNAMES.ORA file entry:
ORCL_DALLAS.mydomain =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = aadfwcluster2node1-vip)(PORT = 1521))
(ADDRESS = (PROTOCOL = TCP)(HOST = aadfwcluster2node2-vip)(PORT = 1521))
(LOAD_BALANCE = yes)
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = ORCL_DALLAS.mydomain)
(FAILOVER_MODE =
(TYPE = SELECT)
(METHOD = BASIC)
(RETRIES = 180)
(DELAY = 5)
)
)
)
Log into the source database as the Streams administrator.
conn strmadmin/strmadmin
Create a database link first from the source database to the target database. For example, on the source database, enter the following statement to create a database link from the source database to a target database named ORCL_DALLAS.mydomain using the TNSNAMES.ORA alias entry ORCL_DALLAS.mydomain.
create database link ORCL_DALLAS.mydomain connect to strmadmin identified by strmadmin using 'ORCL_DALLAS.mydomain';
Validate the database link is working by issuing the following select * from dual@
select * from dual@ORCL_DALLAS.mydomain;
DUM
---
X
If an error is returned, it is probably due to an incorrect TNSNAMES.ORA descriptor or to an incorrect database name. Do not proceed to the next step until the database link is working properly.
Log into the Streams Administrator account on the Destination database.
conn strmadmin/strmadmin
Create a database link from the Destination database to the source database.
create database link ORCL_FORTWORTH.mydomain connect to strmadmin identified by strmadmin using 'ORCL_FORTWORTH.mydomain';
Validate the database link is working by issuing the following select * from dual@
select * from dual@ORCL_FORTWORTH.mydomain;
If an error occurs, it is probably because of an incorrect TNSNAMES.ORA descriptor or an incorrect database name. Do not proceed to the next section until the database link is working properly.
Set up Directory Objects
In many cases, Oracle Streams instantiates the objects for which changes will be captured and uses Data Pump to initially populate the replica tables at the targetdatabase. As a part of this instantiation process, Data Pump needs a file system directory location on both the source and target database server for staging and moving all Streams objects. Streams uses Oracle Directory objects that point to these file system directory locations.
Directory objects are needed by Streams for instantiation purposes. Ensure thatthe locations to which the directory objects point have sufficient storage space tosupport all objects that Streams instantiates. On both the source and downstream databases servers, determine an appropriate location for the file system directory and create Oracle directory objects on each of the databases. For example, as the Streams administrator user, create the Oracle Directory objects as follows:
create directory streams_dir as '/HR/oracle/admin/ORCL1/dpdump';
Account for object or tablespace name differences when replicating DDLs
If you choose to replicate DDLs, then you need to account for object or tablespace name differences between the source and target databases. Here are some best practices:
Avoid system-generated naming for constraints or indexes
Keep the same tablespace names between databases or use a DDL handler to explicitly address the naming differences.
Recommendations for Bidirectional Configurations
Downstream capture is typically used to replicate a full database, to offload processing from the source database to the target database, or to reduce data loss with ASYNC or SYNC redo transport.
Specify initialization parameters on the source and target databases.
Note the example below. Be sure that the parameters you specify match the locations and parameter values in your Streams configuration.
Source Database
LOG_ARCHIVE_TARGET_1=
'LOCATION=+DATA/ORCL_FORTWORTH/
VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) MANDATORY'
LOG_ARCHIVE_TARGET_2=
'SERVICE=ORCL_DALLAS.mydomain
LGWR ASYNC NOREGISTER
VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE)
DB_UNIQUE_NAME=ORCL_DALLAS'
LOG_ARCHIVE_TARGET_STATE_1=ENABLE
LOG_ARCHIVE_TARGET_STATE_2=DEFER
LOG_ARCHIVE_CONFIG='SEND, DG_CONFIG=(ORCL_FORTWORTH,ORCL_DALLAS)'
LOG_ARCHIVE_MAX_PROCESSES=4
Target Database
LOG_ARCHIVE_TARGET_1=
'LOCATION=use_db_recovery_file_dest
VALID_FOR=(ONLINE_LOGFILE,PRIMARY_ROLE)'
LOG_ARCHIVE_TARGET_2=
'LOCATION=+DATA/ORCL_DALLAS/ARCH_FORTWORTH_STREAMS
VALID_FOR=(STANDBY_LOGFILE,PRIMARY_ROLE)'
LOG_ARCHIVE_TARGET_STATE_1=ENABLE
LOG_ARCHIVE_TARGET_STATE_2=ENABLE
LOG_ARCHIVE_CONFIG='RECEIVE, DG_CONFIG=(ORCL_FORTWORTH,ORCL_DALLAS)'
LOG_ARCHIVE_MAX_PROCESSES=4
Configure standby redo logs.
To enable real-time mining for capture at the downstream database, you must configure standby redo logs (SRLs) on the downstream database to accept redo from the source database. The Streams capture process mines the standby redo logs and any archived redo logs created from the standby redo logs.
We should have minimum of (No. Of threads)*(groups per Threads + 1). We have total 2 Threads and each has 4 groups. So in this example, we need 5 standby Redo Logs per Thread at Minimum. Get the Max Group# from v$log.
select thread#, group# from v$log order by 1,2;
THREAD# GROUP#
---------- ----------
1 1
1 2
1 3
1 4
2 5
2 6
2 7
2 8
8 rows selected.
I will start with the Group No. of 9 to add the Standby Redo Logs because the Last Online Redo Group# is 18.
alter system set standby_file_management=manual sid='*';
ALTER DATABASE ADD STANDBY LOGFILE
THREAD 1
GROUP 9 SIZE 50M,
GROUP 10 SIZE 50M,
GROUP 11 SIZE 50M,
GROUP 12 SIZE 50M,
GROUP 13 SIZE 50M;
ALTER DATABASE ADD STANDBY LOGFILE
THREAD 2
GROUP 14 SIZE 50M,
GROUP 15 SIZE 50M,
GROUP 16 SIZE 50M,
GROUP 17 SIZE 50M,
GROUP 18 SIZE 50M;
select thread#, group# from v$standby_log order by 1,2;
THREAD# GROUP#
---------- ----------
1 9
1 10
1 11
1 12
1 13
2 14
2 15
2 16
2 17
2 18
10 rows selected.alter system set standby_file_management=auto sid='*';
Bidirectional Streams Setup You must have at least one schema (Ex. "HR") in two different databases Source and Target. Now set up two queues for Capture and apply in Source Database as shown below :
conn strmadmin/strmadmin@ORCL_FORTWORTH
begin dbms_streams_adm.set_up_queue( queue_table => 'apply_FORTWORTHtab', queue_name => 'apply_FORTWORTH', queue_user => 'strmadmin');
end;
/
begin dbms_streams_adm.set_up_queue( queue_table => 'capture_FORTWORTHtab', queue_name => 'capture_FORTWORTH', queue_user => 'strmadmin');
end;
/
Set up two queues for Capture and apply in Target Database as shown below :
conn strmadmin/strmadmin@ORCL_DALLAS
begin dbms_streams_adm.set_up_queue( queue_table => 'apply_DALLAStab', queue_name => 'apply_DALLAS', queue_user => 'strmadmin');
end;
/
begin dbms_streams_adm.set_up_queue( queue_table => 'capture_DALLAStab', queue_name => 'capture_DALLAS', queue_user => 'strmadmin');
end;
/
Configure Capture process on Source database.
conn strmadmin/strmadmin@ORCL_FORTWORTH begin dbms_streams_adm.add_schema_rules ( schema_name => 'HR', streams_type => 'capture', streams_name => 'captures_FORTWORTH', queue_name => 'capture_FORTWORTH', include_dml => true, include_ddl => true, inclusion_rule => true); end; /
Configure Apply process on Source database
conn strmadmin/strmadmin@ORCL_FORTWORTH begin dbms_streams_adm.add_schema_rules ( schema_name => 'HR', streams_type => 'apply', streams_name => 'applies_FORTWORTH', queue_name => 'apply_FORTWORTH', include_dml => true, include_ddl => true, source_database => 'ORCL_DALLAS.mydomain'); end; /
Configure Propagation process on Source Database:
conn strmadmin/strmadmin@ORCL_FORTWORTH begin dbms_streams_adm.add_schema_propagation_rules ( schema_name => 'HR', streams_name => 'prop_FORTWORTH_to_DALLAS', source_queue_name => 'capture_FORTWORTH', destination_queue_name => 'apply_DALLAS@ORCL_DALLAS.mydomain', include_dml => true, include_ddl => true, source_database => 'ORCL_FORTWORTH.mydomain'); end; /
Configure capture process on Target Database :
conn strmadmin/strmadmin@ORCL_DALLAS begin dbms_streams_adm.add_schema_rules ( schema_name => 'HR', streams_type => 'capture', streams_name => 'captures_DALLAS', queue_name => 'capture_DALLAS', include_dml => true, include_ddl => true); end; /
Set the Schema Instantiation SCN on Source using the SCN of Target database :
connect to strmadmin/strmadmin@ORCL_DALLAS declare v_scn number; begin v_scn := dbms_flashback.get_system_change_number(); dbms_apply_adm.set_schema_instantiation_scn@ORCL_FORTWORTH.mydomain( source_schema_name => 'HR', source_database_name => 'ORCL_DALLAS.mydomain', instantiation_scn => v_scn, recursive => true); end; /
If you experiences any TNS issues, reload the listener on the Source and retry.
Configure Apply process on Target :
connect strmadmin/strmadmin@ORCL_DALLAS
begin dbms_streams_adm.add_schema_rules ( schema_name => 'HR', streams_type => 'apply', streams_name => 'applies_DALLAS', queue_name => 'apply_DALLAS', include_dml => true, include_ddl => true, source_database => 'ORCL_FORTWORTH.mydomain'); end; /
Configure Propagation process on Target .
connect strmadmin/strmadmin@ORCL_DALLAS begin dbms_streams_adm.add_schema_propagation_rules ( schema_name => 'HR', streams_name => 'prop_dest_to_FORTWORTH', source_queue_name => 'capture_DALLAS', destination_queue_name => 'apply_FORTWORTH@ORCL_FORTWORTH.mydomain', include_dml => true, include_ddl => true, source_database => 'ORCL_DALLAS'); end; /
At this point, the databases should now be replicating to one another. The next step is in case you need to restart the replication process.
You can confirm the environments are in sync by executing the following SQL on each:
select current_scn from v$database;
If the SCN numbers are identical, the Streams configuration is ready to start the replication.
Set Schema Instantiation on Target Database : There are many ways to instantiate HR@Target Database. If object is not already exists in the Target database, instantiation can be done using export/import. If object already exists , Instantiation can be done using dbms_apply_adm.set_schema_instantiation_scn Say object already exists in HR@Target then,
conn strmadmin/strmadmin@ORCL_FORTWORTH declare v_scn number; begin v_scn := dbms_flashback.get_system_change_number(); dbms_apply_adm.set_schema_instantiation_scn@ORCL_DALLAS.mydomain( source_schema_name => 'HR', source_database_name => 'ORCL_FORTWORTH.mydomain', instantiation_scn => v_scn, recursive => true); end; /
Ensure that supplemental logging is present for objects present in both Source and Target databases. Start Apply processes on Target :
Start Apply. Set the parameter disable_on_error to 'N' to continue processing row LCR even it encounters errors.
conn strmadmin/strmadmin@ORCL_DALLASbegin dbms_apply_adm.set_parameter ( apply_name => 'applies_DALLAS', parameter => 'disable_on_error', value => 'N'); end; /
exec dbms_apply_adm.start_apply (apply_name=> 'applies_DALLAS');
Start Capture process in Target :
exec dbms_capture_adm.start_capture (capture_name=>'captures_DALLAS');
Start Apply processes on Source :
conn strmadmin/strmadmin@ORCL_FORTWORTH
begin dbms_apply_adm.set_parameter ( apply_name => 'applies_FORTWORTH', parameter => 'disable_on_error', value => 'N'); end; / exec dbms_apply_adm.start_apply (apply_name=> 'applies_FORTWORTH'); Start Capture process in Source : exec dbms_capture_adm.start_capture (capture_name=>'captures_FORTWORTH');
Testing of the Bidirectional Steams setup can be done with DML & DDL Statements between HR@Source and HR@Target Schemas
Friday, June 19, 2009
Setting up Oracle 10gR2 Database Control on a RAC Cluster
Using Oracle 10g RAC can be intimidating enough so if you do not have access to Grid Control, here is the quick, down and dirty way to setup Database Control for you cluster. By using Database Control on a RAC Cluster, it makes a DBA's life much easier then relying on command line alone.
How to Set Up Database Control
Before you begin running any commands, you will need a few things handy. You will need to know your Database's Unique Name, the listener port number it uses, your sys password, and the cluster name. If you are using ASM you will also need to know the ASM HOME, ASM's listener port number, and finally the sys password for ASM. Now you are ready to begin.
As the OS user oracle, type the following:
$ emca -config dbcontrol db -repos create -cluster
You will see the following message:
STARTED EMCA at Jun 19, 2009 1:06:45 PMEM Configuration Assistant, Version 10.2.0.1.0 ProductionCopyright (c) 2003, 2005, Oracle. All rights reserved.
Next, you will be prompted to answer a series of questions. In this example, I will include the use of ASM.
Enter the following information:
Database unique name: orcl
Listener port number: 1521
Cluster name: crs1
Password for SYS user: syspassw0rd
Password for DBSNMP user: "syspassw0rd" <- must use quotes!
Password for SYSMAN user: "syspassw0rd" <- must use quotes!
Email address for notifications (optional):
Outgoing Mail (SMTP) server for notifications (optional):
ASM ORACLE_HOME [ /AASSMDBD/oracle/product/rac ]: /u01/app/oracle/product/asm
ASM port [ 1521 ]: 1522
ASM user role [ SYSDBA ]:
ASM username [ SYS ]:
ASM user password: asmpassw0rd
-----------------------------------------------------------------
You have specified the following settings
Database ORACLE_HOME ................ /AASSMDBD/oracle/product/rac
Database instance hostname ................ mine01
Listener port number ................1521
Cluster name ................ crs1
Database unique name ................ orcl
Email address for notifications ...............
Outgoing Mail (SMTP) server for notifications ...............
ASM ORACLE_HOME ................ /u01/app/oracle/product/asm
ASM port ................ 1522
ASM user role ................ SYSDBA
ASM username ................ SYS
-----------------------------------------------------------------
Do you wish to continue? [yes(Y)/no(N)]: y
If everything goes smoothly, you will receive the following message.
Note your Database Control URL! You now can access your database and its instances.
But in the real world, everything doesn't go so smoothly. If an error occurs, you may see something like this:
Jun 19, 2009 1:10:27 PM oracle.sysman.emcp.util.DBControlUtil start
OMSINFO: Starting Database Control (this may take a while) ...
Jun 19, 2009 1:13:07 PM oracle.sysman.emcp.EMConfig perform
SEVERE: Error starting Database Control
Refer to the log file at /u01/app/oracle/product/rac/cfgtoollogs/emca/orcl1/emca_2009-06-19_01-06-45-PM.log for more details.Could not complete the configuration.
Refer to the log file at /u01/oracle/product/rac/cfgtoollogs/emca/orcl1/emca_2009-06-19_01-06-45-PM.log for more details.
Oracle isn't kidding when it says refer to the file. It normally tells you exactly why it failed which is nice for a change. In my case, I did not have my second instance started so I received a CONFIG error message. Whatever the problem is, whether its the fact that SYSMAN existed or you had to drop the user MGMT_VIEW, once you have corrected it you now have to deconfigure the Database Control before starting again. As the user oracle issues the following:
$ emca -deconfig dbcontrol db -repos drop cluster
You will be prompted for answers to the following questions:
Database unique name: orcl
Listener port number: 1521
Password for SYS user: syspassw0rd
Password for SYSMAN user: "syspassw0rd"
In this example, I included the quotes around the sys password but because the user is going to be dropped, its not really necessary.
Once you have finished cleaning up, start over. Sooner or later you will succeed and receive the following message as confirmation of your success:
INFO: Starting Database Control (this may take a while) ...
Jun 19, 2009 2:05:51 PM oracle.sysman.emcp.EMDBPostConfig performConfiguration
INFO: Database Control started successfully
Jun 19, 2009 2:05:51 PM oracle.sysman.emcp.EMDBPostConfig performConfiguration
INFO: >>>>>>>>>>> The Database Control URL is http://mine:1158/em <<<<<<<<<<<
Jun 19, 2009 2:05:51 PM oracle.sysman.emcp.EMDBPostConfig showClusterDBCAgentMessage
INFO:
****************
Current Configuration
****************
INSTANCE NODE DBCONTROL_UPLOAD_HOST
---------- ---------- ---------------------
orcl1 mine mine
orcl2 yours yours
Enterprise Manager configuration completed successfully
FINISHED EMCA at Jun 19, 2009 2:05:51 PM
How to Set Up Database Control
Before you begin running any commands, you will need a few things handy. You will need to know your Database's Unique Name, the listener port number it uses, your sys password, and the cluster name. If you are using ASM you will also need to know the ASM HOME, ASM's listener port number, and finally the sys password for ASM. Now you are ready to begin.
As the OS user oracle, type the following:
$ emca -config dbcontrol db -repos create -cluster
You will see the following message:
STARTED EMCA at Jun 19, 2009 1:06:45 PMEM Configuration Assistant, Version 10.2.0.1.0 ProductionCopyright (c) 2003, 2005, Oracle. All rights reserved.
Next, you will be prompted to answer a series of questions. In this example, I will include the use of ASM.
Enter the following information:
Database unique name: orcl
Listener port number: 1521
Cluster name: crs1
Password for SYS user: syspassw0rd
Password for DBSNMP user: "syspassw0rd" <- must use quotes!
Password for SYSMAN user: "syspassw0rd" <- must use quotes!
Email address for notifications (optional):
Outgoing Mail (SMTP) server for notifications (optional):
ASM ORACLE_HOME [ /AASSMDBD/oracle/product/rac ]: /u01/app/oracle/product/asm
ASM port [ 1521 ]: 1522
ASM user role [ SYSDBA ]:
ASM username [ SYS ]:
ASM user password: asmpassw0rd
-----------------------------------------------------------------
You have specified the following settings
Database ORACLE_HOME ................ /AASSMDBD/oracle/product/rac
Database instance hostname ................ mine01
Listener port number ................1521
Cluster name ................ crs1
Database unique name ................ orcl
Email address for notifications ...............
Outgoing Mail (SMTP) server for notifications ...............
ASM ORACLE_HOME ................ /u01/app/oracle/product/asm
ASM port ................ 1522
ASM user role ................ SYSDBA
ASM username ................ SYS
-----------------------------------------------------------------
Do you wish to continue? [yes(Y)/no(N)]: y
If everything goes smoothly, you will receive the following message.
Note your Database Control URL! You now can access your database and its instances.
But in the real world, everything doesn't go so smoothly. If an error occurs, you may see something like this:
Jun 19, 2009 1:10:27 PM oracle.sysman.emcp.util.DBControlUtil start
OMSINFO: Starting Database Control (this may take a while) ...
Jun 19, 2009 1:13:07 PM oracle.sysman.emcp.EMConfig perform
SEVERE: Error starting Database Control
Refer to the log file at /u01/app/oracle/product/rac/cfgtoollogs/emca/orcl1/emca_2009-06-19_01-06-45-PM.log for more details.Could not complete the configuration.
Refer to the log file at /u01/oracle/product/rac/cfgtoollogs/emca/orcl1/emca_2009-06-19_01-06-45-PM.log for more details.
Oracle isn't kidding when it says refer to the file. It normally tells you exactly why it failed which is nice for a change. In my case, I did not have my second instance started so I received a CONFIG error message. Whatever the problem is, whether its the fact that SYSMAN existed or you had to drop the user MGMT_VIEW, once you have corrected it you now have to deconfigure the Database Control before starting again. As the user oracle issues the following:
$ emca -deconfig dbcontrol db -repos drop cluster
You will be prompted for answers to the following questions:
Database unique name: orcl
Listener port number: 1521
Password for SYS user: syspassw0rd
Password for SYSMAN user: "syspassw0rd"
In this example, I included the quotes around the sys password but because the user is going to be dropped, its not really necessary.
Once you have finished cleaning up, start over. Sooner or later you will succeed and receive the following message as confirmation of your success:
INFO: Starting Database Control (this may take a while) ...
Jun 19, 2009 2:05:51 PM oracle.sysman.emcp.EMDBPostConfig performConfiguration
INFO: Database Control started successfully
Jun 19, 2009 2:05:51 PM oracle.sysman.emcp.EMDBPostConfig performConfiguration
INFO: >>>>>>>>>>> The Database Control URL is http://mine:1158/em <<<<<<<<<<<
Jun 19, 2009 2:05:51 PM oracle.sysman.emcp.EMDBPostConfig showClusterDBCAgentMessage
INFO:
****************
Current Configuration
****************
INSTANCE NODE DBCONTROL_UPLOAD_HOST
---------- ---------- ---------------------
orcl1 mine mine
orcl2 yours yours
Enterprise Manager configuration completed successfully
FINISHED EMCA at Jun 19, 2009 2:05:51 PM
Subscribe to:
Posts (Atom)
