[MIG] Oracle 12c rac to single clond DB (수정중)

Test Scenario

- RAC DB(ASM)에서 특정 시점의 데이터를 SingleDB(Filesystem)으로 복구하는 내용

※ 변경내역
 - DB Name 변경
 - ASM > File system 변경

 

Source DB RAC 확인

SQL> select instance_name,version,status
  2  from gv$instance;

INSTANCE_NAME                    VERSION                            STATUS
-------------------------------- ---------------------------------- ------------------------
+ASM1                            12.2.0.1.0                         STARTED
+ASM2                            12.2.0.1.0                         STARTED

 

아카이브 모드 확인

SQL> archive log list;
Database log mode              Archive Mode
Automatic archival             Enabled
Archive destination            USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence     36
Next log sequence to archive   37
Current log sequence           37

 

Tablespace 확인

SQL> select tablespace_name,file_name
  2  from dba_data_files;

TABLESPACE_NAME                                              FILE_NAME
------------------------------------------------------------ ------------------------------
SYSTEM                                                       +DATA/RACDB/system01.dbf
SYSAUX                                                       +DATA/RACDB/sysaux01.dbf
UNDOTBS1                                                     +DATA/RACDB/undotbs01.dbf
UNDOTBS2                                                     +DATA/RACDB/undotbs02.dbf
USERS                                                        +DATA/RACDB/users01.dbf

 

백업용 테이블 스페이스 생성

SQL> create tablespace backup_tbs
  2  datafile '+DATA/RACDB/backup_tbs01.dbf'
  3  size 10G
  4  autoextend on next 100m;

 

전 후 비교를 위해 백업용 유저 생성 및 데이터 생성

SQL> create user backup identified by backup default tablespace backup_tbs;

User created.

SQL> grant dba to backup;

Grant succeeded.

create table test1 (c1 number, c2 number);

begin
for i in 1..10000
          loop
                   insert into test1 values (i, i+i);
          end loop;
end;
/

commit;

create table test2 (c1 number, c2 number);

begin
for i in 10001..100000
          loop
                   insert into test2 values (i, i+i);
          end loop;
end;
/

commit;

create table test3 (c1 number, c2 number);

begin
for i in 1..1000000
          loop
                   insert into test3 values (i, i+i);
          end loop;
end;
/

commit;

 

rman을 통해서 백업 진행 

 

[SOURCE]

- backup 본 저장할 디렉토리
oracle@ol79rac1[RACDB1]:/home/oracle$ mkdir rman

oracle@ol79rac1[RACDB1]:/home/oracle/rman$ rman target /

Recovery Manager: Release 12.2.0.1.0 - Production on Tue Apr 11 15:03:35 2023

Copyright (c) 1982, 2017, Oracle and/or its affiliates.  All rights reserved.

connected to target database: RACDB (DBID=1127918394)

RMAN>

-백업 전 환경 설정
- configure channel device type disk format '경로';
   - 특정한 경로를 백업 경로로 지정
   - %U : 파일명이 중복되지 않도록 unique한 번호로 파일 이름 생성하여 백업 수행
   - %T : 백업 날짜를 표기
etc) 
- configure channel device type to disk; 
   - 파라미터파일에 지정된 디폴트 경로를 백업 경로로 지정 
     (파라미터명 : db_recovery_file_dest)

RMAN> configure channel device type disk format '/home/oracle/rman/RACDB_%U_%T';

new RMAN configuration parameters:
CONFIGURE CHANNEL DEVICE TYPE DISK FORMAT   '/home/oracle/rman/RACDB_%U_%T';
new RMAN configuration parameters are successfully stored

- control file 경로 지정

RMAN> CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '/home/oracle/rman/RACDB_control_%F';

new RMAN configuration parameters:
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '/home/oracle/rman/RACDB_control_%F';
new RMAN configuration parameters are successfully stored

- rman 설정 확인

RMAN> show all;

RMAN configuration parameters for database with db_unique_name RACDB are:
CONFIGURE RETENTION POLICY TO REDUNDANCY 1; # default
CONFIGURE BACKUP OPTIMIZATION OFF; # default
CONFIGURE DEFAULT DEVICE TYPE TO DISK; # default
CONFIGURE CONTROLFILE AUTOBACKUP ON; # default
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '/home/oracle/rman/RACDB_control_%F';
CONFIGURE DEVICE TYPE DISK PARALLELISM 1 BACKUP TYPE TO BACKUPSET; # default
CONFIGURE DATAFILE BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
CONFIGURE ARCHIVELOG BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
CONFIGURE CHANNEL DEVICE TYPE DISK FORMAT   '/home/oracle/rman/RACDB_%U_%T';
CONFIGURE MAXSETSIZE TO UNLIMITED; # default
CONFIGURE ENCRYPTION FOR DATABASE OFF; # default
CONFIGURE ENCRYPTION ALGORITHM 'AES128'; # default
CONFIGURE COMPRESSION ALGORITHM 'BASIC' AS OF RELEASE 'DEFAULT' OPTIMIZE FOR LOAD TRUE ; # default
CONFIGURE RMAN OUTPUT TO KEEP FOR 7 DAYS; # default
CONFIGURE ARCHIVELOG DELETION POLICY TO NONE; # default
CONFIGURE SNAPSHOT CONTROLFILE NAME TO '/ora_engine/12c/dbhome_1/dbs/snapcf_RACDB1.f'; # default

-백업 진행
RMAN> backup database;

