Oracle数据库通过RMAN还原参数文件实验过程

最新推荐文章于 2022-12-19 13:33:52 发布
xiaohacute
最新推荐文章于 2022-12-19 13:33:52 发布
阅读量250
点赞数
分类专栏： Oracle
本文链接：https://blog.csdn.net/qq_44906445/article/details/105724091
版权
Oracle 专栏收录该内容
4 篇文章 0 订阅
订阅专栏
Oracle数据库通过RMAN还原参数文件实验过程

[oracle@xiaoha dbs]$ sqlplus / as sysdba    #进入数据库

SQL*Plus: Release 11.2.0.1.0 Production on Sun Apr 26 00:10:57 2020

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

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

SQL> select * from HHH;    #查询表HHH

        ID NAME                 TO_ATTEND
---------- -------------------- ---------
         2 xiaoli               24-APR-20
         1 xiaoha               24-APR-20

[oracle@xiaoha dbs]$ rman target /    #进入RMAN中（OLTP (DBID=1618378223)）

Recovery Manager: Release 11.2.0.1.0 - Production on Sun Apr 26 00:13:25 2020

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

connected to target database: OLTP (DBID=1618378223)

RMAN> list backup;    #查看备份片（参数文件和控制文件在数据片19上）

using target database control file instead of recovery catalog

List of Backup Sets
===================

BS Key  Size       Device Type Elapsed Time Completion Time
------- ---------- ----------- ------------ ---------------
17      196.21M    DISK        00:00:04     24-APR-20
        BP Key: 17   Status: AVAILABLE  Compressed: NO  Tag: TAG20200424T160514
        Piece Name: /u01/app/oracle/flash_recovery_area/OLTP/backupset/2020_04_24/o1_mf_annnn_TAG20200424T160514_hb57ftvx_.bkp

  List of Archived Logs in backup set 17
  Thrd Seq     Low SCN    Low Time  Next SCN   Next Time
  ---- ------- ---------- --------- ---------- ---------
  1    6       1027193    23-APR-20 1039801    23-APR-20
  1    7       1039801    23-APR-20 1066997    23-APR-20
  1    8       1066997    23-APR-20 1081357    23-APR-20
  1    9       1081357    23-APR-20 1112223    24-APR-20
  1    10      1112223    24-APR-20 1157390    24-APR-20
  1    11      1157390    24-APR-20 1160474    24-APR-20
  1    12      1160474    24-APR-20 1160515    24-APR-20
  1    13      1160515    24-APR-20 1181122    24-APR-20
  1    14      1181122    24-APR-20 1183191    24-APR-20
  1    15      1183191    24-APR-20 1183218    24-APR-20
  1    16      1183218    24-APR-20 1186895    24-APR-20
  1    17      1186895    24-APR-20 1186926    24-APR-20
  1    18      1186926    24-APR-20 1189361    24-APR-20
  1    19      1189361    24-APR-20 1189383    24-APR-20
  1    20      1189383    24-APR-20 1195936    24-APR-20

BS Key  Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
18      Full    1.05G      DISK        00:00:21     24-APR-20
        BP Key: 18   Status: AVAILABLE  Compressed: NO  Tag: TAG20200424T160522
        Piece Name: /u01/app/oracle/flash_recovery_area/OLTP/backupset/2020_04_24/o1_mf_nnndf_TAG20200424T160522_hb57g2r9_.bkp
  List of Datafiles in backup set 18
  File LV Type Ckp SCN    Ckp Time  Name
  ---- -- ---- ---------- --------- ----
  1       Full 1195947    24-APR-20 /u01/app/oracle/oradata/oltp/system01.dbf
  2       Full 1195947    24-APR-20 /u01/app/oracle/oradata/oltp/sysaux01.dbf
  3       Full 1195947    24-APR-20 /u01/app/oracle/oradata/oltp/undotbs01.dbf
  4       Full 1195947    24-APR-20 /u01/app/oracle/oradata/oltp/users01.dbf
  5       Full 1195947    24-APR-20 /u01/app/oracle/oradata/oltp/example01.dbf
  6       Full 1195947    24-APR-20 /u01/app/oracle/oradata/oltp/xiaohaspace01.dbf
  7       Full 1195947    24-APR-20 /u01/app/oracle/oradata/oltp/xiaohaspace02.dbf

