设为首页收藏本站

Botang唐波's Oracle Station

查看: 199|回复: 0

课程第14次(2018-06-26星期二)

[复制链接]

740

主题

1112

帖子

8057

积分

管理员

Rank: 9Rank: 9Rank: 9

积分
8057
发表于 2018-6-26 19:58:35 | 显示全部楼层 |阅读模式
从文件系统进ASM:
  1. select  * from v$log;

  2. alter database drop logfile group 3;

  3. alter database add logfile group 3 size 50M;

  4. select  member from v$logfile;

  5. alter database drop logfile group 1;


  6. alter database add logfile group 1 size 50M;

  7. alter system switch logfile;

  8. alter system checkpoint;

  9. alter database drop logfile group 2;

  10. alter database add logfile group 2 size 50M;

  11. ------------------------------------------------------------------

复制代码
  1. SQL> startup mount
  2. ORACLE instance started.

  3. Total System Global Area 1046224896 bytes
  4. Fixed Size                    1341140 bytes
  5. Variable Size                  603982124 bytes
  6. Database Buffers          436207616 bytes
  7. Redo Buffers                    4694016 bytes
  8. ORA-00205: error in identifying control file, check alert log for more info


  9. SQL> show parameter control

  10. NAME                                     TYPE
  11. ------------------------------------ ---------------------------------
  12. VALUE
  13. ------------------------------
  14. control_file_record_keep_time             integer
  15. 7
  16. control_files                             string
  17. /u01/app/oracle/product/11.2.0
  18. /dbhome_1/dbs/cntrlorcl.dbf
  19. control_management_pack_access             string
  20. DIAGNOSTIC+TUNING
  21. SQL> alter system set control_files='/u01/app/oracle/oradata/orcl/control01.ctl' scope=spfile;

  22. System altered.

  23. SQL> shutdown immediate
  24. ORA-01507: database not mounted


  25. ORACLE instance shut down.
  26. SQL> startup mount
  27. ORACLE instance started.

  28. Total System Global Area 1046224896 bytes
  29. Fixed Size                    1341140 bytes
  30. Variable Size                  603982124 bytes
  31. Database Buffers          436207616 bytes
  32. Redo Buffers                    4694016 bytes
  33. Database mounted.
  34. SQL> exit
  35. Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
  36. With the Partitioning, OLAP, Data Mining and Real Application Testing options
  37. [oracle@station86 dbs]$ rman target /

  38. Recovery Manager: Release 11.2.0.1.0 - Production on Tue Jun 26 19:54:12 2018

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

  40. connected to target database: ORCL (DBID=1359978017, not open)

  41. RMAN> exit


  42. Recovery Manager complete.
  43. [oracle@station86 dbs]$ sqlplus /nolog

  44. SQL*Plus: Release 11.2.0.1.0 Production on Tue Jun 26 19:54:56 2018

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

  46. SQL> conn / as sysdba
  47. Connected.
  48. SQL> show parameter db_recovery

  49. NAME                                     TYPE         VALUE
  50. ------------------------------------ ----------- ------------------------------
  51. db_recovery_file_dest                     string         +FRA
  52. db_recovery_file_dest_size             big integer 4G
  53. SQL> alter system set db_recovery_file_dest='+DATA';

  54. System altered.

  55. SQL> exit
  56. Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
  57. With the Partitioning, OLAP, Data Mining and Real Application Testing options
  58. [oracle@station86 dbs]$ rman target /

  59. Recovery Manager: Release 11.2.0.1.0 - Production on Tue Jun 26 19:55:20 2018

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

  61. connected to target database: ORCL (DBID=1359978017, not open)

  62. RMAN> backup as copy database;

  63. Starting backup at 2018-06-26:19:55:49
  64. using target database control file instead of recovery catalog
  65. allocated channel: ORA_DISK_1
  66. channel ORA_DISK_1: SID=136 device type=DISK
  67. allocated channel: ORA_DISK_2
  68. channel ORA_DISK_2: SID=7 device type=DISK
  69. allocated channel: ORA_DISK_3
  70. channel ORA_DISK_3: SID=49 device type=DISK
  71. allocated channel: ORA_DISK_4
  72. channel ORA_DISK_4: SID=93 device type=DISK
  73. allocated channel: ORA_DISK_5
  74. channel ORA_DISK_5: SID=137 device type=DISK
  75. allocated channel: ORA_DISK_6
  76. channel ORA_DISK_6: SID=8 device type=DISK
  77. allocated channel: ORA_DISK_7
  78. channel ORA_DISK_7: SID=50 device type=DISK
  79. allocated channel: ORA_DISK_8
  80. channel ORA_DISK_8: SID=94 device type=DISK
  81. channel ORA_DISK_1: starting datafile copy
  82. input datafile file number=00001 name=/u01/app/oracle/oradata/orcl/system01.dbf
  83. channel ORA_DISK_2: starting datafile copy
  84. input datafile file number=00002 name=/u01/app/oracle/oradata/orcl/sysaux01.dbf
  85. channel ORA_DISK_3: starting datafile copy
  86. input datafile file number=00003 name=/u01/app/oracle/oradata/orcl/undotbs1.dbf
  87. channel ORA_DISK_4: starting datafile copy
  88. input datafile file number=00005 name=/u01/app/oracle/oradata/orcl/example01.dbf
  89. channel ORA_DISK_5: starting datafile copy
  90. input datafile file number=00004 name=/u01/app/oracle/oradata/orcl/users01.dbf
  91. channel ORA_DISK_6: starting datafile copy
  92. input datafile file number=00006 name=/u01/app/oracle/oradata/orcl/tbs1.dbf
  93. channel ORA_DISK_7: starting datafile copy
  94. input datafile file number=00007 name=/u01/app/oracle/oradata/orcl/tbs2.dbf
  95. output file name=+DATA/orcl/datafile/users.265.979847755 tag=TAG20180626T195551 RECID=17 STAMP=979847767
  96. channel ORA_DISK_5: datafile copy complete, elapsed time: 00:00:25
  97. output file name=+DATA/orcl/datafile/tbs1.256.979847755 tag=TAG20180626T195551 RECID=15 STAMP=979847764
  98. channel ORA_DISK_6: datafile copy complete, elapsed time: 00:00:25
  99. output file name=+DATA/orcl/datafile/tbs2.257.979847755 tag=TAG20180626T195551 RECID=16 STAMP=979847765
  100. channel ORA_DISK_7: datafile copy complete, elapsed time: 00:00:26
  101. output file name=+DATA/orcl/datafile/undotbs1.258.979847755 tag=TAG20180626T195551 RECID=19 STAMP=979847811
  102. channel ORA_DISK_3: datafile copy complete, elapsed time: 00:01:07
  103. output file name=+DATA/orcl/datafile/example.259.979847765 tag=TAG20180626T195551 RECID=18 STAMP=979847810
  104. channel ORA_DISK_4: datafile copy complete, elapsed time: 00:01:07
  105. output file name=+DATA/orcl/datafile/sysaux.267.979847755 tag=TAG20180626T195551 RECID=20 STAMP=979847825
  106. channel ORA_DISK_2: datafile copy complete, elapsed time: 00:01:19
  107. output file name=+DATA/orcl/datafile/system.268.979847755 tag=TAG20180626T195551 RECID=21 STAMP=979847830
  108. channel ORA_DISK_1: datafile copy complete, elapsed time: 00:01:29
  109. Finished backup at 2018-06-26:19:57:20

  110. Starting Control File and SPFILE Autobackup at 2018-06-26:19:57:20
  111. piece handle=+DATA/orcl/autobackup/2018_06_26/s_979847323.263.979847841 comment=NONE
  112. Finished Control File and SPFILE Autobackup at 2018-06-26:19:57:21

  113. RMAN> switch database to copy ;

  114. datafile 1 switched to datafile copy "+DATA/orcl/datafile/system.268.979847755"
  115. datafile 2 switched to datafile copy "+DATA/orcl/datafile/sysaux.267.979847755"
  116. datafile 3 switched to datafile copy "+DATA/orcl/datafile/undotbs1.258.979847755"
  117. datafile 4 switched to datafile copy "+DATA/orcl/datafile/users.265.979847755"
  118. datafile 5 switched to datafile copy "+DATA/orcl/datafile/example.259.979847765"
  119. datafile 6 switched to datafile copy "+DATA/orcl/datafile/tbs1.256.979847755"
  120. datafile 7 switched to datafile copy "+DATA/orcl/datafile/tbs2.257.979847755"

  121. RMAN> exit


  122. Recovery Manager complete.
  123. [oracle@station86 dbs]$ sqlplus /nolog

  124. SQL*Plus: Release 11.2.0.1.0 Production on Tue Jun 26 19:57:44 2018

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

  126. SQL> conn / as sysdba
  127. Connected.
  128. SQL> show parameter db_recovery

  129. NAME                                     TYPE         VALUE
  130. ------------------------------------ ----------- ------------------------------
  131. db_recovery_file_dest                     string         +DATA
  132. db_recovery_file_dest_size             big integer 4G
  133. SQL> alter system set db_recovery_file_dest='+FRA';

  134. System altered.

  135. SQL>