Starting backup at 11-APR-23
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=103 instance=RACDB1 device type=DISK
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00006 name=+DATA/RACDB/backup_tbs01.dbf
input datafile file number=00002 name=+DATA/RACDB/sysaux01.dbf
input datafile file number=00001 name=+DATA/RACDB/system01.dbf
input datafile file number=00003 name=+DATA/RACDB/undotbs01.dbf
input datafile file number=00004 name=+DATA/RACDB/undotbs02.dbf
input datafile file number=00005 name=+DATA/RACDB/users01.dbf
channel ORA_DISK_1: starting piece 1 at 11-APR-23
channel ORA_DISK_1: finished piece 1 at 11-APR-23
piece handle=/home/oracle/rman/RACDB_2c1pbats_1_1_20230411 tag=TAG20230411T151820 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:01:05
Finished backup at 11-APR-23

Starting Control File and SPFILE Autobackup at 11-APR-23
piece handle=/home/oracle/rman/RACDB_control_c-1127918394-20230411-00 comment=NONE
Finished Control File and SPFILE Autobackup at 11-APR-23

-백업 확인
1. list backup
2. 설정한 경로에 backup 파일

1. list backup

RMAN> list backup;

List of Backup Sets
===================


BS Key  Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
1       Full    18.88M     DISK        00:00:06     10-APR-23
        BP Key: 1   Status: AVAILABLE  Compressed: NO  Tag: TAG20230410T103337
        Piece Name: +RECO/RACDB/AUTOBACKUP/2023_04_10/s_1133778817.278.1133778821
  SPFILE Included: Modification time: 10-APR-23
  SPFILE db_unique_name: RACDB
  Control File Included: Ckp SCN: 3069696      Ckp time: 10-APR-23

BS Key  Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
2       Full    1.67G      DISK        00:01:01     11-APR-23
        BP Key: 2   Status: AVAILABLE  Compressed: NO  Tag: TAG20230411T151820
        Piece Name: /home/oracle/rman/RACDB_2c1pbats_1_1_20230411
  List of Datafiles in backup set 2
  File LV Type Ckp SCN    Ckp Time  Abs Fuz SCN Sparse Name
  ---- -- ---- ---------- --------- ----------- ------ ----
  1       Full 3130806    11-APR-23              NO    +DATA/RACDB/system01.dbf
  2       Full 3130806    11-APR-23              NO    +DATA/RACDB/sysaux01.dbf
  3       Full 3130806    11-APR-23              NO    +DATA/RACDB/undotbs01.dbf
  4       Full 3130806    11-APR-23              NO    +DATA/RACDB/undotbs02.dbf
  5       Full 3130806    11-APR-23              NO    +DATA/RACDB/users01.dbf
  6       Full 3130806    11-APR-23              NO    +DATA/RACDB/backup_tbs01.dbf

BS Key  Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
3       Full    18.88M     DISK        00:00:02     11-APR-23
        BP Key: 3   Status: AVAILABLE  Compressed: NO  Tag: TAG20230411T151927
        Piece Name: /home/oracle/rman/RACDB_control_c-1127918394-20230411-00
  SPFILE Included: Modification time: 11-APR-23
  SPFILE db_unique_name: RACDB
  Control File Included: Ckp SCN: 3130849      Ckp time: 11-APR-23

2. 사전에 설정한 경로에서 파일 확인

oracle@ol79rac1[RACDB1]:/home/oracle/rman$ pwd
/home/oracle/rman
oracle@ol79rac1[RACDB1]:/home/oracle/rman$ ls -l
total 1768952
-rw-r-----. 1 oracle dba 1791598592 Apr 11 15:19 RACDB_2c1pbats_1_1_20230411
-rw-r-----. 1 oracle dba   19808256 Apr 11 15:19 RACDB_control_c-1127918394-20230411-00

 

Source > Target 작업

 

[source]

- 현재 시간 확인을 통해 복구 시점 지정

SQL> select to_char(sysdate,'YYYY-MM-DD HH24:MI:SS') dt
  2  from dual;

DT
--------------------------------------
2023-04-11 15:25:14

-pfile 백업 및 target db에 전송

oracle@ol79rac1[RACDB1]:/ora_engine/12c/dbhome_1/dbs$ pwd
/ora_engine/12c/dbhome_1/dbs

oracle@ol79rac1[RACDB1]:/ora_engine/12c/dbhome_1/dbs$ ls -l
total 19268
...
-rw-r--r--. 1 oracle dba     1678 Apr 11 10:57 initRACDB1.ora
...

oracle@ol79rac1[RACDB1]:/ora_engine/12c/dbhome_1/dbs$ scp initRACDB1.ora root@192.168.100.100:/tmp
The authenticity of host '192.168.100.100 (192.168.100.100)' can't be established.
ECDSA key fingerprint is SHA256:rsudVRmbCAy2+dQqdXB/IjQbGI9VXTRCzmTkgcHOpSY.
ECDSA key fingerprint is MD5:b5:65:5f:78:9f:f9:50:e5:42:17:85:1a:60:ff:21:3d.
Are you sure you want to continue connecting (yes/no)? yes
Warning: Permanently added '192.168.100.100' (ECDSA) to the list of known hosts.
root@192.168.100.100's password:
initRACDB1.ora                                                                                                 100% 1678   560.7KB/s   00:00

 

[Target]

-CLONE DB 관련 폴더 생성

mkdir -p /home/clon_12c/rman (source 백업 파일)
mkdir -p /u01/app/oracle/oradata
mkdir -p /u01/app/oracle/arch
mkdir -p /u01/app/oracle/adump

