spfile 이 정상적으로 asm disk에 들어가야 하지만. 메뉴얼하게 db를 생성 하면 그리 되지 않습니다.
아래 절차대로 수행 해 보도록 하겠습니다.
메뉴얼하게 DB 추가 작업 - GRID 리소스에 DB 추가 작업
에 이은 추가 된 내용 이므로 사전에 위의 내용 기반으로 보시면 이해가 쉬울듯 합니다.
--- SPFILE ASM 추가 작업 하기
/***
[oracle@db19c1(DBRAC1):/db]srvctl config database -db DBRAC
Database unique name: DBRAC
Database name: DBRAC
Oracle home: /db/app/product/dbhome
Oracle user: oracle
Spfile: +DATA_DG/DBRAC/PARAMETERFILE/spfile.268.1038311291
Password file: +DATA_DG/DBRAC/PASSWORD/pwddbrac.256.1038307069
Domain:
Start options: open
Stop options: immediate
Database role: PRIMARY
Management policy: AUTOMATIC
Server pools:
Disk Groups: DATA_DG,FRA_DG
Mount point paths:
Services:
Type: RAC
Start concurrency:
Stop concurrency:
OSDBA group: dba
OSOPER group: dba
Database instances: DBRAC1,DBRAC2
Configured nodes: db19c1,db19c2
CSS critical: no
CPU count: 0
Memory target: 0
Maximum memory: 0
Default network number for database services:
Database is administrator managed
[oracle@db19c1(DBRAC1):/db]
**/
[oracle@db19c1(DBRAC1):/db]srvctl config database -db DB_TEST
Database unique name: DB_TEST
Database name:
Oracle home: /db/app/product/dbhome
Oracle user: oracle
Spfile:
Password file:
Domain:
Start options: open
Stop options: immediate
Database role: PRIMARY
Management policy: AUTOMATIC
Server pools:
Disk Groups: DATA_DG
Mount point paths:
Services:
Type: RAC
Start concurrency:
Stop concurrency:
OSDBA group: dba
OSOPER group: dba
Database instances: DB_TEST1,DB_TEST2
Configured nodes: db19c1,db19c2
CSS critical: no
CPU count: 0
Memory target: 0
Maximum memory: 0
Default network number for database services:
Database is administrator managed
[oracle@db19c1(DBRAC1):/db]
-- initDB_TEST1.ora
*.cluster_database=true
*.control_files='+DATA_DG/DB_TEST/CONTROLFILE/current.281.1046811189'#Oracle managed file
*.db_block_size=8192
*.db_create_file_dest='+DATA_DG'
*.db_name='DB_TEST'
*.db_recovery_file_dest='+FRA_DG'
*.db_recovery_file_dest_size=10240M
*.db_securefile='PREFERRED'
*.resource_manager_plan='DEFAULT_PLAN'
*.undo_management='auto'
## add 1Node, 2Node
DB_TEST1.instance_number=1
DB_TEST2.instance_number=2
DB_TEST1.thread=1
DB_TEST2.thread=2
DB_TEST1.undo_tablespace='UNDOTBS1'
DB_TEST2.undo_tablespace='UNDOTBS2'
[oracle@db19c1(DBRAC1):/db]
[oracle@db19c1(DBRAC1):/db]export ORACLE_SID=DB_TEST1
[oracle@db19c1(DB_TEST1):/db]
[oracle@db19c1(DB_TEST1):/db]
[oracle@db19c1(DB_TEST1):/db]sqlplus
SQL*Plus: Release 19.0.0.0.0 - Production on Mon Jul 27 11:51:41 2020
Version 19.7.0.0.0
Copyright (c) 1982, 2020, Oracle. All rights reserved.
Enter user-name: /as sysdba
Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.7.0.0.0
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>
SQL>
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]scp initDB_TEST1.ora oracle@db19c2:/db/app/product/dbhome/dbs
initDB_TEST1.ora 100% 534 847.5KB/s 00:00
[oracle@db19c1(DB_TEST1):/db/app/product/dbhome/dbs]
[oracle@db19c2(DB_TEST2):/db/app/product/dbhome/dbs]mv initDB_TEST1.ora initDB_TEST2.ora
mv: overwrite 'initDB_TEST2.ora'? y
[oracle@db19c2(DB_TEST2):/db/app/product/dbhome/dbs]
[oracle@db19c2(DB_TEST2):/db/app/product/dbhome/dbs]more initDB_TEST2.ora
*.cluster_database=true
*.control_files='+DATA_DG/DB_TEST/CONTROLFILE/current.281.1046811189'#Oracle managed file
*.db_block_size=8192
*.db_create_file_dest='+DATA_DG'
*.db_name='DB_TEST'
*.db_recovery_file_dest='+FRA_DG'
*.db_recovery_file_dest_size=10240M
*.db_securefile='PREFERRED'
*.resource_manager_plan='DEFAULT_PLAN'
*.undo_management='auto'
## add 1Node, 2Node
DB_TEST1.instance_number=1
DB_TEST2.instance_number=2
DB_TEST1.thread=1
DB_TEST2.thread=2
DB_TEST1.undo_tablespace='UNDOTBS1'
DB_TEST2.undo_tablespace='UNDOTBS2'
[oracle@db19c2(DB_TEST2):/db/app/product/dbhome/dbs]
[oracle@db19c2(DB_TEST2):/db/app/product/dbhome/dbs]
[oracle@db19c2(DB_TEST2):/db/app/product/dbhome/dbs]sqlplus
SQL*Plus: Release 19.0.0.0.0 - Production on Mon Jul 27 13:53:39 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_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>
SQL> alter database open;
Database altered.
SQL>
[oracle@db19c1(DB_TEST1):/db/app/product/dbhome/dbs]srvctl config database -db DB_TEST | grep Spfile
Spfile:
[oracle@db19c1(DB_TEST1):/db/app/product/dbhome/dbs]
[oracle@db19c1(DB_TEST1):/db/app/product/dbhome/dbs]sqlplus
SQL*Plus: Release 19.0.0.0.0 - Production on Mon Jul 27 13:59:26 2020
Version 19.7.0.0.0
Copyright (c) 1982, 2020, Oracle. All rights reserved.
Enter user-name: /as sysdba
Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.7.0.0.0
SQL> create spfile='+DATA_DG' from pfile='/db/app/product/dbhome/dbs/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]srvctl config database -db DB_TEST | grep Spfile
Spfile: +DATA_DG/DB_TEST/PARAMETERFILE/spfile.279.1046872779
[oracle@db19c1(DB_TEST1):/db/app/product/dbhome/dbs]
[oracle@db19c2(DB_TEST2):/db/app/product/dbhome/dbs]srvctl config database -db DB_TEST | grep Spfile
Spfile: +DATA_DG/DB_TEST/PARAMETERFILE/spfile.279.1046872779
[oracle@db19c2(DB_TEST2):/db/app/product/dbhome/dbs]
-- SPFILE 삭제 방법 ( ASM DISK 그룹에서 삭제 )
[oracle@db19c1(DB_TEST1):/db/app/product/dbhome/dbs]srvctl config database -db DB_TEST
Database unique name: DB_TEST
Database name:
Oracle home: /db/app/product/dbhome
Oracle user: oracle
Spfile: +DATA_DG/DB_TEST/PARAMETERFILE/spfile.279.1046865319
Password file:
Domain:
Start options: open
Stop options: immediate
Database role: PRIMARY
Management policy: AUTOMATIC
Server pools:
Disk Groups: DATA_DG
Mount point paths:
Services:
Type: RAC
Start concurrency:
Stop concurrency:
OSDBA group: dba
OSOPER group: dba
Database instances: DB_TEST1,DB_TEST2
Configured nodes: db19c1,db19c2
CSS critical: no
CPU count: 0
Memory target: 0
Maximum memory: 0
Default network number for database services:
Database is administrator managed
[oracle@db19c1(DB_TEST1):/db/app/product/dbhome/dbs]
[oracle@db19c1(DB_TEST1):/db/app/product/dbhome/dbs]srvctl modify database -db DB_TEST -p ''
[oracle@db19c1(DB_TEST1):/db/app/product/dbhome/dbs]
[oracle@db19c1(DB_TEST1):/db/app/product/dbhome/dbs]srvctl config database -db DB_TEST | grep Spfile
Spfile:
[oracle@db19c1(DB_TEST1):/db/app/product/dbhome/dbs]asmcmd
ASMCMD> ls -lart
State Type Rebal Name
MOUNTED EXTERN N OCRVOT_DG/
MOUNTED EXTERN N MGMT_DG/
MOUNTED EXTERN N FRA_DG/
MOUNTED EXTERN N DATA_DG/
ASMCMD> cd DATA_DG
ASMCMD> ls -lart
Type Redund Striped Time Sys Name
N DBRAC/
Y DB_TEST/
ASMCMD> cd DB_TEST
ASMCMD> ls -lart
Type Redund Striped Time Sys Name
Y CONTROLFILE/
Y DATAFILE/
Y ONLINELOG/
Y PARAMETERFILE/
Y TEMPFILE/
ASMCMD> cd PARAMETERFILE
ASMCMD> ls
spfile.279.1046865319
ASMCMD> cd ..
ASMCMD> ls -lart
Type Redund Striped Time Sys Name
Y CONTROLFILE/
Y DATAFILE/
Y ONLINELOG/
Y PARAMETERFILE/
Y TEMPFILE/
ASMCMD> rm -fr PARAMETERFILE
ASMCMD> ls -lart
Type Redund Striped Time Sys Name
Y CONTROLFILE/
Y DATAFILE/
Y ONLINELOG/
Y TEMPFILE/
ASMCMD>
/***
[oracle@db19c1(DBRAC1):/db]srvctl config database -db DBRAC
Database unique name: DBRAC
Database name: DBRAC
Oracle home: /db/app/product/dbhome
Oracle user: oracle
Spfile: +DATA_DG/DBRAC/PARAMETERFILE/spfile.268.1038311291
Password file: +DATA_DG/DBRAC/PASSWORD/pwddbrac.256.1038307069
Domain:
Start options: open
Stop options: immediate
Database role: PRIMARY
Management policy: AUTOMATIC
Server pools:
Disk Groups: DATA_DG,FRA_DG
Mount point paths:
Services:
Type: RAC
Start concurrency:
Stop concurrency:
OSDBA group: dba
OSOPER group: dba
Database instances: DBRAC1,DBRAC2
Configured nodes: db19c1,db19c2
CSS critical: no
CPU count: 0
Memory target: 0
Maximum memory: 0
Default network number for database services:
Database is administrator managed
[oracle@db19c1(DBRAC1):/db]
**/
[oracle@db19c1(DBRAC1):/db]srvctl config database -db DB_TEST
Database unique name: DB_TEST
Database name:
Oracle home: /db/app/product/dbhome
Oracle user: oracle
Spfile:
Password file:
Domain:
Start options: open
Stop options: immediate
Database role: PRIMARY
Management policy: AUTOMATIC
Server pools:
Disk Groups: DATA_DG
Mount point paths:
Services:
Type: RAC
Start concurrency:
Stop concurrency:
OSDBA group: dba
OSOPER group: dba
Database instances: DB_TEST1,DB_TEST2
Configured nodes: db19c1,db19c2
CSS critical: no
CPU count: 0
Memory target: 0
Maximum memory: 0
Default network number for database services:
Database is administrator managed
[oracle@db19c1(DBRAC1):/db]
-- initDB_TEST1.ora
*.cluster_database=true
*.control_files='+DATA_DG/DB_TEST/CONTROLFILE/current.281.1046811189'#Oracle managed file
*.db_block_size=8192
*.db_create_file_dest='+DATA_DG'
*.db_name='DB_TEST'
*.db_recovery_file_dest='+FRA_DG'
*.db_recovery_file_dest_size=10240M
*.db_securefile='PREFERRED'
*.resource_manager_plan='DEFAULT_PLAN'
*.undo_management='auto'
## add 1Node, 2Node
DB_TEST1.instance_number=1
DB_TEST2.instance_number=2
DB_TEST1.thread=1
DB_TEST2.thread=2
DB_TEST1.undo_tablespace='UNDOTBS1'
DB_TEST2.undo_tablespace='UNDOTBS2'
[oracle@db19c1(DBRAC1):/db]
[oracle@db19c1(DBRAC1):/db]export ORACLE_SID=DB_TEST1
[oracle@db19c1(DB_TEST1):/db]
[oracle@db19c1(DB_TEST1):/db]
[oracle@db19c1(DB_TEST1):/db]sqlplus
SQL*Plus: Release 19.0.0.0.0 - Production on Mon Jul 27 11:51:41 2020
Version 19.7.0.0.0
Copyright (c) 1982, 2020, Oracle. All rights reserved.
Enter user-name: /as sysdba
Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.7.0.0.0
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>
SQL>
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]scp initDB_TEST1.ora oracle@db19c2:/db/app/product/dbhome/dbs
initDB_TEST1.ora 100% 534 847.5KB/s 00:00
[oracle@db19c1(DB_TEST1):/db/app/product/dbhome/dbs]
[oracle@db19c2(DB_TEST2):/db/app/product/dbhome/dbs]mv initDB_TEST1.ora initDB_TEST2.ora
mv: overwrite 'initDB_TEST2.ora'? y
[oracle@db19c2(DB_TEST2):/db/app/product/dbhome/dbs]
[oracle@db19c2(DB_TEST2):/db/app/product/dbhome/dbs]more initDB_TEST2.ora
*.cluster_database=true
*.control_files='+DATA_DG/DB_TEST/CONTROLFILE/current.281.1046811189'#Oracle managed file
*.db_block_size=8192
*.db_create_file_dest='+DATA_DG'
*.db_name='DB_TEST'
*.db_recovery_file_dest='+FRA_DG'
*.db_recovery_file_dest_size=10240M
*.db_securefile='PREFERRED'
*.resource_manager_plan='DEFAULT_PLAN'
*.undo_management='auto'
## add 1Node, 2Node
DB_TEST1.instance_number=1
DB_TEST2.instance_number=2
DB_TEST1.thread=1
DB_TEST2.thread=2
DB_TEST1.undo_tablespace='UNDOTBS1'
DB_TEST2.undo_tablespace='UNDOTBS2'
[oracle@db19c2(DB_TEST2):/db/app/product/dbhome/dbs]
[oracle@db19c2(DB_TEST2):/db/app/product/dbhome/dbs]
[oracle@db19c2(DB_TEST2):/db/app/product/dbhome/dbs]sqlplus
SQL*Plus: Release 19.0.0.0.0 - Production on Mon Jul 27 13:53:39 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_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>
SQL> alter database open;
Database altered.
SQL>
[oracle@db19c1(DB_TEST1):/db/app/product/dbhome/dbs]srvctl config database -db DB_TEST | grep Spfile
Spfile:
[oracle@db19c1(DB_TEST1):/db/app/product/dbhome/dbs]
[oracle@db19c1(DB_TEST1):/db/app/product/dbhome/dbs]sqlplus
SQL*Plus: Release 19.0.0.0.0 - Production on Mon Jul 27 13:59:26 2020
Version 19.7.0.0.0
Copyright (c) 1982, 2020, Oracle. All rights reserved.
Enter user-name: /as sysdba
Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.7.0.0.0
SQL> create spfile='+DATA_DG' from pfile='/db/app/product/dbhome/dbs/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]srvctl config database -db DB_TEST | grep Spfile
Spfile: +DATA_DG/DB_TEST/PARAMETERFILE/spfile.279.1046872779
[oracle@db19c1(DB_TEST1):/db/app/product/dbhome/dbs]
[oracle@db19c2(DB_TEST2):/db/app/product/dbhome/dbs]srvctl config database -db DB_TEST | grep Spfile
Spfile: +DATA_DG/DB_TEST/PARAMETERFILE/spfile.279.1046872779
[oracle@db19c2(DB_TEST2):/db/app/product/dbhome/dbs]
-- SPFILE 삭제 방법 ( ASM DISK 그룹에서 삭제 )
[oracle@db19c1(DB_TEST1):/db/app/product/dbhome/dbs]srvctl config database -db DB_TEST
Database unique name: DB_TEST
Database name:
Oracle home: /db/app/product/dbhome
Oracle user: oracle
Spfile: +DATA_DG/DB_TEST/PARAMETERFILE/spfile.279.1046865319
Password file:
Domain:
Start options: open
Stop options: immediate
Database role: PRIMARY
Management policy: AUTOMATIC
Server pools:
Disk Groups: DATA_DG
Mount point paths:
Services:
Type: RAC
Start concurrency:
Stop concurrency:
OSDBA group: dba
OSOPER group: dba
Database instances: DB_TEST1,DB_TEST2
Configured nodes: db19c1,db19c2
CSS critical: no
CPU count: 0
Memory target: 0
Maximum memory: 0
Default network number for database services:
Database is administrator managed
[oracle@db19c1(DB_TEST1):/db/app/product/dbhome/dbs]
[oracle@db19c1(DB_TEST1):/db/app/product/dbhome/dbs]srvctl modify database -db DB_TEST -p ''
[oracle@db19c1(DB_TEST1):/db/app/product/dbhome/dbs]
[oracle@db19c1(DB_TEST1):/db/app/product/dbhome/dbs]srvctl config database -db DB_TEST | grep Spfile
Spfile:
[oracle@db19c1(DB_TEST1):/db/app/product/dbhome/dbs]asmcmd
ASMCMD> ls -lart
State Type Rebal Name
MOUNTED EXTERN N OCRVOT_DG/
MOUNTED EXTERN N MGMT_DG/
MOUNTED EXTERN N FRA_DG/
MOUNTED EXTERN N DATA_DG/
ASMCMD> cd DATA_DG
ASMCMD> ls -lart
Type Redund Striped Time Sys Name
N DBRAC/
Y DB_TEST/
ASMCMD> cd DB_TEST
ASMCMD> ls -lart
Type Redund Striped Time Sys Name
Y CONTROLFILE/
Y DATAFILE/
Y ONLINELOG/
Y PARAMETERFILE/
Y TEMPFILE/
ASMCMD> cd PARAMETERFILE
ASMCMD> ls
spfile.279.1046865319
ASMCMD> cd ..
ASMCMD> ls -lart
Type Redund Striped Time Sys Name
Y CONTROLFILE/
Y DATAFILE/
Y ONLINELOG/
Y PARAMETERFILE/
Y TEMPFILE/
ASMCMD> rm -fr PARAMETERFILE
ASMCMD> ls -lart
Type Redund Striped Time Sys Name
Y CONTROLFILE/
Y DATAFILE/
Y ONLINELOG/
Y TEMPFILE/
ASMCMD>
