[关闭]
@tony-yin 2017-06-30T02:22:40.000000Z 字数 3525 阅读 827

Oracle Database Backup/Recovery in WeBackup

Resource


  1. 备份原理

    • 使用Oracle RMAN进行数据库的备份和恢复
    • 对处于Archive log(归档)模式下数据库进行热备份
    • 采用全备份加增量备份的备份策略
      备份内容包括:数据文件、Archive log、控制文件以及服务器配置参数
  2. 数据库安装

  3. 数据库创建

    • 安装后的默认数据库为orcl,如需创建新的数据库

      • 使用辅助工具(Linux UI mode)

        root@oracledb: xhost local:oracledb
        root@oracledb: su - oracle
        oracle@oracledb: dbca

      • 使用em管理WebUI

    • 创建表空间

      • 使用sqlplus

        su - oracle (Windows下此步骤省略)
        sqlplus / as sysdba
        create tablespace test_space datafile '/opt/oracle/oradata/test_data.dbf' size 500M

      • 使用em管理WebUI

    • 创建用户

      • 使用sqlplus

        su - oracle (Windows下此步骤省略)
        sqlplus / as sysdba
        create user test identified by test default tablespace test_space;

      • 使用em管理WebUI

    • 授权用户权限

      • 使用sqlplus

        su - oracle (Windows下此步骤省略)
        sqlplus / as sysdba
        grant connect, resource to test;

      • 使用em管理WebUI

    • 授权DBA权限(RMAN需要dba权限来运行)

      • 使用sqlplus

        su - oracle (Windows下此步骤省略)
        sqlplus / as sysdba
        grant sysdba to test;

      • 使用em管理WebUI

  4. 数据库启动/关闭

    • 切换用户

      root@oracledb: su - oracle

    • 启动监听

      lsnrctl start

    • 启动实例

      su - oracle (Windows下此步骤省略)
      sqlplus /nolog
      conn /as sysdba
      startup

    • 关闭实例

      su - oracle (Windows下此步骤省略)
      sqlplus /nolog
      conn /as sysdba
      shutdown immediate
      exit

    • 关闭监听

      lsnrctl stop

  5. 归档日志模式启动/关闭

    • 启动归档日志模式

      sqlplus /as sysdba
      shutdown immediate
      startup mount
      alter database archivelog
      alter database open

    • 关闭归档日志模式

      sqlplus /as sysdba
      shutdown immediate
      startup mount
      alter database noarchivelog
      alter database open

  6. Backup scripts

  7. Questions

    • Difference between image copy and backup sets
      • Backup sets
        RMAN-specific proprietary format (Default backup format)
      • Image copy
        Bit-for-bit copy of a file
    • Backup archivelog, control file and parameter file
    • Restore specific KBP
  8. Issues
    Q: How to recover to specific SCN with backupset?

    1. Env setup
      I have a table g schema as follow:
      id int, name varchar(16)
      Env cleanup (Delete records in g and delete archivelog)
      • sqlplus> delete from g;
      • rman> sql 'alter system checkpoint';
      • rman> sql 'alter system archivelog current';
      • rman> delete archivelog all;
    2. Rman cmd
      • Full backup rman>
        SQL 'ALTER SYSTEM CHECKPOINT';
        SQL 'ALTER SYSTEM ARCHIVELOG CURRENT';
        RUN {
        CONFIGURE BACKUP OPTIMIZATION ON;
        ALLOCATE CHANNEL ch1 TYPE DISK;
        BACKUP INCREMENTAL LEVEL 0 FORMAT '/dir/bk_%%d_%%u_%%s_%%p' AS COMPRESSED BACKUPSET DATABASE PLUS ARCHIVELOG DELETE INPUT;
        BACKUP CURRENT CONTROLFILE FORMAT '/dir/PC{n}';
        RELEASE CHANNEL ch1;
        }
      • Incremental backup rman>
        SQL 'ALTER SYSTEM CHECKPOINT';
        SQL 'ALTER SYSTEM ARCHIVELOG CURRENT';
        RUN {
        CONFIGURE BACKUP OPTIMIZATION ON;
        ALLOCATE CHANNEL ch1 TYPE DISK;
        BACKUP INCREMENTAL LEVEL 1 FORMAT '/dir/bk_%%d_%%u_%%s_%%p' AS COMPRESSED BACKUPSET DATABASE PLUS ARCHIVELOG DELETE INPUT;
        BACKUP CURRENT CONTROLFILE FORMAT '/dir/PC{n}';
        RELEASE CHANNEL ch1;
        }
      • Save scn after each backup for restore usage
        sqlplus> select * from (select next_change# from v$log_history order by recid desc) where rownum = 1;
      • Restore rman>
        SHUTDOWN IMMEDIATE;
        STARTUP NOMOUNT;
        RESTORE CONTROLFILE FROM '/dir/PC{n}';
        SQL 'ALTER DATABASE MOUN;
        RUN {
        RESTORE DATABASE UNTIL SCN {scn};
        RECOVER DATABASE UNTIL SCN {scn};
        }
        SQL 'ALTER DATABASE OPEN RESETLOGS';
  9. Insert records and backup

    • Backup0: Full backup
      • sqlplus> insert into g values(0, 'full');
      • Do full backup
      • Record SCN
    • Backup1: Incremental backup
      • sqlplus> insert into g values(1, 'incr1');
      • Do incremental backup
      • Record SCN
    • Backup2: Incremental backup
      • sqlplus> insert into g values(2, 'incr2');
      • Do incremental backup
      • Record SCN
    • Add one more record
      • sqlplus> insert into g values(3, 'incr3');
  10. Restore

    • Restore to backup1;
添加新批注
在作者公开此批注前,只有你和作者可见。
回复批注