기존상태 확인
[oracle@ol7 DATASYNCXML]$ sqlplus "/as sysdba"
SQL*Plus: Release 11.2.0.4.0 Production on Thu Sep 29 09:34:42 2022
Copyright (c) 1982, 2013, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> --SPFILE 확인
SQL> show parameter spfile
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
spfile string /dev/raw/raw9
SQL>
SQL> --Control File 확인
SQL> select name from v$controlfile;
NAME
--------------------------------------------------------------------------------
/dev/raw/raw10
/dev/raw/raw11
SQL>
SQL> --Data File 확인
SQL> col FILE_NAME for a30
SQL> select TABLESPACE_NAME, FILE_NAME from dba_data_files;
TABLESPACE_NAME FILE_NAME
------------------------------ ------------------------------
SYSTEM /dev/raw/raw1
SYSAUX /dev/raw/raw2
UNDOTBS1 /dev/raw/raw5
USERS /dev/raw/raw4
TS_DATA /dev/raw/raw12
TS_IDX /dev/raw/raw13
6 rows selected.
SQL>
SQL> --Temp File 확인
SQL> select TABLESPACE_NAME, FILE_NAME from dba_temp_files;
TABLESPACE_NAME FILE_NAME
------------------------------ ------------------------------
TEMP /dev/raw/raw3
SQL>
SQL> --Redo Log 확인
SQL> SET LINESIZE 150
SQL> COLUMN member FORMAT A20
SQL> COLUMN first_change# FORMAT 99999999999999999999
SQL> COLUMN next_change# FORMAT 99999999999999999999
SQL> SELECT l.thread#,
SQL> lf.group#,
SQL> lf.member,
SQL> TRUNC(l.bytes/1024/1024) AS size_mb,
SQL> l.status,
SQL> l.archived,
SQL> lf.type,
SQL> lf.is_recovery_dest_file AS rdf,
SQL> l.sequence#,
SQL> l.first_change#,
SQL> l.next_change#
SQL> FROM v$logfile lf
SQL> JOIN v$log l ON l.group# = lf.group#
SQL> ORDER BY l.thread#,lf.group#, lf.member;
THREAD# GROUP# MEMBER SIZE_MB STATUS ARC TYPE RDF SEQUENCE# FIRST_CHANGE# NEXT_CHANGE#
---------- ---------- -------------------- ---------- ---------------- --- ------- --- ---------- --------------------- ---------------------
1 1 /dev/raw/raw6 50 CURRENT NO ONLINE NO 85 818528 281474976710655
1 2 /dev/raw/raw7 50 INACTIVE YES ONLINE NO 83 818196 818419
1 3 /dev/raw/raw8 50 INACTIVE YES ONLINE NO 84 818419 818528
SQL>
SQL> --Archive Log 확인
SQL> archive log list
Database log mode Archive Mode
Automatic archival Enabled
Archive destination /log/arch/
Oldest online log sequence 83
Next log sequence to archive 85
Current log sequence 85
SQL>
SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
[oracle@ol7 DATASYNCXML]$
RMAN를 이용한 Rawdevice를 Filesystem으로 변경 하기
사전 확인 / 주요 파일 백업 DB 종료
[oracle@ol7 DATASYNCXML]$ id
uid=54321(oracle) gid=54322(dba) groups=54322(dba) context=unconfined_u:unconfined_r:unconfined_t:s0-s0:c0.c1023
[oracle@ol7 DATASYNCXML]$ pwd
/home/oracle/DATASYNCXML
[oracle@ol7 DATASYNCXML]$ ls
[oracle@ol7 DATASYNCXML]$ sqlplus "/as sysdba"
SQL*Plus: Release 11.2.0.4.0 Production on Thu Sep 29 09:54:41 2022
Copyright (c) 1982, 2013, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> --Spfile 백업
SQL> create pfile='/home/oracle/DATASYNCXML/initTST.ora_org' from spfile;
File created.
SQL>
SQL> --Control File 백업
SQL> ALTER DATABASE BACKUP CONTROLFILE TO TRACE AS '/home/oracle/DATASYNCXML/TST_cr_controlfile.sql';
Database altered.
SQL>
SQL> --주요 Table Row수 확인
SQL> select count(*) from scott.big_emp;
COUNT(*)
----------
3440640
SQL>
SQL> --SCN Number 확인
SQL> select dbms_flashback.get_system_change_number from dual;
GET_SYSTEM_CHANGE_NUMBER
------------------------
819396
SQL>
SQL> --DB 종료
SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
[oracle@ol7 DATASYNCXML]$
RMAN으로 Rawdevice를 Filesystem으로 변경 하기(Temp/Redo/Control 제외)
[oracle@ol7 DATASYNCXML]$ id
uid=54321(oracle) gid=54322(dba) groups=54322(dba) context=unconfined_u:unconfined_r:unconfined_t:s0-s0:c0.c1023
[oracle@ol7 DATASYNCXML]$ sqlplus "/as sysdba"
SQL*Plus: Release 11.2.0.4.0 Production on Thu Sep 29 10:30:54 2022
Copyright (c) 1982, 2013, Oracle. All rights reserved.
Connected to an idle instance.
SQL> --DB를 Mounut 하기
SQL> startup mount;
ORACLE instance started.
Total System Global Area 1653518336 bytes
Fixed Size 2253784 bytes
Variable Size 1040190504 bytes
Database Buffers 603979776 bytes
Redo Buffers 7094272 bytes
Database mounted.
SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
[oracle@ol7 DATASYNCXML]$
[oracle@ol7 DATASYNCXML]$
[oracle@ol7 DATASYNCXML]$ rman target /
Recovery Manager: Release 11.2.0.4.0 - Production on Thu Sep 29 10:36:46 2022
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
connected to target database: TST (DBID=2257768950, not open)
RMAN> run {
2> allocate channel c1 type disk;
3> copy datafile '/dev/raw/raw1' to '/oradata/system/sytem01.dbf';
4> copy datafile '/dev/raw/raw2' to '/oradata/system/sysaux01.dbf';
5> copy datafile '/dev/raw/raw5' to '/oradata/system/unotodb01.dbf';
6> copy datafile '/dev/raw/raw4' to '/oradata/system/user01.dbf';
7> copy datafile '/dev/raw/raw12' to '/oradata/data/ts_data_01.dbf';
8> copy datafile '/dev/raw/raw13' to '/oradata/idx/ts_indx_01.dbf';
9> }
using target database control file instead of recovery catalog
allocated channel: c1
channel c1: SID=133 device type=DISK
Starting backup at 29-SEP-22
channel c1: starting datafile copy
input datafile file number=00001 name=/dev/raw/raw1
output file name=/oradata/system/sytem01.dbf tag=TAG20220929T103806 RECID=1 STAMP=1116671888
channel c1: datafile copy complete, elapsed time: 00:00:03
Finished backup at 29-SEP-22
Starting backup at 29-SEP-22
channel c1: starting datafile copy
input datafile file number=00002 name=/dev/raw/raw2
output file name=/oradata/system/sysaux01.dbf tag=TAG20220929T103809 RECID=2 STAMP=1116671891
channel c1: datafile copy complete, elapsed time: 00:00:03
Finished backup at 29-SEP-22
Starting backup at 29-SEP-22
channel c1: starting datafile copy
input datafile file number=00003 name=/dev/raw/raw5
output file name=/oradata/system/unotodb01.dbf tag=TAG20220929T103812 RECID=3 STAMP=1116671895
channel c1: datafile copy complete, elapsed time: 00:00:03
Finished backup at 29-SEP-22
Starting backup at 29-SEP-22
channel c1: starting datafile copy
input datafile file number=00004 name=/dev/raw/raw4
output file name=/oradata/system/user01.dbf tag=TAG20220929T103815 RECID=4 STAMP=1116671895
channel c1: datafile copy complete, elapsed time: 00:00:01
Finished backup at 29-SEP-22
Starting backup at 29-SEP-22
channel c1: starting datafile copy
input datafile file number=00005 name=/dev/raw/raw12
output file name=/oradata/data/ts_data_01.dbf tag=TAG20220929T103816 RECID=5 STAMP=1116671899
channel c1: datafile copy complete, elapsed time: 00:00:03
Finished backup at 29-SEP-22
Starting backup at 29-SEP-22
channel c1: starting datafile copy
input datafile file number=00006 name=/dev/raw/raw13
output file name=/oradata/idx/ts_indx_01.dbf tag=TAG20220929T103820 RECID=6 STAMP=1116671902
channel c1: datafile copy complete, elapsed time: 00:00:03
Finished backup at 29-SEP-22
released channel: c1
RMAN> exit
Recovery Manager complete.
[oracle@ol7 DATASYNCXML]$
[oracle@ol7 DATASYNCXML]$ sqlplus "/as sysdba"
SQL*Plus: Release 11.2.0.4.0 Production on Thu Sep 29 10:40:08 2022
Copyright (c) 1982, 2013, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> select status from v$instance;
STATUS
------------
MOUNTED
SQL>
SQL> --DBF File Rename
SQL> alter database rename file '/dev/raw/raw1' to '/oradata/system/sytem01.dbf';
Database altered.
SQL> alter database rename file '/dev/raw/raw2' to '/oradata/system/sysaux01.dbf';
Database altered.
SQL> alter database rename file '/dev/raw/raw5' to '/oradata/system/unotodb01.dbf';
Database altered.
SQL> alter database rename file '/dev/raw/raw4' to '/oradata/system/user01.dbf';
Database altered.
SQL> alter database rename file '/dev/raw/raw12' to '/oradata/data/ts_data_01.dbf';
Database altered.
SQL> alter database rename file '/dev/raw/raw13' to '/oradata/idx/ts_indx_01.dbf';
Database altered.
SQL>
SQL> --Database Open
SQL> alter database open;
Database altered.
SQL> select status from v$instance;
STATUS
------------
OPEN
SQL> select count(*) from scott.big_emp;
COUNT(*)
----------
3440640
SQL> select dbms_flashback.get_system_change_number from dual;
GET_SYSTEM_CHANGE_NUMBER
------------------------
819977
SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
[oracle@ol7 DATASYNCXML]$
Temp Tablespace Filesystem으로 생성 및 기존 Raw Device Temp Tablespace 삭제
[oracle@ol7 DATASYNCXML]$ sqlplus "/as sysdba"
SQL*Plus: Release 11.2.0.4.0 Production on Thu Sep 29 10:46:40 2022
Copyright (c) 1982, 2013, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL>
SQL> --사용자 기본 Temp Tablespace 확인
SQL> select username, TEMPORARY_TABLESPACE from dba_users where ACCOUNT_STATUS='OPEN';
USERNAME TEMPORARY_TABLESPACE
------------------------------ ------------------------------
SYSTEM TEMP
SYS TEMP
SCOTT TEMP
SQL>
SQL> --Temp Tablespace 상태 학인
SQL> col FILE_NAME for a20
SQL> select TABLESPACE_NAME, FILE_NAME from dba_temp_files;
TABLESPACE_NAME FILE_NAME
------------------------------ --------------------
TEMP /dev/raw/raw3
SQL>
SQL> --임시 Temp Tablespace 생성 및 기존 Raw Device Temp Tablespace 삭제
SQL> create temporary tablespace temp2 tempfile '/oradata/temp/imsi01.dbf' size 512m autoextend on;
Tablespace created.
SQL> alter database default temporary tablespace temp2;
Database altered.
SQL> select TABLESPACE_NAME, FILE_NAME from dba_temp_files;
TABLESPACE_NAME FILE_NAME
------------------------------ ------------------------------
TEMP /dev/raw/raw3
TEMP2 /oradata/temp/imsi01.dbf
SQL> select username, TEMPORARY_TABLESPACE from dba_users where ACCOUNT_STATUS='OPEN';
USERNAME TEMPORARY_TABLESPACE
------------------------------ ------------------------------
SYSTEM TEMP2
SYS TEMP2
SCOTT TEMP2
SQL>
SQL> drop tablespace temp;
Tablespace dropped.
SQL> select TABLESPACE_NAME, FILE_NAME from dba_temp_files;
TABLESPACE_NAME FILE_NAME
------------------------------ ------------------------------
TEMP2 /oradata/temp/imsi01.dbf
SQL>
SQL> --정식 Temp Tablespace 생성 및 임시 Temp Tablespace 삭제
SQL> create temporary tablespace temp tempfile '/oradata/temp/temp01.dbf' size 512m autoextend on;
Tablespace created.
SQL> alter database default temporary tablespace temp;
Database altered.
SQL> select TABLESPACE_NAME, FILE_NAME from dba_temp_files;
TABLESPACE_NAME FILE_NAME
------------------------------ ------------------------------
TEMP /oradata/temp/temp01.dbf
TEMP2 /oradata/temp/imsi01.dbf
SQL> select username, TEMPORARY_TABLESPACE from dba_users where ACCOUNT_STATUS='OPEN';
USERNAME TEMPORARY_TABLESPACE
------------------------------ ------------------------------
SYSTEM TEMP
SYS TEMP
SCOTT TEMP
SQL>
SQL> drop tablespace TEMP2;
Tablespace dropped.
SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
[oracle@ol7 DATASYNCXML]$
File system으로 Redolog 생성 및 기존 Raw Device Redolog 삭제
[oracle@ol7 DATASYNCXML]$ sqlplus "/as sysdba"
SQL*Plus: Release 11.2.0.4.0 Production on Thu Sep 29 11:09:32 2022
Copyright (c) 1982, 2013, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL>
SQL> --현 상태 학인
SQL> SET LINESIZE 150
SQL> COLUMN member FORMAT A50
SQL> SELECT l.thread#, lf.group#, lf.member, l.status, l.archived, lf.type
2 FROM v$logfile lf
3 JOIN v$log l ON l.group# = lf.group#
4 ORDER BY l.thread#,lf.group#, lf.member;
THREAD# GROUP# MEMBER STATUS ARC TYPE
---------- ---------- -------------------------------------------------- ---------------- --- -------
1 1 /dev/raw/raw6 CURRENT NO ONLINE
1 2 /dev/raw/raw7 INACTIVE YES ONLINE
1 3 /dev/raw/raw8 INACTIVE YES ONLINE
SQL>
SQL> --임시 Redo Log 생성
SQL> alter database add logfile group 11 '/oradata/redo1/imsi_redo11.log' size 200m;
Database altered.
SQL> alter database add logfile group 12 '/oradata/redo1/imsi_redo12.log' size 200m;
Database altered.
SQL> alter database add logfile group 13 '/oradata/redo1/imsi_redo13.log' size 200m;
Database altered.
SQL> SELECT l.thread#, lf.group#, lf.member, l.status, l.archived, lf.type
2 FROM v$logfile lf
3 JOIN v$log l ON l.group# = lf.group#
4 ORDER BY l.thread#,lf.group#, lf.member;
THREAD# GROUP# MEMBER STATUS ARC TYPE
---------- ---------- -------------------------------------------------- ---------------- --- -------
1 1 /dev/raw/raw6 CURRENT NO ONLINE
1 2 /dev/raw/raw7 INACTIVE YES ONLINE
1 3 /dev/raw/raw8 INACTIVE YES ONLINE
1 11 /oradata/redo1/imsi_redo11.log UNUSED YES ONLINE
1 12 /oradata/redo1/imsi_redo12.log UNUSED YES ONLINE
1 13 /oradata/redo1/imsi_redo13.log UNUSED YES ONLINE
6 rows selected.
SQL>
SQL> --기존 Rawdevice Redo Log 삭제
SQL> alter system switch logfile; --3~4회 반복
System altered.
SQL> SELECT l.thread#, lf.group#, lf.member, l.status, l.archived, lf.type
2 FROM v$logfile lf
3 JOIN v$log l ON l.group# = lf.group#
4 ORDER BY l.thread#,lf.group#, lf.member;
THREAD# GROUP# MEMBER STATUS ARC TYPE
---------- ---------- -------------------------------------------------- ---------------- --- -------
1 1 /dev/raw/raw6 INACTIVE YES ONLINE
1 2 /dev/raw/raw7 INACTIVE YES ONLINE
1 3 /dev/raw/raw8 INACTIVE YES ONLINE
1 11 /oradata/redo1/imsi_redo11.log CURRENT NO ONLINE
1 12 /oradata/redo1/imsi_redo12.log INACTIVE YES ONLINE
1 13 /oradata/redo1/imsi_redo13.log INACTIVE YES ONLINE
6 rows selected.
SQL>
SQL> --리두로그 상태가 INACTIVE 인 경우만 삭제 가능
SQL> alter database drop logfile group 1;
Database altered.
SQL> alter database drop logfile group 2;
Database altered.
SQL> alter database drop logfile group 3;
Database altered.
SQL> SELECT l.thread#, lf.group#, lf.member, l.status, l.archived, lf.type
2 FROM v$logfile lf
3 JOIN v$log l ON l.group# = lf.group#
4 ORDER BY l.thread#,lf.group#, lf.member;
THREAD# GROUP# MEMBER STATUS ARC TYPE
---------- ---------- -------------------------------------------------- ---------------- --- -------
1 11 /oradata/redo1/imsi_redo11.log CURRENT NO ONLINE
1 12 /oradata/redo1/imsi_redo12.log INACTIVE YES ONLINE
1 13 /oradata/redo1/imsi_redo13.log INACTIVE YES ONLINE
SQL>
SQL>
SQL> --정식 Redo log 생성
SQL> alter database add logfile group 1 '/oradata/redo1/redo11.log' size 200m;
Database altered.
SQL> alter database add logfile group 2 '/oradata/redo1/redo12.log' size 200m;
Database altered.
SQL> alter database add logfile group 3 '/oradata/redo1/redo13.log' size 200m;
Database altered.
SQL> SELECT l.thread#, lf.group#, lf.member, l.status, l.archived, lf.type
2 FROM v$logfile lf
3 JOIN v$log l ON l.group# = lf.group#
4 ORDER BY l.thread#,lf.group#, lf.member;
THREAD# GROUP# MEMBER STATUS ARC TYPE
---------- ---------- -------------------------------------------------- ---------------- --- -------
1 1 /oradata/redo1/redo11.log UNUSED YES ONLINE
1 2 /oradata/redo1/redo12.log UNUSED YES ONLINE
1 3 /oradata/redo1/redo13.log UNUSED YES ONLINE
1 11 /oradata/redo1/imsi_redo11.log CURRENT NO ONLINE
1 12 /oradata/redo1/imsi_redo12.log INACTIVE YES ONLINE
1 13 /oradata/redo1/imsi_redo13.log INACTIVE YES ONLINE
6 rows selected.
SQL>
SQL> --임시 Redo Log 삭제
SQL> alter system switch logfile; --3~4회 반복
System altered.
SQL> SELECT l.thread#, lf.group#, lf.member, l.status, l.archived, lf.type
2 FROM v$logfile lf
3 JOIN v$log l ON l.group# = lf.group#
4 ORDER BY l.thread#,lf.group#, lf.member;
THREAD# GROUP# MEMBER STATUS ARC TYPE
---------- ---------- -------------------------------------------------- ---------------- --- -------
1 1 /oradata/redo1/redo11.log INACTIVE YES ONLINE
1 2 /oradata/redo1/redo12.log CURRENT NO ONLINE
1 3 /oradata/redo1/redo13.log INACTIVE YES ONLINE
1 11 /oradata/redo1/imsi_redo11.log INACTIVE YES ONLINE
1 12 /oradata/redo1/imsi_redo12.log INACTIVE YES ONLINE
1 13 /oradata/redo1/imsi_redo13.log INACTIVE YES ONLINE
6 rows selected.
SQL>
SQL> --리두로그 상태가 INACTIVE 인 경우만 삭제 가능
SQL> alter database drop logfile group 11;
Database altered.
SQL> alter database drop logfile group 12;
Database altered.
SQL> alter database drop logfile group 13;
Database altered.
SQL> SELECT l.thread#, lf.group#, lf.member, l.status, l.archived, lf.type
2 FROM v$logfile lf
3 JOIN v$log l ON l.group# = lf.group#
4 ORDER BY l.thread#,lf.group#, lf.member;
THREAD# GROUP# MEMBER STATUS ARC TYPE
---------- ---------- -------------------------------------------------- ---------------- --- -------
1 1 /oradata/redo1/redo11.log INACTIVE YES ONLINE
1 2 /oradata/redo1/redo12.log CURRENT NO ONLINE
1 3 /oradata/redo1/redo13.log INACTIVE YES ONLINE
SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
[oracle@ol7 DATASYNCXML]$
Control File/Spfile을 File systemd로 변경 하기
[oracle@ol7 DATASYNCXML]$ sqlplus "/as sysdba"
SQL*Plus: Release 11.2.0.4.0 Production on Thu Sep 29 11:34:55 2022
Copyright (c) 1982, 2013, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> --Control File 상태 확인
SQL> select name from v$controlfile;
NAME
--------------------------------------------------------------------------------
/dev/raw/raw10
/dev/raw/raw11
SQL> --DB 중지
SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
[oracle@ol7 DATASYNCXML]$
[oracle@ol7 DATASYNCXML]$
[oracle@ol7 DATASYNCXML]$ sqlplus "/as sysdba"
SQL*Plus: Release 11.2.0.4.0 Production on Thu Sep 29 11:37:46 2022
Copyright (c) 1982, 2013, Oracle. All rights reserved.
Connected to an idle instance.
SQL> --DB NOMOUNT
SQL> startup nomount;
ORACLE instance started.
Total System Global Area 1653518336 bytes
Fixed Size 2253784 bytes
Variable Size 1040190504 bytes
Database Buffers 603979776 bytes
Redo Buffers 7094272 bytes
SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
[oracle@ol7 DATASYNCXML]$
[oracle@ol7 DATASYNCXML]$ --rman을 이용하여 Control File Resotre
[oracle@ol7 DATASYNCXML]$ rman target /
Recovery Manager: Release 11.2.0.4.0 - Production on Thu Sep 29 11:40:17 2022
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
connected to target database: TST (not mounted)
RMAN> restore controlfile to '/oradata/control/control01.ctl' from '/dev/raw/raw10';
Starting restore at 29-SEP-22
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=134 device type=DISK
channel ORA_DISK_1: copied control file copy
Finished restore at 29-SEP-22
RMAN> quit
Recovery Manager complete.
[oracle@ol7 DATASYNCXML]$ --Control File을 다중화를 위해서 복사
[oracle@ol7 DATASYNCXML]$ ls -al /oradata/control/control01.ctl
-rw-r-----. 1 oracle dba 10043392 Sep 29 11:40 /oradata/control/control01.ctl
[oracle@ol7 DATASYNCXML]$ cp /oradata/control/control01.ctl /oradata/control/control02.ctl
[oracle@ol7 DATASYNCXML]$ ls -al /oradata/control/control*
-rw-r-----. 1 oracle dba 10043392 Sep 29 11:40 /oradata/control/control01.ctl
-rw-r-----. 1 oracle dba 10043392 Sep 29 11:41 /oradata/control/control02.ctl
[oracle@ol7 DATASYNCXML]$ sqlplus "/as sysdba"
SQL*Plus: Release 11.2.0.4.0 Production on Thu Sep 29 11:41:23 2022
Copyright (c) 1982, 2013, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> --DB Shutdown
SQL> shutdown immediate
ORA-01507: database not mounted
ORACLE instance shut down.
SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
[oracle@ol7 DATASYNCXML]$
[oracle@ol7 DATASYNCXML]$ pwd
/home/oracle/DATASYNCXML
[oracle@ol7 DATASYNCXML]$ ls
initTST.ora_org TST_cr_controlfile.sql
[oracle@ol7 DATASYNCXML]$ cp initTST.ora_org imsiTST.ora
[oracle@ol7 DATASYNCXML]$ vi imsiTST.ora
[oracle@ol7 DATASYNCXML]$ more imsiTST.ora
*.audit_file_dest='/log/dblog/admin/TST/adump'
*.audit_trail='none'
*.compatible='11.2.0.4.0'
#*.control_files='/dev/raw/raw10','/dev/raw/raw11' --주석 처리
*.control_files='/oradata/control/control01.ctl','/oradata/control/control02.ctl' --변경 Control File 추가
*.db_block_size=8192
*.db_domain=''
*.db_name='TST'
*.diagnostic_dest='/log/dblog/'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=TSTXDB)'
*.log_archive_dest_1='location=/log/arch/'
*.log_archive_format='TST_%t_%s_%r.log'
*.memory_target=1645215744
*.open_cursors=300
*.processes=150
*.remote_login_passwordfile='EXCLUSIVE'
*.undo_tablespace='UNDOTBS1'
[oracle@ol7 DATASYNCXML]$
[oracle@ol7 DATASYNCXML]$ sqlplus "/as sysdba"
SQL*Plus: Release 11.2.0.4.0 Production on Thu Sep 29 11:45:59 2022
Copyright (c) 1982, 2013, Oracle. All rights reserved.
Connected to an idle instance.
SQL> --임시 Pfile을 이용하여 DB Startup
SQL> startup pfile='/home/oracle/DATASYNCXML/imsiTST.ora';
ORACLE instance started.
Total System Global Area 1653518336 bytes
Fixed Size 2253784 bytes
Variable Size 1006636072 bytes
Database Buffers 637534208 bytes
Redo Buffers 7094272 bytes
Database mounted.
Database opened.
SQL>
SQL> --Spfile 생성
SQL> create spfile from pfile='/home/oracle/DATASYNCXML/imsiTST.ora';
File created.
SQL> !ls -al /u01/app/oracle/product/11.2.0.4/dbs/sp*
-rw-r-----. 1 oracle dba 2560 Sep 29 11:47 /u01/app/oracle/product/11.2.0.4/dbs/spfileTST.ora
SQL>
SQL> --DB Restart
SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup
ORACLE instance started.
Total System Global Area 1653518336 bytes
Fixed Size 2253784 bytes
Variable Size 1006636072 bytes
Database Buffers 637534208 bytes
Redo Buffers 7094272 bytes
Database mounted.
Database opened.
SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
[oracle@ol7 DATASYNCXML]$
최종 상태 확인
[oracle@ol7 DATASYNCXML]$ sqlplus "/as sysdba"
SQL*Plus: Release 11.2.0.4.0 Production on Thu Sep 29 11:50:35 2022
Copyright (c) 1982, 2013, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL>
SQL> --SPFILE 확인
SQL> show parameter spfile
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
spfile string /u01/app/oracle/product/11.2.0.4/dbs/spfileTST.ora
SQL>
SQL> --Control File 확인
SQL> select name from v$controlfile;
NAME
--------------------------------------------------------------------------------
/oradata/control/control01.ctl
/oradata/control/control02.ctl
SQL>
SQL> --Data File 확인
SQL> col FILE_NAME for a30
SQL> select TABLESPACE_NAME, FILE_NAME from dba_data_files;
TABLESPACE_NAME FILE_NAME
------------------------------ ------------------------------
SYSTEM /oradata/system/sytem01.dbf
SYSAUX /oradata/system/sysaux01.dbf
UNDOTBS1 /oradata/system/unotodb01.dbf
USERS /oradata/system/user01.dbf
TS_DATA /oradata/data/ts_data_01.dbf
TS_IDX /oradata/idx/ts_indx_01.dbf
6 rows selected.
SQL>
SQL> --Temp File 확인
SQL> select TABLESPACE_NAME, FILE_NAME from dba_temp_files;
TABLESPACE_NAME FILE_NAME
------------------------------ ------------------------------
TEMP /oradata/temp/temp01.dbf
SQL>
SQL> --Redo Log 확인
SQL> SET LINESIZE 200
SQL> COLUMN member FORMAT A50
SQL> COLUMN first_change# FORMAT 99999999999999999999
SQL> COLUMN next_change# FORMAT 99999999999999999999
SQL> SELECT l.thread#,lf.group#,lf.member,TRUNC(l.bytes/1024/1024) AS size_mb,l.status,l.archived,lf.type,
2 lf.is_recovery_dest_file AS rdf,l.sequence#,l.first_change#,l.next_change#
3 FROM v$logfile lf
4 JOIN v$log l ON l.group# = lf.group#
5 ORDER BY l.thread#,lf.group#, lf.member;
THREAD# GROUP# MEMBER SIZE_MB STATUS ARC TYPE RDF SEQUENCE# FIRST_CHANGE# NEXT_CHANGE#
---------- ---------- -------------------------------------------------- ---------- ---------------- --- ------- --- ---------- --------------------- ---------------------
1 1 /oradata/redo1/redo11.log 200 INACTIVE YES ONLINE NO 105 822039 822042
1 2 /oradata/redo1/redo12.log 200 CURRENT NO ONLINE NO 106 822042 281474976710655
1 3 /oradata/redo1/redo13.log 200 INACTIVE YES ONLINE NO 101 822002 822005
SQL>
SQL> --Archive Log 확인
SQL> archive log list
Database log mode Archive Mode
Automatic archival Enabled
Archive destination /log/arch/
Oldest online log sequence 101
Next log sequence to archive 106
Current log sequence 106
SQL>
SQL> --주요 Table Row수 확인
SQL> select count(*) from scott.big_emp;
COUNT(*)
----------
3440640
SQL>
SQL> --SCN Number 확인
SQL> select dbms_flashback.get_system_change_number from dual;
GET_SYSTEM_CHANGE_NUMBER
------------------------
823169
SQL>
SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
[oracle@ol7 DATASYNCXML]$
참고문서
Raw Device 에서 File System 으로 데이타파일 변환하기 (문서 ID 1907460.1)