Oracle standby

Еще раз про Oracle standby

Oracle Data Guard: Развертывание физического Standby средствами Oracle Database

MURGBPROD — primary база
MURGBSTAN — standby база

На сервере MURGBPROD:
alter database force logging;

Создаем standby redo logs:
alter database add standby logfile group 4 ‘C:\oradata\MURGB\stnbylog01.log’ size 50m;
alter database add standby logfile group 5 ‘C:\oradata\MURGB\stnbylog02.log’ size 50m;
alter database add standby logfile group 6 ‘C:\oradata\MURGB\stnbylog03.log’ size 50m;

create pfile=’C:\backup\pfilePROD.ora’ from spfile;

db_unique_name=’MURGBPROD’
log_archive_config=’dg_config=(MURGBPROD,MURGBSTAN)’
log_archive_dest_1=’SERVICE=MURGBSTAN LGWR ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) db_unique_name=MURGBSTAN’
log_archive_dest_2=’LOCATION=C:\oradata\MURGB\archive VALID_FOR=(ALL_LOGFILES,ALL_ROLES) db_unique_name=MURGBPROD’
log_archive_dest_state_1=ENABLE
log_archive_dest_state_2=ENABLE
fal_client=’MURGBPROD’
fal_server=’MURGBSTAN’
standby_file_management=’AUTO’

shutdown immediate;
startup nomount pfile=’C:\backup\pfilePROD.ora’;
create spfile from pfile=’C:\backup\pfilePROD.ora’;
shutdown immediate;
startup;

RMAN> backup current controlfile for standby format ‘C:\backup\standbycontrol.ctl’;
RMAN> change archivelog all crosscheck;
RMAN> run
{
allocate channel c1 device type disk format ‘C:\backup\%u’;
backup database plus archivelog;
}

Добавляем в оба tnsnames.ora:

MURGBPROD =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.2.192)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SID = MURGB)
)
)

MURGBSTAN =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.2.179)(PORT = 1521))
)
(CONNECT_DATA =
(SID = MURGB)
)
)

Добавляем в оба listener.ora:

(SID_DESC =
(ORACLE_HOME = C:\app\product\11.2.0\dbhome_1)
(SID_NAME = MURGB)
)

На сервере MURGBSTAN:

pfileSTAN.ora:

db_unique_name=’MURGBSTAN’
log_archive_config=’dg_config=(MURGBPROD,MURGBSTAN)’
log_archive_dest_1=’SERVICE=MURGBPROD LGWR ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) db_unique_name=MURGBPROD’
log_archive_dest_2=’LOCATION=C:\oradata\MURGB\archive VALID_FOR=(ALL_LOGFILES,ALL_ROLES) db_unique_name=MURGBSTAN’
log_archive_dest_state_1=ENABLE
log_archive_dest_state_2=ENABLE
fal_client=’MURGBSTAN’
fal_server=’MURGBPROD’
standby_file_management=’AUTO’

startup nomount pfile=’C:\backup\pfileSTAN.ora’;
create spfile from pfile=’C:\backup\pfileSTAN.ora’;
shutdown immediate;
startup nomount;

На сервере MURGBPROD:
RMAN> connect auxiliary sys/sys@MURGBSTAN
RMAN> duplicate target database for standby nofilenamecheck dorecover;

На сервере MURGBSTAN:
Создаем папку C:\oradata\MURGB\archive
alter database recover managed standby database using current logfile disconnect;

Проверка
На сервере MURGBPROD:
SQL> alter system switch logfile;
SQL> archive log list
SQL> select max(sequence#) from v$archived_log;
На сервере MURGBSTAN:
select max(sequence#) from v$archived_log;

select recovery_mode from v$archive_dest_status;
select name,open_mode,log_mode from v$database;

select sequence#, first_time, next_time, applied FROM v$archived_log ORDER BY sequence#;
select process, status, thread#, sequence#, block#, blocks from v$managed_standby;

Если ошибка на сервере MURGBSTAN:
alter database recover managed standby database using current logfile disconnect;
ORA-01665: control file is not a standby control file
shutdown immediate;
startup mount;
ALTER DATABASE CONVERT TO PHYSICAL STANDBY;
shutdown immediate;
startup;