starland: 3월 2010

2010년 3월 30일 화요일

[프로그래밍] java jdbc tomcat

쿼리결과 엑셀로 추출하기

http://blog.naver.com/iconfidence?Redirect=Log&logNo=100005912605

jcbc 연동테스트

http://blog.naver.com/pray44u?Redirect=Log&logNo=20100408706

java /tomcat 설치하기

http://reedids.springnote.com/pages/1882896

tomcat 다운

http://tomcat.apache.org/download-55.cgi

eclipse(wtp-all-in-one-sdk-win32)
http://archive.eclipse.org/webtools/downloads/drops/R2.0/R-2.0-200706260303/
http://www.eclipse.org/downloads/packages/release/europa/winter

eclipse 단축키
http://blog.naver.com/ccson94/67956393

관련글모인곳
http://blog.naver.com/dihkang?Redirect=Log&logNo=30016060654

3가지 셋팅
JSP 개발환경 구축 (java + tomcat + eclipse 설치 및 환경변수 셋팅)

2010년 3월 27일 토요일

[ORACLE] 펌 spfile 과 pfile (startup pfile='/xxxx/xxxx/init<SID>.ora')

출처 : http://blog.naver.com/khi830/20097894487

파라미터+파일[1]_1.txt

#############################

파라미터 파일

#############################
1.pfile (text file) : $ORACLE_HOME/dbs/initsid.ora

Dynamic parameter : java_pool_size, large_pool_size .... : 명령으로 수정 가능 -> 그러나 파라미터 파일에 내용은 변화되지 않는다.
Static parameter : log_buffer ... : 명령으로 수정 불가능 -> 수정이 필요하면 파라미터 파일을 수정한 뒤 restartup

2.spfile (binary file) : $ORACLE_HOME/dbs/spfilesid.ora --> persistent(지속) 파라미터 파일

Dynamic parameter : java_pool_size, large_pool_size .... : 명령으로 수정 가능 -> 파라미터 파일의 내용이 자동으로 수정된다.
Static parameter : log_buffer ... : 명령으로 수정 가능 -> scope=spfile 옵션을 붙여서 수정한 뒤 restartup

cf.Startup 할 경우의 파라미터 파일 우선 순위

SQL> startup --> $ORACLE_HOME/dbs/spfilesid.ora
--> $ORACLE_HOME/dbs/spfile.ora
--> $ORACLE_HOME/dbs/initsid.ora
--> 에러

SQL> startup pfile = '/home/oracle/myinit.ora'

##################################################################################################

=================================
Pfile을 사용할 경우
=================================

SQL> !rm $ORACLE_HOME/dbs/spfiletestdb.ora

SQL> startup force

SQL> show parameter

SQL> show parameter db_cache_size

NAME TYPE VALUE
------------------------------------ ---------------------- ------------------------------
db_cache_size big integer 64M

SQL> show parameter pool_size

NAME TYPE VALUE
------------------------------------ ---------------------- ------------------------------
global_context_pool_size string
java_pool_size big integer    24M
large_pool_size    big integer    0
olap_page_pool_size    big integer    0
shared_pool_size big integer    72M
streams_pool_size    big integer    0

SQL> alter system set large_pool_size=4m;

alter system set large_pool_size=4m
*
ERROR at line 1:
ORA-02097: parameter cannot be modified because specified value is invalid
ORA-04033: Insufficient memory to grow pool

SQL> alter system set java_pool_size=16m;
SQL> alter system set large_pool_size=4m;

SQL> show parameter pool_size

NAME TYPE VALUE
------------------------------------ ---------------------- ------------------------------
global_context_pool_size string
java_pool_size big integer    16M
large_pool_size    big integer    4M
olap_page_pool_size    big integer    0
shared_pool_size big integer    72M
streams_pool_size    big integer    0

SQL> show parameter log_buffer

NAME TYPE VALUE
------------------------------------ ---------------------- -------------------
log_buffer integer 29276

SQL> alter system set log_buffer=4m;

alter system set log_buffer=4m
*
ERROR at line 1:
ORA-02095: specified initialization parameter cannot be modified

SQL> !vi $ORACLE_HOME/dbs/inittestdb.ora

# 다음 파라미터를 가장 마지막 라인에 추가하세요.

log_buffer = 4194304

SQL> startup force

=================================
spfile을 사용할 경우
=================================

(1) spfile 생성 : 반드시 pfile이 있어야 가능

SQL> !ls -l $ORACLE_HOME/dbs

-rw-r----- 1 oracle oinstall 3584 1월 14 09:56 spfileorcl.ora
-rw-r--r-- 1 oracle oinstall 399 1월 14 11:54 inittestdb.ora

