Bo's Oracle Station

查看: 2735|回复: 1

剩余课程

[复制链接]

75

主题

115

帖子

2688

积分

管理员

Rank: 9Rank: 9Rank: 9

积分
2688
发表于 2018-11-20 20:53:28 | 显示全部楼层 |阅读模式
课程第17次(2018-11-20星期二)
  1. [oracle@station90 ~]$ mkdir second_arc
  2. [oracle@station90 ~]$ sqlplus /nolog

  3. SQL*Plus: Release 11.2.0.3.0 Production on Tue Nov 20 20:37:55 2018

  4. Copyright (c) 1982, 2011, Oracle.  All rights reserved.

  5. SQL> conn / as sysdba
  6. Connected.
  7. SQL> show parameter log_Archive_dest_1

  8. NAME                                     TYPE         VALUE
  9. ------------------------------------ ----------- ------------------------------
  10. log_archive_dest_1                     string
  11. log_archive_dest_10                     string
  12. log_archive_dest_11                     string
  13. log_archive_dest_12                     string
  14. log_archive_dest_13                     string
  15. log_archive_dest_14                     string
  16. log_archive_dest_15                     string
  17. log_archive_dest_16                     string
  18. log_archive_dest_17                     string
  19. log_archive_dest_18                     string
  20. log_archive_dest_19                     string
  21. SQL> alter system set  log_Archive_dest_2='location=/home/oracle/second_arch' ;

  22. System altered.

  23. SQL> alter system set log_archive_dest_1='location=use_db_recovery_file_dest';

  24. System altered.

  25. SQL> show parameter log_archive_format

  26. NAME                                     TYPE         VALUE
  27. ------------------------------------ ----------- ------------------------------
  28. log_archive_format                     string         %t_%s_%r.dbf
  29. SQL>  alter system set log_archive_dest_1='';

  30. System altered.

  31. SQL> alter system set  log_Archive_dest_2='';

  32. System altered.

  33. SQL> alter system switch logfile;

  34. System altered.

  35. SQL> alter system set log_archive_dest_1='location=use_db_recovery_file_dest';

  36. System altered.

  37. SQL> alter system switch logfile;

  38. System altered.

  39. SQL> exit
复制代码
1Z0-052第14章(16/40)

课程第18次(2018-11-21星期三)
关于第4号实验:
  1. [oracle@station90 ~]$ sqlplus /nolog

  2. SQL*Plus: Release 11.2.0.3.0 Production on Wed Nov 21 19:32:51 2018

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

  4. SQL> conn / as sysdba
  5. Connected.
  6. SQL> alter database create datafile '+DATA/orcl/datafile/tbsocp05_test.267.992805849' as new ;

  7. Database altered.
复制代码

关于第1d号实验:
  1. SQL> conn  / as sysdba
  2. Connected to an idle instance.
  3. SQL> startup
  4. ORACLE instance started.

  5. Total System Global Area 6664212480 bytes
  6. Fixed Size                    2240944 bytes
  7. Variable Size                 3707768400 bytes
  8. Database Buffers         2936012800 bytes
  9. Redo Buffers                   18190336 bytes
  10. Database mounted.
  11. ORA-10873: file 4 needs to be either taken out of backup mode or media
  12. recovered
  13. ORA-01110: data file 4: '+DATA/orcl/datafile/users.259.992728183'


  14. SQL> select  * from v$backup;

  15.      FILE# STATUS                 CHANGE# TIME
  16. ---------- ------------------ ---------- ------------------
  17.          1 NOT ACTIVE                       0
  18.          2 NOT ACTIVE                       0
  19.          3 NOT ACTIVE                       0
  20.          4 ACTIVE                 2681584 21-NOV-18
  21.          5 NOT ACTIVE                       0
  22.          7 NOT ACTIVE                       0

  23. 6 rows selected.

  24. SQL> alter  database end backup;

  25. Database altered.

  26. SQL> select  * from v$backup;

  27.      FILE# STATUS                 CHANGE# TIME
  28. ---------- ------------------ ---------- ------------------
  29.          1 NOT ACTIVE                       0
  30.          2 NOT ACTIVE                       0
  31.          3 NOT ACTIVE                       0
  32.          4 NOT ACTIVE                 2681584 21-NOV-18
  33.          5 NOT ACTIVE                       0
  34.          7 NOT ACTIVE                       0

  35. 6 rows selected.

  36. SQL> alter database open;

  37. Database altered.

  38. SQL>
复制代码
在ASM磁盘组中,某个文件夹中的文件时间排序:
  1. thread_1_seq_166.283.992805855
  2. thread_1_seq_167.285.992807497
  3. thread_1_seq_168.286.992807497
  4. thread_1_seq_169.287.992807497
  5. thread_1_seq_170.288.992807497
  6. thread_1_seq_171.289.992807499
  7. thread_1_seq_172.290.992807499
  8. thread_1_seq_173.291.992807741
  9. thread_1_seq_174.292.992808351
  10. thread_1_seq_175.293.992808353
  11. thread_1_seq_176.294.992808353
  12. thread_1_seq_177.295.992808355
  13. thread_1_seq_178.296.992808359
  14. thread_1_seq_179.297.992808359
  15. thread_1_seq_1.299.992809501
  16. thread_1_seq_2.300.992809503
  17. thread_1_seq_3.303.992809503
  18. thread_1_seq_4.304.992809507
  19. thread_1_seq_5.305.992809507
  20. thread_1_seq_6.307.992809507
  21. thread_1_seq_7.308.992809507
  22. thread_1_seq_8.309.992809509
  23. thread_1_seq_10.311.992809513
  24. thread_1_seq_11.312.992809513
  25. thread_1_seq_9.310.992809513
  26. thread_1_seq_12.313.992809515
  27. thread_1_seq_13.314.992809517
  28. thread_1_seq_14.315.992809517
  29. thread_1_seq_15.316.992809517
  30. thread_1_seq_16.317.992809533
  31. thread_1_seq_17.318.992809535
  32. thread_1_seq_18.319.992809535
  33. thread_1_seq_19.320.992809537
  34. thread_1_seq_20.321.992809539
  35. [oracle@station90 ~]$ asmcmd ls +fra/orcl/ARCHIVELOG/2018_11_21 | sort -n -t . -k 3
复制代码
  1. [oracle@station90 ~]$ sqlplus /nolog

  2. SQL*Plus: Release 11.2.0.3.0 Production on Wed Nov 21 20:37:07 2018

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

  4. SQL> conn / as sysdba
  5. Connected.
  6. SQL> recover database until cancel;
  7. ORA-00279: change 2706558 generated at 11/21/2018 20:25:39 needed for thread 1
  8. ORA-00289: suggestion : +FRA
  9. ORA-00280: change 2706558 for thread 1 is in sequence #21


  10. Specify log: {<RET>=suggested | filename | AUTO | CANCEL}

  11. ORA-00308: cannot open archived log '+FRA'
  12. ORA-17503: ksfdopn:2 Failed to open file +FRA
  13. ORA-15045: ASM file name '+FRA' is not in reference form


  14. ORA-01547: warning: RECOVER succeeded but OPEN RESETLOGS would get error below
  15. ORA-01194: file 1 needs more recovery to be consistent
  16. ORA-01110: data file 1: '+DATA/orcl/datafile/system.256.816169553'


  17. SQL> recover database until cancel;
  18. ORA-00279: change 2706558 generated at 11/21/2018 20:25:39 needed for thread 1
  19. ORA-00289: suggestion : +FRA
  20. ORA-00280: change 2706558 for thread 1 is in sequence #21


  21. Specify log: {<RET>=suggested | filename | AUTO | CANCEL}

  22. ORA-01547: warning: RECOVER succeeded but OPEN RESETLOGS would get error below
  23. ORA-01194: file 1 needs more recovery to be consistent
  24. ORA-01110: data file 1: '+DATA/orcl/datafile/system.256.816169553'



  25. SQL>
  26. SQL>
  27. SQL> exit
  28. Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
  29. With the Partitioning, Automatic Storage Management, OLAP, Data Mining
  30. and Real Application Testing options
  31. [oracle@station90 ~]$ rman target /

  32. Recovery Manager: Release 11.2.0.3.0 - Production on Wed Nov 21 20:38:13 2018

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

  34. connected to target database: ORCL (DBID=1343950367, not open)

  35. RMAN> run {
  36. 2> set until sequence 21 thread 1;
  37. 3> restore database;
  38. 4> recover database;
  39. 5> }
复制代码
1Z0-052第4章(17/40)

课程第19次(2018-11-22星期四)
  1. select count(*)  from dba_tables t
  2. where t.tablespace_name='SYSAUX';

  3. select count(*)  from dba_tables t
  4. where t.tablespace_name='SYSAUX'  and t.owner='SYS' ;

  5. select count(*)  from dba_tables t
  6. where t.tablespace_name='SYSAUX'  and t.owner='SYSMAN';

  7. --AWR
  8. select count(*)  from dba_tables t
  9. where t.tablespace_name='SYSAUX'  and t.owner='SYS'
  10. and t.table_name like 'WR_$%';

  11. --AWR
  12. select  substr( t.table_name,3,1),count(*)  from dba_tables t
  13. where t.tablespace_name='SYSAUX'  and t.owner='SYS'
  14. and t.table_name like 'WR_$%'
  15. group by  substr( t.table_name,3,1) ;


复制代码
   SUBSTR(T.TABLE_NAME,3,1)COUNT(*)
1R15
2H100
3I80
4M10

  1. select  * from dba_alert_history;

  2. select  * from dba_views v
  3. where v.view_name='DBA_ALERT_HISTORY';
复制代码
  1. select sequence_id,
  2.             reason_id,
  3.             owner,
  4.             object_name,
  5.             subobject_name,
  6.             typnam_keltosd AS object_type,
  7.             dbms_server_alert.expand_message(userenv('LANGUAGE'),
  8.                                              mid_keltsd,
  9.                                              reason_argument_1,
  10.                                              reason_argument_2,
  11.                                              reason_argument_3,
  12.                                              reason_argument_4,
  13.                                              reason_argument_5) AS reason,
  14.             time_suggested,
  15.             creation_time,
  16.             dbms_server_alert.expand_message(userenv('LANGUAGE'),
  17.                                              amid_keltsd,
  18.                                              action_argument_1,
  19.                                              action_argument_2,
  20.                                              action_argument_3,
  21.                                              action_argument_4,
  22.                                              action_argument_5)
  23.               AS suggested_action,
  24.             advisor_name,
  25.             metric_value,
  26.             decode(message_level, 32, 'Notification', 'Warning')
  27.               AS message_type,
  28.             nam_keltgsd AS message_group,
  29.             message_level,
  30.             hosting_client_id,
  31.             mdid_keltsd AS module_id,
  32.             process_id,
  33.             host_id,
  34.             host_nw_addr,
  35.             instance_name,
  36.             instance_number,
  37.             user_id,
  38.             execution_context_id,
  39.             error_instance_id,
  40.             decode(resolution, 1, 'cleared', 'N/A') AS resolution
  41.   FROM wri$_alert_history, X$KELTSD, X$KELTOSD, X$KELTGSD,
  42.        dba_advisor_definitions
  43.   WHERE reason_id = rid_keltsd
  44.     AND otyp_keltsd = typid_keltosd
  45.     AND grp_keltsd = id_keltgsd
  46.     AND aid_keltsd = advisor_id(+)
复制代码
a.png


  1. select  dbms_stats.get_prefs(pname => 'STALE_PERCENT')   from dual;

  2. begin
  3.   dbms_stats.set_global_prefs(pname =>  'STALE_PERCENT',pvalue => '13')  ;
  4. end;

  5. select  dbms_stats.get_prefs(pname => 'STALE_PERCENT')   from dual;

  6. begin
  7.    dbms_stats.gather_database_stats;
  8. end;

  9. --针对个别的挥发+倾斜表,单独收集:
  10. begin
  11.    dbms_stats.gather_table_stats(ownname => 'HR',tabname => 'EMPLOYEES',
  12.        estimate_percent =>100,
  13.        method_opt => 'for all columns size 254');
  14. end;
  15.    
  16.         
复制代码
课程第20/21次(2018-11-24星期六上下午)
研究AWR制造的工作负载:
update_t04209_uname.sql/update2_t04209_uname.sql   
create table hr.t_big as select * from dba_source;
select  text, count(*)  from t_big group by text;

ADDM之后进一步,就要做SQL调优集:
  1. BEGIN dbms_sqltune.create_sqlset(sqlset_name => 'STS1', sqlset_owner =>'SYS'); END;
  2. begin DBMS_SCHEDULER.CREATE_JOB(job_name => 'CREATE_STS_星期六十一月24_095624_113', job_type => 'PLSQL_BLOCK', job_action => 'DECLARE sqlset_cur dbms_sqltune.sqlset_cursor; bf VARCHAR2(39); BEGIN bf := q''#UPPER(PARSING_SCHEMA_NAME) = ''HR'' #''; OPEN sqlset_cur FOR SELECT VALUE(P) FROM TABLE(dbms_sqltune.select_workload_repository( baseline_name=>''PRESERV101_103'', basic_filter=>bf, attribute_list=>''TYPICAL'')) P; dbms_sqltune.load_sqlset( sqlset_name=>''STS1'', populate_cursor=>sqlset_cur, load_option => ''MERGE'', update_option => ''ACCUMULATE'', sqlset_owner=>''SYS''); END;', enabled => TRUE); end;
复制代码
  1. begin
  2.    dbms_workload_repository.create_baseline(start_snap_id => 101,
  3.    end_snap_id => 103,
  4.    baseline_name => 'PRESERV101_103');
  5. end;
  6.    
复制代码
  1. select  * from dba_tablespaces;

  2. select  * from dba_sql_profiles;

  3. create tablespace tbs05212  datafile size 10M autoextend off;

  4. select  bytes/1024/1024, tablespace_name  from dba_segments s
  5. where s.owner='HR' and s.segment_name='T04209_UNAME';

  6. alter table hr.t04209_uname move tablespace tbs05212;

  7. select  * from dba_outstanding_alerts;

  8. select  * from dba_alert_history  h  order by  h.time_suggested desc
  9. ;


复制代码
1Z0-052第12章(18/40)
  1.   select  * from V$MEMORY_TARGET_ADVICE;
复制代码
1Z0-052第13章(19/40)
  1. select  t.table_name, t.buffer_pool   from dba_tables t
  2. where t.owner='HR' and t.table_name='EMPLOYEES';


  3. alter table hr.employees storage (buffer_pool  keep );

  4. select    o.NAME , o.KEPT  from v_$db_object_cache o
  5.    where owner='HR';
  6.    
  7.    begin
  8.       dbms_shared_pool.keep('HR.PROC1');
  9.    end;
复制代码

a.png

1Z0-053第13章(20/40)
  1. select s.USERNAME, s.SID,s.SERIAL#,
  2.            s.SQL_HASH_VALUE, s.SQL_ID
  3. from v_$session s
  4. where s.TERMINAL='pts/13';

  5. select  * from v$sql where sql_id='dh73w3ss300hp';

  6. select  * from V$SYSTEM_WAIT_CLASS;
复制代码
1Z0-053第14章(21/40)
b.png
c.png

  1. select  * from dba_directories;

  2. create directory replaydir as '/home/oracle/replaydir';


  3. select * from dba_sql_profiles;

  4. begin
  5.    dbms_advisor.quick_tune(advisor_name => 'SQL Access Advisor',
  6.    task_name => 'MYTASK1',
  7.    attr1 =>'select  text,count(*) from hr.t_big  group by text',
  8.    template => 'SQLACCESS_OLTP');
  9. end;



  10. begin
  11.    dbms_advisor.quick_tune(advisor_name => 'SQL Access Advisor',
  12.    task_name => 'MYTASK2',
  13.    attr1 =>'select  text,count(*) from hr.t_big  group by text',
  14.    template => 'SQLACCESS_WAREHOUSE');
  15. end;
复制代码
1Z0-053第15章(22/40)

课程第22次(2018-11-25星期日下午)
行链接和行迁移:
  1. [root@station90 ~]# su - oracle
  2. [oracle@station90 ~]$ sqlplus /nolog

  3. SQL*Plus: Release 11.2.0.1.0 Production on Sat May 30 15:13:48 2015

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

  5. SQL> conn hr/oracle_4U
  6. ERROR:
  7. ORA-28002: the password will expire within 5 days


  8. Connected.
  9. SQL> create table t05318_chain(a varchar2(2000), b varchar2(2000), c varchar2(2000), d varchar2(2000));

  10. Table created.

  11. SQL> insert into t05318_chain(a) values('aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa');

  12. 1 row created.

  13. SQL> commit;

  14. Commit complete.

  15. SQL> update t05318_chain  set b='aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa';

  16. 1 row updated.

  17. SQL> commit;

  18. Commit complete.

  19. SQL> update t05318_chain set c='aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa';

  20. 1 row updated.

  21. SQL> commit;

  22. Commit complete.

  23. SQL> select dbms_rowid.rowid_block_number ( rowid ) from t05318_chain;

  24. DBMS_ROWID.ROWID_BLOCK_NUMBER(ROWID)
  25. ------------------------------------
  26.                                  559

  27. SQL> select  * from CHAINED_ROWS;
  28. select        * from CHAINED_ROWS
  29.                *
  30. ERROR at line 1:
  31. ORA-00942: table or view does not exist


  32. SQL> @?/rdbms/admin/utlchain.sql

  33. Table created.

  34. SQL> set linesize 1000
  35. SQL> select  * from CHAINED_ROWS;

  36. no rows selected

  37. SQL> analyze table t05318_chain list chained rows;

  38. Table analyzed.

  39. SQL> select  * from CHAINED_ROWS;

  40. no rows selected

  41. SQL> update t05318_chain set d='aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa';

  42. 1 row updated.

  43. SQL> commit;

  44. Commit complete.

  45. SQL> select dbms_rowid.rowid_block_number ( rowid ) from t05318_chain;

  46. DBMS_ROWID.ROWID_BLOCK_NUMBER(ROWID)
  47. ------------------------------------
  48.                                  559

  49. SQL> analyze table t05318_chain list chained rows;

  50. Table analyzed.

  51. SQL> select  * from CHAINED_ROWS;

  52. OWNER_NAME                       TABLE_NAME                      CLUSTER_NAME                     PARTITION_NAME                    SUBPARTITION_NAME                   HEAD_ROWID              ANALYZE_TIMESTAMP
  53. ------------------------------ ------------------------------ ------------------------------ ------------------------------ ------------------------------ ------------------ ------------------
  54. HR                               T05318_CHAIN                                                                                    N/A                            AAASOPAAEAAAAIvAAA 30-MAY-15

  55. SQL> truncate table CHAINED_ROWS;

  56. Table truncated.

  57. SQL> select  * from CHAINED_ROWS;

  58. no rows selected

  59. SQL> analyze table t05318_chain list chained rows;

  60. Table analyzed.

  61. SQL> select  * from CHAINED_ROWS;

  62. OWNER_NAME                       TABLE_NAME                      CLUSTER_NAME                     PARTITION_NAME                    SUBPARTITION_NAME                   HEAD_ROWID              ANALYZE_TIMESTAMP
  63. ------------------------------ ------------------------------ ------------------------------ ------------------------------ ------------------------------ ------------------ ------------------
  64. HR                               T05318_CHAIN                                                                                    N/A                            AAASOPAAEAAAAIvAAA 30-MAY-15

  65. SQL> truncate table CHAINED_ROWS;

  66. Table truncated.

  67. SQL> select  * from CHAINED_ROWS;

  68. no rows selected

  69. SQL> alter table t05318_chain  move tablspace tbs16k;
  70. alter table t05318_chain  move tablspace tbs16k
  71.                                *
  72. ERROR at line 1:
  73. ORA-14133: ALTER TABLE MOVE cannot be combined with other operations


  74. SQL> alter table t05318_chain  move tablespace tbs16k;

  75. Table altered.

  76. SQL> analyze table t05318_chain list chained rows;

  77. Table analyzed.

  78. SQL> select  * from CHAINED_ROWS;

  79. no rows selected

  80. SQL> create table t05318_migrate( a varchar2(2000))  pctfree 0;

  81. Table created.

  82. SQL> show user
  83. USER is "HR"
  84. begin
  85.    for i in 1..733
  86.    loop
  87.       insert into  t05318_migrate values('A');
  88.     end loop;
  89.     commit;
  90.   7  end;
  91.   8  /

  92. PL/SQL procedure successfully completed.

  93. SQL> select  dbms_rowid.rowid_block_number(rowid) , count(*)  from t05318_migrate
  94.   2   group by dbms_rowid.rowid_block_number(rowid);

  95. DBMS_ROWID.ROWID_BLOCK_NUMBER(ROWID)   COUNT(*)
  96. ------------------------------------ ----------
  97.                                  559            733

  98. SQL> begin
  99.   2    for i in 1..100
  100.   3    loop
  101.   4      insert into  t05318_migrate values('B');
  102.   5    end loop;
  103.   6    commit;
  104.   7  end;
  105.   8  /

  106. PL/SQL procedure successfully completed.

  107. SQL> select  dbms_rowid.rowid_block_number(rowid) , count(*)  from t05318_migrate
  108.   2    group by dbms_rowid.rowid_block_number(rowid);

  109. DBMS_ROWID.ROWID_BLOCK_NUMBER(ROWID)   COUNT(*)
  110. ------------------------------------ ----------
  111.                                  555            100
  112.                                  559            733

  113. SQL> delete from t05318_migrate where A='B';

  114. 100 rows deleted.

  115. SQL> commit;

  116. Commit complete.

  117. SQL> select  dbms_rowid.rowid_block_number(rowid) , count(*)  from t05318_migrate
  118.   2    group by dbms_rowid.rowid_block_number(rowid);

  119. DBMS_ROWID.ROWID_BLOCK_NUMBER(ROWID)   COUNT(*)
  120. ------------------------------------ ----------
  121.                                  559            733

  122. SQL> analyze table t05318_migrate  list chained rows;

  123. Table analyzed.

  124. SQL> select  * from chained_rows;

  125. no rows selected

  126. SQL> update t05318_migrate set a=''aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa');
  127. ERROR:
  128. ORA-00972: identifier is too long


  129. SQL> update t05318_migrate set a='aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa' where rownum=1;

  130. 1 row updated.

  131. SQL> commit;

  132. Commit complete.

  133. SQL> analyze table t05318_migrate  list chained rows;

  134. Table analyzed.

  135. SQL> select  * from chained_rows;

  136. OWNER_NAME                       TABLE_NAME                      CLUSTER_NAME                     PARTITION_NAME                    SUBPARTITION_NAME                   HEAD_ROWID              ANALYZE_TIMESTAMP
  137. ------------------------------ ------------------------------ ------------------------------ ------------------------------ ------------------------------ ------------------ ------------------
  138. HR                               T05318_MIGRATE                                                                                    N/A                            AAASOUAAEAAAAIvAAA 30-MAY-15

  139. SQL> select  dbms_rowid.rowid_block_number(rowid) , count(*)  from t05318_migrate
  140.   2   group by dbms_rowid.rowid_block_number(rowid);

  141. DBMS_ROWID.ROWID_BLOCK_NUMBER(ROWID)   COUNT(*)
  142. ------------------------------------ ----------
  143.                                  559            733

  144. SQL> alter table T05318_MIGRATE move tablespace users;

  145. Table altered.

  146. SQL> select  dbms_rowid.rowid_block_number(rowid) , count(*)  from t05318_migrate
  147.   2   group by dbms_rowid.rowid_block_number(rowid);

  148. DBMS_ROWID.ROWID_BLOCK_NUMBER(ROWID)   COUNT(*)
  149. ------------------------------------ ----------
  150.                                  588            185
  151.                                  587            548

  152. SQL> alter table T05318_MIGRATE  pctfree 20;

  153. Table altered.

  154. SQL>  alter table T05318_MIGRATE move tablespace users;

  155. Table altered.

  156. SQL> select  dbms_rowid.rowid_block_number(rowid) , count(*)  from t05318_migrate
  157.   2   group by dbms_rowid.rowid_block_number(rowid);

  158. DBMS_ROWID.ROWID_BLOCK_NUMBER(ROWID)   COUNT(*)
  159. ------------------------------------ ----------
  160.                                  555            402
  161.                                  556            331

  162. SQL> truncate table chained_rows;

  163. Table truncated.

  164. SQL> analyze table T05318_MIGRATE list chained rows;

  165. Table analyzed.

  166. SQL> select  * from chained_rows;

  167. no rows selected

  168. SQL>

复制代码
  1. CREATE TABLE "HR"."IOT1" ( "A" NUMBER, "B" VARCHAR2(20), CONSTRAINT "IOT1_PK" PRIMARY KEY ("A") VALIDATE ) ORGANIZATION INDEX PCTTHRESHOLD 20 OVERFLOW TABLESPACE "EXAMPLE"
  2.         
复制代码

IOT:
  1. select  * from dba_tables t where t.owner='HR' and t.table_name='IOT1';

  2. select  * from dba_segments s where s.owner='HR' and s.segment_name='IOT1';

  3. select  * from dba_constraints c where c.owner='HR' and c.table_name='IOT1';

  4. select  *  from dba_indexes i where i.owner='HR' and i.index_name='IOT1_PK';

  5. select  * from dba_objects o where o.owner='HR' and o.object_name='IOT1';

  6. select  * from dba_tables t where t.owner='HR' and t.table_name  like '%82191%';

  7. select  * from dba_segments s where s.owner='HR' and s.segment_name='SYS_IOT_OVER_82191';
复制代码

  1. SQL> create bitmap index bix_iot1 on iot1(b) ;
  2. create bitmap index bix_iot1 on iot1(b)
  3.                                 *
  4. ERROR at line 1:
  5. ORA-28669: bitmap index can not be created on an IOT with no mapping table

  6. alter table hr.iot1 move mapping  table;

  7. alter table hr.iot1  move nomapping;


  8. SQL> create bitmap index bix_iot1 on iot1(b) ;

  9. Index created.
