I need to push myself to blog again. When I look back, I feel disappointed that I did not blog all these years. It is time to pull up the sleeves and start hitting the keyboard.
How much time a rollback will take?
Metalink NoteID: 169138.1
select
sum(distinct(ktuxesiz)) from x$ktuxe;
Workflow load distribution - Item wise
select wi.item_type ITEM_TYPE,
wit.persistence_type P_TYPE,
decode (wi.end_date, NULL, 'OPEN', 'CLOSED') Status,
count(*) COUNT
from wf_items wi,
wf_item_types wit
where wit.name = wi.item_type
group by item_type,
wit.persistence_type,
WIT.PERSISTENCE_DAYS,
decode (wi.end_date, NULL, 'OPEN', 'CLOSED')
order by decode (wi.end_date, NULL, 'OPEN', 'CLOSED'), 4 desc
Trick to make query run fast
There may be some actions that need full table scans and takes more time to execute.
Following are certatin such actions:
- Index creation
- Select count(*) from a table ; to get the number of rows
- Gather table statistics
To speed up the above queries we need to set the following session parameters and then execute the required action
alter session set sort_area_size=536870912;
alter session set sort_multiblock_read_count=128;
alter session set db_file_multiblock_read_count=128;
TOP I/O Consuming sessions
Query to find the top I/O consuming sessions:
select
sid,
username,
round(100 * total_user_io/total_io,2) tot_io_pct
from
(select
b.sid sid,
nvl(b.username,p.name) username,
sum(value) total_user_io
from
sys.v_$statname c,
sys.v_$sesstat a,
sys.v_$session b,
sys.v_$bgprocess p
where
a.statistic#=c.statistic# and
p.paddr (+) = b.paddr and
b.sid=a.sid and
c.name in ('physical reads',
'physical writes',
'physical writes direct',
'physical reads direct',
'physical writes direct (lob)',
'physical reads direct (lob)')
group by
b.sid, nvl(b.username,p.name)),
(select
sum(value) total_io
from
sys.v_$statname c,
sys.v_$sesstat a
where
a.statistic#=c.statistic# and
c.name in ('physical reads',
'physical writes',
'physical writes direct',
'physical reads direct',
'physical writes direct (lob)',
'physical reads direct (lob)'))
order by
3 desc;
Courtesy : Malli
select
sid,
username,
round(100 * total_user_io/total_io,2) tot_io_pct
from
(select
b.sid sid,
nvl(b.username,p.name) username,
sum(value) total_user_io
from
sys.v_$statname c,
sys.v_$sesstat a,
sys.v_$session b,
sys.v_$bgprocess p
where
a.statistic#=c.statistic# and
p.paddr (+) = b.paddr and
b.sid=a.sid and
c.name in ('physical reads',
'physical writes',
'physical writes direct',
'physical reads direct',
'physical writes direct (lob)',
'physical reads direct (lob)')
group by
b.sid, nvl(b.username,p.name)),
(select
sum(value) total_io
from
sys.v_$statname c,
sys.v_$sesstat a
where
a.statistic#=c.statistic# and
c.name in ('physical reads',
'physical writes',
'physical writes direct',
'physical reads direct',
'physical writes direct (lob)',
'physical reads direct (lob)'))
order by
3 desc;
Courtesy : Malli
Export and Import Table stats between databases
STEPS
Step1: Create a table to store the stats.
Step 2: Export the stats from the required table to the table created in step1.
Step3: Export the table using either exp or expdp from database 1
Step4: Import the table that contain stats into database2
Step5: Use dbms_stats to import the stats into database2
COMMANDS
exec dbms_stats.create_stat_table('APPS','OKC_K_ITEMS_TABLE_STATS')
exec dbms_stats.export_table_stats('OKC','OKC_K_ITEMS',null,'OKC_K_ITEMS_TABLE_STATS',null,true,'APPS')
exp apps file=exp_crp_stats.dmp tables=OKC_K_ITEMS_TABLE_STATS
imp apps file=exp_crp_stats.dmp full=y
exec dbms_stats.import_table_stats(OKC,'OKC_K_ITEMS',null,OKC_K_ITEMS_TABLE_STATS',null,true,'APPS')
Step1: Create a table to store the stats.
Step 2: Export the stats from the required table to the table created in step1.
Step3: Export the table using either exp or expdp from database 1
Step4: Import the table that contain stats into database2
Step5: Use dbms_stats to import the stats into database2
COMMANDS
exec dbms_stats.create_stat_table('APPS','OKC_K_ITEMS_TABLE_STATS')
exec dbms_stats.export_table_stats('OKC','OKC_K_ITEMS',null,'OKC_K_ITEMS_TABLE_STATS',null,true,'APPS')
exp apps file=exp_crp_stats.dmp tables=OKC_K_ITEMS_TABLE_STATS
imp apps file=exp_crp_stats.dmp full=y
exec dbms_stats.import_table_stats(OKC,'OKC_K_ITEMS',null,OKC_K_ITEMS_TABLE_STATS',null,true,'APPS')
HTTP-503 ORA-12538
We got "HTTP-503 ORA-12538" error in error_log_pls located in "$APACHE_TOP/Apache/log" folder.
This instance is a clone instance and after completing the adcfgclone we tried to login to the instance with the sysadmin user credentials.
The login page is appearing, after entering the credentials and clicking on login button, we got the following error,
Service Temporarily UnavailableThe server is temporarily unable to service your request due to maintenance downtime or capacity problems. Please try again later.
Searched Aapche access_log and error_log.
error_log has " request failed: URI too long" error message
error_log_pls has "HTTP-503 ORA-12538"
ORA-12538
TNS: no such protocol adapter
Metalink NoteID to be followed : 152580.1
And executed the following commands on the applications tier after sourcing the environment file of Application Tier.
export ORACLE_HOME=$IAS_TOP
cd $ORACLE_HOME/bin
./gennttab
cd $ORACLE_HOME/network/lib
make -f ins_net_client.mk ntcontab.o
cd $ORACLE_HOME/bin
./genclntsh
cd $ORACLE_HOME/network/lib
make -f ins_net_client.mk install
make -f ins_net_server.mk install
cd $ORACLE_HOME/sqlplus/lib
make -f ins_sqlplus.mk install
This instance is a clone instance and after completing the adcfgclone we tried to login to the instance with the sysadmin user credentials.
The login page is appearing, after entering the credentials and clicking on login button, we got the following error,
Service Temporarily UnavailableThe server is temporarily unable to service your request due to maintenance downtime or capacity problems. Please try again later.
Searched Aapche access_log and error_log.
error_log has " request failed: URI too long" error message
error_log_pls has "HTTP-503 ORA-12538"
ORA-12538
TNS: no such protocol adapter
Metalink NoteID to be followed : 152580.1
And executed the following commands on the applications tier after sourcing the environment file of Application Tier.
export ORACLE_HOME=$IAS_TOP
cd $ORACLE_HOME/bin
./gennttab
cd $ORACLE_HOME/network/lib
make -f ins_net_client.mk ntcontab.o
cd $ORACLE_HOME/bin
./genclntsh
cd $ORACLE_HOME/network/lib
make -f ins_net_client.mk install
make -f ins_net_server.mk install
cd $ORACLE_HOME/sqlplus/lib
make -f ins_sqlplus.mk install
Before executing the above commands, execute "slibclean" to cleanup the library files held by the AIX kernel. "Slibclean" needs to be run from the "root" account.
Verification
1.
$ adapters ./sqlplus
Net8 Protocol Adapters linked with ./sqlplus are:
BEQ Protocol Adapter
IPC Protocol Adapter
TCP/IP Protocol Adapter
RAW Protocol Adapter
2.
Also verify the size of libclntsh.o located under $IAS_TOP/lib. It should have latest time stamp as well as the size should match with other instances.
3.
Verify the output of the commands executed before. Incase any command fails with TIMEOUT error then re-execute it.
FTP access on HP-UNIX PA RISC
I was facing a typical problem where in the root user can ftp to the unix box, where as other users it denies with the message invalid login. Though we are giving right combination of userid/password. Telnet is working but ftp is not working. Finally resolved it in the following way,
1. /etc/ftpd/ftpusers : this file will have userids that are denied ftp access to the servers.
Ok. The above information is fine. But in our case the above file doesnt exist. So we are not denying any users. So the above info is just info and not related to our problem.
2. ftp calls getusershell which by default checks password information (that is, the entry in /etc/passwd for the user who is trying to log in) against a fixed list. If the shell isn’t on the list, ftp won’t let the user in, so if you use an unusual shell you may not be able to ftp even to your own system
Ok... so what..... well thats not all it does...
getusershell can be made aware of other shells via /etc/shells. Thats it... i checked for /etc/shells and this file doesnt even exist at all. Checked /etc/passwd for the shell we use and found that its /bin/ksh. So created the file /etc/shells with an entry "/bin/ksh" and viola.... ftp works.....
So theres a lot behind the ftp process...........
Credits : http://www.techsolutions.hp.com/en/B2355-90950/ch04s04.html
1. /etc/ftpd/ftpusers : this file will have userids that are denied ftp access to the servers.
Ok. The above information is fine. But in our case the above file doesnt exist. So we are not denying any users. So the above info is just info and not related to our problem.
2. ftp calls getusershell which by default checks password information (that is, the entry in /etc/passwd for the user who is trying to log in) against a fixed list. If the shell isn’t on the list, ftp won’t let the user in, so if you use an unusual shell you may not be able to ftp even to your own system
Ok... so what..... well thats not all it does...
getusershell can be made aware of other shells via /etc/shells. Thats it... i checked for /etc/shells and this file doesnt even exist at all. Checked /etc/passwd for the shell we use and found that its /bin/ksh. So created the file /etc/shells with an entry "/bin/ksh" and viola.... ftp works.....
So theres a lot behind the ftp process...........
Credits : http://www.techsolutions.hp.com/en/B2355-90950/ch04s04.html
Hurray ..........and ............
Ya finally the results for the beta tests which I have written have come.
I have passed both the tests that I wrote way back in January. The tests are:
Exam 1Z1-232: Oracle 11i System Administration
Exam 1Z1-233: Oracle 11i Install, Patch & Maintain Oracle Applications
But, Oracle has changed the criteria for the Oracle Applications DBA certification track.
May be they would have got good feedback from the Applications DBA community. And rightly they have removed "Oracle 11i System Administration" and "Oracle Workflow Administration" from the track and added Oracle 9i or 10g DBA Certification test as part of the certification pack.
Hence, of the two tests I have written one is not really not useful and one will be useful.
I am already a Oracle 8i certified DBA and by upgrading to 10g can get this certification as well.
I have passed both the tests that I wrote way back in January. The tests are:
Exam 1Z1-232: Oracle 11i System Administration
Exam 1Z1-233: Oracle 11i Install, Patch & Maintain Oracle Applications
But, Oracle has changed the criteria for the Oracle Applications DBA certification track.
May be they would have got good feedback from the Applications DBA community. And rightly they have removed "Oracle 11i System Administration" and "Oracle Workflow Administration" from the track and added Oracle 9i or 10g DBA Certification test as part of the certification pack.
Hence, of the two tests I have written one is not really not useful and one will be useful.
I am already a Oracle 8i certified DBA and by upgrading to 10g can get this certification as well.
Oracle 11i Certification
I have attempted the Beta certification tests of Oracle 11i Applications Technology track.
There are three tests in this track and classroom training is waived of if the tests are taken before May 2007.
The three tests that needs to be passed to obtain the certification are :
1Z0-231 - Implement Oracle Workflow 11i
1Z1-232 - Oracle 11i System Administration
1Z1-233 - 11i Install Patch and Maintain Oracle Applications
The last two tests were in beta phase till 31st January 2007. So I have appeared for those tests on 25th of January and 31st of January. Managed to prepare and appear for these tests before the beta expires.
1Z0-231 : Implement Oracle Workflow 11i is not in beta test and is not available as of today. So I need to wait for this test to become officially available to write it. Also till now I didnt get oppurtunity to work on workflow and implement it. So I will be utilizing the time to prepare for the test and do some hands on. Luckily my current project has workflow implementation with AME (Approval Management Engine). So this test will be useful for me as well as my current implementation experience for the test.
1Z1-232: Oracle 11i System Administration. Wrote this test yesterday on 31st January. Just before the beta version expires. So its still fresh in my mind. There are 103 questions with 180 minutes to complete it. Well as per agreement I cannot reveal much about the questions, but I can say that the questions are good and a person with good hands on can easily pass through. We need to study more on topics like User Management, Forms personalization and Data Security apart from the other topics given in the course contents. As User Management is a new chapter with Oracle moving towards RBAC I felt we need to study more on this new topic and there are quite a few number of questions from this.
1Z1-233 : 11i Install Patch and Maintain Oracle Applications
Wrote this test last week and this is the first Beta test I have appeared from Oracle. So I was not prepared for the plethora of questions that Oracle has bombarded on me. There were 153 questions and 3 hours time to complete all. The test center room was too cold and in between my hands went numb. Well leaving the environment factors and coming to the actual questions felt they were tough. Even though cloning, patching and installing are part of my day to day activities, I do not remember the commands and rely mostly on Metalink.
Now I need to prepare for the workflow test and keep my fingers crossed till the results of the Beta tests are announced. Meanwhile I am planning to appear for Oracle 10g Application server Administration -I test. Though I am not keen on writing it so soon, but i got a 25% discount voucher from my company and it expires in the first week of March. So if I have to save some money, then I better have to pull up my socks and prepare well to appear before the voucher expires.
There are three tests in this track and classroom training is waived of if the tests are taken before May 2007.
The three tests that needs to be passed to obtain the certification are :
1Z0-231 - Implement Oracle Workflow 11i
1Z1-232 - Oracle 11i System Administration
1Z1-233 - 11i Install Patch and Maintain Oracle Applications
The last two tests were in beta phase till 31st January 2007. So I have appeared for those tests on 25th of January and 31st of January. Managed to prepare and appear for these tests before the beta expires.
1Z0-231 : Implement Oracle Workflow 11i is not in beta test and is not available as of today. So I need to wait for this test to become officially available to write it. Also till now I didnt get oppurtunity to work on workflow and implement it. So I will be utilizing the time to prepare for the test and do some hands on. Luckily my current project has workflow implementation with AME (Approval Management Engine). So this test will be useful for me as well as my current implementation experience for the test.
1Z1-232: Oracle 11i System Administration. Wrote this test yesterday on 31st January. Just before the beta version expires. So its still fresh in my mind. There are 103 questions with 180 minutes to complete it. Well as per agreement I cannot reveal much about the questions, but I can say that the questions are good and a person with good hands on can easily pass through. We need to study more on topics like User Management, Forms personalization and Data Security apart from the other topics given in the course contents. As User Management is a new chapter with Oracle moving towards RBAC I felt we need to study more on this new topic and there are quite a few number of questions from this.
1Z1-233 : 11i Install Patch and Maintain Oracle Applications
Wrote this test last week and this is the first Beta test I have appeared from Oracle. So I was not prepared for the plethora of questions that Oracle has bombarded on me. There were 153 questions and 3 hours time to complete all. The test center room was too cold and in between my hands went numb. Well leaving the environment factors and coming to the actual questions felt they were tough. Even though cloning, patching and installing are part of my day to day activities, I do not remember the commands and rely mostly on Metalink.
Now I need to prepare for the workflow test and keep my fingers crossed till the results of the Beta tests are announced. Meanwhile I am planning to appear for Oracle 10g Application server Administration -I test. Though I am not keen on writing it so soon, but i got a 25% discount voucher from my company and it expires in the first week of March. So if I have to save some money, then I better have to pull up my socks and prepare well to appear before the voucher expires.
Subscribe to:
Posts (Atom)