Excluíndo um banco de dados a partir do RMAN

Se você tiver um ambiente em que você tem que apagar constantemente o bancos de dados, ou mesmo se você só deseja excluir um banco de dados, um modo mais segura de remover os arquivos correspondentes é usando o comando DROP DATABASE dentro do RMAN. Num post anterior, eu mostrei a mesma atividade que vamos ver aqui neste post, mas usando o sqlplus, caso queira visualizar click no link.

Se você estiver usando um ambiente em RAC, a primeira coisa que você tem a fazer é definir o parâmetro cluster_database como FALSE, depois de ter feito isso, faça o desligamento do banco de dados

alter system set cluster_database=FALSE scope=spfile;

$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.4.0 Production on Thu Jul 9 12:56:39 2015
Copyright (c) 1982, 2013, Oracle.  All rights reserved.

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 – 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> shutdown immediate;
ORA-01507: database not mounted
ORACLE instance shut down.
SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 – 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
[oracle@dg-rac dbs]$

Antes de acessar o RMAN, verifique se  as variáveis de  ambiente estão corretas, pois você não quer excuir o banco de dados errado. Após isto,  vamos abri o banco de dados no modo mount e definir uma sessão restrita.

1 – Entrar no RMAN

[oracle@dg-rac dbs]$ rman target /

Recovery Manager: Release 11.2.0.4.0 – Production on Thu Jul 9 12:58:03 2015
Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.
connected to target database (not started)

2 – Montar o banco de dados

RMAN> startup mount;

Oracle instance started
database mounted

Total System Global Area    1169149952 bytes

Fixed Size                     2252624 bytes
Variable Size                 889192624 bytes
Database Buffers          268435456 bytes
Redo Buffers                 9269248 bytes

RMAN>

3 – Colocar o banco de dados em Restrict Session

RMAN>  SQL ‘ALTER SYSTEM ENABLE RESTRICTED SESSION’;

using target database control file instead of recovery catalog
sql statement: ALTER SYSTEM ENABLE RESTRICTED SESSION

RMAN>

4 – Verificando os backups existentes da base de dados

RMAN> list backup of database;

List of Backup Sets
===================
BS Key  Type LV Size       Device Type Elapsed Time Completion Time
——- —- — ———- ———– ———— —————
1       Full    631.81M    DISK        00:02:36     12-MAY-15
BP Key: 1   Status: AVAILABLE  Compressed: NO  Tag: TAG20150512T095600
Piece Name: /u01/app/oracle/backup/rman/RAC_1_1_01q6o81h_1_1.bkp
List of Datafiles in backup set 1
File LV Type Ckp SCN    Ckp Time  Name
—- — —- ———- ——— —-
1       Full 1085852    12-MAY-15 /oradata/rac/datafiles/rac/datafile/system.256.869525059
4       Full 1085852    12-MAY-15 /oradata/rac/datafiles/rac/datafile/users.259.869525061

BS Key  Type LV Size       Device Type Elapsed Time Completion Time
——- —- — ———- ———– ———— —————
2       Full    450.62M    DISK        00:03:12     12-MAY-15
BP Key: 2   Status: AVAILABLE  Compressed: NO  Tag: TAG20150512T095600
Piece Name: /u01/app/oracle/backup/rman/RAC_2_1_02q6o81h_1_1.bkp
List of Datafiles in backup set 2
File LV Type Ckp SCN    Ckp Time  Name
—- — —- ———- ——— —-
2       Full 1085859    12-MAY-15 /oradata/rac/datafiles/rac/datafile/sysaux.257.869525061
3       Full 1085859    12-MAY-15 /oradata/rac/datafiles/rac/datafile/undotbs1.258.869525061
5       Full 1085859    12-MAY-15 /oradata/rac/datafiles/rac/datafile/undotbs2.264.869525285

BS Key  Type LV Size       Device Type Elapsed Time Completion Time
——- —- — ———- ———– ———— —————
3       Full    631.81M    DISK        00:01:04     12-MAY-15
BP Key: 3   Status: AVAILABLE  Compressed: NO  Tag: TAG20150512T100042
Piece Name: /u01/app/oracle/backup/rman/RAC_3_1_03q6o8aa_1_1.bkp
List of Datafiles in backup set 3
File LV Type Ckp SCN    Ckp Time  Name
—- — —- ———- ——— —-
1       Full 1086592    12-MAY-15 /oradata/rac/datafiles/rac/datafile/system.256.869525059
4       Full 1086592    12-MAY-15 /oradata/rac/datafiles/rac/datafile/users.259.869525061

