SQL> r
1* select to_char(sysdate,'DD.MON.YYYY HH24:MI:SS') from dual
select to_char(sysdate,'DD.MON.YYYY HH24:MI:SS') from dual
*
ERROR at line 1:
ORA-03135: connection lost contact
Elapsed: 00:05:22.63
Note the elapsed time of 5 mintes 22 seconds.
To debug enable a client side SQL*net trace and reproduce the error. In this case after several tests the error occured after 30 minutes.
Oracle 11.1+ database versions mask the "ORA-03135" error and return a result after the 5 minutes which may lead the DBA down the wrong path believing there is a problem with the database.
For 11g the DIAG_ADR_ENABLED=off must be set otherwise trace files will go to the DIAGNOSTICS destination.
To not interfer with other users create the SQLNET.ORA and TNSNAMES.ORA in your own directory and use them by setting the TNS_ADMIN variable.
The trace file data above matches what is in MOS note:
ORA-3135 with Recovery Catalog Creation Across the Network (Firewall included) [ID 805088.1]
"where nt[1]=32 is Operating System Dependent(OSD) error code.
OSD error is Err#32 Broken pipe. This OSD error is also defined in errno.h:
* #define EPIPE 32 /* Broken pipe */"
Resolution was to set an SQLNET.EXPIRE_TIME=15 in the database product $ORACLE_HOME/network/admin/sqlnet.ora (choose 15 minutes because it was less than the 30 minutes the firewall was closing the session).
Other references
Troubleshooting ORA-3135 Connection Lost Contact [ID 787354.1]
When manually copying a file from the filesystem to ASM using ASMCMD the file will be placed under a generic ASM directory regardless of specifying the destination.
Below example a file is copied to +DATA/mynewdb1/ however file this creates an alias to +DATA/ASM/DATAFILE/
ASMCMD> cp /app/oracle/backup/mydb1/DF.2105.2105.784545123 +DATA/mynewdb1/datafile/DF.2105
copying /app/oracle/backup/mydb1/DF.2105.2105.784545123 -> +DATA/mynewdb1/datafile/DF.2105
ASMCMD> cd +DATA/mynewdb1/datafile/
ASMCMD> ls -al
WARNING:option 'a' is deprecated for 'ls'
please use 'absolutepath'
Type Redund Striped Time Sys Name
...
N DF.2105 => +DATA/ASM/DATAFILE/DF.2105.401.784554241
...
ASMCMD> pwd
+DATA/mynewdb1/datafile
According to Oracle this is expected behaviour.
However files transferred to ASM via the database ONNN background process set the context to which to store the files and are placed in the "correct" location. For example using RMAN connected to the target database:
RMAN> restore controlfile from '/app/oracle/backup/db1/Current.312.769340147';
Starting restore at 23-SEP-12
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=242 instance=MYNEWDB1 device type=DISK
channel ORA_DISK_1: copied control file copy
output file name=+DATA/mynewdb1/control01.ctl
output file name=+DATA/mynewdb1/control02.ctl
Finished restore at 23-SEP-12
RMAN>
To copy a file without it ending up being an alias to the generic location use RMAN which will communicate with ASM via the ONNN process and place it in the appropriate location for the database.
11.2 Background Processes"Onnn, ASM Connection Pool Process, Maintains a connection to the ASM instance for metadata operations, Onnn slave processes are spawned on demand. These processes communicate with the ASM instance."
On a semi-related note, why did Oracle consider "ls -absolutepath" was better than "ls -al"? "ls -al" had fewer keystrokes.
ASMCMD> ls -al
WARNING:option 'a' is deprecated for 'ls'
please use 'absolutepath'
CRS-5017: The resource action "ora.mydb.db start" encountered the following error:
ORA-27504: IPC error creating OSD context
ORA-27300: OS system dependent operation:check if failed with status: 0
ORA-27301: OS failure message: Error 0
ORA-27302: failure occurred at: skgxpcini3
ORA-27303: additional information: requested interface vnet5:1 interface not up
_disable_interface_checking = TRUE to disable this check for single instance cluster.
Check output from ifconfig co
Alert log error for instance:
SKGXP: ospid 10823: network interface with IP address 169.254.20.147 is DOWN
The error message mentions network interface vnet5:1 In this case vnet5 and
vnet6 are private interconnects using HAIP hence the 169.254.x.x address (Link Local Address).
On SERVER01, vnet5 is UP, no 169. address bound to it:
vnet5: flags=1000843 mtu 9000 index 6
inet 10.0.84.194 netmask fffffff0 broadcast 10.0.84.207
On SERVER02, vnet5 not UP, has 169.254.20.147 bound to both vnet5 and vnet6!
vnet5: flags=1000842 mtu 9000 index 8
inet 10.0.84.195 netmask fffffff0 broadcast 10.0.84.207
vnet5:1: flags=1000842 mtu 9000 index 8
inet 169.254.20.147 netmask ffff8000 broadcast 169.254.127.255
vnet6: flags=1000843 mtu 9000 index 7
inet 10.0.84.211 netmask fffffff0 broadcast 10.0.84.223
vnet6:1: flags=1000843 mtu 9000 index 7
inet 169.254.164.136 netmask ffff8000 broadcast 169.254.255.255
vnet6:2: flags=1000843 mtu 9000 index 7
inet 169.254.20.147 netmask ffff8000 broadcast 169.254.127.255
Fix was to reboot SERVER02 as it turned out the UNIX administrator had performed some work on the blades concerning MAC addresses for vnet5. Somewhere vnet5 and vnet6 got the link-local address mixed up.
The below shows an "ORA-27102 out of memory" error on startup for an Oracle RAC database however this error can occur in single instances also.
$ srvctl start database -d MYDB
PRCR-1079 : Failed to start resource ora.MYDB.db
CRS-5017: The resource action "ora.MYDB.db start" encountered the following error:
ORA-27102: out of memory
SVR4 Error: 22: Invalid argument
. For details refer to "(:CLSN00107:)" in
"/app/11.2.0/grid/log/myserver01/agent/crsd/oraagent_oracle/oraagent_oracle.log".
In alert log:
WARNING: The system does not seem to be configured
optimally. Creating a segment of size 0x000000009b000000
failed. Please change the shm parameters so that
a segment can be created for this size. While this is
not a fatal issue, creating one segment may improve
performance
9b000000 hex = 2400M = MEMORY_TARGET and MEMORY_MAX_TARGET settings for this particular instance.
Check the shared memory max in the project.
Databases are being started by the 'grid' user for RAC however single instances will usually be 'oracle':
Shared memory maximum allocated to the project is 4G. If the sum of all SGA/PGAs allocated for instances is more than 4G within the project an instance will encounter the error either on startup or even during operation.
To fix either increase the maximum shared memory limit (OS resources permitting) or decrease the SGA/PGA in individual instances to under the limit.
The following metalink note describes the problem and solutions:
Database Startup On Solaris 10 Fails With Ora-27102 Out Of Memory Error [ID 399895.1]
Why does Logical Standby say an applied log is 'CURRENT' instead of 'YES' when the Standby is up to date? Reason is because there's at least one session on the Primary that hasn't yet committed or rolled back. Until a commit or rollback occurs on the Primary the Standby keeps the logs in a 'CURRENT' state and on the Standby server filesystem as they may still be required to apply transactions.
(output below formatted to fit)
column file_name heading 'Archive Log' format a80
column applied heading 'Applied' format a30
select file_name, sequence#, timestamp,
decode(applied,'NO',applied||' - Not Applied Logs!','YES',applied||' - OK',applied) applied
from dba_logstdby_log
order by sequence#;
Archive Log SEQUENCE# TIMESTAMP Applied
-------------------------------------------------------------------------------- ---------- -------------------- ------------------------------
/oradata/MYDB/arch/logapplyarch/arch1_1247463_699468119.log 1247463 10.AUG.2012 16:16:33 CURRENT
/oradata/MYDB/arch/logapplyarch/arch1_1247464_699468119.log 1247464 10.AUG.2012 16:17:04 CURRENT
...
...
The 10.2 documentation doesn't give many details:
"APPLIED, Indicates whether each archive log has been applied (YES) or not (NO)"
To resolve:
On the Primary look for a session that has a lock requested around the time when the first (oldest) 'CURRENT' archive log appeared (16:16:33 in the above).
set linesize 500
column program format a40
column event format a30
column username format a15
column osuser format a15
column lock_requested format a30 heading 'Lock Requested'
column object_name format a30
select
s.sid,
s.serial#,
s.username,
s.osuser,
s.program,
s.event,
l.type,
decode(l.lmode,
0, 'NONE',
1, 'NULL',
2, 'ROW SHARE',
3, 'ROW EXCLUSIVE',
4, 'SHARE',
5, 'SHARE ROW EXCLUSIVE',
6, 'EXCLUSIVE', '?') "Mode",
decode(l.request,
0, 'NONE',
1, 'NULL',
2, 'ROW SHARE',
3, 'ROW EXCLUSIVE',
4, 'SHARE',
5, 'SHARE ROW EXCLUSIVE',
6, 'EXCLUSIVE', '?') "Request",
o.object_name,
to_char(sysdate-l.ctime / 86400,'DD-MON-YYYY HH24:MI:SS') lock_requested
from
v$lock l, dba_objects o, v$session s
where l.id1 = o.object_id (+)
and l.sid = s.sid
order by l.sid, l.type
/
SID SERIAL# USERNAME OSUSER PROGRAM EVENT TY Mode Request OBJECT_NAME Lock Requested
--------- ----------------------- ------ ------------------------- ------------------------------ -- ------------------- ------------- ------------------ -----------------------------
...
113 34978 SCOTT scott prog@tigerserv (TNS V1-V3) SQL*Net message from client TM ROW EXCLUSIVE NONE scott25X 10-AUG-2012 16:16:32
113 34978 SCOTT scott prog@tigerserv (TNS V1-V3) SQL*Net message from client TM ROW EXCLUSIVE NONE scott25J 10-AUG-2012 16:16:32
113 34978 SCOTT scott prog@tigerserv (TNS V1-V3) SQL*Net message from client TM ROW EXCLUSIVE NONE scott25I 10-AUG-2012 16:16:32
113 34978 SCOTT scott prog@tigerserv (TNS V1-V3) SQL*Net message from client TX EXCLUSIVE NONE
...
...
Above there was a DML lock at 16:16:32 on the Primary. Investigate why the session has uncommitted transactions. There is nothing necessarily wrong but if the Standby has been uncharacteristically building up 'CURRENT' applied logs for hours or the filesystem is starting to fill up with shipped archive logs then maybe there is an SQL performance problem or it could be something as simple as a user has not exited their session.
"Error
Connection to {hostname} as user {username} failed."
In the agent home on the host:
AGENT_HOME/sysman/log/emagent.trc:
ERROR util.fileops: error: file AGENT_HOME/bin/nmo is not a setuid file
WARN Authentication: nmo binary in current oraHome doesn't have setuid privileges !!!
ERROR Authentication: altNmo binary doesn't exist ... reverting back to nmo
Check that the root.sh was run when the agent product was installed, if not then UNIX admin need to run root.sh to set the proper permissions for the Oracle binaries. This should be under AGENT_HOME/root.sh
Try and set preferred/default credetials again in Grid Control.
"Information
Credentials successfully verified for {hostname}."
The DBMS_RCVMAN.num2displaysize procedure can be used to display human readable byte sizes.
The DBMS_RCVMAN.Sec2DisplayTime procedure can display a time format given seconds.
The drawback is the DBMS_RCVMAN package is only available to SYS.
SQL> select sys.dbms_rcvman.num2displaysize(1024) sz from dual;
SZ
-------------------------------------------------------------------------------
1.00K
SQL> select sys.dbms_rcvman.num2displaysize(235236346) sz from dual;
SZ
-------------------------------------------------------------------------------
224.34M
SQL> select sys.dbms_rcvman.num2displaysize(1024*1024*1024*1024) sz from dual;
SZ
-------------------------------------------------------------------------------
1.00T
Given a number of seconds display in time format:
SQL> select dbms_rcvman.Sec2DisplayTime(600) dt from dual;
DT
------------------------------
00:10:00
SQL> select dbms_rcvman.Sec2DisplayTime(90035) dt from dual;
DT
------------------------------
25:00:35
SQL>
The second pre-requisite you should undertake before assessing any security risks is understand your environment!. This should include an understanding of all product installs, networks, firewall, user access and public facing infrastructure.
Every 3 months Oracle release a Critical Patch Update (CPU) which usually applies to a majority of Oracle products. I haven't seen an instance where there was a Critical Patch Update that didn't apply to the Oracle Database. It is good practice to regularly review these advisories when they are released. The simplest form of assessing the Risk Matrix is to see what version and component of a product is affected, whether that product exists in their environment and then apply the CPU.
To give you an idea of vulnerabilities and their seriousness there was recently the release of a zero-day attack for the Oracle database known as the TCP Listener Poison attack. To view the most recent advisories see the below link and select "View the most recent Critical Patch Update Advisory":
For each product there is a Risk Matrix. This matrix is the basis for assessing risk to a component associated with a product. I'm only concentrating on the Database product however the same principles in assessing the CVSS (Common Vulnerability Scoring System) applies for other products also.
I have provided a short summary on some of the key aspects in understanding risk matrix and meaning of the CVSS headings. Hopefully for high risks that gives people imperitive to patch and help present a case to management to undertake a patching exercise.
Prior to the Risk Matrix Oracle provide a good summary of the vulnerabilities for a product. From the April 2012 Database advisory:
"This Critical Patch Update contains 6 new security fixes for the Oracle Database Server. 3 of these vulnerabilities may be remotely exploitable without authentication, i.e., may be exploited over a network without the need for a username and password. 1 of these fixes is applicable to client-only installations, i.e., installations that do not have the Oracle Database Server installed.
Below is an excerpt of the April 2012 Database Product Risk Matrix:
Here is a quick guide to assessing the risk matrix before understanding the CVSS completely for your environment:
As already stated understand your environment completely! It would be an incomplete assessment if you're going to miss parts of it and a pointless exercise.
Assess whether the “Component” exists for any databases, if the package or privilege required exists and the base score’s value (a higher value obviously requires more attention).
Identify components and current products listed in the Risk matrix. As stated know your environment.
Are there any default usernames/passwords in any databases especially for this component? Vulnerabilities that require single or no authentication and make use of packages/privileges readily available are good candidates for patching. Of course follow the security checklist and change default username passwords and lock these accounts.
Base score – a base score of 10 means the product should be patched as fast as reasonably possible.
An Access vector of “Network” or “Adjacent Network” means the vulnerability is remotely exploitable. Assess the extent at which access is available to a database/server (number of people, other servers which can connect to the database/server).
Search the vulnerability # on the internet and examine the search results for more information. Vulnerabilities where exploits are available off the internet need higher consideration.
Even in the event of a component (say “Oracle Spatial”) has a locked MDSYS account and changed default password, there may be access to packages granted to other users or PUBLIC which are still accessible. Having locked a database account does not necessarily mean that vulnerable package is not exploitable.
The Risk Matrix only lists latest affected and supported versions. The vulnerability may apply for lower versions so the entire branch needs to be examined. For example, say 11.2.0.3 is listed as affected then assume the 11.2.x branch is affected however the patch is only available for 11.2.0.3 The question might then be raised on why the database is still on a lower patch set.
As recommended by the Security Checklist change default usernames/password (and lock if not required), and revoke privileges that are not required (eg, UTL_TCP). An example security vulnerability, a default database username/password that has EXECUTE on UTL_TCP (up to 11.x by default granted to PUBLIC) is a risk to the extent that once this is exploited all hosts accessible on the network are vulnerable to attack.
Understanding Risk Matrix columns
The columns Base Score, Access Vector, Access complexity, Authentication, Confidentiality, Integrity and Availability are taken from the CVSS verion 2.0 open framework. Oracle have added the "Remote Exploit Without Authentication", and also modified the “Partial” definition to include “Partial+”.
“The CVSS base score defines the severity of the vulnerability and ranges between 0.0 and 10.0, where 10.0 represents the highest severity. Each risk matrix is ordered using this value, with the most severe vulnerability at the top of each risk matrix.”
Access Vector
The poorest score is "Network".
Local (L) - A vulnerability exploitable with only local access requires the attacker to have either physical access to the vulnerable system or a local (shell) account. Examples of locally exploitable vulnerabilities are peripheral attacks such as Firewire/USB DMA attacks, and local privilege escalations (e.g., sudo).
Adjacent Network (A) - A vulnerability exploitable with adjacent network access requires the attacker to have access to either the broadcast or collision domain of the vulnerable software. Examples of local networks include local IP subnet, Bluetooth, IEEE 802.11, and local Ethernet segment.
Network (N) - A vulnerability exploitable with network access means the vulnerable software is bound to the network stack and the attacker does not require local network access or local access. Such a vulnerability is often termed "remotely exploitable". An example of a network attack is an RPC buffer overflow.
Access Complexity
The poorest score is "Low".
High (H) - Specialized access conditions exist. For example:
In most configurations, the attacking party must already have elevated privileges or spoof additional systems in addition to the attacking system (e.g., DNS hijacking).
The attack depends on social engineering methods that would be easily detected by knowledgeable people. For example, the victim must perform several suspicious or atypical actions.
The vulnerable configuration is seen very rarely in practice.
If a race condition exists, the window is very narrow.
Medium (M) - The access conditions are somewhat specialized; the following are examples:
The attacking party is limited to a group of systems or users at some level of authorization, possibly untrusted.
Some information must be gathered before a successful attack can be launched.
The affected configuration is non-default, and is not commonly configured (e.g., a vulnerability present when a server performs user account authentication via a specific scheme, but not present for another authentication scheme).
The attack requires a small amount of social engineering that might occasionally fool cautious users (e.g., phishing attacks that modify a web browser’s status bar to show a false link, having to be on someone’s “buddy” list before sending an IM exploit).
Low (L) - Specialized access conditions or extenuating circumstances do not exist. The following are examples:
The affected product typically requires access to a wide range of systems and users, possibly anonymous and untrusted (e.g., Internet-facing web or mail server).
The affected configuration is default or ubiquitous.
The attack can be performed manually and requires little skill or additional information gathering.
The “race condition” is a lazy one (i.e., it is technically a race but easily winnable).
Authentication
The poorest score is "None".
Multiple (M) - Exploiting the vulnerability requires that the attacker authenticate two or more times, even if the same credentials are used each time. An example is an attacker authenticating to an operating system in addition to providing credentials to access an application hosted on that system.
Single (S) - The vulnerability requires an attacker to be logged into the system (such as at a command line or via a desktop session or web interface).
None (N) - Authentication is not required to exploit the vulnerability.
Confidentiality
The poorest score is "Complete".
None (N) - There is no impact to the confidentiality of the system.
Partial (P) - There is considerable informational disclosure. Access to some system files is possible, but the attacker does not have control over what is obtained, or the scope of the loss is constrained. An example is a vulnerability that divulges only certain tables in a database.
Complete (C) - There is total information disclosure, resulting in all system files being revealed. The attacker is able to read all of the system's data (memory, files, etc.)
Integrity
The poorest score is "Complete".
None (N) - There is no impact to the integrity of the system.
Partial (P) - Modification of some system files or information is possible, but the attacker does not have control over what can be modified, or the scope of what the attacker can affect is limited. For example, system or application files may be overwritten or modified, but either the attacker has no control over which files are affected or the attacker can modify files within only a limited context or scope.
Complete (C) - There is a total compromise of system integrity. There is a complete loss of system protection, resulting in the entire system being compromised. The attacker is able to modify any files on the target system.
Availability
The poorest score is "Complete".
None (N) - There is no impact to the availability of the system.
Partial (P) - There is reduced performance or interruptions in resource availability. An example is a network-based flood attack that permits a limited number of successful connections to an Internet service.
Complete (C) - There is a total shutdown of the affected resource. The attacker can render the resource completely unavailable.
Additional Notes about CVSS terms
The difference between Integrity and Confidentiality: confidentiality relates to data, integrity can also relate to access controls and security, for example, an exploit that gives elevated privileges to a set of users compromises the integrity of a system.
Availability includes a reduction in performance of a system (denial of service for example).
Here is also a link to the Common Vulnerability Scoring System Version 2 Calculator. I've included it so you can see how a score is calculated. This does not include the Oracle created columns (for eg, there is no Partial+) but still worth having a look at.
Using SQL*Plus it is possible to create presentable HTML reports that improve readability over traditional ASCII based reports.
A Simple report:
set markup html on
set verify off trimspool on trimout on feedback off heading on echo off pages 100 termout off
spool currentTime1.html
select to_char(sysdate,'DD.MON.YYYY HH24:MI:SS') "Current Time" from dual
/
spool off
Output:
Current Time
06.JUN.2012 18:56:29
Taking a plain report and adding color to it and stylesheet:
spool currentTime2.html
set markup html on spool on entmap off -
head 'The Current Time -
' -
body 'text=black bgcolor=fffffff align=left' -
table 'align=center width=99% border=3 bordercolor=black bgcolor=grey'
select
'
set verify off trimspool on trimout on feedback off heading on echo off pages 100 termout off
set markup html on spool on entmap off -
head 'USERENV -
' -
body 'text=black bgcolor=fffffff align=left' -
table 'align=center width=99% border=3 bordercolor=black bgcolor=grey'
define COLOR_BEGIN_GREEN = '';
define COLOR_END_GREEN = '';
spool userenv.html
select
sys_context('USERENV','SESSION_USER') "Session User",
''|| sys_context('USERENV','INSTANCE_NAME')||
'' "Instance Name",
'&COLOR_BEGIN_GREEN' || sys_context('USERENV','SERVER_HOST') ||
'&COLOR_END_GREEN' "DB Hostname"
from dual
/
spool off
Output:
Session User
Instance Name
DB Hostname
SYS
lexus
jupiter
Displaying columns in different colors based on a condition. This can help highlighting an issue in a report:
set verify off trimspool on trimout on feedback off heading on echo off pages 100 termout off
set markup html on spool on entmap off -
head 'The Current Time -
' -
body 'text=black bgcolor=fffffff align=left' -
table 'align=center width=99% border=3 bordercolor=black bgcolor=grey'
define BEGIN_WARNING = '';
define END_WARNING = '';
define BEGIN_OK = '';
define END_OK = '';
column server_host new_value server_host
column instance_name new_value instance_name
select
sys_context('USERENV','SERVER_HOST') server_host,
sys_context('USERENV','INSTANCE_NAME') instance_name
from dual
/
spool logMode.html
prompt
Putting it all together, show init.ora prameters and comment, highlight problem areas in red otherwise in green:
set verify off trimspool on trimout on feedback off heading on echo off pages 100 termout off
define BEGIN_WARNING = '';
define END_WARNING = '';
define BEGIN_OK = '';
define END_OK = '';
column server_host new_value server_host
column instance_name new_value instance_name
select
sys_context('USERENV','SERVER_HOST') server_host,
sys_context('USERENV','INSTANCE_NAME') instance_name
from dual
/
set markup html on spool on entmap off -
head 'INIT.ORA Parameters - &server_host..&instance_name -
' -
body 'text=black bgcolor=fffffff align=left' -
table 'align=center width=99% border=3 bordercolor=black bgcolor=grey'
spool initParameters.html
prompt
select
name "Parameter Name",
value "Parameter Value",
comments "Comments"
from
(
select
name name,
decode(value,null,'',value) value,
case
--
-- control_file_record_keep_time
--
when name = 'control_file_record_keep_time'
then
case
when value <=14 then
'&BEGIN_WARNING Value of '||value||
' potentially low for retention of RMAN backup information &END_WARNING'
end
--
-- destinations *dest*, dump and log destinations
--
when name like '%dest%'
then
case when lower(value) like '%product%'
then
'&BEGIN_WARNING Destination possibly under the Oracle product directory '||value||
' &END_WARNING'
when lower(value) like '%temp%' or lower(value) like '%tmp%'
then
'&BEGIN_WARNING Destination possibly under a temporary directory!'||value||
' &END_WARNING'
end
--
-- processes
--
when name = 'processes'
then
case
when value < 100
then
'&BEGIN_WARNING '||name||' value '||value||
' may be too low, see v$resource_limit max utilisation &END_WARNING'
end
--
-- skip_unusable_indexes
--
when name = 'skip_unusable_indexes'
then
decode(value,'FALSE',value, '&BEGIN_WARNING '||value||' &END_WARNING')
--
-- SPFILE
--
when name = 'spfile'
then
decode(value,'?', '&BEGIN_WARNING No SPFILE set &END_WARNING',
'&BEGIN_OK SPFILE in USE &END_OK')
--
-- undo management
--
when name = 'undo_management'
then
case when lower(value) != 'auto'
then
'&BEGIN_WARNING UNDO_MANAGEMENT is not AUTO '|| value ||
' &END_WARNING'
end
end comments
from v$parameter, v$instance
)
where comments is not null
order by name, value
/
spool off
jupiter.lexus - INIT.ORA Parameters
Parameter Name
Parameter Value
Comments
control_file_record_keep_time
7
Value of 7 potentially low for retention of RMAN backup info rmation
Using two network interfaces, vnet1 and vnet2 it is possible to create a redundant public interface that is used as one by Grid Infrastructure. If one of the interfaces (and depending on network design, links) is unavailable the public interface is still available.
Current configuration:
$ srvctl config nodeapps
Network exists: 1/165.156.22.240/255.255.255.240/vnet1, type static
VIP exists: /myserver01-vip/165.156.22.246/165.156.22.240/255.255.255.240/vnet1, hosting node myserver01
VIP exists: /myserver02-vip/165.156.22.247/165.156.22.240/255.255.255.240/vnet1, hosting node myserver02
GSD exists
ONS exists: Local port 6100, remote port 6200, EM port 2016
$ crsctl check cluster -all
**************************************************************
myserver01:
CRS-4537: Cluster Ready Services is online
CRS-4529: Cluster Synchronization Services is online
CRS-4533: Event Manager is online
**************************************************************
myserver02:
CRS-4537: Cluster Ready Services is online
CRS-4529: Cluster Synchronization Services is online
CRS-4533: Event Manager is online
**************************************************************
Below are the network interfaces, you can see the VIP (.246) and SCANs (.248, .250) bound to vnet1. vnet2 has nothing running on it.
$ /usr/sbin/ifconfig -a
....
vnet1: flags=9040843 mtu 1500 index 3
inet 165.156.22.251 netmask fffffff0 broadcast 165.156.22.255
groupname topside
vnet1:1: flags=1000843 mtu 1500 index 3
inet 165.156.22.244 netmask fffffff0 broadcast 165.156.22.255
vnet1:2: flags=1040843 mtu 1500 index 3
inet 165.156.22.246 netmask fffffff0 broadcast 165.156.22.255
vnet1:4: flags=1040843 mtu 1500 index 3
inet 165.156.22.248 netmask fffffff0 broadcast 165.156.22.255
vnet1:6: flags=1040843 mtu 1500 index 3
inet 165.156.22.250 netmask fffffff0 broadcast 165.156.22.255
vnet2: flags=9040843 mtu 1500 index 4
inet 165.156.22.252 netmask fffffff0 broadcast 165.156.22.255
groupname topside
...
...
To perform the change an outage for all PUBLIC traffic is required:
Passing the -debug option to runInstaller provides more information on where a pre-requisite could be failing. The 11.1 Grid Agent no longer uses a GUI and is required to be installed command line. The following error message does not provide many clues as to what the pre-requisite fail could be:
Checking Temp space: must be greater than 150 MB. Actual 1083 MB Passed
Checking swap space: must be greater than 150 MB. Actual 16383 MB Passed
Preparing to launch Oracle Universal Installer from /tmp/OraInstall2012-05-10_10-36-59AM.
Please wait ...
*** Check for updates ***
*** Select Installation Type ***
*** Check Prerequisites ***
Some recommended prerequisites checks are failed. You might get errors during installation. Please fix those prerequisites and start the installation again.
Further, the README.TXT contains the following:
For detailed information on using silent installation method to install the Management agent please refer to .Enterprise Manager Silent Installations. described in the Enterprise Manager Grid Control Basic Installation available at:
After reading through the documentation, the -debug flag can be used for the runInstaller which gives us more detailed information as to what the problem can be:
Checking if Oracle software certified on the current O/S... 1
In Progress
Checking for required packages installed on the system .... 1
In Progress
Checking whether the software compatibile for current o/s... 7
In Progress
Checking for sufficient diskspace in TEMP location... 1
In Progress
Checking for sufficient diskspace in Inventory location... 1
In Progress
Checking for the Hostname... 1
This is a prerequisite condition to test whether the Oracle software is certified on the current O/S or not.Succeeded
This is a prerequisite condition to test whether the minimum required packages are available on the system.Succeeded
This is a prerequisite condition to test whether the software is compatible for this o/sFailed
This check ensures that sufficient diskspace is available in system TEMP location.Succeeded
This check ensures that sufficient diskspace is available in system Inventory location.Succeeded
This is a prerequisite condition to test whether the host name where the installation will be done, is correct or not.Succeeded
Silent Install page at 3 oracle.sysman.install.oneclick.EMGCPrereqDialog,pageTitle=Check Prerequisites,label=Check Prerequisites,selected
Some recommended prerequisites checks are failed. You might get errors during installation. Please fix those prerequisites and start the installation again.
At some stage a DBA will encounter the following error when resizing a datafile:
ORA-03297: file contains used data beyond requested RESIZE value
What may be more frustrating is that the datafile has plenty of free space.
So what can be done to solve this error and resize down the datafile? Why does it occur? Some might be tempted to just add more space to the filesystem and be done with it. Not everyone has that option.
A datafile cannot be resized down below the High Water Mark (HWM) and that's what causes the ORA-03297 error. . In the lifetime of the datafile some extents have been created bumping up the HWM. Others below the HWM have also been DELETED.
I will show an example of a datafile encountering this error, what is happening inside the datafile and how to resolve the error. Let's create a tablespace first and the two tables, one with many rows and a second with a few rows.
CREATE TABLESPACE "MYTS" DATAFILE '/oradata1/MYDB//mytsdatafile01.dbf' SIZE 32M
AUTOEXTEND ON NEXT 1310720 MAXSIZE 64M
LOGGING ONLINE PERMANENT BLOCKSIZE 8192
EXTENT MANAGEMENT LOCAL AUTOALLOCATE DEFAULT NOCOMPRESS SEGMENT SPACE MANAGEMENT AUTO
Tablespace created.
SQL> create table myuser.mytab1 tablespace MYTS as
(select level id,dbms_random.string('a',dbms_random.value(1,4000)) str
from dual
connect by level < 20000
)
/
Table created.
SQL> create table myuser.mytab2 tablespace MYTS as
(select level id,dbms_random.string('a',dbms_random.value(1,4000)) str
from dual
connect by level < 10
)
/
Table created.
How many MB is now in use by these tables?
select segment_name, bytes/1024/1024
from dba_segments
where segment_name in ('MYTAB1','MYTAB2')
and owner = 'MYUSER'
/
SEGMENT_NAME BYTES/1024/1024
-----------------------------------------------
MYTAB1 54
MYTAB2 .0625
To demonstrate how these two tables fit in the MYTS tablespace I want to firstly show a few things about the datafile.
DFSIZEMB = the datafile size in MB on disk.
HWMMB = the location of the HWM in the datafile.
DFREEMB = the number of FREE MB in the datafile.
%FREE = the percentage of FREE space in the datafile. RESIZEABLE = the amount of MB that can be gained from resizing.
Use the below SQL to see how much space can be gained from a RESIZE and the location of the HWM in a datafile. For a database with many datafiles this query may take a while.
column file_name format a50
column tablespace_name format a20
select
tablespace_name,
file_id,
file_name,
dfsizeMB,
hwmMB,
dffreeMB,
trunc((dffreeMB/dfsizeMB)*100,2) "% Free",
trunc(dfsizeMB-hwmMB,2) "Resizeble"
from
(
select
df.tablespace_name tablespace_name,
df.file_id file_id,
df.file_name file_name,
df.bytes/1024/1024 dfsizeMB,
trunc((ex.hwm*(ts.block_size))/1024/1024,2) hwmMB,
dffreeMB
from
dba_data_files df,
dba_tablespaces ts,
(
select file_id, sum(bytes/1024/1024) dffreeMB
from dba_free_space
group by file_id
) free,
(
select file_id, max(block_id+blocks) hwm
from dba_extents
group by file_id
) ex
where df.file_id = ex.file_id
and df.tablespace_name = ts.tablespace_name
and df.file_id = free.file_id (+)
order by df.tablespace_name, df.file_id
)
/
TABLESPACE_NAME FILE_ID File Name DFSIZEMB HWMMB DFFREEMB % Free Resizeble
-------------------- ---------- ------------------------------ ---------- ---------- ---------- ---------- ----------...
MYTS 14 /oradata1/MYDB/mytsdatafile01.dbf 55.75 55.06 .6875 1.23 .69
...
Above, the datafile can only be resized by 0.69MB and the HWM is at the 55MB mark with the datafile size on disk being 55.75MB.
How do these two tables look like within the datafile? NOTE: This query may take a while for a large datafile with lots of extents.
QUERY 2 - location of segments within a datafile
column segment heading 'Segment Name' format a14
column file_name heading 'File Name' format a40
column segment_type heading 'Segment Type' format a10
select
file_name,
segment_type,
owner||'.'||segment_name segment,
block_id,
blockIdMB
from
(
select
ex.owner owner,
ex.segment_name segment_name,
ex.segment_type segment_type,
ex.block_id block_id,
df.file_name file_name,
trunc((ex.block_id*(ts.block_size))/1024/1024,2) blockIdMB
from
dba_extents ex, dba_data_files df, dba_tablespaces ts
where df.file_id = &file_id
and df.file_id = ex.file_id
and df.tablespace_name = ts.tablespace_name
order by ex.block_id desc
)
where rownum <= 100
/
Enter value for file_id: 14
File Name Segment Ty Segment Name BLOCK_ID BLOCKIDMB
--------------------------------- ---------- -------------- ---------- ----------
/oradata1/MYDB/mytsdatafile01.dbf TABLE MYUSER.MYTAB2 7040 55
/oradata1/MYDB/mytsdatafile01.dbf TABLE MYUSER.MYTAB1 6912 54
/oradata1/MYDB/mytsdatafile01.dbf TABLE MYUSER.MYTAB1 6784 53
/oradata1/MYDB/mytsdatafile01.dbf TABLE MYUSER.MYTAB1 6656 52
/oradata1/MYDB/mytsdatafile01.dbf TABLE MYUSER.MYTAB1 6528 51
/oradata1/MYDB/mytsdatafile01.dbf TABLE MYUSER.MYTAB1 6400 50
...
...
/oradata1/MYDB/mytsdatafile01.dbf TABLE MYUSER.MYTAB1 144 1.12
/oradata1/MYDB/mytsdatafile01.dbf TABLE MYUSER.MYTAB1 136 1.06
/oradata1/MYDB/mytsdatafile01.dbf TABLE MYUSER.MYTAB1 128 1
We can see table MYTAB1 ends around the 54MB block in the datafile and MYTAB2 starts around the 55MB block.
So we now have two tables in the MYTS tablespace created one after the other. Further, how about we create a situation where there is alot of free space in the datafile and cannot be resized resulting in "ORA-03297: file contains used data beyond requested RESIZE value". DELETING all the rows from the MYTAB1 table will not free up any space and will still have it allocated to MYTAB1, so let's TRUNCATE the table MYTAB1 and run the same SQL above again to see the result of the datafile.
The datafile now has 97% and 54MB FREE space. The HWM is still at the 55MB mark. Inspecting the datafile, MYTAB1 is at block 1MB and MYTAB2 is still at of course 55MB.
File Name Segment Ty Segment Name BLOCK_ID BLOCKIDMB
--------------------------------- ---------- -------------- ---------- ----------
/oradata1/MYDB/mytsdatafile01.dbf TABLE MYUSER.MYTAB2 7040 55
/oradata1/MYDB/mytsdatafile01.dbf TABLE MYUSER.MYTAB1 128 1
So seeing we have 54MB FREE space in a 55MB datafile let's try to resize it down to 50MB.
SQL> alter database datafile '/oradata1/MYDB/mytsdatafile01.dbf' resize 50M;
alter database datafile '/oradata1/MYDB/mytsdatafile01.dbf' resize 50M
*
ERROR at line 1:
ORA-03297: file contains used data beyond requested RESIZE value
The only way to resize the datafile is to lower the HWM. This is achieved by moving the extents located at the HWM either to another tablespace or the same tablespace. For the same tablespace ensure plenty of free space and run QUERY 2 after each MOVE to see if anything is achieved.
Of course for a production environment where there are many users accessing the table, index, LOB etc it might not be that easy and this sort of activity would need to be carefully planned, especially with LOB segments. Also, when moving a table the underlying INDEXES are marked UNUSABLE and need to be rebuilt.
SQL> alter table myuser.mytab2 move tablespace MYTS;
Table altered.
Now the datafile shows that MYTAB2 is at block 1MB.
File Name Segment Ty Segment Name BLOCK_ID BLOCKIDMB
----------------------------------- ---------- -------------- ---------- ----------
/oradata1/MYDB/mytsdatafile01.dbf TABLE MYUSER.MYTAB2 136 1.06
/oradata1/MYDB/mytsdatafile01.dbf TABLE MYUSER.MYTAB1 128 1
Now the HWM is at the 1.12 MB mark and there is 54MB of reclaimable space in the datafile.
SQL> alter database datafile '/oradata1/MYDB/mytsdatafile01.dbf' resize 2M;
Database altered.
Datafile now resized! In a production environment there may be many different objects extents near the HWM and simply moving one table might not be so common. It's best to see how many different objects are around the HWM and calculate how much space can be reclaimed via the MOVE. Unless there is a benefit in reclaiming a large amount of space, or moving tables, indexes, LOBs etc is something that just has to be done, then if possible get more space added to the filesystem.
Using a GROUP BY and some arithmetic it is possible to improve the performance of SQL comparing data of two tables. Even better, in Oracle this can be achieved with the PARTITION BY analytic function.
Below is an example comparing two tables (TAB1, TAB2) with two columns (col1 number, col2 varchar2) using a PARTITION BY analytic function:
create table tab1 (col1 number, col2 varchar2(3));
create table tab2 (col1 number, col2 varchar2(3));
insert into tab1 (col1, col2) values (3, 'ccc');
insert into tab1 (col1, col2) values (1, 'aaa');
insert into tab2 (col1, col2) values (1, 'aaa');
insert into tab2 (col1, col2) values (2, 'aaa');
select * from
-- list all results
-- change to "select count(*) from" for a count of differences
(
select rid, -- ROWID
col1, -- column 1 of table to compare
col2, -- column 2 of table to compare
sum(summ) over (partition by col1, col2) summ
-- must list all columns to compare in the PARTITION BY
from
(select 'In TAB1, NOT in TAB2' tab,
rowid rid, -- need to alias ROWID
-- list all/some columns of table to compare
col1, -- column 1 of table to compare
col2, -- column 2 of table to compare
------
1 summ
-- summ is used for sum() operation,
-- a matched row from other table will sum to 0
from tab1
UNION ALL
-- change to UNION for unique rows only
select 'In TAB2, NOT in TAB1' tab,
rowid rid, -- need to alias ROWID
-- list all/some columns of table to compare
col1, -- column 1 of table to compare
col2, -- column 2 of table to compare
------
-1 summ
-- summ used for sum() operation,
-- a matched row from other table will sum to 0
from tab2)
)
-- all the rows that didn't have a match have a non-zero sum()
where summ != 0
/
RID COL1 COL SUMM
------------------ ---------- --- ----------
AAAWK9AAGAAADNHAAB 2 aaa -1
AAAWK8AAGAAADM/AAA 3 ccc 1
------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 4 | 164 | 7 (15)| 00:00:01 |
|* 1 | VIEW | | 4 | 164 | 7 (15)| 00:00:01 |
| 2 | WINDOW SORT | | 4 | 124 | 7 (15)| 00:00:01 |
| 3 | VIEW | | 4 | 124 | 6 (0)| 00:00:01 |
| 4 | UNION-ALL | | | | | |
| 5 | TABLE ACCESS FULL| TAB1 | 2 | 56 | 3 (0)| 00:00:01 |
| 6 | TABLE ACCESS FULL| TAB2 | 2 | 56 | 3 (0)| 00:00:01 |
------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("SUMM"<>0)
Two full table scans and a SORT. Compare this to what traditionally people have used:
As good as this sounds may spell a time bomb (sleeper) for some applications with hard coded passwords. For example Application Servers with no procedures in place for regular password changes. Some may not even deal with the grace period message warning of an impending lock out.
When logging into an account that has entered a GRACE period the following message is displayed
SQL> connect username/password ERROR: ORA-28002: the password will expire within 3 days
When logging into an account that has passed the EXPIRED period the following message is displayed
SQL> connect username Enter password: ERROR: ORA-28001: the password has expired
Changing password for username New password: Retype new password: Password changed Connected. SQL>
Finding user accounts that have expired or grace passwords:
select username, account_status, expiry_date from dba_users
Since Oracle 10g there is a quicker way to access the DUAL table. Many applications use this table such as a "ping" from an application server. Below demonstrates how to SELECT from it over the "old" DUAL table. The difference is 0 consistent gets over 3 consistent gets using the old method. This may not sound like much but adds up for an application executing thousands of these requests in a short span of time.
In order to use FAST DUAL avoid using "SELECT * FROM DUAL", if possible use "SELECT 1 FROM DUAL".
Example:
SQL> set autotrace traceonly timing on SQL> select * from dual;
Elapsed: 00:00:00.00
Execution Plan ---------------------------------------------------------- Plan hash value: 272002086