复制代码
  1. SQL>
  2. SQL> alter database backup controlfile to trace as '/home/oracle/control8c-2.sql'
  3.   2  ;

  4. Database altered.

  5. SQL> exit
复制代码
control8c-2.sql:
  1. CREATE CONTROLFILE REUSE DATABASE "ORCL" NORESETLOGS  ARCHIVELOG
  2.     MAXLOGFILES 16
  3.     MAXLOGMEMBERS 3
  4.     MAXDATAFILES 100
  5.     MAXINSTANCES 8
  6.     MAXLOGHISTORY 292
  7. LOGFILE
  8.   GROUP 1 (
  9.     '+DATA/orcl/onlinelog/group_1.264.979847063',
  10.     '+FRA/orcl/onlinelog/group_1.320.979847065'
  11.   ) SIZE 50M BLOCKSIZE 512,
  12.   GROUP 2 (
  13.     '+DATA/orcl/onlinelog/group_2.260.979847183',
  14.     '+FRA/orcl/onlinelog/group_2.350.979847185'
  15.   ) SIZE 50M BLOCKSIZE 512,
  16.   GROUP 3 (
  17.     '+DATA/orcl/onlinelog/group_3.266.979846987',
  18.     '+FRA/orcl/onlinelog/group_3.294.979846989'
  19.   ) SIZE 50M BLOCKSIZE 512
  20. DATAFILE
  21.   '+DATA/orcl/datafile/system.268.979847755',
  22.   '+DATA/orcl/datafile/sysaux.267.979847755',
  23.   '+DATA/orcl/datafile/undotbs1.258.979847755',
  24.   '+DATA/orcl/datafile/users.265.979847755',
  25.   '+DATA/orcl/datafile/example.259.979847765',
  26.   '+DATA/orcl/datafile/tbs1.256.979847755',
  27.   '+DATA/orcl/datafile/tbs2.257.979847755'
  28. CHARACTER SET AL32UTF8
  29. ;

  30. -- Configure RMAN configuration record 1
  31. VARIABLE RECNO NUMBER;
  32. EXECUTE :RECNO := SYS.DBMS_BACKUP_RESTORE.SETCONFIG('RETENTION POLICY','TO RECOVERY WINDOW OF 1 DAYS');
  33. -- Configure RMAN configuration record 2
  34. VARIABLE RECNO NUMBER;
  35. EXECUTE :RECNO := SYS.DBMS_BACKUP_RESTORE.SETCONFIG('BACKUP OPTIMIZATION','ON');
  36. -- Configure RMAN configuration record 3
  37. VARIABLE RECNO NUMBER;
  38. EXECUTE :RECNO := SYS.DBMS_BACKUP_RESTORE.SETCONFIG('CONTROLFILE AUTOBACKUP','ON');
  39. -- Configure RMAN configuration record 4
  40. VARIABLE RECNO NUMBER;
  41. EXECUTE :RECNO := SYS.DBMS_BACKUP_RESTORE.SETCONFIG('DEVICE TYPE','DISK PARALLELISM 8 BACKUP TYPE TO BACKUPSET');
  42. -- Configure RMAN configuration record 5
  43. VARIABLE RECNO NUMBER;
  44. EXECUTE :RECNO := SYS.DBMS_BACKUP_RESTORE.SETCONFIG('DEFAULT DEVICE TYPE TO','DISK');
  45. -- Configure RMAN configuration record 6
  46. VARIABLE RECNO NUMBER;
  47. EXECUTE :RECNO := SYS.DBMS_BACKUP_RESTORE.SETCONFIG('DEVICE TYPE','''SBT_TAPE'' PARALLELISM 2 BACKUP TYPE TO BACKUPSET');
  48. -- Configure RMAN configuration record 7
  49. VARIABLE RECNO NUMBER;
  50. EXECUTE :RECNO := SYS.DBMS_BACKUP_RESTORE.SETCONFIG('CHANNEL','1 DEVICE TYPE ''SBT_TAPE'' PARMS  ''ENV=(OB_MEDIA_FAMILY=station86)''');
  51. -- Configure RMAN configuration record 8
  52. VARIABLE RECNO NUMBER;
  53. EXECUTE :RECNO := SYS.DBMS_BACKUP_RESTORE.SETCONFIG('CHANNEL','2 DEVICE TYPE ''SBT_TAPE'' PARMS  ''ENV=(OB_MEDIA_FAMILY=station86)''');
  54. -- Configure RMAN configuration record 9
  55. VARIABLE RECNO NUMBER;
  56. EXECUTE :RECNO := SYS.DBMS_BACKUP_RESTORE.SETCONFIG('CHANNEL','DEVICE TYPE ''SBT_TAPE'' PARMS  ''ENV=(OB_MEDIA_FAMILY=station86)''');
