ORACLE DBA Knowledge Share: 2007

10g R2 RAC on vmware

after burning midnight oils for many days I was able to get 10g RAC installed on windows 2003 enterprize edition using vmware. I later installed 10g RAC on linux later in the year. Below links were major help to me.

http://www.oracle-base.com/articles/10g/OracleDB10gR2RACInstallationOnWindows2003UsingVMware.php

http://www.dbasupport.com/oracle/ora10g/RACingAhead0101.shtml

http://forums.oracle.com/forums/thread.jspa?messageID=1123638

http://www.idevelopment.info/data/Oracle/DBA_tips/Oracle10gRAC/CLUSTER_14.shtml#Overview

http://www.oracle.com/technology/obe/10gr2_db_vmware/manage/clusterintro/clusterintro.htm

After long work and long sleep on blog I'm presenting some more useful links:

-- Listed in order as I found them and kept in store for U in random fashion --
http://www.vmware.com/community/thread.jspa?messageID=737640

http://dba.ipbhost.com/index.php?showtopic=8138

http://edelivery.oracle.com/EPD/GetUserInfo/get_form?caller=LinuxWelcome
http://www.oracle.com/technology/pub/articles/calish_file_commands.html

http://forums.oracle.com/forums/thread.jspa?threadID=345155

http://esemrick.blogspot.com/

http://www.oracle.com/technology/oramag/oracle/04-jul/index.html

http://download.oracle.com/docs/cd/B28359_01/server.111/b28310/undo007.htm

http://advancyn.com/advancyn/

http://www.pythian.com/blogs/279/failovers-with-oracle-dataguard

http://www.bloggingaboutoracle.org/archives/poc-maa-blog-10-implementing-a-physical-standby-database

http://www.orafaq.com/forum/t/70522/0/

http://www.idevelopment.info/cgi/ORACLE_dba_tips.cgi

http://www.bijoos.com/oracle/unix4dba1.htm

http://www.idevelopment.info/data/Oracle/DBA_tips/Unix/UNIX_3.shtml

http://www.idevelopment.info/cgi/ORACLE_dba_tips.cgi#Unix

http://www.oraclecoach.com/index.php?option=com_content&task=view&id=48&Itemid=1

http://kevinclosson.wordpress.com/2006/12/17/partition-or-real-application-clusters-will-not-work/

http://www.cnetics.com/RAC_tips.shtml

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

http://www.dbasupport.com/forums/archive/index.php/t-11409.html

http://www.dizwell.com/prod/node/681?page=0%2C3

*************
http://dbaworkshop.blogspot.com/2007/01/oracle-10g-rac-installation-using.html
http://vault.centos.org/4.2/isos/i386/

http://www.oracle.com/technology/pub/articles/hunter_rac10gr2.html

http://www.idevelopment.info/data/Oracle/DBA_tips/VMware_Workstation_50/VMWARE_11.shtml

http://www.idevelopment.info/data/Oracle/DBA_tips/VMware_Workstation_50/VMWARE_31.shtml

http://blogs.smokeytech.com/blogs/index.php?blog=1&p=38&more=1&c=1&tb=1&pb=1

http://kevinclosson.wordpress.com/

http://startoracle.com/2007/09/30/so-you-want-to-play-with-oracle-11gs-rac-heres-how/

FOR FTP ********************

http://www.linux.com/base/ldp/howto/FTP-3.html

http://www.phy.duke.edu/~rgb/Beowulf/smp-faq/prive/mentre/smp-faq/smp-faq-3.html

http://www.oracle.com/technology/pub/articles/smiley_10gdb_install.html

http://www.dbazine.com/olc/olc-articles/still5

http://download.oracle.com/docs/cd/B28359_01/server.111/b31107/asmdiskgrps.htm#i1020539

_________________________

http://download-uk.oracle.com/docs/cd/B10501_01/rac.920/a96600/cfgdsk.htm

http://www.puschitz.com/InstallingOracle9iRAC.shtml#StartingAndStoppingOracle9iClusterManager

C:\Documents and Settings\pradeep.singh\Desktop\zodiac\temp\Installing Oracle9i (9_2_0) on Red Hat Linux 8_0 using RAW devices.htm

http://www.dell.com/content/topics/global.aspx/power/en/ps3q03_mahmoodmigration?c=us&cs=555&l=en&s=biz

http://www.ardentperf.com/

http://articles.techrepublic.com.com/5100-1035-5224960.html

While I was able to instal log R2 RAc on vmware CentOS 4, I needed to troubleshoot for GSD and ons errors. Following is inline from http://blog.360.yahoo.com/blog-sWxKwVc0YarVsfeFmtf3gg--?cq=1&p=47
Manually register Oracle CRS resources (listeners, ASM) ----------------------------------------------
Create listeners
----------------------------------------------
In my example, after Oracle 10g Database software installed, when running "netca" to configure listener, the following error happened: "CRS-0259" Owner of the resource does not belong to the group.

The listener file was created, but not complete.

$ cat listener.ora
# listener.ora.sles101 Network Configuration File: /u01/app/oracle/product/10.2.0/db_1/network/admin/listener.ora.sles101
# Generated by Oracle configuration tools.

SID_LIST_LISTENER_SLES101 =
(SID_LIST =
(SID_DESC =
(SID_NAME = PLSExtProc)
(ORACLE_HOME = /u01/app/oracle/product/10.2/db_1)
(PROGRAM = extproc)
)
)

LISTENER_SLES101 =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(PORT = 1521)(IP = FIRST))
(ADDRESS = (PROTOCOL = TCP)(HOST = 172.16.2.145)(PORT = 1521)(IP = FIRST))
)
)

Modify the listener.ora file:
$ cat listener.ora
# listener.ora.sles101 Network Configuration File: /u01/app/oracle/product/10.2.0/db_1/network/admin/listener.ora.sles101
# Generated by Oracle configuration tools.

SID_LIST_LISTENER_SLES101 =
(SID_LIST =
(SID_DESC =
(SID_NAME = PLSExtProc)
(ORACLE_HOME = /u01/app/oracle/product/10.2.0/db_1)
(PROGRAM = extproc)
)
)

LISTENER_SLES101 =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = sles101-vip.test.com)(PORT = 1521)(IP = FIRST))
(ADDRESS = (PROTOCOL = TCP)(HOST = 172.16.2.145)(PORT = 1521)(IP = FIRST))
)
)

Then, start the listener manually by running "lsnctrl start LISTENER_SLES101".We will find that the server already started listening on port 1521 by both public IP And VIP.
$ netstat -nltp|grep 1521
(Not all processes could be identified, non-owned process info
will not be shown, you would have to be root to see it all.)
tcp 0 0 172.16.2.145:1521 0.0.0.0:* LISTEN 18449/tnslsnr
tcp 0 0 172.16.2.147:1521 0.0.0.0:* LISTEN 18449/tnslsnr

Although the listener could be started, while check the crs status by running "crs_stat -t", we will find that listener resources were not registered.

$ ./crs_stat -t
Name Type Target State Host
------------------------------------------------------------
ora....101.gsd application ONLINE ONLINE sles101
ora....101.ons application ONLINE ONLINE sles101
ora....101.vip application ONLINE ONLINE sles101
ora....102.gsd application ONLINE ONLINE sles102
ora....102.ons application ONLINE ONLINE sles102
ora....102.vip application ONLINE ONLINE sles102

Register manually by running "crs_register listener", got the following error:
$ crs_register listener
CRS-0181: Cannot access the resource profile '/u01/app/oracle/product/10.2.0/crs_1/crs/public/listener.cap'.

If we check other RAC machines that runs correctly, we will find some listener cap files like:
-rw-r--r-- 1 oracle oinstall 848 2006-11-25 18:42 ora.sles103.LISTENER_SLES101.lsnr.cap
-rw-r--r-- 1 oracle oinstall 848 2006-11-25 18:42 ora.sles104.LISTENER_SLES102.lsnr.cap

So we need to create manually 2 cap files - ora.sles101.LISTENER_SLES101.lsnr.cap, ora.sles102.LISTENER_SLES102.lsnr.cap under path "/u01/app/oracle/product/10.2.0/crs_1/crs/public/"

$ cat ora.sles101.LISTENER_SLES101.lsnr.cap
NAME=ora.sles101.LISTENER_SLES101.lsnr
TYPE=application
ACTION_SCRIPT=/u01/app/oracle/product/10.2.0/db_1/bin/racgwrap
ACTIVE_PLACEMENT=0
AUTO_START=1
CHECK_INTERVAL=600
DESCRIPTION=CRS application for listener on node
FAILOVER_DELAY=0
FAILURE_INTERVAL=0
FAILURE_THRESHOLD=0
HOSTING_MEMBERS=sles101
OPTIONAL_RESOURCES=
PLACEMENT=restricted
REQUIRED_RESOURCES=ora.sles101.vip
RESTART_ATTEMPTS=5
SCRIPT_TIMEOUT=600
START_TIMEOUT=0
STOP_TIMEOUT=0
UPTIME_THRESHOLD=7d
USR_ORA_ALERT_NAME=
USR_ORA_CHECK_TIMEOUT=0
USR_ORA_CONNECT_STR=/ as sysdba
USR_ORA_DEBUG=0
USR_ORA_DISCONNECT=false
USR_ORA_FLAGS=
USR_ORA_IF=
USR_ORA_INST_NOT_SHUTDOWN=
USR_ORA_LANG=
USR_ORA_NETMASK=
USR_ORA_OPEN_MODE=
USR_ORA_OPI=false
USR_ORA_PFILE=
USR_ORA_PRECONNECT=none
USR_ORA_SRV=
USR_ORA_START_TIMEOUT=0
USR_ORA_STOP_MODE=immediate
USR_ORA_STOP_TIMEOUT=0
USR_ORA_VIP=

$ ll
total 12
drwxrwxrwt 2 oracle users 192 2006-11-25 18:42 .
drwxr-xr-x 14 oracle oinstall 336 2006-11-24 19:13 ..
-rw-rw---- 1 oracle users 3396 2004-08-03 11:26 action_scr.scr
-rw-r--r-- 1 oracle oinstall 848 2006-11-25 18:42 ora.sles101.LISTENER_SLES101.lsnr.cap
-rw-r--r-- 1 oracle oinstall 848 2006-11-25 18:42 ora.sles102.LISTENER_SLES102.lsnr.cap
[oracle@sles101 /u01/app/oracle/product/10.2.0/crs_1/crs/public]

