Oracle DBA's Web

By default, PMON will register the database service with the listener on port 1521.if your listener is running on non-default port i.e 1522 you must add

Local_listener parameter

C:\Documents and Settings\Iqbal.Aulakh>lsnrctl start

LSNRCTL for 32-bit Windows: Version 10.2.0.5.0 - Production on 20-JAN-2011 16:34:15

Copyright (c) 1991, 2010, Oracle. All rights reserved.

Starting tnslsnr: please wait...

TNSLSNR for 32-bit Windows: Version 10.2.0.5.0 - Production

System parameter file is D:\Oracle\app\oracle\product\10.2.0\db_1\network\admin\listener.ora

Log messages written to D:\Oracle\app\oracle\product\10.2.0\db_1\network\log\listener.log

Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(PIPENAME=\\.\pipe\EXTPROC1ipc)))

Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=somxl042707.accounts.cdcr.ca.gov)(PORT=1522)))

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=somxl042707.accounts.cdcr.ca.gov)(PORT=1522)))

STATUS of the LISTENER

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

Alias LISTENER

Version TNSLSNR for 32-bit Windows: Version 10.2.0.5.0 - Production

Start Date 20-JAN-2011 16:34:17

Uptime 0 days 0 hr. 0 min. 3 sec

Trace Level off

Security ON: Local OS Authentication

SNMP OFF

Listener Parameter File D:\Oracle\app\oracle\product\10.2.0\db_1\network\admin\listener.ora

Listener Log File D:\Oracle\app\oracle\product\10.2.0\db_1\network\log\listener.log

Listening Endpoints Summary...

(DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(PIPENAME=\\.\pipe\EXTPROC1ipc)))

(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=somxl042707.accounts.cdcr.ca.gov)(PORT=1522)))

Services Summary...

Service "PLSExtProc" has 1 instance(s).

Instance "PLSExtProc", status UNKNOWN, has 1 handler(s) for this service...

The command completed successfully

C:\Documents and Settings\Iqbal.Aulakh>

USING A NON DEFAULT LISTENER

When a non-default listener is used, then a listener.ora must be configured with the relevant listener address. For example,

LISTENER =

(DESCRIPTION_LIST =

(DESCRIPTION =

(ADDRESS = (PROTOCOL = TCP)(HOST = host1.sales.mydomain.com)(PORT = 1522))

(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1522))

)

In the database

Change/add the parameter

LOCAL_LISTENER=listener_t10g

If you try to start now, you will receive error

ORACLE instance shut down.

SQL> startup nomount pfile='D:\Oracle\app\oracle\oradata\test\test-temp-pfile.ora'

ORA-00119: invalid specification for system parameter LOCAL_LISTENER

ORA-00132: syntax error or unresolved network name 'LISTENER_T10G'You are getting error because PMON unable to resolve the LISTENER_TEST10G.

You need to add TNS Entry in you TNSNAMES.ORA file

LISTENER_T10G =