复制代码
  1. [oracle@station86 ~]$ sqlplus /nolog

  2. SQL*Plus: Release 11.2.0.1.0 Production on Tue Jun 26 20:02:48 2018

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

  4. SQL> conn  / as sysdba
  5. Connected.
  6. SQL> show parameter control

  7. NAME                                     TYPE         VALUE
  8. ------------------------------------ ----------- ------------------------------
  9. control_file_record_keep_time             integer         7
  10. control_files                             string         /u01/app/oracle/oradata/orcl/c
  11.                                                  ontrol01.ctl
  12. control_management_pack_access             string         DIAGNOSTIC+TUNING
  13. SQL> alter system set control_files='+DATA','+FRA' scope=spfile;

  14. System altered.

  15. SQL> shutdown immediate
  16. ORA-01109: database not open


  17. Database dismounted.
  18. ORACLE instance shut down.
  19. SQL> startup nomount
  20. ORACLE instance started.

  21. Total System Global Area 1046224896 bytes
  22. Fixed Size                    1341140 bytes
  23. Variable Size                  603982124 bytes
  24. Database Buffers          436207616 bytes
  25. Redo Buffers                    4694016 bytes
  26. SQL> @/home/oracle/control8c-2

  27. Control file created.


  28. PL/SQL procedure successfully completed.


  29. PL/SQL procedure successfully completed.


  30. PL/SQL procedure successfully completed.


  31. PL/SQL procedure successfully completed.


  32. PL/SQL procedure successfully completed.


  33. PL/SQL procedure successfully completed.


  34. PL/SQL procedure successfully completed.


  35. PL/SQL procedure successfully completed.


  36. PL/SQL procedure successfully completed.

  37. SQL> alter database open ;

  38. Database altered.

  39. SQL> alter tablespace temp add tempfile '+DATA' size 30M autoextend on ;

  40. Tablespace altered.

  41. SQL> show parameter spfile;

  42. NAME                                     TYPE         VALUE
  43. ------------------------------------ ----------- ------------------------------
  44. spfile                                     string         /u01/app/oracle/product/11.2.0
  45.                                                  /dbhome_1/dbs/spfileorcl.ora
  46. SQL> create pfile='/home/oracle/pfile.ora' from spfile;

  47. File created.

  48. SQL> create spfile='+data/orcl/spfileorcl.ora' from pfile='/home/oracle/pfile.ora';

  49. File created.

  50. SQL> shutdown immediate
  51. Database closed.
  52. Database dismounted.
  53. ORACLE instance shut down.
  54. SQL>