Register the 2 listeners again:

$ crs_register ora.sles101.LISTENER_SLES101.lsnr
$ crs_register ora.sles102.LISTENER_SLES102.lsnr

$ crs_stat -t
Name Type Target State Host
------------------------------------------------------------
ora....01.lsnr application OFFLINE OFFLINE
ora....101.gsd application ONLINE ONLINE sles101
ora....101.ons application ONLINE ONLINE sles101
ora....101.vip application ONLINE ONLINE sles101
ora....02.lsnr application OFFLINE OFFLINE
ora....102.gsd application ONLINE ONLINE sles102
ora....102.ons application ONLINE ONLINE sles102
ora....102.vip application ONLINE ONLINE sles102

Stop and restart the listener on sles101:
$ lsnrctl stop

LSNRCTL for Linux: Version 10.2.0.1.0 - Production on 25-NOV-2006 19:20:25

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

Connecting to (ADDRESS=(PROTOCOL=tcp)(HOST=)(PORT=1521))
The command completed successfully
[oracle@sles104 /u01/app/oracle/product/10.2.0/db_1/bin]
$ lsnrctl start LISTENER_SLES101

LSNRCTL for Linux: Version 10.2.0.1.0 - Production on 25-NOV-2006 19:20:50

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

Starting /u01/app/oracle/product/10.2.0/db_1/bin/tnslsnr: please wait...

TNSLSNR for Linux: Version 10.2.0.1.0 - Production
System parameter file is /u01/app/oracle/product/10.2.0/db_1/network/admin/listener.ora
Log messages written to /u01/app/oracle/product/10.2.0/db_1/network/log/listener_sles101.log
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=172.16.2.145)(PORT=1521)))
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=172.16.2.147)(PORT=1521)))

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=sles101-vip.test.com)(PORT=1521)(IP=FIRST)))
STATUS of the LISTENER
------------------------
Alias LISTENER_SLES101
Version TNSLSNR for Linux: Version 10.2.0.1.0 - Production
Start Date 25-NOV-2006 19:20:50
Uptime 0 days 0 hr. 0 min. 0 sec
Trace Level off
Security ON: Local OS Authentication
SNMP OFF
Listener Parameter File /u01/app/oracle/product/10.2.0/db_1/network/admin/listener.ora
Listener Log File /u01/app/oracle/product/10.2.0/db_1/network/log/listener_sles101.log
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=172.16.2.147)(PORT=1521)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=172.16.2.145)(PORT=1521)))
Services Summary...
Service "PLSExtProc" has 1 instance(s).
Instance "PLSExtProc", status UNKNOWN, has 1 handler(s) for this service...
The command completed successfully

Check the CRS status again, we will find listener resources registered for machine sles101.
[oracle@sles101 /u01/app/oracle/product/10.2.0/crs_1/bin]
$ ./crs_stat -t
Name Type Target State Host
------------------------------------------------------------
ora....01.lsnr application ONLINE ONLINE sles101
ora....101.gsd application ONLINE ONLINE sles101
ora....101.ons application ONLINE ONLINE sles101
ora....101.vip application ONLINE ONLINE sles101
ora....02.lsnr application OFFLINE OFFLINE
ora....102.gsd application ONLINE ONLINE sles102
ora....102.ons application ONLINE ONLINE sles102
ora....102.vip application ONLINE ONLINE sles102

The register process does not need to be run again on the other machine, only one registeration on one machine is required. Just stop and restart the listener on the other node, the registration process is now completed successfully.

$ ./crs_stat -t
Name Type Target State Host
------------------------------------------------------------
ora....01.lsnr application ONLINE ONLINE sles101
ora....101.gsd application ONLINE ONLINE sles101
ora....101.ons application ONLINE ONLINE sles101
ora....101.vip application ONLINE ONLINE sles101
ora....02.lsnr application ONLINE ONLINE sles102
ora....102.gsd application ONLINE ONLINE sles102
ora....102.ons application ONLINE ONLINE sles102
ora....102.vip application ONLINE ONLINE sles102

----------------------------------------------
Add ASM instance resources
----------------------------------------------
In this example, it assumes that ASM instances have been created on both RAC nodes, but they are not registered with CRS, therefore, they are non-clusterware, and could not be used to create an RAC database.

'srvctl' command line will be used to manage/add CRS instances in Oracle 10g, but in SuSE10.1, some modifications have to be applied before running it.

1. Backup file 'srvctl'
$ cp srvctl srvctl.bak

2. Uncomment the export of LD_ASSUME_KERNEL
$ cat srvctl.bak | sed "s/export LD_ASSUME_KERNEL/#export LD_ASSUME_KERNEL/" > srvctl

3. Run 'srvctl' to add ASM resources into the CRS.
Use the srvctl add asm command with the following syntax:
srvctl add asm -n node_name -i asm_instance_name -o oracle_home
(For asm_instance_name, just use ASM1 or ASM2, do not use full name like "ora.sles101.ASM1.asm")

[oracle@sles101 /u01/app/oracle/product/10.2.0/crs_1/bin]
$ ./srvctl add asm -n sles101 -i ASM1 -o /u01/app/oracle/product/10.2.0/db_1
[oracle@sles101 /u01/app/oracle/product/10.2.0/crs_1/bin]
$ ./srvctl add asm -n sles102 -i ASM2 -o /u01/app/oracle/product/10.2.0/db_1
[oracle@sles101 /u01/app/oracle/product/10.2.0/crs_1/bin]

If you do not uncomment LD_ASSUME_KERNEL, you will get error message:
[oracle@sles101 /u01/app/oracle/product/10.2.0/crs_1/bin]
$ ./srvctl add asm -n sles101 -i ASM1 -o /u01/app/oracle/product/10.2.0/db_1
/u01/app/oracle/product/10.2.0/crs_1/jdk/jre/bin/java: error while loading shared libraries: libpthread.so.0: cannot open shared object file: No such file or directory

If you run 'srvctl' in path '/u01/app/oracle/product/10.2.0/crs_1/bin', remember to add './' before 'srvctl', otherwise, you will also get error message:

$ srvctl
/u01/app/oracle/product/10.2.0/crs_1/jdk/jre/bin/java: error while loading shared libraries: libpthread.so.0: cannot open shared object file: No such file or directory

4. Check the status, the ASM instance created, but the state is "OFFLINE"
$ ./crs_stat -t
Name Type Target State Host
------------------------------------------------------------
ora....SM1.asm application OFFLINE OFFLINE
ora....01.lsnr application ONLINE ONLINE sles101
ora....101.gsd application ONLINE ONLINE sles101
ora....101.ons application ONLINE ONLINE sles101
ora....101.vip application ONLINE ONLINE sles101
ora....SM2.asm application OFFLINE OFFLINE
ora....02.lsnr application ONLINE ONLINE sles102
ora....102.gsd application ONLINE ONLINE sles102
ora....102.ons application ONLINE ONLINE sles102
ora....102.vip application ONLINE ONLINE sles102
[oracle@sles101 /u01/app/oracle/product/10.2.0/crs_1/bin]

5. Start the registered ASM resources manually.
$ ./crs_start ora.sles101.ASM1.asm
Attempting to start `ora.sles101.ASM1.asm` on member `sles101`
Start of `ora.sles101.ASM1.asm` on member `sles101` succeeded.
[oracle@sles101 /u01/app/oracle/product/10.2.0/crs_1/bin]
$ ./crs_start ora.sles102.ASM2.asm
Attempting to start `ora.sles102.ASM2.asm` on member `sles102`
Start of `ora.sles102.ASM2.asm` on member `sles102` succeeded.
[oracle@sles101 /u01/app/oracle/product/10.2.0/crs_1/bin]
$ ./crs_stat -t
Name Type Target State Host
------------------------------------------------------------
ora....SM1.asm application ONLINE ONLINE sles101
ora....01.lsnr application ONLINE ONLINE sles101
ora....101.gsd application ONLINE ONLINE sles101
ora....101.ons application ONLINE ONLINE sles101
ora....101.vip application ONLINE ONLINE sles101
ora....SM2.asm application ONLINE ONLINE sles102
ora....02.lsnr application ONLINE ONLINE sles102
ora....102.gsd application ONLINE ONLINE sles102
ora....102.ons application ONLINE ONLINE sles102
ora....102.vip application ONLINE ONLINE sles102
[oracle@sles101 /u01/app/oracle/product/10.2.0/crs_1/bin]

Now, the ASM instances are registered with CRS, and you can use 'dbca' to create RAC database.
Tags: oracle, suse

Installing 9i/10g on RHEL 3

Prerequisite: RHEL 3 has been installed with all required packages(see bottom of this post)
Also check, if you need OS Patch. For install of 9i u need 3006854 patch.Not needed for oracle 10g

Install the 3006854 patch:
unzip p3006854_9204_LINUX.zip
cd 3006854
sh rhel3_pre_install.sh

1. system configuration change
Login from root user and add following entries to /etc/sysctl.conf file:

kernel.shmmax = 2147483648
kernel.shmmni = 4096
kernel.shmall = 2097152
kernel.sem = 250 32000 100 128
fs.file-max = 65536
net.ipv4.ip_local_port_range = 1024 65000

In addition the following lines can be added to the /etc/security/limits.conf file:
oracle soft nofile 1024
oracle hard nofile 65536
oracle soft nproc 2047
oracle hard nproc 16384

2. install groups/user creation
Create the new groups and users:

groupadd oinstall
groupadd dba

useradd -g oinstall -G dba oracle

change password for oracle user
passwd oracle

3. install directory creation /setup
Create the directories in which the Oracle software will be installed and give ownership to install user

mkdir /u01
chown oracle.dba /u01
chmod 777 /u01

4. install user configuration
Login as the install user ,here oracle user and add the following lines at the end of the .bash_profile file:

# Oracle 9i /10g
export ORACLE_BASE=/u01/app/oracle
export ORACLE_HOME=$ORACLE_BASE/product/9.2.0.1.0
export PATH=$PATH:$ORACLE_HOME/bin
export ORACLE_SID=orcl
export LD_LIBRARY_PATH=$ORACLE_HOME/lib
export CLASSPATH=$ORACLE_HOME/JRE:$ORACLE_HOME/jlib:$ORACLE_HOME/rdbms/jlib:
export CLASSPATH=$CLASSPATH:$ORACLE_HOME/network/jlib;
export LD_ASSUME_KERNEL=2.4.1

5. run OUI
For 9i /mnt/cdrom/runInstallerv

10g does not support installation by cd so
For 10g ,copy the 10g software install cd to disk and begin installation .

Now follow the steps suggested by OUI , for troubleshooting , see the install log etc.

required package selection list

1. X Window System.
2. GNOME Desktop Environment.
3. Graphical Internet.
4. Text-based Internet.
5. Server Configuration Tools.
6. FTP Server.
7. Legacy Network Server.
8 Development Tools.
9. Legacy Software Development.
10. Administration Tools.
11. System Tools.

be careful with 7,8,9
click on details and try to select all inside components of the packages.

I hope it helps you.

ORA-02082: a loopback database link must have a connection qualifier

case : scott user wantt to access objects of the sales user in same database by using dblink.(assume he has select any table privilege)

SQL> conn scott/tiger
Connected.

SQL> select * from session_privs;

PRIVILEGE
----------------------------------------
CREATE SESSION
ALTER SESSION
UNLIMITED TABLESPACE
CREATE TABLE
CREATE CLUSTER
CREATE SYNONYM
CREATE VIEW
CREATE SEQUENCE
CREATE DATABASE LINK
CREATE PROCEDURE
CREATE TRIGGER

PRIVILEGE
----------------------------------------
CREATE TYPE
CREATE OPERATOR
CREATE INDEXTYPE

14 rows selected.

SQL> create database link proddb connect to sales identified by password using 'proddblocal'
create database link proddb connect to sales identified by password using 'proddblocal'
*
ERROR at line 1:
ORA-02082: a loopback database link must have a connection qualifier
SQL> ed
Wrote file afiedt.buf
1 create database link proddb@proddb
2 connect to sales identified by password
3* using 'proddblocal'
SQL> /

Database link created.

SQL> select count(*) from dual@proddb ;

COUNT(*)
----------
1

SQL> select * from user_db_links;

DB_LINK USERNAME PASSWORD HOST

CREATED
proddb.sales.ORG@proddb sales password proddblocal

27-APR-07

SQL> conn sales/password@proddblocal
Connected.
SQL> select count(*) from tsales;

COUNT(*)
----------
246678

SQL> conn scott/tiger
Connected.
SQL> select count(*) from tsales@proddb@proddb;

COUNT(*)
----------
246678

using pen drive in linux, mount usb pen drive in linux

Now a days from RHEL 5 linux is able to auto mount the pen drive but if it is not the case then you can follow below steps to mount pen drive in linux.

Back 6 years ago , I had to transfer oracle 11g download to linux machine found ftp services were not installed and no linux cd available. luckily I had 2G pen drive and I was able to move my 2g 11g download from windows to linux.

It was no different than mounting any normal disk.

How I did :

0. created a directory /pendrive

1. When I started linux saw removable devices sercies status [OK]

2. next I gave fdisk -l and noticed the output as below.
[root@lnx2 root]# fdisk -l

Disk /dev/sda: 10.7 GB, 10737418240 bytes
255 heads, 63 sectors/track, 1305 cylinders
Units = cylinders of 16065 * 512 = 8225280 bytes

Device Boot Start End Blocks Id System
/dev/sda1 * 1 13 104391 83 Linux
/dev/sda2 14 172 1277167+ 82 Linux swap
/dev/sda3 173 236 514080 83 Linux
/dev/sda4 237 1305 8586742+ f Win95 Ext'd (LBA)
/dev/sda5 237 1305 8586711 83 Linux

3. now
[root@lnx2 root]# fdisk -l

Disk /dev/sda: 10.7 GB, 10737418240 bytes
255 heads, 63 sectors/track, 1305 cylinders
Units = cylinders of 16065 * 512 = 8225280 bytes

Device Boot Start End Blocks Id System
/dev/sda1 * 1 13 104391 83 Linux
/dev/sda2 14 172 1277167+ 82 Linux swap
/dev/sda3 173 236 514080 83 Linux
/dev/sda4 237 1305 8586742+ f Win95 Ext'd (LBA)
/dev/sda5 237 1305 8586711 83 Linux

Disk /dev/sdb: 1024 MB, 1024966656 bytes
32 heads, 63 sectors/track, 993 cylinders
Units = cylinders of 2016 * 512 = 1032192 bytes

Device Boot Start End Blocks Id System
/dev/sdb1 1 992 999813+ 6 FAT16

then noticed the difference , I found the pen drive device name

[root@lnx2 root]# mount /dev/sdb1 /pendrive

now pen drive content are loaded in this location /pendriver .

after my read from ths location and wrote all desired stuff to local hard disk but cancelled meanwhile.I tried dismounting pen drive using umount but found

[root@lnx2 root]# umount /dev/sdb1
umount: /pendrive: device is busy

I retried after a minute and got successful
[root@lnx2 root]# umount /dev/sdb1

Making 10g RAC ready for worst

focussing on vulenrable part of RAC

RAC gives you high availablity and performance,but what if you dont have clustered disks and your voting disk or cluster registry fails. from 10g R2 these can be mirrored but you should regularly backup and voting/ocr disk.Also you should be heedfull to run integrity test.

manual mirriring of voting disk be

query for current voting disk :
C:\>crsctl query css votedisk
0. 0 P:\cdata\crs\votedsk

located 1 votedisk(s).

adding redundant voting disk:
C:\>crsctl add css votedisk Q:\cdata\crs\votedsk
Cluster is not in a ready state for online disk addition

C:\>crsctl add css votedisk Q:\cdata\crs\votedsk -force
Now formatting voting disk: Q:\cdata\crs\votedsk
successful addition of votedisk Q:\cdata\crs\votedsk.

C:\>crsctl query css votedisk
0. 0 P:\cdata\crs\votedsk
1. 0 Q:\cdata\crs\votedsk

located 2 votedisk(s).

C:\>crsctl add css votedisk O:\cdata\crs\votedsk
Cluster is not in a ready state for online disk addition

investigate ( is CRS running/stopped)

C:\>crsctl add css votedisk O:\cdata\crs\votedsk -force
Now formatting voting disk: O:\cdata\crs\votedsk
successful addition of votedisk O:\cdata\crs\votedsk.

running integrity test
C:\>cluvfy comp ocr -n all

Verifying OCR integrity

Checking OCR integrity...

Checking the absence of a non-clustered configuration...
All nodes free of non-clustered, local-only configurations.

Uniqueness check for OCR device passed.

Checking the version of OCR...
OCR of correct Version "2" exists.

Checking data integrity of OCR...
Data integrity check for OCR passed.

OCR integrity check passed.

Verification of OCR integrity was successful.

qyuery of sweetie

Asked by a old DBA friend about waits caused by wait event "OS THREAD STARTUP" found in his 10g RAC(4 nodes on solaris with ASM)

SELECT DECODE (session_state, 'WAITING', event, NULL) event,
session_state, COUNT(*), SUM (time_waited) time_waited
FROM gv$active_session_history
WHERE sample_time > SYSDATE - 1
GROUP BY DECODE (session_state, 'WAITING', event, NULL),
session_state order by time_waited desc;

Node 1

enq: TX - row lock contention
WAITING
3517
1734721441

os thread startup
WAITING
743
681425655

log file sync
WAITING
3557
530889167

DFS lock handle
WAITING
1131
528346071

db file sequential read
WAITING
34162
502368757

PX Deq: Signal ACK
WAITING
3917
406436176

reliable message
WAITING
283
251896304

gc buffer busy
WAITING
580
175092687

db file parallel read
WAITING
2606
169615544

Streams AQ: qmn coordinator waiting for slave to start
WAITING
123
121679315

I'm concluding on base of my experiecne of parallel queries(on test
machine with single CPU!!!! so no benefit in my case no matter I had RAC)

I see there are PX events indicating your RAC database
instances has parallel query/dml executions but I
guess the os thread startup wait event has nothing to
do with rac environment. This event indicates the
waitupon starting os process(thread) to start query
slave process for execution of parallel query. if u
set init parameter parallel_min_server sufficently
high,you may slightly cut this overhead but its not
recomendded. But instead of following rules of thumb
you check trade-off if you have significant waits.

basic question- deciding index type

guy 1: a table has 10,00000 db blocks
guy 1: it has 9999999999 rows
guy 2: vempires r on the floor
guy 1: on column productid there are 23 distinct values
guy 1: what index would u prefer
guy 2: b+tree
guy 1: nope
guy 1: this column has very high cardinality
guy 2: k
guy 2: sir
guy 1: this column has very few distinct values as comparing to the number of rows in table
guy 1: so very less selectivity . if it was highly selctive only then b+tree
guy 2: yup
guy 1: column seems good candidate for for bmp index
guy 2: im recalling
guy 1: but if heavy dml then even bmp index is also avoided
guy 2: its good .commender
guy 1: because it locks segments (actallyu exents) of objects
guy 2: k
guy 1: so no index if heavy dml on above
guy 2: k

Log_buffer parameter has no effect from 10g

I installed ORACLE SPOTLIGHT for RAC and found LOG_BUFFER was shown to occupy unusually large value, then I investigated and found fixed sga and log buffer are given a one granule size(4m) irrespective of log_buffer value.

SQL> select * from v$sgainfo;

NAME BYTES RES
-------------------------------- ---------- ---
Fixed SGA Size 1290952 No
Redo Buffers 2899968 No
Buffer Cache Size 243269632 Yes
Shared Pool Size 163577856 Yes
Large Pool Size 4194304 Yes
Java Pool Size 4194304 Yes
Streams Pool Size 0 Yes
Granule Size 4194304 No
Maximum SGA Size 419430400 No
Startup overhead in Shared Pool 37748736 No
Free SGA Memory Available 0

11 rows selected.

SQL> select (1290952+ 2899968) /1024 from dual;

