{"id":6179,"date":"2021-08-16T23:52:52","date_gmt":"2021-08-16T23:52:52","guid":{"rendered":"https:\/\/swiv.com.br\/restoring-the-spfile-from-autobackup\/"},"modified":"2021-08-16T23:52:52","modified_gmt":"2021-08-16T23:52:52","slug":"restoring-the-spfile-from-autobackup","status":"publish","type":"post","link":"https:\/\/swiv.com.br\/index.php\/2021\/08\/16\/restoring-the-spfile-from-autobackup\/","title":{"rendered":"Restoring the SPFILE from AUTOBACKUP"},"content":{"rendered":"\n

Neste artigo, vamos simular a recupera\u00e7\u00e3o de um SPFILE perdido, onde o banco de dados est\u00e1 indispon\u00edvel no momento da recupera\u00e7\u00e3o. Assumimos que a base esteja rodando em ARCHIVELOG mode, e que o recurso de AUTOBACKUP esteja configurado.<\/p>\n\n\n\n

Reconhecendo nosso ambiente de laborat\u00f3rio:<\/p>\n\n\n

\n[oracle@oel8 ~]$ rman target \/\n \nRecovery Manager: Release 18.0.0.0.0 - Production on Mon Aug 16 20:28:31 2021\nVersion 18.13.0.0.0\n \nCopyright (c) 1982, 2018, Oracle and\/or its affiliates.  All rights reserved.\n \nconnected to target database: RMANDB (DBID=3825250984)\n \nRMAN> SELECT NAME,OPEN_MODE,LOG_MODE FROM V$DATABASE;\n \nusing target database control file instead of recovery catalog\nNAME      OPEN_MODE            LOG_MODE\n--------- -------------------- ------------\nRMANDB    READ WRITE           ARCHIVELOG\n<\/pre><\/div>\n\n\n
Realizando um backup full do banco de dados:<\/p>\n\n\n
\nRMAN> BACKUP DATABASE;\n \nStarting backup at 2021-08-16:20:31:41\nusing channel ORA_DISK_1\nchannel ORA_DISK_1: starting full datafile backup set\nchannel ORA_DISK_1: specifying datafile(s) in backup set\ninput datafile file number=00001 name=\/oracle\/dados\/RMANDB\/datafile\/o1_mf_system_h8nynqfx_.dbf\ninput datafile file number=00003 name=\/oracle\/dados\/RMANDB\/datafile\/o1_mf_sysaux_h8nyq35q_.dbf\ninput datafile file number=00004 name=\/oracle\/dados\/RMANDB\/datafile\/o1_mf_undotbs1_h8nyrjdr_.dbf\ninput datafile file number=00005 name=\/oracle\/dados\/RMANDB\/datafile\/ts_cortex_catalog.dbf\ninput datafile file number=00002 name=\/oracle\/dados\/RMANDB\/datafile\/ts_hipo_catalog.dbf\ninput datafile file number=00007 name=\/oracle\/dados\/RMANDB\/datafile\/o1_mf_users_h8nyrkn7_.dbf\nchannel ORA_DISK_1: starting piece 1 at 2021-08-16:20:31:42\nchannel ORA_DISK_1: finished piece 1 at 2021-08-16:20:33:37\npiece handle=\/oracle\/fra\/RMANDB\/backupset\/2021_08_16\/o1_mf_nnndf_TAG20210816T203141_jkoxbyqr_.bkp tag=TAG20210816T203141 comment=NONE\nchannel ORA_DISK_1: backup set complete, elapsed time: 00:01:55\nFinished backup at 2021-08-16:20:33:37\n \nStarting Control File and SPFILE Autobackup at 2021-08-16:20:33:37\npiece handle=\/oracle\/fra\/RMANDB\/autobackup\/2021_08_16\/o1_mf_s_1080765217_jkoxgl0s_.bkp comment=NONE\nFinished Control File and SPFILE Autobackup at 2021-08-16:20:33:40\n<\/pre><\/div>\n\n\n
Para esse cen\u00e1rio de recupera\u00e7\u00e3o, usaremos 2 informa\u00e7\u00f5es que vamos coletar agora: o DBID (pois n\u00e3o estamos usando um cat\u00e1logo de backup) e o diret\u00f3rio onde reside a nossa FRA:<\/p>\n\n\n
\nSQL> SELECT DBID FROM V$DATABASE;\n \n      DBID\n----------\n3825250984\n \nSQL> SHOW PARAMETER DB_RECOVERY_FILE_DEST;\n \nNAME                                 TYPE        VALUE\n------------------------------------ ----------- ------------------------------\ndb_recovery_file_dest                string      \/oracle\/fra\ndb_recovery_file_dest_size           big integer 5G\n<\/pre><\/div>\n\n\n
Deletando o SPFILE:<\/p>\n\n\n
\nSQL> SHO PARAMETER SPFILE;\n \nNAME                                 TYPE        VALUE\n------------------------------------ ----------- ------------------------------\nspfile                               string      \/oracle\/18.0.0\/product\/dbs\/spf\n                                                 ileRMANDB.ora\nSQL> ! rm \/oracle\/18.0.0\/product\/dbs\/spfileRMANDB.ora\n<\/pre><\/div>\n\n\n
Baixando a nossa inst\u00e2ncia:<\/p>\n\n\n
\nSQL> SHU IMMEDIATE;\nDatabase closed.\nDatabase dismounted.\nORACLE instance shut down.\n<\/pre><\/div>\n\n\n
Ao tentar levantar o ambiente, o SPFILE n\u00e3o \u00e9 identificado e a opera\u00e7\u00e3o falha:<\/p>\n\n\n
\nSQL> STARTUP;\nORA-01078: failure in processing system parameters\nLRM-00109: could not open parameter file '\/oracle\/18.0.0\/product\/dbs\/initRMANDB.ora'\n<\/pre><\/div>\n\n
O processo de recupera\u00e7\u00e3o \u00e9 simples. Primeiro iniciamos uma inst\u00e2ncia com o comando abaixo, onde a cl\u00e1usula FORCE utiliza um “dummy parameter file”, para que possamos dar in\u00edcio ao restore que acontecer\u00e1 em seguida (esse detalhe podemos ver na documenta\u00e7\u00e3o, conforme este LINK<\/a>):<\/p>\n\n
\n[oracle@oel8 RMANDB]$ rman target \/\n \nRecovery Manager: Release 18.0.0.0.0 - Production on Mon Aug 16 20:43:39 2021\nVersion 18.13.0.0.0\n \nCopyright (c) 1982, 2018, Oracle and\/or its affiliates.  All rights reserved.\n \nconnected to target database (not started)\n \nRMAN> STARTUP FORCE NOMOUNT;\n \nstartup failed: ORA-01078: failure in processing system parameters\nLRM-00109: could not open parameter file '\/oracle\/18.0.0\/product\/dbs\/initRMANDB.ora'\n \nstarting Oracle instance without parameter file for retrieval of spfile\nOracle instance started\n \nTotal System Global Area    1073740616 bytes\n \nFixed Size                     8665928 bytes\nVariable Size                281018368 bytes\nDatabase Buffers             780140544 bytes\nRedo Buffers                   3915776 bytes\n<\/pre><\/div>\n\n\n
Conforme j\u00e1 citado, como n\u00e3o estamos usando um cat\u00e1logo de backup, precisaremos definir o DBID do nosso banco, al\u00e9m de passar o diret\u00f3rio da nossa FRA, conforme exemplo abaixo:<\/p>\n\n\n
\nRUN{\n    SET DBID 3825250984; \n    RESTORE SPFILE FROM AUTOBACKUP DB_RECOVERY_FILE_DEST='\/oracle\/fra' DB_NAME='RMANDB'; \n   }\n<\/pre><\/div>\n\n\n
Log de execu\u00e7\u00e3o:<\/p>\n\n\n
\nRMAN> RUN{\n        SET DBID 3825250984;\n        RESTORE SPFILE FROM AUTOBACKUP DB_RECOVERY_FILE_DEST='\/oracle\/fra' DB_NAME='RMANDB';\n   }2> 3> 4>\n \nexecuting command: SET DBID\n \nStarting restore at 2021-08-16:20:46:49\nusing channel ORA_DISK_1\n \nrecovery area destination: \/oracle\/fra\ndatabase name (or database unique name) used for search: RMANDB\nchannel ORA_DISK_1: AUTOBACKUP \/oracle\/fra\/RMANDB\/autobackup\/2021_08_16\/o1_mf_s_1080765217_jkoxgl0s_.bkp found in the recovery area\nchannel ORA_DISK_1: looking for AUTOBACKUP on day: 20210816\nchannel ORA_DISK_1: restoring spfile from AUTOBACKUP \/oracle\/fra\/RMANDB\/autobackup\/2021_08_16\/o1_mf_s_1080765217_jkoxgl0s_.bkp\nchannel ORA_DISK_1: SPFILE restore from AUTOBACKUP complete\nFinished restore at 2021-08-16:20:46:50\n<\/pre><\/div>\n\n\n
Agora basta baixarmos a inst\u00e2ncia e subir o ambiente:<\/p>\n\n\n
\nSQL> SHU IMMEDIATE;\nORA-01507: database not mounted\n \n \nORACLE instance shut down.\nSQL> STARTUP;\nORACLE instance started.\n \nTotal System Global Area 1610612016 bytes\nFixed Size                  8658224 bytes\nVariable Size             520093696 bytes\nDatabase Buffers         1073741824 bytes\nRedo Buffers                8118272 bytes\nDatabase mounted.\nDatabase opened.\nSQL> ! ls -lthr \/oracle\/18.0.0\/product\/dbs\/spfileRMANDB.ora\n-rw-r-----. 1 oracle oinstall 9.5K Aug 16 20:48 \/oracle\/18.0.0\/product\/dbs\/spfileRMANDB.ora\n<\/pre><\/div>\n\n\n
Apenas para termos conhecimento, caso nosso ambiente utilize cat\u00e1logo de backup, a recupera\u00e7\u00e3o do SPFILE \u00e9 feita apenas com o comando abaixo:<\/p>\n\n\n
\nRMAN> STARTUP FORCE NOMOUNT;\nRMAN> RESTORE SPFILE FROM AUTOBACKUP;\n<\/pre><\/div>\n\n\n
Al\u00e9m disso, caso precisemos restaurar o SPFILE para um nome ou diret\u00f3rio espec\u00edfico, podemos fazer conforme exemplo abaixo:<\/p>\n\n\n
\n[oracle@oel8 RMANDB]$ rman target \/\n \nRecovery Manager: Release 18.0.0.0.0 - Production on Mon Aug 16 20:51:51 2021\nVersion 18.13.0.0.0\n \nCopyright (c) 1982, 2018, Oracle and\/or its affiliates.  All rights reserved.\n \nconnected to target database: RMANDB (DBID=3825250984)\n \nRMAN> RESTORE SPFILE TO '\/tmp\/BSS.ora' FROM AUTOBACKUP;\n \nStarting restore at 2021-08-16:20:52:04\nusing target database control file instead of recovery catalog\nallocated channel: ORA_DISK_1\nchannel ORA_DISK_1: SID=68 device type=DISK\n \nrecovery area destination: \/oracle\/fra\ndatabase name (or database unique name) used for search: RMANDB\nchannel ORA_DISK_1: AUTOBACKUP \/oracle\/fra\/RMANDB\/autobackup\/2021_08_16\/o1_mf_s_1080765217_jkoxgl0s_.bkp found in the recovery area\nchannel ORA_DISK_1: looking for AUTOBACKUP on day: 20210816\nchannel ORA_DISK_1: restoring spfile from AUTOBACKUP \/oracle\/fra\/RMANDB\/autobackup\/2021_08_16\/o1_mf_s_1080765217_jkoxgl0s_.bkp\nchannel ORA_DISK_1: SPFILE restore from AUTOBACKUP complete\nFinished restore at 2021-08-16:20:52:06\n \nRMAN> host 'ls -lthr \/tmp\/BSS.ora';\n \n-rw-r-----. 1 oracle oinstall 9.5K Aug 16 20:52 \/tmp\/BSS.ora\nhost command complete\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 simular a recupera\u00e7\u00e3o de um SPFILE perdido, onde o banco de dados est\u00e1 indispon\u00edvel no momento da recupera\u00e7\u00e3o. Assumimos que a base esteja rodando em ARCHIVELOG mode, e que o recurso de AUTOBACKUP esteja configurado. Reconhecendo nosso ambiente de laborat\u00f3rio: Realizando um backup full do banco de dados: Para esse cen\u00e1rio de […]<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[3],"tags":[],"class_list":["post-6179","post","type-post","status-publish","format-standard","hentry","category-backup-recovery"],"_links":{"self":[{"href":"https:\/\/swiv.com.br\/index.php\/wp-json\/wp\/v2\/posts\/6179","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=6179"}],"version-history":[{"count":0,"href":"https:\/\/swiv.com.br\/index.php\/wp-json\/wp\/v2\/posts\/6179\/revisions"}],"wp:attachment":[{"href":"https:\/\/swiv.com.br\/index.php\/wp-json\/wp\/v2\/media?parent=6179"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/swiv.com.br\/index.php\/wp-json\/wp\/v2\/categories?post=6179"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/swiv.com.br\/index.php\/wp-json\/wp\/v2\/tags?post=6179"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}