剩余课程

admin · 发表于 2018-11-20 20:53:28

课程第17次（2018-11-20星期二）

[oracle@station90 ~]$ mkdir second_arc
[oracle@station90 ~]$ sqlplus /nolog
SQL*Plus: Release 11.2.0.3.0 Production on Tue Nov 20 20:37:55 2018
Copyright (c) 1982, 2011, Oracle. All rights reserved.
SQL> conn / as sysdba
Connected.
SQL> show parameter log_Archive_dest_1
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
log_archive_dest_1 string
log_archive_dest_10 string
log_archive_dest_11 string
log_archive_dest_12 string
log_archive_dest_13 string
log_archive_dest_14 string
log_archive_dest_15 string
log_archive_dest_16 string
log_archive_dest_17 string
log_archive_dest_18 string
log_archive_dest_19 string
SQL> alter system set log_Archive_dest_2='location=/home/oracle/second_arch' ;
System altered.
SQL> alter system set log_archive_dest_1='location=use_db_recovery_file_dest';
System altered.
SQL> show parameter log_archive_format
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
log_archive_format string %t_%s_%r.dbf
SQL> alter system set log_archive_dest_1='';
System altered.
SQL> alter system set log_Archive_dest_2='';
System altered.
SQL> alter system switch logfile;
System altered.
SQL> alter system set log_archive_dest_1='location=use_db_recovery_file_dest';
System altered.
SQL> alter system switch logfile;
System altered.
SQL> exit

复制代码

1Z0-052第14章（16/40）

课程第18次（2018-11-21星期三）
关于第4号实验：

[oracle@station90 ~]$ sqlplus /nolog
SQL*Plus: Release 11.2.0.3.0 Production on Wed Nov 21 19:32:51 2018
Copyright (c) 1982, 2011, Oracle. All rights reserved.
SQL> conn / as sysdba
Connected.
SQL> alter database create datafile '+DATA/orcl/datafile/tbsocp05_test.267.992805849' as new ;
Database altered.

复制代码

关于第1d号实验:

SQL> conn / as sysdba
Connected to an idle instance.
SQL> startup
ORACLE instance started.
Total System Global Area 6664212480 bytes
Fixed Size 2240944 bytes
Variable Size 3707768400 bytes
Database Buffers 2936012800 bytes
Redo Buffers 18190336 bytes
Database mounted.
ORA-10873: file 4 needs to be either taken out of backup mode or media
recovered
ORA-01110: data file 4: '+DATA/orcl/datafile/users.259.992728183'
SQL> select * from v$backup;
FILE# STATUS CHANGE# TIME
---------- ------------------ ---------- ------------------
1 NOT ACTIVE 0
2 NOT ACTIVE 0
3 NOT ACTIVE 0
4 ACTIVE 2681584 21-NOV-18
5 NOT ACTIVE 0
7 NOT ACTIVE 0
6 rows selected.
SQL> alter database end backup;
Database altered.
SQL> select * from v$backup;
FILE# STATUS CHANGE# TIME
---------- ------------------ ---------- ------------------
1 NOT ACTIVE 0
2 NOT ACTIVE 0
3 NOT ACTIVE 0
4 NOT ACTIVE 2681584 21-NOV-18
5 NOT ACTIVE 0
7 NOT ACTIVE 0
6 rows selected.
SQL> alter database open;
Database altered.
SQL>

复制代码

在ASM磁盘组中，某个文件夹中的文件时间排序：

thread_1_seq_166.283.992805855
thread_1_seq_167.285.992807497
thread_1_seq_168.286.992807497
thread_1_seq_169.287.992807497
thread_1_seq_170.288.992807497
thread_1_seq_171.289.992807499
thread_1_seq_172.290.992807499
thread_1_seq_173.291.992807741
thread_1_seq_174.292.992808351
thread_1_seq_175.293.992808353
thread_1_seq_176.294.992808353
thread_1_seq_177.295.992808355
thread_1_seq_178.296.992808359
thread_1_seq_179.297.992808359
thread_1_seq_1.299.992809501
thread_1_seq_2.300.992809503
thread_1_seq_3.303.992809503
thread_1_seq_4.304.992809507
thread_1_seq_5.305.992809507
thread_1_seq_6.307.992809507
thread_1_seq_7.308.992809507
thread_1_seq_8.309.992809509
thread_1_seq_10.311.992809513
thread_1_seq_11.312.992809513
thread_1_seq_9.310.992809513
thread_1_seq_12.313.992809515
thread_1_seq_13.314.992809517
thread_1_seq_14.315.992809517
thread_1_seq_15.316.992809517
thread_1_seq_16.317.992809533
thread_1_seq_17.318.992809535
thread_1_seq_18.319.992809535
thread_1_seq_19.320.992809537
thread_1_seq_20.321.992809539
[oracle@station90 ~]$ asmcmd ls +fra/orcl/ARCHIVELOG/2018_11_21 | sort -n -t . -k 3

复制代码

[oracle@station90 ~]$ sqlplus /nolog
SQL*Plus: Release 11.2.0.3.0 Production on Wed Nov 21 20:37:07 2018
Copyright (c) 1982, 2011, Oracle. All rights reserved.
SQL> conn / as sysdba
Connected.
SQL> recover database until cancel;
ORA-00279: change 2706558 generated at 11/21/2018 20:25:39 needed for thread 1
ORA-00289: suggestion : +FRA
ORA-00280: change 2706558 for thread 1 is in sequence #21
Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
ORA-00308: cannot open archived log '+FRA'
ORA-17503: ksfdopn:2 Failed to open file +FRA
ORA-15045: ASM file name '+FRA' is not in reference form
ORA-01547: warning: RECOVER succeeded but OPEN RESETLOGS would get error below
ORA-01194: file 1 needs more recovery to be consistent
ORA-01110: data file 1: '+DATA/orcl/datafile/system.256.816169553'
SQL> recover database until cancel;
ORA-00279: change 2706558 generated at 11/21/2018 20:25:39 needed for thread 1
ORA-00289: suggestion : +FRA
ORA-00280: change 2706558 for thread 1 is in sequence #21
Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
ORA-01547: warning: RECOVER succeeded but OPEN RESETLOGS would get error below
ORA-01194: file 1 needs more recovery to be consistent
ORA-01110: data file 1: '+DATA/orcl/datafile/system.256.816169553'
SQL>
SQL>
SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, Automatic Storage Management, OLAP, Data Mining
and Real Application Testing options
[oracle@station90 ~]$ rman target /
Recovery Manager: Release 11.2.0.3.0 - Production on Wed Nov 21 20:38:13 2018
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
connected to target database: ORCL (DBID=1343950367, not open)
RMAN> run {
2> set until sequence 21 thread 1;
3> restore database;
4> recover database;
5> }

复制代码

1Z0-052第4章（17/40）

课程第19次（2018-11-22星期四）

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

复制代码

	SUBSTR(T.TABLE_NAME,3,1)	COUNT(*)
1	R	15
2	H	100
3	I	80
4	M	10

select * from dba_alert_history;
select * from dba_views v
where v.view_name='DBA_ALERT_HISTORY';

复制代码

select sequence_id,
reason_id,
owner,
object_name,
subobject_name,
typnam_keltosd AS object_type,
dbms_server_alert.expand_message(userenv('LANGUAGE'),
mid_keltsd,
reason_argument_1,
reason_argument_2,
reason_argument_3,
reason_argument_4,
reason_argument_5) AS reason,
time_suggested,
creation_time,
dbms_server_alert.expand_message(userenv('LANGUAGE'),
amid_keltsd,
action_argument_1,
action_argument_2,
action_argument_3,
action_argument_4,
action_argument_5)
AS suggested_action,
advisor_name,
metric_value,
decode(message_level, 32, 'Notification', 'Warning')
AS message_type,
nam_keltgsd AS message_group,
message_level,
hosting_client_id,
mdid_keltsd AS module_id,
process_id,
host_id,
host_nw_addr,
instance_name,
instance_number,
user_id,
execution_context_id,
error_instance_id,
decode(resolution, 1, 'cleared', 'N/A') AS resolution
FROM wri$_alert_history, X$KELTSD, X$KELTOSD, X$KELTGSD,
dba_advisor_definitions
WHERE reason_id = rid_keltsd
AND otyp_keltsd = typid_keltosd
AND grp_keltsd = id_keltgsd
AND aid_keltsd = advisor_id(+)

复制代码

select dbms_stats.get_prefs(pname => 'STALE_PERCENT') from dual;
begin
dbms_stats.set_global_prefs(pname => 'STALE_PERCENT',pvalue => '13') ;
end;
select dbms_stats.get_prefs(pname => 'STALE_PERCENT') from dual;
begin
dbms_stats.gather_database_stats;
end;
--针对个别的挥发+倾斜表，单独收集：
begin
dbms_stats.gather_table_stats(ownname => 'HR',tabname => 'EMPLOYEES',
estimate_percent =>100,
method_opt => 'for all columns size 254');
end;

复制代码

课程第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调优集：

BEGIN dbms_sqltune.create_sqlset(sqlset_name => 'STS1', sqlset_owner =>'SYS'); END;
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;

复制代码

begin
dbms_workload_repository.create_baseline(start_snap_id => 101,
end_snap_id => 103,
baseline_name => 'PRESERV101_103');
end;

复制代码

select * from dba_tablespaces;
select * from dba_sql_profiles;
create tablespace tbs05212 datafile size 10M autoextend off;
select bytes/1024/1024, tablespace_name from dba_segments s
where s.owner='HR' and s.segment_name='T04209_UNAME';
alter table hr.t04209_uname move tablespace tbs05212;
select * from dba_outstanding_alerts;
select * from dba_alert_history h order by h.time_suggested desc
;

复制代码

1Z0-052第12章（18/40）

select * from V$MEMORY_TARGET_ADVICE;

复制代码

1Z0-052第13章（19/40）

select t.table_name, t.buffer_pool from dba_tables t
where t.owner='HR' and t.table_name='EMPLOYEES';
alter table hr.employees storage (buffer_pool keep );
select o.NAME , o.KEPT from v_$db_object_cache o
where owner='HR';
begin
dbms_shared_pool.keep('HR.PROC1');
end;

复制代码

1Z0-053第13章（20/40）

select s.USERNAME, s.SID,s.SERIAL#,
s.SQL_HASH_VALUE, s.SQL_ID
from v_$session s
where s.TERMINAL='pts/13';
select * from v$sql where sql_id='dh73w3ss300hp';
select * from V$SYSTEM_WAIT_CLASS;

复制代码

1Z0-053第14章（21/40）

select * from dba_directories;
create directory replaydir as '/home/oracle/replaydir';
select * from dba_sql_profiles;
begin
dbms_advisor.quick_tune(advisor_name => 'SQL Access Advisor',
task_name => 'MYTASK1',
attr1 =>'select text,count(*) from hr.t_big group by text',
template => 'SQLACCESS_OLTP');
end;
begin
dbms_advisor.quick_tune(advisor_name => 'SQL Access Advisor',
task_name => 'MYTASK2',
attr1 =>'select text,count(*) from hr.t_big group by text',
template => 'SQLACCESS_WAREHOUSE');
end;

复制代码

1Z0-053第15章（22/40）

课程第22次（2018-11-25星期日下午）
行链接和行迁移：

[root@station90 ~]# su - oracle
[oracle@station90 ~]$ sqlplus /nolog
SQL*Plus: Release 11.2.0.1.0 Production on Sat May 30 15:13:48 2015
Copyright (c) 1982, 2009, Oracle. All rights reserved.
SQL> conn hr/oracle_4U
ERROR:
ORA-28002: the password will expire within 5 days
Connected.
SQL> create table t05318_chain(a varchar2(2000), b varchar2(2000), c varchar2(2000), d varchar2(2000));
Table created.
SQL> insert into t05318_chain(a) values('aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa');
1 row created.
SQL> commit;
Commit complete.
SQL> update t05318_chain set b='aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa';
1 row updated.
SQL> commit;
Commit complete.
SQL> update t05318_chain set c='aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa';
1 row updated.
SQL> commit;
Commit complete.
SQL> select dbms_rowid.rowid_block_number ( rowid ) from t05318_chain;
DBMS_ROWID.ROWID_BLOCK_NUMBER(ROWID)
------------------------------------
559
SQL> select * from CHAINED_ROWS;
select * from CHAINED_ROWS
*
ERROR at line 1:
ORA-00942: table or view does not exist
SQL> @?/rdbms/admin/utlchain.sql
Table created.
SQL> set linesize 1000
SQL> select * from CHAINED_ROWS;
no rows selected
SQL> analyze table t05318_chain list chained rows;
Table analyzed.
SQL> select * from CHAINED_ROWS;
no rows selected
SQL> update t05318_chain set d='aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa';
1 row updated.
SQL> commit;
Commit complete.
SQL> select dbms_rowid.rowid_block_number ( rowid ) from t05318_chain;
DBMS_ROWID.ROWID_BLOCK_NUMBER(ROWID)
------------------------------------
559
SQL> analyze table t05318_chain list chained rows;
Table analyzed.
SQL> select * from CHAINED_ROWS;
OWNER_NAME TABLE_NAME CLUSTER_NAME PARTITION_NAME SUBPARTITION_NAME HEAD_ROWID ANALYZE_TIMESTAMP
------------------------------ ------------------------------ ------------------------------ ------------------------------ ------------------------------ ------------------ ------------------
HR T05318_CHAIN N/A AAASOPAAEAAAAIvAAA 30-MAY-15
SQL> truncate table CHAINED_ROWS;
Table truncated.
SQL> select * from CHAINED_ROWS;
no rows selected
SQL> analyze table t05318_chain list chained rows;
Table analyzed.
SQL> select * from CHAINED_ROWS;
OWNER_NAME TABLE_NAME CLUSTER_NAME PARTITION_NAME SUBPARTITION_NAME HEAD_ROWID ANALYZE_TIMESTAMP
------------------------------ ------------------------------ ------------------------------ ------------------------------ ------------------------------ ------------------ ------------------
HR T05318_CHAIN N/A AAASOPAAEAAAAIvAAA 30-MAY-15
SQL> truncate table CHAINED_ROWS;
Table truncated.
SQL> select * from CHAINED_ROWS;
no rows selected
SQL> alter table t05318_chain move tablspace tbs16k;
alter table t05318_chain move tablspace tbs16k
*
ERROR at line 1:
ORA-14133: ALTER TABLE MOVE cannot be combined with other operations
SQL> alter table t05318_chain move tablespace tbs16k;
Table altered.
SQL> analyze table t05318_chain list chained rows;
Table analyzed.
SQL> select * from CHAINED_ROWS;
no rows selected
SQL> create table t05318_migrate( a varchar2(2000)) pctfree 0;
Table created.
SQL> show user
USER is "HR"
begin
for i in 1..733
loop
insert into t05318_migrate values('A');
end loop;
commit;
7 end;
8 /
PL/SQL procedure successfully completed.
SQL> select dbms_rowid.rowid_block_number(rowid) , count(*) from t05318_migrate
2 group by dbms_rowid.rowid_block_number(rowid);
DBMS_ROWID.ROWID_BLOCK_NUMBER(ROWID) COUNT(*)
------------------------------------ ----------
559 733
SQL> begin
2 for i in 1..100
3 loop
4 insert into t05318_migrate values('B');
5 end loop;
6 commit;
7 end;
8 /
PL/SQL procedure successfully completed.
SQL> select dbms_rowid.rowid_block_number(rowid) , count(*) from t05318_migrate
2 group by dbms_rowid.rowid_block_number(rowid);
DBMS_ROWID.ROWID_BLOCK_NUMBER(ROWID) COUNT(*)
------------------------------------ ----------
555 100
559 733
SQL> delete from t05318_migrate where A='B';
100 rows deleted.
SQL> commit;
Commit complete.
SQL> select dbms_rowid.rowid_block_number(rowid) , count(*) from t05318_migrate
2 group by dbms_rowid.rowid_block_number(rowid);
DBMS_ROWID.ROWID_BLOCK_NUMBER(ROWID) COUNT(*)
------------------------------------ ----------
559 733
SQL> analyze table t05318_migrate list chained rows;
Table analyzed.
SQL> select * from chained_rows;
no rows selected
SQL> update t05318_migrate set a=''aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa');
ERROR:
ORA-00972: identifier is too long
SQL> update t05318_migrate set a='aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa' where rownum=1;
1 row updated.
SQL> commit;
Commit complete.
SQL> analyze table t05318_migrate list chained rows;
Table analyzed.
SQL> select * from chained_rows;
OWNER_NAME TABLE_NAME CLUSTER_NAME PARTITION_NAME SUBPARTITION_NAME HEAD_ROWID ANALYZE_TIMESTAMP
------------------------------ ------------------------------ ------------------------------ ------------------------------ ------------------------------ ------------------ ------------------
HR T05318_MIGRATE N/A AAASOUAAEAAAAIvAAA 30-MAY-15
SQL> select dbms_rowid.rowid_block_number(rowid) , count(*) from t05318_migrate
2 group by dbms_rowid.rowid_block_number(rowid);
DBMS_ROWID.ROWID_BLOCK_NUMBER(ROWID) COUNT(*)
------------------------------------ ----------
559 733
SQL> alter table T05318_MIGRATE move tablespace users;
Table altered.
SQL> select dbms_rowid.rowid_block_number(rowid) , count(*) from t05318_migrate
2 group by dbms_rowid.rowid_block_number(rowid);
DBMS_ROWID.ROWID_BLOCK_NUMBER(ROWID) COUNT(*)
------------------------------------ ----------
588 185
587 548
SQL> alter table T05318_MIGRATE pctfree 20;
Table altered.
SQL> alter table T05318_MIGRATE move tablespace users;
Table altered.
SQL> select dbms_rowid.rowid_block_number(rowid) , count(*) from t05318_migrate
2 group by dbms_rowid.rowid_block_number(rowid);
DBMS_ROWID.ROWID_BLOCK_NUMBER(ROWID) COUNT(*)
------------------------------------ ----------
555 402
556 331
SQL> truncate table chained_rows;
Table truncated.
SQL> analyze table T05318_MIGRATE list chained rows;
Table analyzed.
SQL> select * from chained_rows;
no rows selected
SQL>

复制代码

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

复制代码

IOT：

select * from dba_tables t where t.owner='HR' and t.table_name='IOT1';
select * from dba_segments s where s.owner='HR' and s.segment_name='IOT1';
select * from dba_constraints c where c.owner='HR' and c.table_name='IOT1';
select * from dba_indexes i where i.owner='HR' and i.index_name='IOT1_PK';
select * from dba_objects o where o.owner='HR' and o.object_name='IOT1';
select * from dba_tables t where t.owner='HR' and t.table_name like '%82191%';
select * from dba_segments s where s.owner='HR' and s.segment_name='SYS_IOT_OVER_82191';

复制代码

SQL> create bitmap index bix_iot1 on iot1(b) ;
create bitmap index bix_iot1 on iot1(b)
*
ERROR at line 1:
ORA-28669: bitmap index can not be created on an IOT with no mapping table
alter table hr.iot1 move mapping table;
alter table hr.iot1 move nomapping;
SQL> create bitmap index bix_iot1 on iot1(b) ;
Index created.

复制代码

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

复制代码

压缩：