(1290952+2899968)/1024
----------------------
4092.69531

Recovery Writer process (RVWR) 10g onwards

Recovery Writer process (RVWR)
The Recovery Writer process is responsible for writing to the flashback logs in the flash recovery area.

These logs are required to restore the database to a previous point in time by using the "flashback" option for Oracle databases (Oracle 10g and later).

U enable the flashback feature in your database and c its power

what fascinates to RAC DBAs and my peers..more and more nodes

great scenario for 2gbps insertion speed......

please look at
http://forums.oracle.com/forums/thread.jspa?threadID=533820&tstart=-2

BT (British Telecom) ran a 82 node (or more) OPS cluster of Pyramid Nile boxes from Siemens.

Resource intensive sessions and SQL

I frequently talk about optimizing top resource consumption.And now most obvious question from audience is always how to find such sessions/sqls. here are ways to get both while you dont have statspack snapshot you can use them as alternate.

Also see my previous blog magical ......

QUERY(cusomizable,according to your workload and) and Sample Output from SPOOL

1 SELECT ses.sid
2 , DECODE(ses.action,NULL,'online','batch') "User"
3 , MAX(DECODE(sta.statistic#,9,sta.value,0))
4 /greatest(3600*24*(sysdate-ses.logon_time),1) "Log IO/s"
5 , MAX(DECODE(sta.statistic#,40,sta.value,0))
6 /greatest(3600*24*(sysdate-ses.logon_time),1) "Phy IO/s"
7 , 60*24*(sysdate-ses.logon_time) "Minutes"
8 FROM V$SESSION ses
9 , V$SESSTAT sta
10 WHERE ses.status = 'ACTIVE'
11 AND sta.sid = ses.sid
12 AND sta.statistic# IN (9,40)
13 GROUP BY ses.sid, ses.action, ses.logon_time
14 ORDER BY
15 SUM( DECODE(sta.statistic#,40,100*sta.value,sta.value) )
16* / greatest(3600*24*(sysdate-ses.logon_time),1) DESC
17
SQL> /

SID User Log IO/s Phy IO/s Minutes
---------- ------ ---------- ---------- ----------
9 online 12913.0075 12912.9963 26.7166667
5 online 98.9491051 98.9362416 29.8
14 online 4578.26388 .012476606 26.7166667
19 online 3170.5866 .00270636 24.6333333
20 online 1328.76316 .035087719 1.9
18 online .111731844 .026536313 11.9333333
7 online .749860101 0 29.7833333
21 online .2 0 .5
6 online .016219239 0 29.8
1 online 0 0 29.8166667
2 online 0 0 29.8166667

SID User Log IO/s Phy IO/s Minutes
---------- ------ ---------- ---------- ----------
3 online 0 0 29.8166667
4 online 0 0 29.8

13 rows selected.

1 SELECT hash_value, executions, buffer_gets, disk_reads, parse_calls
2 FROM V$SQLAREA
3 WHERE buffer_gets > 1000
4 OR disk_reads > 100
5* ORDER BY buffer_gets + 100*disk_reads DESC
SQL>
SQL> /

HASH_VALUE EXECUTIONS BUFFER_GETS DISK_READS PARSE_CALLS
---------- ---------- ----------- ---------- -----------
2626326413 1 572 384 1
690085868 1 2575 134 1
2963598673 349 1095 55 6
657604649 1 1172 18 1

Use of AWR history data:

SELECT T1.*, DBA_HIST_SQLTEXT.sql_text,dba_hist_snapshot.BEGIN_INTERVAL_TIME
FROM
(
SELECT
sub.sql_id,
ROUND(sub.seconds_since_date/60,2) elapsed_time_delta_mins,
sub.execs_since_date,
sub.gets_since_date,
sub.snap_id,
ROUND(sub.seconds_since_date/DECODE(execs_since_date,0,1,execs_since_date)/60,2) avg_exec_time
FROM
( -- sub to sort before rownum
SELECT
sql_id,
ROUND(SUM(elapsed_time_delta)/1000000) AS seconds_since_date,
SUM(executions_delta) AS execs_since_date,
SUM(buffer_gets_delta) AS gets_since_date,
snap_id
FROM
dba_hist_snapshot NATURAL JOIN dba_hist_sqlstat
WHERE
dba_hist_sqlstat.parsing_schema_name='schema_name'
AND
begin_interval_time BETWEEN TRUNC(SYSDATE-1) AND TRUNC(SYSDATE)
-- SYSDATE-1 AND SYSDATE
GROUP BY
sql_id,snap_id
ORDER BY
2 DESC
) sub
WHERE ROWNUM <= 200
)T1 , DBA_HIST_SQLTEXT , dba_hist_snapshot
WHERE T1.sql_id=DBA_HIST_SQLTEXT.sql_id
AND T1.snap_id=dba_hist_snapshot.snap_id
--AND LOWER(DBA_HIST_SQLTEXT.sql_text) LIKE '%rule%'
ORDER BY T1.avg_exec_time DESC

select sql_id from top sqls in awr report and then see its execution plan as below:
select * from table (dbms_xplan.display_awr(sql_id));

simple log management scripts

select * from v&log; --status of logfile groups
select * from v&logfile order by group#; --status of logfiles
select * from v&instance; --status of the archiver
alter system archive log start; --restart the archiver
alter system switch logfile; --switch online log
alter system set log_archive_max_processes=4;

--Add logfile group
alter database add logfile group 4
('&logfilename1',
'&logfilename2') size 64M;

--Drop logfile group and all members in it
alter database drop logfile group &N;

--Add logfile member
alter database add logfile member '&logfilename' reuse to group 4;

--Drop logfile member
alter database drop logfile member '&logfilename';

--Checking archivelog mode
select dbid, name, resetlogs_time, log_mode from v&database;

alter system archive log start; -- restarts the archiver
select * from v&archive_dest; -- archiver destinations

--Altering destination
alter system set log_archive_dest_1='location=&path';
alter system set log_archive_dest_state_1='enable';

--Archived log info from the control file
select * from v&archived_log;

--The sequence# of last backed up log
select thread#, max(sequence#) from v&archived_log
where BACKUP_COUNT>0 group by thread#;

--Redo size (MB) per day, last 30 days
select trunc(first_time) arc_date, sum(blocks * block_size)/1048576 arc_size
from v&archived_log
where first_time >= (trunc(sysdate)-30)
group by trunc(first_time);

Moving schema tables/indexes

--1 Move group of TABLE segments (check for unusable indexes after that)
select 'alter table '||owner||'.'||segment_name||' move '||
decode(segment_type,
'TABLE PARTITION','partition '||partition_name,
'TABLE SUBPARTITION','subpartition '||partition_name,null)||' tablespace &NEW_TS_NAME;' sql
from dba_segments
where segment_type like 'TABLE%'
and tablespace_name='&TS_NAME'
and owner='&OWNER'
and segment_name='&SEG_NAME'
;

--2 Move group of INDEX segments
select 'alter index '||owner||'.'||segment_name||' rebuild '||
decode(segment_type,
'INDEX PARTITION','partition '||partition_name,
'INDEX SUBPARTITION','subpartition '||partition_name,null)||' tablespace &NEW_TS_NAME;' sql
from dba_segments
where segment_type like 'INDEX%'
and tablespace_name='&TS_NAME'
and owner='&OWNER'
and segment_name='&SEG_NAME'
;

--3 List segments that will fail to expand
select /*+ all_rows */ segs.*
from
dba_segments segs,
sys.seg& s,
(select ts#,max(length) m from sys.fet& group by ts#) f
where s.ts#=f.ts# and extsize>m
and segs.header_file=s.file# and segs.header_block=s.block#
;

--4 List of fragmented segments
select segs.*
from
dba_segments segs,
(select file#, segblock# from sys.uet&
group by file#, segblock#
having count(*) > 1024
) f
where segs.header_file=f.file# and segs.header_block=f.segblock#
;

Moving tables/partitions (9i onwards)

--1 Move table from one tablespace to another-- (check for unusable indexes after that).
alter table $OWNER.$TABLE_NAME move tablespace $NEW_TS_NAME
;

--2 Move table partition from one tablespace to another
-- (check for unusable indexes and partitoned indexes after that).
alter table $OWNER.$TABLE_NAME
move partition $TAB_PART_NAME tablespace $NEW_TS_NAME
;

--3 Move table subpartition from one tablespace to another
-- (check for unusable indexes, partitioned indexes, and subpartitioned indexes).
alter table $OWNER.$TABLE_NAME
move subpartition $TAB_SUBPART_NAME tablespace $NEW_TS_NAME
;

keeping eye on sessions

from 11g onwards you can keep eye on long running queries(queries taking more than 5 seconds) from sql monitoring facility. You can get same by setting hint MONITOR in any query no matter it executes within 5 seconds

Old method:

--Long operations:

select sid,username usern,serial#,opname,target,sofar,totalwork tot_work,units,
time_remaining remaning,elapsed_seconds elapsed,last_update_time last_time
from v&session_longops --where sid=73
order by last_update_time desc;

--All active sessions
select * from v&session where status='ACTIVE'
--and sid in (37,43)
order by sid;

--Find session's sid or process id by it's sid or process idselect sid, a.serial#, spid, a.username, status, taddr, a.program
from v&session a, v&process b
where a.paddr=b.addr and a.username is not null
--and (sid=163 or spid=28179)
order by status, sid;

--Kill sessionalter system kill session '&sid,&serial';

Typical Index Maintenance Tasks( 9i/10g)

--1 Move index from one tablespace to another
alter index &OWNER.&INDEX_NAME rebuild tablespace &NEW_TS_NAME
;

--2 Moving index partition from one tablespace to another
alter index &OWNER.&INDEX_NAME
rebuild partition &IND_PART_NAME tablespace &NEW_TS_NAME
;

--3 Moving all index subpartitions from one tablespace to anotheralter index &OWNER.&INDEX_NAME
rebuild subpartition &IND_SUBPART_NAME tablespace &NEW_TS_NAME
;

--4 Unusable indexesselect 'alter index '||owner||'.'||index_name||' rebuild;'
from dba_indexes where status='UNUSABLE';

--5 Unusable index partitionsselect 'alter index '||index_owner||'.'||index_name||
' rebuild partition '||partition_name||';' sql
from dba_ind_partitions where status='UNUSABLE';

--6 Unusable index subpartitionsselect 'alter index '||index_owner||'.'||index_name||
' rebuild subpartition '||subpartition_name||';' sql
from dba_ind_subpartitions where status='UNUSABLE';

--7 All things togetherselect 'alter index '||owner||'.'||index_name||' rebuild;' sql
from dba_indexes where status='UNUSABLE'
union all
select 'alter index '||index_owner||'.'||index_name||
' rebuild partition '||partition_name||';'
from dba_ind_partitions where status='UNUSABLE'
union all
select 'alter index '||index_owner||'.'||index_name||
' rebuild subpartition '||subpartition_name||';'
from dba_ind_subpartitions where status='UNUSABLE';

no big deal any longer - getting ddl of objects from 9i/10g

--1 Get DDL of the object:
declare
clb CLOB; pos INTEGER:=1; amt INTEGER; len INTEGER; txt VARCHAR2(4000);
begin
clb := dbms_metadata.get_ddl ('$OBJ_TYPE','$OBJ_NAME','$OWNER');
len := LENGTH(clb);
LOOP
amt := nvl(INSTR (clb, chr(10), pos),len) - pos;
IF amt>0 THEN txt := NVL(SUBSTR (clb, pos, amt),' '); ELSE txt:=''; END IF;
pos := pos + amt + 1;
DBMS_OUTPUT.put_line (SUBSTR(txt,1,250));
EXIT WHEN pos>=len;
END LOOP;
end;
/

--2 Get DDL of all $OBJ_TYPE objects in schema:
declare
clb CLOB; pos INTEGER; amt INTEGER; len INTEGER; txt VARCHAR2(4000);
handle NUMBER; transhandle NUMBER; cnt NUMBER;
begin
handle := dbms_metadata.open ('$OBJ_TYPE');
--dbms_metadata.set_filter (handle, 'NAME_EXPR','like ''%''');
dbms_metadata.set_filter (handle, 'SCHEMA', '$OWNER');
dbms_metadata.set_count (handle, 10);
transhandle := dbms_metadata.add_transform (handle, 'DDL');
dbms_metadata.set_transform_param (transhandle, 'SQLTERMINATOR', TRUE);
LOOP
clb := dbms_metadata.fetch_clob (handle);
EXIT WHEN clb is null;
pos := 1;
len := LENGTH(clb);
txt := '';
LOOP
amt := nvl(INSTR (clb, chr(10), pos),len) - pos;
IF amt>0 THEN txt := NVL(SUBSTR (clb, pos, amt),' '); ELSE txt:=''; END IF;
pos := pos + amt + 1;
DBMS_OUTPUT.put_line (SUBSTR(txt,1,250));
EXIT WHEN pos>=len;
END LOOP;
END LOOP;
end;
/

easier way to get query execution plan from 10g onwards

traditional method before 10g

select * from v$sql where address='&ADDRESS' and hash_value=&HASH_VALUE;

10g :

select * from v$sql where sql_id='&SQL_ID';
select * from v$sqlstats where sql_id='&SQL_ID';
select * from dba_hist_sqlstat where sql_id='&SQL_ID';

select * from table(DBMS_XPLAN.DISPLAY_CURSOR('&SQL_ID', NULL, 'ALL'));

select sql_id from top sqls in awr report and then see its execution plan as below:
select * from table (dbms_xplan.display_awr(sql_id)); This shows execution plan for all occurence of query with sql_id while below gives execution plan specific to plan_hash

select * from table(DBMS_XPLAN.DISPLAY_AWR('&SQL_ID',&PLAN_HASH,NULL,'ALL'));

WHAT 9i: gave:

explain plan FOR SELECT * FROM dual

SELECT * FROM TABLE(dbms_xplan.display)

CREATE TABLE t6 AS SELECT * FROM dual WHERE 1=2

explain plan FOR INSERT INTO t6 SELECT * FROM dual

SELECT * FROM TABLE(dbms_xplan.display)

explain plan FOR CREATE TABLE t8 AS SELECT * FROM dual WHERE 1=2

SELECT * FROM TABLE(dbms_xplan.display)

Plan hash value: 2951603110

-------------------------------------------------------------------------------
| Id | Operation | Name | ROWS | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------
| 0 | CREATE TABLE STATEMENT | | 1 | 2 | 1 (0)| 00:00:01 |
| 1 | LOAD AS SELECT | T8 | | | | |
|* 2 | FILTER | | | | | |
| 3 | TABLE ACCESS FULL | DUAL | 1 | 2 | 2 (0)| 00:00:01 |
-------------------------------------------------------------------------------

Predicate Information (IDENTIFIED BY operation id):
---------------------------------------------------

2 - filter(NULL IS NOT NULL)

Database creation in RAC environment( 2 instances). can not rely on dbca always

CREATE PARAMETER FILE AND PLACE IT IN SHARED LOCATION
(O:\TEST\INITTEST.ORA shared location here ) AS FOLLOWING
SO THAT BOTH RAC INSTANCES CAN SHARE IT. point to this pfile/spfile from individual instance home/dbs.

# START OF INITTEST.ORA

*.aq_tm_processes=1
*.test1.background_dump_dest='D:\Oracle\admin\test1\bdump'
*.test2.background_dump_dest='D:\Oracle\admin\test2\bdump'

*.cluster_database_instances=2
*.cluster_database=TRUE

*.compatible='9.2.0.0.0'
*.control_files='O:\test\control01.ctl','O:\test\control02.ctl','O:\test\control03.ctl'
*.test1.core_dump_dest='D:\Oracle\admin\test1\cdump'
*.test2.core_dump_dest='D:\Oracle\admin\test2\cdump'
*.db_block_size=8192
*.db_cache_size=25165824
*.db_file_multiblock_read_count=16
*.db_name='test'

test1.instance_name='test1'
test2.instance_name='test2'
test1.instance_number=1
test2.instance_number=2

*.job_queue_processes=1

*.pga_aggregate_target=25165824
*.processes=150
*.remote_login_passwordfile='exclusive'
*.shared_pool_size=150994944
test1.thread=1
test2.thread=2
*.undo_management='AUTO'
*.undo_retention=10800
#test2.undo_tablespace='UNDOTBS2'
test1.undo_tablespace='UNDOTBS1'
test2.user_dump_dest='D:\Oracle\admin\test2\udump'

# END INITTEST.ORA

AT RAC NODE 1

SQL>STARTUP NOMOUNT PFILE='O:\TEST\INITTEST.ORA'
THEN GIVE CREATE DATABASE STMT

1 CREATE DATABASE test
2 MAXINSTANCES 2
3 --MAXLOGHISTORY
4 -- MAXLOGFILES 192
5 --MAXLOGMEMBERS 3
6 --MAXDATAFILES 1024
7 controlfile reuse
8 DATAFILE 'o:\test\system01.dbf' SIZE 200M REUSE AUTOEXTEND ON NEXT 10240K MAXSIZE UNLIMITED
9 DEFAULT TEMPORARY TABLESPACE TEMP TEMPFILE 'o:\test\temp01.dbf' SIZE 5M REUSE AUTOEXTEND ON NEXT 640K MAXSIZE UNLIMIT
10 UNDO TABLESPACE "UNDOTBS1" DATAFILE 'o:\test\undotbs01.dbf' SIZE 20M REUSE AUTOEXTEND ON NEXT 5120K MAXSIZE UNLIMITED
11 CHARACTER SET WE8MSWIN1252
12 NATIONAL CHARACTER SET AL16UTF16
13 LOGFILE 'o:\test\redo01.log' SIZE 10240K REUSE,
14 'o:\test\redo02.log' SIZE 10240K REUSE,
Database created.

SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL>

GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIM
---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- ---------
1 1 1 10485760 1 NO INACTIVE 1 06-OCT-06
2 1 2 10485760 1 NO CURRENT 7129 06-OCT-06

SQL> SELECT MEMBER FROM V$LOGFILE ;

MEMBER
---------------------------------------------------------------------------------------------------------------------------
O:\TEST\REDO01.LOG
O:\TEST\REDO02.LOG

Database altered.

SQL> ALTER DATABASE ADD LOGFILE THREAD 2 'O:\TEST\REDO03.LOG' ;

Database altered.

SQL> ALTER DATABASE ADD LOGFILE THREAD 2 'O:\TEST\REDO04.LOG' ;

Database altered.

SQL> SELECT * fROM V$LOG;

GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIM
---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- ---------
1 1 1 10485760 1 NO INACTIVE 1 06-OCT-06
2 1 2 10485760 1 NO CURRENT 7129 06-OCT-06
3 2 0 10485760 1 YES UNUSED 0
4 2 0 10485760 1 YES UNUSED 0

SQL> ALTER DATABASE ENABLE THREAD 2 ;
Database altered.

******************now OPEN THE INSTANCE 2 ***************
**********THEN QUERY AGAIN HERE **********

SQL> SELECT * fROM V$LOG;

GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIM
---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- ---------
1 1 1 10485760 1 NO INACTIVE 1 06-OCT-06
2 1 2 10485760 1 NO CURRENT 7129 06-OCT-06
3 2 1 10485760 1 NO CURRENT 8271 06-OCT-06
4 2 0 10485760 1 YES UNUSED 0

Now dont forget to create and assign undo tablespace for second instance

What is Right What is Wrong

Found a DBA giving following commands at standby site to switchover while standby database was mounted.

SQL> ALTER DATABASE COMMIT TO SWITCHOVER TO PRIMARY;
ALTER DATABASE COMMIT TO SWITCHOVER TO PRIMARY
*
ERROR at line 1:
ORA-16139: media recovery required

SQL> recover managed standby database disconnect;
Media recovery complete.
SQL> ALTER DATABASE COMMIT TO SWITCHOVER TO PRIMARY;
ALTER DATABASE COMMIT TO SWITCHOVER TO PRIMARY

ERROR at line 1:
ORA-16139: media recovery required
**********
from another SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE FINISH;

Database altered.

SQL> ALTER DATABASE ACTIVATE STANDBY DATABASE;

Database altered.

Helping optimizer to take decision (10g and previous)

Give more knowledge to cost based optimizer about object statistics
and make it intelligent..

U remember Knowledge is Power - Aristotle

-- Gather stats for a table. Auto sampling and auto number of histogram
-- buckets, might not work in previous Oracle version.begin
dbms_stats.gather_table_stats('&OWNER','&TABLE_NAME',null,
DBMS_STATS.AUTO_SAMPLE_SIZE -- put null or percentage for older oracle
,false,'FOR ALL COLUMNS SIZE AUTO' -- put 1 for older oracle
,1,'GLOBAL',false,null,null,null,false);
end;
/
--Gather stats for a schema
begin
dbms_stats.gather_schema_stats('&OWNER',DBMS_STATS.AUTO_SAMPLE_SIZE,false,
'FOR ALL COLUMNS SIZE AUTO',1,'GLOBAL',true,null,null,'GATHER AUTO');
end;
/
--See some column statistics
select column_name,num_distinct,rawtohex(low_value),rawtohex(high_value),density
from dba_tab_col_statistics
where owner='&OWNER' and table_name='&TABLE_NAME';

--See some histogram info
select * from dba_tab_histograms
where owner='&OWNER' and table_name='&TABLE_NAME'
order by column_name, endpoint_number;

--Old fashion analyze
analyze table &OWNER.&TABLE_NAME compute statistics;
analyze table &OWNER.&TABLE_NAME estimate statistics;
analyze table &OWNER.&TABLE_NAME delete statistics;

--Turning table monitoring on:
select 'alter table "'||owner||'"."'||table_name||'" monitoring;' stmt
from all_tables
where monitoring ='NO' and tablespace_name <>'SYSTEM';

few sql scripts useful for getting transaction progress( any version oracle)

select * from v$transaction;

--All tranasactions + sid and username
select s.sid,s.username, t.start_time, t.used_ublk, t.used_urec
from v$transaction t, v$session s
where t.ses_addr=s.saddr;

--All tranasactions + sid and username + first 64 bytes of SQL
select s.sid,s.username, t.start_time, t.used_ublk, t.used_urec,sql.sql_text
from v$transaction t, v$session s, v$sql sql
where t.ses_addr=s.saddr
and s.sql_address=sql.address and s.sql_hash_value=sql.hash_value;

Some Useful Links

http://www.oracle.com/technology/obe/demos/admin/demos.html

http://www.oracle.com/technology/obe/demos/10gr2/RAC_CSS_Diag/10gR2_Vote_Diag_viewlet_swf.html

http://www.oracle.com/technology/obe/demos/10gr2/10gR2_RAC_Obj_Affinity/10gR2_RAC_Obj_Affinity_viewlet_swf.html
http://sysdba.wordpress.com/category/rac/

http://www.oracle.com/technology/pub/articles/smiley_rac10g_install.html

http://oraclezone.wordpress.com/rac/

http://download-west.oracle.com/docs/cd/B19306_01/rac.102/b14197/admcon.htm#CJHFFBJJ

http://blogs.oracle.com/AlejandroVargas/stories/storyReader$15

http://oraclezone.wordpress.com/2007/11/18/weird-rac-issue-due-to-an-incorrect-setup/

http://www.cyberciti.biz/tips/linux-bond-or-team-multiple-network-interfaces-nic-into-single-interface.html

something about sqlldr

Oracle Installer check of prerequisite

this check is not done when used switch ignoreSysPrereqs with runInstaller.

further you can help to ignore warnings but not errors.

Ignoring these errors may later need fixing same warnings.

So follow the check of prerequisite and install all necessary OS pacthes/RPMs or oracle supplied rpms OS patch.

below is how 10gR1 prerequisite or checked on RHEL 3 :

1.Checking operating system certification
Expected result: One of redhat-2.1,redhat-3,UnitedLinux-1.0
Actual Result: redhat-3
Check complete. The overall result of this check is: Passed
=======================================================================

Checking kernel parameters
Checking for VERSION=2.4.9.25; found VERSION=2.4.21. Passed
Checking for shmall=2097152; found shmall=2097152. Passed
Checking for shmseg=10; found shmseg=4096. Passed
Checking for semmsl=250; found semmsl=250. Passed
Checking for semmni=128; found semmni=128. Passed
Checking for filemax=65536; found filemax=65536. Passed
Checking for shmmni=4096; found shmmni=4096. Passed
Checking for semmns=32000; found semmns=32000. Passed
Checking for semopm=100; found semopm=100. Passed
Checking for shmmin=1; found shmmin=1. Passed
Checking for shmmax=2147483648; found shmmax=2147483648. Passed
Check complete. The overall result of this check is: Passed
=======================================================================

Checking recommended operating system packages
Checking for make-3.79; found make-3.79.1-17. Passed
Checking for binutils-2.11.90.0.8-12; found binutils-2.14.90.0.4-26. Passed
Checking for gcc-2.96; found gcc-3.2.3-20. Passed
Checking for openmotif-2.1.30-11; found openmotif-2.2.2-16. Passed
Check complete. The overall result of this check is: Passed
=======================================================================

Checking recommended glibc version
Expected result: 2.2.4.31.7
Actual Result: 2.3.2.95.3
Check complete. The overall result of this check is: Passed
=======================================================================

Validating ORACLE_BASE location (if set)
Check complete. The overall result of this check is: Passed
=======================================================================

*****************************************8
************************
*************

9/10g install on Linux/Solaris

see other post http://orababy.blogspot.com/2007/08/installing-9i10g-on-rhel-3.html for clean steps for linux only

Install Oracle 9i Database on Linux RHEL AS 3

the following lines can be added to the /etc/sysctl.conf file:
kernel.shmmax = 2147483648
kernel.shmmni = 128
kernel.shmall = 2097152
kernel.sem = 250 32000 100 128
fs.file-max = 65536
net.ipv4.ip_local_port_range = 1024 65000
In addition the following lines can be added to the /etc/security/limits.conf file:
oracle soft nofile 65536
oracle hard nofile 65536
oracle soft nproc 16384
oracle hard nproc 16384
Create the new groups and users:
groupadd oinstall
groupadd dba
useradd -g oinstall -G dba oracle
passwd oracle
Create the directories in which the Oracle software will be installed:
mkdir /u01
chown -R oracle.dba /u01
Login as the oracle user and add the following lines at the end of the .bash_profile file:
# Oracle 9i
export ORACLE_BASE=/u01/app/oracle
export ORACLE_HOME=$ORACLE_BASE/product/9.2.0.1.0
export PATH=$PATH:$ORACLE_HOME/bin
export ORACLE_SID=orcl
export LD_LIBRARY_PATH=$ORACLE_HOME/lib
export CLASSPATH=$ORACLE_HOME/JRE:$ORACLE_HOME/jlib:$ORACLE_HOME/rdbms/jlib
export CLASSPATH=$CLASSPATH:$ORACLE_HOME/network/jlib
export LD_ASSUME_KERNEL=2.4.1
Install the 3006854 patch:
unzip p3006854_9204_LINUX.zip
cd 3006854
sh rhel3_pre_install.sh
/mnt/cdrom/runInstaller
When the ins_oemagent.mk error appears install the 3119415 patch:
unzip p3119415_9203_LINUX.zip
cd 3119415
sh patch.sh
Once this is complete click the retry button. Ignore the ins_ctx.mk error and all other .mk errors that come further in installation as this is fixed in the 9.2.0.4.0 patch. The installation is now complete.

Install Oracle 10g Database on Linux 3 AS

Alternatively the following lines can be added to the /etc/sysctl.conf file:
kernel.shmmax = 2147483648
kernel.shmmni = 4096
kernel.shmall = 2097152
kernel.sem = 250 32000 100 128
fs.file-max = 65536
net.ipv4.ip_local_port_range = 1024 65000
In addition the following lines can be added to the /etc/security/limits.conf file:
oracle soft nofile 1024
oracle hard nofile 65536
oracle soft nproc 2047
oracle hard nproc 16384
vi /etc/pam.d/login
session required /lib/security/pam_limits.so
Create the new groups and users:
groupadd oinstall
groupadd dba
useradd -g oinstall -G dba oracle
passwd oracle
Create the directories in which the Oracle software will be installed:
mkdir /u01
chown oracle.dba /u01
chmod 777 /u01
Login as the oracle user and add the following lines at the end of the .bash_profile file:
# Oracle 9i
export ORACLE_BASE=/u01/app/oracle
export ORACLE_HOME=$ORACLE_BASE/product/9.2.0.1.0
export PATH=$PATH:$ORACLE_HOME/bin
export ORACLE_SID=orcl
export LD_LIBRARY_PATH=$ORACLE_HOME/lib
export CLASSPATH=$ORACLE_HOME/JRE:$ORACLE_HOME/jlib:$ORACLE_HOME/rdbms/jlib:
export CLASSPATH=$CLASSPATH:$ORACLE_HOME/network/jlib;
export LD_ASSUME_KERNEL=2.4.1
Install the 3006854 patch:
unzip p3006854_9204_LINUX.zip
cd 3006854
sh rhel3_pre_install.sh
/mnt/cdrom/runInstaller
The installation is now complete.

On solaris +mix

Checking memory/swap etc.
1./usr/sbin/swap –l
2 . check df –h /tmp and if required create tmp and export it along
with tmpdir
3. /usr/sbin/prtconf grep “Memory Size”
4. df –g grep “block size”
5. ulimit –Sa
6. ulimit –Ha
7. id oracle
8. id nobody
9. tar xvf ora******.tar
9.check for availability of required executables
/usr/bin/which make
/usr/bin/which ar
/usr/bin/which ld
/usr/bin/which nm
10.$ export DISPLAY=hostname:0.0
$xclock
11.to determine system architecture /bin/isainfo –kv
12. find version of solaris uname –r
11. pkginfo –i SUNWarc SUNWbtool SUNWhea SUNWlibm SUNWlibms SUNWsprot
\ SUNWbtoo SUNWi1cs SUNWi10f SUNWsprox SUNWxefnt
SUNWi15cs
13. finding showrev –p
Adding patch /usr/sbin/patchadd –p grep patch_number (without version)
Sunsolve.sun.com
Verify hostname set by /etc/hosts only
$Cat etc/nsswitch.conf grep hosts
$hostname
$domainname
$cat /etc/hosts grep ‘eval hostname’
$/usr/sbin/patchadd –p grep 111713
14. Finding release $cat /etc/release
15. edit /etc/system file
1. set shmsys:shminfo_shmmax=4294967295
2. set shmsys:shminfo_shmmin=1
3. set shmsys:shminfo_shmmni=100
4. set shmsys:shminfo_shmseg=10
5. set semsys:seminfo_semmns=2000
6. set semsys:seminfo_semmsl=1000
7. set semsys:seminfo_semmni=100
16. id –a oracle
groupadd -g 115 dba
/usr/sbin/useradd -u 175 –c “oracle software owner.” –g
oinstall –G dba ,oper
-d /u01/app/oracle -m -s /bin/ksh oracle
id nobody
.profile
unzip xyz.zip
cpio –idmv

------

pkginfo -i SUNWarc SUNWbtool SUNWhea SUNWlibm SUNWlibms SUNWsprot \
SUNWsprox SUNWtoo SUNWi1of SUNWi1cs SUNWi15cs SUNWxwfnt

Profile :

$ pkginfo -i SUNWarc SUNWbtool SUNWhea SUNWlibm SUNWlibms SUNWsprot \
SUNWsprox SUNWtoo SUNWi1of SUNWi1cs SUNWi15cs SUNWxwfnt>
system SUNWarc Lint Libraries (usr)
system SUNWbtool CCS tools bundled with SunOS
system SUNWhea SunOS Header Files
system SUNWi15cs X11 ISO8859-15 Codeset Support
system SUNWi1cs X11 ISO8859-1 Codeset Support
system SUNWi1of ISO-8859-1 (Latin-1) Optional Fonts
system SUNWlibm Math & Microtasking Library Headers & Lint Files (Usr)
system SUNWlibms Math & Microtasking Libraries (Usr)
system SUNWsprot Solaris Bundled tools
system SUNWtoo Programming Tools
system SUNWxwfnt X Window System platform required fonts
ERROR: information for "SUNWsprox" was not found

$ cat .profile
ORACLE_BASE=/opt/oracle/app/oracle
ORACLE_HOME=$ORACLE_BASE/product/10.2.4/db_1
LD_LIBRARY_PATH=$ORACLE_HOME/lib
LD_LIBRARY_PATH=$LD_LIBRARY_PATH:/usr/lib/lwp
LD_LIBRARY_PATH_64=/usr/lib/lwp/64:$LD_LIBRARY_PATH_64
ORACLE_SID=mrxprod
#PATH=$PATH:$ORACLE_HOME/bin
export ORACLE_BASE ORACLE_HOME LD_LIBRARY_PATH LD_LIBRARY_PATH_64 ORACLE_SID PATH
$

$ cat /etc/system
*ident "@(#)system 1.18 97/06/27 SMI" /* SVR4 1.5 */
*
* SYSTEM SPECIFICATION FILE
*

* moddir:
*
* Set the search path for modules. This has a format similar to the
* csh path variable. If the module isn't found in the first directory
* it tries the second and so on. The default is /kernel /usr/kernel
*
* Example:
* moddir: /kernel /usr/kernel /other/modules

* root device and root filesystem configuration:
*
* The following may be used to override the defaults provided by
* the boot program:
*
* rootfs: Set the filesystem type of the root.
*
* rootdev: Set the root device. This should be a fully
* expanded physical pathname. The default is the
* physical pathname of the device where the boot
* program resides. The physical pathname is
* highly platform and configuration dependent.
*
* Example:
* rootfs:ufs
* rootdev:/sbus@1,f8000000/esp@0,800000/sd@3,0:a
*
* (Swap device configuration should be specified in /etc/vfstab.)

* exclude:
*
* Modules appearing in the moddir path which are NOT to be loaded,
* even if referenced. Note that `exclude' accepts either a module name,
* or a filename which includes the directory.
*
* Examples:
* exclude: win
* exclude: sys/shmsys

* forceload:
*
* Cause these modules to be loaded at boot time, (just before mounting
* the root filesystem) rather than at first reference. Note that
* forceload expects a filename which includes the directory. Also
* note that loading a module does not necessarily imply that it will
* be installed.
*
* Example:
* forceload: drv/foo

* set:
*
* Set an integer variable in the kernel or a module to a new value.
* This facility should be used with caution. See system(4).
*
* Examples:
*
* To set variables in 'unix':
*
* set nautopush=32
* set maxusers=40
*
* To set a variable named 'debug' in the module named 'test_module'
*
* set test_module:debug = 0x13
noexec_user_stack=1
set semsys:seminfo_semmni=100
set semsys:seminfo_semmns=1024
set semsys:seminfo_semmsl=256
set semsys:seminfo_semvmx=32767
set shmsys:shminfo_shmmax=17179869184
set shmsys:shminfo_shmmin=1
set shmsys:shminfo_shmmni=100
set shmsys:shminfo_shmseg=10
$

groupadd oinstall
groupadd dba

mkdir /opt/oracle
useradd -g oinstall -G dba -d /opt/oracle oracle

chown oracle:oinstall /opt/oracle -R

bash-3.00$ cd ~
bash-3.00$ cat .profile
ORACLE_BASE=/opt/oracle/app/oracle
ORACLE_HOME=$ORACLE_BASE/product/10.2.4/db_1
LD_LIBRARY_PATH=$ORACLE_HOME/lib
LD_LIBRARY_PATH=$LD_LIBRARY_PATH:/usr/lib/lwp
LD_LIBRARY_PATH_64=/usr/lib/lwp/64:$LD_LIBRARY_PATH_64
ORACLE_SID=mrxprod
#PATH=$PATH:$ORACLE_HOME/bin
export ORACLE_BASE ORACLE_HOME LD_LIBRARY_PATH LD_LIBRARY_PATH_64 ORACLE_SID PATH

Enabling command history in sqlplus on linux

command history in linux :tested in rhel3 , other
flavours esp. fedora may require slight modification

downlaod rpm file rlwrap_0.18_1.i386.rpm (24k)
Install the atatched package by going in the download
location of the attached rpm from root user.

rpm -ivh rlwrap-0.18-1.i386.rpm

and then
add following lines to the .bashrc

alias sqlplus='rlwrap sqlplus'
alias rman='rlwrap rman'
alias exp='rlwrap exp'

logout and login back ,now your oracle command line
tool behaves same as in windows command history.

illustration example - Migrating dictionary managed tablespace to locally managed

SQL> EXEC DBMS_SPACE_ADMIN.TABLESPACE_MIGRATE_TO_LOCAL('TEMPD')
BEGIN DBMS_SPACE_ADMIN.TABLESPACE_MIGRATE_TO_LOCAL('TEMPD'); END;
*
ERROR at line 1:
ORA-03245: Tablespace has to be dictionary managed, online and permanent to be able to migrate
ORA-06512: at "SYS.DBMS_SPACE_ADMIN", line 0
ORA-06512: at line 1

SQL> EXEC DBMS_SPACE_ADMIN.TABLESPACE_MIGRATE_TO_LOCAL('USERSD')
PL/SQL procedure successfully completed.
SQL> EXEC DBMS_SPACE_ADMIN.TABLESPACE_MIGRATE_TO_LOCAL('SYSTEM')
BEGIN DBMS_SPACE_ADMIN.TABLESPACE_MIGRATE_TO_LOCAL('SYSTEM'); END;
*
ERROR at line 1:
ORA-10643: Database should be mounted in restricted mode and Exclusive mode
ORA-06512: at "SYS.DBMS_SPACE_ADMIN", line 0
ORA-06512: at line 1

SQL> SHUTDOWN IMMEDIATE
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> STARTUP RESTRICT
ORACLE instance started.
Total System Global Area 160504432 bytes
Fixed Size 453232 bytes
Variable Size 125829120 bytes
Database Buffers 33554432 bytes
Redo Buffers 667648 bytes
Database mounted.
Database opened.
SQL>
SQL> EXEC DBMS_SPACE_ADMIN.TABLESPACE_MIGRATE_TO_LOCAL('SYSTEM')
BEGIN DBMS_SPACE_ADMIN.TABLESPACE_MIGRATE_TO_LOCAL('SYSTEM'); END;
*
ERROR at line 1:
ORA-10647: Tablespace other than SYSTEM,UNDOTBS1, TEMPD not found in read only mode
ORA-06512: at "SYS.DBMS_SPACE_ADMIN", line 0
ORA-06512: at line 1

SQL> SELECT * FROM V$TABLESPACE;
TS# NAME INC
---------- ------------------------------ ---
0 SYSTEM YES
1 UNDOTBS1 YES
2 TEMP YES
3 USERS YES
5 USERSD YES
6 TEMPD YES
7 SMALL YES
8 TEMPL YES
8 rows selected.
SQL> ALTER TABLESPACE USERS READ ONLY;
Tablespace altered.
SQL> ALTER TABLESPACE SMALL READ ONLY;
Tablespace altered.
SQL> ALTER TABLESPACE USERSD READ ONLY;
Tablespace altered.
SQL> EXEC DBMS_SPACE_ADMIN.TABLESPACE_MIGRATE_TO_LOCAL('SYSTEM')
BEGIN DBMS_SPACE_ADMIN.TABLESPACE_MIGRATE_TO_LOCAL('SYSTEM'); END;
*
ERROR at line 1:
ORA-10647: Tablespace other than SYSTEM,UNDOTBS1, TEMPD not found in read only mode
ORA-06512: at "SYS.DBMS_SPACE_ADMIN", line 0
ORA-06512: at line 1

SQL> DROP TABLESPACE TEMPD;
DROP TABLESPACE TEMPD
*
ERROR at line 1:
ORA-12906: cannot drop default temporary tablespace

SQL> ALTER DATABASE DEFAULT TEMPORARY TABLESPACE TEMPL ;
Database altered.
SQL> EXEC DBMS_SPACE_ADMIN.TABLESPACE_MIGRATE_TO_LOCAL('SYSTEM')
BEGIN DBMS_SPACE_ADMIN.TABLESPACE_MIGRATE_TO_LOCAL('SYSTEM'); END;
*
ERROR at line 1:
ORA-10647: Tablespace other than SYSTEM,UNDOTBS1, TEMPL not found in read only mode
ORA-06512: at "SYS.DBMS_SPACE_ADMIN", line 0
ORA-06512: at line 1

SQL> DROP TABLESPACE TEMPD;
Tablespace dropped.
SQL> EXEC DBMS_SPACE_ADMIN.TABLESPACE_MIGRATE_TO_LOCAL('SYSTEM')
BEGIN DBMS_SPACE_ADMIN.TABLESPACE_MIGRATE_TO_LOCAL('SYSTEM'); END;
*
ERROR at line 1:
ORA-10647: Tablespace other than SYSTEM,UNDOTBS1, TEMPL not found in read only mode
ORA-06512: at "SYS.DBMS_SPACE_ADMIN", line 0
ORA-06512: at line 1

SQL> SELECT NAME FROM V$TABLESPACE;
NAME
------------------------------
SYSTEM
UNDOTBS1
TEMP
USERS
USERSD
SMALL
TEMPL
7 rows selected.
SQL> DROP TABLESPACE &1;
Enter value for 1: USERS
old 1: DROP TABLESPACE &1
new 1: DROP TABLESPACE USERS
Tablespace dropped.
SQL> /
Enter value for 1: USERSD
old 1: DROP TABLESPACE &1
new 1: DROP TABLESPACE USERSD
DROP TABLESPACE USERSD
*
ERROR at line 1:
ORA-01549: tablespace not empty, use INCLUDING CONTENTS option

SQL> DROP TABLESPACE USERSD INCLUDING CONTENTS ;
Tablespace dropped.
SQL> DROP TABLESPACE &1;
Enter value for 1: SAMLL
old 1: DROP TABLESPACE &1
new 1: DROP TABLESPACE SAMLL
DROP TABLESPACE SAMLL
*
ERROR at line 1:
ORA-00959: tablespace 'SAMLL' does not exist

SQL> /
Enter value for 1: SMALL
old 1: DROP TABLESPACE &1
new 1: DROP TABLESPACE SMALL
Tablespace dropped.
SQL> /
Enter value for 1: TEMPL
old 1: DROP TABLESPACE &1
new 1: DROP TABLESPACE TEMPL
DROP TABLESPACE TEMPL
*
ERROR at line 1:
ORA-12906: cannot drop default temporary tablespace

SQL> SELECT NAME FROM V$TABLESPACE;
NAME
------------------------------
SYSTEM
UNDOTBS1
TEMP
TEMPL
SQL> EXEC DBMS_SPACE_ADMIN.TABLESPACE_MIGRATE_TO_LOCAL('SYSTEM')
BEGIN DBMS_SPACE_ADMIN.TABLESPACE_MIGRATE_TO_LOCAL('SYSTEM'); END;
*
ERROR at line 1:
ORA-10647: Tablespace other than SYSTEM,UNDOTBS1, TEMPL not found in read only mode
ORA-06512: at "SYS.DBMS_SPACE_ADMIN", line 0
ORA-06512: at line 1

SQL> DROP TABLESPACE TEMP;
Tablespace dropped.
SQL> EXEC DBMS_SPACE_ADMIN.TABLESPACE_MIGRATE_TO_LOCAL('SYSTEM')
PL/SQL procedure successfully completed.
SQL> CREATE TABLESPACE TESTD
2 DATAFILE 'TESTD'
3 EXTENT MANAGEMENT DICTIONARY ;
CREATE TABLESPACE TESTD
*
ERROR at line 1:
ORA-12913: Cannot create dictionary managed tablespace

SQL> SHOW PARAMETER READ
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_file_multiblock_read_count integer 16
parallel_threads_per_cpu integer 2
read_only_open_delayed boolean FALSE
thread integer 0
SQL> CREATE TABLESPACE TESTD
2 DATAFILE 'TESTD' ;
CREATE TABLESPACE TESTD
*
ERROR at line 1:
ORA-01119: error in creating database file 'TESTD'
ORA-17610: file 'TESTD' does not exist and no size specified
ORA-27041: unable to open file
OSD-04002: unable to open file
O/S-Error: (OS 2) The system cannot find the file specified.

SQL> ED
Wrote file afiedt.buf
1 CREATE TABLESPACE TESTD
2* DATAFILE 'TESTD' SIZE 1M
3
SQL>
SQL> /
Tablespace created.
SQL> ALTER TABLESPACE TESTD READ ONLY ;
Tablespace altered.

Out of deep Woods-Thanks Heaven ORA-26500: error on caching "SALES"."TRANSACT

Few days back I found a huge index on my main transaction table in primary production database(9i RAC environment), which I suspected was not used by any query. So I decided to monitor its usage using oracle 9i Monitoring usage command. And after monitoring it for a week I found it was not used by any query and I dropped it just before production hour began at monday after weekly backup and other maintenance activities were finished. This table was replicated to three other databases using updateable materialized view setup.So this production database was mater site for replication.

To meet my surprise I found that even though 45 minutes of production time elapsed no new records were append to the main transaction table whose unused function based index was dropped.
In my database alert log I got very weird error in both my RAC instances
Errors in file d:\oracle\admin\ctdp\udump\ctdp1_ora_424.trc
:ORA-26500: error on caching "SALES"."TRANSACT" "

search on metalink and Google suggested
DBMS_REPUTIL.MAKE_INTERNAL_PKG ('EDBMS','TEST');
DBMS_REPUTIL.SYNC_UP_REP('EDBMS','TEST');
but it was riskier too as said links.

Then I decided to regenerate replciation support.

took following steps on repadmin user at master site

begin
dbms_repcat.suspend_master_activity(gname=>'master_replication_group_name');
end;/
begin
dbms_repcat.generate_replication_support( sname => 'schemanameofmastertable',
oname => 'tablenamewhichisrepliciated',
type => 'TABLE',
min_communication => TRUE);
end;
/

begin
dbms_repcat.resume_master_activity(gname=>'master_replication_group_name');
end;
/

thanks God it worked.

Now It was clear the culprit was poor documentation of Oracle as oracle says only that you need to regenerate replication support when you change the object only. But I did not change the master table .I dropped its one index and that can not be said change to master table.Thats only a indirect change not a change to structure of DB .

setting up 10g physical standby database

STEPS for creating 10g dataguard

prerequisite : 9i dataguard setup knowledge

Step1 : Prepare initSID.ora file for primary and standby databases as follow.

** STANDBY setup parameters are given in bold and highlighted

part A)

**** Production database primary file ****

prod.__db_cache_size=125829120
prod.__java_pool_size=4194304
prod.__large_pool_size=4194304
prod.__shared_pool_size=79691776
prod.__streams_pool_size=0
*.audit_file_dest='C:\oracle\product\10.2.0\admin\prod\adump'
*.background_dump_dest='C:\oracle\product\10.2.0\admin\prod\bdump'
*.compatible='10.2.0.3.0'
*.control_files='Q:\oradata\prod\control01.ctl','Q:\oradata\prod\control02.ctl','Q:\oradata\prod\control03.ctl'
*.core_dump_dest='C:\oracle\product\10.2.0\admin\prod\cdump'
*.db_block_size=8192
*.db_domain=''
*.db_file_multiblock_read_count=16
*.db_name='prod'
*.db_recovery_file_dest='C:\oracle\product\10.2.0\flash_recovery_area'
*.db_recovery_file_dest_size=2147483648

*.instance_name='prod'
*.job_queue_processes=10

*.db_unique_name='prod'
*.fal_client='prod'
*.fal_server='prod_stdby'
*.log_archive_config='DG_CONFIG=(prod,stdby)'
*.log_archive_dest_1='LOCATION=Q:\oradata\prod\arch VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=prod'
*.log_archive_dest_2='SERVICE=prod_stdby VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=stdby LGWR ASYNC REOPEN=10'
*.remote_login_passwordfile='EXCLUSIVE'

*.log_archive_dest_state_1='ENABLE'
*.log_archive_dest_state_2='ENABLE'
*.log_archive_format='%t%s%r.arc'
*.open_cursors=300
*.pga_aggregate_target=72351744
*.processes=150
*.service_names='prod'
*.sga_target=218103808
*.standby_archive_dest='q:\arch'
*.standby_file_management='AUTO'
*.undo_management='AUTO'
*.undo_tablespace='UNDOTBS1'
*.user_dump_dest='C:\oracle\product\10.2.0\admin\prod\udump'

Part B)

**** standby database primary file ****

*.compatible='10.2.0.3.0'
*.control_files='C:\oradata\prod\stdby.ctl'
*.instance_name='stdby'
*.db_name='prod'
*.db_unique_name='stdby'
*.fal_client='prod_stdby'
*.fal_server='prod'
*.log_archive_config='DG_CONFIG=(prod,stdby)'
*.log_archive_dest_1='LOCATION=C:\oradata\prod\stdby\arch VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=stdby'
*.log_archive_dest_2='SERVICE=prod VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=prod LGWR ASYNC REOPEN=10'
*.db_file_name_convert='Q:\','C:\'
*.log_file_name_convert='Q:\','C:\'
*.remote_login_passwordfile='EXCLUSIVE'

*.log_archive_dest_state_1='ENABLE'
*.log_archive_dest_state_2='ENABLE'
*.log_archive_format='%t_%s_%r'
*.service_names='stdby'
*.shared_pool_size=104857600
*.standby_file_management='AUTO'

STEP 2:

part A)

shutdown primary database cleanly and copy datafiles to standby location.

Part B) create standby controlfile by giving following command at production database at mount stage

alter databse create standby controlfiel as 'location\filename' ;

move this generated file to standby controlfile location as pointed by standby initSID.ora file

step 3)
create oracle service and password file with the same password at standby database location

step 4)
prepare the TNSNAMES.ORA and LISTENER.ora at both production and standy locations.

To check archive log gap

SELECT * FROM (
SELECT sequence#, archived, applied,
TO_CHAR(completion_time, 'RRRR/MM/DD HH24:MI') AS completed
FROM sys.v$archived_log
ORDER BY sequence# DESC)
WHERE ROWNUM <= 10

ORACLE DBA Knowledge Share

Translate

Search This Blog