About DATABASE

-- 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