Passo a passo para criar um Dataguard FISICO em RAC 10g R2 com 3 nós

Dataguard FISICO em RAC 10g
R2


CONFIGURAÇÂO
Nossa
Base
de Produção será a BDAP e roda em RAC 10g R2 com
3 nós (cot-db-001,cot-db-002, cot-db-003)
DB_UNIQUE_NAME do
site Primary é BDAP e no Standby Site é
BDCUR
Producção/Primary Site está em
COTIA.
Nos prefixamos DB Aliases com “COT” quando
referenciamos explicitamente para o banco de COTIA
Nosso Standby
Site estará em CURITIBA.
Nos prefixamos DB Aliases com
“CUR” quando referenciamos explicitamente para o banco de
CURITIBA (standby)
Nosso Standby também rodará em
RAC 10g R2 com3 Nós (cur-db-001, cur-db-002,
cur-db-003)
Iremos usar Log Write (LGWR) em modo assincrono para o
transporte de redo.
Qualquer um dos nós no site Standby
podem receber os redo/Archives, porem somente um nó (Nó
1 em nosso caso) ira aplicá-los.
ORACLE_HOME é
/oracle/product/app/10R2/db/
ASM_HOME é
/oracle/product/asm
Clusterware, RDBMS e ASM Software já
estão instalados no Site Standby.


Configurando o Banco primário para
Data Guard- habilite force logging no Banco primário


alter database force logging;


Ajuste Standby para maximizar a
performance.


ALTER DATABASE SET STANDBY DATABASE TO MAXIMIZE
PERFORMANCE;


Configuração dos parâmetros
de inicialização no Banco primário.


alter system set DB_UNIQUE_NAME=’BDAP’
scope=spfile sid=’*’;
alter system set
LOG_ARCHIVE_CONFIG=’DG_CONFIG=(BDAP,BDCUR)’ scope=spfile
sid=’*’;
alter system set
LOG_ARCHIVE_DEST_1=’LOCATION=USE_DB_RECOVERY_FILE_DEST
VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=BDAP’ scope=spfile
sid=’*’;
alter system set LOG_ARCHIVE_DEST_2=’SERVICE=CURBDCUR
VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=BDCUR LGWR
ASYNC=102400 REOPEN=15 MAX_FAILURE=10 NET_TIMEOUT=30 NOAFFIRM
OPTIONAL’ scope=spfile sid=’*’;
alter system set
LOG_ARCHIVE_DEST_STATE_1=ENABLE scope=spfile sid=’*’;
alter system
set LOG_ARCHIVE_DEST_STATE_2=DEFER scope=spfile sid=’*’;
alter
system set log_archive_format=’arch_%d_%s_%p_%t.arc’ scope=spfile
sid=’*’;
alter system set FAL_SERVER=BDCUR1,BDCUR2,BDCUR3
scope=spfile sid=’*’;
alter system set FAL_CLIENT=COTBDAP
scope=spfile sid=’*’;
alter system set
STANDBY_FILE_MANAGEMENT=AUTO scope=spfile sid=’*’;
alter system
set LOG_FILE_NAME_CONVERT='(‘,’)’ scope=spfile sid=’*’;
alter
system set service_names=’BDAP’,’COTBDAP’ scope=spfile sid=’*’;


Habilitar o broken connection detection
ajustando o parâmetro SQLNET.EXPIRE_TIME para 2 (minutos) no
arquivo de parâmetro SQLNET.ORA em TODOS nós Primários


vi
$ORACLE_HOME/network/admin/sqlnet.ora
SQLNET.EXPIRE_TIME=2


No nó Primario adicionar essas
entradas no tnsnames.ora


vi
$ORACLE_HOME/network/admin/tnsnames.ora
##########################################
#
Data Guard
##########################################
COTBDAP
=
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST =
cot-db-001-vip)(PORT = 1521))
(ADDRESS = (PROTOCOL = TCP)(HOST =
cot-db-002-vip)(PORT = 1521))
(ADDRESS = (PROTOCOL = TCP)(HOST =
cot-db-003-vip)(PORT =
1521))
(LOAD_BALANCE=ON)
(FAILOVER=ON)
(CONNECT_DATA
=
(SERVER=DEDICATED)
(SERVICE_NAME = BDAP)
(FAILOVER_MODE
=
(TYPE = SELECT)
(METHOD = BASIC)
(RETRIES = 20)
(DELAY
= 60)
)
)
)
)
BDAP1 =
(DESCRIPTION =
(ADDRESS =
(PROTOCOL = TCP)(HOST = cot-db-001-vip)(PORT = 1521))
(CONNECT_DATA
=
(SERVER=DEDICATED)
(SERVICE_NAME = BDAP)
(INSTANCE_NAME =
BDAP1)
)
)


