|
1Z0-053第8章
1Z0-052共19章(上完13章),1Z0-053共21章(上完14章)和1Z0-063多租户部分共9章(上完0章)
总共上完全部49章中的27章
- select * from repair_table;
- select * from orphan_key_table;
- BEGIN
- DBMS_REPAIR.ADMIN_TABLES (
- table_name => 'REPAIR_TABLE',
- table_type => DBMS_REPAIR.REPAIR_TABLE,
- action => DBMS_REPAIR.CREATE_ACTION,
- tablespace => 'USERS');
- END;
- BEGIN
- DBMS_REPAIR.ADMIN_TABLES (
- table_name => 'ORPHAN_KEY_TABLE',
- table_type => DBMS_REPAIR.ORPHAN_TABLE,
- action => DBMS_REPAIR.CREATE_ACTION,
- tablespace => 'USERS');
- END;
- ----
- DECLARE
- num_corrupt INT;
- BEGIN
- num_corrupt := 0;
- DBMS_REPAIR.CHECK_OBJECT (
- schema_name => 'HR',
- object_name => 'TLOGICAL',
- repair_table_name => 'REPAIR_TABLE',
- corrupt_count => num_corrupt);
- dbms_output.put_line( 'Find '|| num_corrupt||' Bolcks.' );
- END;
- -------
- /*
- DECLARE
- num_fix INT;
- BEGIN
- num_fix := 0;
- DBMS_REPAIR.FIX_CORRUPT_BLOCKS (
- schema_name => 'HR',
- object_name => 'TLOGICAL',
- object_type => DBMS_REPAIR.TABLE_OBJECT,
- repair_table_name => 'REPAIR_TABLE',
- fix_count => num_fix);
- dbms_output.put_line( 'Find '|| num_fix ||' Bolcks.' );
- END;
- */
- select * from repair_table;
- select * from orphan_key_table;
- -----
- DECLARE
- num_orphans INT;
- BEGIN
- num_orphans := 0;
- DBMS_REPAIR.DUMP_ORPHAN_KEYS (
- schema_name => 'HR',
- object_name => 'ILOGICAL_A',
- object_type => DBMS_REPAIR.INDEX_OBJECT,
- repair_table_name => 'REPAIR_TABLE',
- orphan_table_name => 'ORPHAN_KEY_TABLE',
- key_count => num_orphans);
- DBMS_OUTPUT.PUT_LINE('orphan key count: ' || TO_CHAR(num_orphans));
- END;
- ----
- select * from orphan_key_table;
- ----
- DECLARE
- num_orphans INT;
- BEGIN
- num_orphans := 0;
- DBMS_REPAIR.DUMP_ORPHAN_KEYS (
- schema_name => 'HR',
- object_name => 'ILOGICAL_B',
- object_type => DBMS_REPAIR.INDEX_OBJECT,
- repair_table_name => 'REPAIR_TABLE',
- orphan_table_name => 'ORPHAN_KEY_TABLE',
- key_count => num_orphans);
- DBMS_OUTPUT.PUT_LINE('orphan key count: ' || TO_CHAR(num_orphans));
- END;
- ----
- select * from orphan_key_table;
- select dump(key) from orphan_key_table
- where keyrowid||' '='AAATrIAIgAAAIABAAA'||' '
- and index_name='ILOGICAL_B';
- --196 41 2 86 11
- --40018510
-
- select dump(key) from orphan_key_table
- where keyrowid||' '='AAATrIAIgAAAIABAAA'||' '
- and index_name='ILOGICAL_A';
-
- --- 108 99 119 99 108 122 99 122 108
- --- l c w c l z c z l
-
-
- begin
- dbms_repair.skip_corrupt_blocks(schema_name => 'HR',
- object_name => 'TLOGICAL');
- end;
-
- begin
- dbms_stats.gather_table_stats('HR','TLOGICAL');
- end;
-
-
-
- select i.index_name, i.status
- from dba_indexes i
- where i.table_owner='HR' and i.table_name='TLOGICAL';
-
- -- alter index ilogical rebuild online;
-
- drop index hr.ilogical_a;
-
- drop index hr.ilogical_b;
-
- create index hr.ilogical_a on hr.tlogical(a) ;
-
- create index hr.ilogical_b on hr.tlogical(b) ;
-
-
-
-
-
复制代码- SQL> select rowid from tlogical;
- ERROR:
- ORA-01578: ORACLE data block corrupted (file # 6, block # 136)
- ORA-01110: data file 6: '+DATA/orcl/datafile/tbslogical.267.955745025'
- ORA-26040: Data block was loaded using the NOLOGGING option
- no rows selected
- SQL> dsc tlogical
- SP2-0734: unknown command beginning "dsc tlogic..." - rest of line ignored.
- SQL> desc tlogical
- Name Null? Type
- ----------------------------------------- -------- ----------------------------
- A VARCHAR2(20)
- B NUMBER
- SQL> select dump(1) from dual;
- DUMP(1)
- ------------------
- Typ=2 Len=2: 193,2
- SQL> select dump(10) from dual;
- DUMP(10)
- -------------------
- Typ=2 Len=2: 193,11
- SQL> select dump(100) from dual;
- DUMP(100)
- ------------------
- Typ=2 Len=2: 194,2
- SQL> select dump(1000) from dual;
- DUMP(1000)
- -------------------
- Typ=2 Len=2: 194,11
- SQL> select dump(110) from dual;
- DUMP(110)
- ---------------------
- Typ=2 Len=3: 194,2,11
- SQL> select dump(0) from dual;
- DUMP(0)
- ----------------
- Typ=2 Len=1: 128
- SQL> select dump(-1) from dual;
- DUMP(-1)
- -----------------------
- Typ=2 Len=3: 62,100,102
- SQL> select dump(-10) from dual;
- DUMP(-10)
- ----------------------
- Typ=2 Len=3: 62,91,102
- SQL> select dump(-100) from dual;
- DUMP(-100)
- -----------------------
- Typ=2 Len=3: 61,100,102
- SQL> select dump(-1000) from dual;
- DUMP(-1000)
- ----------------------
- Typ=2 Len=3: 61,91,102
- SQL> select dump(-110) from dual;
- DUMP(-110)
- --------------------------
- Typ=2 Len=4: 61,100,91,102
- SQL> select dump(123456.789) from dual;
- DUMP(123456.789)
- -------------------------------
- Typ=2 Len=6: 195,13,35,57,79,91
- SQL> select dump(-123456.789) from dual;
- DUMP(-123456.789)
- ----------------------------------
- Typ=2 Len=7: 60,89,67,45,23,11,102
- SQL> select dump(-123456.78901) from dual;
- DUMP(-123456.78901)
- -------------------------------------
- Typ=2 Len=8: 60,89,67,45,23,11,91,102
- SQL>
复制代码
--------------------------------------------------------------------------
- SELECT SID, SPID, CLIENT_INFO
- FROM V$PROCESS p, V$SESSION s
- WHERE p.ADDR = s.PADDR
- AND CLIENT_INFO LIKE '%id=zhang3%';
- SELECT SID, SPID, CLIENT_INFO
- FROM V$PROCESS p, V$SESSION s
- WHERE p.ADDR = s.PADDR
- AND s.program LIKE '%rman%';
-
-
- select * from v_$session_longops s
- where s.SID in ( 139 , 14 , 200 );
-
-
- select * from v$backup_sync_io;
-
- select * from v$backup_async_io;
-
复制代码
关于备份优化:
Each file need at least 4 buffers!
DISK Read(Backup/Restore)
Max4files_16Mchannel
1. buffer=1Mbuffer
2. 1file=16*1Mbuffer
2files=8*1Mbuffer+8*1Mbuffer
3files=8*1Mbuffer+4*1Mbuffer+4*1Mbuffer
4files=4*1Mbuffer+4*1Mbuffer+4*1Mbuffer+4*1Mbuffer
5-Max8files_less16Mchannel
1. buffer=512Kbuffer
2. 5files=5*4*512Kbuffer=10Mchannel
3. 6files=6*4*512Kbuffer=12Mchannel
4. 7files=7*4*512Kbuffer=14Mchannel
5. 8files=8*4*512Kbuffer=16Mchannel
8+files
1. buffer=128Kbuffer
2. 9files=9*4*128Kbuffer=4.5Mchannel
3. 16files=16*4*128Kbuffer=8Mchannel
- select b.DISCRETE_BYTES_PER_SECOND from v_$backup_sync_io b;
-
- select a.SHORT_WAIT_TIME_TOTAL, a.LONG_WAIT_TIME_TOTAL
- from v_$backup_async_io a ;
-
复制代码- RMAN> run {
- 2> allocate channel c1 device type sbt maxopenfiles 4;
- 3> allocate channel c2 device type sbt maxopenfiles 4;
- 4> backup (datafile 1 channel c1) ( datafile 2,3,4,5,6 channel c2 ) ;
- 5> }
- released channel: ORA_SBT_TAPE_1
- released channel: ORA_SBT_TAPE_2
- allocated channel: c1
- channel c1: SID=15 device type=SBT_TAPE
- channel c1: Oracle Secure Backup
- allocated channel: c2
- channel c2: SID=202 device type=SBT_TAPE
- channel c2: Oracle Secure Backup
- Starting backup at 10-OCT-17
- channel c1: starting full datafile backup set
- channel c1: specifying datafile(s) in backup set
- input datafile file number=00001 name=+DATA/orcl/datafile/system.256.816169553
- channel c1: starting piece 1 at 10-OCT-17
- channel c2: starting full datafile backup set
- channel c2: specifying datafile(s) in backup set
- input datafile file number=00003 name=+DATA/orcl/datafile/undotbs1.258.816169553
- input datafile file number=00002 name=+DATA/orcl/datafile/sysaux.257.816169553
- input datafile file number=00005 name=+DATA/orcl/datafile/example.265.955748593
- input datafile file number=00004 name=+DATA/orcl/datafile/users.259.816169553
- input datafile file number=00006 name=+DATA/orcl/datafile/tbslogical.267.955745025
- channel c2: starting piece 1 at 10-OCT-17
- channel c2: finished piece 1 at 10-OCT-17
- piece handle=4csgml96_1_1 tag=TAG20171010T213325 comment=API Version 2.0,MMS Version 10.4.0.4
- channel c2: backup set complete, elapsed time: 00:00:20
- channel c1: finished piece 1 at 10-OCT-17
- piece handle=4bsgml96_1_1 tag=TAG20171010T213325 comment=API Version 2.0,MMS Version 10.4.0.4
- channel c1: backup set complete, elapsed time: 00:00:30
- Finished backup at 10-OCT-17
- Starting Control File and SPFILE Autobackup at 10-OCT-17
- piece handle=c-1343950367-20171010-07 comment=API Version 2.0,MMS Version 10.4.0.4
- Finished Control File and SPFILE Autobackup at 10-OCT-17
- released channel: c1
- released channel: c2
- RMAN>
复制代码- RMAN> backup tag 'MYBACKUP3' duration 00:03 minimize load tablespace users ;
- Starting backup at 2017-10-10:21:39:39
- using channel ORA_SBT_TAPE_1
- using channel ORA_SBT_TAPE_2
- 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=00004 name=+DATA/orcl/datafile/users.259.816169553
- channel ORA_SBT_TAPE_1: starting piece 1 at 2017-10-10:21:39:39
- channel ORA_SBT_TAPE_1: finished piece 1 at 2017-10-10:21:42:42
- piece handle=4gsgmlkr_1_1 tag=MYBACKUP3 comment=API Version 2.0,MMS Version 10.4.0.4
- channel ORA_SBT_TAPE_1: backup set complete, elapsed time: 00:03:03
- channel ORA_SBT_TAPE_1: throttle time: 0:02:42
- Finished backup at 2017-10-10:21:42:42
- Starting Control File and SPFILE Autobackup at 2017-10-10:21:42:42
- piece handle=c-1343950367-20171010-09 comment=API Version 2.0,MMS Version 10.4.0.4
- Finished Control File and SPFILE Autobackup at 2017-10-10:21:43:07
复制代码
|
|