设为首页收藏本站

Botang唐波's Oracle Station

查看: 250|回复: 0

课程第15次(2018-06-28星期四)

[复制链接]

744

主题

1117

帖子

8077

积分

管理员

Rank: 9Rank: 9Rank: 9

积分
8077
发表于 2018-6-28 19:51:47 | 显示全部楼层 |阅读模式
1. 创建recovery catalog(在192.168.0.90的rcat.example.com):
  1. select  * from dba_Tablespaces;

  2. create user u100 identified by oracle_4U
  3. default tablespace rcat
  4. quota unlimited on rcat;

  5. grant recovery_catalog_owner to u100;
复制代码
2. 在station86上,只连接catalog,创建catalog,并且注册数据库:
  1. [oracle@station86 admin]$ rman  catalog u86/oracle_4U@rcat

  2. Recovery Manager: Release 11.2.0.1.0 - Production on Thu Jun 28 19:49:11 2018

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

  4. connected to recovery catalog atabase

  5. RMAN>
复制代码
Screenshot.png

在station86上,同时连target和catalog:
  1. [oracle@station86 admin]$ rman  target / catalog u86/oracle_4U@rcat
复制代码
由于station86上的target(orcl.example.com)已经mount,所以会显示dbid:
  1. Recovery Manager: Release 11.2.0.1.0 - Production on Thu Jun 28 19:56:13 2018

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

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

  5. RMAN>
复制代码
注册数据库:
Screenshot-1.png

在target上看v$与在catalog上看rc_是一一对应的,只不过在rc_上看每一个备份集的id号都变大了些(为了保证注册库间唯一性):
Screenshot-2.png


外脚本ttio.rcv:
  1. run{
  2. set newname for datafile 5 to '/home/oracle/examplettio.dbf';
  3. restore datafile 5;
  4. delete datafilecopy '/home/oracle/examplettio.dbf';
  5. }
复制代码
运行外脚本:rman target / cmdfile=ttio.rcv log=ttio.log

变成内脚本,需要catalog:
[oracle@station86 ~]$ rman target /   

Recovery Manager: Release 11.2.0.1.0 - Production on Thu Jun 28 20:15:36 2018

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

connected to target database: ORCL (DBID=1359978017)

RMAN> create script ttio from file '/home/oracle/ttio.rcv';

using target database control file instead of recovery catalog
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of create script command at 06/28/2018 20:16:21
RMAN-06002: command not allowed when not connected to a recovery catalog

RMAN>
  1. [oracle@station86 ~]$ rman target /   catalog u86/oracle_4U@rcat

  2. Recovery Manager: Release 11.2.0.1.0 - Production on Thu Jun 28 20:17:50 2018

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

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

  6. RMAN> create script ttio from file '/home/oracle/ttio.rcv';

  7. script commands will be loaded from file /home/oracle/ttio.rcv
  8. created script ttio