Oracle11gR2的表压缩特性通过压缩表中的数据以减少对空间的占用。Oracle11gR2支持三种不同的压缩方式：
· 支持直接路径加载的Basic压缩方式（10x）
· 支持针对所有DML操作的OLTP压缩方式（2-4x）
· Exadata专属的Hybrid columnar compression压缩方式
Oracle公司建议我们采用以上三种不同的压缩方式，针对特定的应用场合来进行表压缩。如果该表包含大量冗长的和重复的值，通过被压缩可以减少该表对磁盘空间的占用和对SGA中数据库缓冲区缓存的占用。
先从图形界面入手，我们发现在新建表空间时Oracle11gR2的界面出现压缩选项：
在下面各个部分所有的实验中我们都创建两个表：一个表在TBS_NOCOMPRESSION表空间，另一个表在有对应默认压缩选项的表空间。最后我们把在 TBS_NOCOMPRESSION表空间上的那个表转换成对应压缩选项表。通过对比实验来弄清Oracle11gR2这方面的新特性。
--sys--
create tablespace tbs_nocompression datafile size 10M autoextend on;
create tablespace tbs_basic datafile size 10M autoextend on default compress basic;
create tablespace tbs_oltp datafile size 10M autoextend on default compress for oltp;
create tablespace tbs_query datafile size 10M autoextend on default compress for query;
create tablespace tbs_archive datafile size 10M autoextend on default compress for archive;
select t.tablespace_name, t.def_tab_compression, t.compress_for from dba_tablespaces t
where t.tablespace_name in ('TBS_NOCOMPRESSION',
'TBS_BASIC', 'TBS_OLTP',
'TBS_QUERY',
'TBS_ARCHIVE');
--hr--
create table t_nocompression (a varchar2(200)) tablespace tbs_nocompression;
create table t_basic (a varchar2(200)) tablespace tbs_basic;
create table t_oltp (a varchar2(200)) tablespace tbs_oltp;
--hr-error--
create table t_query (a varchar2(200)) tablespace tbs_query;
create table t_archive (a varchar2(200)) tablespace tbs_archive;
--hr--
begin
for i in 1..400
loop
insert into t_nocompression values('AAAAAAAAAAAAAAAAAAAA');
end loop;
commit;
end;
--hr--
begin
for i in 1..400
loop
insert into t_basic values('AAAAAAAAAAAAAAAAAAAA');
end loop;
commit;
end;
--hr--
begin
for i in 1..400
loop
insert into t_oltp values('AAAAAAAAAAAAAAAAAAAA');
end loop;
commit;
end;
--hr--
select t.TABLE_NAME, t.PCT_FREE , t.COMPRESSION , t.COMPRESS_FOR, t.TABLESPACE_NAME
from user_tables t where t.TABLE_NAME in ('T_NOCOMPRESSION','T_BASIC','T_OLTP');
select count(*) , substr(rowid, 10, 6 ) from hr.t_nocompression group by substr(rowid, 10, 6 );
select count(*) , substr(rowid, 10, 6 ) from hr.t_basic group by substr(rowid, 10, 6 );
select count(*) , substr(rowid, 10, 6 ) from hr.t_oltp group by substr(rowid, 10, 6 );
--sys--
select count(*) , dbms_compression.get_compression_type(ownname => 'HR',tabname => 'T_NOCOMPRESSION',row_id => rowid)
from hr.T_NOCOMPRESSION group by dbms_compression.get_compression_type(ownname => 'HR',tabname => 'T_NOCOMPRESSION',row_id => rowid);
--sys--
select count(*) , dbms_compression.get_compression_type(ownname => 'HR',tabname => 'T_BASIC',row_id => rowid)
from hr.T_BASIC group by dbms_compression.get_compression_type(ownname => 'HR',tabname => 'T_BASIC',row_id => rowid);
--sys--
select count(*) , dbms_compression.get_compression_type(ownname => 'HR',tabname => 'T_OLTP',row_id => rowid)
from hr.T_OLTP group by dbms_compression.get_compression_type(ownname => 'HR',tabname => 'T_OLTP',row_id => rowid);
--hr--
create table t_basic2 (a varchar2(200)) tablespace tbs_nocompression compress;
create table t_oltp2 (a varchar2(200)) tablespace tbs_nocompression compress for oltp;
--hr-error--
create table t_query2 (a varchar2(200)) tablespace tbs_nocompression compress for query;
create table t_archive2 (a varchar2(200)) tablespace tbs_nocompression compress for archive;
--hr--
select t.TABLE_NAME, t.PCT_FREE , t.COMPRESSION , t.COMPRESS_FOR, t.TABLESPACE_NAME
from user_tables t where t.TABLE_NAME in ('T_NOCOMPRESSION','T_BASIC','T_OLTP', 'T_BASIC2','T_OLTP2' );
--hr--
begin
for i in 1..400
loop
insert into t_basic2 values('AAAAAAAAAAAAAAAAAAAA');
end loop;
commit;
end;
--hr--
begin
for i in 1..400
loop
insert into t_oltp2 values('AAAAAAAAAAAAAAAAAAAA');
end loop;
commit;
end;
--hr--
select count(*) , substr(rowid, 10, 6 ) from hr.t_basic2 group by substr(rowid, 10, 6 );
select count(*) , substr(rowid, 10, 6 ) from hr.t_oltp2 group by substr(rowid, 10, 6 );
--sys--
select count(*) , dbms_compression.get_compression_type(ownname => 'HR',tabname => 'T_BASIC2',row_id => rowid)
from hr.T_BASIC2 group by dbms_compression.get_compression_type(ownname => 'HR',tabname => 'T_BASIC2',row_id => rowid);
--sys--
select count(*) , dbms_compression.get_compression_type(ownname => 'HR',tabname => 'T_OLTP2',row_id => rowid)
from hr.T_OLTP2 group by dbms_compression.get_compression_type(ownname => 'HR',tabname => 'T_OLTP2',row_id => rowid);
--hr--
alter table t_nocompression compress for oltp;
select t.TABLE_NAME, t.PCT_FREE , t.COMPRESSION , t.COMPRESS_FOR, t.TABLESPACE_NAME
from user_tables t where t.TABLE_NAME in ('T_NOCOMPRESSION','T_BASIC','T_OLTP', 'T_BASIC2','T_OLTP2' );
select count(*) , substr(rowid, 10, 6 ) from hr.t_nocompression group by substr(rowid, 10, 6 );
--sys--
select count(*) , dbms_compression.get_compression_type(ownname => 'HR',tabname => 'T_NOCOMPRESSION',row_id => rowid)
from hr.T_NOCOMPRESSION group by dbms_compression.get_compression_type(ownname => 'HR',tabname => 'T_NOCOMPRESSION',row_id => rowid);
--hr--
begin
for i in 1..400
loop
insert into t_nocompression values('AAAAAAAAAAAAAAAAAAAA');
end loop;
commit;
end;
--hr--
select count(*) , substr(rowid, 10, 6 ) from hr.t_nocompression group by substr(rowid, 10, 6 );
--sys--
select count(*) , dbms_compression.get_compression_type(ownname => 'HR',tabname => 'T_NOCOMPRESSION',row_id => rowid)
from hr.T_NOCOMPRESSION group by dbms_compression.get_compression_type(ownname => 'HR',tabname => 'T_NOCOMPRESSION',row_id => rowid);
--hr--
alter table t_nocompression move tablespace tbs_nocompression;
select count(*) , substr(rowid, 10, 6 ) from hr.t_nocompression group by substr(rowid, 10, 6 );
--sys--
select count(*) , dbms_compression.get_compression_type(ownname => 'HR',tabname => 'T_NOCOMPRESSION',row_id => rowid)
from hr.T_NOCOMPRESSION group by dbms_compression.get_compression_type(ownname => 'HR',tabname => 'T_NOCOMPRESSION',row_id => rowid);
--hr--
select count(*) , substr(rowid, 10, 6 ) from hr.t_basic group by substr(rowid, 10, 6 );
insert /*+ append */ into t_basic select * from t_basic;
commit;
select count(*) , substr(rowid, 10, 6 ) from hr.t_basic group by substr(rowid, 10, 6 );
--sys--
select count(*) , dbms_compression.get_compression_type(ownname => 'HR',tabname => 'T_BASIC',row_id => rowid)
from hr.T_BASIC group by dbms_compression.get_compression_type(ownname => 'HR',tabname => 'T_BASIC',row_id => rowid);
--hr--
alter table t_basic move tablespace TBS_NOCOMPRESSION;
select count(*) , substr(rowid, 10, 6 ) from hr.t_basic group by substr(rowid, 10, 6 );
--sys--
select count(*) , dbms_compression.get_compression_type(ownname => 'HR',tabname => 'T_BASIC',row_id => rowid)
from hr.T_BASIC group by dbms_compression.get_compression_type(ownname => 'HR',tabname => 'T_BASIC',row_id => rowid);
--sys-evolution--
drop table hr.t_basic_big;
create table hr.t_basic_big compress as select * from dba_source;
--sys-advisor--
declare
v_blkcnt_cmp number;
v_blkcnt_uncmp number;
v_row_cmp number;
v_row_uncmp number;
v_cmp_ratio number;
v_comptype_str varchar2(200);
BEGIN
DBMS_COMPRESSION.GET_COMPRESSION_RATIO(scratchtbsname => 'USERS',
ownname =>'HR',
tabname =>'T_BASIC_BIG',
partname =>null,
comptype => 2,
blkcnt_cmp => v_blkcnt_cmp,
blkcnt_uncmp => v_blkcnt_uncmp,
row_cmp =>v_row_cmp,
row_uncmp => v_row_uncmp,
cmp_ratio => v_cmp_ratio,
comptype_str =>v_comptype_str);
DBMS_OUTPUT.PUT_LINE('Blk count compressed = ' || v_blkcnt_cmp);
DBMS_OUTPUT.PUT_LINE('Blk count uncompressed = ' || v_blkcnt_uncmp);
DBMS_OUTPUT.PUT_LINE('Row count per block compressed = ' || v_row_cmp);
DBMS_OUTPUT.PUT_LINE('Row count per block uncompressed = ' || v_row_uncmp);
DBMS_OUTPUT.PUT_LINE('ratio: '||v_cmp_ratio);
DBMS_OUTPUT.PUT_LINE('Compression type = ' || v_comptype_str);
end;
--输出--
Blk count compressed = 1785
Blk count uncompressed = 2340
Row count per block compressed = 68
Row count per block uncompressed = 51
ratio: 1.31092436974789915966386554621848739496
Compression type = "Compress For OLTP"
--OLTP压缩一下表--
验证表大小
--sys--
select count(*) , dbms_compression.get_compression_type(ownname => 'HR',tabname => 'T_BASIC_BIG',row_id => rowid)
from hr.T_BASIC_BIG group by dbms_compression.get_compression_type(ownname => 'HR',tabname => 'T_BASIC_BIG',row_id => rowid);
--hr--
create table t_basic_col( a number , b varchar2(20)) compress ;
insert into t_basic_col values ( 1,'A') ;
commit;
create table t_oltp_col( a number , b varchar2(20)) compress for oltp;
insert into t_oltp_col values ( 1,'A') ;
commit;
select * from t_basic_col;
select * from t_oltp_col;
alter table t_basic_col drop column b;
alter table t_basic_col drop ( b);
alter table t_oltp_col drop column b;
select * from t_oltp_col;

复制代码

create tablespace tbs05318 datafile size 5M autoextend off;
select file_name , bytes/1024/1024 , d.autoextensible
from dba_data_files d where tablespace_name='TBS05318';
grant resumable to hr;

复制代码

SQL> conn / as sysdba
Connected.
SQL> show parameter resum
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
resumable_timeout integer 0
SQL> conn hr/oracle_4U
Connected.
SQL> create table t05318 ( a number ) tablespace tbs05318 storage ( initial 6M ) ;
create table t05318 ( a number ) tablespace tbs05318 storage ( initial 6M )
*
ERROR at line 1:
ORA-00955: name is already used by an existing object
SQL> create table t05318_b ( a number ) tablespace tbs05318 storage ( initial 6M ) ;
Table created.
SQL> insert into t05318_b values (1) ;
insert into t05318_b values (1)
*
ERROR at line 1:
ORA-01659: unable to allocate MINEXTENTS beyond 4 in tablespace TBS05318
SQL> alter session enable resumable;
ERROR:
ORA-01031: insufficient privileges
SQL> alter session set resumable_timeout=5;
Session altered.
SQL> insert into t05318_b values (1) ;
insert into t05318_b values (1)
*
ERROR at line 1:
ORA-30032: the suspended (resumable) statement has timed out
ORA-01659: unable to allocate MINEXTENTS beyond 4 in tablespace TBS05318
SQL> insert into t05318_b values (1) ;
insert into t05318_b values (1)
*
ERROR at line 1:
ORA-30032: the suspended (resumable) statement has timed out
ORA-01659: unable to allocate MINEXTENTS beyond 4 in tablespace TBS05318
SQL>

复制代码

create tablespace tbs05318 datafile size 5M autoextend off;
select file_name , bytes/1024/1024 , d.autoextensible
from dba_data_files d where tablespace_name='TBS05318';
grant resumable to hr;
--+DATA/orcl/datafile/tbs05318.274.993144427
CREATE OR REPLACE TRIGGER SYS.TRG_SUSPEND
AFTER SUSPEND
ON DATABASE
declare
v_size number;
pragma AUTONOMOUS_TRANSACTION;
begin
select bytes into v_size from dba_data_files where file_name='+DATA/orcl/datafile/tbs05318.274.993144427';
v_size := v_size +3145728 ;
execute immediate 'alter database datafile ''+DATA/orcl/datafile/tbs05318.274.993144427'' resize '||to_char(v_size);
commit;
end;

复制代码

1Z0-053第18章（23/40）

课程第23次（2018-11-26星期一）

[oracle@station80 ~]$ rman target /
Recovery Manager: Release 11.2.0.1.0 - Production on Mon Nov 26 04:46:07 2018
Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.
connected to target database: ORCL (DBID=1359978017)
RMAN> show all;
using target database control file instead of recovery catalog
RMAN configuration parameters for database with db_unique_name ORCL are:
CONFIGURE RETENTION POLICY TO RECOVERY WINDOW OF 1 DAYS;
CONFIGURE BACKUP OPTIMIZATION ON;
CONFIGURE DEFAULT DEVICE TYPE TO 'SBT_TAPE';
CONFIGURE CONTROLFILE AUTOBACKUP ON;
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '%F'; # default
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE SBT_TAPE TO '%F'; # default
CONFIGURE DEVICE TYPE DISK PARALLELISM 8 BACKUP TYPE TO BACKUPSET;
CONFIGURE DEVICE TYPE SBT_TAPE PARALLELISM 1 BACKUP TYPE TO BACKUPSET; # default
CONFIGURE DATAFILE BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
CONFIGURE DATAFILE BACKUP COPIES FOR DEVICE TYPE SBT_TAPE TO 1; # default
CONFIGURE ARCHIVELOG BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
CONFIGURE ARCHIVELOG BACKUP COPIES FOR DEVICE TYPE SBT_TAPE TO 1; # default
CONFIGURE MAXSETSIZE TO UNLIMITED; # default
CONFIGURE ENCRYPTION FOR DATABASE OFF; # default
CONFIGURE ENCRYPTION ALGORITHM 'AES128'; # default
CONFIGURE COMPRESSION ALGORITHM 'BASIC' AS OF RELEASE 'DEFAULT' OPTIMIZE FOR LOAD TRUE ; # default
CONFIGURE ARCHIVELOG DELETION POLICY TO NONE; # default
CONFIGURE SNAPSHOT CONTROLFILE NAME TO '/u01/app/oracle/product/11.2.0/dbhome_1/dbs/snapcf_orcl.f'; # default
RMAN> CONFIGURE DEVICE TYPE SBT_TAPE PARALLELISM 2 BACKUP TYPE TO BACKUPSET;
new RMAN configuration parameters:
CONFIGURE DEVICE TYPE 'SBT_TAPE' PARALLELISM 2 BACKUP TYPE TO BACKUPSET;
new RMAN configuration parameters are successfully stored
RMAN> CONFIGURE CHANNEL 1 DEVICE TYPE 'SBT_TAPE' PARMS 'ENV=(OB_MEDIA_FAMILY=station80)';
new RMAN configuration parameters:
CONFIGURE CHANNEL 1 DEVICE TYPE 'SBT_TAPE' PARMS 'ENV=(OB_MEDIA_FAMILY=station80)';
new RMAN configuration parameters are successfully stored
RMAN> CONFIGURE CHANNEL 2 DEVICE TYPE 'SBT_TAPE' PARMS 'ENV=(OB_MEDIA_FAMILY=station80)';
new RMAN configuration parameters:
CONFIGURE CHANNEL 2 DEVICE TYPE 'SBT_TAPE' PARMS 'ENV=(OB_MEDIA_FAMILY=station80)';
new RMAN configuration parameters are successfully stored
RMAN> show all;
RMAN configuration parameters for database with db_unique_name ORCL are:
CONFIGURE RETENTION POLICY TO RECOVERY WINDOW OF 1 DAYS;
CONFIGURE BACKUP OPTIMIZATION ON;
CONFIGURE DEFAULT DEVICE TYPE TO 'SBT_TAPE';
CONFIGURE CONTROLFILE AUTOBACKUP ON;
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '%F'; # default
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE SBT_TAPE TO '%F'; # default
CONFIGURE DEVICE TYPE DISK PARALLELISM 8 BACKUP TYPE TO BACKUPSET;
CONFIGURE DEVICE TYPE 'SBT_TAPE' PARALLELISM 2 BACKUP TYPE TO BACKUPSET;
CONFIGURE DATAFILE BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
CONFIGURE DATAFILE BACKUP COPIES FOR DEVICE TYPE SBT_TAPE TO 1; # default
CONFIGURE ARCHIVELOG BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
CONFIGURE ARCHIVELOG BACKUP COPIES FOR DEVICE TYPE SBT_TAPE TO 1; # default
CONFIGURE CHANNEL 1 DEVICE TYPE 'SBT_TAPE' PARMS 'ENV=(OB_MEDIA_FAMILY=station80)';
CONFIGURE CHANNEL 2 DEVICE TYPE 'SBT_TAPE' PARMS 'ENV=(OB_MEDIA_FAMILY=station80)';
CONFIGURE MAXSETSIZE TO UNLIMITED; # default
CONFIGURE ENCRYPTION FOR DATABASE OFF; # default
CONFIGURE ENCRYPTION ALGORITHM 'AES128'; # default
CONFIGURE COMPRESSION ALGORITHM 'BASIC' AS OF RELEASE 'DEFAULT' OPTIMIZE FOR LOAD TRUE ; # default
CONFIGURE ARCHIVELOG DELETION POLICY TO NONE; # default
CONFIGURE SNAPSHOT CONTROLFILE NAME TO '/u01/app/oracle/product/11.2.0/dbhome_1/dbs/snapcf_orcl.f'; # default
RMAN>

复制代码

