Standby (резервная СУБД)

Primary

select switchover_status from v$database;

switchover_status
-----------------
sessions active

Сессии перед переключением должны быть завершены вручную.
Или команда переключения должна быть с опцией with session shutdown


Standby

select switchover_status from v$database;

switchover_status
-----------------
not allowed

Этот статус означает, что запрос на switchover не был получен от Primary database.


Делаем switchover

Так как на Primary:  session active
то выполним следующую команду:

alter database commit to switchover to physical standby with session shutdown;


Если теперь посмотреть switchover_status на standby, то мы увидим :

select switchover_status from v$database;

switchover_status
-----------------
switchover pending


Это означает что Primary уже отправил запрос на переключение.
(но переключение ещё не завершилось)


После завершения операции переключения запускаем бывший Primary так:

shutdown immediate;
startup nomount;
alter database mount standby database;


и проверяем role:

select database_role from v$database;

database_role
----------------
physical standby


Проверяем статус бывшей резервной базы standby:

select switchover_status from v$database;

switchover_status
-----------------
to primary


Ну и переключаем её:

alter database commit to switchover to primary;
shutdown immediate;
startup;

Проверим её роль:

select database_role from v$database;

database_role
----------------
primary



Проверим статус новой Primary БД:

select switchover_status from v$database;

switchover_status
-----------------
resolvable gap

Это значит, что резервная БД имеет разрыв цепочки redo.

Эта ситуация может разрешиться автоматически путем получения недостающих redo от primary.


Проверяем статус нового Standby:

select switchover_status from v$database;

switchover_status
-----------------
recovery needed

Это означает, что резервная база нуждается в применении дополнительных redo,
перед переключением её на роль primary.


Перед переключением на Primary статус должен быть:

select switchover_status from v$database;

switchover_status
-----------------
sessions active

или

switchover_status
-----------------
to standby


Переключаемся

alter database commit to switchover to physical standby with session shutdown;
shutdown immediate;
startup mount;

Теперь у нас две резервных БД (старая и новая).
Статус на обоих должен быть:

select switchover_status from v$database;

switchover_status
-----------------
to prymary


Теперь переводим старую резервную БД в основную.

alter database commit to switchover to primary;

Если 10gR2:
alter database open;


Если 10gR1:
shutdown immediate
startup

Переключаем новую резервную БД в режим непрерывного наката логов:

alter database recover managed standby database using current logfile disconnect;


Убедимся что все работает:

На Primary:

alter system switch logfile;
alter system switch logfile;
alter system switch logfile;
alter system switch logfile;
alter system switch logfile;

