[RAC 환경에서의 auto start]
아래에서 하는 명령어는 19c에서 많이 바뀐거다. 따라서 그 이전 버전을 만지게 되면 명령어 좀 찾아보기.
1. 서버 기동 시 crs 자동 기동
root 계정에서)
[root@oel8db1 ~]# crsctl config crs -- 현재 상태 확인
CRS-4622: Oracle High Availability Services autostart is enabled..
[root@oel8db1 ~]# crsctl disable crs -- crs 자동 기동 disable
CRS-4621: Oracle High Availability Services autostart is disabled.
[root@oel8db1 ~]# crsctl enable crs -- crs 자동 기동 enable
CRS-4622: Oracle High Availability Services autostart is enabled.2. crs 기동 시 DB 자동 open
oracle 계정에서)
[oracle@oel8db1 ~]$ srvctl config database -d oradb -a
Database unique name: ORADB
Database name: ORADB
Oracle home: /oracle/app/oracle/product/19c
Oracle user: oracle
Spfile: +DATA/ORADB/PARAMETERFILE/spfile.268.1189873561
Password file: +DATA/ORADB/PASSWORD/pwdoradb.256.1189872789
Domain:
Start options: open -- db start 시 open단계까지 가는 게 목표
Stop options: immediate -- db shutdown 시 immediate 옵션이 기본값
Database role: PRIMARY
Management policy: AUTOMATIC -- db 자동 기동(automatic / manual)
Server pools:
Disk Groups: DATA
Mount point paths:
Services:
Type: RAC
Start concurrency:
Stop concurrency:
Database is enabled
Database is individually enabled on nodes:
Database is individually disabled on nodes:
OSDBA group: dba
OSOPER group: dba
Database instances: ORADB1,ORADB2
Configured nodes: oel8db1,oel8db2
CSS critical: no
CPU count: 0
Memory target: 0
Maximum memory: 0
Default network number for database services:
Database is administrator managed
--------------------------------------------------------
[oracle@oel8db1 ~]$ srvctl modify database -d oradb -y manual
Management policy: MANUAL
[oracle@oel8db1 ~]$ srvctl modify database -d oradb -y automatic
Management policy: AUTOMATIC[oracle@oel8db1 ~]$ crsctl stat res ora.oradb.db -p | grep AUTO_START
AUTO_START=restore -- 수정 불가.
-- 만약 DB가 꺼졌던 상태였다면 그걸 기억해두고 재시작 시에도 DB 기동을 자동으로 하지 않음. 이전상태를 기억해서 그대로 만드는 느낌.
# DB 켜진 상태에서 crsctl stop crs -> crsctl start crs 했을 때 : ONLINE 이 목표고 ONLINE 상태
ora.oradb.db
1 ONLINE ONLINE oel8db1 Open,HOME=/oracle/ap
p/oracle/product/19c
,STABLE
2 ONLINE ONLINE oel8db2 Open,HOME=/oracle/ap
p/oracle/product/19c
,STABLE
# DB 꺼진 상태에서 crsctl stop crs -> crsctl start crs 했을 때 : OFFLINE 이 목표고 OFFLINE 상태
ora.oradb.db
1 OFFLINE OFFLINE Instance Shutdown,S T
ABLE
2 ONLINE ONLINE oel8db2 Open,HOME=/oracle/a p
p/oracle/product/19 c
,STABLE[백업]
1. online backup (= hot backup)
archive log mode 상태에서 백업 가능.
DB online 중 테이블스페이스, 데이터파일 단위로 백업.
begin backup 모드로 전환 후 백업 수행 후 end backup 으로 전환.
[RAC에서 archive log mode로 전환]
1. archive directory 생성 (양쪽 인스턴스 모두에)
1, 2 서버 모두에 root 계정으로 수행)
mkdir -p /oraarch
chown -R oracle:dba /oraarch2. arch destination 설정
select *
from v$parameter
where name like 'log_archive_dest_1';
alter system set log_archive_dest_1 = 'location=/oraarch' scope=spfile;3. DB shutdown (1,2 모두)
-- 한쪽 서버 oracle계정에서만 수행하면 두 서버 DB 모두 내려감.)
srvctl stop database -d oradb4. mount 단계 기동(1번 노드 진행) 후 archive log mode로 바꾸기
> startup mount
> alter database archivelog;
> shutdown immediate5. DB open 후 확인
srvctl start database -d oradb
> archive log list6. log switch
> alter system switch logfile;
> /
> /만약 alter system set 에서 실수해서 db 안 올라올 경우
spfile 이용해서 pfile 만들고 그거 수정해서 일단 db 올린 뒤에 다시 spfile 만들어서 복구하는 게 끝임!
> create pfile from spfile='+DATA/ORADB/PARAMETERFILE/spfile.268.1189873561';
-- spfile 경로 지정해줘야 pfile이 $ORACLE_HOME/dbs 경로에 만들어짐
vi $ORACLE_HOME/dbs/initORADB1.ora
-- log_archive_dest_1 이든 뭐든 문제있던 부분 수정하고 저장하기
> startup pfile='/oracle/app/oracle/product/19c/dbs/initORADB1.ora'
-- pfile 이용해서 startup
> create spfile='+DATA/ORADB/PARAMETERFILE/SPFILE' from pfile;
-- spfile 경로 지정 안하면 +DATA/spfileORADB1.ora 이렇게 생겨서 startup이 안됨.
> shutdown immediate
> startup
-- 하면 복구 끝![사전 환경 설정]
백업 디렉터리 생성(1번, root계정)
mkdir -p /backup/oradb/hot/20250110
mkdir -p /backup/oradb/cold/20250110
chown -R oracle:dba /backup[hot backup]
1. DB open
srvctl start database -d oradb2. begin backup
ALTER TABLESPACE SYSTEM BEGIN BACKUP;
ALTER TABLESPACE SYSAUX BEGIN BACKUP;
ALTER TABLESPACE UNDOTBS1 BEGIN BACKUP;
ALTER TABLESPACE UNDOTBS2 BEGIN BACKUP;
ALTER TABLESPACE USERS BEGIN BACKUP;3. cp (datafile만)
asmcmd cp '+DATA/ORADB/DATAFILE/SYSAUX.261.1189872801' /backup/oradb/hot/20250110
asmcmd cp '+DATA/ORADB/DATAFILE/SYSTEM.260.1189872799' /backup/oradb/hot/20250110
asmcmd cp '+DATA/ORADB/DATAFILE/UNDOTBS1.262.1189872801' /backup/oradb/hot/20250110
asmcmd cp '+DATA/ORADB/DATAFILE/UNDOTBS2.264.1189872805' /backup/oradb/hot/20250110
asmcmd cp '+DATA/ORADB/DATAFILE/USERS.265.1189872807' /backup/oradb/hot/202501104. end backup
ALTER TABLESPACE SYSTEM END BACKUP;
ALTER TABLESPACE SYSAUX END BACKUP;
ALTER TABLESPACE UNDOTBS1 END BACKUP;
ALTER TABLESPACE UNDOTBS2 END BACKUP;
ALTER TABLESPACE USERS END BACKUP;5. end backup 찍혔는지 안 찍혔는지 확인
select
ts.name as tablespace_name,
df.name as datafile_name,
df.file#,
b.status
from
v$tablespace ts,
v$datafile df,
v$backup b
where
ts.ts# = df.ts#
and df.file# = b.file#;
-- status 가 not active 여야 end backup이 찍힌 것.2. offline backup (= cold backup)
cluster on / db shutdown immediate 상태에서 백업.
crs on => asm on. asm 이 열려있어야 파일을 빼올 수 있음.
1. DB shutdown
srvctl stop database -d oradb2. controfile, datafile, redologfile, tempfile 모두 copy
asmcmd cp '+DATA/ORADB/CONTROLFILE/Current.257.1189872799' /backup/oradb/cold/20250110
asmcmd cp '+DATA/ORADB/DATAFILE/SYSAUX.261.1189872801' /backup/oradb/cold/20250110
asmcmd cp '+DATA/ORADB/DATAFILE/SYSTEM.260.1189872799' /backup/oradb/cold/20250110
asmcmd cp '+DATA/ORADB/DATAFILE/UNDOTBS1.262.1189872801' /backup/oradb/cold/20250110
asmcmd cp '+DATA/ORADB/DATAFILE/UNDOTBS2.264.1189872805' /backup/oradb/cold/20250110
asmcmd cp '+DATA/ORADB/DATAFILE/USERS.265.1189872807' /backup/oradb/cold/20250110
asmcmd cp '+DATA/ORADB/ONLINELOG/group_1.258.1189872799' /backup/oradb/cold/20250110
asmcmd cp '+DATA/ORADB/ONLINELOG/group_2.259.1189872799' /backup/oradb/cold/20250110
asmcmd cp '+DATA/ORADB/ONLINELOG/group_3.266.1189873559' /backup/oradb/cold/20250110
asmcmd cp '+DATA/ORADB/ONLINELOG/group_4.267.1189873559' /backup/oradb/cold/20250110
asmcmd cp '+DATA/ORADB/TEMPFILE/TEMP.263.1189872803' /backup/oradb/cold/20250110[restore – online backup]
1. DB shutdown (양쪽 노드)
srvctl stop database -d oradb2. 장애 발생
asmcmd rm '+DATA/oradb/datafile/*'
asmcmd mkdir '+DATA/oradb/DATAFILE' -- rm하면 디렉터리까지 다 날아가서3. restore – 뒤에 숫자들(이카네이션 넘버?)이 없어야 cp가 된다.
SYSAUX.261.1189872801 에서
가운데 세자리 : Relative File Number로, 데이터베이스에서 데이터파일을 식별하기 위한 고유 번호
뒤의 숫자들 : Unique FIle ID, 데이터파일에 대해 전역적으로 고유한 식별자. DB가 알아서 관리함.
asmcmd cp '/backup/oradb/hot/20250110/SYSAUX.261.1189872801' to '+DATA/ORADB/DATAFILE/SYSAUX.261'
asmcmd cp '/backup/oradb/hot/20250110/SYSTEM.260.1189872799' to '+DATA/ORADB/DATAFILE/SYSTEM.261'
asmcmd cp '/backup/oradb/hot/20250110/UNDOTBS1.262.1189872801' to '+DATA/ORADB/DATAFILE/UNDOTBS1.262'
asmcmd cp '/backup/oradb/hot/20250110/UNDOTBS2.264.1189872805' to '+DATA/ORADB/DATAFILE/UNDOTBS2.264'
asmcmd cp '/backup/oradb/hot/20250110/USERS.265.1189872807' to '+DATA/ORADB/DATAFILE/USERS.265'4. datafile rename – 뒤에 숫자들이 빠졌으니깐.
> startup -- mount 까지 밖에 못 감.
alter database rename file '+DATA/ORADB/DATAFILE/SYSAUX.261.1189872801' to '+DATA/ORADB/DATAFILE/SYSAUX.261';
alter database rename file '+DATA/ORADB/DATAFILE/SYSTEM.260.1189872799' to '+DATA/ORADB/DATAFILE/SYSTEM.261';
alter database rename file '+DATA/ORADB/DATAFILE/UNDOTBS1.262.1189872801' to '+DATA/ORADB/DATAFILE/UNDOTBS1.262';
alter database rename file '+DATA/ORADB/DATAFILE/UNDOTBS2.264.1189872805' to '+DATA/ORADB/DATAFILE/UNDOTBS2.264';
alter database rename file '+DATA/ORADB/DATAFILE/USERS.265.1189872807' to '+DATA/ORADB/DATAFILE/USERS.265';5. recover
recover database;
alter database open;[restore – offline backup]
1. DB shutdown
srvctl stop database -d oradb2. 장애 발생 (datafile, controlfile, redologfile)
asmcmd rm '+DATA/oradb/datafile/*'
asmcmd mkdir '+DATA/oradb/DATAFILE'
asmcmd rm '+DATA/oradb/controlfile/*'
asmcmd mkdir '+DATA/oradb/CONTROLFILE'
asmcmd rm '+DATA/oradb/onlinelog/*'
asmcmd mkdir '+DATA/oradb/ONLINELOG'3. restore (datafile, controlfile, redologfile)
asmcmd cp '/backup/oradb/cold/20250110/SYSAUX.261.1189872801' to '+DATA/ORADB/DATAFILE/SYSAUX.261'
asmcmd cp '/backup/oradb/cold/20250110/SYSTEM.260.1189872799' to '+DATA/ORADB/DATAFILE/SYSTEM.261'
asmcmd cp '/backup/oradb/cold/20250110/UNDOTBS1.262.1189872801' to '+DATA/ORADB/DATAFILE/UNDOTBS1.262'
asmcmd cp '/backup/oradb/cold/20250110/UNDOTBS2.264.1189872805' to '+DATA/ORADB/DATAFILE/UNDOTBS2.264'
asmcmd cp '/backup/oradb/cold/20250110/USERS.265.1189872807' to '+DATA/ORADB/DATAFILE/USERS.265'
asmcmd cp '/backup/oradb/cold/20250110/Current.257.1189872799' to '+DATA/ORADB/CONTROLFILE/Current.257'
asmcmd cp '/backup/oradb/cold/20250110/group_1.258.1189872799' to '+DATA/ORADB/ONLINELOG/group_1.258'
asmcmd cp '/backup/oradb/cold/20250110/group_2.259.1189872799' to '+DATA/ORADB/ONLINELOG/group_2.259'
asmcmd cp '/backup/oradb/cold/20250110/group_3.266.1189873559' to '+DATA/ORADB/ONLINELOG/group_3.266'
asmcmd cp '/backup/oradb/cold/20250110/group_4.267.1189873559' to '+DATA/ORADB/ONLINELOG/group_4.267'4. pfile 생성 및 수정 (controlfile 경로)
create pfile from spfile='+DATA/ORADB/PARAMETERFILE/SPFILE';
vi $ORACLE_HOME/dbs/initORADB1.ora
-------------------------------------------------------------------------
*.control_files='+DATA/ORADB/CONTROLFILE/current.257'5. mount
startup pfile='/oracle/app/oracle/product/19c/dbs/initORADB1.ora' -- mount까지만 가짐.6. rename (datafile, redologfile)
alter database rename file '+DATA/ORADB/DATAFILE/SYSAUX.261.1189872801' to '+DATA/ORADB/DATAFILE/SYSAUX.261';
alter database rename file '+DATA/ORADB/DATAFILE/SYSTEM.260.1189872799' to '+DATA/ORADB/DATAFILE/SYSTEM.261';
alter database rename file '+DATA/ORADB/DATAFILE/UNDOTBS1.262.1189872801' to '+DATA/ORADB/DATAFILE/UNDOTBS1.262';
alter database rename file '+DATA/ORADB/DATAFILE/UNDOTBS2.264.1189872805' to '+DATA/ORADB/DATAFILE/UNDOTBS2.264';
alter database rename file '+DATA/ORADB/DATAFILE/USERS.265.1189872807' to '+DATA/ORADB/DATAFILE/USERS.265';
alter database rename file '+DATA/ORADB/ONLINELOG/group_1.258.1189872799' to '+DATA/ORADB/ONLINELOG/group_1.258';
alter database rename file '+DATA/ORADB/ONLINELOG/group_2.259.1189872799' to '+DATA/ORADB/ONLINELOG/group_2.259';
alter database rename file '+DATA/ORADB/ONLINELOG/group_3.266.1189873559' to '+DATA/ORADB/ONLINELOG/group_3.266';
alter database rename file '+DATA/ORADB/ONLINELOG/group_4.267.1189873559' to '+DATA/ORADB/ONLINELOG/group_4.267';7. open
alter database open;8. spfile 생성
create spfile='+DATA/ORADB/PARAMETERFILE/SPFILE' from pfile;9. db 재기동
shutdown immediate
srvctl start database -d oradb[sqlplus / asmcmd 방향키 설정]
(root 계정 수행)
# yum -y install epel-release
# yum -y install rlwrap
# su - oracle
$ vi .bash_profile (아래 추가 및 수정)
alias ss='rlwrap sqlplus / as sysdba'
alias asmcmd='rlwrap asmcmd'
$ . .bash_profile