In Apache configuration, a password can be saved in dads.conf file to let users to view webpages with a password. dataTool.pl is the tool to hide the real password. There are a few of environment variables need to be specified before the execution on dadTool.pl works.
1. Make sure $ORACLE_HOME points to the OMS Home. Such as
export ORACLE_HOME=/app/oracle/product/oms10g
2. Here are variables I specified in Grid Control (10.2.0.1 & 10.2.0.4) installations:
export PATH=$ORACLE_HOME/Apache/modplsql/conf:$PATH
export PATH=$ORACLE_HOME/perl/bin:$PATH
export PERL5LIB=$ORACLE_HOME/perl/lib/5.6.1
export LIBPATH=$ORACLE_HOME/lib
export LD_LIBRARY_PATH=$ORACLE_HOME/lib
Note: on AIX (64-bit) server, ponit LIBPATH to $ORACLE_HOME/lib32 (not to the 64 bit libraries):
export LIBPATH=$ORACLE_HOME/lib32
3. The result should be
$ cd $ORACLE_HOME/Apache/modplsql/conf
$ perl dadTool.pl -o
Information
-------------------------------------------------------------
Backed up older dads.conf as /app/oracle/product/oms10g/Apache/modplsql/conf/dads.conf.orig.2009-05-26_14-12
All passwords successfully obfuscated. New obfuscations : 1
Tuesday, May 26, 2009
Tuesday, April 21, 2009
Logon trigger to change CURSOR_SHARING=FORCE
Bind variable placeholders are used to calculate the SQL hash value for looking up an existing cursor in library cache. This should reduce the amount of hard parsing and shared pool garbage. They can also be used for enforcing a SQL profile for SQL statement.
Use a trigger to change CURSOR_SHARING to FORCE in a session while a user is logging onto 10G database and its ID and its program match the record in LOGIN_CURSOR_CTL table. This is a solution for those legacy or 3rd-party applications which use literal values in queries and are not easy to change their code for using binding variable.
CREATE TABLE ORADBA.LOGIN_CURSOR_CTL
( USER_ID VARCHAR2(30) NOT NULL,
MACHINE VARCHAR2(200),
PROGRAM VARCHAR2(200),
CREATE_DT DATE DEFAULT sysdate
)
TABLESPACE USER01;
CREATE OR REPLACE TRIGGER ORADBA.LOGIN_PARSE
AFTER LOGON ON DATABASE
DECLARE
v_db_user VARCHAR2(30);
v_machine VARCHAR2(200);
v_program VARCHAR2(200);
v_os_user VARCHAR2(30);
v_count INTEGER;
BEGIN
v_db_username := SYS_CONTEXT('USERENV','SESSION_USER');
v_machine := SYS_CONTEXT('USERENV', 'HOST');
v_program := SYS_CONTEXT('USERENV', 'MODULE');
v_os_user := SYS_CONTEXT('USERENV','OS_USER');
SELECT COUNT(*) INTO v_count FROM LOGIN_CURSOR_CTL
WHERE USER_ID=v_db_user AND NVL(machine,v_machine)=v_machine
AND LOWER(v_program) LIKE LOWER(NVL(PROGRAM,'%'))
; -- Note: NULL values of program/machine of LOGIN_CURSOR_CTL means "ANY" cases
IF v_count >=1 THEN
EXECUTE IMMEDIATE ('ALTER SESSION SET CURSOR_SHARING=''FORCE''');
INSERT INTO LOGIN_AUDIT (username, machine, PROGRAM, osuser, login_time )
VALUES (v_db_user ,v_machine, v_program, v_os_user, SYSDATE );
END IF; -- LOGIN_AUDIT is another table.
END;
/
Check bind variable is used or not:
SQL> select /* WXYZ */ * from dba_objects where object_name = 'ABCD';
SQL> select sql_text, executions, parse_calls, last_active_time FROM v$sql where sql_text like '%WXYZ%' ;
Use a trigger to change CURSOR_SHARING to FORCE in a session while a user is logging onto 10G database and its ID and its program match the record in LOGIN_CURSOR_CTL table. This is a solution for those legacy or 3rd-party applications which use literal values in queries and are not easy to change their code for using binding variable.
CREATE TABLE ORADBA.LOGIN_CURSOR_CTL
( USER_ID VARCHAR2(30) NOT NULL,
MACHINE VARCHAR2(200),
PROGRAM VARCHAR2(200),
CREATE_DT DATE DEFAULT sysdate
)
TABLESPACE USER01;
CREATE OR REPLACE TRIGGER ORADBA.LOGIN_PARSE
AFTER LOGON ON DATABASE
DECLARE
v_db_user VARCHAR2(30);
v_machine VARCHAR2(200);
v_program VARCHAR2(200);
v_os_user VARCHAR2(30);
v_count INTEGER;
BEGIN
v_db_username := SYS_CONTEXT('USERENV','SESSION_USER');
v_machine := SYS_CONTEXT('USERENV', 'HOST');
v_program := SYS_CONTEXT('USERENV', 'MODULE');
v_os_user := SYS_CONTEXT('USERENV','OS_USER');
SELECT COUNT(*) INTO v_count FROM LOGIN_CURSOR_CTL
WHERE USER_ID=v_db_user AND NVL(machine,v_machine)=v_machine
AND LOWER(v_program) LIKE LOWER(NVL(PROGRAM,'%'))
; -- Note: NULL values of program/machine of LOGIN_CURSOR_CTL means "ANY" cases
IF v_count >=1 THEN
EXECUTE IMMEDIATE ('ALTER SESSION SET CURSOR_SHARING=''FORCE''');
INSERT INTO LOGIN_AUDIT (username, machine, PROGRAM, osuser, login_time )
VALUES (v_db_user ,v_machine, v_program, v_os_user, SYSDATE );
END IF; -- LOGIN_AUDIT is another table.
END;
/
Check bind variable is used or not:
SQL> select /* WXYZ */ * from dba_objects where object_name = 'ABCD';
SQL> select sql_text, executions, parse_calls, last_active_time FROM v$sql where sql_text like '%WXYZ%' ;
Friday, April 17, 2009
Re-install Grid Control Agent (10.2.0.4)
Here are steps to re-install Grid Control agent on db_server:
0. Have a blackout on the host level to avoid all kinds of GC alerts.
1. Remove Host db_server from the GC site at http://gc_server:7777/em/console/logon/logon , use "Super Administrator" account.
Note: remove the listerner, databases first, then remove the Agent and Host as the last step after the Agent is stopped.
2. On db_server, login as oracle. Stop the Agent and make sure "ps -ef | grep emagent" does not return anything. Then run .../runInstaller to just remove agent10g Home from the server.
3. Remove or rename folder $ORACLE_BASE/product/agent10g. Create a new folder, say, $ORACLE_BASE/product/Agent204download to hold the script.
4. Go to the new folder, and run wget http://gc_server:4889/agent_download/10.2.0.4.0/aix/agentDownload.aix
5. The easiest way is to make a shell script to launch the installation. Here is the content of my file agentinstall.sh
#!/bin/sh
export ORACLE_BASE=/u01/app/oracle
export PATH=$PWD:$PATH
export ORACLE_HOME=$ORACLE_BASE/product/agent10g
export AGENT_HOME=$ORACLE_BASE/product/agent10
./agentDownload.aix -b $ORACLE_BASE/product > agentdownload.log
6. Run the script to fire the installation process
./agentinstall.sh
If the screen is not moving (hanging), hit “Enter” key once during the entire installation!! You may open a different Putty (OS) session to view the log for monitoring the process.
Troubleshootings:
1. Make sure the db_server name is defined in /etc/hosts file.
2. "$ nslookup db_server" to check the IP address and hostname.
3. "$ netstat -an | grep 3872" to verify port is not used (Note 443524.1)
4. On the server where the entire $ORACLE_BASE (Oracle binary) was copied from a different server by SAN COPY on disk level, the Agent installation keeps getting the wrong hostname. I have to add ORACLE_HOSTNAME=db_server to the end of the "InstallCmd=" line in the agentDownload.aix script to force the right hostname (after reading Note 370300.1).
0. Have a blackout on the host level to avoid all kinds of GC alerts.
1. Remove Host db_server from the GC site at http://gc_server:7777/em/console/logon/logon , use "Super Administrator" account.
Note: remove the listerner, databases first, then remove the Agent and Host as the last step after the Agent is stopped.
2. On db_server, login as oracle. Stop the Agent and make sure "ps -ef | grep emagent" does not return anything. Then run .../runInstaller to just remove agent10g Home from the server.
3. Remove or rename folder $ORACLE_BASE/product/agent10g. Create a new folder, say, $ORACLE_BASE/product/Agent204download to hold the script.
4. Go to the new folder, and run wget http://gc_server:4889/agent_download/10.2.0.4.0/aix/agentDownload.aix
5. The easiest way is to make a shell script to launch the installation. Here is the content of my file agentinstall.sh
#!/bin/sh
export ORACLE_BASE=/u01/app/oracle
export PATH=$PWD:$PATH
export ORACLE_HOME=$ORACLE_BASE/product/agent10g
export AGENT_HOME=$ORACLE_BASE/product/agent10
./agentDownload.aix -b $ORACLE_BASE/product > agentdownload.log
6. Run the script to fire the installation process
./agentinstall.sh
If the screen is not moving (hanging), hit “Enter” key once during the entire installation!! You may open a different Putty (OS) session to view the log for monitoring the process.
Troubleshootings:
1. Make sure the db_server name is defined in /etc/hosts file.
2. "$ nslookup db_server" to check the IP address and hostname.
3. "$ netstat -an | grep 3872" to verify port is not used (Note 443524.1)
4. On the server where the entire $ORACLE_BASE (Oracle binary) was copied from a different server by SAN COPY on disk level, the Agent installation keeps getting the wrong hostname. I have to add ORACLE_HOSTNAME=db_server to the end of the "InstallCmd=" line in the agentDownload.aix script to force the right hostname (after reading Note 370300.1).
Friday, March 20, 2009
Errors from Using DATAPUMP
Datapump moves data on block level and is faster than Export/Import. It took about 4 hours to export 360GB dump data, and took 13 and half hours to import it on AIX servers with 4 physical CUPs.
Here are some errors I ran into, and the fixes:
- The OS folder can not be recognized by datapump:
ORA-39001: invalid argument value
ORA-39000: bad dump file specification
ORA-31641: unable to create dump file "/images/DB_full.dmp"
ORA-27054: NFS file system where the file is created or resides is not mounted with correct options
Fix (Note 370513.1): Mount the disk by using the recommended mounting options which are: (hard,bg,proto=tcp,suid,rsize=32768,wsize=32768,noac)
- Datapump can not locate the directory:
ORA-39002: invalid operation
ORA-39070: Unable to open the log file.
ORA-29283: invalid file operation
ORA-06512: at "SYS.UTL_FILE", line 475
ORA-29283: invalid file operation
Fix: Make sure to include "/" at the beginning (or the end) of the path, such as '/home/oracle/dumdir' when creating the DIRECTORY in the database.
- On a RAC environment, got errors when running datapump on one of the nodes:
ORA-31626: job does not exist
ORA-31650: timeout waiting for master process response
Wed Mar 18 19:58:06 2009
kwqbcsrmsg: Timeout contacting inst 2 for buffered oper 1,
IPC ret status=36
queue = SYS KUPC$S_1_20090318195629
kupprdp: master process DM00 started with pid=38, OS id=799160
to execute - SYS.KUPM$MCP.MAIN('SYS_EXPORT_FULL_01', 'SYSTEM', 'KUPC$C_1_20090318195629', 'KUPC$S_1_20090318195629', 0);
Wed Mar 18 19:59:00 2009
Fix (Note 454639.1): "alter system set aq_tm_processes=1"
SQL> select value from v$parameter
where name = 'aq_tm_processes';
VALUE
------
0
SQL> alter system set aq_tm_processes=1 scope=both;
SQL> select value from v$parameter
where name = 'aq_tm_processes';
VALUE
------
1
- Got below errors from datapump, and ORA-600 in alert log:
UDE-00008: operation generated ORACLE error 31623
ORA-31623: a job is not attached to this session via the specified handle
ORA-06512: at "SYS.DBMS_DATAPUMP", line 2745
ORA-06512: at "SYS.DBMS_DATAPUMP", line 3712
ORA-06512: at line 1
ORA-00600: internal error code, arguments: [kwqbgqc: bad state], [1], [1], [], [], [], [], []
Fix (Note 754401.1): Re-create the SYS.KUPC$DATAPUMP_QUETAB queue.
SQL> startup restrict
SQL> exec dbms_aqadm.drop_queue_table(queue_table=>'SYS.KUPC$DATAPUMP_QUETAB',force=>TRUE);
PL/SQL procedure successfully completed.
SQL> BEGIN
2 dbms_aqadm.create_queue_table(queue_table => 'SYS.KUPC$DATAPUMP_QUETAB',
3 multiple_consumers => TRUE,
4 queue_payload_type =>'SYS.KUPC$_MESSAGE',
5 comment => 'DataPump Queue Table',
6 compatible=>'8.1.3');
7 EXCEPTION
8 WHEN OTHERS THEN
9 IF SQLCODE = -24001 THEN NULL;
10 ELSE RAISE;
11 END IF;
12 END;
13 /
PL/SQL procedure successfully completed.
Here are some errors I ran into, and the fixes:
- The OS folder can not be recognized by datapump:
ORA-39001: invalid argument value
ORA-39000: bad dump file specification
ORA-31641: unable to create dump file "/images/DB_full.dmp"
ORA-27054: NFS file system where the file is created or resides is not mounted with correct options
Fix (Note 370513.1): Mount the disk by using the recommended mounting options which are: (hard,bg,proto=tcp,suid,rsize=32768,wsize=32768,noac)
- Datapump can not locate the directory:
ORA-39002: invalid operation
ORA-39070: Unable to open the log file.
ORA-29283: invalid file operation
ORA-06512: at "SYS.UTL_FILE", line 475
ORA-29283: invalid file operation
Fix: Make sure to include "/" at the beginning (or the end) of the path, such as '/home/oracle/dumdir' when creating the DIRECTORY in the database.
- On a RAC environment, got errors when running datapump on one of the nodes:
ORA-31626: job does not exist
ORA-31650: timeout waiting for master process response
Wed Mar 18 19:58:06 2009
kwqbcsrmsg: Timeout contacting inst 2 for buffered oper 1,
IPC ret status=36
queue = SYS KUPC$S_1_20090318195629
kupprdp: master process DM00 started with pid=38, OS id=799160
to execute - SYS.KUPM$MCP.MAIN('SYS_EXPORT_FULL_01', 'SYSTEM', 'KUPC$C_1_20090318195629', 'KUPC$S_1_20090318195629', 0);
Wed Mar 18 19:59:00 2009
Fix (Note 454639.1): "alter system set aq_tm_processes=1"
SQL> select value from v$parameter
where name = 'aq_tm_processes';
VALUE
------
0
SQL> alter system set aq_tm_processes=1 scope=both;
SQL> select value from v$parameter
where name = 'aq_tm_processes';
VALUE
------
1
- Got below errors from datapump, and ORA-600 in alert log:
UDE-00008: operation generated ORACLE error 31623
ORA-31623: a job is not attached to this session via the specified handle
ORA-06512: at "SYS.DBMS_DATAPUMP", line 2745
ORA-06512: at "SYS.DBMS_DATAPUMP", line 3712
ORA-06512: at line 1
ORA-00600: internal error code, arguments: [kwqbgqc: bad state], [1], [1], [], [], [], [], []
Fix (Note 754401.1): Re-create the SYS.KUPC$DATAPUMP_QUETAB queue.
SQL> startup restrict
SQL> exec dbms_aqadm.drop_queue_table(queue_table=>'SYS.KUPC$DATAPUMP_QUETAB',force=>TRUE);
PL/SQL procedure successfully completed.
SQL> BEGIN
2 dbms_aqadm.create_queue_table(queue_table => 'SYS.KUPC$DATAPUMP_QUETAB',
3 multiple_consumers => TRUE,
4 queue_payload_type =>'SYS.KUPC$_MESSAGE',
5 comment => 'DataPump Queue Table',
6 compatible=>'8.1.3');
7 EXCEPTION
8 WHEN OTHERS THEN
9 IF SQLCODE = -24001 THEN NULL;
10 ELSE RAISE;
11 END IF;
12 END;
13 /
PL/SQL procedure successfully completed.
Thursday, March 19, 2009
Paralle Degree in Creating MVW
When using below statement to create a materialized view, I find that it not only creates the materialized view with a DEFAULT degree, but also uses parallelism of DEFAULT degree to create the materialized view.
CREATE MATERIALIZED VIEW DSS_DIM_MVW
TABLESPACE PDW_DATA
NOCACHE
NOLOGGING
NOCOMPRESS
PARALLEL ( DEGREE DEFAULT INSTANCES 1 )
BUILD IMMEDIATE
REFRESH COMPLETE ON DEMAND
WITH PRIMARY KEY
AS
SELECT "......query from multiple tables of DOP 1"
;
On the server with 4 LCPU, the database uses 2 slave sets of each 8 (4 * 2, default) parallel processes to run the statement. So totally, 16 slaves are recuited to run the creation. The two slaves in the first slave set could be scanning a table and passing the results to the slaves in slave set 2 for sorting.
After the query finishes, the materialized view gets DEFAULT as its DOP.
CREATE MATERIALIZED VIEW DSS_DIM_MVW
TABLESPACE PDW_DATA
NOCACHE
NOLOGGING
NOCOMPRESS
PARALLEL ( DEGREE DEFAULT INSTANCES 1 )
BUILD IMMEDIATE
REFRESH COMPLETE ON DEMAND
WITH PRIMARY KEY
AS
SELECT "......query from multiple tables of DOP 1"
;
On the server with 4 LCPU, the database uses 2 slave sets of each 8 (4 * 2, default) parallel processes to run the statement. So totally, 16 slaves are recuited to run the creation. The two slaves in the first slave set could be scanning a table and passing the results to the slaves in slave set 2 for sorting.
After the query finishes, the materialized view gets DEFAULT as its DOP.
Subscribe to:
Comments (Atom)