Database Sync

전체 글

OCI DBCS Oracle Database 19c SE2 Transparent Data Encryption(TDE) 2022.10.04
Oracle Linux 8에 xorg-x11-apps Package 설치 하기 2022.09.26
Oracle Restart RMAN 백업 복구 2022.09.19
Oracle Restart Log 경로 변경 하기 2022.09.13
전통적인 MOS 화면과 OCI MOS화면 2022.09.05

OCI DBCS Oracle Database 19c SE2 Transparent Data Encryption(TDE)

2022. 10. 4. 09:00

일반적인 On-Premise환경에서 Oracle DB Standard Edition은 Transparent Data Encryption(TDE)를 지원 하지 않는다.
그러나 OCI DBCS환경에서는 Oracle DB Standard Edition에도 TDE를 지원 한다.

TDE 설정 확인

[oracle@one19c admin]$ pwd
/u01/app/oracle/product/19.0.0.0/dbhome_1/network/admin
[oracle@one19c admin]$ ls
samples  shrept.lst  sqlnet.ora  tnsnames.ora
[oracle@one19c admin]$ more sqlnet.ora
# ENCRYPTION_WALLET_LOCATION=(SOURCE=(METHOD=FILE)(METHOD_DATA=(DIRECTORY=/opt/oracle/dcs/commonstore/wallets/tde/$ORACLE_UNQNAME)))
ENCRYPTION_WALLET_LOCATION=(SOURCE=(METHOD=FILE)(METHOD_DATA=(DIRECTORY=/opt/oracle/dcs/commonstore/wallets/$ORACLE_UNQNAME/tde)))
SQLNET.ENCRYPTION_SERVER=REQUIRED
SQLNET.CRYPTO_CHECKSUM_SERVER=REQUIRED
SQLNET.ENCRYPTION_TYPES_SERVER=(AES256,AES192,AES128)
SQLNET.CRYPTO_CHECKSUM_TYPES_SERVER=(SHA1)
SQLNET.ENCRYPTION_CLIENT=REQUIRED
SQLNET.CRYPTO_CHECKSUM_CLIENT=REQUIRED
SQLNET.ENCRYPTION_TYPES_CLIENT=(AES256,AES192,AES128)
SQLNET.CRYPTO_CHECKSUM_TYPES_CLIENT=(SHA1)
SQLNET.EXPIRE_TIME=10
[oracle@one19c admin]$
[oracle@one19c ~]$ sqlplus "/as sysdba"
SQL*Plus: Release 19.0.0.0.0 - Production on Thu Sep 22 16:15:52 2022
Version 19.16.0.0.0
Copyright (c) 1982, 2022, Oracle.  All rights reserved.
Connected to:
Oracle Database 19c Standard Edition 2 Release 19.0.0.0.0 - Production Version 19.16.0.0.0
SQL> select banner from v$version;
BANNER
--------------------------------------------------------------------------------
Oracle Database 19c Standard Edition 2 Release 19.0.0.0.0 - Production
SQL> select PARAMETER, VALUE from v$option where parameter='Transparent Data Encryption';
PARAMETER                                                        VALUE
---------------------------------------------------------------- ----------------------------------------------------------------
Transparent Data Encryption                                      FALSE
SQL>
SQL> show pdbs

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         2 PDB$SEED                       READ ONLY  NO
         3 ONE19C_PDB1                    READ WRITE NO
         4 SEPDB1                         READ WRITE NO
SQL>
SQL>
SQL> show con_name

CON_NAME
------------------------------
CDB$ROOT
SQL>
SQL> set linesize 200
SQL> col WRL_TYPE for a10
SQL> col WRL_PARAMETER for a60
SQL> select * from v$encryption_wallet;
WRL_TYPE   WRL_PARAMETER                                                STATUS                         WALLET_TYPE          WALLET_OR KEYSTORE FULLY_BAC     CON_ID
---------- ------------------------------------------------------------ ------------------------------ -------------------- --------- -------- --------- ----------
FILE       /opt/oracle/dcs/commonstore/wallets/one19c_icn1s3/tde/       OPEN                           AUTOLOGIN            SINGLE    NONE     NO                 1
FILE                                                                    OPEN                           AUTOLOGIN            SINGLE    UNITED   NO                 2
FILE                                                                    OPEN                           AUTOLOGIN            SINGLE    UNITED   NO                 3
FILE                                                                    OPEN                           AUTOLOGIN            SINGLE    UNITED   NO                 4
SQL>
SQL> select TABLESPACE_NAME, ENCRYPTED from dba_tablespaces;

TABLESPACE_NAME                ENC
------------------------------ ---
SYSTEM                         NO
SYSAUX                         NO
UNDOTBS1                       NO
TEMP                           NO
USERS                          YES
SQL>

암호화 Tablespace 생성

[oracle@one19c ~]$ sqlplus "/as sysdba"
SQL*Plus: Release 19.0.0.0.0 - Production on Thu Sep 22 16:15:52 2022
Version 19.16.0.0.0
Copyright (c) 1982, 2022, Oracle.  All rights reserved.
Connected to:
Oracle Database 19c Standard Edition 2 Release 19.0.0.0.0 - Production Version 19.16.0.0.0
SQL> alter session set container=SEPDB1;
SQL> show pdbs
    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         4 SEPDB1                         READ WRITE NO
SQL>
SQL> show con_name
CON_NAME
------------------------------
SEPDB1
SQL>
SQL> select banner from v$version;
BANNER
--------------------------------------------------------------------------------
Oracle Database 19c Standard Edition 2 Release 19.0.0.0.0 - Production

SQL> select PARAMETER, VALUE from v$option where parameter='Transparent Data Encryption';
PARAMETER                                                        VALUE
---------------------------------------------------------------- ----------------------------------------------------------------
Transparent Data Encryption                                      FALSE
SQL>
SQL> set linesize 200
SQL> col WRL_TYPE for a10
SQL> col WRL_PARAMETER for a60
SQL> SELECT * FROM V$ENCRYPTION_WALLET;
WRL_TYPE   WRL_PARAMETER                                                STATUS                         WALLET_TYPE          WALLET_OR KEYSTORE FULLY_BAC     CON_ID
---------- ------------------------------------------------------------ ------------------------------ -------------------- --------- -------- --------- ----------
FILE                                                                    OPEN                           AUTOLOGIN            SINGLE    UNITED   NO                 4
SQL>
SQL> select TABLESPACE_NAME, ENCRYPTED from dba_tablespaces;
TABLESPACE_NAME                ENC
------------------------------ ---
SYSTEM                         YES
SYSAUX                         YES
UNDOTBS1                       YES
TEMP                           YES
USERS                          YES
SQL>
SQL> SELECT VT.NAME, VET.ENCRYPTIONALG FROM V$TABLESPACE VT, V$ENCRYPTED_TABLESPACES VET WHERE VT.TS#=VET.TS#;
NAME                           ENCRYPT
------------------------------ -------
SYSTEM                         AES128
SYSAUX                         AES128
UNDOTBS1                       AES128
TEMP                           AES128
USERS                          AES128
SQL>
SQL> CREATE TABLESPACE ENC_TS_AES256 DATAFILE SIZE 1G AUTOEXTEND ON NEXT 64K ENCRYPTION USING 'AES256' DEFAULT STORAGE(ENCRYPT);
Tablespace created.
SQL> CREATE TABLESPACE ENC_TS_DEFAULT DATAFILE SIZE 1G AUTOEXTEND ON NEXT 64K ENCRYPTION DEFAULT STORAGE(ENCRYPT);
Tablespace created.
SQL> CREATE TABLESPACE ENC_TS_3DES168 DATAFILE SIZE 1G AUTOEXTEND ON NEXT 64K ENCRYPTION USING '3DES168' DEFAULT STORAGE(ENCRYPT);
Tablespace created.
SQL> CREATE TABLESPACE ENC_TS_AES192 DATAFILE SIZE 1G AUTOEXTEND ON NEXT 64K ENCRYPTION USING 'AES192' DEFAULT STORAGE(ENCRYPT);
Tablespace created.
SQL> CREATE TABLESPACE ENC_TS_ARIA192 DATAFILE SIZE 1G AUTOEXTEND ON NEXT 64K ENCRYPTION USING 'ARIA128' DEFAULT STORAGE(ENCRYPT);
Tablespace created.
SQL> CREATE TABLESPACE ENC_TS_ARIA256 DATAFILE SIZE 1G AUTOEXTEND ON NEXT 64K ENCRYPTION USING 'ARIA192' DEFAULT STORAGE(ENCRYPT);
Tablespace created.
SQL> CREATE TABLESPACE ENC_TS_SEED128 DATAFILE SIZE 1G AUTOEXTEND ON NEXT 64K ENCRYPTION USING 'SEED128' DEFAULT STORAGE(ENCRYPT);
Tablespace created.
SQL> CREATE TABLESPACE ENC_TS_GOST256 DATAFILE SIZE 1G AUTOEXTEND ON NEXT 64K ENCRYPTION USING 'GOST256' DEFAULT STORAGE(ENCRYPT);
Tablespace created.
SQL>
SQL> select TABLESPACE_NAME, ENCRYPTED from dba_tablespaces;
TABLESPACE_NAME                ENC
------------------------------ ---
SYSTEM                         YES
SYSAUX                         YES
UNDOTBS1                       YES
TEMP                           YES
USERS                          YES
ENC_TS_AES256                  YES
ENC_TS_DEFAULT                 YES
ENC_TS_3DES168                 YES
ENC_TS_AES192                  YES
ENC_TS_ARIA192                 YES
ENC_TS_ARIA256                 YES
ENC_TS_SEED128                 YES
ENC_TS_GOST256                 YES
13 rows selected.
SQL>
SQL> SELECT VT.NAME, VET.ENCRYPTIONALG FROM V$TABLESPACE VT, V$ENCRYPTED_TABLESPACES VET WHERE VT.TS#=VET.TS#;
NAME                           ENCRYPT
------------------------------ -------
SYSTEM                         AES128
SYSAUX                         AES128
UNDOTBS1                       AES128
TEMP                           AES128
USERS                          AES128
ENC_TS_AES256                  AES256
ENC_TS_DEFAULT                 AES128
ENC_TS_3DES168                 3DES168
ENC_TS_AES192                  AES192
ENC_TS_ARIA192                 ARIA128
ENC_TS_ARIA256                 ARIA192
ENC_TS_SEED128                 SEED128
ENC_TS_GOST256                 GOST256
SQL> exit
[oracle@one19c ~]$

