Taking Backup of the Oracle Control File

Backup & Recovery / 14/07/2022

Checando o parâmetro persistente do RMAN sobre o controlfile autobackup:

[oracle@oel8 ~]$ rman target /
 
Recovery Manager: Release 18.0.0.0.0 - Production on Mon Mar 15 04:52:32 2021
Version 18.3.0.0.0
 
Copyright (c) 1982, 2018, Oracle and/or its affiliates.  All rights reserved.
 
connected to target database: RMANDB (DBID=3825250984)
 
RMAN> SHOW CONTROLFILE AUTOBACKUP;
 
using target database control file instead of recovery catalog
RMAN configuration parameters for database with db_unique_name RMANDB are:
CONFIGURE CONTROLFILE AUTOBACKUP ON; # default
 
RMAN>

Esta configuração permite que o RMAN faça o backup do mesmo, quando uma operação de backup for disparada, como no exemplo abaixo:

RMAN> BACKUP TABLESPACE USERS;
 
Starting backup at 2021-03-15:04:55:43
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=91 device type=DISK
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00007 name=/oracle/dados/RMANDB/datafile/o1_mf_users_h8nyrkn7_.dbf
channel ORA_DISK_1: starting piece 1 at 2021-03-15:04:55:44
channel ORA_DISK_1: finished piece 1 at 2021-03-15:04:55:45
piece handle=/oracle/fra/RMANDB/backupset/2021_03_15/o1_mf_nnndf_TAG20210315T045544_j4y4r0x8_.bkp tag=TAG20210315T045544 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
Finished backup at 2021-03-15:04:55:45
 
Starting Control File and SPFILE Autobackup at 2021-03-15:04:55:45
piece handle=/oracle/fra/RMANDB/autobackup/2021_03_15/o1_mf_s_1067230546_j4y4r39t_.bkp comment=NONE
Finished Control File and SPFILE Autobackup at 2021-03-15:04:55:49

Consultando detalhes do backup realizado do control file:

RMAN> LIST BACKUP OF CONTROLFILE;
 
 
List of Backup Sets
===================
 
 
BS Key  Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ -------------------
26      Full    10.20M     DISK        00:00:01     2021-03-15:04:55:47
        BP Key: 26   Status: AVAILABLE  Compressed: NO  Tag: TAG20210315T045546
        Piece Name: /oracle/fra/RMANDB/autobackup/2021_03_15/o1_mf_s_1067230546_j4y4r39t_.bkp
  Control File Included: Ckp SCN: 1811359      Ckp time: 2021-03-15:04:55:46

Outra maneira de realizar o backup do control file é, de maneira explícita, citá-lo no comando, conforme exemplo abaixo:

RMAN> BACKUP TABLESPACE USERS INCLUDE CURRENT CONTROLFILE;
 
Starting backup at 2021-03-15:04:57:46
using channel ORA_DISK_1
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
including current control file in backup set
channel ORA_DISK_1: starting piece 1 at 2021-03-15:04:57:47
channel ORA_DISK_1: finished piece 1 at 2021-03-15:04:57:48
piece handle=/oracle/fra/RMANDB/backupset/2021_03_15/o1_mf_ncnnf_TAG20210315T045746_j4y4vvo0_.bkp tag=TAG20210315T045746 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00007 name=/oracle/dados/RMANDB/datafile/o1_mf_users_h8nyrkn7_.dbf
channel ORA_DISK_1: starting piece 1 at 2021-03-15:04:57:48
channel ORA_DISK_1: finished piece 1 at 2021-03-15:04:57:50
piece handle=/oracle/fra/RMANDB/backupset/2021_03_15/o1_mf_nnndf_TAG20210315T045746_j4y4vx8z_.bkp tag=TAG20210315T045746 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:02
Finished backup at 2021-03-15:04:57:50
 
Starting Control File and SPFILE Autobackup at 2021-03-15:04:57:50
piece handle=/oracle/fra/RMANDB/autobackup/2021_03_15/o1_mf_s_1067230670_j4y4vytm_.bkp comment=NONE
Finished Control File and SPFILE Autobackup at 2021-03-15:04:57:53

No script abaixo, realizamos o backup do control file e utilizamos o format para especificar o local que será utilizado para criar o backup piece:

RMAN> BACKUP CURRENT CONTROLFILE FORMAT '/tmp/control.bkp';
 
Starting backup at 2021-03-15:05:01:24
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=113 device type=DISK
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
including current control file in backup set
channel ORA_DISK_1: starting piece 1 at 2021-03-15:05:01:27
channel ORA_DISK_1: finished piece 1 at 2021-03-15:05:01:28
piece handle=/tmp/control.bkp tag=TAG20210315T050125 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
Finished backup at 2021-03-15:05:01:28
 
