{"id":2613,"date":"2021-03-14T08:13:12","date_gmt":"2021-03-14T08:13:12","guid":{"rendered":"https:\/\/swiv.com.br\/creating-a-logical-standby-database\/"},"modified":"2026-05-27T20:02:51","modified_gmt":"2026-05-27T19:02:51","slug":"creating-a-logical-standby-database","status":"publish","type":"post","link":"https:\/\/swiv.com.br\/index.php\/2021\/03\/14\/creating-a-logical-standby-database\/","title":{"rendered":"Creating a Logical Standby Database"},"content":{"rendered":"

Como um dos pr\u00e9-requisitos do Logical Standby \u00e9 cri\u00e1-lo a partir do Physical Standby, fiz um clone das m\u00e1quinas que utilizei NESTE<\/a> artigo, para investir melhor o tempo.<\/p>\n\n\n

Confirmando que o primary est\u00e1 dispon\u00edvel:<\/p>\n\n\n

\n[oracle@fornix1 ~]$ srvctl status database -d cortex\nDatabase is running.\n<\/pre><\/div>\n\n\n
Checando o (at\u00e9 ent\u00e3o) Physical Standby:<\/p>\n\n\n
\n[oracle@fornix2 ~]$ srvctl status database -d cortexdr\nDatabase is running.\n<\/pre><\/div>\n\n\n
Habilitando replica\u00e7\u00e3o entre os bancos:<\/p>\n\n\n
\n[oracle@fornix2 ~]$ sqlplus \/ as sysdba\n \nSQL*Plus: Release 19.0.0.0.0 - Production on Sat Mar 13 15:53:09 2021\nVersion 19.3.0.0.0\n \nCopyright (c) 1982, 2019, Oracle.  All rights reserved.\n \n \nConnected to:\nOracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production\nVersion 19.3.0.0.0\n \nSQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT;\n \nDatabase altered.\n<\/pre><\/div>\n\n\n
O Logical Standby possui algumas limita\u00e7\u00f5es de replica\u00e7\u00e3o que devem ser mapeadas antes de efetivamente ser criado. No banco Primary, \u00e9 poss\u00edvel acessar a lista de objetos que n\u00e3o sejam suportados. No meu caso, como \u00e9 um banco de laborat\u00f3rio, n\u00e3o h\u00e1 nada efetivamente rodando nele, e consequentemente nenhum objeto que n\u00e3o seja suportado:<\/p>\n\n\n
\n[oracle@fornix1 ~]$ sqlplus \/ as sysdba\n \nSQL*Plus: Release 19.0.0.0.0 - Production on Sat Mar 13 15:56:21 2021\nVersion 19.3.0.0.0\n \nCopyright (c) 1982, 2019, Oracle.  All rights reserved.\n \n \nConnected to:\nOracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production\nVersion 19.3.0.0.0\n \nSQL> set pagesize 25\nSQL> column owner format a5\nSQL> SELECT DISTINCT OWNER, TABLE_NAME FROM DBA_LOGSTDBY_UNSUPPORTED ORDER BY OWNER,TABLE_NAME;\n \nno rows selected\n<\/pre><\/div>\n\n\n
Criando uma tabela de teste:<\/p>\n\n\n
\nSQL> CREATE TABLE SOE.BSS (COLUNA BFILE);\n \nTable created.\n<\/pre><\/div>\n\n\n
Realizando consulta novamente:<\/p>\n\n\n
\nSQL> set pagesize 25\nSQL> column owner format a5\nSQL> SELECT DISTINCT OWNER, TABLE_NAME FROM DBA_LOGSTDBY_UNSUPPORTED ORDER BY OWNER,TABLE_NAME;\n \nOWNER\n-----\nTABLE_NAME\n--------------------------------------------------------------------------------\nSOE\nBSS\n<\/pre><\/div>\n\n\n
Neste caso, podemos ver mais detalhes sobre a limita\u00e7\u00e3o, utilizando o comando abaixo:<\/p>\n\n\n
\nSQL> SELECT COLUMN_NAME,DATA_TYPE FROM DBA_LOGSTDBY_UNSUPPORTED WHERE OWNER='SOE' AND TABLE_NAME= 'BSS';\n \nCOLUMN_NAME\n--------------------------------------------------------------------------------\nDATA_TYPE\n--------------------------------------------------------------------------------\nCOLUNA\nBFILE\n<\/pre><\/div>\n\n\n
O script abaixo tamb\u00e9m nos reporta as tabelas que possuem alguma limita\u00e7\u00f5es por datatype de colunas:<\/p>\n\n\n
\nSQL> SELECT * FROM LOGSTDBY_UNSUPPORTED_TABLES;\n \nOWNER\n-----\nTABLE_NAME\n--------------------------------------------------------------------------------\nSOE\nBSS\n<\/pre><\/div>\n\n\n
Outra caracter\u00edstica do Logical Standby, \u00e9 que o mesmo n\u00e3o suporta schemas classificados como internos, vide exemplo abaixo:<\/p>\n\n\n
\nSQL> SET PAGES 100\nSQL> column owner format a25\nSQL> SELECT OWNER FROM DBA_LOGSTDBY_SKIP WHERE STATEMENT_OPT = 'INTERNAL SCHEMA' ORDER BY 1;\n \nOWNER\n-------------------------\nANONYMOUS\nAPPQOSSYS\nAUDSYS\nCTXSYS\nDBSFWUSER\nDBSNMP\nDIP\nDVF\nDVSYS\nGGSYS\nGSMADMIN_INTERNAL\nGSMCATUSER\nGSMROOTUSER\nGSMUSER\nLBACSYS\nMDSYS\nOJVMSYS\nOLAPSYS\nORACLE_OCM\nORDDATA\nORDPLUGINS\nORDSYS\nOUTLN\nREMOTE_SCHEDULER_AGENT\nSI_INFORMTN_SCHEMA\nSYS\nSYS$UMF\nSYSBACKUP\nSYSDG\nSYSKM\nSYSRAC\nSYSTEM\nWMSYS\nXDB\nXS$NULL\n \n35 rows selected.\n<\/pre><\/div>\n\n\n
Tabelas que n\u00e3o tenham identificador \u00fanico tamb\u00e9m n\u00e3o s\u00e3o suportadas pelo Logical, e podem ser encontradas na consulta abaixo:<\/p>\n\n\n
\nSQL> COL OWNER FORMAT A30\nSQL> COL TABLE_NAME FORMAT A80\nSQL> SELECT OWNER, TABLE_NAME,BAD_COLUMN FROM DBA_LOGSTDBY_NOT_UNIQUE ;\n \nOWNER                          TABLE_NAME                                                                       B\n------------------------------ -------------------------------------------------------------------------------- -\nSOE                            WAREHOUSES                                                                       N\nSOE                            LOGON                                                                            N\nSOE                            PRODUCT_DESCRIPTIONS                                                             N\nSOE                            ORDERENTRY_METADATA                                                              N\n<\/pre><\/div>\n\n\n
Todos os objetos que s\u00e3o expostos nas consultas dos passos acima devem ser tratados e avaliados, para que a cria\u00e7\u00e3o do Logical Standby seja saud\u00e1vel, e logicamente, n\u00e3o trazer problemas futuro ao ambiente. Agora vamos efetivamente \u00e0 constru\u00e7\u00e3o do Logical:<\/p>\n\n\n\n
Parando o Redo Apply no Standby:<\/p>\n\n\n
\nSQL> ALTER SYSTEM SWITCH LOGFILE;\n \nSystem altered.\n \nSQL> !hostname\nfornix1\n<\/pre><\/div>\n\n
\nSQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;\n \nDatabase altered.\n \nSQL> !hostname\nfornix2\n<\/pre><\/div>\n\n\n
No ambiente primary, precisamos rodar o comando abaixo para a cria\u00e7\u00e3o do LogMiner Dictionary:<\/p>\n\n\n
\nSQL> execute DBMS_LOGSTDBY.BUILD;\n \nPL\/SQL procedure successfully completed.\n<\/pre><\/div>\n\n\n
\u00c9 recomandado tamb\u00e9m aumentar a reten\u00e7\u00e3o de UNDO em ambos ambientes:<\/p>\n\n\n
\nSQL> SELECT INSTANCE_NAME,STATUS FROM V$INSTANCE;\n \nINSTANCE_NAME    STATUS\n---------------- ------------\ncortex           OPEN\n \nSQL> show parameter UNDO_RETENTION\n \nNAME                                 TYPE        VALUE\n------------------------------------ ----------- ------------------------------\nundo_retention                       integer     900\nSQL> alter system set UNDO_RETENTION=3600 scope=both;\n \nSystem altered.\n<\/pre><\/div>\n\n
\nSQL> SELECT INSTANCE_NAME,STATUS FROM V$INSTANCE;\n \nINSTANCE_NAME    STATUS\n---------------- ------------\nCORTEXDR         MOUNTED\n \nSQL> show parameter UNDO_RETENTION\n \nNAME                                 TYPE        VALUE\n------------------------------------ ----------- ------------------------------\nundo_retention                       integer     900\nSQL> alter system set UNDO_RETENTION=3600 scope=both;\n \nSystem altered.\n<\/pre><\/div>\n\n\n
Convertendo o Physical Standby para Logical Standby:<\/p>\n\n\n
\nSQL> SHU IMMEDIATE;\nORA-01109: database not open\n \n \nDatabase dismounted.\nORACLE instance shut down.\nSQL> STARTUP MOUNT EXCLUSIVE;\nORACLE instance started.\n \nTotal System Global Area 2583690520 bytes\nFixed Size                  8899864 bytes\nVariable Size             553648128 bytes\nDatabase Buffers         2013265920 bytes\nRedo Buffers                7876608 bytes\nDatabase mounted.\nSQL> ALTER DATABASE RECOVER TO LOGICAL STANDBY CORTEXDR;\n \nDatabase altered.\n<\/pre><\/div>\n\n\n
Baixando o standby e abrindo-o com resetlogs, al\u00e9m de iniciar o servi\u00e7o de SQL apply:<\/p>\n\n\n
\nSQL> SHU IMMEDIATE;\nORA-01507: database not mounted\n\n\nORACLE instance shut down.\nSQL> STARTUP MOUNT;\nORACLE instance started.\n\nTotal System Global Area 2583690520 bytes\nFixed Size                  8899864 bytes\nVariable Size             553648128 bytes\nDatabase Buffers         2013265920 bytes\nRedo Buffers                7876608 bytes\nDatabase mounted.\nSQL> ALTER DATABASE OPEN RESETLOGS;\n\nDatabase altered.\n\nSQL> ALTER DATABASE START LOGICAL STANDBY APPLY IMMEDIATE;\n\nDatabase altered.\n<\/pre><\/div>\n\n\n
Validando os status dos Online Redo Logs no standby:<\/p>\n\n\n
\nSQL> SELECT THREAD#, SEQUENCE#, ARCHIVED, STATUS FROM V$LOG;\n \n   THREAD#  SEQUENCE# ARC STATUS\n---------- ---------- --- ----------------\n         1          1 NO  CURRENT\n         1          0 YES UNUSED\n         1          0 YES UNUSED\n<\/pre><\/div>\n\n\n
Para validar a replica\u00e7\u00e3o, vou criar uma tabela nova com 1 registro no primary:<\/p>\n\n\n
\nSQL> CREATE TABLE SOE.BSS1 (NOME VARCHAR2(50));\n \nTable created.\n \nSQL> ALTER TABLE SOE.BSS1 ADD PRIMARY KEY (NOME);\n \nSQL> INSERT INTO SOE.BSS1 (NOME) VALUES ('BRUNO');\n \n1 row created.\n \nSQL> COMMIT;\n \nCommit complete.\n \nSQL> ALTER SYSTEM SWITCH LOGFILE;\n \nSystem altered.\n<\/pre><\/div>\n\n\n
Checando o Logical Standby:<\/p>\n\n\n
\nSQL> DESC SOE.BSS1;\n Name                                      Null?    Type\n ----------------------------------------- -------- ----------------------------\n NOME                                      NOT NULL VARCHAR2(50)\n \nSQL> SELECT * FROM SOE.BSS1;\n \nNOME\n--------------------------------------------------\nBRUNO\n<\/pre><\/div>\n\n\n
Por \u00faltimo, precisamos ajustar o atributo VALID_FOR dos 2 bancos, para que j\u00e1 fiquem configurados quando um switchover ocorrer.<\/p>\n\n\n\n
Criando diret\u00f3rio de destino dos archived redo log files do standby redo logs:<\/p>\n\n\n
\nASMCMD> pwd\n+DG_RECO\/CORTEX\/ARCHIVELOG\nASMCMD> !hostname\nfornix1\nASMCMD> mkdir ARCHREDOLOG\nASMCMD> ls ARCHREDOLOG\n<\/pre><\/div>\n\n\n
Alterando par\u00e2metros do primary:<\/p>\n\n\n
\n[oracle@fornix1 ~]$ sqlplus \/ as sysdba\n \nSQL*Plus: Release 19.0.0.0.0 - Production on Sun Mar 14 04:54:44 2021\nVersion 19.3.0.0.0\n \nCopyright (c) 1982, 2019, Oracle.  All rights reserved.\n \n \nConnected to:\nOracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production\nVersion 19.3.0.0.0\n \nSQL> show parameter LOG_ARCHIVE_DEST_1\n \nNAME                                 TYPE        VALUE\n------------------------------------ ----------- ------------------------------\nlog_archive_dest_1                   string      LOCATION=+DG_RECO\nlog_archive_dest_10                  string\nlog_archive_dest_11                  string\nlog_archive_dest_12                  string\nlog_archive_dest_13                  string\nlog_archive_dest_14                  string\nlog_archive_dest_15                  string\nlog_archive_dest_16                  string\nlog_archive_dest_17                  string\nlog_archive_dest_18                  string\nlog_archive_dest_19                  string\nSQL> ALTER SYSTEM SET LOG_ARCHIVE_DEST_1='LOCATION=+DG_RECO VALID_FOR=( ONLINE_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=CORTEX' scope=both;\n \nSystem altered.\n \nSQL> show parameter LOG_ARCHIVE_DEST_2\n \nNAME                                 TYPE        VALUE\n------------------------------------ ----------- ------------------------------\nlog_archive_dest_2                   string      SERVICE=cortexDR ASYNC VALID_F\n                                                 OR=(ONLINE_LOGFILES,PRIMARY_RO\n                                                 LE) DB_UNIQUE_NAME=cortexDR\nlog_archive_dest_20                  string\nlog_archive_dest_21                  string\nlog_archive_dest_22                  string\nlog_archive_dest_23                  string\nlog_archive_dest_24                  string\nlog_archive_dest_25                  string\nlog_archive_dest_26                  string\nlog_archive_dest_27                  string\n \nNAME                                 TYPE        VALUE\n------------------------------------ ----------- ------------------------------\nlog_archive_dest_28                  string\nlog_archive_dest_29                  string\nSQL> ALTER SYSTEM SET LOG_ARCHIVE_DEST_3='LOCATION=+DG_RECO\/CORTEX\/ARCHIVELOG\/ARCHREDOLOG\/ VALID_FOR=(STANDBY_LOGFILES,STANDBY_ROLE) DB_UNIQUE_NAME=CORTEX' scope=both;\n \nSystem altered.\n \nSQL> ALTER SYSTEM SET LOG_ARCHIVE_DEST_STATE_3=ENABLE;\n \nSystem altered.\n<\/pre><\/div>\n\n\n
Criando diret\u00f3rio no Standby:<\/p>\n\n\n
\nASMCMD> !hostname\nfornix2\nASMCMD> pwd\n+DG_RECO\/CORTEXDR\/ARCHIVELOG\nASMCMD> mkdir ARCHREDOLOG\n<\/pre><\/div>\n\n\n
Alterando par\u00e2metros no standby:<\/p>\n\n\n
\n[oracle@fornix2 ~]$ sqlplus \/ as sysdba\n \nSQL*Plus: Release 19.0.0.0.0 - Production on Sun Mar 14 05:04:25 2021\nVersion 19.3.0.0.0\n \nCopyright (c) 1982, 2019, Oracle.  All rights reserved.\n \n \nConnected to:\nOracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production\nVersion 19.3.0.0.0\n \nSQL> show parameter LOG_ARCHIVE_DEST_1;\n \nNAME                                 TYPE        VALUE\n------------------------------------ ----------- ------------------------------\nlog_archive_dest_1                   string      LOCATION=+DG_RECO\nlog_archive_dest_10                  string\nlog_archive_dest_11                  string\nlog_archive_dest_12                  string\nlog_archive_dest_13                  string\nlog_archive_dest_14                  string\nlog_archive_dest_15                  string\nlog_archive_dest_16                  string\nlog_archive_dest_17                  string\nlog_archive_dest_18                  string\nlog_archive_dest_19                  string\nSQL> ALTER SYSTEM SET LOG_ARCHIVE_DEST_1='LOCATION=+DG_RECO VALID_FOR=( ONLINE_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=CORTEXDR' scope=both;\n \nSystem altered.\n \nSQL> show parameter LOG_ARCHIVE_DEST_2;\n \nNAME                                 TYPE        VALUE\n------------------------------------ ----------- ------------------------------\nlog_archive_dest_2                   string      SERVICE=CORTEX ASYNC DB_UNIQUE\n                                                 _NAME=cortex VALID_FOR=(ONLINE\n                                                 _LOGFILE,PRIMARY_ROLE)\nlog_archive_dest_20                  string\nlog_archive_dest_21                  string\nlog_archive_dest_22                  string\nlog_archive_dest_23                  string\nlog_archive_dest_24                  string\nlog_archive_dest_25                  string\nlog_archive_dest_26                  string\nlog_archive_dest_27                  string\n \nNAME                                 TYPE        VALUE\n------------------------------------ ----------- ------------------------------\nlog_archive_dest_28                  string\nlog_archive_dest_29                  string\nSQL> ALTER SYSTEM SET LOG_ARCHIVE_DEST_2= 'SERVICE=CORTEX ASYNC NOAFFIRM delay=0 reopen=300 VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=CORTEX' SCOPE=BOTH ;\n \nSystem altered.\n \nSQL> ALTER SYSTEM SET LOG_ARCHIVE_DEST_STATE_2=ENABLE;\n \nSystem altered.\n \nSQL> ALTER SYSTEM SET LOG_ARCHIVE_DEST_3='LOCATION=+DG_RECO\/CORTEXDR\/ARCHIVELOG\/ARCHREDOLOG\/ VALID_FOR=(STANDBY_LOGFILES,STANDBY_ROLE) DB_UNIQUE_NAME=CORTEXDR' scope=both;\n \nSystem altered.\n \nSQL> ALTER SYSTEM SET LOG_ARCHIVE_DEST_STATE_3=ENABLE;\n \nSystem altered.\n \nSQL>\n<\/pre><\/div>\n\n\n
E finalmente, caso queiramos parar o servi\u00e7o de replica\u00e7\u00e3o, basta rodar o comando abaixo no standby:<\/p>\n\n\n
\nSQL> ALTER DATABASE STOP LOGICAL STANDBY APPLY;\n \nDatabase altered.\n<\/pre><\/div>\n\n\n
Obs: Este procedimento foi criado pelo senhor Ahmed Baraka (www.ahmedbaraka.com) e foi apenas reproduzido por mim em um laborat\u00f3rio pessoal para fins de aprendizado.<\/p>\n","protected":false},"excerpt":{"rendered":"
Como um dos pr\u00e9-requisitos do Logical Standby \u00e9 cri\u00e1-lo a partir do Physical Standby, fiz um clone das m\u00e1quinas que utilizei NESTE artigo, para investir melhor o tempo. Confirmando que o primary est\u00e1 dispon\u00edvel: Checando o (at\u00e9 ent\u00e3o) Physical Standby: Habilitando replica\u00e7\u00e3o entre os bancos: O Logical Standby possui algumas limita\u00e7\u00f5es de replica\u00e7\u00e3o que devem […]<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"closed","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[5],"tags":[],"class_list":["post-2613","post","type-post","status-publish","format-standard","hentry","category-high-availability"],"_links":{"self":[{"href":"https:\/\/swiv.com.br\/index.php\/wp-json\/wp\/v2\/posts\/2613","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/swiv.com.br\/index.php\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/swiv.com.br\/index.php\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/swiv.com.br\/index.php\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/swiv.com.br\/index.php\/wp-json\/wp\/v2\/comments?post=2613"}],"version-history":[{"count":1,"href":"https:\/\/swiv.com.br\/index.php\/wp-json\/wp\/v2\/posts\/2613\/revisions"}],"predecessor-version":[{"id":9235,"href":"https:\/\/swiv.com.br\/index.php\/wp-json\/wp\/v2\/posts\/2613\/revisions\/9235"}],"wp:attachment":[{"href":"https:\/\/swiv.com.br\/index.php\/wp-json\/wp\/v2\/media?parent=2613"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/swiv.com.br\/index.php\/wp-json\/wp\/v2\/categories?post=2613"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/swiv.com.br\/index.php\/wp-json\/wp\/v2\/tags?post=2613"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}