Oracle Linux 8에 xorg-x11-apps Package 설치 하기

2022. 9. 26. 09:00

현 상태 확인

[root@ol8-19c-sinlge ~]# id
uid=0(root) gid=0(root) groups=0(root) context=unconfined_u:unconfined_r:unconfined_t:s0-s0:c0.c1023
[root@ol8-19c-sinlge ~]# more /etc/oracle-release
Oracle Linux Server release 8.6
[root@ol8-19c-sinlge ~]#
[root@ol8-19c-sinlge ~]# uname -a
Linux ol8-19c-sinlge.localdomain 5.4.17-2136.309.5.el8uek.x86_64 #2 SMP Sat Jul 16 13:06:22 PDT 2022 x86_64 x86_64 x86_64 GNU/Linux
[root@ol8-19c-sinlge ~]#
[root@ol8-19c-sinlge ~]# dnf install xorg-x11-apps
Last metadata expiration check: 0:03:23 ago on Mon 29 Aug 2022 04:03:27 PM KST.
No match for argument: xorg-x11-apps
Error: Unable to find a match: xorg-x11-apps
[root@ol8-19c-sinlge ~]#

xorg-x11-apps Package 설치 하기

[root@ol8-19c-sinlge ~]# dnf config-manager --enable ol8_codeready_builder
[root@ol8-19c-sinlge ~]# dnf install xorg-x11-apps
Oracle Linux 8 BaseOS Latest (x86_64)                                                                                                                                         6.4 kB/s | 3.6 kB     00:00
Oracle Linux 8 Application Stream (x86_64)                                                                                                                                    7.0 kB/s | 3.9 kB     00:00
Oracle Linux 8 CodeReady Builder (x86_64) - Unsupported                                                                                                                       2.4 MB/s | 6.5 MB     00:02
Dependencies resolved.
==============================================================================================================================================================================================================
 Package                                               Architecture                             Version                                         Repository                                               Size
==============================================================================================================================================================================================================
Installing:
 xorg-x11-apps                                         x86_64                                   7.7-21.el8                                      ol8_codeready_builder                                   334 k
Installing dependencies:
 xorg-x11-fonts-misc                                   noarch                                   7.5-19.el8                                      ol8_appstream                                           5.8 M
 xorg-x11-xbitmaps                                     noarch                                   1.1.1-13.el8                                    ol8_appstream                                            42 k

Transaction Summary
==============================================================================================================================================================================================================
Install  3 Packages

Total download size: 6.1 M
Installed size: 8.0 M
Is this ok [y/N]: y
Downloading Packages:
(1/3): xorg-x11-xbitmaps-1.1.1-13.el8.noarch.rpm                                                                                                                               53 kB/s |  42 kB     00:00
(2/3): xorg-x11-apps-7.7-21.el8.x86_64.rpm                                                                                                                                    235 kB/s | 334 kB     00:01
(3/3): xorg-x11-fonts-misc-7.5-19.el8.noarch.rpm                                                                                                                              2.3 MB/s | 5.8 MB     00:02
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Total                                                                                                                                                                         2.5 MB/s | 6.1 MB     00:02
Running transaction check
Transaction check succeeded.
Running transaction test
Transaction test succeeded.
Running transaction
  Preparing        :                                                                                                                                                                                      1/1
  Installing       : xorg-x11-xbitmaps-1.1.1-13.el8.noarch                                                                                                                                                1/3
  Installing       : xorg-x11-fonts-misc-7.5-19.el8.noarch                                                                                                                                                2/3
  Running scriptlet: xorg-x11-fonts-misc-7.5-19.el8.noarch                                                                                                                                                2/3
  Installing       : xorg-x11-apps-7.7-21.el8.x86_64                                                                                                                                                      3/3
  Running scriptlet: xorg-x11-apps-7.7-21.el8.x86_64                                                                                                                                                      3/3
/sbin/ldconfig: /etc/ld.so.conf.d/kernel-5.4.17-2102.201.3.el8uek.x86_64.conf:6: hwcap directive ignored

  Verifying        : xorg-x11-fonts-misc-7.5-19.el8.noarch                                                                                                                                                1/3
  Verifying        : xorg-x11-xbitmaps-1.1.1-13.el8.noarch                                                                                                                                                2/3
  Verifying        : xorg-x11-apps-7.7-21.el8.x86_64                                                                                                                                                      3/3

Installed:
  xorg-x11-apps-7.7-21.el8.x86_64                                 xorg-x11-fonts-misc-7.5-19.el8.noarch                                 xorg-x11-xbitmaps-1.1.1-13.el8.noarch

Complete!
[root@ol8-19c-sinlge ~]#
[root@ol8-19c-sinlge ~]# which xclock
/usr/bin/xclock
[root@ol8-19c-sinlge ~]#

How To Install "xorg-x11-apps" Package in Oracle Linux 8 (문서 ID 2849317.1)

Oracle Restart RMAN 백업 복구

2022. 9. 19. 09:00

Oracle RMAN 백업 스크리립트 및 백업

Full Backup 스크립트

[oracle@onerac DATASYNCXML]$ pwd
/home/oracle/DATASYNCXML
[oracle@onerac DATASYNCXML]$ more rman_full.rman
RUN {
ALLOCATE CHANNEL ch00 DEVICE TYPE DISK;
ALLOCATE CHANNEL ch01 DEVICE TYPE DISK;
BACKUP
    SKIP INACCESSIBLE
    TAG full_level0
    FILESPERSET 5
    FORMAT '/backup/rman/full_%s_%p_%t'
    INCREMENTAL LEVEL 0
DATABASE;
sql 'alter system archive log current';
RELEASE CHANNEL ch00;
RELEASE CHANNEL ch01;

ALLOCATE CHANNEL ch00 DEVICE TYPE DISK;
ALLOCATE CHANNEL ch01 DEVICE TYPE DISK;
BACKUP
    filesperset 20
    FORMAT '/backup/rman/archive_%s_%p_%t'
    ARCHIVELOG ALL DELETE INPUT;
RELEASE CHANNEL ch00;
RELEASE CHANNEL ch01;

ALLOCATE CHANNEL ch00 DEVICE TYPE DISK;
BACKUP
    FORMAT '/backup/rman/control_%s_%p_%t'
    CURRENT CONTROLFILE;
RELEASE CHANNEL ch00;
}
[oracle@onerac DATASYNCXML]$

