Note on cursor

Note on open cursors
Good REF: http://orafaq.com/node/758

-- Total cursors open, by session
select a.value, s.username, s.sid, s.serial#
from v$sesstat a, v$statname b, v$session s
where a.statistic# = b.statistic# and s.sid=a.sid
and b.name = 'opened cursors current';
-- Total open cursors, by username & machine select sum(a.value) total_cur, avg(a.value) avg_cur, max(a.value) max_cur, s.username, s.machine from v$sesstat a, v$statname b, v$session s where a.statistic# = b.statistic# and s.sid=a.sid and b.name = 'opened cursors current' group by s.username, s.machine order by 1 desc;


Sample Oracle 9i network files (listener)

******************************************************************
* listener.ora
******************************************************************

# LISTENER.ORA Network Configuration File: /opt/oracle/product/9.2.0/network/admin/listener.ora
# Generated by Oracle configuration tools.

LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = plum)(PORT = 1521))
)
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC))
)
)
)

SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(SID_NAME = PLSExtProc)
(ORACLE_HOME = /opt/oracle/product/9.2.0)
(PROGRAM = extproc)
)
(SID_DESC =
(GLOBAL_DBNAME = thenidb)
(ORACLE_HOME = /opt/oracle/product/9.2.0)
(SID_NAME = nidb)
)
)


My 9i sqlnet

******************************************************************
* sqlnet.ora
******************************************************************

# SQLNET.ORA Network Configuration File: /opt/oracle/product/9.2.0/network/admin/sqlnet.ora
# Generated by Oracle configuration tools.

NAMES.DIRECTORY_PATH= (TNSNAMES)


9i tnsnames

******************************************************************
* tnsnames.ora
******************************************************************

# TNSNAMES.ORA Network Configuration File: /opt/oracle/product/9.2.0/network/admin/tnsnames.ora
# Generated by Oracle configuration tools.

NAZRULDB =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = plum)(PORT = 1521))
)
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = nazruldb)
)
)

INST1_HTTP =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = plum)(PORT = 1521))
)
(CONNECT_DATA =
(SERVER = SHARED)
(SERVICE_NAME = MODOSE)
(PRESENTATION = http://HRService)
)
)

EXTPROC_CONNECTION_DATA =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC))
)
(CONNECT_DATA =
(SID = PLSExtProc)
(PRESENTATION = RO)
)
)


10g Standby database

Creating a Physical Standby Database

On primary database

ALTER DATABASE FORCE LOGGING;

Did this on primary and then dropped, still not 100% sure where this goes.

ALTER DATABASE ADD STANDBY LOGFILE GROUP 10

('/u01/oracle/oradata/X1AR/redo01_stb.log') SIZE 50M;

ALTER DATABASE ADD STANDBY LOGFILE GROUP 11

('/u02/oracle/oradata/X1AR/redo02_stb.log') SIZE 50M;

ALTER DATABASE ADD STANDBY LOGFILE GROUP 12

('/u03/oracle/oradata/X1AR/redo03_stb.log') SIZE 50M;

ALTER DATABASE ADD STANDBY LOGFILE GROUP 13

('/u03/oracle/oradata/X1AR/redo04_stb.log') SIZE 50M;

SELECT GROUP#,THREAD#,SEQUENCE#,ARCHIVED,STATUS FROM V$STANDBY_LOG;

Create a password file if one does not already exist. Note password of sys needs to be same on both primary and standby.

For ease of modify parameters, I am making the primary database use pfile

create pfile from spfile;

cd /u01/oracle/product/10.2.0/dbs/

mv spfileX1DB.ora x_spfileX1DB.ora

shut immediate;

startup;

Parameters for primary database:

*.DB_NAME=X1DB

*.DB_UNIQUE_NAME=X1DB

*.LOG_ARCHIVE_CONFIG='DG_CONFIG=(X1DB,Z1DB)'

*.CONTROL_FILES='/u01/oracle/oradata/X1DB/control01.ctl','/u02/oracle/oradata/X1DB/control02.ctl','/u03/oracle/oradata/X1DB/control03.ctl'

*.LOG_ARCHIVE_DEST_1='LOCATION=/u04/oracle/arch_X1DB/ VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=X1DB'

*.LOG_ARCHIVE_DEST_2='SERVICE=Z1DB LGWR ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=Z1DB'

*.LOG_ARCHIVE_DEST_STATE_1=ENABLE

*.LOG_ARCHIVE_DEST_STATE_2=ENABLE

*.REMOTE_LOGIN_PASSWORDFILE=EXCLUSIVE

*.LOG_ARCHIVE_FORMAT=%t_%s_%r.arc

*.LOG_ARCHIVE_MAX_PROCESSES=30

*.FAL_SERVER=Z1DB

