pat98's always & forever

crsctl query 명령어 확인, crs sw version 및 patch 번호 등을 간단히 확인 할수 있다.

[TEST1]oracle@rac1:/home/oracle# crsctl query crs autostart
'Autostart delay':       0
'Autostart servercount': 1

[TEST1]oracle@rac1:/home/oracle# crsctl query crs releaseversion
Oracle High Availability Services release version on the local node is [12.1.0.2.0]

[TEST1]oracle@rac1:/home/oracle# crsctl query crs softwareversion
Oracle Clusterware version on node [rac1] is [12.1.0.2.0]

[TEST1]oracle@rac1:/home/oracle# crsctl query crs releasepatch
Oracle Clusterware release patch level is [2039526626] and the complete list of patches [19769480 20299023 20831110 21359755 21436941 21948354 22291127 22502518 22502555 ] have been applied on the local node.

[TEST1]oracle@rac1:/home/oracle# crsctl query crs softwarepatch
Oracle Clusterware patch level on node rac1 is [2039526626].

RAC DB의 INSTSNCE_NAME 및 DB_NAME 을 변경해 보자.

Single 과 크게 다를것은 없다. nid 를 이용하여 변경하면 되고 cluter_database=false 후 parameter 재설정 작업만 해주면 된다. 그외 리소스 삭제후 추가작업

-- 먼저 INSTANCE 이름을 바꾸어 본다.

현재 RAC1,RAC2 를 TEST1,TEST2 로 바꾸자

1. 현재 parameter file 확인

------------------------------------ ---------------------- ------------------------------
spfile                               string                 +DATA/RAC/PARAMETERFILE/spfile
                                                            .270.910889297
2. 현재 Instance 이름 확인

sys@RAC1> show parameter instance_name

NAME                                 TYPE                   VALUE
------------------------------------ ---------------------- ------------------------------
instance_name                        string                 RAC1

sys@RAC1> alter system set instance_name=TEST1 scope=spfile sid='RAC1';

System altered.

Elapsed: 00:00:00.16

3. 재기동 한후 확인, 별거없다. 바뀌었다.

sys@RAC1> show parameter instance_name

NAME                                 TYPE                   VALUE
------------------------------------ ---------------------- ------------------------------
instance_name                        string                 TEST1

sys@RAC1> show parameter db_name

NAME                                 TYPE                   VALUE
------------------------------------ ---------------------- ------------------------------
db_name                              string                 RAC
sys@RAC1> show parameter db_unique

NAME                                 TYPE                   VALUE
------------------------------------ ---------------------- ------------------------------
db_unique_name                       string                 RAC

sys@RAC1> SELECT instance FROM v$thread;

INSTANCE
-------------------------------------------------------------------------------------------
RAC1
RAC2

2번 노드도 동일하게 작업한다.

sys@RAC2> alter system set instance_name=TEST2 scope=spfile sid='RAC2';

.....

작업후 변경확인..잘된다.

-- 이제 DB_NAME 도 바꾸어 보겠다.

1. pfile 생성 후 내용수정

SQL> create pfile from spfile ;

생성된 pfile 을 수정해서 RAC1,RAC2 로 된 부분을 모두 TEST1,2 로 변경한다.