증분백업 스크립트

[oracle@onerac DATASYNCXML]$ more rman_inc.rman
RUN {
ALLOCATE CHANNEL ch00 DEVICE TYPE DISK;
ALLOCATE CHANNEL ch01 DEVICE TYPE DISK;
BACKUP
    SKIP INACCESSIBLE
    TAG inc_level1
    FILESPERSET 5
    FORMAT '/backup/rman/inc_%s_%p_%t'
    INCREMENTAL LEVEL 1
DATABASE;
sql 'alter system archive log current';
RELEASE CHANNEL ch00;
RELEASE CHANNEL ch01;

ALLOCATE CHANNEL ch00 DEVICE TYPE DISK;
ALLOCATE CHANNEL ch01 DEVICE TYPE DISK;
BACKUP
    filesperset 20
    FORMAT '/backup/rman/archive_%s_%p_%t'
    ARCHIVELOG ALL DELETE INPUT;
RELEASE CHANNEL ch00;
RELEASE CHANNEL ch01;

ALLOCATE CHANNEL ch00 DEVICE TYPE DISK;
BACKUP
    FORMAT '/backup/rman/control_%s_%p_%t'
    CURRENT CONTROLFILE;
RELEASE CHANNEL ch00;
}
[oracle@onerac DATASYNCXML]$

Full 백업 실행

[oracle@onerac DATASYNCXML]$ ls
rman_full.rman  rman_inc.rman
[oracle@onerac DATASYNCXML]$ rman target /
Recovery Manager: Release 19.0.0.0.0 - Production on Fri Aug 26 12:57:45 2022
Version 19.16.0.0.0
Copyright (c) 1982, 2019, Oracle and/or its affiliates.  All rights reserved.
connected to target database: TST (DBID=2254224548)
RMAN> @rman_full.rman
RMAN> RUN {
2> ALLOCATE CHANNEL ch00 DEVICE TYPE DISK;
3> ALLOCATE CHANNEL ch01 DEVICE TYPE DISK;
4> BACKUP
5>     SKIP INACCESSIBLE
6>     TAG full_level0
7>     FILESPERSET 5
8>     FORMAT '/backup/rman/full_%s_%p_%t'
9>     INCREMENTAL LEVEL 0
10> DATABASE;
11> sql 'alter system archive log current';
12> RELEASE CHANNEL ch00;
13> RELEASE CHANNEL ch01;
14>
15> ALLOCATE CHANNEL ch00 DEVICE TYPE DISK;
16> ALLOCATE CHANNEL ch01 DEVICE TYPE DISK;
17> BACKUP
18>     filesperset 20
19>     FORMAT '/backup/rman/archive_%s_%p_%t'
20>     ARCHIVELOG ALL DELETE INPUT;
21> RELEASE CHANNEL ch00;
22> RELEASE CHANNEL ch01;
23>
24> ALLOCATE CHANNEL ch00 DEVICE TYPE DISK;
25> BACKUP
26>     FORMAT '/backup/rman/control_%s_%p_%t'
27>     CURRENT CONTROLFILE;
28> RELEASE CHANNEL ch00;
29> }
using target database control file instead of recovery catalog
allocated channel: ch00
channel ch00: SID=273 device type=DISK
allocated channel: ch01
channel ch01: SID=402 device type=DISK
Starting backup at 26-AUG-22
channel ch00: starting incremental level 0 datafile backup set
channel ch00: specifying datafile(s) in backup set
input datafile file number=00001 name=+DATA/TST/DATAFILE/system.261.1113060137
input datafile file number=00002 name=+DATA/TST/DATAFILE/sysaux.262.1113060139
channel ch00: starting piece 1 at 26-AUG-22
channel ch01: starting incremental level 0 datafile backup set
channel ch01: specifying datafile(s) in backup set
input datafile file number=00003 name=+DATA/TST/DATAFILE/undotbs1.263.1113060139
input datafile file number=00005 name=+DATA/TST/DATAFILE/tst.267.1113735843
input datafile file number=00004 name=+DATA/TST/DATAFILE/users.265.1113060145
channel ch01: starting piece 1 at 26-AUG-22
channel ch00: finished piece 1 at 26-AUG-22
piece handle=/backup/rman/full_19_1_1113742675 tag=FULL_LEVEL0 comment=NONE
channel ch00: backup set complete, elapsed time: 00:00:03
channel ch01: finished piece 1 at 26-AUG-22
piece handle=/backup/rman/full_20_1_1113742675 tag=FULL_LEVEL0 comment=NONE
channel ch01: backup set complete, elapsed time: 00:00:03
Finished backup at 26-AUG-22
Starting Control File and SPFILE Autobackup at 26-AUG-22
piece handle=/u01/app/oracle/product/19.0.0/db_1/dbs/c-2254224548-20220826-07 comment=NONE
Finished Control File and SPFILE Autobackup at 26-AUG-22
sql statement: alter system archive log current
released channel: ch00
released channel: ch01
allocated channel: ch00
channel ch00: SID=273 device type=DISK
allocated channel: ch01
channel ch01: SID=402 device type=DISK
Starting backup at 26-AUG-22
current log archived
channel ch00: starting archived log backup set
channel ch00: specifying archived log(s) in backup set
input archived log thread=1 sequence=35 RECID=12 STAMP=1113742679
channel ch00: starting piece 1 at 26-AUG-22
channel ch01: starting archived log backup set
channel ch01: specifying archived log(s) in backup set
input archived log thread=1 sequence=36 RECID=13 STAMP=1113742679
channel ch01: starting piece 1 at 26-AUG-22
channel ch00: finished piece 1 at 26-AUG-22
piece handle=/backup/rman/archive_22_1_1113742680 tag=TAG20220826T125800 comment=NONE
channel ch00: backup set complete, elapsed time: 00:00:01
channel ch00: deleting archived log(s)
archived log file name=/backup/arch/TST_35_1_1113060132.log RECID=12 STAMP=1113742679
channel ch01: finished piece 1 at 26-AUG-22
piece handle=/backup/rman/archive_23_1_1113742680 tag=TAG20220826T125800 comment=NONE
channel ch01: backup set complete, elapsed time: 00:00:01
channel ch00: deleting archived log(s)
archived log file name=/backup/arch/TST_36_1_1113060132.log RECID=13 STAMP=1113742679
Finished backup at 26-AUG-22
Starting Control File and SPFILE Autobackup at 26-AUG-22
piece handle=/u01/app/oracle/product/19.0.0/db_1/dbs/c-2254224548-20220826-08 comment=NONE
Finished Control File and SPFILE Autobackup at 26-AUG-22
released channel: ch00
released channel: ch01
allocated channel: ch00
channel ch00: SID=273 device type=DISK
Starting backup at 26-AUG-22
channel ch00: starting full datafile backup set
channel ch00: specifying datafile(s) in backup set
including current control file in backup set
channel ch00: starting piece 1 at 26-AUG-22
channel ch00: finished piece 1 at 26-AUG-22
piece handle=/backup/rman/control_25_1_1113742682 tag=TAG20220826T125802 comment=NONE
channel ch00: backup set complete, elapsed time: 00:00:01
Finished backup at 26-AUG-22
Starting Control File and SPFILE Autobackup at 26-AUG-22
piece handle=/u01/app/oracle/product/19.0.0/db_1/dbs/c-2254224548-20220826-09 comment=NONE
Finished Control File and SPFILE Autobackup at 26-AUG-22
released channel: ch00
RMAN> **end-of-file**
RMAN> quit
Recovery Manager complete.
[oracle@onerac DATASYNCXML]$
[oracle@onerac rman]$ pwd
/backup/rman
[oracle@onerac rman]$
[oracle@onerac rman]$ ls -al
total 1473412
drwxr-xr-x. 2 oracle dba        155 Aug 26 12:58 .
drwxr-xr-x. 4 oracle dba         30 Aug 26 09:33 ..
-rw-r-----. 1 oracle dba    5424640 Aug 26 12:58 archive_22_1_1113742680
-rw-r-----. 1 oracle dba       4608 Aug 26 12:58 archive_23_1_1113742680
-rw-r-----. 1 oracle dba   10682368 Aug 26 12:58 control_25_1_1113742682
-rw-r-----. 1 oracle dba 1485070336 Aug 26 12:57 full_19_1_1113742675
-rw-r-----. 1 oracle dba    7585792 Aug 26 12:57 full_20_1_1113742675
[oracle@onerac rman]$