复制代码
  1. select  * from dba_tables t where t.owner='HR' and t.table_name  like '%82191%';
复制代码

压缩:
  1. Oracle11gR2的表压缩特性通过压缩表中的数据以减少对空间的占用。Oracle11gR2支持三种不同的压缩方式:
  2. ·        支持直接路径加载的Basic压缩方式(10x)
  3. ·        支持针对所有DML操作的OLTP压缩方式(2-4x)
  4. ·        Exadata专属的Hybrid columnar compression压缩方式

  5.     Oracle公司建议我们采用以上三种不同的压缩方式,针对特定的应用场合来进行表压缩。如果该表包含大量冗长的和重复的值,通过被压缩可以减少该表对磁盘空间的占用和对SGA中数据库缓冲区缓存的占用。
  6. 先从图形界面入手,我们发现在新建表空间时Oracle11gR2的界面出现压缩选项:
  7. 在 下面各个部分所有的实验中我们都创建两个表:一个表在TBS_NOCOMPRESSION表空间,另一个表在有对应默认压缩选项的表空间。最后我们把在 TBS_NOCOMPRESSION表空间上的那个表转换成对应压缩选项表。通过对比实验来弄清Oracle11gR2这方面的新特性。
  8. --sys--
  9. create tablespace tbs_nocompression datafile size 10M autoextend on;
  10. create tablespace tbs_basic datafile size 10M autoextend on default compress basic;
  11. create tablespace tbs_oltp datafile size 10M autoextend on default compress for oltp;
  12. create tablespace tbs_query datafile size 10M autoextend on default compress for query;
  13. create tablespace tbs_archive datafile size 10M autoextend on default compress for archive;
  14. select  t.tablespace_name, t.def_tab_compression, t.compress_for  from dba_tablespaces t
  15. where t.tablespace_name in ('TBS_NOCOMPRESSION',
  16.                                                   'TBS_BASIC', 'TBS_OLTP',
  17.                                                    'TBS_QUERY',
  18.                                                    'TBS_ARCHIVE');
  19. --hr--                                                   
  20. create table t_nocompression (a varchar2(200)) tablespace tbs_nocompression;
  21. create table t_basic (a varchar2(200)) tablespace tbs_basic;
  22. create table t_oltp (a varchar2(200)) tablespace tbs_oltp;
  23. --hr-error--
  24. create table t_query (a varchar2(200)) tablespace tbs_query;
  25. create table t_archive (a varchar2(200)) tablespace tbs_archive;  
  26. --hr--
  27. begin
  28.    for i in 1..400
  29.    loop
  30.       insert into t_nocompression values('AAAAAAAAAAAAAAAAAAAA');
  31.    end loop;
  32.    commit;
  33. end;
  34. --hr--
  35. begin
  36.    for i in 1..400
  37.    loop
  38.       insert into t_basic values('AAAAAAAAAAAAAAAAAAAA');
  39.    end loop;
  40.    commit;
  41. end;
  42. --hr--
  43. begin
  44.    for i in 1..400
  45.    loop
  46.       insert into t_oltp values('AAAAAAAAAAAAAAAAAAAA');
  47.    end loop;
  48.    commit;
  49. end;  
  50. --hr--
  51. select  t.TABLE_NAME, t.PCT_FREE , t.COMPRESSION , t.COMPRESS_FOR, t.TABLESPACE_NAME
  52. from user_tables t where t.TABLE_NAME   in ('T_NOCOMPRESSION','T_BASIC','T_OLTP');
  53. select  count(*)  , substr(rowid, 10, 6 ) from hr.t_nocompression  group by  substr(rowid, 10, 6 );
  54. select  count(*)  , substr(rowid, 10, 6 ) from hr.t_basic  group by  substr(rowid, 10, 6 );      
  55. select  count(*)  , substr(rowid, 10, 6 ) from hr.t_oltp  group by  substr(rowid, 10, 6 );  
  56. --sys--
  57. select   count(*)  ,    dbms_compression.get_compression_type(ownname => 'HR',tabname => 'T_NOCOMPRESSION',row_id => rowid)
  58.       from hr.T_NOCOMPRESSION  group by     dbms_compression.get_compression_type(ownname => 'HR',tabname => 'T_NOCOMPRESSION',row_id => rowid);
  59. --sys--
  60. select   count(*)  ,    dbms_compression.get_compression_type(ownname => 'HR',tabname => 'T_BASIC',row_id => rowid)
  61.       from hr.T_BASIC group by     dbms_compression.get_compression_type(ownname => 'HR',tabname => 'T_BASIC',row_id => rowid);
  62. --sys--      
  63. select   count(*)  ,    dbms_compression.get_compression_type(ownname => 'HR',tabname => 'T_OLTP',row_id => rowid)
  64.       from hr.T_OLTP group by     dbms_compression.get_compression_type(ownname => 'HR',tabname => 'T_OLTP',row_id => rowid);
  65. --hr--      
  66. create table t_basic2 (a varchar2(200)) tablespace tbs_nocompression compress;
  67. create table t_oltp2 (a varchar2(200)) tablespace tbs_nocompression compress for oltp;
  68. --hr-error--
  69. create table t_query2 (a varchar2(200)) tablespace tbs_nocompression compress for query;
  70. create table t_archive2 (a varchar2(200)) tablespace tbs_nocompression compress for archive;     
  71. --hr--
  72. select  t.TABLE_NAME, t.PCT_FREE , t.COMPRESSION , t.COMPRESS_FOR, t.TABLESPACE_NAME
  73. from user_tables t where t.TABLE_NAME   in ('T_NOCOMPRESSION','T_BASIC','T_OLTP',  'T_BASIC2','T_OLTP2' );
  74. --hr--
  75. begin
  76.    for i in 1..400
  77.    loop
  78.       insert into t_basic2 values('AAAAAAAAAAAAAAAAAAAA');
  79.    end loop;
  80.    commit;
  81. end;
  82. --hr--
  83. begin
  84.    for i in 1..400
  85.    loop
  86.       insert into t_oltp2 values('AAAAAAAAAAAAAAAAAAAA');
  87.    end loop;
  88.    commit;
  89. end;
  90. --hr--
  91. select  count(*)  , substr(rowid, 10, 6 ) from hr.t_basic2  group by  substr(rowid, 10, 6 );
  92. select  count(*)  , substr(rowid, 10, 6 ) from hr.t_oltp2  group by  substr(rowid, 10, 6 );
  93. --sys--
  94. select   count(*)  ,    dbms_compression.get_compression_type(ownname => 'HR',tabname => 'T_BASIC2',row_id => rowid)
  95.       from hr.T_BASIC2 group by     dbms_compression.get_compression_type(ownname => 'HR',tabname => 'T_BASIC2',row_id => rowid);
  96. --sys--      
  97. select   count(*)  ,    dbms_compression.get_compression_type(ownname => 'HR',tabname => 'T_OLTP2',row_id => rowid)
  98.       from hr.T_OLTP2 group by     dbms_compression.get_compression_type(ownname => 'HR',tabname => 'T_OLTP2',row_id => rowid);
  99. --hr--
  100. alter table t_nocompression compress for oltp;
  101. select  t.TABLE_NAME, t.PCT_FREE , t.COMPRESSION , t.COMPRESS_FOR, t.TABLESPACE_NAME
  102. from user_tables t where t.TABLE_NAME   in ('T_NOCOMPRESSION','T_BASIC','T_OLTP',  'T_BASIC2','T_OLTP2' );
  103. select  count(*)  , substr(rowid, 10, 6 ) from hr.t_nocompression  group by  substr(rowid, 10, 6 );     
  104. --sys--
  105. select   count(*)  ,    dbms_compression.get_compression_type(ownname => 'HR',tabname => 'T_NOCOMPRESSION',row_id => rowid)
  106.    from hr.T_NOCOMPRESSION  group by     dbms_compression.get_compression_type(ownname => 'HR',tabname => 'T_NOCOMPRESSION',row_id => rowid);   
  107. --hr--
  108. begin
  109.    for i in 1..400
  110.    loop
  111.       insert into t_nocompression values('AAAAAAAAAAAAAAAAAAAA');
  112.    end loop;
  113.    commit;
  114. end;
  115. --hr--
  116. select  count(*)  , substr(rowid, 10, 6 ) from hr.t_nocompression  group by  substr(rowid, 10, 6 );
  117. --sys--
  118. select   count(*)  ,    dbms_compression.get_compression_type(ownname => 'HR',tabname => 'T_NOCOMPRESSION',row_id => rowid)
  119.       from hr.T_NOCOMPRESSION  group by     dbms_compression.get_compression_type(ownname => 'HR',tabname => 'T_NOCOMPRESSION',row_id => rowid);
  120. --hr--
  121. alter table t_nocompression move tablespace tbs_nocompression;
  122. select  count(*)  , substr(rowid, 10, 6 ) from hr.t_nocompression  group by  substr(rowid, 10, 6 );   
  123. --sys--
  124. select   count(*)  ,    dbms_compression.get_compression_type(ownname => 'HR',tabname => 'T_NOCOMPRESSION',row_id => rowid)
  125.       from hr.T_NOCOMPRESSION  group by     dbms_compression.get_compression_type(ownname => 'HR',tabname => 'T_NOCOMPRESSION',row_id => rowid);
  126. --hr--
  127. select  count(*)  , substr(rowid, 10, 6 ) from hr.t_basic  group by  substr(rowid, 10, 6 );  
  128. insert /*+ append */ into t_basic select  * from  t_basic;
  129. commit;
  130. select  count(*)  , substr(rowid, 10, 6 ) from hr.t_basic  group by  substr(rowid, 10, 6 );
  131. --sys--
  132. select   count(*)  ,    dbms_compression.get_compression_type(ownname => 'HR',tabname => 'T_BASIC',row_id => rowid)
  133.       from hr.T_BASIC group by     dbms_compression.get_compression_type(ownname => 'HR',tabname => 'T_BASIC',row_id => rowid);
  134. --hr--
  135. alter table t_basic move tablespace  TBS_NOCOMPRESSION;
  136. select  count(*)  , substr(rowid, 10, 6 ) from hr.t_basic  group by  substr(rowid, 10, 6 );      
  137. --sys--
  138. select   count(*)  ,    dbms_compression.get_compression_type(ownname => 'HR',tabname => 'T_BASIC',row_id => rowid)
  139.       from hr.T_BASIC group by     dbms_compression.get_compression_type(ownname => 'HR',tabname => 'T_BASIC',row_id => rowid);
  140. --sys-evolution--
  141. drop table hr.t_basic_big;
  142. create table hr.t_basic_big compress as select  * from dba_source;
  143. --sys-advisor--
  144. declare
  145.   v_blkcnt_cmp number;
  146.   v_blkcnt_uncmp  number;
  147.   v_row_cmp number;
  148.   v_row_uncmp number;
  149.   v_cmp_ratio number;
  150.   v_comptype_str  varchar2(200);
  151. BEGIN
  152. DBMS_COMPRESSION.GET_COMPRESSION_RATIO(scratchtbsname => 'USERS',
  153.                                                                                              ownname =>'HR',
  154.                                                                                              tabname =>'T_BASIC_BIG',
  155.                                                                                              partname =>null,
  156.                                                                                              comptype => 2,
  157.                                                                                              blkcnt_cmp => v_blkcnt_cmp,
  158.                                                                                              blkcnt_uncmp =>  v_blkcnt_uncmp,
  159.                                                                                              row_cmp =>v_row_cmp,
  160.                                                                                              row_uncmp => v_row_uncmp,
  161.                                                                                              cmp_ratio =>  v_cmp_ratio,
  162.                                                                                              comptype_str =>v_comptype_str);                                                  
  163. DBMS_OUTPUT.PUT_LINE('Blk count compressed = ' || v_blkcnt_cmp);
  164. DBMS_OUTPUT.PUT_LINE('Blk count uncompressed = ' || v_blkcnt_uncmp);
  165. DBMS_OUTPUT.PUT_LINE('Row count per block compressed = ' || v_row_cmp);
  166. DBMS_OUTPUT.PUT_LINE('Row count per block uncompressed = ' || v_row_uncmp);
  167. DBMS_OUTPUT.PUT_LINE('ratio: '||v_cmp_ratio);
  168. DBMS_OUTPUT.PUT_LINE('Compression type = ' || v_comptype_str);
  169. end;
  170. --输出--
  171. Blk count compressed = 1785
  172. Blk count uncompressed = 2340
  173. Row count per block compressed = 68
  174. Row count per block uncompressed = 51
  175. ratio: 1.31092436974789915966386554621848739496
  176. Compression type = "Compress For OLTP"
  177. --OLTP压缩一下表--
  178. 验证表大小

  179. --sys--
  180. select   count(*)  ,    dbms_compression.get_compression_type(ownname => 'HR',tabname => 'T_BASIC_BIG',row_id => rowid)
  181.       from hr.T_BASIC_BIG group by     dbms_compression.get_compression_type(ownname => 'HR',tabname => 'T_BASIC_BIG',row_id => rowid);  
  182. --hr--
  183. create table t_basic_col( a  number , b varchar2(20))  compress ;
  184. insert into t_basic_col values ( 1,'A') ;
  185. commit;
  186. create table t_oltp_col( a  number , b varchar2(20))  compress  for oltp;
  187. insert into t_oltp_col values ( 1,'A') ;
  188. commit;
  189. select  * from  t_basic_col;
  190. select  * from t_oltp_col;
  191. alter table  t_basic_col drop column b;
  192. alter table  t_basic_col drop ( b);
  193. alter table t_oltp_col drop column b;
  194. select  * from t_oltp_col;

复制代码

a.png


  1. create tablespace tbs05318 datafile size 5M autoextend off;

  2. select file_name , bytes/1024/1024 , d.autoextensible
  3. from  dba_data_files d  where tablespace_name='TBS05318';

  4. grant resumable to hr;
复制代码
  1. SQL> conn / as sysdba
  2. Connected.
  3. SQL> show parameter resum

  4. NAME                                     TYPE         VALUE
  5. ------------------------------------ ----------- ------------------------------
  6. resumable_timeout                     integer         0
  7. SQL> conn hr/oracle_4U
  8. Connected.
  9. SQL> create table t05318 ( a number )  tablespace tbs05318 storage ( initial 6M ) ;
  10. create table t05318 ( a number )  tablespace tbs05318 storage ( initial 6M )
  11.              *
  12. ERROR at line 1:
  13. ORA-00955: name is already used by an existing object


  14. SQL> create table t05318_b ( a number )  tablespace tbs05318 storage ( initial 6M ) ;

  15. Table created.

  16. SQL> insert into t05318_b values (1) ;
  17. insert into t05318_b values (1)
  18.             *
  19. ERROR at line 1:
  20. ORA-01659: unable to allocate MINEXTENTS beyond 4 in tablespace TBS05318


  21. SQL> alter session enable resumable;
  22. ERROR:
  23. ORA-01031: insufficient privileges


  24. SQL> alter session set resumable_timeout=5;

  25. Session altered.

  26. SQL> insert into t05318_b values (1) ;
  27. insert into t05318_b values (1)
  28.             *
  29. ERROR at line 1:
  30. ORA-30032: the suspended (resumable) statement has timed out
  31. ORA-01659: unable to allocate MINEXTENTS beyond 4 in tablespace TBS05318


  32. SQL> insert into t05318_b values (1) ;
  33. insert into t05318_b values (1)
  34. *
  35. ERROR at line 1:
  36. ORA-30032: the suspended (resumable) statement has timed out
  37. ORA-01659: unable to allocate MINEXTENTS beyond 4 in tablespace TBS05318


  38. SQL>
复制代码
  1. create tablespace tbs05318 datafile size 5M autoextend off;

  2. select file_name , bytes/1024/1024 , d.autoextensible
  3. from  dba_data_files d  where tablespace_name='TBS05318';

  4. grant resumable to hr;

  5. --+DATA/orcl/datafile/tbs05318.274.993144427

  6. CREATE OR REPLACE TRIGGER SYS.TRG_SUSPEND
  7. AFTER SUSPEND
  8. ON DATABASE
  9. declare
  10. v_size number;
  11.   pragma AUTONOMOUS_TRANSACTION;
  12. begin
  13.   select  bytes into v_size from dba_data_files where  file_name='+DATA/orcl/datafile/tbs05318.274.993144427';
  14.   v_size := v_size +3145728 ;
  15.   execute immediate 'alter database datafile  ''+DATA/orcl/datafile/tbs05318.274.993144427''  resize '||to_char(v_size);
  16.   commit;
  17. end;
复制代码

1Z0-053第18章(23/40)

课程第23次(2018-11-26星期一)
  1. [oracle@station80 ~]$ rman target /

  2. Recovery Manager: Release 11.2.0.1.0 - Production on Mon Nov 26 04:46:07 2018

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

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

  5. RMAN> show all;

  6. using target database control file instead of recovery catalog
  7. RMAN configuration parameters for database with db_unique_name ORCL are:
  8. CONFIGURE RETENTION POLICY TO RECOVERY WINDOW OF 1 DAYS;
  9. CONFIGURE BACKUP OPTIMIZATION ON;
  10. CONFIGURE DEFAULT DEVICE TYPE TO 'SBT_TAPE';
  11. CONFIGURE CONTROLFILE AUTOBACKUP ON;
  12. CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '%F'; # default
  13. CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE SBT_TAPE TO '%F'; # default
  14. CONFIGURE DEVICE TYPE DISK PARALLELISM 8 BACKUP TYPE TO BACKUPSET;
  15. CONFIGURE DEVICE TYPE SBT_TAPE PARALLELISM 1 BACKUP TYPE TO BACKUPSET; # default
  16. CONFIGURE DATAFILE BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
  17. CONFIGURE DATAFILE BACKUP COPIES FOR DEVICE TYPE SBT_TAPE TO 1; # default
  18. CONFIGURE ARCHIVELOG BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
  19. CONFIGURE ARCHIVELOG BACKUP COPIES FOR DEVICE TYPE SBT_TAPE TO 1; # default
  20. CONFIGURE MAXSETSIZE TO UNLIMITED; # default
  21. CONFIGURE ENCRYPTION FOR DATABASE OFF; # default
  22. CONFIGURE ENCRYPTION ALGORITHM 'AES128'; # default
  23. CONFIGURE COMPRESSION ALGORITHM 'BASIC' AS OF RELEASE 'DEFAULT' OPTIMIZE FOR LOAD TRUE ; # default
  24. CONFIGURE ARCHIVELOG DELETION POLICY TO NONE; # default
  25. CONFIGURE SNAPSHOT CONTROLFILE NAME TO '/u01/app/oracle/product/11.2.0/dbhome_1/dbs/snapcf_orcl.f'; # default

  26. RMAN> CONFIGURE DEVICE TYPE SBT_TAPE PARALLELISM 2 BACKUP TYPE TO BACKUPSET;

  27. new RMAN configuration parameters:
  28. CONFIGURE DEVICE TYPE 'SBT_TAPE' PARALLELISM 2 BACKUP TYPE TO BACKUPSET;
  29. new RMAN configuration parameters are successfully stored

  30. RMAN> CONFIGURE CHANNEL 1 DEVICE TYPE 'SBT_TAPE'    PARMS  'ENV=(OB_MEDIA_FAMILY=station80)';

  31. new RMAN configuration parameters:
  32. CONFIGURE CHANNEL 1 DEVICE TYPE 'SBT_TAPE' PARMS  'ENV=(OB_MEDIA_FAMILY=station80)';
  33. new RMAN configuration parameters are successfully stored

  34. RMAN> CONFIGURE CHANNEL 2 DEVICE TYPE 'SBT_TAPE'    PARMS  'ENV=(OB_MEDIA_FAMILY=station80)';

  35. new RMAN configuration parameters:
  36. CONFIGURE CHANNEL 2 DEVICE TYPE 'SBT_TAPE' PARMS  'ENV=(OB_MEDIA_FAMILY=station80)';
  37. new RMAN configuration parameters are successfully stored

  38. RMAN> show all;

  39. RMAN configuration parameters for database with db_unique_name ORCL are:
  40. CONFIGURE RETENTION POLICY TO RECOVERY WINDOW OF 1 DAYS;
  41. CONFIGURE BACKUP OPTIMIZATION ON;
  42. CONFIGURE DEFAULT DEVICE TYPE TO 'SBT_TAPE';
  43. CONFIGURE CONTROLFILE AUTOBACKUP ON;
  44. CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '%F'; # default
  45. CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE SBT_TAPE TO '%F'; # default
  46. CONFIGURE DEVICE TYPE DISK PARALLELISM 8 BACKUP TYPE TO BACKUPSET;
  47. CONFIGURE DEVICE TYPE 'SBT_TAPE' PARALLELISM 2 BACKUP TYPE TO BACKUPSET;
  48. CONFIGURE DATAFILE BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
  49. CONFIGURE DATAFILE BACKUP COPIES FOR DEVICE TYPE SBT_TAPE TO 1; # default
  50. CONFIGURE ARCHIVELOG BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
  51. CONFIGURE ARCHIVELOG BACKUP COPIES FOR DEVICE TYPE SBT_TAPE TO 1; # default
  52. CONFIGURE CHANNEL 1 DEVICE TYPE 'SBT_TAPE' PARMS  'ENV=(OB_MEDIA_FAMILY=station80)';
  53. CONFIGURE CHANNEL 2 DEVICE TYPE 'SBT_TAPE' PARMS  'ENV=(OB_MEDIA_FAMILY=station80)';
  54. CONFIGURE MAXSETSIZE TO UNLIMITED; # default
  55. CONFIGURE ENCRYPTION FOR DATABASE OFF; # default
  56. CONFIGURE ENCRYPTION ALGORITHM 'AES128'; # default
  57. CONFIGURE COMPRESSION ALGORITHM 'BASIC' AS OF RELEASE 'DEFAULT' OPTIMIZE FOR LOAD TRUE ; # default
  58. CONFIGURE ARCHIVELOG DELETION POLICY TO NONE; # default
  59. CONFIGURE SNAPSHOT CONTROLFILE NAME TO '/u01/app/oracle/product/11.2.0/dbhome_1/dbs/snapcf_orcl.f'; # default

  60. RMAN>
