Welcome to My World.

Clean Up 10g CRS After Failed Installation

2008-06-16T00:08:00.000-07:00

Some people might be don't have access to metalink, so i would like to help those people in clean up failed CRS installation in Oracle 10g RAC

1. Run rootdelete.sh script then the rootdeinstall.sh script from the $ORA_CRS_HOME/install directory on any nodes you are removing CRS. Running these scripts should be sufficent to clean up your CRS install. If you have any problems with these scripts please open a service request.

If for some reason you have to manually remove the install due to problems with the scripts, continue to step 2:

2. Stop the Nodeapps on all nodes:

srvctl stop nodeapps -n

3. Prevent CRS from starting when the node reboot. To do this issue the following as root:

Sun:
rm /etc/init.d/init.cssd
rm /etc/init.d/init.crs
rm /etc/init.d/init.crsd
rm /etc/init.d/init.evmd
rm /etc/rc3.d/K96init.crs
rm /etc/rc3.d/S96init.crs
rm -Rf /var/opt/oracle/scls_scr
rm -Rf /var/opt/oracle/oprocd
rm /etc/inittab.crs
cp /etc/inittab.orig /etc/inittab

Linux:
rm /etc/oracle/*
rm -f /etc/init.d/init.cssd
rm -f /etc/init.d/init.crs
rm -f /etc/init.d/init.crsd
rm -f /etc/init.d/init.evmd
rm -f /etc/rc2.d/K96init.crs
rm -f /etc/rc2.d/S96init.crs
rm -f /etc/rc3.d/K96init.crs
rm -f /etc/rc3.d/S96init.crs
rm -f /etc/rc5.d/K96init.crs
rm -f /etc/rc5.d/S96init.crs
rm -Rf /etc/oracle/scls_scr
rm -f /etc/inittab.crs
cp /etc/inittab.orig /etc/inittab

HP-UX:
rm /sbin/init.d/init.cssd
rm /sbin/init.d/init.crs
rm /sbin/init.d/init.crsd
rm /sbin/init.d/init.evmd
rm /sbin/rc2.d/K960init.crs
rm /sbin/rc2.d/K001init.crs
rm /sbin/rc3.d/K960init.crs
rm /sbin/rc3.d/S960init.crs
rm -Rf /var/opt/oracle/scls_scr
rm -Rf /var/opt/oracle/oprocd
rm /etc/inittab.crs
cp /etc/inittab.orig /etc/inittab

HP Tru64:
rm /sbin/init.d/init.cssd
rm /sbin/init.d/init.crs
rm /sbin/init.d/init.crsd
rm /sbin/init.d/init.evmd
rm /sbin/rc3.d/K96init.crs
rm /sbin/rc3.d/S96init.crs
rm -Rf /var/opt/oracle/scls_scr
rm -Rf /var/opt/oracle/oprocd
rm /etc/inittab.crs
cp /etc/inittab.orig /etc/inittab

IBM AIX:
rm /etc/init.cssd
rm /etc/init.crs
rm /etc/init.crsd
rm /etc/init.evmd
rm /etc/rc.d/rc2.d/K96init.crs
rm /etc/rc.d/rc2.d/S96init.crs
rm -Rf /etc/oracle/scls_scr
rm -Rf /etc/oracle/oprocd
rm /etc/inittab.crs
cp /etc/inittab.orig /etc/inittab

4. If they are not already down, kill off EVM, CRS, and CSS processes or reboot the node:

ps -ef | grep crs
kill
ps -ef | grep evm
kill
ps -ef | grep css
kill

Do not kill any OS processes, for example icssvr_daemon process

5. If there is no other Oracle software running (like listeners, DB's, etc...), you can remove the files in /var/tmp/.oracle or /tmp/.oracle. Example:

rm -f /var/tmp/.oracle
or
rm -f /tmp/.oracle

6. Remove the ocr.loc in /etc/oracle

7. De-install the CRS home in the Oracle Universal Installer

8. Remove the CRS install location:

rm -Rf /*

9. Clean out the OCR and Voting Files with dd commands. Example:

dd if=/dev/zero of=/dev/rdsk/V1064_vote_01_20m.dbf bs=8192 count=2560
dd if=/dev/zero of=/dev/rdsk/ocrV1064_100m.ora bs=8192 count=12800

If you placed the OCR and voting disk on a shared filesystem, remove them.

If you are removing the RDBMS installation, also clean out any ASM disks if
they have already been used.

10.If you would like to re-install CRS, follow the steps in the RAC Installation manual.

Hope this will help you out

Cheers

PLS-213: package STANDARD not accessible

2008-05-29T00:56:00.000-07:00

Today when i was creating a new database (10.2.0.2) using DBCA for CMS Production, i got error "PLS-213: package STANDARD not accessible". How to solve this problem?. Well there are few things that you have to check

1. Check standard.sql does exists under ORACLE_HOME/rdbms/admin
ora10g@bahamut:/home/ora10g/10.2/rdbms/admin> ls -arlt st*
-rw-r----- 1 ora10g dba 144274 Jun 30 2005 stdspec.sql
-rw-r----- 1 ora10g dba 19277 Jun 30 2005 stdbody.sql
-rw-r----- 1 ora10g dba 65 Jun 30 2005 standard.sql

2. Check that glogin.sql (ORACLE_HOME/sqlplus/admin) does not contain "set serveroutput on"

Here my problem was caused because serveroutput was set to on, after remove it for temporary then i can continue database creation through DBCA

GBU

Could not connect from production application to production database

2008-03-03T23:16:00.000-08:00

This problem just happen a moment ago and still fresh i think. Right after i finished my lunch, application sys admin asked why we could not connect from hawaii through sqlplus?

Then i tried to start investigating

ora10g@hawaii:/home/ora10g> sqlplus system@zpacdb

SQL*Plus: Release 10.2.0.2.0 - Production on Tue Mar 4 13:41:05 2008

Copyright (c) 1982, 2005, Oracle. All Rights Reserved.

Enter password:
ERROR:
ORA-00022: invalid session ID; access denied

First i thought something must be blocking the connection. Then i tried to connect from my local and it appears OK, secondly then i checked the listener because my local and the application server using different listnener. again this appears OK when i tried connect from my local using application server listener.

So what is the problem? why we could not connect?

I worked together with application sys admin and UNIX sys admin and they found that the hostname of the application server was changed.

ora10g@hawaii:/home/ora10g> hostname
tahiti

Oh my GOD, hostname shouldn't tahiti, it should be hawaii. So we changed it back as soon as possible

ora10g@hawaii:/home/ora10g> hostname
hawaii

and now we can connect again and continue our live

Have a nice day

FZheng

How to find characters in string?

2008-02-28T23:50:00.000-08:00

Again, one of application testing people came to me and ask "How could we find character in string?". Well i suddenly remember there is one new SQL function in Oracle 10g. Lets take a look the example

vd@bahamut> select postal from t2;

POSTAL
----------
11470
11520
15880
13963
11788
11880
15576
1190a
1A7I1
11ao2
16502
15570
16890
I99it

We have 4 rows that contains character in postal code column. How do we find this 4 rows?

vd@bahamut> select postal from t2 WHERE REGEXP_LIKE (postal, '[a-zA-Z]');

POSTAL
----------
1190a
1A7I1
11ao2
I99it

yes, the answer is SQL new function REGEXP_LIKE.

[a-zA-Z] is to find from 'a' to 'z' and 'A' to 'Z'.

remember that this only work in Oracle 10g and future release

Hope this article will help you

Cheers

FZheng

How about if my current redolog is lost?

2008-02-20T01:25:00.000-08:00

Some of people might be not aware of losing current redolog and some other might be thinking 'No, We won't be able to do recovery if current redolog is lost?. So the question can we do or can't we do?

Actually yes we can do recover and depends on your database is closed consistently or not. If your database is not closed consistently then you need to restore from backup and perform point in time recovery (you should run your database in archivelog mode) and there might be lost transaction.

Here i did a testing that database is not closed consistently (i presumed i have lost everthing, i mean the box)

First restore from backup, here i used RMAN

RMAN> run {
2> allocate channel ch_1 type disk;
3> restore controlfile to '/backup/restore/dbvdasm/control01.ctl';
4> set newname for datafile '/backup/restore/dbvd/sysaux01.dbf' to '/backup/restore/dbvdasm/sysaux01.dbf';
5> set newname for datafile '/backup/restore/dbvd/system01.dbf' to '/backup/restore/dbvdasm/system01.dbf';
6> set newname for datafile '/backup/restore/dbvd/test1.dbf' to '/backup/restore/dbvdasm/test1.dbf';
7> set newname for datafile '/backup/restore/dbvd/undotbs01.dbf' to '/backup/restore/dbvdasm/undotbs01.dbf';
8> set newname for datafile '/backup/restore/dbvd/users01.dbf' to '/backup/restore/dbvdasm/users01.dbf';
9> restore database;
10> set archivelog destination to '/backup/restore/dbvdasm/';
11> restore archivelog all;
12> release channel ch_1;
13> }

allocated channel: ch_1
channel ch_1: sid=142 devtype=DISK

Starting restore at 20-FEB-08

channel ch_1: starting datafile backupset restore
channel ch_1: restoring control file
output filename=/backup/restore/dbvdasm/control01.ctl
channel ch_1: reading from backup piece /backup/restore/df_0mj96lbf_1_1.DBVD.bus
channel ch_1: restored backup piece 1
piece handle=/backup/restore/df_0mj96lbf_1_1.DBVD.bus tag=TAG20080220T145750
channel ch_1: restore complete, elapsed time: 00:00:03
Finished restore at 20-FEB-08

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

Starting restore at 20-FEB-08

channel ch_1: starting datafile backupset restore
channel ch_1: specifying datafile(s) to restore from backup set
restoring datafile 00001 to /backup/restore/dbvdasm/system01.dbf
restoring datafile 00002 to /backup/restore/dbvdasm/undotbs01.dbf
restoring datafile 00003 to /backup/restore/dbvdasm/sysaux01.dbf
restoring datafile 00004 to /backup/restore/dbvdasm/users01.dbf
restoring datafile 00005 to /backup/restore/dbvdasm/test1.dbf
channel ch_1: reading from backup piece /backup/restore/df_0lj96lbe_1_1.DBVD.bus
channel ch_1: restored backup piece 1
piece handle=/backup/restore/df_0lj96lbe_1_1.DBVD.bus tag=TAG20080220T145750
channel ch_1: restore complete, elapsed time: 00:00:08
Finished restore at 20-FEB-08

executing command: SET ARCHIVELOG DESTINATION

Starting restore at 20-FEB-08

channel ch_1: starting archive log restore to user-specified destination
archive log destination=/backup/restore/dbvdasm/
channel ch_1: restoring archive log
archive log thread=1 sequence=10
channel ch_1: starting archive log restore to user-specified destination
archive log destination=/backup/restore/dbvdasm/
channel ch_1: restoring archive log
archive log thread=1 sequence=17
channel ch_1: restoring archive log
archive log thread=1 sequence=18
channel ch_1: restoring archive log
archive log thread=1 sequence=19
channel ch_1: restoring archive log
archive log thread=1 sequence=20
channel ch_1: starting archive log restore to user-specified destination
archive log destination=/backup/restore/dbvdasm/
channel ch_1: restoring archive log
archive log thread=1 sequence=11
channel ch_1: restoring archive log
archive log thread=1 sequence=12
channel ch_1: restoring archive log
archive log thread=1 sequence=13
channel ch_1: restoring archive log
archive log thread=1 sequence=14
channel ch_1: restoring archive log
archive log thread=1 sequence=15
channel ch_1: restoring archive log
archive log thread=1 sequence=16
channel ch_1: starting archive log restore to user-specified destination
archive log destination=/backup/restore/dbvdasm/
channel ch_1: restoring archive log
archive log thread=1 sequence=21

RMAN>

At this point we have a restored of controlfile, datafiles and archives but remember we still don't have redolog at this moment.

ora10g@bahamut:/backup/restore/dbvdasm> ls -arlt
total 1320552
drwxr-xr-x 4 ora10g dba 4096 Feb 20 14:57 ..
-rw-r----- 1 ora10g dba 2048 Feb 20 15:05 1_31_647005853.dbf
-rw-r----- 1 ora10g dba 287744 Feb 20 15:05 1_30_647005853.dbf
-rw-r----- 1 ora10g dba 1024 Feb 20 15:05 1_29_647005853.dbf
-rw-r----- 1 ora10g dba 1024 Feb 20 15:05 1_28_647005853.dbf
-rw-r----- 1 ora10g dba 2048 Feb 20 15:05 1_27_647005853.dbf
-rw-r----- 1 ora10g dba 6144 Feb 20 15:05 1_26_647005853.dbf
-rw-r----- 1 ora10g dba 1024 Feb 20 15:05 1_25_647005853.dbf
-rw-r----- 1 ora10g dba 2048 Feb 20 15:05 1_24_647005853.dbf
-rw-r----- 1 ora10g dba 3950592 Feb 20 15:05 1_23_647005853.dbf
-rw-r----- 1 ora10g dba 391168 Feb 20 15:05 1_22_647005853.dbf
-rw-r----- 1 ora10g dba 1024 Feb 20 15:05 1_32_647005853.dbf
drwxr-xr-x 2 ora10g dba 4096 Feb 20 15:05 .
-rw-r----- 1 ora10g dba 5251072 Feb 20 15:12 users01.dbf
-rw-r----- 1 ora10g dba 209723392 Feb 20 15:12 undotbs01.dbf
-rw-r----- 1 ora10g dba 10493952 Feb 20 15:12 test1.dbf
-rw-r----- 1 ora10g dba 314580992 Feb 20 15:12 system01.dbf
-rw-r----- 1 ora10g dba 125837312 Feb 20 15:12 sysaux01.dbf
-rw-r----- 1 ora10g dba 5488640 Feb 20 15:12 control01.ctl

Now go to sqlplus and change the location of datafiles and redologs to new location

ora10g@bahamut:/home/ora10g/10.2/dbs> sqlplus '/ as sysdba'

SQL*Plus: Release 10.2.0.2.0 - Production on Wed Feb 20 15:08:02 2008

Copyright (c) 1982, 2005, Oracle. All Rights Reserved.

Connected to an idle instance.

SYS@dbvd> startup mount;
ORACLE instance started.

Total System Global Area 922746880 bytes
Fixed Size 2075568 bytes
Variable Size 494928976 bytes
Database Buffers 419430400 bytes
Redo Buffers 6311936 bytes
Database mounted.
SYS@dbvd> alter database rename file '/backup/restore/dbvd/sysaux01.dbf' to '/backup/restore/dbvdasm/sysaux01.dbf';

Database altered.

----------------------------------some more output here-----------------------------------------------------------

Next we need to recover the database

SYS@dbvd> recover database using backup controlfile until cancel;
ORA-00279: change 214846 generated at 02/20/2008 14:57:50 needed for thread 1
ORA-00289: suggestion : /backup/restore/dbvdasm/1_32_647005853.dbf
ORA-00280: change 214846 for thread 1 is in sequence #32

Specify log: {=suggested | filename | AUTO | CANCEL}
/backup/restore/dbvdasm/1_32_647005853.dbf
ORA-00279: change 214850 generated at 02/20/2008 14:57:52 needed for thread 1
ORA-00289: suggestion : /backup/restore/dbvdasm/1_33_647005853.dbf
ORA-00280: change 214850 for thread 1 is in sequence #33
ORA-00278: log file '/backup/restore/dbvdasm/1_32_647005853.dbf' no longer needed for this recovery

Specify log: {=suggested | filename | AUTO | CANCEL}
CANCEL
Media recovery cancelled.
SYS@dbvd> alter database open resetlogs;

Database altered.

SYS@dbvd> select open_mode from v$database;

OPEN_MODE
----------
READ WRITE

Last but not least don't forget to re-create temporary tablespace

ora10g@bahamut:/backup/restore/dbvdasm> ls -arlt
total 1525368
drwxr-xr-x 4 ora10g dba 4096 Feb 20 14:57 ..
-rw-r----- 1 ora10g dba 2048 Feb 20 15:05 1_31_647005853.dbf
-rw-r----- 1 ora10g dba 287744 Feb 20 15:05 1_30_647005853.dbf
-rw-r----- 1 ora10g dba 1024 Feb 20 15:05 1_29_647005853.dbf
-rw-r----- 1 ora10g dba 1024 Feb 20 15:05 1_28_647005853.dbf
-rw-r----- 1 ora10g dba 2048 Feb 20 15:05 1_27_647005853.dbf
-rw-r----- 1 ora10g dba 6144 Feb 20 15:05 1_26_647005853.dbf
-rw-r----- 1 ora10g dba 1024 Feb 20 15:05 1_25_647005853.dbf
-rw-r----- 1 ora10g dba 2048 Feb 20 15:05 1_24_647005853.dbf
-rw-r----- 1 ora10g dba 3950592 Feb 20 15:05 1_23_647005853.dbf
-rw-r----- 1 ora10g dba 391168 Feb 20 15:05 1_22_647005853.dbf
-rw-r----- 1 ora10g dba 1024 Feb 20 15:05 1_32_647005853.dbf
-rw-r----- 1 ora10g dba 5251072 Feb 20 15:13 users01.dbf
-rw-r----- 1 ora10g dba 209723392 Feb 20 15:13 undotbs01.dbf
-rw-r----- 1 ora10g dba 10493952 Feb 20 15:13 test1.dbf
-rw-r----- 1 ora10g dba 314580992 Feb 20 15:13 system01.dbf
-rw-r----- 1 ora10g dba 125837312 Feb 20 15:13 sysaux01.dbf
-rw-r----- 1 ora10g dba 52429312 Feb 20 15:13 redo01.log
drwxr-xr-x 2 ora10g dba 4096 Feb 20 15:13 .
-rw-r----- 1 ora10g dba 52429312 Feb 20 15:13 redo02.log
-rw-r----- 1 ora10g dba 5488640 Feb 20 15:13 control01.ctl

But how about if we did shutdown database and suddendly current redolog is corrupt or lost? For this we do not need to perform restore from backup

SYS@dbvd> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SYS@dbvd> startup mount;
ORACLE instance started.

Total System Global Area 922746880 bytes
Fixed Size 2075568 bytes
Variable Size 494928976 bytes
Database Buffers 419430400 bytes
Redo Buffers 6311936 bytes
Database mounted.
SYS@dbvd> select status, group#, members from v$log;

STATUS GROUP# MEMBERS
---------------- ---------- ----------
CURRENT 1 1
INACTIVE 3 1
INACTIVE 2 1

ora10g@bahamut:/backup/restore/dbvd> ls -a redo01*
-rw-r----- 1 ora10g dba 52429312 Feb 20 15:38 redo01.log

ora10g@bahamut:/backup/restore/dbvd> mv redo01.log ../
ora10g@bahamut:/backup/restore/dbvd> ls -arlt
total 1680960
drwxr-xr-x 6 ora10g dba 256 Feb 18 11:46 dump
-rw-r----- 1 ora10g dba 20979712 Feb 18 12:00 temp01.dbf
-rw-r----- 1 ora10g dba 52429312 Feb 18 12:34 redo03.log
-rw-r--r-- 1 ora10g dba 723 Feb 20 09:29 sqlnet.log
-rw-r----- 1 ora10g dba 52429312 Feb 20 15:37 redo02.log
-rw-r----- 1 ora10g dba 52429312 Feb 20 15:37 redo_03.log
drwxr-xr-x 2 ora10g dba 4096 Feb 20 15:37 arc
-rw-r----- 1 ora10g dba 5251072 Feb 20 15:38 users01.dbf
-rw-r----- 1 ora10g dba 209723392 Feb 20 15:38 undotbs01.dbf
-rw-r----- 1 ora10g dba 10493952 Feb 20 15:38 test1.dbf
-rw-r----- 1 ora10g dba 314580992 Feb 20 15:38 system01.dbf
-rw-r----- 1 ora10g dba 125837312 Feb 20 15:38 sysaux01.dbf
drwxr-xr-x 4 ora10g dba 4096 Feb 20 15:39 ..
drwxr-xr-x 4 ora10g dba 4096 Feb 20 15:39 .
-rw-r----- 1 ora10g dba 5488640 Feb 20 15:39 control03.ctl
-rw-r----- 1 ora10g dba 5488640 Feb 20 15:39 control02.ctl
-rw-r----- 1 ora10g dba 5488640 Feb 20 15:39 control01.ctl

As we can see our redo01.log is gone

SYS@dbvd> recover database;
ORA-00283: recovery session canceled due to errors
ORA-00264: no recovery required

Oppss we need to perform point in time recovery

SYS@dbvd> recover database until cancel;
Media recovery complete.
SYS@dbvd> alter database open resetlogs;

Database altered.

SYS@dbvd> select status, group#, members from v$log;

STATUS GROUP# MEMBERS
---------------- ---------- ----------
CURRENT 1 1
UNUSED 2 1
UNUSED 3 1

ora10g@bahamut:/backup/restore/dbvd> ls -a redo01*
-rw-r----- 1 ora10g dba 52429312 Feb 20 15:40 redo01.log

Yes, Thanks GOD our database are back now and we can see the redologs are back too. Moral of the story, always make sure you have a valid and good backup

Have a nice day

Cheers
FZheng

Rank Row

2008-02-15T00:14:00.000-08:00

One of the developer came to me yesterday. she was asking "I have some duplicate rows in col1 but not in col2 how can i just want to display the first row?

Here is the sample table so you can imagine it

me@d012band> select cust_no, name1 from vd.t2;

CUST_NO NAME1
---------------- --------
0000000900000377 ABC 1
0000000900000377 ABC 2
0000000900000377 ABC 3
0000000900001059 DEF 1
0000000900001059 DEF 2
0000000900001059 DEF 3
0000000900001059 BEA 1
0000000900003125 WER 1
0000000900003125 WER 2
0000000900003125 WER 3
0000000900004777 AFG 1
0000000900004777 AFG 2
0000000900004777 AFG 3

13 rows selected.

the output that she wanted to is something like this

CUST_NO NAME1
---------------- --------
0000000900000377 ABC 1
0000000900001059 DEF 1
0000000900003125 WER 1
0000000900004777 AFG 1

So, what i did is run this query and bingo we got the answer she wanted to

me@d012band> select cust_no, name1 from
2 (select cust_no, name1,rank() over (partition by cust_no order by rowid) rank_n from vd.t2)
3 where rank_n = 1;

CUST_NO NAME1
---------------- --------
0000000900000377 ABC 1
0000000900001059 DEF 1
0000000900003125 WER 1
0000000900004777 AFG 1

I used rank() function to rank every row, thanks to Oracle that build this kind function so that can make our query easier

Have a nice day

FZheng