증분백업 실행

[oracle@onerac DATASYNCXML]$ ls
rman_full.rman  rman_inc.rman
[oracle@onerac DATASYNCXML]$ rman target /
Recovery Manager: Release 19.0.0.0.0 - Production on Fri Aug 26 13:05:01 2022
Version 19.16.0.0.0
Copyright (c) 1982, 2019, Oracle and/or its affiliates.  All rights reserved.
connected to target database: TST (DBID=2254224548)
RMAN> @rman_inc.rman
RMAN> RUN {
2> ALLOCATE CHANNEL ch00 DEVICE TYPE DISK;
3> ALLOCATE CHANNEL ch01 DEVICE TYPE DISK;
4> BACKUP
5>     SKIP INACCESSIBLE
6>     TAG inc_level1
7>     FILESPERSET 5
8>     FORMAT '/backup/rman/inc_%s_%p_%t'
9>     INCREMENTAL LEVEL 1
10> DATABASE;
11> sql 'alter system archive log current';
12> RELEASE CHANNEL ch00;
13> RELEASE CHANNEL ch01;
14>
15> ALLOCATE CHANNEL ch00 DEVICE TYPE DISK;
16> ALLOCATE CHANNEL ch01 DEVICE TYPE DISK;
17> BACKUP
18>     filesperset 20
19>     FORMAT '/backup/rman/archive_%s_%p_%t'
20>     ARCHIVELOG ALL DELETE INPUT;
21> RELEASE CHANNEL ch00;
22> RELEASE CHANNEL ch01;
23>
24> ALLOCATE CHANNEL ch00 DEVICE TYPE DISK;
25> BACKUP
26>     FORMAT '/backup/rman/control_%s_%p_%t'
27>     CURRENT CONTROLFILE;
28> RELEASE CHANNEL ch00;
29> }
using target database control file instead of recovery catalog
allocated channel: ch00
channel ch00: SID=273 device type=DISK
allocated channel: ch01
channel ch01: SID=402 device type=DISK
Starting backup at 26-AUG-22
channel ch00: starting incremental level 1 datafile backup set
channel ch00: specifying datafile(s) in backup set
input datafile file number=00001 name=+DATA/TST/DATAFILE/system.261.1113060137
input datafile file number=00002 name=+DATA/TST/DATAFILE/sysaux.262.1113060139
channel ch00: starting piece 1 at 26-AUG-22
channel ch01: starting incremental level 1 datafile backup set
channel ch01: specifying datafile(s) in backup set
input datafile file number=00003 name=+DATA/TST/DATAFILE/undotbs1.263.1113060139
input datafile file number=00005 name=+DATA/TST/DATAFILE/tst.267.1113735843
input datafile file number=00004 name=+DATA/TST/DATAFILE/users.265.1113060145
channel ch01: starting piece 1 at 26-AUG-22
channel ch00: finished piece 1 at 26-AUG-22
piece handle=/backup/rman/inc_27_1_1113743110 tag=INC_LEVEL1 comment=NONE
channel ch00: backup set complete, elapsed time: 00:00:03
channel ch01: finished piece 1 at 26-AUG-22
piece handle=/backup/rman/inc_28_1_1113743110 tag=INC_LEVEL1 comment=NONE
channel ch01: backup set complete, elapsed time: 00:00:03
Finished backup at 26-AUG-22
Starting Control File and SPFILE Autobackup at 26-AUG-22
piece handle=/u01/app/oracle/product/19.0.0/db_1/dbs/c-2254224548-20220826-0a comment=NONE
Finished Control File and SPFILE Autobackup at 26-AUG-22
sql statement: alter system archive log current
released channel: ch00
released channel: ch01
allocated channel: ch00
channel ch00: SID=273 device type=DISK
allocated channel: ch01
channel ch01: SID=402 device type=DISK
Starting backup at 26-AUG-22
current log archived
channel ch00: starting archived log backup set
channel ch00: specifying archived log(s) in backup set
input archived log thread=1 sequence=37 RECID=14 STAMP=1113743114
channel ch00: starting piece 1 at 26-AUG-22
channel ch01: starting archived log backup set
channel ch01: specifying archived log(s) in backup set
input archived log thread=1 sequence=38 RECID=15 STAMP=1113743115
channel ch01: starting piece 1 at 26-AUG-22
channel ch00: finished piece 1 at 26-AUG-22
piece handle=/backup/rman/archive_30_1_1113743115 tag=TAG20220826T130515 comment=NONE
channel ch00: backup set complete, elapsed time: 00:00:01
channel ch00: deleting archived log(s)
archived log file name=/backup/arch/TST_37_1_1113060132.log RECID=14 STAMP=1113743114
channel ch01: finished piece 1 at 26-AUG-22
piece handle=/backup/rman/archive_31_1_1113743115 tag=TAG20220826T130515 comment=NONE
channel ch01: backup set complete, elapsed time: 00:00:01
channel ch00: deleting archived log(s)
archived log file name=/backup/arch/TST_38_1_1113060132.log RECID=15 STAMP=1113743115
Finished backup at 26-AUG-22
Starting Control File and SPFILE Autobackup at 26-AUG-22
piece handle=/u01/app/oracle/product/19.0.0/db_1/dbs/c-2254224548-20220826-0b comment=NONE
Finished Control File and SPFILE Autobackup at 26-AUG-22
released channel: ch00
released channel: ch01
allocated channel: ch00
channel ch00: SID=273 device type=DISK
Starting backup at 26-AUG-22
channel ch00: starting full datafile backup set
channel ch00: specifying datafile(s) in backup set
including current control file in backup set
channel ch00: starting piece 1 at 26-AUG-22
channel ch00: finished piece 1 at 26-AUG-22
piece handle=/backup/rman/control_33_1_1113743118 tag=TAG20220826T130517 comment=NONE
channel ch00: backup set complete, elapsed time: 00:00:01
Finished backup at 26-AUG-22
Starting Control File and SPFILE Autobackup at 26-AUG-22
piece handle=/u01/app/oracle/product/19.0.0/db_1/dbs/c-2254224548-20220826-0c comment=NONE
Finished Control File and SPFILE Autobackup at 26-AUG-22
released channel: ch00
RMAN> **end-of-file**
RMAN> quit
Recovery Manager complete.
[oracle@onerac DATASYNCXML]$
[oracle@onerac rman]$ pwd
/backup/rman
[oracle@onerac rman]$ ls -ltr
total 1495240
-rw-r-----. 1 oracle dba    7585792 Aug 26 12:57 full_20_1_1113742675
-rw-r-----. 1 oracle dba 1485070336 Aug 26 12:57 full_19_1_1113742675
-rw-r-----. 1 oracle dba    5424640 Aug 26 12:58 archive_22_1_1113742680
-rw-r-----. 1 oracle dba       4608 Aug 26 12:58 archive_23_1_1113742680
-rw-r-----. 1 oracle dba   10682368 Aug 26 12:58 control_25_1_1113742682
-rw-r-----. 1 oracle dba    3252224 Aug 26 13:05 inc_28_1_1113743110
-rw-r-----. 1 oracle dba    4718592 Aug 26 13:05 inc_27_1_1113743110
-rw-r-----. 1 oracle dba    3692544 Aug 26 13:05 archive_30_1_1113743115
-rw-r-----. 1 oracle dba       4096 Aug 26 13:05 archive_31_1_1113743115
-rw-r-----. 1 oracle dba   10682368 Aug 26 13:05 control_33_1_1113743118
[oracle@onerac rman]$