BDAP2 =
(DESCRIPTION =
(ADDRESS = (PROTOCOL
= TCP)(HOST = cot-db-002-vip)(PORT = 1521))
(CONNECT_DATA
=
(SERVER=DEDICATED)
(SERVICE_NAME = BDAP)
(INSTANCE_NAME =
BDAP2)
)
)


BDAP3 =
(DESCRIPTION =
(ADDRESS = (PROTOCOL
= TCP)(HOST = cot-db-003-vip)(PORT = 1521))
(CONNECT_DATA
=
(SERVER=DEDICATED)
(SERVICE_NAME = BDAP)
(INSTANCE_NAME =
BDAP3)
)
)


BDCUR =
(DESCRIPTION =
(ADDRESS = (PROTOCOL
= TCP)(HOST = cur-db-001-vip)(PORT = 1521))
(ADDRESS = (PROTOCOL =
TCP)(HOST = cur-db-002-vip)(PORT = 1521))
(ADDRESS = (PROTOCOL =
TCP)(HOST = cur-db-003-vip)(PORT =
1521))
(LOAD_BALANCE=ON)
(FAILOVER=ON)
(CONNECT_DATA
=
(SERVER=DEDICATED)
(SERVICE_NAME = BDAP)
(FAILOVER_MODE
=
(TYPE = SELECT)
(METHOD = BASIC)
(RETRIES = 20)
(DELAY
= 60)
)
)
)
BDCUR1 =
(DESCRIPTION =
(ADDRESS =
(PROTOCOL = TCP)(HOST = cur-db-002-vip)(PORT = 1521))
(CONNECT_DATA
=
(SERVICE_NAME = BDAP)
(INSTANCE_NAME = BDAP1)
)
)


BDCUR2 =
(DESCRIPTION =
(ADDRESS = (PROTOCOL
= TCP)(HOST = cur-db-002-vip)(PORT = 1521))
(CONNECT_DATA
=
(SERVICE_NAME = BDAP)
(INSTANCE_NAME = BDAP2)
)
)
BDCUR3
=
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST =
cur-db-003-vip)(PORT = 1521))
(CONNECT_DATA =
(SERVICE_NAME =
BDAP)
(INSTANCE_NAME = BDAP3)
)
)
Atualizar os
arquivos listener.ora nos 3 nós Primários


vi
$ORACLE_HOME/network/admin/listener.ora


PROD NÓ COT-DB-001
=========


SID_LIST_LISTENER_ cot-db-001 =
(SID_LIST
=
(SID_DESC =
(SID_NAME = PLSExtProc)
(ORACLE_HOME =
/oracle/product/10.2.0/db)
(PROGRAM = extproc)
)
(SID_DESC
=
(GLOBAL_DBNAME = COTBDAP_DGMGRL)
(ORACLE_HOME =
/oracle/product/10.2.0/db)
(SID_NAME = BDAP1)
)
(SID_DESC
=
(SID_NAME = +ASM1)
(ORACLE_HOME =
/oracle/product/asm)
(GLOBAL_DBNAME = +ASM1)
)
)


PROD NÓ COT-DB-002
=========


SID_LIST_LISTENER_ cot-db-002 =
(SID_LIST
=
(SID_DESC =
(SID_NAME = PLSExtProc)
(ORACLE_HOME =
/oracle/product/10.2.0/db)
(PROGRAM = extproc)
)
(SID_DESC
=
(GLOBAL_DBNAME = COTBDAP_DGMGRL)
(ORACLE_HOME =
/oracle/product/10.2.0/db)
(SID_NAME = BDAP2)
)
(SID_DESC
=
(SID_NAME = +ASM2)
(ORACLE_HOME =
/oracle/product/asm)
(GLOBAL_DBNAME = +ASM2)
)
)


PROD NÓ COT-DB-003
===========


SID_LIST_LISTENER_ cot-db-003 =
(SID_LIST
=
(SID_DESC =
(SID_NAME = PLSExtProc)
(ORACLE_HOME =
/oracle/product/10.2.0/db)
(PROGRAM = extproc)
)
(SID_DESC
=
(GLOBAL_DBNAME = COTBDAP_DGMGRL)
(ORACLE_HOME =
/oracle/product/10.2.0/db)
(SID_NAME = BDAP3)
)
(SID_DESC
=
(SID_NAME = +ASM3)
(ORACLE_HOME =
/oracle/product/asm)
(GLOBAL_DBNAME = +ASM3)
)
)
Recarregue
os listeners em todos os nós