select max(sequence#) from v$archived_log;


На Standby

select max(sequence#) from v$archived_log;

значение должно совпадать со значением на Primary.



  Останов основной БД и активация standby
(например если standby используется для миграции БД)
На Primary

alter system set job_queue_processes=0;
alter system set aq_tm_processes=0;
shutdown immediate

Иногда, вмесо этого, достаточно остановить передачу логов.

lsnrctl stop lsnr_primary
alter system set log_archive_dest_state_2='DEFER';

На Standby

select switchover_status from v$database;

switchover_status
-----------------
sessions active


alter database recover managed standby database finish;

select switchover_status from v$database;

switchover_status
-----------------
to primary

alter database commit to switchover to primary;

alter database open;

select database_role from v$database;

database_role
----------------
primary



Как переименовать  UNNAMED  файл на Standby.
Находим имя файла:

SELECT FILE#, NAME FROM V$DATAFILE

FILE# NAME
---------- ------------------------------------------------------------
1 /db01/prod/system01.dbf
2 /db01/prod/ca_data-01.dbf
3 /db01/prod/sysaux01.dbf
4 /db01/prod/users01.dbf
5 /db01/prod/proddata-01.dbf
6 /db01/prod/proddata-02.dbf
7 /oracle/app/oracle/product/10.2.0/db_1/dbs/UNNAMED003

Остановим накат Standby:

alter database recover managed standby database disconnect from session

Установим параметр  statndby_file_management в MANUAL

alter system set standby_file_management=manual;

Переименуем UNNAMED datafile:

alter database create datafile 'Unnamed Filename with Path' as 'Origianal file name New location';

alter database create datafile '/oracle/app/oracle/product/10.2.0/db_1/dbs/UNNAMED003' as '/db01/prod/proddata-03.dbf';

Вернём значение параметра statndby_file_management в AUTO:

alter system set standby_file_management=auto;

Запустим накат Standby:
alter database recover managed standby database disconnect



Если база данных на ASM то синтаксис такой:

SQL> alter database create datafile 'unnamed datafile' as '+TargetDiskGroupName' size xM;

SQL> ALTER DATABASE CREATE DATAFILE
     '/u01/app/oracle/product/11.2.0/dbhome_1/dbs/UNNAMED00005'
      AS '+DATA' size 500M;




Изменяем Protection Mode Dataguard

MAXIMUM AVAILABILITY,
MAXIMUM PERFORMANCE,
MAXIMUM PROTECTION,

Standby Redo Log, SYNC, synchronous mode

Primary Database :  SRPRIM
Standby Database :  SRPSTB
Database version :  11gR2

   
SQL> select status,instance_name,database_role,protection_mode from v$database,v$instance;

STATUS INSTANCE_NAME DATABASE_ROLE PROTECTION_MODE
------ ------------- ------------- --------------------
OPEN   srprim        PRIMARY       MAXIMUM PERFORMANCE

Для режимов  MAXIMUM AVAILABILITY/MAXIMUM PROTECTION
необходимы standby redo logs сконфигурированные на standby database.
А на primary parameter (log_archive_dest_2) должен использовать SYNCHRONOUS ('SYNC') mode.

   
SQL> select group#,bytes/1024/1024 from v$log;

GROUP# BYTES/1024/1024
------ ------------------
1      100
2      100
3      100


   
SQL> select group#,bytes/1024/1024 from v$standby_log;

no rows selected

Нет standby redo logs
Добавляем
   
SQL> alter database add standby logfile group 4 size 100M;

Database altered.

SQL> alter database add standby logfile group 5 size 100M;

Database altered.

SQL> alter database add standby logfile group 6 size 100M;

Database altered.

SQL> alter database add standby logfile group 7 size 100M;

Database altered.


   
SQL> select group#,bytes/1024/1024 from v$standby_log;

GROUP# BYTES/1024/1024
------ ---------------
4      100
5      100
6      100
7      100

SQL> select thread#,max(sequence#) from v$archived_log group bythread#;

THREAD# MAX(SEQUENCE#)
------- --------------
1       311



Standby:
   
SQL> select thread#,max(sequence#) from v$archived_log where applied='YES' group by thread#;

THREAD# MAX(SEQUENCE#)
------- --------------
1       311

   
SQL> select status,instance_name,database_role from v$database,v$Instance;

STATUS  INSTANCE_NAME DATABASE_ROLE
------- ------------- ------------------
MOUNTED srpstb        PHYSICAL STANDBY


   
SQL> select group#,bytes/1024/1024 from v$log;

GROUP# BYTES/1024/1024
------ ----------------
1      100
3      100
2      100

SQL> select group#,bytes/1024/1024 from v$standby_log;

no rows selected


Остановим MRP (Managed Recovery Process) и добавим standby redo log groups
   
SQL> select process,status,sequence# from v$managed_standby;

PROCESS STATUS       SEQUENCE#
------- ------------ ----------
ARCH    CONNECTED    0
ARCH    CONNECTED    0
ARCH    CONNECTED    0
ARCH    CONNECTED    0
RFS     IDLE         0
RFS     IDLE         0
RFS     IDLE         0
MRP0    WAIT_FOR_LOG 312

8 rows selected.

Cancel the MRP process on the standby database:
   
SQL> alter database recover managed standby database cancel;

Database altered.

   
SQL> alter database add standby logfile group 4 size 100M;

Database altered.

SQL> alter database add standby logfile group 5 size 100M;

Database altered.

SQL> alter database add standby logfile group 6 size 100M;

Database altered.

SQL> alter database add standby logfile group 7 size 100M;

Database altered.

SQL> select group#,bytes/1024/1024 from v$standby_log;

GROUP# BYTES/1024/1024
------ ---------------
4      100
5      100
6      100
7      100

Запустим  MRP на standby database
   
SQL> alter database recover managed standby database disconnectfrom session;

Database altered.

SQL> select process,status,sequence# from v$managed_standby;

PROCESS STATUS       SEQUENCE#
------- -----------  ----------
ARCH    CONNECTED    0
ARCH    CONNECTED    0
ARCH    CONNECTED    0
ARCH    CONNECTED    0
RFS     IDLE         0
RFS     IDLE         0
RFS     IDLE         0
MRP0    WAIT_FOR_LOG 312

8 rows selected.

Сконфигурируем parameter (log_archive_dest_2) на primary database
на использование SYNCHRONOUS mode.

Primary database:
   
SQL> show parameter log_archive_dest_2

NAME               TYPE   VALUE
------------------ ------ -----------------------------------
log_archive_dest_2 string service=srpstb valid_for=(online_logfiles,primary_role) db_unique_name=srp                          stb

SQL> alter system set log_archive_dest_2='service=srpstb LGWR AFFIRM SYNC valid_for=(online_logfiles,primary_role) db_unique_name=srpstb';

System altered.

SQL> show parameter dest_2

NAME                        TYPE   VALUE
--------------------------- ------ ----------------------------
db_create_online_log_dest_2 string
log_archive_dest_2          string service=srpstb LGWR AFFIRM SYNC valid_for=(online_logfiles,primary_role) db_unique_name=srpstb

Shutdown the primary database and mount it.
   
SQL> shut immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount
ORACLE instance started.

Total System Global Area 208769024 bytes
Fixed Size 2226936 bytes
Variable Size 180356360 bytes
Database Buffers 20971520 bytes
Redo Buffers 5214208 bytes
Database mounted.
SQL>

Изменение protection mode

На primary database :

alter database set standby database to maximize {AVAILABILITY | PROTECTION| PERFORMANCE}

   
SQL> alter database set standby database to maximize availability;

Database altered.

SQL> alter database open;

Database altered.

SQL> select status,instance_name,database_role,protection_mode from v$database,v$instance;

STATUS INSTANCE_NAME DATABASE_ROLE PROTECTION_MODE
------ ------------- ------------- ----------------------
OPEN   srprim        PRIMARY       MAXIMUM AVAILABILITY


On primary:
   
SQL> select thread#,max(sequence#) from v$archived_log group bythread#;

THREAD# MAX(SEQUENCE#)
------- --------------
1       316

Standby:
   
SQL> select status,instance_name,database_role,protection_mode from v$database,v$instance;

STATUS  INSTANCE_NAME DATABASE_ROLE    PROTECTION_MODE
------- ------------- ---------------- --------------------
MOUNTED srpstb        PHYSICAL STANDBY MAXIMUM AVAILABILITY

SQL> select thread#,max(sequence#) from v$archived_log where applied='YES' group by thread#;

THREAD# MAX(SEQUENCE#)
------- --------------
 1      316





Создание  Data Guard 12c  (RAC Physical Standby Database)


Убедимся что primary находится в archivelog и включен Force logging:

SQL> select log_mode,database_role,open_mode from v$database;
LOG_MODE DATABASE_ROLE OPEN_MODE
----------------- ----------------------- --------------------
ARCHIVELOG PRIMARY READ WRITE


SQL> select force_logging from v$database;
FORCE_LOGGING
-----------------------
YES

Если нет то включить:

SQL> alter database force logging;


1. Конфигурация RAC primary database для поддержки primary role такая:
SQL> alter system set log_archive_config='DG_CONFIG=(cdbrac,stdrac)' scope=both sid='*';

SQL> alter system set log_archive_dest_1='LOCATION=USE_DB_RECOVERY_FILE_DEST VALID_FOR=(ALL_LOGFILES,ALL_ROLES)
DB_UNIQUE_NAME=cdbrac' scope= both sid='*';

SQL> alter system set log_archive_dest_2='SERVICE=stdrac VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE)
DB_UNIQUE_NAME=stdrac' scope=both sid='*'

SQL> alter system set LOG_ARCHIVE_FORMAT='%t_%s_%r.arc' scope=both sid='*'

SQL> alter system set LOG_ARCHIVE_MAX_PROCESSES=8 scope=both sid='*'

SQL> alter system set REMOTE_LOGIN_PASSWORDFILE=EXCLUSIVE scope=both sid='*'

SQL> alter system set DB_UNIQUE_NAME=cdbrac scope=spfile sid='*'


2. Конфигурация RAC primary database для поддержки standby role такая:

SQL> alter system set FAL_SERVER='stdrac1,stdrac2' scope=both sid='*'
DB_FILE_NAME_CONVERT/LOG_FILE_NAME_CONVERT : If the primary and standby file system/Disk group are different then we must configure these two parameters.

SQL> alter system set DB_FILE_NAME_CONVERT='+DATA','+DATADG' scope=spfile sid='*'

SQL> alter system set LOG_FILE_NAME_CONVERT= '+FRA','+FRADG' scope=spfile sid='*'

SQL> alter system set STANDBY_FILE_MANAGEMENT=AUTO scope=spfile sid='*'


3. Создаём каталог для backup:
[oracle@racnroll1 ~]$ cd $HOME
[oracle@racnroll1 ~]$ mkdir backup

SQL> create pfile='/home/oracle/backup/initSTDRAC.ora' from spfile;

4. Копируем на RAC physical standby database
[oracle@racnroll1 backup]$ scp /home/oracle/backup/initSTDRAC.ora oracle@racnroll3:/u01/app/oracle/product/12.1.0.1/db_1/dbs/

5. We are performing Active duplicate of 11g feature, hence no need to take backup or copy
   to the standby server.
6. Создаём standby redo logs on the RAC primary database to support the standby role.

SQL> select group#,thread#,bytes from v$log;
GROUP# THREAD# BYTES
------------ ------------- ----------
1 1 52428800
2 1 52428800
3 2 52428800
4 2 52428800

Добавляем  standby redo logs файлы в thread1 на один больше (т.е три)
и в thread2 тоже на один больше (т.е три)
Всего добавляем шесть standby redo logs файлов
SQL> ALTER DATABASE ADD STANDBY LOGFILE THREAD 1 ('+FRA') SIZE 50M;
SQL> ALTER DATABASE ADD STANDBY LOGFILE THREAD 1 ('+FRA') SIZE 50M;
SQL> ALTER DATABASE ADD STANDBY LOGFILE THREAD 1 ('+FRA') SIZE 50M;
SQL> ALTER DATABASE ADD STANDBY LOGFILE THREAD 2 ('+FRA') SIZE 50M;
SQL> ALTER DATABASE ADD STANDBY LOGFILE THREAD 2 ('+FRA') SIZE 50M;
SQL> ALTER DATABASE ADD STANDBY LOGFILE THREAD 2 ('+FRA') SIZE 50M;


SQL> select group#,thread#,bytes from v$standby_log;
GROUP# THREAD# BYTES
---------- ------------- ----------
5 1 52428800
6 1 52428800
7 1 52428800
8 2 52428800
9 2 52428800
10 2 52428800
6 rows selected.


7. Копируем файл паролей на  RAC physical standby database.
[oracle@racnroll1 ~]$ . oraenv
ORACLE_SID = [cdbrac1] ? +ASM1

[oracle@racnroll1 ~]$ asmcmd
ASMCMD> pwget --dbuniquename cdbrac
+DATA/cdbrac/orapwcdbrac

ASMCMD> pwcopy --dbuniquename cdbrac '+DATA/cdbrac/orapwcdbrac' '/home/oracle/backup'
copying +DATA/cdbrac/orapwcdbrac -> /home/oracle/backup/orapwcdbrac

ASMCMD> exit

[oracle@racnroll1 ~]$ cd /home/oracle/backup/
[oracle@racnroll1 backup]$ ls -lrth
[oracle@racnroll1 backup]$ scp orapwcdbrac oracle@racnroll3:/home/oracle/backup/

8. Подключимся к  RAC physical standby database и скопируем файл паролей в ASM directory.
[oracle@racnroll3 ~]$ . oraenv
ORACLE_SID = [stdrac1] ? +ASM1

[oracle@racnroll3 ~]$ asmcmd
ASMCMD> pwcopy --dbuniquename stdrac '/home/oracle/backup/orapwcdbrac' '+DATA/stdrac/orapwcdbrac'
copying /home/oracle/backup/orapwcdbrac -> +DATA/stdrac/orapwcdbrac
ASMCMD> exit

9. Настроим tnsnames на standby чтобы RMAN duplicate смог приконнектиться к auxiliary instance

STDRAC1 =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = racnroll3-vip)
(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = stdrac) (UR=A)
(INSTANCE_NAME = stdrac1)
)
)


10. Заходим на первый standby database instance и выполняем сценарий создания резервной БД.

[oracle@racnroll3 ~]$ export ORACLE_SID=stdrac1
[oracle@racnroll3 ~]$ export ORACLE_BASE=/u01/app/oracle
[oracle@racnroll3 ~]$ export ORACLE_HOME=/u01/app/oracle/product/12.1.0.1/db_1
[oracle@racnroll3 ~]$ sqlplus / as sysdba

SQL> startup nomount pfile='/u01/app/oracle/product/12.1.0.1/db_1/dbs/initSTDRAC.ora
SQL> exit;

[oracle@racnroll3 ~]$ rman target sys/oracle@cdbrac1 auxiliary sys/oracle@stdrac1

RMAN> duplicate target database for standby from active database nofilenamecheck;
Starting Duplicate Db at 27-JUL-15
using target database control file instead of recovery catalog
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: SID=28 device type=DISK
(Output is truncated……)

RMAN> alter system register;
using target database control file instead of recovery catalog
Statement processed

RMAN> exit

11. Останавливаем RAC physical standby database instance (stdrac1) и изменяем
initialization parameters :
control_files и cluster_database в initSTDRAC.ora file.

SQL> shutdown immediate;

[oracle@racnroll3 ~]$ vi /u01/app/oracle/product/12.1.0.1/db_1/dbs/initSTDRAC.ora
[oracle@racnroll3 ~]$ export ORACLE_SID=stdrac1
[oracle@racnroll3 ~]$ export ORACLE_BASE=/u01/app/oracle
[oracle@racnroll3 ~]$ export ORACLE_HOME=/u01/app/oracle/product/12.1.0.1/db_1
[oracle@racnroll3 ~]$ sqlplus / as sysdba

SQL> select open_mode, database_role from v$database;
OPEN_MODE DATABASE_ROLE
-------------------- --------------------------
MOUNTED PHYSICAL STANDBY

SQL> show parameter control_files
SQL> show parameter cluster_database
SQL> exit


12. Добавим Standby в конфигурацию кластера