DB 삭제 전 주요 파일 백업 및 DB 삭제

Oracle DB SPFILE / Controlfile 백업

[oracle@onerac DATASYNCXML]$ env | grep SID
ORACLE_SID=TST
[oracle@onerac DATASYNCXML]$ pwd
/home/oracle/DATASYNCXML
[oracle@onerac DATASYNCXML]$ sqlplus "/as sysdba"
SQL*Plus: Release 19.0.0.0.0 - Production on Fri Aug 26 13:07:35 2022
Version 19.16.0.0.0
Copyright (c) 1982, 2022, Oracle.  All rights reserved.
Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.16.0.0.0
SQL> show parameter spfile;
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
spfile                               string      +DATA/TST/PARAMETERFILE/spfile.266.1113061615
SQL> create pfile='/home/oracle/DATASYNCXML/initTST.org_ORG' from spfile;
File created.
SQL> alter database backup controlfile to trace as '/home/oracle/DATASYNCXML/cr_controlfile.sql_org';
Database altered.
SQL> exit
Disconnected from Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.16.0.0.0
[oracle@onerac DATASYNCXML]$ ls
cr_controlfile.sql_org  initTST.org_ORG  rman_full.rman  rman_inc.rman
[oracle@onerac DATASYNCXML]$

Oracle ASM spfile 백업
[oracle@onerac DATASYNCXML]$ env | grep SID
ORACLE_SID=+ASM
[oracle@onerac DATASYNCXML]$ pwd
/home/oracle/DATASYNCXML
[oracle@onerac DATASYNCXML]$ sqlplus "/as sysasm"
SQL*Plus: Release 19.0.0.0.0 - Production on Fri Aug 26 13:13:35 2022
Version 19.16.0.0.0
Copyright (c) 1982, 2022, Oracle.  All rights reserved.
Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.16.0.0.0
SQL> show parameter spfile;
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
spfile                               string      +DATA/ASM/ASMPARAMETERFILE/registry.253.1113059361
SQL> create pfile='/home/oracle/DATASYNCXML/initASM.ora_ORG' from spfile;
File created.
SQL> exit
Disconnected from Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.16.0.0.0
[oracle@onerac DATASYNCXML]$ ls
cr_controlfile.sql_org  initASM.ora_ORG  initTST.org_ORG  rman_full.rman  rman_inc.rman
[oracle@onerac DATASYNCXML]$
[oracle@onerac ~]$ env | grep SID
ORACLE_SID=+ASM
[oracle@onerac ~]$
[oracle@onerac ~]$
[oracle@onerac ~]$ crsctl status res -t
--------------------------------------------------------------------------------
Name           Target  State        Server                   State details
--------------------------------------------------------------------------------
Local Resources
--------------------------------------------------------------------------------
ora.DATA.dg
               ONLINE  ONLINE       onerac                   STABLE
ora.LISTENER.lsnr
               ONLINE  ONLINE       onerac                   STABLE
ora.asm
               ONLINE  ONLINE       onerac                   Started,STABLE
ora.ons
               OFFLINE OFFLINE      onerac                   STABLE
--------------------------------------------------------------------------------
Cluster Resources
--------------------------------------------------------------------------------
ora.cssd
      1        ONLINE  ONLINE       onerac                   STABLE
ora.diskmon
      1        OFFLINE OFFLINE                               STABLE
ora.evmd
      1        ONLINE  ONLINE       onerac                   STABLE
ora.tst.db
      1        ONLINE  ONLINE       onerac                   Open,HOME=/u01/app/o
                                                             racle/product/19.0.0
                                                             /db_1,STABLE
--------------------------------------------------------------------------------
[oracle@onerac ~]$
[oracle@onerac ~]$
[oracle@onerac ~]$ srvctl config database -d TST
Database unique name: TST
Database name: TST
Oracle home: /u01/app/oracle/product/19.0.0/db_1
Oracle user: oracle
Spfile: +DATA/TST/PARAMETERFILE/spfile.266.1113061615
Password file:
Domain:
Start options: open
Stop options: immediate
Database role: PRIMARY
Management policy: AUTOMATIC
Disk Groups: DATA
Services:
OSDBA group: dba
OSOPER group: dba
Database instance: TST
[oracle@onerac ~]$

Oracle DB 삭제 및 Oracle CRS에서 DB 리소스 삭제

[oracle@onerac DATASYNCXML]$ env | grep SID
ORACLE_SID=TST
[oracle@onerac DATASYNCXML]$ sqlplus "/as sysdba"
SQL*Plus: Release 19.0.0.0.0 - Production on Fri Aug 26 13:18:40 2022
Version 19.16.0.0.0
Copyright (c) 1982, 2022, Oracle.  All rights reserved.
Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.16.0.0.0
SQL> select count(*) from scott.emp;

  COUNT(*)
----------
        56
SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> exit
Disconnected from Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.16.0.0.0
[oracle@onerac DATASYNCXML]$
[oracle@onerac DATASYNCXML]$ sqlplus "/as sysdba"
SQL*Plus: Release 19.0.0.0.0 - Production on Fri Aug 26 13:20:05 2022
Version 19.16.0.0.0
Copyright (c) 1982, 2022, Oracle.  All rights reserved.
Connected to an idle instance.
SQL> startup mount exclusive restrict
ORACLE instance started.
Total System Global Area 2499804200 bytes
Fixed Size                  8899624 bytes
Variable Size             654311424 bytes
Database Buffers         1828716544 bytes
Redo Buffers                7876608 bytes
Database mounted.
SQL>
SQL>
SQL> drop database;
Database dropped.
Disconnected from Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.16.0.0.0
SQL> exit
[oracle@onerac DATASYNCXML]$
[oracle@onerac DATASYNCXML]$ env | grep SID
ORACLE_SID=+ASM
[oracle@onerac DATASYNCXML]$
[oracle@onerac DATASYNCXML]$ crsctl status res -t
--------------------------------------------------------------------------------
Name           Target  State        Server                   State details
--------------------------------------------------------------------------------
Local Resources
--------------------------------------------------------------------------------
ora.DATA.dg
               ONLINE  ONLINE       onerac                   STABLE
ora.LISTENER.lsnr
               ONLINE  ONLINE       onerac                   STABLE
ora.asm
               ONLINE  ONLINE       onerac                   Started,STABLE
ora.ons
               OFFLINE OFFLINE      onerac                   STABLE
--------------------------------------------------------------------------------
Cluster Resources
--------------------------------------------------------------------------------
ora.cssd
      1        ONLINE  ONLINE       onerac                   STABLE
ora.diskmon
      1        OFFLINE OFFLINE                               STABLE
ora.evmd
      1        ONLINE  ONLINE       onerac                   STABLE
ora.tst.db
      1        OFFLINE OFFLINE                               Instance Shutdown,ST
                                                             ABLE
--------------------------------------------------------------------------------
[oracle@onerac DATASYNCXML]$
[oracle@onerac DATASYNCXML]$
[oracle@onerac DATASYNCXML]$ srvctl remove database -d TST
Remove the database TST? (y/[n]) y
[oracle@onerac DATASYNCXML]$
[oracle@onerac DATASYNCXML]$ crsctl status res -t
--------------------------------------------------------------------------------
Name           Target  State        Server                   State details
--------------------------------------------------------------------------------
Local Resources
--------------------------------------------------------------------------------
ora.DATA.dg
               ONLINE  ONLINE       onerac                   STABLE
ora.LISTENER.lsnr
               ONLINE  ONLINE       onerac                   STABLE
ora.asm
               ONLINE  ONLINE       onerac                   Started,STABLE
ora.ons
               OFFLINE OFFLINE      onerac                   STABLE
--------------------------------------------------------------------------------
Cluster Resources
--------------------------------------------------------------------------------
ora.cssd
      1        ONLINE  ONLINE       onerac                   STABLE
ora.diskmon
      1        OFFLINE OFFLINE                               STABLE
ora.evmd
      1        ONLINE  ONLINE       onerac                   STABLE
--------------------------------------------------------------------------------
[oracle@onerac DATASYNCXML]$
[oracle@onerac DATASYNCXML]$

Oracle DB 복구

Control file을 복구

