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
Wednesday, April 11, 2007
Constraints e CBO
Quem nunca ouviu dizer, que devemos evitar o uso de funções em comparações na clausula WHERE, porque podemos invalidar o uso de índice caso esse exista. É uma boa prática de programação e o que vou demonstrar abaixo não a invalida, porém, mostra a capacidade do otimizador em identificar as informações que damos a ele e como é importante alimentarmos o Oracle com as constraints (primary key, not null, foreign key, checks, etc).
Iniciamos o teste com um tabela com aproximadamente 52 mil registros, nela, haverá duas constraints - uma primary key (o_id) e um check (oname), também haverá um índice normal na coluna (oname). Depois de coletar as estatísticas, farei uma consulta com o predicado UPPER(oname) = 'valor' cujo o uso invalidaria o indice, porém em nosso exemplo, o otimizador sabe que a coluna não permite nulo e só permite valor em UPPER case, então ele faz a dedução e decide usar o índice.
Essa capacidade de entender e decidir usar o índice já está valendo desde a versão 9i. Juntamente com essa demonstração, gostaria de mostrar algumas melhorias que vieram com o 10g. Na demo, há uma query principal que faz a projeção das colunas que estou selecionando e uma subquery, onde busco o predicado UPPER e o max( da primary key ). Agora é possível nomear as subqueries e as queries de uma sentença com o hint /*+ qb_name() */ (Query Block Name) e a partir dessa mesma versão, o dbms_xplan dá mais informações, inclusive dos nomes destas queries.
Preparando o exemplo e coletando estatísticas
Limpar a plan_table
Acima está o exemplo. Note o upper(oname) na linha 6, isso normalmente invalidaria o índice, mas como pode ser visto abaixo, ele usou o índice T_I1, que está relacionado a esta coluna (oname), sem que o índice fosse do tipo FBI (Function Based Index), ou seja, eu não preparei um índice especial para que ele assumisse, não, o índice é um b-tree normal. Note também, os argumentos null, null, 'ALL' que passo para o dbms_xplan.display, isso é novo no 10g e abaixo, repare nas 3 seções que o resultado traz, deixa bem mais fácil a vida da pessoa que está trabalhando com otimização de queries inclusive com os nomes das queries que usei apenas para ilustrar o exemplo.
Iniciamos o teste com um tabela com aproximadamente 52 mil registros, nela, haverá duas constraints - uma primary key (o_id) e um check (oname), também haverá um índice normal na coluna (oname). Depois de coletar as estatísticas, farei uma consulta com o predicado UPPER(oname) = 'valor' cujo o uso invalidaria o indice, porém em nosso exemplo, o otimizador sabe que a coluna não permite nulo e só permite valor em UPPER case, então ele faz a dedução e decide usar o índice.
Essa capacidade de entender e decidir usar o índice já está valendo desde a versão 9i. Juntamente com essa demonstração, gostaria de mostrar algumas melhorias que vieram com o 10g. Na demo, há uma query principal que faz a projeção das colunas que estou selecionando e uma subquery, onde busco o predicado UPPER e o max( da primary key ). Agora é possível nomear as subqueries e as queries de uma sentença com o hint /*+ qb_name() */ (Query Block Name) e a partir dessa mesma versão, o dbms_xplan dá mais informações, inclusive dos nomes destas queries.
Preparando o exemplo e coletando estatísticas
ops$marcio:LX10G> create table t (
2 o_id number constraint t_pk primary key,
3 oname varchar2(30) not null,
4 padding varchar2(200),
5 constraint t_chk_oname check ( oname = upper(oname) )
6 );
Table created.
ops$marcio:LX10G>
ops$marcio:LX10G> create index t_i1 on t ( oname );
Index created.
ops$marcio:LX10G>
ops$marcio:LX10G> insert /*+ append */ into t
2 select object_id,
3 substr(upper(object_name),1,30),
4 lpad('x',200,'x')
5 from dba_objects
6 /
51925 rows created.
ops$marcio:LX10G>
ops$marcio:LX10G> begin
2 dbms_stats.gather_table_stats(
3 user,
4 'T',
5 cascade => true
6 );
7 end;
8 /
PL/SQL procedure successfully completed.
Limpar a plan_table
ops$marcio:LX10G>
ops$marcio:LX10G> delete from plan_table;
6 rows deleted.
ops$marcio:LX10G> commit;
Commit complete.
ops$marcio:LX10G>
ops$marcio:LX10G> explain plan for
2 select /*+ qb_name( principal ) */ *
3 from t t1
4 where o_id = ( select /*+ qb_name ( maxid ) */ max(o_id)
5 from t t2
6 where upper(oname) = 'ALL_OBJECTS' )
7
8 /
Explained.
Acima está o exemplo. Note o upper(oname) na linha 6, isso normalmente invalidaria o índice, mas como pode ser visto abaixo, ele usou o índice T_I1, que está relacionado a esta coluna (oname), sem que o índice fosse do tipo FBI (Function Based Index), ou seja, eu não preparei um índice especial para que ele assumisse, não, o índice é um b-tree normal. Note também, os argumentos null, null, 'ALL' que passo para o dbms_xplan.display, isso é novo no 10g e abaixo, repare nas 3 seções que o resultado traz, deixa bem mais fácil a vida da pessoa que está trabalhando com otimização de queries inclusive com os nomes das queries que usei apenas para ilustrar o exemplo.
ops$marcio:LX10G>
ops$marcio:LX10G> select * from table( dbms_xplan.display( null, null, 'ALL'));
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 2855282288
---------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 229 | 4 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID | T | 1 | 229 | 2 (0)| 00:00:01 |
|* 2 | INDEX UNIQUE SCAN | T_PK | 1 | | 1 (0)| 00:00:01 |
| 3 | SORT AGGREGATE | | 1 | 30 | | |
| 4 | TABLE ACCESS BY INDEX ROWID| T | 1 | 30 | 2 (0)| 00:00:01 |
|* 5 | INDEX RANGE SCAN | T_I1 | 1 | | 1 (0)| 00:00:01 |
---------------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
1 - PRINCIPAL / T1@PRINCIPAL
2 - PRINCIPAL / T1@PRINCIPAL
3 - MAXID
4 - MAXID / T2@MAXID
5 - MAXID / T2@MAXID
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("O_ID"= (SELECT /*+ QB_NAME ("MAXID") */ MAX("O_ID") FROM "T"
"T2" WHERE "ONAME"='ALL_OBJECTS' AND UPPER("ONAME")='ALL_OBJECTS'))
5 - access("ONAME"='ALL_OBJECTS')
filter(UPPER("ONAME")='ALL_OBJECTS')
Column Projection Information (identified by operation id):
-----------------------------------------------------------
1 - "O_ID"[NUMBER,22], "T1"."ONAME"[VARCHAR2,30],
"T1"."PADDING"[VARCHAR2,200]
2 - "T1".ROWID[ROWID,10], "O_ID"[NUMBER,22]
3 - (#keys=0) MAX("O_ID")[22]
4 - "O_ID"[NUMBER,22]
5 - "T2".ROWID[ROWID,10]
39 rows selected.
Labels: New Feature, Performance
Tuesday, March 27, 2007
Drop Datafile (Aprofundando...)
Recebi alguns feedbacks sobre o post anterior, portanto vamos estudar um pouco mais o drop datafile.
Não, os dados não são jogados em outro datafile, neste caso não é permitido o drop datafile e não só nesse caso. Veja:
Visitei hoje seu blog e li o artigo sobre como dropar um datafile. No exemplo você dropa um datafile de uma tablespace que possui dois datafile.
O que acontece com o conteudo daquele datafile? Porque ate onde eu sei não é possivel colocarmos uma determinada tabela por exemplo em um datafile específico, somente especificamos em qual tablespace queremos alocar a tabela e o proprio banco distribui pelos datafiles.
Os dados do datafile dropado são jogados para o outro datafile? Ou todos os objetos que estavam contidos inteiramente ou parcialmente no datafile também são dropados?
Fizemos uma carga e o oracle distribuiu os dados uniformemente através dos datafiles, portanto, 50% para cada. Nesse caso, o comando já não é mais permitido, porque os datafiles não estão vazios.
ops$marcio:LX10G> create tablespace tbs_deldf datafile
2 '/oracle/tmp/df1.dbf' size 10m,
3 '/oracle/tmp/df2.dbf' size 10m
4 /
Tablespace created.
ops$marcio:LX10G>
ops$marcio:LX10G> col file_name format a50
ops$marcio:LX10G> select file_name
2 from dba_data_files
3 where tablespace_name = 'TBS_DELDF'
4 /
FILE_NAME
--------------------------------------------------
/oracle/tmp/df1.dbf
/oracle/tmp/df2.dbf
2 rows selected.
ops$marcio:LX10G>
ops$marcio:LX10G> drop table t;
Table dropped.
ops$marcio:LX10G> create table t tablespace tbs_deldf as select * from all_objects;
Table created.
ops$marcio:LX10G> insert /*+ append */ into t select * from t;
51191 rows created.
ops$marcio:LX10G> commit;
Commit complete.
ops$marcio:LX10G>
ops$marcio:LX10G> select count(*) from t;
COUNT(*)
-------------
102382
1 row selected.
ops$marcio:LX10G>
ops$marcio:LX10G> select file_name, round( bytes/1024/1024 ) mbytes,
2 round( ratio_to_report(bytes) over () * 100, 2 ) pct
3 from (
4 select sum(a.bytes) bytes, b.file_name
5 from dba_extents a,
6 dba_data_files b
7 where a.owner = USER
8 and a.segment_name = 'T'
9 and a.segment_type = 'TABLE'
10 and a.file_id = b.file_id
11 group by b.file_name
12 )
13 order by file_name
14 /
FILE_NAME MBYTES PCT
-------------------------------------------------- ------------- -------------
/oracle/tmp/df1.dbf 6 50
/oracle/tmp/df2.dbf 6 50
2 rows selected.
Outra restrição para o comando, não é permitido dropar o primeiro datafile da tablespace (no momento da criação).
ops$marcio:LX10G> alter tablespace tbs_deldf drop datafile '/oracle/tmp/df2.dbf';
alter tablespace tbs_deldf drop datafile '/oracle/tmp/df2.dbf'
*
ERROR at line 1:
ORA-03262: the file is non-empty
ops$marcio:LX10G> drop table t;Drop é suficiente para limpar um datafile? Não! Não podemos esquecer que um drop na versão 10g, na verdade marca a tabela como dropada, mas o datafile ainda não está vazio enquanto não purgamos os dados. Nesse caso, o comando também falha.
Table dropped.
ops$marcio:LX10G> alter tablespace tbs_deldf drop datafile '/oracle/tmp/df1.dbf';
alter tablespace tbs_deldf drop datafile '/oracle/tmp/df1.dbf'
*
ERROR at line 1:
ORA-03263: cannot drop the first file of tablespace TBS_DELDF
ops$marcio:LX10G> alter tablespace tbs_deldf drop datafile '/oracle/tmp/df2.dbf';Outro caso é quando a tablespace possui apenas um datafile.
alter tablespace tbs_deldf drop datafile '/oracle/tmp/df2.dbf'
*
ERROR at line 1:
ORA-03262: the file is non-empty
ops$marcio:LX10G> purge recyclebin;
Recyclebin purged.
ops$marcio:LX10G> alter tablespace tbs_deldf drop datafile '/oracle/tmp/df2.dbf';
Tablespace altered.
É importante frizar também que o comando drop datafile não substitui o alter database ... offline drop, eles convivem e cada qual tem sua aplicação.
ops$marcio:LX10G> alter tablespace tbs_deldf drop datafile '/oracle/tmp/df1.dbf';
alter tablespace tbs_deldf drop datafile '/oracle/tmp/df1.dbf'
*
ERROR at line 1:
ORA-03261: the tablespace TBS_DELDF has only one file
Labels: New Feature
Monday, March 26, 2007
Drop Datafile (10g)
Até a versão 10g, dropar um datafile era uma tarefa "quase" impossível, apesar da nota no metalink sobre como dropar datafile, convenhamos, não é uma ação para uma sexta-feira à tarde. Porém, com a versão 10g, isso virou um comando bastante simples.
Apenas uma adendo, o comando não funciona para tablespaces do tipo bigfile.
ops$marcio:LX10G> create tablespace tbs_deldf datafile
2 '/oracle/tmp/df1.dbf' size 10m,
3 '/oracle/tmp/df2.dbf' size 10m
4 /
Tablespace created.
ops$marcio:LX10G>
ops$marcio:LX10G> col file_name format a50
ops$marcio:LX10G> select file_name
2 from dba_data_files
3 where tablespace_name = 'TBS_DELDF'
4 /
FILE_NAME
--------------------------------------------------
/oracle/tmp/df1.dbf
/oracle/tmp/df2.dbf
2 rows selected.
ops$marcio:LX10G>
ops$marcio:LX10G> alter tablespace tbs_deldf drop datafile '/oracle/tmp/df2.dbf';
Tablespace altered.
ops$marcio:LX10G>
ops$marcio:LX10G> select file_name
2 from dba_data_files
3 where tablespace_name = 'TBS_DELDF'
4 /
FILE_NAME
--------------------------------------------------
/oracle/tmp/df1.dbf
1 row selected.
Apenas uma adendo, o comando não funciona para tablespaces do tipo bigfile.
Labels: New Feature
Friday, June 17, 2005
Tablespace Resize (10g)
Na versão 10g já há possibilidade de fazer resize na tablespace, anteriormente isso só era permitido através dos datafiles com o comando ALTER DATABASE ... autoextend_clause. Se a tablespace foi criada com a cláusula BIGFILE, ou seja, que tablespace permite apenas adicionar um datafile ou tempfile por tablespace. Veja no exemplo:
ops$marcio@ORA10G> create BIGFILE tablespace tbs datafile 'c:\temp\tbs_df.dbf' size 5m;
Tablespace created.
ops$marcio@ORA10G>
ops$marcio@ORA10G> select tablespace_name, sum(bytes)/1024 Kbytes
2 from dba_data_files
3 where tablespace_name = 'TBS'
4 group by tablespace_name
5 /
TABLESPACE_NAME KBYTES
------------------------------ -------------
TBS 5120
1 row selected.
ops$marcio@ORA10G>
ops$marcio@ORA10G> save tmp_space replace
Wrote file tmp_space.sql
ops$marcio@ORA10G> alter tablespace tbs resize 10m;
Tablespace altered.
ops$marcio@ORA10G> @tmp_space
ops$marcio@ORA10G> select tablespace_name, sum(bytes)/1024 Kbytes
2 from dba_data_files
3 where tablespace_name = 'TBS'
4 group by tablespace_name
5 /
TABLESPACE_NAME KBYTES
------------------------------ -------------
TBS 10240
1 row selected.
ops$marcio@ORA10G> select tablespace_name, bigfile
2 from dba_tablespaces
3 where bigfile = 'YES'
4 /
TABLESPACE_NAME BIG
------------------------------ ---
TBS YES
1 row selected.
ops$marcio@ORA10G>
ops$marcio@ORA10G> drop tablespace tbs including contents and datafiles;
Tablespace dropped.
Labels: New Feature
Monday, May 30, 2005
Audit Trail - FGA (Fine-Grained Audit) 10g
No artigo anterior, vimos uma necessidade que levou-me a optar por uma solução caseira, ou seja, tive que desenvolver o código para instrumentar as DMLs, valores anteriores e novos para cada tabela.
Vou discutir hoje, uma auditoria built in, o FGA (Fine-grained Audit) espécie de controle de acesso granular. Através da DBMS_FGA podemos cadastrar políticas para determinada tabela e ver o resultado na DBA_FGA_AUDIT_TRAIL.
Primeiro vamos entender a DBMS_FGA e o subprograma que adiciona política a ADD_POLICY.
Parâmetros
Partiremos para um exemplo bastante simples, apenas para apresentar o conceito. Será criado um esquema chamado MARCIO e nele criaremos a tabela EMP do SCOTT. Após, faremos a coleta de estatística - a FGA funciona corretamente somente em CBO - e criaremos a política, mais abaixo estarão vários selects com bind variable sem bind, insert e update e logo o resultado na DBA_FGA_AUDIT_TRAIL.
Vou discutir hoje, uma auditoria built in, o FGA (Fine-grained Audit) espécie de controle de acesso granular. Através da DBMS_FGA podemos cadastrar políticas para determinada tabela e ver o resultado na DBA_FGA_AUDIT_TRAIL.
Primeiro vamos entender a DBMS_FGA e o subprograma que adiciona política a ADD_POLICY.
DBMS_FGA.ADD_POLICY(
object_schema VARCHAR2,
object_name VARCHAR2,
policy_name VARCHAR2,
audit_condition VARCHAR2,
audit_column VARCHAR2,
handler_schema VARCHAR2,
handler_module VARCHAR2,
enable BOOLEAN,
statement_types VARCHAR2,
audit_trail BINARY_INTEGER IN DEFAULT,
audit_column_opts BINARY_INTEGER IN DEFAULT);
Parâmetros
object_schema => Esquema do objeto.
Valor default NULL. Se null ele assume o schema corrente
object_name => Nome do objeto.
policy_name => Nome único da política
audit_condition => Indica uma condicional para a auditoria, exemplo: auditar
as queries que possuem, na sua cláusula where "sal > 1000".
NULL é o default.
audit_column => Coluna que será checada por acesso, NULL é default e causará
cheque em todas as colunas acessadas.
handler_schema => Esquema onde está o event handler. Um event hander, pode ser
escrito para tomar alguma decisão quando o audit_condition
for verdadeiro.
handler_module => É o módulo (programa) que gerencia a auditoria, geralmente usado
como alerta, exemplo, uma procedure que envia e-mail a gerência
alertando sobre uma query contra a tabela de ações, por ai vai
conforme a necessidade do negócio. O handler_module deve possuir
a seguinte interface:
PROCEDURE(
object_schema VARCHAR2,
object_name VARCHAR2,
policy_name VARCHAR2 ) AS ...
Onde fname é o nome da procedure, object_schema é o nome do
esquema, object_name nome da tabela que está auditada e
policy_name nome da política.
enable => true/false - true a política estará ativa. Default TRUE.
statement_types => Onde haverá auditoria. Valores aceitos: insert, update,
delete ou select. Podemos utilizar todos separados por vírgula.
Default: SELECT
audit_trail => Determina se será gravado ou não o sql bind e o sql text na
sys.fga_log$. O default é para que seja populado DB_EXTENDED
caso contrário, use DB.
audit_column_opts => Determina se a sentença SQL será audita quando ela acessa
QUALQUER (ANY) ou TODAS (ALL) as colunas. Default ANY_COLUMNS.
Partiremos para um exemplo bastante simples, apenas para apresentar o conceito. Será criado um esquema chamado MARCIO e nele criaremos a tabela EMP do SCOTT. Após, faremos a coleta de estatística - a FGA funciona corretamente somente em CBO - e criaremos a política, mais abaixo estarão vários selects com bind variable sem bind, insert e update e logo o resultado na DBA_FGA_AUDIT_TRAIL.
ops$marcio@ORA10G> connect / as sysdba
Connected.
sys@ORA10G> truncate table fga_log$;
Table truncated.
sys@ORA10G> create user marcio identified by marcio default tablespace users;
User created.
sys@ORA10G>
sys@ORA10G> grant connect, resource to marcio;
Grant succeeded.
sys@ORA10G> grant execute on dbms_lock to marcio;
Grant succeeded.
sys@ORA10G> create table marcio.emp as select * from scott.emp;
Table created.
sys@ORA10G>
sys@ORA10G> exec dbms_stats.gather_table_stats( 'MARCIO', 'EMP', cascade => true);
PL/SQL procedure successfully completed.
sys@ORA10G>
sys@ORA10G> begin
2 dbms_fga.add_policy
3 ( object_schema => 'MARCIO',
4 policy_name => 'marcio_fga',
5 object_name => 'EMP',
6 statement_types => 'insert, select, update'
7 );
8 end;
9 /
PL/SQL procedure successfully completed.
sys@ORA10G>
sys@ORA10G> exec ops$marcio.print_table('select * from dba_audit_policies');
OBJECT_SCHEMA : MARCIO
OBJECT_NAME : EMP
POLICY_NAME : MARCIO_FGA
POLICY_TEXT :
POLICY_COLUMN :
PF_SCHEMA :
PF_PACKAGE :
PF_FUNCTION :
ENABLED : YES
SEL : YES
INS : YES
UPD : YES
DEL : NO
AUDIT_TRAIL : DB_EXTENDED
POLICY_COLUMN_OPTIONS : ANY_COLUMNS
-----------------
PL/SQL procedure successfully completed.
sys@ORA10G>
sys@ORA10G> connect marcio/marcio
Connected.
marcio@ORA10G>
marcio@ORA10G> var x number
marcio@ORA10G> var z varchar2(10)
marcio@ORA10G>
marcio@ORA10G> exec :x := 7900
PL/SQL procedure successfully completed.
X
-------------
7900
marcio@ORA10G> exec :z := 'JAMES'
PL/SQL procedure successfully completed.
Z
--------------------------------
JAMES
marcio@ORA10G>
marcio@ORA10G> select * from emp;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
------------- ---------- --------- ------------- ------------------- ------------- ------------- -------------
7369 SMITH CLERK 7902 17/12/1980 00:00:00 800 20
7499 ALLEN SALESMAN 7698 20/02/1981 00:00:00 1600 300 30
7521 WARD SALESMAN 7698 22/02/1981 00:00:00 1250 500 30
7566 JONES MANAGER 7839 02/04/1981 00:00:00 2975 20
7654 MARTIN SALESMAN 7698 28/09/1981 00:00:00 1250 1400 30
7698 BLAKE MANAGER 7839 01/05/1981 00:00:00 2850 30
7782 CLARK MANAGER 7839 09/06/1981 00:00:00 2450 10
7788 SCOTT ANALYST 7566 19/04/0087 00:00:00 3000 20
7839 KING PRESIDENT 17/11/1981 00:00:00 5000 10
7844 TURNER SALESMAN 7698 08/09/1981 00:00:00 1500 0 30
7876 ADAMS CLERK 7788 23/05/0087 00:00:00 1100 20
7900 JAMES CLERK 7698 03/12/1981 00:00:00 950 30
7902 FORD ANALYST 7566 03/12/1981 00:00:00 3000 20
7934 MILLER CLERK 7782 23/01/1982 00:00:00 1300 10
14 rows selected.
marcio@ORA10G> exec dbms_lock.sleep(5);
PL/SQL procedure successfully completed.
marcio@ORA10G>
marcio@ORA10G> select * from emp where empno = :x and ename = :z;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
------------- ---------- --------- ------------- ------------------- ------------- ------------- -------------
7900 JAMES CLERK 7698 03/12/1981 00:00:00 950 30
1 row selected.
marcio@ORA10G> exec :x := 7782
PL/SQL procedure successfully completed.
X
-------------
7782
marcio@ORA10G> /
no rows selected
marcio@ORA10G> insert into emp values ( 100, 'MARCIO','DBA', 7698, '15/08/2004 00:00:00', 1950, NULL, 30);
1 row created.
marcio@ORA10G> commit;
Commit complete.
marcio@ORA10G> exec :x := 100
PL/SQL procedure successfully completed.
X
-------------
100
marcio@ORA10G> select * from emp where empno = :x;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
------------- ---------- --------- ------------- ------------------- ------------- ------------- -------------
100 MARCIO DBA 7698 15/08/2004 00:00:00 1950 30
1 row selected.
marcio@ORA10G> update emp
2 set sal = 10000
3 where empno = :x;
1 row updated.
marcio@ORA10G>
marcio@ORA10G> connect /
Connected.
ops$marcio@ORA10G>
ops$marcio@ORA10G> column sql_text format a40 word_wrap
ops$marcio@ORA10G> column os_user format a10
ops$marcio@ORA10G> column db_user format a10
ops$marcio@ORA10G> column sql_bind format a25
ops$marcio@ORA10G>
ops$marcio@ORA10G> select timestamp, db_user, os_user, sql_text, sql_bind
2 from dba_fga_audit_trail
3 order by timestamp desc
4 /
TIMESTAMP DB_USER OS_USER SQL_TEXT SQL_BIND
------------------- ---------- ---------- ---------------------------------------- -------------------------
31/05/2005 00:58:35 MARCIO marcio select * from emp where empno = :x and #1(4):7782 #2(5):JAMES
ename = :z
31/05/2005 00:58:35 MARCIO marcio insert into emp values ( 100,
'MARCIO','DBA', 7698, '15/08/2004
00:00:00', 1950, NULL, 30)
31/05/2005 00:58:35 MARCIO marcio select * from emp where empno = :x #1(3):100
31/05/2005 00:58:35 MARCIO marcio update emp #1(3):100
set sal = 10000
where empno = :x
31/05/2005 00:58:34 MARCIO marcio select * from emp where empno = :x and #1(4):7900 #2(5):JAMES
ename = :z
31/05/2005 00:58:29 MARCIO marcio select * from emp
6 rows selected.
ops$marcio@ORA10G>
Labels: New Feature