PROD NÓ
COT-DB-001
=========
lsnrctl
reload LISTENER_ cot-db-001


PROD NÓ
COT-DB-002
=========
lsnrctl
reload LISTENER_ cot-db-002


PROD NÓ
COT-DB-003
=========
lsnrctl
reload LISTENER_ cot-db-003


Reiniciar o banco de dados


Criar os standby redo logs para suportar o papel
de standby. Os standby redo logs devem ser do mesmo tamanho que os
online logs do banco primário.


O numero recomendado de standby redo logs é:
(número máximo de logfiles para cada thread +1) *
máximo # de threads


No nosso caso o número total de Redo logs
groups = (4 + 1) * 3 =15


ALTER DATABASE ADD STANDBY LOGFILE THREAD 1
GROUP
13 SIZE 512000K,
GROUP 14 SIZE 512000K,
GROUP 15 SIZE
512000K,
GROUP 16 SIZE 512000K,
GROUP 17 SIZE 512000K;
ALTER
DATABASE ADD STANDBY LOGFILE THREAD 2
GROUP 18 SIZE 512000K,
GROUP
19 SIZE 512000K,
GROUP 20 SIZE 512000K,
GROUP 21 SIZE
512000K,
GROUP 22 SIZE 512000K;
ALTER DATABASE ADD STANDBY
LOGFILE THREAD 3
GROUP 23 SIZE 512000K,
GROUP 24 SIZE
512000K,
GROUP 25 SIZE 512000K,
GROUP 26 SIZE 512000K,
GROUP
27 SIZE 512000K;


Pegue os arquivos e faça um Backup, no nó
primário, faça um RMAN backup do banco primário
coloque os backup pieces no diretório de staging. Por exemplo:


/oracle/dba/local/bin/rman_backup.ksh BDAP 0
rman
connect catalog rman/password@rman_catalog
backup
current controlfile for standby;
EOF


No nó primário, identifique
o diretório corrente do rman e do backup dos archives.


/DADOS/FULL/rman e /DADOS/FULL/archbkp


Crie exatamente o mesmo caminho em uma das
maquinas do standby database:


mkdir -p /DADOS/FULL/rman
mkdir -p
/DADOS/FULL/archbkp


OBS: (no meu caso é uma area de NFS
que já estava criada)


No nó primário, conecte no
banco primário e crie um PFILE pelo SPFILE no diretório
de staging. Por exemplo:


CREATE
PFILE=’/ora01/orabkup/BDAP/rman/BDAP/20090825/initBDAP1.ora’ FROM
SPFILE;


Copie o arquivo de password para a área
de staging.


cp $ORACLE_HOME/dbs/orapwBDAP1
/ora01/orabkup/BDAP/rman/BDAP/20090825


Coloque a copia do tnsnames.ora, e
sqlnet.ora diretório de staging
.


cp $ORACLE_HOME/network/admin/tnsnames.ora
/ora01/orabkup/BDAP/rman/BDAP/20090825
cp
$ORACLE_HOME/network/admin/sqlnet.ora
/ora01/orabkup/BDAP/rman/BDAP/20090825


Copie o conteúdo do diretório
de staging do nó primário do RAC para o do diretório
de staging que foi criado nó de standby.