(DESCRIPTION =

(ADDRESS = (PROTOCOL = TCP)(HOST = host1.sales.mydomain.com)(PORT = 1522))

(CONNECT_DATA =

(SERVER = DEDICATED)

(SERVICE_NAME = test10g)

)

Note:

Make sure your ORACLE_HOME and path variable are also pointing to the correct oracle_home. Even if you set the correct TNS_ADMIN, still you will get above error as It will ignore the tns_admin and look for the tnsnames.ora,sqlnet.ora in you database's Oracle_home. This is applicable when you are having multiple oracle home on the same machine.
On Windows System, make sure you edit the registry and add the TNS_ADMIN entry in your ORACLE_HOME.
i.e In HKEY_LOCAL_MACHINE\SOFTWARE\ORACLE\KEY_OraDb10g_home1
Create New String Key TNS_ADMIN and modify to add the path to your ORACL_HOME/NETWORK/ADMIN directory.

Create Physical Standby- 10g (10.2.0.5.0)

Enable force logging

Place the primary database in FORCE LOGGING mode after database creation using the following SQL statement:

ALTER DATABASE FORCE LOGGING;

SQL> ALTER DATABASE FORCE LOGGING;

Database altered.

SQL> col FORCE_LOGGING for a20

SQL> SELECT FORCE_LOGGING FROM V$DATABASE;

Create a Password File

SQL> select * from v$pwfile_users;

USERNAME SYSDB SYSOP

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

SYS TRUE TRUE

SQL>

If it doesn't exist, use the following command to create one:

- On Windows:
$cd %ORACLE_HOME%\database
$orapwd file=pwdPRIM.ora password=xxxxxxxx force=y
(Note: Replace xxxxxxxxx with the password for the SYS user.)

- On UNIX:

$Cd $ORACLE_HOME/dbs
$Orapwd file=pwdPRIM.ora password=xxxxxxxx force=y
(Note: Replace xxxxxxxxx with your actual password for the SYS user.)

Configure a Standby Redo log.

The size of the standby redo log files should match the size of the current Primary database online redo log files. To find out the size of your online redo log files:
SQL> col member for a100
SQL> select bytes from v$log;

BYTES
----------
52428800
52428800
52428800
Use the following command to determine your current log file groups:
SQL> set line 200
SQL> select group#, member from v$logfile;

GROUP# MEMBER
---------- -----------------------------------------------
3 D:\ORACLE\APP\ORACLE\ORADATA\TESTDG\REDO03.LOG
2 D:\ORACLE\APP\ORACLE\ORADATA\TESTDG\REDO02.LOG
1 D:\ORACLE\APP\ORACLE\ORADATA\TESTDG\REDO01.LOG

SQL>
Create standby Redo log groups.
My primary database had 3 log file groups originally and I created 3 standby redo log groups using the following commands:

SQL> ALTER DATABASE ADD STANDBY logfile GROUP 4 'D:\Oracle\app\oracle\oradata\testdg\stdredo04' SIZE 50M;

Database altered.

SQL> ALTER DATABASE ADD STANDBY logfile GROUP 5 'D:\Oracle\app\oracle\oradata\testdg\stdredo05' SIZE 50M;

Database altered.

SQL> ALTER DATABASE ADD STANDBY logfile GROUP 6 'D:\Oracle\app\oracle\oradata\testdg\stdredo06' SIZE 50M;

Database altered.
SQL> select group#, member from v$logfile;

GROUP# MEMBER
---------- ----------------------------------------------------------------------------------------------------
3 D:\ORACLE\APP\ORACLE\PRODUCT\10.2.0\ORADATA\TEST10G\REDO03.LOG
2 D:\ORACLE\APP\ORACLE\PRODUCT\10.2.0\ORADATA\TEST10G\REDO02.LOG
1 D:\ORACLE\APP\ORACLE\PRODUCT\10.2.0\ORADATA\TEST10G\REDO01.LOG
4 D:\ORACLE\APP\ORACLE\PRODUCT\10.2.0\ORADATA\TEST10G\STDREDO04
5 D:\ORACLE\APP\ORACLE\PRODUCT\10.2.0\ORADATA\TEST10G\STDREDO05
6 D:\ORACLE\APP\ORACLE\PRODUCT\10.2.0\ORADATA\TEST10G\STDREDO06

6 rows selected.

SQL>
To verify the results of the standby redo log groups creation, run the following query:
SQL> select group#, member from v$logfile;

GROUP# MEMBER
---------- ----------------------------------------------
3 D:\ORACLE\APP\ORACLE\ORADATA\TESTDG\REDO03.LOG
2 D:\ORACLE\APP\ORACLE\ORADATA\TESTDG\REDO02.LOG
1 D:\ORACLE\APP\ORACLE\ORADATA\TESTDG\REDO01.LOG
4 D:\ORACLE\APP\ORACLE\ORADATA\TESTDG\STDREDO04
5 D:\ORACLE\APP\ORACLE\ORADATA\TESTDG\STDREDO05
6 D:\ORACLE\APP\ORACLE\ORADATA\TESTDG\STDREDO06

6 rows selected.

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

GROUP# THREAD# SEQUENCE# ARC STATUS
---------- ---------- ---------- --- ----------
4 0 0 YES UNASSIGNED
5 0 0 YES UNASSIGNED
6 0 0 YES UNASSIGNED

SQL>

Create pfile from spfile

SQL> show parameter spfile

NAME TYPE VALUE

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

spfile string D:\ORACLE\APP\ORACLE\ADMIN\TES

TDG\PFILE\SPFILETESTDG.ORA

SQL> SQL> create pfile='D:\ORACLE\APP\ORACLE\PRODUCT\10.2.0\ORADATA\TEST10G\Pfile-temp-test10g-for-stdby.ora' from spfile;

File created.

SQL>

HERE IS THE CONTENT OF PFILE

testdg.__db_cache_size=301989888

testdg.__java_pool_size=4194304

testdg.__large_pool_size=4194304

testdg.__shared_pool_size=117440512

testdg.__streams_pool_size=0

*.audit_file_dest='D:\Oracle\app\oracle\admin\testdg\adump'

*.background_dump_dest='D:\Oracle\app\oracle\admin\testdg\bdump'

*.compatible='10.2.0.5.0'

*.control_files='D:\Oracle\app\oracle\oradata\testdg\control01.ctl','D:\Oracle\app\oracle\oradata\testdg\control02.ctl','D:\Oracle\app\oracle\oradata\testdg\control03.ctl'

*.core_dump_dest='D:\Oracle\app\oracle\admin\testdg\cdump'

*.db_block_size=8192

*.db_domain=''

*.db_file_multiblock_read_count=16

*.db_recovery_file_dest='D:\Oracle\app\oracle\flash_recovery_area'

*.db_recovery_file_dest_size=8388608000

*.dispatchers='(PROTOCOL=TCP) (SERVICE=testdgXDB)'

*.job_queue_processes=10

*.open_cursors=300

*.pga_aggregate_target=144703488

*.processes=500

*.remote_login_passwordfile='EXCLUSIVE'

*.sessions=555

*.sga_target=434110464

*.undo_management='AUTO'

*.undo_tablespace='UNDOTBS1'

*.user_dump_dest='D:\Oracle\app\oracle\admin\testdg\udump'

DB_NAME=testdg

DB_UNIQUE_NAME=testdg

LOG_ARCHIVE_CONFIG='DG_CONFIG=(testdg,testdgs)'

*.control_files='D:\Oracle\app\oracle\oradata\testdg\control01.ctl','D:\Oracle\app\oracle\oradata\testdg\control02.ctl','D:\Oracle\app\oracle\oradata\testdg\control03.ctl'

LOG_ARCHIVE_DEST_1=

'LOCATION=USE_DB_RECOVERY_FILE_DEST

VALID_FOR=(ALL_LOGFILES,ALL_ROLES)

DB_UNIQUE_NAME=testdg'

LOG_ARCHIVE_DEST_2=

'SERVICE=testdgs LGWR ASYNC

VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE)