复制代码

写/u01/app/oracle/product/11.2.0/dbhome_1/dbs/initorcl.ora为spfile='+data/orcl/spfileorcl.ora'
再启动,做完。

  1. [oracle@station86 ~]$ srvctl remove  database -d orcl
  2. Remove the database orcl? (y/[n]) y
  3. [oracle@station86 ~]$ srvctl add database -d orcl -o /u01/app/oracle/product/11.2.0/dbhome_1
  4. [oracle@station86 ~]$ crs_stat -t
  5. Name           Type           Target    State     Host        
  6. ------------------------------------------------------------
  7. ora.DATA.dg    ora....up.type ONLINE    ONLINE    station86   
  8. ora.FRA.dg     ora....up.type ONLINE    ONLINE    station86   
  9. ora....ER.lsnr ora....er.type ONLINE    ONLINE    station86   
  10. ora.asm        ora.asm.type   ONLINE    ONLINE    station86   
  11. ora.cssd       ora.cssd.type  ONLINE    ONLINE    station86   
  12. ora.diskmon    ora....on.type ONLINE    ONLINE    station86   
  13. ora.orcl.db    ora....se.type OFFLINE   OFFLINE               
  14. [oracle@station86 ~]$ crsctl start res ora.orcl.db
  15. CRS-2672: Attempting to start 'ora.orcl.db' on 'station86'
