План Data Guard

NVLPU1 - primary база
STANDBY - standby база 
------------------------------------------------------------
tnsnames.ora:
------------------------------------------------------------
NVLPU1 =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.17.10)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = nvlpu1_DGMGRL)
    )
  )

STANDBY =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 1192.168.17.6)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = standby_DGMGRL)
    )
  )
  
------------------------------------------------------------
NVLPU1 listener.ora:
------------------------------------------------------------
SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
      (SID_NAME = nvlpu1)
      (ORACLE_HOME = /u01/app/oracle/product/11.2.0/db_1)
    )
    (SID_DESC =
	(GLOBAL_DBNAME = nvlpu1_DGMGRL)
      (SID_NAME = nvlpu1)
      (ORACLE_HOME = /u01/app/oracle/product/11.2.0/db_1)
    )
  )
------------------------------------------------------------
STANDBY listener.ora:
------------------------------------------------------------
SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
      (SID_NAME = nvlpu1)
      (ORACLE_HOME = /u01/app/oracle/product/11.2.0/dbhome_1)
    )
    (SID_DESC =
	  (GLOBAL_DBNAME = standby_DGMGRL)
      (SID_NAME = nvlpu1)
      (ORACLE_HOME = /u01/app/oracle/product/11.2.0/dbhome_1)
    )
  )
-----------------------------------------------------------
На сервере NVLPU1:
-----------------------------------------------------------
alter database force logging;
alter database add standby logfile group 4 '/u03/ORADATA/nvlpu1/stnbylog01.log' size 100m;
alter database add standby logfile group 5 '/u03/ORADATA/nvlpu1/stnbylog02.log' size 100m;
alter database add standby logfile group 6 '/u03/ORADATA/nvlpu1/stnbylog03.log' size 100m;
alter database add standby logfile group 7 '/u03/ORADATA/nvlpu1/stnbylog04.log' size 100m;


alter system set log_archive_config='dg_config=(nvlpu1,standby)';
alter system set log_archive_dest_1='LOCATION=/u01/app/nvlpu1/archive/ VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=nvlpu1';
alter system set log_archive_dest_2='SERVICE=standby LGWR ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) db_unique_name=standby';
alter system set log_archive_dest_state_1=ENABLE;
alter system set log_archive_dest_state_2=ENABLE;
alter system set FAL_SERVER=standby;
alter system set FAL_CLIENT=nvlpu1;
alter system set standby_file_management='AUTO';

Выключение:
alter system set log_archive_dest_state_1=defer scope=both;
alter system set log_archive_dest_state_2=defer scope=both;

-----------------------------------------
RMAN> backup current controlfile for standby format '/u04/backup/standbycontrol.ctl';
RMAN> change archivelog all crosscheck;
RMAN> run
{
allocate channel c1 device type disk format '/u04/backup/%u';
backup database plus archivelog;
}

-----------------------------------------
scp -r /u04/backup oracle@1192.168.17.6:/u04
копировать listener, tnsnames, dbs/orapwnvlpu1, dbs/spfilenvlpu1

-----------------------------------------------------------
На сервере STANDBY:
-----------------------------------------------------------
nano /etc/oratab
nvlpu1:/u01/app/oracle/product/11.2.0/dbhome_1:Y

.bash_profile:

ORACLE_HOME=/u01/app/oracle/product/11.2.0/dbhome_1
ORACLE_BASE=/u01/app/oracle
PATH=$ORACLE_HOME/bin:$PATH
ORACLE_SID=nvlpu1
export ORACLE_SID
export ORACLE_HOME
export ORACLE_BASE
export PATH

initNVLPU1.ora:

db_unique_name='standby'
log_archive_config='dg_config=(nvlpu1,standby)'
log_archive_dest_1='SERVICE=nvlpu1 LGWR ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) db_unique_name=nvlpu1' 
log_archive_dest_2='LOCATION=/u01/app/nvlpu1/archive/ VALID_FOR=(ALL_LOGFILES,ALL_ROLES) db_unique_name=standby'
log_archive_dest_state_1=ENABLE
log_archive_dest_state_2=ENABLE
fal_client='standby'
fal_server='nvlpu1'
standby_file_management='AUTO'

startup nomount pfile='/u01/app/oracle/product/11.2.0/dbhome_1/dbs/initNVLPU1.ora;
create spfile from pfile='/u01/app/oracle/product/11.2.0/dbhome_1/dbs/initNVLPU1.ora';
shutdown immediate;
startup nomount;
 
Файлы должны быть orapwNVLPU1 и spfileNVLPU1.ora 
Если файл паролей в нинем регистре orapwnvlpu1 то ошибка: insufficient privileges.
 
-----------------------------------------------------------
На сервере NVLPU1:
-----------------------------------------------------------
RMAN> connect auxiliary sys/up1rzOft@STANDBY
RMAN> duplicate target database for standby nofilenamecheck dorecover;
  
-----------------------------------------------------------
Real-time apply redo На сервере STANDBY:
-----------------------------------------------------------
alter database recover managed standby database using current logfile disconnect; 