-백업된 pfile 확인 및 변경 진행 (원본)
  1. init 명은 initCLONE.ora로 변경
  2. controlfile 위치 변경
  3. DBNAME은 기존과 동일, 다른 디렉토리 명은 새 서버에 맞게 설정
  4. RACDB1,2._ 부분 삭제 또는 주석처리
  5. audit_file_dest,dispatchers 변경
  6. local_listener 서버에 맞게 변경
  7. 파라미터 주석처리
      : cluster,db_create_file_dest
      
[clon_12c@oraclevm ~]$ cat initRACDB1.ora
RACDB2.__data_transfer_cache_size=0
RACDB1.__data_transfer_cache_size=0
RACDB2.__db_cache_size=2432696320
RACDB1.__db_cache_size=2432696320
RACDB2.__inmemory_ext_roarea=0
RACDB1.__inmemory_ext_roarea=0
RACDB2.__inmemory_ext_rwarea=0
RACDB1.__inmemory_ext_rwarea=0
RACDB2.__java_pool_size=16777216
RACDB1.__java_pool_size=16777216
RACDB2.__large_pool_size=33554432
RACDB1.__large_pool_size=33554432
RACDB1.__oracle_base='/ora_engine'#ORACLE_BASE set from environment
RACDB2.__oracle_base='/ora_engine'#ORACLE_BASE set from environment
RACDB2.__pga_aggregate_target=1056964608
RACDB1.__pga_aggregate_target=1056964608
RACDB2.__sga_target=3154116608
RACDB1.__sga_target=3154116608
RACDB2.__shared_io_pool_size=0
RACDB1.__shared_io_pool_size=0
RACDB2.__shared_pool_size=654311424
RACDB1.__shared_pool_size=654311424
RACDB2.__streams_pool_size=0
RACDB1.__streams_pool_size=0
*.audit_file_dest='/ora_engine/admin/RACDB/adump'
*.audit_trail='db'
*.cluster_database=true
*.compatible='12.2.0'
*.control_files='+DATA/RACDB/control01.ctl','+RECO/RACDB/control02.ctl'
*.db_block_size=8192
*.db_name='RACDB'
*.db_recovery_file_dest='+RECO'
*.db_recovery_file_dest_size=4500m
*.diagnostic_dest='/ora_engine'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=RACDBXDB)'
family:dw_helper.instance_mode='read-only'
RACDB1.instance_number=1
RACDB2.instance_number=2
*.local_listener='-oraagent-dummy-'
*.nls_language='AMERICAN'
*.nls_territory='AMERICA'
*.open_cursors=300
*.pga_aggregate_target=1000m
*.processes=300
*.remote_login_passwordfile='exclusive'
*.sga_target=2998m
RACDB2.thread=2
RACDB1.thread=1
*.undo_tablespace='UNDOTBS1'
RACDB1.undo_tablespace='UNDOTBS1'
RACDB2.undo_tablespace='UNDOTBS2'

--파라미터 변경

#RACDB1.__java_pool_size=16777216
#RACDB2.__large_pool_size=33554432
#RACDB1.__large_pool_size=33554432
#RACDB1.__oracle_base='/ora_engine'#ORACLE_BASE set from environment
#RACDB2.__oracle_base='/ora_engine'#ORACLE_BASE set from environment
#RACDB2.__pga_aggregate_target=1056964608
#RACDB1.__pga_aggregate_target=1056964608
#RACDB2.__sga_target=3154116608
#RACDB1.__sga_target=3154116608
#RACDB2.__shared_io_pool_size=0
#RACDB1.__shared_io_pool_size=0
#RACDB2.__shared_pool_size=654311424
#RACDB1.__shared_pool_size=654311424
#RACDB2.__streams_pool_size=0
#RACDB1.__streams_pool_size=0
*.audit_file_dest='/u01/app/oracle/CLONE/adump'
*.audit_trail='db'
#*.cluster_database=true
*.compatible='12.2.0'
#*.control_files='+DATA/RACDB/control01.ctl','+RECO/RACDB/control02.ctl'
*.control_files='/u01/app/oracle/oradata/control01.ctl','/u01/app/oracle/oradata/control02.ctl'
*.db_block_size=8192
*.db_name='RACDB'
#*.db_recovery_file_dest='+RECO'
#*.db_recovery_file_dest_size=4500m
#*.diagnostic_dest='/ora_engine'
#*.dispatchers='(PROTOCOL=TCP) (SERVICE=RACDBXDB)'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=CLONEDB)'
family:dw_helper.instance_mode='read-only'
#RACDB1.instance_number=1
#RACDB2.instance_number=2
#*.local_listener='-oraagent-dummy-'
*.log_archive_dest_1='location=/u01/app/oracle/CLONE/arch'
*.log_archive_format='%t_%s_%r.arc'
*.nls_language='AMERICAN'
*.nls_territory='AMERICA'
*.open_cursors=300
*.pga_aggregate_target=1000m
*.processes=300
*.remote_login_passwordfile='exclusive'
*.sga_target=2998m
#RACDB2.thread=2
#RACDB1.thread=1
*.undo_tablespace='UNDOTBS1'

listener 파라미터 내용 수정

[clon_12c@oraclevm admin]$ vi /u01/app/oracle/12c/dbhome_1/network/admin/tnsnames.ora

# The entries in tnsnames.ora are an alternative to using
# the names server with the onames adapter.
# They are a collection of aliases for the addresses that
# the listener(s) is(are) listening for a database or
# several databases.

# The following is the general syntax for any entry in
# a tnsnames.ora file. There could be several such entries
# tailored to the user's needs.