复制代码
关于ASM迁移的最后的讨论:
当时的操作时串行:
  1. RMAN>  run {
  2. 2> allocate channel c1 device type disk format '/u01/app/oracle/oradata/orcl/system01.dbf';
  3. 3> backup as copy datafile 1;
  4. 4> release channel c1;
  5. 5> allocate channel c1 device type disk format '/u01/app/oracle/oradata/orcl/sysaux01.dbf';
  6. 6> backup as copy datafile 2;
  7. 7> release channel c1;
  8. 8> allocate channel c1 device type disk format '/u01/app/oracle/oradata/orcl/undotbs1.dbf';
  9. 9> backup as copy datafile 3;
  10. 10> release channel c1;
  11. 11> allocate channel c1 device type disk format '/u01/app/oracle/oradata/orcl/users01.dbf';
  12. 12> backup as copy datafile 4;
  13. 13> release channel c1;
  14. 14> allocate channel c1 device type disk format '/u01/app/oracle/oradata/orcl/example01.dbf';
  15. 15> backup as copy datafile 5;
  16. 16> release channel c1;
  17. 17>  allocate channel c1 device type disk format '/u01/app/oracle/oradata/orcl/tbs1.dbf';
  18. 18> backup as copy datafile 6;
  19. 19> release channel c1;
  20. 20> allocate channel c1 device type disk format '/u01/app/oracle/oradata/orcl/tbs2.dbf';
  21. 21> backup as copy datafile 7;
  22. 22> release channel c1;
  23. 23> }
复制代码

做数据文件迁移的时候能改进一下吗:

  1. RMAN>  run {
  2. 2> allocate channel c1 device type disk format '/u01/app/oracle/oradata/orcl/system01.dbf';
  3. 3> allocate channel c2 device type disk format '/u01/app/oracle/oradata/orcl/sysaux01.dbf';
  4. 4> allocate channel c3 device type disk format '/u01/app/oracle/oradata/orcl/undotbs1.dbf';
  5. 5> allocate channel c4 device type disk format '/u01/app/oracle/oradata/orcl/users01.dbf';
  6. 6> allocate channel c5 device type disk format '/u01/app/oracle/oradata/orcl/example01.dbf';
  7. 7>  allocate channel c6 device type disk format '/u01/app/oracle/oradata/orcl/tbs1.dbf';
  8. 8> allocate channel c7device type disk format '/u01/app/oracle/oradata/orcl/tbs2.dbf';
  9. 9> backup as copy  database;
  10. 10>}
复制代码