DB_UNIQUE_NAME=testdgs'

LOG_ARCHIVE_DEST_STATE_1=ENABLE

LOG_ARCHIVE_DEST_STATE_2=ENABLE

REMOTE_LOGIN_PASSWORDFILE=EXCLUSIVE

LOG_ARCHIVE_FORMAT='ARC%S_%R.%T'

LOG_ARCHIVE_MAX_PROCESSES=30

######Primary Database: Standby Role Initialization Parameters##########

FAL_SERVER=testdgs

FAL_CLIENT=tet10g

DB_FILE_NAME_CONVERT='D:\app\oracle\oradata\testdgs','D:\Oracle\app\oracle\oradata\testdg'

LOG_FILE_NAME_CONVERT='D:\app\oracle\oradata\testdgs','D:\Oracle\app\oracle\oradata\testdg','D:\app\oracle\product\flash_recovery_area','D:\Oracle\app\oracle\flash_recovery_area'

STANDBY_FILE_MANAGEMENT=AUTO

3.1.5 Enable Archiving

If archiving is not enabled, issue the following statements to put the primary database in ARCHIVELOG mode and enable automatic archiving:

SQL> SHUTDOWN IMMEDIATE;

SQL> STARTUP MOUNT;

SQL> ALTER DATABASE ARCHIVELOG;

SQL> ALTER DATABASE OPEN;

SQL> archive log list

Database log mode Archive Mode

Automatic archival Enabled

Archive destination USE_DB_RECOVERY_FILE_DEST

Oldest online log sequence 3

Next log sequence to archive 5

Current log sequence 5

SQL>

Create standby controle file

SQL> alter database create standby controlfile as 'D:\ORACLE\APP\ORACLE\ORADATA\standctrl.ctl';

Database altered.

SQL>

Wednesday, January 19, 2011

How to Install MS Windows Services For Oracle Middleware 11g Rel 1 WebLogic Admin and Managed Servers? [ID 1060058.1]

Note: Document copied from support.oracle.com for personal reference

Applies to: Oracle Weblogic Server - Version: 10.3.1 to 10.3.3
Oracle Fusion Middleware - Version: 11.1.1.1.0 to 11.1.1.3.0]
Generic Windows

Goal

By default the only Fusion Middleware 11g components configured as MS Windows Services by the install process are:

Weblogic Node Manager
(configured as a MS Windows Service if Custom Install route is chosen when installing Weblogic)
FMW 11g Process Manager (OPMN)
(installed as a MS Windows Service if a System Component Instance is configured)