[oracle@racnroll3 ~]$ srvctl add database -d stdrac -n cdbrac -o /u01/app/oracle/product/12.1.0.1/db_1 -p +DATA/STDRAC/parameterfile/spfileSTDRAC.ora -r physical_standby -a DATA,FRA
[oracle@racnroll3 ~]$ srvctl add instance -d stdrac -i stdrac1 -n racnroll3
[oracle@racnroll3 ~]$ srvctl add instance -d stdrac -i stdrac2 -n racnroll4
[oracle@racnroll3 ~]$ srvctl start database -d stdrac -o mount
[oracle@racnroll3 ~]$ ps -ef | grep pmon
[oracle@racnroll3 ~]$ srvctl config database -d stdrac


Опции:

-d option specifies the database unique name (DB_UNIQUE_NAME) of the database.
-i option specifies the database instance name.
-n option specifies the node on which the instance is running.
-o option specifies the Oracle home of the database.


13. Запустим процесс наката RAC Physical Standby Database
start the MRP (Media Recovery Process) on any one node of the RAC physical standby database.

SQL> alter database recover managed standby database disconnect from session;

14. Мониторим REDO transport status
(redo transport and checking apply lag or transport lag).

Primary:

SQL> select thread#,max(sequence#) from v$archived_log group by thread#;

Standby:

SQL> select thread#,max(sequence#) from v$archived_log where applied=’YES’ group by thread#;


Ещё запросы для мониторинга:
SQL> SELECT MAX(SEQUENCE#), THREAD#
FROM V$ARCHIVED_LOG
WHERE RESETLOGS_CHANGE# = (SELECT MAX(RESETLOGS_CHANGE#)
FROM V$ARCHIVED_LOG)
GROUP BY THREAD#;

SQL> col destination format a20
SQL> col status format a10

SQL> SELECT DESTINATION, STATUS, ARCHIVED_THREAD#, ARCHIVED_SEQ#
FROM V$ARCHIVE_DEST_STATUS
WHERE STATUS <> 'DEFERRED' AND STATUS <> 'INACTIVE';


Мониторинг Standby

1) Basic information of database (primary or standby)

SQL> SELECT DATABASE_ROLE, DB_UNIQUE_NAME INSTANCE, OPEN_MODE, PROTECTION_MODE, PROTECTION_LEVEL, SWITCHOVER_STATUS FROM V$DATABASE;
DATABASE_ROLE INSTANCE OPEN_MODE PROTECTION_MODE PROTECTION_LEVEL SWITCHOVER_STATUS
–––––––– ––––––––––––––– ––––––––––
PHYSICAL STANDBY stdby READ ONLY MAXIMUM PERFORMANCE MAXIMUM PERFORMANCE NOT ALLOWED


2) Check for messages/errors

SQL> SELECT MESSAGE FROM V$DATAGUARD_STATUS;
MESSAGE
––––––––––––––––––––––––––––––––––––––––
ARC0: Archival started
ARC1: Archival started
ARC2: Archival started
ARC2: Becoming the ‘no FAL’ ARCH
ARC1: Becoming the heartbeat ARCH
ARC1: Becoming the active heartbeat ARCH
ARC3: Archival started


3) To display current status information for specific physical standby database background processes.

SQL> SELECT PROCESS, STATUS, THREAD#, SEQUENCE#, BLOCK#, BLOCKS FROM V$MANAGED_STANDBY ;
PROCESS STATUS THREAD# SEQUENCE# BLOCK# BLOCKS
––––- –––––– ––––– ––––– ––––– –––––
ARCH CONNECTED 0 0 0 0
ARCH CONNECTED 0 0 0 0
ARCH CLOSING 1 54 45056 755
ARCH CLOSING 1 57 1 373
RFS IDLE 0 0 0 0
RFS IDLE 0 0 0 0
RFS IDLE 0 0 0 0
RFS IDLE 1 58 30239 1

8 rows selected.


4) Show received archived logs on physical standby
Run this query on physical standby

SQL> select registrar, creator, thread#, sequence#, first_change#, next_change# from v$archived_log;
REGISTR CREATOR THREAD# SEQUENCE# FIRST_CHANGE# NEXT_CHANGE#
–––- –––- ––––– ––––– ––––––- ––––––
RFS ARCH 1 29 1630326 1631783
RFS ARCH 1 30 1631783 1632626
RFS LGWR 1 31 1632626 1669359
RFS ARCH 1 33 1676050 1676124
RFS ARCH 1 32 1669359 1676050
RFS ARCH 1 35 1681145 1681617
RFS ARCH 1 34 1676124 1681145
RFS ARCH 1 37 1688494 1688503
RFS ARCH 1 36 1681617 1688494
RFS ARCH 1 38 1688503 1689533
RFS LGWR 1 39 1689533 1697243


5) To check the log status

SQL> select ‘Last Log applied : ‘ Logs, to_char(next_time,‘DD-MON-YY:HH24:MI:SS’) Time
from v$archived_log
where sequence# = (select max(sequence#) from v$archived_log where applied=’YES’)
union
select ‘Last Log received : ‘ Logs, to_char(next_time,‘DD-MON-YY:HH24:MI:SS’) Time
from v$archived_log
where sequence# = (select max(sequence#) from v$archived_log);
LOGS TIME
–––––––––– –––––––––––––-
Last Log applied : 24-MAR-14:10:11:10
Last Log received : 27-MAR-14:12:40:17


6) To display various information about the redo data. This includes redo data generated by the primary database that is not yet available on the standby database and how much redo has not yet been applied to the standby database.
set lines 132
col value format a20
SQL> select name, value from V$DATAGUARD_STATS;
NAME VALUE
––––––––––––––– ––––––––––
transport lag +00 00:00:00
apply lag
apply finish time
estimated startup time 23


7) to monitor efficient recovery operations as well as to estimate the time required to complete the current operation in progress:
SQL> select to_char(start_time, ‘DD-MON-RR HH24:MI:SS’) start_time,
item, round(sofar/1024,2) “MB/Sec”
from v$recovery_progress
where (item=‘Active Apply Rate’ or item=‘Average Apply Rate’);
START_TIME ITEM MB/SEC
–––––- –––––––––––––––- ––––
27-MAR-14 15:49:44 Active Apply Rate 8.5
27-MAR-14 15:49:44 Average Apply Rate 6.30


8) To find last applied log