复制代码

  1. [root@station80 mhvtl]# ls
  2. CLN101L4  E01004L4  E01010L4  E01016L4  F01031L5  F01037L5  G03004TA  G03010TA  G03016TA  G03022TA  G03028TA  G03034TA
  3. CLN102L5  E01005L4  E01011L4  E01017L4  F01032L5  F01038L5  G03005TA  G03011TA  G03017TA  G03023TA  G03029TA  G03035TA
  4. CLN303TA  E01006L4  E01012L4  E01018L4  F01033L5  F01039L5  G03006TA  G03012TA  G03018TA  G03024TA  G03030TA  G03036TA
  5. E01001L4  E01007L4  E01013L4  E01019L4  F01034L5  G03001TA  G03007TA  G03013TA  G03019TA  G03025TA  G03031TA  G03037TA
  6. E01002L4  E01008L4  E01014L4  E01020L4  F01035L5  G03002TA  G03008TA  G03014TA  G03020TA  G03026TA  G03032TA  G03038TA
  7. E01003L4  E01009L4  E01015L4  F01030L5  F01036L5  G03003TA  G03009TA  G03015TA  G03021TA  G03027TA  G03033TA  G03039TA
  8. [root@station80 mhvtl]# cd /stage/
  9. [root@station80 stage]# ls
  10. client                     lzo-devel-2.02-2.el5.1.i386.rpm    osb.txt                          service_oraclesetup2.0
  11. copytape-root.sh           mhvtl-1.2                          OSPATCHS                         sqlplus_extentsetup3.0
  12. database                   oracleasmlib-2.0.4-1.el5.i386.rpm  qscintilla-1.7.1-1.fc6.i386.rpm  stardict-2.4.4-1.i386.rpm
  13. grid                       osb-10.3.0.3.0_linux32             reuseosb.sh                      tora-1.3.23-1.el5.i386.rpm
  14. lzo-2.02-2.el5.1.i386.rpm  osb.sh                             revertape-root.sh
  15. [root@station80 stage]# vim osb.txt
  16. [root@station80 stage]# obtool
  17. ob> lsmf
  18. OSB-CATALOG-MF   write 7 days             keep 14 days            
  19. RMAN-DEFAULT                              content manages reuse   
  20. sexample                                  content manages reuse   
  21. station80                                 content manages reuse   
  22. ob> exit
  23. [root@station80 stage]# su - oracle
  24. [oracle@station80 ~]$ rman target /

  25. Recovery Manager: Release 11.2.0.1.0 - Production on Mon Nov 26 04:52:58 2018

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

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

  28. RMAN> show all;

  29. using target database control file instead of recovery catalog
  30. RMAN configuration parameters for database with db_unique_name ORCL are:
  31. CONFIGURE RETENTION POLICY TO RECOVERY WINDOW OF 1 DAYS;
  32. CONFIGURE BACKUP OPTIMIZATION ON;
  33. CONFIGURE DEFAULT DEVICE TYPE TO 'SBT_TAPE';
  34. CONFIGURE CONTROLFILE AUTOBACKUP ON;
  35. CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '%F'; # default
  36. CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE SBT_TAPE TO '%F'; # default
  37. CONFIGURE DEVICE TYPE DISK PARALLELISM 8 BACKUP TYPE TO BACKUPSET;
  38. CONFIGURE DEVICE TYPE 'SBT_TAPE' PARALLELISM 2 BACKUP TYPE TO BACKUPSET;
  39. CONFIGURE DATAFILE BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
  40. CONFIGURE DATAFILE BACKUP COPIES FOR DEVICE TYPE SBT_TAPE TO 1; # default
  41. CONFIGURE ARCHIVELOG BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
  42. CONFIGURE ARCHIVELOG BACKUP COPIES FOR DEVICE TYPE SBT_TAPE TO 1; # default
  43. CONFIGURE CHANNEL 1 DEVICE TYPE 'SBT_TAPE' PARMS  'ENV=(OB_MEDIA_FAMILY=station80)';
  44. CONFIGURE CHANNEL 2 DEVICE TYPE 'SBT_TAPE' PARMS  'ENV=(OB_MEDIA_FAMILY=station80)';
  45. CONFIGURE MAXSETSIZE TO UNLIMITED; # default
  46. CONFIGURE ENCRYPTION FOR DATABASE OFF; # default
  47. CONFIGURE ENCRYPTION ALGORITHM 'AES128'; # default
  48. CONFIGURE COMPRESSION ALGORITHM 'BASIC' AS OF RELEASE 'DEFAULT' OPTIMIZE FOR LOAD TRUE ; # default
  49. CONFIGURE ARCHIVELOG DELETION POLICY TO NONE; # default
  50. CONFIGURE SNAPSHOT CONTROLFILE NAME TO '/u01/app/oracle/product/11.2.0/dbhome_1/dbs/snapcf_orcl.f'; # default

  51. RMAN> delete backup;  

  52. allocated channel: ORA_DISK_1
  53. channel ORA_DISK_1: SID=73 device type=DISK
  54. allocated channel: ORA_DISK_2
  55. channel ORA_DISK_2: SID=10 device type=DISK
  56. allocated channel: ORA_DISK_3
  57. channel ORA_DISK_3: SID=144 device type=DISK
  58. allocated channel: ORA_DISK_4
  59. channel ORA_DISK_4: SID=201 device type=DISK
  60. allocated channel: ORA_DISK_5
  61. channel ORA_DISK_5: SID=8 device type=DISK
  62. allocated channel: ORA_DISK_6
  63. channel ORA_DISK_6: SID=80 device type=DISK
  64. allocated channel: ORA_DISK_7
  65. channel ORA_DISK_7: SID=136 device type=DISK
  66. allocated channel: ORA_DISK_8
  67. channel ORA_DISK_8: SID=202 device type=DISK

  68. List of Backup Pieces
  69. BP Key  BS Key  Pc# Cp# Status      Device Type Piece Name
  70. ------- ------- --- --- ----------- ----------- ----------
  71. 9       9       1   1   AVAILABLE   DISK        +FRA/orcl/backupset/2018_11_26/nnndf0_tag20181126t032513_0.290.993180335
  72. 10      10      1   1   AVAILABLE   DISK        +FRA/orcl/backupset/2018_11_26/nnndf0_tag20181126t032513_0.291.993180359
  73. 11      11      1   1   AVAILABLE   DISK        +FRA/orcl/backupset/2018_11_26/nnndf0_tag20181126t032513_0.292.993180393
  74. 12      12      1   1   AVAILABLE   DISK        +FRA/orcl/backupset/2018_11_26/nnndf0_tag20181126t032513_0.289.993180325
  75. 13      13      1   1   AVAILABLE   DISK        +FRA/orcl/backupset/2018_11_26/nnndf0_tag20181126t032513_0.288.993180317
  76. 14      14      1   1   AVAILABLE   DISK        +FRA/orcl/autobackup/2018_11_26/s_993180492.293.993180493

  77. Do you really want to delete the above objects (enter YES or NO)? YES
  78. deleted backup piece
  79. backup piece handle=+FRA/orcl/backupset/2018_11_26/nnndf0_tag20181126t032513_0.290.993180335 RECID=9 STAMP=993180332
  80. deleted backup piece
  81. backup piece handle=+FRA/orcl/backupset/2018_11_26/nnndf0_tag20181126t032513_0.291.993180359 RECID=10 STAMP=993180354
  82. deleted backup piece
  83. backup piece handle=+FRA/orcl/backupset/2018_11_26/nnndf0_tag20181126t032513_0.292.993180393 RECID=11 STAMP=993180382
  84. deleted backup piece
  85. backup piece handle=+FRA/orcl/backupset/2018_11_26/nnndf0_tag20181126t032513_0.289.993180325 RECID=12 STAMP=993180322
  86. deleted backup piece
  87. backup piece handle=+FRA/orcl/backupset/2018_11_26/nnndf0_tag20181126t032513_0.288.993180317 RECID=13 STAMP=993180316
  88. deleted backup piece
  89. backup piece handle=+FRA/orcl/autobackup/2018_11_26/s_993180492.293.993180493 RECID=14 STAMP=993180493
  90. Deleted 6 objects


  91. RMAN> list copy;

  92. specification does not match any datafile copy in the repository
  93. specification does not match any control file copy in the repository
  94. List of Archived Log Copies for database with db_unique_name ORCL
  95. =====================================================================

  96. Key     Thrd Seq     S Low Time
  97. ------- ---- ------- - ---------
  98. 1       1    6       A 22-NOV-13
  99.         Name: +FRA/orcl/archivelog/2013_11_22/thread_1_seq_6.260.832198939

  100. 2       1    7       A 22-NOV-13
  101.         Name: +FRA/orcl/archivelog/2013_11_22/thread_1_seq_7.267.832199113

  102. 3       1    8       A 22-NOV-13
  103.         Name: +FRA/orcl/archivelog/2018_11_26/thread_1_seq_8.270.993169353

  104. 4       1    9       A 26-NOV-18
  105.         Name: +FRA/orcl/archivelog/2018_11_26/thread_1_seq_9.271.993169399

  106. 5       1    10      A 26-NOV-18
  107.         Name: +FRA/orcl/archivelog/2018_11_26/thread_1_seq_10.272.993169437

  108. 6       1    11      A 26-NOV-18
  109.         Name: +FRA/orcl/archivelog/2018_11_26/thread_1_seq_11.273.993169487

  110. 7       1    12      A 26-NOV-18
  111.         Name: +FRA/orcl/archivelog/2018_11_26/thread_1_seq_12.274.993169533

  112. 8       1    13      A 26-NOV-18
  113.         Name: +FRA/orcl/archivelog/2018_11_26/thread_1_seq_13.275.993169593

  114. 9       1    14      A 26-NOV-18
  115.         Name: +FRA/orcl/archivelog/2018_11_26/thread_1_seq_14.276.993169655

  116. 10      1    15      A 26-NOV-18
  117.         Name: +FRA/orcl/archivelog/2018_11_26/thread_1_seq_15.277.993169705

  118. 11      1    16      A 26-NOV-18
  119.         Name: +FRA/orcl/archivelog/2018_11_26/thread_1_seq_16.278.993169753

  120. 12      1    17      A 26-NOV-18
  121.         Name: +FRA/orcl/archivelog/2018_11_26/thread_1_seq_17.279.993169805

  122. 13      1    18      A 26-NOV-18
  123.         Name: +FRA/orcl/archivelog/2018_11_26/thread_1_seq_18.280.993169867

  124. 14      1    19      A 26-NOV-18
  125.         Name: +FRA/orcl/archivelog/2018_11_26/thread_1_seq_19.281.993169933

  126. 15      1    20      A 26-NOV-18
  127.         Name: +FRA/orcl/archivelog/2018_11_26/thread_1_seq_20.282.993169981

  128. 16      1    21      A 26-NOV-18
  129.         Name: +FRA/orcl/archivelog/2018_11_26/thread_1_seq_21.283.993170155

  130. 17      1    22      A 26-NOV-18
  131.         Name: +FRA/orcl/archivelog/2018_11_26/thread_1_seq_22.284.993170401

  132. 18      1    23      A 26-NOV-18
  133.         Name: +FRA/orcl/archivelog/2018_11_26/thread_1_seq_23.285.993173819

  134. 19      1    24      A 26-NOV-18
  135.         Name: +FRA/orcl/archivelog/2018_11_26/thread_1_seq_24.269.993176269

  136. 20      1    25      A 26-NOV-18
  137.         Name: +FRA/orcl/archivelog/2018_11_26/thread_1_seq_25.268.993176731

  138. 21      1    26      A 26-NOV-18
  139.         Name: +FRA/orcl/archivelog/2018_11_26/thread_1_seq_26.262.993176731

  140. 22      1    27      A 26-NOV-18
  141.         Name: +FRA/orcl/archivelog/2018_11_26/thread_1_seq_27.263.993176735

  142. 23      1    28      A 26-NOV-18
  143.         Name: +FRA/orcl/archivelog/2018_11_26/thread_1_seq_28.266.993176741

  144. 24      1    29      A 26-NOV-18
  145.         Name: +FRA/orcl/archivelog/2018_11_26/thread_1_seq_29.265.993176743

  146. 25      1    30      A 26-NOV-18
  147.         Name: +FRA/orcl/archivelog/2018_11_26/thread_1_seq_30.264.993176747

  148. 26      1    31      A 26-NOV-18
  149.         Name: +FRA/orcl/archivelog/2018_11_26/thread_1_seq_31.261.993176749

  150. 27      1    32      A 26-NOV-18
  151.         Name: +FRA/orcl/archivelog/2018_11_26/thread_1_seq_32.287.993178433

  152. 28      1    33      A 26-NOV-18
  153.         Name: +FRA/orcl/archivelog/2018_11_26/thread_1_seq_33.286.993178495


  154. RMAN> delete copy;

  155. released channel: ORA_DISK_1
  156. released channel: ORA_DISK_2
  157. released channel: ORA_DISK_3
  158. released channel: ORA_DISK_4
  159. released channel: ORA_DISK_5
  160. released channel: ORA_DISK_6
  161. released channel: ORA_DISK_7
  162. released channel: ORA_DISK_8
  163. allocated channel: ORA_DISK_1
  164. channel ORA_DISK_1: SID=73 device type=DISK
  165. allocated channel: ORA_DISK_2
  166. channel ORA_DISK_2: SID=10 device type=DISK
  167. allocated channel: ORA_DISK_3
  168. channel ORA_DISK_3: SID=144 device type=DISK
  169. allocated channel: ORA_DISK_4
  170. channel ORA_DISK_4: SID=201 device type=DISK
  171. allocated channel: ORA_DISK_5
  172. channel ORA_DISK_5: SID=8 device type=DISK
  173. allocated channel: ORA_DISK_6
  174. channel ORA_DISK_6: SID=80 device type=DISK
  175. allocated channel: ORA_DISK_7
  176. channel ORA_DISK_7: SID=136 device type=DISK
  177. allocated channel: ORA_DISK_8
  178. channel ORA_DISK_8: SID=202 device type=DISK
  179. specification does not match any datafile copy in the repository
  180. specification does not match any control file copy in the repository
  181. List of Archived Log Copies for database with db_unique_name ORCL
  182. =====================================================================

  183. Key     Thrd Seq     S Low Time
  184. ------- ---- ------- - ---------
  185. 1       1    6       A 22-NOV-13
  186.         Name: +FRA/orcl/archivelog/2013_11_22/thread_1_seq_6.260.832198939

  187. 2       1    7       A 22-NOV-13
  188.         Name: +FRA/orcl/archivelog/2013_11_22/thread_1_seq_7.267.832199113

  189. 3       1    8       A 22-NOV-13
  190.         Name: +FRA/orcl/archivelog/2018_11_26/thread_1_seq_8.270.993169353

  191. 4       1    9       A 26-NOV-18
  192.         Name: +FRA/orcl/archivelog/2018_11_26/thread_1_seq_9.271.993169399

  193. 5       1    10      A 26-NOV-18
  194.         Name: +FRA/orcl/archivelog/2018_11_26/thread_1_seq_10.272.993169437

  195. 6       1    11      A 26-NOV-18
  196.         Name: +FRA/orcl/archivelog/2018_11_26/thread_1_seq_11.273.993169487

  197. 7       1    12      A 26-NOV-18
  198.         Name: +FRA/orcl/archivelog/2018_11_26/thread_1_seq_12.274.993169533

  199. 8       1    13      A 26-NOV-18
  200.         Name: +FRA/orcl/archivelog/2018_11_26/thread_1_seq_13.275.993169593

  201. 9       1    14      A 26-NOV-18
  202.         Name: +FRA/orcl/archivelog/2018_11_26/thread_1_seq_14.276.993169655

  203. 10      1    15      A 26-NOV-18
  204.         Name: +FRA/orcl/archivelog/2018_11_26/thread_1_seq_15.277.993169705

  205. 11      1    16      A 26-NOV-18
  206.         Name: +FRA/orcl/archivelog/2018_11_26/thread_1_seq_16.278.993169753

  207. 12      1    17      A 26-NOV-18
  208.         Name: +FRA/orcl/archivelog/2018_11_26/thread_1_seq_17.279.993169805

  209. 13      1    18      A 26-NOV-18
  210.         Name: +FRA/orcl/archivelog/2018_11_26/thread_1_seq_18.280.993169867

  211. 14      1    19      A 26-NOV-18
  212.         Name: +FRA/orcl/archivelog/2018_11_26/thread_1_seq_19.281.993169933

  213. 15      1    20      A 26-NOV-18
  214.         Name: +FRA/orcl/archivelog/2018_11_26/thread_1_seq_20.282.993169981

  215. 16      1    21      A 26-NOV-18
  216.         Name: +FRA/orcl/archivelog/2018_11_26/thread_1_seq_21.283.993170155

  217. 17      1    22      A 26-NOV-18
  218.         Name: +FRA/orcl/archivelog/2018_11_26/thread_1_seq_22.284.993170401

  219. 18      1    23      A 26-NOV-18
  220.         Name: +FRA/orcl/archivelog/2018_11_26/thread_1_seq_23.285.993173819

  221. 19      1    24      A 26-NOV-18
  222.         Name: +FRA/orcl/archivelog/2018_11_26/thread_1_seq_24.269.993176269

  223. 20      1    25      A 26-NOV-18
  224.         Name: +FRA/orcl/archivelog/2018_11_26/thread_1_seq_25.268.993176731

  225. 21      1    26      A 26-NOV-18
  226.         Name: +FRA/orcl/archivelog/2018_11_26/thread_1_seq_26.262.993176731

  227. 22      1    27      A 26-NOV-18
  228.         Name: +FRA/orcl/archivelog/2018_11_26/thread_1_seq_27.263.993176735

  229. 23      1    28      A 26-NOV-18
  230.         Name: +FRA/orcl/archivelog/2018_11_26/thread_1_seq_28.266.993176741

  231. 24      1    29      A 26-NOV-18
  232.         Name: +FRA/orcl/archivelog/2018_11_26/thread_1_seq_29.265.993176743

  233. 25      1    30      A 26-NOV-18
  234.         Name: +FRA/orcl/archivelog/2018_11_26/thread_1_seq_30.264.993176747

  235. 26      1    31      A 26-NOV-18
  236.         Name: +FRA/orcl/archivelog/2018_11_26/thread_1_seq_31.261.993176749

  237. 27      1    32      A 26-NOV-18
  238.         Name: +FRA/orcl/archivelog/2018_11_26/thread_1_seq_32.287.993178433

  239. 28      1    33      A 26-NOV-18
  240.         Name: +FRA/orcl/archivelog/2018_11_26/thread_1_seq_33.286.993178495


  241. Do you really want to delete the above objects (enter YES or NO)? YES
  242. deleted archived log
  243. archived log file name=+FRA/orcl/archivelog/2013_11_22/thread_1_seq_6.260.832198939 RECID=1 STAMP=832198941
  244. deleted archived log
  245. archived log file name=+FRA/orcl/archivelog/2013_11_22/thread_1_seq_7.267.832199113 RECID=2 STAMP=832199113
  246. deleted archived log
  247. archived log file name=+FRA/orcl/archivelog/2018_11_26/thread_1_seq_8.270.993169353 RECID=3 STAMP=993169358
  248. deleted archived log
  249. archived log file name=+FRA/orcl/archivelog/2018_11_26/thread_1_seq_9.271.993169399 RECID=4 STAMP=993169405
  250. deleted archived log
  251. archived log file name=+FRA/orcl/archivelog/2018_11_26/thread_1_seq_10.272.993169437 RECID=5 STAMP=993169442
  252. deleted archived log
  253. archived log file name=+FRA/orcl/archivelog/2018_11_26/thread_1_seq_11.273.993169487 RECID=6 STAMP=993169493
  254. deleted archived log
  255. archived log file name=+FRA/orcl/archivelog/2018_11_26/thread_1_seq_12.274.993169533 RECID=7 STAMP=993169539
  256. deleted archived log
  257. archived log file name=+FRA/orcl/archivelog/2018_11_26/thread_1_seq_13.275.993169593 RECID=8 STAMP=993169599
  258. deleted archived log
  259. archived log file name=+FRA/orcl/archivelog/2018_11_26/thread_1_seq_14.276.993169655 RECID=9 STAMP=993169661
  260. deleted archived log
  261. archived log file name=+FRA/orcl/archivelog/2018_11_26/thread_1_seq_15.277.993169705 RECID=10 STAMP=993169710
  262. deleted archived log
  263. archived log file name=+FRA/orcl/archivelog/2018_11_26/thread_1_seq_16.278.993169753 RECID=11 STAMP=993169759
  264. deleted archived log
  265. archived log file name=+FRA/orcl/archivelog/2018_11_26/thread_1_seq_17.279.993169805 RECID=12 STAMP=993169812
  266. deleted archived log
  267. archived log file name=+FRA/orcl/archivelog/2018_11_26/thread_1_seq_18.280.993169867 RECID=13 STAMP=993169872
  268. deleted archived log
  269. archived log file name=+FRA/orcl/archivelog/2018_11_26/thread_1_seq_19.281.993169933 RECID=14 STAMP=993169943
  270. deleted archived log
  271. archived log file name=+FRA/orcl/archivelog/2018_11_26/thread_1_seq_20.282.993169981 RECID=15 STAMP=993169988
  272. deleted archived log
  273. archived log file name=+FRA/orcl/archivelog/2018_11_26/thread_1_seq_21.283.993170155 RECID=16 STAMP=993170161
  274. deleted archived log
  275. archived log file name=+FRA/orcl/archivelog/2018_11_26/thread_1_seq_22.284.993170401 RECID=17 STAMP=993170406
  276. deleted archived log
  277. archived log file name=+FRA/orcl/archivelog/2018_11_26/thread_1_seq_23.285.993173819 RECID=18 STAMP=993173823
  278. deleted archived log
  279. archived log file name=+FRA/orcl/archivelog/2018_11_26/thread_1_seq_24.269.993176269 RECID=19 STAMP=993176272
  280. deleted archived log
  281. archived log file name=+FRA/orcl/archivelog/2018_11_26/thread_1_seq_25.268.993176731 RECID=20 STAMP=993176730
  282. deleted archived log
  283. archived log file name=+FRA/orcl/archivelog/2018_11_26/thread_1_seq_26.262.993176731 RECID=21 STAMP=993176731
  284. deleted archived log
  285. archived log file name=+FRA/orcl/archivelog/2018_11_26/thread_1_seq_27.263.993176735 RECID=22 STAMP=993176735
  286. deleted archived log
  287. archived log file name=+FRA/orcl/archivelog/2018_11_26/thread_1_seq_28.266.993176741 RECID=23 STAMP=993176741
  288. deleted archived log
  289. archived log file name=+FRA/orcl/archivelog/2018_11_26/thread_1_seq_29.265.993176743 RECID=24 STAMP=993176742
  290. deleted archived log
  291. archived log file name=+FRA/orcl/archivelog/2018_11_26/thread_1_seq_30.264.993176747 RECID=25 STAMP=993176746
  292. deleted archived log
  293. archived log file name=+FRA/orcl/archivelog/2018_11_26/thread_1_seq_31.261.993176749 RECID=26 STAMP=993176750
  294. deleted archived log
  295. archived log file name=+FRA/orcl/archivelog/2018_11_26/thread_1_seq_32.287.993178433 RECID=27 STAMP=993178439
  296. deleted archived log
  297. archived log file name=+FRA/orcl/archivelog/2018_11_26/thread_1_seq_33.286.993178495 RECID=28 STAMP=993178502
  298. Deleted 28 objects


  299. RMAN> backup tag '1T_WHOLE_INCR0' incremental level 0  database plus archivelog delete all input;


  300. Starting backup at 26-NOV-18
  301. current log archived
  302. released channel: ORA_DISK_1
  303. released channel: ORA_DISK_2
  304. released channel: ORA_DISK_3
  305. released channel: ORA_DISK_4
  306. released channel: ORA_DISK_5
  307. released channel: ORA_DISK_6
  308. released channel: ORA_DISK_7
  309. released channel: ORA_DISK_8
  310. allocated channel: ORA_SBT_TAPE_1
  311. channel ORA_SBT_TAPE_1: SID=73 device type=SBT_TAPE
  312. channel ORA_SBT_TAPE_1: Oracle Secure Backup
  313. allocated channel: ORA_SBT_TAPE_2
  314. channel ORA_SBT_TAPE_2: SID=10 device type=SBT_TAPE
  315. channel ORA_SBT_TAPE_2: Oracle Secure Backup
  316. channel ORA_SBT_TAPE_1: starting archived log backup set
  317. channel ORA_SBT_TAPE_1: specifying archived log(s) in backup set
  318. input archived log thread=1 sequence=34 RECID=29 STAMP=993186538
  319. channel ORA_SBT_TAPE_1: starting piece 1 at 26-NOV-18
  320. channel ORA_SBT_TAPE_1: finished piece 1 at 26-NOV-18
  321. piece handle=0jtj5knc_1_1 tag=1T_WHOLE_INCR0 comment=API Version 2.0,MMS Version 10.3.0.2
  322. channel ORA_SBT_TAPE_1: backup set complete, elapsed time: 00:00:35
  323. channel ORA_SBT_TAPE_1: deleting archived log(s)
  324. archived log file name=+FRA/orcl/archivelog/2018_11_26/thread_1_seq_34.286.993186533 RECID=29 STAMP=993186538
  325. Finished backup at 26-NOV-18

  326. Starting backup at 26-NOV-18
  327. using channel ORA_SBT_TAPE_1
  328. using channel ORA_SBT_TAPE_2
  329. channel ORA_SBT_TAPE_1: starting incremental level 0 datafile backup set
  330. channel ORA_SBT_TAPE_1: specifying datafile(s) in backup set
  331. input datafile file number=00001 name=+DATA/orcl/datafile/system.256.832197063
  332. input datafile file number=00004 name=+DATA/orcl/datafile/users.259.832197065
  333. input datafile file number=00005 name=+DATA/orcl/datafile/example.265.832197353
  334. channel ORA_SBT_TAPE_1: starting piece 1 at 26-NOV-18
  335. channel ORA_SBT_TAPE_2: starting incremental level 0 datafile backup set
  336. channel ORA_SBT_TAPE_2: specifying datafile(s) in backup set
  337. input datafile file number=00002 name=+DATA/orcl/datafile/sysaux.257.832197065
  338. input datafile file number=00003 name=+DATA/orcl/datafile/undotbs1.258.832197065
  339. channel ORA_SBT_TAPE_2: starting piece 1 at 26-NOV-18
  340. channel ORA_SBT_TAPE_2: finished piece 1 at 26-NOV-18
  341. piece handle=0ltj5kog_1_1 tag=1T_WHOLE_INCR0 comment=API Version 2.0,MMS Version 10.3.0.2
  342. channel ORA_SBT_TAPE_2: backup set complete, elapsed time: 00:02:06
  343. channel ORA_SBT_TAPE_1: finished piece 1 at 26-NOV-18
  344. piece handle=0ktj5kog_1_1 tag=1T_WHOLE_INCR0 comment=API Version 2.0,MMS Version 10.3.0.2
  345. channel ORA_SBT_TAPE_1: backup set complete, elapsed time: 00:02:46
  346. Finished backup at 26-NOV-18

  347. Starting backup at 26-NOV-18
  348. current log archived
  349. using channel ORA_SBT_TAPE_1
  350. using channel ORA_SBT_TAPE_2
  351. channel ORA_SBT_TAPE_1: starting archived log backup set
  352. channel ORA_SBT_TAPE_1: specifying archived log(s) in backup set
  353. input archived log thread=1 sequence=35 RECID=30 STAMP=993186742
  354. channel ORA_SBT_TAPE_1: starting piece 1 at 26-NOV-18
  355. channel ORA_SBT_TAPE_1: finished piece 1 at 26-NOV-18
  356. piece handle=0mtj5ktn_1_1 tag=1T_WHOLE_INCR0 comment=API Version 2.0,MMS Version 10.3.0.2
  357. channel ORA_SBT_TAPE_1: backup set complete, elapsed time: 00:00:25
  358. channel ORA_SBT_TAPE_1: deleting archived log(s)
  359. archived log file name=+FRA/orcl/archivelog/2018_11_26/thread_1_seq_35.286.993186743 RECID=30 STAMP=993186742
  360. Finished backup at 26-NOV-18

  361. Starting Control File and SPFILE Autobackup at 26-NOV-18
  362. piece handle=c-1359978017-20181126-01 comment=API Version 2.0,MMS Version 10.3.0.2
  363. Finished Control File and SPFILE Autobackup at 26-NOV-18

  364. RMAN> list backup;


  365. List of Backup Sets
  366. ===================


  367. BS Key  Size       Device Type Elapsed Time Completion Time
  368. ------- ---------- ----------- ------------ ---------------
  369. 15      38.25M     SBT_TAPE    00:00:32     26-NOV-18      
  370.         BP Key: 15   Status: AVAILABLE  Compressed: NO  Tag: 1T_WHOLE_INCR0
  371.         Handle: 0jtj5knc_1_1   Media: station80-000001

  372.   List of Archived Logs in backup set 15
  373.   Thrd Seq     Low SCN    Low Time  Next SCN   Next Time
  374.   ---- ------- ---------- --------- ---------- ---------
  375.   1    34      1050037    26-NOV-18 1071412    26-NOV-18

  376. BS Key  Type LV Size       Device Type Elapsed Time Completion Time
  377. ------- ---- -- ---------- ----------- ------------ ---------------
  378. 16      Incr 0  401.13M    SBT_TAPE    00:02:00     26-NOV-18      
  379.         BP Key: 16   Status: AVAILABLE  Compressed: NO  Tag: 1T_WHOLE_INCR0
  380.         Handle: 0ltj5kog_1_1   Media: station80-000002
  381.   List of Datafiles in backup set 16
  382.   File LV Type Ckp SCN    Ckp Time  Name
  383.   ---- -- ---- ---------- --------- ----
  384.   2    0  Incr 1071446    26-NOV-18 +DATA/orcl/datafile/sysaux.257.832197065
  385.   3    0  Incr 1071446    26-NOV-18 +DATA/orcl/datafile/undotbs1.258.832197065

  386. BS Key  Type LV Size       Device Type Elapsed Time Completion Time
  387. ------- ---- -- ---------- ----------- ------------ ---------------
  388. 17      Incr 0  666.81M    SBT_TAPE    00:02:43     26-NOV-18      
  389.         BP Key: 17   Status: AVAILABLE  Compressed: NO  Tag: 1T_WHOLE_INCR0
  390.         Handle: 0ktj5kog_1_1   Media: station80-000001
  391.   List of Datafiles in backup set 17
  392.   File LV Type Ckp SCN    Ckp Time  Name
  393.   ---- -- ---- ---------- --------- ----
  394.   1    0  Incr 1071444    26-NOV-18 +DATA/orcl/datafile/system.256.832197063
  395.   4    0  Incr 1071444    26-NOV-18 +DATA/orcl/datafile/users.259.832197065
  396.   5    0  Incr 1071444    26-NOV-18 +DATA/orcl/datafile/example.265.832197353

  397. BS Key  Size       Device Type Elapsed Time Completion Time
  398. ------- ---------- ----------- ------------ ---------------
  399. 18      128.00K    SBT_TAPE    00:00:20     26-NOV-18      
  400.         BP Key: 18   Status: AVAILABLE  Compressed: NO  Tag: 1T_WHOLE_INCR0
  401.         Handle: 0mtj5ktn_1_1   Media: station80-000001

  402.   List of Archived Logs in backup set 18
  403.   Thrd Seq     Low SCN    Low Time  Next SCN   Next Time
  404.   ---- ------- ---------- --------- ---------- ---------
  405.   1    35      1071412    26-NOV-18 1071546    26-NOV-18

  406. BS Key  Type LV Size       Device Type Elapsed Time Completion Time
  407. ------- ---- -- ---------- ----------- ------------ ---------------
  408. 19      Full    9.38M      SBT_TAPE    00:00:23     26-NOV-18      
  409.         BP Key: 19   Status: AVAILABLE  Compressed: NO  Tag: TAG20181126T051248
  410.         Handle: c-1359978017-20181126-01   Media: station80-000001
  411.   SPFILE Included: Modification time: 26-NOV-18
  412.   SPFILE db_unique_name: ORCL
  413.   Control File Included: Ckp SCN: 1071567      Ckp time: 26-NOV-18

  414. RMAN>
