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
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;
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;
Отличный пост! Всё в одном месте. Повторил, то, что знал + узнал пару новых вещей. Как раз для себя хотел что-то подобное написать, чтобы не потерять. Спасибо!
ОтветитьУдалить