SQL> select instance_name from v$instance;

INSTANCE_NAME
--------------------------------
testdb

SQL> create spfile from pfile; --> 기본위치의 기본 pfile로 기본위치에 기본 spfile을 생성

SQL> !ls -l $ORACLE_HOME/dbs

-rw-r----- 1 oracle oinstall 3584 1월 14 09:56 spfileorcl.ora
-rw-r--r-- 1 oracle oinstall 399 1월 14 11:54 inittestdb.ora
-rw-r----- 1 oracle oinstall 1536 1월 14 12:09 spfiletestdb.ora

SQL> show parameter spfile

NAME TYPE VALUE
------------------------------------ ---------------------- ------------------------------
spfile string

SQL> startup force --> pfile과 spfile이 모두 있으므로, spfile을 이용해서 startup 된다.

SQL> show parameter spfile

NAME TYPE VALUE
------------------------------------ ---------------------- ------------------------------
spfile string /u01/app/oracle/product/10.2.0
/db_1/dbs/spfiletestdb.ora

SQL> show parameter pool_size

SQL> alter system set java_pool_size=16m; --> spfile을 이용할 경우 이 명령은 파라미터 파일도 수정한다.
SQL> alter system set large_pool_size=4m;

SQL> startup force

SQL> show parameter pool_size

NAME TYPE VALUE
------------------------------------ ---------------------- ------------------------------
global_context_pool_size string
java_pool_size big integer    16M
large_pool_size    big integer    4M
olap_page_pool_size    big integer    0
shared_pool_size big integer    72M
streams_pool_size    big integer    0

(2) spfile을 사용하면서 dynamic 파라미터 변경

SQL> alter system set large_pool_size=4m scope=both;    --> both가 디폴트이며 memory와 spfile이 모두 변경된다.
SQL> alter system set large_pool_size=4m scope=memory;    --> memory만 변경된다. 효과는 즉시 발휘된다. 하지만 restartup하면 이 변경은 사라진다.
SQL> alter system set large_pool_size=4m scope=spfile;    --> spfile만 변경된다. 효과는 restarup을 해야 발휘된다.

(3) spfile을 사용하면서 static 파라미터 변경

SQL> alter system set log_buffer = 4194304 scope=both;    --> 에러
SQL> alter system set log_buffer = 4194304 scope=memory;    --> 에러
SQL> alter system set log_buffer = 4194304 scope=spfile;    --> restartup을 해야 한다.

cf.pfile을 재료로 spfile 생성

SQL> create spfile='/home/oracle/my.ora' from pfile;
SQL> create spfile='/home/oracle/my.ora' from pfile='/etc/day.txt';

cf.spfile을 재료로 pfile 생성

SQL> create pfile from spfile;

===============================================
원하는 파라미터 파일을 이용해서 starup 하기
===============================================

(1) 원하는 pfile로 시작

SQL> !cp $ORACLE_HOME/dbs/inittestdb.ora /home/oracle/myday.ora

SQL> !vi /home/oracle/myday.ora

# 다음 파라미터의 값을 100에서 20으로 수정

processes = 20

SQL> shutdown abort

SQL> startup pfile='/home/oracle/myday.ora'

SQL> show parameter processes

NAME TYPE VALUE
------------------------------------ ---------------------- ------------------------------

processes integer 20

(2) 원하는 spfile로 시작

SQL> create spfile='/home/oracle/myspday.ora' from pfile='/home/oracle/myday.ora';

SQL> shutdown abort

SQL> startup pfile='/home/oracle/myspday.ora' -- 에러 : ORA-01078: failure in processing system parameters
SQL> startup spfile='/home/oracle/myspday.ora' -- 에러 : SP2-0714: invalid combination of STARTUP options

SQL> !vi /home/oracle/a.txt

spfile=/home/oracle/myspday.ora

SQL> startup pfile='/home/oracle/a.txt'

SQL> show parameter spfile

NAME TYPE VALUE
------------------------------------ ---------------------- ------------------------------
spfile string /home/oracle/myspday.ora

== 파라미터 파일 ==

1.pfile (text file) : $ORACLE_HOME/dbs/initsid.ora

2.spfile (binary file) : $ORACLE_HOME/dbs/spfilesid.ora --> persistent(지속) 파라미터 파일

cf.Startup 할 경우의 파라미터 파일 우선 순위

SQL> startup --> $ORACLE_HOME/dbs/spfilesid.ora
--> $ORACLE_HOME/dbs/spfile.ora
--> $ORACLE_HOME/dbs/initsid.ora
--> 에러

SQL> startup pfile = '/home/oracle/myinit.ora'

##################################################################################################

