Para relembrar, nosso Data Guard com Logical Standby replicará todos os objetos, exceto em 3 situações:
- Objetos de schemas internos (como SYS e SYSTEM): é possível checarmos essa lista usando a query abaixo no primary:
SQL> SELECT DISTINCT(OWNER) FROM DBA_LOGSTDBY_SKIP WHERE STATEMENT_OPT='INTERNAL SCHEMA';
OWNER
--------------------------------------------------------------------------------
APPQOSSYS
DBSNMP
GSMCATUSER
SYS
SYSTEM
XS$NULL
ANONYMOUS
DBSFWUSER
GGSYS
GSMUSER
REMOTE_SCHEDULER_AGENT
OWNER
--------------------------------------------------------------------------------
SYSBACKUP
CTXSYS
GSMROOTUSER
SYSRAC
AUDSYS
DVF
DVSYS
OJVMSYS
SI_INFORMTN_SCHEMA
DIP
GSMADMIN_INTERNAL
OWNER
--------------------------------------------------------------------------------
ORDPLUGINS
LBACSYS
MDSYS
OLAPSYS
ORDDATA
SYSKM
ORACLE_OCM
OUTLN
SYS$UMF
ORDSYS
SYSDG
OWNER
--------------------------------------------------------------------------------
WMSYS
XDB
35 rows selected.
- Objetos que possuam algum datatype incompatível (que podem ser coletados nas views DBA_LOGSTDBY_UNSUPPORTED e LOGSTDBY_UNSUPPORTED_TABLES):
SQL> SELECT OWNER,TABLE_NAME FROM DBA_LOGSTDBY_UNSUPPORTED;
OWNER
--------------------------------------------------------------------------------
TABLE_NAME
--------------------------------------------------------------------------------
SOE
BSS
SQL> SELECT * FROM LOGSTDBY_UNSUPPORTED_TABLES;
OWNER
--------------------------------------------------------------------------------
TABLE_NAME
--------------------------------------------------------------------------------
SOE
BSS
- Regras criadas de forma explícita pelo DBA, por variáveis motivos. É sobre essa terceita categoria que irei explorar neste artigo.
Para isso, vamos criar uma tabela de testes no ambiente primary, vide abaixo:
[oracle@fornix1 ~]$ sqlplus / as sysdba
SQL*Plus: Release 19.0.0.0.0 - Production on Fri Apr 2 04:47:12 2021
Version 19.3.0.0.0
Copyright (c) 1982, 2019, Oracle. All rights reserved.
Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0
SQL> CREATE TABLE SOE.MERCURIO ( ID NUMBER PRIMARY KEY, NOME VARCHAR2(30)) ;
Table created.
SQL> INSERT INTO SOE.MERCURIO VALUES (1, 'STRING A') ;
1 row created.
SQL> INSERT INTO SOE.MERCURIO VALUES (2, 'STRING B') ;
1 row created.
SQL> INSERT INTO SOE.MERCURIO VALUES (3, 'STRING C') ;
1 row created.
SQL> COMMIT;
Commit complete.
SQL> ALTER SYSTEM SWITCH LOGFILE;
System altered.
Confirmando que o SQL Apply já replicou o objeto para o Logical Standby:
[oracle@fornix2 trace]$ sqlplus / as sysdba
SQL*Plus: Release 19.0.0.0.0 - Production on Fri Apr 2 04:48:41 2021
Version 19.3.0.0.0
Copyright (c) 1982, 2019, Oracle. All rights reserved.
Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0
SQL> SELECT OBJECT_NAME, OBJECT_TYPE FROM DBA_OBJECTS WHERE OBJECT_NAME='MERCURIO';
OBJECT_NAME
--------------------------------------------------------------------------------
OBJECT_TYPE
-----------------------
MERCURIO
TABLE
Criando regra de Skipp desta tabela no Primary:
[oracle@fornix1 ~]$ sqlplus / as sysdba
SQL*Plus: Release 19.0.0.0.0 - Production on Fri Apr 2 04:53:53 2021
Version 19.3.0.0.0
Copyright (c) 1982, 2019, Oracle. All rights reserved.
Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0
SQL> exec DBMS_LOGSTDBY.SKIP(stmt=>'DML',schema_name=>'SOE',object_name =>'MERCURIO');
PL/SQL procedure successfully completed.
SQL> exec DBMS_LOGSTDBY.SKIP(stmt=>'SCHEMA_DDL',schema_name=>'SOE',object_name =>'MERCURIO');
PL/SQL procedure successfully completed.
Criando a regra também no Standby (realizando o stop e start do SQL Apply):
[oracle@fornix2 trace]$ sqlplus / as sysdba
SQL*Plus: Release 19.0.0.0.0 - Production on Fri Apr 2 04:54:48 2021
Version 19.3.0.0.0
Copyright (c) 1982, 2019, Oracle. All rights reserved.
Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0
SQL> ALTER DATABASE STOP LOGICAL STANDBY APPLY;
Database altered.
SQL> exec DBMS_LOGSTDBY.SKIP(stmt=>'DML',schema_name=>'SOE',object_name =>'MERCURIO');
PL/SQL procedure successfully completed.
SQL> exec DBMS_LOGSTDBY.SKIP(stmt=>'SCHEMA_DDL',schema_name=>'SOE',object_name =>'MERCURIO');
PL/SQL procedure successfully completed.
SQL> ALTER DATABASE START LOGICAL STANDBY APPLY IMMEDIATE;
Database altered.
Com a query abaixo é possível listarmos todos os objetos e regras que estão criados para obedecer o skipp:
SQL> select OWNER, STATEMENT_OPT , NAME, USE_LIKE, ESC from DBA_LOGSTDBY_SKIP WHERE OWNER='SOE';
OWNER
--------------------------------------------------------------------------------
STATEMENT_OPT
--------------------------------------------------------------------------------
NAME
--------------------------------------------------------------------------------
U E
- -
SOE
DML
MERCURIO
Y
OWNER
--------------------------------------------------------------------------------
STATEMENT_OPT
--------------------------------------------------------------------------------
NAME
--------------------------------------------------------------------------------
U E
- -
SOE
SCHEMA_DDL
MERCURIO
Y
Adicionando 2 registros na tabela no primary:
SQL> INSERT INTO SOE.MERCURIO VALUES (4, 'STRING D') ;
1 row created.
SQL> INSERT INTO SOE.MERCURIO VALUES (5, 'STRING E') ;
1 row created.
SQL> COMMIT;
Commit complete.
SQL> ALTER SYSTEM SWITCH LOGFILE;
System altered.
Importante salientar que a regra de Skipp não impede que os dados sejam emitidos do primary para o standby, mas sim que o Standby não aplique o SQL Apply que contempla a regra. Mesmo aguardando um tempo, percebemos que os 2 novos registros não são replicados ao standby (o que era esperado):
[oracle@fornix2 trace]$ sqlplus / as sysdba
SQL*Plus: Release 19.0.0.0.0 - Production on Fri Apr 2 05:01:25 2021
Version 19.3.0.0.0
Copyright (c) 1982, 2019, Oracle. All rights reserved.
Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0
SQL> select count(*) from SOE.MERCURIO;
COUNT(*)
----------
3
Como criamos regras também para operações de DDL nesta tabela, farei um truncate na mesma no primary:
[oracle@fornix1 ~]$ sqlplus / as sysdba
SQL*Plus: Release 19.0.0.0.0 - Production on Fri Apr 2 05:02:37 2021
Version 19.3.0.0.0
Copyright (c) 1982, 2019, Oracle. All rights reserved.
Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0
SQL> TRUNCATE TABLE SOE.MERCURIO;
Table truncated.
SQL> ALTER SYSTEM SWITCH LOGFILE;
System altered.
Esta operação também não foi replicada ao Standby:
[oracle@fornix2 trace]$ sqlplus / as sysdba
SQL*Plus: Release 19.0.0.0.0 - Production on Fri Apr 2 05:04:48 2021
Version 19.3.0.0.0
Copyright (c) 1982, 2019, Oracle. All rights reserved.
Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0
SQL> select count(*) from SOE.MERCURIO;
COUNT(*)
----------
3
O UN-SKIPP requer a criação de um DB_LINK no standby apontando para o primary, que permitirá o “instantiate” da tabela, que nada mais seria que a sua replicação total ao destino:
[oracle@fornix2 admin]$ sqlplus / as sysdba
SQL*Plus: Release 19.0.0.0.0 - Production on Fri Apr 2 05:10:05 2021
Version 19.3.0.0.0
Copyright (c) 1982, 2019, Oracle. All rights reserved.
Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0
SQL> CREATE PUBLIC DATABASE LINK BRUNO_LINK CONNECT TO system IDENTIFIED BY oracle USING 'CORTEX';
Database link created.
SQL> select sysdate from dual@BRUNO_LINK ;
SYSDATE
-------------------
2021-04-02:05:10:14
Removendo a regra no primary:
[oracle@fornix1 ~]$ sqlplus / as sysdba
SQL*Plus: Release 19.0.0.0.0 - Production on Fri Apr 2 05:11:37 2021
Version 19.3.0.0.0
Copyright (c) 1982, 2019, Oracle. All rights reserved.
Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0
SQL> exec DBMS_LOGSTDBY.UNSKIP(stmt=>'DML',schema_name=>'SOE',object_name =>'MERCURIO');
PL/SQL procedure successfully completed.
SQL> exec DBMS_LOGSTDBY.UNSKIP(stmt=>'SCHEMA_DDL',schema_name=>'SOE',object_name =>'MERCURIO');
PL/SQL procedure successfully completed.
Removendo a regra no Standby:
[oracle@fornix2 admin]$ sqlplus / as sysdba
SQL*Plus: Release 19.0.0.0.0 - Production on Fri Apr 2 05:12:15 2021
Version 19.3.0.0.0
Copyright (c) 1982, 2019, Oracle. All rights reserved.
Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0
SQL> ALTER DATABASE STOP LOGICAL STANDBY APPLY;
Database altered.
SQL> exec DBMS_LOGSTDBY.UNSKIP(stmt=>'DML',schema_name=>'SOE',object_name =>'MERCURIO');
PL/SQL procedure successfully completed.
SQL> exec DBMS_LOGSTDBY.UNSKIP(stmt=>'SCHEMA_DDL',schema_name=>'SOE',object_name =>'MERCURIO');
PL/SQL procedure successfully completed.
Como havia truncado a tabela na origem, vou adicionar algumas linhas no primary:
[oracle@fornix1 ~]$ sqlplus / as sysdba
SQL*Plus: Release 19.0.0.0.0 - Production on Fri Apr 2 05:13:32 2021
Version 19.3.0.0.0
Copyright (c) 1982, 2019, Oracle. All rights reserved.
Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0
SQL> INSERT INTO SOE.MERCURIO VALUES (1, 'STRING A') ;
INSERT INTO SOE.MERCURIO VALUES (2, 'STRING B') ;
INSERT INTO SOE.MERCURIO VALUES (3, 'STRING C') ;
1 row created.
SQL>
1 row created.
SQL>
1 row created.
SQL> COMMIT;
Commit complete.
No standby, realizando o instantiate da tabela:
[oracle@fornix2 admin]$ sqlplus / as sysdba
SQL*Plus: Release 19.0.0.0.0 - Production on Fri Apr 2 05:14:42 2021
Version 19.3.0.0.0
Copyright (c) 1982, 2019, Oracle. All rights reserved.
Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0
SQL> EXEC DBMS_LOGSTDBY.INSTANTIATE_TABLE (SCHEMA_NAME => 'SOE', TABLE_NAME => 'MERCURIO', DBLINK => 'BRUNO_LINK');
PL/SQL procedure successfully completed.
Inicializando o SQL Apply e confirmando que a tabela foi replicada com sucesso:
SQL> ALTER DATABASE START LOGICAL STANDBY APPLY IMMEDIATE;
Database altered.
SQL> SELECT COUNT(*) FROM SOE.MERCURIO;
COUNT(*)
----------
3
Obs: Este procedimento foi criado pelo senhor Ahmed Baraka (www.ahmedbaraka.com) e foi apenas reproduzido por mim em um laboratório pessoal para fins de aprendizado.
Portuguese
English
Spanish