-- 12.2.0.1 기준 rac 에서 single로 변환
SQL*Plus: Release 12.2.0.1.0 Production on Sat Jan 27 22:13:02 2024
Copyright (c) 1982, 2016, Oracle. All rights reserved.
Connected to:
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production
SQL> show parameter pfile;
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
spfile string +DATA/ARACDB/PARAMETERFILE/spf
ile.276.1136924839
SQL> create pfile from spfile;
File created.
[aracdb1:/u01/app/oracle/product/12c/db_1/dbs]> ls -ltr
합계 19272
-rw-r--r--. 1 oracle oinstall 3079 5월 15 2015 init.ora
-rw-r-----. 1 oracle oinstall 2048 5월 23 2023 orapwaracdb1
-rw-r--r--. 1 oracle oinstall 71 5월 23 2023 afiedt.buf
-rw-r-----. 1 oracle asmadmin 19709952 1월 25 16:34 snapcf_aracdb1.f
-rw-rw----. 1 oracle asmadmin 1544 1월 27 22:12 hc_aracdb1.dat
-rw-r--r--. 1 oracle asmadmin 1816 1월 27 22:13 initaracdb1.ora
[aracdb1:/u01/app/oracle/product/12c/db_1/dbs]> cat initaracdb1.ora
aracdb1.__data_transfer_cache_size=0
aracdb2.__data_transfer_cache_size=0
aracdb1.__db_cache_size=2264924160
aracdb2.__db_cache_size=2264924160
aracdb1.__inmemory_ext_roarea=0
aracdb2.__inmemory_ext_roarea=0
aracdb1.__inmemory_ext_rwarea=0
aracdb2.__inmemory_ext_rwarea=0
aracdb1.__java_pool_size=16777216
aracdb2.__java_pool_size=16777216
aracdb1.__large_pool_size=33554432
aracdb2.__large_pool_size=33554432
aracdb1.__oracle_base='/u01/app/oracle'#ORACLE_BASE set from environment
aracdb2.__oracle_base='/u01/app/oracle'#ORACLE_BASE set from environment
aracdb1.__pga_aggregate_target=1056964608
aracdb2.__pga_aggregate_target=1056964608
aracdb1.__sga_target=3154116608
aracdb2.__sga_target=3154116608
aracdb1.__shared_io_pool_size=167772160
aracdb2.__shared_io_pool_size=167772160
aracdb1.__shared_pool_size=654311424
aracdb2.__shared_pool_size=654311424
aracdb1.__streams_pool_size=0
aracdb2.__streams_pool_size=0
*.audit_file_dest='/u01/app/oracle/admin/aracdb/adump'
*.audit_trail='db'
*.cluster_database=true
*.compatible='12.2.0'
*.control_files='+DATA/ARACDB/CONTROLFILE/current.269.1136924667','+FRA/ARACDB/CONTROLFILE/current.259.1136924667'
*.db_block_size=8192
*.db_create_file_dest='+DATA'
*.db_name='aracdb'
*.db_recovery_file_dest='+FRA'
*.db_recovery_file_dest_size=10g
*.diagnostic_dest='/u01/app/oracle'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=aracdbXDB)'
family:dw_helper.instance_mode='read-only'
aracdb1.instance_number=1
aracdb2.instance_number=2
*.local_listener='-oraagent-dummy-'
*.log_archive_format='%t_%s_%r.dbf'
*.nls_language='KOREAN'
*.nls_territory='KOREA'
*.open_cursors=300
*.pga_aggregate_target=1000m
*.processes=320
*.remote_login_passwordfile='exclusive'
*.sga_target=2998m
aracdb2.thread=2
aracdb1.thread=1
aracdb1.undo_tablespace='UNDOTBS1'
aracdb2.undo_tablespace='UNDOTBS2'
[root@arac1 ~]# crsctl stat res -t
--------------------------------------------------------------------------------
Name Target State Server State details
--------------------------------------------------------------------------------
Local Resources
--------------------------------------------------------------------------------
ora.ASMNET1LSNR_ASM.lsnr
ONLINE ONLINE arac1 STABLE
ONLINE ONLINE arac2 STABLE
ora.CRS.dg
ONLINE ONLINE arac1 STABLE
ONLINE ONLINE arac2 STABLE
ora.DATA.dg
ONLINE ONLINE arac1 STABLE
ONLINE ONLINE arac2 STABLE
ora.FRA.dg
ONLINE ONLINE arac1 STABLE
ONLINE ONLINE arac2 STABLE
ora.LISTENER.lsnr
ONLINE ONLINE arac1 STABLE
ONLINE ONLINE arac2 STABLE
ora.MGMT.dg
ONLINE ONLINE arac1 STABLE
ONLINE ONLINE arac2 STABLE
ora.chad
ONLINE ONLINE arac1 STABLE
ONLINE ONLINE arac2 STABLE
ora.net1.network
ONLINE ONLINE arac1 STABLE
ONLINE ONLINE arac2 STABLE
ora.ons
ONLINE ONLINE arac1 STABLE
ONLINE ONLINE arac2 STABLE
ora.proxy_advm
OFFLINE OFFLINE arac1 STABLE
OFFLINE OFFLINE arac2 STABLE
--------------------------------------------------------------------------------
Cluster Resources
--------------------------------------------------------------------------------
ora.LISTENER_SCAN1.lsnr
1 ONLINE ONLINE arac1 STABLE
ora.MGMTLSNR
1 ONLINE ONLINE arac1 169.254.51.97 192.16
8.0.111,STABLE
ora.arac1.vip
1 ONLINE ONLINE arac1 STABLE
ora.arac2.vip
1 ONLINE ONLINE arac2 STABLE
ora.aracdb.db
1 ONLINE OFFLINE arac1 Instance Shutdown,ST
ARTING
2 ONLINE OFFLINE arac2 Instance Shutdown,ST
ARTING
ora.asm
1 ONLINE ONLINE arac1 Started,STABLE
2 ONLINE ONLINE arac2 Started,STABLE
3 OFFLINE OFFLINE STABLE
ora.cvu
1 ONLINE ONLINE arac1 STABLE
ora.mgmtdb
1 ONLINE ONLINE arac1 Open,STABLE
ora.qosmserver
1 ONLINE ONLINE arac1 STABLE
ora.scan1.vip
1 ONLINE ONLINE arac1 STABLE
--------------------------------------------------------------------------------
1. Shutdown all the instance in RAC environment.
[root@arac1 ~]# srvctl stop database -d aracdb
[root@arac1 ~]# crsctl stat res -t
--------------------------------------------------------------------------------
Name Target State Server State details
--------------------------------------------------------------------------------
Local Resources
--------------------------------------------------------------------------------
ora.ASMNET1LSNR_ASM.lsnr
ONLINE ONLINE arac1 STABLE
ONLINE ONLINE arac2 STABLE
ora.CRS.dg
ONLINE ONLINE arac1 STABLE
ONLINE ONLINE arac2 STABLE
ora.DATA.dg
ONLINE ONLINE arac1 STABLE
ONLINE ONLINE arac2 STABLE
ora.FRA.dg
ONLINE ONLINE arac1 STABLE
ONLINE ONLINE arac2 STABLE
ora.LISTENER.lsnr
ONLINE ONLINE arac1 STABLE
ONLINE ONLINE arac2 STABLE
ora.MGMT.dg
ONLINE ONLINE arac1 STABLE
ONLINE ONLINE arac2 STABLE
ora.chad
ONLINE ONLINE arac1 STABLE
ONLINE ONLINE arac2 STABLE
ora.net1.network
ONLINE ONLINE arac1 STABLE
ONLINE ONLINE arac2 STABLE
ora.ons
ONLINE ONLINE arac1 STABLE
ONLINE ONLINE arac2 STABLE
ora.proxy_advm
OFFLINE OFFLINE arac1 STABLE
OFFLINE OFFLINE arac2 STABLE
--------------------------------------------------------------------------------
Cluster Resources
--------------------------------------------------------------------------------
ora.LISTENER_SCAN1.lsnr
1 ONLINE ONLINE arac1 STABLE
ora.MGMTLSNR
1 ONLINE ONLINE arac1 169.254.51.97 192.16
8.0.111,STABLE
ora.arac1.vip
1 ONLINE ONLINE arac1 STABLE
ora.arac2.vip
1 ONLINE ONLINE arac2 STABLE
ora.aracdb.db
1 OFFLINE OFFLINE Instance Shutdown,ST
ABLE
2 OFFLINE OFFLINE Instance Shutdown,ST
ABLE
ora.asm
1 ONLINE ONLINE arac1 Started,STABLE
2 ONLINE ONLINE arac2 Started,STABLE
3 OFFLINE OFFLINE STABLE
ora.cvu
1 ONLINE ONLINE arac1 STABLE
ora.mgmtdb
1 ONLINE ONLINE arac1 Open,STABLE
ora.qosmserver
1 ONLINE ONLINE arac1 STABLE
ora.scan1.vip
1 ONLINE ONLINE arac1 STABLE
--------------------------------------------------------------------------------
2. Shutdown all the listeners.
[root@arac1 ~]# srvctl stop listener -n arac1
[root@arac1 ~]# srvctl stop listener -n arac2
[aracdb1:/u01/app/oracle/product/12c/db_1/dbs]> srvctl stop scan_listener
[aracdb1:/u01/app/oracle/product/12c/db_1/dbs]> crsctl stat res -t
--------------------------------------------------------------------------------
Name Target State Server State details
--------------------------------------------------------------------------------
Local Resources
--------------------------------------------------------------------------------
ora.ASMNET1LSNR_ASM.lsnr
ONLINE ONLINE arac1 STABLE
ONLINE ONLINE arac2 STABLE
ora.CRS.dg
ONLINE ONLINE arac1 STABLE
ONLINE ONLINE arac2 STABLE
ora.DATA.dg
ONLINE ONLINE arac1 STABLE
ONLINE ONLINE arac2 STABLE
ora.FRA.dg
ONLINE ONLINE arac1 STABLE
ONLINE ONLINE arac2 STABLE
ora.LISTENER.lsnr
OFFLINE OFFLINE arac1 STABLE
OFFLINE OFFLINE arac2 STABLE
ora.MGMT.dg
ONLINE ONLINE arac1 STABLE
ONLINE ONLINE arac2 STABLE
ora.chad
ONLINE ONLINE arac1 STABLE
ONLINE ONLINE arac2 STABLE
ora.net1.network
ONLINE ONLINE arac1 STABLE
ONLINE ONLINE arac2 STABLE
ora.ons
ONLINE ONLINE arac1 STABLE
ONLINE ONLINE arac2 STABLE
ora.proxy_advm
OFFLINE OFFLINE arac1 STABLE
OFFLINE OFFLINE arac2 STABLE
--------------------------------------------------------------------------------
Cluster Resources
--------------------------------------------------------------------------------
ora.LISTENER_SCAN1.lsnr
1 OFFLINE OFFLINE STABLE
ora.MGMTLSNR
1 ONLINE ONLINE arac1 169.254.51.97 192.16
8.0.111,STABLE
ora.arac1.vip
1 ONLINE ONLINE arac1 STABLE
ora.arac2.vip
1 ONLINE ONLINE arac2 STABLE
ora.aracdb.db
1 OFFLINE OFFLINE Instance Shutdown,ST
ABLE
2 OFFLINE OFFLINE Instance Shutdown,ST
ABLE
ora.asm
1 ONLINE ONLINE arac1 Started,STABLE
2 ONLINE ONLINE arac2 Started,STABLE
3 OFFLINE OFFLINE STABLE
ora.cvu
1 ONLINE ONLINE arac1 STABLE
ora.mgmtdb
1 ONLINE ONLINE arac1 Open,STABLE
ora.qosmserver
1 ONLINE ONLINE arac1 STABLE
ora.scan1.vip
1 ONLINE ONLINE arac1 STABLE
--------------------------------------------------------------------------------
3. Relink Oracle executable with rac_off option.
$ make -f ins_rdbms.mk rac_off
$ make -f ins_rdbms.mk ioracle
[aracdb1:/u01/app/oracle/product/12c/db_1/rdbms/lib]> make -f ins_rdbms.mk rac_off
rm -f /u01/app/oracle/product/12c/db_1/lib/libskgxp12.so
cp /u01/app/oracle/product/12c/db_1/lib//libskgxpg.so /u01/app/oracle/product/12c/db_1/lib/libskgxp12.so
rm -f /u01/app/oracle/product/12c/db_1/lib/libskgxn2.so
cp /u01/app/oracle/product/12c/db_1/lib//libskgxns.so \
/u01/app/oracle/product/12c/db_1/lib/libskgxn2.so
/usr/bin/ar d /u01/app/oracle/product/12c/db_1/rdbms/lib/libknlopt.a kcsm.o
/usr/bin/ar cr /u01/app/oracle/product/12c/db_1/rdbms/lib/libknlopt.a /u01/app/oracle/product/12c/db_1/rdbms/lib/ksnkcs.o
[aracdb1:/u01/app/oracle/product/12c/db_1/rdbms/lib]> make -f ins_rdbms.mk ioracle
chmod 755 /u01/app/oracle/product/12c/db_1/bin
- Linking Oracle
rm -f /u01/app/oracle/product/12c/db_1/rdbms/lib/oracle
/u01/app/oracle/product/12c/db_1/bin/orald -o /u01/app/oracle/product/12c/db_1/rdbms/lib/oracle -m64 -z noexecstack -Wl,--disable-new-dtags -L/u01/app/oracle/product/12c/db_1/rdbms/lib/ -L/u01/app/oracle/product/12c/db_1/lib/ -L/u01/app/oracle/product/12c/db_1/lib/stubs/ -Wl,-E /u01/app/oracle/product/12c/db_1/rdbms/lib/opimai.o /u01/app/oracle/product/12c/db_1/rdbms/lib/ssoraed.o /u01/app/oracle/product/12c/db_1/rdbms/lib/ttcsoi.o -Wl,--whole-archive -lperfsrv12 -Wl,--no-whole-archive /u01/app/oracle/product/12c/db_1/lib/nautab.o /u01/app/oracle/product/12c/db_1/lib/naeet.o /u01/app/oracle/product/12c/db_1/lib/naect.o /u01/app/oracle/product/12c/db_1/lib/naedhs.o /u01/app/oracle/product/12c/db_1/rdbms/lib/config.o -ldmext -lserver12 -lodm12 -lofs -lcell12 -lnnet12 -lskgxp12 -lsnls12 -lnls12 -lcore12 -lsnls12 -lnls12 -lcore12 -lsnls12 -lnls12 -lxml12 -lcore12 -lunls12 -lsnls12 -lnls12 -lcore12 -lnls12 -lclient12 -lvsn12 -lcommon12 -lgeneric12 -lknlopt `if /usr/bin/ar tv /u01/app/oracle/product/12c/db_1/rdbms/lib/libknlopt.a | grep xsyeolap.o > /dev/null 2>&1 ; then echo "-loraolap12" ; fi` -lskjcx12 -lslax12 -lpls12 -lrt -lplp12 -ldmext -lserver12 -lclient12 -lvsn12 -lcommon12 -lgeneric12 `if [ -f /u01/app/oracle/product/12c/db_1/lib/libavserver12.a ] ; then echo "-lavserver12" ; else echo "-lavstub12"; fi` `if [ -f /u01/app/oracle/product/12c/db_1/lib/libavclient12.a ] ; then echo "-lavclient12" ; fi` -lknlopt -lslax12 -lpls12 -lrt -lplp12 -ljavavm12 -lserver12 -lwwg `cat /u01/app/oracle/product/12c/db_1/lib/ldflags` -lncrypt12 -lnsgr12 -lnzjs12 -ln12 -lnl12 -lngsmshd12 -lnro12 `cat /u01/app/oracle/product/12c/db_1/lib/ldflags` -lncrypt12 -lnsgr12 -lnzjs12 -ln12 -lnl12 -lngsmshd12 -lnnzst12 -lzt12 -lztkg12 -lmm -lsnls12 -lnls12 -lcore12 -lsnls12 -lnls12 -lcore12 -lsnls12 -lnls12 -lxml12 -lcore12 -lunls12 -lsnls12 -lnls12 -lcore12 -lnls12 -lztkg12 `cat /u01/app/oracle/product/12c/db_1/lib/ldflags` -lncrypt12 -lnsgr12 -lnzjs12 -ln12 -lnl12 -lngsmshd12 -lnro12 `cat /u01/app/oracle/product/12c/db_1/lib/ldflags` -lncrypt12 -lnsgr12 -lnzjs12 -ln12 -lnl12 -lngsmshd12 -lnnzst12 -lzt12 -lztkg12 -lsnls12 -lnls12 -lcore12 -lsnls12 -lnls12 -lcore12 -lsnls12 -lnls12 -lxml12 -lcore12 -lunls12 -lsnls12 -lnls12 -lcore12 -lnls12 `if /usr/bin/ar tv /u01/app/oracle/product/12c/db_1/rdbms/lib/libknlopt.a | grep "kxmnsd.o" > /dev/null 2>&1 ; then echo " " ; else echo "-lordsdo12 -lserver12"; fi` -L/u01/app/oracle/product/12c/db_1/ctx/lib/ -lctxc12 -lctx12 -lzx12 -lgx12 -lctx12 -lzx12 -lgx12 -lordimt12 -lclsra12 -ldbcfg12 -lhasgen12 -lskgxn2 -lnnzst12 -lzt12 -lxml12 -lgeneric12 -locr12 -locrb12 -locrutl12 -lhasgen12 -lskgxn2 -lnnzst12 -lzt12 -lxml12 -lgeneric12 -lgeneric12 -lorazip -loraz -llzopro5 -lorabz2 -lipp_z -lipp_bz2 -lippdcemerged -lippsemerged -lippdcmerged -lippsmerged -lippcore -lippcpemerged -lippcpmerged -lsnls12 -lnls12 -lcore12 -lsnls12 -lnls12 -lcore12 -lsnls12 -lnls12 -lxml12 -lcore12 -lunls12 -lsnls12 -lnls12 -lcore12 -lnls12 -lsnls12 -lunls12 -lsnls12 -lnls12 -lcore12 -lsnls12 -lnls12 -lcore12 -lsnls12 -lnls12 -lxml12 -lcore12 -lunls12 -lsnls12 -lnls12 -lcore12 -lnls12 -lasmclnt12 -lcommon12 -lcore12 -laio -lons -lfthread12 `cat /u01/app/oracle/product/12c/db_1/lib/sysliblist` -Wl,-rpath,/u01/app/oracle/product/12c/db_1/lib -lm `cat /u01/app/oracle/product/12c/db_1/lib/sysliblist` -ldl -lm -L/u01/app/oracle/product/12c/db_1/lib `test -x /usr/bin/hugeedit -a -r /usr/lib64/libhugetlbfs.so && test -r /u01/app/oracle/product/12c/db_1/rdbms/lib/shugetlbfs.o && echo -Wl,-zcommon-page-size=2097152 -Wl,-zmax-page-size=2097152 -lhugetlbfs`
test ! -f /u01/app/oracle/product/12c/db_1/bin/oracle || (\
mv -f /u01/app/oracle/product/12c/db_1/bin/oracle /u01/app/oracle/product/12c/db_1/bin/oracleO &&\
chmod 600 /u01/app/oracle/product/12c/db_1/bin/oracleO )
mv /u01/app/oracle/product/12c/db_1/rdbms/lib/oracle /u01/app/oracle/product/12c/db_1/bin/oracle
chmod 6751 /u01/app/oracle/product/12c/db_1/bin/oracle
[aracdb1:/u01/app/oracle/product/12c/db_1/rdbms/lib]>
4. Remove all the parameters from init.ora which points to second instance and it is on other node.
[aracdb1:/u01/app/oracle/product/12c/db_1/dbs]> cat initaracdb1.ora
*.audit_file_dest='/u01/app/oracle/admin/aracdb/adump'
*.audit_trail='db'
*.cluster_database=true
*.compatible='12.2.0'
*.control_files='+DATA/ARACDB/CONTROLFILE/current.269.1136924667','+FRA/ARACDB/CONTROLFILE/current.259.1136924667'
*.db_block_size=8192
*.db_create_file_dest='+DATA'
*.db_name='aracdb'
*.db_recovery_file_dest='+FRA'
*.db_recovery_file_dest_size=10g
*.diagnostic_dest='/u01/app/oracle'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=aracdbXDB)'
family:dw_helper.instance_mode='read-only'
aracdb1.instance_number=1
*.local_listener='-oraagent-dummy-'
*.log_archive_format='%t_%s_%r.dbf'
*.nls_language='KOREAN'
*.nls_territory='KOREA'
*.open_cursors=300
*.pga_aggregate_target=1000m
*.processes=320
*.remote_login_passwordfile='exclusive'
*.sga_target=2998m
aracdb1.thread=1
aracdb1.undo_tablespace='UNDOTBS1'
5. Remove cluster_database parameter
-- 주석 처리
#*.cluster_database=true
6. Remove undo_tablespace parameter which points to second instance
-- 주석 처리
#aracdb1.undo_tablespace='UNDOTBS2'
7. Start-up the database in mount stage and execute..
SQL> startup pfile='/u01/app/oracle/product/12c/db_1/dbs/initaracdb1.ora'
ORACLE instance started.
Total System Global Area 3154116608 bytes
Fixed Size 8625656 bytes
Variable Size 788529672 bytes
Database Buffers 2348810240 bytes
Redo Buffers 8151040 bytes
Database mounted.
Database opened.
alter database disable thread <thread of other instance>
SQL> select INSTANCE_NUMBER,INSTANCE_NAME,THREAD#,STATUS from v$instance;
INSTANCE_NUMBER INSTANCE_NAME THREAD# STATUS
--------------- ---------------- ---------- ------------
1 aracdb 1 OPEN
파라미터 파일 인식
SQL> create spfile from pfile;
-- +data에 spfilearacdb1.ora 생긴다
ASMCMD> pwd
+data
ASMCMD> ls -al
Type Redund Striped Time Sys Name
Y ASM/
N aracdb/
PARAMETERFILE UNPROT COARSE JAN 29 14:00:00 N spfilearacdb1.ora => +DATA/aracdb/PARAMETERFILE/spfile.278.1159539453
mv initaracdb1.ora initaracdb1.ora.old
vi initaracdb1.ora
spfile='+DATA/aracdb/PARAMETERFILE/spfile.278.1159539453'
SQL> startup
ORACLE instance started.
Total System Global Area 3154116608 bytes
Fixed Size 8625656 bytesvi initaracdb1.ora
Variable Size 788529672 bytes
Database Buffers 2348810240 bytes
Redo Buffers 8151040 bytes
Database mounted.
Database opened.
SQL> show parameter pfile
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
spfile string +DATA/aracdb/PARAMETERFILE/spf
ile.278.1159539453