=================================
Pfile을 사용할 경우
=================================

SQL> !rm $ORACLE_HOME/dbs/spfiletestdb.ora

SQL> startup force

SQL> show parameter

SQL> show parameter db_cache_size

NAME TYPE VALUE
------------------------------------ ---------------------- ------------------------------
db_cache_size big integer 64M

SQL> show parameter pool_size

SQL> alter system set large_pool_size=4m;

alter system set large_pool_size=4m
*
ERROR at line 1:
ORA-02097: parameter cannot be modified because specified value is invalid
ORA-04033: Insufficient memory to grow pool

SQL> alter system set java_pool_size=16m;
SQL> alter system set large_pool_size=4m;

SQL> show parameter pool_size

NAME TYPE VALUE
------------------------------------ ---------------------- ------------------------------
global_context_pool_size string
java_pool_size big integer    16M
large_pool_size    big integer    4M
olap_page_pool_size    big integer    0
shared_pool_size big integer    72M
streams_pool_size    big integer    0

SQL> show parameter log_buffer

NAME TYPE VALUE
------------------------------------ ---------------------- -------------------
log_buffer integer 29276

SQL> alter system set log_buffer=4m;

alter system set log_buffer=4m
*
ERROR at line 1:
ORA-02095: specified initialization parameter cannot be modified

SQL> !vi $ORACLE_HOME/dbs/inittestdb.ora

# 다음 파라미터를 가장 마지막 라인에 추가하세요.

log_buffer = 4194304

SQL> startup force

=================================
spfile을 사용할 경우
=================================

(1) spfile 생성 : 반드시 pfile이 있어야 가능

SQL> !ls -l $ORACLE_HOME/dbs

-rw-r----- 1 oracle oinstall 3584 1월 14 09:56 spfileorcl.ora
-rw-r--r-- 1 oracle oinstall 399 1월 14 11:54 inittestdb.ora

SQL> select instance_name from v$instance;

INSTANCE_NAME
--------------------------------
testdb

SQL> create spfile from pfile; --> 기본위치의 기본 pfile로 기본위치에 기본 spfile을 생성

SQL> !ls -l $ORACLE_HOME/dbs

-rw-r----- 1 oracle oinstall 3584 1월 14 09:56 spfileorcl.ora
-rw-r--r-- 1 oracle oinstall 399 1월 14 11:54 inittestdb.ora
-rw-r----- 1 oracle oinstall 1536 1월 14 12:09 spfiletestdb.ora

SQL> show parameter spfile

NAME TYPE VALUE
------------------------------------ ---------------------- ------------------------------
spfile string

SQL> startup force --> pfile과 spfile이 모두 있으므로, spfile을 이용해서 startup 된다.

SQL> show parameter spfile

NAME TYPE VALUE
------------------------------------ ---------------------- ------------------------------
spfile string /u01/app/oracle/product/10.2.0
/db_1/dbs/spfiletestdb.ora

SQL> show parameter pool_size

SQL> alter system set java_pool_size=16m; --> spfile을 이용할 경우 이 명령은 파라미터 파일도 수정한다.
SQL> alter system set large_pool_size=4m;

SQL> startup force

SQL> show parameter pool_size

NAME TYPE VALUE
------------------------------------ ---------------------- ------------------------------
global_context_pool_size string
java_pool_size big integer    16M
large_pool_size    big integer    4M
olap_page_pool_size    big integer    0
shared_pool_size big integer    72M
streams_pool_size    big integer    0

(2) spfile을 사용하면서 dynamic 파라미터 변경

(3) spfile을 사용하면서 static 파라미터 변경

cf.pfile을 재료로 spfile 생성

SQL> create spfile='/home/oracle/my.ora' from pfile;
SQL> create spfile='/home/oracle/my.ora' from pfile='/etc/day.txt';

cf.spfile을 재료로 pfile 생성

SQL> create pfile from spfile;

===============================================
원하는 파라미터 파일을 이용해서 starup 하기
===============================================

(1) 원하는 pfile로 시작

SQL> !cp $ORACLE_HOME/dbs/inittestdb.ora /home/oracle/myday.ora

SQL> !vi /home/oracle/myday.ora

# 다음 파라미터의 값을 100에서 20으로 수정

processes = 20

SQL> shutdown abort

SQL> startup pfile='/home/oracle/myday.ora'

SQL> show parameter processes

NAME TYPE VALUE
------------------------------------ ---------------------- ------------------------------

processes integer 20

(2) 원하는 spfile로 시작

SQL> create spfile='/home/oracle/myspday.ora' from pfile='/home/oracle/myday.ora';

SQL> shutdown abort