BS Key  Type LV Size       Device Type Elapsed Time Completion Time
——- —- — ———- ———– ———— —————
4       Full    451.56M    DISK        00:01:55     12-MAY-15
BP Key: 4   Status: AVAILABLE  Compressed: NO  Tag: TAG20150512T100042
Piece Name: /u01/app/oracle/backup/rman/RAC_4_1_04q6o8ab_1_1.bkp
List of Datafiles in backup set 4
File LV Type Ckp SCN    Ckp Time  Name
—- — —- ———- ——— —-
2       Full 1086602    12-MAY-15 /oradata/rac/datafiles/rac/datafile/sysaux.257.869525061
3       Full 1086602    12-MAY-15 /oradata/rac/datafiles/rac/datafile/undotbs1.258.869525061
5       Full 1086602    12-MAY-15 /oradata/rac/datafiles/rac/datafile/undotbs2.264.869525285

RMAN>

5 – Excluíndo a base de dados

RMAN> DROP DATABASE INCLUDING BACKUPS;

database name is “RAC” and DBID is 2457507198

Do you really want to drop all backups and the database (enter YES or NO)? yes

allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=396 device type=DISK

List of Backup Pieces
BP Key  BS Key  Pc# Cp# Status      Device Type Piece Name
——- ——- — — ———– ———– ———-
1       1       1   1   AVAILABLE   DISK        /u01/app/oracle/backup/rman/RAC_1_1_01q6o81h_1_1.bkp
2       2       1   1   AVAILABLE   DISK        /u01/app/oracle/backup/rman/RAC_2_1_02q6o81h_1_1.bkp
3       3       1   1   AVAILABLE   DISK        /u01/app/oracle/backup/rman/RAC_3_1_03q6o8aa_1_1.bkp
4       4       1   1   AVAILABLE   DISK        /u01/app/oracle/backup/rman/RAC_4_1_04q6o8ab_1_1.bkp
5       5       1   1   AVAILABLE   DISK        /u01/app/oracle/backup/rman/RAC_5_1_05q6o8dc_1_1.bkp
6       6       1   1   AVAILABLE   DISK        /u01/app/oracle/backup/rman/RAC_6_1_06q6o8e7_1_1.bkp
7       7       1   1   AVAILABLE   DISK        /u01/app/oracle/backup/rman/ARCH_RAC_7_1_07q6o8eh_1_1.bkp
8       8       1   1   AVAILABLE   DISK        /u01/app/oracle/backup/rman/ARCH_RAC_8_1_08q6o8ei_1_1.bkp
9       9       1   1   AVAILABLE   DISK        /u01/app/oracle/backup/rman/control_RAC_09q6o8ep_1_1.bkp
deleted backup piece
backup piece handle=/u01/app/oracle/backup/rman/RAC_1_1_01q6o81h_1_1.bkp RECID=1 STAMP=879501361
deleted backup piece
backup piece handle=/u01/app/oracle/backup/rman/RAC_2_1_02q6o81h_1_1.bkp RECID=2 STAMP=879501395
deleted backup piece
backup piece handle=/u01/app/oracle/backup/rman/RAC_3_1_03q6o8aa_1_1.bkp RECID=3 STAMP=879501643
deleted backup piece
backup piece handle=/u01/app/oracle/backup/rman/RAC_4_1_04q6o8ab_1_1.bkp RECID=4 STAMP=879501706
deleted backup piece
backup piece handle=/u01/app/oracle/backup/rman/RAC_5_1_05q6o8dc_1_1.bkp RECID=5 STAMP=879501767
deleted backup piece
backup piece handle=/u01/app/oracle/backup/rman/RAC_6_1_06q6o8e7_1_1.bkp RECID=6 STAMP=879501769
deleted backup piece
backup piece handle=/u01/app/oracle/backup/rman/ARCH_RAC_7_1_07q6o8eh_1_1.bkp RECID=7 STAMP=879501778
deleted backup piece
backup piece handle=/u01/app/oracle/backup/rman/ARCH_RAC_8_1_08q6o8ei_1_1.bkp RECID=8 STAMP=879501779
deleted backup piece
backup piece handle=/u01/app/oracle/backup/rman/control_RAC_09q6o8ep_1_1.bkp RECID=9 STAMP=879501788
Deleted 9 objects

released channel: ORA_DISK_1
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=396 device type=DISK
specification does not match any datafile copy in the repository
List of Control File Copies
===========================
Key     S Completion Time Ckp SCN    Ckp Time
——- – ————— ———- —————
1       A 13-MAY-15       1170085    13-MAY-15
Name: /u01/app/oracle/product/11.2.0/db_1/dbs/snapcf_RAC_2.f
Tag: TAG20150513T090651

1       A 13-MAY-15       1170085    13-MAY-15
Name: /u01/app/oracle/product/11.2.0/db_1/dbs/snapcf_RAC_2.f
Tag: TAG20150513T090651

