Re: 오늘의 마지막 문제. (백업과 복구 이수자 평가 2번 문제)

[22기_정민수]

sql@nero> 
shutdown immediate
cp -rp /u01/app/oracle/oradata/NERO/* /home/oracle/NEROSTBY/

sqlplus / as sysdba

select instance_name, status FROM v$instance;

sh>
mkdir -p /home/oracle/NERO/arch    # archive log
mkdir -p /home/oracle/NERO/flash   # flashback database log

mkdir -p /home/oracle/NEROSTBY/arch
mkdir -p /home/oracle/NEROSTBY/flash


sql@nero>
shutdown immediate
startup mount
alter database create standby controlfile as '$HOME/physical.ctl' reuse;
exit



sh>
cd
ls -l physical.ctl

cp $HOME/physical.ctl /home/oracle/NEROSTBY/disk1/ctrl1.ctl
cp $HOME/physical.ctl /home/oracle/NEROSTBY/disk2/ctrl2.ctl
cp $HOME/physical.ctl /home/oracle/NEROSTBY/disk3/ctrl3.ctl

cd $ORACLE_HOME/network/admin
mv listener.ora listener.bak   # 실제로 listener.ora가 없을 수도 있음
vi listener.ora

/*
LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.23.241)(PORT = 1521))
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
    )
  )

SID_LIST_LISTENER=
  (SID_LIST =
    (SID_DESC =
      (ORACLE_HOME=/u01/app/oracle/product/19.3.0/dbhome_1)
      (SID_NAME=NERO)
    )
    (SID_DESC =
      (ORACLE_HOME=/u01/app/oracle/product/19.3.0/dbhome_1)
      (SID_NAME=NEROSTBY)
    )
  )
  */
  
lsnrctl stop
lsnrctl start
  
  
cd $ORACLE_HOME/network/admin
vi tnsnames.ora

----------------------------------------------------------------------------------
NERO =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.23.241)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = NERO)
    )
  )

NEROSTBY =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.23.241)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = NEROSTBY)
    )
  )
----------------------------------------------------------------------------------  
  
cd $ORACLE_HOME/dbs
vi initNEROSTBY.ora
-----------------------------------------------------------------------------  
compatible=19.3.0.0
control_files = (/home/oracle/NEROSTBY/disk1/ctrl1.ctl ,
/home/oracle/NEROSTBY/disk2/ctrl2.ctl ,
/home/oracle/NEROSTBY/disk3/ctrl3.ctl )
db_block_size=8192
db_name=NERO
service_names=NEROSTBY
global_names=true
job_queue_processes=10
open_cursors=500
processes=100
remote_login_passwordfile='EXCLUSIVE'
memory_target=1G
undo_management='AUTO'
undo_tablespace='UNDOTBS1'
db_recovery_file_dest_size=4G
db_recovery_file_dest=/home/oracle/NEROSTBY/flash
db_unique_name=NEROSTBY
standby_file_management=auto
db_file_name_convert='/u01/app/oracle/oradata/NERO','/home/oracle/NEROSTBY'
log_file_name_convert='/u01/app/oracle/oradata/NERO','/home/oracle/NEROSTBY'
log_archive_dest_1='location=/home/oracle/NEROSTBY/arch valid_for=(all_logfiles, all_roles)'
log_archive_dest_2='service=NERO LGWR SYNC AFFIRM valid_for=(online_logfiles, primary_role)'
fal_server=NERO
fal_client=NEROSTBY
  
-----------------------------------------------------------------------------  
  
sql@nero>
alter database add standby logfile '/u01/app/oracle/oradata/NERO/disk1/standby01.log' size 100m;
alter database add standby logfile '/u01/app/oracle/oradata/NERO/disk1/standby02.log' size 100m;
 
 
 
sql@nerostby>
startup pfile=$ORACLE_HOME/dbs/initNEROSTBY.ora

SELECT value
  FROM v$diag_info
 WHERE name ='Diag Trace';
 
alter database add standby logfile '/home/oracle/NEROSTBY/disk1/standby01.log' size 100m;
alter database add standby logfile '/home/oracle/NEROSTBY/disk1/standby02.log' size 100m;

-- 각 서버의 status 확인. (아래 작업은 모두 mount 상태에서 진행)
select instance_name, status from v$instance;

shutdown immediate;
startup mount;

alter database flashback on;

-- MRP(Media Recovery Process) 확인 (초기에는 안 뜰 수 있음)
select process, status from v$managed_standby;

-- Managed Recovery 시작
recover managed standby database disconnect;

-- MRP가 뜨는지 재확인
select process, status from v$managed_standby;

PROCESS   STATUS
--------- ------------
ARCH      CONNECTED
DGRD      ALLOCATED
DGRD      ALLOCATED
ARCH      CONNECTED
ARCH      CONNECTED
ARCH      CONNECTED
MRP0      WAIT_FOR_LOG
DGRD      ALLOCATED


-- 인스턴스 상태 확인 (standby)
select status from v$instance;
-- STATUS: MOUNTED


sql@NERO >
alter database open;

select name from v$datafile;

create tablespace ts9000
datafile '/u01/app/oracle/oradata/NERO/disk2/ts9000.dbf' size 10m;

alter system switch logfile;