How to install MS Windows Services for Fusion Middleware (FMW) 11g Weblogic Admin or Managed Servers?

How To Identify High Number Of Child Cursors That Can Be The Root Cause For ORA-600 [17059] [ID 1213715.1]

Note: This document is copied from support.oracle.com

Applies to:
Oracle Server - Enterprise Edition - Version: 8.1.7.0 and later [Release: 8.1.7 and later ]
Information in this document applies to any platform.
Goal
Is to find high number of child cursor that are not shared and can be related to
know bugs. This can be done by using views V$SQLAREA and V$SQL_SHARED_CURSOR.
Complete list why cursors are not shared is available in the documentation for
V$SQL_SHARED_CURSOR.

RAC Cheatsheet

This is a quick cheat sheet on Oracle RAC 10g and is a beginners guide on the commands and information that you may require to administer Oracle RAC.
Acronyms

Acronyms
GCS	Global Cache Services	in memory database containing current locks and awaiting locks, also known as PCM
GES	Global Enqueue Services	coordinates the requests of all global enqueues uses the GCS, also known as non-PCM
GRD	Global Resource Directory	all resources available to the cluster (formed and managed by GCS and GES), see GRD for more details
GRM	Global Resource Manager	helps to coordinate and communicate the locks requests between Oracle processes
GSD	Global Services Daemon	runs on each node with one GSD process per node. The GSD coordinates with the cluster manager to receive requests from clients such as the DBCA, EM, and the SRVCTL utility to execute administrative job tasks such as instance startup or shutdown. The GSD is not an Oracle instance background process and is therefore not started with the Oracle instance
PCM (IDLM)	Parallel Cache Management	formly know as (integrated) Distributed Lock Manager, its another name for GCS
Resource	n/a	it is a identifiable entity it basically has a name or a reference, it can be a area in memory, a disk file or an abstract entity
Resource (Global)	n/a	a resource that can be accessed by all the nodes within the cluster examples would be the following Data Buffer Cache Block Transaction Enqueue Database Data Structures
LVB	Lock Value Block	contains a small amount of data regarding the lock
TRFC	Traffic Controller	controls the DLM traffic between instances (messaging tickets)

ASM Instance Creation on Windows and Move Database to ASM

ASM Instance Creation

Creating a dummy disks

First Create Folder where you want to store the asmdiks on window
and Then Create Three Disks of 2GB Each with the asmtool command

asmtool -create <Full Path with File Name> <Size in MB>

D:\>cd D:\Oracle\app\oracle

D:\Oracle\app\oracle>mkdir asmdisks

D:\Oracle\app\oracle>cd asmdisks

D:\Oracle\app\oracle\asmdisks>asmtool -create D:\Oracle\app\oracle\asmdisks\disk1 2046

D:\Oracle\app\oracle\asmdisks>asmtool -create D:\Oracle\app\oracle\asmdisks\disk2 2046

D:\Oracle\app\oracle\asmdisks>asmtool -create D:\Oracle\app\oracle\asmdisks\disk3 2046

D:\Oracle\app\oracle\asmdisks>asmtool -create D:\Oracle\app\oracle\asmdisks\disk4 2046

D:\Oracle\app\oracle\asmdisks>asmtool -create D:\Oracle\app\oracle\asmdisks\disk5 2046

Oracle DBA's Web

Monday, January 24, 2011

Create Physical Standby- 10g (10.2.0.5.0)

Enable force logging

Create a Password File

Configure a Standby Redo log.

Create pfile from spfile

3.1.5 Enable Archiving

Create standby controle file

Thursday, January 20, 2011

How to Use Non-Default Listner WITH LOCAL_LISTENER PARAMETER

USING A NON DEFAULT LISTENER

Create Physical Standby- 10g (10.2.0.5.0)

Enable force logging

Create a Password File

Configure a Standby Redo log.

Create pfile from spfile

3.1.5 Enable Archiving

Create standby controle file

Wednesday, January 19, 2011

How to Install MS Windows Services For Oracle Middleware 11g Rel 1 WebLogic Admin and Managed Servers? [ID 1060058.1]

Goal

How To Identify High Number Of Child Cursors That Can Be The Root Cause For ORA-600 [17059] [ID 1213715.1]

Tuesday, January 18, 2011

RAC Cheatsheet

Saturday, January 15, 2011

ASM Instance Creation on Windows and Move Database to ASM

ASM Instance Creation

Creating a dummy disks