Installing Oracle Database 11g Release 2 on Oracle Solaris 11

 

I just finished installing Oracle database 11g Release 2 (11.2.0.3) on Oracle Solaris 11 and from what I experienced, Oracle Solaris 11 has a minimal package prerequisite requirement in order to install Oracle database 11g Release 2 (11.2.0.3) on Oracle Solaris 11. Most of the requirements from Solaris 10 has been integrated or consolidated into other packages which has reduced number of prerequisite packages needed in order to install Oracle database 11g Release 2



In order for remote DISPLAY to work on S11, you will need the following package: SUNWxwplt

root@s11_sparc# pkg install pkg://solaris/compatibility/packages/SUNWxwplt

You can now set the DISPLAY variable for the user installing Oracle database.







Regarding dependency on Motif and Xt libraries, you can set the toolkit as environment variable for the user installing the oracle database:

$ export AWT_TOOLKIT=XToolkit

(For more info on XToolkit, refer: http://docs.oracle.com/javase/1.5.0/docs/guide/awt/1.5/xawt.html)

Thats it! Now you should be able to proceed with Oracle database 11g Release 2 (11.2.0.3) installation on Solaris 11 server by running runInstaller

Securing the Oracle Listener

The Oracle Database Listener is the database server software component that manages the network traffic between the Oracle Database and the client. The Oracle Database Listener listens on a specific network port (default 1521) and forwards network connections to the Database.

The listener is one of the most critical components to database operations;

• It is responsible for the ability to have a client/server communication
• In dedicated mode it is responsible for creating a new process (or thread on Windows) on behalf of the client and setting up the communications
• On Windows each such server process actually speaks on a new tcpip port and the listener redirects the client to this port
• On Unix streaming continues on the original port
  • The listener forks a new process
  • The listener then closes its own fd-s; the new process continues to speak on the fd-s

In MTS the listener is responsible to assign and set up the connection with the least loaded dispatcher. The dispatchers get requests from the client and place them on the request queues for the shared server processes, and read responses from the response queues to send to the client

How to set listener password

Set the Listener password to stop most attacks and security issues. Setting the password manually in listener.ora using the PASSWORDS_ parameter will result in the password being stored in cleartext.


LSNRCTL> set current_listener
LSNRCTL> change_password Old password:

New password:
Reenter new password:

LSNRCTL> set password Password:
LSNRCTL> save_config

MYSQL basic command

MySQL Commands List

Here you will find a collection of basic MySQL statements

General Commands

USE database_name	Change to this database. You need to change to some database when you first connect to MySQL mysql> SELECT DATABASE();
DESCRIBE table_name
SET PASSWORD=PASSWORD('new_password')
OPTIMIZE [table]
SHOW variables
SHOW DATABASES	Lists all MySQL databases on the system. To find out which database is currently selected, use the DATABASE() function
show tables  [FROM database_name]	Lists all tables from the current database or from the database given in the command
SHOW FIELDS FROM table_name
SHOW COLUMNS FROM table_name	These commands all give a list of all columns (fields) from the given table, along with column type and other info.
SHOW INDEX FROM table_name	Lists all indexes from this tables.
show open tables
show procedure status
show function status
show binary log	Lists the binary log files on the server
show master log	Lists the binary log files on the server

How to troubleshoot mysql database server high cpu usage/slowness

1. Firstly find out what's causing server CPU high usage
• vmstat 2 20top -b -n 5
2. check mysql error log , slow query log etc from /etc/my.cnf
• innodb_buffer_pool_size=20000M 
4. mysql > show engine innodb status\G

Other administration commands

• display in vertical : -E, --vertical
• variables : --print-defaults
• save output : -tee=
• save html : -H
• alive : mysqladmin -p ping
• status : mysqladmin -p ,
• How to Find out current Status of MySQL server?
• mysqladmin -u root -p extended-status
• How to check MySQL version?
• : mysqladmin -u root -p version
• How to stop MYSQL?
• : mysqladmin -p shutdown
• How to set root password?
• : mysqladmin -u root password YOURNEWPASSWORD
• How to check all the running Process of MySQL server?
• mysqladmin -u root -p processlist
• How to kill a process


• : mysqladmin kill id
• How to create a job
• http://www.mysqltutorial.org/mysql-triggers/working-mysql-

Configuration Files

• my.cnf - usually located in /etc/my.cnf, /etc/mysql/my.cnf

Directories

• basedir ($MYSQL_HOME)
  - e.g. /opt/mysql-5.1.16-beta-linux-i686-glib23
• datadir (defaults to $MYSQL_HOME/data)
• tmpdir (important as mysql behaves unpredictability if full)
• innodb_[...]_home_dir
  - mysql> SHOW GLOBAL VARIABLES LIKE '%dir' 

Install Oracle Solaris 11 on Oracle VM VirtualBox

http://www.oracle.com/technetwork/systems/hands-on-labs/s11-vbox-install-1408628.html

Cascading Dataguard

You have a primary database in your corporate offices, and you want to create a standby database in another building on your local area network (LAN). In addition, you have a legal insurance requirement to keep the redo data and backup copies off site at a geographically distant location outside of your LAN but on your wide area network (WAN).

You could define two destinations on your primary database so that redo data could be transmitted to both of these sites, but this would put an extra workload on your primary database throughput due to the network latency of sending the redo data over the WAN.

To solve this problem, you could define a tight connection between your primary and physical standby databases in your LAN using the LGWR and SYNC network transports and standby redo log files. This would protect against losing access to the primary database, and it provides an alternate site for production when maintenance is required on the primary database. The secondary location on the WAN could be serviced by the physical standby database, ensuring that the redo data is stored off site. Nightly backups on the production database could then be moved to the WAN remote standby database, which removes the requirement to ship tapes to the off-site storage area.

Finally, in a worst case scenario, where you lose access to both the primary database and the physical standby database on the LAN, you could fail over to the remote standby database with minimal data loss. If you can gain access to the redo log file of the last standby database from the original standby database, you could recover it on the remote standby database, incurring no data loss.

The only time you would incur problems by sending the information over the WAN is during a switchover or failover, when the physical standby database has transitioned to the primary role. However, this configuration would still meet your insurance requirements.

To reduce the load on your primary system, you can implement cascaded destinations, whereby a standby database receives its redo data from another standby database, instead of directly from the primary database. You can configure a physical standby database to retransmit the incoming redo data it receives from the primary database to other remote destinations in the same manner as the primary database

Column Masking using Virtual Private Database (VPD)

Column masking is a simple way to hide you valuable data from certain users without having to apply encrypt/decrypt techniques, In conventional Virtual Private Database the VPD Policy is applied to the whole row. By default a Column-Level VPD Policy allows you to restrict the rows displayed only if specified columns are accessed.

Column masking behaviour is implemented by using the "sec_relevant_cols_opt => DBMS_RLS.ALL_ROWS" parameter. This allows you to display all rows but mask the values of the specified columns for the restricted rows

There are 3 steps for accomplish column masking:

1. A function to be used by the policy (function policy) created in next step.
2. Use dbms_rls package to create the policy.
3. Assign “exempt access policy” to users to be excluded from the policy. These users can see all data with no masking.

Example

CONN sys/password@db10g AS SYSDBA GRANT EXECUTE ON dbms_rls TO scott;

CONN scott/tiger@db10g -- Create the policy function to restrict access to SAL and COMM columns -- if the employee is not part of the department 20. CREATE OR REPLACE FUNCTION pf_job (oowner IN VARCHAR2, ojname IN VARCHAR2) RETURN VARCHAR2 AS   con VARCHAR2 (200); BEGIN   con := 'deptno = 20';   RETURN (con); END pf_job; /

-- Add policy BEGIN   DBMS_RLS.ADD_POLICY (object_schema         => 'scott',                        object_name           => 'emp',                        policy_name           => 'sp_job',                        function_schema       => 'scott',                        policy_function       => 'pf_job',                        sec_relevant_cols     => 'sal,comm',                        sec_relevant_cols_opt => DBMS_RLS.ALL_ROWS); END; /

-- All rows are returned but the SAL and COMM values are only
-- shown for employees in department 20.

-- Remove the policy function from the table.

BEGIN   DBMS_RLS.DROP_POLICY (object_schema => 'scott', object_name => 'emp', policy_name => 'sp_job'); END; /

Are you encrypting database traffic?

Encrypting Oracle network traffic safeguards sensitive data such as social security numbers, credit card numbers and other personally identifiable information against packet sniffing. Packet sniffing is where an attacker tries to capture unencrypted data by using a network sniffer. This sniffing takes place without the knowledge of either the client machine or database server.

With Oracle Advanced Security, you can set up network encryption to your database in a matter of hours. You can also configure your Oracle databases to only accept mutually authenticated and encrypted connections. This means that in addition to protecting against network eavesdropping, you can also protect against unauthorized connections to your database.

 

Oracle Net Native Encryption

These lines were added to sqlnet.ora on the database server: SQLNET.ENCRYPTION_TYPES_SERVER = RC4_256
SQLNET.ENCRYPTION_SERVER = required

You can also use Oracle Net manager to achieve the same result (do this on both client/server)

1.       Go to Local -> Profile

2.       Select Oracle Advanced Security -> Encrytion

 

No additional configuration was necessary. We connected to the database and retrieved the same data.

 http://www.oracle.com/webfolder/technetwork/tutorials/obe/db/11g/r2/prod/security/network_encrypt/ntwrkencrypt.htm

Client Access Control

Oracle Net valid node checking lets you allow or deny access to an Oracle database server based on the IP address (or host name) of the client machine making the request. You can control access to the database server by specifying either which machines are allowed access or which machines are denied access.

To use the node validation feature, set the following sqlnet.ora (protocol.ora for Oracle 8) parameters on the database server:

# Enable node validation
tcp.validnode_checking = YES

# Prevent these client IP addresses from
# making connections to the Oracle listener.
tcp.excluded_nodes = {list of IP addresses}

# Allow these IP addresses to connect.
tcp.invited_nodes = {list of IP addresses}

Protecting Oracle Network Traffic with SSH Tunnelling

SSH provides a secure encrypted communications channel between two machines over an insecure network. A client machine can connect to an Oracle database over a secure SSH connection by using port forwarding. SSH port forwarding provides another way to protect data privacy through encryption and safeguard against data interception and alteration.

Creating an SSH tunnel between a client machine and an Oracle database server requires an SSH client to be present on the client machine and an SSH server to be present on the database server. No configuration is necessary on the database server.

On the Server

1.       Install CopSSH http://www.itefix.no/i2/copssh

2.       Create a windows user and activate this user

On the Client

1.       Install Putty

2.       Under connection ->SSH -> Tunnels.

a.       Enter a source port (can be any free port eg 8080)

b.      Enter the Destination , the database server IP address, listener port (eg 192.168.1.1:1521

c.       Check the local ports accept connections from other hosts

 

 

Modified the tnsnames.ora to use localhost and source port

In addition to being encrypted, data passed through an SSH tunnel is automatically integrity checked and authenticated by using SSH credential

Extracting DDL using DBMS_METADATA and DATAPUMP

How to extract the table definition (DDL statement) from an Oracle database ?

DBMS_METADATA can be used to extract DDL definitions from a database. You can also use DataPump to extract the DDL.

 

DBMS_METADATA

set pagesize 0
set long 90000
set feedback off
select DBMS_METADATA.GET_DDL('TABLE','') from DUAL;

 

DATAPUMP

In this example we are exporting only the package "IFC". The output of the DDL is located as myoutput.txt .  The impdp keyword here is sqlfile

Create a parameter file ""parfile.parschemas=volors
directory=data_pump_dir
include=package:"= 'IFC'"

> expdp system/password parfile=c:\parfile.par

> impdp system/password directory=data_pump_dir sqlfile=myoutput.txt

Network Import using datapump

With network mode imports, one doesn't need any intermediate dump files (GREAT, no more FTP'ing of dump files). Data is exported across a database link and imported directly into the target database.



Example:

SQL> create user new_scott identified by tiger; User created. SQL> grant connect, resource to new_scott; Grant succeeded. SQL> grant read, write on directory dmpdir to new_scott; Grant succeeded. SQL> grant create database link to new_scott; Grant succeeded. SQL> conn new_Scott/tiger Connected. SQL> create database link old_scott connect to scott identified by tiger using 'orcl.oracle.com'; Database link created. impdp new_scott/tiger DIRECTORY=dmpdir NETWORK_LINK=old_scott remap_schema=scott:new_scott

How to Migrate ASM Diskgroups To Another SAN

If your plans are replacing the current disks associated to your diskgroups with a new storage, this operation can be accomplished without any downtime, so you can follow the next steps


1) Backup all your databases and valid the backup (always required to protect your data).
2) Add the new path (new disks from the new storage) to your asm_disktring to be recognized by ASM:


Example:

SQL> alter system set asm_diskstring = '/dev/emcpowerc*' ,

'/dev/emcpowerh*';
Where: '/dev/emcpowerc*' are the current disks.
Where: '/dev/emcpowerh*' are the new disks.

3) Confirm that the new disks are being detected by ASM:

SQL> select path from v$asm_disk;

4) Add the new disks to your desired diskgroup:

SQL> alter diskgroup add disk ‘’, ‘’, ‘’, ‘’, . . . ‘’ rebalance power <#>;

5) Then wait until the rebalance operation completes:

SQL> select * from v$asm_operation; SQL> select * from gv$asm_operation;

6) Finally, remove the old disks:

SQL> alter diskgroup drop disk , , , , . . . rebalance power <#>;

7) Then wait until the rebalance operation completes:

SQL> select * from v$asm_operation; SQL> select * from gv$asm_operation;

8) Done, your ASM diskgroups and database have been migrated to the new storage.
Note: Alternatively, we can execute add disk & drop disk statements in one operation, in that way only one rebalance operation will be started as follow:

SQL> alter diskgroup add disk '', .., '' drop disk , , .., rebalance power <#>;

This is more efficient than separated commands (add disk & drop disk statements).

Installing Oracle VM Manager 3.0.3 under Dom0 host