复制代码
  1. [root@station80 mhvtl]# du -sh *
  2. 8.0K        CLN101L4
  3. 8.0K        CLN102L5
  4. 8.0K        CLN303TA
  5. 8.0K        E01001L4
  6. 8.0K        E01002L4
  7. 8.0K        E01003L4
  8. 8.0K        E01004L4
  9. 8.0K        E01005L4
  10. 8.0K        E01006L4
  11. 8.0K        E01007L4
  12. 8.0K        E01008L4
  13. 8.0K        E01009L4
  14. 8.0K        E01010L4
  15. 8.0K        E01011L4
  16. 8.0K        E01012L4
  17. 8.0K        E01013L4
  18. 8.0K        E01014L4
  19. 8.0K        E01015L4
  20. 8.0K        E01016L4
  21. 8.0K        E01017L4
  22. 8.0K        E01018L4
  23. 8.0K        E01019L4
  24. 8.0K        E01020L4
  25. 8.0K        F01030L5
  26. 8.0K        F01031L5
  27. 8.0K        F01032L5
  28. 8.0K        F01033L5
  29. 8.0K        F01034L5
  30. 8.0K        F01035L5
  31. 8.0K        F01036L5
  32. 8.0K        F01037L5
  33. 8.0K        F01038L5
  34. 8.0K        F01039L5
  35. 8.0K        G03001TA
  36. 8.0K        G03002TA
  37. 8.0K        G03003TA
  38. 8.0K        G03004TA
  39. 8.0K        G03005TA
  40. 8.0K        G03006TA
  41. 8.0K        G03007TA
  42. 8.0K        G03008TA
  43. 8.0K        G03009TA
  44. 8.0K        G03010TA
  45. 8.0K        G03011TA
  46. 8.0K        G03012TA
  47. 8.0K        G03013TA
  48. 8.0K        G03014TA
  49. 8.0K        G03015TA
  50. 8.0K        G03016TA
  51. 8.0K        G03017TA
  52. 8.0K        G03018TA
  53. 8.0K        G03019TA
  54. 8.0K        G03020TA
  55. 8.0K        G03021TA
  56. 8.0K        G03022TA
  57. 8.0K        G03023TA
  58. 8.0K        G03024TA
  59. 8.0K        G03025TA
  60. 8.0K        G03026TA
  61. 8.0K        G03027TA
  62. 8.0K        G03028TA
  63. 8.0K        G03029TA
  64. 8.0K        G03030TA
  65. 8.0K        G03031TA
  66. 8.0K        G03032TA
  67. 8.0K        G03033TA
  68. 8.0K        G03034TA
  69. 8.0K        G03035TA
  70. 8.0K        G03036TA
  71. 8.0K        G03037TA
  72. 8.0K        G03038TA
  73. 8.0K        G03039TA
  74. [root@station80 mhvtl]# obtool
  75. Oracle Secure Backup 10.3.0.3.0  
  76. Warning: auto-login failed - login token has expired
  77. login: admin
  78. Password: ******
  79. ob> lsvol -L lib01 -l
  80. Inventory of library lib01:
  81.     in    1:             vacant
  82.     in    2:             vacant
  83.     in    3:             barcode E01003L4, oid 102
  84.     in    4:             barcode E01004L4, oid 103
  85.     in    5:             barcode E01005L4, oid 104
  86.     in    6:             barcode E01006L4, oid 105
  87.     in    7:             barcode E01007L4, oid 106
  88.     in    8:             barcode E01008L4, oid 107
  89.     in    9:             barcode E01009L4, oid 108
  90.     in    10:            barcode E01010L4, oid 109
  91.     in    11:            barcode E01011L4, oid 110
  92.     in    12:            barcode E01012L4, oid 111
  93.     in    13:            barcode E01013L4, oid 112
  94.     in    14:            barcode E01014L4, oid 113
  95.     in    15:            barcode E01015L4, oid 114
  96.     in    16:            barcode E01016L4, oid 115
  97.     in    17:            barcode E01017L4, oid 116
  98.     in    18:            barcode E01018L4, oid 117
  99.     in    19:            barcode E01019L4, oid 118
  100.     in    20:            barcode E01020L4, oid 119
  101.     in    21:            vacant
  102.     in    22:            barcode CLN101L4, oid 120
  103.     in    23:            barcode CLN102L5, oid 121
  104.     in    24:            vacant
  105.     in    25:            vacant
  106.     in    26:            vacant
  107.     in    27:            vacant
  108.     in    28:            vacant
  109.     in    29:            vacant
  110.     in    30:            barcode F01030L5, oid 122
  111.     in    31:            barcode F01031L5, oid 123
  112.     in    32:            barcode F01032L5, oid 124
  113.     in    33:            barcode F01033L5, oid 125
  114.     in    34:            barcode F01034L5, oid 126
  115.     in    35:            barcode F01035L5, oid 127
  116.     in    36:            barcode F01036L5, oid 128
  117.     in    37:            barcode F01037L5, oid 129
  118.     in    38:            barcode F01038L5, oid 130
  119.     in    39:            barcode F01039L5, oid 131
  120.     in    iee1:          vacant
  121.     in    iee2:          vacant
  122.     in    iee3:          vacant
  123.     in    iee4:          vacant
  124.     in    dte1:          vacant
  125.     in    dte2:          vacant
  126.     in    dte3:          volume station80-000002, barcode E01002L4, oid 135, 0 kb remaining, content manages reuse, lastse 2
  127.     in    dte4:          volume station80-000001, barcode E01001L4, oid 133, content manages reuse, lastse 1
  128. ob> lsjob
  129. ob> lsjob
  130. Job ID           Sched time  Contents                       State
  131. ---------------- ----------- ------------------------------ ---------------------------------------
  132. oracle/4         none        database orcl (dbid=1359978017) processed; Oracle job(s) scheduled
  133. oracle/4.1       none        controlfile autobackup         running since 2018/11/26.05:12
  134. ob> lspiece
  135.     POID Database   Content    Copy Created      Host             Piece name
  136.      100 orcl       archivelog    0 11/26.05:09  station80        0jtj5knc_1_1
  137.      101 orcl       incremental    0 11/26.05:09  station80        0ltj5kog_1_1
  138.      102 orcl       incremental    0 11/26.05:09  station80        0ktj5kog_1_1
  139.      103 orcl       archivelog    0 11/26.05:12  station80        0mtj5ktn_1_1
  140.      104 orcl       autobackup    0 11/26.05:12  station80        c-1359978017-20181126-01
  141. ob> lsvol -L lib01 -l
  142. Inventory of library lib01:
  143.     in    1:             volume station80-000001, barcode E01001L4, oid 133, 0 kb remaining, content manages reuse
  144.     in    2:             volume station80-000002, barcode E01002L4, oid 135, 0 kb remaining, content manages reuse
  145.     in    3:             barcode E01003L4, oid 102
  146.     in    4:             barcode E01004L4, oid 103
  147.     in    5:             barcode E01005L4, oid 104
  148.     in    6:             barcode E01006L4, oid 105
  149.     in    7:             barcode E01007L4, oid 106
  150.     in    8:             barcode E01008L4, oid 107
  151.     in    9:             barcode E01009L4, oid 108
  152.     in    10:            barcode E01010L4, oid 109
  153.     in    11:            barcode E01011L4, oid 110
  154.     in    12:            barcode E01012L4, oid 111
  155.     in    13:            barcode E01013L4, oid 112
  156.     in    14:            barcode E01014L4, oid 113
  157.     in    15:            barcode E01015L4, oid 114
  158.     in    16:            barcode E01016L4, oid 115
  159.     in    17:            barcode E01017L4, oid 116
  160.     in    18:            barcode E01018L4, oid 117
  161.     in    19:            barcode E01019L4, oid 118
  162.     in    20:            barcode E01020L4, oid 119
  163.     in    21:            vacant
  164.     in    22:            barcode CLN101L4, oid 120
  165.     in    23:            barcode CLN102L5, oid 121
  166.     in    24:            vacant
  167.     in    25:            vacant
  168.     in    26:            vacant
  169.     in    27:            vacant
  170.     in    28:            vacant
  171.     in    29:            vacant
  172.     in    30:            barcode F01030L5, oid 122
  173.     in    31:            barcode F01031L5, oid 123
  174.     in    32:            barcode F01032L5, oid 124
  175.     in    33:            barcode F01033L5, oid 125
  176.     in    34:            barcode F01034L5, oid 126
  177.     in    35:            barcode F01035L5, oid 127
  178.     in    36:            barcode F01036L5, oid 128
  179.     in    37:            barcode F01037L5, oid 129
  180.     in    38:            barcode F01038L5, oid 130
  181.     in    39:            barcode F01039L5, oid 131
  182.     in    iee1:          vacant
  183.     in    iee2:          vacant
  184.     in    iee3:          vacant
  185.     in    iee4:          vacant
  186.     in    dte1:          vacant
  187.     in    dte2:          vacant
  188.     in    dte3:          vacant
  189.     in    dte4:          vacant
  190. ob> exit
  191. [root@station80 mhvtl]# du -sh *
  192. 8.0K        CLN101L4
  193. 8.0K        CLN102L5
  194. 8.0K        CLN303TA
  195. 282M        E01001L4
  196. 101M        E01002L4
  197. 8.0K        E01003L4
  198. 8.0K        E01004L4
  199. 8.0K        E01005L4
  200. 8.0K        E01006L4
  201. 8.0K        E01007L4
  202. 8.0K        E01008L4
  203. 8.0K        E01009L4
  204. 8.0K        E01010L4
  205. 8.0K        E01011L4
  206. 8.0K        E01012L4
  207. 8.0K        E01013L4
  208. 8.0K        E01014L4
  209. 8.0K        E01015L4
  210. 8.0K        E01016L4
  211. 8.0K        E01017L4
  212. 8.0K        E01018L4
  213. 8.0K        E01019L4
  214. 8.0K        E01020L4
  215. 8.0K        F01030L5
  216. 8.0K        F01031L5
  217. 8.0K        F01032L5
  218. 8.0K        F01033L5
  219. 8.0K        F01034L5
  220. 8.0K        F01035L5
  221. 8.0K        F01036L5
  222. 8.0K        F01037L5
  223. 8.0K        F01038L5
  224. 8.0K        F01039L5
  225. 8.0K        G03001TA
  226. 8.0K        G03002TA
  227. 8.0K        G03003TA
  228. 8.0K        G03004TA
  229. 8.0K        G03005TA
  230. 8.0K        G03006TA
  231. 8.0K        G03007TA
  232. 8.0K        G03008TA
  233. 8.0K        G03009TA
  234. 8.0K        G03010TA
  235. 8.0K        G03011TA
  236. 8.0K        G03012TA
  237. 8.0K        G03013TA
  238. 8.0K        G03014TA
  239. 8.0K        G03015TA
  240. 8.0K        G03016TA
  241. 8.0K        G03017TA
  242. 8.0K        G03018TA
  243. 8.0K        G03019TA
  244. 8.0K        G03020TA
  245. 8.0K        G03021TA
  246. 8.0K        G03022TA
  247. 8.0K        G03023TA
  248. 8.0K        G03024TA
  249. 8.0K        G03025TA
  250. 8.0K        G03026TA
  251. 8.0K        G03027TA
  252. 8.0K        G03028TA
  253. 8.0K        G03029TA
  254. 8.0K        G03030TA
  255. 8.0K        G03031TA
  256. 8.0K        G03032TA
  257. 8.0K        G03033TA
  258. 8.0K        G03034TA
  259. 8.0K        G03035TA
  260. 8.0K        G03036TA
  261. 8.0K        G03037TA
  262. 8.0K        G03038TA
  263. 8.0K        G03039TA
  264. [root@station80 mhvtl]#
复制代码
课程第24次(2018-11-27星期二)

  1. run {
  2. allocate channel oem_disk_backup device type disk;
  3. recover copy of database with tag 'ORA_OEM_LEVEL_0';
  4. backup incremental level 1 cumulative  copies=1 for recover of copy with tag 'ORA_OEM_LEVEL_0' database;
  5. release channel oem_disk_backup;
  6. allocate channel oem_sbt_backup1 type 'SBT_TAPE' format '%U' parms 'ENV=(OB_MEDIA_FAMILY=station90)';
  7. allocate channel oem_sbt_backup2 type 'SBT_TAPE' format '%U' parms 'ENV=(OB_MEDIA_FAMILY=station90)';
  8. backup archivelog all not backed up;
  9. }
  10. allocate channel for maintenance device type 'SBT_TAPE' parms 'ENV=(OB_MEDIA_FAMILY=station90)';
  11. delete noprompt obsolete recovery window of 31 days device type 'SBT_TAPE';
复制代码
  1. run {
  2. allocate channel oem_disk_backup device type disk;
  3. recover copy of database with tag 'ORA_OEM_LEVEL_0';
  4. backup incremental level 1 cumulative  copies=1 for recover of copy with tag 'ORA_OEM_LEVEL_0' database;
  5. release channel oem_disk_backup;
  6. allocate channel oem_sbt_backup1 type 'SBT_TAPE' format '%U' parms 'ENV=(OB_MEDIA_FAMILY=station90)';
  7. allocate channel oem_sbt_backup2 type 'SBT_TAPE' format '%U' parms 'ENV=(OB_MEDIA_FAMILY=station90)';
  8. backup recovery area;
  9. }
  10. allocate channel for maintenance device type 'SBT_TAPE' parms 'ENV=(OB_MEDIA_FAMILY=station90)';
  11. delete noprompt obsolete recovery window of 31 days device type 'SBT_TAPE';
复制代码
1Z0-052第15章(24/40)

1Z0-053第3章(25/40)

课程第25次(2018-11-28星期三)

对备份集再备份(转移备份集):
  1. RMAN> run {
  2. 2> allocate channel c1 device type disk format '/home/oracle/backup/users_%U';
  3. 3> backup tablespace users;
  4. 4> }

  5. released channel: ORA_SBT_TAPE_1
  6. released channel: ORA_SBT_TAPE_2
  7. allocated channel: c1
  8. channel c1: SID=137 device type=DISK

  9. Starting backup at 2018-12-01:23:58:50
  10. channel c1: starting full datafile backup set
  11. channel c1: specifying datafile(s) in backup set
  12. input datafile file number=00004 name=+DATA/orcl/datafile/users.259.992728183
  13. channel c1: starting piece 1 at 2018-12-01:23:58:50
  14. channel c1: finished piece 1 at 2018-12-01:23:58:51
  15. piece handle=/home/oracle/backup/users_45tjnh5q_1_1 tag=TAG20181201T235850 comment=NONE
  16. channel c1: backup set complete, elapsed time: 00:00:01
  17. Finished backup at 2018-12-01:23:58:51
  18. released channel: c1

  19. RMAN> list backup of tablespace users;


  20. List of Backup Sets
  21. ===================


  22. BS Key  Type LV Size       Device Type Elapsed Time Completion Time   
  23. ------- ---- -- ---------- ----------- ------------ -------------------
  24. 106     Incr 0  669.75M    SBT_TAPE    00:00:30     2018-11-28:19:25:55
  25.         BP Key: 107   Status: AVAILABLE  Compressed: NO  Tag: 1T_WHOLE_INCR0
  26.         Handle: 3mtjcfl5_1_1   Media: station90-000010
  27.   List of Datafiles in backup set 106
  28.   File LV Type Ckp SCN    Ckp Time            Name
  29.   ---- -- ---- ---------- ------------------- ----
  30.   4    0  Incr 4674858    2018-11-28:19:25:25 +DATA/orcl/datafile/users.259.992728183

  31. BS Key  Type LV Size       Device Type Elapsed Time Completion Time   
  32. ------- ---- -- ---------- ----------- ------------ -------------------
  33. 110     Incr 0  670.75M    SBT_TAPE    00:00:30     2018-11-29:19:28:22
  34.         BP Key: 111   Status: AVAILABLE  Compressed: NO  Tag: 2T_WHOLE_INCR0
  35.         Handle: 3qtjf45o_1_1   Media: station90-000010
  36.   List of Datafiles in backup set 110
  37.   File LV Type Ckp SCN    Ckp Time            Name
  38.   ---- -- ---- ---------- ------------------- ----
  39.   4    0  Incr 4678909    2018-11-29:19:27:53 +DATA/orcl/datafile/users.259.992728183

  40. BS Key  Type LV Size       Device Type Elapsed Time Completion Time   
  41. ------- ---- -- ---------- ----------- ------------ -------------------
  42. 113     Incr 0  672.00M    SBT_TAPE    00:00:23     2018-12-01:00:03:45
  43.         BP Key: 114   Status: AVAILABLE  Compressed: NO  Tag: 3T_WHOLE_INCR0
  44.         Handle: 3utjkt2a_1_1   Media: station90-000009
  45.   List of Datafiles in backup set 113
  46.   File LV Type Ckp SCN    Ckp Time            Name
  47.   ---- -- ---- ---------- ------------------- ----
  48.   4    0  Incr 4730677    2018-12-01:00:02:41 +DATA/orcl/datafile/users.259.992728183

  49. BS Key  Type LV Size       Device Type Elapsed Time Completion Time   
  50. ------- ---- -- ---------- ----------- ------------ -------------------
  51. 121     Full    8.22M      DISK        00:00:00     2018-12-01:23:58:50
  52.         BP Key: 122   Status: AVAILABLE  Compressed: NO  Tag: TAG20181201T235850
  53.         Piece Name: /home/oracle/backup/users_45tjnh5q_1_1
  54.   List of Datafiles in backup set 121
  55.   File LV Type Ckp SCN    Ckp Time            Name
  56.   ---- -- ---- ---------- ------------------- ----
  57.   4       Full 4730677    2018-12-01:00:02:41 +DATA/orcl/datafile/users.259.992728183

  58. RMAN> backup backupset 121  force  delete input;

  59. Starting backup at 2018-12-02:00:00:19
  60. allocated channel: ORA_SBT_TAPE_1
  61. channel ORA_SBT_TAPE_1: SID=137 device type=SBT_TAPE
  62. channel ORA_SBT_TAPE_1: Oracle Secure Backup
  63. allocated channel: ORA_SBT_TAPE_2
  64. channel ORA_SBT_TAPE_2: SID=24 device type=SBT_TAPE
  65. channel ORA_SBT_TAPE_2: Oracle Secure Backup
  66. channel ORA_SBT_TAPE_1: input backup set: count=133, stamp=993772730, piece=1
  67. channel ORA_SBT_TAPE_1: starting piece 1 at 2018-12-02:00:00:32
  68. channel ORA_SBT_TAPE_1: backup piece /home/oracle/backup/users_45tjnh5q_1_1
  69. piece handle=45tjnh5q_1_2 comment=API Version 2.0,MMS Version 10.4.0.4
  70. channel ORA_SBT_TAPE_1: finished piece 1 at 2018-12-02:00:00:57
  71. channel ORA_SBT_TAPE_1: backup piece complete, elapsed time: 00:00:25
  72. deleted backup piece
  73. backup piece handle=/home/oracle/backup/users_45tjnh5q_1_1 RECID=122 STAMP=993772730
  74. Finished backup at 2018-12-02:00:00:57

  75. RMAN> list backup of users;

  76. RMAN-00571: ===========================================================
  77. RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
  78. RMAN-00571: ===========================================================
  79. RMAN-00558: error encountered while parsing input commands
  80. RMAN-01009: syntax error: found "identifier": expecting one of: "archivelog, controlfile, database, datafile, foreign, spfile, tablespace"
  81. RMAN-01008: the bad identifier was: users
  82. RMAN-01007: at line 1 column 16 file: standard input

  83. RMAN> list backup of  tablespace users;


  84. List of Backup Sets
  85. ===================


  86. BS Key  Type LV Size       Device Type Elapsed Time Completion Time   
  87. ------- ---- -- ---------- ----------- ------------ -------------------
  88. 106     Incr 0  669.75M    SBT_TAPE    00:00:30     2018-11-28:19:25:55
  89.         BP Key: 107   Status: AVAILABLE  Compressed: NO  Tag: 1T_WHOLE_INCR0
  90.         Handle: 3mtjcfl5_1_1   Media: station90-000010
  91.   List of Datafiles in backup set 106
  92.   File LV Type Ckp SCN    Ckp Time            Name
  93.   ---- -- ---- ---------- ------------------- ----
  94.   4    0  Incr 4674858    2018-11-28:19:25:25 +DATA/orcl/datafile/users.259.992728183

  95. BS Key  Type LV Size       Device Type Elapsed Time Completion Time   
  96. ------- ---- -- ---------- ----------- ------------ -------------------
  97. 110     Incr 0  670.75M    SBT_TAPE    00:00:30     2018-11-29:19:28:22
  98.         BP Key: 111   Status: AVAILABLE  Compressed: NO  Tag: 2T_WHOLE_INCR0
  99.         Handle: 3qtjf45o_1_1   Media: station90-000010
  100.   List of Datafiles in backup set 110
  101.   File LV Type Ckp SCN    Ckp Time            Name
  102.   ---- -- ---- ---------- ------------------- ----
  103.   4    0  Incr 4678909    2018-11-29:19:27:53 +DATA/orcl/datafile/users.259.992728183

  104. BS Key  Type LV Size       Device Type Elapsed Time Completion Time   
  105. ------- ---- -- ---------- ----------- ------------ -------------------
  106. 113     Incr 0  672.00M    SBT_TAPE    00:00:23     2018-12-01:00:03:45
  107.         BP Key: 114   Status: AVAILABLE  Compressed: NO  Tag: 3T_WHOLE_INCR0
  108.         Handle: 3utjkt2a_1_1   Media: station90-000009
  109.   List of Datafiles in backup set 113
  110.   File LV Type Ckp SCN    Ckp Time            Name
  111.   ---- -- ---- ---------- ------------------- ----
  112.   4    0  Incr 4730677    2018-12-01:00:02:41 +DATA/orcl/datafile/users.259.992728183

  113. BS Key  Type LV Size       Device Type Elapsed Time Completion Time   
  114. ------- ---- -- ---------- ----------- ------------ -------------------
  115. 121     Full    8.22M      SBT_TAPE    00:00:00     2018-12-01:23:58:50
  116.         BP Key: 123   Status: AVAILABLE  Compressed: NO  Tag: TAG20181201T235850
  117.         Handle: 45tjnh5q_1_2   Media: station90-000009
  118.   List of Datafiles in backup set 121
  119.   File LV Type Ckp SCN    Ckp Time            Name
  120.   ---- -- ---- ---------- ------------------- ----
  121.   4       Full 4730677    2018-12-01:00:02:41 +DATA/orcl/datafile/users.259.992728183

  122. RMAN>
复制代码

2018-11-28 19:25:45   1T_WHOLE_INCR0

2018-11-29 19:28:17   2T_WHOLE_INCR0

2018-11-30 19:28:17 ---- 1T  obsolete  (1 days)

2018-11-30 23:59:00 ----need backup (1.5 days)

2018-12-01 00:02:00 ---read  only  users +3T_WHOLE_INCR0

2018-12-01 23:50:00  4T_WHOLE_INCR0  skip users

2018-12-02 00:00:01  5T_WHOLE_INCR0  backup users again

1Z0-053第2章(26/40)

