ADRCI IPS Command

Create an Incident Package with ADRCI and IPS Commands

Oracle 11g Incident Packaging steps

From the command prompt, the Automatic Diagnostic Repository (ADR) command interface utility is entered by typing “adrci” from the OS prompt:

C:\>adrci </span>

ADRCI: Release 11.1.0.6.0 - Beta on Thu Nov 15 22:21:42 2007 </span>

Copyright (c) 1982, 2007, Oracle.  All rights reserved. </span>

ADR base = "c:\oracle\11g"
adrci>

ADRCI commands are based on one or more base directories known as ADR homes.  Before using the ADRCI commands, it is helpful to set an active ADR home to work with a single instance.  The available homes can be seen by typing:

adrci> show homes
 ADR Homes:
 diag\clients\user_system\host_1475088825_11
 diag\clients\user_varun jain\host_1475088825_11
 diag\rdbms\ora11g4\ora11g4
 diag\rdbms\ora11gr1\ora11gr1
 diag\tnslsnr\dcfx7hb1\listener
 adrci> </span>

The output above shows that there are several available home directories on which ADR can operate.  If a DBA wants to only access diagnostic data from one of the available homes, the active ADR home should be set appropriately.  The active home can be set using the set homepath command:

adrci> set homepath diag\rdbms\ora11gr1\ora11gr1
adrci> show homes
ADR Homes:
diag\rdbms\ora11gr1\ora11gr1
adrci>

The output demonstrates that after setting the active ADR home, only the active home displays.

Creating Incident Packages

Once the incident or problem that should be included in the incident package is identified, two options remain.  The DBA can then either create a physical package immediately or create a logical package to verify the package contents and customize the package to include the files that are needed.

Incidents in the ADR home can be viewed by using the “show incident” command:

adrci> set homepath diag\rdbms\ora11gr1\ora11gr1
adrci> show incident 

ADR Home = c:\oracle\11g\diag\rdbms\ora11gr1\ora11gr1

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

INCIDENT_ID          PROBLEM_KEY                              CREATE_TIME
-------------------- -----------------------------------------------------------------
5138                 ORA 7445 [qmxarFindPartition()+15]       2007-11-15 22:53:58.343000 -08:00
5137                 ORA 600 [qmxarElemAt2]                   2007-11-15 22:53:40.859000 -08:00

2 rows fetched

adrci>

NOTE: If an active ADR home has not been set, this will include incidents from all homes listed in the “show homes” command.

If the problem reported has not been captured as an incident in ADR, then it is best to create a logical package and add the necessary files before generating the zip file.

If the physical package needs to be generated immediately based on an incident, problem, problem key, or time interval without the opportunity to customize the logical package, the “ips pack” command can be used as follows:

ips pack [incident |problem |problemkey prob_key|seconds secs|time to ] [correlate {basic|typical|all}] [in path]

For example:

adrci> ips pack incident 5137 in c:\tmp
Generated package 5 in file C:\tmp\ORA600qmx_20071116175948_COM_1.zip, mode complete
adrci>

To create the logical incident package, use a variation of the “ips create package” command:

ips create package {incident |problem |problemkey |seconds |time to } [correlate basic|typical|all]

It is possible to create a logical package based on incident number such as:

adrci> ips create package incident 5138
Created package 3 based on incident id 5138, correlation level typical
adrci>

This creates a pacakge of the incident 5138 in “incpkg” directory; you can then add diagnostic data to the package.

Finally generate the package for the incident, which then can be uploaded to metalink while seeking support from Oracle.

adrci> ips generate package 3
Generated package 1 in file /app/oracle/product/11.1.0/db_1/dbs/ORA4031_20080407170431_COM_1.zip, mode complete

As reported above a zip file has been created with all relevant logs. Now you can upload this zip file to Oracle support and seek their help in resolving your problem.

You can also view the information generated in “incpkg” directory

$cd /app/oracle/diag/rdbms/11GLAB/incpkg/pkg_3/seq_1
$ ls -ltr
-rw-r----- 1 oracle oinstall 499 Apr 7 17:14 metadata.xml
-rw-r----- 1 oracle oinstall 21968 Apr 7 17:14 manifest_1_1.xml
-rw-r----- 1 oracle oinstall 26270 Apr 7 17:14 manifest_3_1.txt
-rw-r----- 1 oracle oinstall 20064 Apr 7 17:14 manifest_3_1.html
drwxr-xr-x 2 oracle oinstall 4096 Apr 7 17:14 export
drwxr-xr-x 2 oracle oinstall 4096 Apr 7 17:14 crs
-rw-r----- 1 oracle oinstall 62789 Apr 7 17:14 config.xml

