课程第15次（2018-06-28星期四）

botang · 发表于 2018-6-28 19:51:47

1. 创建recovery catalog（在192.168.0.90的rcat.example.com）：

select * from dba_Tablespaces;
create user u100 identified by oracle_4U
default tablespace rcat
quota unlimited on rcat;
grant recovery_catalog_owner to u100;

复制代码

2. 在station86上，只连接catalog，创建catalog，并且注册数据库：

[oracle@station86 admin]$ rman catalog u86/oracle_4U@rcat
Recovery Manager: Release 11.2.0.1.0 - Production on Thu Jun 28 19:49:11 2018
Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.
connected to recovery catalog atabase
RMAN>

复制代码

在station86上，同时连target和catalog：

[oracle@station86 admin]$ rman target / catalog u86/oracle_4U@rcat

复制代码

由于station86上的target(orcl.example.com）已经mount，所以会显示dbid：

Recovery Manager: Release 11.2.0.1.0 - Production on Thu Jun 28 19:56:13 2018
Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.
connected to target database: ORCL (DBID=1359978017)
connected to recovery catalog database
RMAN>

复制代码

注册数据库：

在target上看v$与在catalog上看rc_是一一对应的，只不过在rc_上看每一个备份集的id号都变大了些（为了保证注册库间唯一性）:

外脚本ttio.rcv：

run{
set newname for datafile 5 to '/home/oracle/examplettio.dbf';
restore datafile 5;
delete datafilecopy '/home/oracle/examplettio.dbf';
}

复制代码

运行外脚本：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>

[oracle@station86 ~]$ rman target / catalog u86/oracle_4U@rcat
Recovery Manager: Release 11.2.0.1.0 - Production on Thu Jun 28 20:17:50 2018
Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.
connected to target database: ORCL (DBID=1359978017)
connected to recovery catalog database
RMAN> create script ttio from file '/home/oracle/ttio.rcv';
script commands will be loaded from file /home/oracle/ttio.rcv
created script ttio

复制代码

RMAN> list script names
2> ;
List of Stored Scripts in Recovery Catalog
Scripts of Target Database ORCL
Script Name
Description
-----------------------------------------------------------------------
ttio
RMAN> print script ttio;
printing stored script: ttio
run{
set newname for datafile 5 to '/home/oracle/examplettio.dbf';
restore datafile 5;
delete datafilecopy '/home/oracle/examplettio.dbf';
}
RMAN> run { execute script ttio;}
executing script: ttio
executing command: SET NEWNAME
Starting restore at 2018-06-28:20:18:36
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=17 device type=DISK
allocated channel: ORA_DISK_2
channel ORA_DISK_2: SID=16 device type=DISK
allocated channel: ORA_DISK_3
channel ORA_DISK_3: SID=99 device type=DISK
allocated channel: ORA_DISK_4
channel ORA_DISK_4: SID=147 device type=DISK
allocated channel: ORA_DISK_5
channel ORA_DISK_5: SID=52 device type=DISK
allocated channel: ORA_DISK_6
channel ORA_DISK_6: SID=104 device type=DISK
allocated channel: ORA_DISK_7
channel ORA_DISK_7: SID=146 device type=DISK
allocated channel: ORA_DISK_8
channel ORA_DISK_8: SID=15 device type=DISK
allocated channel: ORA_SBT_TAPE_1
channel ORA_SBT_TAPE_1: SID=60 device type=SBT_TAPE
channel ORA_SBT_TAPE_1: Oracle Secure Backup
allocated channel: ORA_SBT_TAPE_2
channel ORA_SBT_TAPE_2: SID=148 device type=SBT_TAPE
channel ORA_SBT_TAPE_2: Oracle Secure Backup
channel ORA_DISK_1: restoring datafile 00005
input datafile copy RECID=25 STAMP=979984114 file name=+FRA/orcl/datafile/example.332.979984101
destination for restore of datafile 00005: /home/oracle/examplettio.dbf
channel ORA_DISK_1: copied datafile copy of datafile 00005
output file name=/home/oracle/examplettio.dbf RECID=30 STAMP=980021922
Finished restore at 2018-06-28:20:18:46
released channel: ORA_DISK_1
released channel: ORA_DISK_2
released channel: ORA_DISK_3
released channel: ORA_DISK_4
released channel: ORA_DISK_5
released channel: ORA_DISK_6
released channel: ORA_DISK_7
released channel: ORA_DISK_8
released channel: ORA_SBT_TAPE_1
released channel: ORA_SBT_TAPE_2
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=17 device type=DISK
allocated channel: ORA_DISK_2
channel ORA_DISK_2: SID=16 device type=DISK
allocated channel: ORA_DISK_3
channel ORA_DISK_3: SID=99 device type=DISK
allocated channel: ORA_DISK_4
channel ORA_DISK_4: SID=147 device type=DISK
allocated channel: ORA_DISK_5
channel ORA_DISK_5: SID=52 device type=DISK
allocated channel: ORA_DISK_6
channel ORA_DISK_6: SID=104 device type=DISK
allocated channel: ORA_DISK_7
channel ORA_DISK_7: SID=146 device type=DISK
allocated channel: ORA_DISK_8
channel ORA_DISK_8: SID=15 device type=DISK
List of Datafile Copies
=======================
Key File S Completion Time Ckp SCN Ckp Time
------- ---- - ------------------- ---------- -------------------
235 5 A 2018-06-28:20:18:42 1371651 2018-06-28:09:48:21
Name: /home/oracle/examplettio.dbf
Tag: ORA_OEM_LEVEL_0
Do you really want to delete the above objects (enter YES or NO)? YES
deleted datafile copy
datafile copy file name=/home/oracle/examplettio.dbf RECID=30 STAMP=980021922
Deleted 1 objects
RMAN>

复制代码

备份recovey catalog：

[oracle@station90 rcat]$ expdp system/oracle_4U@rcat dumpfile=u86.dmp schemas=u86

复制代码

恢复recovery catalog：

[oracle@station90 rcat]$ impdp system/oracle_4U@rcat dumpfile=u86.dmp
Import: Release 11.2.0.3.0 - Production on Thu Jun 28 20:27:39 2018
UDI-28002: operation generated ORACLE error 28002
ORA-28002: the password will expire within 7 days
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Master table "SYSTEM"."SYS_IMPORT_FULL_01" successfully loaded/unloaded
Starting "SYSTEM"."SYS_IMPORT_FULL_01": system/********@rcat dumpfile=u86.dmp
Processing object type SCHEMA_EXPORT/USER
Processing object type SCHEMA_EXPORT/ROLE_GRANT
Processing object type SCHEMA_EXPORT/DEFAULT_ROLE
Processing object type SCHEMA_EXPORT/TABLESPACE_QUOTA
Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
Processing object type SCHEMA_EXPORT/TYPE/TYPE_SPEC
Processing object type SCHEMA_EXPORT/SEQUENCE/SEQUENCE
Processing object type SCHEMA_EXPORT/SEQUENCE/GRANT/OWNER_GRANT/OBJECT_GRANT
Processing object type SCHEMA_EXPORT/TABLE/TABLE
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
. . imported "U86"."CFS" 89.82 KB 157 rows
. . imported "U86"."AL" 17.48 KB 16 rows
. . imported "U86"."BCF" 10.92 KB 10 rows
. . imported "U86"."BDF" 17.09 KB 7 rows
. . imported "U86"."BP" 18.09 KB 18 rows
. . imported "U86"."BRL" 11.41 KB 18 rows
. . imported "U86"."BS" 13.57 KB 18 rows
. . imported "U86"."BSF" 8.195 KB 10 rows
. . imported "U86"."CDF" 20.89 KB 14 rows
. . imported "U86"."CKP" 8.765 KB 3 rows
. . imported "U86"."CONF" 8.023 KB 9 rows
. . imported "U86"."CONFIG" 5.421 KB 1 rows
. . imported "U86"."DB" 5.820 KB 1 rows
. . imported "U86"."DBINC" 7.437 KB 1 rows
. . imported "U86"."DF" 15.18 KB 7 rows
. . imported "U86"."NODE" 20.59 KB 1 rows
. . imported "U86"."ORL" 7.796 KB 6 rows
. . imported "U86"."RCVER" 5.015 KB 1 rows
. . imported "U86"."RLH" 9.390 KB 18 rows
. . imported "U86"."ROUT" 27.67 KB 328 rows
. . imported "U86"."RSR" 24.92 KB 85 rows
. . imported "U86"."RT" 7.820 KB 1 rows
. . imported "U86"."SCR" 6.218 KB 1 rows
. . imported "U86"."SCRL" 6.398 KB 5 rows
. . imported "U86"."SITE_DFATT" 6.156 KB 7 rows
. . imported "U86"."SITE_TFATT" 8.257 KB 1 rows
. . imported "U86"."TF" 8.226 KB 1 rows
. . imported "U86"."TS" 9.343 KB 8 rows
. . imported "U86"."TSATT" 7.164 KB 8 rows
. . imported "U86"."BCB" 0 KB 0 rows
. . imported "U86"."BCR" 0 KB 0 rows
. . imported "U86"."CCB" 0 KB 0 rows
. . imported "U86"."CCF" 0 KB 0 rows
. . imported "U86"."FB" 0 KB 0 rows
. . imported "U86"."GRSP" 0 KB 0 rows
. . imported "U86"."NRSP" 0 KB 0 rows
. . imported "U86"."OFFR" 0 KB 0 rows
. . imported "U86"."RR" 0 KB 0 rows
. . imported "U86"."TEMPRES" 0 KB 0 rows
. . imported "U86"."VPC_DATABASES" 0 KB 0 rows
. . imported "U86"."VPC_USERS" 0 KB 0 rows
. . imported "U86"."XAL" 0 KB 0 rows
. . imported "U86"."XCF" 0 KB 0 rows
. . imported "U86"."XDF" 0 KB 0 rows
Processing object type SCHEMA_EXPORT/PACKAGE/PACKAGE_SPEC
Processing object type SCHEMA_EXPORT/PACKAGE/GRANT/OWNER_GRANT/OBJECT_GRANT
Processing object type SCHEMA_EXPORT/FUNCTION/FUNCTION
Processing object type SCHEMA_EXPORT/FUNCTION/GRANT/OWNER_GRANT/OBJECT_GRANT
Processing object type SCHEMA_EXPORT/PACKAGE/COMPILE_PACKAGE/PACKAGE_SPEC/ALTER_PACKAGE_SPEC
Processing object type SCHEMA_EXPORT/FUNCTION/ALTER_FUNCTION
Processing object type SCHEMA_EXPORT/TABLE/INDEX/INDEX
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type SCHEMA_EXPORT/VIEW/VIEW
Processing object type SCHEMA_EXPORT/VIEW/GRANT/OWNER_GRANT/OBJECT_GRANT
Processing object type SCHEMA_EXPORT/PACKAGE/PACKAGE_BODY
Processing object type SCHEMA_EXPORT/TYPE/TYPE_BODY
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/REF_CONSTRAINT
Processing object type SCHEMA_EXPORT/TABLE/TRIGGER
Job "SYSTEM"."SYS_IMPORT_FULL_01" successfully completed at 20:28:02
[oracle@station90 rcat]$

复制代码

定位RMAN的会话和通道：

SELECT sid,OPNAME, CONTEXT, SOFAR, TOTALWORK,
ROUND(SOFAR/TOTALWORK*100,2) "%_COMPLETE"
FROM V$SESSION_LONGOPS
WHERE OPNAME LIKE 'RMAN%'
AND OPNAME NOT LIKE '%aggregate%'
AND TOTALWORK != 0
AND SOFAR <> TOTALWORK
and sid in (select sid from v$session where terminal =
(select s.TERMINAL from v_$session s
where s.CLIENT_INFO like '%apple%'));

复制代码

RMAN> run {
2> set command id to 'apple';
3> backup tag 'apple_1' database;
4> }
executing command: SET COMMAND ID
Starting backup at 2018-06-28:21:22:47
allocated channel: ORA_SBT_TAPE_1
channel ORA_SBT_TAPE_1: SID=13 device type=SBT_TAPE
channel ORA_SBT_TAPE_1: Oracle Secure Backup
allocated channel: ORA_SBT_TAPE_2
channel ORA_SBT_TAPE_2: SID=18 device type=SBT_TAPE
channel ORA_SBT_TAPE_2: Oracle Secure Backup
channel ORA_SBT_TAPE_1: starting full datafile backup set
channel ORA_SBT_TAPE_1: specifying datafile(s) in backup set
input datafile file number=00002 name=+DATA/orcl/datafile/sysaux.267.979847755
input datafile file number=00003 name=+DATA/orcl/datafile/undotbs1.258.979847755
input datafile file number=00005 name=+DATA/orcl/datafile/example.259.979847765
channel ORA_SBT_TAPE_1: starting piece 1 at 2018-06-28:21:22:47
channel ORA_SBT_TAPE_2: starting full datafile backup set
channel ORA_SBT_TAPE_2: specifying datafile(s) in backup set
input datafile file number=00001 name=+DATA/orcl/datafile/system.268.979853699
input datafile file number=00004 name=+DATA/orcl/datafile/users.265.979852989
input datafile file number=00006 name=+DATA/orcl/datafile/tbs1.256.979847755
input datafile file number=00007 name=+DATA/orcl/datafile/tbs2.257.979847755
channel ORA_SBT_TAPE_2: starting piece 1 at 2018-06-28:21:22:48
channel ORA_SBT_TAPE_1: finished piece 1 at 2018-06-28:21:24:43
piece handle=25t6k0d7_1_1 tag=APPLE_1 comment=API Version 2.0,MMS Version 10.3.0.2
channel ORA_SBT_TAPE_1: backup set complete, elapsed time: 00:01:56
channel ORA_SBT_TAPE_2: finished piece 1 at 2018-06-28:21:24:43
piece handle=26t6k0d7_1_1 tag=APPLE_1 comment=API Version 2.0,MMS Version 10.3.0.2
channel ORA_SBT_TAPE_2: backup set complete, elapsed time: 00:01:55
Finished backup at 2018-06-28:21:24:43
Starting Control File and SPFILE Autobackup at 2018-06-28:21:24:43

复制代码

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