RAC1]oracle@rac1:/u01/app/oracle/product/12.1.0.2/dbhome_1/dbs# vi initTEST1.ora
TEST1.__data_transfer_cache_size=0
TEST2.__data_transfer_cache_size=0
TEST1.__db_cache_size=83886080
TEST2.__db_cache_size=130023424
TEST1.__java_pool_size=4194304
TEST2.__java_pool_size=4194304
TEST1.__large_pool_size=4194304
TEST2.__large_pool_size=4194304
TEST1.__oracle_base='/u01/app/oracle'#OTESTLE_BASE set from environment
TEST2.__oracle_base='/u01/app/oracle'#OTESTLE_BASE set from environment
TEST1.__pga_aggregate_target=104857600
TEST2.__pga_aggregate_target=104857600
TEST1.__sga_target=419430400
TEST2.__sga_target=419430400
TEST1.__shared_io_pool_size=8388608
TEST2.__shared_io_pool_size=12582912
TEST1.__shared_pool_size=310378496
TEST2.__shared_pool_size=260046848
TEST1.__streams_pool_size=0
TEST2.__streams_pool_size=0
*.audit_file_dest='/u01/app/oracle/admin/TEST/adump'
*.audit_sys_operations=FALSE
*.audit_trail='NONE'
*.cluster_database=true
*.compatible='12.1.0'
*.control_files='+DATA/rac/controlfile/current.256.907339295','+RECO/rac/controlfile/current.256.907339297'
*.db_block_size=8192
*.db_create_file_dest='+DATA'
*.db_domain=''
*.db_name='TEST'
*.db_recovery_file_dest='+RECO'
*.db_recovery_file_dest_size=6005194752
*.diagnostic_dest='/u01/app/oracle'
TEST2.instance_number=2
TEST1.instance_number=1
*.open_cursors=300
*.pga_aggregate_target=104857600
*.processes=150
*.remote_listener='rac-scan:1521'
*.remote_login_passwordfile='exclusive'
*.sec_case_sensitive_logon=FALSE
*.sga_target=419430400
TEST2.thread=2
TEST1.thread=1
TEST2.undo_tablespace='UNDOTBS2'
TEST1.undo_tablespace='UNDOTBS1'

2. password 화일 복사, adump directory 생성, 기존 initRAC1 화일 백업

[RAC1]oracle@rac1:/u01/app/oracle/product/12.1.0.2/dbhome_1/dbs# mkdir -p /u01/app/oracle/admin/TEST/adump
[RAC1]oracle@rac1:/u01/app/oracle/product/12.1.0.2/dbhome_1/dbs# cp orapwRAC1 orapwTEST1
[RAC1]oracle@rac1:/u01/app/oracle/product/12.1.0.2/dbhome_1/dbs# cp initRAC1.ora  initRAC1.ora.old

3. 양쪽 모두 내린다.

srvctl stop database -d RAC

4. exclusive 로 1번 startup

idle> startup mount exclusive pfile='/u01/app/oracle/product/12.1.0.2/dbhome_1/dbs/initRAC1.ora

ORACLE instance started.

Total System Global Area  419430400 bytes
Fixed Size                  2925120 bytes
Variable Size             318770624 bytes
Database Buffers           92274688 bytes
Redo Buffers                5459968 bytes
Database mounted.

5. nid 로 변경

[RAC1]oracle@rac1:/home/oracle# nid target=sys/oracle dbname=TEST setname=yes

DBNEWID: Release 12.1.0.2.0 - Production on Tue May 17 10:56:34 2016

Copyright (c) 1982, 2014, Oracle and/or its affiliates.  All rights reserved.

Connected to database RAC (DBID=2495321304)

Connected to server version 12.1.0

Control Files in database:
    +DATA/rac/controlfile/current.256.907339295
    +RECO/rac/controlfile/current.256.907339297

Change database name of database RAC to TEST? (Y/[N]) => y

Proceeding with operation
Changing database name from RAC to TEST
    Control File +DATA/rac/controlfile/current.256.907339295 - modified
    Control File +RECO/rac/controlfile/current.256.907339297 - modified
    Datafile +DATA/rac/datafile/system.259.90733933 - wrote new name
    Datafile +DATA/rac/datafile/sysaux.260.90733937 - wrote new name
    Datafile +DATA/rac/datafile/undotbs1.261.90733940 - wrote new name
    Datafile +DATA/rac/datafile/undotbs2.263.90733943 - wrote new name
    Datafile +DATA/rac/datafile/users.264.90733943 - wrote new name
    Datafile +DATA/rac/tempfile/temp.262.90733941 - wrote new name
    Control File +DATA/rac/controlfile/current.256.907339295 - wrote new name
    Control File +RECO/rac/controlfile/current.256.907339297 - wrote new name
    Instance shut down

Database name changed to TEST.
Modify parameter file and generate a new password file before restarting.
Succesfully changed database name.
DBNEWID - Completed succesfully.

6. profile의 환경변수 TEST로 변경, cluster_database=false 로 하고 pfile로 기동,

*.cluster_database=false
 
idle> startup pfile=/u01/app/oracle/product/12.1.0.2/dbhome_1/dbs/initTEST1.ora;
ORACLE instance started.

Total System Global Area  419430400 bytes
Fixed Size                  2925120 bytes
Variable Size             318770624 bytes
Database Buffers           92274688 bytes
Redo Buffers                5459968 bytes
Database mounted.
Database opened.