BS Key  Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
19      Full    9.36M      DISK        00:00:01     24-APR-20
        BP Key: 19   Status: AVAILABLE  Compressed: NO  Tag: TAG20200424T160522
        Piece Name: /u01/app/oracle/flash_recovery_area/OLTP/backupset/2020_04_24/o1_mf_ncsnf_TAG20200424T160522_hb57gwsw_.bkp
  SPFILE Included: Modification time: 24-APR-20
  SPFILE db_unique_name: OLTP
  Control File Included: Ckp SCN: 1195961      Ckp time: 24-APR-20

BS Key  Size       Device Type Elapsed Time Completion Time
------- ---------- ----------- ------------ ---------------
20      11.50K     DISK        00:00:00     24-APR-20
        BP Key: 20   Status: AVAILABLE  Compressed: NO  Tag: TAG20200424T160549
        Piece Name: /u01/app/oracle/flash_recovery_area/OLTP/backupset/2020_04_24/o1_mf_annnn_TAG20200424T160549_hb57gy68_.bkp

  List of Archived Logs in backup set 20
  Thrd Seq     Low SCN    Low Time  Next SCN   Next Time
  ---- ------- ---------- --------- ---------- ---------
  1    21      1195936    24-APR-20 1195967    24-APR-20

SQL> show parameter spfile;     #查看参数文件的位置

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
spfile                               string      /u01/app/oracle/product/11.2.0
                                                 /dbhome_1/dbs/spfileoltp.ora

[oracle@xiaoha ~]$ cd /u01/app/oracle/product/11.2.0/dbhome_1/dbs/     #进入到参数文件的文件夹中
[oracle@xiaoha dbs]$ ls    #有spfileoltp.ora和init.ora这两个文件（init.ora是普通文件，spfileoltp.ora是二进制文件，数据库启动的时候会读取init.ora和spfileoltp.ora文件）
hc_DBUA0.dat  init.ora  orapwoltp      spfileoltp.ora
hc_oltp.dat   lkOLTP    snapcf_oltp.f
[oracle@xiaoha dbs]$ rm init.ora    #删除参数文件init.ora
[oracle@xiaoha dbs]$ rm spfileoltp.ora    #删除二进制参数文件spfileoltp.ora

SQL> shutdown immediate;    #关闭数据库
Database closed.
Database dismounted.
ORACLE instance shut down.

SQL> startup     #开启数据库时报错
ORA-01078: failure in processing system parameters
LRM-00109: could not open parameter file '/u01/app/oracle/product/11.2.0/dbhome_1/dbs/initoltp.ora'

RMAN> restore database;    #通过RMAN还原整个数据库失败（因为还原数据库至少是在mount模式下，而参数文件的缺失会导致数据库连nomount模式都打开不了，数据库无法启动，就没有办法通过备份集来进行还原）

Starting restore at 26-APR-20
using target database control file instead of recovery catalog
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of restore command at 04/26/2020 00:21:58
RMAN-12010: automatic channel allocation initialization failed
RMAN-06403: could not obtain a fully authorized session
ORA-01034: ORACLE not available
ORA-27101: shared memory realm does not exist
Linux-x86_64 Error: 2: No such file or directory

[oracle@xiaoha dbs]$ set oracle_sid=oltp     #临时去构建一个参数文件（设置数据库的实例名oracle_sid=oltp）
[oracle@xiaoha dbs]$ rman target /

Recovery Manager: Release 11.2.0.1.0 - Production on Sun Apr 26 00:24:48 2020

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

connected to target database (not started)

RMAN> set DBID=1618378223      #设置数据库的DBID=1618378223，DBID的值需要提前记下来

executing command: SET DBID

RMAN> startup nomount;     #启动到nomount模式下，临时构建一个参数文件

startup failed: ORA-01078: failure in processing system parameters
LRM-00109: could not open parameter file '/u01/app/oracle/product/11.2.0/dbhome_1/dbs/initoltp.ora'

starting Oracle instance without parameter file for retrieval of spfile
Oracle instance started

