[DICA] Standby Database - parte II

DBA Geral (instalação, configuração do banco, scripts do Sql*Plus, shell scripts, batch scripts, etc...)
Responder
pauloaleo
Rank: DBA Júnior
Rank: DBA Júnior
Mensagens: 181
Registrado em: Qua, 09 Fev 2005 12:30 pm
Localização: SÃO BERNARDO DO CAMPO - SP

As instâncias criadas (db1n e db2n), a princípio definidas como db1n = nó primário e db2n = nó standby físico. Vamos então aos copies e pastes.

Garantia que não havia nenhuma instance viva.

Selecionar tudo

[oracle@paleo ~]$ ps -ef | grep pmon_
oracle    6175  6044  0 00:07 pts/1    00:00:00 grep pmon_
[oracle@paleo ~]$
[oracle@paleo ~]$
[oracle@paleo ~]$ cd /oracle/admin/
[oracle@paleo 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 db1n
drwxr-xr-x  9 oracle dba 4096 Jun  3 23:58 db2n
[oracle@paleo admin]$
Levantando nó primário

Selecionar tudo

[oracle@paleo admin]$ export ORACLE_SID=db1n
[oracle@paleo 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

Selecionar tudo

idle> show parameter archive

NAME                                 TYPE        VALUE
------------------------------------ ----------- -----------------------------
archive_lag_target                   integer     0
log_archive_config                   string      DG_CONFIG=(b1n, b2n)
log_archive_dest                     string
log_archive_dest_1                   string      LOCATION=/backup/b1n/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/b1n/arch

idle> show parameter db_name

NAME                                 TYPE        VALUE
------------------------------------ ----------- ---------------------------
db_name                              string      DB1N
idle> show parameter db_unique_name

NAME                                 TYPE        VALUE
------------------------------------ ----------- ---------------------------
db_unique_name                       string      b1n
idle> show parameter fal

NAME                                 TYPE        VALUE
------------------------------------ ----------- ---------------------------
fal_client                           string      B1N
fal_server                           string      B2N
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.

Selecionar tudo

idle> alter system set LOG_ARCHIVE_DEST_1='LOCATION=/backup/b1n/arch
  2                        VALID_FOR=(ALL_LOGFILES,ALL_ROLES)
  3                        DB_UNIQUE_NAME=b1n'
  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.

Selecionar tudo

idle> alter system set LOG_ARCHIVE_DEST_2='SERVICE=b2n
  2                   VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE)
  3                   DB_UNIQUE_NAME=b2n';
  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@paleo admin]$
[oracle@paleo admin]$
[oracle@paleo admin]$ echo $ORACLE_SID
db1n
[oracle@paleo admin]$ export ORACLE_SID=db2n

Passo 2: Configurar o standby (db2n) - Não esquecer que a parte de rede foi previamente definida no artigo anterior.