1Z0-053第4章(27/40)
  1. [oracle@station90 orcl]$ mkdir wallet
  2. [oracle@station90 orcl]$ pwd
  3. /u01/app/oracle/admin/orcl
复制代码
  1. SQL> conn / as sysdba
  2. Connected.
  3. SQL> select  *  from v$encryption_wallet;

  4. WRL_TYPE
  5. --------------------
  6. WRL_PARAMETER
  7. --------------------------------------------------------------------------------
  8. STATUS
  9. ------------------
  10. file
  11. /u01/app/oracle/admin/orcl/wallet
  12. CLOSED


  13. SQL> alter system set encryption key identified by "oracle123";

  14. System altered.
复制代码
  1. [oracle@station90 wallet]$ ls
  2. ewallet.p12
复制代码
  1. SQL> alter system set encryption key identified by "oracle123";

  2. System altered.

  3. SQL> select  *  from v$encryption_wallet;

  4. WRL_TYPE
  5. --------------------
  6. WRL_PARAMETER
  7. --------------------------------------------------------------------------------
  8. STATUS
  9. ------------------
  10. file
  11. /u01/app/oracle/admin/orcl/wallet
  12. OPEN


  13. SQL> alter system set encryption wallet close identified by "oracle1234";
  14. alter system set encryption wallet close identified by "oracle1234"
  15. *
  16. ERROR at line 1:
  17. ORA-28391: cannot close wallet or HSM, password mismatch


  18. SQL> alter system set encryption wallet close identified by "oracle123";

  19. System altered.

  20. SQL> select  *  from v$encryption_wallet;

  21. WRL_TYPE
  22. --------------------
  23. WRL_PARAMETER
  24. --------------------------------------------------------------------------------
  25. STATUS
  26. ------------------
  27. file
  28. /u01/app/oracle/admin/orcl/wallet
  29. CLOSED


  30. SQL> alter system set encryption wallet open identified by "oracle123";

  31. System altered.

  32. SQL>  select  *  from v$encryption_wallet;

  33. WRL_TYPE
  34. --------------------
  35. WRL_PARAMETER
  36. --------------------------------------------------------------------------------
  37. STATUS
  38. ------------------
  39. file
  40. /u01/app/oracle/admin/orcl/wallet
  41. OPEN


  42. SQL>
复制代码


课程第26次(2018-11-29星期四)

  1. RMAN> backup validate database;

  2. Starting backup at 2018-12-02:23:16:43
  3. using target database control file instead of recovery catalog
  4. allocated channel: ORA_SBT_TAPE_1
  5. channel ORA_SBT_TAPE_1: SID=71 device type=SBT_TAPE
  6. channel ORA_SBT_TAPE_1: Oracle Secure Backup
  7. allocated channel: ORA_SBT_TAPE_2
  8. channel ORA_SBT_TAPE_2: SID=69 device type=SBT_TAPE
  9. channel ORA_SBT_TAPE_2: Oracle Secure Backup
  10. channel ORA_SBT_TAPE_1: starting full datafile backup set
  11. channel ORA_SBT_TAPE_1: specifying datafile(s) in backup set
  12. input datafile file number=00002 name=+DATA/orcl/datafile/sysaux.257.816169553
  13. input datafile file number=00007 name=+DATA/orcl/datafile/undotbs1.269.992771913
  14. input datafile file number=00005 name=+DATA/orcl/datafile/example.265.816169651
  15. channel ORA_SBT_TAPE_2: starting full datafile backup set
  16. channel ORA_SBT_TAPE_2: specifying datafile(s) in backup set
  17. input datafile file number=00001 name=+DATA/orcl/datafile/system.256.816169553
  18. input datafile file number=00004 name=+DATA/orcl/datafile/users.259.993778285
  19. input datafile file number=00003 name=+DATA/orcl/datafile/tbsocp05_test.267.992806411
  20. channel ORA_SBT_TAPE_1: backup set complete, elapsed time: 00:00:07
  21. List of Datafiles
  22. =================
  23. File Status Marked Corrupt Empty Blocks Blocks Examined High SCN
  24. ---- ------ -------------- ------------ --------------- ----------
  25. 2    OK     0              20648        97389           4824532   
  26.   File Name: +DATA/orcl/datafile/sysaux.257.816169553
  27.   Block Type Blocks Failing Blocks Processed
  28.   ---------- -------------- ----------------
  29.   Data       0              24499           
  30.   Index      0              18156           
  31.   Other      0              33977           

  32. File Status Marked Corrupt Empty Blocks Blocks Examined High SCN
  33. ---- ------ -------------- ------------ --------------- ----------
  34. 5    OK     0              1688         12805           3640343   
  35.   File Name: +DATA/orcl/datafile/example.265.816169651
  36.   Block Type Blocks Failing Blocks Processed
  37.   ---------- -------------- ----------------
  38.   Data       0              6587            
  39.   Index      0              1261            
  40.   Other      0              3264            

  41. File Status Marked Corrupt Empty Blocks Blocks Examined High SCN
  42. ---- ------ -------------- ------------ --------------- ----------
  43. 7    OK     0              12161        51200           4824532   
  44.   File Name: +DATA/orcl/datafile/undotbs1.269.992771913
  45.   Block Type Blocks Failing Blocks Processed
  46.   ---------- -------------- ----------------
  47.   Data       0              0               
  48.   Index      0              0               
  49.   Other      0              39039           

  50. channel ORA_SBT_TAPE_1: starting full datafile backup set
  51. channel ORA_SBT_TAPE_1: specifying datafile(s) in backup set
  52. including current control file in backup set
  53. channel ORA_SBT_TAPE_2: backup set complete, elapsed time: 00:00:07
  54. List of Datafiles
  55. =================
  56. File Status Marked Corrupt Empty Blocks Blocks Examined High SCN
  57. ---- ------ -------------- ------------ --------------- ----------
  58. 1    OK     0              30051        113968          4824483   
  59.   File Name: +DATA/orcl/datafile/system.256.816169553
  60.   Block Type Blocks Failing Blocks Processed
  61.   ---------- -------------- ----------------
  62.   Data       0              63614           
  63.   Index      0              14505           
  64.   Other      0              5750            

  65. File Status Marked Corrupt Empty Blocks Blocks Examined High SCN
  66. ---- ------ -------------- ------------ --------------- ----------
  67. 3    OK     0              57           641             2681035   
  68.   File Name: +DATA/orcl/datafile/tbsocp05_test.267.992806411
  69.   Block Type Blocks Failing Blocks Processed
  70.   ---------- -------------- ----------------
  71.   Data       0              5               
  72.   Index      0              0               
  73.   Other      0              578            

  74. File Status Marked Corrupt Empty Blocks Blocks Examined High SCN
  75. ---- ------ -------------- ------------ --------------- ----------
  76. 4    OK     0              140          23044           4322852   
  77.   File Name: +DATA/orcl/datafile/users.259.993778285
  78.   Block Type Blocks Failing Blocks Processed
  79.   ---------- -------------- ----------------
  80.   Data       0              642            
  81.   Index      0              46              
  82.   Other      0              22212           

  83. channel ORA_SBT_TAPE_2: starting full datafile backup set
  84. channel ORA_SBT_TAPE_2: specifying datafile(s) in backup set
  85. including current SPFILE in backup set
  86. channel ORA_SBT_TAPE_1: backup set complete, elapsed time: 00:00:00
  87. List of Control File and SPFILE
  88. ===============================
  89. File Type    Status Blocks Failing Blocks Examined
  90. ------------ ------ -------------- ---------------
  91. Control File OK     0              612            
  92. channel ORA_SBT_TAPE_2: backup set complete, elapsed time: 00:00:01
  93. List of Control File and SPFILE
  94. ===============================
  95. File Type    Status Blocks Failing Blocks Examined
  96. ------------ ------ -------------- ---------------
  97. SPFILE       OK     0              2               
  98. Finished backup at 2018-12-02:23:17:03
复制代码
  1. RMAN> show all;

  2. RMAN configuration parameters for database with db_unique_name ORCL are:
  3. CONFIGURE RETENTION POLICY TO RECOVERY WINDOW OF 1 DAYS;
  4. CONFIGURE BACKUP OPTIMIZATION ON;
  5. CONFIGURE DEFAULT DEVICE TYPE TO 'SBT_TAPE';
  6. CONFIGURE CONTROLFILE AUTOBACKUP OFF;
  7. CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '%F'; # default
  8. CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE SBT_TAPE TO '%F'; # default
  9. CONFIGURE DEVICE TYPE DISK PARALLELISM 8 BACKUP TYPE TO BACKUPSET;
  10. CONFIGURE DEVICE TYPE 'SBT_TAPE' PARALLELISM 2 BACKUP TYPE TO BACKUPSET;
  11. CONFIGURE DATAFILE BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
  12. CONFIGURE DATAFILE BACKUP COPIES FOR DEVICE TYPE SBT_TAPE TO 1; # default
  13. CONFIGURE ARCHIVELOG BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
  14. CONFIGURE ARCHIVELOG BACKUP COPIES FOR DEVICE TYPE SBT_TAPE TO 1; # default
  15. CONFIGURE CHANNEL 1 DEVICE TYPE 'SBT_TAPE' PARMS  'ENV=(OB_MEDIA_FAMILY=station90)';
  16. CONFIGURE CHANNEL 2 DEVICE TYPE 'SBT_TAPE' PARMS  'ENV=(OB_MEDIA_FAMILY=station90)';
  17. CONFIGURE CHANNEL DEVICE TYPE 'SBT_TAPE' PARMS  'ENV=(OB_MEDIA_FAMILY=station90)';
  18. CONFIGURE MAXSETSIZE TO UNLIMITED; # default
  19. CONFIGURE ENCRYPTION FOR DATABASE ON;
  20. CONFIGURE ENCRYPTION ALGORITHM 'AES128'; # default
  21. CONFIGURE COMPRESSION ALGORITHM 'BASIC' AS OF RELEASE 'DEFAULT' OPTIMIZE FOR LOAD TRUE ; # default
  22. CONFIGURE ARCHIVELOG DELETION POLICY TO NONE; # default
  23. CONFIGURE SNAPSHOT CONTROLFILE NAME TO '/u01/app/oracle/product/11.2.0/dbhome_1/dbs/snapcf_orcl.f'; # default

  24. RMAN> CONFIGURE ENCRYPTION FOR DATABASE  off;

  25. old RMAN configuration parameters:
  26. CONFIGURE ENCRYPTION FOR DATABASE ON;
  27. new RMAN configuration parameters:
  28. CONFIGURE ENCRYPTION FOR DATABASE OFF;
  29. new RMAN configuration parameters are successfully stored

  30. RMAN> backup tag '5T_WHOLE_FULL' database keep until time 'sysdate+365';

  31. Starting backup at 2018-12-02:23:24:09
  32. current log archived

  33. using channel ORA_SBT_TAPE_1
  34. using channel ORA_SBT_TAPE_2
  35. backup will be obsolete on date 2019-12-02:23:24:09
  36. archived logs required to recover from this backup will be backed up
  37. channel ORA_SBT_TAPE_1: starting full datafile backup set
  38. channel ORA_SBT_TAPE_1: specifying datafile(s) in backup set
  39. input datafile file number=00002 name=+DATA/orcl/datafile/sysaux.257.816169553
  40. input datafile file number=00007 name=+DATA/orcl/datafile/undotbs1.269.992771913
  41. input datafile file number=00005 name=+DATA/orcl/datafile/example.265.816169651
  42. channel ORA_SBT_TAPE_1: starting piece 1 at 2018-12-02:23:24:09
  43. channel ORA_SBT_TAPE_2: starting full datafile backup set
  44. channel ORA_SBT_TAPE_2: specifying datafile(s) in backup set
  45. input datafile file number=00001 name=+DATA/orcl/datafile/system.256.816169553
  46. input datafile file number=00004 name=+DATA/orcl/datafile/users.259.993778285
  47. input datafile file number=00003 name=+DATA/orcl/datafile/tbsocp05_test.267.992806411
  48. channel ORA_SBT_TAPE_2: starting piece 1 at 2018-12-02:23:24:10
  49. channel ORA_SBT_TAPE_1: finished piece 1 at 2018-12-02:23:24:35
  50. piece handle=55tjq3gp_1_1 tag=5T_WHOLE_FULL comment=API Version 2.0,MMS Version 10.4.0.4
  51. channel ORA_SBT_TAPE_1: backup set complete, elapsed time: 00:00:26
  52. channel ORA_SBT_TAPE_2: finished piece 1 at 2018-12-02:23:24:45
  53. piece handle=56tjq3gp_1_1 tag=5T_WHOLE_FULL comment=API Version 2.0,MMS Version 10.4.0.4
  54. channel ORA_SBT_TAPE_2: backup set complete, elapsed time: 00:00:35

  55. using channel ORA_SBT_TAPE_1
  56. using channel ORA_SBT_TAPE_2
  57. backup will be obsolete on date 2019-12-02:23:24:45
  58. archived logs required to recover from this backup will be backed up
  59. channel ORA_SBT_TAPE_1: starting full datafile backup set
  60. channel ORA_SBT_TAPE_1: specifying datafile(s) in backup set
  61. including current SPFILE in backup set
  62. channel ORA_SBT_TAPE_1: starting piece 1 at 2018-12-02:23:24:45
  63. channel ORA_SBT_TAPE_1: finished piece 1 at 2018-12-02:23:25:10
  64. piece handle=57tjq3ht_1_1 tag=5T_WHOLE_FULL comment=API Version 2.0,MMS Version 10.4.0.4
  65. channel ORA_SBT_TAPE_1: backup set complete, elapsed time: 00:00:25

  66. current log archived
  67. using channel ORA_SBT_TAPE_1
  68. using channel ORA_SBT_TAPE_2
  69. backup will be obsolete on date 2019-12-02:23:25:10
  70. archived logs required to recover from this backup will be backed up
  71. channel ORA_SBT_TAPE_1: starting archived log backup set
  72. channel ORA_SBT_TAPE_1: specifying archived log(s) in backup set
  73. input archived log thread=1 sequence=84 RECID=277 STAMP=993857110
  74. channel ORA_SBT_TAPE_1: starting piece 1 at 2018-12-02:23:25:10
  75. channel ORA_SBT_TAPE_1: finished piece 1 at 2018-12-02:23:25:35
  76. piece handle=58tjq3im_1_1 tag=5T_WHOLE_FULL comment=API Version 2.0,MMS Version 10.4.0.4
  77. channel ORA_SBT_TAPE_1: backup set complete, elapsed time: 00:00:25

  78. using channel ORA_SBT_TAPE_1
  79. using channel ORA_SBT_TAPE_2
  80. backup will be obsolete on date 2019-12-02:23:25:36
  81. archived logs required to recover from this backup will be backed up
  82. channel ORA_SBT_TAPE_1: starting full datafile backup set
  83. channel ORA_SBT_TAPE_1: specifying datafile(s) in backup set
  84. including current control file in backup set
  85. channel ORA_SBT_TAPE_1: starting piece 1 at 2018-12-02:23:25:37
  86. channel ORA_SBT_TAPE_1: finished piece 1 at 2018-12-02:23:26:02
  87. piece handle=59tjq3jg_1_1 tag=5T_WHOLE_FULL comment=API Version 2.0,MMS Version 10.4.0.4
  88. channel ORA_SBT_TAPE_1: backup set complete, elapsed time: 00:00:25
  89. Finished backup at 2018-12-02:23:26:02

  90. RMAN>
复制代码
  1. RMAN> change backupset 134 nokeep;

  2. allocated channel: ORA_DISK_1
  3. channel ORA_DISK_1: SID=201 device type=DISK
  4. allocated channel: ORA_DISK_2
  5. channel ORA_DISK_2: SID=16 device type=DISK
  6. allocated channel: ORA_DISK_3
  7. channel ORA_DISK_3: SID=75 device type=DISK
  8. allocated channel: ORA_DISK_4
  9. channel ORA_DISK_4: SID=141 device type=DISK
  10. allocated channel: ORA_DISK_5
  11. channel ORA_DISK_5: SID=200 device type=DISK
  12. allocated channel: ORA_DISK_6
  13. channel ORA_DISK_6: SID=14 device type=DISK
  14. allocated channel: ORA_DISK_7
  15. channel ORA_DISK_7: SID=20 device type=DISK
  16. allocated channel: ORA_DISK_8
  17. channel ORA_DISK_8: SID=143 device type=DISK
  18. using channel ORA_SBT_TAPE_1
  19. using channel ORA_SBT_TAPE_2
  20. keep attributes for the backup are deleted
  21. backup set key=134 RECID=134 STAMP=993779511

  22. RMAN> change backupset 135 nokeep;

  23. using channel ORA_DISK_1
  24. using channel ORA_DISK_2
  25. using channel ORA_DISK_3
  26. using channel ORA_DISK_4
  27. using channel ORA_DISK_5
  28. using channel ORA_DISK_6
  29. using channel ORA_DISK_7
  30. using channel ORA_DISK_8
  31. using channel ORA_SBT_TAPE_1
  32. using channel ORA_SBT_TAPE_2
  33. keep attributes for the backup are deleted
  34. backup set key=135 RECID=135 STAMP=993857068

  35. RMAN> list backup;


  36. List of Backup Sets
  37. ===================


  38. BS Key  Type LV Size       Device Type Elapsed Time Completion Time   
  39. ------- ---- -- ---------- ----------- ------------ -------------------
  40. 131     Full    8.25M      SBT_TAPE    00:00:17     2018-12-02:01:15:54
  41.         BP Key: 134   Status: UNAVAILABLE  Compressed: NO  Tag: 1ST_USERS_FULL
  42.         Handle: 4ktjnllp_1_1   Media: station90-000013
  43.   List of Datafiles in backup set 131
  44.   File LV Type Ckp SCN    Ckp Time            Name
  45.   ---- -- ---- ---------- ------------------- ----
  46.   4       Full 4730677    2018-12-01:00:02:41 +DATA/orcl/datafile/users.259.993778285

  47. BS Key  Type LV Size       Device Type Elapsed Time Completion Time   
  48. ------- ---- -- ---------- ----------- ------------ -------------------
  49. 132     Full    8.25M      SBT_TAPE    00:00:16     2018-12-02:01:17:18
  50.         BP Key: 135   Status: AVAILABLE  Compressed: NO  Tag: 2ST_USERS_FULL
  51.         Handle: 4ltjnloe_1_1   Media: station90-000013
  52.   List of Datafiles in backup set 132
  53.   File LV Type Ckp SCN    Ckp Time            Name
  54.   ---- -- ---- ---------- ------------------- ----
  55.   4       Full 4730677    2018-12-01:00:02:41 +DATA/orcl/datafile/users.259.993778285

  56. BS Key  Type LV Size       Device Type Elapsed Time Completion Time   
  57. ------- ---- -- ---------- ----------- ------------ -------------------
  58. 133     Full    620.25M    SBT_TAPE    00:02:47     2018-12-02:01:47:13
  59.   List of Datafiles in backup set 133
  60.   File LV Type Ckp SCN    Ckp Time            Name
  61.   ---- -- ---- ---------- ------------------- ----
  62.   2       Full 4800067    2018-12-02:01:44:26 +DATA/orcl/datafile/sysaux.257.816169553

  63.   Backup Set Copy #1 of backup set 133
  64.   Device Type Elapsed Time Completion Time     Compressed Tag
  65.   ----------- ------------ ------------------- ---------- ---
  66.   SBT_TAPE    00:02:47     2018-12-02:01:47:13 NO         3MPT_SYSAUX_FULL

  67.     List of Backup Pieces for backup set 133 Copy #1
  68.     BP Key  Pc# Status      Media                   Piece Name
  69.     ------- --- ----------- ----------------------- ----------
  70.     136     1   AVAILABLE   station90-000013        4mtjnnbq_1_1
  71.     137     2   AVAILABLE   station90-000013        4mtjnnbq_2_1
  72.     138     3   AVAILABLE   station90-000013        4mtjnnbq_3_1
  73.     139     4   AVAILABLE   station90-000013        4mtjnnbq_4_1
  74.     140     5   AVAILABLE   station90-000013        4mtjnnbq_5_1
  75.     141     6   AVAILABLE   station90-000013        4mtjnnbq_6_1
  76.     142     7   AVAILABLE   station90-000013        4mtjnnbq_7_1

  77. BS Key  Type LV Size       Device Type Elapsed Time Completion Time   
  78. ------- ---- -- ---------- ----------- ------------ -------------------
  79. 134     Full    621.50M    SBT_TAPE    00:01:35     2018-12-02:01:51:51
  80.   List of Datafiles in backup set 134
  81.   File LV Type Ckp SCN    Ckp Time            Name
  82.   ---- -- ---- ---------- ------------------- ----
  83.   2       Full 4800568    2018-12-02:01:50:16 +DATA/orcl/datafile/sysaux.257.816169553

  84.   Backup Set Copy #1 of backup set 134
  85.   Device Type Elapsed Time Completion Time     Compressed Tag
  86.   ----------- ------------ ------------------- ---------- ---
  87.   SBT_TAPE    00:01:35     2018-12-02:01:51:50 NO         4SST_SYSAUX_FULL

  88.     List of Backup Pieces for backup set 134 Copy #1
  89.     BP Key  Pc# Status      Media                   Piece Name
  90.     ------- --- ----------- ----------------------- ----------
  91.     144     1   AVAILABLE   station90-000013        4ntjnnmo_1_1
  92.     143     2   AVAILABLE   station90-000014        4ntjnnmo_2_1
  93.     145     3   AVAILABLE   station90-000013        4ntjnnmo_3_1
  94.     146     4   AVAILABLE   station90-000014        4ntjnnmo_4_1
  95.     147     5   AVAILABLE   station90-000013        4ntjnnmo_5_1
  96.     148     6   AVAILABLE   station90-000014        4ntjnnmo_6_1
  97.     150     7   AVAILABLE   station90-000014        4ntjnnmo_7_1
  98.     149     8   AVAILABLE   station90-000013        4ntjnnmo_8_1

  99. BS Key  Type LV Size       Device Type Elapsed Time Completion Time   
  100. ------- ---- -- ---------- ----------- ------------ -------------------
  101. 135     Full    702.00M    SBT_TAPE    00:00:19     2018-12-02:23:24:28
  102.         BP Key: 151   Status: AVAILABLE  Compressed: NO  Tag: 5T_WHOLE_FULL
  103.         Handle: 55tjq3gp_1_1   Media: station90-000013
  104.   List of Datafiles in backup set 135
  105.   File LV Type Ckp SCN    Ckp Time            Name
  106.   ---- -- ---- ---------- ------------------- ----
  107.   2       Full 4825330    2018-12-02:23:24:09 +DATA/orcl/datafile/sysaux.257.816169553
  108.   5       Full 4825330    2018-12-02:23:24:09 +DATA/orcl/datafile/example.265.816169651
  109.   7       Full 4825330    2018-12-02:23:24:09 +DATA/orcl/datafile/undotbs1.269.992771913

  110. BS Key  Type LV Size       Device Type Elapsed Time Completion Time   
  111. ------- ---- -- ---------- ----------- ------------ -------------------
  112. 136     Full    672.25M    SBT_TAPE    00:00:30     2018-12-02:23:24:39
  113.         BP Key: 152   Status: AVAILABLE  Compressed: NO  Tag: 5T_WHOLE_FULL
  114.         Handle: 56tjq3gp_1_1   Media: station90-000014
  115.         Keep: BACKUP_LOGS        Until: 2019-12-02:23:24:09
  116.   List of Datafiles in backup set 136
  117.   File LV Type Ckp SCN    Ckp Time            Name
  118.   ---- -- ---- ---------- ------------------- ----
  119.   1       Full 4825332    2018-12-02:23:24:10 +DATA/orcl/datafile/system.256.816169553
  120.   3       Full 4825332    2018-12-02:23:24:10 +DATA/orcl/datafile/tbsocp05_test.267.992806411
  121.   4       Full 4825332    2018-12-02:23:24:10 +DATA/orcl/datafile/users.259.993778285

  122. BS Key  Type LV Size       Device Type Elapsed Time Completion Time   
  123. ------- ---- -- ---------- ----------- ------------ -------------------
  124. 137     Full    256.00K    SBT_TAPE    00:00:15     2018-12-02:23:25:00
  125.         BP Key: 153   Status: AVAILABLE  Compressed: NO  Tag: 5T_WHOLE_FULL
  126.         Handle: 57tjq3ht_1_1   Media: station90-000013
  127.         Keep: BACKUP_LOGS        Until: 2019-12-02:23:24:45
  128.   SPFILE Included: Modification time: 2018-12-02:23:09:51
  129.   SPFILE db_unique_name: ORCL

  130. BS Key  Size       Device Type Elapsed Time Completion Time   
  131. ------- ---------- ----------- ------------ -------------------
  132. 138     256.00K    SBT_TAPE    00:00:16     2018-12-02:23:25:26
  133.         BP Key: 154   Status: AVAILABLE  Compressed: NO  Tag: 5T_WHOLE_FULL
  134.         Handle: 58tjq3im_1_1   Media: station90-000013
  135.         Keep: BACKUP_LOGS        Until: 2019-12-02:23:25:10

  136.   List of Archived Logs in backup set 138
  137.   Thrd Seq     Low SCN    Low Time            Next SCN   Next Time
  138.   ---- ------- ---------- ------------------- ---------- ---------
  139.   1    84      4825326    2018-12-02:23:24:09 4825394    2018-12-02:23:25:10

  140. BS Key  Type LV Size       Device Type Elapsed Time Completion Time   
  141. ------- ---- -- ---------- ----------- ------------ -------------------
  142. 139     Full    9.75M      SBT_TAPE    00:00:16     2018-12-02:23:25:52
  143.         BP Key: 155   Status: AVAILABLE  Compressed: NO  Tag: 5T_WHOLE_FULL
  144.         Handle: 59tjq3jg_1_1   Media: station90-000013
  145.         Keep: BACKUP_LOGS        Until: 2019-12-02:23:25:36
  146.   Control File Included: Ckp SCN: 4825420      Ckp time: 2018-12-02:23:25:36

  147. RMAN>