-----------------------------------------------------------
Установка режима работы.
-----------------------------------------------------------
Узнать режим:
SELECT PROTECTION_MODE, PROTECTION_LEVEL FROM V$DATABASE;

alter database set standby database to maximize performance;
alter database set standby database to maximize protection;
Нам нужен availability:
alter database set standby database to maximize availability;
  
-----------------------------------------------------------
Проверка
-----------------------------------------------------------
На сервере NVLPU1:
alter system switch logfile;
archive log list
select max(sequence#) from v$archived_log;

На сервере STANDBY:
select max(sequence#) from v$archived_log;

select recovery_mode from v$archive_dest_status;
select name, open_mode, log_mode, switchover_status 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;
  

-----------------------------------------------------------
Перевод STANDBY в READ ONLY:
-----------------------------------------------------------
alter database recover managed standby database cancel;
alter database open read only;

Теперь логи все еще поступают на STANDBY, но она уже не «подкатывается».
Вернуть базу в режим «подката» логов

alter database recover managed standby database using current logfile disconnect; 

произвести ручную «накатку»:

recover standby database;

-----------------------------------------------------------
Переключение (SWITCHOVER) 
-----------------------------------------------------------
select name, open_mode, log_mode, switchover_status from v$database;
select max(sequence#) from v$log;

На STANDBY:
select client_process, process, sequence#, status from v$managed_standby;


Необходимо убедиться, что redo current, и все redo журналы были применены к standby database.
Необходимо, чтобы процесс MRP0 с тем же sequence был в статусе APPLYING_LOG
Если Вы не видете MRP0 line, значит, применение не запущено.
Если Вы видите статус WAIT_FOR_GAP, тогда, Вы недолжны делать switch over, пока проблема не будет решена.
Если Вы видите статус WAIT_FOR_LOG, тогда Вы не работаете в режиме применения в реальном времени

На PRIMARY
alter system archive log current;
alter database commit to switchover to physical standby with session shutdown;

На STANDBY:
alter database commit to switchover to primary with session shutdown;
alter database open;

На PRIMARY
shutdown immediate;
quit
sqlplus / as sysdba
startup mount;
alter database recover managed standby database using current logfile disconnect;

На STANDBY:
alter system switch logfile;
select max(sequence#) from v$archived_log;

select name, open_mode, log_mode, switchover_status from v$database;


-----------------------------------------------------------
 Client tnsnames.ora:
-----------------------------------------------------------
NVLPU1 =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.17.10)(PORT = 1521))
      (ADDRESS = (PROTOCOL = TCP)(HOST = 1192.168.17.6)(PORT = 1521))
      (FAILOVER= ON)
	  (LOAD_BALANCE=OFF) 
    )
    (CONNECT_DATA =
      (SID = NVLPU1)
      (SERVER=DEDICATED)
    )
  )

-----------------------------------------------
Включаем FlashBack на каждом сервере
-----------------------------------------------
SELECT flashback_on FROM v$database;
shutdown immediate;
startup mount;
ALTER DATABASE FLASHBACK ON;
alter database open;
(На PRIMARY startup mount;)

----------------------------------------------
Включение broker на PRIMARY и STANDBY:
----------------------------------------------
show parameter DG_BROKER_START
alter system set DG_BROKER_START=true scope=both;
show parameter DG_BROKER_START

----------------------------------------------
Настройка DataGuard Broker
----------------------------------------------
dgmgrl
connect sys/up1rzOft@STANDBY
connect sys/up1rzOft@NVLPU1

show configuration;

CREATE CONFIGURATION nvlpu1conf AS
PRIMARY DATABASE IS NVLPU1
CONNECT IDENTIFIER IS NVLPU1;

ADD DATABASE STANDBY AS
CONNECT IDENTIFIER IS STANDBY
MAINTAINED AS PHYSICAL;

show database verbose NVLPU1;
show database verbose STANDBY;

show configuration;
enable configuration;
enable database STANDBY;

show database NVLPU1;
show database STANDBY;

EDIT DATABASE NVLPU1 SET PROPERTY 'LogXptMode'='SYNC';
EDIT DATABASE STANDBY SET PROPERTY 'LogXptMode'='SYNC';

EDIT DATABASE NVLPU1 SET PROPERTY FastStartFailoverTarget='STANDBY';
EDIT DATABASE STANDBY SET PROPERTY FastStartFailoverTarget='NVLPU1';

EDIT CONFIGURATION SET PROTECTION MODE AS MAXAVAILABILITY;

start observer;

ENABLE FAST_START FAILOVER;

show configuration;

----------------------------------------------
InconsistentProperties
----------------------------------------------
show database NVLPU1 InconsistentProperties;
show database STANDBY InconsistentProperties;

show parameter log_archive_max_processes;

alter system set log_archive_max_processes=1 scope=spfile;

EDIT DATABASE 'nvlpu1' SET PROPERTY 'LogArchiveMaxProcesses'=1;
EDIT DATABASE 'standby' SET PROPERTY 'LogArchiveMaxProcesses'=1;