tag:blogger.com,1999:blog-42327759394021258072024-03-13T21:11:54.660-07:00My technical journalCronoshttp://www.blogger.com/profile/15427827507143505689noreply@blogger.comBlogger92125tag:blogger.com,1999:blog-4232775939402125807.post-49335344715746061212013-05-20T14:15:00.000-07:002013-05-20T14:15:30.128-07:00Network Adapter Could not Establish Connection<div dir="ltr" style="text-align: left;" trbidi="on">
This is a funny scenario: you have a java program that connects to an Oracle database using IP adresses, but cannot connect to the database even if you use the same exact connect string in tnsnames and you're able to connect to that database using the SQL/Plus client.<br />
<br />
But let's start from the beginning, when you don't have any issues at all (because the Oracle database server name has a full-qualified domain name and is resolvable):<br />
<pre><span style="font-size: 75%;">
oracle@test:~$ cd $ORACLE_HOME/network/admin
oracle@test:~/app/oracle/product/10.2.0/server/network/admin$ cat tnsnames.ora
# tnsnames.ora Network Configuration File:
XE =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = hera.localdomain)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = XE)
)
)
XE2 =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.108.76)(PORT = 1521))
(CONNECT_DATA =
(SERVER = SHARED)
(SERVICE_NAME = XE)
)
)
XE3 =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.108.76)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = XE)
)
)
</span></pre>
<br />
As you can see, there are three different connect strings defined in this tnsnames file: one with a FQDN as dedicated connection, one with an IP address as shared connection, and the last one with an IP address as dedicated connection. Now you test the name of the server and try to connect using these three services:<br />
<pre><span style="font-size: 75%;">
oracle@test:~$ ping hera
PING hera.localdomain (192.168.108.76) 56(84) bytes of data.
64 bytes from hera.localdomain (192.168.108.76): icmp_req=1 ttl=64 time=8.34 ms
64 bytes from hera.localdomain (192.168.108.76): icmp_req=2 ttl=64 time=3.58 ms
--- hera.localdomain ping statistics ---
2 packets transmitted, 2 received, 0% packet loss, time 1003ms
rtt min/avg/max/mdev = 3.589/5.964/8.340/2.376 ms
oracle@test:~$ sqlplus hr/hr@xe
SQL*Plus: Release 10.2.0.1.0 - Production on Wed May 15 15:44:16 2013
Copyright (c) 1982, 2005, Oracle. All rights reserved.
Connected to:
Oracle Database 10g Express Edition Release 10.2.0.1.0 - Production
SQL> exit
Disconnected from Oracle Database 10g Express Edition Release 10.2.0.1.0 - Production
oracle@test:~$ sqlplus hr/hr@xe2
SQL*Plus: Release 10.2.0.1.0 - Production on Wed May 15 15:44:21 2013
Copyright (c) 1982, 2005, Oracle. All rights reserved.
Connected to:
Oracle Database 10g Express Edition Release 10.2.0.1.0 - Production
SQL> exit
Disconnected from Oracle Database 10g Express Edition Release 10.2.0.1.0 - Production
oracle@test:~$ sqlplus hr/hr@xe3
SQL*Plus: Release 10.2.0.1.0 - Production on Wed May 15 15:44:26 2013
Copyright (c) 1982, 2005, Oracle. All rights reserved.
Connected to:
Oracle Database 10g Express Edition Release 10.2.0.1.0 - Production
SQL> exit
Disconnected from Oracle Database 10g Express Edition Release 10.2.0.1.0 - Production
</span></pre>
<br />
Everything was fine as expected, and even this <a href="http://javarevisited.blogspot.mx/2012/04/java-program-to-connect-oracle-database.html">little handy java test program</a> is working:<br />
<pre><span style="font-size: 75%;">
oracle@test:~$ cat OracleJdbcExample.java |grep jdbc
String url = "jdbc:oracle:thin:@(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.108.76)(PORT=1521))(CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=XE)))";
oracle@test:~$ javac -g OracleJdbcExample.java
oracle@test:~$ export CLASSPATH=$CLASSPATH:/usr/lib/oracle/MyJDBC/ojdbc6.jar
oracle@test:~$ java OracleJdbcExample
Current Date from Oracle : 2013-05-15 16:06:08
done
</span></pre>
<br />
But what happens when the host name is not resolvable anymore? Like when commenting the nameserver lines in /etc/resolv.conf:<br />
<pre><span style="font-size: 75%;">
oracle@test:/etc# ping hera
ping: unknown host hera
oracle@test:~$ sqlplus hr/hr@xe
SQL*Plus: Release 10.2.0.1.0 - Production on Wed May 15 16:02:25 2013
Copyright (c) 1982, 2005, Oracle. All rights reserved.
ERROR:
ORA-12560: TNS:protocol adapter error
Enter user-name:
oracle@test:~$ sqlplus hr/hr@xe2
SQL*Plus: Release 10.2.0.1.0 - Production on Wed May 15 16:02:44 2013
Copyright (c) 1982, 2005, Oracle. All rights reserved.
Connected to:
Oracle Database 10g Express Edition Release 10.2.0.1.0 - Production
SQL> exit
Disconnected from Oracle Database 10g Express Edition Release 10.2.0.1.0 - Production
oracle@test:~$ sqlplus hr/hr@xe3
SQL*Plus: Release 10.2.0.1.0 - Production on Wed May 15 16:02:50 2013
Copyright (c) 1982, 2005, Oracle. All rights reserved.
Connected to:
Oracle Database 10g Express Edition Release 10.2.0.1.0 - Production
SQL> exit
Disconnected from Oracle Database 10g Express Edition Release 10.2.0.1.0 - Production
</span></pre>
<br />
As expected, the first connect string that uses the hostname is not working, but the other two that use IP addresses still work. Therefore, the java program has to work as well, right?<br />
<pre><span style="font-size: 75%;">
oracle@test:~$ java OracleJdbcExample
Exception in thread "main" java.sql.SQLRecoverableException: IO Error: Connection reset
at oracle.jdbc.driver.T4CConnection.logon(T4CConnection.java:421)
at oracle.jdbc.driver.PhysicalConnection.<init>(PhysicalConnection.java:531)
at oracle.jdbc.driver.T4CConnection.<init>(T4CConnection.java:221)
at oracle.jdbc.driver.T4CDriverExtension.getConnection(T4CDriverExtension.java:32)
at oracle.jdbc.driver.OracleDriver.connect(OracleDriver.java:503)
at java.sql.DriverManager.getConnection(DriverManager.java:582)
at java.sql.DriverManager.getConnection(DriverManager.java:154)
at OracleJdbcExample.main(OracleJdbcExample.java:25)
Caused by: java.net.SocketException: Connection reset
at java.net.SocketOutputStream.socketWrite(SocketOutputStream.java:96)
at java.net.SocketOutputStream.write(SocketOutputStream.java:136)
at oracle.net.ns.DataPacket.send(DataPacket.java:199)
at oracle.net.ns.NetOutputStream.flush(NetOutputStream.java:211)
at oracle.net.ns.NetInputStream.getNextPacket(NetInputStream.java:227)
at oracle.net.ns.NetInputStream.read(NetInputStream.java:175)
at oracle.net.ns.NetInputStream.read(NetInputStream.java:100)
at oracle.net.ns.NetInputStream.read(NetInputStream.java:85)
at oracle.jdbc.driver.T4CSocketInputStreamWrapper.readNextPacket(T4CSocketInputStreamWrapper.java:122)
at oracle.jdbc.driver.T4CSocketInputStreamWrapper.read(T4CSocketInputStreamWrapper.java:78)
at oracle.jdbc.driver.T4CMAREngine.unmarshalUB1(T4CMAREngine.java:1179)
at oracle.jdbc.driver.T4CMAREngine.unmarshalSB1(T4CMAREngine.java:1155)
at oracle.jdbc.driver.T4CTTIfun.receive(T4CTTIfun.java:279)
at oracle.jdbc.driver.T4CTTIfun.doRPC(T4CTTIfun.java:186)
at oracle.jdbc.driver.T4CTTIoauthenticate.doOAUTH(T4CTTIoauthenticate.java:366)
at oracle.jdbc.driver.T4CTTIoauthenticate.doOAUTH(T4CTTIoauthenticate.java:752)
at oracle.jdbc.driver.T4CConnection.logon(T4CConnection.java:359)
... 7 more
</init></init></span></pre>
<br />
That's not the case. According to Oracle's Metalink note <a href="https://support.oracle.com/CSP/main/article?cmd=show&type=NOT&id=139775.1">139775.1</a>, when you're trying to connect to an Oracle service via a dedicated connection using java, the connect string has IP addresses instead of full-qualified domain names, and those IP addresses are not resolvable in the client machine, you will get a <b>Network Adapter Could not Establish Connection</b> error message, even if from that client you can reach the database using the SQL/Plus client. In this example the error message is different, I guess because I'm using Oracle 10g XE.<br />
<br />
More information:<br />
<br />
<a href="http://haitaoblog.blogspot.mx/2007/11/io-exception-network-adapter-could-not.html">Io exception: The Network Adapter could not establish the connection</a><br />
<a href="http://dbaforums.org/oracle/index.php?showtopic=990">NL Exception trying to connect to 10g RAC w/JDBC</a></div>
Cronoshttp://www.blogger.com/profile/15427827507143505689noreply@blogger.com0tag:blogger.com,1999:blog-4232775939402125807.post-67275215800237459692013-04-05T10:11:00.001-07:002013-04-05T10:11:18.993-07:00Resuming a failed file copy<div dir="ltr" style="text-align: left;" trbidi="on">
Let's say that you want to copy a very big file, in slow or faulty media like a cheap USB stick or disk, and the copy fails leaving you with an incomplete file:<br />
<pre><span style="font-size: 75%;">
olimpo:/Linux_minimal# ls -la
total 18739092
drwx------ 2 root root 4096 Apr 3 16:22 .
drwxr-xr-x 8 root root 4096 Apr 3 15:11 ..
-rw-r--r-- 1 root root 19170066432 Apr 3 16:46 Linux.dsk
-rw-r--r-- 1 root root 211 May 2 2010 create.txt
-rwxr-xr-x 1 root root 154 Apr 2 16:02 launch
olimpo:/Linux_minimal# ls -la ../Linux_test2
total 19417644
drwxr-xr-x 2 root root 4096 Mar 28 16:55 .
drwxr-xr-x 8 root root 4096 Apr 3 15:11 ..
-rw-r--r-- 1 root root 19864223744 Apr 2 15:47 Linux.dsk
-rw-r--r-- 1 root root 211 May 2 2010 create.txt
-rwxr-xr-x 1 root root 154 Apr 2 16:02 launch
</span></pre>
<br />
What a frustration! No matter how many times you try to copy the file, the copy operation aborts before doing the task.<br />
<br />
Fortunately you can resume the copy with rsync:<br />
<pre><span style="font-size: 75%;">
olimpo:/Linux_minimal# rsync --progress --partial --append ../Linux_test2/Linux.dsk Linux.dsk
Linux.dsk
19864223744 100% 10.87MB/s 0:01:00 (xfer#1, to-check=0/1)
sent 694242119 bytes received 31 bytes 11107874.40 bytes/sec
total size is 19864223744 speedup is 28.61
</span></pre>
<br />
And just to be sure, you can check the files with diff (the slow way) or md5sum (the quick way):<br />
<pre><span style="font-size: 75%;">
olimpo:/Linux_minimal# md5sum Linux.dsk
eccc1436fe7a10acb44973449fd430bc Linux.dsk
olimpo:/Linux_minimal# md5sum ../Linux_test2/Linux.dsk
eccc1436fe7a10acb44973449fd430bc ../Linux_test2/Linux.dsk
</span></pre>
<br />
More information:<br />
<br />
<a href="http://www.linuxquestions.org/questions/linux-newbie-8/how-to-resume-failed-copy-cp-command-where-it-left-off-183092/">How To Resume Failed copy ( cp command ) where it left off?</a></div>
Cronoshttp://www.blogger.com/profile/15427827507143505689noreply@blogger.com0tag:blogger.com,1999:blog-4232775939402125807.post-18473519516189471882013-03-13T14:49:00.001-07:002013-03-13T15:00:58.017-07:00Managing maintenance tasks in Oracle 11g<div dir="ltr" style="text-align: left;" trbidi="on">
As you might know, if you want to change execution parameters of automated database maintenance tasks in Oracle 10g you have to modify the jobs and windows associated to those tasks; therefore, you might be tempted to change 11g maintenance tasks the same way but you'll find that there are no jobs that look like maintenance tasks, and the windows still exist but are disabled:<br />
<pre><span style="font-size: 75%;">
SQL> set linesize 120
SQL> column WINDOW_NAME format a20
SQL> column ENABLED format a7
SQL> column REPEAT_INTERVAL format a60
SQL> column DURATION format a15
SQL> select window_name, enabled, repeat_interval, duration from dba_scheduler_windows</span></pre>
<pre><span style="font-size: 75%;">where window_name like 'WEEK%';
WINDOW_NAME ENABLED REPEAT_INTERVAL DURATION
-------------------- ------- ------------------------------------------------------------ ---------------
WEEKNIGHT_WINDOW FALSE freq=daily;byday=MON,TUE,WED,THU,FRI;byhour=22;byminute=0; b +000 08:00:00
ysecond=0
WEEKEND_WINDOW FALSE freq=daily;byday=SAT;byhour=0;byminute=0;bysecond=0 +002 00:00:00
</span></pre>
<br />
Did nobody tell you? In Oracle 11g you have to use the DBMS_AUTO_TASK_ADMIN procedures to control the three maintenance tasks: Automatic Optimizer Statistics Collection, Automatic Segment Advisor and Automatic SQL Tuning Advisor.<br />
<br />
To check tasks information you can do this:<br />
<pre><span style="font-size: 75%;">
SQL> column CLIENT_NAME format a32
SQL> column STATUS format a10
SQL> column WINDOW_GROUP format a20
SQL> column ATTRIBUTES format a40
SQL> select client_name, status, window_group, attributes from dba_autotask_client;
CLIENT_NAME STATUS WINDOW_GROUP ATTRIBUTES
-------------------------------- ---------- -------------------- ----------------------------------------
auto optimizer stats collection ENABLED ORA$AT_WGRP_OS ON BY DEFAULT, VOLATILE, SAFE TO KILL
auto space advisor ENABLED ORA$AT_WGRP_SA ON BY DEFAULT, VOLATILE, SAFE TO KILL
sql tuning advisor ENABLED ORA$AT_WGRP_SQ ONCE PER WINDOW, ON BY DEFAULT, VOLATILE
, SAFE TO KILL
</span></pre>
<br />
As you can see, all the three tasks are enabled by default and have its own window group, but all the window groups have the same window members, one per day of the week:<br />
<pre><span style="font-size: 75%;">
SQL> select window_name, enabled, repeat_interval, duration from dba_scheduler_windows where window_name in
(select WINDOW_NAME from dba_scheduler_wingroup_members where WINDOW_GROUP_NAME='ORA$AT_WGRP_SQ');
WINDOW_NAME ENABLED REPEAT_INTERVAL DURATION
-------------------- ------- ------------------------------------------------------------ ---------------
SATURDAY_WINDOW TRUE freq=daily;byday=SAT;byhour=6;byminute=0; bysecond=0 +000 20:00:00
WEDNESDAY_WINDOW TRUE freq=daily;byday=WED;byhour=22;byminute=0; bysecond=0 +000 04:00:00
THURSDAY_WINDOW TRUE freq=daily;byday=THU;byhour=22;byminute=0; bysecond=0 +000 04:00:00
SUNDAY_WINDOW TRUE freq=daily;byday=SUN;byhour=6;byminute=0; bysecond=0 +000 20:00:00
FRIDAY_WINDOW TRUE freq=daily;byday=FRI;byhour=22;byminute=0; bysecond=0 +000 04:00:00
TUESDAY_WINDOW TRUE freq=daily;byday=TUE;byhour=22;byminute=0; bysecond=0 +000 04:00:00
MONDAY_WINDOW TRUE freq=daily;byday=MON;byhour=22;byminute=0; bysecond=0 +000 04:00:00
7 rows selected.
</span></pre>
<br />
Then, if you want to disable a task you can do it this way:<br />
<pre><span style="font-size: 75%;">
SQL> begin
dbms_auto_task_admin.disable
(client_name => 'sql tuning advisor',
operation => NULL,
window_name => NULL);
end;
/
PL/SQL procedure successfully completed.
SQL> select client_name, status, window_group, attributes from dba_autotask_client;
CLIENT_NAME STATUS WINDOW_GROUP ATTRIBUTES
-------------------------------- ---------- -------------------- ----------------------------------------
auto optimizer stats collection ENABLED ORA$AT_WGRP_OS ON BY DEFAULT, VOLATILE, SAFE TO KILL
auto space advisor ENABLED ORA$AT_WGRP_SA ON BY DEFAULT, VOLATILE, SAFE TO KILL
sql tuning advisor DISABLED ORA$AT_WGRP_SQ ONCE PER WINDOW, ON BY DEFAULT, VOLATILE
, SAFE TO KILL
</span></pre>
<br />
And for enabling it again:<br />
<pre><span style="font-size: 75%;">
SQL> begin
dbms_auto_task_admin.enable
(client_name => 'sql tuning advisor',
operation => NULL,
window_name => NULL);
end;
/
PL/SQL procedure successfully completed.
SQL> select client_name, status, window_group, attributes from dba_autotask_client;
CLIENT_NAME STATUS WINDOW_GROUP ATTRIBUTES
-------------------------------- ---------- -------------------- ----------------------------------------
auto optimizer stats collection ENABLED ORA$AT_WGRP_OS ON BY DEFAULT, VOLATILE, SAFE TO KILL
auto space advisor ENABLED ORA$AT_WGRP_SA ON BY DEFAULT, VOLATILE, SAFE TO KILL
sql tuning advisor ENABLED ORA$AT_WGRP_SQ ONCE PER WINDOW, ON BY DEFAULT, VOLATILE
, SAFE TO KILL
</span></pre>
<br />
Now, if you want to disable a task for a certain day (window), you can do this:<br />
<pre><span style="font-size: 75%;">
SQL> begin
dbms_auto_task_admin.disable
(client_name => 'sql tuning advisor',
operation => NULL,
window_name => 'MONDAY_WINDOW');
end;
/
PL/SQL procedure successfully completed.
SQL> select WINDOW_NAME, OPTIMIZER_STATS, SEGMENT_ADVISOR, SQL_TUNE_ADVISOR from dba_autotask_window_clients;
WINDOW_NAME OPTIMIZER_STATS SEGMENT_ADVISOR SQL_TUNE_ADVISOR
-------------------- ------------------------ ------------------------ ------------------------
WEDNESDAY_WINDOW ENABLED ENABLED ENABLED
FRIDAY_WINDOW ENABLED ENABLED ENABLED
SATURDAY_WINDOW ENABLED ENABLED ENABLED
THURSDAY_WINDOW ENABLED ENABLED ENABLED
TUESDAY_WINDOW ENABLED ENABLED ENABLED
SUNDAY_WINDOW ENABLED ENABLED ENABLED
MONDAY_WINDOW ENABLED ENABLED DISABLED
7 rows selected.
</span></pre>
<br />
And for enabling a task in a window:<br />
<pre><span style="font-size: 75%;">
SQL> begin
dbms_auto_task_admin.enable
(client_name => 'sql tuning advisor',
operation => NULL,
window_name => 'MONDAY_WINDOW');
end;
/
PL/SQL procedure successfully completed.
SQL> select WINDOW_NAME, OPTIMIZER_STATS, SEGMENT_ADVISOR, SQL_TUNE_ADVISOR from dba_autotask_window_clients;
WINDOW_NAME OPTIMIZER_STATS SEGMENT_ADVISOR SQL_TUNE_ADVISOR
-------------------- ------------------------ ------------------------ ------------------------
WEDNESDAY_WINDOW ENABLED ENABLED ENABLED
FRIDAY_WINDOW ENABLED ENABLED ENABLED
SATURDAY_WINDOW ENABLED ENABLED ENABLED
THURSDAY_WINDOW ENABLED ENABLED ENABLED
TUESDAY_WINDOW ENABLED ENABLED ENABLED
SUNDAY_WINDOW ENABLED ENABLED ENABLED
MONDAY_WINDOW ENABLED ENABLED ENABLED
7 rows selected.
</span></pre>
<br />
Finally, if you want to change window attributes like the repeat interval, you can do it as usual:<br />
<pre><span style="font-size: 75%;">
SQL> exec dbms_scheduler.set_attribute('MONDAY_WINDOW','repeat_interval','freq=daily;byday=MON;byhour=21;byminute=0; bysecond=0');
PL/SQL procedure successfully completed.
SQL> select window_name, enabled, repeat_interval, duration from dba_scheduler_windows where window_name in</span></pre>
<pre><span style="font-size: 75%;">(select WINDOW_NAME from dba_scheduler_wingroup_members where WINDOW_GROUP_NAME='ORA$AT_WGRP_SQ');
WINDOW_NAME ENABLED REPEAT_INTERVAL DURATION
-------------------- ------- ------------------------------------------------------------ ---------------
SATURDAY_WINDOW TRUE freq=daily;byday=SAT;byhour=6;byminute=0; bysecond=0 +000 20:00:00
MONDAY_WINDOW TRUE freq=daily;byday=MON;byhour=21;byminute=0; bysecond=0 +000 04:00:00
WEDNESDAY_WINDOW TRUE freq=daily;byday=WED;byhour=22;byminute=0; bysecond=0 +000 04:00:00
THURSDAY_WINDOW TRUE freq=daily;byday=THU;byhour=22;byminute=0; bysecond=0 +000 04:00:00
SUNDAY_WINDOW TRUE freq=daily;byday=SUN;byhour=6;byminute=0; bysecond=0 +000 20:00:00
FRIDAY_WINDOW TRUE freq=daily;byday=FRI;byhour=22;byminute=0; bysecond=0 +000 04:00:00
TUESDAY_WINDOW TRUE freq=daily;byday=TUE;byhour=22;byminute=0; bysecond=0 +000 04:00:00
7 rows selected.
</span></pre>
<br />
And just in case you want to know what operation is associated to what client:<br />
<pre><span style="font-size: 75%;">
SQL> column OPERATION_NAME format a40
SQL> select client_name, operation_name from dba_autotask_operation;
CLIENT_NAME OPERATION_NAME
-------------------------------- ----------------------------------------
auto optimizer stats collection auto optimizer stats job
auto space advisor auto space advisor job
sql tuning advisor automatic sql tuning task
</span></pre>
<br />
More information:<br />
<br />
<a href="http://docs.oracle.com/cd/B28359_01/server.111/b28310/tasks.htm">Managing Automated Database Maintenance Tasks</a><br />
<a href="http://docs.oracle.com/cd/B28359_01/appdev.111/b28419/d_autotaskadm.htm">DBMS_AUTO_TASK_ADMIN</a></div>
Cronoshttp://www.blogger.com/profile/15427827507143505689noreply@blogger.com0tag:blogger.com,1999:blog-4232775939402125807.post-22880112055052732102013-03-07T11:49:00.000-08:002013-03-07T11:49:09.383-08:00Fixing failures in Oracle 11g with DRA and RMAN<div dir="ltr" style="text-align: left;" trbidi="on">
The Data Recovery Advisor was introduced in Oracle 11g, a feature that gives advice regarding database failures and also repairs them if possible. The DRA relies in the Health Monitor for diagnostics and can be used by means of RMAN or Enterprise Manager. It looks fine but since I don't have a lot of faulty databases I don't have experience with it; however, I have to study it in order to get the OCP 11g certification upgrade. At first look it seems like a good feature for administrators in a hurry or without so much experience, but personally I prefer to do things by hand and by myself.<br />
<br />
Anyway, if you want to check the DRA capabilities and you have a database for practicing it might be worth the time trying it. The following is an exercise for doing that; first of all, back up your database because backups are an important part of recovering using the DRA. Next, shut down your database and rename (or delete if you're faithful enough) a datafile, and try to start up your database:<br />
<pre><span style="font-size: 75%;">
oracle@olimpo:~$ sqlplus '/ as sysdba'
SQL*Plus: Release 11.2.0.1.0 Production on Wed Mar 6 13:35:56 2013
Copyright (c) 1982, 2009, Oracle. All rights reserved.
Connected to an idle instance.
SQL> startup
ORACLE instance started.
Total System Global Area 263639040 bytes
Fixed Size 1335892 bytes
Variable Size 213913004 bytes
Database Buffers 41943040 bytes
Redo Buffers 6447104 bytes
Database mounted.
ORA-01157: cannot identify/lock data file 5 - see DBWR trace file
ORA-01110: data file 5: '/opt/oracle/oradata/orcl11g/example01.dbf'
</span></pre>
<br />
The database didn't open as expected so the next step is running RMAN to use the DRA. As you might know, it's necessary to have the database at least mounted in order to be able to use RMAN:<br />
<pre><span style="font-size: 75%;">
oracle@olimpo:~$ rman target /
Recovery Manager: Release 11.2.0.1.0 - Production on Wed Mar 6 13:36:56 2013
Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.
connected to target database: ORCL11G (DBID=915822427, not open)
</span></pre>
<br />
The next step is listing the current known failures:<br />
<pre><span style="font-size: 75%;">
RMAN> list failure;
using target database control file instead of recovery catalog
List of Database Failures
=========================
Failure ID Priority Status Time Detected Summary
---------- -------- --------- ------------- -------
42 HIGH OPEN 06-MAR-13 One or more non-system datafiles are missing
</span></pre>
<br />
If you want to know more details about certain failure you can do it as well: <br />
<pre><span style="font-size: 75%;">
RMAN> list failure 42 detail;
List of Database Failures
=========================
Failure ID Priority Status Time Detected Summary
---------- -------- --------- ------------- -------
42 HIGH OPEN 06-MAR-13 One or more non-system datafiles are missing
Impact: See impact for individual child failures
List of child failures for parent failure ID 42
Failure ID Priority Status Time Detected Summary
---------- -------- --------- ------------- -------
145 HIGH OPEN 06-MAR-13 Datafile 5: '/opt/oracle/oradata/orcl11g/example01.dbf' is missing
Impact: Some objects in tablespace EXAMPLE might be unavailable
</span></pre>
<br />
The Health Monitor runs diagnostics automatically when some high or critical failure happens, but if for some reason there is not a diagnostic performed first then you cannot get advice regarding a failure. The next step involves getting advice regarding the open failures:<br />
<pre><span style="font-size: 75%;">
RMAN> advise failure;
List of Database Failures
=========================
Failure ID Priority Status Time Detected Summary
---------- -------- --------- ------------- -------
42 HIGH OPEN 06-MAR-13 One or more non-system datafiles are missing
Impact: See impact for individual child failures
List of child failures for parent failure ID 42
Failure ID Priority Status Time Detected Summary
---------- -------- --------- ------------- -------
145 HIGH OPEN 06-MAR-13 Datafile 5: '/opt/oracle/oradata/orcl11g/example01.dbf' is missing
Impact: Some objects in tablespace EXAMPLE might be unavailable
analyzing automatic repair options; this may take some time
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=133 device type=DISK
analyzing automatic repair options complete
Mandatory Manual Actions
========================
no manual actions available
Optional Manual Actions
=======================
1. If file /opt/oracle/oradata/orcl11g/example01.dbf was unintentionally renamed or moved, restore it
Automated Repair Options
========================
Option Repair Description
------ ------------------
1 NOARCHIVELOG mode restore datafile 5
Strategy: The repair includes complete media recovery with no data loss
Repair script: /opt/oracle/diag/rdbms/orcl11g/orcl11g/hm/reco_1400180561.hm
</span></pre>
<br />
As you might guess, the DRA first checks relevant and mandatory prerequisites before giving any automated repair option, like database backups. The optional manual actions are recommendations about solving the failure by hand, instead of letting the ADR fix the failures automatically. Sometimes you have to carry on mandatory manual actions before being able to do any manual or automated repair, and in this case there is no mandatory manual actions to do.<br />
<br />
Moreover, there are two kinds of repair options: with no data loss and with data loss, be sure to check the strategy to know if the selected repair option involves no data loss, or at least to understand what you're going to lose if you select that option in case there is no better option.<br />
<br />
Also, if you want you can check and modify the repair script listed before doing any repair, or you can preview the repair procedure without doing any repair at all this way:<br />
<pre><span style="font-size: 75%;">
RMAN> repair failure preview;
Strategy: The repair includes complete media recovery with no data loss
Repair script: /opt/oracle/diag/rdbms/orcl11g/orcl11g/hm/reco_1400180561.hm
contents of repair script:
# NOARCHIVELOG mode restore datafile
restore datafile 5;
recover datafile 5;
</span></pre>
<br />
If you're happy with the proposed automatic repair options then you can repair the open database failures this way:<br />
<pre><span style="font-size: 75%;">
RMAN> repair failure;
Strategy: The repair includes complete media recovery with no data loss
Repair script: /opt/oracle/diag/rdbms/orcl11g/orcl11g/hm/reco_1400180561.hm
contents of repair script:
# NOARCHIVELOG mode restore datafile
restore datafile 5;
recover datafile 5;
Do you really want to execute the above repair (enter YES or NO)? YES
executing repair script
Starting restore at 06-MAR-13
using channel ORA_DISK_1
channel ORA_DISK_1: starting datafile backup set restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_DISK_1: restoring datafile 00005 to /opt/oracle/oradata/orcl11g/example01.dbf
channel ORA_DISK_1: reading from backup piece /opt/oracle/flash_recovery_area/ORCL11G/backupset/2013_03_05/o1_mf_nnndf_TAG20130305T165413_8mdxwq5t_.bkp
channel ORA_DISK_1: piece handle=/opt/oracle/flash_recovery_area/ORCL11G/backupset/2013_03_05/o1_mf_nnndf_TAG20130305T165413_8mdxwq5t_.bkp tag=TAG20130305T165413
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:07
Finished restore at 06-MAR-13
Starting recover at 06-MAR-13
using channel ORA_DISK_1
starting media recovery
media recovery complete, elapsed time: 00:00:01
Finished recover at 06-MAR-13
repair failure complete
Do you want to open the database (enter YES or NO)? YES
database opened
RMAN> exit
Recovery Manager complete.
</span></pre>
<br />
You have to confirm that you really want to execute repairs, and as a final step you might want to open the database before leaving RMAN. And that's all! In this case everything went fine and the database was repaired with no data loss:<br />
<pre><span style="font-size: 75%;">
oracle@olimpo:~$ sqlplus '/ as sysdba'
SQL*Plus: Release 11.2.0.1.0 Production on Wed Mar 6 13:43:10 2013
Copyright (c) 1982, 2009, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> column INSTANCE_NAME format a20
SQL> column STATUS format a20
SQL> column DATABASE_STATUS format a20
SQL> select INSTANCE_NAME, STATUS, DATABASE_STATUS from v$instance;
INSTANCE_NAME STATUS DATABASE_STATUS
-------------------- -------------------- --------------------
orcl11g OPEN ACTIVE
</span></pre>
<br />
More information:<br />
<br />
<a href="http://docs.oracle.com/cd/E11882_01/backup.112/e10642/rcmrepai.htm">Diagnosing and Repairing Failures with Data Recovery Advisor</a></div>
Cronoshttp://www.blogger.com/profile/15427827507143505689noreply@blogger.com0tag:blogger.com,1999:blog-4232775939402125807.post-40132554140471388392013-03-06T08:36:00.001-08:002013-03-06T08:41:25.022-08:00RMAN crash course<div dir="ltr" style="text-align: left;" trbidi="on">
It has been a long time since I posted here! In my previous job (this is my second week in my new one) I didn't have so much chance to keep posting because my position didn't involve neither UNIX server administration nor database administration, but I hope this new job will give me plenty of material to write interesting posts.<br />
<br />
So let's begin with something simple, backing up an Oracle database with RMAN. Let's say that you're learning database management or you have a small and hopefully non-critical database that you want to backup, and you don't mind using the Flash Recovery Area; the directory used as FRA is set by the <b>db_recovery_file_dest</b> parameter in the database. First of all, you have to set the Oracle environment variables needed to connect to your selected database:<br />
<pre><span style="font-size: 75%;">
oracle@olimpo:~/rman$ set|grep ORACLE
ORACLE_BASE=/opt/oracle
ORACLE_HOME=/opt/oracle/product/10.2.0/server
ORACLE_SID=orcl11g
</span></pre>
<br />
Then you can run RMAN to connect to the current target database (orcl11g):<br />
<pre><span style="font-size: 75%;">
oracle@olimpo:~/rman$ rman target /
Recovery Manager: Release 11.2.0.1.0 - Production on Tue Mar 5 16:53:00 2013
Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.
connected to target database: ORCL11G (DBID=915822427, not open)
</span></pre>
<br />
You might have noticed that my database is not opened, that's because it has no archive logs so in order to make a consistent backup I put it in mount state. If this is the first time you launch RMAN then it would be better to check its parameters:<br />
<pre><span style="font-size: 75%;">
RMAN> show all;
using target database control file instead of recovery catalog
RMAN configuration parameters for database with db_unique_name ORCL11G are:
CONFIGURE RETENTION POLICY TO REDUNDANCY 1; # default
CONFIGURE BACKUP OPTIMIZATION OFF; # default
CONFIGURE DEFAULT DEVICE TYPE TO DISK; # default
CONFIGURE CONTROLFILE AUTOBACKUP OFF; # default
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '%F'; # default
CONFIGURE DEVICE TYPE DISK PARALLELISM 1 BACKUP TYPE TO BACKUPSET; # default
CONFIGURE DATAFILE BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
CONFIGURE ARCHIVELOG BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
CONFIGURE MAXSETSIZE TO UNLIMITED; # default
CONFIGURE ENCRYPTION FOR DATABASE OFF; # default
CONFIGURE ENCRYPTION ALGORITHM 'AES128'; # default
CONFIGURE COMPRESSION ALGORITHM 'BASIC' AS OF RELEASE 'DEFAULT' OPTIMIZE FOR LOAD TRUE ; # default
CONFIGURE ARCHIVELOG DELETION POLICY TO NONE; # default
CONFIGURE SNAPSHOT CONTROLFILE NAME TO '/opt/oracle/product/10.2.0/server/dbs/snapcf_orcl11g.f'; # default
</span></pre>
<br />
For example, you might want to check the compression algorithm and retention policy (how many backups you want to keep at the same time); if you want to change something you just have to issue the command the same way it's shown:<br />
<pre><span style="font-size: 75%;">
RMAN> CONFIGURE RETENTION POLICY TO REDUNDANCY 2;
new RMAN configuration parameters:
CONFIGURE RETENTION POLICY TO REDUNDANCY 2;
new RMAN configuration parameters are successfully stored
RMAN> show all;
RMAN configuration parameters for database with db_unique_name ORCL11G are:
CONFIGURE RETENTION POLICY TO REDUNDANCY 2;
CONFIGURE BACKUP OPTIMIZATION OFF; # default
CONFIGURE DEFAULT DEVICE TYPE TO DISK; # default
CONFIGURE CONTROLFILE AUTOBACKUP OFF; # default
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '%F'; # default
CONFIGURE DEVICE TYPE DISK PARALLELISM 1 BACKUP TYPE TO BACKUPSET; # default
CONFIGURE DATAFILE BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
CONFIGURE ARCHIVELOG BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
CONFIGURE MAXSETSIZE TO UNLIMITED; # default
CONFIGURE ENCRYPTION FOR DATABASE OFF; # default
CONFIGURE ENCRYPTION ALGORITHM 'AES128'; # default
CONFIGURE COMPRESSION ALGORITHM 'BASIC' AS OF RELEASE 'DEFAULT' OPTIMIZE FOR LOAD TRUE ; # default
CONFIGURE ARCHIVELOG DELETION POLICY TO NONE; # default
CONFIGURE SNAPSHOT CONTROLFILE NAME TO '/opt/oracle/product/10.2.0/server/dbs/snapcf_orcl11g.f'; # default
</span></pre>
<br />
Then if you're ready to backup your database issue this simple backup command:<br />
<pre><span style="font-size: 75%;">
RMAN> backup check logical database;
Starting backup at 05-MAR-13
using channel ORA_DISK_1
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00001 name=/opt/oracle/oradata/orcl11g/system01.dbf
input datafile file number=00002 name=/opt/oracle/oradata/orcl11g/sysaux01.dbf
input datafile file number=00005 name=/opt/oracle/oradata/orcl11g/example01.dbf
input datafile file number=00003 name=/opt/oracle/oradata/orcl11g/undotbs01.dbf
input datafile file number=00004 name=/opt/oracle/oradata/orcl11g/users01.dbf
channel ORA_DISK_1: starting piece 1 at 05-MAR-13
channel ORA_DISK_1: finished piece 1 at 05-MAR-13
piece handle=/opt/oracle/flash_recovery_area/ORCL11G/backupset/2013_03_05/o1_mf_nnndf_TAG20130305T165413_8mdxwq5t_.bkp tag=TAG20130305T165413 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:01:16
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
including current control file in backup set
including current SPFILE in backup set
channel ORA_DISK_1: starting piece 1 at 05-MAR-13
channel ORA_DISK_1: finished piece 1 at 05-MAR-13
piece handle=/opt/oracle/flash_recovery_area/ORCL11G/backupset/2013_03_05/o1_mf_ncsnf_TAG20130305T165413_8mdxz43d_.bkp tag=TAG20130305T165413 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
Finished backup at 05-MAR-13
</span></pre>
<br />
This way you can backup a database with no archive logs enabled; if you have a database with archive logs enabled then append the <b>plus archivelog</b> subclause at the end of the backup command. Also, the <b>check logical</b> subclause is for checking logical errors in the database while performing the backup, the command for backing up a database can be as simple as <b>backup database</b>.<br />
<br />
Now, if you want to check that your backup is really there you can do it this way:<br />
<pre><span style="font-size: 75%;">
RMAN> list backup summary;
List of Backups
===============
Key TY LV S Device Type Completion Time #Pieces #Copies Compressed Tag
------- -- -- - ----------- --------------- ------- ------- ---------- ---
1 B F A DISK 05-MAR-13 1 1 NO TAG20130305T165413
2 B F A DISK 05-MAR-13 1 1 NO TAG20130305T165413
</span></pre>
<br />
But wait, why there are two backups instead of one? Maybe a detailed list would show us why:<br />
<pre><span style="font-size: 75%;">
RMAN> list backup;
List of Backup Sets
===================
BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
1 Full 1.00G DISK 00:01:15 05-MAR-13
BP Key: 1 Status: AVAILABLE Compressed: NO Tag: TAG20130305T165413
Piece Name: /opt/oracle/flash_recovery_area/ORCL11G/backupset/2013_03_05/o1_mf_nnndf_TAG20130305T165413_8mdxwq5t_.bkp
List of Datafiles in backup set 1
File LV Type Ckp SCN Ckp Time Name
---- -- ---- ---------- --------- ----
1 Full 854520 05-MAR-13 /opt/oracle/oradata/orcl11g/system01.dbf
2 Full 854520 05-MAR-13 /opt/oracle/oradata/orcl11g/sysaux01.dbf
3 Full 854520 05-MAR-13 /opt/oracle/oradata/orcl11g/undotbs01.dbf
4 Full 854520 05-MAR-13 /opt/oracle/oradata/orcl11g/users01.dbf
5 Full 854520 05-MAR-13 /opt/oracle/oradata/orcl11g/example01.dbf
BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
2 Full 9.36M DISK 00:00:02 05-MAR-13
BP Key: 2 Status: AVAILABLE Compressed: NO Tag: TAG20130305T165413
Piece Name: /opt/oracle/flash_recovery_area/ORCL11G/backupset/2013_03_05/o1_mf_ncsnf_TAG20130305T165413_8mdxz43d_.bkp
SPFILE Included: Modification time: 05-MAR-13
SPFILE db_unique_name: ORCL11G
Control File Included: Ckp SCN: 854520 Ckp time: 05-MAR-13
</span></pre>
<br />
The first backup set is the full backup of the database, and the second one is the backup of the database's SPFILE, made by default. And before exiting from RMAN and in order to not fill up the FRA (its size set in the <b>db_recovery_file_dest_size</b> parameter), let's delete the obsolete backups, that is, backups no longer needed according to the retention policy:<br />
<pre><span style="font-size: 75%;">
RMAN> delete noprompt obsolete;
RMAN retention policy will be applied to the command
RMAN retention policy is set to redundancy 2
using channel ORA_DISK_1
no obsolete backups found
RMAN> quit
Recovery Manager complete.
</span></pre>
<br />
More information:<br />
<br />
<a href="http://docs.oracle.com/cd/E11882_01/backup.112/e10642/rcmquick.htm">Getting Started with RMAN</a><br />
<a href="http://docs.oracle.com/cd/E11882_01/backup.112/e10643/rcmsynta007.htm">Oracle Database Backup and Recovery Reference (Backup)</a></div>
Cronoshttp://www.blogger.com/profile/15427827507143505689noreply@blogger.com0tag:blogger.com,1999:blog-4232775939402125807.post-75232161744945992182012-05-12T04:12:00.001-07:002012-05-12T04:13:17.727-07:00Configuring a Tata Photon+ USB device in Linux<div dir="ltr" style="text-align: left;" trbidi="on">
It has been a long time since my last post because I changed jobs three months ago, and in fact this post might give a hint about my new situation; anyway I hope to keep posting Linux and UNIX-related messages and this one is about dealing with a mobile broadband USB device.
It is not so difficult to work with this kind of devices in Linux, and is very simple to configure it with NetworkManager, but first you need to have installed the usb-modeswitch software that changes the device's state from USB storage to USB modem in order to use the USB dongle as a modem. In Debian Squeeze you can do it installing the usb-modeswitch package.
If you don't have the usb-modeswitch software you will see just an USB storage device, something like this:
<span style="font-size: 75%;"><pre>May 12 15:23:38 olimpo kernel: [ 64.172559] usb 2-1: new full speed USB device using uhci_hcd and address 2
May 12 15:23:38 olimpo kernel: [ 64.339573] usb 2-1: New USB device found, idVendor=12d1, idProduct=1446
May 12 15:23:38 olimpo kernel: [ 64.339584] usb 2-1: New USB device strings: Mfr=1, Product=2, SerialNumber=4
May 12 15:23:38 olimpo kernel: [ 64.339593] usb 2-1: Product: HUAWEI Mobile
May 12 15:23:38 olimpo kernel: [ 64.339600] usb 2-1: Manufacturer: HUAWEI TECHNOLOGIES
May 12 15:23:38 olimpo kernel: [ 64.339607] usb 2-1: SerialNumber: XXXXXXXXXXXXXXXXXX
May 12 15:23:38 olimpo kernel: [ 64.339850] usb 2-1: configuration #1 chosen from 1 choice
May 12 15:23:38 olimpo kernel: [ 64.350119] scsi4 : SCSI emulation for USB Mass Storage devices
May 12 15:23:38 olimpo kernel: [ 64.360054] usb-storage: device found at 2
May 12 15:23:38 olimpo kernel: [ 64.360061] usb-storage: waiting for device to settle before scanning</pre>
</span>
After installing the usb-modeswitch software you can plug your USB modem, and you will see at the end something like this:
<span style="font-size: 75%;"><pre>May 12 15:23:40 olimpo usb_modeswitch: switching 12d1:1446 (HUAWEI TECHNOLOGIES: HUAWEI Mobile)
May 12 15:23:46 olimpo kernel: [ 70.408061] usb 2-1: new full speed USB device using uhci_hcd and address 3
May 12 15:23:46 olimpo kernel: [ 70.576318] usb 2-1: New USB device found, idVendor=12d1, idProduct=140b
May 12 15:23:46 olimpo kernel: [ 70.576330] usb 2-1: New USB device strings: Mfr=1, Product=2, SerialNumber=4
May 12 15:23:46 olimpo kernel: [ 70.576339] usb 2-1: Product: HUAWEI Mobile
May 12 15:23:46 olimpo kernel: [ 70.576345] usb 2-1: Manufacturer: HUAWEI TECHNOLOGIES
May 12 15:23:46 olimpo kernel: [ 70.576352] usb 2-1: SerialNumber: XXXXXXXXXXXXXXXXXX
May 12 15:23:46 olimpo kernel: [ 70.576600] usb 2-1: configuration #1 chosen from 1 choice
May 12 15:23:46 olimpo kernel: [ 70.587360] scsi8 : SCSI emulation for USB Mass Storage devices
May 12 15:23:46 olimpo kernel: [ 70.588245] usb-storage: device found at 3
May 12 15:23:46 olimpo kernel: [ 70.588252] usb-storage: waiting for device to settle before scanning
May 12 15:23:46 olimpo kernel: [ 70.785972] usbcore: registered new interface driver usbserial
May 12 15:23:46 olimpo usb_modeswitch: switched to 12d1:140b (HUAWEI TECHNOLOGIES: HUAWEI Mobile)
May 12 15:23:46 olimpo kernel: [ 70.787067] USB Serial support registered for generic
May 12 15:23:46 olimpo kernel: [ 70.788192] usbcore: registered new interface driver usbserial_generic
May 12 15:23:46 olimpo kernel: [ 70.788201] usbserial: USB Serial Driver core
May 12 15:23:46 olimpo kernel: [ 71.042606] USB Serial support registered for GSM modem (1-port)
May 12 15:23:46 olimpo kernel: [ 71.048829] option 2-1:1.0: GSM modem (1-port) converter detected
May 12 15:23:46 olimpo kernel: [ 71.050191] usb 2-1: GSM modem (1-port) converter now attached to ttyUSB0
May 12 15:23:46 olimpo kernel: [ 71.050242] option 2-1:1.1: GSM modem (1-port) converter detected
May 12 15:23:46 olimpo kernel: [ 71.050660] usb 2-1: GSM modem (1-port) converter now attached to ttyUSB1
May 12 15:23:46 olimpo kernel: [ 71.050702] option 2-1:1.2: GSM modem (1-port) converter detected
May 12 15:23:46 olimpo kernel: [ 71.056719] usb 2-1: GSM modem (1-port) converter now attached to ttyUSB2
May 12 15:23:46 olimpo kernel: [ 71.056795] usbcore: registered new interface driver option
May 12 15:23:46 olimpo kernel: [ 71.056803] option: v0.7.2:USB Driver for GSM modems</pre>
</span>
This is the most important part, to have detected your USB dongle as a GSM modem. If you like you can configure it easily with NetworkManager, but if you prefer something that you can do in a terminal then install the wvdial package and fill the /etc/wvdial.conf and /etc/ppp/chap-secrets files with this:
<span style="font-size: 75%;"><pre>olimpo:/var/log# cat /etc/wvdial.conf
[Dialer Defaults]
Init1 = ATZ
Init2 = ATQ0 V1 E1 S0=0 &C1 &D2 +FCLASS=0
Modem Type = USB Modem
ISDN = 0
Phone = #777
New PPPD = yes
Modem = /dev/ttyUSB0
Username = internet
Password = internet
Baud = 230400
olimpo:/var/log# cat /etc/ppp/chap-secrets
# Secrets for authentication using CHAP
# client server secret IP addresses
internet * internet</pre>
</span>
And if you're using Debian you can also issue the dpkg-reconfigure wvdial command and give all the information required to the configuration program. Then you can launch wvdial as root from a terminal prompt and connect to the Internet, and you will see something like this in the syslog:
<span style="font-size: 75%;"><pre>May 12 15:47:06 olimpo pppd[11490]: pppd 2.4.5 started by root, uid 0
May 12 15:47:06 olimpo kernel: [ 1770.688570] PPP generic driver version 2.4.2
May 12 15:47:06 olimpo pppd[11490]: Using interface ppp0
May 12 15:47:06 olimpo pppd[11490]: Connect: ppp0 <--> /dev/ttyUSB0
May 12 15:47:09 olimpo pppd[11490]: CHAP authentication succeeded
May 12 15:47:09 olimpo pppd[11490]: CHAP authentication succeeded
May 12 15:47:09 olimpo kernel: [ 1773.210976] PPP BSD Compression module registered
May 12 15:47:09 olimpo kernel: [ 1773.225216] PPP Deflate Compression module registered
May 12 15:47:09 olimpo pppd[11490]: local IP address 59.161.17.54
May 12 15:47:09 olimpo pppd[11490]: remote IP address 172.29.145.65
May 12 15:47:09 olimpo pppd[11490]: primary DNS address 4.2.2.3
May 12 15:47:09 olimpo pppd[11490]: secondary DNS address 121.242.190.181
...
May 12 15:54:11 olimpo pppd[11490]: Terminating on signal 15
May 12 15:54:11 olimpo pppd[11490]: Connect time 7.1 minutes.
May 12 15:54:11 olimpo pppd[11490]: Sent 460447 bytes, received 1883330 bytes.</pre>
</span>
Of course, this procedure might be helpful for configuring other USB modems as well.</div>Cronoshttp://www.blogger.com/profile/15427827507143505689noreply@blogger.com0tag:blogger.com,1999:blog-4232775939402125807.post-84382286195783947402012-02-13T13:55:00.000-08:002012-02-13T14:30:10.786-08:00ORA-09817 error and full file systemsLet's say that you're trying to log into your database and suddenly got this error message:<br /><span style="font-size:75%;"><pre><br />oracle@mydb$ sqlplus '/ as sysdba'<br /><br />SQL*Plus: Release 11.1.0.7.0 - Production on Mon Feb 13 15:47:27 2012<br /><br />Copyright (c) 1982, 2008, Oracle. All rights reserved.<br /><br />ERROR:<br />ORA-09817: Write to audit file failed.<br />SVR4 Error: 28: No space left on device<br />ORA-01075: you are currently logged on<br /><br /><br />Enter user-name: <br /><br />oracle@mydb$ <br /></pre></span><br />This is an easy problem to guess and the key is in the message <span style="font-weight:bold;">No space left on device</span>. The instance cannot write audit files because the file system is full so you have to figure out why is that file system full:<br /><span style="font-size:75%;"><pre><br />oracle@mydb$ set | grep ORA<br />ORACLE_BASE=/oracle<br />ORACLE_HOME=/oracle/orahome<br />ORACLE_SID=mydb<br /><br />oracle@mydb$ strings $ORACLE_HOME/dbs/*mydb.ora | grep audit_file_dest<br />*.audit_file_dest='/oracle/admin/mydb/adump'<br /><br />oracle@mydb$ df -k | egrep '(Filesystem)|(/oracle)'<br />Filesystem kbytes used avail capacity Mounted on<br />/dev/vx/dsk/orafs/oracle 16327680 16327680 0 100% /oracle<br /></pre></span><br />In this case the audit file destination is in the Oracle base file system, therefore the problem might be in other directory:<br /><span style="font-size:75%;"><pre><br />oracle@mydb$ du -k /oracle | sort -nr | head <br />16035094 /oracle<br />8412062 /oracle/diag<br />8297523 /oracle/diag/rdbms/mydb/mydb<br />8297523 /oracle/diag/rdbms/mydb<br />8297523 /oracle/diag/rdbms<br />8250027 /oracle/diag/rdbms/mydb/mydb/trace<br />6926099 /oracle/orahome<br />1631834 /oracle/orahome/mydirectory<br />938085 /oracle/orahome/mydirectory/somefiles<br />727017 /oracle/orahome/bin<br /><br />oracle@mydb$ du -ka /oracle/diag/rdbms/mydb/mydb/trace | sort -nr | head<br />8250027 /oracle/diag/rdbms/mydb/mydb/trace<br />5737533 /oracle/diag/rdbms/mydb/mydb/trace/mydb_ora_5198.trc<br />2506955 /oracle/diag/rdbms/mydb/mydb/trace/mydb_ora_18305.trc<br />1946 /oracle/diag/rdbms/mydb/mydb/trace/mydb_ora_5198.trm<br />969 /oracle/diag/rdbms/mydb/mydb/trace/mydb_ora_18305.trm<br />457 /oracle/diag/rdbms/mydb/mydb/trace/mydb_ora_3695.trc<br />380 /oracle/diag/rdbms/mydb/mydb/trace/alert_mydb.log<br />314 /oracle/diag/rdbms/mydb/mydb/trace/mydb_j002_8800.trc<br />314 /oracle/diag/rdbms/mydb/mydb/trace/mydb_j002_13295.trc<br />314 /oracle/diag/rdbms/mydb/mydb/trace/mydb_j000_23311.trc<br /></pre></span><br />Here is the problem: there are two very big trace files that occupy 8 gigabytes, half the file system size. When you know why your file system is full you have to decide what to do, like erasing, moving or compressing files, just be sure not to discard files you might need.Cronoshttp://www.blogger.com/profile/15427827507143505689noreply@blogger.com0tag:blogger.com,1999:blog-4232775939402125807.post-54796017617653966202012-02-08T10:30:00.000-08:002012-02-08T10:57:26.789-08:00Creating partitioned tables and ORA-00922 errorThere is a lot of different version of the Oracle Database software and even if the logic and operation of the database is more or less the same across versions, it is also more or less different across versions; for example, there is no <a href="http://cronostech.blogspot.com/2011/04/dropping-database-in-oracle.html">drop database sentence in Oracle 9i</a>.<br /><br />So you work usually with 11g databases and love the tables partitioned by intervals, and you create one:<br /><span style="font-size:75%;"><pre><br />oracle@my11gdb~$ sqlplus '/ as sysdba'<br /><br />SQL*Plus: Release 11.2.0.3.0 Production on Wed Feb 8 12:43:42 2012<br /><br />Copyright (c) 1982, 2011, Oracle. All rights reserved.<br /><br /><br />Connected to:<br />Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production<br />With the Partitioning, OLAP, Data Mining and Real Application Testing options<br /><br />SQL> CREATE TABLE MY_TABLE<br />( <br />SOMETEXT VARCHAR2(4000),<br />MOREINFO VARCHAR2(256),<br />ADATE DATE<br />) PARTITION BY RANGE (ADATE)<br />INTERVAL (NUMTODSINTERVAL(1,'day'))<br />(partition p0 values less than (to_date('01-jan-2000','dd-mon-yyyy'))<br />);<br /><br />Table created.<br /></pre></span><br />And you try to create that same table in a 10g database just to find that you can't:<br /><span style="font-size:75%;"><pre><br />oracle@my10gdb~$ sqlplus '/ as sysdba'<br /><br />SQL*Plus: Release 10.2.0.4.0 - Production on Wed Feb 8 12:46:15 2012<br /><br />Copyright (c) 1982, 2007, Oracle. All Rights Reserved.<br /><br /><br />Connected to:<br />Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production<br />With the Partitioning, Real Application Clusters, OLAP, Data Mining<br />and Real Application Testing options<br /><br />SQL> CREATE TABLE MY_TABLE<br />( <br />SOMETEXT VARCHAR2(4000),<br />MOREINFO VARCHAR2(256),<br />ADATE DATE<br />) PARTITION BY RANGE (ADATE)<br />INTERVAL (NUMTODSINTERVAL(1,'day'))<br />(partition p0 values less than (to_date('01-jan-2000','dd-mon-yyyy'))<br />);<br />INTERVAL (NUMTODSINTERVAL(1,'day'))<br />*<br />ERROR at line 7:<br />ORA-00922: missing or invalid option<br /></pre></span><br />Look no more: the ORA-00922 error in a 10g database means that you're trying to use a functionality that doesn't exist; in a 11g database it would mean missing brackets or syntax errors.<br /><span style="font-size:75%;"><pre><br />oracle@my11gdb~$ sqlplus '/ as sysdba'<br /><br />SQL*Plus: Release 11.2.0.3.0 Production on Wed Feb 8 12:50:54 2012<br /><br />Copyright (c) 1982, 2011, Oracle. All rights reserved.<br /><br /><br />Connected to:<br />Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production<br />With the Partitioning, OLAP, Data Mining and Real Application Testing options<br /><br />SQL> CREATE TABLE MY_TABLE<br />( <br />SOMETEXT VARCHAR2(4000),<br />MOREINFO VARCHAR2(256),<br />ADATE DATE<br />) PARTITION BY RANGE (ADATE)<br />INTERVAL (NUMTODSINTERVAL(1,'day'))<br />partition p0 values less than (to_date('01-jan-2000','dd-mon-yyyy'));<br />partition p0 values less than (to_date('01-jan-2000','dd-mon-yyyy'))<br />*<br />ERROR at line 8:<br />ORA-00922: missing or invalid option<br /></pre></span><br />More information:<br /><br /><a href="http://docs.oracle.com/cd/B19306_01/server.102/b14200/statements_7002.htm">CREATE TABLE (10g Release 2)</a><br /><a href="http://docs.oracle.com/cd/B28359_01/server.111/b28286/statements_7002.htm">CREATE TABLE (11g Release 1)</a><br /><a href="http://docs.oracle.com/cd/B28359_01/server.111/b32024/part_admin.htm">Partition Administration</a>Cronoshttp://www.blogger.com/profile/15427827507143505689noreply@blogger.com0tag:blogger.com,1999:blog-4232775939402125807.post-52454333824369342242012-02-07T10:28:00.003-08:002012-02-07T10:56:14.838-08:00Adding numbers in a fileIf you have a small file filled with numbers and are tasked to add all of them, you can do it with bc and paste:<br /><span style="font-size:75%;"><pre><br />cronos@olimpo:~$ cat file.txt <br />1|11<br />2|22<br />3|33<br />4|44<br />5|55<br />|<br />6|66<br />7|77<br />8|88<br />9|99<br /><br />cronos@olimpo:~$ cat file.txt | cut -d '|' -f 1 | grep -v '^$' | paste -sd '+' - | bc<br />45<br /></pre></span><br />With cut you select the field to sum, with grep you select out empty fields, with paste you add at the end of each line a <span style="font-style:italic;">+</span> sign, and with bc you do the addition.<br /><br />But if you have a very big file this won't work; you can instead use awk:<br /><span style="font-size:75%;"><pre><br />cronos@olimpo:~$ cat file.txt | awk 'BEGIN {FS="|";OFMT="%.2f"} {a+=$1;b+=$2} END {print "First field:",a,"Second field:",b}'<br /><br />First field: 45 Second field: 495<br /></pre></span><br />With FS you set the field delimiter and with OFMT you set the number format, in this case two decimal places with no scientific notation. With awk a nice extra is to be able to add two or more columns at the same time.<br /><br />More information:<br /><br /><a href="http://unstableme.blogspot.com/2009/11/sum-of-numbers-in-file-unix.html">Sum of numbers in file - UNIX alternatives</a>Cronoshttp://www.blogger.com/profile/15427827507143505689noreply@blogger.com0tag:blogger.com,1999:blog-4232775939402125807.post-10951124841819974372012-01-31T12:47:00.000-08:002012-02-01T12:39:57.944-08:00Locking statistics of tablesHaving accurate statistics of database objects is mandatory in order to get the best execution path, and usually you can collect statistics using the DBMS_STATS package with no problem at all. But sometimes you have special tables that needs special treatment, like "temporary" tables (normal tables that are populated and its data modified very fast) that can change a lot the execution path of a SQL sentence and drive you crazy trying to pinpoint the root cause of that execution path changes.<br /><br />As a demonstration of this behavior we will create two tables, one that will change a lot (test1) and other that is more or less static (test2):<br /><span style="font-size:75%;"><pre><br />SQL> create table test1 (id number primary key, id_char varchar2(15));<br /><br />Table created.<br /><br />SQL> declare<br />mynum number := 1;<br />begin<br />while mynum < 10 loop<br /> insert into test1 values(mynum,to_char(mynum));<br /> mynum := mynum + 1;<br /> end loop;<br />end;<br />/<br /><br />PL/SQL procedure successfully completed.<br /><br />SQL> commit;<br /><br />Commit complete.<br /><br />SQL> create table test2 (id number primary key, id_char varchar2(15));<br /><br />Table created.<br /><br />SQL> declare<br />mynum number := 1;<br />begin<br />while mynum < 100000 loop<br /> insert into test2 values(mynum,to_char(mynum));<br /> mynum := mynum + 1;<br /> end loop;<br />end;<br />/<br /><br />PL/SQL procedure successfully completed.<br /><br />SQL> commit;<br /><br />Commit complete.<br /></pre></span><br />At the beginning test1 table has 9 records, and we want to join test1 and test2 tables to get a few records, but first we will gather statistics of test1:<br /><span style="font-size:75%;"><pre><br />SQL> BEGIN DBMS_STATS.GATHER_TABLE_STATS( OWNNAME => 'MYUSER', TABNAME => 'TEST1'); END;<br /> 2 /<br /><br />PL/SQL procedure successfully completed.<br /><br />SQL> select a.ID, b.ID_CHAR from test1 a join test2 b on (a.ID=b.ID) where a.ID between 4 and 7;<br /><br /> ID ID_CHAR<br />---------- ---------------<br /> 4 4<br /> 5 5<br /> 6 6<br /> 7 7<br /></pre></span><br />Next we will get the current execution plan for this small query:<br /><span style="font-size:75%;"><pre><br />SQL> EXPLAIN PLAN FOR<br />select a.ID, b.ID_CHAR from test1 a join test2 b on (a.ID=b.ID) where a.ID between 4 and 7;<br /><br />Explained.<br /><br />SQL> @$ORACLE_HOME/rdbms/admin/utlxpls.sql<br /><br />PLAN_TABLE_OUTPUT<br />----------------------------------------------------------------------------------------------------------------------------------<br />Plan hash value: 3677731951<br /><br />--------------------------------------------------------------------------------------------<br />| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |<br />--------------------------------------------------------------------------------------------<br />| 0 | SELECT STATEMENT | | 4 | 56 | 4 (0) | 00:00:01 |<br />| 1 | NESTED LOOPS | | 4 | 56 | 4 (0) | 00:00:01 |<br />| 2 | TABLE ACCESS BY INDEX ROWID| TEST2 | 5 | 55 | 4 (0) | 00:00:01 |<br />|* 3 | INDEX RANGE SCAN | SYS_C004858 | 5 | | 3 (0) | 00:00:01 |<br />|* 4 | INDEX UNIQUE SCAN | SYS_C004857 | 1 | 3 | 0 (0) | 00:00:01 |<br />--------------------------------------------------------------------------------------------<br /><br />Predicate Information (identified by operation id):<br />---------------------------------------------------<br /><br /> 3 - access("B"."ID">=4 AND "B"."ID"<=7)<br /> 4 - access("A"."ID"="B"."ID")<br /> filter("A"."ID">=4 AND "A"."ID"<=7)<br /><br />18 rows selected.<br /></pre></span><br />This execution plan looks good and really doesn't matter a lot because we got just a few records, but wait! Nine records is not the typical size of test1 table; now we will create more records:<br /><span style="font-size:75%;"><pre><br />SQL> delete from test1;<br /><br />9 rows deleted.<br /><br />SQL> declare<br />mynum number := 1;<br />begin<br />while mynum < 10000 loop<br /> insert into test1 values(mynum,to_char(mynum));<br /> mynum := mynum + 1;<br /> end loop;<br />end;<br />/<br /><br />PL/SQL procedure successfully completed.<br /><br />SQL> commit;<br /><br />Commit complete.<br /></pre></span><br />You might think that there is not a lot of difference having ten or ten thousand records in test1, but Oracle's optimizer does not think the same way and this time the execution plan is different:<br /><span style="font-size:75%;"><pre><br />SQL> EXPLAIN PLAN FOR<br /> 2 select a.ID, b.ID_CHAR from test1 a join test2 b on (a.ID=b.ID) where a.ID between 4000 and 7000;<br /><br />Explained.<br /><br />SQL> @$ORACLE_HOME/rdbms/admin/utlxpls.sql<br /><br />PLAN_TABLE_OUTPUT<br />----------------------------------------------------------------------------------------------------------------------------------<br />Plan hash value: 2418654178<br /><br />--------------------------------------------------------------------------------------------<br />| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |<br />--------------------------------------------------------------------------------------------<br />| 0 | SELECT STATEMENT | | 1 | 14 | 3 (0) | 00:00:01 |<br />| 1 | NESTED LOOPS | | | | | |<br />| 2 | NESTED LOOPS | | 1 | 14 | 3 (0) | 00:00:01 |<br />|* 3 | INDEX RANGE SCAN | SYS_C004857 | 1 | 3 | 1 (0) | 00:00:01 |<br />|* 4 | INDEX UNIQUE SCAN | SYS_C004858 | 1 | | 1 (0) | 00:00:01 |<br />| 5 | TABLE ACCESS BY INDEX ROWID| TEST2 | 1 | 11 | 2 (0) | 00:00:01 |<br />--------------------------------------------------------------------------------------------<br /><br />Predicate Information (identified by operation id):<br />---------------------------------------------------<br /><br /> 3 - access("A"."ID">=4000 AND "A"."ID"<=7000)<br /> 4 - access("A"."ID"="B"."ID")<br /> filter("B"."ID"<=7000 AND "B"."ID">=4000)<br /><br />19 rows selected.<br /></pre></span><br />You might think that retrieving four records is different of retrieving four thousand and that's right, but the optimizer choose the execution path based in wrong data; look the amount of rows Oracle thinks it will work to.<br /><br />You will see it clearly refreshing the test1 table statistics and getting the new execution path:<br /><span style="font-size:75%;"><pre><br />SQL> BEGIN DBMS_STATS.GATHER_TABLE_STATS( OWNNAME => 'SYS', TABNAME => 'TEST1'); END;<br /> 2 /<br /><br />PL/SQL procedure successfully completed.<br /><br />SQL> EXPLAIN PLAN FOR<br />select a.ID, b.ID_CHAR from test1 a join test2 b on (a.ID=b.ID) where a.ID between 4000 and 7000;<br /><br />Explained.<br /><br />SQL> @$ORACLE_HOME/rdbms/admin/utlxpls.sql<br /><br />PLAN_TABLE_OUTPUT<br />----------------------------------------------------------------------------------------------------------------------------------<br />Plan hash value: 3677731951<br /><br />--------------------------------------------------------------------------------------------<br />| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |<br />--------------------------------------------------------------------------------------------<br />| 0 | SELECT STATEMENT | | 3001 | 45015 | 17 (12) | 00:00:01 |<br />| 1 | NESTED LOOPS | | 3001 | 45015 | 17 (12) | 00:00:01 |<br />| 2 | TABLE ACCESS BY INDEX ROWID| TEST2 | 3002 | 33022 | 15 (0) | 00:00:01 |<br />|* 3 | INDEX RANGE SCAN | SYS_C004858 | 3002 | | 8 (0) | 00:00:01 |<br />|* 4 | INDEX UNIQUE SCAN | SYS_C004857 | 1 | 4 | 0 (0) | 00:00:01 |<br />--------------------------------------------------------------------------------------------<br /><br />Predicate Information (identified by operation id):<br />---------------------------------------------------<br /><br /> 3 - access("B"."ID">=4000 AND "B"."ID"<=7000)<br /> 4 - access("A"."ID"="B"."ID")<br /> filter("A"."ID">=4000 AND "A"."ID"<=7000)<br /><br />18 rows selected.<br /></pre></span><br />Can you see the problem? Having to deal with this kind of tables you may: use hints to force the optimal execution of the SQL sentence, refresh statistics every time you change a table like this, or lock the statistics when you have a typical table size or optimal execution path.<br /><br />We will consider that at this point the test1 table has a typical size and its statistics help the optimizer to chose an optimal execution plan, therefore we will lock its statistics this way:<br /><span style="font-size:75%;"><pre><br />SQL> exec DBMS_STATS.LOCK_TABLE_STATS ('MYUSER', 'TEST1');<br /><br />PL/SQL procedure successfully completed.<br /></pre></span><br />Now we will repopulate again the test1 table with nine records:<br /><span style="font-size:75%;"><pre><br />SQL> delete from TEST1;<br /><br />9999 rows deleted.<br /><br />SQL> declare<br />mynum number := 1;<br />begin<br />while mynum < 10 loop<br /> insert into test1 values(mynum,to_char(mynum));<br /> mynum := mynum + 1;<br /> end loop;<br />end;<br />/<br /><br />PL/SQL procedure successfully completed.<br /><br />SQL> commit;<br /><br />Commit complete.<br /><br />SQL> BEGIN DBMS_STATS.GATHER_TABLE_STATS( OWNNAME => 'MYUSER', TABNAME => 'TEST1'); END;<br /> 2 /<br />BEGIN DBMS_STATS.GATHER_TABLE_STATS( OWNNAME => 'MYUSER', TABNAME => 'TEST1'); END;<br />*<br />ERROR at line 1:<br />ORA-20005: object statistics are locked (stattype = ALL)<br />ORA-06512: at "SYS.DBMS_STATS", line 23829<br />ORA-06512: at "SYS.DBMS_STATS", line 23880<br />ORA-06512: at line 1<br /></pre></span><br />As long as the statistics of test1 table were locked you cannot gather new statistics unless you unlock the statistics of the table. Anyway, the point of doing this is to help the optimizer to chose a good execution plan:<br /><span style="font-size:75%;"><pre><br />SQL> EXPLAIN PLAN FOR<br />select a.ID, b.ID_CHAR from test1 a join test2 b on (a.ID=b.ID) where a.ID between 4 and 7;<br /><br />Explained.<br /><br />SQL> @$ORACLE_HOME/rdbms/admin/utlxpls.sql<br /><br />PLAN_TABLE_OUTPUT<br />----------------------------------------------------------------------------------------------------------------------------------<br />Plan hash value: 3677731951<br /><br />--------------------------------------------------------------------------------------------<br />| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |<br />--------------------------------------------------------------------------------------------<br />| 0 | SELECT STATEMENT | | 4 | 60 | 4 (0) | 00:00:01 |<br />| 1 | NESTED LOOPS | | 4 | 60 | 4 (0) | 00:00:01 |<br />| 2 | TABLE ACCESS BY INDEX ROWID| TEST2 | 5 | 55 | 4 (0) | 00:00:01 |<br />|* 3 | INDEX RANGE SCAN | SYS_C004858 | 5 | | 3 (0) | 00:00:01 |<br />|* 4 | INDEX UNIQUE SCAN | SYS_C004857 | 1 | 4 | 0 (0) | 00:00:01 |<br />--------------------------------------------------------------------------------------------<br /><br />Predicate Information (identified by operation id):<br />---------------------------------------------------<br /><br /> 3 - access("B"."ID">=4 AND "B"."ID"<=7)<br /> 4 - access("A"."ID"="B"."ID")<br /> filter("A"."ID"<=7 AND "A"."ID">=4)<br /><br />18 rows selected.<br /><br />SQL> delete from TEST1;<br /><br />9 rows deleted.<br /><br />SQL> declare<br />mynum number := 1;<br />begin<br />while mynum < 10000 loop<br /> insert into test1 values(mynum,to_char(mynum));<br /> mynum := mynum + 1;<br /> end loop;<br />end;<br />/<br /><br />PL/SQL procedure successfully completed.<br /><br />SQL> commit;<br /><br />Commit complete.<br /><br />SQL> EXPLAIN PLAN FOR<br />select a.ID, b.ID_CHAR from test1 a join test2 b on (a.ID=b.ID) where a.ID between 4000 and 7000;<br /><br />Explained.<br /><br />SQL> @$ORACLE_HOME/rdbms/admin/utlxpls.sql<br /><br />PLAN_TABLE_OUTPUT<br />----------------------------------------------------------------------------------------------------------------------------------<br />Plan hash value: 3677731951<br /><br />--------------------------------------------------------------------------------------------<br />| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |<br />--------------------------------------------------------------------------------------------<br />| 0 | SELECT STATEMENT | | 3001 | 45015 | 17 (12) | 00:00:01 |<br />| 1 | NESTED LOOPS | | 3001 | 45015 | 17 (12) | 00:00:01 |<br />| 2 | TABLE ACCESS BY INDEX ROWID| TEST2 | 3002 | 33022 | 15 (0) | 00:00:01 |<br />|* 3 | INDEX RANGE SCAN | SYS_C004858 | 3002 | | 8 (0) | 00:00:01 |<br />|* 4 | INDEX UNIQUE SCAN | SYS_C004857 | 1 | 4 | 0 (0) | 00:00:01 |<br />--------------------------------------------------------------------------------------------<br /><br />Predicate Information (identified by operation id):<br />---------------------------------------------------<br /><br /> 3 - access("B"."ID">=4000 AND "B"."ID"<=7000)<br /> 4 - access("A"."ID"="B"."ID")<br /> filter("A"."ID">=4000 AND "A"."ID"<=7000)<br /><br />18 rows selected.<br /></pre></span><br />As you can see, this time the execution plan was the same for ten or ten thousand records in test1 table and the number of rows managed in the execution plans were more or less accurate.<br /><br />By the way, if you want to unlock the statistics of a table you can do it this way:<br /><span style="font-size:75%;"><pre><br />SQL> exec DBMS_STATS.UNLOCK_TABLE_STATS ('MYUSER', 'TEST1');<br /><br />PL/SQL procedure successfully completed.<br /><br />SQL> BEGIN DBMS_STATS.GATHER_TABLE_STATS( OWNNAME => 'MYUSER', TABNAME => 'TEST1'); END;<br /> 2 /<br /><br />PL/SQL procedure successfully completed.<br /></pre></span><br /><br />More information:<br /><br /><a href="http://docs.oracle.com/cd/B19306_01/appdev.102/b14258/d_stats.htm#i1045149">Locking or Unlocking Statistics</a><br /><a href="https://support.oracle.com/CSP/main/article?cmd=show&type=NOT&id=283890.1">Preserving Statistics using DBMS_STATS.LOCK_TABLE_STATS</a>Cronoshttp://www.blogger.com/profile/15427827507143505689noreply@blogger.com0tag:blogger.com,1999:blog-4232775939402125807.post-30134837373829179962012-01-30T07:34:00.000-08:002012-01-30T08:28:32.851-08:00Oracle and OCFS2 not releasing space usedSometimes you have to drop a tablespace to recover storage space and use it to create other tablespace, like when you have to resize an undo tablespace. And usually is not a big issue unless you have <a href="http://oss.oracle.com/projects/ocfs2/">OCFS2</a> file systems:<br /><span style="font-size:75%;"><pre><br />oracle@myserver$ df -m|egrep '(Filesystem)|(/mydb/undo)'<br />Filesystem 1M-blocks Used Available Use% Mounted on<br /> 24576 24572 4 100% /mydb/undo<br /><br />oracle@myserver$ sqlplus '/ as sysdba'<br /><br />SQL*Plus: Release 10.2.0.4.0 - Production on Thu Jan 26 22:57:35 2012<br /><br />Copyright (c) 1982, 2007, Oracle. All Rights Reserved.<br /><br /><br />Connected to:<br />Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production<br />With the Partitioning, Real Application Clusters, OLAP, Data Mining<br />and Real Application Testing options<br /><br />SQL> drop tablespace myundo including contents and datafiles;<br /><br />Tablespace dropped.<br /><br />SQL> create undo tablespace mynewundo datafile '/mydb/undo/undo.dbf' size 10g;<br />create undo tablespace mynewundo datafile '/mydb/undo/undo.dbf' size 10g<br />*<br />ERROR at line 1:<br />ORA-01119: error in creating database file '/mydb/undo/undo.dbf'<br />ORA-27044: unable to write the header block of file<br />Linux-ia64 Error: 28: No space left on device<br />Additional information: 4<br /><br />SQL> quit<br />Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production<br />With the Partitioning, Real Application Clusters, OLAP, Data Mining<br />and Real Application Testing options<br /><br />oracle@myserver$ df -m|egrep '(Filesystem)|(/mydb/undo)'<br />Filesystem 1M-blocks Used Available Use% Mounted on<br /> 24576 24572 4 100% /mydb/undo<br /><br />oracle@myserver$ ls -la /mydb/undo<br />total 16<br />drwxr-xr-x 4 root root 4096 Jan 20 2010 .<br />drwxr-xr-x 4 root root 4096 Dec 17 2009 ..<br />drwxr-xr-x 2 root root 4096 Dec 17 2009 lost+found<br /></pre></span><br />As you might notice there is no space recovered from dropping that undo tablespace, and since we need to create a new undo tablespace in the same file system we have to do something.<br /><br />I don't have a lot of dabatases placed in OCFS2 file systems so I didn't care about finding the root cause of this problem, and since I had this database with no users connected I just created a small undo tablespace somewhere else, shut down the instance, and after doing it I got that space back:<br /><span style="font-size:75%;"><pre><br />oracle@myserver$ df -m|egrep '(Filesystem)|(/mydb/undo)'<br />Filesystem 1M-blocks Used Available Use% Mounted on<br /> 24576 359 24217 2% /mydb/undo<br /><br />oracle@myserver$ ls -la /mydb/undo<br />total 16<br />drwxr-xr-x 4 root root 4096 Jan 20 2010 .<br />drwxr-xr-x 4 root root 4096 Dec 17 2009 ..<br />drwxr-xr-x 2 root root 4096 Dec 17 2009 lost+found<br /><br />oracle@myserver$ mount|grep /mydb/undo<br />/dev/mapper/myvg-undo on /mydb/undo type ocfs2 (rw,_netdev,datavolume,nointr,heartbeat=local)<br /><br />oracle@myserver$ uname -a<br />Linux myserver.localdomain 2.6.9-67.EL #1 SMP Wed Nov 7 13:43:35 EST 2007 ia64 ia64 ia64 GNU/Linux<br /><br />oracle@myserver$ /sbin/lsmod|grep ocf<br />ocfs2 744096 26 <br />debugfs 27340 2 ocfs2<br />ocfs2_dlmfs 53416 1 <br />ocfs2_dlm 424192 2 ocfs2,ocfs2_dlmfs<br />ocfs2_nodemanager 316791 32 ocfs2,ocfs2_dlmfs,ocfs2_dlm<br />configfs 65468 2 ocfs2_nodemanager<br />jbd 149272 2 ocfs2,ext3<br /><br />oracle@myserver$ /sbin/modinfo ocfs2<br />filename: /lib/modules/2.6.9-67.EL/kernel/fs/ocfs2/ocfs2.ko<br />license: GPL<br />author: Oracle<br />version: 1.2.9 1B43458FD47258934A48F1C<br />description: OCFS2 1.2.9 Mon May 19 14:04:34 PDT 2008 (build a693806cb619dd7f225004092b675ede)<br />depends: ocfs2_nodemanager,ocfs2_dlm,jbd,debugfs<br />vermagic: 2.6.9-67.EL SMP ia64gcc-3.4<br /></pre></span><br />I know this is just a workaround and not a good one, but it works fine and you can move on the next thing.Cronoshttp://www.blogger.com/profile/15427827507143505689noreply@blogger.com0tag:blogger.com,1999:blog-4232775939402125807.post-3171134421287011932012-01-25T10:37:00.000-08:002012-01-25T13:27:08.760-08:00How to configure a VPN PPTP connectionIf you need to open a VPN PPTP connection from a Linux computer, you can use the pptp client to configure a PPP connection and launch the pppd daemon to make the connection. You need to know at least the VPN server, an authorized user to make the connection and its password, and we will work with the root account for practical purposes but it's not mandatory.<br /><br />First, if you don't have the pptp client you have to install it or configure the PPP connection by hand or by other means, in Debian Squeeze you could install the pptp-linux package.<br /><br />Next, this is the easy way to configure your VPN PPTP connection:<br /><span style="font-size:75%;"><pre><br />pptpsetup --create MYVPN --server myvpnserver --username myvpnuser --password mypassword<br /></pre></span><br />You have to substitute myvpnserver, myvpnuser and mypassword with the VPN server, the user and password respectively; MYVPN is the name of the PPP configuration you will create and is chosen by you.<br /><br />After this you will have a new configuration file in /etc/ppp/peers and also an extra line in /etc/ppp/chap-secrets:<br /><span style="font-size:75%;"><pre><br />olimpo:~# cat /etc/ppp/peers/MYVPN<br /># written by pptpsetup<br />pty "pptp myvpnserver --nolaunchpppd"<br />lock<br />noauth<br />nobsdcomp<br />nodeflate<br />name myvpnuser<br />remotename MYVPN<br />ipparam MYVPN<br /><br />olimpo:~# cat /etc/ppp/chap-secrets<br /># Secrets for authentication using CHAP<br /># client server secret IP addresses<br /><br /># added by pptpsetup for MYVPN<br />myvpnuser MYVPN "mypassword" *<br /></pre></span><br />If you want to configure the pppd daemon by hand, you just have to create a file in /etc/ppp/peers and add the authentication information in /etc/ppp/chap-secrets and it must be the same.<br /><br />At this time you can open the PPP connection launching the pppd daemon:<br /><span style="font-size:75%;"><pre><br />olimpo:~# pppd call MYVPN updetach<br />Using interface ppp0<br />Connect: ppp0 <--> /dev/pts/3<br />CHAP authentication succeeded<br />local IP address 10.5.15.222<br />remote IP address 10.5.15.127<br /><br />olimpo:~# tail --lines=16 /var/log/syslog<br />Jan 25 13:04:12 olimpo pppd[29036]: pppd 2.4.5 started by root, uid 0<br />Jan 25 13:04:12 olimpo pppd[29036]: Using interface ppp0<br />Jan 25 13:04:12 olimpo pppd[29036]: Connect: ppp0 <--> /dev/pts/3<br />Jan 25 13:04:13 olimpo pptp[29037]: anon log[main:pptp.c:314]: The synchronous pptp option is NOT activated <br />Jan 25 13:04:13 olimpo pptp[29041]: anon log[ctrlp_rep:pptp_ctrl.c:251]: Sent control packet type is 1 'Start-Control-Connection-Request' <br />Jan 25 13:04:13 olimpo pptp[29041]: anon log[ctrlp_disp:pptp_ctrl.c:739]: Received Start Control Connection Reply<br />Jan 25 13:04:13 olimpo pptp[29041]: anon log[ctrlp_disp:pptp_ctrl.c:773]: Client connection established.<br />Jan 25 13:04:14 olimpo NetworkManager[1919]: SCPlugin-Ifupdown: devices added (path: /sys/devices/virtual/net/ppp0, iface: ppp0)<br />Jan 25 13:04:14 olimpo NetworkManager[1919]: SCPlugin-Ifupdown: device added (path: /sys/devices/virtual/net/ppp0, iface: ppp0): no ifupdown configuration found.<br />Jan 25 13:04:14 olimpo pptp[29041]: anon log[ctrlp_rep:pptp_ctrl.c:251]: Sent control packet type is 7 'Outgoing-Call-Request' <br />Jan 25 13:04:14 olimpo pptp[29041]: anon log[ctrlp_disp:pptp_ctrl.c:858]: Received Outgoing Call Reply.<br />Jan 25 13:04:14 olimpo pptp[29041]: anon log[ctrlp_disp:pptp_ctrl.c:897]: Outgoing call established (call ID 0, peer's call ID 8918). <br />Jan 25 13:04:14 olimpo modem-manager: (net/ppp0): could not get port's parent device<br />Jan 25 13:04:17 olimpo pppd[29036]: CHAP authentication succeeded<br />Jan 25 13:04:17 olimpo pppd[29036]: local IP address 10.5.15.222<br />Jan 25 13:04:17 olimpo pppd[29036]: remote IP address 10.5.15.127<br /><br />olimpo:~# ifconfig ppp0<br />ppp0 Link encap:Point-to-Point Protocol <br /> inet addr:10.5.15.222 P-t-P:10.5.15.127 Mask:255.255.255.255<br /> UP POINTOPOINT RUNNING NOARP MULTICAST MTU:1500 Metric:1<br /> RX packets:7 errors:0 dropped:0 overruns:0 frame:0<br /> TX packets:7 errors:0 dropped:0 overruns:0 carrier:0<br /> collisions:0 txqueuelen:3 <br /> RX bytes:99 (99.0 B) TX bytes:93 (93.0 B)<br /><br />olimpo:~# route<br />Kernel IP routing table<br />Destination Gateway Genmask Flags Metric Ref Use Iface<br />10.5.15.127 * 255.255.255.255 UH 0 0 0 ppp0<br />static.customer 10.123.46.1 255.255.255.255 UGH 0 0 0 br0<br />10.123.46.0 * 255.255.255.0 U 0 0 0 br0<br />default 10.123.46.1 0.0.0.0 UG 0 0 0 br0<br /></pre></span><br />That's it, you have a VPN connection open to you VPN server! Or it's almost done, because we have a routing problem.<br /><span style="font-size:75%;"><pre><br />olimpo:~# ping 10.5.15.123<br />PING 10.5.15.123 (10.5.15.123) 56(84) bytes of data.<br />^C<br />--- 10.5.15.123 ping statistics ---<br />7 packets transmitted, 0 received, 100% packet loss, time 6047ms<br /><br />olimpo:~# traceroute 10.5.15.123<br />traceroute to 10.5.15.123 (10.5.15.123), 30 hops max, 60 byte packets<br /> 1 10.123.81.4 (10.123.81.4) 0.407 ms 0.429 ms 0.492 ms<br /> 2 10.123.148.1 (10.123.148.1) 0.313 ms 0.352 ms 0.345 ms<br /> 3 * * *<br /> 4 * * *<br /> 5 * * *<br /> 6 *^C<br /></pre></span><br />As you might notice in the route information, there is no route to the VPN network from your Linux computer. You could append <span style="font-weight:bold;">defaultroute</span> to the pppd command, but this way you will have connectivity ONLY to the VPN network; to put it simple: is like if you were attached directly to the other network. If you had internet access and there is no way to access internet in the VPN network then you'll have no internet access, or if you had access to other networks after using pppd with defaultroute you will no longer have access to them.<br /><br />And in fact that's the way a VPN is supposed to work. For example, if you got a trojan in your computer and that program is tailored specifically for your organization, and you connect your computer to the VPN network and still have access to the internet, then that trojan might steal information and send it to someone else easily. Or you might thing that leaving your VPN connection open and your computer accessible from the internet would be handy to share your VPN connection; this way you're creating a security hole in the VPN network so be careful and do it if you really need to.<br /><br />Therefore, if you need to keep connectivity to other networks and don't want to append defaultroute to the pppd command, you have to create the route by hand. In this case, we want to access the VPN network 10.5.15.0/24, therefore we will issue this route command:<br /><span style="font-size:75%;"><pre><br />olimpo:~# route add -net 10.5.15.0 netmask 255.255.255.0 dev ppp0<br /></pre></span><br />As you can see in the route and traceroute information, this time you can reach the example server (10.5.15.123) through your PPP gateway (10.5.15.127):<br /><span style="font-size:75%;"><pre><br />olimpo:~# route<br />Kernel IP routing table<br />Destination Gateway Genmask Flags Metric Ref Use Iface<br />10.5.15.127 * 255.255.255.255 UH 0 0 0 ppp0<br />static.customer 10.123.46.1 255.255.255.255 UGH 0 0 0 br0<br />10.123.46.0 * 255.255.255.0 U 0 0 0 br0<br />10.5.15.0 * 255.255.255.0 U 0 0 0 ppp0<br />default 10.123.46.1 0.0.0.0 UG 0 0 0 br0<br /><br />olimpo:~# traceroute 10.5.15.123<br />traceroute to 10.5.15.123 (10.5.15.123), 30 hops max, 60 byte packets<br /> 1 10.5.15.127 (10.5.15.127) 163.061 ms 163.027 ms 163.010 ms<br /> 2 10.5.15.123 (10.5.15.123) 174.981 ms 174.967 ms 174.954 ms<br /></pre></span><br />Finally, in order to end your PPP connection you have to end the pppd daemon (gracefully); you might do this getting the pppd daemon PID and issuing a kill command:<br /><span style="font-size:75%;"><pre><br />olimpo:~# ps ax|grep pppd<br />10094 pts/1 S+ 0:00 grep pppd<br />29037 pts/1 S 0:00 pptp myvpnserver --nolaunchpppd<br />29041 pts/1 S 0:00 pptp myvpnserver --nolaunchpppd<br />29044 pts/1 S 0:00 pppd call MYVPN updetach<br /><br />olimpo:~# cat /var/run/ppp0.pid <br />29044<br /><br />olimpo:~/bin# kill -TERM 29044<br /><br />olimpo:~/bin# tail --lines=11 /var/log/syslog<br />Jan 25 15:06:43 olimpo pppd[29044]: Terminating on signal 15<br />Jan 25 15:06:43 olimpo pppd[29044]: Modem hangup<br />Jan 25 15:06:43 olimpo pppd[29044]: Connect time 122.5 minutes.<br />Jan 25 15:06:43 olimpo pppd[29044]: Sent 1140 bytes, received 1056 bytes.<br />Jan 25 15:06:43 olimpo pptp[29041]: anon log[callmgr_main:pptp_callmgr.c:258]: Closing connection (shutdown)<br />Jan 25 15:06:43 olimpo pptp[29041]: anon log[ctrlp_rep:pptp_ctrl.c:251]: Sent control packet type is 12 'Call-Clear-Request' <br />Jan 25 15:06:43 olimpo pptp[29041]: anon log[call_callback:pptp_callmgr.c:79]: Closing connection (call state)<br />Jan 25 15:06:44 olimpo pppd[29044]: Connection terminated.<br />Jan 25 15:06:44 olimpo avahi-daemon[1488]: Withdrawing workstation service for ppp0.<br />Jan 25 15:06:44 olimpo NetworkManager[1919]: SCPlugin-Ifupdown: devices removed (path: /sys/devices/virtual/net/ppp0, iface: ppp0)<br />Jan 25 15:06:49 olimpo pppd[29044]: Exit.<br /><br />olimpo:~# ps ax|grep pppd<br />11587 pts/1 S+ 0:00 grep pppd<br /></pre></span><br />More information:<br /><br /><a href="http://tldp.org/LDP/nag2/x-087-2-ppp.html">The Point-to-Point Protocol</a>Cronoshttp://www.blogger.com/profile/15427827507143505689noreply@blogger.com0tag:blogger.com,1999:blog-4232775939402125807.post-34954687190584407982011-12-28T10:50:00.000-08:002011-12-28T13:08:25.545-08:00Oracle datafiles and Veritas Quick I/O filesIf you want to have a maximum performance in datafile access you should use raw devices for datafiles, but most modern operating systems manage file access very well, you can use ASM for Oracle database storage management, and managing raw devices requires a skilled sysadmin and is time-consuming, therefore almost all the time the extra speed gained with raw devices is not worth the extra skills and effort required to maintain raw devices.<br /><br />But if you have an Oracle database in a Solaris server with Veritas VxFS filesystems with Quick I/O included (that is, licensed), then you can use Quick I/O access with regular files; this way you can have and manage regular files but at the same time you can access them like raw devices. An unprivileged user can create Quick I/O links and is very easy to do; first you have to create your datafile at operating system level with qiomkfile:<br /><span style="font-size:75%;"><pre><br />oracle@myserver$ qiomkfile -h 32k -s 16000M /myfs/mydatabase/mydatafile.dbf<br /><br />oracle@myserver$ ls -la /myfs/mydatabase<br />total 32768066<br />drwxr-xr-x 3 oracle dba 96 Sep 24 11:32 .<br />drwxr-xr-x 61 oracle dba 1024 Sep 23 16:57 ..<br />-rw-r--r-- 1 oracle dba 16777248768 Sep 24 11:32 .mydatafile.dbf<br />lrwxrwxrwx 1 oracle dba 26 Sep 24 11:32 mydatafile.dbf -> .mydatafile.dbf::cdev:vxfs:<br />drwxr-xr-x 2 oracle dba 96 Sep 23 16:50 lost+found<br /></pre></span><br />The secret is that .mydatafile.dbf is a regular (but single contiguous extent) file, and <br />mydatafile.dbf is a link to .mydatafile.dbf::cdev:vxfs: but the operating system recognizes the ::cdev:vxfs: suffix as Quick I/O and access .mydatafile.dbf like a raw device; that's why the .mydatafile.dbf::cdev:vxfs: file must not exist. The qiomkfile parameters are simple; -h is the extra space added to the file to use it as an Oracle datafile (in this case 16,000 megabytes plus 32 kilobytes) because Oracle adds one database block (DB_BLOCK_SIZE parameter) to each datafile created, and -s is the required size of the Oracle datafile that should match the DATAFILE ... SIZE clause.<br /><br /><br />This was the difficult part; in order to create datafiles that use Quick I/O features you just have to use the REUSE clause:<br /><span style="font-size:75%;"><pre><br />SQL> CREATE TABLESPACE MYTBL DATAFILE '/myfs/mydatabase/mydatafile.dbf' SIZE 16000M REUSE;<br /><br />Tablespace created.<br /><br />SQL> ALTER TABLESPACE MYTBL ADD DATAFILE '/myfs/mydatabase/mydatafile2.dbf' SIZE 16000M REUSE;<br /><br />Tablespace altered.<br /></pre></span><br />And talking about dropping tablespaces with Quick I/O datafiles, don't use the INCLUDING CONTENTS AND DATAFILES clause or you will get an error, just drop the tablespace and erase the files with rm at operating system level.<br /><br />More information:<br /><br /><a href="http://docs.oracle.com/cd/E19668-01/875-3895-10/875-3895-10.pdf">Veritas Storage Foundation for Oracle Administrator’s Guide (chapter 4)</a><br /><a href="http://docs.oracle.com/cd/E19668-01/">Veritas Storage Foundation 5.0 Software</a>Cronoshttp://www.blogger.com/profile/15427827507143505689noreply@blogger.com0tag:blogger.com,1999:blog-4232775939402125807.post-84542417313553378682011-12-20T08:12:00.000-08:002011-12-23T14:43:23.183-08:00About Oracle OCA and OCP certificationsSome time ago I got first the Oracle Certified Associate certification and later the Oracle Certified Professional certification, both on Oracle Database 10g. Since then there has been some people asking me about the procedure I followed and also the study material I used to prepare this tests, and it's until today I decided to write about it.<br /><br />First of all, the tests are multiple-choice question exams that are difficult enought to not let to pass anybody that can pay the exam fees (I think low by the way), and not being a native English speaker adds a lot more complexity to the tests. If you have experience managing Oracle databases AND have a good and fresh knowledge of Oracle Database concepts you will have a good chance to pass the tests; in this kind of tests it is very important to have rock-solid concepts knowledge because all answers looks equal and the subtle differences are the key to choose the correct answer, not to mention the limited amount of time to answer the whole test. Oh, did I stress having good English skills?<br /><br />On the other hand, if you're on budget you will find that a thorough study of the official (and free) Oracle documentation may be enough to pass this tests, and even the Express Edition (free) is enough to practice in your own computer. But if you can spare some money, I highly recommend to buy the SelfTest's <a href="http://www.selftestsoftware.com/certtestprep/practice-test/oracle/1z0-051.kap">Oracle Database 11g: SQL Fundamentals I</a>, <a href="http://www.selftestsoftware.com/certtestprep/practice-test/oracle/1z0-042.kap">Oracle Database 10g: Administration I</a> and <a href="http://www.selftestsoftware.com/certtestprep/practice-test/oracle/1z0-043.kap">Oracle Database 10g: Administration II</a> practice tests; the price of this software is close to the price of each certification test but it might save you frustration and time, not to mention money, therefore is up to you to prepare for this tests just with free documentation (or expensive courses) or pay the extra money to practice tests with this software. If you buy this software be sure to score at least 90% a few times and you will have a good opportunity to pass the real tests.<br /><br />But if you're a cheapskate like me you would prefer to study the official Oracle documentation. To be honest, at this time I can't remember exactly what I had to study for each certification test, but if you are willing to hold the OCP certification simply study all the following guides and you will be safe.<br /><br />For the SQL test (Oracle Database 11g: SQL Fundamentals I, 1Z0-051):<br /><br /><a href="http://docs.oracle.com/cd/B19306_01/server.102/b14196/toc.htm">Oracle Database 2 Day DBA</a><br /><a href="http://docs.oracle.com/cd/B25329_01/doc/appdev.102/b25108/toc.htm">Oracle Database Express Edition 2 Day Developer Guide</a><br /><br />For the OCA certification (Oracle Database 10g: Administration I, 1Z0-042):<br /><br /><a href="http://docs.oracle.com/cd/B19306_01/server.102/b14231/toc.htm">Oracle Database Administrator's Guide (chapters 1 to 20)</a><br /><a href="http://docs.oracle.com/cd/B19306_01/backup.102/b14192/toc.htm">Oracle Database Backup and Recovery Basics</a><br /><br />For the OCP certification (Oracle Database 10g: Administration II, 1Z0-043):<br /><br /><a href="http://docs.oracle.com/cd/B19306_01/server.102/b14231/toc.htm">Oracle Database Administrator's Guide (chapters 21 to 28)</a><br /><a href="http://docs.oracle.com/cd/B19306_01/backup.102/b14191/toc.htm">Oracle Database Backup and Recovery Advanced User's Guide</a><br /><br />The problem of studying Oracle documentation is that it is not intended to be a self-learning guide, therefore you have to figure out exercises to practice what you read; just reading this documentation without practice nor experience will not be enough to pass the tests. But in my personal experience I think these guides are so well written that if you manage to learn them you will not have any need to buy (or read) other books; in fact I cannot recommend books because I didn't read anything besides the Oracle guides to prepare for the certification tests.<br /><br />Another problem is that these guides are good for preparing the OCP and OCA tests, but for the SQL Fundamentals test I couldn't find one Oracle guide that covered everything; in fact I was very close to fail that test but the SelfTest software saved me. On the other hand, the OCA test was difficult to me but it was less than the SQL test, and the OCP test was the easiest one to me. Therefore, if you're more a DBA than a developer it would be better to study SQL sentences and functions a lot.<br /><br />Now let's talk about the <a href="http://education.oracle.com/pls/web_prod-plq-dad/db_pages.getpage?page_id=143#3">certification track</a> and prerequisites. If you're pursuing the <a href="http://education.oracle.com/pls/web_prod-plq-dad/db_pages.getpage?page_id=151">OCP or OCA certifications</a> you first have to pass one of the SQL tests, more likely the <a href="http://education.oracle.com/pls/web_prod-plq-dad/db_pages.getpage?page_id=41&p_exam_id=1Z0_051">Oracle Database 11g: SQL Fundamentals I</a>. You can take the OCA or OCP tests first but you won't get any certification unless you clear all the certification prerequisites, therefore first try to pass the SQL test.<br /><br />If you manage to pass the SQL test then you can take the <a href="http://education.oracle.com/pls/web_prod-plq-dad/db_pages.getpage?page_id=41&p_exam_id=1Z0_042">Oracle Database 10g: Administration I</a> test, and if you pass you will hold the OCA certification. Easy, isn't it?<br /><br />But this is just the beginning, the goal should be the OCP certification but there are two prerequisites not so easy to cover: <a href="http://education.oracle.com/pls/web_prod-plq-dad/db_pages.getpage?page_id=244#3">take an approved course</a> and <a href="http://education.oracle.com/pls/web_prod-plq-dad/db_pages.getpage?page_id=99">fill the Course Submission Form</a>. The difficult part of taking an approved course is just paying for it, and if your course diploma has a course serial number and you took that course recently you will have no problem at all filling the Course Submission Form.<br /><br />At of this writing, the submission of the form and the scheduling of tests should be done at the <a href="http://www.pearsonvue.com/oracle/">Pearson Vue site</a>, and unfortunately I cannot remember what I have done exactly but you would get help at the moment of contacting a testing center.<br /><br />Finally, if you cleared all the prerequisites and manage to pass the <a href="http://education.oracle.com/pls/web_prod-plq-dad/db_pages.getpage?page_id=41&p_exam_id=1Z0_043">Oracle Database 10g: Administration II</a> test, you will become an Oracle Certified Professional and get a nice plastic card just in case you are willing to boast about it.<br /><br />There are people that put in question the value of this kind of certifications, but the truth is that I learned a lot studying for the certification tests, and because these certifications are valid worldwide they are a good point of reference for recruiters looking for talent. Therefore, if you have already took an Oracle approved course then put a little effort and money and go for the OCA and OCP certifications, I think they have good value for the money.Cronoshttp://www.blogger.com/profile/15427827507143505689noreply@blogger.com2tag:blogger.com,1999:blog-4232775939402125807.post-67914345797269860162011-12-09T09:43:00.000-08:002011-12-14T09:18:00.959-08:00Patching an Oracle RAC databasePatching an Oracle RAC database is a little different from patching a single instance database because you have to patch at least two installations (CRS, database and ASM if exist), patch it in certain order, and shutdown and startup CRS services not existing in single instance installations; you can even apply patches with or without shutting down the entire RAC database, and apply them in each node or propagate patches to all nodes executing patches in just one node.<br /><br />In this example we will patch a two-node Oracle 10g RAC database (10.2.0.1) with ASM, sharing the database and ASM the same home, first to upgrade it to patch 10.2.0.5.0 and finally to PSU 10.2.0.5.4, shutting down the whole RAC database and applying the patches to both nodes from one of them. Usual recommendations apply: read patch's documentation and backup your database and Oracle home before patching.<br /><br />First, we will download patches 8202632 (patch 10.2.0.5.0), 12419392 (PSU 10.2.0.5.4), and 6880880 (Opatch 10.2.0.5.1), and uncompress it in one node at least:<br /><span style="font-size:75%;"><pre><br />oracle@myracn1$ ls -la<br />total 2659400<br />drwxr-xr-x 4 oracle dba 512 Nov 17 18:20 .<br />drwxr-xr-x 23 oracle dba 1024 Nov 17 13:03 ..<br />drwxr-xr-x 5 oracle dba 512 Jun 16 00:42 12419392<br />drwxr-xr-x 6 oracle dba 512 Nov 17 15:16 Disk1<br />-rw-r--r-- 1 oracle dba 11319286 Nov 15 21:33 p12419392_10205_SOLARIS64.zip<br />-rw-r--r-- 1 oracle dba 28674793 Nov 17 18:17 p6880880_102000_SOLARIS64.zip<br />-rw-r--r-- 1 oracle dba 1320728471 Nov 15 21:36 p8202632_10205_SOLARIS64.zip<br />-rwxr-xr-x 1 oracle dba 175026 May 19 2010 README.html<br /></pre></span><br />Next, shutdown all RAC instances and services:<br /><span style="font-size:75%;"><pre><br />oracle@myracn1$ srvctl stop database -d myrcdb<br />oracle@myracn1$ srvctl stop asm -n myracn1<br />oracle@myracn1$ srvctl stop asm -n myracn2<br />oracle@myracn1$ srvctl stop listener -n myracn1<br />oracle@myracn1$ srvctl stop listener -n myracn2<br />oracle@myracn1$ srvctl stop nodeapps -n myracn1<br />oracle@myracn1$ srvctl stop nodeapps -n myracn2<br /><br />oracle@myracn1$ cd /opt/oracle/crs/bin<br /><br />oracle@myracn1$ ./crs_stat -t<br />Name Type Target State Host <br />------------------------------------------------------------<br />ora....SM1.asm application ONLINE OFFLINE <br />ora....T2.lsnr application OFFLINE OFFLINE <br />ora.myracn1.gsd application OFFLINE OFFLINE <br />ora.myracn1.ons application OFFLINE OFFLINE <br />ora.myracn1.vip application OFFLINE OFFLINE <br />ora....SM2.asm application ONLINE OFFLINE <br />ora....T4.lsnr application OFFLINE OFFLINE <br />ora.myracn2.gsd application OFFLINE OFFLINE <br />ora.myracn2.ons application OFFLINE OFFLINE <br />ora.myracn2.vip application OFFLINE OFFLINE <br />ora.myrcdb.db application ONLINE OFFLINE <br />ora....l1.inst application ONLINE OFFLINE <br />ora....l2.inst application ONLINE OFFLINE <br /></pre></span><br />Next we will launch OUI to patch first the CRS software. It's very easy to follow the instructions of Oracle Universal Installer, you just have to select the CRS home when apply and be sure to select all the RAC nodes to install this patch; everything else is pretty straightforward.<br /><span style="font-size:75%;"><pre><br />oracle@myracn1$ cd /opt/oracle/parches/Disk1<br /><br />oracle@myracn1$ ./runInstaller<br />Starting Oracle Universal Installer...<br /><br />Checking installer requirements...<br /><br />Checking operating system version: must be 5.8, 5.9 or 5.10. Actual 5.10<br /> Passed<br /><br />Checking Temp space: must be greater than 250 MB. Actual 13017 MB Passed<br />Checking swap space: must be greater than 500 MB. Actual 15218 MB Passed<br />Checking monitor: must be configured to display at least 256 colors. Actual 16777216 Passed<br /><br />All installer requirements met.<br /><br />Preparing to launch Oracle Universal Installer from /tmp/OraInstall2011-11-17_01-45-53PM. Please wait ...<br />oracle@myracn1$ Oracle Universal Installer, Version 10.2.0.5.0 Production<br />Copyright (C) 1999, 2010, Oracle. All rights reserved.<br /><br />Warning: Cannot convert string "-monotype-arial-regular-r-normal--*-140-*-*-p-*-iso8859-1" to type FontStruct<br /></pre></span><br />As the last step of this patch application and before leaving OUI, we have to run the <span style="font-weight:bold;">root102.sh</span> script as root after stopping CRS services, <span style="font-weight:bold;">in all RAC nodes</span>:<br /><span style="font-size:75%;"><pre><br />oracle@myracn1$ su -<br />Password: <br /><br />root@myracn1# /opt/oracle/crs/bin/crsctl stop crs<br />Stopping resources.<br />Successfully stopped CRS resources <br />Stopping CSSD.<br />Shutting down CSS daemon.<br />Shutdown request successfully issued.<br /><br />root@myracn1# /opt/oracle/crs/install/root102.sh<br />Creating pre-patch directory for saving pre-patch clusterware files<br />Completed patching clusterware files to /opt/oracle/crs<br />Relinking some shared libraries.<br />ar: writing /opt/oracle/crs/lib/libn10.a<br />ar: writing /opt/oracle/crs/lib32/libn10.a<br />ar: writing /opt/oracle/crs/lib/libn10.a<br />Relinking of patched files is complete.<br />WARNING: directory '/opt/oracle' is not owned by root<br />Preparing to recopy patched init and RC scripts.<br />Recopying init and RC scripts.<br />Startup will be queued to init within 30 seconds.<br />Starting up the CRS daemons.<br />Waiting for the patched CRS daemons to start.<br /> This may take a while on some systems.<br />.<br />10205 patch successfully applied.<br />clscfg: EXISTING configuration version 3 detected.<br />clscfg: version 3 is 10G Release 2.<br />Successfully deleted 1 values from OCR.<br />Successfully deleted 1 keys from OCR.<br />Successfully accumulated necessary OCR keys.<br />Using ports: CSS=49895 CRS=49896 EVMC=49898 and EVMR=49897.<br />node <nodenumber>: <nodename> <private interconnect name> <hostname><br />node 1: myracn1 myracn1-priv myracn1<br />Creating OCR keys for user 'root', privgrp 'root'..<br />Operation successful.<br />clscfg -upgrade completed successfully<br />Creating '/opt/oracle/crs/install/paramfile.crs' with data used for CRS configuration<br />Setting CRS configuration values in /opt/oracle/crs/install/paramfile.crs<br /><br />root@myracn1# exit<br /></pre></span><br />At this point we have the CRS software patched in all nodes, therefore we will patch the ASM and database homes next. In this example ASM and the database has the same Oracle home, but otherwise you have to patch first the ASM home and next the database home.<br /><br />Just in case you didn't notice yet, the 10.2.0.5 patch is the same patch for CRS, ASM and databases, therefore we will apply the same patch almost the same way as before but this time we will select the ASM and database home, of course after shutting down all RAC database instances and services:<br /><span style="font-size:75%;"><pre><br />oracle@myracn1$ srvctl stop database -d myrcdb<br />oracle@myracn1$ srvctl stop asm -n myracn1<br />oracle@myracn1$ srvctl stop asm -n myracn2<br />oracle@myracn1$ srvctl stop listener -n myracn1<br />oracle@myracn1$ srvctl stop listener -n myracn2<br />oracle@myracn1$ srvctl stop nodeapps -n myracn1<br />oracle@myracn1$ srvctl stop nodeapps -n myracn2<br /><br />oracle@myracn1$ cd -<br />/opt/oracle/crs/bin<br /><br />oracle@myracn1$ ./crs_stat -t<br />Name Type Target State Host <br />------------------------------------------------------------<br />ora....SM1.asm application ONLINE OFFLINE <br />ora....T2.lsnr application OFFLINE OFFLINE <br />ora.myracn1.gsd application OFFLINE OFFLINE <br />ora.myracn1.ons application OFFLINE OFFLINE <br />ora.myracn1.vip application OFFLINE OFFLINE <br />ora....SM2.asm application ONLINE OFFLINE <br />ora....T4.lsnr application OFFLINE OFFLINE <br />ora.myracn2.gsd application OFFLINE OFFLINE <br />ora.myracn2.ons application OFFLINE OFFLINE <br />ora.myracn2.vip application OFFLINE OFFLINE <br />ora.myrcdb.db application ONLINE OFFLINE <br />ora....l1.inst application ONLINE OFFLINE <br />ora....l2.inst application ONLINE OFFLINE <br /><br />oracle@myracn1$ cd -<br />/opt/oracle/parches/Disk1<br /><br />oracle@myracn1$ ./runInstaller<br />Starting Oracle Universal Installer...<br /><br />Checking installer requirements...<br /><br />Checking operating system version: must be 5.8, 5.9 or 5.10. Actual 5.10<br /> Passed<br /><br />Checking Temp space: must be greater than 250 MB. Actual 12967 MB Passed<br />Checking swap space: must be greater than 500 MB. Actual 15169 MB Passed<br />Checking monitor: must be configured to display at least 256 colors. Actual 16777216 Passed<br /><br />All installer requirements met.<br /><br />Preparing to launch Oracle Universal Installer from /tmp/OraInstall2011-11-17_02-23-05PM. Please wait ...<br />oracle@myracn1$ Oracle Universal Installer, Version 10.2.0.5.0 Production<br />Copyright (C) 1999, 2010, Oracle. All rights reserved.<br /><br />Warning: Cannot convert string "-monotype-arial-regular-r-normal--*-140-*-*-p-*-iso8859-1" to type FontStruct<br /></pre></span><br />Like before, we have to run as root the <span style="font-weight:bold;">root.sh</span> script before exiting the Oracle Universal Installer, <span style="font-weight:bold;">in all RAC nodes</span>:<br /><span style="font-size:75%;"><pre><br />oracle@myracn1$ su -<br />Password: <br />root@myracn1# /opt/oracle/db/root.sh<br />Running Oracle 10g root.sh script...<br /><br />The following environment variables are set as:<br /> ORACLE_OWNER= oracle<br /> ORACLE_HOME= /opt/oracle/db<br /><br />Enter the full pathname of the local bin directory: [/usr/local/bin]: <br />The file "dbhome" already exists in /usr/local/bin. Overwrite it? (y/n) [n]: y<br /> Copying dbhome to /usr/local/bin ...<br />The file "oraenv" already exists in /usr/local/bin. Overwrite it? (y/n) [n]: y<br /> Copying oraenv to /usr/local/bin ...<br />The file "coraenv" already exists in /usr/local/bin. Overwrite it? (y/n) [n]: y<br /> Copying coraenv to /usr/local/bin ...<br /><br />Entries will be added to the /var/opt/opt/oracle/oratab file as needed by<br />Database Configuration Assistant when a database is created<br />Finished running generic part of root.sh script.<br />Now product-specific root actions will be performed.<br /><br />root@myracn1# exit<br /></pre></span><br />At this point we have the 10.2.0.5 patch applied at software level so next step is to start up ASM and RAC services without database instances:<br /><span style="font-size:75%;"><pre><br />oracle@myracn1$ srvctl start listener -n myracn1<br />oracle@myracn1$ srvctl start listener -n myracn2<br />oracle@myracn1$ srvctl start nodeapps -n myracn1<br />oracle@myracn1$ srvctl start nodeapps -n myracn2<br /><br />oracle@myracn1$ ./crs_stat -t<br />Name Type Target State Host <br />------------------------------------------------------------<br />ora....SM1.asm application ONLINE OFFLINE <br />ora....T2.lsnr application ONLINE ONLINE myracn1 <br />ora.myracn1.gsd application ONLINE ONLINE myracn1 <br />ora.myracn1.ons application ONLINE ONLINE myracn1 <br />ora.myracn1.vip application ONLINE ONLINE myracn1 <br />ora....SM2.asm application ONLINE OFFLINE <br />ora....T4.lsnr application ONLINE ONLINE myracn2 <br />ora.myracn2.gsd application ONLINE ONLINE myracn2 <br />ora.myracn2.ons application ONLINE ONLINE myracn2 <br />ora.myracn2.vip application ONLINE ONLINE myracn2 <br />ora.myrcdb.db application ONLINE OFFLINE <br />ora....l1.inst application ONLINE OFFLINE <br />ora....l2.inst application ONLINE OFFLINE <br /><br />oracle@myracn1$ srvctl start asm -n myracn1<br />oracle@myracn1$ srvctl start asm -n myracn2<br /><br />oracle@myracn1$ ps -fea|grep pmon<br /> oracle 8812 6323 0 15:08:58 pts/1 0:00 grep pmon<br /> oracle 5280 1 0 15:03:28 ? 0:01 asm_pmon_+ASM1<br /></pre></span><br />In order to update the database catalog, we will set the CLUSTER_DATABASE parameter as false to start up just one instance; you have to do this only in the instance you will work but the whole database catalog upgrade procedure should be done for every database in the RAC.<br /><br />Just to be clear, in this example we are working with one ASM and one normal database (myrcdb) in a RAC cluster with two nodes; there is one instance of ASM running in each node (two ASM instances in total), and also one instance of the myrcdb database running in each node (two database instances in total).<br /><br />Also, you don't have to run a catalog upgrade in an ASM instance, just in database instances.<br /><span style="font-size:75%;"><pre><br />oracle@myracn1$ sqlplus '/ as sysdba'<br /><br />SQL*Plus: Release 10.2.0.5.0 - Production on Thu Nov 17 15:09:17 2011<br /><br />Copyright (c) 1982, 2010, Oracle. All Rights Reserved.<br /><br />Connected to an idle instance.<br /><br />SQL> startup nomount<br />ORACLE instance started.<br /><br />Total System Global Area 1610612736 bytes<br />Fixed Size 2052448 bytes<br />Variable Size 385879712 bytes<br />Database Buffers 1207959552 bytes<br />Redo Buffers 14721024 bytes<br />SQL> ALTER SYSTEM SET CLUSTER_DATABASE=FALSE SCOPE=spfile;<br /><br />System altered.<br /><br />SQL> shutdown <br />ORA-01507: database not mounted<br /><br /><br />ORACLE instance shut down.<br />SQL> exit<br /></pre></span><br />Before running the database catalog upgrade script we will start up the instance in upgrade mode, and as a good practice, run the upgrade information script to check if everything is fine to upgrade the catalog.<br /><span style="font-size:75%;"><pre><br />oracle@myracn1$ sqlplus '/ as sysdba'<br /><br />SQL*Plus: Release 10.2.0.5.0 - Production on Thu Nov 17 15:11:33 2011<br /><br />Copyright (c) 1982, 2010, Oracle. All Rights Reserved.<br /><br />Connected to an idle instance.<br /><br />SQL> startup upgrade;<br />ORACLE instance started.<br /><br />Total System Global Area 1610612736 bytes<br />Fixed Size 2052448 bytes<br />Variable Size 385879712 bytes<br />Database Buffers 1207959552 bytes<br />Redo Buffers 14721024 bytes<br />Database mounted.<br />Database opened.<br />SQL> @?/rdbms/admin/utlu102i.sql<br />Oracle Database 10.2 Upgrade Information Utility 11-17-2011 15:13:03<br />.<br />**********************************************************************<br />Database:<br />**********************************************************************<br />--> name: myrcdb<br />--> version: 10.2.0.1.0<br />--> compatible: 10.2.0.1.0<br />--> blocksize: 8192<br />.<br />**********************************************************************<br />Tablespaces: [make adjustments in the current environment]<br />**********************************************************************<br />--> SYSTEM tablespace is adequate for the upgrade.<br />.... minimum required size: 420 MB<br />--> UNDOTBS1 tablespace is adequate for the upgrade.<br />.... minimum required size: 402 MB<br />.... AUTOEXTEND additional space required: 202 MB<br />--> SYSAUX tablespace is adequate for the upgrade.<br />.... minimum required size: 185 MB<br />.... AUTOEXTEND additional space required: 5 MB<br />--> TEMP tablespace is adequate for the upgrade.<br />.... minimum required size: 58 MB<br />.... AUTOEXTEND additional space required: 38 MB<br />.<br />**********************************************************************<br />Update Parameters: [Update Oracle Database 10.2 init.ora or spfile]<br />**********************************************************************<br />-- No update parameter changes are required.<br />.<br />**********************************************************************<br />Renamed Parameters: [Update Oracle Database 10.2 init.ora or spfile]<br />**********************************************************************<br />-- No renamed parameters found. No changes are required.<br />.<br />**********************************************************************<br />Obsolete/Deprecated Parameters: [Update Oracle Database 10.2 init.ora or spfile]<br />**********************************************************************<br />-- No obsolete parameters found. No changes are required<br />.<br />**********************************************************************<br />Components: [The following database components will be upgraded or installed]<br />**********************************************************************<br />--> Oracle Catalog Views [upgrade] VALID<br />--> Oracle Packages and Types [upgrade] VALID<br />--> JServer JAVA Virtual Machine [upgrade] VALID<br />--> Oracle XDK for Java [upgrade] VALID<br />--> Oracle Java Packages [upgrade] VALID<br />--> Oracle XML Database [upgrade] VALID<br />--> Real Application Clusters [upgrade] VALID<br />--> Oracle Workspace Manager [upgrade] VALID<br />--> Oracle interMedia [upgrade] VALID<br />--> Expression Filter [upgrade] VALID<br />--> Rule Manager [upgrade] VALID<br />.<br /><br />PL/SQL procedure successfully completed.<br /></pre></span><br />Read carefully the report of the Upgrade Information Utility script and make any changes suggested; you can just execute the catalog upgrade script but if you ignore suggestions of this report the upgrade might fail, and you don't want to end with a corrupted catalog, believe me.<br /><br />After this we will execute the upgrade catalog script; remember, you have to execute this script for every database you have in your RAC cluster, but just in one instance per database:<br /><span style="font-size:75%;"><pre><br />SQL> spool patch.log<br />SQL> @?/rdbms/admin/catupgrd.sql<br /><br />...<br /><br />PL/SQL procedure successfully completed.<br /><br /><br />PL/SQL procedure successfully completed.<br /><br /><br />TIMESTAMP<br />--------------------------------------------------------------------------------<br />COMP_TIMESTAMP UPGRD_END 2011-11-17 17:20:37<br /><br />1 row selected.<br /><br />.<br />Oracle Database 10.2 Upgrade Status Utility 11-17-2011 17:20:37<br />.<br />Component Status Version HH:MM:SS<br />Oracle Database Server VALID 10.2.0.5.0 00:54:11<br />JServer JAVA Virtual Machine VALID 10.2.0.5.0 00:15:23<br />Oracle XDK VALID 10.2.0.5.0 00:02:40<br />Oracle Database Java Packages VALID 10.2.0.5.0 00:01:21<br />Oracle XML Database VALID 10.2.0.5.0 00:12:22<br />Oracle Real Application Clusters VALID 10.2.0.5.0 00:00:06<br />Oracle Workspace Manager VALID 10.2.0.5.0 00:04:02<br />Oracle interMedia VALID 10.2.0.5.0 00:24:00<br />Oracle Expression Filter VALID 10.2.0.5.0 00:00:58<br />Oracle Rule Manager VALID 10.2.0.5.0 00:00:47<br />.<br />Total Upgrade Time: 02:04:12<br /><br />PL/SQL procedure successfully completed.<br /><br />DOC>#######################################################################<br />DOC>#######################################################################<br />DOC><br />DOC> The above PL/SQL lists the SERVER components in the upgraded<br />DOC> database, along with their current version and status.<br />DOC><br />DOC> Please review the status and version columns and look for<br />DOC> any errors in the spool log file. If there are errors in the spool<br />DOC> file, or any components are not VALID or not the current version,<br />DOC> consult the Oracle Database Upgrade Guide for troubleshooting<br />DOC> recommendations.<br />DOC><br />DOC> Next shutdown immediate, restart for normal operation, and then<br />DOC> run utlrp.sql to recompile any invalid application objects.<br />DOC><br />DOC>#######################################################################<br />DOC>#######################################################################<br />DOC>#<br />SQL> spool off<br /></pre></span><br />As stated in the final message of the catupgrd.sql script check that all the components are present, has a valid status and proper version, and just to be sure check the patch.log file for ORA error messages.<br /><br />At this point we have almost finished applying the 10.2.0.5 patch, and we will recompile and look for invalid objects:<br /><span style="font-size:75%;"><pre><br />SQL> shutdown immediate<br />Database closed.<br />Database dismounted.<br />ORACLE instance shut down.<br />SQL> exit<br />Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - 64bit Production<br />With the Partitioning, Real Application Clusters, OLAP, Data Mining<br />and Real Application Testing options<br /><br />oracle@myracn1$ sqlplus '/ as sysdba'<br /><br />SQL*Plus: Release 10.2.0.5.0 - Production on Thu Nov 17 17:23:06 2011<br /><br />Copyright (c) 1982, 2010, Oracle. All Rights Reserved.<br /><br />Connected to an idle instance.<br /><br />SQL> startup <br />ORACLE instance started.<br /><br />Total System Global Area 1610612736 bytes<br />Fixed Size 2052448 bytes<br />Variable Size 436211360 bytes<br />Database Buffers 1157627904 bytes<br />Redo Buffers 14721024 bytes<br />Database mounted.<br />Database opened.<br />SQL> @?/rdbms/admin/utlrp.sql<br /><br />TIMESTAMP<br />--------------------------------------------------------------------------------<br />COMP_TIMESTAMP UTLRP_BGN 2011-11-17 17:24:02<br /><br />DOC> The following PL/SQL block invokes UTL_RECOMP to recompile invalid<br />DOC> objects in the database. Recompilation time is proportional to the<br />DOC> number of invalid objects in the database, so this command may take<br />DOC> a long time to execute on a database with a large number of invalid<br />DOC> objects.<br /><br /><br />...<br /><br /><br />DOC> The following query reports the number of objects that have compiled<br />DOC> with errors (objects that compile with errors have status set to 3 in<br />DOC> obj$). If the number is higher than expected, please examine the error<br />DOC> messages reported with each object (using SHOW ERRORS) to see if they<br />DOC> point to system misconfiguration or resource constraints that must be<br />DOC> fixed before attempting to recompile these objects.<br />DOC>#<br /><br />OBJECTS WITH ERRORS<br />-------------------<br /> 0<br /><br />DOC> The following query reports the number of errors caught during<br />DOC> recompilation. If this number is non-zero, please query the error<br />DOC> messages in the table UTL_RECOMP_ERRORS to see if any of these errors<br />DOC> are due to misconfiguration or resource constraints that must be<br />DOC> fixed before objects can compile successfully.<br />DOC>#<br /><br />ERRORS DURING RECOMPILATION<br />---------------------------<br /> 0<br /><br /><br />PL/SQL procedure successfully completed.<br /><br />SQL> column COMP_NAME format a50<br />SQL> column VERSION format a15<br />SQL> SELECT COMP_NAME, VERSION, STATUS FROM SYS.DBA_REGISTRY;<br /><br />COMP_NAME VERSION STATUS<br />-------------------------------------------------- --------------- -----------<br />Oracle interMedia 10.2.0.5.0 VALID<br />Oracle XML Database 10.2.0.5.0 VALID<br />Oracle Expression Filter 10.2.0.5.0 VALID<br />Oracle Rule Manager 10.2.0.5.0 VALID<br />Oracle Workspace Manager 10.2.0.5.0 VALID<br />Oracle Database Catalog Views 10.2.0.5.0 VALID<br />Oracle Database Packages and Types 10.2.0.5.0 VALID<br />JServer JAVA Virtual Machine 10.2.0.5.0 VALID<br />Oracle XDK 10.2.0.5.0 VALID<br />Oracle Database Java Packages 10.2.0.5.0 VALID<br />Oracle Real Application Clusters 10.2.0.5.0 VALID<br /><br />11 rows selected.<br /></pre></span><br />If everything looks fine, we just have to restore the CLUSTER_DATABASE parameter value to true and start the RAC database and services:<br /><span style="font-size:75%;"><pre><br />SQL> ALTER SYSTEM SET CLUSTER_DATABASE=TRUE SCOPE=spfile;<br /><br />System altered.<br /><br />SQL> shutdown immediate<br />Database closed.<br />Database dismounted.<br />ORACLE instance shut down.<br />SQL> exit<br />Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - 64bit Production<br />With the Partitioning, Real Application Clusters, OLAP, Data Mining<br />and Real Application Testing options<br /><br />oracle@myracn1$ srvctl start nodeapps -n myracn1<br />oracle@myracn1$ srvctl start nodeapps -n myracn2<br />oracle@myracn1$ srvctl start listener -n myracn1<br />oracle@myracn1$ srvctl start listener -n myracn2<br />oracle@myracn1$ srvctl start asm -n myracn1<br />oracle@myracn1$ srvctl start asm -n myracn2<br />oracle@myracn1$ srvctl start database -d myrcdb<br /><br />oracle@myracn1$ cd -<br />/opt/oracle/crs/bin<br /><br />oracle@myracn1$ ./crs_stat -t<br />Name Type Target State Host <br />------------------------------------------------------------<br />ora....SM1.asm application ONLINE ONLINE myracn1 <br />ora....T2.lsnr application ONLINE ONLINE myracn1 <br />ora.myracn1.gsd application ONLINE ONLINE myracn1 <br />ora.myracn1.ons application ONLINE ONLINE myracn1 <br />ora.myracn1.vip application ONLINE ONLINE myracn1 <br />ora....SM2.asm application ONLINE ONLINE myracn2 <br />ora....T4.lsnr application ONLINE ONLINE myracn2 <br />ora.myracn2.gsd application ONLINE ONLINE myracn2 <br />ora.myracn2.ons application ONLINE ONLINE myracn2 <br />ora.myracn2.vip application ONLINE ONLINE myracn2 <br />ora.myrcdb.db application ONLINE ONLINE myracn2 <br />ora....l1.inst application ONLINE ONLINE myracn1 <br />ora....l2.inst application ONLINE ONLINE myracn2 <br /></pre></span><br />We finished applying the 10.2.0.5 patch in our Oracle RAC! Now we will apply the PSU 10.2.0.5.4 just to be completely up to date, but first a quick check to the inventory:<br /><span style="font-size:75%;"><pre><br />oracle@myracn1$ export PATH=$PATH:$ORACLE_HOME/OPatch<br /><br />oracle@myracn1$ opatch lsinventory<br />Invoking OPatch 10.2.0.4.9<br /><br />Oracle Interim Patch Installer version 10.2.0.4.9<br />Copyright (c) 2009, Oracle Corporation. All rights reserved.<br /><br /><br />Oracle Home : /opt/oracle/db<br />Central Inventory : /opt/oracle/oraInventory<br /> from : /var/opt/opt/oracle/oraInst.loc<br />OPatch version : 10.2.0.4.9<br />OUI version : 10.2.0.5.0<br />OUI location : /opt/oracle/db/oui<br />Log file location : /opt/oracle/db/cfgtoollogs/opatch/opatch2011-11-17_17-48-04PM.log<br /><br />Patch history file: /opt/oracle/db/cfgtoollogs/opatch/opatch_history.txt<br /><br />Lsinventory Output file location : /opt/oracle/db/cfgtoollogs/opatch/lsinv/lsinventory2011-11-17_17-48-04PM.txt<br /><br />--------------------------------------------------------------------------------<br />Installed Top-level Products (2): <br /><br />Oracle Database 10g 10.2.0.1.0<br />Oracle Database 10g Release 2 Patch Set 4 10.2.0.5.0<br />There are 2 products installed in this Oracle Home.<br /><br /><br />There are no Interim patches installed in this Oracle Home.<br /><br /><br />Rac system comprising of multiple nodes<br /> Local node = myracn1<br /> Remote node = myracn2<br /><br />--------------------------------------------------------------------------------<br /><br />OPatch succeeded.<br /></pre></span><br />Next, shutdown all RAC instances and services:<br /><span style="font-size:75%;"><pre><br />oracle@myracn1$ srvctl stop database -d myrcdb<br />oracle@myracn1$ srvctl stop asm -n myracn1<br />oracle@myracn1$ srvctl stop asm -n myracn2<br />oracle@myracn1$ srvctl stop listener -n myracn1<br />oracle@myracn1$ srvctl stop listener -n myracn2<br />oracle@myracn1$ srvctl stop nodeapps -n myracn1<br />oracle@myracn1$ srvctl stop nodeapps -n myracn2<br /><br />oracle@myracn1$ cd /opt/oracle/crs/bin<br /><br />oracle@myracn1$ ./crs_stat -t<br />Name Type Target State Host <br />------------------------------------------------------------<br />ora....SM1.asm application ONLINE OFFLINE <br />ora....T2.lsnr application OFFLINE OFFLINE <br />ora.myracn1.gsd application OFFLINE OFFLINE <br />ora.myracn1.ons application OFFLINE OFFLINE <br />ora.myracn1.vip application OFFLINE OFFLINE <br />ora....SM2.asm application ONLINE OFFLINE <br />ora....T4.lsnr application OFFLINE OFFLINE <br />ora.myracn2.gsd application OFFLINE OFFLINE <br />ora.myracn2.ons application OFFLINE OFFLINE <br />ora.myracn2.vip application OFFLINE OFFLINE <br />ora.myrcdb.db application ONLINE OFFLINE <br />ora....l1.inst application ONLINE OFFLINE <br />ora....l2.inst application ONLINE OFFLINE <br /></pre></span><br />Then, we try to apply the PSU but ...<br /><span style="font-size:75%;"><pre><br />oracle@myracn1$ cd /opt/oracle/parches<br />oracle@myracn1$ opatch napply 12419392 -all_nodes<br />Invoking OPatch 10.2.0.4.9<br /><br />Oracle Interim Patch Installer version 10.2.0.4.9<br />Copyright (c) 2009, Oracle Corporation. All rights reserved.<br /><br />UTIL session<br /><br />Oracle Home : /opt/oracle/db<br />Central Inventory : /opt/oracle/oraInventory<br /> from : /var/opt/opt/oracle/oraInst.loc<br />OPatch version : 10.2.0.4.9<br />OUI version : 10.2.0.5.0<br />OUI location : /opt/oracle/db/oui<br />Log file location : /opt/oracle/db/cfgtoollogs/opatch/opatch2011-11-17_17-48-44PM.log<br /><br />Patch history file: /opt/oracle/db/cfgtoollogs/opatch/opatch_history.txt<br /><br />Invoking utility "napply"<br />UtilSession failed: <br /><br />Patch 12419392 requires OPatch version 10.2.0.5.0.<br />The OPatch version being used (10.2.0.4.9) doesn't meet the minimum version required by the patch(es). Please download latest OPatch from My Oracle Support.<br /><br /><br />OPatch failed with error code 73<br /></pre></span><br />This is why we downloaded the 6880880 patch; in order to apply the PSU 10.2.0.5.4 we need an OPatch version 10.2.0.5.0 or above.<br /><br />Anyway, it's very easy to upgrade OPatch, we just have to remove the original OPatch directory and unzip the new in the Oracle Home:<br /><span style="font-size:75%;"><pre><br />oracle@myracn1$ cd $ORACLE_HOME<br />oracle@myracn1$ rm -r OPatch<br />oracle@myracn1$ unzip /opt/oracle/parches/p6880880_102000_SOLARIS64.zip<br />Archive: /opt/oracle/parches/p6880880_102000_SOLARIS64.zip<br /> creating: OPatch/<br /> creating: OPatch/ocm/<br /> extracting: OPatch/ocm/ocm.zip <br /> creating: OPatch/ocm/lib/<br /> inflating: OPatch/ocm/lib/osdt_jce.jar <br /><br />...<br /><br /><br /> inflating: OPatch/docs/Prereq_Users_Guide.txt <br /> inflating: OPatch/docs/FAQ <br /> inflating: OPatch/docs/Users_Guide.txt <br /> inflating: OPatch/README.txt <br /><br />oracle@myracn1$ ls -la OPatch/<br />total 120<br />drwxr-xr-x 7 oracle dba 512 Nov 17 18:20 .<br />drwxr-x--- 62 oracle dba 1536 Nov 17 18:20 ..<br />drwxr-xr-x 3 oracle dba 512 Nov 3 2010 crs<br />drwxr-xr-x 2 oracle dba 512 Nov 3 2010 docs<br />-rw-r--r-- 1 oracle dba 23695 Nov 3 2010 emdpatch.pl<br />drwxr-xr-x 2 oracle dba 512 Nov 3 2010 jlib<br />drwxr-xr-x 4 oracle dba 512 Nov 3 2010 ocm<br />-r-x--x--- 1 oracle dba 13252 Nov 3 2010 opatch<br />-rwxr-xr-x 1 oracle dba 8085 Nov 3 2010 opatch.bat<br />-rw-r--r-- 1 oracle dba 49 Nov 3 2010 opatch.ini<br />-rw-r--r-- 1 oracle dba 2576 Nov 3 2010 opatch.pl<br />drwxr-xr-x 4 oracle dba 512 Nov 3 2010 opatchprereqs<br />-rw-r--r-- 1 oracle dba 2417 Nov 3 2010 README.txt<br /></pre></span><br />With the OPatch upgraded now we are really ready to apply the PSU to all RAC nodes at the same time, and fortunately for this patch we need to answer just a few easy questions:<br /><span style="font-size:75%;"><pre><br />oracle@myracn1$ cd /opt/oracle/parches<br /><br />oracle@myracn1$ opatch napply 12419392 -all_nodes<br />Invoking OPatch 10.2.0.5.1<br /><br />Oracle Interim Patch Installer version 10.2.0.5.1<br />Copyright (c) 2010, Oracle Corporation. All rights reserved.<br /><br />UTIL session<br /><br />Oracle Home : /opt/oracle/db<br />Central Inventory : /opt/oracle/oraInventory<br /> from : /var/opt/opt/oracle/oraInst.loc<br />OPatch version : 10.2.0.5.1<br />OUI version : 10.2.0.5.0<br />OUI location : /opt/oracle/db/oui<br />Log file location : /opt/oracle/db/cfgtoollogs/opatch/opatch2011-11-17_18-23-00PM.log<br /><br />Patch history file: /opt/oracle/db/cfgtoollogs/opatch/opatch_history.txt<br /><br />Invoking utility "napply"<br />Checking conflict among patches...<br />Checking if Oracle Home has components required by patches...<br />Checking conflicts against Oracle Home...<br />OPatch continues with these patches: 12419392 <br /><br />Do you want to proceed? [y|n]<br />y<br />User Responded with: Y<br /><br />Running prerequisite checks...<br />Provide your email address to be informed of security issues, install and<br />initiate Oracle Configuration Manager. Easier for you if you use your My<br />Oracle Support Email address/User Name.<br />Visit http://www.oracle.com/support/policies.html for details.<br />Email address/User Name: <br /><br />You have not provided an email address for notification of security issues.<br />Do you wish to remain uninformed of security issues ([Y]es, [N]o) [N]: Y<br /><br />OPatch detected the node list and the local node from the inventory. OPatch will patch the local system then propagate the patch to the remote nodes.<br /><br /><br />This node is part of an Oracle Real Application Cluster.<br />Remote nodes: 'myracn2' <br />Local node: 'myracn1'<br />Please shut down Oracle instances running out of this ORACLE_HOME on all the nodes.<br />(Oracle Home = '/opt/oracle/db')<br /><br /><br />Are all the nodes ready for patching? [y|n]<br />y<br />User Responded with: Y<br />Backing up files affected by the patch 'NApply' for restore. This might take a while...<br />Execution of 'sh /opt/oracle/parches/12419392/custom/scripts/pre -apply 12419392 ':<br /><br /><br />Return Code = 0<br /><br />Applying patch 12419392...<br /><br />ApplySession applying interim patch '12419392' to OH '/opt/oracle/db'<br />ApplySession: Optional component(s) [ oracle.rdbms.dv, 10.2.0.5.0 ] , [ oracle.rdbms.dv.oc4j, 10.2.0.5.0 ] not present in the Oracle Home or a higher version is found.<br />Backing up files affected by the patch '12419392' for rollback. This might take a while...<br /><br />Patching component oracle.rdbms, 10.2.0.5.0...<br />Updating archive file "/opt/oracle/db/lib/libserver10.a" with "lib/libserver10.a/kcbl.o"<br />Updating archive file "/opt/oracle/db/lib/libserver10.a" with "lib/libserver10.a/qecsel.o"<br /><br /><br />...<br /><br /><br />Updating jar file "/opt/oracle/db/sysman/jlib/emjsp.jar" with "/sysman/jlib/emjsp.jar/_database/_dbObjectsList.class"<br />Copying file to "/opt/oracle/db/oc4j/j2ee/oc4j_applications/applications/em/em/admin/rep/editUserSummary.uix"<br /><br />Patching component oracle.xdk.rsf, 10.2.0.5.0...<br />Updating archive file "/opt/oracle/db/lib/libxml10.a" with "lib/libxml10.a/lpxpar.o"<br />Updating archive file "/opt/oracle/db/lib32/libxml10.a" with "lib32/libxml10.a/lpxpar.o"<br /><br />Patching component oracle.precomp.common, 10.2.0.5.0...<br /><br />Patching component oracle.rdbms.rman, 10.2.0.5.0...<br />ApplySession adding interim patch '12419392' to inventory<br /><br />Verifying the update...<br />Inventory check OK: Patch ID 12419392 is registered in Oracle Home inventory with proper meta-data.<br />Files check OK: Files from Patch ID 12419392 are present in Oracle Home.<br />Running make for target client_sharedlib<br />Running make for target ioracle<br />Running make for target iwrap<br />Running make for target client_sharedlib<br />Running make for target proc<br />Running make for target irman<br /><br />The local system has been patched and can be restarted.<br /><br /><br />Patching in all-node mode.<br /><br />Updating nodes 'myracn2' <br /> Apply-related files are:<br /> FP = "/opt/oracle/db/.patch_storage/NApply/2011-11-17_18-23-00PM/rac/copy_files.txt"<br /> DP = "/opt/oracle/db/.patch_storage/NApply/2011-11-17_18-23-00PM/rac/copy_dirs.txt"<br /> MP = "/opt/oracle/db/.patch_storage/NApply/2011-11-17_18-23-00PM/rac/make_cmds.txt"<br /> RC = "/opt/oracle/db/.patch_storage/NApply/2011-11-17_18-23-00PM/rac/remote_cmds.txt"<br /><br />Instantiating the file "/opt/oracle/db/.patch_storage/NApply/2011-11-17_18-23-00PM/rac/copy_files.txt.instantiated" by replacing $ORACLE_HOME in "/opt/oracle/db/.patch_storage/NApply/2011-11-17_18-23-00PM/rac/copy_files.txt" with actual path.<br />Propagating files to remote nodes...<br />Instantiating the file "/opt/oracle/db/.patch_storage/NApply/2011-11-17_18-23-00PM/rac/copy_dirs.txt.instantiated" by replacing $ORACLE_HOME in "/opt/oracle/db/.patch_storage/NApply/2011-11-17_18-23-00PM/rac/copy_dirs.txt" with actual path.<br />Propagating directories to remote nodes...<br />Instantiating the file "/opt/oracle/db/.patch_storage/NApply/2011-11-17_18-23-00PM/rac/make_cmds.txt.instantiated" by replacing $ORACLE_HOME in "/opt/oracle/db/.patch_storage/NApply/2011-11-17_18-23-00PM/rac/make_cmds.txt" with actual path.<br />Running command on remote node 'myracn2': <br />cd /opt/oracle/db/rdbms/lib; /usr/ccs/bin/make -f ins_rdbms.mk client_sharedlib ORACLE_HOME=/opt/oracle/db || echo REMOTE_MAKE_FAILED::>&2 <br /><br />Running command on remote node 'myracn2': <br />cd /opt/oracle/db/rdbms/lib; /usr/ccs/bin/make -f ins_rdbms.mk ioracle ORACLE_HOME=/opt/oracle/db || echo REMOTE_MAKE_FAILED::>&2 <br /><br />Running command on remote node 'myracn2': <br />cd /opt/oracle/db/plsql/lib; /usr/ccs/bin/make -f ins_plsql.mk iwrap ORACLE_HOME=/opt/oracle/db || echo REMOTE_MAKE_FAILED::>&2 <br /><br />Running command on remote node 'myracn2': <br />cd /opt/oracle/db/network/lib; /usr/ccs/bin/make -f ins_net_client.mk client_sharedlib ORACLE_HOME=/opt/oracle/db || echo REMOTE_MAKE_FAILED::>&2 <br /><br />Running command on remote node 'myracn2': <br />cd /opt/oracle/db/precomp/lib; /usr/ccs/bin/make -f ins_precomp.mk proc ORACLE_HOME=/opt/oracle/db || echo REMOTE_MAKE_FAILED::>&2 <br /><br />Running command on remote node 'myracn2': <br />cd /opt/oracle/db/rdbms/lib; /usr/ccs/bin/make -f ins_rdbms.mk irman ORACLE_HOME=/opt/oracle/db || echo REMOTE_MAKE_FAILED::>&2 <br /><br />RC file not exist. There are no commands to be run on the remote nodes.<br /><br />All nodes have been patched. You may start Oracle instances on the local system and nodes 'myracn2' <br /><br />UtilSession: N-Apply done.<br /><br />OPatch succeeded.<br /></pre></span><br />Applying a PSU is faster than applying a patch that needs OUI, but for this particular PSU we also need to upgrade the database catalog after starting up the RAC database and services:<br /><span style="font-size:75%;"><pre><br />oracle@myracn1$ srvctl start nodeapps -n myracn1<br />oracle@myracn1$ srvctl start nodeapps -n myracn2<br />oracle@myracn1$ srvctl start listener -n myracn1<br />oracle@myracn1$ srvctl start listener -n myracn2<br />oracle@myracn1$ srvctl start asm -n myracn1<br />oracle@myracn1$ srvctl start asm -n myracn2<br />oracle@myracn1$ srvctl start database -d myrcdb<br /><br />oracle@myracn1$ cd /opt/oracle/crs/bin<br /><br />oracle@myracn1$ ./crs_stat -t<br />Name Type Target State Host <br />------------------------------------------------------------<br />ora....SM1.asm application ONLINE ONLINE myracn1 <br />ora....T2.lsnr application ONLINE ONLINE myracn1 <br />ora.myracn1.gsd application ONLINE ONLINE myracn1 <br />ora.myracn1.ons application ONLINE ONLINE myracn1 <br />ora.myracn1.vip application ONLINE ONLINE myracn1 <br />ora....SM2.asm application ONLINE ONLINE myracn2 <br />ora....T4.lsnr application ONLINE ONLINE myracn2 <br />ora.myracn2.gsd application ONLINE ONLINE myracn2 <br />ora.myracn2.ons application ONLINE ONLINE myracn2 <br />ora.myracn2.vip application ONLINE ONLINE myracn2 <br />ora.myrcdb.db application ONLINE ONLINE myracn1 <br />ora....l1.inst application ONLINE ONLINE myracn1 <br />ora....l2.inst application ONLINE ONLINE myracn2 <br /></pre></span><br />To upgrade the database catalog, we will run the catbundle.sql script for every database in the RAC but just once; for this example we will run it for the myrcdb just one time:<br /><span style="font-size:75%;"><pre><br />oracle@myracn1$ sqlplus '/ as sysdba'<br /><br />SQL*Plus: Release 10.2.0.5.0 - Production on Thu Nov 17 18:43:44 2011<br /><br />Copyright (c) 1982, 2010, Oracle. All Rights Reserved.<br /><br /><br />Connected to:<br />Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - 64bit Production<br />With the Partitioning, Real Application Clusters, OLAP, Data Mining<br />and Real Application Testing options<br /><br />SQL> @?/rdbms/admin/catbundle.sql psu apply<br /><br />PL/SQL procedure successfully completed.<br /><br /><br />PL/SQL procedure successfully completed.<br /><br /><br />Generating apply and rollback scripts...<br /><br /><br />...<br /><br /><br />Updating registry...<br /><br />1 row created.<br /><br /><br />Commit complete.<br /><br />Check the following log file for errors:<br />/opt/oracle/db/cfgtoollogs/catbundle/catbundle_PSU_myrcdb_APPLY_2011Nov17_18_44_18.log<br /></pre></span><br />As suggested, it might be good to check the log file for ORA errors just to be sure. And as the last step, we will recompile and look for invalid objects:<br /><span style="font-size:75%;"><pre><br />SQL> @?/rdbms/admin/utlrp.sql<br /><br />TIMESTAMP<br />--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------<br />COMP_TIMESTAMP UTLRP_BGN 2011-11-17 18:44:47<br /><br />DOC> The following PL/SQL block invokes UTL_RECOMP to recompile invalid<br />DOC> objects in the database. Recompilation time is proportional to the<br />DOC> number of invalid objects in the database, so this command may take<br />DOC> a long time to execute on a database with a large number of invalid<br />DOC> objects.<br /><br /><br />...<br /><br /><br />DOC> The following query reports the number of objects that have compiled<br />DOC> with errors (objects that compile with errors have status set to 3 in<br />DOC> obj$). If the number is higher than expected, please examine the error<br />DOC> messages reported with each object (using SHOW ERRORS) to see if they<br />DOC> point to system misconfiguration or resource constraints that must be<br />DOC> fixed before attempting to recompile these objects.<br />DOC>#<br /><br />OBJECTS WITH ERRORS<br />-------------------<br /> 0<br /><br />DOC> The following query reports the number of errors caught during<br />DOC> recompilation. If this number is non-zero, please query the error<br />DOC> messages in the table UTL_RECOMP_ERRORS to see if any of these errors<br />DOC> are due to misconfiguration or resource constraints that must be<br />DOC> fixed before objects can compile successfully.<br />DOC>#<br /><br />ERRORS DURING RECOMPILATION<br />---------------------------<br /> 0<br /><br /><br />PL/SQL procedure successfully completed.<br /><br />SQL> exit<br /></pre></span><br />That's it! At this point we have an up to date RAC cluster with Oracle 10.2.0.5.4; we had to shut down the database service to apply this patch but this way is easier and quicker than applying a patch with the rolling upgrade method. Doing a rolling upgrade means applying a patch with no downtime, but the patch must be suitable for this method and is more complex and time-consuming than just shutting down a RAC and applying a patch.<br /><br />Listing the inventory you can check that the 12419392 patch is registered:<br /><span style="font-size:75%;"><pre><br />oracle@myracn1$ opatch lsinventory<br />Invoking OPatch 10.2.0.5.1<br /><br />Oracle Interim Patch Installer version 10.2.0.5.1<br />Copyright (c) 2010, Oracle Corporation. All rights reserved.<br /><br /><br />Oracle Home : /opt/oracle/db<br />Central Inventory : /opt/oracle/oraInventory<br /> from : /var/opt/opt/oracle/oraInst.loc<br />OPatch version : 10.2.0.5.1<br />OUI version : 10.2.0.5.0<br />OUI location : /opt/oracle/db/oui<br />Log file location : /opt/oracle/db/cfgtoollogs/opatch/opatch2011-11-17_18-46-17PM.log<br /><br />Patch history file: /opt/oracle/db/cfgtoollogs/opatch/opatch_history.txt<br /><br />Lsinventory Output file location : /opt/oracle/db/cfgtoollogs/opatch/lsinv/lsinventory2011-11-17_18-46-17PM.txt<br /><br />--------------------------------------------------------------------------------<br />Installed Top-level Products (2): <br /><br />Oracle Database 10g 10.2.0.1.0<br />Oracle Database 10g Release 2 Patch Set 4 10.2.0.5.0<br />There are 2 products installed in this Oracle Home.<br /><br /><br />Interim patches (1) :<br /><br />Patch 12419392 : applied on Thu Nov 17 18:28:53 CST 2011<br />Unique Patch ID: 13856866<br /> Created on 15 Jun 2011, 22:41:17 hrs PST8PDT<br /> Bugs fixed:<br /> 6402302, 10269717, 10327190, 8865718, 10017048, 9024850, 8394351, 8546356<br /> 9360157, 9770451, 9020537, 9772888, 8664189, 10091698, 12551710, 7519406<br /> 10132870, 8771916, 9109487, 10173237, 10068982, 8350262, 11792865<br /> 11724962, 11725006, 9184754, 8544696, 9320130, 7026523, 8277300, 9726739<br /> 8412426, 12419392, 6651220, 9150282, 9659614, 9949948, 10327179, 8882576<br /> 7612454, 9711859, 9714832, 10248542, 9952230, 9469117, 9952270, 8660422<br /> 10324526, 12419258, 9713537, 10010310, 9390484, 9963497, 12551700<br /> 12551701, 10249537, 12551702, 12551703, 8211733, 12551704, 9548269<br /> 12551705, 12551706, 9337325, 12551707, 7602341, 12551708, 9308296<br /> 10157402, 11737047<br /><br /><br /><br />Rac system comprising of multiple nodes<br /> Local node = myracn1<br /> Remote node = myracn2<br /><br />--------------------------------------------------------------------------------<br /><br />OPatch succeeded.<br /></pre></span><br />By the way, did you notice that there was no PSU for CRS? Usually there is the same patch and PSU for all Oracle Database components (CRS, ASM, databases), and also for all platforms, but in this particular case there was no 10.2.0.5.4 PSU for CRS. If there is, it would be preferably (or even mandatory) to have the same patch and PSU across all Oracle Database components, but this time it's fine this way.<br /><br />More information:<br /><br /><a href="https://support.oracle.com/CSP/main/article?cmd=show&type=NOT&id=756671.1#r10205">10.2.0.5 Current Recommended Patches</a>Cronoshttp://www.blogger.com/profile/15427827507143505689noreply@blogger.com7tag:blogger.com,1999:blog-4232775939402125807.post-48291685758739244352011-12-06T12:35:00.000-08:002011-12-27T08:42:14.612-08:00Getting Oracle SQL execution plansIn order to tune SQL sentences you need to get its execution plan, that is, information about how Oracle database is going to process your SQL sentence to deliver results in the best way possible. This an important topic and a very complex one, and this post is intended only to show how to get execution plans, not how to analyze them.<br /><br />The easiest way to get an execution plan is to use the <span style="font-weight:bold;">EXPLAIN PLAN FOR</span> statement and utlxpls.sql script:<br /><span style="font-size:75%;"><pre><br />SQL> set linesize 130<br />SQL> set pagesize 9999<br />SQL> EXPLAIN PLAN FOR<br />select OBJECT_NAME from dba_objects where OWNER='SYS' order by OBJECT_NAME;<br /><br />Explained.<br /><br />SQL> @$ORACLE_HOME/rdbms/admin/utlxpls.sql<br /><br />PLAN_TABLE_OUTPUT<br />----------------------------------------------------------------------------------------------------------------------------------<br />Plan hash value: 2325770649<br /><br />------------------------------------------------------------------------------------------------<br />| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |<br />------------------------------------------------------------------------------------------------<br />| 0 | SELECT STATEMENT | | 1025 | 85075 | 7 (29)| 00:00:01 |<br />| 1 | SORT ORDER BY | | 1025 | 85075 | 7 (29)| 00:00:01 |<br />| 2 | VIEW | DBA_OBJECTS | 1025 | 85075 | 6 (17)| 00:00:01 |<br />| 3 | UNION-ALL | | | | | |<br />|* 4 | FILTER | | | | | |<br />| 5 | NESTED LOOPS | | 1179 | 101K| 4 (25)| 00:00:01 |<br />| 6 | TABLE ACCESS BY INDEX ROWID| USER$ | 1 | 14 | 1 (0)| 00:00:01 |<br />|* 7 | INDEX UNIQUE SCAN | I_USER1 | 1 | | 0 (0)| 00:00:01 |<br />|* 8 | TABLE ACCESS FULL | OBJ$ | 1179 | 87246 | 3 (34)| 00:00:01 |<br />|* 9 | TABLE ACCESS BY INDEX ROWID | IND$ | 1 | 8 | 1 (0)| 00:00:01 |<br />|* 10 | INDEX UNIQUE SCAN | I_IND1 | 1 | | 1 (0)| 00:00:01 |<br />| 11 | NESTED LOOPS | | 1 | 27 | 2 (0)| 00:00:01 |<br />| 12 | TABLE ACCESS BY INDEX ROWID | USER$ | 1 | 14 | 1 (0)| 00:00:01 |<br />|* 13 | INDEX UNIQUE SCAN | I_USER1 | 1 | | 0 (0)| 00:00:01 |<br />|* 14 | INDEX RANGE SCAN | I_LINK1 | 1 | 13 | 1 (0)| 00:00:01 |<br />------------------------------------------------------------------------------------------------<br /><br />Predicate Information (identified by operation id):<br />---------------------------------------------------<br /><br /> 4 - filter("O"."TYPE#"1 AND "O"."TYPE#"10 OR "O"."TYPE#"=1 AND (SELECT 1 FROM<br /> "SYS"."IND$" "I" WHERE "I"."OBJ#"=:B1 AND ("I"."TYPE#"=1 OR "I"."TYPE#"=2 OR<br /> "I"."TYPE#"=3 OR "I"."TYPE#"=4 OR "I"."TYPE#"=6 OR "I"."TYPE#"=7 OR "I"."TYPE#"=9))=1)<br /> 7 - access("U"."NAME"='SYS')<br /> 8 - filter("O"."NAME"'_NEXT_OBJECT' AND "O"."NAME"'_default_auditing_options_'<br /> AND "O"."LINKNAME" IS NULL AND BITAND("O"."FLAGS",128)=0 AND "O"."OWNER#"="U"."USER#")<br /> 9 - filter("I"."TYPE#"=1 OR "I"."TYPE#"=2 OR "I"."TYPE#"=3 OR "I"."TYPE#"=4 OR<br /> "I"."TYPE#"=6 OR "I"."TYPE#"=7 OR "I"."TYPE#"=9)<br /> 10 - access("I"."OBJ#"=:B1)<br /> 13 - access("U"."NAME"='SYS')<br /> 14 - access("L"."OWNER#"="U"."USER#")<br /><br />36 rows selected.<br /></pre></span><br />As you can see there is a lot of information about this very simple SQL sentence, therefore don't forget to read the documentation listed at the end of this post to have a better knowledge about execution plans.<br /><br />By the way, if you're executing sentences with parallelism you would get more information using the utlxplp.sql script:<br /><span style="font-size:75%;"><pre><br />SQL> EXPLAIN PLAN FOR<br />select /*+ PARALLEL(b,4) */ b.OBJECT_NAME from dba_objects b where OWNER='SYS' order by OBJECT_NAME;<br /><br />Explained.<br /><br />SQL> @$ORACLE_HOME/rdbms/admin/utlxplp.sql<br /><br />PLAN_TABLE_OUTPUT<br />----------------------------------------------------------------------------------------------------------------------------------<br />Plan hash value: 2762843742<br /><br />----------------------------------------------------------------------------------------------------------------------------------<br />| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | TQ |IN-OUT| PQ Distrib |<br />----------------------------------------------------------------------------------------------------------------------------------<br />| 0 | SELECT STATEMENT | | 1025 | 85075 | 15 (7)| 00:00:01 | | | |<br />| 1 | SORT ORDER BY | | 1025 | 85075 | 15 (7)| 00:00:01 | | | |<br />| 2 | VIEW | DBA_OBJECTS | 1025 | 85075 | 14 (0)| 00:00:01 | | | |<br />| 3 | UNION-ALL | | | | | | | | |<br />|* 4 | FILTER | | | | | | | | |<br />| 5 | PX COORDINATOR | | | | | | | | |<br />| 6 | PX SEND QC (RANDOM) | :TQ10001 | 1179 | 101K| 12 (0)| 00:00:01 | Q1,01 | P->S | QC (RAND) |<br />| 7 | NESTED LOOPS | | 1179 | 101K| 12 (0)| 00:00:01 | Q1,01 | PCWP | |<br />| 8 | BUFFER SORT | | | | | | Q1,01 | PCWC | |<br />| 9 | PX RECEIVE | | | | | | Q1,01 | PCWP | |<br />| 10 | PX SEND BROADCAST | :TQ10000 | | | | | | S->P | BROADCAST |<br />| 11 | TABLE ACCESS BY INDEX ROWID| USER$ | 1 | 14 | 1 (0)| 00:00:01 | | | |<br />|* 12 | INDEX UNIQUE SCAN | I_USER1 | 1 | | 0 (0)| 00:00:01 | | | |<br />| 13 | PX BLOCK ITERATOR | | 1179 | 87246 | 11 (0)| 00:00:01 | Q1,01 | PCWC | |<br />|* 14 | TABLE ACCESS FULL | OBJ$ | 1179 | 87246 | 11 (0)| 00:00:01 | Q1,01 | PCWP | |<br />|* 15 | TABLE ACCESS BY INDEX ROWID | IND$ | 1 | 8 | 1 (0)| 00:00:01 | | | |<br />|* 16 | INDEX UNIQUE SCAN | I_IND1 | 1 | | 1 (0)| 00:00:01 | | | |<br />| 17 | NESTED LOOPS | | 1 | 27 | 2 (0)| 00:00:01 | | | |<br />| 18 | TABLE ACCESS BY INDEX ROWID | USER$ | 1 | 14 | 1 (0)| 00:00:01 | | | |<br />|* 19 | INDEX UNIQUE SCAN | I_USER1 | 1 | | 0 (0)| 00:00:01 | | | |<br />|* 20 | INDEX RANGE SCAN | I_LINK1 | 1 | 13 | 1 (0)| 00:00:01 | | | |<br />----------------------------------------------------------------------------------------------------------------------------------<br /><br />Predicate Information (identified by operation id):<br />---------------------------------------------------<br /><br /> 4 - filter("O"."TYPE#"1 AND "O"."TYPE#"10 OR "O"."TYPE#"=1 AND (SELECT 1 FROM "SYS"."IND$" "I" WHERE<br /> "I"."OBJ#"=:B1 AND ("I"."TYPE#"=1 OR "I"."TYPE#"=2 OR "I"."TYPE#"=3 OR "I"."TYPE#"=4 OR "I"."TYPE#"=6 OR "I"."TYPE#"<br />=7 OR<br /><br /> "I"."TYPE#"=9))=1)<br /> 12 - access("U"."NAME"='SYS')<br /> 14 - filter("O"."NAME"'_NEXT_OBJECT' AND "O"."NAME"'_default_auditing_options_' AND "O"."LINKNAME" IS NULL AND<br /> BITAND("O"."FLAGS",128)=0 AND "O"."OWNER#"="U"."USER#")<br /> 15 - filter("I"."TYPE#"=1 OR "I"."TYPE#"=2 OR "I"."TYPE#"=3 OR "I"."TYPE#"=4 OR "I"."TYPE#"=6 OR "I"."TYPE#"=7 OR<br /> "I"."TYPE#"=9)<br /> 16 - access("I"."OBJ#"=:B1)<br /> 19 - access("U"."NAME"='SYS')<br /> 20 - access("L"."OWNER#"="U"."USER#")<br /><br />42 rows selected.<br /></pre></span><br />As said above, using <span style="font-weight:bold;">EXPLAIN PLAN FOR</span> is the easiest way to have an idea about an SQL sentence's execution plan without having to execute it, but it might not be the same plan used to execute it because plan selection happens at execution time and depends of a lot of factors like load, accuracy of statistics and hints to name a few. In fact, you would be more interested in knowing the execution plan of a running SQL rather than the execution plan of an SQL sentence not executed yet; if that's the case you can get it this way:<br /><span style="font-size:75%;"><pre><br />SQL> column SID format a6<br />SQL> column USERNAME format a10<br />SQL> column PROGRAM format a50<br />SQL> column EVENT format a30<br /><br />SQL> select to_char(s.sid) AS sid, s.username, s.status, s.program, s.event<br />FROM v$session s JOIN v$process p ON (p.addr = s.paddr)<br />WHERE s.username = 'SYS' ORDER BY 1;<br /><br />SID USERNAME STATUS PROGRAM EVENT<br />---- ---------- ---------- ------------------------------------------------ ------------------------------<br />1065 SYS ACTIVE sqlplus@myracnode (TNS V1-V3) SQL*Net message to client<br />1087 SYS ACTIVE sqlplus@myracnode (TNS V1-V3) db file scattered read<br />1259 SYS INACTIVE racgimon@myracnode (TNS V1-V3) SQL*Net message from client<br />1275 SYS INACTIVE racgimon@myracnode (TNS V1-V3) SQL*Net message from client<br />1276 SYS ACTIVE racgimon@myracnode (TNS V1-V3) Streams AQ: waiting for messag<br /> es in the queue<br /><br />1302 SYS INACTIVE racgimon@myracnode (TNS V1-V3) SQL*Net message from client<br /><br />6 rows selected.<br /><br />SQL> SELECT p.plan_table_output FROM v$session s, table(dbms_xplan.display_cursor(s.sql_id, s.sql_child_number)) p<br />where s.sid = 1087;<br /><br />PLAN_TABLE_OUTPUT<br />----------------------------------------------------------------------------------------------------------------------------------<br />SQL_ID 8qdgqzzzgk4as, child number 0<br />-------------------------------------<br />select count(*) from my_big_table where some_date < '25-JAN-08'<br /><br />Plan hash value: 857495206<br /><br />--------------------------------------------------------------------------------------<br />| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |<br />--------------------------------------------------------------------------------------<br />| 0 | SELECT STATEMENT | | | | 2678 (100)| |<br />| 1 | SORT AGGREGATE | | 1 | 14 | | |<br />|* 2 | TABLE ACCESS FULL| MY_BIG_TABLE | 101K| 1382K| 2678 (27)| 00:00:27 |<br />--------------------------------------------------------------------------------------<br /><br />Predicate Information (identified by operation id):<br />---------------------------------------------------<br /><br /> 2 - filter(("B"."SOME_DATE"<'25-JAN-08'))<br /><br /><br />21 rows selected.<br /></pre></span><br />As you can see, you should first identify the SID of the session running the SQL sentence you're interested in, and with dbms_xplan.display_cursor function you can get the sentence and the actual execution plan used.<br /><br />On the other hand, if you want to check the execution plan of certain query that were executed recently you might try to find its SQL_ID in v$sqlarea; remember that SQL_TEXT is case-sensitive:<br /><span style="font-size:75%;"><pre><br />SQL> column SQL_TEXT format a80<br />SQL> select SQL_ID, SQL_TEXT from v$sqlarea where upper(SQL_TEXT) like '%DBA_OBJECTS%';<br /><br />SQL_ID SQL_TEXT<br />------------- --------------------------------------------------------------------------------<br />grq6tgwtun603 select SQL_ID, SQL_TEXT from v$sqlarea where upper(SQL_TEXT) like '%DBA_OBJECTS%<br /> '<br /><br />dfg3s1x621u6b select /*+ PARALLEL(b,4) */ count(*) from dba_objects b where OWNER='SYS'<br />cgyvau8fub4rq /* OracleOEM */ SELECT P.OBJECT_NAME,O.OBJECT_TYPE,P.OBJECT_OWNER,P.POLICY_NAM<br /> E,P.POLICY_GROUP,P.ENABLE FROM DBA_POLICIES P, DBA_OBJECTS O WHERE P.OBJECT_NAME<br /> =O.OBJECT_NAME<br /><br /><br />SQL> SELECT p.plan_table_output FROM table(dbms_xplan.display_cursor('dfg3s1x621u6b')) p;<br /><br />PLAN_TABLE_OUTPUT<br />----------------------------------------------------------------------------------------------------------------------------------<br />SQL_ID dfg3s1x621u6b, child number 0<br />-------------------------------------<br />select /*+ PARALLEL(b,4) */ count(*) from dba_objects b where OWNER='SYS'<br /><br />Plan hash value: 3694869755<br /><br />----------------------------------------------------------------------------------------------------------------------------------<br />| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | TQ |IN-OUT| PQ Distrib |<br />----------------------------------------------------------------------------------------------------------------------------------<br />| 0 | SELECT STATEMENT | | | | 25 (100)| | | | |<br />| 1 | SORT AGGREGATE | | 1 | 17 | | | | | |<br />| 2 | VIEW | DBA_OBJECTS | 104 | 1768 | 25 (0)| 00:00:01 | | | |<br />| 3 | UNION-ALL | | | | | | | | |<br />|* 4 | FILTER | | | | | | | | |<br />| 5 | PX COORDINATOR | | | | | | | | |<br />| 6 | PX SEND QC (RANDOM) | :TQ10001 | 122 | 10736 | 24 (0)| 00:00:01 | Q1,01 | P->S | QC (RAND) |<br />| 7 | NESTED LOOPS | | 122 | 10736 | 24 (0)| 00:00:01 | Q1,01 | PCWP | |<br />| 8 | BUFFER SORT | | | | | | Q1,01 | PCWC | |<br />| 9 | PX RECEIVE | | | | | | Q1,01 | PCWP | |<br />| 10 | PX SEND BROADCAST | :TQ10000 | | | | | | S->P | BROADCAST |<br />| 11 | TABLE ACCESS BY INDEX ROWID| USER$ | 1 | 14 | 1 (0)| 00:00:01 | | | |<br />|* 12 | INDEX UNIQUE SCAN | I_USER1 | 1 | | 0 (0)| | | | |<br />| 13 | PX BLOCK ITERATOR | | 122 | 9028 | 23 (0)| 00:00:01 | Q1,01 | PCWC | |<br />|* 14 | TABLE ACCESS FULL | OBJ$ | 122 | 9028 | 23 (0)| 00:00:01 | Q1,01 | PCWP | |<br />|* 15 | TABLE ACCESS BY INDEX ROWID | IND$ | 1 | 8 | 2 (0)| 00:00:01 | | | |<br />|* 16 | INDEX UNIQUE SCAN | I_IND1 | 1 | | 1 (0)| 00:00:01 | | | |<br />| 17 | NESTED LOOPS | | 1 | 27 | 1 (0)| 00:00:01 | | | |<br />| 18 | TABLE ACCESS BY INDEX ROWID | USER$ | 1 | 14 | 1 (0)| 00:00:01 | | | |<br />|* 19 | INDEX UNIQUE SCAN | I_USER1 | 1 | | 0 (0)| | | | |<br />|* 20 | INDEX RANGE SCAN | I_LINK1 | 1 | 13 | 0 (0)| | | | |<br />----------------------------------------------------------------------------------------------------------------------------------<br /><br />Predicate Information (identified by operation id):<br />---------------------------------------------------<br /><br /> 4 - filter((("O"."TYPE#"1 AND "O"."TYPE#"10) OR ("O"."TYPE#"=1 AND =1)))<br /> 12 - access("U"."NAME"='SYS')<br /> 14 - access(:Z>=:Z AND :Z<=:Z)<br /> filter(("O"."NAME"'_NEXT_OBJECT' AND "O"."NAME"'_default_auditing_options_' AND "O"."LINKNAME" IS NULL AND<br /> BITAND("O"."FLAGS",128)=0 AND "O"."OWNER#"="U"."USER#"))<br /> 15 - filter(("I"."TYPE#"=1 OR "I"."TYPE#"=2 OR "I"."TYPE#"=3 OR "I"."TYPE#"=4 OR "I"."TYPE#"=6 OR "I"."TYPE#"=7 OR<br /> "I"."TYPE#"=9))<br /> 16 - access("I"."OBJ#"=:B1)<br /> 19 - access("U"."NAME"='SYS')<br /> 20 - access("L"."OWNER#"="U"."USER#")<br /><br /><br />46 rows selected.<br /></pre></span><br />Also, if you're executing one by one SQL sentences you can get the execution plan calling dbms_xplan.display_cursor with no parameters immediately after the SQL to analize:<br /><span style="font-size:75%;"><pre><br />SQL> select /*+ PARALLEL(b,4) */ count(*) from dba_objects b where OWNER='SYS';<br /><br /> COUNT(*)<br />----------<br /> 7107<br /><br />SQL> SELECT p.plan_table_output FROM table(dbms_xplan.display_cursor()) p;<br /><br />PLAN_TABLE_OUTPUT<br />----------------------------------------------------------------------------------------------------------------------------------<br />SQL_ID dfg3s1x621u6b, child number 0<br />-------------------------------------<br />select /*+ PARALLEL(b,4) */ count(*) from dba_objects b where OWNER='SYS'<br /><br />Plan hash value: 3694869755<br /><br />----------------------------------------------------------------------------------------------------------------------------------<br />| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | TQ |IN-OUT| PQ Distrib |<br />----------------------------------------------------------------------------------------------------------------------------------<br />| 0 | SELECT STATEMENT | | | | 25 (100)| | | | |<br />| 1 | SORT AGGREGATE | | 1 | 17 | | | | | |<br />| 2 | VIEW | DBA_OBJECTS | 104 | 1768 | 25 (0)| 00:00:01 | | | |<br />| 3 | UNION-ALL | | | | | | | | |<br />|* 4 | FILTER | | | | | | | | |<br />| 5 | PX COORDINATOR | | | | | | | | |<br />| 6 | PX SEND QC (RANDOM) | :TQ10001 | 122 | 10736 | 24 (0)| 00:00:01 | Q1,01 | P->S | QC (RAND) |<br />| 7 | NESTED LOOPS | | 122 | 10736 | 24 (0)| 00:00:01 | Q1,01 | PCWP | |<br />| 8 | BUFFER SORT | | | | | | Q1,01 | PCWC | |<br />| 9 | PX RECEIVE | | | | | | Q1,01 | PCWP | |<br />| 10 | PX SEND BROADCAST | :TQ10000 | | | | | | S->P | BROADCAST |<br />| 11 | TABLE ACCESS BY INDEX ROWID| USER$ | 1 | 14 | 1 (0)| 00:00:01 | | | |<br />|* 12 | INDEX UNIQUE SCAN | I_USER1 | 1 | | 0 (0)| | | | |<br />| 13 | PX BLOCK ITERATOR | | 122 | 9028 | 23 (0)| 00:00:01 | Q1,01 | PCWC | |<br />|* 14 | TABLE ACCESS FULL | OBJ$ | 122 | 9028 | 23 (0)| 00:00:01 | Q1,01 | PCWP | |<br />|* 15 | TABLE ACCESS BY INDEX ROWID | IND$ | 1 | 8 | 2 (0)| 00:00:01 | | | |<br />|* 16 | INDEX UNIQUE SCAN | I_IND1 | 1 | | 1 (0)| 00:00:01 | | | |<br />| 17 | NESTED LOOPS | | 1 | 27 | 1 (0)| 00:00:01 | | | |<br />| 18 | TABLE ACCESS BY INDEX ROWID | USER$ | 1 | 14 | 1 (0)| 00:00:01 | | | |<br />|* 19 | INDEX UNIQUE SCAN | I_USER1 | 1 | | 0 (0)| | | | |<br />|* 20 | INDEX RANGE SCAN | I_LINK1 | 1 | 13 | 0 (0)| | | | |<br />----------------------------------------------------------------------------------------------------------------------------------<br /><br />Predicate Information (identified by operation id):<br />---------------------------------------------------<br /><br /> 4 - filter((("O"."TYPE#"1 AND "O"."TYPE#"10) OR ("O"."TYPE#"=1 AND =1)))<br /> 12 - access("U"."NAME"='SYS')<br /> 14 - access(:Z>=:Z AND :Z<=:Z)<br /> filter(("O"."NAME"'_NEXT_OBJECT' AND "O"."NAME"'_default_auditing_options_' AND "O"."LINKNAME" IS NULL AND<br /> BITAND("O"."FLAGS",128)=0 AND "O"."OWNER#"="U"."USER#"))<br /> 15 - filter(("I"."TYPE#"=1 OR "I"."TYPE#"=2 OR "I"."TYPE#"=3 OR "I"."TYPE#"=4 OR "I"."TYPE#"=6 OR "I"."TYPE#"=7 OR<br /> "I"."TYPE#"=9))<br /> 16 - access("I"."OBJ#"=:B1)<br /> 19 - access("U"."NAME"='SYS')<br /> 20 - access("L"."OWNER#"="U"."USER#")<br /><br /><br />46 rows selected.<br /></pre></span><br />This can be useful even if you're debugging an SQL script, but if you need to get execution plans of SQL sentences placed in a PL/SQL script then you need to print the execution plan line by line:<br /><span style="font-size:75%;"><pre><br />SQL> SET TERMOUT ON<br />SQL> SET PAGESIZE 0<br />SQL> SET SERVEROUTPUT ON<br />SQL> SET LINESIZE 200<br /><br />SQL> declare<br />v_count number;<br />v_plan varchar2(200);<br />CURSOR c1 IS SELECT p.plan_table_output FROM table(dbms_xplan.display_cursor()) p;<br /><br />begin<br /> select /*+ PARALLEL(b,4) */ count(*) into v_count from dba_objects b where OWNER='SYS';<br /> dbms_output.put_line ('Number of rows: '||v_count); <br /><br /> OPEN c1;<br /> LOOP<br /> FETCH c1 INTO v_plan;<br /> EXIT WHEN c1%NOTFOUND;<br /> dbms_output.put_line (v_plan); <br /> END LOOP;<br /> CLOSE c1;<br />end;<br />/<br /><br />Number of rows: 7107<br />SQL_ID dfg3s1x621u6b, child number 0<br />-------------------------------------<br />select /*+ PARALLEL(b,4) */ count(*) from dba_objects b where OWNER='SYS'<br /><br />Plan hash value: 3694869755<br /><br />----------------------------------------------------------------------------------------------------------------------------------<br />| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | TQ |IN-OUT| PQ Distrib |<br />----------------------------------------------------------------------------------------------------------------------------------<br />| 0 | SELECT STATEMENT | | | | 25 (100)| | | | |<br />| 1 | SORT AGGREGATE | | 1 | 17 | | | | | |<br />| 2 | VIEW | DBA_OBJECTS | 104 | 1768 | 25 (0)| 00:00:01 | | | |<br />| 3 | UNION-ALL | | | | | | | | |<br />|* 4 | FILTER | | | | | | | | |<br />| 5 | PX COORDINATOR | | | | | | | | |<br />| 6 | PX SEND QC (RANDOM) | :TQ10001 | 122 | 10736 | 24 (0)| 00:00:01 | Q1,01 | P->S | QC (RAND) |<br />| 7 | NESTED LOOPS | | 122 | 10736 | 24 (0)| 00:00:01 | Q1,01 | PCWP | |<br />| 8 | BUFFER SORT | | | | | | Q1,01 | PCWC | |<br />| 9 | PX RECEIVE | | | | | | Q1,01 | PCWP | |<br />| 10 | PX SEND BROADCAST | :TQ10000 | | | | | | S->P | BROADCAST |<br />| 11 | TABLE ACCESS BY INDEX ROWID| USER$ | 1 | 14 | 1 (0)| 00:00:01 | | | |<br />|* 12 | INDEX UNIQUE SCAN | I_USER1 | 1 | | 0 (0)| | | | |<br />| 13 | PX BLOCK ITERATOR | | 122 | 9028 | 23 (0)| 00:00:01 | Q1,01 | PCWC | |<br />|* 14 | TABLE ACCESS FULL | OBJ$ | 122 | 9028 | 23 (0)| 00:00:01 | Q1,01 | PCWP | |<br />|* 15 | TABLE ACCESS BY INDEX ROWID | IND$ | 1 | 8 | 2 (0)| 00:00:01 | | | |<br />|* 16 | INDEX UNIQUE SCAN | I_IND1 | 1 | | 1 (0)| 00:00:01 | | | |<br />| 17 | NESTED LOOPS | | 1 | 27 | 1 (0)| 00:00:01 | | | |<br />| 18 | TABLE ACCESS BY INDEX ROWID | USER$ | 1 | 14 | 1 (0)| 00:00:01 | | | |<br />|* 19 | INDEX UNIQUE SCAN | I_USER1 | 1 | | 0 (0)| | | | |<br />|* 20 | INDEX RANGE SCAN | I_LINK1 | 1 | 13 | 0 (0)| | | | |<br />----------------------------------------------------------------------------------------------------------------------------------<br /><br />Predicate Information (identified by operation id):<br />---------------------------------------------------<br /><br /> 4 - filter((("O"."TYPE#"1 AND "O"."TYPE#"10) OR ("O"."TYPE#"=1 AND =1)))<br /> 12 - access("U"."NAME"='SYS')<br /> 14 - access(:Z>=:Z AND :Z<=:Z)<br /> filter(("O"."NAME"'_NEXT_OBJECT' AND "O"."NAME"'_default_auditing_options_' AND "O"."LINKNAME" IS NULL AND<br /> BITAND("O"."FLAGS",128)=0 AND "O"."OWNER#"="U"."USER#"))<br /> 15 - filter(("I"."TYPE#"=1 OR "I"."TYPE#"=2 OR "I"."TYPE#"=3 OR "I"."TYPE#"=4 OR "I"."TYPE#"=6 OR "I"."TYPE#"=7 OR<br /> "I"."TYPE#"=9))<br /> 16 - access("I"."OBJ#"=:B1)<br /> 19 - access("U"."NAME"='SYS')<br /> 20 - access("L"."OWNER#"="U"."USER#")<br /><br />PL/SQL procedure successfully completed.<br /></pre></span><br />Finally, it would be useful to know the real execution time of sentences you're running, and you can know that with the <span style="font-weight:bold;">set timing on</span> sentence:<br /><span style="font-size:75%;"><pre><br />SQL> set timing on<br />SQL> select /*+ PARALLEL(b,4) */ count(*) from dba_objects b where OWNER='SYS';<br /><br /> COUNT(*)<br />----------<br /> 7092<br /><br />Elapsed: 00:00:00.46<br /></pre></span><br />More information:<br /><br /><a href="http://www.orafaq.com/node/1420">Understanding Explain Plan</a><br /><a href="http://www.oracle-base.com/articles/9i/DBMS_XPLAN.php">DBMS_XPLAN : Display Oracle Execution Plans</a><br /><a href="http://docs.oracle.com/cd/B19306_01/server.102/b14211/ex_plan.htm">Using EXPLAIN PLAN</a>Cronoshttp://www.blogger.com/profile/15427827507143505689noreply@blogger.com0tag:blogger.com,1999:blog-4232775939402125807.post-20870945353264332962011-11-30T09:24:00.000-08:002011-12-26T12:47:16.829-08:00Monitoring a JVM in Grid ControlAs you might know, Oracle Enterprise Manager Grid Control lets you monitor and manage Oracle databases, listeners, instances and things like that as expected, but you can also monitor and manage other systems and software like JVM.<br /><br />Let's say that you want to monitor a custom service written in Java:<br /><span style="font-size:75%;"><pre><br />root@myserver # ps ax|grep mycustomserver<br /> 8945 ? Sl 11:04 /opt/mycustomserver/server -JVMargs /opt/mycustomserver/server.ini -n mycustomserver<br />14810 pts/2 S+ 0:00 grep mycustomserver<br /></pre></span><br />You can do it by means of JMX, setting JVM's jmxremote parameters as arguments or in a configuration file. You have to set at least jmxremote.port, there is no default value for it:<br /><span style="font-size:75%;"><pre><br />root@myserver # cat /opt/mycustomserver/server.ini|grep jmxremote<br />JVMOPTS3=-Dcom.sun.management.jmxremote.port=2055<br />JVMOPTS4=-Dcom.sun.management.jmxremote.authenticate=false<br />JVMOPTS5=-Dcom.sun.management.jmxremote.ssl=false<br /></pre></span><br />And if you don't know the application but want to try to monitor it, you can try as root to guess the monitoring port number with netstat:<br /><span style="font-size:75%;"><pre><br />root@myserver # netstat -tuanp|grep 8945|grep LISTEN<br />tcp 0 0 0.0.0.0:2055 0.0.0.0:* LISTEN 8945/mycustomserver<br />tcp 0 0 127.0.0.1:1852 0.0.0.0:* LISTEN 8945/mycustomserver<br /></pre></span><br />With the remote port number, and in case authentication is turned on with the corresponding user and password, you can add your Java application to Grid Control clicking in the agent link in Targets -> All Targets tab, and in the next page select JVM from the Add drop-down menu. In the Monitoring Configuration page you have to set at least the Machine name (would be 127.0.0.1) and the Admin Port number fields, and click the OK button.<br /><br />After adding your JVM properly to Oracle Enterprise Manager, you will be able to check in the All Metrics link a lot of interesting metrics, at least interesting if you have knowledge of Java.<br /><br />More information:<br /><br /><a href="http://docs.oracle.com/javase/1.5.0/docs/guide/management/agent.html">Monitoring and Management Using JMX</a><br /><a href="http://docs.oracle.com/javase/6/docs/technotes/guides/management/agent.html">Monitoring and Management Using JMX Technology</a>Cronoshttp://www.blogger.com/profile/15427827507143505689noreply@blogger.com0tag:blogger.com,1999:blog-4232775939402125807.post-86717697019660506632011-11-29T10:10:00.000-08:002011-12-20T13:58:00.998-08:00Installing an Enterprise Manager agent for a TimesTen databaseThis is a small howto about installing an Enterprise Manager 11g agent for a TimesTen 11 database; this howto assumes that you have already downloaded the agent in the client machine and configured the Management Plug-in for Oracle TimesTen In-Memory Database in your Enterprise Manager server.<br /><br />First, you have to configure a response file to do a silent installation; an installation with OUI should be easy enough to not have to cover it here. In the response directory below your agent staging directory you should find an additional_agent.rsp file, copy it and change at least this fields:<br /><span style="font-size:75%;"><pre><br />timesten@myserver $ diff additional_agent.rsp my.rsp<br />41,42c41,42<br />< SECURITY_UPDATES_VIA_MYORACLESUPPORT=<Value Unspecified><br />< DECLINE_SECURITY_UPDATES=<Value Unspecified><br />---<br />> SECURITY_UPDATES_VIA_MYORACLESUPPORT=FALSE<br />> DECLINE_SECURITY_UPDATES=TRUE<br />85c85<br />< ORACLE_AGENT_HOME_LOCATION=<Value Unspecified><br />---<br />> ORACLE_AGENT_HOME_LOCATION=/myagent<br />169,170c169,170<br />< OMS_HOST=<Value Unspecified><br />< OMS_PORT=<Value Unspecified><br />---<br />> OMS_HOST=myoemserver<br />> OMS_PORT=4889<br /></pre></span><br />Next, launch runInstaller with the full path of your response file:<br /><span style="font-size:75%;"><pre><br />timesten@myserver $ cd /myagent/linux_x64/agent<br />timesten@myserver $ ./runInstaller -silent -responseFile /myagent/linux_x64/response/my.rsp<br /></pre></span><br />This way you will install an agent in /myagent directory; don't forget to run as root the root.sh configuration script after finishing runInstaller:<br /><span style="font-size:75%;"><pre><br />root@myserver # /myagent/agent11g/root.sh<br /></pre></span><br />If you want to check if the agent is running and communicating with the Grid Control server, run emctl and pay attention to the <span style="font-weight:bold;">Last successful upload</span> message:<br /><span style="font-size:75%;"><pre><br />timesten@myserver $ cd /myagent/agent11g/bin/<br />timesten@myserver $ ./emctl status agent<br />Oracle Enterprise Manager 11g Release 1 Grid Control 11.1.0.1.0 <br />Copyright (c) 1996, 2010 Oracle Corporation. All rights reserved.<br />---------------------------------------------------------------<br />Agent Version : 11.1.0.1.0<br />OMS Version : 11.1.0.1.0<br />Protocol Version : 11.1.0.0.0<br />Agent Home : /myagent/agent11g<br />Agent binaries : /myagent/agent11g<br />Agent Process ID : 5493<br />Parent Process ID : 14892<br />Agent URL : http://myserver:3872/emd/main/<br />Repository URL : http://myoemserver:4889/em/upload/<br />Started at : 2011-11-29 09:48:12<br />Started by user : timesten<br />Last Reload : 2011-11-29 11:50:40<br />Last successful upload : 2011-11-29 12:53:25<br />Total Megabytes of XML files uploaded so far : 13.84<br />Number of XML files pending upload : 0<br />Size of XML files pending upload(MB) : 0.00<br />Available disk space on upload filesystem : 66.57%<br />Last successful heartbeat to OMS : 2011-11-29 12:53:29<br />---------------------------------------------------------------<br />Agent is Running and Ready<br /></pre></span><br />If you need to change the configuration of the agent for some reason then stop the agent, edit the emd.properties and targets.xml files, and optionally delete previous files:<br /><span style="font-size:75%;"><pre><br />timesten@myserver $ cd /myagent/agent11g/bin<br />timesten@myserver $ ./emctl stop agent<br />Oracle Enterprise Manager 11g Release 1 Grid Control 11.1.0.1.0 <br />Copyright (c) 1996, 2010 Oracle Corporation. All rights reserved.<br />Stopping agent ... stopped.<br />timesten@myserver $ cd ..<br />timesten@myserver $ vi sysman/config/emd.properties<br />timesten@myserver $ vi sysman/emd/targets.xml<br />timesten@myserver $ cd sysman/emd<br />timesten@myserver $ rm -rf recv/* protocol.ini lastupld.xml collection/* state/* cputrack/* agntstmp.txt upload/* ../log/* emagent_storage.config core* sqlnet.log *sql<br />timesten@myserver $ cd /myagent/agent11g/bin<br />timesten@myserver $ ./emctl start agent<br />Oracle Enterprise Manager 11g Release 1 Grid Control 11.1.0.1.0 <br />Copyright (c) 1996, 2010 Oracle Corporation. All rights reserved.<br />Starting agent ..... started.<br /></pre></span><br />After these steps you have to be able to see the host and its agent in Grid Control at Targets tab, All Targets option. But in order to add a new TimesTen database to Enterprise Manager you have to first set the agent's password at Preferences -> Preferred Credentials -> Agent's Set Credentials icon; in that page you have to find your new agent and fill in the Host Username and Host Password fields. Next you will go to the Setup -> Management Plug-ins page and click the deploy icon of TimesTen management plug-in, in the next page add the new agent and click the Next button, and finally the Finish button in the last page.<br /><br />Now you'll be able to add the TimesTen database clicking in the agent link in Targets page; it might be a delay to appear the TimesTen In Memory Database 11g option in the Add drop-down menu so take this into account. In that page you have to select the TimesTen In Memory Database 11g option from the Add menu, and then fill in the TimesTen instance name, Data Source Name, TimesTen user name and TimesTen password fields. Finally, click the Test Connection button and the OK button to complete the operation.<br /><br />And if you're new to TimesTen and have no idea what the instance name and data source name would be, but the TimesTen user has the TT_HOME environment variable set, there is only one database at the server, and you know the username and password's database, you could guess TimesTen instance name and data source name this way:<br /><span style="font-size:75%;"><pre><br />timesten@myserver $ basename $TT_HOME<br /><span style="font-weight:bold;">tt1121</span><br />timesten@myserver $ ttstatus | grep Data<br />Data store /TimesTen/DataStore/MyDataStore/<span style="font-weight:bold;">MyDataStore</span><br /></pre></span><br />More information:<br /><br /><a href="http://docs.oracle.com/cd/E11857_01/install.111/e15838/appdx_hardware_reqs.htm#CACJBDGF">Meeting Hardware Requirements</a><br /><a href="http://docs.oracle.com/cd/E11857_01/install.111/e15838/appdx_package_reqs.htm#BABIJAJB">Meeting Package, Kernel Parameter, and Library Requirements</a>Cronoshttp://www.blogger.com/profile/15427827507143505689noreply@blogger.com0tag:blogger.com,1999:blog-4232775939402125807.post-66437670163496031222011-11-28T09:25:00.000-08:002011-11-28T10:16:00.210-08:00About the RHCSA certificationFrom time to time there are people that ask me how I got the OCP certification and I regret not having posted about it, therefore now that I'm a shiny, brand-new Red Hat Certified System Administrator I would like to write about this.<br /><br />First of all, this is a practical exam so if you enjoy taking multiple choice quizzes this is not the case. You can check all the information about this test in the <a href="https://www.redhat.com/certification/rhcsa/">Red Hat Certified System Administrator</a> page. Something worth of reading it is the <a href="https://www.redhat.com/certification/rhcsa/objectives/">RHCSA Exam Objectives</a> page, mandatory if you choose to take the test without enrolling into <a href="https://www.redhat.com/courses/ex200_rhcsa_exam/">Red Hat recommended courses</a>. By the way, if you are unsure if you have the level required for passing the test without enrolling into a course, it would be a good idea to take the <a href="https://www.redhat.com/explore/pre-assessment/">pre-assessment quiz</a>; you have to fill in a form with personal information in order to take the quiz, but if you take the certification test you'll have to give it anyway.<br /><br />I prefer a lot to study a book and practice with my home computers, and for this test I studied the <a href="http://books.google.com/books?id=wSKQz503C5MC&dq=rhce+red+hat+certified+engineer+linux+study+guide&hl=en&ei=osjTTp-2LNK9tgfq5dm5DQ&sa=X&oi=book_result&ct=result&resnum=1&ved=0CC8Q6AEwAA">RHCE Red Hat Certified Engineer Linux Study Guide</a>, by Michael Jang. Talking about other certification book, I read some time ago a review that blasted that book because there were some things that weren't covered in the book that were in the test, and unfortunately this is the case for this book too. But taking into account that no book can just put the exact test with its answers and that at this time I don't know a guide for studying for the new Red Hat Enterprise Linux 6 exams, I think that Jang's book is a good one for preparing for the RHCSA exam. I recommend reading the whole book and PRACTICING its exercises, even if this book is intended to be a guide for the (now obsolete) RHCT and RHCE exams; if you study the RHCE part you'll be a lot more confident while taking the test than if you just study the RHCT part.<br /><br />Finally, as any good certification test, it's aimed to check your skills and knowledge about a concrete field, therefore having experience in this topics will help a lot and would be decisive in passing the test; if you don't have experience in some topics try at lest to practice it a lot before taking the test.<br /><br />I think that holding a certification is a good way to back your skills and studying for the test is a good opportunity to keep up to date your knowledge, therefore I wish good luck for everybody that decide to take the RHCSA test.Cronoshttp://www.blogger.com/profile/15427827507143505689noreply@blogger.com0tag:blogger.com,1999:blog-4232775939402125807.post-69753932262273577492011-11-18T09:45:00.000-08:002011-11-18T10:41:03.196-08:00OUI in a Linux ItaniumAs you might know, runInstaller is the script you run for launching OUI for applying patches and installing Oracle, and usually you don't have to care about it unless you're running it in an Itanium server.<br /><br />Lets say that you want to apply some patch:<br /><span style="font-size:75%;"><pre><br />oracle@myserver$ ./runInstaller <br />Starting Oracle Universal Installer...<br /><br />Checking installer requirements...<br /><br />Checking operating system version: must be redhat-3, SuSE-9, SuSE-10, SuSE-11, redhat-4, redhat-5, UnitedLinux-1.0, asianux-1, asianux-2 or asianux-3<br /> Passed<br /><br /><br />All installer requirements met.<br /><br />Preparing to launch Oracle Universal Installer from /tmp/OraInstall2011-11-18_12-09-55PM. Please wait ...<br />oracle@myserver$ <br /></pre></span><br />And after launching runInstaller you wait forever for the Oracle Universal Installer and it never shows, and in an eureka moment you decide to check the java distributed with the patch:<br /><span style="font-size:75%;"><pre><br />oracle@myserver$ java -version<br />java version "1.4.2"<br />gcj (GCC) 3.4.6 20060404 (Red Hat 3.4.6-9)<br />Copyright (C) 2006 Free Software Foundation, Inc.<br />This is free software; see the source for copying conditions. There is NO<br />warranty; not even for MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.<br /><br />oracle@myserver$ /tmp/OraInstall2011-11-18_12-09-55PM/jre/1.4.2/bin/java -version<br /><br />Segmentation fault<br /></pre></span><br />As documented in Metalink note <a href="https://support.oracle.com/CSP/main/article?cmd=show&type=NOT&id=391844.1">391844.1</a>, there is a bug in the JDK version bundled with the installer so you need to install another java distribution. Metalink note <a href="https://support.oracle.com/CSP/main/article?cmd=show&type=NOT&id=400227.1">400227.1</a> lists different methods according to your Oracle version and operating system so for example, in case of installing Oracle patch 10.2.0.4 in a Linux Itanium server, you have to download patch 5390722 and after installing it, you can launch OUI more or less this way:<br /><span style="font-size:75%;"><pre><br />./runInstaller -jreLoc $ORACLE_HOME/jre<br /></pre></span><br />More information:<br /><br /><a href="http://www.linuxquestions.org/questions/linux-server-73/oracle-10g-r2-installation-problem-585723/">Oracle 10g R2 Installation Problem</a>Cronoshttp://www.blogger.com/profile/15427827507143505689noreply@blogger.com0tag:blogger.com,1999:blog-4232775939402125807.post-5113630337569669622011-10-26T09:01:00.000-07:002011-10-26T10:13:15.273-07:00Diagnosing ORA-00600 and ORA-07445 errorsWhen I'm unlucky enough to find an ORA-600/ORA-7445 error in one database I'm tasked to manage, I like a lot using Google to find practical, real solutions to this kind of problems, and almost all the time I got a plain explanation of the problem and a precise solution or document to look for the solution. The problem is, if you have to justify your solution you cannot simply say "This is the way because I say so", or "I think the guy of that forum is correct because I feel so".<br /><br />Therefore, if you need an official document (Oracle) to back your diagnostic or you enjoy a lot using heavy Flash pages then you could try to find information about these errors in Metalink. But first, you will have to gather information about the error in the alert log file and trace files generated:<br /><span style="font-size:75%;"><pre><br />$ more alert_mydb.log<br /> ...skipping...<br />Wed Oct 13 11:56:33 2008<br />Errors in file /oracle/admin/mydb/udump/mydb_ora_11180.trc:<br />ORA-00600: internal error code, arguments: [736], [7], [0], [128], [128], [], [], []<br />Wed Oct 13 11:56:35 2008<br />Errors in file /oracle/admin/mydb/udump/mydb_ora_11180.trc:<br />ORA-07445: exception encountered: core dump [kxsbndinf()+604] [SIGSEGV] [unknown code] [0x100010001] [] []<br />ORA-00600: internal error code, arguments: [736], [7], [0], [128], [128], [], [], []<br />Wed Oct 13 11:56:36 2008<br />Errors in file /oracle/admin/mydb/udump/mydb_ora_11180.trc:<br />ORA-07445: exception encountered: core dump [kxsbndinf()+604] [SIGSEGV] [unknown code] [0x100010001] [] []<br />ORA-07445: exception encountered: core dump [kxsbndinf()+604] [SIGSEGV] [unknown code] [0x100010001] [] []<br />ORA-00600: internal error code, arguments: [736], [7], [0], [128], [128], [], [], []<br /><br />$ more /oracle/admin/mydb/udump/mydb_ora_11180.trc<br />/oracle/admin/mydb/udump/mydb_ora_11180.trc<br />Oracle9i Enterprise Edition Release 9.2.0.5.0 - 64bit Production<br />With the Partitioning option<br />JServer Release 9.2.0.5.0 - Production<br />ORACLE_HOME = /oracle/product/9.2<br />System name: HP-UX<br />Node name: myserver<br />Release: B.11.11<br />Version: U<br />Machine: 9000/800<br />Instance name: mydb<br />Redo thread mounted by this instance: 1<br />Oracle process number: 28<br />Unix process pid: 11180, image: oracle@myserver (TNS V1-V3)<br /><br />*** SESSION ID:(31.12323) 2008-10-13 11:56:33.939<br />*** 2008-10-13 11:56:33.939<br />ksedmp: internal or fatal error<br />ORA-00600: internal error code, arguments: [736], [7], [0], [128], [128], [], [], []<br />Current SQL statement for this session:<br />INSERT INTO MYTABLE (FIELD1, FIELD2, FIELD3) VALUES (SYSDATE, :B1, :B2)<br />----- PL/SQL Call Stack -----<br /> object line object<br /> handle number name<br />c0000000aecb8208 14 function MYUSER.MYFUNCTION1<br />c0000000ae910e40 58 function MYUSER.MYFUNCTION2<br />c0000000ae91a908 1 anonymous block<br />----- Call Stack Trace -----<br />calling call entry argument values in hex <br />location type point (? means dubious value) <br />-------------------- -------- -------------------- ----------------------------<br />ksedmp()+184 ? ksedst() 4000000002D91857 ?<br /> 000000004 ?<br /> C0000000ADD000E8 ?<br /> 000000000 ?<br />ksfdmp()+32 ? ksedmp() C0000000B162AD60 ?<br /> 800003FA00054A68 ?<br /> 000000007 ?<br /> C0000000B1628A40 ?<br />kgeriv()+152 ? ksfdmp() 000000007 ?<br /> C0000000B1628A40 ?<br /> 40000000011979C3 ?<br /> 800000010000B980 ?<br />kgeasi()+172 ? kgeriv() 800000010000B980 ?<br /> 800000010000B8A8 ?<br /> 800000010000B3D8 ?<br /> 80000001000DA060 ?<br />kxsrbb()+1972 ? kgeasi() C0000000B162AD60 ?<br /> 800003FFC0001D20 ?<br /> 000115464 ? 000000030 ?<br />kxsxsi()+668 ? kxsrbb() 800000000000000 ?<br /> 4000000000844158 ?<br /> 000008108 ?<br /> 40000000004E55F0 ?<br />opitca()+5140 ? kxsxsi() 800000000000000 ?<br /> 80000001000DB8B0 ?<br /> 800003FFC00010C8 ?<br /> C0000000B162A99C ?<br />kkslod()+2932 ? opitca() 80000001000C10B0 ?<br /> 800003F980068828 ?<br /> C0000000AC3B3D50 ?<br /> 000000000 ?<br />kglobld()+1516 ? kkslod() 80000001000D90B8 ?<br /> 400000000071C2A0 ?<br /> DD40F440010385D0 ?<br /> 000000000 ?<br />kglobpn()+1156 ? kglobld() 00000009C ? 000000000 ?<br /> 800003FFBFFFD4C0 ?<br /></pre></span><br />If you haven't done so, read the <a href="https://support.oracle.com/CSP/main/article?cmd=show&type=NOT&id=1092832.1">Master Note for Diagnosing ORA-600</a> Metalink document to understand the basics of diagnosing errors, and if you read carefully you will notice a nice tool called <a href="https://support.oracle.com/CSP/main/article?cmd=show&type=NOT&id=153788.1">ORA-600/ORA-7445 Error Look-up</a>; it would be better to use the <a href="https://support.oracle.com/CSP/ui/flash.html#tab=KBHome%28page=KBHome&id=%28%29%29,%28page=KBNavigator&id=%28bmDocTitle=ORA-600/ORA-7445%20Error%20Look-up%20Tool&bmDocID=153788.1&bmDocType=TROUBLESHOOTING&bmDocDsrc=KB&viewingMode=1143&from=BOOKMARK%29%29">Flash version</a> of this document in order to have full features available.<br /><br />With this tool you will have two options to look for errors, the first one I recommend to try is <span style="font-weight:bold;">Look-up By Error or Error Code's First Argument</span>. In this box you have to select the error code (ORA-600/ORA-7445), enter the error code arguments in order (like <span style="font-weight:bold;">[736], [7], [0], [128]</span>), and select your database version if apply. The more arguments you input the more precise diagnostic you might get, but if you get nothing then try to enter less arguments.<br /><br />If you are not sure about this result, try the <span style="font-weight:bold;">Search by Stack Trace</span> option. There you have to enter the stack trace below the dashed line; again, the more stack trace you input the more precise diagnostic you might get, but if you get nothing then try to enter less stack trace.<br /><br />That's it! This way you can search for information about Oracle bugs in Metalink less painfully; hope you don't have to use this tool too often.Cronoshttp://www.blogger.com/profile/15427827507143505689noreply@blogger.com0tag:blogger.com,1999:blog-4232775939402125807.post-21293888945617527142011-10-25T10:19:00.000-07:002011-10-25T10:41:38.347-07:00Getting partition's sizeGetting the size of a partition in Linux is very simple if you're logged in as root:<br /><span style="font-size:75%;"><pre><br />olimpo:~# fdisk -l /dev/sda<br /><br />Disk /dev/sda: 160.0 GB, 160000000000 bytes<br />255 heads, 63 sectors/track, 19452 cylinders<br />Units = cylinders of 16065 * 512 = 8225280 bytes<br />Sector size (logical/physical): 512 bytes / 512 bytes<br />I/O size (minimum/optimal): 512 bytes / 512 bytes<br />Disk identifier: 0xabd592d5<br /><br /> Device Boot Start End Blocks Id System<br />/dev/sda1 * 1 1567 12586896 7 HPFS/NTFS<br />/dev/sda2 1568 2546 7863817+ 17 Hidden HPFS/NTFS<br />/dev/sda3 2547 2796 2008125 82 Linux swap / Solaris<br />/dev/sda4 2797 19452 133789320 83 Linux<br /></pre></span><br />But it might be that you're just a DBA with access only to some partitions instead of the whole disk, and you got this server with no idea about partition sizes. If this is the case, then you can still know the size of your partitions with fdisk:<br /><span style="font-size:75%;"><pre><br />oracle@olimpo:/myASM$ /sbin/fdisk -l /dev/sda<br />Cannot open /dev/sda<br />oracle@olimpo:/myASM$ ls -la<br />total 8<br />drwxr-xr-x 2 oracle dba 4096 Oct 25 12:15 .<br />drwxr-xr-x 113 root root 4096 Oct 25 12:15 ..<br />brw-rw---- 1 oracle dba 8, 4 Oct 3 08:44 sda4<br />oracle@olimpo:/myASM$ /sbin/fdisk -s sda4<br />133789320<br /></pre></span><br />In Solaris is a bit different as the -s flag is not the same as in Linux:<br /><span style="font-size:75%;"><pre><br />oracle@myserver:/myASM$ ls -la<br />total 4<br />drwxr-xr-x 2 oracle dba 512 Oct 22 2008 .<br />drwxr-xr-x 38 root root 1024 Oct 18 16:57 ..<br />crw-r--r-- 1 oracle dba 32, 24 Oct 22 2008 disk1<br />crw-r--r-- 1 oracle dba 32, 25 Oct 25 09:31 disk2<br />crw-r--r-- 1 oracle dba 32, 26 Oct 22 2008 disk3<br /><br />oracle@myserver:/myASM$ /sbin/fdisk -Rg disk2<br />* Label geometry for device disk2<br />* PCYL NCYL ACYL BCYL NHEAD NSECT SECSIZ<br /> 35135 35135 2 0 64 255 512 <br /><br />oracle@myserver:/myASM$ echo "(35135*64*255*512)/1024/1024"|bc<br />279982<br /></pre></span><br />With the last command you calculate the partition size in megabytes.Cronoshttp://www.blogger.com/profile/15427827507143505689noreply@blogger.com0tag:blogger.com,1999:blog-4232775939402125807.post-71554208716548616592011-10-18T10:58:00.000-07:002011-10-18T11:21:25.223-07:00Playing with ssh, xauth and DISPLAYIf you need to connect by ssh to an UNIX server and later change user (by means of su or otherwise), but you want to keep X11 forwarding to your screen, then look at this example using xauth and DISPLAY. The key is getting cookie information with xauth list and identifying the screen opened by ssh (usually screen 10 of the server), and after changing user pasting the whole line after the xauth add command:<br /><span style="font-size:75%;"><pre><br />myuser@olimpo:~$ ssh -X -l myuser myserver1<br />Password: <br />Last login: Mon Oct 17 18:00:46 2011 from olimpo<br />$ xclock<br />$ xauth list<br />myserver1.mynet.net:10 MIT-MAGIC-COOKIE-1 eb3713bfc73799efede775bf1a04cb19<br />$ su - oracle<br />Password: <br />myserver1:/home/oracle> xclock<br />Error: Can't open display: <br />myserver1:/home/oracle> xauth add myserver1.mynet.net:10 MIT-MAGIC-COOKIE-1 eb3713bfc73799efede775bf1a04cb19<br />myserver1:/home/oracle> export DISPLAY=myserver1.mynet.net:10<br />myserver1:/home/oracle> xclock<br />myserver1:/home/oracle> uname -n<br />myserver1<br />myserver1:/home/oracle><br /></pre></span><br />The uname -n command from above example was an extra for the following case: this time you try to login to other server but you get a xauth error message and are unable to forward any X11 window:<br /><span style="font-size:75%;"><pre><br />myuser@olimpo:~$ ssh -X -l oracle myserver2<br />Password: <br />/usr/openwin/bin/xauth: (stdin):1: bad display name "unix:10.0" in "add" command<br /><br />:/home/oracle> xclock<br />X11 connection rejected because of wrong authentication.<br />X connection to localhost:10.0 broken (explicit kill or server shutdown).<br />:/home/oracle> echo $DISPLAY<br />localhost:10.0<br />:/home/oracle> uname -n<br /><br />:/home/oracle> <br /></pre></span><br />As you might have noticed the DISPLAY variable seems correct, but there is a problem with the host name: there is no host name set; that's why xauth fails at login time. You have to set properly the host name according to your UNIX version in order to correct this problem.<br /><br />More information:<br /><br /><a href="http://www.debian-administration.org/articles/494">Getting X11 forwarding through ssh working after running su</a>Cronoshttp://www.blogger.com/profile/15427827507143505689noreply@blogger.com0tag:blogger.com,1999:blog-4232775939402125807.post-30161063917681228612011-10-13T10:58:00.000-07:002011-10-13T11:26:49.345-07:00Getting HP-UX system informationIf you need to check information about your HP-UX server like hardware and filesystems, or even information about a high availability cluster (having root access), you can use the lanscan, ioscan, bdf and cmviewcl commands. Or if you're looking for information about memory usage you can check <a href="http://cronostech.blogspot.com/2011/04/checking-memory-usage-in-hp-ux.html">this post</a>.<br /><span style="font-size:75%;"><pre><br />$ /usr/sbin/lanscan<br />Hardware Station Crd Hdw Net-Interface NM MAC HP-DLPI DLPI<br />Path Address In# State NamePPA ID Type Support Mjr#<br />0/1/2/0 0x001635700001 0 UP lan0 snap0 1 ETHER Yes 119<br />0/3/1/0/6/0 0x001635300002 1 UP lan1 snap1 2 ETHER Yes 119<br />0/4/1/0/6/0 0x001635300003 2 UP lan2 snap2 3 ETHER Yes 119<br />$ /usr/sbin/ioscan -fnk<br />Class I H/W Path Driver S/W State H/W Type Description<br />=============================================================================<br />root 0 root CLAIMED BUS_NEXUS <br />ioa 0 0 sba CLAIMED BUS_NEXUS System Bus Adapter (880)<br />ba 0 0/0 lba CLAIMED BUS_NEXUS Local PCI-X Bus Adapter (783)<br />usb 0 0/0/1/0 hcd CLAIMED INTERFACE NEC USB Interface<br />usbhub 0 0/0/1/0.1 hub CLAIMED DEVICE USB Root Hub<br />usb 1 0/0/1/1 hcd CLAIMED INTERFACE NEC USB Interface<br />usbhub 1 0/0/1/1.1 hub CLAIMED DEVICE USB Root Hub<br /> /dev/hub<br />usb 2 0/0/1/2 ehci CLAIMED INTERFACE NEC USB Interface<br />sideba 0 0/0/2/0 side_ba CLAIMED INTERFACE CMD IDE controller<br />ext_bus 0 0/0/2/0.0 side CLAIMED INTERFACE IDE Primary Channel<br />target 0 0/0/2/0.0.0 tgt CLAIMED DEVICE <br />disk 0 0/0/2/0.0.0.0 sdisk CLAIMED DEVICE TEAC DV-28E-N<br /> /dev/dsk/c0t0d0 /dev/rdsk/c0t0d0<br />target 1 0/0/2/0.0.7 tgt CLAIMED DEVICE <br />ctl 0 0/0/2/0.0.7.0 sctl CLAIMED DEVICE Initiator<br /> /dev/rscsi/c0t7d0<br />ext_bus 1 0/0/2/0.1 side CLAIMED INTERFACE IDE Secondary Channel<br />target 2 0/0/2/0.1.7 tgt CLAIMED DEVICE <br />ctl 1 0/0/2/0.1.7.0 sctl CLAIMED DEVICE Initiator<br /> /dev/rscsi/c1t7d0<br />ba 1 0/1 lba CLAIMED BUS_NEXUS Local PCI-X Bus Adapter (783)<br />ext_bus 2 0/1/1/0 c8xx CLAIMED INTERFACE SCSI C1010 Ultra160 Wide LVD<br />target 3 0/1/1/0.0 tgt CLAIMED DEVICE <br />disk 2 0/1/1/0.0.0 sdisk CLAIMED DEVICE HP 73.4GMAX3073NC<br /> /dev/dsk/c2t0d0 /dev/rdsk/c2t0d0<br />target 4 0/1/1/0.1 tgt CLAIMED DEVICE <br />disk 1 0/1/1/0.1.0 sdisk CLAIMED DEVICE HP 73.4GMAX3073NC<br /> /dev/dsk/c2t1d0 /dev/rdsk/c2t1d0<br />target 5 0/1/1/0.7 tgt CLAIMED DEVICE <br />ctl 2 0/1/1/0.7.0 sctl CLAIMED DEVICE Initiator<br /> /dev/rscsi/c2t7d0<br />ext_bus 3 0/1/1/1 c8xx CLAIMED INTERFACE SCSI C1010 Ultra160 Wide LVD<br />target 6 0/1/1/1.7 tgt CLAIMED DEVICE <br />ctl 3 0/1/1/1.7.0 sctl CLAIMED DEVICE Initiator<br /> /dev/rscsi/c3t7d0<br />lan 0 0/1/2/0 igelan CLAIMED INTERFACE HP PCI 1000Base-T Core<br />ba 2 0/2 lba CLAIMED BUS_NEXUS Local PCI-X Bus Adapter (783)<br />ba 3 0/3 lba CLAIMED BUS_NEXUS Local PCI-X Bus Adapter (783)<br />ba 4 0/3/1/0 PCItoPCI CLAIMED BUS_NEXUS PCItoPCI Bridge<br />fc 0 0/3/1/0/4/0 fcd CLAIMED INTERFACE HP 2Gb PCI/PCI-X Fibre Channel FC/GigE Dual Port Combo Adapter<br /> /dev/fcd0<br />fcp 0 0/3/1/0/4/0.8 fcd_fcp CLAIMED INTERFACE FCP Protocol Adapter<br />ext_bus 7 0/3/1/0/4/0.8.0.2.0 fcd_vbus CLAIMED INTERFACE FCP Array Interface<br />target 7 0/3/1/0/4/0.8.0.2.0.0 tgt CLAIMED DEVICE <br />disk 89 0/3/1/0/4/0.8.0.2.0.0.0 sdisk CLAIMED DEVICE COMPAQ MSA1000 VOLUME<br /> /dev/dsk/c7t0d0 /dev/rdsk/c7t0d0<br />disk 8 0/3/1/0/4/0.8.0.2.0.0.1 sdisk CLAIMED DEVICE COMPAQ MSA1000 VOLUME<br /> /dev/dsk/c7t0d1 /dev/rdsk/c7t0d1<br />disk 23 0/3/1/0/4/0.8.0.2.0.0.2 sdisk CLAIMED DEVICE COMPAQ MSA1000 VOLUME<br /> /dev/dsk/c7t0d2 /dev/rdsk/c7t0d2<br />disk 24 0/3/1/0/4/0.8.0.2.0.0.3 sdisk CLAIMED DEVICE COMPAQ MSA1000 VOLUME<br /> /dev/dsk/c7t0d3 /dev/rdsk/c7t0d3<br />ext_bus 4 0/3/1/0/4/0.8.0.255.0 fcd_vbus CLAIMED INTERFACE FCP Device Interface<br />target 8 0/3/1/0/4/0.8.0.255.0.2 tgt CLAIMED DEVICE <br />ctl 23 0/3/1/0/4/0.8.0.255.0.2.0 sctl CLAIMED DEVICE COMPAQ MSA1000<br /> /dev/rscsi/c4t2d0<br />lan 1 0/3/1/0/6/0 igelan CLAIMED INTERFACE HP A9784-60002 PCI/PCI-X 1000Base-T FC/GigE Combo Adapter<br />ba 5 0/4 lba CLAIMED BUS_NEXUS Local PCI-X Bus Adapter (783)<br />ba 6 0/4/1/0 PCItoPCI CLAIMED BUS_NEXUS PCItoPCI Bridge<br />fc 1 0/4/1/0/4/0 fcd CLAIMED INTERFACE HP 2Gb PCI/PCI-X Fibre Channel FC/GigE Dual Port Combo Adapter<br /> /dev/fcd1<br />fcp 1 0/4/1/0/4/0.8 fcd_fcp CLAIMED INTERFACE FCP Protocol Adapter<br />ext_bus 6 0/4/1/0/4/0.8.0.1.0 fcd_vbus CLAIMED INTERFACE FCP Array Interface<br />target 9 0/4/1/0/4/0.8.0.1.0.0 tgt CLAIMED DEVICE <br />disk 19 0/4/1/0/4/0.8.0.1.0.0.0 sdisk CLAIMED DEVICE COMPAQ MSA1000 VOLUME<br /> /dev/dsk/c6t0d0 /dev/rdsk/c6t0d0<br />disk 20 0/4/1/0/4/0.8.0.1.0.0.1 sdisk CLAIMED DEVICE COMPAQ MSA1000 VOLUME<br /> /dev/dsk/c6t0d1 /dev/rdsk/c6t0d1<br />disk 21 0/4/1/0/4/0.8.0.1.0.0.2 sdisk CLAIMED DEVICE COMPAQ MSA1000 VOLUME<br /> /dev/dsk/c6t0d2 /dev/rdsk/c6t0d2<br />disk 22 0/4/1/0/4/0.8.0.1.0.0.3 sdisk CLAIMED DEVICE COMPAQ MSA1000 VOLUME<br /> /dev/dsk/c6t0d3 /dev/rdsk/c6t0d3<br />ext_bus 5 0/4/1/0/4/0.8.0.255.0 fcd_vbus CLAIMED INTERFACE FCP Device Interface<br />target 10 0/4/1/0/4/0.8.0.255.0.1 tgt CLAIMED DEVICE <br />ctl 6 0/4/1/0/4/0.8.0.255.0.1.0 sctl CLAIMED DEVICE COMPAQ MSA1000<br /> /dev/rscsi/c5t1d0<br />lan 2 0/4/1/0/6/0 igelan CLAIMED INTERFACE HP A9784-60002 PCI/PCI-X 1000Base-T FC/GigE Combo Adapter<br />ba 7 0/6 lba CLAIMED BUS_NEXUS Local PCI-X Bus Adapter (783)<br />ba 8 0/7 lba CLAIMED BUS_NEXUS Local PCI-X Bus Adapter (783)<br />tty 0 0/7/1/0 asio0 CLAIMED INTERFACE PCI SimpleComm (103c1290)<br /> /dev/diag/mux0 /dev/mux0 /dev/tty0p0 <br />tty 1 0/7/1/1 asio0 CLAIMED INTERFACE PCI Serial (103c1048)<br /> /dev/GSPdiag1 /dev/diag/mux1 /dev/mux1 /dev/tty1p0 /dev/tty1p2 /dev/tty1p4 <br />unknown -1 0/7/2/0 UNCLAIMED UNKNOWN PCI Display (10025159)<br />memory 0 8 memory CLAIMED MEMORY Memory<br />ipmi 0 16 ipmi CLAIMED INTERFACE IPMI Controller<br /> /dev/ipmi<br />processor 0 128 processor CLAIMED PROCESSOR Processor<br />processor 1 129 processor CLAIMED PROCESSOR Processor<br />$ bdf -i<br />Filesystem kbytes used avail %used iused ifree %iuse Mounted on<br />/dev/vg00/lvol3 1048576 799824 246928 76% 2888 7768 27% /<br />/dev/vg00/lvol1 511672 54328 406176 12% 65 50111 0% /stand<br />/dev/vg00/lvol8 8388608 1890448 6447536 23% 23785 203063 10% /var<br />/dev/vg00/lvol7 2097152 1575968 517184 75% 31502 16274 66% /usr<br />/dev/vg00/lvol4 1048576 768632 278256 73% 357 8731 4% /tmp<br />/dev/vg00/lvol6 4194304 2195952 1982784 53% 32112 62448 34% /opt<br />/dev/vg00/lvol5 2097152 669912 1416104 32% 1512 44600 3% /home<br /># cmviewcl -v<br /><br />CLUSTER STATUS <br />my-cluster up <br /><br /> NODE STATUS STATE <br /> node-01 up running <br /><br /> Network_Parameters:<br /> INTERFACE STATUS PATH NAME <br /> PRIMARY up 0/1/2/0 lan0 <br /> PRIMARY up 0/3/1/0/6/0 lan1 <br /> STANDBY up 0/4/1/0/6/0 lan2 <br /><br /> PACKAGE STATUS STATE AUTO_RUN NODE <br /> mypkg up running enabled node-01 <br /><br /> Policy_Parameters:<br /> POLICY_NAME CONFIGURED_VALUE<br /> Failover configured_node<br /> Failback manual<br /><br /> Script_Parameters:<br /> ITEM STATUS MAX_RESTARTS RESTARTS NAME<br /> Service up 3 0 MYMONITOR <br /> Subnet up 10.0.1.0 <br /><br /> Node_Switching_Parameters:<br /> NODE_TYPE STATUS SWITCHING NAME <br /> Primary up enabled node-01 (current) <br /> Alternate up enabled node-02 <br /><br /> NODE STATUS STATE <br /> node-02 up running <br /><br /> Network_Parameters:<br /> INTERFACE STATUS PATH NAME <br /> PRIMARY up 0/1/2/0 lan0 <br /> PRIMARY up 0/3/1/0/6/0 lan1 <br /> STANDBY up 0/4/1/0/6/0 lan2 <br /></pre></span>Cronoshttp://www.blogger.com/profile/15427827507143505689noreply@blogger.com0tag:blogger.com,1999:blog-4232775939402125807.post-50209555665598039032011-10-10T14:20:00.000-07:002011-10-10T14:52:12.579-07:00Dropping old partitions with a scriptLets say that you created a partitioned table some time ago to hold a lot of data generated by day:<br /><span style="font-size:75%;"><pre><br />CREATE TABLE MY_TABLE<br />( <br />SOMETEXT VARCHAR2(4000),<br />MOREINFO VARCHAR2(256),<br />ADATE DATE<br />) PARTITION BY RANGE (ADATE)<br />INTERVAL (NUMTODSINTERVAL(1,'day'))<br />(partition p0 values less than (to_date('01-jan-2000','dd-mon-yyyy'))<br />);<br /></pre></span><br />This data is needed to generate some reports, but after some time it is not necessary anymore so you have to drop old partitions in order to not waste disk space. What a simple task, isn't it? You just have to look for information in USER_TAB_PARTITIONS and drop the oldest partitions:<br /><span style="font-size:75%;"><pre><br />SQL> select PARTITION_NAME, HIGH_VALUE from USER_TAB_PARTITIONS<br />where TABLE_NAME='MY_TABLE' and PARTITION_NAME!='P0';<br /><br />PARTITION_NAME HIGH_VALUE<br />------------------------------ --------------------------------------------------------------------------------<br />SYS_P193448 TO_DATE(' 2011-10-08 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA<br />SYS_P193449 TO_DATE(' 2011-10-09 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA<br />SYS_P193450 TO_DATE(' 2011-10-10 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA<br /><br />SQL> select PARTITION_NAME, HIGH_VALUE from USER_TAB_PARTITIONS where TABLE_NAME='MY_TABLE' and PARTITION_NAME!='P0' order by HIGH_VALUE;<br />select PARTITION_NAME, HIGH_VALUE from USER_TAB_PARTITIONS where TABLE_NAME='MY_TABLE' and PARTITION_NAME!='P0' order by HIGH_VALUE<br /> *<br />ERROR at line 1:<br />ORA-00997: illegal use of LONG datatype<br /><br /><br />SQL> select PARTITION_NAME, SUBSTR(HIGH_VALUE,11,19) from USER_TAB_PARTITIONS where TABLE_NAME='MY_TABLE' and PARTITION_NAME!='P0';<br />select PARTITION_NAME, SUBSTR(HIGH_VALUE,11,19) from USER_TAB_PARTITIONS where TABLE_NAME='MY_TABLE' and PARTITION_NAME!='P0'<br /> *<br />ERROR at line 1:<br />ORA-00932: inconsistent datatypes: expected NUMBER got LONG<br /></pre></span><br />What a problem; there is a lot of restrictions manipulating LONG datatypes so you cannot just get the date from the HIGH_VALUE column. For this task you'll need a PL/SQL script:<br /><span style="font-size:75%;"><pre><br />SQL> DECLARE<br /> v_sql varchar2(500);<br /> v_date date;<br /> v_partition_name user_tab_partitions.partition_name%TYPE;<br /> v_high_value user_tab_partitions.high_value%TYPE;<br /> CURSOR c1 IS select PARTITION_NAME, HIGH_VALUE<br /> from user_tab_partitions where table_name='MY_TABLE' and PARTITION_NAME!='P0';<br />BEGIN<br /> OPEN c1;<br /> LOOP<br /> FETCH c1 INTO v_partition_name, v_high_value;<br /> EXIT WHEN c1%NOTFOUND;<br /> v_date := to_date(SUBSTR(v_high_value,11,19),'YYYY-MM-DD HH24:MI:SS');<br /> IF v_date < (sysdate - 2) then<br /> v_sql := 'alter table MY_TABLE drop partition ' || v_partition_name;<br /> execute immediate v_sql;<br /> END IF;<br /> END LOOP;<br /> CLOSE c1;<br />END;<br />/<br /><br />PL/SQL procedure successfully completed.<br /><br />SQL> select PARTITION_NAME, HIGH_VALUE from USER_TAB_PARTITIONS<br />where TABLE_NAME='MY_TABLE' and PARTITION_NAME!='P0';<br /><br />PARTITION_NAME HIGH_VALUE<br />------------------------------ --------------------------------------------------------------------------------<br />SYS_P193449 TO_DATE(' 2011-10-09 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA<br />SYS_P193450 TO_DATE(' 2011-10-10 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA<br /></pre></span><br />With this script you can drop partitions from 2 days ago and older, and put it as a daily job in order to automate this task.Cronoshttp://www.blogger.com/profile/15427827507143505689noreply@blogger.com7