Starting Control File and SPFILE Autobackup at 2021-03-15:05:01:28
piece handle=/oracle/fra/RMANDB/autobackup/2021_03_15/o1_mf_s_1067230888_j4y52scl_.bkp comment=NONE
Finished Control File and SPFILE Autobackup at 2021-03-15:05:01:31
 
RMAN> host 'ls /tmp/control.bkp';
 
/tmp/control.bkp
host command complete

Utilizando a opção AS COPY, que cria um arquivo de backup exatamente igual ao arquivo e origem (snapshot):

RMAN> BACKUP AS COPY CURRENT CONTROLFILE FORMAT '/tmp/control.ctl';
 
Starting backup at 2021-03-15:05:05:13
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=64 device type=DISK
channel ORA_DISK_1: starting datafile copy
copying current control file
output file name=/tmp/control.ctl tag=TAG20210315T050514 RECID=1 STAMP=1067231115
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:03
Finished backup at 2021-03-15:05:05:17
 
Starting Control File and SPFILE Autobackup at 2021-03-15:05:05:17
piece handle=/oracle/fra/RMANDB/autobackup/2021_03_15/o1_mf_s_1067231117_j4y59y8p_.bkp comment=NONE
Finished Control File and SPFILE Autobackup at 2021-03-15:05:05:18
 
RMAN> host 'ls /tmp/control.ctl';
 
/tmp/control.ctl
host command complete

Por falar no snapshot do controlfile, esse mecanismo é usado pelo Oracle para garantir a consistência do control file nas operações de backup. Desse modo, podemos checar onde este snapshot está criado com o comando abaixo:

RMAN> SHOW SNAPSHOT CONTROLFILE NAME;
 
RMAN configuration parameters for database with db_unique_name RMANDB are:
CONFIGURE SNAPSHOT CONTROLFILE NAME TO '/oracle/18.0.0/product/dbs/snapcf_RMANDB.f'; # default

Em ambientes RAC, o ideal seria apontar o snapshot para um disco compartilhado por todas as instâncias, ou Disk Group, utilizando o comando abaixo de exemplo:

RMAN> CONFIGURE SNAPSHOT CONTROLFILE NAME TO '/tmp/snapcf_RMANDB.f';
 
new RMAN configuration parameters:
CONFIGURE SNAPSHOT CONTROLFILE NAME TO '/tmp/snapcf_RMANDB.f';
new RMAN configuration parameters are successfully stored
 
RMAN> SHOW SNAPSHOT CONTROLFILE NAME;
 
RMAN configuration parameters for database with db_unique_name RMANDB are:
CONFIGURE SNAPSHOT CONTROLFILE NAME TO '/tmp/snapcf_RMANDB.f';
 
RMAN> CONFIGURE SNAPSHOT CONTROLFILE NAME CLEAR;
 
old RMAN configuration parameters:
CONFIGURE SNAPSHOT CONTROLFILE NAME TO '/tmp/snapcf_RMANDB.f';
RMAN configuration parameters are successfully reset to default value
 
RMAN> SHOW SNAPSHOT CONTROLFILE NAME;
 
RMAN configuration parameters for database with db_unique_name RMANDB are:
CONFIGURE SNAPSHOT CONTROLFILE NAME TO '/oracle/18.0.0/product/dbs/snapcf_RMANDB.f'; # default

Vendo o detalhe de todos os backups de control file (backupset e image copy):

RMAN> LIST BACKUP OF CONTROLFILE;
 
 
List of Backup Sets
===================
 
 
BS Key  Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ -------------------
26      Full    10.20M     DISK        00:00:01     2021-03-15:04:55:47
        BP Key: 26   Status: AVAILABLE  Compressed: NO  Tag: TAG20210315T045546
        Piece Name: /oracle/fra/RMANDB/autobackup/2021_03_15/o1_mf_s_1067230546_j4y4r39t_.bkp
  Control File Included: Ckp SCN: 1811359      Ckp time: 2021-03-15:04:55:46
 
BS Key  Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ -------------------
27      Full    10.17M     DISK        00:00:01     2021-03-15:04:57:47
        BP Key: 27   Status: AVAILABLE  Compressed: NO  Tag: TAG20210315T045746
        Piece Name: /oracle/fra/RMANDB/backupset/2021_03_15/o1_mf_ncnnf_TAG20210315T045746_j4y4vvo0_.bkp
  Control File Included: Ckp SCN: 1811585      Ckp time: 2021-03-15:04:57:46
 
BS Key  Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ -------------------
29      Full    10.20M     DISK        00:00:00     2021-03-15:04:57:50
        BP Key: 29   Status: AVAILABLE  Compressed: NO  Tag: TAG20210315T045750
        Piece Name: /oracle/fra/RMANDB/autobackup/2021_03_15/o1_mf_s_1067230670_j4y4vytm_.bkp
  Control File Included: Ckp SCN: 1811595      Ckp time: 2021-03-15:04:57:50
 