[root@station80 mhvtl]# ls
CLN101L4 E01004L4 E01010L4 E01016L4 F01031L5 F01037L5 G03004TA G03010TA G03016TA G03022TA G03028TA G03034TA
CLN102L5 E01005L4 E01011L4 E01017L4 F01032L5 F01038L5 G03005TA G03011TA G03017TA G03023TA G03029TA G03035TA
CLN303TA E01006L4 E01012L4 E01018L4 F01033L5 F01039L5 G03006TA G03012TA G03018TA G03024TA G03030TA G03036TA
E01001L4 E01007L4 E01013L4 E01019L4 F01034L5 G03001TA G03007TA G03013TA G03019TA G03025TA G03031TA G03037TA
E01002L4 E01008L4 E01014L4 E01020L4 F01035L5 G03002TA G03008TA G03014TA G03020TA G03026TA G03032TA G03038TA
E01003L4 E01009L4 E01015L4 F01030L5 F01036L5 G03003TA G03009TA G03015TA G03021TA G03027TA G03033TA G03039TA
[root@station80 mhvtl]# cd /stage/
[root@station80 stage]# ls
client lzo-devel-2.02-2.el5.1.i386.rpm osb.txt service_oraclesetup2.0
copytape-root.sh mhvtl-1.2 OSPATCHS sqlplus_extentsetup3.0
database oracleasmlib-2.0.4-1.el5.i386.rpm qscintilla-1.7.1-1.fc6.i386.rpm stardict-2.4.4-1.i386.rpm
grid osb-10.3.0.3.0_linux32 reuseosb.sh tora-1.3.23-1.el5.i386.rpm
lzo-2.02-2.el5.1.i386.rpm osb.sh revertape-root.sh
[root@station80 stage]# vim osb.txt
[root@station80 stage]# obtool
ob> lsmf
OSB-CATALOG-MF write 7 days keep 14 days
RMAN-DEFAULT content manages reuse
sexample content manages reuse
station80 content manages reuse
ob> exit
[root@station80 stage]# su - oracle
[oracle@station80 ~]$ rman target /
Recovery Manager: Release 11.2.0.1.0 - Production on Mon Nov 26 04:52:58 2018
Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.
connected to target database: ORCL (DBID=1359978017)
RMAN> show all;
using target database control file instead of recovery catalog
RMAN configuration parameters for database with db_unique_name ORCL are:
CONFIGURE RETENTION POLICY TO RECOVERY WINDOW OF 1 DAYS;
CONFIGURE BACKUP OPTIMIZATION ON;
CONFIGURE DEFAULT DEVICE TYPE TO 'SBT_TAPE';
CONFIGURE CONTROLFILE AUTOBACKUP ON;
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '%F'; # default
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE SBT_TAPE TO '%F'; # default
CONFIGURE DEVICE TYPE DISK PARALLELISM 8 BACKUP TYPE TO BACKUPSET;
CONFIGURE DEVICE TYPE 'SBT_TAPE' PARALLELISM 2 BACKUP TYPE TO BACKUPSET;
CONFIGURE DATAFILE BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
CONFIGURE DATAFILE BACKUP COPIES FOR DEVICE TYPE SBT_TAPE TO 1; # default
CONFIGURE ARCHIVELOG BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
CONFIGURE ARCHIVELOG BACKUP COPIES FOR DEVICE TYPE SBT_TAPE TO 1; # default
CONFIGURE CHANNEL 1 DEVICE TYPE 'SBT_TAPE' PARMS 'ENV=(OB_MEDIA_FAMILY=station80)';
CONFIGURE CHANNEL 2 DEVICE TYPE 'SBT_TAPE' PARMS 'ENV=(OB_MEDIA_FAMILY=station80)';
CONFIGURE MAXSETSIZE TO UNLIMITED; # default
CONFIGURE ENCRYPTION FOR DATABASE OFF; # default
CONFIGURE ENCRYPTION ALGORITHM 'AES128'; # default
CONFIGURE COMPRESSION ALGORITHM 'BASIC' AS OF RELEASE 'DEFAULT' OPTIMIZE FOR LOAD TRUE ; # default
CONFIGURE ARCHIVELOG DELETION POLICY TO NONE; # default
CONFIGURE SNAPSHOT CONTROLFILE NAME TO '/u01/app/oracle/product/11.2.0/dbhome_1/dbs/snapcf_orcl.f'; # default
RMAN> delete backup;
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=73 device type=DISK
allocated channel: ORA_DISK_2
channel ORA_DISK_2: SID=10 device type=DISK
allocated channel: ORA_DISK_3
channel ORA_DISK_3: SID=144 device type=DISK
allocated channel: ORA_DISK_4
channel ORA_DISK_4: SID=201 device type=DISK
allocated channel: ORA_DISK_5
channel ORA_DISK_5: SID=8 device type=DISK
allocated channel: ORA_DISK_6
channel ORA_DISK_6: SID=80 device type=DISK
allocated channel: ORA_DISK_7
channel ORA_DISK_7: SID=136 device type=DISK
allocated channel: ORA_DISK_8
channel ORA_DISK_8: SID=202 device type=DISK
List of Backup Pieces
BP Key BS Key Pc# Cp# Status Device Type Piece Name
------- ------- --- --- ----------- ----------- ----------
9 9 1 1 AVAILABLE DISK +FRA/orcl/backupset/2018_11_26/nnndf0_tag20181126t032513_0.290.993180335
10 10 1 1 AVAILABLE DISK +FRA/orcl/backupset/2018_11_26/nnndf0_tag20181126t032513_0.291.993180359
11 11 1 1 AVAILABLE DISK +FRA/orcl/backupset/2018_11_26/nnndf0_tag20181126t032513_0.292.993180393
12 12 1 1 AVAILABLE DISK +FRA/orcl/backupset/2018_11_26/nnndf0_tag20181126t032513_0.289.993180325
13 13 1 1 AVAILABLE DISK +FRA/orcl/backupset/2018_11_26/nnndf0_tag20181126t032513_0.288.993180317
14 14 1 1 AVAILABLE DISK +FRA/orcl/autobackup/2018_11_26/s_993180492.293.993180493
Do you really want to delete the above objects (enter YES or NO)? YES
deleted backup piece
backup piece handle=+FRA/orcl/backupset/2018_11_26/nnndf0_tag20181126t032513_0.290.993180335 RECID=9 STAMP=993180332
deleted backup piece
backup piece handle=+FRA/orcl/backupset/2018_11_26/nnndf0_tag20181126t032513_0.291.993180359 RECID=10 STAMP=993180354
deleted backup piece
backup piece handle=+FRA/orcl/backupset/2018_11_26/nnndf0_tag20181126t032513_0.292.993180393 RECID=11 STAMP=993180382
deleted backup piece
backup piece handle=+FRA/orcl/backupset/2018_11_26/nnndf0_tag20181126t032513_0.289.993180325 RECID=12 STAMP=993180322
deleted backup piece
backup piece handle=+FRA/orcl/backupset/2018_11_26/nnndf0_tag20181126t032513_0.288.993180317 RECID=13 STAMP=993180316
deleted backup piece
backup piece handle=+FRA/orcl/autobackup/2018_11_26/s_993180492.293.993180493 RECID=14 STAMP=993180493
Deleted 6 objects
RMAN> list copy;
specification does not match any datafile copy in the repository
specification does not match any control file copy in the repository
List of Archived Log Copies for database with db_unique_name ORCL
=====================================================================
Key Thrd Seq S Low Time
------- ---- ------- - ---------
1 1 6 A 22-NOV-13
Name: +FRA/orcl/archivelog/2013_11_22/thread_1_seq_6.260.832198939
2 1 7 A 22-NOV-13
Name: +FRA/orcl/archivelog/2013_11_22/thread_1_seq_7.267.832199113
3 1 8 A 22-NOV-13
Name: +FRA/orcl/archivelog/2018_11_26/thread_1_seq_8.270.993169353
4 1 9 A 26-NOV-18
Name: +FRA/orcl/archivelog/2018_11_26/thread_1_seq_9.271.993169399
5 1 10 A 26-NOV-18
Name: +FRA/orcl/archivelog/2018_11_26/thread_1_seq_10.272.993169437
6 1 11 A 26-NOV-18
Name: +FRA/orcl/archivelog/2018_11_26/thread_1_seq_11.273.993169487
7 1 12 A 26-NOV-18
Name: +FRA/orcl/archivelog/2018_11_26/thread_1_seq_12.274.993169533
8 1 13 A 26-NOV-18
Name: +FRA/orcl/archivelog/2018_11_26/thread_1_seq_13.275.993169593
9 1 14 A 26-NOV-18
Name: +FRA/orcl/archivelog/2018_11_26/thread_1_seq_14.276.993169655
10 1 15 A 26-NOV-18
Name: +FRA/orcl/archivelog/2018_11_26/thread_1_seq_15.277.993169705
11 1 16 A 26-NOV-18
Name: +FRA/orcl/archivelog/2018_11_26/thread_1_seq_16.278.993169753
12 1 17 A 26-NOV-18
Name: +FRA/orcl/archivelog/2018_11_26/thread_1_seq_17.279.993169805
13 1 18 A 26-NOV-18
Name: +FRA/orcl/archivelog/2018_11_26/thread_1_seq_18.280.993169867
14 1 19 A 26-NOV-18
Name: +FRA/orcl/archivelog/2018_11_26/thread_1_seq_19.281.993169933
15 1 20 A 26-NOV-18
Name: +FRA/orcl/archivelog/2018_11_26/thread_1_seq_20.282.993169981
16 1 21 A 26-NOV-18
Name: +FRA/orcl/archivelog/2018_11_26/thread_1_seq_21.283.993170155
17 1 22 A 26-NOV-18
Name: +FRA/orcl/archivelog/2018_11_26/thread_1_seq_22.284.993170401
18 1 23 A 26-NOV-18
Name: +FRA/orcl/archivelog/2018_11_26/thread_1_seq_23.285.993173819
19 1 24 A 26-NOV-18
Name: +FRA/orcl/archivelog/2018_11_26/thread_1_seq_24.269.993176269
20 1 25 A 26-NOV-18
Name: +FRA/orcl/archivelog/2018_11_26/thread_1_seq_25.268.993176731
21 1 26 A 26-NOV-18
Name: +FRA/orcl/archivelog/2018_11_26/thread_1_seq_26.262.993176731
22 1 27 A 26-NOV-18
Name: +FRA/orcl/archivelog/2018_11_26/thread_1_seq_27.263.993176735
23 1 28 A 26-NOV-18
Name: +FRA/orcl/archivelog/2018_11_26/thread_1_seq_28.266.993176741
24 1 29 A 26-NOV-18
Name: +FRA/orcl/archivelog/2018_11_26/thread_1_seq_29.265.993176743
25 1 30 A 26-NOV-18
Name: +FRA/orcl/archivelog/2018_11_26/thread_1_seq_30.264.993176747
26 1 31 A 26-NOV-18
Name: +FRA/orcl/archivelog/2018_11_26/thread_1_seq_31.261.993176749
27 1 32 A 26-NOV-18
Name: +FRA/orcl/archivelog/2018_11_26/thread_1_seq_32.287.993178433
28 1 33 A 26-NOV-18
Name: +FRA/orcl/archivelog/2018_11_26/thread_1_seq_33.286.993178495
RMAN> delete copy;
released channel: ORA_DISK_1
released channel: ORA_DISK_2
released channel: ORA_DISK_3
released channel: ORA_DISK_4
released channel: ORA_DISK_5
released channel: ORA_DISK_6
released channel: ORA_DISK_7
released channel: ORA_DISK_8
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=73 device type=DISK
allocated channel: ORA_DISK_2
channel ORA_DISK_2: SID=10 device type=DISK
allocated channel: ORA_DISK_3
channel ORA_DISK_3: SID=144 device type=DISK
allocated channel: ORA_DISK_4
channel ORA_DISK_4: SID=201 device type=DISK
allocated channel: ORA_DISK_5
channel ORA_DISK_5: SID=8 device type=DISK
allocated channel: ORA_DISK_6
channel ORA_DISK_6: SID=80 device type=DISK
allocated channel: ORA_DISK_7
channel ORA_DISK_7: SID=136 device type=DISK
allocated channel: ORA_DISK_8
channel ORA_DISK_8: SID=202 device type=DISK
specification does not match any datafile copy in the repository
specification does not match any control file copy in the repository
List of Archived Log Copies for database with db_unique_name ORCL
=====================================================================
Key Thrd Seq S Low Time
------- ---- ------- - ---------
1 1 6 A 22-NOV-13
Name: +FRA/orcl/archivelog/2013_11_22/thread_1_seq_6.260.832198939
2 1 7 A 22-NOV-13
Name: +FRA/orcl/archivelog/2013_11_22/thread_1_seq_7.267.832199113
3 1 8 A 22-NOV-13
Name: +FRA/orcl/archivelog/2018_11_26/thread_1_seq_8.270.993169353
4 1 9 A 26-NOV-18
Name: +FRA/orcl/archivelog/2018_11_26/thread_1_seq_9.271.993169399
5 1 10 A 26-NOV-18
Name: +FRA/orcl/archivelog/2018_11_26/thread_1_seq_10.272.993169437
6 1 11 A 26-NOV-18
Name: +FRA/orcl/archivelog/2018_11_26/thread_1_seq_11.273.993169487
7 1 12 A 26-NOV-18
Name: +FRA/orcl/archivelog/2018_11_26/thread_1_seq_12.274.993169533
8 1 13 A 26-NOV-18
Name: +FRA/orcl/archivelog/2018_11_26/thread_1_seq_13.275.993169593
9 1 14 A 26-NOV-18
Name: +FRA/orcl/archivelog/2018_11_26/thread_1_seq_14.276.993169655
10 1 15 A 26-NOV-18
Name: +FRA/orcl/archivelog/2018_11_26/thread_1_seq_15.277.993169705
11 1 16 A 26-NOV-18
Name: +FRA/orcl/archivelog/2018_11_26/thread_1_seq_16.278.993169753
12 1 17 A 26-NOV-18
Name: +FRA/orcl/archivelog/2018_11_26/thread_1_seq_17.279.993169805
13 1 18 A 26-NOV-18
Name: +FRA/orcl/archivelog/2018_11_26/thread_1_seq_18.280.993169867
14 1 19 A 26-NOV-18
Name: +FRA/orcl/archivelog/2018_11_26/thread_1_seq_19.281.993169933
15 1 20 A 26-NOV-18
Name: +FRA/orcl/archivelog/2018_11_26/thread_1_seq_20.282.993169981
16 1 21 A 26-NOV-18
Name: +FRA/orcl/archivelog/2018_11_26/thread_1_seq_21.283.993170155
17 1 22 A 26-NOV-18
Name: +FRA/orcl/archivelog/2018_11_26/thread_1_seq_22.284.993170401
18 1 23 A 26-NOV-18
Name: +FRA/orcl/archivelog/2018_11_26/thread_1_seq_23.285.993173819
19 1 24 A 26-NOV-18
Name: +FRA/orcl/archivelog/2018_11_26/thread_1_seq_24.269.993176269
20 1 25 A 26-NOV-18
Name: +FRA/orcl/archivelog/2018_11_26/thread_1_seq_25.268.993176731
21 1 26 A 26-NOV-18
Name: +FRA/orcl/archivelog/2018_11_26/thread_1_seq_26.262.993176731
22 1 27 A 26-NOV-18
Name: +FRA/orcl/archivelog/2018_11_26/thread_1_seq_27.263.993176735
23 1 28 A 26-NOV-18
Name: +FRA/orcl/archivelog/2018_11_26/thread_1_seq_28.266.993176741
24 1 29 A 26-NOV-18
Name: +FRA/orcl/archivelog/2018_11_26/thread_1_seq_29.265.993176743
25 1 30 A 26-NOV-18
Name: +FRA/orcl/archivelog/2018_11_26/thread_1_seq_30.264.993176747
26 1 31 A 26-NOV-18
Name: +FRA/orcl/archivelog/2018_11_26/thread_1_seq_31.261.993176749
27 1 32 A 26-NOV-18
Name: +FRA/orcl/archivelog/2018_11_26/thread_1_seq_32.287.993178433
28 1 33 A 26-NOV-18
Name: +FRA/orcl/archivelog/2018_11_26/thread_1_seq_33.286.993178495
Do you really want to delete the above objects (enter YES or NO)? YES
deleted archived log
archived log file name=+FRA/orcl/archivelog/2013_11_22/thread_1_seq_6.260.832198939 RECID=1 STAMP=832198941
deleted archived log
archived log file name=+FRA/orcl/archivelog/2013_11_22/thread_1_seq_7.267.832199113 RECID=2 STAMP=832199113
deleted archived log
archived log file name=+FRA/orcl/archivelog/2018_11_26/thread_1_seq_8.270.993169353 RECID=3 STAMP=993169358
deleted archived log
archived log file name=+FRA/orcl/archivelog/2018_11_26/thread_1_seq_9.271.993169399 RECID=4 STAMP=993169405
deleted archived log
archived log file name=+FRA/orcl/archivelog/2018_11_26/thread_1_seq_10.272.993169437 RECID=5 STAMP=993169442
deleted archived log
archived log file name=+FRA/orcl/archivelog/2018_11_26/thread_1_seq_11.273.993169487 RECID=6 STAMP=993169493
deleted archived log
archived log file name=+FRA/orcl/archivelog/2018_11_26/thread_1_seq_12.274.993169533 RECID=7 STAMP=993169539
deleted archived log
archived log file name=+FRA/orcl/archivelog/2018_11_26/thread_1_seq_13.275.993169593 RECID=8 STAMP=993169599
deleted archived log
archived log file name=+FRA/orcl/archivelog/2018_11_26/thread_1_seq_14.276.993169655 RECID=9 STAMP=993169661
deleted archived log
archived log file name=+FRA/orcl/archivelog/2018_11_26/thread_1_seq_15.277.993169705 RECID=10 STAMP=993169710
deleted archived log
archived log file name=+FRA/orcl/archivelog/2018_11_26/thread_1_seq_16.278.993169753 RECID=11 STAMP=993169759
deleted archived log
archived log file name=+FRA/orcl/archivelog/2018_11_26/thread_1_seq_17.279.993169805 RECID=12 STAMP=993169812
deleted archived log
archived log file name=+FRA/orcl/archivelog/2018_11_26/thread_1_seq_18.280.993169867 RECID=13 STAMP=993169872
deleted archived log
archived log file name=+FRA/orcl/archivelog/2018_11_26/thread_1_seq_19.281.993169933 RECID=14 STAMP=993169943
deleted archived log
archived log file name=+FRA/orcl/archivelog/2018_11_26/thread_1_seq_20.282.993169981 RECID=15 STAMP=993169988
deleted archived log
archived log file name=+FRA/orcl/archivelog/2018_11_26/thread_1_seq_21.283.993170155 RECID=16 STAMP=993170161
deleted archived log
archived log file name=+FRA/orcl/archivelog/2018_11_26/thread_1_seq_22.284.993170401 RECID=17 STAMP=993170406
deleted archived log
archived log file name=+FRA/orcl/archivelog/2018_11_26/thread_1_seq_23.285.993173819 RECID=18 STAMP=993173823
deleted archived log
archived log file name=+FRA/orcl/archivelog/2018_11_26/thread_1_seq_24.269.993176269 RECID=19 STAMP=993176272
deleted archived log
archived log file name=+FRA/orcl/archivelog/2018_11_26/thread_1_seq_25.268.993176731 RECID=20 STAMP=993176730
deleted archived log
archived log file name=+FRA/orcl/archivelog/2018_11_26/thread_1_seq_26.262.993176731 RECID=21 STAMP=993176731
deleted archived log
archived log file name=+FRA/orcl/archivelog/2018_11_26/thread_1_seq_27.263.993176735 RECID=22 STAMP=993176735
deleted archived log
archived log file name=+FRA/orcl/archivelog/2018_11_26/thread_1_seq_28.266.993176741 RECID=23 STAMP=993176741
deleted archived log
archived log file name=+FRA/orcl/archivelog/2018_11_26/thread_1_seq_29.265.993176743 RECID=24 STAMP=993176742
deleted archived log
archived log file name=+FRA/orcl/archivelog/2018_11_26/thread_1_seq_30.264.993176747 RECID=25 STAMP=993176746
deleted archived log
archived log file name=+FRA/orcl/archivelog/2018_11_26/thread_1_seq_31.261.993176749 RECID=26 STAMP=993176750
deleted archived log
archived log file name=+FRA/orcl/archivelog/2018_11_26/thread_1_seq_32.287.993178433 RECID=27 STAMP=993178439
deleted archived log
archived log file name=+FRA/orcl/archivelog/2018_11_26/thread_1_seq_33.286.993178495 RECID=28 STAMP=993178502
Deleted 28 objects
RMAN> backup tag '1T_WHOLE_INCR0' incremental level 0 database plus archivelog delete all input;
Starting backup at 26-NOV-18
current log archived
released channel: ORA_DISK_1
released channel: ORA_DISK_2
released channel: ORA_DISK_3
released channel: ORA_DISK_4
released channel: ORA_DISK_5
released channel: ORA_DISK_6
released channel: ORA_DISK_7
released channel: ORA_DISK_8
allocated channel: ORA_SBT_TAPE_1
channel ORA_SBT_TAPE_1: SID=73 device type=SBT_TAPE
channel ORA_SBT_TAPE_1: Oracle Secure Backup
allocated channel: ORA_SBT_TAPE_2
channel ORA_SBT_TAPE_2: SID=10 device type=SBT_TAPE
channel ORA_SBT_TAPE_2: Oracle Secure Backup
channel ORA_SBT_TAPE_1: starting archived log backup set
channel ORA_SBT_TAPE_1: specifying archived log(s) in backup set
input archived log thread=1 sequence=34 RECID=29 STAMP=993186538
channel ORA_SBT_TAPE_1: starting piece 1 at 26-NOV-18
channel ORA_SBT_TAPE_1: finished piece 1 at 26-NOV-18
piece handle=0jtj5knc_1_1 tag=1T_WHOLE_INCR0 comment=API Version 2.0,MMS Version 10.3.0.2
channel ORA_SBT_TAPE_1: backup set complete, elapsed time: 00:00:35
channel ORA_SBT_TAPE_1: deleting archived log(s)
archived log file name=+FRA/orcl/archivelog/2018_11_26/thread_1_seq_34.286.993186533 RECID=29 STAMP=993186538
Finished backup at 26-NOV-18
Starting backup at 26-NOV-18
using channel ORA_SBT_TAPE_1
using channel ORA_SBT_TAPE_2
channel ORA_SBT_TAPE_1: starting incremental level 0 datafile backup set
channel ORA_SBT_TAPE_1: specifying datafile(s) in backup set
input datafile file number=00001 name=+DATA/orcl/datafile/system.256.832197063
input datafile file number=00004 name=+DATA/orcl/datafile/users.259.832197065
input datafile file number=00005 name=+DATA/orcl/datafile/example.265.832197353
channel ORA_SBT_TAPE_1: starting piece 1 at 26-NOV-18
channel ORA_SBT_TAPE_2: starting incremental level 0 datafile backup set
channel ORA_SBT_TAPE_2: specifying datafile(s) in backup set
input datafile file number=00002 name=+DATA/orcl/datafile/sysaux.257.832197065
input datafile file number=00003 name=+DATA/orcl/datafile/undotbs1.258.832197065
channel ORA_SBT_TAPE_2: starting piece 1 at 26-NOV-18
channel ORA_SBT_TAPE_2: finished piece 1 at 26-NOV-18
piece handle=0ltj5kog_1_1 tag=1T_WHOLE_INCR0 comment=API Version 2.0,MMS Version 10.3.0.2
channel ORA_SBT_TAPE_2: backup set complete, elapsed time: 00:02:06
channel ORA_SBT_TAPE_1: finished piece 1 at 26-NOV-18
piece handle=0ktj5kog_1_1 tag=1T_WHOLE_INCR0 comment=API Version 2.0,MMS Version 10.3.0.2
channel ORA_SBT_TAPE_1: backup set complete, elapsed time: 00:02:46
Finished backup at 26-NOV-18
Starting backup at 26-NOV-18
current log archived
using channel ORA_SBT_TAPE_1
using channel ORA_SBT_TAPE_2
channel ORA_SBT_TAPE_1: starting archived log backup set
channel ORA_SBT_TAPE_1: specifying archived log(s) in backup set
input archived log thread=1 sequence=35 RECID=30 STAMP=993186742
channel ORA_SBT_TAPE_1: starting piece 1 at 26-NOV-18
channel ORA_SBT_TAPE_1: finished piece 1 at 26-NOV-18
piece handle=0mtj5ktn_1_1 tag=1T_WHOLE_INCR0 comment=API Version 2.0,MMS Version 10.3.0.2
channel ORA_SBT_TAPE_1: backup set complete, elapsed time: 00:00:25
channel ORA_SBT_TAPE_1: deleting archived log(s)
archived log file name=+FRA/orcl/archivelog/2018_11_26/thread_1_seq_35.286.993186743 RECID=30 STAMP=993186742
Finished backup at 26-NOV-18
Starting Control File and SPFILE Autobackup at 26-NOV-18
piece handle=c-1359978017-20181126-01 comment=API Version 2.0,MMS Version 10.3.0.2
Finished Control File and SPFILE Autobackup at 26-NOV-18
RMAN> list backup;
List of Backup Sets
===================
BS Key Size Device Type Elapsed Time Completion Time
------- ---------- ----------- ------------ ---------------
15 38.25M SBT_TAPE 00:00:32 26-NOV-18
BP Key: 15 Status: AVAILABLE Compressed: NO Tag: 1T_WHOLE_INCR0
Handle: 0jtj5knc_1_1 Media: station80-000001
List of Archived Logs in backup set 15
Thrd Seq Low SCN Low Time Next SCN Next Time
---- ------- ---------- --------- ---------- ---------
1 34 1050037 26-NOV-18 1071412 26-NOV-18
BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
16 Incr 0 401.13M SBT_TAPE 00:02:00 26-NOV-18
BP Key: 16 Status: AVAILABLE Compressed: NO Tag: 1T_WHOLE_INCR0
Handle: 0ltj5kog_1_1 Media: station80-000002
List of Datafiles in backup set 16
File LV Type Ckp SCN Ckp Time Name
---- -- ---- ---------- --------- ----
2 0 Incr 1071446 26-NOV-18 +DATA/orcl/datafile/sysaux.257.832197065
3 0 Incr 1071446 26-NOV-18 +DATA/orcl/datafile/undotbs1.258.832197065
BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
17 Incr 0 666.81M SBT_TAPE 00:02:43 26-NOV-18
BP Key: 17 Status: AVAILABLE Compressed: NO Tag: 1T_WHOLE_INCR0
Handle: 0ktj5kog_1_1 Media: station80-000001
List of Datafiles in backup set 17
File LV Type Ckp SCN Ckp Time Name
---- -- ---- ---------- --------- ----
1 0 Incr 1071444 26-NOV-18 +DATA/orcl/datafile/system.256.832197063
4 0 Incr 1071444 26-NOV-18 +DATA/orcl/datafile/users.259.832197065
5 0 Incr 1071444 26-NOV-18 +DATA/orcl/datafile/example.265.832197353
BS Key Size Device Type Elapsed Time Completion Time
------- ---------- ----------- ------------ ---------------
18 128.00K SBT_TAPE 00:00:20 26-NOV-18
BP Key: 18 Status: AVAILABLE Compressed: NO Tag: 1T_WHOLE_INCR0
Handle: 0mtj5ktn_1_1 Media: station80-000001
List of Archived Logs in backup set 18
Thrd Seq Low SCN Low Time Next SCN Next Time
---- ------- ---------- --------- ---------- ---------
1 35 1071412 26-NOV-18 1071546 26-NOV-18
BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
19 Full 9.38M SBT_TAPE 00:00:23 26-NOV-18
BP Key: 19 Status: AVAILABLE Compressed: NO Tag: TAG20181126T051248
Handle: c-1359978017-20181126-01 Media: station80-000001
SPFILE Included: Modification time: 26-NOV-18
SPFILE db_unique_name: ORCL
Control File Included: Ckp SCN: 1071567 Ckp time: 26-NOV-18
RMAN>

复制代码