以上的做法有可能存在通道输入不是你想要的对应关系:
  1.         Tag: ORA_OEM_LEVEL_0


  2. RMAN> list copy of datafile 5;

  3. List of Datafile Copies
  4. =======================

  5. Key     File S Completion Time     Ckp SCN    Ckp Time           
  6. ------- ---- - ------------------- ---------- -------------------
  7. 9       5    A 2018-06-26:20:18:09 1334275    2018-06-26:20:17:12
  8.         Name: /u01/app/oracle/oradata/orcl/users01.dbf
  9.         Tag: TAG20180626T201706

  10. 4       5    A 2018-06-26:20:16:50 1333986    2018-06-26:20:16:40
  11.         Name: +FRA/orcl/datafile/example.340.979849001
  12.         Tag: ORA_OEM_LEVEL_0


  13. RMAN>
复制代码

可以提前看一下1Z0-053第8章的解决方案:

  1. RMAN> report schema;

  2. Report of database schema for database with db_unique_name ORCL

  3. List of Permanent Datafiles
  4. ===========================
  5. File Size(MB) Tablespace           RB segs Datafile Name
  6. ---- -------- -------------------- ------- ------------------------
  7. 1    690      SYSTEM               ***     +DATA/orcl/datafile/system.268.979847755
  8. 2    570      SYSAUX               ***     +DATA/orcl/datafile/sysaux.267.979847755
  9. 3    290      UNDOTBS1             ***     +DATA/orcl/datafile/undotbs1.258.979847755
  10. 4    5        USERS                ***     +DATA/orcl/datafile/users.265.979847755
  11. 5    198      EXAMPLE              ***     +DATA/orcl/datafile/example.259.979847765
  12. 6    5        TBS1                 ***     +DATA/orcl/datafile/tbs1.256.979847755
  13. 7    5        TBS2                 ***     +DATA/orcl/datafile/tbs2.257.979847755

  14. List of Temporary Files
  15. =======================
  16. File Size(MB) Tablespace           Maxsize(MB) Tempfile Name
  17. ---- -------- -------------------- ----------- --------------------
  18. 1    30       TEMP                 32767       +DATA/orcl/tempfile/temp.261.979848279

复制代码

  1. RMAN> run {
  2. 2> allocate channel c1 device type disk format '/u01/app/oracle/oradata/orcl/system01.dbf';
  3. 3> allocate channel c2 device type disk format '/u01/app/oracle/oradata/orcl/sysaux01.dbf';
  4. 4> allocate channel c3 device type disk format '/u01/app/oracle/oradata/orcl/undotbs1.dbf';
  5. 5> allocate channel c4 device type disk format '/u01/app/oracle/oradata/orcl/users01.dbf';
  6. 6> allocate channel c5 device type disk format '/u01/app/oracle/oradata/orcl/example01.dbf';
  7. 7> allocate channel c6 device type disk format '/u01/app/oracle/oradata/orcl/tbs1.dbf';
  8. 8> allocate channel c7 device type disk format '/u01/app/oracle/oradata/orcl/tbs2.dbf';
  9. 9> backup as copy  (datafile 1 channel c1  )
  10.                                (datafile 2 channel c2)
  11.                                (datafile 3 channel c3)
  12.                                (datafile 4 channel c4)
  13.                                (datafile 5 channel c5)
  14.                                (datafile 6  channel c6)
  15.                                (datafile 7 channel c7) ;
  16. 10>}