BS Key  Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ -------------------
30      Full    10.17M     DISK        00:00:02     2021-03-15:05:01:27
        BP Key: 30   Status: AVAILABLE  Compressed: NO  Tag: TAG20210315T050125
        Piece Name: /tmp/control.bkp
  Control File Included: Ckp SCN: 1811709      Ckp time: 2021-03-15:05:01:25
 
BS Key  Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ -------------------
31      Full    10.20M     DISK        00:00:01     2021-03-15:05:01:29
        BP Key: 31   Status: AVAILABLE  Compressed: NO  Tag: TAG20210315T050128
        Piece Name: /oracle/fra/RMANDB/autobackup/2021_03_15/o1_mf_s_1067230888_j4y52scl_.bkp
  Control File Included: Ckp SCN: 1811718      Ckp time: 2021-03-15:05:01:28
 
BS Key  Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ -------------------
32      Full    10.20M     DISK        00:00:01     2021-03-15:05:05:18
        BP Key: 32   Status: AVAILABLE  Compressed: NO  Tag: TAG20210315T050517
        Piece Name: /oracle/fra/RMANDB/autobackup/2021_03_15/o1_mf_s_1067231117_j4y59y8p_.bkp
  Control File Included: Ckp SCN: 1811950      Ckp time: 2021-03-15:05:05:17
 
RMAN> LIST COPY OF CONTROLFILE;
 
List of Control File Copies
===========================
 
Key     S Completion Time     Ckp SCN    Ckp Time
------- - ------------------- ---------- -------------------
1       A 2021-03-15:05:05:15 1811941    2021-03-15:05:05:14
        Name: /tmp/control.ctl
        Tag: TAG20210315T050514

Deletando os arquivos de backup:

RMAN> DELETE BACKUPSET OF CONTROLFILE;
 
using channel ORA_DISK_1
 
List of Backup Pieces
BP Key  BS Key  Pc# Cp# Status      Device Type Piece Name
------- ------- --- --- ----------- ----------- ----------
26      26      1   1   AVAILABLE   DISK        /oracle/fra/RMANDB/autobackup/2021_03_15/o1_mf_s_1067230546_j4y4r39t_.bkp
27      27      1   1   AVAILABLE   DISK        /oracle/fra/RMANDB/backupset/2021_03_15/o1_mf_ncnnf_TAG20210315T045746_j4y4vvo0_.bkp
29      29      1   1   AVAILABLE   DISK        /oracle/fra/RMANDB/autobackup/2021_03_15/o1_mf_s_1067230670_j4y4vytm_.bkp
30      30      1   1   AVAILABLE   DISK        /tmp/control.bkp
31      31      1   1   AVAILABLE   DISK        /oracle/fra/RMANDB/autobackup/2021_03_15/o1_mf_s_1067230888_j4y52scl_.bkp
32      32      1   1   AVAILABLE   DISK        /oracle/fra/RMANDB/autobackup/2021_03_15/o1_mf_s_1067231117_j4y59y8p_.bkp
 
Do you really want to delete the above objects (enter YES or NO)? YES
deleted backup piece
backup piece handle=/oracle/fra/RMANDB/autobackup/2021_03_15/o1_mf_s_1067230546_j4y4r39t_.bkp RECID=26 STAMP=1067230547
deleted backup piece
backup piece handle=/oracle/fra/RMANDB/backupset/2021_03_15/o1_mf_ncnnf_TAG20210315T045746_j4y4vvo0_.bkp RECID=27 STAMP=1067230667
deleted backup piece
backup piece handle=/oracle/fra/RMANDB/autobackup/2021_03_15/o1_mf_s_1067230670_j4y4vytm_.bkp RECID=29 STAMP=1067230670
deleted backup piece
backup piece handle=/tmp/control.bkp RECID=30 STAMP=1067230887
deleted backup piece
backup piece handle=/oracle/fra/RMANDB/autobackup/2021_03_15/o1_mf_s_1067230888_j4y52scl_.bkp RECID=31 STAMP=1067230889
deleted backup piece
backup piece handle=/oracle/fra/RMANDB/autobackup/2021_03_15/o1_mf_s_1067231117_j4y59y8p_.bkp RECID=32 STAMP=1067231118
Deleted 6 objects
 
 
RMAN> DELETE COPY OF CONTROLFILE;
 
released channel: ORA_DISK_1
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=64 device type=DISK
List of Control File Copies
===========================
 
Key     S Completion Time     Ckp SCN    Ckp Time
------- - ------------------- ---------- -------------------
1       A 2021-03-15:05:05:15 1811941    2021-03-15:05:05:14
        Name: /tmp/control.ctl
        Tag: TAG20210315T050514
 
 