SQL> select to_char(max(FIRST_TIME),‘hh24:mi:ss dd/mm/yyyy’) FROM V$ARCHIVED_LOG where applied=‘YES’;
TO_CHAR(MAX(FIRST_T
–––––––––-
10:11:08 24/03/2014


9) To see if standby redo logs have been created. The standby redo logs should be the same size as the online redo logs. There should be (( # of online logs per thread + 1) * # of threads) standby redo logs. A value of 0 for the thread# means the log has never been allocated.

SQL> SELECT thread#, group#, sequence#, bytes, archived, status FROM v$standby_log order by thread#, group#;
THREAD# GROUP# SEQUENCE# BYTES ARC STATUS
––––– ––––– ––––– ––––– –- –––––––––-
1 8 0 104857600 NO UNASSIGNED
1 9 58 104857600 YES ACTIVE
1 10 0 104857600 NO UNASSIGNED
1 11 0 104857600 YES UNASSIGNED


10) To produce a list of defined archive destinations. It shows if they are enabled, what process is servicing that destination, if the destination is local or remote, and if remote what the current mount ID is. For a physical standby we should have at least one remote destination that points the primary set.

column destination format a35 wrap
column process format a7
column ID format 99
column mid format 99
SQL> SELECT thread#, dest_id, destination, gvad.status, target, schedule, process, mountid mid FROM gv$archive_dest gvad, gv$instance gvi WHERE gvad.inst_id = gvi.inst_id AND destination is NOT NULL ORDER BY thread#, dest_id;
THREAD# DEST_ID DESTINATION STATUS TARGET SCHEDULE PROCESS MID
–––– –––- –––––––––––––––––––––––––––––––––––––––––
1 1 USE_DB_RECOVERY_FILE_DEST VALID LOCAL ACTIVE ARCH 0
1 2 brij VALID REMOTE PENDING LGWR 0
1 32 USE_DB_RECOVERY_FILE_DEST VALID LOCAL ACTIVE RFS 0


11) Verify the last sequence# received and the last sequence# applied to standby database.

SQL> SELECT al.thrd “Thread”, almax “Last Seq Received”, lhmax “Last Seq Applied” FROM (select thread# thrd, MAX(sequence#) almax FROM v$archived_log WHERE resetlogs_change#=(SELECT resetlogs_change# FROM v$database) GROUP BY thread#) al, (SELECT thread# thrd, MAX(sequence#) lhmax FROM v$log_history WHERE resetlogs_change#=(SELECT resetlogs_change# FROM v$database) GROUP BY thread#) lh WHERE al.thrd = lh.thrd;
Thread Last Seq Received Last Seq Applied
––––– ––––––––- ––––––––
1 57 53

Проверка режима работы резервной БД:

col NAME format a30
select NAME, VALUE from v$dataguard_stats;


col ITEM format a65
col TYPE format a16
select START_TIME, TYPE,
case
 when ITEM='Active Apply Rate' then 'Active Apply Rate => '||SOFAR||' ('||UNITS||')'
 when ITEM='Last Applied Redo' then 'Last Applied Redo => '||TIMESTAMP||' ('||COMMENTS||')'
else NULL
end as ITEM
from v$recovery_progress
where ITEM in ('Active Apply Rate','Last Applied Redo')
  and START_TIME = (select max(START_TIME) from v$recovery_progress);


Или так:

COL DB_NAME FORMAT A8
COL PRIMARY_DB FORMAT A23
COL STANDBY_DB FORMAT A23
COL LOG_ARCHIVED FORMAT 999999
COL LOG_APPLIED FORMAT 999999
COL LOG_GAP FORMAT 9999
COL APPLIED_TIME FORMAT A19

SELECT (SELECT NAME FROM V$DATABASE) DB_NAME,
       (SELECT VALUE from V$PARAMETER where name='db_unique_name') PRIMARY_DB,
       (SELECT DB_UNIQUE_NAME STANDBY_DB from v$ARCHIVE_DEST where TARGET='STANDBY' and DEST_ID=S.DEST_ID) STANDBY_DB,
       P.DEST_ID PRIMARY_DEST_ID,
       S.DEST_ID STANDBY_DEST_ID,
       P.THREAD#,
       P.LOG_ARCHIVED,
       S.LOG_APPLIED,
       APPLIED_TIME,
       LOG_ARCHIVED-LOG_APPLIED LOG_GAP,
       (SYSDATE-APPLIED_TIME)*3600*24 TIME_GAP_SEC,
       round((SYSDATE-APPLIED_TIME)*3600*24/60) TIME_GAP_MIN
FROM (SELECT DEST_ID, THREAD#, MAX(SEQUENCE#) LOG_ARCHIVED, MAX(COMPLETION_TIME) ARCHIVED_TIME FROM V$ARCHIVED_LOG WHERE STANDBY_DEST = 'NO'  AND ARCHIVED='YES' and resetlogs_id=(select distinct resetlogs_id from gv$database_incarnation where status='CURRENT') GROUP BY DEST_ID, THREAD#) P,
     (SELECT DEST_ID, THREAD#, MAX(SEQUENCE#) LOG_APPLIED,  MAX(COMPLETION_TIME) APPLIED_TIME  FROM V$ARCHIVED_LOG WHERE STANDBY_DEST = 'YES' AND  APPLIED='YES' and resetlogs_id=(select distinct resetlogs_id from gv$database_incarnation where status='CURRENT') GROUP BY DEST_ID, THREAD#) S
WHERE P.DEST_ID!=S.DEST_ID
  AND P.THREAD#=S.THREAD#;

Режимы:

Существует Data Guard созданный по умолчанию в режиме MAXIMIZE PERFORMANCE

Мы хотим перевести его в режим MAXIMIZE AVAILABILITY

Для этого необходимо:

1. Убедиться что на Standby созданы standby redo logs:

[oradb]$ sqlplus /nolog

SQL*Plus: Release 10.2.0.4.0 - Production on Thu Oct 28 16:26:20 2010

Copyright (c) 1982, 2007, Oracle. All Rights Reserved.

SQL> connect sys/oracle@TESTASM_S AS SYSDBA
Connected.
SQL>

SQL> col member format a35
SQL> set lines 132
SQL> set trims on
SQL> select group#,type,member from v$logfile;

GROUP# TYPE MEMBER
---------- ------- -----------------------------------
1 ONLINE +REDOA/testasm_s/onlinelog/group_1.
275.733316931

1 ONLINE +REDOB/testasm_s/onlinelog/group_1.
275.733316931

2 ONLINE +REDOA/testasm_s/onlinelog/group_2.
276.733316931

2 ONLINE +REDOB/testasm_s/onlinelog/group_2.
276.733316933

GROUP# TYPE MEMBER
---------- ------- -----------------------------------

3 ONLINE +REDOA/testasm_s/onlinelog/group_3.
277.733316935

3 ONLINE +REDOB/testasm_s/onlinelog/group_3.
277.733316937

4 STANDBY +REDOA/testasm_s/onlinelog/group_4.
278.731243271

4 STANDBY +REDOB/testasm_s/onlinelog/group_4.

GROUP# TYPE MEMBER
---------- ------- -----------------------------------
278.731243271

5 STANDBY +REDOA/testasm_s/onlinelog/group_5.
279.731243279

5 STANDBY +REDOB/testasm_s/onlinelog/group_5.
279.731243279

6 STANDBY +REDOA/testasm_s/onlinelog/group_6.
280.731243287


GROUP# TYPE MEMBER
---------- ------- -----------------------------------
6 STANDBY +REDOB/testasm_s/onlinelog/group_6.
280.731243287


Если их нет то создать:


SQL> SELECT GROUP#, BYTES, 'ONLINE' AS TYPE FROM V$LOG
UNION
SELECT GROUP#, BYTES, 'STANDBY' AS TYPE FROM V$STANDBY_LOG
ORDER BY 1;
2 3 4
GROUP# BYTES TYPE
---------- ---------- -------
1 104857600 ONLINE
2 104857600 ONLINE
3 104857600 ONLINE

3 rows selected.

SQL>

SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;


SQL> ALTER DATABASE ADD STANDBY LOGFILE GROUP 4 SIZE 100M;
SQL> ALTER DATABASE ADD STANDBY LOGFILE GROUP 5 SIZE 100M;
SQL> ALTER DATABASE ADD STANDBY LOGFILE GROUP 6 SIZE 100M;

SQL> SELECT GROUP#, BYTES, 'ONLINE' AS TYPE FROM V$LOG
UNION
SELECT GROUP#, BYTES, 'STANDBY' AS TYPE FROM V$STANDBY_LOG
ORDER BY 1;
2 3 4
GROUP# BYTES TYPE
---------- ---------- -------
1 104857600 ONLINE
2 104857600 ONLINE
3 104857600 ONLINE
4 104857600 STANDBY
5 104857600 STANDBY
6 104857600 STANDBY

6 rows selected.


SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION;


Также можно создать standby redo logs и на primary (при смене ролей они уже будут существовать)

2. Сконфигурируем log transport services для поддержки режима MAXIMIZE AVAILABILITY

[oradb]$ sqlplus /nolog

SQL*Plus: Release 10.2.0.4.0 - Production on Thu Oct 28 16:27:25 2010

Copyright (c) 1982, 2007, Oracle. All Rights Reserved.

SQL> connect sys/oracle@TESTASM_P AS SYSDBA;
Connected.
SQL>
SQL>

SQL> alter system set LOG_ARCHIVE_DEST_2='SERVICE=testasm_s LGWR SYNC AFFIRM VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=testasm_s';

SQL> alter system set LOG_ARCHIVE_DEST_STATE_2=ENABLE;

SQL> SHUTDOWN IMMEDIATE
SQL> STARTUP MOUNT
SQL> ALTER DATABASE SET STANDBY DATABASE TO MAXIMIZE AVAILABILITY;
SQL> ALTER DATABASE OPEN;

SQL> SELECT PROTECTION_MODE, PROTECTION_LEVEL, DATABASE_ROLE FROM V$DATABASE;



3. Режимы Protection Mode:

By default a newly created standby database is in maximum performance mode.

SELECT protection_mode FROM v$database;

PROTECTION_MODE
--------------------
MAXIMUM PERFORMANCE

SQL>



В режим maximum availability переходим так:

ALTER SYSTEM SET LOG_ARCHIVE_DEST_2='SERVICE=testasm_s AFFIRM SYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=testasm_s';
ALTER DATABASE SET STANDBY DATABASE TO MAXIMIZE AVAILABILITY;

В режим maximum Performance переходим так:

ALTER SYSTEM SET LOG_ARCHIVE_DEST_2='SERVICE=testasm_s NOAFFIRM ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=testasm_s';
ALTER DATABASE SET STANDBY DATABASE TO MAXIMIZE PERFORMANCE;

В режим maximum Protection переходим так:

ALTER SYSTEM SET LOG_ARCHIVE_DEST_2='SERVICE=testasm_s AFFIRM SYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=testasm_s';
SHUTDOWN IMMEDIATE;
STARTUP MOUNT;
ALTER DATABASE SET STANDBY DATABASE TO MAXIMIZE PROTECTION;
ALTER DATABASE OPEN;

Data Guard Broker

1. На primary:

[oradb]$ more listener.ora
SID_LIST_LISTENERDGMGRL=
   (SID_LIST=
     (SID_DESC=
         (SID_NAME=testasm)
         (GLOBAL_DBNAME=testasm_p_DGMGRL.mydomain.com)
         (ORACLE_HOME=/u01/app/oradb/product/10.2.0/db_1)
     )
   )

LISTENERDGMGRL =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = server-1.mydomain.com)(PORT = 1522))
    )
  )

[oradb]$ more tnsnames.ora
TESTASM_P =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = server-1.mydomain.com)(PORT = 1522))
    )
    (CONNECT_DATA =
      (SERVER = DEDICATED)
        (SERVICE_NAME = testasm_p_DGMGRL.mydomain.com)
    )
  )

TESTASM_S =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = server-2.mydomain.com)(PORT = 1522))
    )
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = testasm_s_DGMGRL.mydomain.com)
    )
  )

[oradb]$


2. На standby

[oradb]$ more listener.ora
SID_LIST_LISTENERDGMGRL=
   (SID_LIST=
     (SID_DESC=
         (SID_NAME=testasm)
         (GLOBAL_DBNAME=testasm_s_DGMGRL.mydomain.com)
         (ORACLE_HOME=/u01/app/oradb/product/10.2.0/db_1)
     )
   )

LISTENERDGMGRL =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = server-2.mydomain.com)(PORT = 1522))
    )
  )
[oradb]$

[oradb]$ more tnsnames.ora
TESTASM_P =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = server-1.mydomain.com)(PORT = 1522))
    )
    (CONNECT_DATA =
      (SERVER = DEDICATED)
        (SERVICE_NAME = testasm_p_DGMGRL.mydomain.com)
    )
  )