Check out how you can install both Oracle VM manager and VM server on the same machine. I have done it successfully.

http://www.pythian.com/blog/installing-oracle-vm-manager-3-0-3-under-dom0-host-or-how-to-save-resources-on-your-sandbox/

DB Control Not Starting due to Incorrect Timezone

Cause

The Agent does not start because of incorrect time zone.

Solution

1. check ORACLE_HOME/sysman/admin/supportedtzs.lst and ORACLE_HOME/sysman/admin/nsupportedtzs.lst for a correct value on TZ
2. Comment the line AgentTZRegion="<some value>" in
   ORACLE_HOME/<hostname>_<SID>/sysman/config/emd.properties
3. export TZ=<chosen time zone from step 1>
4. emctl config agent getTZ
5. emctl config agent updateTZ
6. emctl start dbconsole

Note: on 11.1 and higher DB Console, it is enough to run "emctl resettz agent" and then follow the instructions in the output (ie. running th mgmt_target.set_agent_tzrgn procedure) to change or set the correct timezone on the DB Console.

 

How to Dual Boot Windows 7 and Linux using BCDEdit | iceflatline

http://www.iceflatline.com/2009/09/how-to-dual-boot-windows-7-and-linux-using-bcdedit/

Manually trigger metric collection in Grid Control

Metric Collection errors are generally down to the following reasons:

• Configuration Error as described above
• Temporary Collection Errors
• Oracle Bugs 

Reevaluating Metric Collections
If you are running a Management Agent Release 10.2, then you can use the following command to perform an immediate reevaluation of a metric collection:
emctl control agent runCollection <targetName>:<targetType> <colletionItemName>
emctl control agent runCollection testdb.us.oracle.com:oracle_database oracle_dbconfig

1. Run "emctl config agent listtargets" to list all the targets in the agent
2. Run "emctl control agent runCollection"
3. Eg : To manually upgrade mgmt$tablespaces
   
   emctl control agent runCollection testdb.us.oracle.com:oracle_database oracle_dbconfig

Checking scheduler status

> emctl status agent scheduler

How to Use a Windows Server 2003 Network File Share as Storage

dThis document describes how to enable and use a Windows Server 2003 Release 2 Server Network File Share (NFS) as the NFS storage repository

 

1. Install the Microsoft Services for NFS component using the Add/Remove Windows Components wizard
   
2. Select the Microsoft Services for NFS check box and click OK
3. After installing the Microsoft Services for NFS component, open the Properties page of the folder or drive that you want to share as NFS storage
   
4. Select the Share this folder option button.
5. Type the share name exactly .
6. Select the Allow anonymous access check box
7. Click the Permissions button.
   
8. On the Type of access, Change the Type of access to Read-Write.
9. Click OK.
10. Select the Security tab, add the ANONYMOUS LOGON account, and give it Full Control permission.
    

Active Database duplication

Oracle 11g introduced the ability to create duplicate databases directly without the need for a backup. This is known as active database duplication. The process is similar to the backup-based duplication