SQL> !vi /home/oracle/a.txt

spfile=/home/oracle/myspday.ora

SQL> startup pfile='/home/oracle/a.txt'

SQL> show parameter spfile

NAME TYPE VALUE
------------------------------------ ---------------------- ------------------------------
spfile string /home/oracle/myspday.ora

2010년 3월 2일 화요일

[ORACLE] 11g RAC관련 유틸 (crsctl 등)

출처 : http://www.oracledba.org/11g/rac/11g_RAC_Admin_Utilities.html

11g RAC Administration and Maintenance Tasks and Utilities:

Task List:

Checking CRS Status
Viewing Name Of the Cluster
Viewing Nodes Configuration
Checking Votedisk Information
Checking OCR Disk information
Timeout Settings in Cluster
ADD/Remove OCR files
ADD/Remove Votedisk
Backing Up OCR
Backing Up Votedisk
Restoring OCR Devices
Restoring Voting Disk Devices
Changing Public IPs as well as Virtual IPs

Checking CRS Status:

The below two commands are generally used to check the status of CRS. The first command lists the status of CRS
on the local node where as the other command shows the CRS status across all the nodes in Cluster.

crsctl check crs <<-- for the local node

crsctl check cluster <<-- for remote nodes in the cluster

[root@node1-pub ~]# crsctl check crs
Cluster Synchronization Services appears healthy
Cluster Ready Services appears healthy
Event Manager appears healthy
[root@node1-pub ~]#

Checking Viability of CSS across nodes:

crsctl check cluster

For this command to run, CSS needs to be running on the local node. The "ONLINE" status for remote node says that CSS is running on that node.
When CSS is down on the remote node, the status of "OFFLINE" is displayed for that node.

[root@node1-pub ~]# crsctl check cluster
node1-pub    ONLINE
node2-pub    ONLINE

Viewing Cluster name:

I use below command to get the name of Cluster. You can also dump the ocr and view the name from the dump file.

ocrdump -stdout -keyname SYSTEM | grep -A 1 clustername | grep ORATEXT | awk '{print $3}'

[root@node1-pub ~]# ocrdump -stdout -keyname SYSTEM | grep -A 1 clustername | grep ORATEXT | awk '{print $3}'
test-crs
[root@node1-pub ~]# 

OR

ocrconfig -export /tmp/ocr_exp.dat -s online


for i in `strings /tmp/ocr_exp.dat | grep -A 1 clustername` ; do if [ $i != 'SYSTEM.css.clustername' ]; then echo $i; fi; done


[root@node1-pub ~]# ocrconfig -export /tmp/ocr_exp.dat -s online
[root@node1-pub ~]# for i in `strings /tmp/ocr_exp.dat | grep -A 1 clustername` ; do if [ $i != 'SYSTEM.css.clustername' ]; then echo $i; fi; done
test-crs
[root@node1-pub ~]#

OR

Oracle creates a directory with the same name as Cluster under the $ORA_CRS_HOME/cdata. you can get the cluster name from this directory as well.

[root@node1-pub ~]# ls /u01/app/crs/cdata
localhost  test-crs

Viewing No. Of Nodes configured in Cluster:

The below command can be used to find out the number of nodes registered into the cluster.
It also displays the node's Public name, Private name and Virtual name along with their numbers.

olsnodes -n -p -i

[root@node1-pub ~]# olsnodes -n -p -i 
node1-pub       1       node1-prv       node1-vip
node2-pub       2       node2-prv       node2-vip

Viewing Votedisk Information:

The below command is used to view the no. of Votedisks configured in the Cluster.

crsctl query css votedisk

[root@node1-pub ~]# crsctl query css votedisk
0. 0    /u02/ocfs2/vote/VDFile_0
1. 0    /u02/ocfs2/vote/VDFile_1
2. 0    /u02/ocfs2/vote/VDFile_2
Located 3 voting disk(s).
[root@node1-pub ~]#

[root@node1-pub ~]# crsctl check crs
Cluster Synchronization Services appears healthy
Cluster Ready Services appears healthy
Event Manager appears healthy
[root@node1-pub ~]#

Viewing OCR Disk Information:

The below command is used to view the no. of OCR files configured in the Cluster. It also displays the version of OCR
as well as storage space information. You can only have 2 OCR files at max.

ocrcheck

[root@node1-pub ~]# ocrcheck
Status of Oracle Cluster Registry is as follows :
   Version    :    2
   Total space (kbytes) : 262120
   Used space (kbytes)    : 3848
   Available space (kbytes) : 258272
   ID : 744414276
   Device/File Name : /u02/ocfs2/ocr/OCRfile_0