TESTASM_S =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = server-2.mydomain.com)(PORT = 1522))
    )
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = testasm_s_DGMGRL.mydomain.com)
    )
  )

[oradb]$


3. Создание конфигурации

 на primary:

SQL> show parameter DG_BROKER_START

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
dg_broker_start                      boolean     FALSE

SQL> alter system set DG_BROKER_START=true scope=both;

System altered.
 
SQL> show parameter DG_BROKER_START

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
dg_broker_start                      boolean     TRUE

SQL> exit

на standby:

SQL> show parameter DG_BROKER_START

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
dg_broker_start                      boolean     FALSE

SQL> alter system set DG_BROKER_START=true scope=both;

System altered.

SQL> show parameter DG_BROKER_START

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
dg_broker_start                      boolean     TRUE

SQL>


[oradb]$ dgmgrl
DGMGRL for Linux: Version 10.2.0.4.0 - 64bit Production

Copyright (c) 2000, 2005, Oracle. All rights reserved.

Welcome to DGMGRL, type "help" for information.
DGMGRL> connect sys/oracle@TESTASM_P
Connected.
DGMGRL> create configuration 'brokerdg' as
> primary database is testasm_p
> connect identifier is TESTASM_P;
Configuration "brokerdg" created with primary database "testasm_p"
DGMGRL>


DGMGRL> add database testasm_s as
> connect identifier is TESTASM_S
> maintained as physical;
Database "testasm_s" added
DGMGRL>
DGMGRL> show configuration

Configuration
  Name:                brokerdg
  Enabled:             NO
  Protection Mode:     MaxPerformance
  Fast-Start Failover: DISABLED
  Databases:
    testasm_p - Primary database
    testasm_s - Physical standby database

Current status for "brokerdg":
DISABLED

DGMGRL> enable configuration;
Enabled.
DGMGRL>
DGMGRL> show configuration

Configuration
  Name:                brokerdg
  Enabled:             YES
  Protection Mode:     MaxPerformance
  Fast-Start Failover: DISABLED
  Databases:
    testasm_p - Primary database
    testasm_s - Physical standby database

Current status for "brokerdg":
SUCCESS
DGMGRL>

DGMGRL> switchover to testasm_p
Performing switchover NOW, please wait...
Operation requires shutdown of instance "testasm" on database "testasm_s"
Shutting down instance "testasm"...
ORA-01109: database not open

Database dismounted.
ORACLE instance shut down.
Operation requires shutdown of instance "testasm" on database "testasm_p"
Shutting down instance "testasm"...
ORA-01109: database not open

Database dismounted.
ORACLE instance shut down.
Operation requires startup of instance "testasm" on database "testasm_s"
Starting instance "testasm"...
ORACLE instance started.
Database mounted.
Operation requires startup of instance "testasm" on database "testasm_p"
Starting instance "testasm"...
ORACLE instance started.
Database mounted.
Switchover succeeded, new primary is "testasm_p"
DGMGRL>

4.Удаление конфигурации

[oradb]$ dgmgrl
DGMGRL for Linux: Version 10.2.0.4.0 - 64bit Production

Copyright (c) 2000, 2005, Oracle. All rights reserved.

Welcome to DGMGRL, type "help" for information.
DGMGRL> connect sys/oracle@TESTASM_P
Connected.
DGMGRL> show configuration;

Configuration
  Name:                brokerdg
  Enabled:             YES
  Protection Mode:     MaxPerformance
  Fast-Start Failover: DISABLED
  Databases:
    testasm_p - Primary database
    testasm_s - Physical standby database

Current status for "brokerdg":
SUCCESS

DGMGRL> REMOVE DATABASE testasm_s;
Removed database "testasm_s" from the configuration
DGMGRL> REMOVE CONFIGURATION;
Removed configuration
DGMGRL> show configuration;
Error: ORA-16532: Data Guard broker configuration does not exist

Configuration details cannot be determined by DGMGRL
DGMGRL>

Data Guard

1. На primary:

[oradb]$ more inittestasm.ora
*.audit_file_dest='/u01/app/oradb/admin/testasm/adump'
*.background_dump_dest='/u01/app/oradb/admin/testasm/bdump'
*.core_dump_dest='/u01/app/oradb/admin/testasm/cdump'
*.user_dump_dest='/u01/app/oradb/admin/testasm/udump'
*.compatible='10.2.0.3.0'
*.control_files='+DATA/testasm/controlfile/current.256.730810345','+FRA/testasm/controlfile/current.256.730810345'
*.db_block_size=8192
*.db_create_file_dest='+DATA'
*.db_create_online_log_dest_1='+REDOA'
*.db_create_online_log_dest_2='+REDOB'
*.db_domain='mydomain.com'
*.db_file_multiblock_read_count=16
*.db_name='testasm'
*.db_recovery_file_dest='+FRA'
*.db_recovery_file_dest_size=209715200000
*.dispatchers='(PROTOCOL=TCP) (SERVICE=testasmXDB)'
*.job_queue_processes=10
*.log_archive_format='%t_%s_%r.dbf'
*.open_cursors=300
*.pga_aggregate_target=198180864
*.processes=150
*.sga_target=595591168
*.undo_management='AUTO'
*.undo_tablespace='UNDOTBS1'


[oradb]$ export ORACLE_SID=testasm
[oradb]$ sqlplus "/as sysdba"

SQL*Plus: Release 10.2.0.4.0 - Production on Tue Oct 26 14:11:56 2010

Copyright (c) 1982, 2007, Oracle. All Rights Reserved.

Connected to an idle instance.

SQL> startup
ORACLE instance started.

Total System Global Area 595591168 bytes
Fixed Size 2085744 bytes
Variable Size 176163984 bytes
Database Buffers 411041792 bytes
Redo Buffers 6299648 bytes
Database mounted.
Database opened.
SQL>
SQL> create spfile='+DATA' from pfile='/tmp/inittestasm.ora';

SQL> alter system set DB_UNIQUE_NAME='testasm_p' SCOPE=SPFILE;

SQL> archive log list
SQL> shutdown immediate
SQL> startup mount
SQL> alter database archivelog;
SQL> alter database open
SQL> archive log list

SQL> alter database force logging;
SQL> alter system set LOG_ARCHIVE_CONFIG='DG_CONFIG=(testasm_p, testasm_s)';
SQL> alter system set LOG_ARCHIVE_DEST_1='LOCATION=+FRA VALID_FOR=(ALL_LOGFILES, ALL_ROLES) DB_UNIQUE_NAME=testasm_p';
SQL> alter system set LOG_ARCHIVE_DEST_2='SERVICE=testasm_s LGWR ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=testasm_s';
SQL> alter system set LOG_ARCHIVE_DEST_STATE_1=ENABLE;
SQL> alter system set remote_login_passwordfile='EXCLUSIVE';
SQL> alter system set FAL_SERVER=testasm_s;
SQL> alter system set FAL_CLIENT=testasm_p;
SQL> alter system set STANDBY_FILE_MANAGEMENT=AUTO scope=spfile;

SQL> ALTER DATABASE ADD STANDBY LOGFILE GROUP 4 SIZE 50M;
SQL> ALTER DATABASE ADD STANDBY LOGFILE GROUP 5 SIZE 50M;
SQL> ALTER DATABASE ADD STANDBY LOGFILE GROUP 5 SIZE 50M;


SQL> select l.group# , l.bytes , l.status , lf.member
from v$logfile lf , v$log l
where lf.group# = l.group#;

[oradb]$ export ORACLE_SID=testasm
[oradb]$ rman target /

Recovery Manager: Release 10.2.0.4.0 - Production on Fri Oct 1 09:59:15 2010

Copyright (c) 1982, 2007, Oracle. All rights reserved.

connected to target database: TESTASM (DBID=28698405)

RMAN> sql 'alter system archive log current';

using target database control file instead of recovery catalog
sql statement: alter system archive log current

RMAN> backup database format '/mnt/u00/oradb/backups/%U';

Starting backup at 01-OCT-10
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=148 devtype=DISK
channel ORA_DISK_1: starting full datafile backupset
channel ORA_DISK_1: specifying datafile(s) in backupset
input datafile fno=00001 name=+DATA/testasm/datafile/system.260.730810349
input datafile fno=00002 name=+DATA/testasm/datafile/undotbs1.261.730810359
input datafile fno=00003 name=+DATA/testasm/datafile/sysaux.262.730810363
input datafile fno=00004 name=+DATA/testasm/datafile/users.264.730810367
channel ORA_DISK_1: starting piece 1 at 01-OCT-10
channel ORA_DISK_1: finished piece 1 at 01-OCT-10
piece handle=/mnt/u00/oradb/backups/0qlpbkp8_1_1 tag=TAG20101001T100008 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:25
channel ORA_DISK_1: starting full datafile backupset
channel ORA_DISK_1: specifying datafile(s) in backupset
including current control file in backupset
including current SPFILE in backupset
channel ORA_DISK_1: starting piece 1 at 01-OCT-10
channel ORA_DISK_1: finished piece 1 at 01-OCT-10
piece handle=/mnt/u00/oradb/backups/0rlpbkq2_1_1 tag=TAG20101001T100008 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:02
Finished backup at 01-OCT-10

RMAN> sql 'alter system archive log current';

sql statement: alter system archive log current