• You don't need a backup of the source system, but it does have to be in archive mode
• The passwords in the password files must match for both servers
• Both the source and destination database servers require a "tnsnames.ora" entry for the destination database
• The destination server requires static listener configuration in a "listener.ora" file
  SID_LIST_LISTENER =
    (SID_LIST =
      (SID_DESC =
        (GLOBAL_DBNAME = DB11G.WORLD)
        (ORACLE_HOME = /u01/app/oracle/product/11.2.0/db_1)
        (SID_NAME = DB11G)
      )
    )
  
  LISTENER =
    (DESCRIPTION_LIST =
      (DESCRIPTION =
        (ADDRESS = (PROTOCOL = TCP)(HOST = ol5-112-dup2.localdomain)(PORT = 1521))
      )
      (DESCRIPTION =
        (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
      )
    )

The command are

RMAN > DUPLICATE DATABASE TO DB11G   FROM ACTIVE DATABASE   SPFILE   NOFILENAMECHECK;

To create a standby database from active database files:

DUPLICATE TARGET DATABASE   FOR STANDBY   FROM ACTIVE DATABASE   DORECOVER   SPFILE     SET "db_unique_name"="foou" COMMENT ''Is a duplicate''     SET LOG_ARCHIVE_DEST_2="service=inst3 ASYNC REGISTER      VALID_FOR=(online_logfile,primary_role)"     SET FAL_CLIENT="inst3" COMMENT "Is standby"     SET FAL_SERVER="inst1" COMMENT "Is primary"   NOFILENAMECHECK

Format Oracle OCFS Drive

Offline cfs drive. Need to offline the drive before you can format it

1. c:\> ocfsutil /c offlinevol /m X:

To format cfs on O drive

1. create a drive letter O in the windows
2. C:\oracle\1020\crs\cfs>ocfsformat /m o: /c 1024 /v data /f

Check ocr status

1. ocrcheck

To do a manual export ocr

1. ocrconfig -export <backup_file_name>

To import from the backup

1. stop crs service first
2. create an empty file data.ocr
3. ocrconfig -import <OCR export_filename>

Add ocr disk

1. create an empty file first
2. ocrconfig -replace mirror x:\cdata\orange_cluster\data.ocr

Remove ocr disk

1. ocrconfig -replace ocr

Query voting disk

1. C:\>crsctl query css votedisk
   0.     0    J:\cdata\orange_cluster\votedsk

Add voting disk

1. create the directory first
2. c:\> crsctl add css votedisk J:\cdata\orange_cluster\votedsk -force  

Delete voting disk

1. stop the crs service before deleting voting disk
2. crsctl delete css votedisk <VOTEDISK_LOCATION> -force

ADRCI: A survival guide for the DBA

adrci: A survival guide for the DBA

Starting with 11gR1, we have a new way to deal with Oracle Errors & Tracefiles: There is now a special command line utility dedicated for that purpose called adrci (Automatic Diagnostic Repository Command Interpreter). This posting is intended to show you the (in my view) essential commands, a DBA ought to know in order to use it. We will look at

1. Viewing the alert.log
2. The relation between incident & problem
3. Creation of Packages & ZIP files to send to Oracle Support
4. Managing, especially purging tracefiles

I will at first create a problem. Don’t do that with your Production Database! Especially: Never do DML on dictionary tables!

[oracle@uhesse ~]$ sqlplus / as sysdba  

SQL*Plus: Release 11.2.0.2.0 Production on Wed Jun 1 10:25:06 2011  Copyright (c) 1982, 2010, Oracle.  All rights reserved.  

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production With the Partitioning, OLAP, Data Mining and Real Application Testing options  

SQL> select * from v$version;  

BANNER -------------------------------------------------------------------------------- 

Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production PL/SQL Release 11.2.0.2.0 - Production CORE    11.2.0.2.0    

Production TNS for Linux: Version 11.2.0.2.0 - Production NLSRTL Version 11.2.0.2.0 - Production  

SQL> show parameter diagnostic  

NAME                     TYPE     VALUE 

------------------------------------ ----------- ------------------------------ 

diagnostic_dest              string     /u01/app/oracle  

SQL> grant dba to adam identified by adam;  

Grant succeeded.  SQL> connect adam/adam 

Connected. 

SQL> create table t (n number);  

Table created.  

SQL> select object_id from user_objects;   

OBJECT_ID ----------      75719  

SQL> connect / as sysdba 

Connected. 

SQL> update tab$ set cols=2 where obj#=75719;  

1 row updated.  

SQL> commit;  

Commit complete.  

SQL> alter system flush shared_pool;  

System altered.  

SQL> connect adam/adam Connected. 

SQL> select * from t; 

select * from t               * 

ERROR at line 1: 

ORA-03113: end-of-file on communication channel 

Process ID: 2236 Session ID: 29 Serial number: 9

I flushed the Shared Pool to get the Data Dictionary Cache empty. Else the select may not crash the session as it did. Imagine the user calls me now on the phone. Our first idea as an experienced DBA: We look at the alert.log! Right so. Please notice that we now have two different kinds of the alert.log.

One is present in the conventional text format, per OFA in $ORACLE_BASE/diag/rdbms/name of the db/name of the instance/trace This location is determined by the new initialization parameter DIAGNOSTIC_DEST, while BACKGROUND_DUMP_DEST is deprecated in 11g.

1. Viewing the alert.log

The other one is in XML format placed in $ORACLE_BASE/diag/rdbms/name of the db/name of the instance/alert This version of the alert.log is accessed by adrci:

[oracle@uhesse ~]$ adrci  ADRCI: Release 11.2.0.2.0 - Production on Wed Jun 1 10:20:08 2011  

Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.  

ADR base = "/u01/app/oracle" 

adrci> show home 

ADR Homes: diag/tnslsnr/uhesse/listener diag/rdbms/orcl/orcl

Please notice that we have different ADR Homes. In my case only two, because I am not using Grid Infrastructure on this Demo System, else there would be another one. I specify my Database Home first. Then I look at the alert.log. Good news if you are on Windows: Finally, you can tail -f your alert.log also

adrci> set home diag/rdbms/orcl/orcl 

adrci> show alert -tail -f 

2011-06-01 10:16:35.337000 +02:00 db_recovery_file_dest_size of 4032 MB is 0.00% used. 

This is a user-specified limit on the amount of space that will be used by this database for recovery-related files, and does not reflect the amount of space available in the underlying filesystem or ASM diskgroup. Starting background process CJQ0 CJQ0 started with pid=21, OS id=2204 2011-06-01 10:18:42.668000 +02:00 Exception [type: SIGSEGV, Address not mapped to object] [ADDR:0x0] [PC:0x90D891A, qcstda()+702] [flags: 0x0, count: 1] Errors in file /u01/app/oracle/diag/rdbms/orcl/orcl/trace/orcl_ora_2236.trc  (incident=6153): ORA-07445: exception encountered: core dump [qcstda()+702] [SIGSEGV] [ADDR:0x0] [PC:0x90D891A] [Address not mapped to object] [] Incident details in: /u01/app/oracle/diag/rdbms/orcl/orcl/incident/incdir_6153/orcl_ora_2236_i6153.trc Use ADRCI or Support Workbench to package the incident. See Note 411.1 at My Oracle Support for error and packaging details. 2011-06-01 10:18:47.518000 +02:00 Dumping diagnostic data in directory=[cdmp_20110601101847], requested by (instance=1, osid=2236), summary=[incident=6153]. 2011-06-01 10:18:48.727000 +02:00 Sweep [inc][6153]: completed Sweep [inc2][6153]: completed

2. The relation between Incident & Problem

You see the incident was recorded in the alert.log. And it tells you “Use ADRCI or Support Workbench to package the incident.” We will soon see how to do that. First I’d like to explain the relation between incident and problem: An incident is the concrete occurrence of a problem. In other words: The same problem may have multiple incidents. To show that, I will open another terminal and do again a select against the table t, while still tailing the alert log from the first session.

Second terminal:

[oracle@uhesse ~]$ sqlplus adam/adam  

SQL*Plus: Release 11.2.0.2.0 Production on Wed Jun 1 10:21:52 2011  Copyright (c) 1982, 2010, Oracle.  All rights reserved.  

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production With the Partitioning, OLAP, Data Mining and Real Application Testing options  

SQL> select * from t where n=42; 

select * from t where n=42                          

* ERROR at line 1: ORA-03113: end-of-file on communication channel Process ID: 2299 Session ID: 36 Serial number: 11

First terminal:

2011-06-01 10:21:31.367000 +02:00 Starting background process SMCO SMCO started with pid=19, OS id=2268 2011-06-01 10:22:08.781000 +02:00 Exception [type: SIGSEGV, Address not mapped to object] [ADDR:0x0] [PC:0x90D891A, qcstda()+702] [flags: 0x0, count: 1] Errors in file /u01/app/oracle/diag/rdbms/orcl/orcl/trace/orcl_ora_2299.trc  (incident=6201): ORA-07445: exception encountered: core dump [qcstda()+702] [SIGSEGV] [ADDR:0x0] [PC:0x90D891A] [Address not mapped to object] [] Incident details in: /u01/app/oracle/diag/rdbms/orcl/orcl/incident/incdir_6201/orcl_ora_2299_i6201.trc Use ADRCI or Support Workbench to package the incident. See Note 411.1 at My Oracle Support for error and packaging details. 2011-06-01 10:22:11.135000 +02:00 Dumping diagnostic data in directory=[cdmp_20110601102211], requested by (instance=1, osid=2299), summary=[incident=6201]. 2011-06-01 10:22:13.370000 +02:00 Sweep [inc][6201]: completed Sweep [inc2][6201]: completed

I have seen the second incident recorded. I exit out of the tail -f with CTRL+C and continue:

adrci> show problem  

ADR Home = /u01/app/oracle/diag/rdbms/orcl/orcl: 

************************************************************************* 

PROBLEM_ID           PROBLEM_KEY                                                 LAST_INCIDENT        LASTINC_TIME                              -------------------- ----------------------------------------------------------- -------------------- ---------------------------------------- 1                    ORA 7445 [qcstda()+702]                                     6201                 2011-06-01 10:22:08.924000 +02:00        1 rows fetched

So I have one problem with the ID 1 and the last incident occurred at 10:22. Are there more?

adrci> show incident  

ADR Home = /u01/app/oracle/diag/rdbms/orcl/orcl: 

************************************************************************* 

INCIDENT_ID          PROBLEM_KEY                                                 CREATE_TIME                               

-------------------- ----------------------------------------------------------- ---------------------------------------- 

6153                 ORA 7445 [qcstda()+702]                                     2011-06-01 10:18:42.995000 +02:00        6201                 ORA 7445 [qcstda()+702]                                     2011-06-01 10:22:08.924000 +02:00        2 rows fetched

I want to see some more detail about the incidents:

adrci> show incident -mode detail -p "incident_id=6201"  

ADR Home = /u01/app/oracle/diag/rdbms/orcl/orcl: 

*************************************************************************  ********************************************************** INCIDENT INFO RECORD 1 **********************************************************    INCIDENT_ID                   6201    STATUS                        ready    CREATE_TIME                   2011-06-01 10:22:08.924000 +02:00    PROBLEM_ID                    1    CLOSE_TIME                       FLOOD_CONTROLLED              none    ERROR_FACILITY                ORA    ERROR_NUMBER                  7445    ERROR_ARG1                    qcstda()+702    ERROR_ARG2                    SIGSEGV    ERROR_ARG3                    ADDR:0x0    ERROR_ARG4                    PC:0x90D891A    ERROR_ARG5                    Address not mapped to object    ERROR_ARG6                       ERROR_ARG7                       ERROR_ARG8                       ERROR_ARG9                       ERROR_ARG10                      ERROR_ARG11                      ERROR_ARG12                      SIGNALLING_COMPONENT          SQL_Parser    SIGNALLING_SUBCOMPONENT          SUSPECT_COMPONENT                SUSPECT_SUBCOMPONENT             ECID                             IMPACTS                       0    PROBLEM_KEY                   ORA 7445 [qcstda()+702]    FIRST_INCIDENT                6153    FIRSTINC_TIME                 2011-06-01 10:18:42.995000 +02:00    LAST_INCIDENT                 6201    LASTINC_TIME                  2011-06-01 10:22:08.924000 +02:00    IMPACT1                       0    IMPACT2                       0    IMPACT3                       0    IMPACT4                       0    KEY_NAME                      ProcId    KEY_VALUE                     25.3    KEY_NAME                      Client ProcId    KEY_VALUE                     oracle@uhesse (TNS V1-V3).2299_140262306875136    KEY_NAME                      PQ    KEY_VALUE                     (0, 1306916528)    KEY_NAME                      SID    KEY_VALUE                     36.11    OWNER_ID                      1    INCIDENT_FILE                 /u01/app/oracle/diag/rdbms/orcl/orcl/trace/orcl_ora_2299.trc    OWNER_ID                      1    INCIDENT_FILE                 /u01/app/oracle/diag/rdbms/orcl/orcl/incident/incdir_6201/orcl_ora_2299_i6201.trc 1 rows fetched

I want to look at the incident tracefile mentioned above:

adrci> show trace /u01/app/oracle/diag/rdbms/orcl/orcl/incident/incdir_6201/orcl_ora_2299_i6201.trc /u01/app/oracle/diag/rdbms/orcl/orcl/incident/incdir_6201/orcl_ora_2299_i6201.trc  ----------------------------------------------------------  LEVEL PAYLOAD  ----- ------------------------------------------------------------------------------------------------------------------------------------------------  Dump file /u01/app/oracle/diag/rdbms/orcl/orcl/incident/incdir_6201/orcl_ora_2299_i6201.trc  Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production  With the Partitioning, OLAP, Data Mining and Real Application Testing options  ORACLE_HOME = /u01/app/oracle/product/11.2.0/db_1  System name:      Linux  Node name:        uhesse  Release:  2.6.32-100.28.5.el6.x86_64  Version:  #1 SMP Wed Feb 2 18:40:23 EST 2011  Machine:  x86_64  Instance name: orcl  Redo thread mounted by this instance: 1  Oracle process number: 25  Unix process pid: 2299, image: oracle@uhesse (TNS V1-V3) *** 2011-06-01 10:22:08.929  *** SESSION ID:(36.11) 2011-06-01 10:22:08.929  *** CLIENT ID:() 2011-06-01 10:22:08.929  *** SERVICE NAME:(SYS$USERS) 2011-06-01 10:22:08.929  *** MODULE NAME:(SQL*Plus) 2011-06-01 10:22:08.929  *** ACTION NAME:() 2011-06-01 10:22:08.929 Dump continued from file: /u01/app/oracle/diag/rdbms/orcl/orcl/trace/orcl_ora_2299.trc  1>     ***** Error Stack *****  ORA-07445: exception encountered: core dump [qcstda()+702] [SIGSEGV] [ADDR:0x0] [PC:0x90D891A] [Address not mapped to object] []  1<     ***** Error Stack *****  1>     ***** Dump for incident 6201 (ORA 7445 [qcstda()+702]) *****  2>      ***** Beginning of Customized Incident Dump(s) *****  2>      ***** Beginning of Customized Incident Dump(s) *****  Exception [type: SIGSEGV, Address not mapped to object] [ADDR:0x0] [PC:0x90D891A, qcstda()+702] [flags: 0x0, count: 1]  Registers:  %rax: 0x0000000000000000 %rbx: 0x00007f915c77f0e0 %rcx: 0x0000000000000007  %rdx: 0x0000000000000000 %rdi: 0x00007f915c77be98 %rsi: 0x0000000000000000  %rsp: 0x00007fffc65178e0 %rbp: 0x00007fffc6517960  %r8: 0x0000000000000028  %r9: 0x0000000000002000 %r10: 0x00000000093849c0 %r11: 0x0000000000000168  %r12: 0x00007f915c77ade8 %r13: 0x000000008edbb178 %r14: 0x00007f915c777da0  %r15: 0x00007f915c77ae28 %rip: 0x00000000090d891a %efl: 0x0000000000010246  qcstda()+686 (0x90d890a) mov -0x40(%rbp),%rdi  qcstda()+690 (0x90d890e) mov %rdx,0x18(%rbx)  qcstda()+694 (0x90d8912) mov 0x60(%r15),%rsi  qcstda()+698 (0x90d8916) mov %ecx,0x8(%r15)  > qcstda()+702 (0x90d891a) mov %ecx,(%rsi)  qcstda()+704 (0x90d891c) mov 0x78(%rdi),%rdx  qcstda()+708 (0x90d8920) test %rdx,%rdx  qcstda()+711 (0x90d8923) jnz 0x90d8d03  qcstda()+717 (0x90d8929) mov -0x70(%rbp),%rdi *** 2011-06-01 10:22:08.963  dbkedDefDump(): Starting a non-incident diagnostic dump (flags=0x3, level=3, mask=0x0)  3>       ***** Current SQL Statement for this session (sql_id=8r222qucmawdt) *****           select * from t where n=42  3<       ***** current_sql_statement ***** 3 

3. Creation of Packages & ZIP files to send to Oracle Support

I may not be able to solve the problem myself. Oracle Support will help me with that one. I gather all the required information with a method called “Incident Packaging Service” (IPS):

adrci> ips create package problem 1 correlate all  

Created package 2 based on problem id 1, correlation level all

This did not yet create a ZIP file and is therefore referred to as “Logical Package”. The ZIP file is generated from the Logical Package that was created:

adrci> ips generate package 2 in "/home/oracle"   

Generated package 2 in file /home/oracle/ORA7445qc_20110601112533_COM_1.zip, 

mode complete

4. Managing, especially purging of tracefiles

Now to the management of tracefiles. You may notice that 11g creates lots of tracefiles that need to be purged from time to time. In fact, this is done automatically, but you may want to change the default purge policy:

adrci> show tracefile -rt  

01-JUN-11 10:31:48  diag/rdbms/orcl/orcl/trace/orcl_mmon_2106.trc  

01-JUN-11 09:43:43  diag/rdbms/orcl/orcl/trace/orcl_ckpt_2100.trc  

01-JUN-11 09:22:13  diag/rdbms/orcl/orcl/trace/alert_orcl.log  

01-JUN-11 09:22:11  diag/rdbms/orcl/orcl/trace/orcl_diag_2088.trc  

01-JUN-11 09:22:10  diag/rdbms/orcl/orcl/trace/orcl_ora_2299.trc  

01-JUN-11 09:22:10  diag/rdbms/orcl/orcl/incident/incdir_6201/orcl_ora_2299_i6201.trc  

01-JUN-11 09:18:47  diag/rdbms/orcl/orcl/trace/orcl_ora_2236.trc  

01-JUN-11 09:18:47  diag/rdbms/orcl/orcl/incident/incdir_6153/orcl_ora_2236_i6153.trc 

I have already got some tracefiles. How long are they going to stay?

adrci> show control  

ADR Home = /u01/app/oracle/diag/rdbms/orcl/orcl: 

************************************************************************* 

ADRID                SHORTP_POLICY        LONGP_POLICY         LAST_MOD_TIME                            LAST_AUTOPRG_TIME                        LAST_MANUPRG_TIME                        ADRDIR_VERSION       ADRSCHM_VERSION      ADRSCHMV_SUMMARY     ADRALERT_VERSION     CREATE_TIME                               -------------------- -------------------- -------------------- ---------------------------------------- ---------------------------------------- ---------------------------------------- -------------------- -------------------- -------------------- -------------------- ---------------------------------------- 1335663986           720                  8760                 2011-05-27 10:16:46.997118 +02:00                                                                                          1                    2                    80                   1                    2011-05-27 10:16:46.997118 +02:00        1 rows fetched

The ordinary tracefiles will stay for 30 days (720 hours), while files like incident files stay one year (8760 hours) by default. We can change that policy with for example:

adrci> set control (SHORTP_POLICY = 360)  

adrci> set control (LONGP_POLICY = 2190)  

adrci> show control 

ADR Home = /u01/app/oracle/diag/rdbms/orcl/orcl: 

************************************************************************* 

ADRID                SHORTP_POLICY        LONGP_POLICY         LAST_MOD_TIME                            LAST_AUTOPRG_TIME                        LAST_MANUPRG_TIME                        ADRDIR_VERSION       ADRSCHM_VERSION      ADRSCHMV_SUMMARY     ADRALERT_VERSION     CREATE_TIME -------------------- -------------------- -------------------- ---------------------------------------- ---------------------------------------- ---------------------------------------- -------------------- -------------------- -------------------- -------------------- ---------------------------------------- 1335663986           360                  2190                 2011-06-01 11:42:17.208064 +02:00                                                                                          1                    2                    80                   1                    2011-05-27 10:16:46.997118 +02:00 1 rows fetched

Also, we may want to purge tracefiles manually. Following command will manually purge all tracefiles older than 2 days (2880 minutes):

adrci> purge -age 2880 -type trace 

adrci> show tracefile -rt    

01-JUN-11 10:46:54  diag/rdbms/orcl/orcl/trace/orcl_mmon_2106.trc    

01-JUN-11 09:43:43  diag/rdbms/orcl/orcl/trace/orcl_ckpt_2100.trc    01-JUN-11 09:22:13  diag/rdbms/orcl/orcl/trace/alert_orcl.log    01-JUN-11 09:22:11  diag/rdbms/orcl/orcl/trace/orcl_diag_2088.trc    01-JUN-11 09:22:10  diag/rdbms/orcl/orcl/incident/incdir_6201/orcl_ora_2299_i6201.trc    01-JUN-11 09:22:10  diag/rdbms/orcl/orcl/trace/orcl_ora_2299.trc    01-JUN-11 09:18:47  diag/rdbms/orcl/orcl/incident/incdir_6153/orcl_ora_2236_i6153.trc    01-JUN-11 09:18:47  diag/rdbms/orcl/orcl/trace/orcl_ora_2236.trc    01-JUN-11 09:17:19  diag/rdbms/orcl/orcl/trace/orcl_dbrm_2090.trc    01-JUN-11 09:16:44  diag/rdbms/orcl/orcl/trace/orcl_j002_2210.trc    01-JUN-11 09:16:30  diag/rdbms/orcl/orcl/trace/orcl_ora_2187.trc    01-JUN-11 09:16:19  diag/rdbms/orcl/orcl/trace/orcl_mman_2094.trc    01-JUN-11 09:16:16  diag/rdbms/orcl/orcl/trace/orcl_vktm_2082.trc    01-JUN-11 09:16:14  diag/rdbms/orcl/orcl/trace/orcl_ora_2016.trc    30-MAY-11 14:07:02  diag/rdbms/orcl/orcl/trace/orcl_mmon_2093.trc    30-MAY-11 11:15:30  diag/rdbms/orcl/orcl/trace/orcl_ora_3414.trc    30-MAY-11 11:00:01  diag/rdbms/orcl/orcl/trace/orcl_j000_2245.trc    30-MAY-11 10:56:58  diag/rdbms/orcl/orcl/trace/orcl_dbrm_2077.trc    30-MAY-11 10:56:20  diag/rdbms/orcl/orcl/trace/orcl_j002_2201.trc    30-MAY-11 10:56:06  diag/rdbms/orcl/orcl/trace/orcl_ora_2178.trc    30-MAY-11 10:55:58  diag/rdbms/orcl/orcl/trace/orcl_mman_2081.trc    30-MAY-11 10:55:55  diag/rdbms/orcl/orcl/trace/orcl_vktm_2069.trc    30-MAY-11 10:55:53  diag/rdbms/orcl/orcl/trace/orcl_ora_2006.trc

Conclusion: With adrci, we have a new and efficient utility to deal with Oracle Errors – especially for collecting information to send them to Oracle Support. This functionality is called Incident Packaging Service. 11g is generating lots of tracefiles. We can control the purging policy of them with adrci. Finally, we can now tail -f our alert.log from any OS.