[oracle@onerac DATASYNCXML]$ env | grep SID
ORACLE_SID=TST
[oracle@onerac DATASYNCXML]$ pwd
/home/oracle/DATASYNCXML
[oracle@onerac DATASYNCXML]$ ls
cr_controlfile.sql_org  initASM.ora_ORG  initTST.org_ORG  rman_full.rman  rman_inc.rman
[oracle@onerac DATASYNCXML]$ cp initTST.org_ORG initTST.org_recover
[oracle@onerac DATASYNCXML]$ vi initTST.org_recover
[oracle@onerac DATASYNCXML]$ more initTST.org_recover
*.audit_file_dest='/logs/db/admin/TST/adump'
*.audit_trail='none'
*.compatible='19.0.0'
#*.control_files='+DATA/TST/CONTROLFILE/current.257.1113060137' --주석처리
*.control_files='+DATA' --임시 추가
*.db_block_size=8192
*.db_create_file_dest='+DATA'
*.db_name='TST'
*.diagnostic_dest='/logs/db/'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=TSTXDB)'
*.log_archive_dest_1='location=/backup/arch/'
*.log_archive_format='TST_%s_%t_%r.log'
*.nls_language='AMERICAN'
*.nls_territory='AMERICA'
*.open_cursors=300
*.pga_aggregate_target=795m
*.processes=320
*.remote_login_passwordfile='EXCLUSIVE'
*.sga_target=2382m
*.undo_tablespace='UNDOTBS1'
[oracle@onerac DATASYNCXML]$
[oracle@onerac DATASYNCXML]$ ls
cr_controlfile.sql_org  initASM.ora_ORG  initTST.org_ORG  initTST.org_recover  rman_full.rman  rman_inc.rman
[oracle@onerac DATASYNCXML]$
[oracle@onerac DATASYNCXML]$
[oracle@onerac DATASYNCXML]$ sqlplus "/as sysdba"
SQL*Plus: Release 19.0.0.0.0 - Production on Fri Aug 26 14:16:51 2022
Version 19.16.0.0.0
Copyright (c) 1982, 2022, Oracle.  All rights reserved.
Connected to an idle instance.
SQL> startup nomount pfile='/home/oracle/DATASYNCXML/initTST.org_recover';
ORACLE instance started.
Total System Global Area 2499804200 bytes
Fixed Size                  8899624 bytes
Variable Size             536870912 bytes
Database Buffers         1946157056 bytes
Redo Buffers                7876608 bytes
SQL>
SQL> show parameter db_name
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_name                              string      TST
SQL>
SQL> show parameter db_unique
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_unique_name                       string      TST
SQL> exit
Disconnected from Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.16.0.0.0
[oracle@onerac DATASYNCXML]$
[oracle@onerac DATASYNCXML]$ ps -ef | grep pmon
oracle      5049       1  0 09:25 ?        00:00:01 asm_pmon_+ASM
oracle     20023       1  0 14:18 ?        00:00:00 ora_pmon_TST
oracle     20156   19890  0 14:18 pts/0    00:00:00 grep --color=auto pmon
[oracle@onerac DATASYNCXML]$
[oracle@onerac rman]$ pwd
/backup/rman
[oracle@onerac rman]$
[oracle@onerac rman]$ ls -ltr
total 1495240
-rw-r-----. 1 oracle dba    7585792 Aug 26 12:57 full_20_1_1113742675
-rw-r-----. 1 oracle dba 1485070336 Aug 26 12:57 full_19_1_1113742675
-rw-r-----. 1 oracle dba    5424640 Aug 26 12:58 archive_22_1_1113742680
-rw-r-----. 1 oracle dba       4608 Aug 26 12:58 archive_23_1_1113742680
-rw-r-----. 1 oracle dba   10682368 Aug 26 12:58 control_25_1_1113742682
-rw-r-----. 1 oracle dba    3252224 Aug 26 13:05 inc_28_1_1113743110
-rw-r-----. 1 oracle dba    4718592 Aug 26 13:05 inc_27_1_1113743110
-rw-r-----. 1 oracle dba    3692544 Aug 26 13:05 archive_30_1_1113743115
-rw-r-----. 1 oracle dba       4096 Aug 26 13:05 archive_31_1_1113743115
-rw-r-----. 1 oracle dba   10682368 Aug 26 13:05 control_33_1_1113743118
[oracle@onerac rman]$
[oracle@onerac rman]$ rman target /
Recovery Manager: Release 19.0.0.0.0 - Production on Fri Aug 26 14:22:23 2022
Version 19.16.0.0.0
Copyright (c) 1982, 2019, Oracle and/or its affiliates.  All rights reserved.
connected to target database: TST (not mounted)
RMAN> restore controlfile from '/backup/rman/control_33_1_1113743118';
Starting restore at 26-AUG-22
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=391 device type=DISK
channel ORA_DISK_1: restoring control file
channel ORA_DISK_1: restore complete, elapsed time: 00:00:01
output file name=+DATA/TST/CONTROLFILE/current.266.1113747773
Finished restore at 26-AUG-22
RMAN> exit
Recovery Manager complete.
[oracle@onerac rman]$

Oracle DB Rstore 및 Recovery