RMAN> backup archivelog all format '/mnt/u00/oradb/backups/%U';
Starting backup at 01-OCT-10
current log archived
using channel ORA_DISK_1
channel ORA_DISK_1: starting archive log backupset
channel ORA_DISK_1: specifying archive log(s) in backup set
input archive log thread=1 sequence=125 recid=131 stamp=731238743
input archive log thread=1 sequence=126 recid=132 stamp=731238751
input archive log thread=1 sequence=127 recid=133 stamp=731238757
input archive log thread=1 sequence=128 recid=134 stamp=731239175
input archive log thread=1 sequence=129 recid=135 stamp=731239265
input archive log thread=1 sequence=130 recid=136 stamp=731239278
channel ORA_DISK_1: starting piece 1 at 01-OCT-10
channel ORA_DISK_1: finished piece 1 at 01-OCT-10
piece handle=/mnt/u00/oradb/backups/0slpbkre_1_1 tag=TAG20101001T100118 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:02
Finished backup at 01-OCT-10

RMAN> backup current controlfile for standby format '/mnt/u00/oradb/backups/%U';
Starting backup at 01-OCT-10
using channel ORA_DISK_1
channel ORA_DISK_1: starting full datafile backupset
channel ORA_DISK_1: specifying datafile(s) in backupset
including standby control file in backupset
channel ORA_DISK_1: starting piece 1 at 01-OCT-10
channel ORA_DISK_1: finished piece 1 at 01-OCT-10
piece handle=/mnt/u00/oradb/backups/0tlpbkrv_1_1 tag=TAG20101001T100135 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
Finished backup at 01-OCT-10

RMAN> exit


Recovery Manager complete.
[oradb]$

[oradb]$ ls -l
total 652540
-rw-r----- 1 oradb oinstall 608378880 Oct 1 10:00 0qlpbkp8_1_1
-rw-r----- 1 oradb oinstall 7372800 Oct 1 10:00 0rlpbkq2_1_1
-rw-r----- 1 oradb oinstall 44431360 Oct 1 10:01 0slpbkre_1_1
-rw-r----- 1 oradb oinstall 7340032 Oct 1 10:01 0tlpbkrv_1_1

Копируем backup на сервер server-2
[oradb]$ scp -pr * server-2:/mnt/u00/oradb/backups/
oradb's password:
0qlpbkp8_1_1 100% 580MB 41.4MB/s 00:14
0rlpbkq2_1_1 100% 7200KB 7.0MB/s 00:00
0slpbkre_1_1 100% 42MB 42.4MB/s 00:01
0tlpbkrv_1_1 100% 7168KB 7.0MB/s 00:00
[oradb]$


Копируем pfile с primary на snandby


[oradb]$ sqlplus "/as sysdba"

SQL*Plus: Release 10.2.0.4.0 - Production on Tue Oct 26 12:05:41 2010

Copyright (c) 1982, 2007, Oracle. All Rights Reserved.


Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> create pfile='/tmp/inittestasm.ora' from spfile;

File created.

SQL> exit

scp -pr /tmp/inittestasm.ora server-2:/tmp/inittestasm.ora

[oradb]$ vi /tmp/inittestasm.ora
*.audit_file_dest='/u01/app/oradb/admin/testasm/adump'
*.background_dump_dest='/u01/app/oradb/admin/testasm/bdump'
*.compatible='10.2.0.3.0'
*.control_files='+REDOA/testasm_s/controlfile/current.268.731087529','+REDOB/testasm_s/controlfile/current.268.731087533'
*.core_dump_dest='/u01/app/oradb/admin/testasm/cdump'
*.db_block_size=8192
*.db_create_file_dest='+DATA'
*.db_create_online_log_dest_1='+REDOA'
*.db_create_online_log_dest_2='+REDOB'
*.db_domain='msk.vbrr.loc'
*.db_file_multiblock_read_count=16
*.db_name='testasm'
*.db_recovery_file_dest='+FRA'
*.db_recovery_file_dest_size=209715200000
*.db_unique_name='testasm_s'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=testasmXDB)'
*.fal_client='TESTASM_S'
*.fal_server='TESTASM_P'
*.job_queue_processes=10
*.log_archive_config='DG_CONFIG=(testasm_p, testasm_s)'
*.log_archive_dest_1='LOCATION=+FRA VALID_FOR=(ALL_LOGFILES, ALL_ROLES) DB_UNIQUE_NAME=testasm_s'
*.log_archive_dest_2='SERVICE=testasm_p LGWR ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=testasm_p'
*.log_archive_dest_state_1='ENABLE'
*.log_archive_format='%t_%s_%r.dbf'
*.open_cursors=300
*.pga_aggregate_target=198180864
*.processes=150
*.remote_login_passwordfile='EXCLUSIVE'
*.sga_target=595591168
*.undo_management='AUTO'
*.undo_tablespace='UNDOTBS1'
*.user_dump_dest='/u01/app/oradb/admin/testasm/udump'


[oradb]$ export ORACLE_SID=testasm
[oradb]$ sqlplus "/as sysdba"

SQL*Plus: Release 10.2.0.4.0 - Production on Fri Oct 1 10:09:53 2010

Copyright (c) 1982, 2007, Oracle. All Rights Reserved.

Connected to an idle instance.

SQL> startup nomount pfile='/tmp/inittestasm.ora';
ORACLE instance started.

Total System Global Area 595591168 bytes
Fixed Size 2085744 bytes
Variable Size 167775376 bytes
Database Buffers 419430400 bytes
Redo Buffers 6299648 bytes
SQL> create spfile='+DATA' from pfile='/tmp/inittestasm.ora';

File created.

SQL> exit
Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
[oradb]$

[oradb]$ export ORACLE_SID=testasm
[oradb]$ rman nocatalog;

Recovery Manager: Release 10.2.0.4.0 - Production on Fri Oct 1 10:11:46 2010

Copyright (c) 1982, 2007, Oracle. All rights reserved.

RMAN> connect target sys/oracle@TESTASM_P

connected to target database: TESTASM (DBID=28698405)
using target database control file instead of recovery catalog

RMAN> connect auxiliary / ;

connected to auxiliary database: TESTASM (not mounted)

RMAN> duplicate target database for standby NOFILENAMECHECK;

Starting Duplicate Db at 01-OCT-10
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: sid=156 devtype=DISK

contents of Memory Script:
{
restore clone standby controlfile;
sql clone 'alter database mount standby database';
}
executing Memory Script

Starting restore at 01-OCT-10
using channel ORA_AUX_DISK_1

channel ORA_AUX_DISK_1: starting datafile backupset restore
channel ORA_AUX_DISK_1: restoring control file
channel ORA_AUX_DISK_1: reading from backup piece /mnt/u00/oradb/backups/0tlpbkrv_1_1
channel ORA_AUX_DISK_1: restored backup piece 1
piece handle=/mnt/u00/oradb/backups/0tlpbkrv_1_1 tag=TAG20101001T100135
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:08
output filename=+REDOA/testasm_s/controlfile/current.268.731087529
output filename=+REDOB/testasm_s/controlfile/current.268.731087533
Finished restore at 01-OCT-10

sql statement: alter database mount standby database
released channel: ORA_AUX_DISK_1

contents of Memory Script:
{
set newname for clone tempfile 1 to new;
switch clone tempfile all;
set newname for clone datafile 1 to new;
set newname for clone datafile 2 to new;
set newname for clone datafile 3 to new;
set newname for clone datafile 4 to new;
restore
check readonly
clone database
;
}
executing Memory Script

executing command: SET NEWNAME

renamed temporary file 1 to +DATA in control file

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

Starting restore at 01-OCT-10
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: sid=159 devtype=DISK

channel ORA_AUX_DISK_1: starting datafile backupset restore
channel ORA_AUX_DISK_1: specifying datafile(s) to restore from backup set
restoring datafile 00001 to +DATA
restoring datafile 00002 to +DATA
restoring datafile 00003 to +DATA
restoring datafile 00004 to +DATA
channel ORA_AUX_DISK_1: reading from backup piece /mnt/u00/oradb/backups/0qlpbkp8_1_1
channel ORA_AUX_DISK_1: restored backup piece 1
piece handle=/mnt/u00/oradb/backups/0qlpbkp8_1_1 tag=TAG20101001T100008
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:15
Finished restore at 01-OCT-10

contents of Memory Script:
{
switch clone datafile all;
}
executing Memory Script

datafile 1 switched to datafile copy
input datafile copy recid=5 stamp=731240824 filename=+DATA/testasm_s/datafile/system.287.731240809
datafile 2 switched to datafile copy
input datafile copy recid=6 stamp=731240824 filename=+DATA/testasm_s/datafile/undotbs1.288.731240809
datafile 3 switched to datafile copy
input datafile copy recid=7 stamp=731240824 filename=+DATA/testasm_s/datafile/sysaux.289.731240809
datafile 4 switched to datafile copy
input datafile copy recid=8 stamp=731240824 filename=+DATA/testasm_s/datafile/users.290.731240809
Finished Duplicate Db at 01-OCT-10

RMAN> exit

Recovery Manager complete.
[oradb@x4450-s2 backups]$

[oradb]$ export ORACLE_SID=testasm
[oradb]$ sqlplus "/as sysdba"
SQL*Plus: Release 10.2.0.4.0 - Production on Fri Oct 1 10:35:51 2010

Copyright (c) 1982, 2007, Oracle. All Rights Reserved.


Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> alter database recover managed standby database disconnect from session;

Database altered.

SQL> SELECT SEQUENCE#, FIRST_TIME, NEXT_TIME FROM V$ARCHIVED_LOG ORDER BY SEQUENCE#;

SEQUENCE# FIRST_TIM NEXT_TIME
---------- --------- ---------
129 01-OCT-10 01-OCT-10
130 01-OCT-10 01-OCT-10