Total System Global Area     158662656 bytes

Fixed Size                     2211448 bytes
Variable Size                 92275080 bytes
Database Buffers              58720256 bytes
Redo Buffers                   5455872 bytes

RMAN> restore spfile from '/u01/app/oracle/flash_recovery_area/OLTP/backupset/2020_04_24/o1_mf_ncsnf_TAG20200424T160522_hb57gwsw_.bkp';     #还原参数文件spfile的数据片（这里的文件路径，通过前面备份的历史命令看到，如果不知道是哪个文件，可以找最近日期的文件，rman会自动寻找这里面有没有参数文件）

Starting restore at 26-APR-20
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=96 device type=DISK

channel ORA_DISK_1: restoring spfile from AUTOBACKUP /u01/app/oracle/flash_recovery_area/OLTP/backupset/2020_04_24/o1_mf_ncsnf_TAG20200424T160522_hb57gwsw_.bkp
channel ORA_DISK_1: SPFILE restore from AUTOBACKUP complete
Finished restore at 26-APR-20

RMAN> shutdown immediate;    #关闭数据库

Oracle instance shut down

SQL> startup;    #重新打开数据库
ORACLE instance started.

Total System Global Area  830930944 bytes
Fixed Size                  2217912 bytes
Variable Size             520095816 bytes
Database Buffers          301989888 bytes
Redo Buffers                6627328 bytes
Database mounted.
Database opened.
SQL> select status from v$instance;     #查看数据库的状态

STATUS
------------
OPEN

SQL> select * from HHH;    #查看表HHH数据完好无损

        ID NAME                 TO_ATTEND
---------- -------------------- ---------
         2 xiaoli               24-APR-20
         1 xiaoha               24-APR-20

[oracle@xiaoha dbs]$ pwd   #在当前路径下查看发现只有spfileoltp.ora还原回来了，init.ora却没有
/u01/app/oracle/product/11.2.0/dbhome_1/dbs
[oracle@xiaoha dbs]$ ls
hc_DBUA0.dat  hc_oltp.dat  lkOLTP  orapwoltp  snapcf_oltp.f  spfileoltp.ora

SQL> create pfile from spfile;    #恢复init.ora文件，可以通过二进制文件创建出文本文件（反过来也是可以的）

File created.

[oracle@xiaoha dbs]$ ls    #再次回到参数文件路径下查看，initoltp.ora和spfileoltp.ora都恢复回来了
hc_DBUA0.dat  initoltp.ora  orapwoltp      spfileoltp.ora
hc_oltp.dat   lkOLTP        snapcf_oltp.f

#注：数据库的还原必须当数据库启动的时候才能做，至少是mount状态；当破坏参数文件，数据库是无法启动的，就没有办法通过备份集来进行还原
#还原备份至少是mount状态下，调用rman工具至少是在nomount状态下。（RMAN恢复是要调用SGA区的Large Pool的内存来进行恢复，如果数据库在shutdown状态下是无法调用SGA区的）
#解决方法：临时构造参数文件，将数据库启动，还原参数文件回来

#补充：
[oracle@xiaoha 2020_04_24]$ pwd     #如果不知道参数文件的备份片是哪一个的话可以去找，可以从最后往前一个一个的试
/u01/app/oracle/flash_recovery_area/OLTP/backupset/2020_04_24
[oracle@xiaoha 2020_04_24]$ ls
o1_mf_annnn_TAG20200424T160514_hb57ftvx_.bkp
o1_mf_annnn_TAG20200424T160549_hb57gy68_.bkp
o1_mf_ncsnf_TAG20200424T160522_hb57gwsw_.bkp
o1_mf_nnndf_TAG20200424T160522_hb57g2r9_.bkp

[oracle@xiaoha 2020_04_24]$ rman target /    #查看DBID的方法（方法一：通过RMAN工具可以查看）

Recovery Manager: Release 11.2.0.1.0 - Production on Sun Apr 26 00:50:04 2020

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

connected to target database: OLTP (DBID=1618378223)

SQL> select dbid from v$database;     #查看DBID的方法（方法二：通过SQL可以查看）

      DBID
----------
1618378223