7. spfile 을 맹글어 준다.

idle> create spfile='+DATA' from pfile;

File created.

asmcmd -p 로 +ASM1 접속, 화일 확인

Type           Redund  Striped  Time             Sys  Name
PARAMETERFILE  UNPROT  COARSE   MAY 17 11:00:00  Y    none => spfile.271.912078483
PARAMETERFILE  UNPROT  COARSE   MAY 17 11:00:00  Y    none => spfile.272.912079201
PARAMETERFILE  UNPROT  COARSE   MAY 17 11:00:00  Y    none => spfile.273.912080113

8. pfile에 spfile 경로를 적어준다.

[TEST1]oracle@rac1:/u01/app/oracle/product/12.1.0.2/dbhome_1/dbs# vi initTEST1.ora
SPFILE='+DATA/TEST/PARAMETERFILE/spfile.273.912080113'

7. startup 한후에 다시 내린다.

idle> startup
ORA-32004: obsolete or deprecated parameter(s) specified for RDBMS instance
ORACLE instance started.

Total System Global Area  419430400 bytes
Fixed Size                  2925120 bytes
Variable Size             318770624 bytes
Database Buffers           92274688 bytes
Redo Buffers                5459968 bytes
Database mounted.
Database opened.

9. 2번 노드도 nid 실행하는거 빼고 나머지 절차를 동일하게 진행한다. 잘된다.

10. cluster_database=true 로 변경후 재기동

SQL> alter system set cluster_database=true scope=spfile sid='*';

11. CRS RESOURCE에서 바뀐 INSTANCE 를 인식못하니 기존것 삭제후 새로 등록한다.

ora.rac.db
      1        OFFLINE OFFLINE                               Instance Shutdown,ST
                                                             ABLE
      2        OFFLINE OFFLINE                               Instance Shutdown,ST
                                                             ABLE
12. 기존꺼 삭제 (쫄지 말자, 이건 DB에 아무런 영향을 주지 않는다)

[TEST1]oracle@rac1:/home/oracle# srvctl remove instance -d RAC -i RAC1
Remove instance from the database RAC? (y/[n]) y
[TEST1]oracle@rac1:/home/oracle# srvctl remove instance -d RAC -i RAC2
Remove instance from the database RAC? (y/[n]) y
[TEST1]oracle@rac1:/home/oracle# srvctl remove database -d RAC
Remove the database RAC? (y/[n]) y

13. 새로운거 추가

[TEST1]oracle@rac1:/home/oracle# srvctl add database -d TEST -o /u01/app/oracle/product/12.1.0.2/dbhome_1

[TEST1]oracle@rac1:/home/oracle# srvctl add instance -d TEST -i TEST1 -n rac1
[TEST1]oracle@rac1:/home/oracle# srvctl add instance -d TEST -i TEST2 -n rac2

14. RESOURCE에서 바로 인식 못할수 있으니 깔끔하게 crsctl stop crs 해서 다시 start 해준다.

15. 정상 등록 확인

ora.test.db
      1        ONLINE  ONLINE       rac1                     Open,STABLE
      2        ONLINE  ONLINE       rac2                     Open,STABLE

16. 적용된 값들 확인

sys@TEST1> show parameter instance_name

NAME                                 TYPE                   VALUE
------------------------------------ ---------------------- ------------------------------
instance_name                        string                 TEST1
sys@TEST1> show parameter db_name

NAME                                 TYPE                   VALUE
------------------------------------ ---------------------- ------------------------------
db_name                              string                 TEST
sys@TEST1> show parameter db_unique

NAME                                 TYPE                   VALUE
------------------------------------ ---------------------- ------------------------------
db_unique_name                       string                 TEST

17. 작업후기

RAC 도 별거 없이 잘 바뀌며, 작업시 parameter 백업, cluster_database=true,false 만 manage 잘하면 하면 OK ~~

SEVICE_NAME 등에 변경내역 반영, 접속 확인할 것.

windows 에서는 oracle 이 thread 방식으로 구동되기 때문에  작업관리자 등으로 process 를 살펴볼려고 해도 ORACLE.EXE 하나만 보여서 정확한 정보

를 파악할수 없는  경우가 많다. 떠 있으면 그냥 아 살아있네 하는 정도.?