*.FAL_CLIENT=X1DB

*.DB_FILE_NAME_CONVERT='Z1DB','X1DB'

*.LOG_FILE_NAME_CONVERT='/u01/oracle/oradata/Z1DB/','/u01/oracle/oradata/X1DB/','/u02/oracle/oradata/Z1DB/','/u02/oracle/oradata/X1DB/','/u03/oracle/oradata/Z1DB/','/u03/oracle/oradata/X1DB/'

*.STANDBY_FILE_MANAGEMENT=AUTO

Make sure primary database in ARCHIVELOG mode and restart

shut immediate;

startup mount;

ALTER DATABASE ARCHIVELOG;

alter database open;

mkdir /u01/oracle/oradata/Z1DB

mkdir /u02/oracle/oradata/Z1DB

mkdir /u03/oracle/oradata/Z1DB

mkdir /u04/oracle/ arch_Z1DB

Backup the primary database, I performed a cold backup, then startup priamy.

startup mount;

ALTER DATABASE CREATE STANDBY CONTROLFILE AS '/tmp/Z1DB.ctl';

alter database open;

Copy control file to standby box

cd /u01/oracle/oradata/Z1DB

scp oracle@lnx-suppdb1:/tmp/Z1DB.ctl .

mv Z1DB.ctl control01.ctl

cp control01.ctl /u02/oracle/oradata/Z1DB/control02.ctl

cp control01.ctl /u03/oracle/oradata/Z1DB/control03.ctl

Copy pfile from primary

cd $ORACLE_HOME/dbs

scp oracle@lnx-suppdb1:$ORACLE_HOME/dbs/initX1DB.ora .

mv initX1DB.ora initZ1DB.ora

Modify the standby parameters.

Make sure all the bdump, cdump… folder are in correct location.

Create password file on standby (same passwd as primary)

orapwd file=$ORACLE_HOME/dbs/orapwZ1DB password=oracle entries=4

Make sure Primary box password file there, or create new one:

orapwd file=$ORACLE_HOME/dbs/orapwX1DB password=oracle entries=4

Make sure entries in tnsnames files and listener know about the databases.

Startup standby database:

export ORACLE_SID=Z1DB

First time round gave error since pfile did not have primary db’s SID, it should have X1DB.

SQL> STARTUP MOUNT;

ORACLE instance started.

Total System Global Area 1879048192 bytes

Fixed Size 2021664 bytes

Variable Size 1308624608 bytes

Database Buffers 553648128 bytes

Redo Buffers 14753792 bytes

ORA-01103: database name 'X1DB' in control file is not 'Z1DB'

SQL> !

sqlplus /nolog

STARTUP MOUNT;

ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION;

Error on primary database:

Getting error on primary (alert file), fix was to shutdown both primary and sandby then reviewed both init files and tnsnames files, corrected variables and removed extra spaces from end.

Sat Nov 24 02:40:11 2007

Error 12154 received logging on to the standby

Sat Nov 24 02:40:11 2007

Errors in file /u01/oracle/admin/X1DB/bdump/x1db_arcp_14281.trc:

ORA-12154: Message 12154 not found; No message file for product=RDBMS, facility=ORA

PING[ARCp]: Heartbeat failed to connect to standby 'Z1DB'. Error is 12154.

Test Scenario 1

On primary, switch log file.

ALTER SYSTEM SWITCH LOGFILE;

ALTER SYSTEM SWITCH LOGFILE;

Alert of standby shows:

Sat Nov 24 22:05:00 2007

RFS[15]: Archived Log: '/u04/oracle/arch_Z1DB/1_44_639415217.arc'

Primary database is in MAXIMUM PERFORMANCE mode

RFS[15]: No standby redo logfiles created

Sat Nov 24 22:05:05 2007

Media Recovery Log /u04/oracle/arch_Z1DB/1_44_639415217.arc

Media Recovery Waiting for thread 1 sequence 45 (in transit)

Test successful no the standby file system, can see:

/u04/oracle/arch_Z1DB

[oracle@lnx-qadb1 arch_Z1DB]$ ls -alrt

total 317556

..

-rw-r----- 1 oracle dba 4941824 Nov 24 22:05 1_44_639415217.arc

-rw-r----- 1 oracle dba 1024 Nov 24 22:29 1_46_639415217.arc

-rw-r----- 1 oracle dba 1398784 Nov 24 22:29 1_45_639415217.arc

..

Test Scenario 2

Execute below SQL on both primary and standby:

SELECT SEQUENCE#, FIRST_TIME, NEXT_TIME FROM V$ARCHIVED_LOG ORDER BY SEQUENCE#

Test successful, saw data on both, similar numbers

Test Scenario 3

On primary, add new tablespace

create tablespace INDEXS datafile '/u01/oracle/oradata/X1DB/X1DB_INDEXS_01.dbf'

