Oracle Database Backup/Recovery in WeBackup

Resource

1. 备份原理

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

2. 数据库安装

   • Oracle软件位置: /普亚百度云/软件宝库/oracle11g/
   • Windows比较简单直接
   • Linux稍微复杂，可以参考如下文章进行安装
     
     1. Oracle Database 11g Release 2 Installation on RHEL/CentOS 6.x/5.x/4.x
     2. https://wenku.baidu.com/view/afd5ca9e5022aaea998f0fa2.html
     3. http://dbatricksworld.com/oracle-11gr2-software-and-database-installation-on-oracle-linux-6-5/
     4. http://download.oracle.com/
     5. https://jingyan.baidu.com/article/d45ad148f299e969552b8094.html

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

   • https://github.com/shwpower/ora_backup
   • https://github.com/Tagar/rman
   • https://github.com/webster5361/rman_backups
   • https://github.com/gowrishdba/oracle-backup-recovery
   • http://blog.chinaunix.net/uid-28255964-id-4250134.html

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;