List of Archived Log Copies for database with db_unique_name RACDG
=====================================================================
Key     Thrd Seq     S Low Time
——- —- ——- – ———
1       1    35      A 13-MAY-15
Name: /oradata/rac/archives/RAC_1_35_869525190.arc
2       1    36      A 13-MAY-15
Name: /oradata/rac/archives/RAC_1_36_869525190.arc
3       1    37      A 13-MAY-15
Name: /oradata/rac/archives/RAC_1_37_869525190.arc
4       1    38      A 13-MAY-15
Name: /oradata/rac/archives/RAC_1_38_869525190.arc
5       1    39      A 13-MAY-15
Name: /oradata/rac/archives/RAC_1_39_869525190.arc
7       1    40      A 13-MAY-15
Name: /oradata/rac/archives/RAC_1_40_869525190.arc
9       1    41      A 13-MAY-15
Name: /oradata/rac/archives/RAC_1_41_869525190.arc
11      1    42      A 13-MAY-15
Name: /oradata/rac/archives/RAC_1_42_869525190.arc
13      1    43      A 13-MAY-15
Name: /oradata/rac/archives/RAC_1_43_869525190.arc
14      1    44      A 13-MAY-15
Name: /oradata/rac/archives/RAC_1_44_869525190.arc
15      1    45      A 13-MAY-15
Name: /oradata/rac/archives/RAC_1_45_869525190.arc
16      1    46      A 13-MAY-15
Name: /oradata/rac/archives/RAC_1_46_869525190.arc
17      1    47      A 13-MAY-15
Name: /oradata/rac/archives/RAC_1_47_869525190.arc
18      1    48      A 13-MAY-15
Name: /oradata/rac/archives/RAC_1_48_869525190.arc
19      1    49      A 13-MAY-15
Name: /oradata/rac/archives/RAC_1_49_869525190.arc
20      1    50      A 13-MAY-15
Name: /oradata/rac/archives/RAC_1_50_869525190.arc
21      1    51      A 13-MAY-15
Name: /oradata/rac/archives/RAC_1_51_869525190.arc
22      1    52      A 13-MAY-15
Name: /oradata/rac/archives/RAC_1_52_869525190.arc
26      1    53      A 13-MAY-15
Name: /oradata/rac/archives/RAC_1_53_869525190.arc
27      1    54      A 13-MAY-15
Name: /oradata/rac/archives/RAC_1_54_869525190.arc
29      1    1       A 14-MAY-15
Name: /oradata/rac/archives/RAC_1_1_879698524.arc
28      1    2       A 14-MAY-15
Name: /oradata/rac/archives/RAC_1_2_879698524.arc
30      1    3       A 14-MAY-15
Name: /oradata/rac/archives/RAC_1_3_879698524.arc
31      1    4       A 14-MAY-15
Name: /oradata/rac/archives/RAC_1_4_879698524.arc

deleted control file copy
control file copy file name=/u01/app/oracle/product/11.2.0/db_1/dbs/snapcf_RAC_2.f RECID=1 STAMP=879584821
deleted control file copy
control file copy file name=/u01/app/oracle/product/11.2.0/db_1/dbs/snapcf_RAC_2.f RECID=1 STAMP=879584821
deleted archived log
archived log file name=/oradata/rac/archives/RAC_1_35_869525190.arc RECID=1 STAMP=879594636
deleted archived log
archived log file name=/oradata/rac/archives/RAC_1_36_869525190.arc RECID=2 STAMP=879594962
deleted archived log
archived log file name=/oradata/rac/archives/RAC_1_37_869525190.arc RECID=3 STAMP=879609377
deleted archived log
archived log file name=/oradata/rac/archives/RAC_1_38_869525190.arc RECID=4 STAMP=879609485
deleted archived log
archived log file name=/oradata/rac/archives/RAC_1_39_869525190.arc RECID=5 STAMP=879609878
deleted archived log
archived log file name=/oradata/rac/archives/RAC_1_40_869525190.arc RECID=7 STAMP=879609882
deleted archived log
archived log file name=/oradata/rac/archives/RAC_1_41_869525190.arc RECID=9 STAMP=879610677
deleted archived log
archived log file name=/oradata/rac/archives/RAC_1_42_869525190.arc RECID=11 STAMP=879610764
deleted archived log
archived log file name=/oradata/rac/archives/RAC_1_43_869525190.arc RECID=13 STAMP=879610927
deleted archived log
archived log file name=/oradata/rac/archives/RAC_1_44_869525190.arc RECID=14 STAMP=879610930
deleted archived log
archived log file name=/oradata/rac/archives/RAC_1_45_869525190.arc RECID=15 STAMP=879611562
deleted archived log
archived log file name=/oradata/rac/archives/RAC_1_46_869525190.arc RECID=16 STAMP=879611594
deleted archived log
archived log file name=/oradata/rac/archives/RAC_1_47_869525190.arc RECID=17 STAMP=879612645
deleted archived log
archived log file name=/oradata/rac/archives/RAC_1_48_869525190.arc RECID=18 STAMP=879612648
deleted archived log
archived log file name=/oradata/rac/archives/RAC_1_49_869525190.arc RECID=19 STAMP=879612674
deleted archived log
archived log file name=/oradata/rac/archives/RAC_1_50_869525190.arc RECID=20 STAMP=879613517
deleted archived log
archived log file name=/oradata/rac/archives/RAC_1_51_869525190.arc RECID=21 STAMP=879613517
deleted archived log
archived log file name=/oradata/rac/archives/RAC_1_52_869525190.arc RECID=22 STAMP=879613639
deleted archived log
archived log file name=/oradata/rac/archives/RAC_1_53_869525190.arc RECID=26 STAMP=879698996
deleted archived log
archived log file name=/oradata/rac/archives/RAC_1_54_869525190.arc RECID=27 STAMP=879698997
deleted archived log
archived log file name=/oradata/rac/archives/RAC_1_1_879698524.arc RECID=29 STAMP=879698997
deleted archived log
archived log file name=/oradata/rac/archives/RAC_1_2_879698524.arc RECID=28 STAMP=879698997
deleted archived log
archived log file name=/oradata/rac/archives/RAC_1_3_879698524.arc RECID=30 STAMP=879698998
deleted archived log
archived log file name=/oradata/rac/archives/RAC_1_4_879698524.arc RECID=31 STAMP=879699002
Deleted 26 objects