复制代码
  1. [oracle@station86 ~]$ rman target /

  2. Recovery Manager: Release 11.2.0.1.0 - Production on Tue Jun 26 20:31:26 2018

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

  4. connected to target database: ORCL (DBID=1359978017)

  5. RMAN> list copy of datafile 1;

  6. using target database control file instead of recovery catalog
  7. List of Datafile Copies
  8. =======================

  9. Key     File S Completion Time     Ckp SCN    Ckp Time           
  10. ------- ---- - ------------------- ---------- -------------------
  11. 18      1    A 2018-06-26:20:31:08 1335940    2018-06-26:20:29:58
  12.         Name: /u01/app/oracle/oradata/orcl/system01.dbf
  13.         Tag: TAG20180626T202957


  14. RMAN> list copy of datafile 2;

  15. List of Datafile Copies
  16. =======================

  17. Key     File S Completion Time     Ckp SCN    Ckp Time           
  18. ------- ---- - ------------------- ---------- -------------------
  19. 16      2    A 2018-06-26:20:31:06 1335941    2018-06-26:20:29:58
  20.         Name: /u01/app/oracle/oradata/orcl/sysaux01.dbf
  21.         Tag: TAG20180626T202957


  22. RMAN> list copy of datafile 3;

  23. List of Datafile Copies
  24. =======================

  25. Key     File S Completion Time     Ckp SCN    Ckp Time           
  26. ------- ---- - ------------------- ---------- -------------------
  27. 19      3    A 2018-06-26:20:31:08 1335944    2018-06-26:20:29:59
  28.         Name: /u01/app/oracle/oradata/orcl/undotbs1.dbf
  29.         Tag: TAG20180626T202957


  30. RMAN> list copy of datafile 4;

  31. List of Datafile Copies
  32. =======================

  33. Key     File S Completion Time     Ckp SCN    Ckp Time           
  34. ------- ---- - ------------------- ---------- -------------------
  35. 15      4    A 2018-06-26:20:30:40 1335948    2018-06-26:20:30:02
  36.         Name: /u01/app/oracle/oradata/orcl/users01.dbf
  37.         Tag: TAG20180626T202957


  38. RMAN> list copy of datafile 5;

  39. List of Datafile Copies
  40. =======================

  41. Key     File S Completion Time     Ckp SCN    Ckp Time           
  42. ------- ---- - ------------------- ---------- -------------------
  43. 17      5    A 2018-06-26:20:31:08 1335950    2018-06-26:20:30:06
  44.         Name: /u01/app/oracle/oradata/orcl/example01.dbf
  45.         Tag: TAG20180626T202957


  46. RMAN> list copy of datafile 6;

  47. List of Datafile Copies
  48. =======================

  49. Key     File S Completion Time     Ckp SCN    Ckp Time           
  50. ------- ---- - ------------------- ---------- -------------------
  51. 20      6    A 2018-06-26:20:31:08 1335963    2018-06-26:20:30:16
  52.         Name: /u01/app/oracle/oradata/orcl/tbs1.dbf
  53.         Tag: TAG20180626T202957


  54. RMAN> list copy of datafile 7;

  55. List of Datafile Copies
  56. =======================

  57. Key     File S Completion Time     Ckp SCN    Ckp Time           
  58. ------- ---- - ------------------- ---------- -------------------
  59. 21      7    A 2018-06-26:20:31:08 1335966    2018-06-26:20:30:21
  60.         Name: /u01/app/oracle/oradata/orcl/tbs2.dbf
  61.         Tag: TAG20180626T202957


  62. RMAN> report schema;

  63. Report of database schema for database with db_unique_name ORCL

  64. List of Permanent Datafiles
  65. ===========================
  66. File Size(MB) Tablespace           RB segs Datafile Name
  67. ---- -------- -------------------- ------- ------------------------
  68. 1    690      SYSTEM               ***     +DATA/orcl/datafile/system.268.979847755
  69. 2    570      SYSAUX               ***     +DATA/orcl/datafile/sysaux.267.979847755
  70. 3    290      UNDOTBS1             ***     +DATA/orcl/datafile/undotbs1.258.979847755
  71. 4    5        USERS                ***     +DATA/orcl/datafile/users.265.979847755
  72. 5    198      EXAMPLE              ***     +DATA/orcl/datafile/example.259.979847765
  73. 6    5        TBS1                 ***     +DATA/orcl/datafile/tbs1.256.979847755
  74. 7    5        TBS2                 ***     +DATA/orcl/datafile/tbs2.257.979847755

  75. List of Temporary Files
  76. =======================
  77. File Size(MB) Tablespace           Maxsize(MB) Tempfile Name
  78. ---- -------- -------------------- ----------- --------------------
  79. 1    30       TEMP                 32767       +DATA/orcl/tempfile/temp.261.979848279

  80. RMAN>