Device/File integrity check succeeded
   Device/File Name : /u02/ocfs2/ocr/OCRfile_1
Device/File integrity check succeeded

   Cluster registry integrity check succeeded

Various Timeout Settings in Cluster:

Disktimeout:
Disk Latencies in seconds from node-to-Votedisk. Default Value is 200. (Disk IO)
Misscount:
Network Latencies in second from node-to-node (Interconnect). Default Value is 60 Sec (Linux) and 30 Sec in Unix platform. (Network IO)
Misscount < Disktimeout

NOTE: Do not change them without contacting Oracle Support. This may cause logical corruption to the Data.

IF
  (Disk IO Time > Disktimeout) OR (Network

IO time > Misscount)
THEN
   REBOOT NODE
ELSE
   DO NOT REBOOT
END IF;

crsctl get css disktimeout
crsctl get css misscount
crsctl get css  reboottime

[root@node1-pub ~]# crsctl get css disktimeout
200

[root@node1-pub ~]# crsctl get css misscount
Configuration parameter misscount is not defined. <<<<< This message indicates that the Misscount is not set maually and it is set to it's
Default Value On Linux, it is default to 60 Second. If you want to chang it, you can do that as below. (Not recommended)

[root@node1-pub ~]# crsctl set css misscount 100
Configuration parameter misscount is now set to 100.
[root@node1-pub ~]# crsctl get css misscount
100

The below command sets the value of misscount back to its Default values:

crsctl unset css misscount

[root@node1-pub ~]# crsctl unset css misscount

[root@node1-pub ~]# crsctl get css reboottime
3

Add/Remove OCR file in Cluster:

Removing OCR File

(1) Get the Existing OCR file information by running ocrcheck utility.

[root@node1-pub ~]# ocrcheck
Status of Oracle Cluster Registry is as follows :
         Version                  :          2
         Total space (kbytes)     :     262120
         Used space (kbytes)      :       3852
         Available space (kbytes) :     258268
         ID                       :  744414276
         Device/File Name         : /u02/ocfs2/ocr/OCRfile_0 <-- OCR
                                    Device/File integrity check succeeded
         Device/File Name         : /u02/ocfs2/ocr/OCRfile_1 <-- OCR Mirror
                                    Device/File integrity check succeeded
 
         Cluster registry integrity check succeeded

(2) The First command removes the OCR mirror (/u02/ocfs2/ocr/OCRfile_1). If you want to remove the OCR
file (/u02/ocfs2/ocr/OCRfile_1) run the next command.

ocrconfig -replace ocrmirror
ocrconfig -replace ocr

[root@node1-pub ~]# ocrconfig -replace ocrmirror
[root@node1-pub ~]# ocrcheck
Status of Oracle Cluster Registry is as follows :
   Version    :    2
   Total space (kbytes) : 262120
   Used space (kbytes)    : 3852
   Available space (kbytes) : 258268
   ID : 744414276
   Device/File Name : /u02/ocfs2/ocr/OCRfile_0 <<-- OCR File
Device/File integrity check succeeded

Device/File not configured <-- OCR Mirror not existed any more

   Cluster registry integrity check succeeded

Adding OCR

You need to add OCR or OCR Mirror file in a case where you want to move the existing OCR file location to the different Devices.
The below command add ths OCR mirror file if OCR file alread exists.

(1) Get the Current status of OCR:

[root@node1-pub ~]# ocrconfig -replace ocrmirror
[root@node1-pub ~]# ocrcheck
Status of Oracle Cluster Registry is as follows :
   Version    :    2
   Total space (kbytes) : 262120
   Used space (kbytes)    : 3852
   Available space (kbytes) : 258268
   ID : 744414276
   Device/File Name : /u02/ocfs2/ocr/OCRfile_0 <<-- OCR File
Device/File integrity check succeeded

Device/File not configured <-- OCR Mirror does not exist

   Cluster registry integrity check succeeded

As You can see, I only have one OCR file but not the second file which is OCR Mirror.
So, I can add second OCR (OCR Mirror) as below command.

ocrconfig -replace ocrmirror <File name>

[root@node1-pub ~]# ocrconfig -replace ocrmirror /u02/ocfs2/ocr/OCRfile_1
[root@node1-pub ~]# ocrcheck
Status of Oracle Cluster Registry is as follows :
   Version    :    2
   Total space (kbytes) : 262120
   Used space (kbytes)    : 3852
   Available space (kbytes) : 258268
   ID : 744414276
   Device/File Name : /u02/ocfs2/ocr/OCRfile_0
Device/File integrity check succeeded
   Device/File Name : /u02/ocfs2/ocr/OCRfile_1
Device/File integrity check succeeded

   Cluster registry integrity check succeeded

