Oracle 69일차

by

[tbs 장애 시 복구]

0. tbs 만들고

create tablespace ts_test1 DATAFILE '+DATA/ts_new1.dbf' SIZE 5M AUTOEXTEND OFF;

1. recover_test3 table 만들고

create table recover_test3(no number) tablespace ts_test1;
insert into recover_test3 values(100);
insert into recover_test3 values(200);
insert into recover_test3 values(300);
commit;

2. online backup 후 log switch 5번

-- online backup
--1) begin backup
select 'alter tablespace '||TABLESPACE_NAME||' begin backup;'
  from dba_data_files;
 
--2) asmcmd cp
select '!asmcmd cp '''||FILE_NAME||''''||' /backup/oradb/hot'
  from dba_data_files;
  
--3) end backup
select 'alter tablespace '||TABLESPACE_NAME||' end backup;'
  from dba_data_files;

-- log스위치 5번
alter system switch logfile;

3. 시점 확인

select sysdate from dual;
-- 2025/01/16 19:00:09

4. 장애 발생

drop tablespace ts_test1 including contents and datafiles;

-- 로그 스위치 3번
alter system switch logfile;

5. scp로 parameter, data, archive, controlfile script

-- parameter file
create pfile from spfile;
exit
cd $ORACLE_HOME/dbs
scp initORADB1.ora oracle@oel8copy:/oracle19/recover/data

-- controlfile re-create script
alter database backup controlfile to trace as '/backup/control2.sql';
exit
cd /backup
scp control2.sql oracle@oel8copy:/oracle19/recover/data

-- datafile
cd /backup/oradb/hot
scp * oracle@oel8copy:/oracle19/recover/data

-- 필요한 archive file만 보내기 위해서 쿼리 이용해서 찾기
select name,
       sequence#,
       thread#,
       resetlogs_id,
       first_change#,
       round(block_size*blocks/1024/1024,2) as "size(mb)",
       completion_time
  from v$archived_log
 where completion_time between to_date('2025/01/16 18:59:00','YYYY/MM/DD HH24:MI:SS')  -- 최근 백업 시점
                           and to_date('2025/01/16 19:00:09','YYYY/MM/DD HH24:MI:SS')  -- 복구 시점
    or to_date('2025/01/16 19:00:09','YYYY/MM/DD HH24:MI:SS') between first_time and completion_time
 order by copletion_time;
-- 결과--
/oraarch/1_21_1190387366.dbf	21	1
/oraarch/1_22_1190387366.dbf	22	1
/oraarch/1_23_1190387366.dbf	23	1
/oraarch/1_24_1190387366.dbf	24	1
/oraarch/1_25_1190387366.dbf	25	1
/oraarch/2_12_1190387366.dbf	12	2
-- 보내기 (모든 노드에서!)
cd /oraarch
scp /oraarch/1_21_1190387366.dbf oracle@oel8copy:/oracle19/recover/arch
scp /oraarch/1_22_1190387366.dbf oracle@oel8copy:/oracle19/recover/arch
scp /oraarch/1_23_1190387366.dbf oracle@oel8copy:/oracle19/recover/arch
scp /oraarch/1_24_1190387366.dbf oracle@oel8copy:/oracle19/recover/arch
scp /oraarch/1_25_1190387366.dbf oracle@oel8copy:/oracle19/recover/arch
scp /oraarch/2_12_1190387366.dbf oracle@oel8copy:/oracle19/recover/arch

6. copydb 만들기

-- pfile 수정
cp /oracle19/recover/data/initORADB1.ora $ORACLE_HOME/dbs/initcopydb.ora
vi $ORACLE_HOME/dbs/initcopydb.ora
_no_recovery_through_resetlogs=true  -- 이것도 추가하기

-- controlfile script 수정 (resetlogs mode, ts_new1 datafile 추가
vi /oracle19/recover/data/control2.sql
CREATE CONTROLFILE REUSE DATABASE "ORADB" RESETLOGS  ARCHIVELOG
    MAXLOGFILES 192
    MAXLOGMEMBERS 3
    MAXDATAFILES 1024
    MAXINSTANCES 32
    MAXLOGHISTORY 292
LOGFILE
  GROUP 1 '/oracle19/recover/data/group_1.log'  SIZE 200M BLOCKSIZE 512,
  GROUP 2 '/oracle19/recover/data/group_2.log'  SIZE 200M BLOCKSIZE 512
-- STANDBY LOGFILE
DATAFILE
  '/oracle19/recover/data/system.261',
  '/oracle19/recover/data/sysaux.261',
  '/oracle19/recover/data/undotbs1.262',
  '/oracle19/recover/data/undotbs2.264',
  '/oracle19/recover/data/users.265',
  '/oracle19/recover/data/ts_test1.280.1190573923'
CHARACTER SET KO16MSWIN949
;

-- ORACLE_SID 변경
export ORACLE_SID=copydb

-- nomount
startup nomount

-- controlfile 생성
> @/oracle19/recover/data/control2.sql

7. recover & open

recover database until time '2025/01/16 19:00:09' using backup controlfile;

alter database open resetlogs;

select * from system.recover_test3;

8. dblink로 보내기

copy->RAC는 어제 글에 필기 되어있음.
RAC->copy는 copy쪽에 listener를 만들어야 돼서 복잡함.

<copy db>
1. 리스너 구성 listener.ora 에 추가
cd $ORACLE_HOME/network/admin
vi listener.ora
--아래 내용 추가--
LISTENER_COPYDB =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = oel8copy)(PORT = 1522))
    )
  )

2. tnsnames.ora 에 추가
COPYDB =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = oel8copy)(PORT = 1522))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = oradb)
    )
  )
LISTENER_COPYDB =
  (ADDRESS = (PROTOCOL = TCP)(HOST = oel8copy)(PORT = 1522))

3. listener start
lsnrctl start LISTENER_COPYDB

4. DB에 local listener 등록하기
-- pfile 환경이라서 동적변경이 불가능. 따라서 db 내리고 pfile에 로컬리스너 설정
shutdown immediate
vi initcopydb.ora
-------------------------------------
*.local_listener='LISTENER_COPYDB'
--------------------------------------
startup
-- 조회
select name, value from v$parameter where name = 'local_listener';
lsnrctl status LISTENER_COPYDB

<RAC>
1. dblink 만들기
create database link dblink_copydb connect to system identified by oracle using 'COPYDB';

2. tnsnames.ora 수정하기
COPYDB =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 172.16.124.134)(PORT = 1522))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = oradb)
    )
  )

3. 조회
select name, value from v$parameter@dblink_copydb where name = 'local_listener';
select * from system.recover_test3@dblink_copydb;

[ASM 디스크 교체 / 증설 작업]

select * from v$asm_diskgroup;

1. 디스크 추가

step1) 새 디스크 추가 (공유 디스크 설정)

edit~ -> add ->

용량이랑 allocate, sigle file 설정

알아서 잘 설정

그대로 0:1

.vmx 수정하기

-- 1번 .vmx에 있는 아래 내용을 복사해서
nvme0:1.fileName = "D:\rac19db\storage\disk10.vmdk"
nvme0:1.present = "TRUE"

-- 2번서버의 .vmx에 붙여넣기. 그러면 2번 서버에서도 저 디스크를 물 수 있다.

1번 서버만 기동 root로 접속

-- 디스크가 있나 확인 
#fdisk -l
Disk /dev/nvme0n2: 10 GiB, 10737418240 bytes, 20971520 sectors
Units: sectors of 1 * 512 = 512 bytes
Sector size (logical/physical): 512 bytes / 512 bytes
I/O size (minimum/optimal): 512 bytes / 512 bytes

step2) 물리, 논리 볼륨 생성 – root로 수행

pvcreate /dev/nvme0n2
vgcreate 19c2 /dev/nvme0n2
lvcreate -L 9G -n DATA2 19c2

-- 조회
ls -l /dev/19c2/DATA2

step3) 기존 파일 이동

oracleasm createdisk DATA02 /dev/19c2/DATA2 
oracleasm scandisks
oracleasm listdisks

step4) asmca로 공유 디스크 설정

xming 실행
putty oracle로 접속
$ asmca
disk group -> create -> DATA2 라고 이름 설정 -> external -> 체크하고 -> ok

step5) 2번 서버 기동, crs 확인

crsctl status res -t
새로 추가한 disk group online

step6) 새로 추가된 디스크그룹에 테이블스페이스 설정

create tablespace ts_test2 datafile size 5m;  -- db_create_file_dest에 등록된 디스크 그룹에 생성.
create tablespace ts_test2 datafile '+DATA2' size 5m;  -- 사용자가 원하는 디스크 그룹에 생성.

select * from dba_data_files;

2. 디스크 교체

DB를 구성하는 디스크가 노후화된 경우 새로운 디스크 구성, 기존 파일들을 새로운 디스크로 이동시키는 작업.

step1) controlfile 위치 변경 (online 중 spfile 변경)

alter system set control_files='+DATA2/ORADB/CONTROLFILE/current.257','+DATA2/ORADB/CONTROLFILE/current.258' scope=spfile;

step2) DB shutdown (모든 노드)

srvctl stop database -d oradb

step3) 물리적 파일 cp (+DATA -> +DATA2)

-- controlfile cp
asmcmd mkdir '+DATA2/ORADB/CONTROLFILE'
asmcmd cp '+DATA/ORADB/CONTROLFILE/current.257' '+DATA2/ORADB/CONTROLFILE/current.257'
asmcmd cp '+DATA/ORADB/CONTROLFILE/current.258' '+DATA2/ORADB/CONTROLFILE/current.258'

-- datafile cp
asmcmd mkdir '+DATA2/ORADB/DATAFILE'
asmcmd cp '+DATA/ORADB/DATAFILE/SYSAUX.261' '+DATA2/ORADB/DATAFILE/SYSAUX.261'
asmcmd cp '+DATA/ORADB/DATAFILE/SYSTEM.261' '+DATA2/ORADB/DATAFILE/SYSTEM.261'
asmcmd cp '+DATA/ORADB/DATAFILE/UNDOTBS1.262' '+DATA2/ORADB/DATAFILE/UNDOTBS1.262'
asmcmd cp '+DATA/ORADB/DATAFILE/UNDOTBS2.264' '+DATA2/ORADB/DATAFILE/UNDOTBS2.264'
asmcmd cp '+DATA/ORADB/DATAFILE/USERS.265' '+DATA2/ORADB/DATAFILE/USERS.265'
asmcmd cp '+DATA/ORADB/DATAFILE/TS_TEST1.280.1190573923' '+DATA2/ORADB/DATAFILE/TS_TEST1.280'

-- redo log file cp
asmcmd mkdir '+DATA2/ORADB/ONLINELOG'
asmcmd cp '+DATA/ORADB/ONLINELOG/group_1.274.1190387367' '+DATA2/ORADB/ONLINELOG/group_1.274'
asmcmd cp '+DATA/ORADB/ONLINELOG/group_2.276.1190387367' '+DATA2/ORADB/ONLINELOG/group_2.276'
asmcmd cp '+DATA/ORADB/ONLINELOG/group_3.277.1190387367' '+DATA2/ORADB/ONLINELOG/group_3.277'
asmcmd cp '+DATA/ORADB/ONLINELOG/group_4.278.1190387367' '+DATA2/ORADB/ONLINELOG/group_4.278'

step4) startup mount 1번만

startup mount

step5) datafile, redo log file rename

-- datafile rename
alter database rename file '+DATA/ORADB/DATAFILE/SYSAUX.261' to '+DATA2/ORADB/DATAFILE/SYSAUX.261';
alter database rename file '+DATA/ORADB/DATAFILE/SYSTEM.261' to '+DATA2/ORADB/DATAFILE/SYSTEM.261';
alter database rename file '+DATA/ORADB/DATAFILE/UNDOTBS1.262' to '+DATA2/ORADB/DATAFILE/UNDOTBS1.262';
alter database rename file '+DATA/ORADB/DATAFILE/UNDOTBS2.264' to '+DATA2/ORADB/DATAFILE/UNDOTBS2.264';
alter database rename file '+DATA/ORADB/DATAFILE/USERS.265' to '+DATA2/ORADB/DATAFILE/USERS.265';
alter database rename file '+DATA/ORADB/DATAFILE/TS_TEST1.280.1190573923' to '+DATA2/ORADB/DATAFILE/TS_TEST1.280';

-- redo log file rename
alter database rename file '+DATA/ORADB/ONLINELOG/group_1.274.1190387367' to '+DATA2/ORADB/ONLINELOG/group_1.274';
alter database rename file '+DATA/ORADB/ONLINELOG/group_2.276.1190387367' to '+DATA2/ORADB/ONLINELOG/group_2.276';
alter database rename file '+DATA/ORADB/ONLINELOG/group_3.277.1190387367' to '+DATA2/ORADB/ONLINELOG/group_3.277';
alter database rename file '+DATA/ORADB/ONLINELOG/group_4.278.1190387367' to '+DATA2/ORADB/ONLINELOG/group_4.278';

step6) open

alter database open;

step7) 2번 open

startup

step8) ASM 환경에서 spfile 이동

-- 1. pfile 생성
create pfile='/home/oracle/init.ora' from spfile='+DATA/ORADB/PARAMETERFILE/spfile';

-- 2. spfile 재생성
create spfile='+DATA2/ORADB/PARAMETERFILE/spfile' from pfile='/home/oracle/init.ora';

-- 3. db 재기동 (최근 spfile을 자동으로 물고 올라감)
srvctl stop database -d oradb
srvctl start database -d oradb

step9) temp file 복사

-- tempfile 확인
select tablespace_name, file_id, file_name from dba_temp_files;

1) tempfile이 존재하는 경우
SQL> alter database rename file '+DATA/ORADB/TEMPFILE/TEMP.263.1189816867' to '+DATA2/ORADB/TEMPFILE/TEMP.263';
asmcmd> cp +DATA/ORADB/TEMPFILE/TEMP.263.1189816867 +DATA2/ORADB/TEMPFILE/TEMP.263

2) tempfile이 존재하지 않는 경우 새로 생성
SQL> ALTER TABLESPACE TEMP ADD TEMPFILE '+DATA2/ORADB/TEMPFILE/temp01.dbf' SIZE 137363456 REUSE AUTOEXTEND ON NEXT 655360 MAXSIZE 32767M;

[ASM 환경에서의 password file 이동]

1. password file 확인

select name, value
from v$parameter
where name = 'remote_login_passwordfile';
-- EXCLUSIVE, SHARED : 사용증

2. password file 위치 확인

--1) single instance
ls $ORACLE_HOME/dbs/orapw*

--2) RAC(ASM) 환경
$ srvctl config database -d oradb | grep Password
----
Password file: +DATA/ORADB/PASSWORD/pwdoradb.256.1189816821
----
asmcmd> ls +DATA2/ORADB/PARAMETERFILE/pwd*

3. 삭제

srvctl modify database -d oradb -pwfile
srvctl config database -d oradb | grep Password
-- 비어있을 것.

password file;

4. 재생성

asmcmd> pwcreate --dbuniquename oradb --format 12 +DATA2/ORADB/PASSWORD/pwdoradb
$ srvctl config database -d oradb | grep Password

[기본 디스크 그룹 변경]

1. 기본 위치 변경 (동적 변경 가능)

alter system set db_create_file_dest='+DATA2';

2. 테이블 생성

create tablespace test1 datafile size 5m;

3. 테이블 생성 위치 확인

SELECT tablespace_name, file_id, file_name FROM dba_data_files;
TEST1	8	+DATA2/ORADB/DATAFILE/test1.272.1190646277
TEST1.272.1190646277
TS_TEST2.256.1190628703
ts_test1.283

drop tablespace TS_TEST1 including contents and datafiles;
drop tablespace TS_TEST2 including contents and datafiles;
drop tablespace TEST1 including contents and datafiles;

srvctl stop database -d ORADB
srvctl start database -d ORADB

Leave a Comment