==============================================================
Olá Pessoal pra fechar o ano de 2009 com chave de ouro, estarei compartilhando minha experiência com Data Guard. Agradecimento ao meu amigo David Ricardo que me ajudou com os materiais e aprendizados do Data Guard. Bom vamos lá, como sabemos existem dois tipos de Standby. O Primeiro seria o Standby Manual que o sincronismo é realizado através de scripts linux não necessitando da Versão Enterprise Edition. Ja o Segundo tipo de Standby é o Data Guard, necessita da Versão Enterprise Edition no qual o sincronismo é realizado através dos Arquivos de Inicialização, do Listener e TNSNAMES. Nesse Post estarei comentando sobre o Data Guard no qual implementei em um ambiente de Produção faz um mês, espero que gostem.
So uma observação este meu How To é na versão database 10g, mas eu fiz testes com a versão 11g e está muito boa pois conseguimos sincronização em modo Read-Only, sim realmente é verdade eu testei rs.
==============================================================
==============================================================
AMBIENTE:
==============================================================
- Banco de Dados: Oracle 10g 10.2.0.4 – Enterprise Edition 
- Sistema Operacional: Red Hat Enterprise 5
- Banco de Dados Produção e Banco de Dados Standby estão localizados em diferentes maquinas em diferentes sites.
- Banco de dados Produção é chamado DB10G e o Standby ST10G.
==============================================================
Database DB_UNIQUE_NAME Oracle Net Service Name
==============================================================
Primary db10g db10g
———————————————————————————————————————————
Physical standby st10g st10g
==============================================================
==============================================================
I. PRÉ-REQUISITOS:
==============================================================
1. Garantir que o Sistema Operacional e a Plataforma da Arquitetura do Servidor Produção e Standby são os mesmos.
2. Instalar apenas o Software Oracle no Servidor Standby e Path se necessário.
Garantir o mesma Release do Software Oracle instalado no Servidor Produção e Standby, e caminho do Oracle Home necessitam ser idênticos.
3. Teste a criação do Banco de Dados Standby em um ambiente Teste para depois implementar em um ambiente Produção.
==============================================================
CONFIGURAR HOSTS NO SERVIDOR PRODUÇÃO E STANDBY /ETC/HOSTS
==============================================================
SERVIDOR PRODUÇÃO /ETC/HOSTS
127.0.0.1 localhost.localdomain localhost
192.168.199.239 db10g.db1.com.br db10g
192.168.199.241 st10g.db1.com.br st10g
::1 localhost6.localdomain6 localhost6
SERVIDOR STANDBY /ETC/HOSTS
127.0.0.1 localhost.localdomain localhost
192.168.199.241 st10g.db1.com.br st10g
192.168.199.239 db10g.db1.com.br db10g
::1 localhost6.localdomain6 localhost6
———————————————————————————————————-
CASO NÃO CONFIGURE PODE OCORRER O ERRO ABAIXO:
Warning: ORA-16607: one or more databases have failed
DGMGRL> SHOW DATABASE ‘st10g’ ‘LogXptStatus’;
Error: ORA-12545: Connect failed because target host or object does not exist
———————————————————————————————————-
==============================================================
CONFIGURAR VARIAVEIS DE AMBIENTE NO PRODUÇÃO E STANDBY
==============================================================
#Oracle Settings
TMP=/tmp
TMPDIR=$TMP
TEMP=$TMP
ORACLE_BASE=/oracle/app
export ORACLE_BASE
ORACLE_HOME=$ORACLE_BASE/product/10.2.0/db10g
export ORACLE_HOME
ORACLE_SID=db10g
export ORACLE_SID
ORACLE_TERM=xterm
export ORACLE_TERM
TNS_ADMIN=$ORACLE_HOME/network/admin
export TNS_ADMIN
PATH=$PATH:$ORACLE_HOME/bin:$PATH
LD_LIBRARY_PATH=$ORACLE_HOME/lib:/lib:/usr/lib
CLASSPATH=$ORACLE_HOME/JRE:$ORACLE_HOME/jlib:$ORACLE_HOME/rdbms/jlib
export CLASSPATH
export PATH
#LD_ASSUME_KERNEL=2.4.1; export LD_ASSUME_KERNEL
==============================================================
II. CONFIGURAR CONFIGURAR SERVIDOR PRODUÇÃO
==============================================================
Habilitar Forced Logging em seu Banco de Dados Produção:
SQL> ALTER DATABASE FORCE LOGGING;
Database altered.
Veificar se as Tablespaces estão em Modo Logging
SQL> SELECT TABLESPACE_NAME, LOGGING FROM DBA_TABLESPACES;
TABLESPACE_NAME LOGGING
—————————— ———
SYSTEM LOGGING
UNDOTBS1 LOGGING
SYSAUX LOGGING
TEMP NOLOGGING
USERS LOGGING
EXAMPLE LOGGING
KDB1 LOGGING
DB1LOB LOGGING
Criar um arquivo de senha se ele não existir:
Para verificar se um arquivo de senha ja existe, rode o seguinte comando:
SQL> select * from v$pwfile_users;
USERNAME SYSDB SYSOP
—————————— —– —–
SYS TRUE TRUE
Se ele não existir, use o seguinte comando para criar um:
cd $ORACLE_HOME/dbs
orapwd file=$ORACLE_HOME/dbs/pwdDB10G.ora PASSWORD=oracle ENTRIES=5
==============================================================
CONFIGURAR REDO LOG STANDBY
==============================================================
O Tamanho dos Arquivos de Redolog do Standby deveria ser compativel com o mesmo tamanho dos redo logs online do Banco de Dados Produção. Para encontrar o tamanho de seus redo log files:
SQL> select bytes from v$log;
BYTES
———-
52428800
52428800
52428800
Use o seguinte comnado para determinar seu grupo de log file corrente:
SQL> select group#, member from v$logfile;
GROUP# MEMBER
——————————————————————————–
3 /oracle/app/oradata/db10g/redo03.log
2 /oracle/app/oradata/db10g/redo02.log
1 /oracle/app/oradata/db10g/redo01.log
==============================================================
CRIAR STANDBY REDO LOG ONLINE:
==============================================================
Meu Banco de dados tem 3 grupos originalmente e Crie 3 Grupos de Redolog online 50M usando o seguinte comando:
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.
==============================================================
VERIFICAR STANDBY REDO LOG ONLINE CRIADO
==============================================================
Para verificar os resultados da criação dos Grupos de Redolog Standby, rode a seguinte Query:
SQL> Select GROUP#, DBID, STATUS, ARCHIVED, BYTES from v$Standby_log;
GROUP# DBID STATUS ARC BYTES
———- —————————————- ———- — ———-
4 UNASSIGNED UNASSIGNED YES 52428800
5 UNASSIGNED UNASSIGNED YES 52428800
6 UNASSIGNED UNASSIGNED YES 52428800
==============================================================
ATIVAR ARCHIVELOG SERVIDOR PRODUÇÃO:
==============================================================
Se o seu Banco de Dados não estiver em Modo Archivelog, habilitar em modo archivelog:
#Alterar os parêmetros (caso não esteja setado) log_archive_dest_1 para
#Verificar o formato do achivelog (Obs: apartir do Oracle10g é obrigatorio ter os 3 parametros (%s_%t_%r)
SQL> shutdown immediate;
SQL> startup mount;
SQL> alter system set log_archive_dest_1=’LOCATION=/archivelog/db10g’ scope=spfile;
SQL> alter system set log_archive_format = ‘%t_%s_%r.arc’ scope=spfile;
SQL> alter database archivelog;
==============================================================
ATIVAR LOG DE FLASHBACK NO BANCO DE DADOS PRODUÇÃO. FLASHBACK SERÁ NECESSÁRIO PARA FAST-START FAILOVERS
==============================================================
SQL> alter database flashback on;
SQL> shutdown immediate
SQL> startup
SQL> archive log list;
==============================================================
CRIAR PFILE A PARTIR DO SPFILE:
==============================================================
Criar (PFILE) do Arquivo de Parametro de Servidor(SPFILE),
para adicionar um novo parametro de regras no Banco de Dados Produção.
Criar pfile através do spfile para o Banco de Dados Produção:
SQL> create pfile=’$ORACLE_HOME/dbs/pfileDB10G.ora’ from spfile;
File created.
==============================================================
EDITAR PFILEDB10G.ORA PARA ADICIONAR NOVAS REGRAS DO DATAGUARD NO SERVIDOR PRODUÇÃO:
==============================================================
==================== INIT PRODUÇÃO ==========================
db10g.__db_cache_size=54525952
db10g.__java_pool_size=4194304
db10g.__large_pool_size=4194304
db10g.__shared_pool_size=100663296
db10g.__streams_pool_size=0
audit_file_dest=’/oracle/app/admin/db10g/adump’
background_dump_dest=’/oracle/app/admin/db10g/bdump’
compatible=’10.2.0.3.0′
core_dump_dest=’/oracle/app/admin/db10g/cdump’
db_block_size=8192
db_domain=”
db_file_multiblock_read_count=16
############### Parametros Adicionais Dataguard ########################
DB_NAME=db10g
DB_UNIQUE_NAME=db10g
LOG_ARCHIVE_CONFIG=’DG_CONFIG=(db10g,st10g)’
CONTROL_FILES=’/oracle/app/oradata/db10g/control01.ctl’,'/oracle/app/oradata/db10g/control02.ctl’,'/oracle/app/oradata/db10g/control03.ctl’
LOG_ARCHIVE_DEST_1=
‘LOCATION=/archivelog/db10g/
VALID_FOR=(ALL_LOGFILES,ALL_ROLES)
DB_UNIQUE_NAME=db10g’
LOG_ARCHIVE_DEST_2=
‘SERVICE=st10g LGWR ASYNC
VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE)
DB_UNIQUE_NAME=st10g’
LOG_ARCHIVE_DEST_STATE_1=ENABLE
LOG_ARCHIVE_DEST_STATE_2=ENABLE
REMOTE_LOGIN_PASSWORDFILE=EXCLUSIVE
LOG_ARCHIVE_FORMAT=%t_%s_%r.arc
LOG_ARCHIVE_MAX_PROCESSES=30
FAL_SERVER=st10g
FAL_CLIENT=db10g
#####################################################################
db_recovery_file_dest=’/oracle/app/flash_recovery_area’
db_recovery_file_dest_size=2147483648
dispatchers=’(PROTOCOL=TCP) (SERVICE=db10gXDB)’
job_queue_processes=10
open_cursors=300
pga_aggregate_target=16777216
processes=150
resumable_timeout=900
sga_target=167772160
undo_management=’AUTO’
undo_tablespace=’UNDOTBS1′
==============================================================
EDITAR PFILEDB10G.ORA PARA ADICIONAR NOVAS REGRAS DO DATAGUARD NO SERVIDOR STANDBY:
==============================================================
==================== INIT STANDBY ============================
db10g.__db_cache_size=54525952
db10g.__java_pool_size=4194304
db10g.__large_pool_size=4194304
db10g.__shared_pool_size=100663296
db10g.__streams_pool_size=0
audit_file_dest=’/oracle/app/admin/db10g/adump’
background_dump_dest=’/oracle/app/admin/db10g/bdump’
compatible=’10.2.0.3.0′
core_dump_dest=’/oracle/app/admin/db10g/cdump’
db_block_size=8192
db_domain=”
db_file_multiblock_read_count=16
############### Parametros Adicionais Dataguard ########################
DB_NAME=db10g
DB_UNIQUE_NAME=st10g
LOG_ARCHIVE_CONFIG=’DG_CONFIG=(db10g,st10g)’
CONTROL_FILES=’/oracle/app/oradata/db10g/control01.ctl’,'/oracle/app/oradata/db10g/control02.ctl’,'/oracle/app/oradata/db10g/control03.ctl’
LOG_ARCHIVE_FORMAT=log%t_%s_%r.arc
LOG_ARCHIVE_DEST_1=
‘LOCATION=/archivelog/db10g/
VALID_FOR=(ALL_LOGFILES,ALL_ROLES)
DB_UNIQUE_NAME=st10g’
LOG_ARCHIVE_DEST_2=
‘SERVICE=db10g LGWR ASYNC
VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE)
DB_UNIQUE_NAME=db10g’
LOG_ARCHIVE_DEST_STATE_1=ENABLE
LOG_ARCHIVE_DEST_STATE_2=ENABLE
REMOTE_LOGIN_PASSWORDFILE=EXCLUSIVE
STANDBY_FILE_MANAGEMENT=AUTO
FAL_SERVER=db10g
FAL_CLIENT=st10g
#####################################################################
db_recovery_file_dest=’/oracle/app/flash_recovery_area’
db_recovery_file_dest_size=2147483648
dispatchers=’(PROTOCOL=TCP) (SERVICE=db10gXDB)’
job_queue_processes=10
open_cursors=300
pga_aggregate_target=16777216
processes=150
resumable_timeout=900
sga_target=167772160
undo_management=’AUTO’
undo_tablespace=’UNDOTBS1′
==============================================================
CRIAR SPFILE ATRAVÉS DO PFILE ALTERADO
==============================================================
Data Guard deve usar SPFILE. Criar o SPFILE e reiniciar o Banco de Dados.
SQL> shutdown immediate;
SQL> startup nomount pfile=’$ORACLE_HOME/dbs/pfileDB10G.ora’;
ORACLE instance started.
Total System Global Area 167772160 bytes
Fixed Size 1266392 bytes
Variable Size 109055272 bytes
Database Buffers 54525952 bytes
Redo Buffers 2924544 bytes
SQL> create spfile from pfile=’$ORACLE_HOME/dbs/pfileDB10G.ora’;
File created.
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
==============================================================
CRIAR O CONTROLFILE STANDBY
==============================================================
No Banco de Dados Produção, crie um controlfile para o Banco de Dados Standby Utilizar:
SQL> STARTUP MOUNT;
ORACLE instance started.
Total System Global Area 167772160 bytes
Fixed Size 1266392 bytes
Variable Size 109055272 bytes
Database Buffers 54525952 bytes
Redo Buffers 2924544 bytes
Database mounted.
SQL> ALTER DATABASE CREATE STANDBY CONTROLFILE AS ‘/installoracle/standby.ctl’;
Database altered.
==============================================================
PARAR O BANCO DE DADOS PRODUÇÃO PARA CÓPIA DOS ARQUIVOS
==============================================================
SQL> SHUTDOWN IMMEDIATE
ORA-01109: database not open
Database dismounted.
ORACLE instance shut down.
==============================================================
PRONTO BANCO DE DADOS PRODUCAO PRONTO PARA A COPIA, BASTA TRANSFERIR OS ARQUIVOS PARA O SERVIDOR STANDBY
==============================================================
==============================================================
III. CONFIGURAR SERVIDOR STANDBY
==============================================================
CRIAR DIRETÓRIOS NECESSÁRIOS
==============================================================
No Servidor Standby, criar todos os diretorios requeridos para dump e directories for dump e detinação dos archived log:
Criar diretorios adump, bdump, cdump, udump, e archived log para o Banco de Dados Standby.
mkdir -p /oracle/app/admin/db10g
mkdir -p /archivelog/db10g/
mkdir -p /oracle/app/oradata/db10g
mkdir -p /oracle/app/admin/db10g/adump
mkdir -p /oracle/app/admin/db10g/bdump
mkdir -p /oracle/app/admin/db10g/cdump
mkdir -p /oracle/app/admin/db10g/dpdump
mkdir -p /oracle/app/admin/db10g/pfile
mkdir -p /oracle/app/admin/db10g/udump
mkdir -p /oracle/app/flash_recovery_area/
chown -R oracle.oinstall /oracle/
chown -R oracle.oinstall /archivelog/
==============================================================
TRANSFERÊNCIA DO BANCO DE DADOS – REPLICA DO PRODUÇÃO
============================================================================================================================
COPIANDO…DATAFILES,TEMPFILES E REDO LOG FILES
==============================================================
[oracle@db10g db10g]$ scp * oracle@192.168.199.241:/oracle/app/oradata/db10g/
oracle@192.168.199.241′s password:
control01.ctl 100% 6992KB 6.8MB/s 00:01
control02.ctl 100% 6992KB 6.8MB/s 00:01
control03.ctl 100% 6992KB 6.8MB/s 00:00
db1lob01.dbf 100% 30MB 10.0MB/s 00:03
example01.dbf 100% 100MB 9.1MB/s 00:11
kdb101.dbf 100% 440MB 10.5MB/s 00:42
redo01.log 100% 50MB 8.3MB/s 00:06
redo02.log 100% 50MB 10.0MB/s 00:05
redo03.log 100% 50MB 10.0MB/s 00:05
sysaux01.dbf 100% 250MB 16.7MB/s 00:15
system01.dbf 100% 510MB 18.9MB/s 00:27
temp01.dbf 100% 21MB 21.0MB/s 00:01
undotbs01.dbf 100% 95MB 19.0MB/s 00:05
users01.dbf 100% 5128KB 5.0MB/s 00:00
[oracle@db10g db10g]$
[oracle@db10g db10g]$ du -h
1.6G .
IMPORTANTE: COMPARAR O TAMANHO DOS DIRETORIOS DOS SERVIDORES.
==============================================================
COPIANDO…CONTROLFILE STANDBY E CONFIGURANDO NOVO CONTROLFILE
==============================================================
[oracle@db10g installoracle]$ scp standby.ctl oracle@192.168.199.241:/oracle/app/oradata/db10g/
oracle@192.168.199.241′s password:
standby.ctl 100% 6992KB 6.8MB/s 00:01
rm -rf control01.ctl
rm -rf control02.ctl
rm -rf control03.ctl
cp standby.ctl control01.ctl
cp standby.ctl control02.ctl
mv standby.ctl control03.ctl
==============================================================
COPIANDO…PFILE DO BANCO DE DADOS PRODUÇÃO E ARQUIVO DE SENHA
==============================================================
[oracle@db10g dbs]$ scp pfileDB10G.ora oracle@192.168.199.241:/oracle/app/product/10.2.0/db10g/dbs/
oracle@192.168.199.241′s password:
pfileDB10G.ora 100% 1549 1.5KB/s 00:00
[oracle@db10g dbs]$ scp orapwdb10g oracle@192.168.199.241:/oracle/app/product/10.2.0/db10g/dbs/
oracle@192.168.199.241′s password:
orapwdb10g 100% 1536 1.5KB/s 00:00
==============================================================
COPIANDO…ARCHIVES
==============================================================
[oracle@db10g db10g]$ scp *.arc oracle@192.168.199.241:/archivelog/db10g/
oracle@192.168.199.241′s password:
1_10_694590577.arc 100% 1740KB 1.7MB/s 00:01
1_11_694590577.arc 100% 59KB 59.0KB/s 00:00
1_12_694590577.arc 100% 14KB 14.0KB/s 00:00
1_13_694590577.arc 100% 155KB 154.5KB/s 00:00
1_14_694590577.arc 100% 640KB 639.5KB/s 00:00
1_15_694590577.arc 100% 1716KB 1.7MB/s 00:00
1_16_694590577.arc 100% 788KB 788.0KB/s 00:00
1_17_694590577.arc 100% 4608 4.5KB/s 00:00
1_18_694590577.arc 100% 290KB 290.0KB/s 00:00
1_19_694590577.arc 100% 273KB 272.5KB/s 00:00
1_20_694590577.arc 100% 49MB 9.8MB/s 00:05
1_21_694590577.arc 100% 49MB 9.8MB/s 00:05
1_22_694590577.arc 100% 49MB 9.8MB/s 00:05
1_23_694590577.arc 100% 49MB 9.8MB/s 00:05
1_24_694590577.arc 100% 49MB 12.2MB/s 00:04
1_25_694590577.arc 100% 7115KB 7.0MB/s 00:01
1_26_694590577.arc 100% 15KB 14.5KB/s 00:00
1_2_694590577.arc 100% 14MB 14.3MB/s 00:01
1_27_694590577.arc 100% 6235KB 6.1MB/s 00:01
1_28_694590577.arc 100% 339KB 339.0KB/s 00:00
1_29_694590577.arc 100% 1253KB 1.2MB/s 00:00
1_30_694590577.arc 100% 6494KB 6.3MB/s 00:01
1_3_694590577.arc 100% 4608 4.5KB/s 00:00
1_4_694590577.arc 100% 42MB 10.5MB/s 00:04
1_5_694590577.arc 100% 259KB 259.0KB/s 00:00
1_6_694590577.arc 100% 5742KB 5.6MB/s 00:01
1_7_694590577.arc 100% 142KB 141.5KB/s 00:00
1_8_694590577.arc 100% 55KB 54.5KB/s 00:00
1_9_694590577.arc 100% 1821KB 1.8MB/s 00:00
log1_31_694590577.arc 100% 13MB 13.0MB/s 00:01
log1_32_694590577.arc 100% 310KB 310.0KB/s 00:00
log1_33_694590577.arc 100% 232KB 231.5KB/s 00:00
log1_34_694590577.arc 100% 222KB 221.5KB/s 00:00
log1_35_694590577.arc 100% 158KB 158.0KB/s 00:00
log1_36_694590577.arc 100% 2703KB 2.6MB/s 00:01
[oracle@db10g db10g]$ du -h
352M
==============================================================
CONFIGURAÇÃO DE REDE (NETWORK) – LISTENER E TNSNAMES
==============================================================
==============================================================
CONFIGURAÇÃO DO LISTENER
==============================================================
No Banco de Dados Produção: use Oracle Net Manager para configurar um listener para DB10G and ST10G.
==============================================================
DB10G LISTENER:
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(GLOBAL_DBNAME = db10g)
(ORACLE_HOME = /oracle/app/product/10.2.0/db10g)
(SID_NAME = db10g)
)
)
LISTENER =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = db10g)(PORT = 1521))
)
==============================================================
No Servidor Standby: use Net Manager para configurar listener para DB10G and ST10G.
==============================================================
ST10G LISTENER:
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(GLOBAL_DBNAME = st10g)
(ORACLE_HOME = /oracle/app/product/10.2.0/st10g)
(SID_NAME = st10g)
)
)
LISTENER =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = st10g)(PORT = 1521))
)
==============================================================
CONFIGURAÇÃO DO TNSNAMES
==============================================================
No Banco de Dados Produção: use Oracle Net Manager para criar Nome de Serviço de Rede para DB10G e ST10G.
==============================================================
DB10G TNSNAMES:
DB10G =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.199.239)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = db10g)
)
)
ST10G =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.199.241)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = st10g)
)
)
==============================================================
No Servidor Standby: use Oracle Net Manager para criar Nome de Serviço de Rede para DB10G e ST10G.
==============================================================
ST10G TNSNAMES:
DB10G =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.199.239)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = db10g)
)
)
ST10G =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.199.241)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = st10g)
)
)
==============================================================
TNSPING PARA VERIFICAR CONEXÃO COM O LISTENER
==============================================================
No Servidor Produção:
[oracle@db10g admin]$ tnsping st10g
TNS Ping Utility for Linux: Version 10.2.0.4.0 – Production on 18-AUG-2009 13:30:51
Copyright (c) 1997, 2007, Oracle. All rights reserved.
Used parameter files:
Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.199.241)(PORT = 1521))) (CONNECT_DATA = (SERVICE_NAME = st10g)))
OK (0 msec)
[oracle@db10g admin]$ tnsping db10g
TNS Ping Utility for Linux: Version 10.2.0.4.0 – Production on 18-AUG-2009 13:30:53
Copyright (c) 1997, 2007, Oracle. All rights reserved.
Used parameter files:
Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.199.239)(PORT = 1521))) (CONNECT_DATA = (SERVICE_NAME = db10g)))
OK (10 msec)
Producao
SQL> conn sys/oracle@st10g as sysdba
Connected.
SQL> conn sys/oracle@db10g as sysdba
Connected.
==============================================================
TNSPING PARA VERIFICAR CONEXÃO COM O LISTENER
==============================================================
No Servidor Standby:
[oracle@st10g admin]$ tnsping st10g
TNS Ping Utility for Linux: Version 10.2.0.4.0 – Production on 24-AUG-2009 00:19:47
Copyright (c) 1997, 2007, Oracle. All rights reserved.
Used parameter files:
Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.199.241)(PORT = 1521))) (CONNECT_DATA = (SERVICE_NAME = st10g)))
OK (10 msec)
[oracle@st10g admin]$ tnsping db10g
TNS Ping Utility for Linux: Version 10.2.0.4.0 – Production on 24-AUG-2009 00:19:56
Copyright (c) 1997, 2007, Oracle. All rights reserved.
Used parameter files:
Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.199.239)(PORT = 1521))) (CONNECT_DATA = (SERVICE_NAME = db10g)))
OK (10 msec)
Standby
SQL> conn sys/oracle@st10g as sysdba
Connected.
SQL> conn sys/oracle@db10g as sysdba
Connected.
==============================================================
EDITAR PFILEDB10G.ORA PARA ADICIONAR NOVAS REGRAS DO DATAGUARD NO SERVIDOR STANDBY:
==============================================================
==================== INIT STANDBY ============================
db10g.__db_cache_size=54525952
db10g.__java_pool_size=4194304
db10g.__large_pool_size=4194304
db10g.__shared_pool_size=100663296
db10g.__streams_pool_size=0
audit_file_dest=’/oracle/app/admin/db10g/adump’
background_dump_dest=’/oracle/app/admin/db10g/bdump’
compatible=’10.2.0.3.0′
core_dump_dest=’/oracle/app/admin/db10g/cdump’
db_block_size=8192
db_domain=”
db_file_multiblock_read_count=16
############### Parametros Adicionais Dataguard ########################
DB_NAME=db10g
DB_UNIQUE_NAME=st10g
LOG_ARCHIVE_CONFIG=’DG_CONFIG=(db10g,st10g)’
CONTROL_FILES=’/oracle/app/oradata/db10g/control01.ctl’,'/oracle/app/oradata/db10g/control02.ctl’,'/oracle/app/oradata/db10g/control03.ctl’
LOG_ARCHIVE_FORMAT=log%t_%s_%r.arc
LOG_ARCHIVE_DEST_1=
‘LOCATION=/archivelog/db10g/
VALID_FOR=(ALL_LOGFILES,ALL_ROLES)
DB_UNIQUE_NAME=st10g’
LOG_ARCHIVE_DEST_2=
‘SERVICE=db10g LGWR ASYNC
VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE)
DB_UNIQUE_NAME=db10g’
LOG_ARCHIVE_DEST_STATE_1=ENABLE
LOG_ARCHIVE_DEST_STATE_2=ENABLE
REMOTE_LOGIN_PASSWORDFILE=EXCLUSIVE
STANDBY_FILE_MANAGEMENT=AUTO
FAL_SERVER=db10g
FAL_CLIENT=st10g
#####################################################################
db_recovery_file_dest=’/oracle/app/flash_recovery_area’
db_recovery_file_dest_size=2147483648
dispatchers=’(PROTOCOL=TCP) (SERVICE=db10gXDB)’
job_queue_processes=10
open_cursors=300
pga_aggregate_target=16777216
processes=150
resumable_timeout=900
sga_target=167772160
undo_management=’AUTO’
undo_tablespace=’UNDOTBS1′
==============================================================
CRIAR SPFILE ATRAVÉS DO PFILE ALTERADO
==============================================================
SQL> startup nomount pfile=’$ORACLE_HOME/dbs/pfileDB10G.ora’;
ORACLE instance started.
Total System Global Area 167772160 bytes
Fixed Size 1266392 bytes
Variable Size 109055272 bytes
Database Buffers 54525952 bytes
Redo Buffers 2924544 bytes
SQL> create spfile from pfile=’$ORACLE_HOME/dbs/pfileDB10G.ora’;
File created.
SQL> shutdown immediate
ORA-01507: database not mounted
ORACLE instance shut down.
==============================================================
ATIVAR BANCO DE DADOS STANDBY DATAGUARD
==============================================================
No Banco de Dados Standby, iniciei o Bando de Dados em Modo Mount:
SQL> STARTUP MOUNT;
ORACLE instance started.
Total System Global Area 167772160 bytes
Fixed Size 1266392 bytes
Variable Size 109055272 bytes
Database Buffers 54525952 bytes
Redo Buffers 2924544 bytes
Database mounted.
==============================================================
ATIVAR LOG DE FLASHBACK NO BANCO DE DADOS STANDBY. FLASHBACK SERÁ NECESSÁRIO PARA FAST-START FAILOVERS
==============================================================
SQL> ALTER DATABASE FLASHBACK ON;
Database altered.
==============================================================
CRIAR STANDBY REDO LOG ONLINE NO BANCO DE DADOS STANDBY ==============================================================
Meu Banco de dados tem 3 grupos originalmente e Crie 3 Grupos de Redolog online 50M usando o seguinte comando:
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#, DBID, STATUS, ARCHIVED, BYTES from v$Standby_log;
GROUP# DBID STATUS ARC BYTES
———- —————————————- ———- — ———-
4 UNASSIGNED UNASSIGNED YES 52428800
5 UNASSIGNED UNASSIGNED YES 52428800
6 UNASSIGNED UNASSIGNED YES 52428800
==============================================================
INICIAR SINCRONISMO DO DATAGUARD
==============================================================
======================= NO STANDBY ===========================
INICIAR SINCRONISMO
SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION;
Database altered.
SQL> SELECT RECOVERY_MODE FROM V$ARCHIVE_DEST_STATUS;
RECOVERY_MODE
———————–
MANAGED
MANAGED
MANAGED
MANAGED
MANAGED
11 rows selected.
======================= NO STANDBY ===========================
VERIFICA OS ARCHVIES DO BANCO DE DADOS STANDBY:
SQL> select sequence#, first_time, next_time from v$archived_log;
SEQUENCE# FIRST_TIM NEXT_TIME
———- ——— ———
86 19-AUG-09 19-AUG-09
85 19-AUG-09 19-AUG-09
87 19-AUG-09 19-AUG-09
====================== NO PRODUÇÃO ======================
GERAR ARCHIVE NO BANCO DE DADOS PRODUÇÃO, FORCE LOGFILE SWITCH:
SQL>alter system switch logfile;
======================NO STANDBY ======================
VERIFICA OS ARCHIVES APLICADOS NO BANCO DE DADOS STANDBY:
SQL> select sequence#, applied from v$archived_log order by sequence#;
SEQUENCE# APP
———- —
85 YES
86 YES
87 YES
========================= NO STANDBY =========================
CASO QUEIRA PARAR O SINCRONISMO.
PARAR O SINCRONISMO (APLICAÇÃO DE LOGS):
SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;
Database altered.
SQL> SELECT RECOVERY_MODE FROM V$ARCHIVE_DEST_STATUS;
RECOVERY_MODE
———————–
IDLE
IDLE
IDLE
IDLE
IDLE
11 rows selected.
===================== NO STANDBY =====================
ATIVAR STANDBY EM MODO DE RECOVERY EM REAL TIME(enable the real-time apply).
SQL> alter database recover managed standby database using current logfile disconnect;
SQL> SELECT RECOVERY_MODE FROM V$ARCHIVE_DEST_STATUS;
RECOVERY_MODE
———————–
MANAGED REAL TIME APPLY
MANAGED REAL TIME APPLY
MANAGED REAL TIME APPLY
MANAGED REAL TIME APPLY
11 rows selected.
==================== NO STANDBY ===========================
SE CASO ESTIVER TUDO OK, NO STANDBY APARECERÁ ASSIM
SQL> select sequence#, applied from v$archived_log order by sequence#;
SEQUENCE# APP
———- —
85 YES
86 YES
87 YES
==============================================================
DATA GUARD BROKER
==============================================================
No Banco de Dados Produção ative o Parametro Data Guard Broker.
SQL> show parameter dg_broker_start
NAME TYPE VALUE
———————————— ———– ——————————
dg_broker_start boolean FALSE
SQL> alter system set dg_broker_start=true;
System altered.
######################################################################
No Banco de Dados Standby ative o Parametro Data Guard Broker.
SQL> show parameter dg_broker_start
NAME TYPE VALUE
———————————— ———– ——————————
dg_broker_start boolean FALSE
SQL> alter system set dg_broker_start=true;
System altered.
==============================================================
CONFIGURAR GLOBAL_DB_NAME
==============================================================
Configure GLOBAL_DB_NAME no Banco de Dados Produção e Standby.
######################## LISTENER PRODUÇÃO #######################
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(GLOBAL_DBNAME = db10g)
(ORACLE_HOME = /oracle/app/product/10.2.0/db10g)
(SID_NAME = db10g)
)
(SID_DESC =
(GLOBAL_DBNAME = db10g_DGMGRL)
(ORACLE_HOME = /oracle/app/product/10.2.0/db10g)
(SID_NAME = db10g)
)
)
LISTENER =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.199.239)(PORT = 1521))
)
####################### LISTENER STANDBY ##########################
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(GLOBAL_DBNAME = db10g)
(ORACLE_HOME = /oracle/app/product/10.2.0/db10g)
(SID_NAME = db10g)
)
(SID_DESC =
(GLOBAL_DBNAME = st10g_DGMGRL)
(ORACLE_HOME = /oracle/app/product/10.2.0/db10g)
(SID_NAME = db10g)
)
)
LISTENER =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.199.241)(PORT = 1521))
)
==============================================================
RECARREGAR OS PARAMETROS SETADO NO LISTENER PRODUÇÃO
==============================================================
[oracle@db10g /]$ lsnrctl reload
LSNRCTL for Linux: Version 10.2.0.4.0 – Production on 19-AUG-2009 15:50:33
Copyright (c) 1991, 2007, Oracle. All rights reserved.
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=db10g)(PORT=1521)))
The command completed successfully
[oracle@db10g /]$ lsnrctl services
LSNRCTL for Linux: Version 10.2.0.4.0 – Production on 19-AUG-2009 15:50:48
Copyright (c) 1991, 2007, Oracle. All rights reserved.
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=db10g)(PORT=1521)))
Services Summary…
Service “db10g” has 2 instance(s).
Instance “db10g”, status UNKNOWN, has 1 handler(s) for this service…
Handler(s):
“DEDICATED” established:0 refused:0
LOCAL SERVER
Instance “db10g”, status READY, has 1 handler(s) for this service…
Handler(s):
“DEDICATED” established:0 refused:0 state:ready
LOCAL SERVER
Service “db10gXDB” has 1 instance(s).
Instance “db10g”, status READY, has 1 handler(s) for this service…
Handler(s):
“D000″ established:0 refused:0 current:0 max:1022 state:ready
DISPATCHER <machine: db10g, pid: 23003>
(ADDRESS=(PROTOCOL=tcp)(HOST=db10g.db1.com.br)(PORT=9669))
Service “db10g_DGMGRL” has 1 instance(s).
Instance “db10g”, status UNKNOWN, has 1 handler(s) for this service…
Handler(s):
“DEDICATED” established:0 refused:0
LOCAL SERVER
Service “db10g_XPT” has 1 instance(s).
Instance “db10g”, status READY, has 1 handler(s) for this service…
Handler(s):
“DEDICATED” established:0 refused:0 state:ready
LOCAL SERVER
The command completed successfully
==============================================================
==============================================================
RECARREGAR OS PARAMETROS SETADO NO LISTENER STANDBY
==============================================================
[oracle@st10g /]$ lsnrctl reload
LSNRCTL for Linux: Version 10.2.0.4.0 – Production on 25-AUG-2009 01:49:44
Copyright (c) 1991, 2007, Oracle. All rights reserved.
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=st10g)(PORT=1521)))
The command completed successfully
[oracle@st10g /]$ lsnrctl services;
LSNRCTL for Linux: Version 10.2.0.4.0 – Production on 25-AUG-2009 01:49:56
Copyright (c) 1991, 2007, Oracle. All rights reserved.
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=st10g)(PORT=1521)))
Services Summary…
Service “db10g” has 1 instance(s).
Instance “db10g”, status UNKNOWN, has 1 handler(s) for this service…
Handler(s):
“DEDICATED” established:0 refused:0
LOCAL SERVER
Service “st10g” has 1 instance(s).
Instance “db10g”, status READY, has 1 handler(s) for this service…
Handler(s):
“DEDICATED” established:0 refused:0 state:ready
LOCAL SERVER
Service “st10g_DGMGRL” has 1 instance(s).
Instance “db10g”, status UNKNOWN, has 1 handler(s) for this service…
Handler(s):
“DEDICATED” established:0 refused:0
LOCAL SERVER
Service “st10g_XPT” has 1 instance(s).
Instance “db10g”, status READY, has 1 handler(s) for this service…
Handler(s):
“DEDICATED” established:0 refused:0 state:ready
LOCAL SERVER
The command completed successfully
==============================================================
INICIANDO DGMGRL PARA CONFIGURAR DG BROKER
==============================================================
[oracle@db10g ~]$ dgmgrl
DGMGRL for Linux: Version 10.2.0.4.0 – Production
Copyright (c) 2000, 2005, Oracle. All rights reserved.
Welcome to DGMGRL, type “help” for information.
DGMGRL> connect sys/oracle
Connected.
==============================================================
CRIANDO CONFIGURAÇÃO DO DG BROKER
==============================================================
CREATE CONFIGURATION configuration-name AS
PRIMARY DATABASE IS database-name
CONNECT IDENTIFIER IS connect-identifier;
DGMGRL> CREATE CONFIGURATION ‘DGConfig’ as
PRIMARY DATABASE IS ‘db10g’
CONNECT IDENTIFIER IS DB10G;
Configuration “DGConfig” created with primary database “db10g”
DGMGRL> show configuration
Configuration
Name: DGConfig
Enabled: NO
Protection Mode: MaxPerformance
Fast-Start Failover: DISABLED
Databases:
db10g – Primary database
Current status for “DGConfig”:
DISABLED
DGMGRL> enable configuration
Enabled.
DGMGRL> show configuration
Configuration
Name: DGConfig
Enabled: YES
Protection Mode: MaxPerformance
Fast-Start Failover: DISABLED
Databases:
db10g – Primary database
Current status for “DGConfig”:
SUCCESS
==============================================================
ADICIONANDO STANDBY NA CONFIGURAÇÃO DO DG BROKER
==============================================================
DGMGRL> ADD DATABASE ‘st10g’ AS CONNECT IDENTIFIER IS ST10G MAINTAINED AS PHYSICAL;
Database “st10g” added
DGMGRL> show configuration
Configuration
Name: DGConfig
Enabled: YES
Protection Mode: MaxPerformance
Fast-Start Failover: DISABLED
Databases:
db10g – Primary database
st10g – Physical standby database (disabled)
Current status for “DGConfig”:
SUCCESS
DGMGRL> enable database ‘st10g’;
Enabled.
DGMGRL> show configuration
Configuration
Name: DGConfig
Enabled: YES
Protection Mode: MaxPerformance
Fast-Start Failover: DISABLED
Databases:
db10g – Primary database
st10g – Physical standby database
Current status for “DGConfig”:
SUCCESS
==============================================================
CONFIGURAR DO DG BROKER
==============================================================
DGMGRL> EDIT DATABASE ‘db10g’ SET PROPERTY ‘LogXptMode’='SYNC’;
Property “LogXptMode” updated
DGMGRL> EDIT DATABASE ‘st10g’ SET PROPERTY ‘LogXptMode’='SYNC’;
Property “LogXptMode” updated
==============================================================
ATUALIZAR O MODO DE PROTEÇÃO PARA MAXAVAILABILITY
==============================================================
DGMGRL> EDIT CONFIGURATION SET PROTECTION MODE AS MAXAVAILABILITY;
Succeeded.
==============================================================
VERIFICAR A CONFIGURAÇÃO DO DG BROKER
==============================================================
DGMGRL> show configuration
Configuration
Name: DGConfig
Enabled: YES
Protection Mode: MaxAvailability
Fast-Start Failover: DISABLED
Databases:
db10g – Primary database
st10g – Physical standby database
Current status for “DGConfig”:
SUCCESS
==============================================================
==========================================================
PRONTO DATA GUARD CONFIGURADO COM SUCESSO !!!
==============================================================