You can have at most 2 OCR devices (OCR itself and its single Mirror) in a cluster. Adding extra Mirror gives you below error message

[root@node1-pub ~]# ocrconfig -replace ocrmirror /u02/ocfs2/ocr/OCRfile_2
PROT-21: Invalid parameter
[root@node1-pub ~]#

Add/Remove Votedisk file in Cluster:

Adding Votedisk:

Get the existing Vote Disks associated into the cluster. To be safe, Bring crs cluster stack down on all the nodes
but one on which you are going to add votedisk from.

(1) Stop CRS on all the nodes in cluster but one.

[root@node2-pub ~]# crsctl stop crs

(2) Get the list of Existing Vote Disks

crsctl query css votedisk

[root@node1-pub ~]# crsctl query css votedisk
0. 0    /u02/ocfs2/vote/VDFile_0
1. 0    /u02/ocfs2/vote/VDFile_1
2. 0    /u02/ocfs2/vote/VDFile_2
Located 3 voting disk(s).

(3) Backup the VoteDisk file

Backup the existing votedisks as below as oracle:

dd if=/u02/ocfs2/vote/VDFile_0 of=$ORACLE_BASE/bkp/vd/VDFile_0

[root@node1-pub ~]# su - oracle
[oracle@node1-pub ~]$ dd if=/u02/ocfs2/vote/VDFile_0 of=$ORACLE_BASE/bkp/vd/VDFile_0
41024+0 records in
41024+0 records out
[oracle@node1-pub ~]$

(4) Add an Extra Votedisk into the Cluster:

If it is a OCFS, then touch the file as oracle. On raw devices, initialize the raw devices using "dd" command

touch /u02/ocfs2/vote/VDFile_3 <<-- as oracle
crsctl add css votedisk /u02/ocfs2/vote/VDFile_3 <<-- as oracle
crsctl query css votedisks

[root@node1-pub ~]# su - oracle
[oracle@node1-pub ~]$ touch /u02/ocfs2/vote/VDFile_3
[oracle@node1-pub ~]$ crsctl add css votedisk /u02/ocfs2/vote/VDFile_3
Now formatting voting disk: /u02/ocfs2/vote/VDFile_3.
Successful addition of voting disk /u02/ocfs2/vote/VDFile_3.

(5) Confirm that the file has been added successfully:

[root@node1-pub ~]# ls -l /u02/ocfs2/vote/VDFile_3
-rw-r----- 1 oracle oinstall 21004288 Oct 6 16:31 /u02/ocfs2/vote/VDFile_3
[root@node1-pub ~]# crsctl query css votedisks
Unknown parameter: votedisks
[root@node1-pub ~]# crsctl query css votedisk
0. 0    /u02/ocfs2/vote/VDFile_0
1. 0    /u02/ocfs2/vote/VDFile_1
2. 0    /u02/ocfs2/vote/VDFile_2
3. 0    /u02/ocfs2/vote/VDFile_3
Located 4 voting disk(s).
[root@node1-pub ~]#

Removing Votedisk:

Removing Votedisk from the cluster is very simple. Tthe below command removes the given votedisk from cluster configuration.

crsctl delete css votedisk /u02/ocfs2/vote/VDFile_3

[root@node1-pub ~]# crsctl delete css votedisk /u02/ocfs2/vote/VDFile_3
Successful deletion of voting disk /u02/ocfs2/vote/VDFile_3.
[root@node1-pub ~]#

[root@node1-pub ~]# crsctl query css votedisk
0. 0    /u02/ocfs2/vote/VDFile_0
1. 0    /u02/ocfs2/vote/VDFile_1
2. 0    /u02/ocfs2/vote/VDFile_2
Located 3 voting disk(s).
[root@node1-pub ~]#

Backing Up OCR

Oracle performs physical backup of OCR devices every 4 hours under the default backup direcory $ORA_CRS_HOME/cdata/<CLUSTER_NAME>
and then it rolls that forward to Daily, weekly and monthly backup. You can get the backup information by executing below command.

ocrconfig -showbackup

[root@node1-pub ~]# ocrconfig -showbackup 

node2-pub     2007/09/03 17:46:47     /u01/app/crs/cdata/test-crs/backup00.ocr
 
node2-pub     2007/09/03 13:46:45     /u01/app/crs/cdata/test-crs/backup01.ocr
 
node2-pub     2007/09/03 09:46:44     /u01/app/crs/cdata/test-crs/backup02.ocr
 
node2-pub     2007/09/03 01:46:39     /u01/app/crs/cdata/test-crs/day.ocr
 