[root@station80 mhvtl]# du -sh *
8.0K CLN101L4
8.0K CLN102L5
8.0K CLN303TA
8.0K E01001L4
8.0K E01002L4
8.0K E01003L4
8.0K E01004L4
8.0K E01005L4
8.0K E01006L4
8.0K E01007L4
8.0K E01008L4
8.0K E01009L4
8.0K E01010L4
8.0K E01011L4
8.0K E01012L4
8.0K E01013L4
8.0K E01014L4
8.0K E01015L4
8.0K E01016L4
8.0K E01017L4
8.0K E01018L4
8.0K E01019L4
8.0K E01020L4
8.0K F01030L5
8.0K F01031L5
8.0K F01032L5
8.0K F01033L5
8.0K F01034L5
8.0K F01035L5
8.0K F01036L5
8.0K F01037L5
8.0K F01038L5
8.0K F01039L5
8.0K G03001TA
8.0K G03002TA
8.0K G03003TA
8.0K G03004TA
8.0K G03005TA
8.0K G03006TA
8.0K G03007TA
8.0K G03008TA
8.0K G03009TA
8.0K G03010TA
8.0K G03011TA
8.0K G03012TA
8.0K G03013TA
8.0K G03014TA
8.0K G03015TA
8.0K G03016TA
8.0K G03017TA
8.0K G03018TA
8.0K G03019TA
8.0K G03020TA
8.0K G03021TA
8.0K G03022TA
8.0K G03023TA
8.0K G03024TA
8.0K G03025TA
8.0K G03026TA
8.0K G03027TA
8.0K G03028TA
8.0K G03029TA
8.0K G03030TA
8.0K G03031TA
8.0K G03032TA
8.0K G03033TA
8.0K G03034TA
8.0K G03035TA
8.0K G03036TA
8.0K G03037TA
8.0K G03038TA
8.0K G03039TA
[root@station80 mhvtl]# obtool
Oracle Secure Backup 10.3.0.3.0
Warning: auto-login failed - login token has expired
login: admin
Password: ******
ob> lsvol -L lib01 -l
Inventory of library lib01:
in 1: vacant
in 2: vacant
in 3: barcode E01003L4, oid 102
in 4: barcode E01004L4, oid 103
in 5: barcode E01005L4, oid 104
in 6: barcode E01006L4, oid 105
in 7: barcode E01007L4, oid 106
in 8: barcode E01008L4, oid 107
in 9: barcode E01009L4, oid 108
in 10: barcode E01010L4, oid 109
in 11: barcode E01011L4, oid 110
in 12: barcode E01012L4, oid 111
in 13: barcode E01013L4, oid 112
in 14: barcode E01014L4, oid 113
in 15: barcode E01015L4, oid 114
in 16: barcode E01016L4, oid 115
in 17: barcode E01017L4, oid 116
in 18: barcode E01018L4, oid 117
in 19: barcode E01019L4, oid 118
in 20: barcode E01020L4, oid 119
in 21: vacant
in 22: barcode CLN101L4, oid 120
in 23: barcode CLN102L5, oid 121
in 24: vacant
in 25: vacant
in 26: vacant
in 27: vacant
in 28: vacant
in 29: vacant
in 30: barcode F01030L5, oid 122
in 31: barcode F01031L5, oid 123
in 32: barcode F01032L5, oid 124
in 33: barcode F01033L5, oid 125
in 34: barcode F01034L5, oid 126
in 35: barcode F01035L5, oid 127
in 36: barcode F01036L5, oid 128
in 37: barcode F01037L5, oid 129
in 38: barcode F01038L5, oid 130
in 39: barcode F01039L5, oid 131
in iee1: vacant
in iee2: vacant
in iee3: vacant
in iee4: vacant
in dte1: vacant
in dte2: vacant
in dte3: volume station80-000002, barcode E01002L4, oid 135, 0 kb remaining, content manages reuse, lastse 2
in dte4: volume station80-000001, barcode E01001L4, oid 133, content manages reuse, lastse 1
ob> lsjob
ob> lsjob
Job ID Sched time Contents State
---------------- ----------- ------------------------------ ---------------------------------------
oracle/4 none database orcl (dbid=1359978017) processed; Oracle job(s) scheduled
oracle/4.1 none controlfile autobackup running since 2018/11/26.05:12
ob> lspiece
POID Database Content Copy Created Host Piece name
100 orcl archivelog 0 11/26.05:09 station80 0jtj5knc_1_1
101 orcl incremental 0 11/26.05:09 station80 0ltj5kog_1_1
102 orcl incremental 0 11/26.05:09 station80 0ktj5kog_1_1
103 orcl archivelog 0 11/26.05:12 station80 0mtj5ktn_1_1
104 orcl autobackup 0 11/26.05:12 station80 c-1359978017-20181126-01
ob> lsvol -L lib01 -l
Inventory of library lib01:
in 1: volume station80-000001, barcode E01001L4, oid 133, 0 kb remaining, content manages reuse
in 2: volume station80-000002, barcode E01002L4, oid 135, 0 kb remaining, content manages reuse
in 3: barcode E01003L4, oid 102
in 4: barcode E01004L4, oid 103
in 5: barcode E01005L4, oid 104
in 6: barcode E01006L4, oid 105
in 7: barcode E01007L4, oid 106
in 8: barcode E01008L4, oid 107
in 9: barcode E01009L4, oid 108
in 10: barcode E01010L4, oid 109
in 11: barcode E01011L4, oid 110
in 12: barcode E01012L4, oid 111
in 13: barcode E01013L4, oid 112
in 14: barcode E01014L4, oid 113
in 15: barcode E01015L4, oid 114
in 16: barcode E01016L4, oid 115
in 17: barcode E01017L4, oid 116
in 18: barcode E01018L4, oid 117
in 19: barcode E01019L4, oid 118
in 20: barcode E01020L4, oid 119
in 21: vacant
in 22: barcode CLN101L4, oid 120
in 23: barcode CLN102L5, oid 121
in 24: vacant
in 25: vacant
in 26: vacant
in 27: vacant
in 28: vacant
in 29: vacant
in 30: barcode F01030L5, oid 122
in 31: barcode F01031L5, oid 123
in 32: barcode F01032L5, oid 124
in 33: barcode F01033L5, oid 125
in 34: barcode F01034L5, oid 126
in 35: barcode F01035L5, oid 127
in 36: barcode F01036L5, oid 128
in 37: barcode F01037L5, oid 129
in 38: barcode F01038L5, oid 130
in 39: barcode F01039L5, oid 131
in iee1: vacant
in iee2: vacant
in iee3: vacant
in iee4: vacant
in dte1: vacant
in dte2: vacant
in dte3: vacant
in dte4: vacant
ob> exit
[root@station80 mhvtl]# du -sh *
8.0K CLN101L4
8.0K CLN102L5
8.0K CLN303TA
282M E01001L4
101M E01002L4
8.0K E01003L4
8.0K E01004L4
8.0K E01005L4
8.0K E01006L4
8.0K E01007L4
8.0K E01008L4
8.0K E01009L4
8.0K E01010L4
8.0K E01011L4
8.0K E01012L4
8.0K E01013L4
8.0K E01014L4
8.0K E01015L4
8.0K E01016L4
8.0K E01017L4
8.0K E01018L4
8.0K E01019L4
8.0K E01020L4
8.0K F01030L5
8.0K F01031L5
8.0K F01032L5
8.0K F01033L5
8.0K F01034L5
8.0K F01035L5
8.0K F01036L5
8.0K F01037L5
8.0K F01038L5
8.0K F01039L5
8.0K G03001TA
8.0K G03002TA
8.0K G03003TA
8.0K G03004TA
8.0K G03005TA
8.0K G03006TA
8.0K G03007TA
8.0K G03008TA
8.0K G03009TA
8.0K G03010TA
8.0K G03011TA
8.0K G03012TA
8.0K G03013TA
8.0K G03014TA
8.0K G03015TA
8.0K G03016TA
8.0K G03017TA
8.0K G03018TA
8.0K G03019TA
8.0K G03020TA
8.0K G03021TA
8.0K G03022TA
8.0K G03023TA
8.0K G03024TA
8.0K G03025TA
8.0K G03026TA
8.0K G03027TA
8.0K G03028TA
8.0K G03029TA
8.0K G03030TA
8.0K G03031TA
8.0K G03032TA
8.0K G03033TA
8.0K G03034TA
8.0K G03035TA
8.0K G03036TA
8.0K G03037TA
8.0K G03038TA
8.0K G03039TA
[root@station80 mhvtl]#

复制代码

课程第24次（2018-11-27星期二）

run {
allocate channel oem_disk_backup device type disk;
recover copy of database with tag 'ORA_OEM_LEVEL_0';
backup incremental level 1 cumulative copies=1 for recover of copy with tag 'ORA_OEM_LEVEL_0' database;
release channel oem_disk_backup;
allocate channel oem_sbt_backup1 type 'SBT_TAPE' format '%U' parms 'ENV=(OB_MEDIA_FAMILY=station90)';
allocate channel oem_sbt_backup2 type 'SBT_TAPE' format '%U' parms 'ENV=(OB_MEDIA_FAMILY=station90)';
backup archivelog all not backed up;
}
allocate channel for maintenance device type 'SBT_TAPE' parms 'ENV=(OB_MEDIA_FAMILY=station90)';
delete noprompt obsolete recovery window of 31 days device type 'SBT_TAPE';

复制代码

run {
allocate channel oem_disk_backup device type disk;
recover copy of database with tag 'ORA_OEM_LEVEL_0';
backup incremental level 1 cumulative copies=1 for recover of copy with tag 'ORA_OEM_LEVEL_0' database;
release channel oem_disk_backup;
allocate channel oem_sbt_backup1 type 'SBT_TAPE' format '%U' parms 'ENV=(OB_MEDIA_FAMILY=station90)';
allocate channel oem_sbt_backup2 type 'SBT_TAPE' format '%U' parms 'ENV=(OB_MEDIA_FAMILY=station90)';
backup recovery area;
}
allocate channel for maintenance device type 'SBT_TAPE' parms 'ENV=(OB_MEDIA_FAMILY=station90)';
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星期三）

对备份集再备份（转移备份集）：

RMAN> run {
2> allocate channel c1 device type disk format '/home/oracle/backup/users_%U';
3> backup tablespace users;
4> }
released channel: ORA_SBT_TAPE_1
released channel: ORA_SBT_TAPE_2
allocated channel: c1
channel c1: SID=137 device type=DISK
Starting backup at 2018-12-01:23:58:50
channel c1: starting full datafile backup set
channel c1: specifying datafile(s) in backup set
input datafile file number=00004 name=+DATA/orcl/datafile/users.259.992728183
channel c1: starting piece 1 at 2018-12-01:23:58:50
channel c1: finished piece 1 at 2018-12-01:23:58:51
piece handle=/home/oracle/backup/users_45tjnh5q_1_1 tag=TAG20181201T235850 comment=NONE
channel c1: backup set complete, elapsed time: 00:00:01
Finished backup at 2018-12-01:23:58:51
released channel: c1
RMAN> list backup of tablespace users;
List of Backup Sets
===================
BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ -------------------
106 Incr 0 669.75M SBT_TAPE 00:00:30 2018-11-28:19:25:55
BP Key: 107 Status: AVAILABLE Compressed: NO Tag: 1T_WHOLE_INCR0
Handle: 3mtjcfl5_1_1 Media: station90-000010
List of Datafiles in backup set 106
File LV Type Ckp SCN Ckp Time Name
---- -- ---- ---------- ------------------- ----
4 0 Incr 4674858 2018-11-28:19:25:25 +DATA/orcl/datafile/users.259.992728183
BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ -------------------
110 Incr 0 670.75M SBT_TAPE 00:00:30 2018-11-29:19:28:22
BP Key: 111 Status: AVAILABLE Compressed: NO Tag: 2T_WHOLE_INCR0
Handle: 3qtjf45o_1_1 Media: station90-000010
List of Datafiles in backup set 110
File LV Type Ckp SCN Ckp Time Name
---- -- ---- ---------- ------------------- ----
4 0 Incr 4678909 2018-11-29:19:27:53 +DATA/orcl/datafile/users.259.992728183
BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ -------------------
113 Incr 0 672.00M SBT_TAPE 00:00:23 2018-12-01:00:03:45
BP Key: 114 Status: AVAILABLE Compressed: NO Tag: 3T_WHOLE_INCR0
Handle: 3utjkt2a_1_1 Media: station90-000009
List of Datafiles in backup set 113
File LV Type Ckp SCN Ckp Time Name
---- -- ---- ---------- ------------------- ----
4 0 Incr 4730677 2018-12-01:00:02:41 +DATA/orcl/datafile/users.259.992728183
BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ -------------------
121 Full 8.22M DISK 00:00:00 2018-12-01:23:58:50
BP Key: 122 Status: AVAILABLE Compressed: NO Tag: TAG20181201T235850
Piece Name: /home/oracle/backup/users_45tjnh5q_1_1
List of Datafiles in backup set 121
File LV Type Ckp SCN Ckp Time Name
---- -- ---- ---------- ------------------- ----
4 Full 4730677 2018-12-01:00:02:41 +DATA/orcl/datafile/users.259.992728183
RMAN> backup backupset 121 force delete input;
Starting backup at 2018-12-02:00:00:19
allocated channel: ORA_SBT_TAPE_1
channel ORA_SBT_TAPE_1: SID=137 device type=SBT_TAPE
channel ORA_SBT_TAPE_1: Oracle Secure Backup
allocated channel: ORA_SBT_TAPE_2
channel ORA_SBT_TAPE_2: SID=24 device type=SBT_TAPE
channel ORA_SBT_TAPE_2: Oracle Secure Backup
channel ORA_SBT_TAPE_1: input backup set: count=133, stamp=993772730, piece=1
channel ORA_SBT_TAPE_1: starting piece 1 at 2018-12-02:00:00:32
channel ORA_SBT_TAPE_1: backup piece /home/oracle/backup/users_45tjnh5q_1_1
piece handle=45tjnh5q_1_2 comment=API Version 2.0,MMS Version 10.4.0.4
channel ORA_SBT_TAPE_1: finished piece 1 at 2018-12-02:00:00:57
channel ORA_SBT_TAPE_1: backup piece complete, elapsed time: 00:00:25
deleted backup piece
backup piece handle=/home/oracle/backup/users_45tjnh5q_1_1 RECID=122 STAMP=993772730
Finished backup at 2018-12-02:00:00:57
RMAN> list backup of users;
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-00558: error encountered while parsing input commands
RMAN-01009: syntax error: found "identifier": expecting one of: "archivelog, controlfile, database, datafile, foreign, spfile, tablespace"
RMAN-01008: the bad identifier was: users
RMAN-01007: at line 1 column 16 file: standard input
RMAN> list backup of tablespace users;
List of Backup Sets
===================
BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ -------------------
106 Incr 0 669.75M SBT_TAPE 00:00:30 2018-11-28:19:25:55
BP Key: 107 Status: AVAILABLE Compressed: NO Tag: 1T_WHOLE_INCR0
Handle: 3mtjcfl5_1_1 Media: station90-000010
List of Datafiles in backup set 106
File LV Type Ckp SCN Ckp Time Name
---- -- ---- ---------- ------------------- ----
4 0 Incr 4674858 2018-11-28:19:25:25 +DATA/orcl/datafile/users.259.992728183
BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ -------------------
110 Incr 0 670.75M SBT_TAPE 00:00:30 2018-11-29:19:28:22
BP Key: 111 Status: AVAILABLE Compressed: NO Tag: 2T_WHOLE_INCR0
Handle: 3qtjf45o_1_1 Media: station90-000010
List of Datafiles in backup set 110
File LV Type Ckp SCN Ckp Time Name
---- -- ---- ---------- ------------------- ----
4 0 Incr 4678909 2018-11-29:19:27:53 +DATA/orcl/datafile/users.259.992728183
BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ -------------------
113 Incr 0 672.00M SBT_TAPE 00:00:23 2018-12-01:00:03:45
BP Key: 114 Status: AVAILABLE Compressed: NO Tag: 3T_WHOLE_INCR0
Handle: 3utjkt2a_1_1 Media: station90-000009
List of Datafiles in backup set 113
File LV Type Ckp SCN Ckp Time Name
---- -- ---- ---------- ------------------- ----
4 0 Incr 4730677 2018-12-01:00:02:41 +DATA/orcl/datafile/users.259.992728183
BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ -------------------
121 Full 8.22M SBT_TAPE 00:00:00 2018-12-01:23:58:50
BP Key: 123 Status: AVAILABLE Compressed: NO Tag: TAG20181201T235850
Handle: 45tjnh5q_1_2 Media: station90-000009
List of Datafiles in backup set 121
File LV Type Ckp SCN Ckp Time Name
---- -- ---- ---------- ------------------- ----
4 Full 4730677 2018-12-01:00:02:41 +DATA/orcl/datafile/users.259.992728183
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）

[oracle@station90 orcl]$ mkdir wallet
[oracle@station90 orcl]$ pwd
/u01/app/oracle/admin/orcl

复制代码

SQL> conn / as sysdba
Connected.
SQL> select * from v$encryption_wallet;
WRL_TYPE
--------------------
WRL_PARAMETER
--------------------------------------------------------------------------------
STATUS
------------------
file
/u01/app/oracle/admin/orcl/wallet
CLOSED
SQL> alter system set encryption key identified by "oracle123";
System altered.

复制代码

[oracle@station90 wallet]$ ls
ewallet.p12

复制代码

SQL> alter system set encryption key identified by "oracle123";
System altered.
SQL> select * from v$encryption_wallet;
WRL_TYPE
--------------------
WRL_PARAMETER
--------------------------------------------------------------------------------
STATUS
------------------
file
/u01/app/oracle/admin/orcl/wallet
OPEN
SQL> alter system set encryption wallet close identified by "oracle1234";
alter system set encryption wallet close identified by "oracle1234"
*
ERROR at line 1:
ORA-28391: cannot close wallet or HSM, password mismatch
SQL> alter system set encryption wallet close identified by "oracle123";
System altered.
SQL> select * from v$encryption_wallet;
WRL_TYPE
--------------------
WRL_PARAMETER
--------------------------------------------------------------------------------
STATUS
------------------
file
/u01/app/oracle/admin/orcl/wallet
CLOSED
SQL> alter system set encryption wallet open identified by "oracle123";
System altered.
SQL> select * from v$encryption_wallet;
WRL_TYPE
--------------------
WRL_PARAMETER
--------------------------------------------------------------------------------
STATUS
------------------
file
/u01/app/oracle/admin/orcl/wallet
OPEN
SQL>

复制代码

课程第26次（2018-11-29星期四）

RMAN> backup validate database;
Starting backup at 2018-12-02:23:16:43
using target database control file instead of recovery catalog
allocated channel: ORA_SBT_TAPE_1
channel ORA_SBT_TAPE_1: SID=71 device type=SBT_TAPE
channel ORA_SBT_TAPE_1: Oracle Secure Backup
allocated channel: ORA_SBT_TAPE_2
channel ORA_SBT_TAPE_2: SID=69 device type=SBT_TAPE
channel ORA_SBT_TAPE_2: Oracle Secure Backup
channel ORA_SBT_TAPE_1: starting full datafile backup set
channel ORA_SBT_TAPE_1: specifying datafile(s) in backup set
input datafile file number=00002 name=+DATA/orcl/datafile/sysaux.257.816169553
input datafile file number=00007 name=+DATA/orcl/datafile/undotbs1.269.992771913
input datafile file number=00005 name=+DATA/orcl/datafile/example.265.816169651
channel ORA_SBT_TAPE_2: starting full datafile backup set
channel ORA_SBT_TAPE_2: specifying datafile(s) in backup set
input datafile file number=00001 name=+DATA/orcl/datafile/system.256.816169553
input datafile file number=00004 name=+DATA/orcl/datafile/users.259.993778285
input datafile file number=00003 name=+DATA/orcl/datafile/tbsocp05_test.267.992806411
channel ORA_SBT_TAPE_1: backup set complete, elapsed time: 00:00:07
List of Datafiles
=================
File Status Marked Corrupt Empty Blocks Blocks Examined High SCN
---- ------ -------------- ------------ --------------- ----------
2 OK 0 20648 97389 4824532
File Name: +DATA/orcl/datafile/sysaux.257.816169553
Block Type Blocks Failing Blocks Processed
---------- -------------- ----------------
Data 0 24499
Index 0 18156
Other 0 33977
File Status Marked Corrupt Empty Blocks Blocks Examined High SCN
---- ------ -------------- ------------ --------------- ----------
5 OK 0 1688 12805 3640343
File Name: +DATA/orcl/datafile/example.265.816169651
Block Type Blocks Failing Blocks Processed
---------- -------------- ----------------
Data 0 6587
Index 0 1261
Other 0 3264
File Status Marked Corrupt Empty Blocks Blocks Examined High SCN
---- ------ -------------- ------------ --------------- ----------
7 OK 0 12161 51200 4824532
File Name: +DATA/orcl/datafile/undotbs1.269.992771913
Block Type Blocks Failing Blocks Processed
---------- -------------- ----------------
Data 0 0
Index 0 0
Other 0 39039
channel ORA_SBT_TAPE_1: starting full datafile backup set
channel ORA_SBT_TAPE_1: specifying datafile(s) in backup set
including current control file in backup set
channel ORA_SBT_TAPE_2: backup set complete, elapsed time: 00:00:07
List of Datafiles
=================
File Status Marked Corrupt Empty Blocks Blocks Examined High SCN
---- ------ -------------- ------------ --------------- ----------
1 OK 0 30051 113968 4824483
File Name: +DATA/orcl/datafile/system.256.816169553
Block Type Blocks Failing Blocks Processed
---------- -------------- ----------------
Data 0 63614
Index 0 14505
Other 0 5750
File Status Marked Corrupt Empty Blocks Blocks Examined High SCN
---- ------ -------------- ------------ --------------- ----------
3 OK 0 57 641 2681035
File Name: +DATA/orcl/datafile/tbsocp05_test.267.992806411
Block Type Blocks Failing Blocks Processed
---------- -------------- ----------------
Data 0 5
Index 0 0
Other 0 578
File Status Marked Corrupt Empty Blocks Blocks Examined High SCN
---- ------ -------------- ------------ --------------- ----------
4 OK 0 140 23044 4322852
File Name: +DATA/orcl/datafile/users.259.993778285
Block Type Blocks Failing Blocks Processed
---------- -------------- ----------------
Data 0 642
Index 0 46
Other 0 22212
channel ORA_SBT_TAPE_2: starting full datafile backup set
channel ORA_SBT_TAPE_2: specifying datafile(s) in backup set
including current SPFILE in backup set
channel ORA_SBT_TAPE_1: backup set complete, elapsed time: 00:00:00
List of Control File and SPFILE
===============================
File Type Status Blocks Failing Blocks Examined
------------ ------ -------------- ---------------
Control File OK 0 612
channel ORA_SBT_TAPE_2: backup set complete, elapsed time: 00:00:01
List of Control File and SPFILE
===============================
File Type Status Blocks Failing Blocks Examined
------------ ------ -------------- ---------------
SPFILE OK 0 2
Finished backup at 2018-12-02:23:17:03