LISTENER_CLONE =
  (ADDRESS =
    (PROTOCOL = TCP)
     (HOST = oraclevm)
      (PORT = 2525))

 

[source]

source > target 서버로 rman 파일 전송

oracle@ol79rac1[RACDB1]:/home/oracle$ cd rman/
oracle@ol79rac1[RACDB1]:/home/oracle/rman$ ls
RACDB_2c1pbats_1_1_20230411  RACDB_control_c-1127918394-20230411-00
oracle@ol79rac1[RACDB1]:/home/oracle/rman$ scp RACDB_2c1pbats_1_1_20230411 clon_12c@192.168.100.100:/home/clon_12c/rman
RACDB_2g1pj666_1_1_20230414                                        100% 1709MB  19.3MB/s   01:28

oracle@ol79rac1[RACDB1]:/home/oracle/rman$ scp RACDB_control_c-1127918394-20230411-00 clon_12c@192.168.100.100:/home/clon_12c/rman
RACDB_control_c-1127918394-20230414-01                             100%   19MB  18.9MB/s   00:01

--arvhice file을 백업하지 않아 백입 진행 후 전송

oracle@ol79rac1[RACDB1]:/home/oracle$ rman target /

Recovery Manager: Release 12.2.0.1.0 - Production on Fri Apr 14 12:47:36 2023

Copyright (c) 1982, 2017, Oracle and/or its affiliates.  All rights reserved.

connected to target database: RACDB (DBID=1127918394)

RMAN> backup archivelog
2> from time 'sysdate - 2'
3> format '/home/oracle/rman/AR_%s_%p_%t';

Starting backup at 14-APR-23
current log archived
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=115 instance=RACDB1 device type=DISK
channel ORA_DISK_1: starting archived log backup set
channel ORA_DISK_1: specifying archived log(s) in backup set
input archived log thread=2 sequence=34 RECID=35 STAMP=1133978464
input archived log thread=1 sequence=45 RECID=34 STAMP=1133960439
input archived log thread=1 sequence=46 RECID=36 STAMP=1133978473
input archived log thread=1 sequence=47 RECID=37 STAMP=1134122628
input archived log thread=2 sequence=35 RECID=38 STAMP=1134122630
input archived log thread=2 sequence=36 RECID=39 STAMP=1134122632
input archived log thread=1 sequence=48 RECID=41 STAMP=1134132527
input archived log thread=2 sequence=37 RECID=40 STAMP=1134132521
channel ORA_DISK_1: starting piece 1 at 14-APR-23
channel ORA_DISK_1: finished piece 1 at 14-APR-23
piece handle=/home/oracle/rman/AR_78_1_1134132532 tag=TAG20230414T124851 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:26
Finished backup at 14-APR-23

Starting Control File and SPFILE Autobackup at 14-APR-23
piece handle=/home/oracle/rman/RACDB_control_c-1127918394-20230414-00 comment=NONE
Finished Control File and SPFILE Autobackup at 14-APR-23

oracle@ol79rac1[RACDB1]:/home/oracle/rman$ scp AR_82_1_1134139717 clon_12c@192.168.100.100:/home/clon_12c/rman
clon_12c@192.168.100.100's password:
AR_82_1_1134139717                                                                                                        100%  464MB  17.0MB/s   00:27

 

[Target]

-source로부터 전달받은 파일 확인

[clon_12c@oraclevm rman]$ ls -lrt
total 2425428
-rw-r-----. 1 clon_12c dba 1791598592 Apr 14 10:38 RACDB_2c1pbats_1_1_20230411
-rw-r-----. 1 clon_12c dba   19808256 Apr 14 12:51 RACDB_control_c-1127918394-20230411-00
-rw-r-----. 1 clon_12c dba   19841024 Apr 14 12:52 RACDB_control_c-1127918394-20230414-00
-rw-r-----. 1 clon_12c dba  652386816 Apr 14 12:52 AR_78_1_1134132532

-생성한 pfile copy-

[clon_12c@oraclevm ~]$ cp initCLONE.ora $ORACLE_HOME/dbs/

-sqlplus 접속 후 nomount로 기동-

[clon_12c@oraclevm ~]$ export ORACLE_SID=CLONE
[clon_12c@oraclevm ~]$ sqlplus / as sysdba

SQL*Plus: Release 12.2.0.1.0 Production on Fri Apr 14 13:14:02 2023

Copyright (c) 1982, 2016, Oracle.  All rights reserved.

Connected to an idle instance.

SQL> startup nomount
ORACLE instance started.

Total System Global Area 3154116608 bytes
Fixed Size                  8625656 bytes
Variable Size             771752456 bytes
Database Buffers         2365587456 bytes
Redo Buffers                8151040 bytes

-process 확인-

[clon_12c@oraclevm ~]$ ps -ef | grep pmon
clon_12c  8040     1  0 13:14 ?        00:00:00 ora_pmon_CLONE
clon_12c  8517  6979  0 13:16 pts/1    00:00:00 grep --color=auto pmon

[clon_12c@oraclevm rman]$ rman target /

Recovery Manager: Release 12.2.0.1.0 - Production on Fri Apr 14 13:33:46 2023

Copyright (c) 1982, 2017, Oracle and/or its affiliates.  All rights reserved.

connected to target database: RACDB (not mounted)

RMAN> restore controlfile from '/home/clon_12c/rman/RACDB_control_c-1127918394-20230414-01';

Starting restore at 14-APR-23
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=35 device type=DISK