Do you really want to delete the above objects (enter YES or NO)? YES
deleted control file copy
control file copy file name=/tmp/control.ctl RECID=1 STAMP=1067231115
Deleted 1 objects

Uma última maneira de se ter condição de recuperar um control file, seria gerá-lo em formato SQL utilizando o comando abaixo:

[oracle@oel8 ~]$ sqlplus / as sysdba
 
SQL*Plus: Release 18.0.0.0.0 - Production on Mon Mar 15 05:17:11 2021
Version 18.3.0.0.0
 
Copyright (c) 1982, 2018, Oracle.  All rights reserved.
 
 
Connected to:
Oracle Database 18c Enterprise Edition Release 18.0.0.0.0 - Production
Version 18.3.0.0.0
 
SQL> ALTER DATABASE BACKUP CONTROLFILE TO TRACE;
 
Database altered.

No alert temos a indicação que um arquivo trace foi gerado:

ALTER DATABASE BACKUP CONTROLFILE TO TRACE
Backup controlfile written to trace file /oracle/18.0.0/base/diag/rdbms/rmandb/RMANDB/trace/RMANDB_ora_9426.trc
Completed: ALTER DATABASE BACKUP CONTROLFILE TO TRACE

[oracle@oel8 ~]$ cat /oracle/18.0.0/base/diag/rdbms/rmandb/RMANDB/trace/RMANDB_ora_9426.trc
Trace file /oracle/18.0.0/base/diag/rdbms/rmandb/RMANDB/trace/RMANDB_ora_9426.trc
Oracle Database 18c Enterprise Edition Release 18.0.0.0.0 - Production
Version 18.3.0.0.0
Build label:    RDBMS_18.1.0.0.0_LINUX.X64_180103.1
ORACLE_HOME:    /oracle/18.0.0/product
System name:    Linux
Node name:      oel8.localdomain
Release:        4.18.0-147.el8.x86_64
Version:        #1 SMP Tue Nov 12 11:05:49 PST 2019
Machine:        x86_64
Instance name: RMANDB
Redo thread mounted by this instance: 1
Oracle process number: 34
Unix process pid: 9426, image: oracle@oel8.localdomain (TNS V1-V3)
 
 
*** 2021-03-15T05:17:26.300266-03:00
*** SESSION ID:(64.54520) 2021-03-15T05:17:26.300290-03:00
*** CLIENT ID:() 2021-03-15T05:17:26.300297-03:00
*** SERVICE NAME:(SYS$USERS) 2021-03-15T05:17:26.300302-03:00
*** MODULE NAME:(sqlplus@oel8.localdomain (TNS V1-V3)) 2021-03-15T05:17:26.300308-03:00
*** ACTION NAME:() 2021-03-15T05:17:26.300313-03:00
*** CLIENT DRIVER:(SQL*PLUS) 2021-03-15T05:17:26.300318-03:00
 
...
 
STARTUP NOMOUNT
CREATE CONTROLFILE REUSE DATABASE "RMANDB" NORESETLOGS  ARCHIVELOG
    MAXLOGFILES 16
    MAXLOGMEMBERS 3
    MAXDATAFILES 100
    MAXINSTANCES 8
    MAXLOGHISTORY 292
LOGFILE
  GROUP 1 (
    '/oracle/dados/RMANDB/onlinelog/o1_mf_1_h8nytyh4_.log',
    '/oracle/fra/RMANDB/onlinelog/o1_mf_1_h8nytznd_.log'
  ) SIZE 50M BLOCKSIZE 512,
  GROUP 2 (
    '/oracle/dados/RMANDB/onlinelog/o1_mf_2_h8nytykj_.log',
    '/oracle/fra/RMANDB/onlinelog/o1_mf_2_h8nytzlh_.log'
  ) SIZE 50M BLOCKSIZE 512,
  GROUP 3 (
    '/oracle/dados/RMANDB/onlinelog/o1_mf_3_h8nyv1ok_.log',
    '/oracle/fra/RMANDB/onlinelog/o1_mf_3_h8nyv1y3_.log'
  ) SIZE 50M BLOCKSIZE 512
-- STANDBY LOGFILE
DATAFILE
  '/oracle/dados/RMANDB/datafile/o1_mf_system_h8nynqfx_.dbf',
  '/oracle/dados/RMANDB/datafile/o1_mf_sysaux_h8nyq35q_.dbf',
  '/oracle/dados/RMANDB/datafile/o1_mf_undotbs1_h8nyrjdr_.dbf',
  '/oracle/dados/RMANDB/datafile/ts_cortex_catalog.dbf',
  '/oracle/dados/RMANDB/datafile/o1_mf_users_h8nyrkn7_.dbf'
CHARACTER SET AL32UTF8
;
...

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.

Leave a Comment

Your email address will not be published.

pt Portuguese
en Englishpt Portuguesees Spanish