The statement "select * from table_name where ... for update;" will hold rows of data for next DML statements in the same session. The selected rows is locked until a "commit" or a "rollback" command is issued. Any other session (even only a "select" statement) accessing those rows will have to wait until the lock is released. The Select For Update is easy to create a deadlock in the database. The lock can be seen from views:
SQL> select * from v$locked_object;
SQL> select * from v$transaction;
In a normal select, REDO is not used. But, the Select For Update will use REDO space.
SQL> set autotrace traceonly statistics
SQL> select * from test4update for update;
412167 rows selected.
Statistics
------------------------------------------------------
288 recursive calls
419550 db block gets
6003 consistent gets
0 physical reads
85955168 redo size 31676930 bytes sent via SQL*Net to client
192619 bytes received via SQL*Net from client
27479 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
412167 rows processed
Tuesday, November 30, 2010
Sunday, October 24, 2010
Grant ACL to APPS account after database upgraded to 11g
After database for E-Buiness Suite is upgraded to 11g, you have to grant ACL privilege to APPS account for some EBS modules to work. Here phebsapp is the EBS Apps server name.
1. Check if view dba_network_acls has any row. If yes, run below script to add privilege to APPS account:
DECLARE
acl_path VARCHAR2(4000);
BEGIN
SELECT acl INTO acl_path FROM dba_network_acls
WHERE host = 'phebsapp' AND lower_port IS NULL AND upper_port IS NULL;
IF DBMS_NETWORK_ACL_ADMIN.CHECK_PRIVILEGE(acl_path,'APPS','connect') IS NULL THEN
DBMS_NETWORK_ACL_ADMIN.ADD_PRIVILEGE(acl_path,'APPS', TRUE, 'connect');
DBMS_NETWORK_ACL_ADMIN.ADD_PRIVILEGE(acl_path,'APPS', TRUE, 'resolve');
END IF;
EXCEPTION
WHEN no_data_found THEN
-- below lines will create a ACL
DBMS_NETWORK_ACL_ADMIN.CREATE_ACL('apps.xml','ACL description', 'APPS', TRUE, 'connect');
DBMS_NETWORK_ACL_ADMIN.ADD_PRIVILEGE('apps.xml','APPS', TRUE, 'resolve');
DBMS_NETWORK_ACL_ADMIN.ASSIGN_ACL('apps.xml','phebsapp');
END;
/
SQL> commit;
Please note "commit;" is necessary and very important. After that, you should see the entries in two views:
SQL> select * FROM dba_network_acls;
SQL> select * from dba_network_acl_privileges;
If you do not see rows, something is wrong.
And below two queries should return 1:
SQL> select DBMS_NETWORK_ACL_ADMIN.CHECK_PRIVILEGE('/sys/acls/apps.xml','APPS','connect') from dual;
SQL> select DBMS_NETWORK_ACL_ADMIN.CHECK_PRIVILEGE('/sys/acls/apps.xml','APPS','resolve') from dual;
2. if view dba_network_acls has no data. You can create ACL for the user:
SQL> show user
USER is "SYS"
BEGIN
DBMS_NETWORK_ACL_ADMIN.CREATE_ACL('apps.xml','APPS acess for UTL', 'APPS', TRUE, 'connect');
DBMS_NETWORK_ACL_ADMIN.ADD_PRIVILEGE(acl => 'apps.xml', principal => 'APPS',is_grant => true, privilege => 'resolve');
DBMS_NETWORK_ACL_ADMIN.ASSIGN_ACL('apps.xml','phebsapp');
END;
/
Sometimes, the domain name makes difference. It does not hurt to add full name to the access list:
BEGIN
DBMS_NETWORK_ACL_ADMIN.ASSIGN_ACL('apps.xml','phebsapp.domain.com');
END;
/
SQL> commit;
3. If things does not work, you can start over by dropping entries in dba_network_acls.
SQL> begin
DBMS_NETWORK_ACL_ADMIN.DROP_ACL('apps.xml');
commit;
end;
/
Now dba_network_acls, dba_network_acl_privileges, net$_acl should have nothing on apps.xml and everything no ACL is cleaned up.
SQL> select * from dba_network_acls;
SQL> select * from dba_network_acl_privileges;
SQL> select * from net$_acl;
Then, you can start over again.
1. Check if view dba_network_acls has any row. If yes, run below script to add privilege to APPS account:
DECLARE
acl_path VARCHAR2(4000);
BEGIN
SELECT acl INTO acl_path FROM dba_network_acls
WHERE host = 'phebsapp' AND lower_port IS NULL AND upper_port IS NULL;
IF DBMS_NETWORK_ACL_ADMIN.CHECK_PRIVILEGE(acl_path,'APPS','connect') IS NULL THEN
DBMS_NETWORK_ACL_ADMIN.ADD_PRIVILEGE(acl_path,'APPS', TRUE, 'connect');
DBMS_NETWORK_ACL_ADMIN.ADD_PRIVILEGE(acl_path,'APPS', TRUE, 'resolve');
END IF;
EXCEPTION
WHEN no_data_found THEN
-- below lines will create a ACL
DBMS_NETWORK_ACL_ADMIN.CREATE_ACL('apps.xml','ACL description', 'APPS', TRUE, 'connect');
DBMS_NETWORK_ACL_ADMIN.ADD_PRIVILEGE('apps.xml','APPS', TRUE, 'resolve');
DBMS_NETWORK_ACL_ADMIN.ASSIGN_ACL('apps.xml','phebsapp');
END;
/
SQL> commit;
Please note "commit;" is necessary and very important. After that, you should see the entries in two views:
SQL> select * FROM dba_network_acls;
SQL> select * from dba_network_acl_privileges;
If you do not see rows, something is wrong.
And below two queries should return 1:
SQL> select DBMS_NETWORK_ACL_ADMIN.CHECK_PRIVILEGE('/sys/acls/apps.xml','APPS','connect') from dual;
SQL> select DBMS_NETWORK_ACL_ADMIN.CHECK_PRIVILEGE('/sys/acls/apps.xml','APPS','resolve') from dual;
2. if view dba_network_acls has no data. You can create ACL for the user:
SQL> show user
USER is "SYS"
BEGIN
DBMS_NETWORK_ACL_ADMIN.CREATE_ACL('apps.xml','APPS acess for UTL', 'APPS', TRUE, 'connect');
DBMS_NETWORK_ACL_ADMIN.ADD_PRIVILEGE(acl => 'apps.xml', principal => 'APPS',is_grant => true, privilege => 'resolve');
DBMS_NETWORK_ACL_ADMIN.ASSIGN_ACL('apps.xml','phebsapp');
END;
/
Sometimes, the domain name makes difference. It does not hurt to add full name to the access list:
BEGIN
DBMS_NETWORK_ACL_ADMIN.ASSIGN_ACL('apps.xml','phebsapp.domain.com');
END;
/
SQL> commit;
3. If things does not work, you can start over by dropping entries in dba_network_acls.
SQL> begin
DBMS_NETWORK_ACL_ADMIN.DROP_ACL('apps.xml');
commit;
end;
/
Now dba_network_acls, dba_network_acl_privileges, net$_acl should have nothing on apps.xml and everything no ACL is cleaned up.
SQL> select * from dba_network_acls;
SQL> select * from dba_network_acl_privileges;
SQL> select * from net$_acl;
Then, you can start over again.
Friday, October 15, 2010
Use DBMS_SCHEDULER to schedule jobs
In Oracle 10g and 11g, there are two places holding scheduled jobs:
DBA_SCHEDULER_JOBS
DBA_JOBS
Jobs in DBA_SCHEDULER_JOBS are managed by package DBMS_SCHEDULER, while jobs in DBA_JOBS are managed by package DBA_JOBS.
Here is an example on using DBMS_SCHEDULER to schedule a job:
BEGIN
DBMS_SCHEDULER.CREATE_JOB(
job_name=>'FIXED_OBJECTS_STATUS',
job_type=>'PLSQL_BLOCK',
job_action=>'BEGIN DBMS_STATS.GATHER_FIXED_OBJECTS_STATS; DBMS_STATS.GATHER_SCHEMA_STATS(''SYS'');END;',
REPEAT_INTERVAL=>'FREQ=MONTHLY;BYMONTH=1,7;BYDAY=1 SUN;BYHOUR=14;BYMINUTE=16;BYSECOND=0',
ENABLED=>TRUE
);
END;
/
After that, you can see the job:
SQL> SELECT owner, job_name, job_class, enabled, next_run_date
FROM dba_scheduler_jobs WHERE job_name ='FIXED_OBJECTS_STATUS';
And, run it and check the log. View DBA_SCHEDULER_JOB_RUN_DETAILS holds the execution history for jobs in DBA_SCHEDULER_JOBS.
SQL> exec DBMS_SCHEDULER.run_job('FIXED_OBJECTS_STATUS');
SQL> select job_name, actual_start_date, status, job_name
FROM dba_scheduler_job_run_details WHERE job_name = 'FIXED_OBJECTS_STATUS' ;
Below line will disable a job:
SQL> exec dbms_scheduler.disable('AUTO_SPACE_ADVISOR_JOB');
Recently, a database gave errors in alert log:
Errors in file /u01/app/oracle/admin/dw/bdump/dw_j000_31068352.trc:
ORA-12012: error on auto execute of job 8912
ORA-08103: object no longer exists
I ran query to verify it is a scheduled job and to identify the job name:
SQL> select obj# , object_name FROM sys.scheduler$_job , dba_objects
WHERE obj# = object_id and obj# = 8912;
OBJ# OBJECT_NAME
-------- -----------------
8912 AUTO_SPACE_ADVISOR_JOB
Then I used dbms_scheduler to disable the job.
NOTES:
When I query DBA_SCHEDULER_JOBS using Sql*plus on the database server, I do not get error. But I get error when using Sql*plus on my Windows PC:
SQL> select * from DBA_SCHEDULER_JOBS;
ERROR:
ORA-01882: timezone region %s not found
no rows selected
Check document 414590.1 indicating the DST package on my PC needs an update.
DBA_SCHEDULER_JOBS
DBA_JOBS
Jobs in DBA_SCHEDULER_JOBS are managed by package DBMS_SCHEDULER, while jobs in DBA_JOBS are managed by package DBA_JOBS.
Here is an example on using DBMS_SCHEDULER to schedule a job:
BEGIN
DBMS_SCHEDULER.CREATE_JOB(
job_name=>'FIXED_OBJECTS_STATUS',
job_type=>'PLSQL_BLOCK',
job_action=>'BEGIN DBMS_STATS.GATHER_FIXED_OBJECTS_STATS; DBMS_STATS.GATHER_SCHEMA_STATS(''SYS'');END;',
REPEAT_INTERVAL=>'FREQ=MONTHLY;BYMONTH=1,7;BYDAY=1 SUN;BYHOUR=14;BYMINUTE=16;BYSECOND=0',
ENABLED=>TRUE
);
END;
/
After that, you can see the job:
SQL> SELECT owner, job_name, job_class, enabled, next_run_date
FROM dba_scheduler_jobs WHERE job_name ='FIXED_OBJECTS_STATUS';
And, run it and check the log. View DBA_SCHEDULER_JOB_RUN_DETAILS holds the execution history for jobs in DBA_SCHEDULER_JOBS.
SQL> exec DBMS_SCHEDULER.run_job('FIXED_OBJECTS_STATUS');
SQL> select job_name, actual_start_date, status, job_name
FROM dba_scheduler_job_run_details WHERE job_name = 'FIXED_OBJECTS_STATUS' ;
Below line will disable a job:
SQL> exec dbms_scheduler.disable('AUTO_SPACE_ADVISOR_JOB');
Recently, a database gave errors in alert log:
Errors in file /u01/app/oracle/admin/dw/bdump/dw_j000_31068352.trc:
ORA-12012: error on auto execute of job 8912
ORA-08103: object no longer exists
I ran query to verify it is a scheduled job and to identify the job name:
SQL> select obj# , object_name FROM sys.scheduler$_job , dba_objects
WHERE obj# = object_id and obj# = 8912;
OBJ# OBJECT_NAME
-------- -----------------
8912 AUTO_SPACE_ADVISOR_JOB
Then I used dbms_scheduler to disable the job.
NOTES:
When I query DBA_SCHEDULER_JOBS using Sql*plus on the database server, I do not get error. But I get error when using Sql*plus on my Windows PC:
SQL> select * from DBA_SCHEDULER_JOBS;
ERROR:
ORA-01882: timezone region %s not found
no rows selected
Check document 414590.1 indicating the DST package on my PC needs an update.
Thursday, October 7, 2010
RMAN script for duplicating database
I use below script to refresh our Dev database from Production database monthly, by using the Veritas backup of production database on the tape. It worked well.
ORACLE_SID=devebs ## target database name
export ORACLE_SID
export TNS_ADMIN=$ORACLE_HOME/network/admin
ORACLE_USER=oracle
NLS_LANG=AMERICAN_AMERICA.AL32UTF8
NLS_DATE_FORMAT='YYYY-MM-DD hh24:mi:ss'
export NLS_LANG
export NLS_DATE_FORMAT
NB_ORA_CLASS=ebsdb1p ## server name hosting source database PRODEBS
export NB_ORA_CLASS
DATE=`date +"%h%d%y_%H%M"`
rman catalog rman/rman_pwd@rman_db target sys/source_db_pwd@PRODEBS auxiliary / msglog devEBS_refresh.${DATE}.log << EOF
run {
ALLOCATE AUXILIARY CHANNEL ch00 TYPE 'SBT_TAPE';
ALLOCATE AUXILIARY CHANNEL ch01 TYPE 'SBT_TAPE';
ALLOCATE AUXILIARY CHANNEL ch02 TYPE 'SBT_TAPE';
SEND 'NB_ORA_CLIENT=ebsdb1p';
# Optional: put Temp in /u04 because of the size
SET NEWNAME FOR TEMPFILE 1 to '/u04/oracle/oradata/devebs/temp01.dbf';
SET NEWNAME FOR TEMPFILE 2 to '/u03/oracle/oradata/devebs/temp02.dbf';
duplicate target database to devebs
UNTIL scn = 5960105731382
;
}
EOF
In the script, statement "UNTIL scn = 5960105731382" is important. Without it, the script will NOT restore necessary archive logs for recovering the database and the refresh will fail (as tested in 10.2.0.4) unless you can manually get the archive logs from production server (which is not easy in ASM environment) and then do a manual recovery on the dev server.
In that case, the script only restores the data files from the latest backup to the target. You need to get all archive logs generated during the period when the latest backup started to the latest backup finished.
If you need to duplicate the target database on the same host with a different database SID, similar script can be used. See Document 73912.1 for more details.
ORACLE_SID=devebs ## target database name
export ORACLE_SID
export TNS_ADMIN=$ORACLE_HOME/network/admin
ORACLE_USER=oracle
NLS_LANG=AMERICAN_AMERICA.AL32UTF8
NLS_DATE_FORMAT='YYYY-MM-DD hh24:mi:ss'
export NLS_LANG
export NLS_DATE_FORMAT
NB_ORA_CLASS=ebsdb1p ## server name hosting source database PRODEBS
export NB_ORA_CLASS
DATE=`date +"%h%d%y_%H%M"`
rman catalog rman/rman_pwd@rman_db target sys/source_db_pwd@PRODEBS auxiliary / msglog devEBS_refresh.${DATE}.log << EOF
run {
ALLOCATE AUXILIARY CHANNEL ch00 TYPE 'SBT_TAPE';
ALLOCATE AUXILIARY CHANNEL ch01 TYPE 'SBT_TAPE';
ALLOCATE AUXILIARY CHANNEL ch02 TYPE 'SBT_TAPE';
SEND 'NB_ORA_CLIENT=ebsdb1p';
# Optional: put Temp in /u04 because of the size
SET NEWNAME FOR TEMPFILE 1 to '/u04/oracle/oradata/devebs/temp01.dbf';
SET NEWNAME FOR TEMPFILE 2 to '/u03/oracle/oradata/devebs/temp02.dbf';
duplicate target database to devebs
UNTIL scn = 5960105731382
;
}
EOF
In the script, statement "UNTIL scn = 5960105731382" is important. Without it, the script will NOT restore necessary archive logs for recovering the database and the refresh will fail (as tested in 10.2.0.4) unless you can manually get the archive logs from production server (which is not easy in ASM environment) and then do a manual recovery on the dev server.
In that case, the script only restores the data files from the latest backup to the target. You need to get all archive logs generated during the period when the latest backup started to the latest backup finished.
If you need to duplicate the target database on the same host with a different database SID, similar script can be used. See Document 73912.1 for more details.
Tuesday, September 28, 2010
CPU Usage by User Session
Oracle system views can be used to find the amount of CPU consumed by each database session. This script will display the CPU information:
SELECT
s.username, t.SID, p.spid DB_OS_ID,
t.VALUE/100 cpu_usage_seconds
FROM v$session s, v$sesstat t, v$statname n, v$process p
WHERE t.STATISTIC# = n.STATISTIC#
and n.NAME like '%CPU used by this session%'
and t.SID = s.SID
and s.paddr(+) = p.addr
and s.status='ACTIVE'
and s.username is not null
order by VALUE desc;
The OS commend "ps -ef" also gives CPU info in the 4th column of the output.
To troubleshoot performance issue, you can use below query and the session ID from above query to find the SQL statement for tuning candidate:
SELECT s.username, s.osuser, s.machine, s.program, s.status, s.sid, s.serial#, s.process client_pid, p.spid DB_os_pid, t.sql_text, t.disk_reads, t.executions
FROM v$session s, v$process p, v$sqlarea t
WHERE s.paddr = p.addr and s.sql_address = t.address(+)
and s.sid = &SID
In Oracle 11g, if the init parameter audit_trail is set to DB level by
SQL> alter system set audit_trail='DB' scope=spfile;
And then bounce the database, view dba_audit_session (or sys.aud$) has a column SESSION_CPU displaying session's CPU usage.
SELECT
s.username, t.SID, p.spid DB_OS_ID,
t.VALUE/100 cpu_usage_seconds
FROM v$session s, v$sesstat t, v$statname n, v$process p
WHERE t.STATISTIC# = n.STATISTIC#
and n.NAME like '%CPU used by this session%'
and t.SID = s.SID
and s.paddr(+) = p.addr
and s.status='ACTIVE'
and s.username is not null
order by VALUE desc;
The OS commend "ps -ef" also gives CPU info in the 4th column of the output.
To troubleshoot performance issue, you can use below query and the session ID from above query to find the SQL statement for tuning candidate:
SELECT s.username, s.osuser, s.machine, s.program, s.status, s.sid, s.serial#, s.process client_pid, p.spid DB_os_pid, t.sql_text, t.disk_reads, t.executions
FROM v$session s, v$process p, v$sqlarea t
WHERE s.paddr = p.addr and s.sql_address = t.address(+)
and s.sid = &SID
In Oracle 11g, if the init parameter audit_trail is set to DB level by
SQL> alter system set audit_trail='DB' scope=spfile;
And then bounce the database, view dba_audit_session (or sys.aud$) has a column SESSION_CPU displaying session's CPU usage.
Friday, September 24, 2010
Memory used by a DB session
When a database session is slow, it may be helpful on finding the cause by checking the memory consumed by the session.
1. Identify the session ID (SID), if necessary. Such as:
select * from v$session where lower(program) like '%exe%';
2. Identify the process ID (DB_os_pid / spid) on the Database server:
-- If you know the database sesssion ID, run below query
Select s.username, s.osuser, s.machine, s.program, s.status, s.sid, s.serial#, s.process client_pid, p.spid DB_os_pid, t.sql_text, t.disk_reads, t.executions
from v$session s, v$process p, v$sqlarea t
where s.paddr = p.addr and s.sql_address = t.address(+)
and s.sid = &SID
;
-- If you know the process ID on the client machine (such as Apps server)
Select s.username, s.osuser, s.machine, s.program, s.status, s.sid, s.serial#, s.process client_pid, p.spid DB_os_pid, t.sql_text, t.disk_reads, t.executions
from v$session s, v$process p, v$sqlarea t
where s.paddr = p.addr and s.sql_address = t.address(+)
and s.process = '&clientpid' -- the process ID on the client/MT server
;
3. Run query to see how much memory is used by the session:
select spid, program, PGA_USED_MEM/1024/1024 memory_mb from v$process
where spid = DB_os_pid;
4. Run about query from time to time when the database session is ACTIVE. If the memory value keeps increasing, there may be a memory leak on the session.
5. On the OS level, run "ps -ef | grep 21561522" to confirm the session time and other characters (assuming 21561522 is the DB_os_pid).
1. Identify the session ID (SID), if necessary. Such as:
select * from v$session where lower(program) like '%exe%';
2. Identify the process ID (DB_os_pid / spid) on the Database server:
-- If you know the database sesssion ID, run below query
Select s.username, s.osuser, s.machine, s.program, s.status, s.sid, s.serial#, s.process client_pid, p.spid DB_os_pid, t.sql_text, t.disk_reads, t.executions
from v$session s, v$process p, v$sqlarea t
where s.paddr = p.addr and s.sql_address = t.address(+)
and s.sid = &SID
;
-- If you know the process ID on the client machine (such as Apps server)
Select s.username, s.osuser, s.machine, s.program, s.status, s.sid, s.serial#, s.process client_pid, p.spid DB_os_pid, t.sql_text, t.disk_reads, t.executions
from v$session s, v$process p, v$sqlarea t
where s.paddr = p.addr and s.sql_address = t.address(+)
and s.process = '&clientpid' -- the process ID on the client/MT server
;
3. Run query to see how much memory is used by the session:
select spid, program, PGA_USED_MEM/1024/1024 memory_mb from v$process
where spid = DB_os_pid;
4. Run about query from time to time when the database session is ACTIVE. If the memory value keeps increasing, there may be a memory leak on the session.
5. On the OS level, run "ps -ef | grep 21561522" to confirm the session time and other characters (assuming 21561522 is the DB_os_pid).
Tuesday, September 21, 2010
SQLPLUS Hang
Discoverer users reports to me that they can not run Discoverer Plus reports. On the "Request Progress" screen after entering Discoverer login information, they get the little clock has been around multiple times without making progress.
To address the issue, I stopped the Discoverer services on the server, and then failed to start them. To make sure there is no problem on the Discoverer configuration, I tested Sql*Plus on the server and found it hung by just staying on the logging in:
SQL> connect userID/passwd@TNS_STRING
The strange thing was that if I logged onto the same Linux (SuSe 10) server as a different OS user using LDAP Authentication, userID could run Sql*Plus to log onto the TNS_STRING database without problem.
The trace on sqlnet.ora did not help much. Finally, an Oracle document (736509.1) suggests that the nscd (name service cache daemon) is reqired to run for Sql*Plus to work. After Systems Administrator started the nscd daemon, both Discoverer services and Sql*Plus worked well on the server.
NSCD should be started automatically during server reboot and keeps running all the time. Now, the question becomes what did stop the nscd running?
To address the issue, I stopped the Discoverer services on the server, and then failed to start them. To make sure there is no problem on the Discoverer configuration, I tested Sql*Plus on the server and found it hung by just staying on the logging in:
SQL> connect userID/passwd@TNS_STRING
The strange thing was that if I logged onto the same Linux (SuSe 10) server as a different OS user using LDAP Authentication, userID could run Sql*Plus to log onto the TNS_STRING database without problem.
The trace on sqlnet.ora did not help much. Finally, an Oracle document (736509.1) suggests that the nscd (name service cache daemon) is reqired to run for Sql*Plus to work. After Systems Administrator started the nscd daemon, both Discoverer services and Sql*Plus worked well on the server.
NSCD should be started automatically during server reboot and keeps running all the time. Now, the question becomes what did stop the nscd running?
Wednesday, September 1, 2010
Rollback of Large Transaction by SMON
After killing a large running transaction or shadow process, or aborting the database, database seems to hang or smon and parallel query servers take all the available CPU. You may also see entry in trace log file (in 11gR2):
Parallel Transaction recovery coordinatorcaught exception 10388
During the Apps upgrade from EBS 11i to EBS R12 after the database was upgraded from 10g to 11gR2, Apps DBA stopped the Apps Upgrade patching and re-started it for a couple of times on Apps server. Then, we experenced the poor performance on the database for Apps patching jobs. We did not have ideas on what was the cause until we realized that rollback recovering was running in the database. There are two views to identify the undo recovering:
v$fast_start_transactions: contains one row for each one of the transactions that Oracle is recovering in Parallel.
v$fast_start_servers: provides information about all the recovery slaves performing parallel transaction recovery. This contains one row for each parallel query slave.
Note 414242.1 provides a query to estimate the finish time if recovering is going. Here is the result from my database
SQL> set linesize 100
SQL> alter session set NLS_DATE_FORMAT='DD-MON-YYYY HH24:MI:SS';
Session altered.
SQL> select usn, state, undoblockstotal "Total", undoblocksdone "Done", undoblockstotal-undoblocksdone "ToDo",
decode(cputime,0,'unknown',sysdate+(((undoblockstotal-undoblocksdone) / (undoblocksdone / cputime)) / 86400)) "Estimated time to complete"
from v$fast_start_transactions;
USN STATE Total Done ToDo Estimated time to complete
--- ------------------ ----------- ------- ------ ---------------------------------
41 RECOVERING 254103 303 253800 03-SEP-2010 03:33:56
SQL>
After the recovering completed, the query result becomes
USN STATE Total Done ToDo Estimated time to complete
--- ------------------ ----------- ------- ------ ---------------------------------
41 RECOVERED 112 112 0 03-SEP-2010 09:22:03
And after the recovering completed, there is no row in view v$fast_start_servers and no row returning from "select ktuxesiz from x$ktuxe where ktuxesta='ACTIVE' and ktuxecfl='DEAD';"
If you want to know waht is recovering, below query will help to identify the parallel child sessions:
select
decode(px.qcinst_id,NULL,username,
' - '||lower(substr(s.program,length(s.program)-4,4) ) ) "Username",
decode(px.qcinst_id,NULL, 'QC', '(Slave)') "QC/Slave" ,
to_char( px.server_set) "Slave Set",
to_char(s.sid) "SID",
decode(px.qcinst_id, NULL ,to_char(s.sid) ,px.qcsid) "QC SID",
px.req_degree "Requested DOP",
px.degree "Actual DOP"
from gv$px_session px, gv$session s
where px.sid=s.sid (+) and px.serial#=s.serial#
order by 5 , 1 desc ;
Then, using each SID to query gv$active_session_history (or gv$session_longops) to get more details. Its CURRENT_OBJ# column gives the object_id for the object which is being recovered.
"QC SID" from above query is the session ID for SMON. Dring transaction recovering, the EVENT column in v$session have value "Wait for stopper event to be increased" for the SMON session.
Note 238507.1 gives a way to disable Parallel Transaction Recovery.
If you can shutdown database, bounce the database after defining a parameter inside init.ora file (144332.1):
fast_start_parallel_rollback = false
FAST_START_PARALLEL_ROLLBACK defines the maximum number of processes which may exist for performing parallel rollback. If the value is FALSE, parallel rollback is disabled. If the value is LOW (default), 2 * CPU_COUNT number of processes may be used. If the value is HIGH, at most 4 * CPU_COUNT number of rollback servers are used for parallel rollback.
Parallel Transaction recovery coordinatorcaught exception 10388
During the Apps upgrade from EBS 11i to EBS R12 after the database was upgraded from 10g to 11gR2, Apps DBA stopped the Apps Upgrade patching and re-started it for a couple of times on Apps server. Then, we experenced the poor performance on the database for Apps patching jobs. We did not have ideas on what was the cause until we realized that rollback recovering was running in the database. There are two views to identify the undo recovering:
v$fast_start_transactions: contains one row for each one of the transactions that Oracle is recovering in Parallel.
v$fast_start_servers: provides information about all the recovery slaves performing parallel transaction recovery. This contains one row for each parallel query slave.
Note 414242.1 provides a query to estimate the finish time if recovering is going. Here is the result from my database
SQL> set linesize 100
SQL> alter session set NLS_DATE_FORMAT='DD-MON-YYYY HH24:MI:SS';
Session altered.
SQL> select usn, state, undoblockstotal "Total", undoblocksdone "Done", undoblockstotal-undoblocksdone "ToDo",
decode(cputime,0,'unknown',sysdate+(((undoblockstotal-undoblocksdone) / (undoblocksdone / cputime)) / 86400)) "Estimated time to complete"
from v$fast_start_transactions;
USN STATE Total Done ToDo Estimated time to complete
--- ------------------ ----------- ------- ------ ---------------------------------
41 RECOVERING 254103 303 253800 03-SEP-2010 03:33:56
SQL>
After the recovering completed, the query result becomes
USN STATE Total Done ToDo Estimated time to complete
--- ------------------ ----------- ------- ------ ---------------------------------
41 RECOVERED 112 112 0 03-SEP-2010 09:22:03
And after the recovering completed, there is no row in view v$fast_start_servers and no row returning from "select ktuxesiz from x$ktuxe where ktuxesta='ACTIVE' and ktuxecfl='DEAD';"
If you want to know waht is recovering, below query will help to identify the parallel child sessions:
select
decode(px.qcinst_id,NULL,username,
' - '||lower(substr(s.program,length(s.program)-4,4) ) ) "Username",
decode(px.qcinst_id,NULL, 'QC', '(Slave)') "QC/Slave" ,
to_char( px.server_set) "Slave Set",
to_char(s.sid) "SID",
decode(px.qcinst_id, NULL ,to_char(s.sid) ,px.qcsid) "QC SID",
px.req_degree "Requested DOP",
px.degree "Actual DOP"
from gv$px_session px, gv$session s
where px.sid=s.sid (+) and px.serial#=s.serial#
order by 5 , 1 desc ;
Then, using each SID to query gv$active_session_history (or gv$session_longops) to get more details. Its CURRENT_OBJ# column gives the object_id for the object which is being recovered.
"QC SID" from above query is the session ID for SMON. Dring transaction recovering, the EVENT column in v$session have value "Wait for stopper event to be increased" for the SMON session.
Note 238507.1 gives a way to disable Parallel Transaction Recovery.
If you can shutdown database, bounce the database after defining a parameter inside init.ora file (144332.1):
fast_start_parallel_rollback = false
FAST_START_PARALLEL_ROLLBACK defines the maximum number of processes which may exist for performing parallel rollback. If the value is FALSE, parallel rollback is disabled. If the value is LOW (default), 2 * CPU_COUNT number of processes may be used. If the value is HIGH, at most 4 * CPU_COUNT number of rollback servers are used for parallel rollback.
Monday, May 24, 2010
Datafile Needs Recovery
When there is a disk read/write problem, such as adapter issues connecting SAN luns to server or other server IO problems, Oracle data files may fail to get checkpoint. In this case, Oracle may place the datafile offline and write errors to the alert log:
Errors in file /u01/app/oracle/admin/dw/bdump/dw_ckpt_1081360.trc:
ORA-01171: datafile 7 going offline due to error advancing checkpoint
ORA-01110: data file 7 : '/u06/app/oracle/oradata/dw/dw_idx01.dbf'
ORA-01115: IO error reading block from file 7 (block # 1)
ORA-27063: number of bytes read/written is incorrect
IBM AIX RISC System/6000 Error: 5: I/O error
Additional information: -1
Additional information: 16384
Database view DBA_DATA_FILES will confirm that the datafile needs recovery:
SQL> select * from dba_data_files
where online_status != 'ONLINE';
FILE_NAME ONLINE_STATUS
/u06/app/oracle/oradata/dw/dw_idx01.dbf RECOVER
/u02/app/oracle/oradata/dw/system01.dbf SYSTEM
The fix to the problem is to run two "alter database ..." commands to bring the data file back online, after the disk IO issue has been firmly resolved. Here is the alert log when the two commands ran:
Fri May 7 23:25:30 2010
alter database recover datafile '/u06/app/oracle/oradata/dw/dw_idx01.dbf'
Fri May 7 23:25:30 2010
Media Recovery Start
parallel recovery started with 7 processes
Fri May 7 23:25:31 2010
Recovery of Online Redo Log: Thread 1 Group 2 Seq 70541 Reading mem 0
Mem# 0: /u03/app/oracle/oradata/dw/redo02a.log
Mem# 1: /u03/app/oracle/oradata/dw/redo02b.log
Fri May 7 23:25:34 2010
Recovery of Online Redo Log: Thread 1 Group 3 Seq 70542 Reading mem 0
Mem# 0: /u04/app/oracle/oradata/dw/redo03a.log
Mem# 1: /u04/app/oracle/oradata/dw/redo03b.log
Fri May 7 23:25:36 2010
Media Recovery Complete (dw)
Completed: alter database recover datafile '/u06/app/oracle/oradata/dw/dw_idx01.dbf'
Fri May 7 23:26:07 2010
alter database datafile '/u06/app/oracle/oradata/dw/dw_idx01.dbf' online
Fri May 7 23:26:07 2010
Starting control autobackup
Control autobackup written to DISK device
handle '/u01/app/oracle/product/10.2.0/db_1/dbs/c-1219972149-20100507-02'
Completed: alter database datafile '/u06/app/oracle/oradata/dw/dw_idx01.dbf' online
Errors in file /u01/app/oracle/admin/dw/bdump/dw_ckpt_1081360.trc:
ORA-01171: datafile 7 going offline due to error advancing checkpoint
ORA-01110: data file 7 : '/u06/app/oracle/oradata/dw/dw_idx01.dbf'
ORA-01115: IO error reading block from file 7 (block # 1)
ORA-27063: number of bytes read/written is incorrect
IBM AIX RISC System/6000 Error: 5: I/O error
Additional information: -1
Additional information: 16384
Database view DBA_DATA_FILES will confirm that the datafile needs recovery:
SQL> select * from dba_data_files
where online_status != 'ONLINE';
FILE_NAME ONLINE_STATUS
/u06/app/oracle/oradata/dw/dw_idx01.dbf RECOVER
/u02/app/oracle/oradata/dw/system01.dbf SYSTEM
The fix to the problem is to run two "alter database ..." commands to bring the data file back online, after the disk IO issue has been firmly resolved. Here is the alert log when the two commands ran:
Fri May 7 23:25:30 2010
alter database recover datafile '/u06/app/oracle/oradata/dw/dw_idx01.dbf'
Fri May 7 23:25:30 2010
Media Recovery Start
parallel recovery started with 7 processes
Fri May 7 23:25:31 2010
Recovery of Online Redo Log: Thread 1 Group 2 Seq 70541 Reading mem 0
Mem# 0: /u03/app/oracle/oradata/dw/redo02a.log
Mem# 1: /u03/app/oracle/oradata/dw/redo02b.log
Fri May 7 23:25:34 2010
Recovery of Online Redo Log: Thread 1 Group 3 Seq 70542 Reading mem 0
Mem# 0: /u04/app/oracle/oradata/dw/redo03a.log
Mem# 1: /u04/app/oracle/oradata/dw/redo03b.log
Fri May 7 23:25:36 2010
Media Recovery Complete (dw)
Completed: alter database recover datafile '/u06/app/oracle/oradata/dw/dw_idx01.dbf'
Fri May 7 23:26:07 2010
alter database datafile '/u06/app/oracle/oradata/dw/dw_idx01.dbf' online
Fri May 7 23:26:07 2010
Starting control autobackup
Control autobackup written to DISK device
handle '/u01/app/oracle/product/10.2.0/db_1/dbs/c-1219972149-20100507-02'
Completed: alter database datafile '/u06/app/oracle/oradata/dw/dw_idx01.dbf' online
Thursday, May 13, 2010
Install Oracle Fusion Middleware 11gR1
Oracle Fusion Middleware expands Oracle Applications Server 10g to include more components that you may never need.
I studies the way of installing Oracle Dsicoverer 11.1.1 for Oracle E-business Realease 12 (1074326.1). Here are some key steps to make the installation work.
1. Indentify the installation files from download.
As of today 5/14/10, the location is
http://www.oracle.com/technology/software/products/middleware/htdocs/fmw_11_download.html
This page also has a link to downlaod WebLogic
http://www.oracle.com/technology/software/products/ias/htdocs/wls_main.html
File names in three areas:
- Repository Creation Utility (RCU, 11.1.1.2.1)
ofm_rcu_linux_11.1.1.2.1_disk1_1of1.zip
- Weblogic server (10.3.2)
wls1032_generic.jar
- Portal, Forms, Reports and Discoverer (11.1.1.2.0)
ofm_pfrd_linux_11.1.1.2.0_64_disk1_1of1.zip
ofm_pfrd_linux_11.1.1.2.0_64_disk2_1of1.zip
ofm_pfrd_linux_11.1.1.2.0_64_disk3_1of1.zip
ofm_pfrd_linux_11.1.1.2.0_64_disk4_1of1.zip
2. Need a database for repository
The supported database versions by Oracle Fusion Middleware 11gR1 are
Oracle 10.2.0.4+ (if it's a 10g)
Oracle 11.1.0.7+ (if it's an 11gR1)
Oracle 11.2.0.1+ (if it's an 11gR2)
3. Create repository schemas
Run RCU to install schemas for Oracle Portal and Oracle Discoverer. RCU only works on Linux platform. If the database is on other platforms, run RCU remotely connecting to the database.
4. Install WebLogic server
Run the .jar file to create new Middleware Home directory. Java must be available (by the PATH env variable) for the installtion, and its version must be high enough. Here is the way to check Java version:
$ ./java -version
java version "1.6.0_20"
Java(TM) SE Runtime Environment (build 1.6.0_20-b02)
Java HotSpot(TM) 64-Bit Server VM (build 16.3-b01, mixed mode)
5. Install Oracle Portal, Forms, Reports and Discoverer
Following guid has screenshots on installation:
Installation Guide for Oracle Portal, Forms, Reports and Discoverer
11g Release 1 (11.1.1) E10421-02
I studies the way of installing Oracle Dsicoverer 11.1.1 for Oracle E-business Realease 12 (1074326.1). Here are some key steps to make the installation work.
1. Indentify the installation files from download.
As of today 5/14/10, the location is
http://www.oracle.com/technology/software/products/middleware/htdocs/fmw_11_download.html
This page also has a link to downlaod WebLogic
http://www.oracle.com/technology/software/products/ias/htdocs/wls_main.html
File names in three areas:
- Repository Creation Utility (RCU, 11.1.1.2.1)
ofm_rcu_linux_11.1.1.2.1_disk1_1of1.zip
- Weblogic server (10.3.2)
wls1032_generic.jar
- Portal, Forms, Reports and Discoverer (11.1.1.2.0)
ofm_pfrd_linux_11.1.1.2.0_64_disk1_1of1.zip
ofm_pfrd_linux_11.1.1.2.0_64_disk2_1of1.zip
ofm_pfrd_linux_11.1.1.2.0_64_disk3_1of1.zip
ofm_pfrd_linux_11.1.1.2.0_64_disk4_1of1.zip
2. Need a database for repository
The supported database versions by Oracle Fusion Middleware 11gR1 are
Oracle 10.2.0.4+ (if it's a 10g)
Oracle 11.1.0.7+ (if it's an 11gR1)
Oracle 11.2.0.1+ (if it's an 11gR2)
3. Create repository schemas
Run RCU to install schemas for Oracle Portal and Oracle Discoverer. RCU only works on Linux platform. If the database is on other platforms, run RCU remotely connecting to the database.
4. Install WebLogic server
Run the .jar file to create new Middleware Home directory. Java must be available (by the PATH env variable) for the installtion, and its version must be high enough. Here is the way to check Java version:
$ ./java -version
java version "1.6.0_20"
Java(TM) SE Runtime Environment (build 1.6.0_20-b02)
Java HotSpot(TM) 64-Bit Server VM (build 16.3-b01, mixed mode)
5. Install Oracle Portal, Forms, Reports and Discoverer
Following guid has screenshots on installation:
Installation Guide for Oracle Portal, Forms, Reports and Discoverer
11g Release 1 (11.1.1) E10421-02
Wednesday, April 21, 2010
init parameter PROCESSES
Initialization parameter PROCESSES defines the max number of user connections that can access the database at a same time. The default value for the parameter is 300.
If the max number exceeds the parameter value, new connection will not be able to connect to the database. Grid Control will report the connection problem and the database will give error in trace file:
ORA-00020: maximum number of processes (300) exceeded
In that case, the issues can be confirmed by number of OS sessions and by database view v$process:
$ echo $ORACLE_SID
$ ps -ef | grep $ORACLE_SID | wc -l
299
SQL> select count(*) from v$process;
COUNT(*)
----------
297
To increase the value, run an alter statement. Then a database bounce is necessary.
SQL> alter system set processes=500 scope=pfile;
I read that below three parameters are related by a formula (in 10G, but might not be always). If database reports ORA-00018 error, you may also consider modifying parameter PROCESSES (or find why the number of sessions jumped).
If the max number exceeds the parameter value, new connection will not be able to connect to the database. Grid Control will report the connection problem and the database will give error in trace file:
ORA-00020: maximum number of processes (300) exceeded
In that case, the issues can be confirmed by number of OS sessions and by database view v$process:
$ echo $ORACLE_SID
$ ps -ef | grep $ORACLE_SID | wc -l
299
SQL> select count(*) from v$process;
COUNT(*)
----------
297
To increase the value, run an alter statement. Then a database bounce is necessary.
SQL> alter system set processes=500 scope=pfile;
I read that below three parameters are related by a formula (in 10G, but might not be always). If database reports ORA-00018 error, you may also consider modifying parameter PROCESSES (or find why the number of sessions jumped).
ORA-00018: maximum number of sessions exceeded processes=x
sessions=x*1.1+5
transactions=sessions*1.1 Thursday, April 8, 2010
Oracle Client Patchset
When Oracle releases a database (RDBMS) version, it releases a corresponding client. For example, for 11.2.0.1.0 database server, aix.ppc64_11gR2_client.zip and aix.ppc32_11gR2_client.zip are client files. If you want to upgrade your client (i.e. from 11.2.0.1 to 11.2.0.3), you have to use the server patchset. Document 438049.1 says "For database and client upgrade use the same patch set. There is no separate patch set for client".
Document 207303.1 provides a matrix summarizing client and server combinations that are supported Oracle.
To install Oracle client utilities, choose "Custom" during the installation. Document 437377.1 shows how to install SQL*Loader on the 10G client:
The steps to install SQL*Loader in client in Custom Installation :
1) Start the Oracle Universal Installer (OUI) from the client source (CD)
2) Choose 'Installation Type'
==> Select the "Custom" and then continue with 'Next'
3) Choose the ORACLE_HOME, continue with 'Next'
4) In the 'Available Product Components' Screen
==> Select the "Oracle Database Utilities" and then continue with 'Next'
Note: Only components with a check mark are installed.
5) At the "Summary" screen Click "Install"
Finish the installation. This will Install SQL Loader.
You will be able to find the "sqlldr.exe" (or "sqlldr") in the directory %ORACLE_HOME%\bin in Windows and $ORACLE_HOME\bin in UNIX
Document 207303.1 provides a matrix summarizing client and server combinations that are supported Oracle.
To install Oracle client utilities, choose "Custom" during the installation. Document 437377.1 shows how to install SQL*Loader on the 10G client:
The steps to install SQL*Loader in client in Custom Installation :
1) Start the Oracle Universal Installer (OUI) from the client source (CD)
2) Choose 'Installation Type'
==> Select the "Custom" and then continue with 'Next'
3) Choose the ORACLE_HOME, continue with 'Next'
4) In the 'Available Product Components' Screen
==> Select the "Oracle Database Utilities" and then continue with 'Next'
Note: Only components with a check mark are installed.
5) At the "Summary" screen Click "Install"
Finish the installation. This will Install SQL Loader.
You will be able to find the "sqlldr.exe" (or "sqlldr") in the directory %ORACLE_HOME%\bin in Windows and $ORACLE_HOME\bin in UNIX
Tuesday, January 26, 2010
Get AWR, ADDR, and ASH reports from SQL scripts
One way to identify the causes of poor performance in the database is to review AWR, ADDR, and ASH reports. They should be for a period of 60 or more minutes, covering the poor performance period.
. To generate the AWR report, execute the following script and select two snapshots:
SQL> @$ORACLE_HOME/rdbms/admin/awrrpt.sql
>> Choose the TXT format or the HTML format.
. To generate the ADDM report, execute:
SQL> @$ORACLE_HOME/rdbms/admin/addmrpt
>> Choose the TXT format or the HTML format.
. To generate the ASH report, execute:
SQL> @$ORACLE_HOME/rdbms/admin/ashrpt
>> Choose the TXT format or the HTML format.
. To generate the AWR report, execute the following script and select two snapshots:
SQL> @$ORACLE_HOME/rdbms/admin/awrrpt.sql
>> Choose the TXT format or the HTML format.
. To generate the ADDM report, execute:
SQL> @$ORACLE_HOME/rdbms/admin/addmrpt
>> Choose the TXT format or the HTML format.
. To generate the ASH report, execute:
SQL> @$ORACLE_HOME/rdbms/admin/ashrpt
>> Choose the TXT format or the HTML format.
Friday, January 8, 2010
REDO log sizing
Online redo log sizing is very important. If it is too small, the LGWR, ARCH and DBWR background processes will be busy by the frequent log switches and increase CPU usage. It it is too big, the database risks losing data during an instance crash.
In Oracle 10G, Oracle advisory utility makes recommendation on optimal redo log size. To use this feature, an initialization parameter FAST_START_MTTR_TARGET parameter must be set to a nonzero value, which specifies a target (bounded) time (in seconds) to complete the cache recovery phase of recovering. Using this parameter, Oracle database can now self-tune checkpointing to achieve good recovery times with low impact on normal throughput. DBAs no longer have to set any checkpoint-related parameters and should disable (set to 0) the following parameters:
- LOG_CHECKPOINT_TIMEOUT
- LOG_CHECKPOINT_INTERVAL
- FAST_START_IO_TARGET
View v$instance_recovery holds the MTTR (mean time to recovery ) information with following columns:
optimal_logfile_size -- the suggested size in megabytes
target_mttr -- MTTR target in effect in seconds
estimated_mttr -- the estimated MTTR if a crash happens right now
The data reported in this view may change frequently, based on the DML load on the database. DBAs can use the suggested size when the database is relatively stable to build the redo log properly.
Grid Control also gives the suggested size. The navigation is
Adminstration --> Storage --> Redo Log Group --> Actions dropdown: Sizing advice --> Go.
Oracle Database Backup and Recovery Advanced User's Guide (10gR2, B14191-01) has details on Oracle recovery process and on FAST_START_MTTR_TARGET sizing.
Note 276103.1 has more on Advisors.
In Oracle 10G, Oracle advisory utility makes recommendation on optimal redo log size. To use this feature, an initialization parameter FAST_START_MTTR_TARGET parameter must be set to a nonzero value, which specifies a target (bounded) time (in seconds) to complete the cache recovery phase of recovering. Using this parameter, Oracle database can now self-tune checkpointing to achieve good recovery times with low impact on normal throughput. DBAs no longer have to set any checkpoint-related parameters and should disable (set to 0) the following parameters:
- LOG_CHECKPOINT_TIMEOUT
- LOG_CHECKPOINT_INTERVAL
- FAST_START_IO_TARGET
View v$instance_recovery holds the MTTR (mean time to recovery ) information with following columns:
optimal_logfile_size -- the suggested size in megabytes
target_mttr -- MTTR target in effect in seconds
estimated_mttr -- the estimated MTTR if a crash happens right now
The data reported in this view may change frequently, based on the DML load on the database. DBAs can use the suggested size when the database is relatively stable to build the redo log properly.
Grid Control also gives the suggested size. The navigation is
Adminstration --> Storage --> Redo Log Group --> Actions dropdown: Sizing advice --> Go.
Oracle Database Backup and Recovery Advanced User's Guide (10gR2, B14191-01) has details on Oracle recovery process and on FAST_START_MTTR_TARGET sizing.
Note 276103.1 has more on Advisors.
Subscribe to:
Posts (Atom)