channel ORA_DISK_1: restoring control file
channel ORA_DISK_1: restore complete, elapsed time: 00:00:03
output file name=/u01/app/oracle/oradata/control01.ctl
output file name=/u01/app/oracle/oradata/control02.ctl
Finished restore at 14-APR-23

-mount 상태로 변경-
RMAN> sql 'alter database mount';

sql statement: alter database mount
released channel: ORA_DISK_1

>파라미터는 initCLONE.ora이지만 dbname은 기존과 동일하므로 mount 상태까지 올라감

-상태 확인-

SQL> select instance_name,status
  2  from v$instance;

INSTANCE_NAME    STATUS
---------------- ------------
CLONE            MOUNTED

SQL>
SQL>
SQL>
SQL> select name
  2  from v$database;

NAME
---------
RACDB

-RMAN용 스크립트 작성을 위해 datafile 번호 확인-

[clon_12c@oraclevm rman]$ rman target /

Recovery Manager: Release 12.2.0.1.0 - Production on Fri Apr 14 13:37:02 2023

Copyright (c) 1982, 2017, Oracle and/or its affiliates.  All rights reserved.

connected to target database: RACDB (DBID=1127918394, not open)

RMAN> report schema;

using target database control file instead of recovery catalog
RMAN-06139: warning: control file is not current for REPORT SCHEMA
Report of database schema for database with db_unique_name RACDB

List of Permanent Datafiles
===========================
File Size(MB) Tablespace           RB segs Datafile Name
---- -------- -------------------- ------- ------------------------
1    0        SYSTEM               ***     +DATA/RACDB/system01.dbf
2    0        SYSAUX               ***     +DATA/RACDB/sysaux01.dbf
3    0        UNDOTBS1             ***     +DATA/RACDB/undotbs01.dbf
4    0        UNDOTBS2             ***     +DATA/RACDB/undotbs02.dbf
5    0        USERS                ***     +DATA/RACDB/users01.dbf
6    0        BACKUP_TBS           ***     +DATA/RACDB/backup_tbs01.dbf

List of Temporary Files
=======================
File Size(MB) Tablespace           Maxsize(MB) Tempfile Name
---- -------- -------------------- ----------- --------------------
1    20       TEMP                 32767       +DATA/RACDB/temp01.dbf

#source와 경로가 다를경우 발생한 에러

RMAN> run {
allocate channel ch1 device type disk format '/home/clon_12c/rman/data_%U_%T';
allocate channel ch2 device type disk format '/home/clon_12c/rman/data_%U_%T';

set newname for tempfile 1 to '/u01/app/oracle/oradata/temp01.dbf';
set newname for datafile 1 to '/u01/app/oracle/oradata/system01.dbf';
set newname for datafile 2 to '/u01/app/oracle/oradata/sysaux01.dbf';
set newname for datafile 3 to '/u01/app/oracle/oradata/undotbs01.dbf';
set newname for datafile 4 to '/u01/app/oracle/oradata/undotbs02.dbf';
set newname for datafile 5 to '/u01/app/oracle/oradata/user01.dbf';
set newname for datafile 6 to '/u01/app/oracle/oradata/backup_tbs01.dbf';

restore database;

switch datafile all;
switch tempfile all;
}

executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME

Starting restore at 14-APR-23
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=24 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 00001 to /u01/app/oracle/oradata/system01.dbf
channel ORA_DISK_1: restoring datafile 00002 to /u01/app/oracle/oradata/sysaux01.dbf
channel ORA_DISK_1: restoring datafile 00003 to /u01/app/oracle/oradata/undotbs01.dbf
channel ORA_DISK_1: restoring datafile 00004 to /u01/app/oracle/oradata/undotbs02.dbf
channel ORA_DISK_1: restoring datafile 00005 to /u01/app/oracle/oradata/user01.dbf
channel ORA_DISK_1: restoring datafile 00006 to /u01/app/oracle/oradata/backup_tbs01.dbf
channel ORA_DISK_1: reading from backup piece /home/oracle/rman/RACDB_2g1pj666_1_1_20230414
channel ORA_DISK_1: ORA-19870: error while restoring backup piece /home/oracle/rman/RACDB_2g1pj666_1_1_20230414
ORA-19505: failed to identify file "/home/oracle/rman/RACDB_2g1pj666_1_1_20230414"
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory
Additional information: 7

failover to previous backup

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 /u01/app/oracle/oradata/system01.dbf
channel ORA_DISK_1: restoring datafile 00002 to /u01/app/oracle/oradata/sysaux01.dbf
channel ORA_DISK_1: restoring datafile 00003 to /u01/app/oracle/oradata/undotbs01.dbf
channel ORA_DISK_1: restoring datafile 00004 to /u01/app/oracle/oradata/undotbs02.dbf
channel ORA_DISK_1: restoring datafile 00005 to /u01/app/oracle/oradata/user01.dbf
channel ORA_DISK_1: restoring datafile 00006 to /u01/app/oracle/oradata/backup_tbs01.dbf
channel ORA_DISK_1: reading from backup piece /home/oracle/rman/RACDB_2c1pbats_1_1_20230411
channel ORA_DISK_1: ORA-19870: error while restoring backup piece /home/oracle/rman/RACDB_2c1pbats_1_1_20230411
ORA-19505: failed to identify file "/home/oracle/rman/RACDB_2c1pbats_1_1_20230411"
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory
Additional information: 7

failover to previous backup

creating datafile file number=1 name=/u01/app/oracle/oradata/system01.dbf
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of restore command at 04/14/2023 15:53:38
ORA-01180: can not create datafile 1
ORA-01110: data file 1: '/u01/app/oracle/oradata/system01.dbf'