复制代码

RMAN> show all;
RMAN configuration parameters for database with db_unique_name ORCL are:
CONFIGURE RETENTION POLICY TO RECOVERY WINDOW OF 1 DAYS;
CONFIGURE BACKUP OPTIMIZATION ON;
CONFIGURE DEFAULT DEVICE TYPE TO 'SBT_TAPE';
CONFIGURE CONTROLFILE AUTOBACKUP OFF;
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '%F'; # default
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE SBT_TAPE TO '%F'; # default
CONFIGURE DEVICE TYPE DISK PARALLELISM 8 BACKUP TYPE TO BACKUPSET;
CONFIGURE DEVICE TYPE 'SBT_TAPE' PARALLELISM 2 BACKUP TYPE TO BACKUPSET;
CONFIGURE DATAFILE BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
CONFIGURE DATAFILE BACKUP COPIES FOR DEVICE TYPE SBT_TAPE TO 1; # default
CONFIGURE ARCHIVELOG BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
CONFIGURE ARCHIVELOG BACKUP COPIES FOR DEVICE TYPE SBT_TAPE TO 1; # default
CONFIGURE CHANNEL 1 DEVICE TYPE 'SBT_TAPE' PARMS 'ENV=(OB_MEDIA_FAMILY=station90)';
CONFIGURE CHANNEL 2 DEVICE TYPE 'SBT_TAPE' PARMS 'ENV=(OB_MEDIA_FAMILY=station90)';
CONFIGURE CHANNEL DEVICE TYPE 'SBT_TAPE' PARMS 'ENV=(OB_MEDIA_FAMILY=station90)';
CONFIGURE MAXSETSIZE TO UNLIMITED; # default
CONFIGURE ENCRYPTION FOR DATABASE ON;
CONFIGURE ENCRYPTION ALGORITHM 'AES128'; # default
CONFIGURE COMPRESSION ALGORITHM 'BASIC' AS OF RELEASE 'DEFAULT' OPTIMIZE FOR LOAD TRUE ; # default
CONFIGURE ARCHIVELOG DELETION POLICY TO NONE; # default
CONFIGURE SNAPSHOT CONTROLFILE NAME TO '/u01/app/oracle/product/11.2.0/dbhome_1/dbs/snapcf_orcl.f'; # default
RMAN> CONFIGURE ENCRYPTION FOR DATABASE off;
old RMAN configuration parameters:
CONFIGURE ENCRYPTION FOR DATABASE ON;
new RMAN configuration parameters:
CONFIGURE ENCRYPTION FOR DATABASE OFF;
new RMAN configuration parameters are successfully stored
RMAN> backup tag '5T_WHOLE_FULL' database keep until time 'sysdate+365';
Starting backup at 2018-12-02:23:24:09
current log archived
using channel ORA_SBT_TAPE_1
using channel ORA_SBT_TAPE_2
backup will be obsolete on date 2019-12-02:23:24:09
archived logs required to recover from this backup will be backed up
channel ORA_SBT_TAPE_1: starting full datafile backup set
channel ORA_SBT_TAPE_1: specifying datafile(s) in backup set
input datafile file number=00002 name=+DATA/orcl/datafile/sysaux.257.816169553
input datafile file number=00007 name=+DATA/orcl/datafile/undotbs1.269.992771913
input datafile file number=00005 name=+DATA/orcl/datafile/example.265.816169651
channel ORA_SBT_TAPE_1: starting piece 1 at 2018-12-02:23:24:09
channel ORA_SBT_TAPE_2: starting full datafile backup set
channel ORA_SBT_TAPE_2: specifying datafile(s) in backup set
input datafile file number=00001 name=+DATA/orcl/datafile/system.256.816169553
input datafile file number=00004 name=+DATA/orcl/datafile/users.259.993778285
input datafile file number=00003 name=+DATA/orcl/datafile/tbsocp05_test.267.992806411
channel ORA_SBT_TAPE_2: starting piece 1 at 2018-12-02:23:24:10
channel ORA_SBT_TAPE_1: finished piece 1 at 2018-12-02:23:24:35
piece handle=55tjq3gp_1_1 tag=5T_WHOLE_FULL comment=API Version 2.0,MMS Version 10.4.0.4
channel ORA_SBT_TAPE_1: backup set complete, elapsed time: 00:00:26
channel ORA_SBT_TAPE_2: finished piece 1 at 2018-12-02:23:24:45
piece handle=56tjq3gp_1_1 tag=5T_WHOLE_FULL comment=API Version 2.0,MMS Version 10.4.0.4
channel ORA_SBT_TAPE_2: backup set complete, elapsed time: 00:00:35
using channel ORA_SBT_TAPE_1
using channel ORA_SBT_TAPE_2
backup will be obsolete on date 2019-12-02:23:24:45
archived logs required to recover from this backup will be backed up
channel ORA_SBT_TAPE_1: starting full datafile backup set
channel ORA_SBT_TAPE_1: specifying datafile(s) in backup set
including current SPFILE in backup set
channel ORA_SBT_TAPE_1: starting piece 1 at 2018-12-02:23:24:45
channel ORA_SBT_TAPE_1: finished piece 1 at 2018-12-02:23:25:10
piece handle=57tjq3ht_1_1 tag=5T_WHOLE_FULL comment=API Version 2.0,MMS Version 10.4.0.4
channel ORA_SBT_TAPE_1: backup set complete, elapsed time: 00:00:25
current log archived
using channel ORA_SBT_TAPE_1
using channel ORA_SBT_TAPE_2
backup will be obsolete on date 2019-12-02:23:25:10
archived logs required to recover from this backup will be backed up
channel ORA_SBT_TAPE_1: starting archived log backup set
channel ORA_SBT_TAPE_1: specifying archived log(s) in backup set
input archived log thread=1 sequence=84 RECID=277 STAMP=993857110
channel ORA_SBT_TAPE_1: starting piece 1 at 2018-12-02:23:25:10
channel ORA_SBT_TAPE_1: finished piece 1 at 2018-12-02:23:25:35
piece handle=58tjq3im_1_1 tag=5T_WHOLE_FULL comment=API Version 2.0,MMS Version 10.4.0.4
channel ORA_SBT_TAPE_1: backup set complete, elapsed time: 00:00:25
using channel ORA_SBT_TAPE_1
using channel ORA_SBT_TAPE_2
backup will be obsolete on date 2019-12-02:23:25:36
archived logs required to recover from this backup will be backed up
channel ORA_SBT_TAPE_1: starting full datafile backup set
channel ORA_SBT_TAPE_1: specifying datafile(s) in backup set
including current control file in backup set
channel ORA_SBT_TAPE_1: starting piece 1 at 2018-12-02:23:25:37
channel ORA_SBT_TAPE_1: finished piece 1 at 2018-12-02:23:26:02
piece handle=59tjq3jg_1_1 tag=5T_WHOLE_FULL comment=API Version 2.0,MMS Version 10.4.0.4
channel ORA_SBT_TAPE_1: backup set complete, elapsed time: 00:00:25
Finished backup at 2018-12-02:23:26:02
RMAN>

复制代码

RMAN> change backupset 134 nokeep;
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=201 device type=DISK
allocated channel: ORA_DISK_2
channel ORA_DISK_2: SID=16 device type=DISK
allocated channel: ORA_DISK_3
channel ORA_DISK_3: SID=75 device type=DISK
allocated channel: ORA_DISK_4
channel ORA_DISK_4: SID=141 device type=DISK
allocated channel: ORA_DISK_5
channel ORA_DISK_5: SID=200 device type=DISK
allocated channel: ORA_DISK_6
channel ORA_DISK_6: SID=14 device type=DISK
allocated channel: ORA_DISK_7
channel ORA_DISK_7: SID=20 device type=DISK
allocated channel: ORA_DISK_8
channel ORA_DISK_8: SID=143 device type=DISK
using channel ORA_SBT_TAPE_1
using channel ORA_SBT_TAPE_2
keep attributes for the backup are deleted
backup set key=134 RECID=134 STAMP=993779511
RMAN> change backupset 135 nokeep;
using channel ORA_DISK_1
using channel ORA_DISK_2
using channel ORA_DISK_3
using channel ORA_DISK_4
using channel ORA_DISK_5
using channel ORA_DISK_6
using channel ORA_DISK_7
using channel ORA_DISK_8
using channel ORA_SBT_TAPE_1
using channel ORA_SBT_TAPE_2
keep attributes for the backup are deleted
backup set key=135 RECID=135 STAMP=993857068
RMAN> list backup;
List of Backup Sets
===================
BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ -------------------
131 Full 8.25M SBT_TAPE 00:00:17 2018-12-02:01:15:54
BP Key: 134 Status: UNAVAILABLE Compressed: NO Tag: 1ST_USERS_FULL
Handle: 4ktjnllp_1_1 Media: station90-000013
List of Datafiles in backup set 131
File LV Type Ckp SCN Ckp Time Name
---- -- ---- ---------- ------------------- ----
4 Full 4730677 2018-12-01:00:02:41 +DATA/orcl/datafile/users.259.993778285
BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ -------------------
132 Full 8.25M SBT_TAPE 00:00:16 2018-12-02:01:17:18
BP Key: 135 Status: AVAILABLE Compressed: NO Tag: 2ST_USERS_FULL
Handle: 4ltjnloe_1_1 Media: station90-000013
List of Datafiles in backup set 132
File LV Type Ckp SCN Ckp Time Name
---- -- ---- ---------- ------------------- ----
4 Full 4730677 2018-12-01:00:02:41 +DATA/orcl/datafile/users.259.993778285
BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ -------------------
133 Full 620.25M SBT_TAPE 00:02:47 2018-12-02:01:47:13
List of Datafiles in backup set 133
File LV Type Ckp SCN Ckp Time Name
---- -- ---- ---------- ------------------- ----
2 Full 4800067 2018-12-02:01:44:26 +DATA/orcl/datafile/sysaux.257.816169553
Backup Set Copy #1 of backup set 133
Device Type Elapsed Time Completion Time Compressed Tag
----------- ------------ ------------------- ---------- ---
SBT_TAPE 00:02:47 2018-12-02:01:47:13 NO 3MPT_SYSAUX_FULL
List of Backup Pieces for backup set 133 Copy #1
BP Key Pc# Status Media Piece Name
------- --- ----------- ----------------------- ----------
136 1 AVAILABLE station90-000013 4mtjnnbq_1_1
137 2 AVAILABLE station90-000013 4mtjnnbq_2_1
138 3 AVAILABLE station90-000013 4mtjnnbq_3_1
139 4 AVAILABLE station90-000013 4mtjnnbq_4_1
140 5 AVAILABLE station90-000013 4mtjnnbq_5_1
141 6 AVAILABLE station90-000013 4mtjnnbq_6_1
142 7 AVAILABLE station90-000013 4mtjnnbq_7_1
BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ -------------------
134 Full 621.50M SBT_TAPE 00:01:35 2018-12-02:01:51:51
List of Datafiles in backup set 134
File LV Type Ckp SCN Ckp Time Name
---- -- ---- ---------- ------------------- ----
2 Full 4800568 2018-12-02:01:50:16 +DATA/orcl/datafile/sysaux.257.816169553
Backup Set Copy #1 of backup set 134
Device Type Elapsed Time Completion Time Compressed Tag
----------- ------------ ------------------- ---------- ---
SBT_TAPE 00:01:35 2018-12-02:01:51:50 NO 4SST_SYSAUX_FULL
List of Backup Pieces for backup set 134 Copy #1
BP Key Pc# Status Media Piece Name
------- --- ----------- ----------------------- ----------
144 1 AVAILABLE station90-000013 4ntjnnmo_1_1
143 2 AVAILABLE station90-000014 4ntjnnmo_2_1
145 3 AVAILABLE station90-000013 4ntjnnmo_3_1
146 4 AVAILABLE station90-000014 4ntjnnmo_4_1
147 5 AVAILABLE station90-000013 4ntjnnmo_5_1
148 6 AVAILABLE station90-000014 4ntjnnmo_6_1
150 7 AVAILABLE station90-000014 4ntjnnmo_7_1
149 8 AVAILABLE station90-000013 4ntjnnmo_8_1
BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ -------------------
135 Full 702.00M SBT_TAPE 00:00:19 2018-12-02:23:24:28
BP Key: 151 Status: AVAILABLE Compressed: NO Tag: 5T_WHOLE_FULL
Handle: 55tjq3gp_1_1 Media: station90-000013
List of Datafiles in backup set 135
File LV Type Ckp SCN Ckp Time Name
---- -- ---- ---------- ------------------- ----
2 Full 4825330 2018-12-02:23:24:09 +DATA/orcl/datafile/sysaux.257.816169553
5 Full 4825330 2018-12-02:23:24:09 +DATA/orcl/datafile/example.265.816169651
7 Full 4825330 2018-12-02:23:24:09 +DATA/orcl/datafile/undotbs1.269.992771913
BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ -------------------
136 Full 672.25M SBT_TAPE 00:00:30 2018-12-02:23:24:39
BP Key: 152 Status: AVAILABLE Compressed: NO Tag: 5T_WHOLE_FULL
Handle: 56tjq3gp_1_1 Media: station90-000014
Keep: BACKUP_LOGS Until: 2019-12-02:23:24:09
List of Datafiles in backup set 136
File LV Type Ckp SCN Ckp Time Name
---- -- ---- ---------- ------------------- ----
1 Full 4825332 2018-12-02:23:24:10 +DATA/orcl/datafile/system.256.816169553
3 Full 4825332 2018-12-02:23:24:10 +DATA/orcl/datafile/tbsocp05_test.267.992806411
4 Full 4825332 2018-12-02:23:24:10 +DATA/orcl/datafile/users.259.993778285
BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ -------------------
137 Full 256.00K SBT_TAPE 00:00:15 2018-12-02:23:25:00
BP Key: 153 Status: AVAILABLE Compressed: NO Tag: 5T_WHOLE_FULL
Handle: 57tjq3ht_1_1 Media: station90-000013
Keep: BACKUP_LOGS Until: 2019-12-02:23:24:45
SPFILE Included: Modification time: 2018-12-02:23:09:51
SPFILE db_unique_name: ORCL
BS Key Size Device Type Elapsed Time Completion Time
------- ---------- ----------- ------------ -------------------
138 256.00K SBT_TAPE 00:00:16 2018-12-02:23:25:26
BP Key: 154 Status: AVAILABLE Compressed: NO Tag: 5T_WHOLE_FULL
Handle: 58tjq3im_1_1 Media: station90-000013
Keep: BACKUP_LOGS Until: 2019-12-02:23:25:10
List of Archived Logs in backup set 138
Thrd Seq Low SCN Low Time Next SCN Next Time
---- ------- ---------- ------------------- ---------- ---------
1 84 4825326 2018-12-02:23:24:09 4825394 2018-12-02:23:25:10
BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ -------------------
139 Full 9.75M SBT_TAPE 00:00:16 2018-12-02:23:25:52
BP Key: 155 Status: AVAILABLE Compressed: NO Tag: 5T_WHOLE_FULL
Handle: 59tjq3jg_1_1 Media: station90-000013
Keep: BACKUP_LOGS Until: 2019-12-02:23:25:36
Control File Included: Ckp SCN: 4825420 Ckp time: 2018-12-02:23:25:36
RMAN>

复制代码

[oracle@station90 ~]$ rman target / catalog u90/oracle_4U@rcat
Recovery Manager: Release 11.2.0.3.0 - Production on Sun Dec 2 23:30:08 2018
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
connected to target database: ORCL (DBID=1343950367)
connected to recovery catalog database
RMAN> backup tag '6T_WHOLE_FULL' database keep forever;
Starting backup at 2018-12-02:23:30:15
starting full resync of recovery catalog
full resync complete
current log archived
allocated channel: ORA_SBT_TAPE_1
channel ORA_SBT_TAPE_1: SID=143 device type=SBT_TAPE
channel ORA_SBT_TAPE_1: Oracle Secure Backup
allocated channel: ORA_SBT_TAPE_2
channel ORA_SBT_TAPE_2: SID=21 device type=SBT_TAPE
channel ORA_SBT_TAPE_2: Oracle Secure Backup
backup will never be obsolete
archived logs required to recover from this backup will be backed up
channel ORA_SBT_TAPE_1: starting full datafile backup set
channel ORA_SBT_TAPE_1: specifying datafile(s) in backup set
input datafile file number=00002 name=+DATA/orcl/datafile/sysaux.257.816169553
input datafile file number=00007 name=+DATA/orcl/datafile/undotbs1.269.992771913
input datafile file number=00005 name=+DATA/orcl/datafile/example.265.816169651
channel ORA_SBT_TAPE_1: starting piece 1 at 2018-12-02:23:30:32
channel ORA_SBT_TAPE_2: starting full datafile backup set
channel ORA_SBT_TAPE_2: specifying datafile(s) in backup set
input datafile file number=00001 name=+DATA/orcl/datafile/system.256.816169553
input datafile file number=00004 name=+DATA/orcl/datafile/users.259.993778285
input datafile file number=00003 name=+DATA/orcl/datafile/tbsocp05_test.267.992806411
channel ORA_SBT_TAPE_2: starting piece 1 at 2018-12-02:23:30:33
channel ORA_SBT_TAPE_1: finished piece 1 at 2018-12-02:23:30:58
piece handle=5atjq3so_1_1 tag=6T_WHOLE_FULL comment=API Version 2.0,MMS Version 10.4.0.4
channel ORA_SBT_TAPE_1: backup set complete, elapsed time: 00:00:26
channel ORA_SBT_TAPE_2: finished piece 1 at 2018-12-02:23:30:58
piece handle=5btjq3sp_1_1 tag=6T_WHOLE_FULL comment=API Version 2.0,MMS Version 10.4.0.4
channel ORA_SBT_TAPE_2: backup set complete, elapsed time: 00:00:25
using channel ORA_SBT_TAPE_1
using channel ORA_SBT_TAPE_2
backup will never be obsolete
archived logs required to recover from this backup will be backed up
channel ORA_SBT_TAPE_1: starting full datafile backup set
channel ORA_SBT_TAPE_1: specifying datafile(s) in backup set
including current SPFILE in backup set
channel ORA_SBT_TAPE_1: starting piece 1 at 2018-12-02:23:30:59
channel ORA_SBT_TAPE_1: finished piece 1 at 2018-12-02:23:31:24
piece handle=5ctjq3tj_1_1 tag=6T_WHOLE_FULL comment=API Version 2.0,MMS Version 10.4.0.4
channel ORA_SBT_TAPE_1: backup set complete, elapsed time: 00:00:25
current log archived
using channel ORA_SBT_TAPE_1
using channel ORA_SBT_TAPE_2
backup will never be obsolete
archived logs required to recover from this backup will be backed up
channel ORA_SBT_TAPE_1: starting archived log backup set
channel ORA_SBT_TAPE_1: specifying archived log(s) in backup set
input archived log thread=1 sequence=87 RECID=280 STAMP=993857485
channel ORA_SBT_TAPE_1: starting piece 1 at 2018-12-02:23:31:26
channel ORA_SBT_TAPE_1: finished piece 1 at 2018-12-02:23:31:51
piece handle=5dtjq3ue_1_1 tag=6T_WHOLE_FULL comment=API Version 2.0,MMS Version 10.4.0.4
channel ORA_SBT_TAPE_1: backup set complete, elapsed time: 00:00:25
using channel ORA_SBT_TAPE_1
using channel ORA_SBT_TAPE_2
backup will never be obsolete
archived logs required to recover from this backup will be backed up
channel ORA_SBT_TAPE_1: starting full datafile backup set
channel ORA_SBT_TAPE_1: specifying datafile(s) in backup set
including current control file in backup set
channel ORA_SBT_TAPE_1: starting piece 1 at 2018-12-02:23:31:53
channel ORA_SBT_TAPE_1: finished piece 1 at 2018-12-02:23:32:18
piece handle=5etjq3v8_1_1 tag=6T_WHOLE_FULL comment=API Version 2.0,MMS Version 10.4.0.4
channel ORA_SBT_TAPE_1: backup set complete, elapsed time: 00:00:25
Finished backup at 2018-12-02:23:32:18
RMAN>

复制代码

