{"id":5667,"date":"2021-06-30T08:24:50","date_gmt":"2021-06-30T08:24:50","guid":{"rendered":"https:\/\/swiv.com.br\/recovering-a-primary-missing-datafile-from-the-standby-database\/"},"modified":"2026-05-27T20:02:30","modified_gmt":"2026-05-27T19:02:30","slug":"recovering-a-primary-missing-datafile-from-the-standby-database","status":"publish","type":"post","link":"https:\/\/swiv.com.br\/index.php\/2021\/06\/30\/recovering-a-primary-missing-datafile-from-the-standby-database\/","title":{"rendered":"Recovering a (primary) missing datafile from the standby database"},"content":{"rendered":"\n

Neste artigo, vamos reproduzir um recurso interessante, de podermos recuperar um datafile perdido no banco Primary a partir do banco Standby, economizando assim o tempo para normalizar o ambiente.<\/p>\n\n\n\n

Observando o nosso Data Guard de laborat\u00f3rio:<\/p>\n\n\n

\n[oracle@fornix1 ~]$ dgmgrl sys\/oracle@CORTEX as sysdba\nDGMGRL for Linux: Release 19.0.0.0.0 - Production on Wed Jun 30 05:00:32 2021\nVersion 19.3.0.0.0\n \nCopyright (c) 1982, 2019, Oracle and\/or its affiliates.  All rights reserved.\n \nWelcome to DGMGRL, type "help" for information.\nConnected to "cortex"\nConnected as SYSDBA.\nDGMGRL> SHOW CONFIGURATION;\n \nConfiguration - cortex\n \n  Protection Mode: MaxPerformance\n  Members:\n  cortex   - Primary database\n    cortexdr - Physical standby database\n \nFast-Start Failover:  Disabled\n \nConfiguration Status:\nSUCCESS   (status updated 50 seconds ago)\n \nDGMGRL> SHOW DATABASE CORTEX;\n \nDatabase - cortex\n \n  Role:               PRIMARY\n  Intended State:     TRANSPORT-ON\n  Instance(s):\n    cortex\n \nDatabase Status:\nSUCCESS\n \nDGMGRL> SHOW DATABASE CORTEXDR;\n \nDatabase - cortexdr\n \n  Role:               PHYSICAL STANDBY\n  Intended State:     APPLY-ON\n  Transport Lag:      0 seconds (computed 0 seconds ago)\n  Apply Lag:          0 seconds (computed 0 seconds ago)\n  Average Apply Rate: 64.00 KByte\/s\n  Real Time Query:    ON\n  Instance(s):\n    CORTEXDR\n \nDatabase Status:\nSUCCESS\n \nDGMGRL>\n<\/pre><\/div>\n\n\n
No banco de dados Primary, vou criar uma nova tablespace chamada BSS, e gerar alguns archivelogs:<\/p>\n\n\n
\n[oracle@fornix1 ~]$ sqlplus \/ as sysdba\n \nSQL*Plus: Release 19.0.0.0.0 - Production on Wed Jun 30 05:01:38 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> CREATE TABLESPACE BSS;\n \nTablespace created.\n \nSQL> ALTER SYSTEM SWITCH LOGFILE;\n \nSystem altered.\n \nSQL> \/\n \nSystem altered.\n \nSQL>\n<\/pre><\/div>\n\n\n
Verificando o nome do datafile criado na tablespace em quest\u00e3o:<\/p>\n\n\n
\nSQL> select name from v$datafile where upper(name) like upper('%BSS%');\n \nNAME\n--------------------------------------------------------------------------------\n+DG_DATA\/CORTEX\/DATAFILE\/bss.269.1076562109\n<\/pre><\/div>\n\n\n
Colocando o datafile como offline no Primary Database:<\/p>\n\n\n
\nSQL> ALTER DATABASE DATAFILE '+DG_DATA\/CORTEX\/DATAFILE\/bss.269.1076562109' OFFLINE;\n \nDatabase altered.\n<\/pre><\/div>\n\n\n
Deletando o datafile no primary:<\/p>\n\n\n
\n[oracle@fornix1 ~]$ su - grid\nPassword:\nLast login: Wed Jun 30 04:47:52 -03 2021\n[grid@fornix1 ~]$ asmcmd\nASMCMD> cd +DG_DATA\/CORTEX\/DATAFILE\/\nASMCMD> cp bss.269.1076562109 bss.269.1076562109.BACKUP\ncopying +DG_DATA\/CORTEX\/DATAFILE\/bss.269.1076562109 -> +DG_DATA\/CORTEX\/DATAFILE\/bss.269.1076562109.BACKUP\nASMCMD> rm bss.269.1076562109\nASMCMD>\n<\/pre><\/div>\n\n\n
A parte legal \u00e9 que, caso tenhamos nosso ambiente Data Guard (Physical Standby) sincronizado, podemos utiliz\u00e1-lo para recuperar este nosso datafile perdido no primary. Basta conectarmos no primary e emitir o comando de exemplo abaixo:<\/p>\n\n\n
\n[oracle@fornix1 ~]$ rman target sys\/oracle@CORTEX catalog CAT\/CAT@CORTEX\n \nRecovery Manager: Release 19.0.0.0.0 - Production on Wed Jun 30 05:12:38 2021\nVersion 19.3.0.0.0\n \nCopyright (c) 1982, 2019, Oracle and\/or its affiliates.  All rights reserved.\n \nconnected to target database: CORTEX (DBID=548968087)\nconnected to recovery catalog database\n \nRMAN> RESTORE DATAFILE '+DG_DATA\/CORTEX\/DATAFILE\/bss.269.1076562109' FROM SERVICE CORTEXDR;\n \nStarting restore at 30-JUN-21\nstarting full resync of recovery catalog\nfull resync complete\nallocated channel: ORA_DISK_1\nchannel ORA_DISK_1: SID=71 device type=DISK\n \nchannel ORA_DISK_1: starting datafile backup set restore\nchannel ORA_DISK_1: using network backup set from service CORTEXDR\nchannel ORA_DISK_1: specifying datafile(s) to restore from backup set\nchannel ORA_DISK_1: restoring datafile 00002 to +DG_DATA\/CORTEX\/DATAFILE\/bss.269.1076562109\nchannel ORA_DISK_1: restore complete, elapsed time: 00:00:01\nFinished restore at 30-JUN-21\nstarting full resync of recovery catalog\nfull resync complete\n \nRMAN>\n<\/pre><\/div>\n\n\n
Devido o OMF, o nome do datafile novo \u00e9 diferente, e desse modo podemos pegar este nome conforme abaixo, para procedermos com o seu recover:<\/p>\n\n\n
\nRMAN> RECOVER DATAFILE '+DG_DATA\/CORTEX\/DATAFILE\/bss.269.1076562799';\n \nStarting recover at 30-JUN-21\nusing channel ORA_DISK_1\n \nstarting media recovery\nmedia recovery complete, elapsed time: 00:00:01\n \nFinished recover at 30-JUN-21\n<\/pre><\/div>\n\n\n
Deixando o datafile ONLINE novamente:<\/p>\n\n\n
\n[oracle@fornix1 ~]$ sqlplus \/ as sysdba\n \nSQL*Plus: Release 19.0.0.0.0 - Production on Wed Jun 30 05:23:57 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 DATAFILE '+DG_DATA\/CORTEX\/DATAFILE\/bss.269.1076562799' ONLINE;\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":"
Neste artigo, vamos reproduzir um recurso interessante, de podermos recuperar um datafile perdido no banco Primary a partir do banco Standby, economizando assim o tempo para normalizar o ambiente. Observando o nosso Data Guard de laborat\u00f3rio: No banco de dados Primary, vou criar uma nova tablespace chamada BSS, e gerar alguns archivelogs: Verificando o nome […]<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"closed","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[3,5],"tags":[],"class_list":["post-5667","post","type-post","status-publish","format-standard","hentry","category-backup-recovery","category-high-availability"],"_links":{"self":[{"href":"https:\/\/swiv.com.br\/index.php\/wp-json\/wp\/v2\/posts\/5667","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=5667"}],"version-history":[{"count":1,"href":"https:\/\/swiv.com.br\/index.php\/wp-json\/wp\/v2\/posts\/5667\/revisions"}],"predecessor-version":[{"id":9088,"href":"https:\/\/swiv.com.br\/index.php\/wp-json\/wp\/v2\/posts\/5667\/revisions\/9088"}],"wp:attachment":[{"href":"https:\/\/swiv.com.br\/index.php\/wp-json\/wp\/v2\/media?parent=5667"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/swiv.com.br\/index.php\/wp-json\/wp\/v2\/categories?post=5667"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/swiv.com.br\/index.php\/wp-json\/wp\/v2\/tags?post=5667"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}