services.msc 를 실행하여 개별 서비스 상태를 살펴볼수도 있지만 간혹 맛탱이가 가 있는 경우가 있어서 믿음이 가지 않는다.

유닉스의 ps -ef |grep ora_ 처럼 비스무리하게  process 를 자세하게 살펴볼수 있는데 윈도우즈의 sc 명령어를 활용하는 방법이다.

1. C:\Windows\System32>sc query state= all | findstr SERVICE_NAME | findstr Oracle

SERVICE_NAME: OracleDBConsoleorcl
SERVICE_NAME: OracleJobSchedulerORCL
SERVICE_NAME: OracleJobSchedulerXXX
SERVICE_NAME: OracleMTSRecoveryService
SERVICE_NAME: OracleOraDb11g_home1ClrAgent
SERVICE_NAME: OracleOraDb11g_home1TNSListener
SERVICE_NAME: OracleOraDb11g_home2ClrAgent
SERVICE_NAME: OracleOraDb11g_home2TNSListener
SERVICE_NAME: OracleServiceORCL
SERVICE_NAME: OracleServiceXXX
SERVICE_NAME: OracleVssWriterORCL
SERVICE_NAME: OracleVssWriterXXX

2. C:\Windows\System32>sc query OracleServiceORCL

SERVICE_NAME: OracleServiceORCL
        종류               : 10  WIN32_OWN_PROCESS
        상태              : 1  STOPPED
        WIN32_EXIT_CODE    : 1077  (0x435)
        SERVICE_EXIT_CODE  : 0  (0x0)
        검사점         : 0x0
        WAIT_HINT          : 0x0

3. C:\Windows\System32>sc qc OracleServiceORCL

[SC] QueryServiceConfig 성공

SERVICE_NAME: OracleServiceORCL
        종류               : 10  WIN32_OWN_PROCESS
        START_TYPE         : 3   DEMAND_START
        ERROR_CONTROL      : 1   NORMAL
        BINARY_PATH_NAME   : f:\app\oracle\product\11.2.0\dbhome\bin\ORACLE.EXE ORCL
        LOAD_ORDER_GROUP   :
        태그                : 0
        DISPLAY_NAME       : OracleServiceORCL
        종속성       :
        SERVICE_START_NAME : LocalSystem

12c Real-Time SQL Monitoring 조회

dbms_sql_monitor package 이용

(전제조건 : Tuning Pack 사용이 가능하여야 함)

- REAL TIME SQL 조회

set trimspool on
set trim on
set pagesize 0
set linesize 32767
set long 1000000
set longchunksize 1000000
spool sqlmon_active.html
select dbms_sql_monitor.report_sql_monitor(type=>'active')
from dual;
spool off

- 특정 SQL_ID 로 조회

set trimspool on
set trim on
set pagesize 0
set linesize 32767
set long 1000000
set longchunksize 1000000
spool sqlmon_active.html
select dbms_sql_monitor.report_sql_monitor(
sql_id=>' 1n482vfrxw014', type=>'active')
from dual;
spool off

RAC 11.2.0.4 에서 12.1.0.2 로 수동 업그레이드 후 이상증상 발견..

database 가 수동으로 startup 시에는 잘 올라가나 resource 확인시에는 Instance shutdown 으로 인지 못함..

현재 환경을 확인하기 위해 명령어 치면 아래와 같은 메세지..

[RAC1]oracle@rac1:/home/oracle# srvctl config database -d RAC

PRCD-1229 : An attempt to access configuration of database RAC was rejected because its version 11.2.0.4.0 differs from the program version 12.1.0.2.0. Instead run the program from /u01/app/oracle/product/11.2.0.4/dbhome_1.

헐..확인 명령어 자체가 안 먹다니...짜증 유발...구글신 호출 !!

ocrdump 를 수행해 보거나,

crsctl stat res -p 로 ora.rac.db 의 환경변수를 확인해 보면 아직 ORACLE_HOME 이 ORACLE_HOME=/u01/app/oracle/product/11.2.0.4/dbhome_1 로

OLD_HOME 을 가리키고 있음.

Solution :

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

srvctl upgrade database -d RAC -o /u01/app/oracle/product/12.1.0.2/dbhome_1

해당명령어로 ORACLE_HOME 을 upgrade 후 해결..