复制代码
  1. RMAN> list failure;

  2. using target database control file instead of recovery catalog
  3. List of Database Failures
  4. =========================

  5. Failure ID Priority Status    Time Detected       Summary
  6. ---------- -------- --------- ------------------- -------
  7. 4268       HIGH     OPEN      2018-06-26:21:11:51 Tablespace 4: 'USERS' is offline

  8. RMAN> advise failure;

  9. List of Database Failures
  10. =========================

  11. Failure ID Priority Status    Time Detected       Summary
  12. ---------- -------- --------- ------------------- -------
  13. 4268       HIGH     OPEN      2018-06-26:21:11:51 Tablespace 4: 'USERS' is offline

  14. analyzing automatic repair options; this may take some time
  15. allocated channel: ORA_DISK_1
  16. channel ORA_DISK_1: SID=105 device type=DISK
  17. allocated channel: ORA_DISK_2
  18. channel ORA_DISK_2: SID=147 device type=DISK
  19. allocated channel: ORA_DISK_3
  20. channel ORA_DISK_3: SID=16 device type=DISK
  21. allocated channel: ORA_DISK_4
  22. channel ORA_DISK_4: SID=48 device type=DISK
  23. allocated channel: ORA_DISK_5
  24. channel ORA_DISK_5: SID=104 device type=DISK
  25. allocated channel: ORA_DISK_6
  26. channel ORA_DISK_6: SID=148 device type=DISK
  27. allocated channel: ORA_DISK_7
  28. channel ORA_DISK_7: SID=13 device type=DISK
  29. allocated channel: ORA_DISK_8
  30. channel ORA_DISK_8: SID=53 device type=DISK
  31. allocated channel: ORA_SBT_TAPE_1
  32. channel ORA_SBT_TAPE_1: SID=100 device type=SBT_TAPE
  33. channel ORA_SBT_TAPE_1: Oracle Secure Backup
  34. allocated channel: ORA_SBT_TAPE_2
  35. channel ORA_SBT_TAPE_2: SID=144 device type=SBT_TAPE
  36. channel ORA_SBT_TAPE_2: Oracle Secure Backup
  37. analyzing automatic repair options complete

  38. Mandatory Manual Actions
  39. ========================
  40. no manual actions available

  41. Optional Manual Actions
  42. =======================
  43. no manual actions available

  44. Automated Repair Options
  45. ========================
  46. Option Repair Description
  47. ------ ------------------
  48. 1      Online tablespace USERS  
  49.   Strategy: The repair includes complete media recovery with no data loss
  50.   Repair script: /u01/app/oracle/diag/rdbms/orcl/orcl/hm/reco_87746635.hm

  51. RMAN> repair failure preview;

  52. Strategy: The repair includes complete media recovery with no data loss
  53. Repair script: /u01/app/oracle/diag/rdbms/orcl/orcl/hm/reco_87746635.hm

  54. contents of repair script:
  55.    # online a offline tablespace
  56.    sql "begin sys.dbms_ir.execsqlscript(filename => ''/u01/app/oracle/diag/rdbms/orcl/orcl/hm/reco_2569490396.hm'' ); end;";

  57. RMAN> repair failure;

  58. Strategy: The repair includes complete media recovery with no data loss
  59. Repair script: /u01/app/oracle/diag/rdbms/orcl/orcl/hm/reco_87746635.hm

  60. contents of repair script:
  61.    # online a offline tablespace
  62.    sql "begin sys.dbms_ir.execsqlscript(filename => ''/u01/app/oracle/diag/rdbms/orcl/orcl/hm/reco_2569490396.hm'' ); end;";

  63. Do you really want to execute the above repair (enter YES or NO)? YES
  64. executing repair script

  65. sql statement: begin sys.dbms_ir.execsqlscript(filename => ''/u01/app/oracle/diag/rdbms/orcl/orcl/hm/reco_2569490396.hm'' ); end;
  66. repair failure complete

  67. RMAN> exit


  68. Recovery Manager complete.
  69. [oracle@station86 ~]$
复制代码
Screenshot.png


Screenshot-1.png

Screenshot-2.png







回复

使用道具 举报

您需要登录后才可以回帖 登录 | 立即注册

本版积分规则

QQ|手机版|Botang唐波's Oracle Station   

GMT+8, 2018-11-13 10:13 , Processed in 0.177969 second(s), 27 queries .

快速回复 返回顶部 返回列表