database name is “RAC” and DBID is 2457507198
database dropped

RMAN>

Após a conclusão do comando, a base esta excluída fisicamente e seus registros removidos do catalogo rman.

Um abraço

Modificando Prompt do SQLPLUS

Quando conectamos ao sqlplus vemos o prompt padrão na tela:

$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.3.0 Production on Mon Jun 22 11:28:38 2015
Copyright (c) 1982, 2011, Oracle.  All rights reserved.

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 – Production
With the Partitioning, Automatic Storage Management, OLAP, Data Mining
and Real Application Testing options
SQL>

Este artigo mostra como você pode modificar o prompt no sqlplus usando o comando SET SQLPROMPT
e variáveis de usuário pré-definidas.

Para visualizar as  variáveis de usuário pré-definidas no Ambiente SQLPLUS , digite define no prompt do sqlplus.

SYS@orcl> define
DEFINE _DATE           = “22-JUN-15” (CHAR)
DEFINE _CONNECT_IDENTIFIER = “orcl” (CHAR)
DEFINE _USER           = “SYS” (CHAR)
DEFINE _PRIVILEGE      = “AS SYSDBA” (CHAR)
DEFINE _SQLPLUS_RELEASE = “1102000300” (CHAR)
DEFINE _EDITOR         = “ed” (CHAR)
DEFINE _O_VERSION      = “Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 – Production
With the Partitioning, Automatic Storage Management, OLAP, Data Mining
and Real Application Testing options” (CHAR)
DEFINE _O_RELEASE      = “1102000300” (CHAR)
SYS@orcl>

Configurando o prompt do SQL
SQL> set sqlprompt “HOMOLOGACAO>”
HOMOLOGACAO>

OBS: Quando a sessão for desconectada, a variável prompt voltara ao valor default. Para torná-la permanente o arquivo abaixo devera ser editado:

vi $ORACLE_HOME/sqlplus/admin/glogin.sql

exemplo:

estou adicionando as linhas abaixo:

set verify off
set term off
set sqlprompt “HOMOLOGACAO >”
set verify on
set term on

Depois de salvar o arquivo, saio e abro novamente uma sessão no sqlplus

SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 – Production
With the Partitioning, Automatic Storage Management, OLAP, Data Mining
and Real Application Testing options

Abrindo nova sessão
[oracle@lnxorcl ~]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.3.0 Production on Mon Jun 22 11:51:35 2015

Copyright (c) 1982, 2011, Oracle.  All rights reserved.

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 – Production
With the Partitioning, Automatic Storage Management, OLAP, Data Mining
and Real Application Testing options

HOMOLOGACAO >

Então, cada vez que você se conectar a um banco de dados você verá a variável prompt “HOMOLOGACAO >”.
Quando se trabalha com varias sessões SQLPLUS abertas, identificar as sessões ajuda a não executar instruções
de um ambiente em um outro ambiente evitando problemas futuros.
Essas modificações não são feitas em tempo de execução, é necessário se desconectar e conectar novamente no SQLPLUS.