复制代码
  1. [oracle@station90 ~]$ rman target /  catalog u90/oracle_4U@rcat

  2. Recovery Manager: Release 11.2.0.3.0 - Production on Sun Dec 2 23:30:08 2018

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

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

  6. RMAN> backup tag '6T_WHOLE_FULL' database keep forever;

  7. Starting backup at 2018-12-02:23:30:15
  8. starting full resync of recovery catalog
  9. full resync complete
  10. current log archived

  11. allocated channel: ORA_SBT_TAPE_1
  12. channel ORA_SBT_TAPE_1: SID=143 device type=SBT_TAPE
  13. channel ORA_SBT_TAPE_1: Oracle Secure Backup
  14. allocated channel: ORA_SBT_TAPE_2
  15. channel ORA_SBT_TAPE_2: SID=21 device type=SBT_TAPE
  16. channel ORA_SBT_TAPE_2: Oracle Secure Backup
  17. backup will never be obsolete
  18. archived logs required to recover from this backup will be backed up
  19. channel ORA_SBT_TAPE_1: starting full datafile backup set
  20. channel ORA_SBT_TAPE_1: specifying datafile(s) in backup set
  21. input datafile file number=00002 name=+DATA/orcl/datafile/sysaux.257.816169553
  22. input datafile file number=00007 name=+DATA/orcl/datafile/undotbs1.269.992771913
  23. input datafile file number=00005 name=+DATA/orcl/datafile/example.265.816169651
  24. channel ORA_SBT_TAPE_1: starting piece 1 at 2018-12-02:23:30:32
  25. channel ORA_SBT_TAPE_2: starting full datafile backup set
  26. channel ORA_SBT_TAPE_2: specifying datafile(s) in backup set
  27. input datafile file number=00001 name=+DATA/orcl/datafile/system.256.816169553
  28. input datafile file number=00004 name=+DATA/orcl/datafile/users.259.993778285
  29. input datafile file number=00003 name=+DATA/orcl/datafile/tbsocp05_test.267.992806411
  30. channel ORA_SBT_TAPE_2: starting piece 1 at 2018-12-02:23:30:33
  31. channel ORA_SBT_TAPE_1: finished piece 1 at 2018-12-02:23:30:58
  32. piece handle=5atjq3so_1_1 tag=6T_WHOLE_FULL comment=API Version 2.0,MMS Version 10.4.0.4
  33. channel ORA_SBT_TAPE_1: backup set complete, elapsed time: 00:00:26
  34. channel ORA_SBT_TAPE_2: finished piece 1 at 2018-12-02:23:30:58
  35. piece handle=5btjq3sp_1_1 tag=6T_WHOLE_FULL comment=API Version 2.0,MMS Version 10.4.0.4
  36. channel ORA_SBT_TAPE_2: backup set complete, elapsed time: 00:00:25

  37. using channel ORA_SBT_TAPE_1
  38. using channel ORA_SBT_TAPE_2
  39. backup will never be obsolete
  40. archived logs required to recover from this backup will be backed up
  41. channel ORA_SBT_TAPE_1: starting full datafile backup set
  42. channel ORA_SBT_TAPE_1: specifying datafile(s) in backup set
  43. including current SPFILE in backup set
  44. channel ORA_SBT_TAPE_1: starting piece 1 at 2018-12-02:23:30:59
  45. channel ORA_SBT_TAPE_1: finished piece 1 at 2018-12-02:23:31:24
  46. piece handle=5ctjq3tj_1_1 tag=6T_WHOLE_FULL comment=API Version 2.0,MMS Version 10.4.0.4
  47. channel ORA_SBT_TAPE_1: backup set complete, elapsed time: 00:00:25

  48. current log archived
  49. using channel ORA_SBT_TAPE_1
  50. using channel ORA_SBT_TAPE_2
  51. backup will never be obsolete
  52. archived logs required to recover from this backup will be backed up
  53. channel ORA_SBT_TAPE_1: starting archived log backup set
  54. channel ORA_SBT_TAPE_1: specifying archived log(s) in backup set
  55. input archived log thread=1 sequence=87 RECID=280 STAMP=993857485
  56. channel ORA_SBT_TAPE_1: starting piece 1 at 2018-12-02:23:31:26
  57. channel ORA_SBT_TAPE_1: finished piece 1 at 2018-12-02:23:31:51
  58. piece handle=5dtjq3ue_1_1 tag=6T_WHOLE_FULL comment=API Version 2.0,MMS Version 10.4.0.4
  59. channel ORA_SBT_TAPE_1: backup set complete, elapsed time: 00:00:25

  60. using channel ORA_SBT_TAPE_1
  61. using channel ORA_SBT_TAPE_2
  62. backup will never be obsolete
  63. archived logs required to recover from this backup will be backed up
  64. channel ORA_SBT_TAPE_1: starting full datafile backup set
  65. channel ORA_SBT_TAPE_1: specifying datafile(s) in backup set
  66. including current control file in backup set
  67. channel ORA_SBT_TAPE_1: starting piece 1 at 2018-12-02:23:31:53
  68. channel ORA_SBT_TAPE_1: finished piece 1 at 2018-12-02:23:32:18
  69. piece handle=5etjq3v8_1_1 tag=6T_WHOLE_FULL comment=API Version 2.0,MMS Version 10.4.0.4
  70. channel ORA_SBT_TAPE_1: backup set complete, elapsed time: 00:00:25
  71. Finished backup at 2018-12-02:23:32:18

  72. RMAN>
复制代码
  1. [oracle@station90 ~]$ rman target /

  2. Recovery Manager: Release 11.2.0.3.0 - Production on Sun Dec 2 23:44:46 2018

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

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

  5. RMAN> backup incremental level 0 tablespace users;

  6. Starting backup at 2018-12-02:23:45:16
  7. using target database control file instead of recovery catalog
  8. allocated channel: ORA_SBT_TAPE_1
  9. channel ORA_SBT_TAPE_1: SID=204 device type=SBT_TAPE
  10. channel ORA_SBT_TAPE_1: Oracle Secure Backup
  11. allocated channel: ORA_SBT_TAPE_2
  12. channel ORA_SBT_TAPE_2: SID=130 device type=SBT_TAPE
  13. channel ORA_SBT_TAPE_2: Oracle Secure Backup
  14. channel ORA_SBT_TAPE_1: starting incremental level 0 datafile backup set
  15. channel ORA_SBT_TAPE_1: specifying datafile(s) in backup set
  16. input datafile file number=00004 name=+DATA/orcl/datafile/users.259.993778285
  17. channel ORA_SBT_TAPE_1: starting piece 1 at 2018-12-02:23:45:27
  18. channel ORA_SBT_TAPE_1: finished piece 1 at 2018-12-02:23:45:52
  19. piece handle=5ktjq4on_1_1 tag=TAG20181202T234527 comment=API Version 2.0,MMS Version 10.4.0.4
  20. channel ORA_SBT_TAPE_1: backup set complete, elapsed time: 00:00:25
  21. Finished backup at 2018-12-02:23:45:52

  22. RMAN> backup incremental level 1 tablespace users;

  23. Starting backup at 2018-12-02:23:46:03
  24. using channel ORA_SBT_TAPE_1
  25. using channel ORA_SBT_TAPE_2
  26. channel ORA_SBT_TAPE_1: starting incremental level 1 datafile backup set
  27. channel ORA_SBT_TAPE_1: specifying datafile(s) in backup set
  28. input datafile file number=00004 name=+DATA/orcl/datafile/users.259.993778285
  29. channel ORA_SBT_TAPE_1: starting piece 1 at 2018-12-02:23:46:04
  30. channel ORA_SBT_TAPE_1: finished piece 1 at 2018-12-02:23:46:29
  31. piece handle=5ltjq4pr_1_1 tag=TAG20181202T234603 comment=API Version 2.0,MMS Version 10.4.0.4
  32. channel ORA_SBT_TAPE_1: backup set complete, elapsed time: 00:00:25
  33. Finished backup at 2018-12-02:23:46:29

  34. RMAN> backup incremental level 1 tablespace users;

  35. Starting backup at 2018-12-02:23:46:34
  36. using channel ORA_SBT_TAPE_1
  37. using channel ORA_SBT_TAPE_2
  38. channel ORA_SBT_TAPE_1: starting incremental level 1 datafile backup set
  39. channel ORA_SBT_TAPE_1: specifying datafile(s) in backup set
  40. input datafile file number=00004 name=+DATA/orcl/datafile/users.259.993778285
  41. channel ORA_SBT_TAPE_1: starting piece 1 at 2018-12-02:23:46:35
  42. channel ORA_SBT_TAPE_1: finished piece 1 at 2018-12-02:23:47:00
  43. piece handle=5mtjq4qr_1_1 tag=TAG20181202T234635 comment=API Version 2.0,MMS Version 10.4.0.4
  44. channel ORA_SBT_TAPE_1: backup set complete, elapsed time: 00:00:25
  45. Finished backup at 2018-12-02:23:47:00

  46. RMAN> show all;

  47. RMAN configuration parameters for database with db_unique_name ORCL are:
  48. CONFIGURE RETENTION POLICY TO RECOVERY WINDOW OF 1 DAYS;
  49. CONFIGURE BACKUP OPTIMIZATION ON;
  50. CONFIGURE DEFAULT DEVICE TYPE TO 'SBT_TAPE';
  51. CONFIGURE CONTROLFILE AUTOBACKUP OFF;
  52. CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '%F'; # default
  53. CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE SBT_TAPE TO '%F'; # default
  54. CONFIGURE DEVICE TYPE DISK PARALLELISM 8 BACKUP TYPE TO BACKUPSET;
  55. CONFIGURE DEVICE TYPE 'SBT_TAPE' PARALLELISM 2 BACKUP TYPE TO BACKUPSET;
  56. CONFIGURE DATAFILE BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
  57. CONFIGURE DATAFILE BACKUP COPIES FOR DEVICE TYPE SBT_TAPE TO 1; # default
  58. CONFIGURE ARCHIVELOG BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
  59. CONFIGURE ARCHIVELOG BACKUP COPIES FOR DEVICE TYPE SBT_TAPE TO 1; # default
  60. CONFIGURE CHANNEL 1 DEVICE TYPE 'SBT_TAPE' PARMS  'ENV=(OB_MEDIA_FAMILY=station90)';
  61. CONFIGURE CHANNEL 2 DEVICE TYPE 'SBT_TAPE' PARMS  'ENV=(OB_MEDIA_FAMILY=station90)';
  62. CONFIGURE CHANNEL DEVICE TYPE 'SBT_TAPE' PARMS  'ENV=(OB_MEDIA_FAMILY=station90)';
  63. CONFIGURE MAXSETSIZE TO UNLIMITED; # default
  64. CONFIGURE ENCRYPTION FOR DATABASE OFF;
  65. CONFIGURE ENCRYPTION ALGORITHM 'AES128'; # default
  66. CONFIGURE COMPRESSION ALGORITHM 'BASIC' AS OF RELEASE 'DEFAULT' OPTIMIZE FOR LOAD TRUE ; # default
  67. CONFIGURE ARCHIVELOG DELETION POLICY TO NONE; # default
  68. CONFIGURE SNAPSHOT CONTROLFILE NAME TO '/u01/app/oracle/product/11.2.0/dbhome_1/dbs/snapcf_orcl.f'; # default

  69. RMAN> report need backup incremental 1;

  70. Report of files that need more than 1 incrementals during recovery
  71. File Incrementals Name
  72. ---- ------------ ----------------------------------------------
  73. 4    2            +DATA/orcl/datafile/users.259.993778285

  74. RMAN> report need backup incremental 2;

  75. Report of files that need more than 2 incrementals during recovery
  76. File Incrementals Name
  77. ---- ------------ ----------------------------------------------

  78. RMAN> report need backup incremental 1;

  79. Report of files that need more than 1 incrementals during recovery
  80. File Incrementals Name
  81. ---- ------------ ----------------------------------------------
  82. 4    2            +DATA/orcl/datafile/users.259.993778285

  83. RMAN> report need backup incremental 2;

  84. Report of files that need more than 2 incrementals during recovery
  85. File Incrementals Name
  86. ---- ------------ ----------------------------------------------

  87. RMAN> backup incremental level 1 tablespace users;

  88. Starting backup at 2018-12-02:23:49:13
  89. using channel ORA_SBT_TAPE_1
  90. using channel ORA_SBT_TAPE_2
  91. channel ORA_SBT_TAPE_1: starting incremental level 1 datafile backup set
  92. channel ORA_SBT_TAPE_1: specifying datafile(s) in backup set
  93. input datafile file number=00004 name=+DATA/orcl/datafile/users.259.993778285
  94. channel ORA_SBT_TAPE_1: starting piece 1 at 2018-12-02:23:49:13
  95. channel ORA_SBT_TAPE_1: finished piece 1 at 2018-12-02:23:49:38
  96. piece handle=5ntjq4vp_1_1 tag=TAG20181202T234913 comment=API Version 2.0,MMS Version 10.4.0.4
  97. channel ORA_SBT_TAPE_1: backup set complete, elapsed time: 00:00:25
  98. Finished backup at 2018-12-02:23:49:38

  99. RMAN> report need backup incremental 2;

  100. Report of files that need more than 2 incrementals during recovery
  101. File Incrementals Name
  102. ---- ------------ ----------------------------------------------
  103. 4    3            +DATA/orcl/datafile/users.259.993778285

  104. RMAN> backup incremental level 1  cumulative  tablespace users;

  105. Starting backup at 2018-12-02:23:50:15
  106. using channel ORA_SBT_TAPE_1
  107. using channel ORA_SBT_TAPE_2
  108. channel ORA_SBT_TAPE_1: starting incremental level 1 datafile backup set
  109. channel ORA_SBT_TAPE_1: specifying datafile(s) in backup set
  110. input datafile file number=00004 name=+DATA/orcl/datafile/users.259.993778285
  111. channel ORA_SBT_TAPE_1: starting piece 1 at 2018-12-02:23:50:15
  112. channel ORA_SBT_TAPE_1: finished piece 1 at 2018-12-02:23:50:30
  113. piece handle=5otjq51n_1_1 tag=TAG20181202T235015 comment=API Version 2.0,MMS Version 10.4.0.4
  114. channel ORA_SBT_TAPE_1: backup set complete, elapsed time: 00:00:15
  115. Finished backup at 2018-12-02:23:50:30

  116. RMAN> report need backup incremental 2;

  117. Report of files that need more than 2 incrementals during recovery
  118. File Incrementals Name
  119. ---- ------------ ----------------------------------------------

  120. RMAN> report need backup incremental 1;

  121. Report of files that need more than 1 incrementals during recovery
  122. File Incrementals Name
  123. ---- ------------ ----------------------------------------------

  124. RMAN> report need backup incremental 0;

  125. Report of files that need more than 0 incrementals during recovery
  126. File Incrementals Name
  127. ---- ------------ ----------------------------------------------
  128. 4    1            +DATA/orcl/datafile/users.259.993778285

  129. RMAN>
复制代码



1Z0-053第5章(28/40)

1Z0-053第6章(29/40)
8a:
  1. [oracle@station90 ~]$ rman target /  catalog u90/oracle_4U@rcat

  2. Recovery Manager: Release 11.2.0.3.0 - Production on Mon Dec 3 00:31:38 2018

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

  4. connected to target database: ORCL (not mounted)
  5. connected to recovery catalog database

  6. RMAN> set dbid 1343950367;

  7. executing command: SET DBID
  8. database name is "ORCL" and DBID is 1343950367

  9. RMAN> list backup of controlfile;


  10. List of Backup Sets
  11. ===================


  12. BS Key  Type LV Size       Device Type Elapsed Time Completion Time   
  13. ------- ---- -- ---------- ----------- ------------ -------------------
  14. 1136    Full    9.75M      SBT_TAPE    00:00:17     2018-12-03:00:25:04
  15.         BP Key: 1138   Status: AVAILABLE  Compressed: NO  Tag: LABS-8A-CONTROLFILE
  16.         Handle: 5ptjq72f_1_1   Media: station90-000015
  17.   Control File Included: Ckp SCN: 4846745      Ckp time: 2018-12-03:00:24:47

  18. RMAN> restore controlfile;

  19. Starting restore at 2018-12-03:00:33:17
  20. allocated channel: ORA_DISK_1
  21. channel ORA_DISK_1: SID=68 device type=DISK
  22. allocated channel: ORA_DISK_2
  23. channel ORA_DISK_2: SID=192 device type=DISK
  24. allocated channel: ORA_DISK_3
  25. channel ORA_DISK_3: SID=6 device type=DISK
  26. allocated channel: ORA_DISK_4
  27. channel ORA_DISK_4: SID=69 device type=DISK
  28. allocated channel: ORA_DISK_5
  29. channel ORA_DISK_5: SID=133 device type=DISK
  30. allocated channel: ORA_DISK_6
  31. channel ORA_DISK_6: SID=193 device type=DISK
  32. allocated channel: ORA_DISK_7
  33. channel ORA_DISK_7: SID=8 device type=DISK
  34. allocated channel: ORA_DISK_8
  35. channel ORA_DISK_8: SID=70 device type=DISK
  36. allocated channel: ORA_SBT_TAPE_1
  37. channel ORA_SBT_TAPE_1: SID=134 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=9 device type=SBT_TAPE
  41. channel ORA_SBT_TAPE_2: Oracle Secure Backup

  42. channel ORA_SBT_TAPE_1: starting datafile backup set restore
  43. channel ORA_SBT_TAPE_1: restoring control file
  44. channel ORA_SBT_TAPE_1: reading from backup piece 5ptjq72f_1_1
  45. channel ORA_SBT_TAPE_1: piece handle=5ptjq72f_1_1 tag=LABS-8A-CONTROLFILE
  46. channel ORA_SBT_TAPE_1: restored backup piece 1
  47. channel ORA_SBT_TAPE_1: restore complete, elapsed time: 00:00:25
  48. output file name=+DATA/orcl/controlfile/current.260.993861229
  49. output file name=+FRA/orcl/controlfile/current.256.993861233
  50. Finished restore at 2018-12-03:00:33:56

  51. RMAN> alter database mount;

  52. database mounted
  53. released channel: ORA_DISK_1
  54. released channel: ORA_DISK_2
  55. released channel: ORA_DISK_3
  56. released channel: ORA_DISK_4
  57. released channel: ORA_DISK_5
  58. released channel: ORA_DISK_6
  59. released channel: ORA_DISK_7
  60. released channel: ORA_DISK_8
  61. released channel: ORA_SBT_TAPE_1
  62. released channel: ORA_SBT_TAPE_2

  63. RMAN> recover database;

  64. Starting recover at 2018-12-03:00:34:23
  65. Starting implicit crosscheck backup at 2018-12-03:00:34:23
  66. allocated channel: ORA_DISK_1
  67. channel ORA_DISK_1: SID=71 device type=DISK
  68. allocated channel: ORA_DISK_2
  69. channel ORA_DISK_2: SID=194 device type=DISK
  70. allocated channel: ORA_DISK_3
  71. channel ORA_DISK_3: SID=9 device type=DISK
  72. allocated channel: ORA_DISK_4
  73. channel ORA_DISK_4: SID=70 device type=DISK
  74. allocated channel: ORA_DISK_5
  75. channel ORA_DISK_5: SID=134 device type=DISK
  76. allocated channel: ORA_DISK_6
  77. channel ORA_DISK_6: SID=193 device type=DISK
  78. allocated channel: ORA_DISK_7
  79. channel ORA_DISK_7: SID=8 device type=DISK
  80. allocated channel: ORA_DISK_8
  81. channel ORA_DISK_8: SID=69 device type=DISK
  82. Finished implicit crosscheck backup at 2018-12-03:00:34:25

  83. Starting implicit crosscheck copy at 2018-12-03:00:34:25
  84. using channel ORA_DISK_1
  85. using channel ORA_DISK_2
  86. using channel ORA_DISK_3
  87. using channel ORA_DISK_4
  88. using channel ORA_DISK_5
  89. using channel ORA_DISK_6
  90. using channel ORA_DISK_7
  91. using channel ORA_DISK_8
  92. Finished implicit crosscheck copy at 2018-12-03:00:34:25

  93. searching for all files in the recovery area
  94. cataloging files...
  95. no files cataloged

  96. using channel ORA_DISK_1
  97. using channel ORA_DISK_2
  98. using channel ORA_DISK_3
  99. using channel ORA_DISK_4
  100. using channel ORA_DISK_5
  101. using channel ORA_DISK_6
  102. using channel ORA_DISK_7
  103. using channel ORA_DISK_8
  104. allocated channel: ORA_SBT_TAPE_1
  105. channel ORA_SBT_TAPE_1: SID=133 device type=SBT_TAPE
  106. channel ORA_SBT_TAPE_1: Oracle Secure Backup
  107. allocated channel: ORA_SBT_TAPE_2
  108. channel ORA_SBT_TAPE_2: SID=6 device type=SBT_TAPE
  109. channel ORA_SBT_TAPE_2: Oracle Secure Backup

  110. starting media recovery

  111. archived log for thread 1 with sequence 93 is already on disk as file +FRA/orcl/archivelog/2018_12_03/thread_1_seq_93.361.993860673
  112. archived log for thread 1 with sequence 94 is already on disk as file +FRA/orcl/archivelog/2018_12_03/thread_1_seq_94.352.993860675
  113. archived log for thread 1 with sequence 95 is already on disk as file +FRA/orcl/archivelog/2018_12_03/thread_1_seq_95.351.993860675
  114. archived log for thread 1 with sequence 96 is already on disk as file +DATA/orcl/onlinelog/group_4.258.992810599
  115. archived log file name=+FRA/orcl/archivelog/2018_12_03/thread_1_seq_93.361.993860673 thread=1 sequence=93
  116. archived log file name=+FRA/orcl/archivelog/2018_12_03/thread_1_seq_94.352.993860675 thread=1 sequence=94
  117. archived log file name=+FRA/orcl/archivelog/2018_12_03/thread_1_seq_95.351.993860675 thread=1 sequence=95
  118. archived log file name=+DATA/orcl/onlinelog/group_4.258.992810599 thread=1 sequence=96
  119. media recovery complete, elapsed time: 00:00:01
  120. Finished recover at 2018-12-03:00:34:40

  121. RMAN> alter database open resetlogs;

  122. database opened
  123. new incarnation of database registered in recovery catalog
  124. starting full resync of recovery catalog
  125. full resync complete

  126. RMAN>
复制代码
8b:
  1. [oracle@station90 ~]$ rman target /

  2. Recovery Manager: Release 11.2.0.3.0 - Production on Mon Dec 3 00:47:46 2018

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

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

  5. RMAN> list incarnation of database;

  6. using target database control file instead of recovery catalog

  7. List of Database Incarnations
  8. DB Key  Inc Key DB Name  DB ID            STATUS  Reset SCN  Reset Time
  9. ------- ------- -------- ---------------- --- ---------- ----------
  10. 1       1       ORCL     1343950367       CURRENT 4846785    2018-12-03:00:35:09

  11. RMAN> exit


  12. Recovery Manager complete.
  13. [oracle@station90 ~]$ rman target /  catalog u90/oracle_4U@rcat

  14. Recovery Manager: Release 11.2.0.3.0 - Production on Mon Dec 3 00:48:28 2018

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

  16. connected to target database: ORCL (DBID=1343950367)
  17. connected to recovery catalog database

  18. RMAN> list incarnation of database;

  19. starting full resync of recovery catalog
  20. full resync complete

  21. List of Database Incarnations
  22. DB Key  Inc Key DB Name  DB ID            STATUS  Reset SCN  Reset Time
  23. ------- ------- -------- ---------------- --- ---------- ----------
  24. 1       25      ORCL     1343950367       PARENT  1          2009-08-15:00:16:43
  25. 1       26      ORCL     1343950367       PARENT  945184     2013-05-23:09:47:15
  26. 1       27      ORCL     1343950367       PARENT  2704217    2018-11-21:20:14:20
  27. 1       2       ORCL     1343950367       PARENT  2706559    2018-11-21:20:43:16
  28. 1       1159    ORCL     1343950367       CURRENT 4846785    2018-12-03:00:35:09

  29. RMAN> exit


  30. Recovery Manager complete.
  31. [oracle@station90 ~]$ sqlplus /nolog

  32. SQL*Plus: Release 11.2.0.3.0 Production on Mon Dec 3 00:48:42 2018

  33. Copyright (c) 1982, 2011, Oracle.  All rights reserved.

  34. SQL> conn / as sysdba
  35. Connected.
  36. SQL> select  * from v$tempfile;

  37. no rows selected

  38. SQL> alter tablespace temp add tempfile size 30M autoextend on;

  39. Tablespace altered.

  40. SQL>