[oracle@onerac DATASYNCXML]$ pwd
/home/oracle/DATASYNCXML
[oracle@onerac DATASYNCXML]$ ls
cr_controlfile.sql_org  initASM.ora_ORG  initTST.org_ORG  initTST.org_recover  rman_full.rman  rman_inc.rman
[oracle@onerac DATASYNCXML]$ vi initTST.org_recover
[oracle@onerac DATASYNCXML]$ more initTST.org_recover
*.audit_file_dest='/logs/db/admin/TST/adump'
*.audit_trail='none'
*.compatible='19.0.0'
#*.control_files='+DATA/TST/CONTROLFILE/current.257.1113060137' --주석처리
#*.control_files='+DATA' --임시 추가 주석 처리
*.control_files='+DATA/TST/CONTROLFILE/current.266.1113747773' --RMAN에서 Restore한 Control FIle 추가
*.db_block_size=8192
*.db_create_file_dest='+DATA'
*.db_name='TST'
*.diagnostic_dest='/logs/db/'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=TSTXDB)'
*.log_archive_dest_1='location=/backup/arch/'
*.log_archive_format='TST_%s_%t_%r.log'
*.nls_language='AMERICAN'
*.nls_territory='AMERICA'
*.open_cursors=300
*.pga_aggregate_target=795m
*.processes=320
*.remote_login_passwordfile='EXCLUSIVE'
*.sga_target=2382m
*.undo_tablespace='UNDOTBS1'
[oracle@onerac DATASYNCXML]$
[oracle@onerac DATASYNCXML]$ pwd
/home/oracle/DATASYNCXML
[oracle@onerac DATASYNCXML]$ ls
cr_controlfile.sql_org  initASM.ora_ORG  initTST.org_ORG  initTST.org_recover  rman_full.rman  rman_inc.rman
[oracle@onerac DATASYNCXML]$ sqlplus "/as sysdba"
SQL*Plus: Release 19.0.0.0.0 - Production on Fri Aug 26 14:25:05 2022
Version 19.16.0.0.0
Copyright (c) 1982, 2022, Oracle.  All rights reserved.
Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.16.0.0.0
SQL> shutdown immediate
ORA-01507: database not mounted
ORACLE instance shut down.
SQL> exit
Disconnected from Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.16.0.0.0
[oracle@onerac DATASYNCXML]$
[oracle@onerac DATASYNCXML]$ pwd
/home/oracle/DATASYNCXML
[oracle@onerac DATASYNCXML]$ ls
cr_controlfile.sql_org  initASM.ora_ORG  initTST.org_ORG  initTST.org_recover  rman_full.rman  rman_inc.rman
[oracle@onerac DATASYNCXML]$
[oracle@onerac DATASYNCXML]$ sqlplus "/as sysdba"
SQL*Plus: Release 19.0.0.0.0 - Production on Fri Aug 26 14:27:09 2022
Version 19.16.0.0.0
Copyright (c) 1982, 2022, Oracle.  All rights reserved.
Connected to an idle instance.
SQL> startup mount pfile='/home/oracle/DATASYNCXML/initTST.org_recover';
ORACLE instance started.
Total System Global Area 2499804200 bytes
Fixed Size                  8899624 bytes
Variable Size             536870912 bytes
Database Buffers         1946157056 bytes
Redo Buffers                7876608 bytes
Database mounted.
SQL> exit
Disconnected from Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.16.0.0.0
[oracle@onerac DATASYNCXML]$
[oracle@onerac DATASYNCXML]$ rman target /
Recovery Manager: Release 19.0.0.0.0 - Production on Fri Aug 26 14:28:32 2022
Version 19.16.0.0.0
Copyright (c) 1982, 2019, Oracle and/or its affiliates.  All rights reserved.
connected to target database: TST (DBID=2254224548, not open)
RMAN>
RMAN> restore database;
Starting restore at 26-AUG-22
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=13 device type=DISK
channel ORA_DISK_1: starting datafile backup set restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_DISK_1: restoring datafile 00003 to +DATA/TST/DATAFILE/undotbs1.263.1113060139
channel ORA_DISK_1: restoring datafile 00004 to +DATA/TST/DATAFILE/users.265.1113060145
channel ORA_DISK_1: restoring datafile 00005 to +DATA/TST/DATAFILE/tst.267.1113735843
channel ORA_DISK_1: reading from backup piece /backup/rman/full_20_1_1113742675
channel ORA_DISK_1: piece handle=/backup/rman/full_20_1_1113742675 tag=FULL_LEVEL0
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:01
channel ORA_DISK_1: starting datafile backup set restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_DISK_1: restoring datafile 00001 to +DATA/TST/DATAFILE/system.261.1113060137
channel ORA_DISK_1: restoring datafile 00002 to +DATA/TST/DATAFILE/sysaux.262.1113060139
channel ORA_DISK_1: reading from backup piece /backup/rman/full_19_1_1113742675
channel ORA_DISK_1: piece handle=/backup/rman/full_19_1_1113742675 tag=FULL_LEVEL0
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:07
Finished restore at 26-AUG-22
RMAN>
RMAN> recover database;
Starting recover at 26-AUG-22
using channel ORA_DISK_1
channel ORA_DISK_1: starting incremental datafile backup set restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
destination for restore of datafile 00001: +DATA/TST/DATAFILE/system.259.1113748125
destination for restore of datafile 00002: +DATA/TST/DATAFILE/sysaux.258.1113748125
channel ORA_DISK_1: reading from backup piece /backup/rman/inc_27_1_1113743110
channel ORA_DISK_1: piece handle=/backup/rman/inc_27_1_1113743110 tag=INC_LEVEL1
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:01
channel ORA_DISK_1: starting incremental datafile backup set restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
destination for restore of datafile 00003: +DATA/TST/DATAFILE/undotbs1.257.1113748123
destination for restore of datafile 00004: +DATA/TST/DATAFILE/users.260.1113748123
destination for restore of datafile 00005: +DATA/TST/DATAFILE/tst.264.1113748123
channel ORA_DISK_1: reading from backup piece /backup/rman/inc_28_1_1113743110
channel ORA_DISK_1: piece handle=/backup/rman/inc_28_1_1113743110 tag=INC_LEVEL1
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:01
starting media recovery
channel ORA_DISK_1: starting archived log restore to default destination
channel ORA_DISK_1: restoring archived log
archived log thread=1 sequence=37
channel ORA_DISK_1: reading from backup piece /backup/rman/archive_30_1_1113743115
channel ORA_DISK_1: piece handle=/backup/rman/archive_30_1_1113743115 tag=TAG20220826T130515
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:01
archived log file name=/backup/arch/TST_37_1_1113060132.log thread=1 sequence=37
channel ORA_DISK_1: starting archived log restore to default destination
channel ORA_DISK_1: restoring archived log
archived log thread=1 sequence=38
channel ORA_DISK_1: reading from backup piece /backup/rman/archive_31_1_1113743115
channel ORA_DISK_1: piece handle=/backup/rman/archive_31_1_1113743115 tag=TAG20220826T130515
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:01
archived log file name=/backup/arch/TST_38_1_1113060132.log thread=1 sequence=38
unable to find archived log
archived log thread=1 sequence=39
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of recover command at 08/26/2022 14:30:08
RMAN-06054: media recovery requesting unknown archived log for thread 1 with sequence 39 and starting SCN of 1288960
RMAN>
RMAN> sql 'alter database open RESETLOGS';
sql statement: alter database open RESETLOGS
RMAN> exit
Recovery Manager complete.
[oracle@onerac DATASYNCXML]$

Oracle DB 복구 상태 확인 및 Oracle CRS에 DB 리소스 등록

[oracle@onerac DATASYNCXML]$
[oracle@onerac DATASYNCXML]$ sqlplus "/as sysdba"
SQL*Plus: Release 19.0.0.0.0 - Production on Fri Aug 26 14:34:19 2022
Version 19.16.0.0.0
Copyright (c) 1982, 2022, Oracle.  All rights reserved.
Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.16.0.0.0
SQL> select status from v$instance;
STATUS
------------
OPEN
SQL> select count(*) from scott.emp;

  COUNT(*)
----------
        56
SQL> show parameter spfile;
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
spfile                               string
SQL> create spfile='+DATA' from pfile='/home/oracle/DATASYNCXML/initTST.org_recover';
File created.
SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> exit
Disconnected from Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.16.0.0.0
[oracle@onerac DATASYNCXML]$
[oracle@onerac DATASYNCXML]$ env | grep SID
ORACLE_SID=+ASM
[oracle@onerac DATASYNCXML]$ crsctl status res -t
--------------------------------------------------------------------------------
Name           Target  State        Server                   State details
--------------------------------------------------------------------------------
Local Resources
--------------------------------------------------------------------------------
ora.DATA.dg
               ONLINE  ONLINE       onerac                   STABLE
ora.LISTENER.lsnr
               ONLINE  ONLINE       onerac                   STABLE
ora.asm
               ONLINE  ONLINE       onerac                   Started,STABLE
ora.ons
               OFFLINE OFFLINE      onerac                   STABLE
--------------------------------------------------------------------------------
Cluster Resources
--------------------------------------------------------------------------------
ora.cssd
      1        ONLINE  ONLINE       onerac                   STABLE
ora.diskmon
      1        OFFLINE OFFLINE                               STABLE
ora.evmd
      1        ONLINE  ONLINE       onerac                   STABLE
--------------------------------------------------------------------------------
[oracle@onerac DATASYNCXML]$
[oracle@onerac DATASYNCXML]$ asmcmd -p
ASMCMD [+] > lsdg
State    Type    Rebal  Sector  Logical_Sector  Block       AU  Total_MB  Free_MB  Req_mir_free_MB  Usable_file_MB  Offline_disks  Voting_files  Name
MOUNTED  EXTERN  N         512             512   4096  4194304     10236     6776                0            6776              0             N  DATA/
ASMCMD [+] > cd DATA/TST/PARAMETERFILE
ASMCMD [+DATA/TST/PARAMETERFILE] > pwd
+DATA/TST/PARAMETERFILE
ASMCMD [+DATA/TST/PARAMETERFILE] > ls
spfile.261.1113748535
ASMCMD [+DATA/TST/PARAMETERFILE] > exit
[oracle@onerac DATASYNCXML]$
[oracle@onerac DATASYNCXML]$ srvctl add database -d TST -o /u01/app/oracle/product/19.0.0/db_1 -spfile +DATA/TST/PARAMETERFILE/spfile.261.1113748535
[oracle@onerac DATASYNCXML]$ crsctl status res -t
--------------------------------------------------------------------------------
Name           Target  State        Server                   State details
--------------------------------------------------------------------------------
Local Resources
--------------------------------------------------------------------------------
ora.DATA.dg
               ONLINE  ONLINE       onerac                   STABLE
ora.LISTENER.lsnr
               ONLINE  ONLINE       onerac                   STABLE
ora.asm
               ONLINE  ONLINE       onerac                   Started,STABLE
ora.ons
               OFFLINE OFFLINE      onerac                   STABLE
--------------------------------------------------------------------------------
Cluster Resources
--------------------------------------------------------------------------------
ora.cssd
      1        ONLINE  ONLINE       onerac                   STABLE
ora.diskmon
      1        OFFLINE OFFLINE                               STABLE
