nologging对象的恢复

1.创建一个数据库全备
创建一个全备脚本
rman.sh
rman target / <<eof
run{
delete noprompt backup;
backup as compressed backupset database plus archivelog;
sql 'alter system switch logfile';
sql 'alter system switch logfile';
sql 'alter system switch logfile';
sql 'alter system switch logfile';
sql 'alter system switch logfile';
}
exit
echo "================Backup is Completed================="
eof

chmod u+x rman.sh
./rman.sh

2.创建一张只读的表
SQL> create table nolog_emp nologging as select * from emp where 1=2;

Table created.

SQL> insert /*+ APPEND */ into nolog_emp select * from emp;

14 rows created.

SQL> commit;

Commit complete.

SQL> select * from nolog_Emp;

     EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM     DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
      7369 SMITH      CLERK           7902 17-DEC-80        800                    20
      7499 ALLEN      SALESMAN        7698 20-FEB-81       1600        300         30
      7521 WARD       SALESMAN        7698 22-FEB-81       1250        500         30
      7566 JONES      MANAGER         7839 02-APR-81       2975                    20
      7654 MARTIN     SALESMAN        7698 28-SEP-81       1250       1400         30
      7698 BLAKE      MANAGER         7839 01-MAY-81       2850                    30
      7782 CLARK      MANAGER         7839 09-JUN-81       2450                    10
      7788 SCOTT      ANALYST         7566 19-APR-87       3000                    20
      7839 KING       PRESIDENT            17-NOV-81       5000                    10
      7844 TURNER     SALESMAN        7698 08-SEP-81       1500          0         30
      7876 ADAMS      CLERK           7788 23-MAY-87       1100                    20
      7900 JAMES      CLERK           7698 03-DEC-81        950                    30
      7902 FORD       ANALYST         7566 03-DEC-81       3000                    20
      7934 MILLER     CLERK           7782 23-JAN-82       1300                    10

14 rows selected.

确认该表所在的表空间
SQL> select table_name,tablespace_name from user_tables;

TABLE_NAME                     TABLESPACE_NAME
------------------------------ ------------------------------
NOLOG_EMP                      USERS
EMP                            USERS
SALGRADE                       USERS
BONUS                          USERS
DEPT                           USERS

3.模拟故障
SQL> conn / as sysdba
Connected.
SQL> select name from v$datafile;

NAME
---------------------------------------------------------------------------------------------
/u01/app/oracle/oradata/orcl/system01.dbf
/u01/app/oracle/oradata/orcl/sysaux01.dbf
/u01/app/oracle/oradata/orcl/undotbs01.dbf
/u01/app/oracle/oradata/orcl/users01.dbf
/u01/app/oracle/oradata/orcl/example01.dbf
/u01/app/oracle/oradata/orcl/idx_tbs01.dbf

6 rows selected.

SQL> alter system flush buffer_cache;

System altered.

SQL> alter system flush shared_pool;

System altered.

SQL> !rm -rf /u01/app/oracle/oradata/orcl/users01.dbf

SQL> conn scott/tiger
Connected.
SQL> select * from nolog_emp;
select * from nolog_emp
              *
ERROR at line 1:
ORA-01116: error in opening database file 4
ORA-01110: data file 4: '/u01/app/oracle/oradata/orcl/users01.dbf'
ORA-27041: unable to open file
Linux Error: 2: No such file or directory
Additional information: 3

4.使用rman恢复
rman target /

RMAN> run{
2> sql 'alter database datafile 4 offline';
3> restore datafile 4;
4> recover datafile 4;
5> sql 'alter database datafile 4 online';
6> }

using target database control file instead of recovery catalog
sql statement: alter database datafile 4 offline

Starting restore at 26-MAY-16
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=10 device type=DISK

channel ORA_DISK_1: starting datafile backup set restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_DISK_1: restoring datafile 00004 to /u01/app/oracle/oradata/orcl/users01.dbf
channel ORA_DISK_1: reading from backup piece /u02/ORCL/backupset/2016_05_26/o1_mf_nnndf_TAG20160526T112410_cndv2txk_.bkp
channel ORA_DISK_1: piece handle=/u02/ORCL/backupset/2016_05_26/o1_mf_nnndf_TAG20160526T112410_cndv2txk_.bkp tag=TAG20160526T112410
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:03
Finished restore at 26-MAY-16

Starting recover at 26-MAY-16
using channel ORA_DISK_1

starting media recovery
media recovery complete, elapsed time: 00:00:00
Finished recover at 26-MAY-16

sql statement: alter database datafile 4 online

5.返回数据库，检查恢复的结果
conn scott/tiger

SQL> select * from nolog_emp;
select * from nolog_emp
              *
ERROR at line 1:
ORA-01578: ORACLE data block corrupted (file # 4, block # 147)
ORA-01110: data file 4: '/u01/app/oracle/oradata/orcl/users01.dbf'
ORA-26040: Data block was loaded using the NOLOGGING option

发现，报坏块错误
逻辑损坏
因为redo里面，没有该表的DML信息，无法为该表做数据恢复

重建

查询该表元数据重建一张新表，但是无法恢复原表的数据
select dbms_metadata.get_ddl('TABLE','NOLOG_EMP','SCOTT') from dual;

DBMS_METADATA.GET_DDL('TABLE','NOLOG_EMP','SCOTT')
--------------------------------------------------------------------------------

  CREATE TABLE "SCOTT"."NOLOG_EMP"
   (    "EMPNO" NUMBER(4,0),
        "ENAME" VARCHAR2(10),
        "JOB" VARCHAR2(9),
        "MGR" NUMBER(4,0),
        "HIREDATE" DATE,
        "SAL" NUMBER(7,2),
        "COMM" NUMBER(7,2),
        "DEPTNO" NUMBER(2,0)
   ) SEGMENT CREATION IMMEDIATE
  PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS NOLOGGING
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DE
FAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE "USERS"

6.删除该表重建
SQL> drop table scott.nolog_emp purge;