[oracle@paleo 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/initdb2n.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.

Selecionar tudo

[oracle@paleo admin]$ cd db2n/scripts/
[oracle@paleo scripts]$ ll -tr
total 8
-rw-r--r--  1 oracle dba 482 Jun  3 23:57 db2n.sh
-rw-r--r--  1 oracle dba 646 Jun  4 00:04 init.ora
[oracle@paleo 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/db2n/scripts

idle> create spfile from pfile='/oracle/admin/db2n/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      db1n
idle> show parameter db_unique_name

NAME                                 TYPE        VALUE
------------------------------------ ----------- -------------------------
db_unique_name                       string      b2n

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/b2n/arch';

System altered.

idle> alter system set log_archive_config='DG_CONFIG=(b1n, b2n)';

System altered.

idle> alter system set LOG_ARCHIVE_DEST_1='LOCATION=/backup/b2n/arch
2                        VALID_FOR=(ALL_LOGFILES,ALL_ROLES)
3                        DB_UNIQUE_NAME=b2n'
4 /

System altered.

idle> alter system set LOG_ARCHIVE_DEST_2='SERVICE=b1n
2                        VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE)
3                        DB_UNIQUE_NAME=b1n'
4 /

System altered.

Server do standby é o nó principal (B1N) e client é o (B2N) o inverso do nó primário.

idle> alter system set fal_server=b1n;

System altered.

idle> alter system set fal_client=b2n;

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.

Selecionar tudo

idle> ALTER DATABASE ADD STANDBY LOGFILE GROUP 4 ('/backup/b2n/db2n/srl01.log') SIZE 50M;

Database altered.

idle> ALTER DATABASE ADD STANDBY LOGFILE GROUP 5 ('/backup/b2n/db2n/srl02.log') SIZE 50M;

Database altered.

idle> ALTER DATABASE ADD STANDBY LOGFILE GROUP 6 ('/backup/b2n/db2n/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@paleo scripts]$ export ORACLE_SID=db1n
[oracle@paleo 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:DB1N> ALTER DATABASE ADD STANDBY LOGFILE GROUP 4 ('/backup/b1n/db1n/srl01.log') SIZE 50M;

Database altered.

sys:DB1N> ALTER DATABASE ADD STANDBY LOGFILE GROUP 5 ('/backup/b1n/db1n/srl02.log') SIZE 50M;

Database altered.

sys:DB1N> ALTER DATABASE ADD STANDBY LOGFILE GROUP 6 ('/backup/b1n/db1n/srl03.log') SIZE 50M;

Database altered.

sys:DB1N> 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:DB1N> alter system switch logfile;

System altered.

sys:DB1N> alter system switch logfile;

System altered.

sys:DB1N> 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:DB1N> exit
Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - Production
With the Partitioning, OLAP and Data Mining options
[oracle@paleo scripts]$ export ORACLE_SID=db2n
[oracle@paleo 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 você conhece o broker, nunca mais vai achar difícil fazer switchover, failover ou qualquer outra manutenção no seu dataguard).

Selecionar tudo

[oracle@paleo scripts]$ export ORACLE_SID=db1n
[oracle@paleo scripts]$
[oracle@paleo 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:DB1N> show parameter dg

NAME                                 TYPE        VALUE
------------------------------------ ----------- --------------------------------------------------
dg_broker_config_file1               string      /oracle/10g/dbs/dr1b1n.dat
dg_broker_config_file2               string      /oracle/10g/dbs/dr2b1n.dat
dg_broker_start                      boolean     FALSE
sys:DB1N> alter system set dg_broker_start=true;

System altered.

sys:DB1N> exit
Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - Production
With the Partitioning, OLAP and Data Mining options
[oracle@paleo scripts]$ export ORACLE_SID=db2n
[oracle@paleo 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@paleo scripts]$ export ORACLE_SID=db1n
[oracle@paleo 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.

Selecionar tudo

[oracle@paleo 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 b1n
>  connect identifier is b1n;
Configuration "mportes_dg" created with primary database "b1n"
DGMGRL> ADD DATABASE b2n AS CONNECT IDENTIFIER IS b2n 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.

Selecionar tudo

[oracle@paleo 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.

Selecionar tudo

[oracle@paleo scripts]$ tnsping b2n

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 = paleo.mportes.local)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SID = db2n)))
TNS-12541: TNS:no listener
Bingo! Esqueci de subir o listener.

Selecionar tudo

[oracle@paleo 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=paleo.mportes.local)(PORT=1521)))

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=paleo.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=paleo.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 "b1n_DGMGRL" has 1 instance(s).
  Instance "db1n", status UNKNOWN, has 1 handler(s) for this service...
Service "b2n_DGMGRL" has 1 instance(s).
  Instance "db2n", status UNKNOWN, has 1 handler(s) for this service...
The command completed successfully
Vamos tentar de novo!

Selecionar tudo

[oracle@paleo 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 b2n AS CONNECT IDENTIFIER IS b2n MAINTAINED AS PHYSICAL;
Database "b2n" 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.

Selecionar tudo

DGMGRL> show configuration

Configuration
  Name:                mportes_dg
  Enabled:             NO
  Protection Mode:     MaxPerformance
  Fast-Start Failover: DISABLED
  Databases:
    b1n - Primary database
    b2n - Physical standby database

Current status for "mportes_dg":
DISABLED
Habilite a configuração.

Selecionar tudo

DGMGRL> enable configuration
Enabled.
DGMGRL> show configuration verbose

Configuration
  Name:                mportes_dg
  Enabled:             YES
  Protection Mode:     MaxPerformance
  Fast-Start Failover: DISABLED
  Databases:
    b1n - Primary database
    b2n - Physical standby database

Current status for "mportes_dg":
Warning: ORA-16610: command 'ENABLE DATABASE b2n' 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 você 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.

Selecionar tudo

DGMGRL> show configuration verbose

Configuration
  Name:                mportes_dg
  Enabled:             YES
  Protection Mode:     MaxPerformance
  Fast-Start Failover: DISABLED
  Databases:
    b1n - Primary database
    b2n - 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).

Selecionar tudo

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 ninguém aqui está participando do campionato mundial de "saber tudo de cabeça".

Selecionar tudo

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 b1n

Database
Name:            b1n
Role:            PRIMARY
Enabled:         YES
Intended State:  ONLINE
Instance(s):
  db1n

Properties:
  InitialConnectIdentifier        = 'b1n'
  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                        = 'paleo.mportes.local'
  SidName                         = 'db1n'
  LocalListenerAddress            = '(ADDRESS=(PROTOCOL=tcp)(HOST=paleo.mportes.local)(PORT=1521))'
  StandbyArchiveLocation          = '/backup/b1n/arch'
  AlternateLocation               = ''
  LogArchiveTrace                 = '0'
  LogArchiveFormat                = '%t_%s_%r.dbf'
  LatestLog                       = '(monitor)'
  TopWaitEvents                   = '(monitor)'

Current status for "b1n":
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 DATABASE  SET PROPERTY  = ;

EDIT DATABASE  RENAME TO ;

EDIT DATABASE  SET STATE = 
  [WITH APPLY INSTANCE = ];

EDIT INSTANCE  [ON DATABASE ]
  SET AUTO PFILE [ = {|OFF} ];

EDIT INSTANCE  [ON DATABASE ]
  SET PROPERTY  = ;

Alterar nos dois databases.

Selecionar tudo

DGMGRL> edit database b1n set property LogXptMode='SYNC';
Property "logxptmode" updated
DGMGRL> edit database b2n set property LogXptMode='SYNC';
Property "logxptmode" updated
DGMGRL> alter configuration set protection mode as maxavailability;
Succeeded.
Ok, protection mode alterado! O maxavailability é nosso preferido.

Selecionar tudo

DGMGRL> show configuration verbose

Configuration
  Name:                mportes_dg
  Enabled:             YES
  Protection Mode:     MaxAvailability
  Fast-Start Failover: DISABLED
  Databases:
    b1n - Primary database
    b2n - 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.

Selecionar tudo

[oracle@paleo scripts]$ export ORACLE_SID=db2n
[oracle@paleo 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.

Selecionar tudo

[oracle@paleo 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:
    b1n - Primary database
    b2n - Physical standby database

Current status for "mportes_dg":
SUCCESS

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

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

Database dismounted.
ORACLE instance shut down.
Operation requires startup of instance "db1n" on database "b1n"
Starting instance "db1n"...
ORACLE instance started.
Database mounted.
Operation requires startup of instance "db2n" on database "b2n"
Starting instance "db2n"...
ORACLE instance started.
Database mounted.
Switchover succeeded, new primary is "b2n"
DGMGRL> show configuration

Configuration
  Name:                mportes_dg
  Enabled:             YES
  Protection Mode:     MaxAvailability
  Fast-Start Failover: DISABLED
  Databases:
    b1n - Physical standby database
    b2n - Primary database

Current status for "mportes_dg":
SUCCESS
DGMGRL> exit
Pronto! Viu só? Bastou "switchover to " e ele faz tudo. Não deixe de ver os alerts logs com o tail -f.
Vamos verificar!

Selecionar tudo

[oracle@paleo scripts]$ echo $ORACLE_SID
db2n
[oracle@paleo 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:DB1N> 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
--------- ---------- -------------------- -------------------- -------- ---------------- -------------------- -------- ---
DB1N      READ WRITE MAXIMUM AVAILABILITY MAXIMUM AVAILABILITY ENABLED  PRIMARY          SESSIONS ACTIVE      ENABLED  YES

1 row selected.

sys:DB1N> exit
Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - Production
With the Partitioning, OLAP and Data Mining options
[oracle@paleo scripts]$ export ORACLE_SID=db1n
[oracle@paleo 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ó B1N.

Selecionar tudo

[oracle@paleo 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:
    b1n - Physical standby database
    b2n - Primary database

Current status for "mportes_dg":
SUCCESS

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

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

Database dismounted.
ORACLE instance shut down.
Operation requires startup of instance "db2n" on database "b2n"
Starting instance "db2n"...
ORACLE instance started.
Database mounted.
Operation requires startup of instance "db1n" on database "b1n"
Starting instance "db1n"...
ORACLE instance started.
Database mounted.
Switchover succeeded, new primary is "b1n"
DGMGRL>
DGMGRL> show configuration

Configuration
  Name:                mportes_dg
  Enabled:             YES
  Protection Mode:     MaxAvailability
  Fast-Start Failover: DISABLED
  Databases:
    b1n - Primary database
    b2n - Physical standby database

Current status for "mportes_dg":
SUCCESS

DGMGRL> exit
[oracle@paleo scripts]$ echo $ORACLE_SID
db1n
[oracle@paleo scripts]$ export ORACLE_SID=db2n
[oracle@paleo 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.
Voltar ao topo
Responder

Voltar para “DBA Geral”

  • Informação

  • Quem está online

    Usuários navegando neste fórum: Nenhum usuário registrado e 1 visitante