업무를 하다보면.. GRID가 올라간 상태에서 RAC 인경우에 갑자기 요구 조건에 의해 DB를 생성 하게 될 수도 있네요.
아래 절차는 DB 생성 후 RAC로 변경 후 GRID에 리소스 추가 한 경우 입니다.
설명이 매끄럽지는 않지만 보시기에는 나쁘지 않을듯 합니다.
작업 순서 정의
1.기존 DB가 존재 시 디스크 경로를 참고해서 만들어야 하므로 생성하고자 하는 곳 아무 DB에서 컨트롤 파일을 백업 수행
2. 초기 파라미터 정보 백업 후 수정 후 사용 하기
3. 싱글 DB OPEN 정상 확인 하기
4. RAC DB OPEN 정상 확인 하기
5. 기타 작업
1.기존 DB가 존재 시 디스크 경로를 참고해서 만들어야 하므로 생성하고자 하는 곳 아무 DB에서 컨트롤 파일을 백업 수행
2. 초기 파라미터 정보 백업 후 수정 후 사용 하기
3. 싱글 DB OPEN 정상 확인 하기
4. RAC DB OPEN 정상 확인 하기
5. 기타 작업
기존DB 에서 정보를 추출 합니다. ( 추가 하고자 하는 장비에서 DB가 정상 구동 중인 곳 )
--1. trace 파일 백업 하기 ( 기존 정상 DB 에서 백업 즉 생성 하고자 하는 서버에 DB가 존재시 )
SQL> oradebug setmypid
Statement processed.
SQL> oradebug tracefile_name
/LOGS/diag/rdbms/DB_TEST/DB_TEST1/trace/DB_TEST1_ora_2424.trc
SQL> alter database backup controlfile to trace;
Database altered.
SQL> quit
/LOGS/diag/rdbms/DB_TEST/DB_TEST1/trace/DB_TEST1_ora_2424.trc 트레이스 파일 열어보면 백업 수행이 정상 확인 완료
--2. asm, db init 파일 기존 디비에서 백업 수행
=> GRID 유저에서 수행
SQL> create pfile='asm_pfile.ora' from spfile;
; 정보 확인차 사용
=> ORACLE 유저에서 수행
SQL> create pfile='db_init.ora' from spfile;
; 실질적인 내용 정보 수정 시 사용
SQL> oradebug setmypid
Statement processed.
SQL> oradebug tracefile_name
/LOGS/diag/rdbms/DB_TEST/DB_TEST1/trace/DB_TEST1_ora_2424.trc
SQL> alter database backup controlfile to trace;
Database altered.
SQL> quit
/LOGS/diag/rdbms/DB_TEST/DB_TEST1/trace/DB_TEST1_ora_2424.trc 트레이스 파일 열어보면 백업 수행이 정상 확인 완료
--2. asm, db init 파일 기존 디비에서 백업 수행
=> GRID 유저에서 수행
SQL> create pfile='asm_pfile.ora' from spfile;
; 정보 확인차 사용
=> ORACLE 유저에서 수행
SQL> create pfile='db_init.ora' from spfile;
; 실질적인 내용 정보 수정 시 사용
실질적으로 중요한 내용 입니다.
-- 3. db_init.ora 파일 수정 하기 ( 신규 db 용)
control_files='+DATA_DG'
undo_management = auto
db_name = DB_TEST
db_block_size = 8192
db_create_file_dest='+DATA_DG'
db_recovery_file_dest='+FRA_DG'
db_recovery_file_dest_size=10240M
## DB_TEST.ora 파일 저장 하기
export ORACLE_SID=DB_TEST
--4. trace파일 정리하기
-- 기존 trace 파일을 수정 해야 합니다.
-- 원본중 아무거나 가지고 와서 create databse 명령어게 맞게
-- 수정을 하시면 됩니다. DB NAME을 따라서 수정 잘 하시면 됩니다.
CREATE DATABASE DB_TEST
LOGFILE
GROUP 1 (
'+DATA_DG'
) SIZE 500M BLOCKSIZE 512,
GROUP 2 (
'+DATA_DG'
) SIZE 500M BLOCKSIZE 512
CHARACTER SET AL32UTF8
;
control_files='+DATA_DG'
undo_management = auto
db_name = DB_TEST
db_block_size = 8192
db_create_file_dest='+DATA_DG'
db_recovery_file_dest='+FRA_DG'
db_recovery_file_dest_size=10240M
## DB_TEST.ora 파일 저장 하기
export ORACLE_SID=DB_TEST
--4. trace파일 정리하기
-- 기존 trace 파일을 수정 해야 합니다.
-- 원본중 아무거나 가지고 와서 create databse 명령어게 맞게
-- 수정을 하시면 됩니다. DB NAME을 따라서 수정 잘 하시면 됩니다.
CREATE DATABASE DB_TEST
LOGFILE
GROUP 1 (
'+DATA_DG'
) SIZE 500M BLOCKSIZE 512,
GROUP 2 (
'+DATA_DG'
) SIZE 500M BLOCKSIZE 512
CHARACTER SET AL32UTF8
;
아주 중요한 내용 입니다. ( 제가 생각 하기에는 특히.. )
5. pfile 에서 spfile 로 반드시 변경 해야 함
ORA-00205: error in identifying control file, check alert log for more info
미 수행시 db 정지 후 시작시 에러 발생
ORA-00205: error in identifying control file, check alert log for more info
미 수행시 db 정지 후 시작시 에러 발생
로그 입니다.
작업 로그 실행 잘 보시면 됩니다.
/** ----
[oracle@db19c1(DB_TEST):/db/app/product/dbhome/dbs]echo $ORACLE_SID
DB_TEST
[oracle@db19c1(DB_TEST):/db/app/product/dbhome/dbs]
[oracle@db19c1(DB_TEST):/db/app/product/dbhome/dbs]sqlplus
SQL*Plus: Release 19.0.0.0.0 - Production on Thu Jul 23 16:42:46 2020
Version 19.7.0.0.0
Copyright (c) 1982, 2020, Oracle. All rights reserved.
Enter user-name: /as sysdba
Connected to an idle instance.
SQL>
SQL>
SQL> create spfile='+DATA_DG' from pfile='DB_TEST.ora';
create spfile='+DATA_DG' from pfile='DB_TEST.ora'
*
ERROR at line 1:
ORA-03113: end-of-file on communication channel
Process ID: 0
Session ID: 0 Serial number: 0
SQL> !ls -lart DB_TEST
-rw-r--r--. 1 oracle oinstall 186 Jul 23 16:39 DB_TEST.ora
SQL>
SQL> -- 아래 절차 중요 함
SQL> create spfile from pfile='DB_TEST.ora';
File created.
SQL> startup nomount
ORACLE instance started.
Total System Global Area 272628680 bytes
Fixed Size 8895432 bytes
Variable Size 209715200 bytes
Database Buffers 50331648 bytes
Redo Buffers 3686400 bytes
SQL> select INSTANCE_NAME , STATUS from v$instance;
INSTANCE_NAME STATUS
---------------- ------------
DB_TEST STARTED
SQL>
CREATE DATABASE DB_TEST
LOGFILE
GROUP 1 (
'+DATA_DG'
) SIZE 500M BLOCKSIZE 512,
GROUP 2 (
'+DATA_DG'
) SIZE 500M BLOCKSIZE 512
CHARACTER SET AL32UTF8
;
Database created.
--> 위처럼 하시게 되면... 자동으로 로그 상에..
processing ?/rdbms/admin/dsqlddl.bsq
2020-07-23T16:51:56.012156+09:00
processing ?/rdbms/admin/dmanage.bsq
CREATE TABLESPACE sysaux DATAFILE /* OMF datafile */
EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO ONLINE FORCE LOGGING
2020-07-23T16:51:57.775744+09:00
Completed: CREATE TABLESPACE sysaux DATAFILE /* OMF datafile */
EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO ONLINE FORCE LOGGING
alter tablespace system default compress for all operations
Completed: alter tablespace system default compress for all operations
alter tablespace sysaux default compress for all operations
Completed: alter tablespace sysaux default compress for all operations
2020-07-23T16:51:58.128721+09:00
processing ?/rdbms/admin/dplsql.bsq
processing ?/rdbms/admin/dtxnspc.bsq
CREATE UNDO TABLESPACE SYS_UNDOTS DATAFILE SIZE 10M AUTOEXTEND ON
2020-07-23T16:51:59.747276+09:00
[26116] Successfully onlined Undo Tablespace 2.
Completed: CREATE UNDO TABLESPACE SYS_UNDOTS DATAFILE SIZE 10M AUTOEXTEND ON
ALTER DATABASE DEFAULT TABLESPACE SYSTEM
Completed: ALTER DATABASE DEFAULT TABLESPACE SYSTEM
processing ?/rdbms/admin/dfmap.bsq
processing ?/rdbms/admin/denv.bsq
processing ?/rdbms/admin/drac.bsq
2020-07-23T16:52:00.937240+09:00
processing ?/rdbms/admin/dsec.bsq
2020-07-23T16:52:03.185974+09:00
processing ?/rdbms/admin/doptim.bsq
SQL> select name from v$tablespace;
NAME
------------------------------
SYSTEM
SYSAUX
SYS_UNDOTS --> 자동 생성
SQL>
SQL> select INSTANCE_NAME , STATUS from gv$instance;
INSTANCE_NAME STATUS
---------------- ------------
DB_TEST OPEN
SQL>
SQL>
-- 정상여부 재 확인
SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL>
SQL> startup
ORACLE instance started.
Total System Global Area 272628680 bytes
Fixed Size 8895432 bytes
Variable Size 209715200 bytes
Database Buffers 50331648 bytes
Redo Buffers 3686400 bytes
Database mounted.
Database opened.
SQL>
-- ####### **/
/** ----
[oracle@db19c1(DB_TEST):/db/app/product/dbhome/dbs]echo $ORACLE_SID
DB_TEST
[oracle@db19c1(DB_TEST):/db/app/product/dbhome/dbs]
[oracle@db19c1(DB_TEST):/db/app/product/dbhome/dbs]sqlplus
SQL*Plus: Release 19.0.0.0.0 - Production on Thu Jul 23 16:42:46 2020
Version 19.7.0.0.0
Copyright (c) 1982, 2020, Oracle. All rights reserved.
Enter user-name: /as sysdba
Connected to an idle instance.
SQL>
SQL>
SQL> create spfile='+DATA_DG' from pfile='DB_TEST.ora';
create spfile='+DATA_DG' from pfile='DB_TEST.ora'
*
ERROR at line 1:
ORA-03113: end-of-file on communication channel
Process ID: 0
Session ID: 0 Serial number: 0
SQL> !ls -lart DB_TEST
-rw-r--r--. 1 oracle oinstall 186 Jul 23 16:39 DB_TEST.ora
SQL>
SQL> -- 아래 절차 중요 함
SQL> create spfile from pfile='DB_TEST.ora';
File created.
SQL> startup nomount
ORACLE instance started.
Total System Global Area 272628680 bytes
Fixed Size 8895432 bytes
Variable Size 209715200 bytes
Database Buffers 50331648 bytes
Redo Buffers 3686400 bytes
SQL> select INSTANCE_NAME , STATUS from v$instance;
INSTANCE_NAME STATUS
---------------- ------------
DB_TEST STARTED
SQL>
CREATE DATABASE DB_TEST
LOGFILE
GROUP 1 (
'+DATA_DG'
) SIZE 500M BLOCKSIZE 512,
GROUP 2 (
'+DATA_DG'
) SIZE 500M BLOCKSIZE 512
CHARACTER SET AL32UTF8
;
Database created.
--> 위처럼 하시게 되면... 자동으로 로그 상에..
processing ?/rdbms/admin/dsqlddl.bsq
2020-07-23T16:51:56.012156+09:00
processing ?/rdbms/admin/dmanage.bsq
CREATE TABLESPACE sysaux DATAFILE /* OMF datafile */
EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO ONLINE FORCE LOGGING
2020-07-23T16:51:57.775744+09:00
Completed: CREATE TABLESPACE sysaux DATAFILE /* OMF datafile */
EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO ONLINE FORCE LOGGING
alter tablespace system default compress for all operations
Completed: alter tablespace system default compress for all operations
alter tablespace sysaux default compress for all operations
Completed: alter tablespace sysaux default compress for all operations
2020-07-23T16:51:58.128721+09:00
processing ?/rdbms/admin/dplsql.bsq
processing ?/rdbms/admin/dtxnspc.bsq
CREATE UNDO TABLESPACE SYS_UNDOTS DATAFILE SIZE 10M AUTOEXTEND ON
2020-07-23T16:51:59.747276+09:00
[26116] Successfully onlined Undo Tablespace 2.
Completed: CREATE UNDO TABLESPACE SYS_UNDOTS DATAFILE SIZE 10M AUTOEXTEND ON
ALTER DATABASE DEFAULT TABLESPACE SYSTEM
Completed: ALTER DATABASE DEFAULT TABLESPACE SYSTEM
processing ?/rdbms/admin/dfmap.bsq
processing ?/rdbms/admin/denv.bsq
processing ?/rdbms/admin/drac.bsq
2020-07-23T16:52:00.937240+09:00
processing ?/rdbms/admin/dsec.bsq
2020-07-23T16:52:03.185974+09:00
processing ?/rdbms/admin/doptim.bsq
SQL> select name from v$tablespace;
NAME
------------------------------
SYSTEM
SYSAUX
SYS_UNDOTS --> 자동 생성
SQL>
SQL> select INSTANCE_NAME , STATUS from gv$instance;
INSTANCE_NAME STATUS
---------------- ------------
DB_TEST OPEN
SQL>
SQL>
-- 정상여부 재 확인
SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL>
SQL> startup
ORACLE instance started.
Total System Global Area 272628680 bytes
Fixed Size 8895432 bytes
Variable Size 209715200 bytes
Database Buffers 50331648 bytes
Redo Buffers 3686400 bytes
Database mounted.
Database opened.
SQL>
-- ####### **/
카타로그 수행 및 기타 작업 Single DB 완료
-- 5 catalog etc.. 수행
sqlplus /as sysdba
startup
@?/rdbms/admin/catalog.sql
@?/rdbms/admin/catproc.sql
@?/sqlplus/admin/pupbld.sql
SQL> show parameter spfile
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
spfile string /db/app/product/dbhome/dbs/spfileDB_TEST.ora
SQL>
-- 추가 데이타파일 하기
CREATE TABLESPACE "USERS" LOGGING DATAFILE '+DATA_DG' SIZE 500M;
CREATE TEMPORARY TABLESPACE "TEMP" TEMPFILE '+DATA_DG' SIZE 500M;
CREATE UNDO TABLESPACE "UNDOTBS1" DATAFILE '+DATA_DG' SIZE 500M;
-- Single DB 완료
sqlplus /as sysdba
startup
@?/rdbms/admin/catalog.sql
@?/rdbms/admin/catproc.sql
@?/sqlplus/admin/pupbld.sql
SQL> show parameter spfile
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
spfile string /db/app/product/dbhome/dbs/spfileDB_TEST.ora
SQL>
-- 추가 데이타파일 하기
CREATE TABLESPACE "USERS" LOGGING DATAFILE '+DATA_DG' SIZE 500M;
CREATE TEMPORARY TABLESPACE "TEMP" TEMPFILE '+DATA_DG' SIZE 500M;
CREATE UNDO TABLESPACE "UNDOTBS1" DATAFILE '+DATA_DG' SIZE 500M;
-- Single DB 완료
RAC로 변경 하기
--6. add RAC
@?/rdbms/admin/catclust.sql
== 1번노드 initDB_TEST.ora 파일 수정
DB_TEST1.undo_tablespace='UNDOTBS1'
DB_TEST2.undo_tablespace='UNDOTBS2'
cluster_database=true
thread=1
instance_number=1
scp initDB_TEST.ora oracle@db19c2 :/db/app/product/dbhome/dbs
== 2번노드 initDB_TEST.ora 파일 수정
cluster_database=true
thread=2
instance_number=2
startup mount pfile='initDB_TEST.ora';
[oracle@db19c2(DB_TEST):/db/app/product/dbhome/dbs]sqlplus
SQL*Plus: Release 19.0.0.0.0 - Production on Sun Jul 26 22:13:13 2020
Version 19.7.0.0.0
Copyright (c) 1982, 2020, Oracle. All rights reserved.
Enter user-name: /as sysdba
Connected to an idle instance.
SQL> startup mount pfile='initDB_TEST.ora';
ORACLE instance started.
Total System Global Area 327155264 bytes
Fixed Size 8896064 bytes
Variable Size 264241152 bytes
Database Buffers 50331648 bytes
Redo Buffers 3686400 bytes
ORA-01617: cannot mount: 2 is not a valid thread number
SQL>
==> 1번 노드로 다시 가서
alter database
add logfile thread 2
group 3 ('+DATA_DG') size 500M ,
group 4 ('+DATA_DG') size 500M
;
alter database enable public thread 2;
CREATE UNDO TABLESPACE "UNDOTBS2" DATAFILE '+DATA_DG' SIZE 500M;
/**
SQL> alter database
2 add logfile thread 2
3 group 3 ('+DATA_DG') size 500M ,
4 group 4 ('+DATA_DG') size 500M
5 ;
Database altered.
SQL> alter database enable public thread 2;
Database altered.
SQL> CREATE UNDO TABLESPACE "UNDOTBS2" DATAFILE '+DATA_DG' SIZE 500M;
**/
==> 2번노드에서 다시 기동
startup mount pfile='initDB_TEST.ora';
alter database open;
create spfile from pfile='initDB_TEST.ora';
SQL> startup mount pfile='initDB_TEST.ora';
ORACLE instance started.
Total System Global Area 327155264 bytes
Fixed Size 8896064 bytes
Variable Size 264241152 bytes
Database Buffers 50331648 bytes
Redo Buffers 3686400 bytes
Database mounted.
SQL>
SQL> alter database open;
Database altered.
SQL>
SQL> create spfile from pfile='initDB_TEST.ora';
File created.
SQL>
SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL>
SQL> startup
ORACLE instance started.
Total System Global Area 327155264 bytes
Fixed Size 8896064 bytes
Variable Size 264241152 bytes
Database Buffers 50331648 bytes
Redo Buffers 3686400 bytes
Database mounted.
Database opened.
SQL>
SQL> show parameter spfile;
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
spfile string /db/app/product/dbhome/dbs/spfileDB_TEST.ora
SQL>
SQL>
[oracle@db19c1(DB_TEST):/db/app/product/dbhome/dbs]ps -ef | grep DB_TEST
oracle 13296 1 0 22:28 ? 00:00:00 ora_pmon_DB_TEST
oracle 13298 1 0 22:28 ? 00:00:00 ora_clmn_DB_TEST
oracle 13300 1 0 22:28 ? 00:00:00 ora_psp0_DB_TEST
oracle 13304 1 0 22:28 ? 00:00:00 ora_ipc0_DB_TEST
oracle 13306 1 0 22:28 ? 00:00:00 ora_vktm_DB_TEST
==> 1번 서버
mv initDB_TEST.ora initDB_TEST1.ora
SQL> startup mount pfile='initDB_TEST1.ora';
ORACLE instance started.
Total System Global Area 327155264 bytes
Fixed Size 8896064 bytes
Variable Size 264241152 bytes
Database Buffers 50331648 bytes
Redo Buffers 3686400 bytes
Database mounted.
SQL>
SQL> alter database open;
Database altered.
SQL> create spfile from pfile='initDB_TEST1.ora';
File created.
SQL> quit
Disconnected from Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.7.0.0.0
[oracle@db19c1(DB_TEST1):/db/app/product/dbhome/dbs]
[oracle@db19c1(DB_TEST1):/db/app/product/dbhome/dbs]
[oracle@db19c1(DB_TEST1):/db/app/product/dbhome/dbs]ps -ef | grep DB_TEST
oracle 14722 1 0 22:33 ? 00:00:00 ora_pmon_DB_TEST1
oracle 14725 1 0 22:33 ? 00:00:00 ora_clmn_DB_TEST1
oracle 14728 1 0 22:33 ? 00:00:00 ora_psp0_DB_TEST1
oracle 14734 1 0 22:33 ? 00:00:00 ora_ipc0_DB_TEST1
oracle 14737 1 0 22:33 ? 00:00:00 ora_vktm_DB_TEST1
oracle 14741 1 0 22:33 ? 00:00:00 ora_gen0_DB_TEST1
oracle 14743 1 0 22:33 ? 00:00:00 ora_mman_DB_TEST1
oracle 14747 1 0 22:33 ? 00:00:00 ora_gen1_DB_TEST1
==> 2번 서버
[oracle@db19c2(DB_TEST):/db/app/product/dbhome/dbs]mv initDB_TEST.ora initDB_TEST2.ora
[oracle@db19c2(DB_TEST):/db/app/product/dbhome/dbs]export ORACLE_SID=DB_TEST2
[oracle@db19c2(DB_TEST2):/db/app/product/dbhome/dbs]sqlplus
SQL*Plus: Release 19.0.0.0.0 - Production on Sun Jul 26 22:35:32 2020
Version 19.7.0.0.0
Copyright (c) 1982, 2020, Oracle. All rights reserved.
Enter user-name: /as sysdba
Connected to an idle instance.
SQL>
SQL> startup mount pfile='initDB_TEST2.ora';
ORACLE instance started.
Total System Global Area 327155264 bytes
Fixed Size 8896064 bytes
Variable Size 264241152 bytes
Database Buffers 50331648 bytes
Redo Buffers 3686400 bytes
Database mounted.
SQL>
SQL> alter database open;
Database altered.
SQL> create spfile from pfile='initDB_TEST2.ora';
File created.
SQL>
[oracle@db19c2(DB_TEST2):/db/app/product/dbhome/dbs]ps -ef | grep DB_TEST
oracle 18771 1 0 22:36 ? 00:00:00 ora_pmon_DB_TEST2
oracle 18773 1 0 22:36 ? 00:00:00 ora_clmn_DB_TEST2
oracle 18775 1 0 22:36 ? 00:00:00 ora_psp0_DB_TEST2
oracle 18777 1 0 22:36 ? 00:00:00 ora_ipc0_DB_TEST2
oracle 18779 1 0 22:36 ? 00:00:00 ora_vktm_DB_TEST2
==> 각각노드에서 수행
create pfile from spfile;
shutdown immediate
startup
== 정상 여부 확인 완료 ===
==> OS 유저 oracle 유저에서 수행
==> db 추가 ( srvctl add database -h )
srvctl add database -db DB_TEST -oraclehome /db/app/product/dbhome -dbtype RAC
==> 인스턴스를 추가 ( srvctl add instance -h )
srvctl add instance -db DB_TEST -instance DB_TEST1 -node db19c1
srvctl add instance -db DB_TEST -instance DB_TEST2 -node db19c2
: 안해 주면..
srvctl start database -db DB_TEST 수행시
PRKO-3119 : Database DB_TEST cannot be started since it has no configured instances.
==> crsctl status res -t
~~~~~
ora.db_test.db
1 OFFLINE OFFLINE STABLE
2 OFFLINE OFFLINE STABLE
~~~~~
export ORACLE_SID=DB_TEST1
sqlplus /as sysdba
shut immediate
export ORACLE_SID=DB_TEST2
sqlplus /as sysdba
shut immediate
srvctl start database -db DB_TEST
crsctl status res -t
~~~~~~~~~
ora.db_test.db
1 OFFLINE ONLINE db19c1 Open,HOME=/db/app/pr
oduct/dbhome,STOPPIN
G
2 OFFLINE ONLINE db19c2 Open,HOME=/db/app/pr
oduct/dbhome,STOPPIN
G
~~~~~~~~~
@?/rdbms/admin/catclust.sql
== 1번노드 initDB_TEST.ora 파일 수정
DB_TEST1.undo_tablespace='UNDOTBS1'
DB_TEST2.undo_tablespace='UNDOTBS2'
cluster_database=true
thread=1
instance_number=1
scp initDB_TEST.ora oracle@db19c2 :/db/app/product/dbhome/dbs
== 2번노드 initDB_TEST.ora 파일 수정
cluster_database=true
thread=2
instance_number=2
startup mount pfile='initDB_TEST.ora';
[oracle@db19c2(DB_TEST):/db/app/product/dbhome/dbs]sqlplus
SQL*Plus: Release 19.0.0.0.0 - Production on Sun Jul 26 22:13:13 2020
Version 19.7.0.0.0
Copyright (c) 1982, 2020, Oracle. All rights reserved.
Enter user-name: /as sysdba
Connected to an idle instance.
SQL> startup mount pfile='initDB_TEST.ora';
ORACLE instance started.
Total System Global Area 327155264 bytes
Fixed Size 8896064 bytes
Variable Size 264241152 bytes
Database Buffers 50331648 bytes
Redo Buffers 3686400 bytes
ORA-01617: cannot mount: 2 is not a valid thread number
SQL>
==> 1번 노드로 다시 가서
alter database
add logfile thread 2
group 3 ('+DATA_DG') size 500M ,
group 4 ('+DATA_DG') size 500M
;
alter database enable public thread 2;
CREATE UNDO TABLESPACE "UNDOTBS2" DATAFILE '+DATA_DG' SIZE 500M;
/**
SQL> alter database
2 add logfile thread 2
3 group 3 ('+DATA_DG') size 500M ,
4 group 4 ('+DATA_DG') size 500M
5 ;
Database altered.
SQL> alter database enable public thread 2;
Database altered.
SQL> CREATE UNDO TABLESPACE "UNDOTBS2" DATAFILE '+DATA_DG' SIZE 500M;
**/
==> 2번노드에서 다시 기동
startup mount pfile='initDB_TEST.ora';
alter database open;
create spfile from pfile='initDB_TEST.ora';
SQL> startup mount pfile='initDB_TEST.ora';
ORACLE instance started.
Total System Global Area 327155264 bytes
Fixed Size 8896064 bytes
Variable Size 264241152 bytes
Database Buffers 50331648 bytes
Redo Buffers 3686400 bytes
Database mounted.
SQL>
SQL> alter database open;
Database altered.
SQL>
SQL> create spfile from pfile='initDB_TEST.ora';
File created.
SQL>
SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL>
SQL> startup
ORACLE instance started.
Total System Global Area 327155264 bytes
Fixed Size 8896064 bytes
Variable Size 264241152 bytes
Database Buffers 50331648 bytes
Redo Buffers 3686400 bytes
Database mounted.
Database opened.
SQL>
SQL> show parameter spfile;
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
spfile string /db/app/product/dbhome/dbs/spfileDB_TEST.ora
SQL>
SQL>
[oracle@db19c1(DB_TEST):/db/app/product/dbhome/dbs]ps -ef | grep DB_TEST
oracle 13296 1 0 22:28 ? 00:00:00 ora_pmon_DB_TEST
oracle 13298 1 0 22:28 ? 00:00:00 ora_clmn_DB_TEST
oracle 13300 1 0 22:28 ? 00:00:00 ora_psp0_DB_TEST
oracle 13304 1 0 22:28 ? 00:00:00 ora_ipc0_DB_TEST
oracle 13306 1 0 22:28 ? 00:00:00 ora_vktm_DB_TEST
==> 1번 서버
mv initDB_TEST.ora initDB_TEST1.ora
SQL> startup mount pfile='initDB_TEST1.ora';
ORACLE instance started.
Total System Global Area 327155264 bytes
Fixed Size 8896064 bytes
Variable Size 264241152 bytes
Database Buffers 50331648 bytes
Redo Buffers 3686400 bytes
Database mounted.
SQL>
SQL> alter database open;
Database altered.
SQL> create spfile from pfile='initDB_TEST1.ora';
File created.
SQL> quit
Disconnected from Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.7.0.0.0
[oracle@db19c1(DB_TEST1):/db/app/product/dbhome/dbs]
[oracle@db19c1(DB_TEST1):/db/app/product/dbhome/dbs]
[oracle@db19c1(DB_TEST1):/db/app/product/dbhome/dbs]ps -ef | grep DB_TEST
oracle 14722 1 0 22:33 ? 00:00:00 ora_pmon_DB_TEST1
oracle 14725 1 0 22:33 ? 00:00:00 ora_clmn_DB_TEST1
oracle 14728 1 0 22:33 ? 00:00:00 ora_psp0_DB_TEST1
oracle 14734 1 0 22:33 ? 00:00:00 ora_ipc0_DB_TEST1
oracle 14737 1 0 22:33 ? 00:00:00 ora_vktm_DB_TEST1
oracle 14741 1 0 22:33 ? 00:00:00 ora_gen0_DB_TEST1
oracle 14743 1 0 22:33 ? 00:00:00 ora_mman_DB_TEST1
oracle 14747 1 0 22:33 ? 00:00:00 ora_gen1_DB_TEST1
==> 2번 서버
[oracle@db19c2(DB_TEST):/db/app/product/dbhome/dbs]mv initDB_TEST.ora initDB_TEST2.ora
[oracle@db19c2(DB_TEST):/db/app/product/dbhome/dbs]export ORACLE_SID=DB_TEST2
[oracle@db19c2(DB_TEST2):/db/app/product/dbhome/dbs]sqlplus
SQL*Plus: Release 19.0.0.0.0 - Production on Sun Jul 26 22:35:32 2020
Version 19.7.0.0.0
Copyright (c) 1982, 2020, Oracle. All rights reserved.
Enter user-name: /as sysdba
Connected to an idle instance.
SQL>
SQL> startup mount pfile='initDB_TEST2.ora';
ORACLE instance started.
Total System Global Area 327155264 bytes
Fixed Size 8896064 bytes
Variable Size 264241152 bytes
Database Buffers 50331648 bytes
Redo Buffers 3686400 bytes
Database mounted.
SQL>
SQL> alter database open;
Database altered.
SQL> create spfile from pfile='initDB_TEST2.ora';
File created.
SQL>
[oracle@db19c2(DB_TEST2):/db/app/product/dbhome/dbs]ps -ef | grep DB_TEST
oracle 18771 1 0 22:36 ? 00:00:00 ora_pmon_DB_TEST2
oracle 18773 1 0 22:36 ? 00:00:00 ora_clmn_DB_TEST2
oracle 18775 1 0 22:36 ? 00:00:00 ora_psp0_DB_TEST2
oracle 18777 1 0 22:36 ? 00:00:00 ora_ipc0_DB_TEST2
oracle 18779 1 0 22:36 ? 00:00:00 ora_vktm_DB_TEST2
==> 각각노드에서 수행
create pfile from spfile;
shutdown immediate
startup
== 정상 여부 확인 완료 ===
==> OS 유저 oracle 유저에서 수행
==> db 추가 ( srvctl add database -h )
srvctl add database -db DB_TEST -oraclehome /db/app/product/dbhome -dbtype RAC
==> 인스턴스를 추가 ( srvctl add instance -h )
srvctl add instance -db DB_TEST -instance DB_TEST1 -node db19c1
srvctl add instance -db DB_TEST -instance DB_TEST2 -node db19c2
: 안해 주면..
srvctl start database -db DB_TEST 수행시
PRKO-3119 : Database DB_TEST cannot be started since it has no configured instances.
==> crsctl status res -t
~~~~~
ora.db_test.db
1 OFFLINE OFFLINE STABLE
2 OFFLINE OFFLINE STABLE
~~~~~
export ORACLE_SID=DB_TEST1
sqlplus /as sysdba
shut immediate
export ORACLE_SID=DB_TEST2
sqlplus /as sysdba
shut immediate
srvctl start database -db DB_TEST
crsctl status res -t
~~~~~~~~~
ora.db_test.db
1 OFFLINE ONLINE db19c1 Open,HOME=/db/app/pr
oduct/dbhome,STOPPIN
G
2 OFFLINE ONLINE db19c2 Open,HOME=/db/app/pr
oduct/dbhome,STOPPIN
G
~~~~~~~~~
점검
앞으로 아래 처럼 추가 내용으로 사용하시면 됩니다.
srvctl stop database -db DB_TEST
srvctl start database -db DB_TEST
alter user system identified by "패스워드";
==> pc 에서 tns 정의 (기존에 사용 되었던 리스너 포트 동일 )
접속 성공 후..
select * from gv$instance;
select * from gv$database;
DROP TABLESPACE SYS_UNDOTS INCLUDING CONTENTS;
확인 되면 끝..
srvctl stop database -db DB_TEST
srvctl start database -db DB_TEST
alter user system identified by "패스워드";
==> pc 에서 tns 정의 (기존에 사용 되었던 리스너 포트 동일 )
접속 성공 후..
select * from gv$instance;
select * from gv$database;
DROP TABLESPACE SYS_UNDOTS INCLUDING CONTENTS;
확인 되면 끝..