[oracle@station90 ~]$ rman target /
Recovery Manager: Release 11.2.0.3.0 - Production on Sun Dec 2 23:44:46 2018
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
connected to target database: ORCL (DBID=1343950367)
RMAN> backup incremental level 0 tablespace users;
Starting backup at 2018-12-02:23:45:16
using target database control file instead of recovery catalog
allocated channel: ORA_SBT_TAPE_1
channel ORA_SBT_TAPE_1: SID=204 device type=SBT_TAPE
channel ORA_SBT_TAPE_1: Oracle Secure Backup
allocated channel: ORA_SBT_TAPE_2
channel ORA_SBT_TAPE_2: SID=130 device type=SBT_TAPE
channel ORA_SBT_TAPE_2: Oracle Secure Backup
channel ORA_SBT_TAPE_1: starting incremental level 0 datafile backup set
channel ORA_SBT_TAPE_1: specifying datafile(s) in backup set
input datafile file number=00004 name=+DATA/orcl/datafile/users.259.993778285
channel ORA_SBT_TAPE_1: starting piece 1 at 2018-12-02:23:45:27
channel ORA_SBT_TAPE_1: finished piece 1 at 2018-12-02:23:45:52
piece handle=5ktjq4on_1_1 tag=TAG20181202T234527 comment=API Version 2.0,MMS Version 10.4.0.4
channel ORA_SBT_TAPE_1: backup set complete, elapsed time: 00:00:25
Finished backup at 2018-12-02:23:45:52
RMAN> backup incremental level 1 tablespace users;
Starting backup at 2018-12-02:23:46:03
using channel ORA_SBT_TAPE_1
using channel ORA_SBT_TAPE_2
channel ORA_SBT_TAPE_1: starting incremental level 1 datafile backup set
channel ORA_SBT_TAPE_1: specifying datafile(s) in backup set
input datafile file number=00004 name=+DATA/orcl/datafile/users.259.993778285
channel ORA_SBT_TAPE_1: starting piece 1 at 2018-12-02:23:46:04
channel ORA_SBT_TAPE_1: finished piece 1 at 2018-12-02:23:46:29
piece handle=5ltjq4pr_1_1 tag=TAG20181202T234603 comment=API Version 2.0,MMS Version 10.4.0.4
channel ORA_SBT_TAPE_1: backup set complete, elapsed time: 00:00:25
Finished backup at 2018-12-02:23:46:29
RMAN> backup incremental level 1 tablespace users;
Starting backup at 2018-12-02:23:46:34
using channel ORA_SBT_TAPE_1
using channel ORA_SBT_TAPE_2
channel ORA_SBT_TAPE_1: starting incremental level 1 datafile backup set
channel ORA_SBT_TAPE_1: specifying datafile(s) in backup set
input datafile file number=00004 name=+DATA/orcl/datafile/users.259.993778285
channel ORA_SBT_TAPE_1: starting piece 1 at 2018-12-02:23:46:35
channel ORA_SBT_TAPE_1: finished piece 1 at 2018-12-02:23:47:00
piece handle=5mtjq4qr_1_1 tag=TAG20181202T234635 comment=API Version 2.0,MMS Version 10.4.0.4
channel ORA_SBT_TAPE_1: backup set complete, elapsed time: 00:00:25
Finished backup at 2018-12-02:23:47:00
RMAN> show all;
RMAN configuration parameters for database with db_unique_name ORCL are:
CONFIGURE RETENTION POLICY TO RECOVERY WINDOW OF 1 DAYS;
CONFIGURE BACKUP OPTIMIZATION ON;
CONFIGURE DEFAULT DEVICE TYPE TO 'SBT_TAPE';
CONFIGURE CONTROLFILE AUTOBACKUP OFF;
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '%F'; # default
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE SBT_TAPE TO '%F'; # default
CONFIGURE DEVICE TYPE DISK PARALLELISM 8 BACKUP TYPE TO BACKUPSET;
CONFIGURE DEVICE TYPE 'SBT_TAPE' PARALLELISM 2 BACKUP TYPE TO BACKUPSET;
CONFIGURE DATAFILE BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
CONFIGURE DATAFILE BACKUP COPIES FOR DEVICE TYPE SBT_TAPE TO 1; # default
CONFIGURE ARCHIVELOG BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
CONFIGURE ARCHIVELOG BACKUP COPIES FOR DEVICE TYPE SBT_TAPE TO 1; # default
CONFIGURE CHANNEL 1 DEVICE TYPE 'SBT_TAPE' PARMS 'ENV=(OB_MEDIA_FAMILY=station90)';
CONFIGURE CHANNEL 2 DEVICE TYPE 'SBT_TAPE' PARMS 'ENV=(OB_MEDIA_FAMILY=station90)';
CONFIGURE CHANNEL DEVICE TYPE 'SBT_TAPE' PARMS 'ENV=(OB_MEDIA_FAMILY=station90)';
CONFIGURE MAXSETSIZE TO UNLIMITED; # default
CONFIGURE ENCRYPTION FOR DATABASE OFF;
CONFIGURE ENCRYPTION ALGORITHM 'AES128'; # default
CONFIGURE COMPRESSION ALGORITHM 'BASIC' AS OF RELEASE 'DEFAULT' OPTIMIZE FOR LOAD TRUE ; # default
CONFIGURE ARCHIVELOG DELETION POLICY TO NONE; # default
CONFIGURE SNAPSHOT CONTROLFILE NAME TO '/u01/app/oracle/product/11.2.0/dbhome_1/dbs/snapcf_orcl.f'; # default
RMAN> report need backup incremental 1;
Report of files that need more than 1 incrementals during recovery
File Incrementals Name
---- ------------ ----------------------------------------------
4 2 +DATA/orcl/datafile/users.259.993778285
RMAN> report need backup incremental 2;
Report of files that need more than 2 incrementals during recovery
File Incrementals Name
---- ------------ ----------------------------------------------
RMAN> report need backup incremental 1;
Report of files that need more than 1 incrementals during recovery
File Incrementals Name
---- ------------ ----------------------------------------------
4 2 +DATA/orcl/datafile/users.259.993778285
RMAN> report need backup incremental 2;
Report of files that need more than 2 incrementals during recovery
File Incrementals Name
---- ------------ ----------------------------------------------
RMAN> backup incremental level 1 tablespace users;
Starting backup at 2018-12-02:23:49:13
using channel ORA_SBT_TAPE_1
using channel ORA_SBT_TAPE_2
channel ORA_SBT_TAPE_1: starting incremental level 1 datafile backup set
channel ORA_SBT_TAPE_1: specifying datafile(s) in backup set
input datafile file number=00004 name=+DATA/orcl/datafile/users.259.993778285
channel ORA_SBT_TAPE_1: starting piece 1 at 2018-12-02:23:49:13
channel ORA_SBT_TAPE_1: finished piece 1 at 2018-12-02:23:49:38
piece handle=5ntjq4vp_1_1 tag=TAG20181202T234913 comment=API Version 2.0,MMS Version 10.4.0.4
channel ORA_SBT_TAPE_1: backup set complete, elapsed time: 00:00:25
Finished backup at 2018-12-02:23:49:38
RMAN> report need backup incremental 2;
Report of files that need more than 2 incrementals during recovery
File Incrementals Name
---- ------------ ----------------------------------------------
4 3 +DATA/orcl/datafile/users.259.993778285
RMAN> backup incremental level 1 cumulative tablespace users;
Starting backup at 2018-12-02:23:50:15
using channel ORA_SBT_TAPE_1
using channel ORA_SBT_TAPE_2
channel ORA_SBT_TAPE_1: starting incremental level 1 datafile backup set
channel ORA_SBT_TAPE_1: specifying datafile(s) in backup set
input datafile file number=00004 name=+DATA/orcl/datafile/users.259.993778285
channel ORA_SBT_TAPE_1: starting piece 1 at 2018-12-02:23:50:15
channel ORA_SBT_TAPE_1: finished piece 1 at 2018-12-02:23:50:30
piece handle=5otjq51n_1_1 tag=TAG20181202T235015 comment=API Version 2.0,MMS Version 10.4.0.4
channel ORA_SBT_TAPE_1: backup set complete, elapsed time: 00:00:15
Finished backup at 2018-12-02:23:50:30
RMAN> report need backup incremental 2;
Report of files that need more than 2 incrementals during recovery
File Incrementals Name
---- ------------ ----------------------------------------------
RMAN> report need backup incremental 1;
Report of files that need more than 1 incrementals during recovery
File Incrementals Name
---- ------------ ----------------------------------------------
RMAN> report need backup incremental 0;
Report of files that need more than 0 incrementals during recovery
File Incrementals Name
---- ------------ ----------------------------------------------
4 1 +DATA/orcl/datafile/users.259.993778285
RMAN>

复制代码

1Z0-053第5章（28/40）

1Z0-053第6章（29/40）
8a：

[oracle@station90 ~]$ rman target / catalog u90/oracle_4U@rcat
Recovery Manager: Release 11.2.0.3.0 - Production on Mon Dec 3 00:31:38 2018
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
connected to target database: ORCL (not mounted)
connected to recovery catalog database
RMAN> set dbid 1343950367;
executing command: SET DBID
database name is "ORCL" and DBID is 1343950367
RMAN> list backup of controlfile;
List of Backup Sets
===================
BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ -------------------
1136 Full 9.75M SBT_TAPE 00:00:17 2018-12-03:00:25:04
BP Key: 1138 Status: AVAILABLE Compressed: NO Tag: LABS-8A-CONTROLFILE
Handle: 5ptjq72f_1_1 Media: station90-000015
Control File Included: Ckp SCN: 4846745 Ckp time: 2018-12-03:00:24:47
RMAN> restore controlfile;
Starting restore at 2018-12-03:00:33:17
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=68 device type=DISK
allocated channel: ORA_DISK_2
channel ORA_DISK_2: SID=192 device type=DISK
allocated channel: ORA_DISK_3
channel ORA_DISK_3: SID=6 device type=DISK
allocated channel: ORA_DISK_4
channel ORA_DISK_4: SID=69 device type=DISK
allocated channel: ORA_DISK_5
channel ORA_DISK_5: SID=133 device type=DISK
allocated channel: ORA_DISK_6
channel ORA_DISK_6: SID=193 device type=DISK
allocated channel: ORA_DISK_7
channel ORA_DISK_7: SID=8 device type=DISK
allocated channel: ORA_DISK_8
channel ORA_DISK_8: SID=70 device type=DISK
allocated channel: ORA_SBT_TAPE_1
channel ORA_SBT_TAPE_1: SID=134 device type=SBT_TAPE
channel ORA_SBT_TAPE_1: Oracle Secure Backup
allocated channel: ORA_SBT_TAPE_2
channel ORA_SBT_TAPE_2: SID=9 device type=SBT_TAPE
channel ORA_SBT_TAPE_2: Oracle Secure Backup
channel ORA_SBT_TAPE_1: starting datafile backup set restore
channel ORA_SBT_TAPE_1: restoring control file
channel ORA_SBT_TAPE_1: reading from backup piece 5ptjq72f_1_1
channel ORA_SBT_TAPE_1: piece handle=5ptjq72f_1_1 tag=LABS-8A-CONTROLFILE
channel ORA_SBT_TAPE_1: restored backup piece 1
channel ORA_SBT_TAPE_1: restore complete, elapsed time: 00:00:25
output file name=+DATA/orcl/controlfile/current.260.993861229
output file name=+FRA/orcl/controlfile/current.256.993861233
Finished restore at 2018-12-03:00:33:56
RMAN> alter database mount;
database mounted
released channel: ORA_DISK_1
released channel: ORA_DISK_2
released channel: ORA_DISK_3
released channel: ORA_DISK_4
released channel: ORA_DISK_5
released channel: ORA_DISK_6
released channel: ORA_DISK_7
released channel: ORA_DISK_8
released channel: ORA_SBT_TAPE_1
released channel: ORA_SBT_TAPE_2
RMAN> recover database;
Starting recover at 2018-12-03:00:34:23
Starting implicit crosscheck backup at 2018-12-03:00:34:23
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=71 device type=DISK
allocated channel: ORA_DISK_2
channel ORA_DISK_2: SID=194 device type=DISK
allocated channel: ORA_DISK_3
channel ORA_DISK_3: SID=9 device type=DISK
allocated channel: ORA_DISK_4
channel ORA_DISK_4: SID=70 device type=DISK
allocated channel: ORA_DISK_5
channel ORA_DISK_5: SID=134 device type=DISK
allocated channel: ORA_DISK_6
channel ORA_DISK_6: SID=193 device type=DISK
allocated channel: ORA_DISK_7
channel ORA_DISK_7: SID=8 device type=DISK
allocated channel: ORA_DISK_8
channel ORA_DISK_8: SID=69 device type=DISK
Finished implicit crosscheck backup at 2018-12-03:00:34:25
Starting implicit crosscheck copy at 2018-12-03:00:34:25
using channel ORA_DISK_1
using channel ORA_DISK_2
using channel ORA_DISK_3
using channel ORA_DISK_4
using channel ORA_DISK_5
using channel ORA_DISK_6
using channel ORA_DISK_7
using channel ORA_DISK_8
Finished implicit crosscheck copy at 2018-12-03:00:34:25
searching for all files in the recovery area
cataloging files...
no files cataloged
using channel ORA_DISK_1
using channel ORA_DISK_2
using channel ORA_DISK_3
using channel ORA_DISK_4
using channel ORA_DISK_5
using channel ORA_DISK_6
using channel ORA_DISK_7
using channel ORA_DISK_8
allocated channel: ORA_SBT_TAPE_1
channel ORA_SBT_TAPE_1: SID=133 device type=SBT_TAPE
channel ORA_SBT_TAPE_1: Oracle Secure Backup
allocated channel: ORA_SBT_TAPE_2
channel ORA_SBT_TAPE_2: SID=6 device type=SBT_TAPE
channel ORA_SBT_TAPE_2: Oracle Secure Backup
starting media recovery
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
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
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
archived log for thread 1 with sequence 96 is already on disk as file +DATA/orcl/onlinelog/group_4.258.992810599
archived log file name=+FRA/orcl/archivelog/2018_12_03/thread_1_seq_93.361.993860673 thread=1 sequence=93
archived log file name=+FRA/orcl/archivelog/2018_12_03/thread_1_seq_94.352.993860675 thread=1 sequence=94
archived log file name=+FRA/orcl/archivelog/2018_12_03/thread_1_seq_95.351.993860675 thread=1 sequence=95
archived log file name=+DATA/orcl/onlinelog/group_4.258.992810599 thread=1 sequence=96
media recovery complete, elapsed time: 00:00:01
Finished recover at 2018-12-03:00:34:40
RMAN> alter database open resetlogs;
database opened
new incarnation of database registered in recovery catalog
starting full resync of recovery catalog
full resync complete
RMAN>

复制代码

8b：

[oracle@station90 ~]$ rman target /
Recovery Manager: Release 11.2.0.3.0 - Production on Mon Dec 3 00:47:46 2018
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
connected to target database: ORCL (DBID=1343950367)
RMAN> list incarnation of database;
using target database control file instead of recovery catalog
List of Database Incarnations
DB Key Inc Key DB Name DB ID STATUS Reset SCN Reset Time
------- ------- -------- ---------------- --- ---------- ----------
1 1 ORCL 1343950367 CURRENT 4846785 2018-12-03:00:35:09
RMAN> exit
Recovery Manager complete.
[oracle@station90 ~]$ rman target / catalog u90/oracle_4U@rcat
Recovery Manager: Release 11.2.0.3.0 - Production on Mon Dec 3 00:48:28 2018
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
connected to target database: ORCL (DBID=1343950367)
connected to recovery catalog database
RMAN> list incarnation of database;
starting full resync of recovery catalog
full resync complete
List of Database Incarnations
DB Key Inc Key DB Name DB ID STATUS Reset SCN Reset Time
------- ------- -------- ---------------- --- ---------- ----------
1 25 ORCL 1343950367 PARENT 1 2009-08-15:00:16:43
1 26 ORCL 1343950367 PARENT 945184 2013-05-23:09:47:15
1 27 ORCL 1343950367 PARENT 2704217 2018-11-21:20:14:20
1 2 ORCL 1343950367 PARENT 2706559 2018-11-21:20:43:16
1 1159 ORCL 1343950367 CURRENT 4846785 2018-12-03:00:35:09
RMAN> exit
Recovery Manager complete.
[oracle@station90 ~]$ sqlplus /nolog
SQL*Plus: Release 11.2.0.3.0 Production on Mon Dec 3 00:48:42 2018
Copyright (c) 1982, 2011, Oracle. All rights reserved.
SQL> conn / as sysdba
Connected.
SQL> select * from v$tempfile;
no rows selected
SQL> alter tablespace temp add tempfile size 30M autoextend on;
Tablespace altered.
SQL>

复制代码

[root@station80 ~]# losetup /dev/loop0
/dev/loop0: [fd01]:2127843 (/u01/loop/device/loop0.img)
[root@station80 ~]# losetup /dev/loop1
/dev/loop1: [fd01]:2127844 (/u01/loop/device/loop1.img)
[root@station80 ~]# losetup /dev/loop2
/dev/loop2: [fd01]:2127845 (/u01/loop/device/loop2.img)
[root@station80 ~]# losetup /dev/loop3
/dev/loop3: [fd01]:2127846 (/u01/loop/device/loop3.img)
[root@station80 ~]# losetup /dev/loop4
/dev/loop4: [fd01]:2127847 (/u01/loop/device/loop4.img)
[root@station80 ~]# losetup /dev/loop5
loop: can't get info on device /dev/loop5: No such device or address
[root@station80 ~]# du -sh /u01/loop/device/loop0.img
2.7G /u01/loop/device/loop0.img
[root@station80 ~]# du -sh /u01/loop/device/loop1.img
2.7G /u01/loop/device/loop1.img
[root@station80 ~]# /u01/loop/device/loop2.img
-bash: /u01/loop/device/loop2.img: Permission denied
[root@station80 ~]# du -sh /u01/loop/device/loop2.img
513M /u01/loop/device/loop2.img
[root@station80 ~]# du -sh /u01/loop/device/loop3.img
513M /u01/loop/device/loop3.img
[root@station80 ~]# du -sh /u01/loop/device/loop4.img
513M /u01/loop/device/loop4.img
[root@station80 ~]# rpm -qa | grep oracleasm
oracleasm-2.6.18-238.el5PAE-2.0.5-1.el5
oracleasm-2.6.18-238.el5debug-2.0.5-1.el5
oracleasm-2.6.18-238.el5xen-2.0.5-1.el5
oracleasm-2.6.18-238.el5-2.0.5-1.el5
oracleasm-support-2.1.8-1.el5
oracleasmlib-2.0.4-1.el5
[root@station80 ~]# oracleasm-discover
Using ASMLib from /opt/oracle/extapi/32/asm/orcl/1/libasm.so
[ASM Library - Generic Linux, version 2.0.4 (KABI_V2)]
Discovered disk: ORCL:DISK1 [12289725 blocks (6292339200 bytes), maxio 512]
Discovered disk: ORCL:DISK2 [5638752 blocks (2887041024 bytes), maxio 512]
Discovered disk: ORCL:DISK3 [5638752 blocks (2887041024 bytes), maxio 512]
Discovered disk: ORCL:DISK4 [5638752 blocks (2887041024 bytes), maxio 512]
Discovered disk: ORCL:DISK5 [5638752 blocks (2887041024 bytes), maxio 512]
[root@station80 ~]# oracleasm configure -i
Configuring the Oracle ASM library driver.
This will configure the on-boot properties of the Oracle ASM library
driver. The following questions will determine whether the driver is
loaded on boot and what permissions it will have. The current values
will be shown in brackets ('[]'). Hitting <ENTER> without typing an
answer will keep that current value. Ctrl-C will abort.
Default user to own the driver interface [oracle]:
Default group to own the driver interface [dba]:
Start Oracle ASM library driver on boot (y/n) [y]:
Scan for Oracle ASM disks on boot (y/n) [y]:
Writing Oracle ASM library driver configuration: done
[root@station80 ~]# oracleasm createdisk DISK8 /dev/loop2
Writing disk header: done
Instantiating disk: done
[root@station80 ~]# oracleasm createdisk DISK9 /dev/loop3
Writing disk header: done
Instantiating disk: done
[root@station80 ~]# oracleasm createdisk DISK10 /dev/loop4
Writing disk header: done
Instantiating disk: done
[root@station80 ~]# oracleasm-discover
Using ASMLib from /opt/oracle/extapi/32/asm/orcl/1/libasm.so
[ASM Library - Generic Linux, version 2.0.4 (KABI_V2)]
Discovered disk: ORCL:DISK1 [12289725 blocks (6292339200 bytes), maxio 512]
Discovered disk: ORCL:DISK10 [1048576 blocks (536870912 bytes), maxio 128]
Discovered disk: ORCL:DISK2 [5638752 blocks (2887041024 bytes), maxio 512]
Discovered disk: ORCL:DISK3 [5638752 blocks (2887041024 bytes), maxio 512]
Discovered disk: ORCL:DISK4 [5638752 blocks (2887041024 bytes), maxio 512]
Discovered disk: ORCL:DISK5 [5638752 blocks (2887041024 bytes), maxio 512]
Discovered disk: ORCL:DISK8 [1048576 blocks (536870912 bytes), maxio 128]
Discovered disk: ORCL:DISK9 [1048576 blocks (536870912 bytes), maxio 128]
[root@station80 ~]# oracleasm createdisk DISK6 /dev/loop0
Writing disk header: done
Instantiating disk: done
[root@station80 ~]# oracleasm createdisk DISK7 /dev/loop1
Writing disk header: done
Instantiating disk: done
[root@station80 ~]# oracleasm-discover
Using ASMLib from /opt/oracle/extapi/32/asm/orcl/1/libasm.so
[ASM Library - Generic Linux, version 2.0.4 (KABI_V2)]
Discovered disk: ORCL:DISK1 [12289725 blocks (6292339200 bytes), maxio 512]
Discovered disk: ORCL:DISK10 [1048576 blocks (536870912 bytes), maxio 128]
Discovered disk: ORCL:DISK2 [5638752 blocks (2887041024 bytes), maxio 512]
Discovered disk: ORCL:DISK3 [5638752 blocks (2887041024 bytes), maxio 512]
Discovered disk: ORCL:DISK4 [5638752 blocks (2887041024 bytes), maxio 512]
Discovered disk: ORCL:DISK5 [5638752 blocks (2887041024 bytes), maxio 512]
Discovered disk: ORCL:DISK6 [5632000 blocks (2883584000 bytes), maxio 128]
Discovered disk: ORCL:DISK7 [5632000 blocks (2883584000 bytes), maxio 128]
Discovered disk: ORCL:DISK8 [1048576 blocks (536870912 bytes), maxio 128]
Discovered disk: ORCL:DISK9 [1048576 blocks (536870912 bytes), maxio 128]
[root@station80 ~]# oracleasm scandisk
oracleasm: 'scandisk' is not an oracleasm command
Usage: oracleasm [--exec-path=<exec_path>] <command> [ <args> ]
oracleasm --exec-path
oracleasm -h
oracleasm -V
The basic oracleasm commands are:
configure Configure the Oracle Linux ASMLib driver
init Load and initialize the ASMLib driver
exit Stop the ASMLib driver
scandisks Scan the system for Oracle ASMLib disks
status Display the status of the Oracle ASMLib driver
listdisks List known Oracle ASMLib disks
querydisk Determine if a disk belongs to Oracle ASMlib
createdisk Allocate a device for Oracle ASMLib use
deletedisk Return a device to the operating system
renamedisk Change the label of an Oracle ASMlib disk
update-driver Download the latest ASMLib driver
[root@station80 ~]# oracleasm scandisks
Reloading disk partitions: done
Cleaning any stale ASM disks...
Scanning system for ASM disks...
[root@station80 ~]# oracleasm listdisks
DISK1
DISK10
DISK2
DISK3
DISK4
DISK5
DISK6
DISK7
DISK8
DISK9
[root@station80 ~]#