SQL>


На primary сервере:

[oradb]$ export ORACLE_SID=testasm
[oradb]$ sqlplus "/as sysdba"
SQL*Plus: Release 10.2.0.4.0 - Production on Fri Oct 1 10:40:06 2010

Copyright (c) 1982, 2007, Oracle. All Rights Reserved.


Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> alter system set log_archive_dest_state_2=enable;

System altered.

SQL> alter system switch logfile;
System altered.

SQL> alter system switch logfile;
System altered.

SQL> alter system switch logfile;
System altered.

SQL> alter system switch logfile;

System altered.

SQL>

На standby:

SQL> SELECT SEQUENCE#, FIRST_TIME, NEXT_TIME FROM V$ARCHIVED_LOG ORDER BY SEQUENCE#;

SEQUENCE# FIRST_TIM NEXT_TIME
---------- --------- ---------
129 01-OCT-10 01-OCT-10
130 01-OCT-10 01-OCT-10
131 01-OCT-10 01-OCT-10
132 01-OCT-10 01-OCT-10
133 01-OCT-10 01-OCT-10
134 01-OCT-10 01-OCT-10

6 rows selected.

SQL>


Все работает

Останавливать резервную базу так:

SQL> alter database recover managed standby database disconnect from session;
alter database recover managed standby database disconnect from session
*
ERROR at line 1:
ORA-01153: an incompatible media recovery is active


SQL> alter database recover managed standby database cancel;

Database altered.

SQL> shutdown immediate
ORA-01109: database not open


Database dismounted.
ORACLE instance shut down.
SQL>


Стартовать резервную базу так :

SQL> startup nomount
ORACLE instance started.

Total System Global Area 595591168 bytes
Fixed Size 2085744 bytes
Variable Size 167775376 bytes
Database Buffers 419430400 bytes
Redo Buffers 6299648 bytes
SQL> alter database mount standby database;
Database altered.

SQL> alter database recover managed standby database disconnect from session;

Database altered.

SQL>


Добавим на резервную базу Standby Redo Log (как и на Primary)
SQL>
SQL> alter database recover managed standby database cancel;
Database altered.

SQL> ALTER DATABASE ADD STANDBY LOGFILE GROUP 4 SIZE 50m;
Database altered.

SQL> ALTER DATABASE ADD STANDBY LOGFILE GROUP 5 SIZE 50m;
Database altered.

SQL> ALTER DATABASE ADD STANDBY LOGFILE GROUP 6 SIZE 50m;

Database altered.

SQL> SELECT GROUP#,ThREAD#,SEQUENCE#,ARCHIVED,STATUS FROM V$STANDBY_LOG;

GROUP# THREAD# SEQUENCE# ARC STATUS
---------- ---------- ---------- --- ----------
4 0 0 YES UNASSIGNED
5 0 0 YES UNASSIGNED
6 0 0 YES UNASSIGNED

SQL> column member format a55
SQL> select vs.group#,vs.bytes,vl.member from v$standby_log vs,v$logfile vl where vs.group# = vl.group# order by vs.group#,vl.member;

GROUP# BYTES MEMBER
---------- ---------- -------------------------------------------------------
4 104857600 +REDOA/testasm_s/onlinelog/group_4.278.731243271
4 104857600 +REDOB/testasm_s/onlinelog/group_4.278.731243271
5 104857600 +REDOA/testasm_s/onlinelog/group_5.279.731243279
5 104857600 +REDOB/testasm_s/onlinelog/group_5.279.731243279
6 104857600 +REDOA/testasm_s/onlinelog/group_6.280.731243287
6 104857600 +REDOB/testasm_s/onlinelog/group_6.280.731243287

6 rows selected.

SQL> alter database recover managed standby database disconnect from session;

Database altered.

SQL>


Перевод standby в режим read only;


SQL> alter database recover managed standby database cancel;

Database altered.

SQL> alter database open read only;

Database altered.

В этом режиме можно добавлять временные файлы и запускать отчеты.

SQL> alter tablespace temp add tempfile '+DATA' size 100M reuse;

Tablespace altered.

SQL> select name from v$tempfile;

NAME
--------------------------------------------------------------------------------
+DATA/testasm_s/tempfile/temp.291.731245461
+DATA/testasm_s/tempfile/temp.292.731245479

SQL> alter database recover managed standby database disconnect from session;

Database altered.

SQL>


SQL> select status from v$instance;

STATUS
------------
MOUNTED

SQL> alter database recover managed standby database cancel;

Database altered.

SQL> select status from v$instance;

STATUS
------------
MOUNTED

SQL> alter database open read only;

Database altered.

SQL> select status from v$instance;

STATUS
------------
OPEN

SQL> alter database recover managed standby database disconnect from session;
Database altered.

SQL> select status from v$instance;

STATUS
------------
MOUNTED

SQL>

Проверка отставания Standby:

Выполнить на Primary

COL DB_NAME FORMAT A8
COL PRIMARY_DB FORMAT A23
COL STANDBY_DB FORMAT A23
COL LOG_ARCHIVED FORMAT 999999
COL LOG_APPLIED FORMAT 999999
COL LOG_GAP FORMAT 9999
COL APPLIED_TIME FORMAT A19

SELECT (SELECT NAME FROM V$DATABASE) DB_NAME,
       (SELECT VALUE from V$PARAMETER where name='db_unique_name') PRIMARY_DB,
       (SELECT DB_UNIQUE_NAME STANDBY_DB from v$ARCHIVE_DEST where TARGET='STANDBY' and DEST_ID=S.DEST_ID) STANDBY_DB,
       P.DEST_ID PRIMARY_DEST_ID,
       S.DEST_ID STANDBY_DEST_ID,
       P.THREAD#,
       P.LOG_ARCHIVED,
       S.LOG_APPLIED,
       APPLIED_TIME,
       LOG_ARCHIVED-LOG_APPLIED LOG_GAP,
       (SYSDATE-APPLIED_TIME)*3600*24 TIME_GAP_SEC,
       round((SYSDATE-APPLIED_TIME)*3600*24/60) TIME_GAP_MIN