ora.evmd
      1        ONLINE  ONLINE       onerac                   STABLE
ora.tst.db
      1        OFFLINE OFFLINE                               STABLE
--------------------------------------------------------------------------------
[oracle@onerac DATASYNCXML]$
[oracle@onerac DATASYNCXML]$ srvctl start database -d TST
[oracle@onerac DATASYNCXML]$
[oracle@onerac DATASYNCXML]$ crsctl status res -t
--------------------------------------------------------------------------------
Name           Target  State        Server                   State details
--------------------------------------------------------------------------------
Local Resources
--------------------------------------------------------------------------------
ora.DATA.dg
               ONLINE  ONLINE       onerac                   STABLE
ora.LISTENER.lsnr
               ONLINE  ONLINE       onerac                   STABLE
ora.asm
               ONLINE  ONLINE       onerac                   Started,STABLE
ora.ons
               OFFLINE OFFLINE      onerac                   STABLE
--------------------------------------------------------------------------------
Cluster Resources
--------------------------------------------------------------------------------
ora.cssd
      1        ONLINE  ONLINE       onerac                   STABLE
ora.diskmon
      1        OFFLINE OFFLINE                               STABLE
ora.evmd
      1        ONLINE  ONLINE       onerac                   STABLE
ora.tst.db
      1        ONLINE  ONLINE       onerac                   Open,HOME=/u01/app/o
                                                             racle/product/19.0.0
                                                             /db_1,STABLE
--------------------------------------------------------------------------------
[oracle@onerac DATASYNCXML]$

Oracle Restart Log 경로 변경 하기

2022. 9. 13. 09:00

Oracle ASM Log 위치

[oracle@onerac DATASYNCXML]$ env | grep SID
ORACLE_SID=+ASM
[oracle@onerac DATASYNCXML]$ sqlplus "/as sysasm"
SQL*Plus: Release 19.0.0.0.0 - Production on Mon Aug 22 16:09:29 2022
Version 19.16.0.0.0
Copyright (c) 1982, 2022, Oracle.  All rights reserved.
Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.16.0.0.0
SQL> create pfile='/home/oracle/DATASYNCXML/initASM.ora_org' from spfile;
File created.
SQL>
SQL> show parameter diagnostic_dest
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
diagnostic_dest                      string      /u01/app/oracle
SQL>
SQL>
SQL> show parameter audit_file_dest
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
audit_file_dest                      string      /u01/app/19.0.0/grid/rdbms/audit
SQL>
SQL>
SQL> alter system set diagnostic_dest='/logs/crs/' scope=spfile sid='*';
System altered.
SQL>
SQL> alter system set audit_file_dest='/logs/crs/rdbms/audit' scope=spfile sid='*';
System altered.
SQL>
SQL> host mkdir -p /logs/crs/rdbms/audit
SQL> exit
Disconnected from Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.16.0.0.0
[oracle@onerac DATASYNCXML]$
[oracle@onerac DATASYNCXML]$ srvctl stop database -d TST
[oracle@onerac DATASYNCXML]$
[oracle@onerac DATASYNCXML]$ crsctl stop has
CRS-2791: Starting shutdown of Oracle High Availability Services-managed resources on 'onerac'
CRS-2673: Attempting to stop 'ora.DATA.dg' on 'onerac'
CRS-2673: Attempting to stop 'ora.LISTENER.lsnr' on 'onerac'
CRS-2677: Stop of 'ora.DATA.dg' on 'onerac' succeeded
CRS-2673: Attempting to stop 'ora.asm' on 'onerac'
CRS-2677: Stop of 'ora.LISTENER.lsnr' on 'onerac' succeeded
CRS-2677: Stop of 'ora.asm' on 'onerac' succeeded
CRS-2673: Attempting to stop 'ora.evmd' on 'onerac'
CRS-2677: Stop of 'ora.evmd' on 'onerac' succeeded
CRS-2673: Attempting to stop 'ora.cssd' on 'onerac'
CRS-2677: Stop of 'ora.cssd' on 'onerac' succeeded
CRS-2793: Shutdown of Oracle High Availability Services-managed resources on 'onerac' has completed
CRS-4133: Oracle High Availability Services has been stopped.
[oracle@onerac DATASYNCXML]$
[oracle@onerac DATASYNCXML]$
[oracle@onerac DATASYNCXML]$ crsctl start has
CRS-4123: Oracle High Availability Services has been started.
[oracle@onerac DATASYNCXML]$ sqlplus "/as sysasm"
SQL*Plus: Release 19.0.0.0.0 - Production on Mon Aug 22 16:40:36 2022
Version 19.16.0.0.0
Copyright (c) 1982, 2022, Oracle.  All rights reserved.
Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.16.0.0.0
SQL> show parameter diagnostic_dest
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
diagnostic_dest                      string      /logs/crs/
SQL>
SQL> show parameter audit_file_dest
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
audit_file_dest                      string      /logs/crs/rdbms/audit
SQL> exit
Disconnected from Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.16.0.0.0
[oracle@onerac DATASYNCXML]$

Oracle Database

[oracle@onerac DATASYNCXML]$ env | grep SID
ORACLE_SID=TST
[oracle@onerac DATASYNCXML]$ pwd
/home/oracle/DATASYNCXML
[oracle@onerac DATASYNCXML]$ sqlplus "/as sysdba"
SQL> create pfile='/home/oracle/DATASYNCXML/initTST.ora_org' from spfile;
File created.
SQL>
SQL> show parameter diagnostic_dest
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
diagnostic_dest                      string      /u01/app/oracle
SQL>
SQL> show parameter audit_file_dest
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
audit_file_dest                      string      /u01/app/oracle/admin/TST/adump
SQL>
SQL>
SQL> alter system set diagnostic_dest='/logs/db/' scope=spfile sid='*';
System altered.
SQL> alter system set audit_file_dest='/logs/db/admin/TST/adump' scope=spfile sid='*';
System altered.
SQL> host mkdir -p /logs/db/admin/TST/adump
SQL> !ls -al /logs/db/admin/TST/adump
total 0
drwxr-xr-x. 2 oracle dba  6 Aug 22 16:04 .
drwxr-xr-x. 3 oracle dba 19 Aug 22 16:04 ..
SQL> exit
[oracle@onerac DATASYNCXML]$ srvctl stop database -d TST
[oracle@onerac DATASYNCXML]$
[oracle@onerac DATASYNCXML]$
[oracle@onerac DATASYNCXML]$ ps -ef | grep pmon
oracle      4855       1  0 15:49 ?        00:00:00 asm_pmon_+ASM
oracle      7276    6662  0 16:05 pts/0    00:00:00 grep --color=auto pmon
[oracle@onerac DATASYNCXML]$
[oracle@onerac DATASYNCXML]$
[oracle@onerac DATASYNCXML]$ srvctl start database -d TST
[oracle@onerac DATASYNCXML]$
[oracle@onerac DATASYNCXML]$
[oracle@onerac DATASYNCXML]$ ps -ef | grep pmon
oracle      4855       1  0 15:49 ?        00:00:00 asm_pmon_+ASM
oracle      7351       1  0 16:06 ?        00:00:00 ora_pmon_TST
oracle      7819    6662  0 16:06 pts/0    00:00:00 grep --color=auto pmon
[oracle@onerac DATASYNCXML]$
[oracle@onerac DATASYNCXML]$ sqlplus "/as sysdba"

SQL*Plus: Release 19.0.0.0.0 - Production on Mon Aug 22 16:08:01 2022
Version 19.16.0.0.0
Copyright (c) 1982, 2022, Oracle.  All rights reserved.
Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.16.0.0.0
SQL> show parameter diagnostic_dest
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
diagnostic_dest                      string      /logs/db/
SQL>
SQL> show parameter audit_file_dest
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
audit_file_dest                      string      /logs/db/admin/TST/adump
SQL>
SQL>
SQL> exit
Disconnected from Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.16.0.0.0
[oracle@onerac DATASYNCXML]$

Oracle Listener

Oracle CRS

전통적인 MOS 화면과 OCI MOS화면

2022. 9. 5. 15:01

전통적인 MOS화면

OCI MOS 화면

PREV 1 2 3 4 5 6 7 ···9 NEXT