node2-pub     2007/09/03 01:46:39     /u01/app/crs/cdata/test-crs/week.ocr
[root@node1-pub ~]#

Manually backing up the OCR

ocrconfig -manualbackup <<--Physical Backup of OCR

The above command backs up OCR under the default Backup directory. You can export the contents of the OCR using below command (Logical backup).

ocrconfig -export /tmp/ocr_exp.dat -s online <<-- Logical Backup of OCR

Restoring OCR

The below command is used to restore the OCR from the physical backup. Shutdown CRS on all nodes.

ocrconfig -restore <file name>

Locate the avialable Backups

[root@node1-pub ~]# ocrconfig -showbackup
 
node2-pub     2007/09/03 17:46:47     /u01/app/crs/cdata/test-crs/backup00.ocr
 
node2-pub     2007/09/03 13:46:45     /u01/app/crs/cdata/test-crs/backup01.ocr
 
node2-pub     2007/09/03 09:46:44     /u01/app/crs/cdata/test-crs/backup02.ocr
 
node2-pub     2007/09/03 01:46:39     /u01/app/crs/cdata/test-crs/day.ocr
 
node2-pub     2007/09/03 01:46:39     /u01/app/crs/cdata/test-crs/week.ocr
 
node1-pub     2007/10/07 13:50:41     /u01/app/crs/cdata/test-crs/backup_20071007_135041.ocr

Perform Restore from previous Backup

[root@node2-pub ~]# ocrconfig -restore /u01/app/crs/cdata/test-crs/week.ocr

The above command restore the OCR from week old backup.
If you have logical backup of OCR (taken using export option), then You can import it with the below command.


ocrconfig -import /tmp/ocr_exp.dat

Restoring Votedisks

Shutdown CRS on all the nodes in Cluster.
Locate the current location of the Votedisks
Restore each of the votedisks using "dd" command from the previous good backup of Votedisk taken using the same "dd" command.
Start CRS on all the nodes.

crsctl stop crs
crsctl query css votedisk

dd if=<backup of Votedisk> of=<Votedisk file> <<-- do this for all the votedisks
crsctl start crs

Changing Public and Virtual IP Address:

Current Config Changed to

Node 1:

Public IP: 216.160.37.154    192.168.10.11
VIP: 216.160.37.153    192.168.10.111
subnet:    216.160.37.159    192.168.10.0
Netmask: 255.255.255.248 255.255.255.0
Interface used: eth0    eth0
Hostname:    node1-pub.hingu.net node1-pub.hingu.net

Node 2:

Public IP: 216.160.37.156    192.168.10.22
VIP: 216.160.37.157    192.168.10.222
subnet:    216.160.37.159    192.168.10.0
Netmask: 255.255.255.248 255.255.255.0
Interface used: eth0    eth0
Hostname:    node1-pub.hingu.net node2-pub.hingu.net

=======================================================================

(A)

Take the Services, Database, ASM Instances and nodeapps down on both the Nodes in Cluster.

Also disable the nodeapps, asm and database instances to prevent them from restarting in case if this node gets rebooted during this process.

srvctl stop service -d test

srvctl stop database -d test

srvctl stop asm -n node1-pub

srvctl stop asm -n node2-pub
srvctl stop nodeapps -n node1-pub,node1-pub2
srvctl disable instance -d test -i test1,test2

srvctl disable asm -n node1-pub

srvctl disable asm -n node2-pub

srvctl disable nodeapps -n node1-pub

srvctl disable nodeapps -n node2-pub


(B)
Modify the /etc/hosts and/or DNS, ifcfg-eth0 (local node) with the new IP values
on All the Nodes

(C)
Restart the specific network interface in order to use the new IP.

ifconfig eth0 down
ifconfig eth0 up


Or, you can restart the network.
CAUTION: on NAS, restarting entire network may cause the node to be rebooted.

(D)
Update the OCR with the New Public IP. 
In case of public IP, you have to delete the interface first and then add it back with the new IP address. 

As oracle user, Issue the below command:

oifcfg delif -global eth0
oifcfg setif -global eth0/192.168.10.0:public


(E)
Update the OCR with the New Virtual IP.
Virtual IP is part of the nodeapps and so you can modify the nodeapps to update the Virtual IP information.

As privileged user (root), Issue the below commands:

srvctl modify nodeapps -n node1-pub -A 192.168.10.111/255.255.255.0/eth0 <-- for Node 1

srvctl modify nodeapps -n node1-pub -A 192.168.10.222/255.255.255.0/eth0 <-- for Node 2


(F)
Enable the nodeapps, ASM, database Instances for all the Nodes.

srvctl enable instance -d test -i test1,test2

srvctl enable asm -n node1-pub