复制代码

[oracle@station80 ~]$ sqlplus /nolog
SQL*Plus: Release 11.2.0.1.0 Production on Tue Nov 27 02:38:02 2018
Copyright (c) 1982, 2009, Oracle. All rights reserved.
SQL> conn / as sysasm
Connected.
SQL> create diskgroup highgrp high redundancy
2 failgroup fg1
3 disk 'ORCL:DISK8'
4 failgroup fg2
5 disk 'ORCL:DISK9'
6 failgroup fg3
7 disk 'ORCL:DISK10';
Diskgroup created.
SQL> desc v$asm_diskgroup
Name Null? Type
----------------------------------------- -------- ----------------------------
GROUP_NUMBER NUMBER
NAME VARCHAR2(30)
SECTOR_SIZE NUMBER
BLOCK_SIZE NUMBER
ALLOCATION_UNIT_SIZE NUMBER
STATE VARCHAR2(11)
TYPE VARCHAR2(6)
TOTAL_MB NUMBER
FREE_MB NUMBER
HOT_USED_MB NUMBER
COLD_USED_MB NUMBER
REQUIRED_MIRROR_FREE_MB NUMBER
USABLE_FILE_MB NUMBER
OFFLINE_DISKS NUMBER
COMPATIBILITY VARCHAR2(60)
DATABASE_COMPATIBILITY VARCHAR2(60)
VOTING_FILES VARCHAR2(1)
SQL> select NAME, GROUP_NUMBER , TOTAL_MB,USABLE_FILE_MB from v$asm_diskgroup;
NAME
--------------------------------------------------------------------------------
GROUP_NUMBER TOTAL_MB USABLE_FILE_MB
------------ ---------- --------------
FRA
1 6000 5723
DATA
2 11012 2942
HIGHGRP
3 1536 461
SQL>

复制代码

db 与ASM联系：

[root@station90 桌面]# ps aux | grep ora_ | grep rcat | sort
oracle 11374 0.0 0.3 1812148 52328 ? Ss 23:44 0:00 ora_pmon_rcat
oracle 11376 0.0 0.2 1809864 41700 ? Ss 23:44 0:00 ora_psp0_rcat
oracle 11378 0.9 0.2 1809864 40744 ? Ss 23:44 0:00 ora_vktm_rcat
oracle 11382 0.0 0.2 1809864 41884 ? Ss 23:44 0:00 ora_gen0_rcat
oracle 11384 0.0 0.2 1809864 40600 ? Ss 23:44 0:00 ora_diag_rcat
oracle 11386 0.0 0.3 1810376 63192 ? Ss 23:44 0:00 ora_dbrm_rcat
oracle 11388 0.0 0.3 1812424 57524 ? Ss 23:44 0:00 ora_dia0_rcat
oracle 11390 0.1 0.4 1809864 69576 ? Ss 23:44 0:00 ora_mman_rcat
oracle 11392 0.0 0.3 1817536 50892 ? Ss 23:44 0:00 ora_dbw0_rcat
oracle 11394 0.0 0.3 1825416 51252 ? Ss 23:44 0:00 ora_lgwr_rcat
oracle 11396 0.0 0.3 1809864 51144 ? Ss 23:44 0:00 ora_ckpt_rcat
oracle 11398 0.1 0.4 1810380 66564 ? Ss 23:44 0:00 ora_smon_rcat
oracle 11400 0.0 0.2 1809864 40524 ? Ss 23:44 0:00 ora_reco_rcat
oracle 11402 1.1 0.7 1815808 127836 ? Ss 23:44 0:00 ora_mmon_rcat
oracle 11404 0.0 0.3 1811096 58584 ? Ss 23:44 0:00 ora_mmnl_rcat
oracle 11406 0.0 0.2 1816120 39980 ? Ss 23:44 0:00 ora_d000_rcat
oracle 11408 0.0 0.2 1811060 37716 ? Ss 23:44 0:00 ora_s000_rcat
oracle 11474 0.0 0.2 1811972 45636 ? Ss 23:44 0:00 ora_p000_rcat
oracle 11477 0.0 0.2 1811972 45272 ? Ss 23:44 0:00 ora_p001_rcat
oracle 11480 0.0 0.2 1811972 46164 ? Ss 23:44 0:00 ora_p002_rcat
oracle 11527 0.0 0.2 1809864 44352 ? Ss 23:44 0:00 ora_qmnc_rcat
oracle 11543 0.6 0.5 1820656 90260 ? Ss 23:44 0:00 ora_cjq0_rcat
oracle 11548 0.1 0.2 1809864 42876 ? Ss 23:44 0:00 ora_vkrm_rcat
oracle 11550 0.5 0.5 1811076 89060 ? Ss 23:44 0:00 ora_j000_rcat
oracle 11552 4.0 0.8 1812000 132880 ? Ss 23:44 0:00 ora_j001_rcat
oracle 11554 0.3 0.4 1811440 77788 ? Ss 23:44 0:00 ora_j002_rcat
oracle 11556 1.0 0.5 1811492 90744 ? Ss 23:44 0:00 ora_j003_rcat
oracle 11562 0.6 0.4 1811480 76420 ? Ss 23:44 0:00 ora_j004_rcat
oracle 11564 0.1 0.4 1811432 73344 ? Ss 23:44 0:00 ora_j005_rcat
oracle 11566 0.0 0.3 1811436 63792 ? Ss 23:44 0:00 ora_j006_rcat
oracle 11568 0.0 0.4 1811432 69344 ? Ss 23:44 0:00 ora_j007_rcat
oracle 11570 0.0 0.2 1809860 39616 ? Ss 23:44 0:00 ora_j008_rcat
oracle 11573 0.0 0.3 1811400 60220 ? Ss 23:44 0:00 ora_q000_rcat
oracle 11575 0.0 0.3 1810372 52132 ? Ss 23:44 0:00 ora_q001_rcat
[root@station90 桌面]# ps aux | grep ora_ | grep rcat | sort
oracle 11374 0.0 0.3 1812148 52584 ? Ss 23:44 0:00 ora_pmon_rcat
oracle 11376 0.0 0.2 1809864 41716 ? Ss 23:44 0:00 ora_psp0_rcat
oracle 11378 0.9 0.2 1809864 40744 ? Ss 23:44 0:01 ora_vktm_rcat
oracle 11382 0.0 0.2 1811264 44784 ? Ss 23:44 0:00 ora_gen0_rcat
oracle 11384 0.0 0.2 1809864 40600 ? Ss 23:44 0:00 ora_diag_rcat
oracle 11386 0.0 0.3 1810376 63200 ? Ss 23:44 0:00 ora_dbrm_rcat
oracle 11388 0.0 0.3 1812424 57524 ? Ss 23:44 0:00 ora_dia0_rcat
oracle 11390 0.0 0.4 1809864 69576 ? Ss 23:44 0:00 ora_mman_rcat
oracle 11392 0.0 0.3 1819156 60496 ? Ss 23:44 0:00 ora_dbw0_rcat
oracle 11394 0.0 0.3 1825416 51252 ? Ss 23:44 0:00 ora_lgwr_rcat
oracle 11396 0.0 0.3 1809864 51404 ? Ss 23:44 0:00 ora_ckpt_rcat
oracle 11398 0.0 0.4 1810380 66564 ? Ss 23:44 0:00 ora_smon_rcat
oracle 11400 0.0 0.3 1811400 53964 ? Ss 23:44 0:00 ora_reco_rcat
oracle 11402 0.3 0.7 1815808 127836 ? Ss 23:44 0:00 ora_mmon_rcat
oracle 11404 0.0 0.3 1811096 60684 ? Ss 23:44 0:00 ora_mmnl_rcat
oracle 11406 0.0 0.2 1816120 39980 ? Ss 23:44 0:00 ora_d000_rcat
oracle 11408 0.0 0.2 1811060 37716 ? Ss 23:44 0:00 ora_s000_rcat
oracle 11474 0.0 0.2 1811972 45636 ? Ss 23:44 0:00 ora_p000_rcat
oracle 11477 0.0 0.2 1811972 45272 ? Ss 23:44 0:00 ora_p001_rcat
oracle 11480 0.0 0.2 1811972 46164 ? Ss 23:44 0:00 ora_p002_rcat
oracle 11527 0.0 0.2 1809864 44352 ? Ss 23:44 0:00 ora_qmnc_rcat
oracle 11543 0.1 0.5 1820656 96440 ? Ss 23:44 0:00 ora_cjq0_rcat
oracle 11548 0.1 0.2 1809864 42876 ? Ss 23:44 0:00 ora_vkrm_rcat
oracle 11573 0.0 0.3 1811400 60220 ? Ss 23:44 0:00 ora_q000_rcat
oracle 11575 0.0 0.3 1810372 52132 ? Ss 23:44 0:00 ora_q001_rcat
oracle 11599 0.0 0.2 1811348 46076 ? Ss 23:46 0:00 ora_asmb_rcat
oracle 11603 0.0 0.2 1810460 46340 ? Ss 23:46 0:00 ora_rbal_rcat
oracle 11605 0.0 0.3 1817584 48972 ? Ss 23:46 0:00 ora_mark_rcat
oracle 11607 0.1 0.2 1811740 47632 ? Ss 23:46 0:00 ora_ocf0_rcat
oracle 11611 0.2 0.2 1811348 44244 ? Ss 23:46 0:00 ora_o000_rcat
[root@station90 桌面]#

复制代码

select * from v$asm_diskgroup;
select * from v$asm_attribute;
select * from v$asm_client;
create diskgroup highgrp high redundancy
failgroup fg1
disk 'ORCL:DISK8'
failgroup fg2
disk 'ORCL:DISK9'
failgroup fg3
disk 'ORCL:DISK10'
attribute 'au_size'='4M';

复制代码

select * from dba_data_files;
select * from v$controlfile;
create tablespace tbsunp datafile '+data(temp2)/orcl/tbsunp.dbf' size 10M;

复制代码

select * from v$asm_disk;
alter diskgroup data
add failgroup fg1 disk 'ORCL:DISK6' name fg1_dsk1 size 2753M
add failgroup fg2 disk 'ORCL:DISK7' name fg2_dsk1 size 2753M;
select * from v$asm_template where group_number=2;
select * from v$asm_alias where name='SYSTEM.256.832197063' and group_number=2;
select * from v$asm_file where file_number=256 and group_number=2;
select * from v$asm_alias where lower(name)='tbsunp.dbf' and group_number=2;
select * from v$asm_alias where file_number=268;
select * from v$asm_file where file_number=268 and group_number=2;
alter diskgroup data add template temp1 attributes (unprotected fine ) ;

复制代码

课程第27次（2018-11-30星期五）

1Z0-052第5章（30/40）

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

复制代码

1Z0-053第7章（31/40）

[oracle@station90 ~]$ sqlplus /nolog
SQL*Plus: Release 11.2.0.3.0 Production on Tue Dec 4 01:31:04 2018
Copyright (c) 1982, 2011, Oracle. All rights reserved.
SQL> conn / as sysdba
Connected.
SQL> show paramater db_recovery
SP2-0158: unknown SHOW option "paramater"
SP2-0735: unknown SHOW option beginning "db_recover..."
SQL> show parameter db_recovery
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_recovery_file_dest string +FRA
db_recovery_file_dest_size big integer 3882M
SQL> alter system set db_recovery_file_dest='/u01/app/oracle/fast_recovery_area';
System altered.
SQL> alter system set db_recovery_file_dest='/u01/app/oracle/fast_recovery_area';
System altered.
SQL> show parameter db_create_file_dest
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_create_file_dest string +DATA
SQL> alter system set db_create_file_dest='/u01/app/oracle/oradata';
System altered.
SQL> set linesize 1000
SQL> select * from v$log;
GROUP# THREAD# SEQUENCE# BYTES BLOCKSIZE MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIME NEXT_CHANGE# NEXT_TIME
---------- ---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- ------------------- ------------ -------------------
1 1 109 52428800 512 2 YES INACTIVE 2047121 2018-12-04:01:18:27 2051947 2018-12-04:01:24:16
2 1 110 52428800 512 2 NO CURRENT 2051947 2018-12-04:01:24:16 2.8147E+14
3 1 108 52428800 512 2 YES INACTIVE 2026853 2017-05-22:20:41:42 2047121 2018-12-04:01:18:27
SQL> alter database drop logfile group 1;
Database altered.
SQL> alter database add logfile group 1;
Database altered.
SQL> alter database drop logfile group 3;
Database altered.
SQL> alter database add logfile group 3;
Database altered.
SQL> select member from v$logfile;
MEMBER
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
/u01/app/oracle/oradata/ORCL/onlinelog/o1_mf_3_g0bt8qy3_.log
/u01/app/oracle/fast_recovery_area/ORCL/onlinelog/o1_mf_3_g0bt8r41_.log
+DATA/orcl/onlinelog/group_2.262.816169639
+FRA/orcl/onlinelog/group_2.258.816169639
/u01/app/oracle/oradata/ORCL/onlinelog/o1_mf_1_g0bt66j9_.log
/u01/app/oracle/fast_recovery_area/ORCL/onlinelog/o1_mf_1_g0bt66n3_.log
6 rows selected.
SQL> alter system switch logfile;
System altered.
SQL> alter system checkpoint;
System altered.
SQL> alter database drop logfile group 2;
Database altered.
SQL> alter database add logfile group 2;
Database altered.
SQL> select member from v$logfile;
MEMBER
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
/u01/app/oracle/oradata/ORCL/onlinelog/o1_mf_3_g0bt8qy3_.log
/u01/app/oracle/fast_recovery_area/ORCL/onlinelog/o1_mf_3_g0bt8r41_.log
/u01/app/oracle/oradata/ORCL/onlinelog/o1_mf_2_g0btbg11_.log
/u01/app/oracle/fast_recovery_area/ORCL/onlinelog/o1_mf_2_g0btbg4v_.log
/u01/app/oracle/oradata/ORCL/onlinelog/o1_mf_1_g0bt66j9_.log
/u01/app/oracle/fast_recovery_area/ORCL/onlinelog/o1_mf_1_g0bt66n3_.log
6 rows selected.
SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount
ORACLE instance started.
Total System Global Area 6664212480 bytes
Fixed Size 2240944 bytes
Variable Size 3640659536 bytes
Database Buffers 3003121664 bytes
Redo Buffers 18190336 bytes
Database mounted.
SQL> show parameter db_recovery
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_recovery_file_dest string /u01/app/oracle/fast_recovery_
area
db_recovery_file_dest_size big integer 3882M
SQL> alter system set db_recovery_file_dest='/u01/app/oracle/oradata';
System altered.
SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, Automatic Storage Management, OLAP, Data Mining
and Real Application Testing options
[oracle@station90 ~]$ rman target /
Recovery Manager: Release 11.2.0.3.0 - Production on Tue Dec 4 01:37:38 2018
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
connected to target database: ORCL (DBID=1343950367, not open)
RMAN> backup as copy database;
Starting backup at 2018-12-04:01:37:44
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=133 device type=DISK
allocated channel: ORA_DISK_2
channel ORA_DISK_2: SID=193 device type=DISK
allocated channel: ORA_DISK_3
channel ORA_DISK_3: SID=9 device type=DISK
allocated channel: ORA_DISK_4
channel ORA_DISK_4: SID=70 device type=DISK
allocated channel: ORA_DISK_5
channel ORA_DISK_5: SID=134 device type=DISK
allocated channel: ORA_DISK_6
channel ORA_DISK_6: SID=194 device type=DISK
allocated channel: ORA_DISK_7
channel ORA_DISK_7: SID=10 device type=DISK
allocated channel: ORA_DISK_8
channel ORA_DISK_8: SID=71 device type=DISK
channel ORA_DISK_1: starting datafile copy
input datafile file number=00001 name=+DATA/orcl/datafile/system.256.816169553
channel ORA_DISK_2: starting datafile copy
input datafile file number=00003 name=+DATA/orcl/datafile/undotbs1.258.816169553
channel ORA_DISK_3: starting datafile copy
input datafile file number=00002 name=+DATA/orcl/datafile/sysaux.257.816169553
channel ORA_DISK_4: starting datafile copy
input datafile file number=00005 name=+DATA/orcl/datafile/example.265.816169651
channel ORA_DISK_5: starting datafile copy
input datafile file number=00004 name=+DATA/orcl/datafile/users.259.816169553
output file name=/u01/app/oracle/oradata/ORCL/datafile/o1_mf_users_g0bthdk9_.dbf tag=TAG20181204T013746 RECID=3 STAMP=993951468
channel ORA_DISK_5: datafile copy complete, elapsed time: 00:00:01
output file name=/u01/app/oracle/oradata/ORCL/datafile/o1_mf_example_g0bthd4p_.dbf tag=TAG20181204T013746 RECID=4 STAMP=993951469
channel ORA_DISK_4: datafile copy complete, elapsed time: 00:00:04
output file name=/u01/app/oracle/oradata/ORCL/datafile/o1_mf_system_g0bthc9n_.dbf tag=TAG20181204T013746 RECID=6 STAMP=993951476
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:16
output file name=/u01/app/oracle/oradata/ORCL/datafile/o1_mf_undotbs1_g0bthcj0_.dbf tag=TAG20181204T013746 RECID=7 STAMP=993951477
channel ORA_DISK_2: datafile copy complete, elapsed time: 00:00:16
output file name=/u01/app/oracle/oradata/ORCL/datafile/o1_mf_sysaux_g0bthcqp_.dbf tag=TAG20181204T013746 RECID=5 STAMP=993951476
channel ORA_DISK_3: datafile copy complete, elapsed time: 00:00:16
Finished backup at 2018-12-04:01:38:03
Starting Control File and SPFILE Autobackup at 2018-12-04:01:38:03
piece handle=/u01/app/oracle/oradata/ORCL/autobackup/2018_12_04/o1_mf_s_993951364_g0bthwfc_.bkp comment=NONE
Finished Control File and SPFILE Autobackup at 2018-12-04:01:38:04
RMAN> report schema;
Report of database schema for database with db_unique_name ORCL
List of Permanent Datafiles
===========================
File Size(MB) Tablespace RB segs Datafile Name
---- -------- -------------------- ------- ------------------------
1 730 SYSTEM *** +DATA/orcl/datafile/system.256.816169553
2 600 SYSAUX *** +DATA/orcl/datafile/sysaux.257.816169553
3 650 UNDOTBS1 *** +DATA/orcl/datafile/undotbs1.258.816169553
4 5 USERS *** +DATA/orcl/datafile/users.259.816169553
5 100 EXAMPLE *** +DATA/orcl/datafile/example.265.816169651
List of Temporary Files
=======================
File Size(MB) Tablespace Maxsize(MB) Tempfile Name
---- -------- -------------------- ----------- --------------------
1 71 TEMP 32767 +DATA/orcl/tempfile/temp.264.816169645
RMAN> switch database to copy;
datafile 1 switched to datafile copy "/u01/app/oracle/oradata/ORCL/datafile/o1_mf_system_g0bthc9n_.dbf"
datafile 2 switched to datafile copy "/u01/app/oracle/oradata/ORCL/datafile/o1_mf_sysaux_g0bthcqp_.dbf"
datafile 3 switched to datafile copy "/u01/app/oracle/oradata/ORCL/datafile/o1_mf_undotbs1_g0bthcj0_.dbf"
datafile 4 switched to datafile copy "/u01/app/oracle/oradata/ORCL/datafile/o1_mf_users_g0bthdk9_.dbf"
datafile 5 switched to datafile copy "/u01/app/oracle/oradata/ORCL/datafile/o1_mf_example_g0bthd4p_.dbf"
RMAN> report schema;
Report of database schema for database with db_unique_name ORCL
List of Permanent Datafiles
===========================
File Size(MB) Tablespace RB segs Datafile Name
---- -------- -------------------- ------- ------------------------
1 730 SYSTEM *** /u01/app/oracle/oradata/ORCL/datafile/o1_mf_system_g0bthc9n_.dbf
2 600 SYSAUX *** /u01/app/oracle/oradata/ORCL/datafile/o1_mf_sysaux_g0bthcqp_.dbf
3 650 UNDOTBS1 *** /u01/app/oracle/oradata/ORCL/datafile/o1_mf_undotbs1_g0bthcj0_.dbf
4 5 USERS *** /u01/app/oracle/oradata/ORCL/datafile/o1_mf_users_g0bthdk9_.dbf
5 100 EXAMPLE *** /u01/app/oracle/oradata/ORCL/datafile/o1_mf_example_g0bthd4p_.dbf
List of Temporary Files
=======================
File Size(MB) Tablespace Maxsize(MB) Tempfile Name
---- -------- -------------------- ----------- --------------------
1 71 TEMP 32767 +DATA/orcl/tempfile/temp.264.816169645
RMAN> exit
Recovery Manager complete.
[oracle@station90 ~]$ sqlplus /nolog
SQL*Plus: Release 11.2.0.3.0 Production on Tue Dec 4 01:40:19 2018
Copyright (c) 1982, 2011, Oracle. All rights reserved.
SQL> conn / as sysdba
Connected.
SQL> show parameter control
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
control_file_record_keep_time integer 7
control_files string +DATA/orcl/controlfile/current
.260.816169631, +FRA/orcl/cont
rolfile/current.256.816169633
control_management_pack_access string DIAGNOSTIC+TUNING
SQL> alter system set db_recovery_file_dest='/u01/app/oracle/fast_recovery_area';
System altered.
SQL> alter system set control_files='' scope=spfile;
System altered.
SQL> alter database backup controlfile to trace as '/home/oracle/control8c.sql';
Database altered.
SQL> shutdown immediate
ORA-01109: database not open
Database dismounted.
ORACLE instance shut down.
SQL> startup
ORACLE instance started.
Total System Global Area 6664212480 bytes
Fixed Size 2240944 bytes
Variable Size 3640659536 bytes
Database Buffers 3003121664 bytes
Redo Buffers 18190336 bytes
ORA-03113: end-of-file on communication channel
Process ID: 31083
Session ID: 191 Serial number: 1
SQL> startup nomount
ORA-24324: service handle not initialized
ORA-01041: internal error. hostdef extension doesn't exist
SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
[oracle@station90 ~]$ sqlplus /nolog
SQL*Plus: Release 11.2.0.3.0 Production on Tue Dec 4 01:43:46 2018
Copyright (c) 1982, 2011, Oracle. All rights reserved.
SQL> conn / as sysdba
Connected.
SQL> @control8c.sql
Control file created.
PL/SQL procedure successfully completed.
PL/SQL procedure successfully completed.
PL/SQL procedure successfully completed.
PL/SQL procedure successfully completed.
PL/SQL procedure successfully completed.
PL/SQL procedure successfully completed.
PL/SQL procedure successfully completed.
PL/SQL procedure successfully completed.
SQL> show parameter control
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
control_file_record_keep_time integer 7
control_files string /u01/app/oracle/oradata/ORCL/c
ontrolfile/o1_mf_g0bttqkw_.ctl
, /u01/app/oracle/fast_recover
y_area/ORCL/controlfile/o1_mf_
g0bttqmm_.ctl
control_management_pack_access string DIAGNOSTIC+TUNING
SQL> alter database open ;
Database altered.
SQL> select * from v$tempfile;
no rows selected
SQL> alter tablespace temp add tempfile size 20M autoextend on;
Tablespace altered.
SQL>
[oracle@station90 ~]$ sqlplus /nolog
SQL*Plus: Release 11.2.0.3.0 Production on Tue Dec 4 01:42:55 2018
Copyright (c) 1982, 2011, Oracle. All rights reserved.
SQL> conn / as sysdba
Connected to an idle instance.
SQL> startup nomount
ORACLE instance started.
Total System Global Area 6664212480 bytes
Fixed Size 2240944 bytes
Variable Size 3640659536 bytes
Database Buffers 3003121664 bytes
Redo Buffers 18190336 bytes
SQL> show parameter spfile
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
spfile string +DATA/orcl/spfileorcl.ora
SQL> create pfile from spfile;
File created.
SQL> create spfile from pfile;
File created.
SQL> shutdwon immediate ;
SP2-0734: unknown command beginning "shutdwon i..." - rest of line ignored.
SQL> shutdown immediate ;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL>