We used to achieve the same earlier by running some scripts or collecting the logs/trace files manually. But with adrci, this task is pretty simplified; I think this is surely going to reduce the time to diagnose and resolve any problem

To create an incident package based on a problem, it is necessary to find the problem_id by using the “show incident” command:

show incident [-p predicate_string] [-mode {BASIC|BRIEF|DETAIL}]

For example:

adrci> show incident -mode brief -p "incident_id=5137"

ADR Home = c:\oracle\11g\diag\rdbms\ora11gr1\ora11gr1:

**********************************************************
INCIDENT INFO RECORD 1
**********************************************************
INCIDENT_ID                   5137
STATUS                        ready
CREATE_TIME                   2007-11-15 22:53:40.859000 -08:00
PROBLEM_ID                    1
CLOSE_TIME                    <NULL>
FLOOD_CONTROLLED              none
ERROR_FACILITY                ORA
ERROR_NUMBER                  600
ERROR_ARG1                    qmxarElemAt2
ERROR_ARG2                    144
ERROR_ARG3                    <NULL>
ERROR_ARG4                    <NULL>
ERROR_ARG5                    <NULL>
ERROR_ARG6                    <NULL>
ERROR_ARG7                    <NULL>
ERROR_ARG8                    <NULL>
SIGNALLING_COMPONENT          <NULL>
SIGNALLING_SUBCOMPONENT       <NULL>
SUSPECT_COMPONENT             <NULL>
SUSPECT_SUBCOMPONENT          <NULL>
ECID                          <NULL>
IMPACTS                       0

1 rows fetched

adrci>

This shows that incident 5137 has been assigned the problem id of 1.  Using this problem id, creates the package for this problem:

adrci> ips create package problem 1
Created package 2 based on problem id 1, correlation level typical
adrci>

To create the package based on the problem key, use the problem_key value displayed in the show incident output.  For example:

adrci> ips create package problemkey "ORA 7445 [qmxarFindPartition()+15]"
Created package 3 based on problem key ORA 7445 [qmxarFindPartition()+15], correlation level
typical

adrci>

As previously discussed, creating an incident package based on a problem includes diagnostic information for incidents associated to that problem.  Since the same problem could potentially include many incidents, only the first and last three incidents associated with the problem will be included. This prevents the inclusion of superfluous information in the package.  Additionally, incidents older than 90 days are excluded to prevent the inclusion of incidents that are no longer impacting the database.  These settings apply to all generated incident packages except manually created packages.  Both of these options are configurable by either using IPS set configuration or the Enterprise Manager Support Workbench.

When creating an incident package based on a time interval, the DBA can either include incidents that occurred relative to the current time, or inside of a specific time interval.  To create a package based on incidents that occurred a certain time from the present, use “ips create package seconds [seconds]”.  This will include incidents that occurred within secondsfrom the time the command was run.  For example, “ips create package seconds 600” will create a package including incidents that occurred within the last 10 minutes.

To use a time interval instead, use the command “ips create package time ‘start_time’ to ‘end_time’”.  The start and end times in the command must be in the time format ‘YYYY-MM-DD HH24:MI:SS.FF TZR’ or ‘YYYY-MM-DD HH24:MI:SS TZR’.  The colons in the ‘HH24:MI:SS’ can optionally be substituted with periods.  To create the package based on the incidents that occurred between November 11, 2007 10PM and November 12, 2007 12AM (2 hour window), the command would be:

adrci> IPS CREATE PACKAGE TIME '2007-11-11 22:00:00 -08:00' to '2007-11-12 00:00:00 -08:00'
Created package 4 based on time range 2007-11-11 22:00:00 -08:00 to 2007-11-12 00:00:00 -08:00,
correlation level typical

adrci>

If the issue that is being reported to Oracle Support has not been captured as an incident, create an empty package using “ips create package” without any options.

ORA-04092: cannot COMMIT in a trigger

create a audit table to record modifications on table t1 as follows:

create or replace trigger t1_tgr
after insert or delete or update on t1
for each row
begin
if inserting then
insert into t1_audit values(:new.id, 'INSERT', dbms_flashback.get_system_change_number, '', :new.a);
commit;
elsif deleting then
insert into t1_audit values(:old.id, 'DELETE', dbms_flashback.get_system_change_number, :old.a, '');
commit;
elsif updating then
insert into t1_audit values(:old.id, 'UPDATE', dbms_flashback.get_system_change_number, :old.a, :new.a);
commit;
end if;
end;

when inserting a new row into t1, we got this error

insert into t1 values('insert a new line',999999);
*
ERROR at line 1:
ORA-04092: cannot COMMIT in a trigger
ORA-06512: at "GUANG.T1_TGR", line 4
ORA-04088: error during execution of trigger 'GUANG.T1_TGR'

as explained in metalink

OERR: ORA 4092 cannot COMMIT or ROLLBACK in a trigger [ID 19880.1]

Error: ORA 4092 Text: cannot COMMIT or ROLLBACK in a trigger
-------------------------------------------------------------------------------
Cause: A trigger attempted to COMMIT or ROLLBACK. This is not permitted.
Action: Rewrite the trigger so that COMMIT or ROLLBACK statements are not used</span>

Changing DBID by NID

the Oracle utility NID uses to change DBID of a database.

List available options

the available options are shown below:

$ nid

Keyword     Description                    (Default)
----------------------------------------------------
TARGET      Username/Password              (NONE)
DBNAME      New database name              (NONE)
LOGFILE     Output Log                     (NONE)
REVERT      Revert failed change           NO
SETNAME     Set a new database name only   NO
APPEND      Append to output log           NO
HELP        Displays these messages        NO

Start up database in mount state

In order to change DBID,  the database should be restarted in mount state

SQL> shutdown immediate;
SQL> startup mount;
SQL> alter system register;

Change DBID by NID

$> nid sys/iamsys@db10gr2

$ nid

DBNEWID: Release 10.2.0.5.0 – Production on Fri May 6 11:24:19 2011

Copyright (c) 1982, 2007, Oracle.  All rights reserved.

Keyword     Description                    (Default)
—————————————————-
TARGET      Username/Password              (NONE)
DBNAME      New database name              (NONE)
LOGFILE     Output Log                     (NONE)
REVERT      Revert failed change           NO
SETNAME     Set a new database name only   NO
APPEND      Append to output log           NO
HELP        Displays these messages        NO

Oracle Performance Tuning on AIX

1) Depending on your version, you may need to set filesystemio_options=setall (in 10g and 9i)

2) Make sure mount file system of data file, control file and redo logs with option "-o cio"

3) Create the filesystems for redo logs with -o cio and agblksize=512

4) Plan on increasing your db_cache_size to compensate for the loss of file
system buffering at the OS level

5) set lock_sga=true

6) use AIX large memory pages (e.g. 16M with vmo -p -o lgpg_regions=2048 lgpg_size=16777216)

Import DataPump: How to Import Table Data into a Table that has Different Name ?

Import DataPump: How to Import Table Data into a Table that has Different Name ? [ID 342314.1]
		Modified 20-OCT-2010     Type HOWTO     Status PUBLISHED

In this Document
Goal
Solution

---

Applies to:

Enterprise Manager for RDBMS – Version: 10.1.0.2 to 11.2.0.2 – Release: 10.1 to 11.2
Oracle Server – Enterprise Edition – Version: 10.1.0.2 to 11.2.0.2   [Release: 10.1 to 11.2]
Oracle Server – Personal Edition – Version: 10.1.0.2 to 11.2.0.2   [Release: 10.1 to 11.2]
Oracle Server – Standard Edition – Version: 10.1.0.2 to 11.2.0.2   [Release: 10.1 to 11.2]
Information in this document applies to any platform.

Goal

Checked for relevance on 10-20-2010

How to import table data into a target table that has the same structure as the original source table, but has a different name?

Solution

For Import DataPump using Oracle 10g, you can use the REMAP_SCHEMA parameter to remap the schema name during the import (similar to the FROMUSER and TOUSER parameters in the original import utility). However, there is no parameter to remap table names. This means that Import DataPump can only import data into a table with the same name as the original table.

The examples below are based on:

A. The demo schema SCOTT that is created with script: $ORACLE_HOME/rdbms/admin/scott.sql