scp /ora01/orabkup/BDAP/rman/BDAP/20090825/*
oracle@cur-db-001:/ora01/orabkup/BDAP/rman/BDAP/20090825


Configure o Standby
Copie o tnsnames.ora, e o
sqlnet.ora do diretorio de staging no host standby para o diretório
$ORACLE_HOME/network/admin


cd /ora01/orabkup/BDAP/rman/BDAP/20090825/
cp
tnsnames.ora $ORACLE_HOME/network/admin/


Modifique o tnsnames.ora


vi $ORACLE_HOME/network/admin/tnsnames.ora


substitua os host names
(cot-db-00,cur-db-00) para os DR host names, Copie o tnsnames.ora to
mofificado para os outros nós no Standby


scp $ORACLE_HOME/network/admin/tnsnames.ora
oracle@cur-db-002:
/oracle/product/10.2.0/db_1/network/admin/
scp
$ORACLE_HOME/network/admin/tnsnames.ora oracle@cur-db-003:
/oracle/product/10.2.0/db_1/network/admin/


Certifique-se que as seguintes linhas
sejam adicionadas no linstener.ora on de todos os nós.


vi $ORACLE_HOME/network/admin/listener.ora


DR NÓ
CUR-DB-001
================
SID_LIST_LISTENER_cur-db-001
=
(SID_LIST =
(SID_DESC =
(SID_NAME =
PLSExtProc)
(ORACLE_HOME = /oracle/product/10.2.0/db)
(PROGRAM
= extproc))
(SID_DESC =
(GLOBAL_DBNAME =
BDCUR_DGMGRL)
(ORACLE_HOME = /oracle/product/10.2.0/db)
(SID_NAME
= BDAP1))
(SID_DESC =
(SID_NAME = +ASM1)
(ORACLE_HOME =
/oracle/product/asm)
(GLOBAL_DBNAME = +ASM1)
)
)


DR NÓ CUR-DB-002
==================


SID_LIST_LISTENER_cur-db-002 =
(SID_LIST
=
(SID_DESC =
(SID_NAME = PLSExtProc)
(ORACLE_HOME =
/oracle/product/10.2.0/db)
(PROGRAM = extproc))
(SID_DESC
=
(GLOBAL_DBNAME = BDCUR_DGMGRL)
(ORACLE_HOME =
/oracle/product/10.2.0/db)
(SID_NAME = BDAP2))
(SID_DESC
=
(SID_NAME = +ASM2)
(ORACLE_HOME =
/oracle/product/asm)
(GLOBAL_DBNAME = +ASM2)))


DR NÓ
CUR-DB-003
=================
SID_LIST_LISTENER_cur_db_003
=
(SID_LIST =
(SID_DESC =
(SID_NAME =
PLSExtProc)
(ORACLE_HOME = /oracle/product/10.2.0/db)
(PROGRAM
= extproc))
(SID_DESC =
(GLOBAL_DBNAME =
BDCUR_DGMGRL)
(ORACLE_HOME = /oracle/product/10.2.0/db)
(SID_NAME
= BDAP3))
(SID_DESC =
(SID_NAME = +ASM3)
(ORACLE_HOME =
/oracle/product/asm)
(GLOBAL_DBNAME = +ASM3)
)
)


Recarregue todos os listeners em todos os nós


DR NÓ CUR-DB-001
=================
lsnrctl
reload LISTENER_cur-db-001


DR NÓ CUR-DB-002
=================
lsnrctl
reload LISTENER_cur-db-002


DR NÓ CUR-DB-003
=================
lsnrctl
reload LISTENER_cur-db-003


Crie o Banco e as instancias
Standby, Copie o arquivo de Password da staging area para o
ORACLE_HOME/dbs directory


cd
/ora01/orabkup/BDAP/rman/BDAP/20090825/
cp orapwBDAP1
$ORACLE_HOME/dbs/
cd $ORACLE_HOME/dbs
scp orapwBDAP1
oracle@cur-db-002:/oracle/product/10.2.0/db/dbs/orapwBDAP2
scp
orapwBDAP1 oracle@cur-db-003:/oracle/product/10.2.0/db/dbs/orapwBDAP3



Copie
o Pfile da staging area para o ORACLE_HOME/dbs directory


cd
/ora01/orabkup/BDAP/rman/BDAP/20090825/
cp initBDAP1.ora
$ORACLE_HOME/dbs/


Modifique o pfile com os
seguintes parâmetros:


vi
$ORACLE_HOME/dbs/initBDAP1.ora
*.audit_trail=’NONE’
*.db_create_file_dest=’+DG1′
*.db_unique_name=’BDCUR’
*.fal_client=’CURBDCUR’
*.fal_server=’BDAP1′,’BDAP2′,’BDAP3′
*.log_archive_config=’DG_CONFIG=(BDCUR,COTBDAP)’
alter
system set log_archive_format=’arch_%d_%s_%p_%t’.arc’ scope=spfile
sid=’*’;
*.log_archive_dest_1=’LOCATION=USE_DB_RECOVERY_FILE_DEST
VALID_FOR=(ALL_LOGFILES,ALL_ROLES)
DB_UNIQUE_NAME=BDCUR’
*.control_files=’+DG1/BDAP/control01.ctl’,’+DG2/BDAP/control02.ctl’
*.log_archive_dest_2=’SERVICE=COTBDAP
VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=BDAP LGWR
ASYNC=102400 REOPEN=15 MAX_FAILURE=10 NET_TIMEOUT=30 NOAFFIRM
OPTIONAL’
*.service_names=’BDAP’,’BDCUR’


Conecte no ASM instance do
standby host, e crie um diretório para o database


alter diskgroup DG1 add directory
‘+DG1/BDCUR’;
alter diskgroup DG2 add directory ‘+DG2/BDCUR’;


Conecte no standby database
em um standby host, com o standby em estado IDLE, e crie o SPFILE no
standby disk group DG_DATA1


CREATE
SPFILE=’+DG1/BDCUR/spfileBDAP.ora’ FROM
PFILE=’?/dbs/initBDAP1.ora’;
No
diretorio $ORACLE_HOME/dbs de cada standby host, crie um PFILE que
seja nomeado initoracle_sid.ora e contenha um apontamento para o
SPFILE.


NO DR NÓ
CUR-DB-001
======================
cd $ORACLE_HOME/dbs
cp
initBDAP1.ora initBDAP1.ora.full
echo
"SPFILE=’+DG1/BDCUR/spfileBDAP.ora’" > initBDAP1.ora
scp
initBDAP1.ora
oracle@cur-db-002:/oracle/product/10.2.0/db/dbs/initBDAP2.ora
scp
initBDAP1.ora
oracle@cur-db-003:/oracle/product/10.2.0/db/dbs/initBDAP3.ora


Certifique-se que todos
caminhos existam, caso contrário crie.


mkdir -p
/oracle/admin/BDAP/adump
mkdir /oracle/admin/BDAP/bdump
mkdir
/oracle/admin/BDAP/cdump
mkdir /oracle/admin/BDAP/udump
mkdir
/oracle/admin/BDAP/pfile
mkdir /oracle/admin/BDAP/create
mkdir
/oracle/admin/BDAP/scripts
mkdir /oracle/admin/BDAP/hdump


Configure o ambiente para
suportar o Standby Database edite o /etc/oratab para BDAP no
dataguard


NO DR NÓ
CUR-DB-001:
————-
BDAP1:/oracle/product/app/10R2/db/:Y


NO DR NÓ
CUR-DB-002:
————-
BDAP2:/oracle/product/app/10R2/db/:Y


NO DR NÓ
CUR-DB-003:
————-
BDAP3:/oracle/product/app/10R2/db/:Y


Tenha certesa que as
variaveis ORACLE_HOME e ORACLE_SID estejam declaradas.


Depois de ajustar todas
variáveis de ambiente em cada standby host, como ORACLE_SID,
ORACLE_HOME, e PATH, inicie a instancia do banco standby no standby
host que tem o diretório (criado anteriormente) de staging.


On DR Node A


============


STARTUP NOMOUNT


Restaure o backup do primary
usando o comando duplicate do rman.


rman
connect target
sys/password@COTBDAP

connect
auxiliary /
connect catalog rman/password@rman_catalog

run
{
allocate auxiliary channel disk1 device type disk;
allocate
auxiliary channel disk2 device type disk;
duplicate target
database for standby;
}
Conecte no
standby database, e crie os standby redo logs para suportar o papel
de standby.
Os standby redo logs devem ser do mesmo tamanho que os
primary database online logs.


select thread#, group# from
V$STANDBY_LOG order by 1,2;


Se já existir apague
usando o seguinte comando :


ALTER DATABASE drop logfile group 13,
group 14, group 15, group 16, group 17, group 18, group 19, group 20,
group 21, group 22, group 23, group 24, group 25, group 26, group
27;
The recommended number of standby redo logs is:


(maximum number of logfiles for each
thread +1) * maximo # de threads


No meu caso numero total de grupos de Redo logs =
(4 + 1) * 3 = 15


ALTER DATABASE ADD STANDBY LOGFILE
THREAD 1
GROUP 13 SIZE 512000K,
GROUP 14 SIZE 512000K,
GROUP
15 SIZE 512000K,
GROUP 16 SIZE 512000K,
GROUP 17 SIZE
512000K;
ALTER DATABASE ADD STANDBY LOGFILE THREAD 2
GROUP 18
SIZE 512000K,
GROUP 19 SIZE 512000K,
GROUP 20 SIZE
512000K,
GROUP 21 SIZE 512000K,
GROUP 22 SIZE 512000K;
ALTER
DATABASE ADD STANDBY LOGFILE THREAD 3
GROUP 23 SIZE 512000K,
GROUP
24 SIZE 512000K,
GROUP 25 SIZE 512000K,
GROUP 26 SIZE
512000K,
GROUP 27 SIZE 512000K;


Inicie enviando o Redo para o
standby database No PRIMARY node


alter system set
log_archive_dest_state_2=enable scope=both sid=’*’;


Inicie o database recovery
session no standby system


ALTER DATABASE RECOVER MANAGED
STANDBY DATABASE USING CURRENT LOGFILE DISCONNECT;


Configure o CRS


srvctl remove database -d BDAP
srvctl
remove instance -d BDAP -i BDAP1
srvctl remove instance -d BDAP -i
BDAP2
srvctl remove instance -d BDAP -i BDAP3


srvctl add database -d BDAP -o
/oracle/product/app/10R2/db -r PHYSICAL_STANDBY -s mount -y
AUTOMATIC
srvctl add instance -d BDAP -i BDAP1 -n
cur-db-001
srvctl add instance -d BDAP -i BDAP2 -n
cur-db-002
srvctl add instance -d BDAP -i BDAP3 -n cur-db-003


srvctl add asm -n cur-db-001 -i ASM1
-o /oracle/product/asm
srvctl add asm -n cur-db-002 -i ASM2 -o
/oracle/product/asm
srvctl add asm -n cur-db-003 -i ASM3 -o
/oracle/product/asm



srvctl remove asm -n cur-db-001
-i asm1
srvctl remove asm -n cur-db-002 -i asm2
srvctl remove
asm -n cur-db-003 -i asm3


Reconfigure os agentes


/grid/agent/agent10g/bin/agentca -d
-n BDCUR -c cur-db-001,cur-db-002,cur-db-003


/grid/agent10g/agent10g/bin/agentca
-d -n BDAP -c cot-db-001,cot-db-002,cot-db-003


/grid/agent10g/agent10g/bin/agentca
-d -n COTBDAP -c cot-db-001,cot-db-002,cot-db-003


Para Stop/Restart
DG
==================
Para
Cancelar o recovery
*********************************************
NO
Standby
——
ALTER
DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;


NA PRODUÇÃO
——-
alter
system set log_archive_dest_state_2=defer scope=both sid=’*’;


Para abriro Standby en
Readonly Mode
*********************************************
ALTER
DATABASE OPEN;


para por o Database
novamente em recovery
mode

*********************************************

Envie o REDO
da PRODUÇÂO para
o standby database


alter system set
log_archive_dest_state_2=enable scope=both sid=’*’;


Inicie
o database recovery no standby


ALTER DATABASE RECOVER MANAGED
STANDBY DATABASE USING CURRENT LOGFILE DISCONNECT;


=======================================================================
=======================================================================

Scripts
para verificar o dataguard e saber se os archives estão sendo
aplicados/recebidos
=======================================================================
=======================================================================



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


SELECT THREAD#,SEQUENCE#,APPLIED,
REGISTRAR FROM V$ARCHIVED_LOG ORDER BY 1,2;


SELECT PROCESS, STATUS, THREAD#,
SEQUENCE#, BLOCK#, BLOCKS FROM V$MANAGED_STANDBY;


SELECT ARCHIVED_THREAD#,
ARCHIVED_SEQ#, APPLIED_THREAD#, APPLIED_SEQ# FROM
V$ARCHIVE_DEST_STATUS;


SELECT REGISTRAR, CREATOR, THREAD#,
SEQUENCE#, FIRST_CHANGE#, NEXT_CHANGE# FROM V$ARCHIVED_LOG;


SELECT MESSAGE FROM
V$DATAGUARD_STATUS;


SELECT THREAD#, SEQUENCE#,
FIRST_CHANGE#, NEXT_CHANGE# FROM V$LOG_HISTORY;


===============================================
Verifica
informações arquivadas nos archive logs do Primary
System
===============================================
passo
1 Determine o numero de sequencia do current archived redo log file.


SELECT THREAD#, SEQUENCE#, ARCHIVED,
STATUS FROM V$LOG WHERE STATUS=’CURRENT’;


Passo 2 Determine o archived
redo log file mais recente.


SELECT MAX(SEQUENCE#), THREAD# FROM
V$ARCHIVED_LOG GROUP BY THREAD#;


Passo 3 Determine o archived
redo log file mais recente em cada destino.


set lines 200
col destination
format a35
SELECT INST_ID, DESTINATION, STATUS, ARCHIVED_THREAD#,
ARCHIVED_SEQ# FROM gV$ARCHIVE_DEST_STATUS WHERE STATUS <>
‘DEFERRED’ AND STATUS <> ‘INACTIVE’;
set lines 80


Passo 4 Descubra se os
archived redo log files foram recebidos.


SELECT LOCAL.THREAD#, LOCAL.SEQUENCE#
FROM(SELECT THREAD#, SEQUENCE# FROM V$ARCHIVED_LOG WHERE
DEST_ID=1)LOCAL WHERELOCAL.SEQUENCE# NOT IN(SELECT SEQUENCE# FROM
V$ARCHIVED_LOG WHERE DEST_ID=2 ANDTHREAD# = LOCAL.THREAD#);


Verifique se tem GAP nos
Archives
============================
No
standby execute



SELECT * FROM V$ARCHIVE_GAP;



SCRIPTS ÚTEIS PARA DATAGUARD


conn sys/@BANCO2
as sysdba
select max(SEQUENCE#) from V$ARCHIVED_LOG;


conn sys@BANCO1 as
sysdba
SELECT SEQUENCE# sequencia, APPLIED
aplicado,
to_char(COMPLETION_TIME, ‘dd/mm/yy hh24:mi’)
dia_hora
FROM V$ARCHIVED_LOG
where SEQUENCE# in (select
max(SEQUENCE#) from
V$ARCHIVED_LOG);
—————————————————————————————–


SELECT SEQUENCE#,
to_char(FIRST_TIME, ‘dd-mm-yy
hh24:mi’)||’———‘||
to_char(NEXT_TIME, ‘dd-mm-yy
hh24:mi’)
FROM V$ARCHIVED_LOG
ORDER BY SEQUENCE#;


—————————————————————————————–
SELECT
SEQUENCE#,THREAD#,APPLIED FROM V$ARCHIVED_LOG ORDER BY
SEQUENCE#;
SELECT SEQUENCE#,APPLIED,to_char(COMPLETION_TIME,
‘dd/mm/yy hh24:mi’) FROM V$ARCHIVED_LOG ORDER BY SEQUENCE#


—————————————————————————————-

COLUMN
destination FORMAT A35 WRAP
column process format a7
column
archiver format a8
column ID format 99
select dest_id
"ID",destination,status,target,
archiver,schedule,process,mountid
from
v$archive_dest;


—————————————————————————————–
select
dest_id,
process,
transmit_mode,
async_blocks,
net_timeout,
delay_mins,
reopen_secs,
register,binding
from
v$archive_dest;


—————————————————————————————–
column
error format a55 tru
select dest_id,status,error
from
v$archive_dest;


—————————————————————————————–
column
message format a80
select message, timestamp
from
v$dataguard_status
where severity in (‘Error’,’Fatal’)
order by
timestamp;


—————————————————————————————–
select
ads.dest_id,
max(sequence#) "Current
Sequence",
max(log_sequence) "Last
Archived",
max(applied_seq#) "Last Sequence
Applied"
from v$archived_log al,
v$archive_dest
ad,
v$archive_dest_status ads
where ad.dest_id=al.dest_id
and
al.dest_id=ads.dest_id
group by ads.dest_id;


—————————————————————————————–
column
SRL_Count format 99
column SRL_Active format 99
select
dest_id,database_mode,recovery_mode,
protection_mode,
standby_logfile_count
"SRL_COUNT",
standby_logfile_active
"SRL_ACTIVE",
archived_seq#, applied_seq#
from
v$archive_dest_status;


—————————————————————————————–
select
process,
status,
client_process,
sequence#,
block#,active_agents,
known_agents
from
v$managed_standby;


—————————————————————————————–
select
group#,sequence#,bytes from v$standby_log;


—————————————————————————————–
select
group#,thread#,sequence#,bytes,archived,status from v$log;


—————————————————————————————–
select
* from
V$ARCHIVE_GAP
—————————————————————————————–


When the DBA queries the V$ARCHIVE_GAP view and
has a record returned, this indicates a gap in the archived redo logs
as illustrated below and may require manual intervention by the
DBA:
select * from v$archive_gap;


THREAD# LOW_SEQUENCE# HIGH_SEQUENCE#
——–
————– ————–
1 24 28








DICAS DE OUTROS BLOGS



From the output above, the physical standby
database is currently missing logs from sequence 24 to sequence 28
for thread 1. Note that this view only returns the next gap that is
currently blocking managed recovery from continuing. After resolving
the identified gap and starting managed recovery, the DBA should
query the V$ARCHIVE_GAP view again on the physical standby database
to determine the next (if any) gap sequence. This process should be
repeated until there are no more gaps. After identifying a gap (as
shown above), the DBA will need to query the primary database to
locate the archived redo logs on the primary database. The following
query assumes the local archive destination on the primary database
is LOG_ARCHIVE_DEST_1:


SELECT name
FROM v$archived_log
WHERE
thread# = 1
AND dest_id = 1
AND sequence# BETWEEN 24 and 28;


*****************************************************************************
col
logfl for a150
SELECT ‘ALTER DATABASE REGISTER PHYSICAL LOGFILE
‘||””||name||””||’;’ as logfl
FROM v$archived_log
WHERE
thread# = &inst
AND dest_id = 1
AND sequence# BETWEEN
&numero1 and
&numero2;
*****************************************************************************


— NAME

————————————–

/u02/oraarchive/TESTDB/arch_t1_s24.dbf

/u02/oraarchive/TESTDB/arch_t1_s25.dbf

/u02/oraarchive/TESTDB/arch_t1_s26.dbf

/u02/oraarchive/TESTDB/arch_t1_s27.dbf

/u02/oraarchive/TESTDB/arch_t1_s28.dbf


Copy the above redo log files to the physical
standby database and register them using the ALTER DATABASE REGISTER
LOGFILE …
SQL statement on the physical standby database
… For example:


ALTER DATABASE REGISTER LOGFILE
‘/u02/oraarchive/TESTDB/arch_t1_s24.dbf’;
ALTER DATABASE REGISTER
LOGFILE ‘/u02/oraarchive/TESTDB/arch_t1_s25.dbf’;
ALTER DATABASE
REGISTER LOGFILE ‘/u02/oraarchive/TESTDB/arch_t1_s26.dbf’;
ALTER
DATABASE REGISTER LOGFILE
‘/u02/oraarchive/TESTDB/arch_t1_s27.dbf’;
ALTER DATABASE REGISTER
LOGFILE ‘/u02/oraarchive/TESTDB/arch_t1_s28.dbf’;


After the redo logs have been registered on the
physical standby database, the DBA can restart the managed recovery
operations. For example, to put the physical standby database into
automatic recovery managed mode:
alter database recover managed
standby database disconnect from session;
select process,status
from v$managed_standby;




REAL-TIME APPLY


Use LGWR instead of ARCH to transport logs. Have
to cancel current managed recovery and then alter database recover
managed standby database using current logfile disconnect. (If not
cancelled, that command throws ORA-01153: an incompatible media
recovery is active.)
v$archive_dest_status.recovery_mode=’MANAGED
REAL TIME APPLY’.




LOGS NOT SHIPPED TO STANDBY


In emergency, copy missing logs to standby and
recover standby database, manually applying logs. When you run out of
logs and get "ORA-00308: cannot open archived log", alter
database recover managed standby database. Check Oracle Net and
log_archive_dest_state_n (must be ‘enable’, not ‘defer’). If no delay
is allowed in shipping logs, add NODELAY to log_archive_dest_n on
primary (but preferably on both so it stays like this after
switchover).
















Anúncios

Sobre Alexandre Pires

ORACLE OCS Goldengate Specialist, OCE RAC 10g R2, OCP 12C, 11g, 10g , 9i e 8i - Mais de 25 anos de experiência na área de TI. Participei de projetos na G&P alocado na TOK STOK, EDINFOR alocado na TV CIDADE "NET", 3CON Alocado no PÃO DE AÇUCAR, DISCOVER alocado na VIVO, BANCO IBI e TIVIT, SPC BRASIL, UOLDIVEO alocado no CARREFOUR e atualmente na ORACLE ACS atendendo os seguintes projetos: VIVO, CLARO, TIM, CIELO, CAIXA SEGUROS, MAPFRE, PORTO SEGURO, SULAMERICA, BRADESCO SEGUROS, BANCO BRADESCO, BASA, SANTANDER, CNJ, TSE, ELETROPAULO, EDP, SKY, NATURA, ODEBRESHT, NISSEI, SICREDI, CELEPAR, TAM, TIVIT, IBM, SMILES, CELEPAR, SERPRO,OKI,BANCO PAN, etc
Esse post foi publicado em DATAGUARD, PLSQL SCRIPTS, TUTORIAL e marcado , , , . Guardar link permanente.

Deixe um comentário

Preencha os seus dados abaixo ou clique em um ícone para log in:

Logotipo do WordPress.com

Você está comentando utilizando sua conta WordPress.com. Sair / Alterar )

Imagem do Twitter

Você está comentando utilizando sua conta Twitter. Sair / Alterar )

Foto do Facebook

Você está comentando utilizando sua conta Facebook. Sair / Alterar )

Foto do Google+

Você está comentando utilizando sua conta Google+. Sair / Alterar )

Conectando a %s