보통 11g 이전 버전의 클라이언트에서 12c 붙으려고 하면 해당 에러를 볼수 있다 한다.

ORA-28040: No matching authentication protocol error
ORA-03134: Connections to this server version are no longer supported error.

ORA-1017

기존에 있던 SQLNET.ALLOWED_LOGON_VERSION 값은 12c 에서 쓰지 않음.

설정 예)

1. Single 경우 $ORACLE_HOME/network/admin/sqlnet.ora 편집

   RAC 일 경우 $GI_HOME/network/admin/sqlnet.ora 편집

  테스트 해보니 RAC 환경인 경우에도  $ORACLE_HOME/network/admin/sqlnet.ora 를 편집해 주어야 함.

===================================================================================================
SQLNET.ALLOWED_LOGON_VERSION_SERVER=10

SQLNET.ALLOWED_LOGON_VERSION_CLIENT=10
===================================================================================================

2. listener 재시작 후 접속 확인

여기서 값은 사용하는 버전별로 8,9,10,11,12,12a(12.1.0.2 이상) authentication protocol 을 줄수 있다.

SQLNET.ALLOWED_LOGON_VERSION_CLIENT 은 해당 서버가 dblink 등을 사용하여 다른 서버에 붙을 때 사용한다.

default 값은 11임.

             - ASM 디스크의 상태 확인 / 설명

               SQL>select group_number as "GRP#", name, mount_status as "MOUNT", header_status as "HEADER", mode_status as "MODE", state, free_mb from v$asm_disk where header_status='MEMBER' order by group_number, name;

Install of Clusterware fails while running root.sh on OL7 - ohasd fails to start (문서 ID 1959008.1)

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

linux 7 에서 ohasd process 기동실패로 GI 설치 실패시, (Oracle linux 뿐만 아니라 RHEL 도 해당됨)

rootcrs.log/roothas.log 에 ohasd/crsd start 실패 메세지 나타남.

(Bug 18370031  - RC SCRIPTS (/ETC/RC.D/RC.* , /ETC/INIT.D/* ) ON OL7 FOR CLUSTERWARE)

- 11.2.0.4 or 12.1.0.1 해당됨

방법은 2가지임. 

A) Patch 18370031 미리 적용

B) 수동으로 ohasd service 생성

아직 systemd daeomon 을 제어할 방법이 없기 때문에 root.sh 실행전 해줘야 하는 작업임.

수동으로 생성방법

1. root 유저로

cd /etc/systemd/system/
touch ohasd.service

2. ohas.service 화일 편집

[Unit]
Description=Oracle High Availability Services
After=syslog.target

[Service]
ExecStart=/etc/init.d/init.ohasd run >/dev/null 2>&1 Type=simple
Restart=always

[Install]
WantedBy=multi-user.target

3. root 유저로

systemctl daemon-reload
systemctl enable ohasd.service
systemctl start ohasd.service

(원본 MOS 문서에 오타가 있으므로 수정할것)

4. root.sh 실행 끝.

확인사항..

12c 에서도 Database 인스턴스 버전과 ASM 인스턴스 버전이 다름.

Database 는 SE, ASM 은 EE 를 사용

- Database 인스턴스 버전

[oracle:/home/oracle]#ss

SQL*Plus: Release 12.1.0.2.0 Production on Wed Apr 27 09:46:07 2016

Copyright (c) 1982, 2014, Oracle.  All rights reserved.

Connected to:
Oracle Database 12c Standard Edition Release 12.1.0.2.0 - 64bit Production
With the Real Application Clusters and Automatic Storage Management options

- ASM 인스턴스 버전

[oracle:/home/oracle]#. oraenv
ORACLE_SID = [ORA01] ? +ASM1
The Oracle base has been changed from /oracle to /oragrid/gridbase
[oracle:/home/oracle]#sqlplus "/as sysasm"

SQL*Plus: Release 12.1.0.2.0 Production on Wed Apr 27 09:43:47 2016

Copyright (c) 1982, 2014, Oracle.  All rights reserved.

Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Real Application Clusters and Automatic Storage Management options

8i, 9i, 10g, 11g 오라클 column 갯수의 제한은  1000개임.

https://docs.oracle.com/cd/B28359_01/server.111/b28320/limits003.htm#i288032

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

Logical Database Limits