复制代码
  1. RMAN> list script names
  2. 2> ;

  3. List of Stored Scripts in Recovery Catalog


  4.     Scripts of Target Database ORCL

  5.        Script Name
  6.        Description
  7.        -----------------------------------------------------------------------
  8.        ttio

  9. RMAN> print script ttio;

  10. printing stored script: ttio
  11. run{
  12. set newname for datafile 5 to '/home/oracle/examplettio.dbf';
  13. restore datafile 5;
  14. delete datafilecopy '/home/oracle/examplettio.dbf';
  15. }

  16. RMAN> run { execute script ttio;}

  17. executing script: ttio

  18. executing command: SET NEWNAME

  19. Starting restore at 2018-06-28:20:18:36
  20. allocated channel: ORA_DISK_1
  21. channel ORA_DISK_1: SID=17 device type=DISK
  22. allocated channel: ORA_DISK_2
  23. channel ORA_DISK_2: SID=16 device type=DISK
  24. allocated channel: ORA_DISK_3
  25. channel ORA_DISK_3: SID=99 device type=DISK
  26. allocated channel: ORA_DISK_4
  27. channel ORA_DISK_4: SID=147 device type=DISK
  28. allocated channel: ORA_DISK_5
  29. channel ORA_DISK_5: SID=52 device type=DISK
  30. allocated channel: ORA_DISK_6
  31. channel ORA_DISK_6: SID=104 device type=DISK
  32. allocated channel: ORA_DISK_7
  33. channel ORA_DISK_7: SID=146 device type=DISK
  34. allocated channel: ORA_DISK_8
  35. channel ORA_DISK_8: SID=15 device type=DISK
  36. allocated channel: ORA_SBT_TAPE_1
  37. channel ORA_SBT_TAPE_1: SID=60 device type=SBT_TAPE
  38. channel ORA_SBT_TAPE_1: Oracle Secure Backup
  39. allocated channel: ORA_SBT_TAPE_2
  40. channel ORA_SBT_TAPE_2: SID=148 device type=SBT_TAPE
  41. channel ORA_SBT_TAPE_2: Oracle Secure Backup

  42. channel ORA_DISK_1: restoring datafile 00005
  43. input datafile copy RECID=25 STAMP=979984114 file name=+FRA/orcl/datafile/example.332.979984101
  44. destination for restore of datafile 00005: /home/oracle/examplettio.dbf
  45. channel ORA_DISK_1: copied datafile copy of datafile 00005
  46. output file name=/home/oracle/examplettio.dbf RECID=30 STAMP=980021922
  47. Finished restore at 2018-06-28:20:18:46

  48. released channel: ORA_DISK_1
  49. released channel: ORA_DISK_2
  50. released channel: ORA_DISK_3
  51. released channel: ORA_DISK_4
  52. released channel: ORA_DISK_5
  53. released channel: ORA_DISK_6
  54. released channel: ORA_DISK_7
  55. released channel: ORA_DISK_8
  56. released channel: ORA_SBT_TAPE_1
  57. released channel: ORA_SBT_TAPE_2
  58. allocated channel: ORA_DISK_1
  59. channel ORA_DISK_1: SID=17 device type=DISK
  60. allocated channel: ORA_DISK_2
  61. channel ORA_DISK_2: SID=16 device type=DISK
  62. allocated channel: ORA_DISK_3
  63. channel ORA_DISK_3: SID=99 device type=DISK
  64. allocated channel: ORA_DISK_4
  65. channel ORA_DISK_4: SID=147 device type=DISK
  66. allocated channel: ORA_DISK_5
  67. channel ORA_DISK_5: SID=52 device type=DISK
  68. allocated channel: ORA_DISK_6
  69. channel ORA_DISK_6: SID=104 device type=DISK
  70. allocated channel: ORA_DISK_7
  71. channel ORA_DISK_7: SID=146 device type=DISK
  72. allocated channel: ORA_DISK_8
  73. channel ORA_DISK_8: SID=15 device type=DISK
  74. List of Datafile Copies
  75. =======================

  76. Key     File S Completion Time     Ckp SCN    Ckp Time           
  77. ------- ---- - ------------------- ---------- -------------------
  78. 235     5    A 2018-06-28:20:18:42 1371651    2018-06-28:09:48:21
  79.         Name: /home/oracle/examplettio.dbf
  80.         Tag: ORA_OEM_LEVEL_0


  81. Do you really want to delete the above objects (enter YES or NO)? YES
  82. deleted datafile copy
  83. datafile copy file name=/home/oracle/examplettio.dbf RECID=30 STAMP=980021922
  84. Deleted 1 objects


  85. RMAN>
复制代码

备份recovey catalog:

  1. [oracle@station90 rcat]$ expdp system/oracle_4U@rcat  dumpfile=u86.dmp schemas=u86
复制代码