RMAN> catalog start with '/home/clon_12c/rman/';

searching for all files that match the pattern /home/clon_12c/rman/

List of Files Unknown to the Database
=====================================
File Name: /home/clon_12c/rman/RACDB_2g1pj666_1_1_20230414
File Name: /home/clon_12c/rman/RACDB_control_c-1127918394-20230414-01
File Name: /home/clon_12c/rman/AR_82_1_1134139717
File Name: /home/clon_12c/rman/old/RACDB_2c1pbats_1_1_20230411
File Name: /home/clon_12c/rman/old/RACDB_control_c-1127918394-20230411-00
File Name: /home/clon_12c/rman/old/RACDB_control_c-1127918394-20230414-00
File Name: /home/clon_12c/rman/old/AR_78_1_1134132532

Do you really want to catalog the above files (enter YES or NO)? yes
cataloging files...
cataloging done

List of Cataloged Files
=======================
File Name: /home/clon_12c/rman/RACDB_2g1pj666_1_1_20230414
File Name: /home/clon_12c/rman/RACDB_control_c-1127918394-20230414-01
File Name: /home/clon_12c/rman/AR_82_1_1134139717
File Name: /home/clon_12c/rman/old/RACDB_2c1pbats_1_1_20230411
File Name: /home/clon_12c/rman/old/RACDB_control_c-1127918394-20230411-00
File Name: /home/clon_12c/rman/old/RACDB_control_c-1127918394-20230414-00
File Name: /home/clon_12c/rman/old/AR_78_1_1134132532

-rman script 작성 및 실행으로 datafile restore 진행

RMAN> run {
allocate channel ch1 device type disk format '/home/clon_12c/rman/data_%U_%T';
allocate channel ch2 device type disk format '/home/clon_12c/rman/data_%U_%T';

set newname for tempfile 1 to '/u01/app/oracle/oradata/temp01.dbf';
set newname for datafile 1 to '/u01/app/oracle/oradata/system01.dbf';
set newname for datafile 2 to '/u01/app/oracle/oradata/sysaux01.dbf';
set newname for datafile 3 to '/u01/app/oracle/oradata/undotbs01.dbf';
set newname for datafile 4 to '/u01/app/oracle/oradata/undotbs02.dbf';
set newname for datafile 5 to '/u01/app/oracle/oradata/user01.dbf';
set newname for datafile 6 to '/u01/app/oracle/oradata/backup_tbs01.dbf';

restore database;

switch datafile all;
switch tempfile all;
}2> 3> 4> 5> 6> 7> 8> 9> 10> 11> 12> 13> 14> 15> 16> 17>

released channel: ORA_DISK_1
allocated channel: ch1
channel ch1: SID=24 device type=DISK

allocated channel: ch2
channel ch2: SID=25 device type=DISK

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

Starting restore at 14-APR-23

channel ch1: starting datafile backup set restore
channel ch1: specifying datafile(s) to restore from backup set
channel ch1: restoring datafile 00001 to /u01/app/oracle/oradata/system01.dbf
channel ch1: restoring datafile 00002 to /u01/app/oracle/oradata/sysaux01.dbf
channel ch1: restoring datafile 00003 to /u01/app/oracle/oradata/undotbs01.dbf
channel ch1: restoring datafile 00004 to /u01/app/oracle/oradata/undotbs02.dbf
channel ch1: restoring datafile 00005 to /u01/app/oracle/oradata/user01.dbf
channel ch1: restoring datafile 00006 to /u01/app/oracle/oradata/backup_tbs01.dbf
channel ch1: reading from backup piece /home/oracle/rman/RACDB_2g1pj666_1_1_20230414
channel ch1: errors found reading piece handle=/home/oracle/rman/RACDB_2g1pj666_1_1_20230414
channel ch1: failover to piece handle=/home/clon_12c/rman/RACDB_2g1pj666_1_1_20230414 tag=TAG20230414T144630
channel ch1: restored backup piece 1
channel ch1: restore complete, elapsed time: 00:10:24
Finished restore at 14-APR-23

datafile 1 switched to datafile copy
input datafile copy RECID=7 STAMP=1134144830 file name=/u01/app/oracle/oradata/system01.dbf
datafile 2 switched to datafile copy
input datafile copy RECID=8 STAMP=1134144831 file name=/u01/app/oracle/oradata/sysaux01.dbf
datafile 3 switched to datafile copy
input datafile copy RECID=9 STAMP=1134144840 file name=/u01/app/oracle/oradata/undotbs01.dbf
datafile 4 switched to datafile copy
input datafile copy RECID=10 STAMP=1134144842 file name=/u01/app/oracle/oradata/undotbs02.dbf
datafile 5 switched to datafile copy
input datafile copy RECID=11 STAMP=1134144842 file name=/u01/app/oracle/oradata/user01.dbf
datafile 6 switched to datafile copy
input datafile copy RECID=12 STAMP=1134144843 file name=/u01/app/oracle/oradata/backup_tbs01.dbf

renamed tempfile 1 to /u01/app/oracle/oradata/temp01.dbf in control file
released channel: ch1
released channel: ch2

-컨트롤 파일 백업

SQL> alter database backup controlfile to trace as '/home/clon_12c/recon.sql';

Database altered.

-DB 종료

SQL> shutdown immediate
ORA-01109: database not open

Database dismounted.
ORACLE instance shut down.

-pfile 변경
 db_name : RACDB > CLONE 
 
*.db_name='RACDB' => CLONE

-아까 만들어둔 controlfile 수정 및 재생성
  1. dbname 변경 필요
  2. Reuse resetlogs 를 resetlogs로 변경