复制代码

课程第28/29次（2018-12-1星期六上下午）

1Z0-053第19章（32/40）

1Z0-053第20章（33/40）

run{
duplicate target database to dbclone2
from active database
nofilenamecheck
skip tablespace 'TBS1','TBS2'
spfile
set
control_files='/u01/app/oracle/oradata/dbclone2/control01.ctl','/u01/app/oracle/oradata/dbclone2/control02.ctl','/u01/app/oracle/oradata/dbclone2/control03.ctl'
set
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'
set
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'
set
audit_file_dest='/u01/app/oracle/admin/dbclone2/adump'
set
db_create_file_dest=''
set
db_recovery_file_dest='/u01/app/oracle/fast_recovery_area'
set
memory_target='4G'
;
}

复制代码

为了backup location，而做的备份：

--源头把备份优化关掉，或用force语法
run {
allocate channel c1 device type disk format '/home/oracle/backup/%U';
allocate channel c2 device type disk format '/home/oracle/backup/%U';
allocate channel c3 device type disk format '/home/oracle/backup/%U';
allocate channel c4 device type disk format '/home/oracle/backup/%U';
allocate channel c5 device type disk format '/home/oracle/backup/%U';
allocate channel c6 device type disk format '/home/oracle/backup/%U';
allocate channel c7 device type disk format '/home/oracle/backup/%U';
allocate channel c8 device type disk format '/home/oracle/backup/%U';
backup database plus archivelog force;
backup spfile;
backup current controlfile;
}

复制代码

在目的地（auxiliary)：

run{
allocate auxiliary channel c1 device type disk;
allocate auxiliary channel c2 device type disk;
allocate auxiliary channel c3 device type disk;
allocate auxiliary channel c4 device type disk;
allocate auxiliary channel c5 device type disk;
allocate auxiliary channel c6 device type disk;
allocate auxiliary channel c7 device type disk;
allocate auxiliary channel c8 device type disk;
duplicate target database to mydb
backup location '/home/oracle/backup'
nofilenamecheck
spfile
set
control_files='/u01/app/oracle/oradata/mydb/control01.ctl','/u01/app/oracle/oradata/db11g/control02.ctl','/u01/app/oracle/oradata/db11g/control03.ctl'
set
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'
set
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'
set
audit_file_dest='/u01/app/oracle/admin/mydb/adump'
set
db_create_file_dest=''
set
db_recovery_file_dest='/u01/app/oracle/flash_recovery_area'
}

复制代码

1Z0-053第12章（34/40）

1Z0-053第10章（35/40）
闪回1（它是闪回8的导航）：

select * from flashback_transaction_query;
select * from v$transaction;

复制代码

闪回2：

[oracle@station90 ~]$ sqlplus /nolog
SQL*Plus: Release 11.2.0.3.0 Production on Tue Dec 4 21:47:02 2018
SQL> conn hr/oracle_4U
ERROR:
ORA-28002: the password will expire within 7 days
Connected.
SQL> select salary from employees where employee_id=100;
SALARY
----------
24000
SQL> select to_char(sysdate,'YYYY-MM-DD:HH24:MI:SS') from dual;
TO_CHAR(SYSDATE,'YY
-------------------
2018-12-04:21:47:44
SQL> update employees set salary=24001 where employee_id=100;
1 row updated.
SQL> commit;
Commit complete.
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;
SALARY
----------
24000
SQL>

复制代码

闪回3（它是闪回4的导航）：

select versions_xid,
versions_startscn,
versions_starttime,
versions_operation,
salary
from hr.employees
versions between scn minvalue and maxvalue
where employee_id=100;

复制代码

闪回4：

SQL> show user
USER is "HR"
SQL> select salary from employees
2 as of scn 2098534
3 where employee_id=100;
SALARY
----------
24003
SQL> select salary from employees
2 as of scn 2098533
3 where employee_id=100;
SALARY
----------
24002
SQL> flashback table employees to scn 2098534;
flashback table employees to scn 2098534
*
ERROR at line 1:
ORA-08189: cannot flashback the table because row movement is not enabled
SQL> alter table employees enable row movement ;
Table altered.
SQL> select salary from employees where emplpoyee_id=100;
select salary from employees where emplpoyee_id=100
*
ERROR at line 1:
ORA-00904: "EMPLPOYEE_ID": invalid identifier
SQL> select salary from employees where employee_id=100;
SALARY
----------
24005
SQL> flashback table employees to scn 2098534;
Flashback complete.
SQL> select salary from employees where employee_id=100;
SALARY
----------
24003
SQL>

复制代码

闪回8：

SUPPLEMENTAL_LOG_DATA: alter database add supplemental log data
SUPPLEMENTAL_LOG_DATA_PK: alter database add supplemental log data (primary key) columns

复制代码

t05310.sql (388 Bytes, 下载次数: 65)

2018-12-2 09:27 上传

点击文件名下载附件

select * from flashback_transaction_query ftq
where ftq.logon_user='HR' and ftq.table_name='T_NOCASCADE'
order by 4 ;

复制代码

select * from flashback_transaction_query ftq
where ftq.logon_user='HR' and ftq.table_name='T_NOCASCADE'
order by 4 ;
begin
dbms_flashback.transaction_backout(numtxns => 1,
xids => xid_array('0100150070050000'),
options => dbms_flashback.nocascade);
end;

复制代码

select * from flashback_transaction_query ftq
where ftq.logon_user='HR' and ftq.table_name='T_CASCADE'
order by 4 ;
begin
dbms_flashback.transaction_backout(numtxns => 1,
xids => xid_array('05001400AB060000'),
options => dbms_flashback.cascade);
end;
select * from dba_flashback_txn_report;
select * from hr.t_cascade;
commit;

复制代码

select * from flashback_transaction_query ftq
where ftq.logon_user='HR' and ftq.table_name='T_NONCONFLICT_ONLY'
order by 4 ;
begin
dbms_flashback.transaction_backout(numtxns => 1,
xids => xid_array('01000C0070050000'),
options => dbms_flashback.nonconflict_only);
end;
select * from dba_flashback_txn_report;
select * from hr.t_nonconflict_only;
commit;

复制代码

select * from flashback_transaction_query ftq
where ftq.logon_user='HR' and ftq.table_name='T_NOCASCADE_FORCE'
order by 4 ;
begin
dbms_flashback.transaction_backout(numtxns => 1,
xids => xid_array('03002100FC050000'),
options => dbms_flashback.nocascade_force);
end;
select * from dba_flashback_txn_report;
select * from hr.t_nocascade_force;
commit;

复制代码

课程第30次（2018-12-2星期日上午）
1Z0-053第11章（36/40）

闪回7：

select * from dba_flashback_archive;
select * from dba_flashback_archive_ts;
select * from dba_flashback_archive_tables;
create flashback archive fda1 tablespace users
quota 10M retention 2 year;
alter flashback archive fda1 modify retention 40 day;
alter flashback archive fda1 modify tablespace users quota 20M ;
alter flashback archive fda1 add tablespace example quota 10M ;
alter flashback archive fda1 set default;
grant flashback archive on fda1 to hr;

复制代码

[oracle@station90 ~]$ sqlplus /nolog
SQL*Plus: Release 11.2.0.3.0 Production on Wed Dec 5 14:34:47 2018
SQL> conn hr/oracle_4U
ERROR:
ORA-28002: the password will expire within 6 days
Connected.
SQL> create table part1 ( a number )
2 partition by range (a)
3 ( partition p1 values less than (100) ,
4 partition p2 values less than (maxvalue)) ;
Table created.
SQL> insert into part1 values ( 100) ;
1 row created.
SQL> commit;
Commit complete.
SQL> insert into part1 values ( 1) ;
1 row created.
SQL> commit;
Commit complete.
SQL> select * from part1;
A
----------
1
100
SQL> select * from part1 partition (p1);
A
----------
1
SQL> select * from part1 partition (p2);
A
----------
100
SQL> alter table flashback archive ;
alter table flashback archive
*
ERROR at line 1:
ORA-01735: invalid ALTER TABLE option
SQL> alter table part1 flashback archive ;
Table altered.
SQL>

复制代码

闪回5：

SQL> select * from SYS_FBA_HIST_80638;
select * from SYS_FBA_HIST_80638
*
ERROR at line 1:
ORA-00942: table or view does not exist
SQL> select * from SYS_FBA_HIST_80638;
select * from SYS_FBA_HIST_80638
*
ERROR at line 1:
ORA-00942: table or view does not exist
SQL> select * from SYS_FBA_HIST_80638;
RID
--------------------------------------------------------------------------------
STARTSCN ENDSCN XID O A
---------- ---------- ---------------- - ----------
AAATr/AAEAAAAaSAAA
2155660 1
SQL> update SYS_FBA_HIST_80638 set a=2 where a=1;
update SYS_FBA_HIST_80638 set a=2 where a=1
*
ERROR at line 1:
ORA-55622: DML, ALTER and CREATE UNIQUE INDEX operations are not allowed on
table "HR"."SYS_FBA_HIST_80638"
SQL> select * from part1 (p1) ;
select * from part1 (p1)
*
ERROR at line 1:
ORA-00933: SQL command not properly ended
SQL> select * from part1 partiton (p1) ;
select * from part1 partiton (p1)
*
ERROR at line 1:
ORA-00933: SQL command not properly ended
SQL> select * from part1 partition (p1) ;
A
----------
5
SQL> select * from part1 partition (p1) as of scn 2155660;
A
----------
5
SQL> select * from part1 partition (p1) as of scn 2155659;
A
----------
1
SQL> alter table part1 split partition p1 at (5) into ( part11 , part12 ) ;
alter table part1 split partition p1 at (5) into ( part11 , part12 )
*
ERROR at line 1:
ORA-14004: missing PARTITION keyword
SQL> alter table part1 split partition p1 at (5) into ( partition part11 , partition part12 ) ;
alter table part1 split partition p1 at (5) into ( partition part11 , partition part12 )
*
ERROR at line 1:
ORA-55610: Invalid DDL statement on history-tracked table
SQL>

复制代码

SQL>
SQL> update SYS_FBA_HIST_80638 set a=2 where a=1;
1 row updated.
SQL> commit;
Commit complete.
SQL> alter table part1 split partition p1 at (5) into ( partition part11 , partition part12 ) ;
Table altered.
SQL> select * from part1 partition (p1) as of scn 2155659;
select * from part1 partition (p1) as of scn 2155659
*
ERROR at line 1:
ORA-02149: Specified partition does not exist
SQL> select * from part1 partition (p11) as of scn 2155659;
select * from part1 partition (p11) as of scn 2155659
*
ERROR at line 1:
ORA-02149: Specified partition does not exist
SQL> select * from part1 as of scn 2155659;
A
----------
2
100
SQL>

复制代码

select * from dba_flashback_archive_tables;
select * from dba_tab_partitions tp
where tp.table_owner='HR' and tp.table_name='PART1';
begin
dbms_flashback_archive.disassociate_fba(owner_name => 'HR',
table_name => 'PART1');
end;
begin
dbms_flashback_archive.reassociate_fba(owner_name => 'HR',
table_name => 'PART1');
end;

复制代码

SQL> create table t05311( a number ) ;
Table created.
SQL> insert into t05311 values ( 1 ) ;
1 row created.
SQL> commit;
Commit complete.
SQL> select * from t05311;
A
----------
1
SQL> drop table t05311;
Table dropped.
SQL> show recyclebin
ORIGINAL NAME RECYCLEBIN NAME OBJECT TYPE DROP TIME
---------------- ------------------------------ ------------ -------------------
T05311 BIN$fEH7tH4zYC3gU1oAqMD+yA==$0 TABLE 2018-12-05:15:03:46
SQL> select * from "BIN$fEH7tH4zYC3gU1oAqMD+yA==$0";
A
----------
1
SQL> create table t05311( a date ) ;
Table created.
SQL> insert into t05311 values ( sysdate ) ;
1 row created.
SQL> commit;
Commit complete.
SQL> drop table t05311;
Table dropped.
SQL> create table t05311( a varchar2(20) ) ;
Table created.
SQL> insert into t05311 values ( 'A' ) ;
1 row created.
SQL> commit;
Commit complete.
SQL> show recyclebin
ORIGINAL NAME RECYCLEBIN NAME OBJECT TYPE DROP TIME
---------------- ------------------------------ ------------ -------------------
T05311 BIN$fEH7tH40YC3gU1oAqMD+yA==$0 TABLE 2018-12-05:15:05:23
T05311 BIN$fEH7tH4zYC3gU1oAqMD+yA==$0 TABLE 2018-12-05:15:03:46
SQL> drop table t05311;
Table dropped.
SQL> show recyclebin
ORIGINAL NAME RECYCLEBIN NAME OBJECT TYPE DROP TIME
---------------- ------------------------------ ------------ -------------------
T05311 BIN$fEH7tH41YC3gU1oAqMD+yA==$0 TABLE 2018-12-05:15:05:56
T05311 BIN$fEH7tH40YC3gU1oAqMD+yA==$0 TABLE 2018-12-05:15:05:23
T05311 BIN$fEH7tH4zYC3gU1oAqMD+yA==$0 TABLE 2018-12-05:15:03:46
SQL> select * from "BIN$fEH7tH40YC3gU1oAqMD+yA==$0";
A
-------------------
2018-12-05:15:05:09
SQL> flashback table T05311 to before drop ;
Flashback complete.
SQL> select * from T05311;
A
--------------------
A
SQL> flashback table T05311 to before drop ;
flashback table T05311 to before drop
*
ERROR at line 1:
ORA-38312: original name is used by an existing object
SQL> flashback table "BIN$fEH7tH4zYC3gU1oAqMD+yA==$0" to before drop rename to t05311_a;
Flashback complete.
SQL> select * from t05311_a;
A
----------
1
SQL> show recyclebin
ORIGINAL NAME RECYCLEBIN NAME OBJECT TYPE DROP TIME
---------------- ------------------------------ ------------ -------------------
T05311 BIN$fEH7tH40YC3gU1oAqMD+yA==$0 TABLE 2018-12-05:15:05:23
SQL> show recyclebin
SQL>

复制代码

failure +  incident + problem  都来自ADR

failuser:  v$IR_xxx  和rman命令, 为了自救

incident ->problem：em  的support workbench，为了my oracle support预处理

1Z0-052第16章（37/40）
1Z0-053第9章（38/40）

physical-block.sh (1.25 KB, 下载次数: 63)

jarlean · 发表于 2018-12-3 10:44:56

本帖最后由 jarlean 于 2018-12-3 10:50 编辑

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

查询块号：

SQL> select DBMS_ROWID.ROWID_BLOCK_NUMBER(ROWID),t.* from HR.DEPARTMENTS t;
DBMS_ROWID.ROWID_BLOCK_NUMBER(ROWID) DEPARTMENT_ID DEPARTMENT_NAME MANAGER_ID LOCATION_ID
------------------------------------ ------------- ------------------------------ ---------- -----------
491 10 Administration 200 1700
491 20 Marketing 201 1800
491 30 Purchasing 114 1700
491 40 Human Resources 203 2400
491 50 Shipping 121 1500
491 60 IT 103 1400
491 70 Public Relations 204 2700
491 80 Sales 145 2500
491 90 Executive 100 1700
491 100 Finance 108 1700
491 110 Accounting 205 1700

复制代码

备份数据文件：

RMAN> backup tag='1T_TABSPACE_EXAMPLE' datafile 5;
Starting backup at 2018-12-01:14:26:30
using channel ORA_DISK_1
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00005 name=+DATA/orcl/datafile/example.265.993736237
channel ORA_DISK_1: starting piece 1 at 2018-12-01:14:26:30
channel ORA_DISK_1: finished piece 1 at 2018-12-01:14:26:46
piece handle=+FRA/orcl/backupset/2018_12_01/nnndf0_1t_tabspace_example_0.350.993738391 tag=1T_TABSPACE_EXAMPLE comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:16
Finished backup at 2018-12-01:14:26:46

复制代码

破坏块：

[root@station80 oracle]# sh physical-block11.2.0.1.sh

复制代码

破坏后，修复前：

SQL> select sysdate from dual;
SYSDATE
-------------------
2018-12-01:14:28:58
SQL> select DBMS_ROWID.ROWID_BLOCK_NUMBER(ROWID),t.* from HR.DEPARTMENTS t;
select DBMS_ROWID.ROWID_BLOCK_NUMBER(ROWID),t.* from HR.DEPARTMENTS t
*
ERROR at line 1:
ORA-01578: ORACLE data block corrupted (file # 5, block # 491)
ORA-01110: data file 5: '+DATA/orcl/datafile/example.265.993738467'

复制代码

修复：

RMAN> list failure;
List of Database Failures
=========================
Failure ID Priority Status Time Detected Summary
---------- -------- --------- ------------------- -------
3727 HIGH OPEN 2018-12-01:11:44:11 Datafile 5: '+DATA/orcl/datafile/example.265.993728527' contains one or more corrupt blocks
RMAN> advise failure;
List of Database Failures
=========================
Failure ID Priority Status Time Detected Summary
---------- -------- --------- ------------------- -------
3727 HIGH OPEN 2018-12-01:11:44:11 Datafile 5: '+DATA/orcl/datafile/example.265.993728527' contains one or more corrupt blocks
analyzing automatic repair options; this may take some time
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=202 device type=DISK
analyzing automatic repair options complete
Mandatory Manual Actions
========================
no manual actions available
Optional Manual Actions
=======================
no manual actions available
Automated Repair Options
========================
Option Repair Description
------ ------------------
1 Recover multiple corrupt blocks in datafile 5
Strategy: The repair includes complete media recovery with no data loss
Repair script: /u01/app/oracle/diag/rdbms/orcl/orcl/hm/reco_1535992553.hm
RMAN> repair failure preview;
Strategy: The repair includes complete media recovery with no data loss
Repair script: /u01/app/oracle/diag/rdbms/orcl/orcl/hm/reco_1535992553.hm
contents of repair script:
# block media recovery for multiple blocks
recover datafile 5 block 175, 491;
RMAN> recover datafile 5 block 491;
Starting recover at 2018-12-01:14:31:47
using channel ORA_DISK_1
searching flashback logs for block images until SCN 1458477
finished flashback log search, restored 0 blocks
channel ORA_DISK_1: restoring block(s)
channel ORA_DISK_1: specifying block(s) to restore from backup set
restoring blocks of datafile 00005
channel ORA_DISK_1: reading from backup piece +FRA/orcl/backupset/2018_12_01/nnndf0_1t_tabspace_example_0.350.993738391
channel ORA_DISK_1: piece handle=+FRA/orcl/backupset/2018_12_01/nnndf0_1t_tabspace_example_0.350.993738391 tag=1T_TABSPACE_EXAMPLE
channel ORA_DISK_1: restored block(s) from backup piece 1
channel ORA_DISK_1: block restore complete, elapsed time: 00:00:03
starting media recovery
media recovery complete, elapsed time: 00:00:03
Finished recover at 2018-12-01:14:32:02

复制代码

修复后：

SQL> select sysdate from dual;
SYSDATE
-------------------
2018-12-01:14:32:25
SQL> select DBMS_ROWID.ROWID_BLOCK_NUMBER(ROWID),t.* from HR.DEPARTMENTS t;
DBMS_ROWID.ROWID_BLOCK_NUMBER(ROWID) DEPARTMENT_ID DEPARTMENT_NAME MANAGER_ID LOCATION_ID
------------------------------------ ------------- ------------------------------ ---------- -----------
491 10 Administration 200 1700
491 20 Marketing 201 1800
491 30 Purchasing 114 1700
491 40 Human Resources 203 2400
491 50 Shipping 121 1500
491 60 IT 103 1400
491 70 Public Relations 204 2700
491 80 Sales 145 2500
491 90 Executive 100 1700
491 100 Finance 108 1700
491 110 Accounting 205 1700

复制代码