FROM (SELECT DEST_ID, THREAD#, MAX(SEQUENCE#) LOG_ARCHIVED, MAX(COMPLETION_TIME) ARCHIVED_TIME FROM V$ARCHIVED_LOG WHERE STANDBY_DEST = 'NO'  AND ARCHIVED='YES' and resetlogs_id=(select distinct resetlogs_id from gv$database_incarnation where status='CURRENT') GROUP BY DEST_ID, THREAD#) P,
     (SELECT DEST_ID, THREAD#, MAX(SEQUENCE#) LOG_APPLIED,  MAX(COMPLETION_TIME) APPLIED_TIME  FROM V$ARCHIVED_LOG WHERE STANDBY_DEST = 'YES' AND  APPLIED='YES' and resetlogs_id=(select distinct resetlogs_id from gv$database_incarnation where status='CURRENT') GROUP BY DEST_ID, THREAD#) S
WHERE P.DEST_ID!=S.DEST_ID
  AND P.THREAD#=S.THREAD#;




Запуск Apply Process на standby:

(start redo apply)
SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION;

(start redo apply using real-time apply)
SQL> alter database recover managed standby database using current logfile disconnect;


Остановка Apply Process на standby:

ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;



Apply с отставанием 90 мин.:

ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DELAY 90 DISCONNECT FROM SESSION;
проверяем:
select delay_mins from v$archive_dest where dest_id = 2;

Отменяем задержку:
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE NODELAY DISCONNECT FROM SESSION;


Проверка apply:

на primary
alter database open;
alter system set log_archive_dest_state_2=enable

Пробуем тестировать
на primary:
alter system switch logfile;
убедится что логфайл был успешно применен на standby
select SEQUENCE#,FIRST_TIME,NEXT_TIME,ARCHIVED,APPLIED,creator from v$archived_log where status = 'A' order by sequence# ;

Switchover

На primary:

SQL> SELECT SWITCHOVER_STATUS FROM V$DATABASE;

SWITCHOVER_STATUS
--------------------
SESSIONS ACTIVE

Освобождаем базу от активных сессий

SQL> SELECT SWITCHOVER_STATUS FROM V$DATABASE;

SWITCHOVER_STATUS
--------------------
TO STANDBY

Теперь можно переключаться.

На primary:

SQL> alter database commit to switchover to physical standby;
Database altered.

SQL> shutdown immediate;
ORA-01507: database not mounted
ORACLE instance shut down.

SQL> startup nomount;
SQL> alter database mount standby database;
SQL> alter database recover managed standby database using current logfile disconnect;


На standby:

SQL> alter database commit to switchover to primary;
SQL> alter database open;
SQL>SHUTDOWN IMMEDIATE;
SQL>STARTUP;

На new_primary:

SQL> select max(sequence#) from v$archived_log;
SQL> alter system switch logfile;
SQL> select max(sequence#) from v$archived_log;

Смотрим на new_standby:

SQL> select max(sequence#) from v$archived_log;



Failover:

select * from v$archive_gap;
alter database register logfile '';
select unique thread# as "Thread", max(sequence#) over (partition by thread#) as "Last" from v$archived_log;

alter database recover managed standby database finish;
(или если нет standby redo logs)
alter database recover managed standby database finish skip standby logfile;

alter database commit to switchover to primary;
shutdown immediate;
startup;



select switchover status from v$database;


NOT ALLOWED - database no primary
PREPARING DICTIONARY - Logical standby preparing switch
PREPARING SWITCHOVER - Logical standby preparing switch.
RECOVERY NEEDED - Standby
SESSION ACTIVE - end active session before switchover
SWITHOVER PENDING - Standby
SWITCHOVER LATENT - switchover not end
TO LOGICAL STANBY - Logical standby
TO STANDBY


The following fixed views can be used to monitor the DataGuard Broker and Standby proc:

V$ARCHIVE_DEST Describes for the current instance, all the archived redo log destinations, their current value, mode, and status.
V$ARCHIVE_DEST_STATUS Displays runtime and configuration information for the archived redo log destinations.
V$ARCHIVED_LOG Displays archived redo log information from the control file, including archived log names.
V$DATABASE Provides database information from the control file. Including the status of the database.
V$LOG The view contains log file information from the online redo logs.

V$ARCHIVED_LOG Displays archived redo log information from the control file, including archived log names.
V$DATABASE Provides database information from the control file. Including the status of the database.
V$LOGFILE Contains information about the online/dataguard redo logs.
V$MANAGED_STANDBY Displays current and status information for some Oracle database server processes related to Data Guard.
V$STANDBY_LOG Display information about the dataguard redo logs.
V_$STANDBY_APPLY_SNAPSHOT
V_$DATAGUARD_CONFIG
V_$DATAGUARD_STATS
V_$DATAGUARD_STATUS


 Догоняем standby :

1. On the standby database, stop the managed recovery process (MRP):
(1. Остановка наката на стендбае)
SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;

2.On the standby database, find the SCN which will be used for the incremental backup at the primary database:
(определяем SCN, от которого нужно делать на примари инкрементальный бэкап)

SQL> SELECT CURRENT_SCN FROM V$DATABASE; # 8708213563871

3. In RMAN, connect to the primary database and create an incremental backup from the SCN derived in the previous step:
(Подключившись RMAN'ом к примари делаем инкрементальный бэкап от SCN, полученного в предыдущем шаге)

RMAN> BACKUP INCREMENTAL FROM SCN 8708213563871 DATABASE FORMAT '/flashback/incr_back/inc_%U' tag 'FORSTANDBY';

4.Transfer all backup sets created on the primary system to the standby system (note that there may be more than one backup file created). For example:
(переносим полученную инкрементальную копию на хост стендбая. Например:

SCP /flashback/incr_back/inc_* sbl-db-nw:/sblflashback/incr_back

5. Connect to the standby database as the RMAN target, and catalog all incremental backup pieces:
(подключаемся RMAN к стэндбаю и "каталогизируем" все части этого инкрементального бэкапа)

RMAN> CATALOG START WITH '/sblflashback/incr_back';

6.Recover the standby database with the cataloged incremental backup:
(Накатываем этот бэкап)

RMAN> RECOVER DATABASE NOREDO;

7. In RMAN, connect to the primary database and create a standby control file backup:
(Подключившись RMAN к примари, делаем стендбайную копию контролфайла):

RMAN> BACKUP CURRENT CONTROLFILE FOR STANDBY FORMAT '/flashback/incr_back/ctrl_%U.stb';

8. Copy the standby control file backup to the standby system. For example:
(копируем её на стендбай)
SCP /flashback/incr_back/ctrl_*.stb sbl-db-nw:/sblflashback/incr_back

9,10,11. Восстанавливаем контролфайл на стендбае из свежей копии:

RMAN> SHUTDOWN IMMEDIATE;
RMAN> STARTUP NOMOUNT;
RMAN> RESTORE STANDBY CONTROLFILE FROM '/sblflashback/incr_back/ctrl_4umj23o5_1_1.stb';
RMAN> SHUTDOWN;
RMAN> STARTUP MOUNT;
_____________________________________________________
12,13 - Если местоположение датафайлов или redo-логов на стендбае отличается от примари:
If the primary and standby database data file directories are identical, skip to step 13. If the primary and standby database data file directories are different, then in RMAN, connect to the standby database, catalog the standby data files, and switch the standby database to use the just-cataloged data files. For example:

RMAN> CATALOG START WITH '+DATA_1/CHICAGO/DATAFILE/';
RMAN> SWITCH DATABASE TO COPY;

If the primary and standby database redo log directories are identical, skip to step 14. Otherwise, on the standby database, use an OS utility or the asmcmd utility (if it is an ASM-managed database) to remove all online and standby redo logs from the standby directories and ensure that the LOG_FILE_NAME_CONVERT parameter is properly defined to translate log directory paths. For example, LOG_FILE_NAME_CONVERT='/BOSTON/','/CHICAGO/'.
___________________________________________________

14. On the standby database, clear all standby redo log groups (there may be more than 3):
(на стендбае очищаем гурппы редо-логов)
ALTER DATABASE CLEAR LOGFILE GROUP 11;
ALTER DATABASE CLEAR LOGFILE GROUP 2;
ALTER DATABASE CLEAR LOGFILE GROUP 3;
ALTER DATABASE CLEAR LOGFILE GROUP 4;
ALTER DATABASE CLEAR LOGFILE GROUP 5;
ALTER DATABASE CLEAR LOGFILE GROUP 6;
ALTER DATABASE CLEAR LOGFILE GROUP 7;

15. On the standby database, restart Flashback Database:
(если используется FlashBack, то на стендбае перезапускаем его, чтобы "очистить");
SQL> ALTER DATABASE FLASHBACK OFF;
SQL> ALTER DATABASE FLASHBACK ON;

15.1 Накатываем часть логов
SYS@SIBDB> recover standby database until cancel;

16. On the standby database, restart MRP:
(На стендбае рестартуем накат логов)
SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT;

Или лучше из консоли Data Guard'а

dgmgrl /
DGMGRL> show configuration # - смотрим, с какими именами базы сконфигурированы.

# Для SIBDB :   Databases:
#    sibdb_pr - Primary database
#    sibdb_st - Physical standby database

DGMGRL> edit database sibdb_st set state='APPLY-ON';

Succeeded.

   Проверяем
DGMGRL> show database verbose sibdb_st
Database
  Name:            sibdb_st
  Role:            PHYSICAL STANDBY
  Enabled:         YES
  Intended State:  APPLY-ON
  Instance(s):     ^^^^^^^__________!!!!!!!!
    SIBDB
............



startup nomount;
alter database mount standby database;
recover standby database until cancel;




 ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
 ++++++++++++++++++++++++++++++++++++++++++++++++++
На стэндбае упал накат с 600 ошибкой. Сделали оффлайн дроп файлу, на который он ругнулся и продолжили накат:

1. остановка наката логов на стендбай
2. создание копии файла
RMAN> backup as compressed backupset datafile 237 format '/flashback2/backup/bk_%s_%p_%t' ;
#3. создание нового стэндбайного контролфайла:
#SQL> alter database create standby controlfile as '/flashback2/backup/ctl_SIBDB';
4. копирование
scp -p bk_86885_1_774530065 10.84.160.194:/oracle/arch/SIBDB/back/
#scp -p ctl_SIBDB 10.84.160.194:/oracle/admin/SIBDB/
5. Определяем каталог бэкапа для восстановления:
RMAN> CATALOG START WITH '/oracle/arch/SIBDB/back';
6. и восстанавливаем файл
RMAN> restore datafile 237;
7. переводим в онлайн
SQL> alter database datafile 237 online;
8. ручной накат
recover standby database until cancel;
9. как упадет с сообщение о неполном.отсутсвующем логе - толкаем накат
Alter database recover managed standby database disconnect;


I Переключение ролей (switchower)

При настроенном dataguard broker:

dgmgrl sys/

show configuration

Configuration - itsmprod

  Protection Mode: MaxProtection
  Databases:
    itsmprod     - Primary database
    itsmres      - Physical standby database
    itsmprod_stb - Physical standby database
      Warning: ORA-16826: apply service state is inconsistent with the DelayMins property

(переключаем primary на itsmres)

DGMGRL> switchover to itsmres;

по результатам отработки команды будут выданы "подсказки", что нужно сделать с переключаемыми инстансами:

Стартовать инстансы руками в нужном режиме (mount - для нового стендбая, open - для нового праймери)

Переподключится утилитой dgmgrl и еще раз проверить конфигурацию:

dgmgrl sys/

show configuration

Configuration - itsmprod

  Protection Mode: MaxProtection
  Databases:
    itsmprod     - Physical standby database
    itsmres      - Primary database
    itsmprod_stb - Physical standby database

------------------------------------------------------
Без dataguard broker

1.
-- Convert primary database to standby
CONNECT / AS SYSDBA
ALTER DATABASE COMMIT TO SWITCHOVER TO STANDBY;

-- Shutdown primary database
SHUTDOWN IMMEDIATE;

-- Mount old primary database as standby database
STARTUP NOMOUNT;
ALTER DATABASE MOUNT STANDBY DATABASE;
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION;

+++
-- Convert standby database to primary
CONNECT / AS SYSDBA
ALTER DATABASE COMMIT TO SWITCHOVER TO PRIMARY;

-- Shutdown standby database
SHUTDOWN IMMEDIATE;

-- Open old standby database as primary
STARTUP;

++++++++++++++++++++++++++++++++++++++++++++++++


II Failover - открытие стендбая как праймери при недоступности текущего праймери:

При настроенном dataguard broker:

dgmgrl sys/

DGMGRL> failover to itsmres;

Стендбай автоматически открывается как праймери, конфигурация gataguard разрушается.

----------------------------------------

Без dataguard broker

ALTER DATABASE RECOVER MANAGED STANDBY DATABASE FINISH;
ALTER DATABASE ACTIVATE STANDBY DATABASE;

--------------------------------------