Vídeos de RMAN

Reuni aqui links para todos os vídeos que fiz de RMAN (na verdade, de Recuperação), para ficar mais fácil de quem esteja procurando.

Recuperação da SYSTEM:

Recuperação da UNDO:

Recuperação de todos os CONTROLFILEs:

Recuperação da UNDO sem BACKUP:

Recuperação de DATAFILE da USERS e da SYSTEM:

Restauração de DATAFILE sem BACKUP:

CROSSCHECK e DELETE EXPIRED:

SNAPSHOT CONTROLFILE, CONTROLFILE AUTOBACKUP, e recuperação do CONTROLFILE:

BACKUP OPTIMIZATION:

Recuperação da perda de Redo Log INACTIVE:

Criação de Standby Manual:

8 comments

  1. Olá Portilho,

    Sobre vc prometeu o vídeo de recuperação de redolog active e current.

    Fiz uns teste no active e current que bastou eu dar um ckpt manual e emitir o comando alter database clear unachived logfile group 1; que criou meu redolog apagado.

    Atte.

    1. Para perda de ACTIVE esses comandos funcionam.
      Mas para CURRENT, não:

      SQL> SELECT MEMBER FROM V$LOGFILE WHERE GROUP# IN (SELECT GROUP# FROM v$LOG WHERE STATUS = ‘CURRENT’);

      MEMBER
      ——————————————————————————————————————————————————————————————————————
      /u01/app/oracle/oradata/ORCL/redo02.log

      SQL> !rm /u01/app/oracle/oradata/ORCL/redo02.log

      SQL> ALTER SYSTEM CHECKPOINT;

      System altered.

      SQL> ALTER DATABASE CLEAR UNARCHIVED LOGFILE GROUP 2;
      ALTER DATABASE CLEAR UNARCHIVED LOGFILE GROUP 2
      *
      ERROR at line 1:
      ORA-01624: log 2 needed for crash recovery of instance ORCL (thread 1)
      ORA-00312: online log 2 thread 1: ‘/u01/app/oracle/oradata/ORCL/redo02.log’

      SQL>

      1. Olha aí Portilho,

        SQL> SELECT MEMBER FROM V$LOGFILE WHERE GROUP# IN (SELECT GROUP# FROM v$LOG WHERE STATUS = ‘CURRENT’);

        MEMBER
        ——————————————————————————–
        /u01/app/oracle/oradata/CDB1/onlinelog/o1_mf_2_cvmqcfbd_.log
        /u01/app/oracle/fast_recovery_area/CDB1/onlinelog/o1_mf_2_cvmqcfdo_.log

        SQL>
        SQL>
        SQL>
        SQL> !rm /u01/app/oracle/oradata/CDB1/onlinelog/o1_mf_2_cvmqcfbd_.log

        SQL> ALTER DATABASE CLEAR UNARCHIVED LOGFILE GROUP 2;
        ALTER DATABASE CLEAR UNARCHIVED LOGFILE GROUP 2
        *
        ERROR at line 1:
        ORA-01624: log 2 needed for crash recovery of instance cdb1 (thread 1)
        ORA-00312: online log 2 thread 1:
        ‘/u01/app/oracle/oradata/CDB1/onlinelog/o1_mf_2_cvmqcfbd_.log’
        ORA-00312: online log 2 thread 1:
        ‘/u01/app/oracle/fast_recovery_area/CDB1/onlinelog/o1_mf_2_cvmqcfdo_.log’

        SQL> alter system switch logfile;

        System altered.

        SQL> ALTER DATABASE CLEAR UNARCHIVED LOGFILE GROUP 2;

        Database altered.

        SQL>
        SQL> alter system switch logfile;

        System altered.

        SQL> /

        System altered.

        SQL> /

        System altered.

        SQL>

        1. Aí foi outra recuperação, da perda de um dos membros de um grupo multiplexado.
          Em um grupo não multiplexado, ou com a perda de todos os membros do grupo CURRENT, o CLEAR não vai funcionar.

          1. Tirei a multiplexação.

            SQL> SELECT MEMBER FROM V$LOGFILE WHERE GROUP# IN (SELECT GROUP# FROM v$LOG WHERE STATUS = ‘CURRENT’);

            MEMBER
            ——————————————————————————–
            /u01/app/oracle/oradata/CDB1/onlinelog/redo_02.log

            SQL> !rm /u01/app/oracle/oradata/CDB1/onlinelog/redo_02.log

            SQL>
            SQL>
            SQL> ALTER DATABASE CLEAR UNARCHIVED LOGFILE GROUP 2;
            ALTER DATABASE CLEAR UNARCHIVED LOGFILE GROUP 2
            *
            ERROR at line 1:
            ORA-01624: log 2 needed for crash recovery of instance cdb1 (thread 1)
            ORA-00312: online log 2 thread 1:
            ‘/u01/app/oracle/oradata/CDB1/onlinelog/redo_02.log’

            SQL> alter system switch logfile;

            System altered.

            SQL> ALTER DATABASE CLEAR UNARCHIVED LOGFILE GROUP 2;

            Database altered.

            SQL> alter system switch logfile;

            System altered.

            SQL> /

            System altered.

            SQL> /

            System altered.

            SQL> /

            System altered.

            SQL>

          2. Você está certo, funciona sim.
            O máximo que acontece é ficar um buraco nos Archives, pois a Sequence (52 no meu caso, abaixo) que era Current não pode ser arquivada. Mas até aí que se dane, o importante é ter o banco disponível.

            SQL> SELECT MEMBER FROM V$LOGFILE WHERE GROUP# IN (SELECT GROUP# FROM v$LOG WHERE STATUS = ‘CURRENT’);

            MEMBER
            ——————————————————————————–
            /u01/app/oracle/oradata/NERV/redo01.log

            SQL> !rm /u01/app/oracle/oradata/NERV/redo01.log

            SQL> ALTER DATABASE CLEAR UNARCHIVED LOGFILE GROUP 1;
            ALTER DATABASE CLEAR UNARCHIVED LOGFILE GROUP 1
            *
            ERROR at line 1:
            ORA-01624: log 1 needed for crash recovery of instance NERV (thread 1)
            ORA-00312: online log 1 thread 1: ‘/u01/app/oracle/oradata/NERV/redo01.log’

            SQL> alter system switch logfile;

            System altered.

            SQL> ALTER DATABASE CLEAR UNARCHIVED LOGFILE GROUP 1;

            Database altered.

            SQL> alter system switch logfile;

            System altered.

            SQL> alter system switch logfile;

            System altered.

            SQL> alter system switch logfile;

            System altered.

            SQL> alter system switch logfile;

            System altered.

            SQL> alter system switch logfile;

            System altered.

            SQL> exit
            Disconnected from Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 – 64bit Production
            With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options
            [oracle@nerv06 ~]$ rlwrap rman target /

            Recovery Manager: Release 12.1.0.2.0 – Production on Tue Aug 23 15:24:08 2016

            Copyright (c) 1982, 2014, Oracle and/or its affiliates. All rights reserved.

            connected to target database: NERV (DBID=4079771799)

            RMAN> LIST ARCHIVELOG ALL;

            using target database control file instead of recovery catalog
            List of Archived Log Copies for database with db_unique_name NERV
            =====================================================================

            Key Thrd Seq S Low Time
            ——- —- ——- – ———
            24 1 39 A 22-AUG-16
            Name: /u01/app/oracle/fast_recovery_area/NERV/archivelog/2016_08_22/o1_mf_1_39_cvpmjdft_.arc

            25 1 40 A 22-AUG-16
            Name: /u01/app/oracle/fast_recovery_area/NERV/archivelog/2016_08_22/o1_mf_1_40_cvqclsdo_.arc

            26 1 41 A 22-AUG-16
            Name: /u01/app/oracle/fast_recovery_area/NERV/archivelog/2016_08_23/o1_mf_1_41_cvqmcz95_.arc

            27 1 42 A 23-AUG-16
            Name: /u01/app/oracle/fast_recovery_area/NERV/archivelog/2016_08_23/o1_mf_1_42_cvqp66tl_.arc

            28 1 43 A 23-AUG-16
            Name: /u01/app/oracle/fast_recovery_area/NERV/archivelog/2016_08_23/o1_mf_1_43_cvqpfd5k_.arc

            29 1 44 A 23-AUG-16
            Name: /u01/app/oracle/fast_recovery_area/NERV/archivelog/2016_08_23/o1_mf_1_44_cvqphr8d_.arc

            30 1 45 A 23-AUG-16
            Name: /u01/app/oracle/fast_recovery_area/NERV/archivelog/2016_08_23/o1_mf_1_45_cvqpjqrm_.arc

            31 1 46 A 23-AUG-16
            Name: /u01/app/oracle/fast_recovery_area/NERV/archivelog/2016_08_23/o1_mf_1_46_cvqpkr22_.arc

            32 1 47 A 23-AUG-16
            Name: /u01/app/oracle/fast_recovery_area/NERV/archivelog/2016_08_23/o1_mf_1_47_cvqplr0g_.arc

            33 1 48 A 23-AUG-16
            Name: /u01/app/oracle/fast_recovery_area/NERV/archivelog/2016_08_23/o1_mf_1_48_cvqpmsx2_.arc

            34 1 49 A 23-AUG-16
            Name: /u01/app/oracle/fast_recovery_area/NERV/archivelog/2016_08_23/o1_mf_1_49_cvqppwn5_.arc

            35 1 50 A 23-AUG-16
            Name: /u01/app/oracle/fast_recovery_area/NERV/archivelog/2016_08_23/o1_mf_1_50_cvr44s3b_.arc

            36 1 51 A 23-AUG-16
            Name: /u01/app/oracle/fast_recovery_area/NERV/archivelog/2016_08_23/o1_mf_1_51_cvs08sc1_.arc

            38 1 53 A 23-AUG-16
            Name: /u01/app/oracle/fast_recovery_area/NERV/archivelog/2016_08_23/o1_mf_1_53_cvs53qln_.arc

            39 1 54 A 23-AUG-16
            Name: /u01/app/oracle/fast_recovery_area/NERV/archivelog/2016_08_23/o1_mf_1_54_cvs53rcv_.arc

            40 1 55 A 23-AUG-16
            Name: /u01/app/oracle/fast_recovery_area/NERV/archivelog/2016_08_23/o1_mf_1_55_cvs53t8h_.arc

            41 1 56 A 23-AUG-16
            Name: /u01/app/oracle/fast_recovery_area/NERV/archivelog/2016_08_23/o1_mf_1_56_cvs53vb9_.arc

            42 1 57 A 23-AUG-16
            Name: /u01/app/oracle/fast_recovery_area/NERV/archivelog/2016_08_23/o1_mf_1_57_cvs53y0o_.arc

  2. olá Portilho,

    O crashmanager nao rola quando os arquivos estao em ASM (+DATA) né?!
    Tem alguma opção para esse cenário?

    Valeu.

    1. Não conheço uma opção de automação de falhas em ASM, mas em compensação, é mais difícil remover arquivos de lá:

      ASMCMD [+DATA/ORCL/DATAFILE] > rm USERS.1310.920121645
      ORA-15032: not all alterations performed
      ORA-15028: ASM file ‘+DATA/ORCL/DATAFILE/USERS.1310.920121645’ not dropped; currently being accessed (DBD ERROR: OCIStmtExecute)
      ASMCMD [+DATA/ORCL/DATAFILE] >

Leave a Reply

O seu endereço de e-mail não será publicado. Campos obrigatórios são marcados com *

Esse site utiliza o Akismet para reduzir spam. Aprenda como seus dados de comentários são processados.