########################################
[원본]
########################################

[clon_12c@oraclevm ~]$ cat recon.sql
-- The following are current System-scope REDO Log Archival related
-- parameters and can be included in the database initialization file.
--
-- LOG_ARCHIVE_DEST=''
-- LOG_ARCHIVE_DUPLEX_DEST=''
--
-- LOG_ARCHIVE_FORMAT=%t_%s_%r.arc
--
-- DB_UNIQUE_NAME="RACDB"
--
-- LOG_ARCHIVE_CONFIG='SEND, RECEIVE, NODG_CONFIG'
-- LOG_ARCHIVE_MAX_PROCESSES=4
-- STANDBY_FILE_MANAGEMENT=MANUAL
-- STANDBY_ARCHIVE_DEST=?#/dbs/arch
-- FAL_CLIENT=''
-- FAL_SERVER=''
--
-- LOG_ARCHIVE_DEST_1='LOCATION=/u01/app/oracle/arch'
-- LOG_ARCHIVE_DEST_1='OPTIONAL REOPEN=300 NODELAY'
-- LOG_ARCHIVE_DEST_1='ARCH NOAFFIRM NOVERIFY SYNC'
-- LOG_ARCHIVE_DEST_1='REGISTER NOALTERNATE NODEPENDENCY'
-- LOG_ARCHIVE_DEST_1='NOMAX_FAILURE NOQUOTA_SIZE NOQUOTA_USED NODB_UNIQUE_NAME'
-- LOG_ARCHIVE_DEST_1='VALID_FOR=(PRIMARY_ROLE,ONLINE_LOGFILES)'
-- LOG_ARCHIVE_DEST_STATE_1=ENABLE

--
-- Below are two sets of SQL statements, each of which creates a new
-- control file and uses it to open the database. The first set opens
-- the database with the NORESETLOGS option and should be used only if
-- the current versions of all online logs are available. The second
-- set opens the database with the RESETLOGS option and should be used
-- if online logs are unavailable.
-- The appropriate set of statements can be copied from the trace into
-- a script file, edited as necessary, and executed when there is a
-- need to re-create the control file.
--
--     Set #1. NORESETLOGS case
--
-- The following commands will create a new control file and use it
-- to open the database.
-- Data used by Recovery Manager will be lost.
-- Additional logs may be required for media recovery of offline
-- Use this only if the current versions of all online logs are
-- available.
-- WARNING! The current control file needs to be checked against
-- the datafiles to insure it contains the correct files. The
-- commands printed here may be missing log and/or data files.
-- Another report should be made after the database has been
-- successfully opened.

-- After mounting the created controlfile, the following SQL
-- statement will place the database in the appropriate
-- protection mode:
--  ALTER DATABASE SET STANDBY DATABASE TO MAXIMIZE PERFORMANCE

STARTUP NOMOUNT
CREATE CONTROLFILE REUSE DATABASE "RACDB" NORESETLOGS  ARCHIVELOG
    MAXLOGFILES 192
    MAXLOGMEMBERS 3
    MAXDATAFILES 1024
    MAXINSTANCES 32
    MAXLOGHISTORY 292
LOGFILE
  GROUP 1 '+DATA/RACDB/redo01.log'  SIZE 200M BLOCKSIZE 512,
  GROUP 2 '+DATA/RACDB/redo02.log'  SIZE 200M BLOCKSIZE 512,
  GROUP 3 '+DATA/RACDB/redo03.log'  SIZE 200M BLOCKSIZE 512,
  GROUP 4 '+DATA/RACDB/redo04.log'  SIZE 200M BLOCKSIZE 512
-- STANDBY LOGFILE
DATAFILE
  '/u01/app/oracle/oradata/system01.dbf',
  '/u01/app/oracle/oradata/sysaux01.dbf',
  '/u01/app/oracle/oradata/undotbs01.dbf',
  '/u01/app/oracle/oradata/undotbs02.dbf',
  '/u01/app/oracle/oradata/user01.dbf',
  '/u01/app/oracle/oradata/backup_tbs01.dbf'
CHARACTER SET AL32UTF8
;

-- Configure RMAN configuration record 1
VARIABLE RECNO NUMBER;
EXECUTE :RECNO := SYS.DBMS_BACKUP_RESTORE.SETCONFIG('CHANNEL','DEVICE TYPE DISK FORMAT   ''/home/oracle/rman/RACDB_%U_%T''');
-- Configure RMAN configuration record 2
VARIABLE RECNO NUMBER;
EXECUTE :RECNO := SYS.DBMS_BACKUP_RESTORE.SETCONFIG('CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE','DISK TO ''/home/oracle/rman/RACDB_control_%F''');
-- Commands to re-create incarnation table
-- Below log names MUST be changed to existing filenames on
-- disk. Any one log file from each branch can be used to
-- re-create incarnation records.
-- ALTER DATABASE REGISTER LOGFILE '/u01/app/oracle/arch/1_1_1130301626.arc';
-- Recovery is required if any of the datafiles are restored backups,
-- or if the last shutdown was not normal or immediate.
RECOVER DATABASE

-- All logs need archiving and a log switch is needed.
ALTER SYSTEM ARCHIVE LOG ALL;

-- Database can now be opened normally.
ALTER DATABASE OPEN;