复制代码
  1. [root@station80 ~]# losetup  /dev/loop0
  2. /dev/loop0: [fd01]:2127843 (/u01/loop/device/loop0.img)
  3. [root@station80 ~]# losetup  /dev/loop1
  4. /dev/loop1: [fd01]:2127844 (/u01/loop/device/loop1.img)
  5. [root@station80 ~]# losetup  /dev/loop2
  6. /dev/loop2: [fd01]:2127845 (/u01/loop/device/loop2.img)
  7. [root@station80 ~]# losetup  /dev/loop3
  8. /dev/loop3: [fd01]:2127846 (/u01/loop/device/loop3.img)
  9. [root@station80 ~]# losetup  /dev/loop4
  10. /dev/loop4: [fd01]:2127847 (/u01/loop/device/loop4.img)
  11. [root@station80 ~]# losetup  /dev/loop5
  12. loop: can't get info on device /dev/loop5: No such device or address
  13. [root@station80 ~]# du -sh /u01/loop/device/loop0.img
  14. 2.7G        /u01/loop/device/loop0.img
  15. [root@station80 ~]# du -sh /u01/loop/device/loop1.img
  16. 2.7G        /u01/loop/device/loop1.img
  17. [root@station80 ~]# /u01/loop/device/loop2.img
  18. -bash: /u01/loop/device/loop2.img: Permission denied
  19. [root@station80 ~]# du -sh /u01/loop/device/loop2.img
  20. 513M        /u01/loop/device/loop2.img
  21. [root@station80 ~]# du -sh /u01/loop/device/loop3.img
  22. 513M        /u01/loop/device/loop3.img
  23. [root@station80 ~]# du -sh /u01/loop/device/loop4.img
  24. 513M        /u01/loop/device/loop4.img
  25. [root@station80 ~]# rpm -qa | grep oracleasm
  26. oracleasm-2.6.18-238.el5PAE-2.0.5-1.el5
  27. oracleasm-2.6.18-238.el5debug-2.0.5-1.el5
  28. oracleasm-2.6.18-238.el5xen-2.0.5-1.el5
  29. oracleasm-2.6.18-238.el5-2.0.5-1.el5
  30. oracleasm-support-2.1.8-1.el5
  31. oracleasmlib-2.0.4-1.el5
  32. [root@station80 ~]# oracleasm-discover
  33. Using ASMLib from /opt/oracle/extapi/32/asm/orcl/1/libasm.so
  34. [ASM Library - Generic Linux, version 2.0.4 (KABI_V2)]
  35. Discovered disk: ORCL:DISK1 [12289725 blocks (6292339200 bytes), maxio 512]
  36. Discovered disk: ORCL:DISK2 [5638752 blocks (2887041024 bytes), maxio 512]
  37. Discovered disk: ORCL:DISK3 [5638752 blocks (2887041024 bytes), maxio 512]
  38. Discovered disk: ORCL:DISK4 [5638752 blocks (2887041024 bytes), maxio 512]
  39. Discovered disk: ORCL:DISK5 [5638752 blocks (2887041024 bytes), maxio 512]
  40. [root@station80 ~]# oracleasm configure -i
  41. Configuring the Oracle ASM library driver.

  42. This will configure the on-boot properties of the Oracle ASM library
  43. driver.  The following questions will determine whether the driver is
  44. loaded on boot and what permissions it will have.  The current values
  45. will be shown in brackets ('[]').  Hitting <ENTER> without typing an
  46. answer will keep that current value.  Ctrl-C will abort.

  47. Default user to own the driver interface [oracle]:
  48. Default group to own the driver interface [dba]:         
  49. Start Oracle ASM library driver on boot (y/n) [y]:
  50. Scan for Oracle ASM disks on boot (y/n) [y]:
  51. Writing Oracle ASM library driver configuration: done
  52. [root@station80 ~]# oracleasm createdisk DISK8  /dev/loop2
  53. Writing disk header: done
  54. Instantiating disk: done
  55. [root@station80 ~]# oracleasm createdisk DISK9  /dev/loop3
  56. Writing disk header: done
  57. Instantiating disk: done
  58. [root@station80 ~]# oracleasm createdisk DISK10  /dev/loop4
  59. Writing disk header: done
  60. Instantiating disk: done
  61. [root@station80 ~]# oracleasm-discover
  62. Using ASMLib from /opt/oracle/extapi/32/asm/orcl/1/libasm.so
  63. [ASM Library - Generic Linux, version 2.0.4 (KABI_V2)]
  64. Discovered disk: ORCL:DISK1 [12289725 blocks (6292339200 bytes), maxio 512]
  65. Discovered disk: ORCL:DISK10 [1048576 blocks (536870912 bytes), maxio 128]
  66. Discovered disk: ORCL:DISK2 [5638752 blocks (2887041024 bytes), maxio 512]
  67. Discovered disk: ORCL:DISK3 [5638752 blocks (2887041024 bytes), maxio 512]
  68. Discovered disk: ORCL:DISK4 [5638752 blocks (2887041024 bytes), maxio 512]
  69. Discovered disk: ORCL:DISK5 [5638752 blocks (2887041024 bytes), maxio 512]
  70. Discovered disk: ORCL:DISK8 [1048576 blocks (536870912 bytes), maxio 128]
  71. Discovered disk: ORCL:DISK9 [1048576 blocks (536870912 bytes), maxio 128]
  72. [root@station80 ~]# oracleasm createdisk DISK6  /dev/loop0
  73. Writing disk header: done
  74. Instantiating disk: done
  75. [root@station80 ~]# oracleasm createdisk DISK7  /dev/loop1
  76. Writing disk header: done
  77. Instantiating disk: done
  78. [root@station80 ~]# oracleasm-discover
  79. Using ASMLib from /opt/oracle/extapi/32/asm/orcl/1/libasm.so
  80. [ASM Library - Generic Linux, version 2.0.4 (KABI_V2)]
  81. Discovered disk: ORCL:DISK1 [12289725 blocks (6292339200 bytes), maxio 512]
  82. Discovered disk: ORCL:DISK10 [1048576 blocks (536870912 bytes), maxio 128]
  83. Discovered disk: ORCL:DISK2 [5638752 blocks (2887041024 bytes), maxio 512]
  84. Discovered disk: ORCL:DISK3 [5638752 blocks (2887041024 bytes), maxio 512]
  85. Discovered disk: ORCL:DISK4 [5638752 blocks (2887041024 bytes), maxio 512]
  86. Discovered disk: ORCL:DISK5 [5638752 blocks (2887041024 bytes), maxio 512]
  87. Discovered disk: ORCL:DISK6 [5632000 blocks (2883584000 bytes), maxio 128]
  88. Discovered disk: ORCL:DISK7 [5632000 blocks (2883584000 bytes), maxio 128]
  89. Discovered disk: ORCL:DISK8 [1048576 blocks (536870912 bytes), maxio 128]
  90. Discovered disk: ORCL:DISK9 [1048576 blocks (536870912 bytes), maxio 128]
  91. [root@station80 ~]# oracleasm scandisk
  92. oracleasm: 'scandisk' is not an oracleasm command
  93. Usage: oracleasm [--exec-path=<exec_path>] <command> [ <args> ]
  94.        oracleasm --exec-path
  95.        oracleasm -h
  96.        oracleasm -V

  97. The basic oracleasm commands are:
  98.     configure        Configure the Oracle Linux ASMLib driver
  99.     init             Load and initialize the ASMLib driver
  100.     exit             Stop the ASMLib driver
  101.     scandisks        Scan the system for Oracle ASMLib disks
  102.     status           Display the status of the Oracle ASMLib driver
  103.     listdisks        List known Oracle ASMLib disks
  104.     querydisk        Determine if a disk belongs to Oracle ASMlib
  105.     createdisk       Allocate a device for Oracle ASMLib use
  106.     deletedisk       Return a device to the operating system
  107.     renamedisk       Change the label of an Oracle ASMlib disk
  108.     update-driver    Download the latest ASMLib driver
  109. [root@station80 ~]# oracleasm scandisks
  110. Reloading disk partitions: done
  111. Cleaning any stale ASM disks...
  112. Scanning system for ASM disks...
  113. [root@station80 ~]# oracleasm listdisks
  114. DISK1
  115. DISK10
  116. DISK2
  117. DISK3
  118. DISK4
  119. DISK5
  120. DISK6
  121. DISK7
  122. DISK8
  123. DISK9
  124. [root@station80 ~]#
复制代码
  1. [oracle@station80 ~]$ sqlplus /nolog

  2. SQL*Plus: Release 11.2.0.1.0 Production on Tue Nov 27 02:38:02 2018

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

  4. SQL> conn / as sysasm
  5. Connected.
  6. SQL> create diskgroup highgrp high redundancy  
  7.   2   failgroup fg1
  8.   3    disk 'ORCL:DISK8'        
  9.   4   failgroup fg2
  10.   5    disk 'ORCL:DISK9'
  11.   6   failgroup fg3
  12.   7    disk 'ORCL:DISK10';

  13. Diskgroup created.

  14. SQL> desc v$asm_diskgroup
  15. Name                                           Null?    Type
  16. ----------------------------------------- -------- ----------------------------
  17. GROUP_NUMBER                                            NUMBER
  18. NAME                                                    VARCHAR2(30)
  19. SECTOR_SIZE                                            NUMBER
  20. BLOCK_SIZE                                            NUMBER
  21. ALLOCATION_UNIT_SIZE                                    NUMBER
  22. STATE                                                    VARCHAR2(11)
  23. TYPE                                                    VARCHAR2(6)
  24. TOTAL_MB                                            NUMBER
  25. FREE_MB                                            NUMBER
  26. HOT_USED_MB                                            NUMBER
  27. COLD_USED_MB                                            NUMBER
  28. REQUIRED_MIRROR_FREE_MB                            NUMBER
  29. USABLE_FILE_MB                                     NUMBER
  30. OFFLINE_DISKS                                            NUMBER
  31. COMPATIBILITY                                            VARCHAR2(60)
  32. DATABASE_COMPATIBILITY                             VARCHAR2(60)
  33. VOTING_FILES                                            VARCHAR2(1)

  34. SQL> select  NAME, GROUP_NUMBER , TOTAL_MB,USABLE_FILE_MB  from v$asm_diskgroup;

  35. NAME
  36. --------------------------------------------------------------------------------
  37. GROUP_NUMBER   TOTAL_MB USABLE_FILE_MB
  38. ------------ ---------- --------------
  39. FRA
  40.            1           6000           5723

  41. DATA
  42.            2          11012           2942

  43. HIGHGRP
  44.            3           1536            461


  45. SQL>
复制代码

db 与ASM联系:
  1. [root@station90 桌面]# ps aux | grep ora_  | grep rcat  | sort
  2. oracle   11374  0.0  0.3 1812148 52328 ?       Ss   23:44   0:00 ora_pmon_rcat
  3. oracle   11376  0.0  0.2 1809864 41700 ?       Ss   23:44   0:00 ora_psp0_rcat
  4. oracle   11378  0.9  0.2 1809864 40744 ?       Ss   23:44   0:00 ora_vktm_rcat
  5. oracle   11382  0.0  0.2 1809864 41884 ?       Ss   23:44   0:00 ora_gen0_rcat
  6. oracle   11384  0.0  0.2 1809864 40600 ?       Ss   23:44   0:00 ora_diag_rcat
  7. oracle   11386  0.0  0.3 1810376 63192 ?       Ss   23:44   0:00 ora_dbrm_rcat
  8. oracle   11388  0.0  0.3 1812424 57524 ?       Ss   23:44   0:00 ora_dia0_rcat
  9. oracle   11390  0.1  0.4 1809864 69576 ?       Ss   23:44   0:00 ora_mman_rcat
  10. oracle   11392  0.0  0.3 1817536 50892 ?       Ss   23:44   0:00 ora_dbw0_rcat
  11. oracle   11394  0.0  0.3 1825416 51252 ?       Ss   23:44   0:00 ora_lgwr_rcat
  12. oracle   11396  0.0  0.3 1809864 51144 ?       Ss   23:44   0:00 ora_ckpt_rcat
  13. oracle   11398  0.1  0.4 1810380 66564 ?       Ss   23:44   0:00 ora_smon_rcat
  14. oracle   11400  0.0  0.2 1809864 40524 ?       Ss   23:44   0:00 ora_reco_rcat
  15. oracle   11402  1.1  0.7 1815808 127836 ?      Ss   23:44   0:00 ora_mmon_rcat
  16. oracle   11404  0.0  0.3 1811096 58584 ?       Ss   23:44   0:00 ora_mmnl_rcat
  17. oracle   11406  0.0  0.2 1816120 39980 ?       Ss   23:44   0:00 ora_d000_rcat
  18. oracle   11408  0.0  0.2 1811060 37716 ?       Ss   23:44   0:00 ora_s000_rcat
  19. oracle   11474  0.0  0.2 1811972 45636 ?       Ss   23:44   0:00 ora_p000_rcat
  20. oracle   11477  0.0  0.2 1811972 45272 ?       Ss   23:44   0:00 ora_p001_rcat
  21. oracle   11480  0.0  0.2 1811972 46164 ?       Ss   23:44   0:00 ora_p002_rcat
  22. oracle   11527  0.0  0.2 1809864 44352 ?       Ss   23:44   0:00 ora_qmnc_rcat
  23. oracle   11543  0.6  0.5 1820656 90260 ?       Ss   23:44   0:00 ora_cjq0_rcat
  24. oracle   11548  0.1  0.2 1809864 42876 ?       Ss   23:44   0:00 ora_vkrm_rcat
  25. oracle   11550  0.5  0.5 1811076 89060 ?       Ss   23:44   0:00 ora_j000_rcat
  26. oracle   11552  4.0  0.8 1812000 132880 ?      Ss   23:44   0:00 ora_j001_rcat
  27. oracle   11554  0.3  0.4 1811440 77788 ?       Ss   23:44   0:00 ora_j002_rcat
  28. oracle   11556  1.0  0.5 1811492 90744 ?       Ss   23:44   0:00 ora_j003_rcat
  29. oracle   11562  0.6  0.4 1811480 76420 ?       Ss   23:44   0:00 ora_j004_rcat
  30. oracle   11564  0.1  0.4 1811432 73344 ?       Ss   23:44   0:00 ora_j005_rcat
  31. oracle   11566  0.0  0.3 1811436 63792 ?       Ss   23:44   0:00 ora_j006_rcat
  32. oracle   11568  0.0  0.4 1811432 69344 ?       Ss   23:44   0:00 ora_j007_rcat
  33. oracle   11570  0.0  0.2 1809860 39616 ?       Ss   23:44   0:00 ora_j008_rcat
  34. oracle   11573  0.0  0.3 1811400 60220 ?       Ss   23:44   0:00 ora_q000_rcat
  35. oracle   11575  0.0  0.3 1810372 52132 ?       Ss   23:44   0:00 ora_q001_rcat
  36. [root@station90 桌面]# ps aux | grep ora_  | grep rcat  | sort
  37. oracle   11374  0.0  0.3 1812148 52584 ?       Ss   23:44   0:00 ora_pmon_rcat
  38. oracle   11376  0.0  0.2 1809864 41716 ?       Ss   23:44   0:00 ora_psp0_rcat
  39. oracle   11378  0.9  0.2 1809864 40744 ?       Ss   23:44   0:01 ora_vktm_rcat
  40. oracle   11382  0.0  0.2 1811264 44784 ?       Ss   23:44   0:00 ora_gen0_rcat
  41. oracle   11384  0.0  0.2 1809864 40600 ?       Ss   23:44   0:00 ora_diag_rcat
  42. oracle   11386  0.0  0.3 1810376 63200 ?       Ss   23:44   0:00 ora_dbrm_rcat
  43. oracle   11388  0.0  0.3 1812424 57524 ?       Ss   23:44   0:00 ora_dia0_rcat
  44. oracle   11390  0.0  0.4 1809864 69576 ?       Ss   23:44   0:00 ora_mman_rcat
  45. oracle   11392  0.0  0.3 1819156 60496 ?       Ss   23:44   0:00 ora_dbw0_rcat
  46. oracle   11394  0.0  0.3 1825416 51252 ?       Ss   23:44   0:00 ora_lgwr_rcat
  47. oracle   11396  0.0  0.3 1809864 51404 ?       Ss   23:44   0:00 ora_ckpt_rcat
  48. oracle   11398  0.0  0.4 1810380 66564 ?       Ss   23:44   0:00 ora_smon_rcat
  49. oracle   11400  0.0  0.3 1811400 53964 ?       Ss   23:44   0:00 ora_reco_rcat
  50. oracle   11402  0.3  0.7 1815808 127836 ?      Ss   23:44   0:00 ora_mmon_rcat
  51. oracle   11404  0.0  0.3 1811096 60684 ?       Ss   23:44   0:00 ora_mmnl_rcat
  52. oracle   11406  0.0  0.2 1816120 39980 ?       Ss   23:44   0:00 ora_d000_rcat
  53. oracle   11408  0.0  0.2 1811060 37716 ?       Ss   23:44   0:00 ora_s000_rcat
  54. oracle   11474  0.0  0.2 1811972 45636 ?       Ss   23:44   0:00 ora_p000_rcat
  55. oracle   11477  0.0  0.2 1811972 45272 ?       Ss   23:44   0:00 ora_p001_rcat
  56. oracle   11480  0.0  0.2 1811972 46164 ?       Ss   23:44   0:00 ora_p002_rcat
  57. oracle   11527  0.0  0.2 1809864 44352 ?       Ss   23:44   0:00 ora_qmnc_rcat
  58. oracle   11543  0.1  0.5 1820656 96440 ?       Ss   23:44   0:00 ora_cjq0_rcat
  59. oracle   11548  0.1  0.2 1809864 42876 ?       Ss   23:44   0:00 ora_vkrm_rcat
  60. oracle   11573  0.0  0.3 1811400 60220 ?       Ss   23:44   0:00 ora_q000_rcat
  61. oracle   11575  0.0  0.3 1810372 52132 ?       Ss   23:44   0:00 ora_q001_rcat
  62. oracle   11599  0.0  0.2 1811348 46076 ?       Ss   23:46   0:00 ora_asmb_rcat
  63. oracle   11603  0.0  0.2 1810460 46340 ?       Ss   23:46   0:00 ora_rbal_rcat
  64. oracle   11605  0.0  0.3 1817584 48972 ?       Ss   23:46   0:00 ora_mark_rcat
  65. oracle   11607  0.1  0.2 1811740 47632 ?       Ss   23:46   0:00 ora_ocf0_rcat
  66. oracle   11611  0.2  0.2 1811348 44244 ?       Ss   23:46   0:00 ora_o000_rcat
  67. [root@station90 桌面]#
复制代码
  1. select  * from v$asm_diskgroup;

  2. select * from v$asm_attribute;

  3. select  * from v$asm_client;

  4. create diskgroup highgrp high redundancy  
  5.     failgroup fg1
  6.       disk 'ORCL:DISK8'        
  7.      failgroup fg2
  8.      disk 'ORCL:DISK9'
  9.     failgroup fg3
  10.     disk 'ORCL:DISK10'
  11.     attribute  'au_size'='4M';
复制代码
  1. select  * from dba_data_files;

  2. select  * from v$controlfile;

  3. create tablespace tbsunp datafile '+data(temp2)/orcl/tbsunp.dbf' size 10M;
复制代码

  1. select  * from v$asm_disk;

  2. alter diskgroup data
  3. add failgroup  fg1  disk 'ORCL:DISK6' name fg1_dsk1  size  2753M
  4. add failgroup  fg2  disk 'ORCL:DISK7' name fg2_dsk1 size 2753M;

  5.    
  6. select  * from v$asm_template where group_number=2;

  7. select  * from v$asm_alias where name='SYSTEM.256.832197063'  and group_number=2;


  8. select  * from v$asm_file  where   file_number=256 and  group_number=2;
  9.    
  10. select  * from v$asm_alias where lower(name)='tbsunp.dbf'  and group_number=2;

  11. select  * from v$asm_alias where file_number=268;

  12. select  * from v$asm_file  where   file_number=268 and  group_number=2;

  13. alter diskgroup data add template temp1 attributes (unprotected  fine ) ;

复制代码
课程第27次(2018-11-30星期五)

1Z0-052第5章(30/40)
  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. }
