Saturday, June 09, 2007
Broker, Dataguard e Real-Time Apply - 10g
Este é o complemento do artigo anterior, onde discutimos como montar um Data Guard usando o RMAN. A seguir, farei da mesma forma, ou seja, não haverá explicações específicas para os comandos, já que o melhor source para isso é o próprio manual.
Então começamos com as instâncias criadas no artigo anterior (n1dg e n2dg), a princípio definidas como n1dg = nó primário e n2dg = nó standby físico. Vamos então aos copies e pastes.
Garantia que não havia nenhuma instance viva.
Levantando nó primário.
Checando os parâmetros pré-configurados no artigo anterior.
No nó primário, setar o DEST_1 com o novo parâmetro VALID_FOR Dataguard na versão 10g juntamente com o db_unique_name.
Da mesma forma, porém desta vez vamos configurar o DEST_2, onde será entregue o archive para o standby. Nessa configuração, é importante para aplicação via archives, porém não obrigatório para nosso exemplo, porque faremos real-time apply, ou seja, poderíamos deixar de configurar o DEST_2 e criar somente os standby logfiles. Essa configuração é importante em emergências de rede, etc.
Passo 2: Configurar o standby (n2dg) - Não esquecer que a parte de rede foi previamente definida no artigo anterior.
Acho interessante deixar os problemas que tive durante a configuração. Por exemplo, o erro acima aconteceu porque eu esqueci de criar o spfile no artigo anterior. Então vamos levantar a instância com o pfile já usado anteriormente e a partir dele criar o spfile.
Hora das modificações do standby
Server do standby é o nó principal (N1) e client é o (N2) o inverso do nó primário.
Os próximos passos são a chave para o Real-Time Apply, porque faremos a aplicação das modificações através dos standby lofiles. Há necessidade criar tanto no physical standby como no primário.
Como notado acima, nada de aplicação de redos ainda. Agora entra a parte da configuração do Data Guard Broker através do parâmetro DG_BROKER_START, não esquecer de mudar para TRUE em todos os nós que serão controlados pelo BROKER. (Obs: Depois que voce conhece o broker, nunca mais vai achar difícil fazer switchover, failover ou qualquer outra manutenção no seu dataguard).
Ok, agora que os serviços do Broker estão no ar, vamos ao client para criar a configuração do dataguard, note que o seu standby já está montado, portanto o broker irá herdar a maioria de suas configurações quando o database for adicionado. Neste ponto não esqueça de ter criado os arquivos de senha e colocado a mesma senha do sys para todos os nós, isso facilita na manutenção, porque o broker se conecta nos nós e faz shutdown e startup quando necessário.
Importante o erro acima, nesse ponto vejo qual foi o problema consultando o código de erro online.
Primeira coisa que vem a cabeça é testar a conectividade entre os servidores.
Bingo! Esqueci de subir o listener.
Vamos tentar de novo!
Agora sim, então temos nossa configuração de dataguard criada, porém em estado disable. O comando show configuration mostra o status da sua configuração.
Habilite a configuração.
E tenha certa paciência! :-) Aqui é interessante uma dica: abra duas sessões extras e vá ao diretório do alert e faça um "tail -f alert_..." e veja o broker em ação. Ele fará várias modificações e no momento que voce habilita, ele começa a aplicar os archives e sincronizar os redologs, ainda falta configurar o modo de aplicação, eu quero que seja MAXAVAILABILITY.
Perfeito. Neste ponto faremos a configuração para que o protection mode seja maxavailability, ou seja, sem perda de dados com máximo de performance (possível).
Ok, ok... Faltou alterar o LogXptMode de ASYNC para SYNC.
Os helps abaixo ajudam muito na medida em que não é frequente o uso do client do broker e ninguem aqui está participando do campionato mundial de "saber tudo de cabeça".
Alterar nos dois databases.
Ok, protection mode alterado! O maxavailability é nosso preferido.
Uma vez habilitada a configuração, os bancos já deverão estar sincronizados.
Vamos a alguns testes.
Hora das manutenções... :-) Olha como é difícil fazer SWITCHOVER.
Pronto! Viu só? Bastou "switchover to" e ele faz tudo. Não deixe de ver os alerts logs com o tail -f.
Vamos verificar!
Voltando para o nó N1.
Uma última consideração, quando configuramos o broker para gerenciar o Dataguard, até a versão 9i, não há necessidade de abrir o banco, é possível somente levantar os nós em NOMOUNT que ele faz o resto. Já na versão 10g, é necessário MONTAR o banco para que o broker levante e sincronize os nós.
Um lembrete: Use as instruções acima para exercício em ambiente TESTE e por sua conta e risco!
Então começamos com as instâncias criadas no artigo anterior (n1dg e n2dg), a princípio definidas como n1dg = nó primário e n2dg = nó standby físico. Vamos então aos copies e pastes.
Garantia que não havia nenhuma instance viva.
[marcio@anakin ~]$ ps -ef | grep pmon_
marcio 6175 6044 0 00:07 pts/1 00:00:00 grep pmon_
[marcio@anakin ~]$
[marcio@anakin ~]$
[marcio@anakin ~]$ su - oracle
Password:
[oracle@anakin ~]$
[oracle@anakin ~]$
[oracle@anakin ~]$ cd /oracle/admin/
[oracle@anakin admin]$ ll -tr
total 20
drwxr-x--- 6 oracle dba 4096 Feb 15 14:22 +ASM
drwxr-x--- 8 oracle dba 4096 Feb 15 14:28 lx10g
drwxr-xr-x 7 ora92 dba 4096 Mar 27 16:48 lx92
drwxr-x--- 9 oracle dba 4096 Jun 3 23:06 n1dg
drwxr-xr-x 9 oracle dba 4096 Jun 3 23:58 n2dg
[oracle@anakin admin]$
Levantando nó primário.
[oracle@anakin admin]$ export ORACLE_SID=n1dg
[oracle@anakin admin]$ sqlplus / as sysdba
SQL*Plus: Release 10.2.0.3.0 - Production on Tue Jun 12 00:08:17 2007
Copyright (c) 1982, 2006, Oracle. All Rights Reserved.
Connected to an idle instance.
idle> startup
ORACLE instance started.
Total System Global Area 608174080 bytes
Fixed Size 1263200 bytes
Variable Size 205523360 bytes
Database Buffers 394264576 bytes
Redo Buffers 7122944 bytes
Database mounted.
Database opened.
idle>
Checando os parâmetros pré-configurados no artigo anterior.
idle> show parameter archive
NAME TYPE VALUE
------------------------------------ ----------- -----------------------------
archive_lag_target integer 0
log_archive_config string DG_CONFIG=(n1, n2)
log_archive_dest string
log_archive_dest_1 string LOCATION=/backup/n1/arch
log_archive_dest_10 string
log_archive_dest_2 string
log_archive_dest_3 string
log_archive_dest_4 string
log_archive_dest_5 string
log_archive_dest_6 string
log_archive_dest_7 string
log_archive_dest_8 string
log_archive_dest_9 string
log_archive_dest_state_1 string enable
log_archive_dest_state_10 string enable
log_archive_dest_state_2 string enable
log_archive_dest_state_3 string enable
log_archive_dest_state_4 string enable
log_archive_dest_state_5 string enable
log_archive_dest_state_6 string enable
log_archive_dest_state_7 string enable
log_archive_dest_state_8 string enable
log_archive_dest_state_9 string enable
log_archive_duplex_dest string
log_archive_format string %t_%s_%r.dbf
log_archive_local_first boolean TRUE
log_archive_max_processes integer 2
log_archive_min_succeed_dest integer 1
log_archive_start boolean FALSE
log_archive_trace integer 0
remote_archive_enable string true
standby_archive_dest string /backup/n1/arch
idle> show parameter db_name
NAME TYPE VALUE
------------------------------------ ----------- ---------------------------
db_name string N1DG
idle> show parameter db_unique_name
NAME TYPE VALUE
------------------------------------ ----------- ---------------------------
db_unique_name string n1
idle> show parameter fal
NAME TYPE VALUE
------------------------------------ ----------- ---------------------------
fal_client string N1
fal_server string N2
idle>
No nó primário, setar o DEST_1 com o novo parâmetro VALID_FOR Dataguard na versão 10g juntamente com o db_unique_name.
idle> alter system set LOG_ARCHIVE_DEST_1='LOCATION=/backup/n1/arch
2 VALID_FOR=(ALL_LOGFILES,ALL_ROLES)
3 DB_UNIQUE_NAME=n1'
4 /
System altered.
Da mesma forma, porém desta vez vamos configurar o DEST_2, onde será entregue o archive para o standby. Nessa configuração, é importante para aplicação via archives, porém não obrigatório para nosso exemplo, porque faremos real-time apply, ou seja, poderíamos deixar de configurar o DEST_2 e criar somente os standby logfiles. Essa configuração é importante em emergências de rede, etc.
idle> alter system set LOG_ARCHIVE_DEST_2='SERVICE=n2
2 VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE)
3 DB_UNIQUE_NAME=n2';
4 /
System altered.
idle> show parameter LOG_ARCHIVE_MAX_PROCESSES
NAME TYPE VALUE
------------------------------------ ----------- --------------------
log_archive_max_processes integer 2
idle> exit
Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - Production
With the Partitioning, OLAP and Data Mining options
[oracle@anakin admin]$
[oracle@anakin admin]$
[oracle@anakin admin]$ echo $ORACLE_SID
n1dg
[oracle@anakin admin]$ export ORACLE_SID=n2dg
Passo 2: Configurar o standby (n2dg) - Não esquecer que a parte de rede foi previamente definida no artigo anterior.
[oracle@anakin admin]$ sqlplus / as sysdba
SQL*Plus: Release 10.2.0.3.0 - Production on Tue Jun 12 00:17:21 2007
Copyright (c) 1982, 2006, Oracle. All Rights Reserved.
Connected to an idle instance.
idle> startup mount
ORA-01078: failure in processing system parameters
LRM-00109: could not open parameter file '/oracle/10g/dbs/initn2dg.ora'
idle> exit
Disconnected
Acho interessante deixar os problemas que tive durante a configuração. Por exemplo, o erro acima aconteceu porque eu esqueci de criar o spfile no artigo anterior. Então vamos levantar a instância com o pfile já usado anteriormente e a partir dele criar o spfile.
[oracle@anakin admin]$ cd n2dg/scripts/
[oracle@anakin scripts]$ ll -tr
total 8
-rw-r--r-- 1 oracle dba 482 Jun 3 23:57 n2dg.sh
-rw-r--r-- 1 oracle dba 646 Jun 4 00:04 init.ora
[oracle@anakin scripts]$ sqlplus / as sysdba
SQL*Plus: Release 10.2.0.3.0 - Production on Tue Jun 12 00:17:43 2007
Copyright (c) 1982, 2006, Oracle. All Rights Reserved.
Connected to an idle instance.
idle> startup mount pfile=./init.ora
ORACLE instance started.
Total System Global Area 608174080 bytes
Fixed Size 1263200 bytes
Variable Size 167774624 bytes
Database Buffers 432013312 bytes
Redo Buffers 7122944 bytes
Database mounted.
idle> !pwd
/oracle/admin/n2dg/scripts
idle> create spfile from pfile='/oracle/admin/n2dg/scripts/init.ora';
File created.
idle> startup mount force
ORACLE instance started.
Total System Global Area 608174080 bytes
Fixed Size 1263200 bytes
Variable Size 167774624 bytes
Database Buffers 432013312 bytes
Redo Buffers 7122944 bytes
Database mounted.
idle> show parameter db_name
NAME TYPE VALUE
------------------------------------ ----------- -------------------------
db_name string n1dg
idle> show parameter db_unique_name
NAME TYPE VALUE
------------------------------------ ----------- -------------------------
db_unique_name string n2
Hora das modificações do standby
idle> show parameter STANDBY_ARCHIVE_DEST
NAME TYPE VALUE
------------------------------------ ----------- -------------------------
standby_archive_dest string ?/dbs/arch
idle> alter system set STANDBY_ARCHIVE_DEST='/backup/n2/arch';
System altered.
idle> alter system set log_archive_config='DG_CONFIG=(n1, n2)';
System altered.
idle> alter system set LOG_ARCHIVE_DEST_1='LOCATION=/backup/n2/arch
2 VALID_FOR=(ALL_LOGFILES,ALL_ROLES)
3 DB_UNIQUE_NAME=n2'
4 /
System altered.
idle> alter system set LOG_ARCHIVE_DEST_2='SERVICE=n1
2 VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE)
3 DB_UNIQUE_NAME=n1'
4 /
System altered.
Server do standby é o nó principal (N1) e client é o (N2) o inverso do nó primário.
idle> alter system set fal_server=n1;
System altered.
idle> alter system set fal_client=n2;
System altered.
idle> select * from v$log;
GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIM
------------- ------------- ------------- ------------- ------------- --- ---------------- ------------- ---------
1 1 2 52428800 1 NO CURRENT 485605 03-JUN-07
3 1 1 52428800 1 YES INACTIVE 456954 03-JUN-07
2 1 0 52428800 1 YES UNUSED 0
3 rows selected.
Os próximos passos são a chave para o Real-Time Apply, porque faremos a aplicação das modificações através dos standby lofiles. Há necessidade criar tanto no physical standby como no primário.
idle> ALTER DATABASE ADD STANDBY LOGFILE GROUP 4 ('/backup/n2/n2dg/srl01.log') SIZE 50M;
Database altered.
idle> ALTER DATABASE ADD STANDBY LOGFILE GROUP 5 ('/backup/n2/n2dg/srl02.log') SIZE 50M;
Database altered.
idle> ALTER DATABASE ADD STANDBY LOGFILE GROUP 6 ('/backup/n2/n2dg/srl03.log') SIZE 50M;
Database altered.
idle> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT;
Database altered.
idle> select * from v$log;
GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIM
------------- ------------- ------------- ------------- ------------- --- ---------------- ------------- ---------
1 1 0 52428800 1 NO CURRENT 485605 03-JUN-07
3 1 0 52428800 1 YES UNUSED 456954 03-JUN-07
2 1 0 52428800 1 YES UNUSED 0
3 rows selected.
idle> exit
Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - Production
With the Partitioning, OLAP and Data Mining options
[oracle@anakin scripts]$ export ORACLE_SID=n1dg
[oracle@anakin scripts]$ sqlplus / as sysdba
SQL*Plus: Release 10.2.0.3.0 - Production on Tue Jun 12 00:28:15 2007
Copyright (c) 1982, 2006, Oracle. All Rights Reserved.
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - Production
With the Partitioning, OLAP and Data Mining options
sys:N1DG> ALTER DATABASE ADD STANDBY LOGFILE GROUP 4 ('/backup/n1/n1dg/srl01.log') SIZE 50M;
Database altered.
sys:N1DG> ALTER DATABASE ADD STANDBY LOGFILE GROUP 5 ('/backup/n1/n1dg/srl02.log') SIZE 50M;
Database altered.
sys:N1DG> ALTER DATABASE ADD STANDBY LOGFILE GROUP 6 ('/backup/n1/n1dg/srl03.log') SIZE 50M;
Database altered.
sys:N1DG> select * from v$log;
GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIME
------------- ------------- ------------- ------------- ------------- --- ---------------- ------------- -------------------
1 1 5 52428800 1 YES INACTIVE 528846 05/06/2007 00:00:55
3 1 7 52428800 1 NO CURRENT 602414 12/06/2007 00:08:28
2 1 6 52428800 1 YES INACTIVE 574349 09/06/2007 19:53:50
3 rows selected.
sys:N1DG> alter system switch logfile;
System altered.
sys:N1DG> alter system switch logfile;
System altered.
sys:N1DG> select * from v$log;
GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIME
------------- ------------- ------------- ------------- ------------- --- ---------------- ------------- -------------------
1 1 8 52428800 1 NO ACTIVE 604660 12/06/2007 00:29:30
3 1 7 52428800 1 NO ACTIVE 602414 12/06/2007 00:08:28
2 1 9 52428800 1 NO CURRENT 604662 12/06/2007 00:29:32
3 rows selected.
sys:N1DG> exit
Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - Production
With the Partitioning, OLAP and Data Mining options
[oracle@anakin scripts]$ export ORACLE_SID=n2dg
[oracle@anakin scripts]$ sqlplus / as sysdba
SQL*Plus: Release 10.2.0.3.0 - Production on Tue Jun 12 00:29:55 2007
Copyright (c) 1982, 2006, Oracle. All Rights Reserved.
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - Production
With the Partitioning, OLAP and Data Mining options
idle> select * from v$log;
GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIME
------------- ------------- ------------- ------------- ------------- --- ---------------- ------------- -------------------
1 1 0 52428800 1 NO CURRENT 485605 03/06/2007 23:23:05
3 1 0 52428800 1 YES UNUSED 456954 03/06/2007 23:10:55
2 1 0 52428800 1 YES UNUSED 0
3 rows selected.
idle> exit
Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - Production
With the Partitioning, OLAP and Data Mining options
Como notado acima, nada de aplicação de redos ainda. Agora entra a parte da configuração do Data Guard Broker através do parâmetro DG_BROKER_START, não esquecer de mudar para TRUE em todos os nós que serão controlados pelo BROKER. (Obs: Depois que voce conhece o broker, nunca mais vai achar difícil fazer switchover, failover ou qualquer outra manutenção no seu dataguard).
[oracle@anakin scripts]$ export ORACLE_SID=n1dg
[oracle@anakin scripts]$
[oracle@anakin scripts]$ sqlplus / as sysdba
SQL*Plus: Release 10.2.0.3.0 - Production on Tue Jun 12 00:30:24 2007
Copyright (c) 1982, 2006, Oracle. All Rights Reserved.
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - Production
With the Partitioning, OLAP and Data Mining options
sys:N1DG> show parameter dg
NAME TYPE VALUE
------------------------------------ ----------- --------------------------------------------------
dg_broker_config_file1 string /oracle/10g/dbs/dr1n1.dat
dg_broker_config_file2 string /oracle/10g/dbs/dr2n1.dat
dg_broker_start boolean FALSE
sys:N1DG> alter system set dg_broker_start=true;
System altered.
sys:N1DG> exit
Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - Production
With the Partitioning, OLAP and Data Mining options
[oracle@anakin scripts]$ export ORACLE_SID=n2dg
[oracle@anakin scripts]$ sqlplus / as sysdba
SQL*Plus: Release 10.2.0.3.0 - Production on Tue Jun 12 00:30:48 2007
Copyright (c) 1982, 2006, Oracle. All Rights Reserved.
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - Production
With the Partitioning, OLAP and Data Mining options
idle> alter system set dg_broker_start=true;
System altered.
idle> exit
Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - Production
With the Partitioning, OLAP and Data Mining options
[oracle@anakin scripts]$ export ORACLE_SID=n1dg
[oracle@anakin scripts]$
Ok, agora que os serviços do Broker estão no ar, vamos ao client para criar a configuração do dataguard, note que o seu standby já está montado, portanto o broker irá herdar a maioria de suas configurações quando o database for adicionado. Neste ponto não esqueça de ter criado os arquivos de senha e colocado a mesma senha do sys para todos os nós, isso facilita na manutenção, porque o broker se conecta nos nós e faz shutdown e startup quando necessário.
[oracle@anakin scripts]$ dgmgrl
DGMGRL for Linux: Version 10.2.0.3.0 - Production
Copyright (c) 2000, 2005, Oracle. All rights reserved.
Welcome to DGMGRL, type "help" for information.
DGMGRL> connect sys/oracle
Connected.
DGMGRL>
DGMGRL> create configuration mportes_dg as
> primary database is n1
> connect identifier is n1;
Configuration "mportes_dg" created with primary database "n1"
DGMGRL> ADD DATABASE n2 AS CONNECT IDENTIFIER IS n2 MAINTAINED AS PHYSICAL;
Error: ORA-16796: one or more properties could not be imported from the database
Failed.
DGMGRL> exit
Importante o erro acima, nesse ponto vejo qual foi o problema consultando o código de erro online.
[oracle@anakin scripts]$ oerr ora 16796
16796, 0000, "one or more properties could not be imported from the database"
// *Cause: The broker was unable to import property values for the database
// being added to the broker configuration. This error indicates:
// - the net-service-name specified in DGMGRL's CREATE CONFIGURATION
// or ADD DATABASE command is not one that provides access to the
// database being added, or
// - there are no instances running for the database being added.
// *Action: Remove the database from the configuration using the REMOVE
// CONFIGURATION or REMOVE DATABASE command. Make sure that the
// database to be added has at least one instance running and that the
// net-service-name provides access to the running instance. Then
// reissue the CREATE CONFIGURATION or ADD DATABASE command.
Primeira coisa que vem a cabeça é testar a conectividade entre os servidores.
[oracle@anakin scripts]$ tnsping n2
TNS Ping Utility for Linux: Version 10.2.0.3.0 - Production on 12-JUN-2007 00:32:20
Copyright (c) 1997, 2006, Oracle. All rights reserved.
Used parameter files:
Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = anakin.mportes.local)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SID = n2dg)))
TNS-12541: TNS:no listener
Bingo! Esqueci de subir o listener.
[oracle@anakin scripts]$ lsnrctl start
LSNRCTL for Linux: Version 10.2.0.3.0 - Production on 12-JUN-2007 00:32:25
Copyright (c) 1991, 2006, Oracle. All rights reserved.
Starting /oracle/10g/bin/tnslsnr: please wait...
TNSLSNR for Linux: Version 10.2.0.3.0 - Production
System parameter file is /oracle/10g/network/admin/listener.ora
Log messages written to /oracle/10g/network/log/listener.log
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=anakin.mportes.local)(PORT=1521)))
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=anakin.mportes.local)(PORT=1521)))
STATUS of the LISTENER
------------------------
Alias LISTENER
Version TNSLSNR for Linux: Version 10.2.0.3.0 - Production
Start Date 12-JUN-2007 00:32:25
Uptime 0 days 0 hr. 0 min. 0 sec
Trace Level off
Security ON: Local OS Authentication
SNMP OFF
Listener Parameter File /oracle/10g/network/admin/listener.ora
Listener Log File /oracle/10g/network/log/listener.log
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=anakin.mportes.local)(PORT=1521)))
Services Summary...
Service "PLSExtProc" has 1 instance(s).
Instance "PLSExtProc", status UNKNOWN, has 1 handler(s) for this service...
Service "lx10g" has 1 instance(s).
Instance "lx10g", status UNKNOWN, has 1 handler(s) for this service...
Service "n1_DGMGRL" has 1 instance(s).
Instance "n1dg", status UNKNOWN, has 1 handler(s) for this service...
Service "n2_DGMGRL" has 1 instance(s).
Instance "n2dg", status UNKNOWN, has 1 handler(s) for this service...
The command completed successfully
Vamos tentar de novo!
[oracle@anakin scripts]$ dgmgrl
DGMGRL for Linux: Version 10.2.0.3.0 - Production
Copyright (c) 2000, 2005, Oracle. All rights reserved.
Welcome to DGMGRL, type "help" for information.
DGMGRL> connect sys/oracle
Connected.
DGMGRL> ADD DATABASE n2 AS CONNECT IDENTIFIER IS n2 MAINTAINED AS PHYSICAL;
Database "n2" added
Agora sim, então temos nossa configuração de dataguard criada, porém em estado disable. O comando show configuration mostra o status da sua configuração.
DGMGRL> show configuration
Configuration
Name: mportes_dg
Enabled: NO
Protection Mode: MaxPerformance
Fast-Start Failover: DISABLED
Databases:
n1 - Primary database
n2 - Physical standby database
Current status for "mportes_dg":
DISABLED
Habilite a configuração.
DGMGRL> enable configuration
Enabled.
DGMGRL> show configuration verbose
Configuration
Name: mportes_dg
Enabled: YES
Protection Mode: MaxPerformance
Fast-Start Failover: DISABLED
Databases:
n1 - Primary database
n2 - Physical standby database
Current status for "mportes_dg":
Warning: ORA-16610: command 'ENABLE DATABASE n2' in progress
E tenha certa paciência! :-) Aqui é interessante uma dica: abra duas sessões extras e vá ao diretório do alert e faça um "tail -f alert_..." e veja o broker em ação. Ele fará várias modificações e no momento que voce habilita, ele começa a aplicar os archives e sincronizar os redologs, ainda falta configurar o modo de aplicação, eu quero que seja MAXAVAILABILITY.
DGMGRL> show configuration verbose
Configuration
Name: mportes_dg
Enabled: YES
Protection Mode: MaxPerformance
Fast-Start Failover: DISABLED
Databases:
n1 - Primary database
n2 - Physical standby database
Current status for "mportes_dg":
SUCCESS
Perfeito. Neste ponto faremos a configuração para que o protection mode seja maxavailability, ou seja, sem perda de dados com máximo de performance (possível).
DGMGRL> alter configuration set protection mode as maxavailability;
Error: ORA-16627: operation disallowed since no standby databases would remain to support protection mode
Failed.
Ok, ok... Faltou alterar o LogXptMode de ASYNC para SYNC.
Os helps abaixo ajudam muito na medida em que não é frequente o uso do client do broker e ninguem aqui está participando do campionato mundial de "saber tudo de cabeça".
DGMGRL> help show
Display information about a configuration, database, or instance
Syntax:
SHOW CONFIGURATION;
SHOW DATABASE [VERBOSE][ ];
SHOW INSTANCE [VERBOSE][ ]
[ON DATABASE];
DGMGRL> show database verbose n1
Database
Name: n1
Role: PRIMARY
Enabled: YES
Intended State: ONLINE
Instance(s):
n1dg
Properties:
InitialConnectIdentifier = 'n1'
LogXptMode = 'ASYNC'
Dependency = ''
DelayMins = '0'
Binding = 'OPTIONAL'
MaxFailure = '0'
MaxConnections = '1'
ReopenSecs = '300'
NetTimeout = '180'
LogShipping = 'ON'
PreferredApplyInstance = ''
ApplyInstanceTimeout = '0'
ApplyParallel = 'AUTO'
StandbyFileManagement = 'AUTO'
ArchiveLagTarget = '0'
LogArchiveMaxProcesses = '2'
LogArchiveMinSucceedDest = '1'
DbFileNameConvert = ''
LogFileNameConvert = ''
FastStartFailoverTarget = ''
StatusReport = '(monitor)'
InconsistentProperties = '(monitor)'
InconsistentLogXptProps = '(monitor)'
SendQEntries = '(monitor)'
LogXptStatus = '(monitor)'
RecvQEntries = '(monitor)'
HostName = 'anakin.mportes.local'
SidName = 'n1dg'
LocalListenerAddress = '(ADDRESS=(PROTOCOL=tcp)(HOST=anakin.mportes.local)(PORT=1521))'
StandbyArchiveLocation = '/backup/n1/arch'
AlternateLocation = ''
LogArchiveTrace = '0'
LogArchiveFormat = '%t_%s_%r.dbf'
LatestLog = '(monitor)'
TopWaitEvents = '(monitor)'
Current status for "n1":
SUCCESS
DGMGRL> help alter
Edit a configuration, database, or instance
Syntax:
EDIT CONFIGURATION SET PROTECTION MODE AS
{MaxProtection|MaxAvailability|MaxPerformance};
EDIT CONFIGURATION SET PROPERTY= ;
EDIT DATABASESET PROPERTY = ;
EDIT DATABASERENAME TO ;
EDIT DATABASESET STATE =
[WITH APPLY INSTANCE =];
EDIT INSTANCE[ON DATABASE ]
SET AUTO PFILE [ = {|OFF} ];
EDIT INSTANCE[ON DATABASE ]
SET PROPERTY= ;
Alterar nos dois databases.
DGMGRL> edit database n1 set property LogXptMode='SYNC';
Property "logxptmode" updated
DGMGRL> edit database n2 set property LogXptMode='SYNC';
Property "logxptmode" updated
DGMGRL> alter configuration set protection mode as maxavailability;
Succeeded.
Ok, protection mode alterado! O maxavailability é nosso preferido.
DGMGRL> show configuration verbose
Configuration
Name: mportes_dg
Enabled: YES
Protection Mode: MaxAvailability
Fast-Start Failover: DISABLED
Databases:
n1 - Primary database
n2 - Physical standby database
Current status for "mportes_dg":
SUCCESS
DGMGRL> exit
Uma vez habilitada a configuração, os bancos já deverão estar sincronizados.
Vamos a alguns testes.
[oracle@anakin scripts]$ export ORACLE_SID=n2dg
[oracle@anakin scripts]$ sqlplus / as sysdba
SQL*Plus: Release 10.2.0.3.0 - Production on Tue Jun 12 00:38:37 2007
Copyright (c) 1982, 2006, Oracle. All Rights Reserved.
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - Production
With the Partitioning, OLAP and Data Mining options
idle> select * from v$log;
GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIME
------------- ------------- ------------- ------------- ------------- --- ---------------- ------------- -------------------
1 1 14 52428800 1 YES CLEARING_CURRENT 604871 12/06/2007 00:38:10
3 1 14 52428800 1 YES CLEARING 604871 12/06/2007 00:38:10
2 1 13 52428800 1 YES CLEARING 604838 12/06/2007 00:37:00
3 rows selected.
idle> exit
Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - Production
With the Partitioning, OLAP and Data Mining options
Hora das manutenções... :-) Olha como é difícil fazer SWITCHOVER.
[oracle@anakin scripts]$ dgmgrl
DGMGRL for Linux: Version 10.2.0.3.0 - Production
Copyright (c) 2000, 2005, Oracle. All rights reserved.
Welcome to DGMGRL, type "help" for information.
DGMGRL> connect sys/oracle
Connected.
DGMGRL> show configuration
Configuration
Name: mportes_dg
Enabled: YES
Protection Mode: MaxAvailability
Fast-Start Failover: DISABLED
Databases:
n1 - Primary database
n2 - Physical standby database
Current status for "mportes_dg":
SUCCESS
DGMGRL> switchover to n2
Performing switchover NOW, please wait...
Operation requires shutdown of instance "n1dg" on database "n1"
Shutting down instance "n1dg"...
ORA-01109: database not open
Database dismounted.
ORACLE instance shut down.
Operation requires shutdown of instance "n2dg" on database "n2"
Shutting down instance "n2dg"...
ORA-01109: database not open
Database dismounted.
ORACLE instance shut down.
Operation requires startup of instance "n1dg" on database "n1"
Starting instance "n1dg"...
ORACLE instance started.
Database mounted.
Operation requires startup of instance "n2dg" on database "n2"
Starting instance "n2dg"...
ORACLE instance started.
Database mounted.
Switchover succeeded, new primary is "n2"
DGMGRL> show configuration
Configuration
Name: mportes_dg
Enabled: YES
Protection Mode: MaxAvailability
Fast-Start Failover: DISABLED
Databases:
n1 - Physical standby database
n2 - Primary database
Current status for "mportes_dg":
SUCCESS
DGMGRL> exit
Pronto! Viu só? Bastou "switchover to
Vamos verificar!
[oracle@anakin scripts]$ echo $ORACLE_SID
n2dg
[oracle@anakin scripts]$ sqlplus / as sysdba
SQL*Plus: Release 10.2.0.3.0 - Production on Tue Jun 12 00:44:02 2007
Copyright (c) 1982, 2006, Oracle. All Rights Reserved.
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - Production
With the Partitioning, OLAP and Data Mining options
sys:N1DG> select NAME, OPEN_MODE, PROTECTION_MODE, PROTECTION_LEVEL, REMOTE_ARCHIVE, DATABASE_ROLE,
2 SWITCHOVER_STATUS, DATAGUARD_BROKER, FORCE_LOGGING
3 from v$database
4 /
NAME OPEN_MODE PROTECTION_MODE PROTECTION_LEVEL REMOTE_A DATABASE_ROLE SWITCHOVER_STATUS DATAGUAR FOR
--------- ---------- -------------------- -------------------- -------- ---------------- -------------------- -------- ---
N1DG READ WRITE MAXIMUM AVAILABILITY MAXIMUM AVAILABILITY ENABLED PRIMARY SESSIONS ACTIVE ENABLED YES
1 row selected.
sys:N1DG> exit
Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - Production
With the Partitioning, OLAP and Data Mining options
[oracle@anakin scripts]$ export ORACLE_SID=n1dg
[oracle@anakin scripts]$ sqlplus / as sysdba
SQL*Plus: Release 10.2.0.3.0 - Production on Tue Jun 12 00:45:25 2007
Copyright (c) 1982, 2006, Oracle. All Rights Reserved.
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - Production
With the Partitioning, OLAP and Data Mining options
idle> select sequence#, dest_id, creator, registrar, archived, applied, status from v$archived_log order by 1;
SEQUENCE# DEST_ID CREATOR REGISTR ARC APP S
------------- ------------- ------- ------- --- --- -
2 2 ARCH ARCH YES NO A
3 2 ARCH ARCH YES NO A
4 2 ARCH ARCH YES NO A
5 1 ARCH ARCH YES YES A
5 2 ARCH ARCH YES NO A
6 1 ARCH ARCH YES YES A
6 2 ARCH ARCH YES NO A
7 1 ARCH ARCH YES YES A
7 2 ARCH ARCH YES NO A
8 1 ARCH ARCH YES YES A
8 2 ARCH ARCH YES NO A
9 2 ARCH ARCH YES NO A
9 1 FGRD FGRD YES YES A
10 1 ARCH ARCH YES YES A
10 2 ARCH ARCH YES NO A
11 1 ARCH ARCH YES YES A
11 2 ARCH ARCH YES YES A
12 1 ARCH ARCH YES YES A
12 2 ARCH ARCH YES YES A
13 2 LGWR LGWR YES YES A
13 1 FGRD FGRD YES YES A
14 2 LGWR LGWR YES NO A
14 1 FGRD FGRD YES YES A
15 2 LGWR LGWR YES NO A
15 1 FGRD FGRD YES YES A
16 1 FGRD RFS YES YES A
16 2 FGRD FGRD YES NO A
17 1 ARCH RFS YES YES A
18 1 ARCH RFS YES YES A
29 rows selected.
idle> exit
Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - Production
With the Partitioning, OLAP and Data Mining options
Voltando para o nó N1.
[oracle@anakin scripts]$ dgmgrl
DGMGRL for Linux: Version 10.2.0.3.0 - Production
Copyright (c) 2000, 2005, Oracle. All rights reserved.
Welcome to DGMGRL, type "help" for information.
DGMGRL> connect sys/oracle
Connected.
DGMGRL> show configuration
Configuration
Name: mportes_dg
Enabled: YES
Protection Mode: MaxAvailability
Fast-Start Failover: DISABLED
Databases:
n1 - Physical standby database
n2 - Primary database
Current status for "mportes_dg":
SUCCESS
DGMGRL> switchover to n1
Performing switchover NOW, please wait...
Operation requires shutdown of instance "n2dg" on database "n2"
Shutting down instance "n2dg"...
ORA-01109: database not open
Database dismounted.
ORACLE instance shut down.
Operation requires shutdown of instance "n1dg" on database "n1"
Shutting down instance "n1dg"...
ORA-01109: database not open
Database dismounted.
ORACLE instance shut down.
Operation requires startup of instance "n2dg" on database "n2"
Starting instance "n2dg"...
ORACLE instance started.
Database mounted.
Operation requires startup of instance "n1dg" on database "n1"
Starting instance "n1dg"...
ORACLE instance started.
Database mounted.
Switchover succeeded, new primary is "n1"
DGMGRL>
DGMGRL> show configuration
Configuration
Name: mportes_dg
Enabled: YES
Protection Mode: MaxAvailability
Fast-Start Failover: DISABLED
Databases:
n1 - Primary database
n2 - Physical standby database
Current status for "mportes_dg":
SUCCESS
DGMGRL> exit
[oracle@anakin scripts]$ echo $ORACLE_SID
n1dg
[oracle@anakin scripts]$ export ORACLE_SID=n2dg
[oracle@anakin scripts]$ sqlplus / as sysdba
SQL*Plus: Release 10.2.0.3.0 - Production on Tue Jun 12 00:54:16 2007
Copyright (c) 1982, 2006, Oracle. All Rights Reserved.
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - Production
With the Partitioning, OLAP and Data Mining options
idle> SELECT PROCESS, STATUS,SEQUENCE#,BLOCK#,BLOCKS, DELAY_MINS FROM
2 V$MANAGED_STANDBY;
PROCESS STATUS SEQUENCE# BLOCK# BLOCKS DELAY_MINS
--------- ------------ ------------- ------------- ------------- -------------
ARCH CLOSING 23 1 67 0
ARCH CLOSING 22 1 1 0
MRP0 APPLYING_LOG 24 14 102400 0
RFS IDLE 24 15 1 0
RFS IDLE 0 0 0 0
RFS IDLE 0 0 0 0
6 rows selected.
idle> /
PROCESS STATUS SEQUENCE# BLOCK# BLOCKS DELAY_MINS
--------- ------------ ------------- ------------- ------------- -------------
ARCH CLOSING 23 1 67 0
ARCH CLOSING 22 1 1 0
MRP0 APPLYING_LOG 24 17 102400 0
RFS IDLE 24 18 1 0
RFS IDLE 0 0 0 0
RFS IDLE 0 0 0 0
6 rows selected.
idle> exit
Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - Production
With the Partitioning, OLAP and
Uma última consideração, quando configuramos o broker para gerenciar o Dataguard, até a versão 9i, não há necessidade de abrir o banco, é possível somente levantar os nós em NOMOUNT que ele faz o resto. Já na versão 10g, é necessário MONTAR o banco para que o broker levante e sincronize os nós.
Um lembrete: Use as instruções acima para exercício em ambiente TESTE e por sua conta e risco!
Labels: how to, New Feature
Sunday, June 03, 2007
Montar Dataguard - 10g
A tecnologia de alta disponibilidade e proteção de dados da Oracle é bastante divulgada pelo próprio site e por seus manuais. Então, para escrever esse "howto", tive a intenção de escrever um guia rápido e mostrar a facilidade do processo quando usamos o RMAN. Considere também que a instalação foi feita em uma única máquina - meu desktop.
Resumo do que farei a seguir:
- Criar uma instância do zero.
- Modificar os parâmetros para que seja aceito o data guard (archivelog e alguns parâmetros do spfile).
- Backup do banco usando o RMAN, já exportando controlfile para standby.
- Criação do standby database.
- Configurar o Data Guard para Realtime Apply.
- Criar a configuração para o Broker gerenciar o Data Guard.
- Teste de Switch over.
Criar uma instância do zero ou configurar uma já existente para o standby depende do seu cenário para o teste. No meu caso, eu usei o dbca e criei uma instância do zero. O nome é n1dg, que será meu nó primário.
Acima foram feitas as configurações básicas para a montagem do standby. O banco foi colocado em archivelog, forçado o log, definido nome único (db_unique_name=n1) e populados parâmetros do Dataguard, para melhor entendimento desses parâmetros, por favor consulte o manual.
A base está pronta para o backup, donde será criado o standby. O RMAN vai conectar-se a base target (n1dg) e gerar o backup, note no script usado que o controlfile já será exportado para standby.
No próximo post, faremos a aplicação dos logs realtime e a configuração do Broker.
Resumo do que farei a seguir:
- Criar uma instância do zero.
- Modificar os parâmetros para que seja aceito o data guard (archivelog e alguns parâmetros do spfile).
- Backup do banco usando o RMAN, já exportando controlfile para standby.
- Criação do standby database.
- Configurar o Data Guard para Realtime Apply.
- Criar a configuração para o Broker gerenciar o Data Guard.
- Teste de Switch over.
Criar uma instância do zero ou configurar uma já existente para o standby depende do seu cenário para o teste. No meu caso, eu usei o dbca e criei uma instância do zero. O nome é n1dg, que será meu nó primário.
[oracle@anakin scripts]$ export ORACLE_SID=n1dg
[oracle@anakin scripts]$ sqlplus / as sysdba
SQL*Plus: Release 10.2.0.3.0 - Production on Sun Jun 3 23:20:17 2007
Copyright (c) 1982, 2006, Oracle. All Rights Reserved.
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - Production
With the Partitioning, OLAP and Data Mining options
sys:N1DG> !ls -l /backup/n1
total 8
drwxr-xr-x 2 oracle dba 4096 Jun 3 23:21 arch
drwxr-xr-x 2 oracle dba 4096 Jun 3 23:11 n1dg
sys:N1DG> alter system set log_archive_dest_1='LOCATION=/backup/n1/arch';
System altered.
sys:N1DG> alter system set log_archive_format='%t_%s_%r.dbf' scope=spfile;
System altered.
sys:N1DG> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
sys:N1DG> startup mount
ORACLE instance started.
Total System Global Area 608174080 bytes
Fixed Size 1263200 bytes
Variable Size 180357536 bytes
Database Buffers 419430400 bytes
Redo Buffers 7122944 bytes
Database mounted.
sys:N1DG> alter database archivelog;
Database altered.
sys:N1DG> archive log list
Database log mode Archive Mode
Automatic archival Enabled
Archive destination /backup/n1/arch
Oldest online log sequence 1
Next log sequence to archive 2
Current log sequence 2
sys:N1DG> !ls -l /backup/n1/arch
total 0
sys:N1DG> alter database open;
Database altered.
sys:N1DG> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
sys:N1DG> startup nomount
ORACLE instance started.
Total System Global Area 608174080 bytes
Fixed Size 1263200 bytes
Variable Size 184551840 bytes
Database Buffers 415236096 bytes
Redo Buffers 7122944 bytes
sys:N1DG>
sys:N1DG> alter system set db_unique_name = 'n1' scope=spfile;
System altered.
sys:N1DG> alter database mount;
Database altered.
sys:N1DG> alter database force logging;
Database altered.
sys:N1DG> alter system set log_archive_config='DG_CONFIG=(n1, n2)';
System altered.
sys:N1DG> alter system set standby_file_management=auto;
System altered.
sys:N1DG> alter system set fal_client=n1;
System altered.
sys:N1DG> alter system set standby_archive_dest='/backup/n1/arch';
System altered.
sys:N1DG> alter system set fal_client=n1;
System altered.
sys:N1DG> alter system set fal_server=n2;
System altered.
sys:N1DG> alter database open;
Database altered.
sys:N1DG> show parameter name
NAME TYPE VALUE
------------------------------------ ----------- --------------------------------------------------
db_file_name_convert string
db_name string N1DG
db_unique_name string n1
global_names boolean FALSE
instance_name string n1dg
lock_name_space string
log_file_name_convert string
service_names string n1
sys:N1DG>
Acima foram feitas as configurações básicas para a montagem do standby. O banco foi colocado em archivelog, forçado o log, definido nome único (db_unique_name=n1) e populados parâmetros do Dataguard, para melhor entendimento desses parâmetros, por favor consulte o manual.
A base está pronta para o backup, donde será criado o standby. O RMAN vai conectar-se a base target (n1dg) e gerar o backup, note no script usado que o controlfile já será exportado para standby.
Hora de configurar a instância nova. Para que possamos restaurar o backup do rman na nova instância ela necessita estar iniciada em nomount. Para isso, vamos duplicar as áreas de diretórios (lembre-se que estou fazendo isso em uma mesma máquina, caso seu cenário seja em dois servidores, que é o correto, não há necessidade desta duplicação, basta criar a área da nova instância e criar o standby).
[oracle@anakin scripts]$ echo $ORACLE_SID
n1dg
[oracle@anakin scripts]$ rman target /
Recovery Manager: Release 10.2.0.3.0 - Production on Sun Jun 3 23:48:56 2007
Copyright (c) 1982, 2005, Oracle. All rights reserved.
connected to target database: N1DG (DBID=1215438780)
RMAN> list backup;
using target database control file instead of recovery catalog
RMAN> run {
2> allocate channel d1 type disk;
3> backup format '/backup/n1/df_t%t_s%s_p%p' database
4> include current controlfile for standby;
5> sql 'alter system archive log current';
6> backup format '/backup/n1/al_t%t_s%s_p%p' archivelog all;
7> release channel d1;
8> }
allocated channel: d1
channel d1: sid=144 devtype=DISK
Starting backup at 03-JUN-07
channel d1: starting full datafile backupset
channel d1: specifying datafile(s) in backupset
input datafile fno=00001 name=/backup/n1/n1dg/system01.dbf
input datafile fno=00003 name=/backup/n1/n1dg/sysaux01.dbf
input datafile fno=00002 name=/backup/n1/n1dg/undotbs01.dbf
input datafile fno=00004 name=/backup/n1/n1dg/users01.dbf
channel d1: starting piece 1 at 03-JUN-07
channel d1: finished piece 1 at 03-JUN-07
piece handle=/backup/n1/df_t624325825_s1_p1 tag=TAG20070603T235025 comment=NONE
channel d1: backup set complete, elapsed time: 00:00:25
channel d1: starting full datafile backupset
channel d1: specifying datafile(s) in backupset
including standby control file in backupset
including current SPFILE in backupset
channel d1: starting piece 1 at 03-JUN-07
channel d1: finished piece 1 at 03-JUN-07
piece handle=/backup/n1/df_t624325850_s2_p1 tag=TAG20070603T235025 comment=NONE
channel d1: backup set complete, elapsed time: 00:00:03
Finished backup at 03-JUN-07
sql statement: alter system archive log current
Starting backup at 03-JUN-07
current log archived
channel d1: starting archive log backupset
channel d1: specifying archive log(s) in backup set
input archive log thread=1 sequence=2 recid=1 stamp=624325853
input archive log thread=1 sequence=3 recid=2 stamp=624325853
channel d1: starting piece 1 at 03-JUN-07
channel d1: finished piece 1 at 03-JUN-07
piece handle=/backup/n1/al_t624325854_s3_p1 tag=TAG20070603T235053 comment=NONE
channel d1: backup set complete, elapsed time: 00:00:02
Finished backup at 03-JUN-07
released channel: d1
RMAN>
Standby database configurado! Vamos recapitular o que fizemos até aqui: um backup da instância primária com o rman, criamos uma estrutura de diretórios nova para a instância auxiliar (physical standby) e copiamos o init.ora com algumas alterações para que pudesse subir como standby, criamos um arquivo de senha e configuramos a rede (listener e tnsnames) além de subir o listener. Com a comunicação estabelecida, pudemos conectar ao target, que é nó primário, e no auxiliar instância que está apenas iniciada em NOMOUNT; então, pedimos para restaurar o backup do primário, porém ao invés de clonar, pedimos para ao RMAN que criasse o standby e como visto acimo no select, está perfeitamente configurado e esperando para receber os archives.
[oracle@anakin scripts]$ pwd
/oracle/admin/n1dg/scripts
[oracle@anakin scripts]$ cd ..
[oracle@anakin n1dg]$ cd ..
[oracle@anakin admin]$ ll -tr
total 16
drwxr-x--- 6 oracle dba 4096 Feb 15 14:22 +ASM
drwxr-x--- 8 oracle dba 4096 Feb 15 14:28 lx10g
drwxr-xr-x 7 ora92 dba 4096 Mar 27 16:48 lx92
drwxr-x--- 9 oracle dba 4096 Jun 3 23:06 n1dg
[oracle@anakin admin]$ mkdir -p n2dg/scripts
[oracle@anakin admin]$ cd n2dg/scripts/
[oracle@anakin scripts]$ cat ../../n1dg/scripts/n1dg.sh | sed '1,$s/n1dg/n2dg/' > n2dg.sh
[oracle@anakin scripts]$ cat n2dg.sh
#!/bin/sh
mkdir -p /backup/n1/n2dg
mkdir -p /oracle/10g/cfgtoollogs/dbca/n2dg
mkdir -p /oracle/10g/dbs
mkdir -p /oracle/admin/n2dg/adump
mkdir -p /oracle/admin/n2dg/bdump
mkdir -p /oracle/admin/n2dg/cdump
mkdir -p /oracle/admin/n2dg/dpdump
mkdir -p /oracle/admin/n2dg/pfile
mkdir -p /oracle/admin/n2dg/udump
ORACLE_SID=n2dg; export ORACLE_SID
echo You should Add this entry in the /etc/oratab: n2dg:/oracle/10g:Y
/oracle/10g/bin/sqlplus /nolog @/oracle/admin/n2dg/scripts/n1dg.sql
[oracle@anakin scripts]$ ll -tr
total 4
-rw-r--r-- 1 oracle dba 482 Jun 3 23:57 n2dg.sh
[oracle@anakin scripts]$ sh ./n2dg.sh
You should Add this entry in the /etc/oratab: n2dg:/oracle/10g:Y
SQL*Plus: Release 10.2.0.3.0 - Production on Sun Jun 3 23:58:13 2007
Copyright (c) 1982, 2006, Oracle. All Rights Reserved.
SP2-0310: unable to open file "/oracle/admin/n2dg/scripts/n1dg.sql"
idle> exit
[oracle@anakin scripts]$ ps -ef | grep pmon_
oracle 28293 1 0 23:30 ? 00:00:00 ora_pmon_n1dg
oracle 28418 27728 0 23:58 pts/2 00:00:00 grep pmon_
[oracle@anakin scripts]$ cat ../../n1dg/scripts/init.ora | sed '1,$s/n1dg/n2dg/' | egrep -v '^#' > init.ora
[oracle@anakin scripts]$ vi init.ora
[oracle@anakin scripts]$ cat init.ora
db_block_size=8192
db_file_multiblock_read_count=16
open_cursors=300
db_domain=""
db_name=n1dg
db_unique_name=n2
background_dump_dest=/oracle/admin/n2dg/bdump
core_dump_dest=/oracle/admin/n2dg/cdump
user_dump_dest=/oracle/admin/n2dg/udump
job_queue_processes=10
compatible=10.2.0.3.0
processes=150
sga_target=605028352
audit_file_dest=/oracle/admin/n2dg/adump
remote_login_passwordfile=EXCLUSIVE
dispatchers="(PROTOCOL=TCP) (SERVICE=n2dgXDB)"
pga_aggregate_target=201326592
undo_management=AUTO
undo_tablespace=UNDOTBS1
db_file_name_convert =('/backup/n1/n1dg', '/backup/n2/n2dg')
log_file_name_convert =('/backup/n1/n1dg', '/backup/n2/n2dg')
[oracle@anakin scripts]$ ll -tr
total 8
-rw-r--r-- 1 oracle dba 482 Jun 3 23:57 n2dg.sh
-rw-r--r-- 1 oracle dba 646 Jun 4 00:04 init.ora
[oracle@anakin scripts]$ ll /backup
total 44484
-rw-r--r-- 1 marcio dba 45465757 May 19 16:51 anakin.dmp.18may07.gz
drwx-w---- 2 oracle dba 16384 Feb 15 15:27 lost+found
drwxr-xr-x 3 oracle dba 4096 May 24 00:38 lx10g
drwxr-xr-x 3 ora92 dba 4096 May 21 00:39 lx92
drwxr-xr-x 5 oracle dba 4096 Jun 3 23:58 n1
drwxr-xr-x 3 oracle dba 4096 May 24 00:27 tmp
[oracle@anakin scripts]$
[oracle@anakin scripts]$ mkdir /backup/n2
[oracle@anakin scripts]$ mkdir /backup/n2/n2dg
[oracle@anakin scripts]$ ll /backup
total 44488
-rw-r--r-- 1 marcio dba 45465757 May 19 16:51 anakin.dmp.18may07.gz
drwx-w---- 2 oracle dba 16384 Feb 15 15:27 lost+found
drwxr-xr-x 3 oracle dba 4096 May 24 00:38 lx10g
drwxr-xr-x 3 ora92 dba 4096 May 21 00:39 lx92
drwxr-xr-x 5 oracle dba 4096 Jun 3 23:58 n1
drwxr-xr-x 2 oracle dba 4096 Jun 4 00:04 n2
drwxr-xr-x 3 oracle dba 4096 May 24 00:27 tmp
[oracle@anakin scripts]$ ll /oracle/10g/dbs
total 6992
-rw-rw---- 1 oracle dba 1583 May 31 20:27 ab_+ASM.dat
-rw-rw---- 1 oracle dba 1544 Apr 16 10:14 hc_+ASM.dat
-rw-rw---- 1 oracle dba 1544 Apr 16 10:15 hc_lx10g.dat
-rw-rw---- 1 oracle dba 1544 Jun 3 23:08 hc_n1dg.dat
-rw-rw-r-- 1 oracle dba 12920 May 3 2001 initdw.ora
-rw-r----- 1 oracle dba 37 Feb 15 14:30 initlx10g.ora
-rw-r----- 1 oracle dba 8385 Sep 11 1998 init.ora
-rw-rw---- 1 oracle dba 24 Feb 15 14:22 lk+ASM
-rw-rw---- 1 oracle dba 24 Feb 15 14:28 lkLX10G
-rw-rw---- 1 oracle dba 24 Jun 3 23:29 lkN1
-rw-rw---- 1 oracle dba 24 Jun 3 23:08 lkN1DG
-rw-r----- 1 oracle dba 1536 Feb 15 14:22 orapw+ASM
-rw-r----- 1 oracle dba 1536 May 24 00:38 orapwlx10g
-rw-r----- 1 oracle dba 1536 Jun 3 23:11 orapwn1dg
-rw-r----- 1 oracle dba 7061504 Jun 3 23:50 snapcf_n1dg.f
-rw-r----- 1 oracle dba 1536 Feb 15 14:23 spfile+ASM.ora
-rw-r----- 1 oracle dba 3584 Jun 3 23:30 spfilen1dg.ora
[oracle@anakin scripts]$ orapwd file=/oracle/10g/dbs/orapwn2dg password=oracle
[oracle@anakin scripts]$
[oracle@anakin scripts]$ cd /oracle/10g/network/admin
[oracle@anakin admin]$ ll -tr
total 24
-rw-r--r-- 1 oracle dba 172 Dec 26 2003 shrept.lst
drwxr-x--- 2 oracle dba 4096 Feb 15 14:08 samples
-rw-r--r-- 1 oracle dba 1126 May 23 00:29 tnsnames.ora
-rw-r--r-- 1 oracle dba 4605 May 23 01:06 sqlnet.log
-rw-r--r-- 1 oracle dba 722 May 23 01:10 listener.ora
[oracle@anakin admin]$ vi tnsnames.ora
[oracle@anakin admin]$ cat tnsnames.ora
# tnsnames.ora Network Configuration File: /oracle/10g/network/admin/tnsnames.ora
# Generated by Oracle configuration tools.
LX10G =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = anakin.mportes.local)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = lx10g)
)
)
LX92 =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = anakin.mportes.local)(PORT = 1621))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = lx92)
)
)
n1 =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = anakin.mportes.local)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SID = n1dg)
)
)
n2 =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = anakin.mportes.local)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SID = n2dg)
)
)
EXTPROC_CONNECTION_DATA =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC0))
)
(CONNECT_DATA =
(SID = PLSExtProc)
(PRESENTATION = RO)
)
)
[oracle@anakin admin]$ vi listener.ora
[oracle@anakin admin]$ cat listener.ora
# listener.ora Network Configuration File: /oracle/10g/network/admin/listener.ora
# Generated by Oracle configuration tools.
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(SID_NAME = PLSExtProc)
(ORACLE_HOME = /oracle/10g)
(PROGRAM = extproc)
)
(SID_DESC =
(GLOBAL_DBNAME = lx10g)
(ORACLE_HOME = /oracle/10g)
(SID_NAME = lx10g)
)
(SID_DESC =
(GLOBAL_DBNAME = n1_DGMGRL)
(ORACLE_HOME = /oracle/10g)
(SID_NAME = n1dg)
)
(SID_DESC =
(GLOBAL_DBNAME = n2_DGMGRL)
(ORACLE_HOME = /oracle/10g)
(SID_NAME = n2dg)
)
)
LISTENER =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = anakin.mportes.local)(PORT = 1521))
)
[oracle@anakin admin]$
[oracle@anakin admin]$ cd -
/oracle/admin/n2dg/scripts
[oracle@anakin scripts]$
[oracle@anakin scripts]$ pwd
/oracle/admin/n2dg/scripts
[oracle@anakin scripts]$ ll -tr
total 8
-rw-r--r-- 1 oracle dba 482 Jun 3 23:57 n2dg.sh
-rw-r--r-- 1 oracle dba 646 Jun 4 00:04 init.ora
[oracle@anakin scripts]$ echo $ORACLE_SID
n1dg
[oracle@anakin scripts]$ export ORACLE_SID=n2dg
[oracle@anakin scripts]$ sqlplus / as sysdba
SQL*Plus: Release 10.2.0.3.0 - Production on Mon Jun 4 00:08:31 2007
Copyright (c) 1982, 2006, Oracle. All Rights Reserved.
Connected to an idle instance.
idle> startup nomount pfile=./init.ora
ORACLE instance started.
Total System Global Area 608174080 bytes
Fixed Size 1263200 bytes
Variable Size 167774624 bytes
Database Buffers 432013312 bytes
Redo Buffers 7122944 bytes
idle> exit
Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - Production
With the Partitioning, OLAP and Data Mining options
[oracle@anakin scripts]$ ps -ef | grep pmon_
oracle 28293 1 0 Jun03 ? 00:00:00 ora_pmon_n1dg
oracle 28515 1 0 00:08 ? 00:00:00 ora_pmon_n2dg
oracle 28544 27728 0 00:08 pts/2 00:00:00 grep pmon_
[oracle@anakin scripts]$ tnsping n1
TNS Ping Utility for Linux: Version 10.2.0.3.0 - Production on 04-JUN-2007 00:09:15
Copyright (c) 1997, 2006, Oracle. All rights reserved.
Used parameter files:
Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = anakin.mportes.local)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SID = n1dg)))
TNS-12541: TNS:no listener
[oracle@anakin scripts]$ lsnrctl start
LSNRCTL for Linux: Version 10.2.0.3.0 - Production on 04-JUN-2007 00:09:22
Copyright (c) 1991, 2006, Oracle. All rights reserved.
Starting /oracle/10g/bin/tnslsnr: please wait...
TNSLSNR for Linux: Version 10.2.0.3.0 - Production
System parameter file is /oracle/10g/network/admin/listener.ora
Log messages written to /oracle/10g/network/log/listener.log
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=anakin.mportes.local)(PORT=1521)))
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=anakin.mportes.local)(PORT=1521)))
STATUS of the LISTENER
------------------------
Alias LISTENER
Version TNSLSNR for Linux: Version 10.2.0.3.0 - Production
Start Date 04-JUN-2007 00:09:22
Uptime 0 days 0 hr. 0 min. 0 sec
Trace Level off
Security ON: Local OS Authentication
SNMP OFF
Listener Parameter File /oracle/10g/network/admin/listener.ora
Listener Log File /oracle/10g/network/log/listener.log
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=anakin.mportes.local)(PORT=1521)))
Services Summary...
Service "PLSExtProc" has 1 instance(s).
Instance "PLSExtProc", status UNKNOWN, has 1 handler(s) for this service...
Service "lx10g" has 1 instance(s).
Instance "lx10g", status UNKNOWN, has 1 handler(s) for this service...
Service "n1_DGMGRL" has 1 instance(s).
Instance "n1dg", status UNKNOWN, has 1 handler(s) for this service...
Service "n2_DGMGRL" has 1 instance(s).
Instance "n2dg", status UNKNOWN, has 1 handler(s) for this service...
The command completed successfully
[oracle@anakin scripts]$ tnsping n1
TNS Ping Utility for Linux: Version 10.2.0.3.0 - Production on 04-JUN-2007 00:09:28
Copyright (c) 1997, 2006, Oracle. All rights reserved.
Used parameter files:
Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = anakin.mportes.local)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SID = n1dg)))
OK (0 msec)
[oracle@anakin scripts]$ rman target sys/oracle@n1 auxiliary /
Recovery Manager: Release 10.2.0.3.0 - Production on Mon Jun 4 00:12:38 2007
Copyright (c) 1982, 2005, Oracle. All rights reserved.
connected to target database: N1DG (DBID=1215438780)
connected to auxiliary database: N1DG (not mounted)
RMAN> run {
2> allocate auxiliary channel C1 device type disk;
3> duplicate target database for standby;
4> }
using target database control file instead of recovery catalog
allocated channel: C1
channel C1: sid=156 devtype=DISK
Starting Duplicate Db at 04-JUN-07
contents of Memory Script:
{
restore clone standby controlfile;
sql clone 'alter database mount standby database';
}
executing Memory Script
Starting restore at 04-JUN-07
channel C1: starting datafile backupset restore
channel C1: restoring control file
channel C1: reading from backup piece /backup/n1/df_t624325850_s2_p1
channel C1: restored backup piece 1
piece handle=/backup/n1/df_t624325850_s2_p1 tag=TAG20070603T235025
channel C1: restore complete, elapsed time: 00:00:03
output filename=/oracle/10g/dbs/cntrln2dg.dbf
Finished restore at 04-JUN-07
sql statement: alter database mount standby database
contents of Memory Script:
{
set newname for tempfile 1 to
"/backup/n2/n2dg/temp01.dbf";
switch clone tempfile all;
set newname for datafile 1 to
"/backup/n2/n2dg/system01.dbf";
set newname for datafile 2 to
"/backup/n2/n2dg/undotbs01.dbf";
set newname for datafile 3 to
"/backup/n2/n2dg/sysaux01.dbf";
set newname for datafile 4 to
"/backup/n2/n2dg/users01.dbf";
restore
check readonly
clone database
;
}
executing Memory Script
executing command: SET NEWNAME
renamed temporary file 1 to /backup/n2/n2dg/temp01.dbf in control file
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
Starting restore at 04-JUN-07
channel C1: starting datafile backupset restore
channel C1: specifying datafile(s) to restore from backup set
restoring datafile 00001 to /backup/n2/n2dg/system01.dbf
restoring datafile 00002 to /backup/n2/n2dg/undotbs01.dbf
restoring datafile 00003 to /backup/n2/n2dg/sysaux01.dbf
restoring datafile 00004 to /backup/n2/n2dg/users01.dbf
channel C1: reading from backup piece /backup/n1/df_t624325825_s1_p1
channel C1: restored backup piece 1
piece handle=/backup/n1/df_t624325825_s1_p1 tag=TAG20070603T235025
channel C1: restore complete, elapsed time: 00:00:35
Finished restore at 04-JUN-07
contents of Memory Script:
{
switch clone datafile all;
}
executing Memory Script
datafile 1 switched to datafile copy
input datafile copy recid=5 stamp=624327223 filename=/backup/n2/n2dg/system01.dbf
datafile 2 switched to datafile copy
input datafile copy recid=6 stamp=624327223 filename=/backup/n2/n2dg/undotbs01.dbf
datafile 3 switched to datafile copy
input datafile copy recid=7 stamp=624327223 filename=/backup/n2/n2dg/sysaux01.dbf
datafile 4 switched to datafile copy
input datafile copy recid=8 stamp=624327223 filename=/backup/n2/n2dg/users01.dbf
Finished Duplicate Db at 04-JUN-07
released channel: C1
RMAN> exit
Recovery Manager complete.
[oracle@anakin scripts]$ sqlplus / as sysdba
SQL*Plus: Release 10.2.0.3.0 - Production on Mon Jun 4 00:13:59 2007
Copyright (c) 1982, 2006, Oracle. All Rights Reserved.
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - Production
With the Partitioning, OLAP and Data Mining options
idle> select NAME, OPEN_MODE, PROTECTION_MODE, database_role from v$database;
NAME OPEN_MODE PROTECTION_MODE DATABASE_ROLE
--------- ---------- -------------------- ----------------
N1DG MOUNTED MAXIMUM PERFORMANCE PHYSICAL STANDBY
1 row selected.
idle>
No próximo post, faremos a aplicação dos logs realtime e a configuração do Broker.
Labels: how to