-- Commands to add tempfiles to temporary tablespaces.
-- Online tempfiles have complete space information.
-- Other tempfiles may require adjustment.
ALTER TABLESPACE TEMP ADD TEMPFILE '/u01/app/oracle/oradata/temp01.dbf' REUSE;
-- End of tempfile additions.
--
--     Set #2. RESETLOGS case
--
-- The following commands will create a new control file and use it
-- to open the database.
-- Data used by Recovery Manager will be lost.
-- The contents of online logs will be lost and all backups will
-- be invalidated. Use this only if online logs are damaged.
-- WARNING! The current control file needs to be checked against
-- the datafiles to insure it contains the correct files. The
-- commands printed here may be missing log and/or data files.
-- Another report should be made after the database has been
-- successfully opened.

-- After mounting the created controlfile, the following SQL
-- statement will place the database in the appropriate
-- protection mode:
--  ALTER DATABASE SET STANDBY DATABASE TO MAXIMIZE PERFORMANCE

STARTUP NOMOUNT
CREATE CONTROLFILE REUSE DATABASE "RACDB" RESETLOGS  ARCHIVELOG
    MAXLOGFILES 192
    MAXLOGMEMBERS 3
    MAXDATAFILES 1024
    MAXINSTANCES 32
    MAXLOGHISTORY 292
LOGFILE
  GROUP 1 '+DATA/RACDB/redo01.log'  SIZE 200M BLOCKSIZE 512,
  GROUP 2 '+DATA/RACDB/redo02.log'  SIZE 200M BLOCKSIZE 512
-- STANDBY LOGFILE
DATAFILE
  '/u01/app/oracle/oradata/system01.dbf',
  '/u01/app/oracle/oradata/sysaux01.dbf',
  '/u01/app/oracle/oradata/undotbs01.dbf',
  '/u01/app/oracle/oradata/undotbs02.dbf',
  '/u01/app/oracle/oradata/user01.dbf',
  '/u01/app/oracle/oradata/backup_tbs01.dbf'
CHARACTER SET AL32UTF8
;

-- Configure RMAN configuration record 1
VARIABLE RECNO NUMBER;
EXECUTE :RECNO := SYS.DBMS_BACKUP_RESTORE.SETCONFIG('CHANNEL','DEVICE TYPE DISK FORMAT   ''/home/oracle/rman/RACDB_%U_%T''');
-- Configure RMAN configuration record 2
VARIABLE RECNO NUMBER;
EXECUTE :RECNO := SYS.DBMS_BACKUP_RESTORE.SETCONFIG('CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE','DISK TO ''/home/oracle/rman/RACDB_control_%F''');
-- Commands to re-create incarnation table
-- Below log names MUST be changed to existing filenames on
-- disk. Any one log file from each branch can be used to
-- re-create incarnation records.
-- ALTER DATABASE REGISTER LOGFILE '/u01/app/oracle/arch/1_1_1130301626.arc';
-- Recovery is required if any of the datafiles are restored backups,
-- or if the last shutdown was not normal or immediate.
RECOVER DATABASE USING BACKUP CONTROLFILE

-- Create log files for threads other than thread one.
ALTER DATABASE ADD LOGFILE THREAD 2
  GROUP 3 '+DATA/RACDB/redo03.log' SIZE 200M BLOCKSIZE 512 REUSE,
  GROUP 4 '+DATA/RACDB/redo04.log' SIZE 200M BLOCKSIZE 512 REUSE;

-- Database can now be opened zeroing the online logs.
ALTER DATABASE OPEN RESETLOGS;

-- Commands to add tempfiles to temporary tablespaces.
-- Online tempfiles have complete space information.
-- Other tempfiles may require adjustment.
ALTER TABLESPACE TEMP ADD TEMPFILE '/u01/app/oracle/oradata/temp01.dbf' REUSE;
-- End of tempfile additions.
--

########################################
[변경 후]
########################################

STARTUP NOMOUNT
CREATE CONTROLFILE SET DATABASE "CLONE" RESETLOGS  ARCHIVELOG
    MAXLOGFILES 192
    MAXLOGMEMBERS 3
    MAXDATAFILES 1024
    MAXINSTANCES 32
    MAXLOGHISTORY 292
LOGFILE
  GROUP 1 '+DATA/RACDB/redo01.log'  SIZE 200M BLOCKSIZE 512,
  GROUP 2 '+DATA/RACDB/redo02.log'  SIZE 200M BLOCKSIZE 512,
  GROUP 3 '+DATA/RACDB/redo03.log'  SIZE 200M BLOCKSIZE 512,
  GROUP 4 '+DATA/RACDB/redo04.log'  SIZE 200M BLOCKSIZE 512
-- STANDBY LOGFILE
DATAFILE
  '/u01/app/oracle/oradata/system01.dbf',
  '/u01/app/oracle/oradata/sysaux01.dbf',
  '/u01/app/oracle/oradata/undotbs01.dbf',
  '/u01/app/oracle/oradata/undotbs02.dbf',
  '/u01/app/oracle/oradata/user01.dbf',
  '/u01/app/oracle/oradata/backup_tbs01.dbf'
CHARACTER SET AL32UTF8
;

-기존 컨트롤 파일 제거 
 (rm보다 mv로 진행함)

[clon_12c@oraclevm oradata]$ mv control01.ctl control01.ctl_bk
[clon_12c@oraclevm oradata]$ mv control02.ctl control02.ctl_bk

-컨트롤파일 재생성 (recon.sql)

[clon_12c@oraclevm ~]$ sqlplus / as sysdba

SQL*Plus: Release 12.2.0.1.0 Production on Fri Apr 14 16:32:21 2023

Copyright (c) 1982, 2016, Oracle.  All rights reserved.

Connected to an idle instance.

SQL> @recon.sql