B. The directory object my_dir that refers to an existing directory on the server where the Oracle RDBMS is installed. Example:

Windows:
CONNECT system/manager
CREATE OR REPLACE DIRECTORY my_dir AS 'D:\export';
GRANT read,write ON DIRECTORY my_dir TO public;
Unix:
CONNECT system/manager
CREATE OR REPLACE DIRECTORY my_dir AS '/home/users/export';
GRANT read,write ON DIRECTORY my_dir TO public;

C. Two tables with identical structure, but different names:

CONNECT scott/tiger
CREATE TABLE emp2 AS SELECT * FROM emp WHERE 1=2;
SELECT COUNT(*) FROM emp;
  COUNT(*)
----------
        14
SELECT COUNT(*) FROM emp2;
  COUNT(*)
----------
         0

D. You have an export dumpfile of table SCOTT.EMP that was created with:

expdp system/manager directory=my_dir dumpfile=exp_emp.dmp logfile=exp_emp.log tables=scott.emp

OBJECTIVE: import the data from the export dumpfile into table SCOTT.EMP2
1. If the original source table can be temporary renamed:

-- temporary rename the two tables:
CONNECT scott/tiger
RENAME emp TO emp_temp;
RENAME emp2 TO emp;

-- import data into table EMP:
impdp system/manager directory=my_dir dumpfile=exp_emp.dmp logfile=imp_emp.log full=y
table_exists_action=append content=metadata_only

-- rename tables back to their original names:
CONNECT scott/tiger
RENAME emp TO emp2;
RENAME emp_temp TO emp;

2: If the original source table cannot be renamed:

-- create a temporary user:
CONNECT system/manager
CREATE USER tempuser IDENTIFIED BY tempuser DEFAULT TABLESPACE users;
ALTER USER tempuser QUOTA UNLIMITED ON users;
GRANT create session, create table TO tempuser;

-- import data into user TEMPUSER:
impdp system/manager directory=my_dir dumpfile=exp_emp.dmp logfile=imp_emp.log full=y
remap_schema=scott:tempuser

-- rename the table in the TEMPUSER schema:
CONNECT tempuser/tempuser
RENAME emp TO emp2;

-- export data again, now from (renamed) table TEMPUSER.EMP2:
expdp system/manager directory=my_dir dumpfile=exp_emp2.dmp logfile=exp_emp2.log
tables=tempuser.emp2 content=data_only

-- import data into SCOTT.EMP2:
impdp system/manager directory=my_dir dumpfile=exp_emp2.dmp logfile=imp_emp2.log full=y
remap_schema=tempuser:scott table_exists_action=append

-- check results, and cleanup:
CONNECT system/manager
SELECT COUNT(*) FROM scott.emp;
  COUNT(*)
----------
        14
SELECT COUNT(*) FROM scott.emp2;
  COUNT(*)
----------
        14
DROP USER tempuser CASCADE;

Remarks:

(1) Note that within the same database, this could also be achieved with a single INSERT statement:

SQL> insert into scott.emp2 (select * from scott.emp);

(2) Note that with the original (non-DataPump) export and import utilities there was another solution if the tablename in the export dumpfile did not exist in the schema in the target database. E.g. based on previous example:

-- export from source database:
exp system/manager file=exp_emp.dmp log=exp_emp.log tables=scott.emp

-- on target database: ensure that table EMP doesn't exist:
CONNECT scott/tiger
CREATE TABLE emp2 AS SELECT * FROM emp WHERE 1=2;
DROP TABLE emp;

-- create synonym for table EMP2:
CREATE SYNONYM emp FOR emp2;

-- import data from EMP into table EMP2:
imp system/manager file=exp_emp.dmp log=imp_emp.log full=y ignore=y indexes=n constraints=n

(3) Starting with Oracle 11g, DataPump provides a new command REMAP_TABLE to import the table data into a new table name on the target database.   See the 11g Oracle Database Utilities for more on this new feature.

— import data from EMPLOYEES into table EMPS:
impdp hr DIRECTORY=dpump_dir1 DUMPFILE=expschema.dmp TABLES=hr.employees REMAP_TABLE=hr.employees:emps

‘PLAN_TABLE’ is old version

When using explain plan and dbms_xplan.display() or @?/rdbms/admin/utlxpls.sql, you may find these valuable information are missing:

1. estimated execution time of each step
2. estimated temp space needed
3. predication section of execution plan
4. at the end, appearing with a warning message: “PLAN_TABLE is old version”

e.g instead of showing

guang@db11r2>  @?/rdbms/admin/utlxpls.sql

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------
Plan hash value: 103157781

-----------------------------------------------------------------------------
| Id  | Operation         | Name    | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |         |     1 |    14 |    69   (2)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| QUEUE_T |     1 |    14 |    69   (2)| 00:00:01 |
-----------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

 1 - filter("REC_ID"=100)

it shows

idle> @?/rdbms/admin/utlxpls.sql

PLAN_TABLE_OUTPUT
---------------------------------------------------------------------

--------------------------------------------------------------------
| Id  | Operation            |  Name       | Rows  | Bytes | Cost  |
--------------------------------------------------------------------
|   0 | SELECT STATEMENT     |             |     4 |    12 |     2 |
|   1 |  TABLE ACCESS FULL   | QUEUE_T      |     4 |    12 |     2 |
--------------------------------------------------------------------

'PLAN_TABLE' is old version

Since 9i, Oracle create a global temporary table SYS.PLAN_TABLE$, and a public synonym PLAN_TABLE and grant necessary privileges to PUBLIC.

This problem can be caused by a user still using a old version of PLAN_TABLE in a local schema (e.g. the database has been migrated from previous release). Using this query to check which plan_table is using by users

select owner, object_type, object_name
from all_objects
where
object_name like 'PLAN_TABLE%';

OWNER                          OBJECT_TYPE         OBJECT_NAME
------------------------------ ------------------- -----------
SYS                            TABLE               PLAN_TABLE$
PUBLIC                         SYNONYM             PLAN_TABLE
SH                              TABLE               PLAN_TABLE

This problem can be overcome by

log on as user who has a local plan_table, drop the plan_table and run @?/rdbms/admin/utlxplan.sql as sysdba.

Or

explain plan into SYS.PLAN_TABLE$ for <your_stmt>

set lines 120 pages 100

select * from table(dbms_xplan.display(‘SYS.PLAN_TABLE$’));

Compiling package hangs

One customer reports compiling one package hangs for a long time. He confirms to have closing all sessions running that package.

Find who is the running of the package

COLUMN lock_id2 FORMAT A30
select to_char(SESSION_ID,'999') sid ,
   substr(LOCK_TYPE,1,30) Type,
   substr(lock_id1,1,45) Object_Name,
   substr(mode_held,1,4) HELD,
   substr(mode_requested,1,4) REQ,
   lock_id2 lock_addr
FROM dba_lock_internal
WHERE
   mode_requested <> 'None'
   and mode_requested <> mode_held
;

SID  TYPE                                 OBJECT_NAME                                         HELD     REQ  LOCK_ADDR
---- ---------                           --------------------------------------------- ---- ----    ----------------------------------------
 436 Table/Procedure/Type Definitio          CENPROD.TST_PMA                               None Shar  07000001DB7F5C20
 440 Table/Procedure/Type Definitio          CENPROD.TST_PMA                               None Excl  07000001DB7F5C20

The sid=440 is the customer being blocked by compilation, so we killed the session 436 who was running the package and the compilation succeeds.

AIX High Page Faults

Heavy Virtual Memory Page Faults occur on an AIX 5.3 64bit system.

Listing 1.1