srvctl enable asm -n node2-pub

srvctl enable nodeapps -n node1-pub

srvctl enable nodeapps -n node2-pub

(G)
Update the listener.ora file on each nodes with the correct IP addresses in case if it uses the IP address instead of the hostname.

(H)
Restart the Nodeapps, ASM and Database instance

srvctl start nodeapps -n node1-pub

srvctl start nodeapps -n node2-pub

srvctl start asm -n node1-pub

srvctl start asm -n node2-pub

srvctl start database -d test

=======================================================================

2010년 3월 1일 월요일

[ORACLE] Oracle11g physical standby환경에서 ORA-08103에러

출처 : 허진님의 블로그

운영환경의 oracle 버젼과 동일해 퍼옵니다.

요약하자면, data guard환경에서

primary db에서 특정 table을 truncate후 새 data을 입력하면

standby db에서 갱신된 table에 접근하지 못하는(

ORA-08103: object no longer exists )

버그(Bug 7650993)에 관련된 내용 입니다.

================================================

다음의 환경에서 Data Guard를 운영하고 있습니다.

1. Oracle version – 11.1.0.7.0

2. O/S – Oracle Enterprise Linux 5.3 64bit

물론 primary host 및 standby host에 대해 동일한 O/S가 install되어 있고,

database version도 동일합니다.

몇 일전부터 문제가 발생하기 시작했는데,

어떤 문제인가 하면, 매일 새벽에 기동되는 batch program이 종료된 이후,

특정 table에 대해 select count(*) from를 실행하면

다음과 같이 ORA-08103 error가 발생한다는 것입니다.

SQL> select * from <table_name>;

ERROR:

ORA-08103: object no longer exists

물론 batch program은 primary host상에서 실행되며,

위의 error는 primary database상에서는 발생하지 않고,

오직 standby database상에서만 발생하고 있습니다.

참고로 이 batch program은 일부 tables를 truncate한 후에 새 data를 입력하는 처리를 수행합니다.

근본적인 해결책은 아니지만 임시처방으로서 다음과 같은 방법을 사용할 수 있습니다.

문제의 table이 emp라고 가정하고, primary database상에서 다음과 같이 table을 recreate합니다.

SQL> create table emp_bk as select * from emp;

Table created.

SQL> select dbms_metadata.get_ddl('TABLE', 'EMP', 'SCOTT') from dual;

…

/* primary key를 확인 */

SQL> select index_name from user_indexes where table_name = 'EMP';

…

/* emp와 관련된 indexes를 확인 */

SQL> select dbms_metadata.get_ddl('INDEX', '<index-01>', 'SCOTT') from dual;

SQL> select dbms_metadata.get_ddl('INDEX', '<index-02>', 'SCOTT') from dual;

…

/* index를 생성하기 위한 script를 추출 */

SQL> drop table emp;

Table dropped.

SQL> rename emp_bk to emp;

Table renamed.

/* table명을 변경 */

SQL> alter table emp add constraint … primary key (…);

/* primary key를 생성 */

SQL> create index <index-01> on emp (…);

Index created.

SQL> create index <index-02> on emp (…);

Index created.

이렇게 문제의 table을 recreate하면 ORA-08103 error는 더 이상 발생하지 않았습니다.

하지만 임시방편에 불과하므로 근본적인 해결을 위해

Oracle Support Team에 문의를 했습니다.

문의해 본 결과, 다음과 같은 response를 받았습니다.

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

This issue is caused by Bug 7650993.

You can apply one-off patch 7650993 to fix the issue.

To implement the solution, please execute the following steps:

1. Download and review the readme and pre-requisites for Patch.7650993.

Download Link: http://updates.oracle.com/download/7650993.html

Choose 11.1.0.7 and linux x86-64.

2. Ensure that you have taken a backup of your system before applying the recommended patch.

3. Apply the patch in a test environment.

4. Retest the issue.

5. Migrate the solution as appropriate to other environments.

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

이 patch를 apply하는 방법은 readme파일에도 적혀 있는데 간단히 설명하면,

1. 현재 running중인 $ORACLE_HOME상에 존재하는 모든 processes(listener, instances)를

정지시킨다.

2. 다음과 같이 patch를 apply하는 command를 실행한다.

$ cd 7650993

$ opatch apply

$ opatch lsinventory (제대로 patch가 apply되었는지 확인)

3. 정지시켰던 listener 및 instances를 재기동한다.

참고로 이 7650993 bug는 Oracle 11.1.0.6.0 및 Oracle 11.1.0.7.0에서 발생하고

있다고 합니다.

[출처] Oracle11g physical standby환경에서 ORA-08103에러|작성자 진우준세 아버지