复制代码
1Z0-053第7章(31/40)
  1. [oracle@station90 ~]$ sqlplus /nolog

  2. SQL*Plus: Release 11.2.0.3.0 Production on Tue Dec 4 01:31:04 2018

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

  4. SQL> conn / as sysdba
  5. Connected.
  6. SQL> show paramater db_recovery
  7. SP2-0158: unknown SHOW option "paramater"
  8. SP2-0735: unknown SHOW option beginning "db_recover..."
  9. SQL> show parameter db_recovery

  10. NAME                                     TYPE         VALUE
  11. ------------------------------------ ----------- ------------------------------
  12. db_recovery_file_dest                     string         +FRA
  13. db_recovery_file_dest_size             big integer 3882M
  14. SQL> alter system set db_recovery_file_dest='/u01/app/oracle/fast_recovery_area';

  15. System altered.

  16. SQL> alter system set db_recovery_file_dest='/u01/app/oracle/fast_recovery_area';

  17. System altered.

  18. SQL> show parameter db_create_file_dest

  19. NAME                                     TYPE         VALUE
  20. ------------------------------------ ----------- ------------------------------
  21. db_create_file_dest                     string         +DATA
  22. SQL> alter system set db_create_file_dest='/u01/app/oracle/oradata';

  23. System altered.

  24. SQL> set linesize 1000
  25. SQL> select  * from v$log;

  26.     GROUP#    THREAD#  SEQUENCE#      BYTES  BLOCKSIZE          MEMBERS ARC STATUS               FIRST_CHANGE# FIRST_TIME          NEXT_CHANGE# NEXT_TIME
  27. ---------- ---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- ------------------- ------------ -------------------
  28.          1            1             109   52428800           512                2 YES INACTIVE                     2047121 2018-12-04:01:18:27      2051947 2018-12-04:01:24:16
  29.          2            1             110   52428800           512                2 NO  CURRENT                     2051947 2018-12-04:01:24:16   2.8147E+14
  30.          3            1             108   52428800           512                2 YES INACTIVE                     2026853 2017-05-22:20:41:42      2047121 2018-12-04:01:18:27

  31. SQL> alter database drop logfile group 1;

  32. Database altered.

  33. SQL> alter database add logfile group 1;

  34. Database altered.

  35. SQL> alter database drop logfile group 3;

  36. Database altered.

  37. SQL> alter database add logfile group 3;

  38. Database altered.

  39. SQL> select  member from v$logfile;

  40. MEMBER
  41. ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
  42. /u01/app/oracle/oradata/ORCL/onlinelog/o1_mf_3_g0bt8qy3_.log
  43. /u01/app/oracle/fast_recovery_area/ORCL/onlinelog/o1_mf_3_g0bt8r41_.log
  44. +DATA/orcl/onlinelog/group_2.262.816169639
  45. +FRA/orcl/onlinelog/group_2.258.816169639
  46. /u01/app/oracle/oradata/ORCL/onlinelog/o1_mf_1_g0bt66j9_.log
  47. /u01/app/oracle/fast_recovery_area/ORCL/onlinelog/o1_mf_1_g0bt66n3_.log

  48. 6 rows selected.

  49. SQL> alter system switch logfile;

  50. System altered.

  51. SQL> alter system checkpoint;

  52. System altered.

  53. SQL> alter database drop logfile group 2;

  54. Database altered.

  55. SQL> alter database add logfile group 2;

  56. Database altered.

  57. SQL> select  member from v$logfile;

  58. MEMBER
  59. ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
  60. /u01/app/oracle/oradata/ORCL/onlinelog/o1_mf_3_g0bt8qy3_.log
  61. /u01/app/oracle/fast_recovery_area/ORCL/onlinelog/o1_mf_3_g0bt8r41_.log
  62. /u01/app/oracle/oradata/ORCL/onlinelog/o1_mf_2_g0btbg11_.log
  63. /u01/app/oracle/fast_recovery_area/ORCL/onlinelog/o1_mf_2_g0btbg4v_.log
  64. /u01/app/oracle/oradata/ORCL/onlinelog/o1_mf_1_g0bt66j9_.log
  65. /u01/app/oracle/fast_recovery_area/ORCL/onlinelog/o1_mf_1_g0bt66n3_.log

  66. 6 rows selected.

  67. SQL> shutdown immediate
  68. Database closed.
  69. Database dismounted.
  70. ORACLE instance shut down.
  71. SQL> startup mount
  72. ORACLE instance started.

  73. Total System Global Area 6664212480 bytes
  74. Fixed Size                    2240944 bytes
  75. Variable Size                 3640659536 bytes
  76. Database Buffers         3003121664 bytes
  77. Redo Buffers                   18190336 bytes
  78. Database mounted.
  79. SQL> show parameter db_recovery

  80. NAME                                     TYPE         VALUE
  81. ------------------------------------ ----------- ------------------------------
  82. db_recovery_file_dest                     string         /u01/app/oracle/fast_recovery_
  83.                                                  area
  84. db_recovery_file_dest_size             big integer 3882M
  85. SQL> alter system set db_recovery_file_dest='/u01/app/oracle/oradata';

  86. System altered.

  87. SQL> exit
  88. Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
  89. With the Partitioning, Automatic Storage Management, OLAP, Data Mining
  90. and Real Application Testing options
  91. [oracle@station90 ~]$ rman target /

  92. Recovery Manager: Release 11.2.0.3.0 - Production on Tue Dec 4 01:37:38 2018

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

  94. connected to target database: ORCL (DBID=1343950367, not open)

  95. RMAN> backup as copy database;

  96. Starting backup at 2018-12-04:01:37:44
  97. using target database control file instead of recovery catalog
  98. allocated channel: ORA_DISK_1
  99. channel ORA_DISK_1: SID=133 device type=DISK
  100. allocated channel: ORA_DISK_2
  101. channel ORA_DISK_2: SID=193 device type=DISK
  102. allocated channel: ORA_DISK_3
  103. channel ORA_DISK_3: SID=9 device type=DISK
  104. allocated channel: ORA_DISK_4
  105. channel ORA_DISK_4: SID=70 device type=DISK
  106. allocated channel: ORA_DISK_5
  107. channel ORA_DISK_5: SID=134 device type=DISK
  108. allocated channel: ORA_DISK_6
  109. channel ORA_DISK_6: SID=194 device type=DISK
  110. allocated channel: ORA_DISK_7
  111. channel ORA_DISK_7: SID=10 device type=DISK
  112. allocated channel: ORA_DISK_8
  113. channel ORA_DISK_8: SID=71 device type=DISK
  114. channel ORA_DISK_1: starting datafile copy
  115. input datafile file number=00001 name=+DATA/orcl/datafile/system.256.816169553
  116. channel ORA_DISK_2: starting datafile copy
  117. input datafile file number=00003 name=+DATA/orcl/datafile/undotbs1.258.816169553
  118. channel ORA_DISK_3: starting datafile copy
  119. input datafile file number=00002 name=+DATA/orcl/datafile/sysaux.257.816169553
  120. channel ORA_DISK_4: starting datafile copy
  121. input datafile file number=00005 name=+DATA/orcl/datafile/example.265.816169651
  122. channel ORA_DISK_5: starting datafile copy
  123. input datafile file number=00004 name=+DATA/orcl/datafile/users.259.816169553
  124. output file name=/u01/app/oracle/oradata/ORCL/datafile/o1_mf_users_g0bthdk9_.dbf tag=TAG20181204T013746 RECID=3 STAMP=993951468
  125. channel ORA_DISK_5: datafile copy complete, elapsed time: 00:00:01
  126. output file name=/u01/app/oracle/oradata/ORCL/datafile/o1_mf_example_g0bthd4p_.dbf tag=TAG20181204T013746 RECID=4 STAMP=993951469
  127. channel ORA_DISK_4: datafile copy complete, elapsed time: 00:00:04
  128. output file name=/u01/app/oracle/oradata/ORCL/datafile/o1_mf_system_g0bthc9n_.dbf tag=TAG20181204T013746 RECID=6 STAMP=993951476
  129. channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:16
  130. output file name=/u01/app/oracle/oradata/ORCL/datafile/o1_mf_undotbs1_g0bthcj0_.dbf tag=TAG20181204T013746 RECID=7 STAMP=993951477
  131. channel ORA_DISK_2: datafile copy complete, elapsed time: 00:00:16
  132. output file name=/u01/app/oracle/oradata/ORCL/datafile/o1_mf_sysaux_g0bthcqp_.dbf tag=TAG20181204T013746 RECID=5 STAMP=993951476
  133. channel ORA_DISK_3: datafile copy complete, elapsed time: 00:00:16
  134. Finished backup at 2018-12-04:01:38:03

  135. Starting Control File and SPFILE Autobackup at 2018-12-04:01:38:03
  136. piece handle=/u01/app/oracle/oradata/ORCL/autobackup/2018_12_04/o1_mf_s_993951364_g0bthwfc_.bkp comment=NONE
  137. Finished Control File and SPFILE Autobackup at 2018-12-04:01:38:04

  138. RMAN> report schema;

  139. Report of database schema for database with db_unique_name ORCL

  140. List of Permanent Datafiles
  141. ===========================
  142. File Size(MB) Tablespace           RB segs Datafile Name
  143. ---- -------- -------------------- ------- ------------------------
  144. 1    730      SYSTEM               ***     +DATA/orcl/datafile/system.256.816169553
  145. 2    600      SYSAUX               ***     +DATA/orcl/datafile/sysaux.257.816169553
  146. 3    650      UNDOTBS1             ***     +DATA/orcl/datafile/undotbs1.258.816169553
  147. 4    5        USERS                ***     +DATA/orcl/datafile/users.259.816169553
  148. 5    100      EXAMPLE              ***     +DATA/orcl/datafile/example.265.816169651

  149. List of Temporary Files
  150. =======================
  151. File Size(MB) Tablespace           Maxsize(MB) Tempfile Name
  152. ---- -------- -------------------- ----------- --------------------
  153. 1    71       TEMP                 32767       +DATA/orcl/tempfile/temp.264.816169645

  154. RMAN> switch database to copy;

  155. datafile 1 switched to datafile copy "/u01/app/oracle/oradata/ORCL/datafile/o1_mf_system_g0bthc9n_.dbf"
  156. datafile 2 switched to datafile copy "/u01/app/oracle/oradata/ORCL/datafile/o1_mf_sysaux_g0bthcqp_.dbf"
  157. datafile 3 switched to datafile copy "/u01/app/oracle/oradata/ORCL/datafile/o1_mf_undotbs1_g0bthcj0_.dbf"
  158. datafile 4 switched to datafile copy "/u01/app/oracle/oradata/ORCL/datafile/o1_mf_users_g0bthdk9_.dbf"
  159. datafile 5 switched to datafile copy "/u01/app/oracle/oradata/ORCL/datafile/o1_mf_example_g0bthd4p_.dbf"

  160. RMAN> report schema;

  161. Report of database schema for database with db_unique_name ORCL

  162. List of Permanent Datafiles
  163. ===========================
  164. File Size(MB) Tablespace           RB segs Datafile Name
  165. ---- -------- -------------------- ------- ------------------------
  166. 1    730      SYSTEM               ***     /u01/app/oracle/oradata/ORCL/datafile/o1_mf_system_g0bthc9n_.dbf
  167. 2    600      SYSAUX               ***     /u01/app/oracle/oradata/ORCL/datafile/o1_mf_sysaux_g0bthcqp_.dbf
  168. 3    650      UNDOTBS1             ***     /u01/app/oracle/oradata/ORCL/datafile/o1_mf_undotbs1_g0bthcj0_.dbf
  169. 4    5        USERS                ***     /u01/app/oracle/oradata/ORCL/datafile/o1_mf_users_g0bthdk9_.dbf
  170. 5    100      EXAMPLE              ***     /u01/app/oracle/oradata/ORCL/datafile/o1_mf_example_g0bthd4p_.dbf

  171. List of Temporary Files
  172. =======================
  173. File Size(MB) Tablespace           Maxsize(MB) Tempfile Name
  174. ---- -------- -------------------- ----------- --------------------
  175. 1    71       TEMP                 32767       +DATA/orcl/tempfile/temp.264.816169645

  176. RMAN> exit


  177. Recovery Manager complete.
  178. [oracle@station90 ~]$ sqlplus /nolog

  179. SQL*Plus: Release 11.2.0.3.0 Production on Tue Dec 4 01:40:19 2018

  180. Copyright (c) 1982, 2011, Oracle.  All rights reserved.

  181. SQL> conn / as sysdba
  182. Connected.
  183. SQL> show parameter control

  184. NAME                                     TYPE         VALUE
  185. ------------------------------------ ----------- ------------------------------
  186. control_file_record_keep_time             integer         7
  187. control_files                             string         +DATA/orcl/controlfile/current
  188.                                                  .260.816169631, +FRA/orcl/cont
  189.                                                  rolfile/current.256.816169633
  190. control_management_pack_access             string         DIAGNOSTIC+TUNING
  191. SQL> alter system set db_recovery_file_dest='/u01/app/oracle/fast_recovery_area';

  192. System altered.

  193. SQL> alter system set control_files='' scope=spfile;

  194. System altered.

  195. SQL> alter database backup controlfile to trace as '/home/oracle/control8c.sql';

  196. Database altered.

  197. SQL> shutdown immediate
  198. ORA-01109: database not open


  199. Database dismounted.
  200. ORACLE instance shut down.
  201. SQL> startup
  202. ORACLE instance started.

  203. Total System Global Area 6664212480 bytes
  204. Fixed Size                    2240944 bytes
  205. Variable Size                 3640659536 bytes
  206. Database Buffers         3003121664 bytes
  207. Redo Buffers                   18190336 bytes
  208. ORA-03113: end-of-file on communication channel
  209. Process ID: 31083
  210. Session ID: 191 Serial number: 1


  211. SQL> startup nomount
  212. ORA-24324: service handle not initialized
  213. ORA-01041: internal error. hostdef extension doesn't exist
  214. SQL> exit
  215. Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
  216. With the Partitioning, OLAP, Data Mining and Real Application Testing options


  217. [oracle@station90 ~]$ sqlplus /nolog

  218. SQL*Plus: Release 11.2.0.3.0 Production on Tue Dec 4 01:43:46 2018

  219. Copyright (c) 1982, 2011, Oracle.  All rights reserved.

  220. SQL> conn / as sysdba
  221. Connected.
  222. SQL> @control8c.sql

  223. Control file created.


  224. PL/SQL procedure successfully completed.


  225. PL/SQL procedure successfully completed.


  226. PL/SQL procedure successfully completed.


  227. PL/SQL procedure successfully completed.


  228. PL/SQL procedure successfully completed.


  229. PL/SQL procedure successfully completed.


  230. PL/SQL procedure successfully completed.


  231. PL/SQL procedure successfully completed.

  232. SQL> show parameter control

  233. NAME                     TYPE     VALUE
  234. ------------------------------------ ----------- ------------------------------
  235. control_file_record_keep_time         integer     7
  236. control_files                 string     /u01/app/oracle/oradata/ORCL/c
  237.                          ontrolfile/o1_mf_g0bttqkw_.ctl
  238.                          , /u01/app/oracle/fast_recover
  239.                          y_area/ORCL/controlfile/o1_mf_
  240.                          g0bttqmm_.ctl
  241. control_management_pack_access         string     DIAGNOSTIC+TUNING
  242. SQL> alter database open ;

  243. Database altered.

  244. SQL> select * from v$tempfile;

  245. no rows selected

  246. SQL> alter tablespace temp add tempfile size 20M autoextend on;

  247. Tablespace altered.

  248. SQL>





  249. [oracle@station90 ~]$ sqlplus /nolog

  250. SQL*Plus: Release 11.2.0.3.0 Production on Tue Dec 4 01:42:55 2018

  251. Copyright (c) 1982, 2011, Oracle.  All rights reserved.




  252. SQL> conn / as sysdba
  253. Connected to an idle instance.
  254. SQL> startup nomount
  255. ORACLE instance started.

  256. Total System Global Area 6664212480 bytes
  257. Fixed Size                    2240944 bytes
  258. Variable Size                 3640659536 bytes
  259. Database Buffers         3003121664 bytes
  260. Redo Buffers                   18190336 bytes
  261. SQL> show parameter spfile

  262. NAME                                     TYPE         VALUE
  263. ------------------------------------ ----------- ------------------------------
  264. spfile                                     string         +DATA/orcl/spfileorcl.ora
  265. SQL> create pfile from spfile;

  266. File created.

  267. SQL> create spfile from pfile;

  268. File created.

  269. SQL> shutdwon immediate ;
  270. SP2-0734: unknown command beginning "shutdwon i..." - rest of line ignored.
  271. SQL> shutdown immediate ;
  272. Database closed.
  273. Database dismounted.
  274. ORACLE instance shut down.
  275. SQL>
复制代码
课程第28/29次(2018-12-1星期六上下午)

1Z0-053第19章(32/40)

1Z0-053第20章(33/40)

  1. run{
  2. duplicate target database to dbclone2
  3. from active database
  4. nofilenamecheck
  5. skip tablespace 'TBS1','TBS2'
  6. spfile
  7. set
  8. control_files='/u01/app/oracle/oradata/dbclone2/control01.ctl','/u01/app/oracle/oradata/dbclone2/control02.ctl','/u01/app/oracle/oradata/dbclone2/control03.ctl'
  9. set
  10. db_file_name_convert='+DATA/orcl/datafile/example.265.816169651','/u01/app/oracle/oradata/dbclone2/example01.dbf','+DATA/orcl/datafile/users.259.816169553','/u01/app/oracle/oradata/dbclone2/users01.dbf','+DATA/orcl/datafile/undotbs1.258.816169553','/u01/app/oracle/oradata/dbclone2/undotbs01.dbf','+DATA/orcl/datafile/sysaux.257.816169553','/u01/app/oracle/oradata/dbclone2/sysaux01.dbf','+DATA/orcl/datafile/system.256.816169553','/u01/app/oracle/oradata/dbclone2/system01.dbf'
  11. set
  12. log_file_name_convert='+DATA/orcl/onlinelog/group_1.261.816169635','/u01/app/oracle/oradata/dbclone2/redo01a.log','+FRA/orcl/onlinelog/group_1.257.816169637','/u01/app/oracle/oradata/dbclone2/redo01b.log','+DATA/orcl/onlinelog/group_3.263.816169641','/u01/app/oracle/oradata/dbclone2/redo03a.log','+FRA/orcl/onlinelog/group_3.259.816169641','/u01/app/oracle/oradata/dbclone2/redo03b.log','+DATA/orcl/onlinelog/group_2.262.816169639','/u01/app/oracle/oradata/dbclone2/redo02a.log','+FRA/orcl/onlinelog/group_2.258.816169639','/u01/app/oracle/oradata/dbclone2/redo02b.log'
  13. set
  14. audit_file_dest='/u01/app/oracle/admin/dbclone2/adump'
  15. set
  16. db_create_file_dest=''
  17. set
  18. db_recovery_file_dest='/u01/app/oracle/fast_recovery_area'
  19. set
  20. memory_target='4G'
  21. ;
  22. }
复制代码

为了backup location,而做的备份:
  1. --源头把备份优化关掉,或用force语法

  2. run {
  3. allocate channel  c1 device type disk format '/home/oracle/backup/%U';
  4. allocate channel  c2 device type disk format '/home/oracle/backup/%U';
  5. allocate channel  c3 device type disk format '/home/oracle/backup/%U';
  6. allocate channel  c4 device type disk format '/home/oracle/backup/%U';
  7. allocate channel  c5 device type disk format '/home/oracle/backup/%U';
  8. allocate channel  c6 device type disk format '/home/oracle/backup/%U';
  9. allocate channel  c7 device type disk format '/home/oracle/backup/%U';
  10. allocate channel  c8 device type disk format '/home/oracle/backup/%U';
  11. backup database plus archivelog force;
  12. backup spfile;
  13. backup current controlfile;
  14. }
复制代码
在目的地(auxiliary):
  1. run{
  2. allocate  auxiliary channel c1 device type disk;
  3. allocate  auxiliary channel c2 device type disk;
  4. allocate  auxiliary channel c3 device type disk;
  5. allocate  auxiliary channel c4 device type disk;
  6. allocate  auxiliary channel c5 device type disk;
  7. allocate  auxiliary channel c6 device type disk;
  8. allocate  auxiliary channel c7 device type disk;
  9. allocate  auxiliary channel c8 device type disk;
  10. duplicate target database to mydb
  11. backup location '/home/oracle/backup'
  12. nofilenamecheck
  13. spfile
  14. set
  15. control_files='/u01/app/oracle/oradata/mydb/control01.ctl','/u01/app/oracle/oradata/db11g/control02.ctl','/u01/app/oracle/oradata/db11g/control03.ctl'
  16. set
  17. db_file_name_convert='+DATA/orcl/datafile/example.258.880451611','/u01/app/oracle/oradata/db11g/example01.dbf','+DATA/orcl/datafile/users.259.880451615','/u01/app/oracle/oradata/db11g/users01.dbf','+DATA/orcl/datafile/undotbs1.256.880451607','/u01/app/oracle/oradata/db11g/undotbs01.dbf','+DATA/orcl/datafile/sysaux.257.880451605','/u01/app/oracle/oradata/db11g/sysaux01.dbf','+DATA/orcl/datafile/system.265.880451605','/u01/app/oracle/oradata/db11g/system01.dbf'
  18. set
  19. log_file_name_convert='+DATA/orcl/onlinelog/group_1.270.880453135','/u01/app/oracle/oradata/db11g/redo01a.log','+FRA/orcl/onlinelog/group_1.276.880453137','/u01/app/oracle/oradata/db11g/redo01b.log','+DATA/orcl/onlinelog/group_3.272.880453141','/u01/app/oracle/oradata/db11g/redo03a.log','+FRA/orcl/onlinelog/group_3.274.880453141','/u01/app/oracle/oradata/db11g/redo03b.log','+DATA/orcl/onlinelog/group_2.271.880453137','/u01/app/oracle/oradata/db11g/redo02a.log','+FRA/orcl/onlinelog/group_2.275.880453139','/u01/app/oracle/oradata/db11g/redo02b.log'
  20. set
  21. audit_file_dest='/u01/app/oracle/admin/mydb/adump'
  22. set
  23. db_create_file_dest=''
  24. set
  25. db_recovery_file_dest='/u01/app/oracle/flash_recovery_area'
  26. }
复制代码
1Z0-053第12章(34/40)


1Z0-053第10章(35/40)
闪回1
(它是闪回8的导航)
  1. select  * from  flashback_transaction_query;

  2. select  * from v$transaction;
复制代码
闪回2:
  1. [oracle@station90 ~]$ sqlplus /nolog

  2. SQL*Plus: Release 11.2.0.3.0 Production on Tue Dec 4 21:47:02 2018

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

  4. SQL> conn hr/oracle_4U
  5. ERROR:
  6. ORA-28002: the password will expire within 7 days


  7. Connected.
  8. SQL> select  salary from employees where employee_id=100;

  9.     SALARY
  10. ----------
  11.      24000

  12. SQL> select  to_char(sysdate,'YYYY-MM-DD:HH24:MI:SS')  from dual;

  13. TO_CHAR(SYSDATE,'YY
  14. -------------------
  15. 2018-12-04:21:47:44

  16. SQL> update employees  set salary=24001 where employee_id=100;

  17. 1 row updated.

  18. SQL> commit;

  19. Commit complete.

  20. SQL> select  salary from employees  as of timestamp to_timestamp('2018-12-04:21:47:44','YYYY-MM-DD:HH24:MI:SS')   where employee_id=100;

  21.     SALARY
  22. ----------
  23.      24000

  24. SQL>
复制代码
闪回3(它是闪回4的导航):
  1. select  versions_xid,
  2.            versions_startscn,
  3.            versions_starttime,
  4.            versions_operation,
  5.            salary
  6.   from hr.employees
  7.   versions between scn minvalue and maxvalue
  8.   where employee_id=100;
复制代码
Screenshot.png


闪回4:
  1. SQL> show user
  2. USER is "HR"
  3. SQL> select  salary from employees
  4.   2   as of scn 2098534
  5.   3   where employee_id=100;

  6.     SALARY
  7. ----------
  8.      24003

  9. SQL> select  salary from employees
  10.   2   as of scn 2098533
  11.   3   where employee_id=100;

  12.     SALARY
  13. ----------
  14.      24002

  15. SQL> flashback table   employees to scn 2098534;
  16. flashback table   employees to scn 2098534
  17.                   *
  18. ERROR at line 1:
  19. ORA-08189: cannot flashback the table because row movement is not enabled


  20. SQL> alter table employees  enable row movement ;

  21. Table altered.

  22. SQL> select  salary from employees where emplpoyee_id=100;
  23. select        salary from employees where emplpoyee_id=100
  24.                                     *
  25. ERROR at line 1:
  26. ORA-00904: "EMPLPOYEE_ID": invalid identifier


  27. SQL> select  salary from employees where employee_id=100;

  28.     SALARY
  29. ----------
  30.      24005

  31. SQL> flashback table   employees to scn 2098534;

  32. Flashback complete.

  33. SQL>  select  salary from employees where employee_id=100;

  34.     SALARY
  35. ----------
  36.      24003

  37. SQL>
复制代码

闪回8:
  1. SUPPLEMENTAL_LOG_DATA: alter database add supplemental log data
  2. SUPPLEMENTAL_LOG_DATA_PK: alter database add supplemental log data (primary key) columns
复制代码
t05310.sql (388 Bytes, 下载次数: 65)
回复

使用道具 举报

1

主题

16

帖子

280

积分

中级会员

Rank: 3Rank: 3

积分
280
QQ
发表于 2018-12-3 10:44:56 | 显示全部楼层
本帖最后由 jarlean 于 2018-12-3 10:50 编辑

补充物理坏块及修复实验:

查询块号:
  1. SQL> select DBMS_ROWID.ROWID_BLOCK_NUMBER(ROWID),t.* from  HR.DEPARTMENTS t;

  2. DBMS_ROWID.ROWID_BLOCK_NUMBER(ROWID) DEPARTMENT_ID DEPARTMENT_NAME                MANAGER_ID LOCATION_ID
  3. ------------------------------------ ------------- ------------------------------ ---------- -----------
  4.                                  491            10 Administration                        200        1700
  5.                                  491            20 Marketing                             201        1800
  6.                                  491            30 Purchasing                            114        1700
  7.                                  491            40 Human Resources                       203        2400
  8.                                  491            50 Shipping                              121        1500
  9.                                  491            60 IT                                    103        1400
  10.                                  491            70 Public Relations                      204        2700
  11.                                  491            80 Sales                                 145        2500
  12.                                  491            90 Executive                             100        1700
  13.                                  491           100 Finance                               108        1700
  14.                                  491           110 Accounting                            205        1700
复制代码


备份数据文件:
  1. RMAN> backup tag='1T_TABSPACE_EXAMPLE' datafile 5;

  2. Starting backup at 2018-12-01:14:26:30
  3. using channel ORA_DISK_1
  4. channel ORA_DISK_1: starting full datafile backup set
  5. channel ORA_DISK_1: specifying datafile(s) in backup set
  6. input datafile file number=00005 name=+DATA/orcl/datafile/example.265.993736237
  7. channel ORA_DISK_1: starting piece 1 at 2018-12-01:14:26:30
  8. channel ORA_DISK_1: finished piece 1 at 2018-12-01:14:26:46
  9. piece handle=+FRA/orcl/backupset/2018_12_01/nnndf0_1t_tabspace_example_0.350.993738391 tag=1T_TABSPACE_EXAMPLE comment=NONE
  10. channel ORA_DISK_1: backup set complete, elapsed time: 00:00:16
  11. Finished backup at 2018-12-01:14:26:46
复制代码


破坏块:
  1. [root@station80 oracle]# sh physical-block11.2.0.1.sh
复制代码


破坏后,修复前:
  1. SQL> select sysdate from dual;

  2. SYSDATE
  3. -------------------
  4. 2018-12-01:14:28:58

  5. SQL> select DBMS_ROWID.ROWID_BLOCK_NUMBER(ROWID),t.* from  HR.DEPARTMENTS t;        
  6. select DBMS_ROWID.ROWID_BLOCK_NUMBER(ROWID),t.* from  HR.DEPARTMENTS t
  7.                                                          *
  8. ERROR at line 1:
  9. ORA-01578: ORACLE data block corrupted (file # 5, block # 491)
  10. ORA-01110: data file 5: '+DATA/orcl/datafile/example.265.993738467'
复制代码


修复:
  1. RMAN> list failure;

  2. List of Database Failures
  3. =========================

  4. Failure ID Priority Status    Time Detected       Summary
  5. ---------- -------- --------- ------------------- -------
  6. 3727       HIGH     OPEN      2018-12-01:11:44:11 Datafile 5: '+DATA/orcl/datafile/example.265.993728527' contains one or more corrupt blocks

  7. RMAN> advise failure;  

  8. List of Database Failures
  9. =========================

  10. Failure ID Priority Status    Time Detected       Summary
  11. ---------- -------- --------- ------------------- -------
  12. 3727       HIGH     OPEN      2018-12-01:11:44:11 Datafile 5: '+DATA/orcl/datafile/example.265.993728527' contains one or more corrupt blocks

  13. analyzing automatic repair options; this may take some time
  14. allocated channel: ORA_DISK_1
  15. channel ORA_DISK_1: SID=202 device type=DISK
  16. analyzing automatic repair options complete

  17. Mandatory Manual Actions
  18. ========================
  19. no manual actions available

  20. Optional Manual Actions
  21. =======================
  22. no manual actions available

  23. Automated Repair Options
  24. ========================
  25. Option Repair Description
  26. ------ ------------------
  27. 1      Recover multiple corrupt blocks in datafile 5  
  28.   Strategy: The repair includes complete media recovery with no data loss
  29.   Repair script: /u01/app/oracle/diag/rdbms/orcl/orcl/hm/reco_1535992553.hm

  30. RMAN> repair failure preview;

  31. Strategy: The repair includes complete media recovery with no data loss
  32. Repair script: /u01/app/oracle/diag/rdbms/orcl/orcl/hm/reco_1535992553.hm

  33. contents of repair script:
  34.    # block media recovery for multiple blocks
  35.    recover datafile 5 block 175, 491;

  36. RMAN> recover datafile 5 block 491;     

  37. Starting recover at 2018-12-01:14:31:47
  38. using channel ORA_DISK_1
  39. searching flashback logs for block images until SCN 1458477
  40. finished flashback log search, restored 0 blocks

  41. channel ORA_DISK_1: restoring block(s)
  42. channel ORA_DISK_1: specifying block(s) to restore from backup set
  43. restoring blocks of datafile 00005
  44. channel ORA_DISK_1: reading from backup piece +FRA/orcl/backupset/2018_12_01/nnndf0_1t_tabspace_example_0.350.993738391
  45. channel ORA_DISK_1: piece handle=+FRA/orcl/backupset/2018_12_01/nnndf0_1t_tabspace_example_0.350.993738391 tag=1T_TABSPACE_EXAMPLE
  46. channel ORA_DISK_1: restored block(s) from backup piece 1
  47. channel ORA_DISK_1: block restore complete, elapsed time: 00:00:03

  48. starting media recovery
  49. media recovery complete, elapsed time: 00:00:03

  50. Finished recover at 2018-12-01:14:32:02
复制代码

修复后:
  1. SQL> select sysdate from dual;

  2. SYSDATE
  3. -------------------
  4. 2018-12-01:14:32:25

  5. SQL> select DBMS_ROWID.ROWID_BLOCK_NUMBER(ROWID),t.* from  HR.DEPARTMENTS t;

  6. DBMS_ROWID.ROWID_BLOCK_NUMBER(ROWID) DEPARTMENT_ID DEPARTMENT_NAME                MANAGER_ID LOCATION_ID
  7. ------------------------------------ ------------- ------------------------------ ---------- -----------
  8.                                  491            10 Administration                        200        1700
  9.                                  491            20 Marketing                             201        1800
  10.                                  491            30 Purchasing                            114        1700
  11.                                  491            40 Human Resources                       203        2400
  12.                                  491            50 Shipping                              121        1500
  13.                                  491            60 IT                                    103        1400
  14.                                  491            70 Public Relations                      204        2700
  15.                                  491            80 Sales                                 145        2500
  16.                                  491            90 Executive                             100        1700
  17.                                  491           100 Finance                               108        1700
  18.                                  491           110 Accounting                            205        1700
复制代码

回复 支持 反对

使用道具 举报

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

本版积分规则

QQ|手机版|Bo's Oracle Station   

GMT+8, 2024-3-28 20:12 , Processed in 0.070485 second(s), 28 queries .

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