size 51264K autoextend off logging extent management local

uniform size 512K segment space management auto;

ALTER SYSTEM SWITCH LOGFILE;

ALTER SYSTEM SWITCH LOGFILE;

Alert of standby shows alert switches and adding of tablespace (test successful):

Sat Nov 24 21:48:48 2007

Media Recovery Log /u04/oracle/arch_Z1DB/1_41_639415217.arc

Recovery created file /u01/oracle/oradata/Z1DB/Z1DB_ INDEXS_01.dbf

Successfully added datafile 6 to media recovery

Datafile #6: '/u01/oracle/oradata/Z1DB/Z1DB_INDEXS_01.dbf'

Media Recovery Log /u04/oracle/arch_Z1DB/1_42_639415217.arc

Media Recovery Log /u04/oracle/arch_Z1DB/1_43_639415217.arc

Media Recovery Waiting for thread 1 sequence 44 (in transit)

Alhamdulillah saw Physical Standby 10g working successfully.

Did nothing more, for next test round, do more detail work post setup, e.g. how to everyday maintenance on standby.

SQL tracing

Session SQL trace:

alter session set sql_trace=true;

Other session SQL trace:

exec dbms_system.set_sql_trace_in_session(session_id, serial_id, true);

Auto trace

CONNECT / AS SYSDBA
@$ORACLE_HOME/SQLPLUS/ADMIN/PLUSTRCE.SQL

drop role plustrace;
Role dropped.
create role plustrace;
Role created.
.
grant plustrace to dba with admin option;
Grant succeeded.

GRANT PLUSTRACE TO HR;

Other session SQL trace:

SET AUTOTRACE ON

Eazy connect

Eazy connect, does not use TNSnames file
sqlplus system/oracle@192.168.1.0:1521/NIDB


TNS name files was there but spelling was wrong, 's' was missing tnsnames.ora file

[foglight5@lnx-quest admin]$ tnsping ACTIVE.LNX-ITDB1

TNS Ping Utility for Linux: Version 10.2.0.1.0 - Production on 19-NOV-2007 16:35:47

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

Used parameter files:
/home/foglight5/oracle/product/10.2.0/network/admin/sqlnet.ora

TNS-03505: Failed to resolve name

Default Router Info

As a quick reference, this table shows the default addresses, usernames, and passwords for some common router manufacturers.

Username   Password
======               =======              ========   ========
3Com                 http://192.168.1.1   admin      admin
D-Link               http://192.168.0.1   admin     
Linksys              http://192.168.1.1   admin      admin
Microsoft Broadband  http://192.168.2.1   admin      admin
Netgear              http://192.168.0.1   admin      password

SqlPlus help

http://download.oracle.com/docs/cd/B19306_01/server.102/b14357/ch2.htm

http://forums.oracle.com/forums/thread.jspa?threadID=585195&tstart=0

> Changing the default editor to Textpad for sqlplusw

In login.sql or glogin.sql put following:
DEFINE _EDITOR=yourFavouriteProgram.exe

start new sqlplus instance. It should now run your modified login/glogin.sql and use the new editor as default.

Tuning Shared Pool

select NAMESPACE,
GETS,
GETHITS,
round(GETHITRATIO*100,2) gethit_ratio,
PINS,
PINHITS,
round(PINHITRATIO*100,2) pinhit_ratio,
RELOADS,
INVALIDATIONS
from v$librarycache

SELECT shared_pool_size_for_estimate "Size of Shared Pool in MB",
shared_pool_size_factor "Size Factor",
estd_lc_time_saved "Time Saved in sec"
FROM v$shared_pool_advice;


Good reply bor Johthan Lewis
http://forums.oracle.com/forums/thread.jspa?threadID=587990&tstart=0


V$LIBRARYCACHE
http://www.fortunecity.com/skyscraper/oracle/699/orahtml/hayden/libcache.html

How to Calculate Your Shared Pool Size
Note: 1012046.6


Good doc on tuning shared pool
Metalink doc: 62143.1

Shared Pool Information
http://vsbabu.org/oracle/sect13.html

Unserstaing shared pool
http://download-uk.oracle.com/oowsf2005/003wp.pdf

Oracle Tuning from Uni
http://www.uaex.edu/srea/tuning.htm


Shared Pool Advisory
====================

SHOW PARAMETER statistics_level;

SELECT shared_pool_size_for_estimate,
shared_pool_size_factor,
estd_lc_time_saved
FROM v$shared_pool_advice;

http://www.dbasupport.com/oracle/ora9i/shared_pool.shtml

http://www.orafaq.com/scripts/performance/orayaps.txt

Tax office

0845 302 1455 - Craig
0845 300 3949 - Sape
0845 302 1409 - JP