恢复recovery catalog:
  1. [oracle@station90 rcat]$ impdp system/oracle_4U@rcat dumpfile=u86.dmp

  2. Import: Release 11.2.0.3.0 - Production on Thu Jun 28 20:27:39 2018

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

  4. UDI-28002: operation generated ORACLE error 28002
  5. ORA-28002: the password will expire within 7 days

  6. Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
  7. With the Partitioning, OLAP, Data Mining and Real Application Testing options
  8. Master table "SYSTEM"."SYS_IMPORT_FULL_01" successfully loaded/unloaded
  9. Starting "SYSTEM"."SYS_IMPORT_FULL_01":  system/********@rcat dumpfile=u86.dmp
  10. Processing object type SCHEMA_EXPORT/USER
  11. Processing object type SCHEMA_EXPORT/ROLE_GRANT
  12. Processing object type SCHEMA_EXPORT/DEFAULT_ROLE
  13. Processing object type SCHEMA_EXPORT/TABLESPACE_QUOTA
  14. Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
  15. Processing object type SCHEMA_EXPORT/TYPE/TYPE_SPEC
  16. Processing object type SCHEMA_EXPORT/SEQUENCE/SEQUENCE
  17. Processing object type SCHEMA_EXPORT/SEQUENCE/GRANT/OWNER_GRANT/OBJECT_GRANT
  18. Processing object type SCHEMA_EXPORT/TABLE/TABLE
  19. Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
  20. . . imported "U86"."CFS"                                 89.82 KB     157 rows
  21. . . imported "U86"."AL"                                  17.48 KB      16 rows
  22. . . imported "U86"."BCF"                                 10.92 KB      10 rows
  23. . . imported "U86"."BDF"                                 17.09 KB       7 rows
  24. . . imported "U86"."BP"                                  18.09 KB      18 rows
  25. . . imported "U86"."BRL"                                 11.41 KB      18 rows
  26. . . imported "U86"."BS"                                  13.57 KB      18 rows
  27. . . imported "U86"."BSF"                                 8.195 KB      10 rows
  28. . . imported "U86"."CDF"                                 20.89 KB      14 rows
  29. . . imported "U86"."CKP"                                 8.765 KB       3 rows
  30. . . imported "U86"."CONF"                                8.023 KB       9 rows
  31. . . imported "U86"."CONFIG"                              5.421 KB       1 rows
  32. . . imported "U86"."DB"                                  5.820 KB       1 rows
  33. . . imported "U86"."DBINC"                               7.437 KB       1 rows
  34. . . imported "U86"."DF"                                  15.18 KB       7 rows
  35. . . imported "U86"."NODE"                                20.59 KB       1 rows
  36. . . imported "U86"."ORL"                                 7.796 KB       6 rows
  37. . . imported "U86"."RCVER"                               5.015 KB       1 rows
  38. . . imported "U86"."RLH"                                 9.390 KB      18 rows
  39. . . imported "U86"."ROUT"                                27.67 KB     328 rows
  40. . . imported "U86"."RSR"                                 24.92 KB      85 rows
  41. . . imported "U86"."RT"                                  7.820 KB       1 rows
  42. . . imported "U86"."SCR"                                 6.218 KB       1 rows
  43. . . imported "U86"."SCRL"                                6.398 KB       5 rows
  44. . . imported "U86"."SITE_DFATT"                          6.156 KB       7 rows
  45. . . imported "U86"."SITE_TFATT"                          8.257 KB       1 rows
  46. . . imported "U86"."TF"                                  8.226 KB       1 rows
  47. . . imported "U86"."TS"                                  9.343 KB       8 rows
  48. . . imported "U86"."TSATT"                               7.164 KB       8 rows
  49. . . imported "U86"."BCB"                                     0 KB       0 rows
  50. . . imported "U86"."BCR"                                     0 KB       0 rows
  51. . . imported "U86"."CCB"                                     0 KB       0 rows
  52. . . imported "U86"."CCF"                                     0 KB       0 rows
  53. . . imported "U86"."FB"                                      0 KB       0 rows
  54. . . imported "U86"."GRSP"                                    0 KB       0 rows
  55. . . imported "U86"."NRSP"                                    0 KB       0 rows
  56. . . imported "U86"."OFFR"                                    0 KB       0 rows
  57. . . imported "U86"."RR"                                      0 KB       0 rows
  58. . . imported "U86"."TEMPRES"                                 0 KB       0 rows
  59. . . imported "U86"."VPC_DATABASES"                           0 KB       0 rows
  60. . . imported "U86"."VPC_USERS"                               0 KB       0 rows
  61. . . imported "U86"."XAL"                                     0 KB       0 rows
  62. . . imported "U86"."XCF"                                     0 KB       0 rows
  63. . . imported "U86"."XDF"                                     0 KB       0 rows
  64. Processing object type SCHEMA_EXPORT/PACKAGE/PACKAGE_SPEC
  65. Processing object type SCHEMA_EXPORT/PACKAGE/GRANT/OWNER_GRANT/OBJECT_GRANT
  66. Processing object type SCHEMA_EXPORT/FUNCTION/FUNCTION
  67. Processing object type SCHEMA_EXPORT/FUNCTION/GRANT/OWNER_GRANT/OBJECT_GRANT
  68. Processing object type SCHEMA_EXPORT/PACKAGE/COMPILE_PACKAGE/PACKAGE_SPEC/ALTER_PACKAGE_SPEC
  69. Processing object type SCHEMA_EXPORT/FUNCTION/ALTER_FUNCTION
  70. Processing object type SCHEMA_EXPORT/TABLE/INDEX/INDEX
  71. Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
  72. Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
  73. Processing object type SCHEMA_EXPORT/VIEW/VIEW
  74. Processing object type SCHEMA_EXPORT/VIEW/GRANT/OWNER_GRANT/OBJECT_GRANT
  75. Processing object type SCHEMA_EXPORT/PACKAGE/PACKAGE_BODY
  76. Processing object type SCHEMA_EXPORT/TYPE/TYPE_BODY
  77. Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/REF_CONSTRAINT
  78. Processing object type SCHEMA_EXPORT/TABLE/TRIGGER
  79. Job "SYSTEM"."SYS_IMPORT_FULL_01" successfully completed at 20:28:02

  80. [oracle@station90 rcat]$
复制代码

定位RMAN的会话和通道:
  1. SELECT sid,OPNAME, CONTEXT, SOFAR, TOTALWORK,
  2. ROUND(SOFAR/TOTALWORK*100,2) "%_COMPLETE"
  3. FROM V$SESSION_LONGOPS
  4. WHERE OPNAME LIKE 'RMAN%'
  5. AND OPNAME NOT LIKE '%aggregate%'
  6. AND TOTALWORK != 0
  7. AND SOFAR <> TOTALWORK

  8. and sid in (select  sid from v$session where terminal =
  9. (select  s.TERMINAL  from v_$session  s
  10. where s.CLIENT_INFO like '%apple%'));
复制代码
  1. RMAN> run {
  2. 2> set command id to 'apple';
  3. 3> backup tag 'apple_1'  database;  
  4. 4> }

  5. executing command: SET COMMAND ID

  6. Starting backup at 2018-06-28:21:22:47
  7. allocated channel: ORA_SBT_TAPE_1
  8. channel ORA_SBT_TAPE_1: SID=13 device type=SBT_TAPE
  9. channel ORA_SBT_TAPE_1: Oracle Secure Backup
  10. allocated channel: ORA_SBT_TAPE_2
  11. channel ORA_SBT_TAPE_2: SID=18 device type=SBT_TAPE
  12. channel ORA_SBT_TAPE_2: Oracle Secure Backup
  13. channel ORA_SBT_TAPE_1: starting full datafile backup set
  14. channel ORA_SBT_TAPE_1: specifying datafile(s) in backup set
  15. input datafile file number=00002 name=+DATA/orcl/datafile/sysaux.267.979847755
  16. input datafile file number=00003 name=+DATA/orcl/datafile/undotbs1.258.979847755
  17. input datafile file number=00005 name=+DATA/orcl/datafile/example.259.979847765
  18. channel ORA_SBT_TAPE_1: starting piece 1 at 2018-06-28:21:22:47
  19. channel ORA_SBT_TAPE_2: starting full datafile backup set
  20. channel ORA_SBT_TAPE_2: specifying datafile(s) in backup set
  21. input datafile file number=00001 name=+DATA/orcl/datafile/system.268.979853699
  22. input datafile file number=00004 name=+DATA/orcl/datafile/users.265.979852989
  23. input datafile file number=00006 name=+DATA/orcl/datafile/tbs1.256.979847755
  24. input datafile file number=00007 name=+DATA/orcl/datafile/tbs2.257.979847755
  25. channel ORA_SBT_TAPE_2: starting piece 1 at 2018-06-28:21:22:48
  26. channel ORA_SBT_TAPE_1: finished piece 1 at 2018-06-28:21:24:43
  27. piece handle=25t6k0d7_1_1 tag=APPLE_1 comment=API Version 2.0,MMS Version 10.3.0.2
  28. channel ORA_SBT_TAPE_1: backup set complete, elapsed time: 00:01:56
  29. channel ORA_SBT_TAPE_2: finished piece 1 at 2018-06-28:21:24:43
  30. piece handle=26t6k0d7_1_1 tag=APPLE_1 comment=API Version 2.0,MMS Version 10.3.0.2
  31. channel ORA_SBT_TAPE_2: backup set complete, elapsed time: 00:01:55
  32. Finished backup at 2018-06-28:21:24:43

  33. Starting Control File and SPFILE Autobackup at 2018-06-28:21:24:43

复制代码

sqlplus.png


------------------------------------------------------------------------
保留策略(假设为24h)管3件事情:
1. 24h为标准的report obsolete
2. 24h+日期变更线为标准的report need backup
3. 24h-日期变更线为标准的备份优化

Screenshot.png



回复

使用道具 举报

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

本版积分规则

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

GMT+8, 2018-11-18 01:23 , Processed in 0.171839 second(s), 27 queries .

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