┌─topas_nmon──b=Black&White──────Host=aixprd02───────Refresh=2 secs───16:16.11────
│ Hints-Mode-(verbose) ───────────────────────────────────────────────────────────
│  Code    Resource            Stats   Now       Warn    Danger
│      OK -> CPU               %busy  25.9%      >80%    >90%
│      OK -> Paging size       %free  99.7%      <20%    <10%
│      OK -> Paging Space      RAM:pg100.0%      <50%    <10%
│  DANGER -> Page Faults       faults 11402.0    >16/s   >160/s
│ CPU-Utilisation-Small-View ───────────EntitledCPU=  3.00 UsedCPU=  1.656────────
│Logical  CPUs              0----------25-----------50----------75----------100
│CPU User%  Sys% Wait% Idle%|           |            |           |            |
│  0  94.9   4.5   0.1   0.5|UUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUss>
│  1  98.1   1.6   0.0   0.3|UUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUU>
│  2  91.1   8.5   0.1   0.3|UUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUssss>
│  3  47.9  34.9   1.5  15.7|UUUUUUUUUUUUUUUUUUUUUUUsssssssssssssssss         >
│  4  89.4   9.7   0.2   0.8|UUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUssss>|
│  5  51.9  24.4   0.1  23.7|UUUUUUUUUUUUUUUUUUUUUUUUUssssssssssss            >
│  6  27.6  54.9   3.1  14.4|UUUUUUUUUUUUUsssssssssssssssssssssssssssW        >
│  7  96.8   2.6   0.0   0.5|UUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUUs>
│EntitleCapacity/VirtualCPU +-----------|------------|-----------|------------+
│ EC  51.5   3.3   0.1   0.3|UUUUUUUUUUUUUUUUUUUUUUUUUs-----------------------|
│ VP  38.6   2.4   0.0   0.3|UUUUUUUUUUUUUUUUUUUs-----------------------------|
│EC=  55.2%  VP=  41.4%     +--No Cap---|------------|-----------100% VP=4 CPU+
│ Memory ─────────────────────────────────────────────────────────────────────────
│          Physical  PageSpace |        pages/sec  In     Out | FileSystemCache
│% Used       99.8%      0.3%  | to Paging Space   0.0    0.0 | (numperm) 16.8%
│% Free        0.2%     99.7%  | to File System   59.5  242.0 | Process   68.0%
│MB Used   16350.0MB    43.2MB | Page Scans        0.0        | System    15.0%
│MB Free      34.0MB 16340.8MB | Page Cycles       0.0        | Free       0.2%
│Total(MB) 16384.0MB 16384.0MB | Page Steals       0.0        |           ------
│                              | Page Faults   11402.0        | Total    100.0%
│------------------------------------------------------------ | numclient 16.8%
│Min/Maxperm     777MB(  5%)  13986MB( 85%) <--% of RAM       | maxclient 85.4%
│Min/Maxfree     960   1088       Total Virtual   32.0GB      | User      81.4%
│Min/Maxpgahead    2      8    Accessed Virtual   12.9GB 40.2%| Pinned    15.9%
│ Disk-KBytes/second-(K=1024,M=1024*1024) ────────────────────────────────────────
│Disk     Busy  Read  Write 0----------25-----------50------------75--------100
│ Name          KB/s   KB/s |           |            |             |          |
│hdisk3     0%      0      0|                                                 |
│hdisk1     0%      0      0|                                                 |
│hdisk6     0%      0      0|                                                 |
│hdisk7     0%      0      0|                                                 |
│hdisk2     0%      0      0|                                                 |
│hdisk4     0%      0      0|                                                 |
└─────────Warning: Some Statistics may not be shown───────────────────────────────

It has 16GB physical memory and 16GB virtual memory space. This machine is running as Oracle Server 10g and configured with 8G of SGA and 2G of PGA. As recommended by AWR report, the SGA is till undersized and requires to raise from 8GB to 10GB. As the machine is already suffered from high virtual memory faults (as shown above DANGER: 11402/s), it probably worsens the performance. Need to find out what causes such many paging faults.

Listing 1.2

$ vmstat -v
 4194304 memory pages            <= Total RAM = 4194304 * 4KB = 16GB
 3978160 lruable pages
 5122 free pages
 2 memory pools
 669610 pinned pages
 80.0 maxpin percentage
 5.0 minperm percentage      <= system's minperm for permenent storage (approximately File systems of Oracle)
 90.0 maxperm percentage      <= system's minperm for permenent storage (approximately File systems of Oracle)
 17.1 numperm percentage
 682216 file pages              <= actually cached in permenent storage of RAM
 <= (File systems inlcuding JFS, JFS2 and NFS)
 0.0 compressed percentage
 0 compressed pages
 17.1 numclient percentage
 90.0 maxclient percentage
 682216 client pages            <= among total files pages cached(=682216),
 <= the File systems on JFS2 and NFS are client pages (=682216)
 0 remote pageouts scheduled
 1827 pending disk I/Os blocked with no pbuf
 0 paging space I/Os blocked with no psbuf
 2228 filesystem I/Os blocked with no fsbuf
 0 client filesystem I/Os blocked with no fsbuf
 4563 external pager filesystem I/Os blocked with no fsbuf
 0 Virtualized Partition Memory Page Faults
 0.00 Time resolving virtualized partition memory page faults

 

Reference

Overview of AIX page replacement

Values for minperm and maxperm parameters

Running Oracle on AIX

Tnsping responses very slowly

The Oracle database 10g is running on Oracle Enterprise Linux 5. When I tried to connect to it through Oracle Net service or just tnsping it, it responses very slowly!

[oracle@gchen-pc admin]$ tnsping DB11R2

TNS Ping Utility for Linux: Version 11.2.0.1.0

Copyright (c) 1997, 2009, Oracle.  All rights reserved.

Used parameter files:
Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = gchen-pc.localdomain)(PORT = 1521))) (CONNECT_DATA = (SERVICE_NAME = DB11R2)))
OK <strong>(6030 msec)</strong>

However if I tnsping it using a physical IP, it then responses right away.

[oracle@gchen-pc admin]$ tnsping db11r2

TNS Ping Utility for Linux: Version 11.2.0.1.0

Copyright (c) 1997, 2009, Oracle.  All rights reserved.

Used parameter files:

Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = <strong>192.168.56.10</strong>)(PORT = 1521))) (CONNECT_DATA = (SERVICE_NAME = DB11R2)))
<strong>OK (0 msec)</strong>

So the slow connection must be caused by a poor DNS Resolution, rather than a slow network connection.

Check out the DNS server setting, I realized that recently I worked in a customer network, thus DNS server is still pointing to customer’s DNS server address which is not valid now.

The hostname resolution can also be performed by /etc/hosts as

192.168.56.10 gchen-pc.localdomain gchen-pc

The order in which Linux searches to resolve hostname is configured in files/etc/nsswitch.conf and /etc/host.conf

In file /etc/nsswitch.conf, look for

</pre>
<code>hosts:          files dns mdns4</code>
<pre>

In file /etc/host.conf, look for

</pre>
<code>order hosts,bind</code>
<pre>

where ‘hosts’ refers to /etc/hosts file and ‘bind’ refers DNS resolution.

Check the nsswitch.conf and host.conf man pages for details.

Automating Startup/Shutdown of Oracle Instance, Listener and DB Control on AIX

To automate startup/shutdown Oracle instance using dbstart and dbshut scripts

1) Ensure /etc/oratab contains the following line:

<SID>:<ORACLE_HOME>:Y

where Y indicates that it wants dbstart and dbshut to startup or to shutdown instance.

2) Login as root user and create a script named /etc/dboracle with the following lines:

#! /bin/sh  -x
#
# Change the value of ORACLE_HOME to specify the correct Oracle home
# directory for your installation.

ORACLE_HOME=/gold/GLT/bin/oracle/product/10.2.0/db_1
#
# Change the value of ORACLE to the login name of the
# oracle owner at your site.
#
ORACLE=gltoracle
ORACLE_SID=GOLDTEST
PATH=${PATH}:$ORACLE_HOME/bin
HOST=`hostname`
PLATFORM=`uname`
export ORACLE_HOME ORACLE_SID PATH

case $1 in
'start')
        # Listener and instance are started by 'dbstart'
        su - $ORACLE $ORACLE_HOME/bin/dbstart $ORACLE_HOME >> /var/log/oracle 2>&1 &
        ;;
'stop')
        su - $ORACLE  $ORACLE_HOME/bin/emctl stop dbconsole >> /var/log/oracle
        # Listener and instance are shut down by 'dbstart'
        su - $ORACLE  $ORACLE_HOME/bin/dbshut $ORACLE_HOME >> /var/log/oracle 2>&1 &
        ;;
*)
        echo "usage: $0 {start|stop}"
        exit
        ;;
esac
#
exit

Note: This script can only stop Oracle Net listener for which a password has not been set. In addition, if the listener name is not the default name, LISTENER, then you must specify the listener name in the stop and start commands:

$ORACLE_HOME/bin/lsnrctl {start|stop} listener_name

3) Change the group of the dboracle file to the OSDBA group (typically dba), and set the permissions to 750

# chgrp dba dboracle
# chmod 750 dboracle

4) Create symbolic links to the dboracle script in the appropriate run-level script directories as follows.

# ln -s /etc/dboracle /etc/rc.d/rc2.d/S99dbora
# ln -s /etc/dboracle /etc/rc.d/rc2.d/K01dbora

For understanding how these scripts are used, refer to metalink 1019817.6.

5) Reboot the machine to check.