OCP 11gR2: Administration II 練習筆記 (六)

Lesson 06

===============================================================================

01. Oracle Total Recall (flashback data archive, recycle-bin)

- Flashback archive enable at table level with specified retention period (year).

- FDA contents cannot be modifying directly.

- For audit or historical report

- view "dba_flashback_archive", "dba_flashback_archive_ts", "dba_flashback_archive_tables" or user_

- When data modified > record in undo tbs > when undo expired > record trans to FDA (compress)

- Better create tbs to hold FDA

- Table not support by exadata

- FDA tablespace cannot transport to other server by oracle transport (use RMAN backup & restore)

- You can grant flashback archive administrator to user

e.g   create tablespace FDA_DATA_1 datafile '/location' size 5g autoextend on;

        create flashback archive fda1 tablespace FDA_DATA_1 retention 1 year;

        (don't set quota, if quota is full, the new transaction will stop !)

        alter table 'name' flashback archive fda1;

        - if FDA_DATA_1 tbs full, you may create another tbs and add in to fda1

        create tablespace FDA_DATA_2 datafile 'location' size 5g autoextend on;

        alter flashback archive fda1 add tablespace FDA_DATA_2;

Schema evolution

- after enable FDA, DDL may not support (drop , add column, truncate)

- disassociate and associate table with FDA by dbms_flashback_archive

- if source table DDL change, FDA table also need too

e.g.  exec dbms_flashback_archive.disassociate_FBA('schema','table');

        exec dbms_flashback_archive_reassociate_FBA('schema','table');

* remember to reassociate

* use SCN for first queries

* before flashback, commit or rollback first

02. Flashback Drop and recycle bin

- recycle bin default is on, drop table just rename table

- database will clean recycle bin when space not enough

- flashback drop will restore table object (index, trigger….)

e.g   flashback table 'name' to before drop;

                or

        flashback table 'name' to before drop [rename to 'name'];

- manual remove recycle bin by purge command "purge table 'name';"

- to bypass recycle bin use command "drop table 'name' purge;"

- check view by "dba_recyclebin" or user_

03. Flashback database

- like rewind database (normally flashback logs keep 1 to 2 days)

- used in case of logical data corruption made by user (truncate table)

- flashback opposite is "RMAN recover" (when you give up to flashback database)

- cannot perform when controlfile corrupted

- drop tablespace cannot flashback

- flashback database will also use RMAN backupset, archivelog, redo and undo.

- flashback database retention should not over RMAN retention

- enable flashback log in FRA will overhead system 10% performance

e.g.  shutdown immediate;

        startup mount exclusive; <- exclusive = dba single connection

        alter system set db_flashback_retention_target=2880; <- 2 days

        alter database flashback on;

        alter database open;

        select flashback_on from v$database;

        create restore point 'name' guarantee flashback database;

        truncate table 'name';

        shutdown immediate;

        startup mount exclusive;

        <RMAN / SQL> in exclusive mode;

        flashback database to until time "to_date('20150505 23:00:00','yyyymmdd hh24:mi:ss')";

                Or

        flashback database to until scn xxxxxxx;

                or

        flashback database to restore point 'name';

        alter database open read only;

        - check data

        alter database open resetlogs;

- flashback database view by "v$flashback_database_log;"

OCP 11gR2: Administration II 練習筆記 (七)

OCP 11gR2: Administration II 練習筆記 (五)

OCP 11gR2: Administration II 練習筆記 (五)

Lesson 05

============================================================================

01. Diagnosing the database

- Data Recovery Advisor (DRA)

           Cannot detect data block corruption

           Only for single instance, not support RAC

           Support failover to standby, but not analysis and repair of standby database

           * Create standby database by using data guard

* RMAN> change failure = close impact or change priority, command "change failure 5 priority low;"

* RMAN> list failure [All | critical | high | low | closed];, default is all option [exclude failure (failnum)]

* RMAN> advise failure [All | critical | high | low | closed];, default is all option [exclude failure (failnum)]

* RMAN> repair failure [using advise option failnum {preview | noprompt}];

DRA view: v$ir_failure, v$ir_manual_checklist, v$ir_repair, v$ir_failure_set

- HM automate run and save report, DRA read it by "list failure" to check the db health status

- Proactive check: use validate command "validate database plus archivelog;" for data block corruption

02. Data failure examples

- not accessible components:

           Missing datafile at O/S, incorrect permission, offline tbs and deleted.

- physical corruption

           8KB data block, 4K for checksum: checksum failure may caused by invalid block header (dbf from different platform)

- logical corruption

           Related to schema object, corrupt index, corrupt transaction inconsistencies

- controlfile inconsistencies

- I/O failures, reach limit on max open files in O/S

03. Block corruption detect

- for physical corruption by command "validate database plus archivelog;" will check database all data block

- for logical corruption by command "validate database plus archivelog check logical;" will check index, schema object

- ORA-01578 appear in alert.log , v$database_block_corruption and validate command and caused by hardware issue.

- don't run any defragment tools in oracle database, it will cause corruption

- Analyze command can check error is permanent or not.

           collect statistics for table, index ….. by command "analyze [table|index] 'name' compute statistics; "

Parameter to auto detect corruption

- db_block_checking  <- default is false, oracle recommend "full" [off | false | low | medium | full or true] 10% overhead

                                            (after update, insert, it will run block header and object contents check except index)

- db_block_checksum  <- default is typical, oracle recommend typical [OFF | TYPICAL | FULL]

- db_lost_write_protect <- default is none, for standby database only, the committed transaction will also effect in standby

- db_ultra_safe               <- use this to tune upon 3 option by [OFF | DATA_ONLY | DATA_AND_INDEX]

db_ultra_safe	OFF	DATA_ONLY	DATA_AND_INDEX
db_block_checking	OFF / FALSE	MEDIUM	FULL / TRUE
db_block_checksum	TYPICAL	FULL	FULL
db_lost_write_protect	TYPICAL	TYPICAL	TYPICAL

04. Block media recovery example

- check v$database_block_corruption (record by validate command)

           01. offline tbs, corrupt some block by hex tools then online tbs (may have error)

           02. RMAN> validate tablespace 'name' check logical;

           03. Read alert.log and select * from v$database_block_corruption;

           04. RMAN> recover corruption list;

           05. RMAN> recover tablespace 'name'; and online the tbs

05. Automatic diagnostic

Critical error -> ADR      <- 1. diagnostic_dest

                                           <- 2. $ORACLE_BASE

                                           <- 3. $ORACLE_HOME/log

- 11g not use core_dump, user_dump parameter anymore

- can check from EM database first page

- check by adrci (v$diag_info)

           Show incident, show problem , show alert ….. e.g.

06. Health Monitor (HM)

- v$hm_check (check which option can use to run diagnostic)

- view the HM report by v$hm_run, dbms_hm, adrci , EM

- run HM by dbms_hm or EM

           Example:

           exec dbms_hm.run_check('check_name','name');

           select dbms_hm.get_run_report('name') from dual;

                     or

           adrci -> show hm_run

07. Flashback

- Most depend by undo tablespace (undo management auto, retention, and guarantee)

- parallel insert will take more undo resource

- under sys schema cannot flashback

Object Level	Scenario examples	Flashback Technology	Depends on	Affects data
Database	Truncate table, made multi-table change	Database	Flashback logs	True
Table	Drop table	Drop	Recycle bin	True
	Update with the wrong where clause	Table	Undo data	True
	Compare current data from past	Query	Undo data	False
	Compare version of a row	Version	Undo data	False
	Keep historical transaction data	Data archive	Undo data	True
Transaction	Investigate and back out suspect transaction	transaction	Undo/ redo archivelog	True

- flashback query

        1. Query all data at a specified point in time

        2. only for DML, not support DDL

 e.g.         select * from emp as of timestamp to_timestamp('nnnnnnnn','YYYYMMDD:HH24:MI:SS');

                select * from emp as scn xxxxxxxx;

- flashback version query (will not show haven't committed history)

        1. see all version of a row between two times.

        2. see the transactions that changed the row.

        3. not support external tables, temp tables, fixed tables (sys.table / X$table), view.

        4. not support DDL

 e.g.         select versions_xid, versions_startscn, versions_endscn, versions_operation, ename, sal from

                emp versions between scn xxxxx and xxxxx;

                        or

                select versions_xid, versions_startscn, versions_endscn, versions_operation, ename, sal from

                emp versions between scn minvalue and maxvalue; (enable supplement log)

                        reverse row

                update emp set sal=(select sal from emp as of timestamp to_timestamp('nnnnnnnnn','yyddmm hh24miss')

                where empno=2000) where empno=2000;

- flashback table

        1. not support sys schema

        2. EM -> Perform recovery -> table -> recover

        3. enable row movement

e.g.          alter table emp enable row movement;

                grant flashback any table to scott;

                grant select,insert,update, delete on emp to scott;

                flashback table emp to scn XXXXXXX;

- flashback transaction

        1. see all changes made by a transaction statement (query by versions_XID)

e.g.          select * from flashback_transaction_query where xid='xxxxxxxx';

        2. enable supplemental log

                alter database add supplemental log data;

                alter database add supplemental log data (primary key) columns;

        3. each transaction only can perform once

        3. consider transaction_backout option <- transaction dependency.

        Let's understand a concept before use the option: Transaction Dependence.

        For example, 2 transactions TX1 and TX2, if match below any point that means TX2 dependence TX1:

          01. WAW (Write After Write), means TX1 modify some row and TX2 modify same row again.

          02. Primary Key dependence means the table with primary key. TX1 delete the row and TX2 insert a new           row with same primary key.

          03. Foreign Key dependence means TX1 made new foreign key data after insert or update and TX2 insert           or update rows use new foreign key data.

               

          Understand transaction dependence can help to solve the problem when conflict. Example for point 02, if           you want to reverse TX1 but don't care TX2, the table will have duplicate primary key.

     - [ NOCASCADE ]: TX1 cannot have dependence by other transaction, otherwise cancel operation and error.

     - [ CASCADE ]: TX1 and TX2 reverse together.

     - [ NOCASCADE_FORCE ]: ignore TX2, reverse TX1 if constraint no conflict the operation will success,            otherwise the conflicted constraint will error and operation fail.

     - [ NONCONFILICT_ONLY ]: reverse TX1 without influence TX2. Not same with NOCASCADE_FORCE, it      will filter TX1 reverse SQL and make sure the change not affect TX2.

     Example details for point 01: WAW, for a table only has 3 rows and no constraint:

       

Case 1: Original	Case 2: TX1 update 3 rows	Case 3: TX2 update 2 rows
ID	ID	ID
---------------	---------------	---------------
1	11	11
2	22	222
3	33	333

        

This is typical WAW example, TX2 dependence TX1

Now try to reverse TX1 with different backout option will have different result:

[NOCASCADE] will cause "ORA-55504: Transaction conflicts in NOCASCADE mode" , table status in Case 3

[CASCADE] will reverse table to Case 1

[NOCASCADE_FORCE], TX2 transaction not affect, but TX1 will reverse to below

ID

---------------

1

222

333

       

Here has some strange from the rule of [ NOCASCADE_FORCE ]: the reverse should ignore TX2 and perform on all rows, but why row 2 & 3 didn't reverse ? Because in this sample, reverse SQL the where clause include    ROWID for constraint because database enabled supplemental log.

       

[NONCONFILICT_ONLY] the result is same as [NOCASCADE_FORCE]. However, even the result is same, but  the process is different. The rows reverse SQL which related to TX2 will be filter first.

Real world example:

        Under HR schema, manger wants to increase Michael salary 10%.

        SQL> select FIRST_NAME, SALARY from hr.employees where employee_id=201;

        FIRST_NAME                    SALARY

        --------------------                            ----------

        Michael                           13000

       

        However, by user mistake, all staff increase salary 500% and committed

        SQL> update hr.employees set salary=salary*5;

        106 rows updated.

        SQL> commit;

       

        Then HR on progress increase Michael 10%

        SQL> update hr.employees set salary=salary*1.1 where employee_id=201;

        1 row updated.

        SQL> commit;

       

        Manger just wants Michael salary from 13000 to 14300. However, by the user mistake, Michael salary is           71500.

        After few minutes, HR discovered all staff salary abnormal and contact DBA investigation. DBA decide             use flashback_transaction_query to find out the problem on last 15 minutes.

        SQL> select distinct xid, commit_scn from flashback_transaction_query where table_owner='HR' and         table_name='EMPLOYEES' and commit_timestamp > systimestamp - interval '15' minute order by                     commit_scn;

        DBA found last 15 minutes have 2 transactions

        XID                            COMMIT_SCN

        ----------------                        ----------

        0300080081090000          3062978

        01001D00AE080000        3063594

       

        DBA check XID 0300080081090000 and found abnormal bulk salary update

        SQL> select undo_sql from flashback_transaction_query where xid='0300080081090000'

        UNDO_SQL

        ----------------------------------------------------------------------------------------------------

        update "HR"."EMPLOYEES" set "SALARY" = '10000' where ROWID = 'AAAVTFAAFAAAADOAAJ';

        update "HR"."EMPLOYEES" set "SALARY" = '8300' where ROWID = 'AAAVTFAAFAAAADOAAI';

        update "HR"."EMPLOYEES" set "SALARY" = '10000' where ROWID = 'AAAVTFAAFAAAADOAAG';

        update "HR"."EMPLOYEES" set "SALARY" = '3000' where ROWID = 'AAAVTFAAFAAAADNABh';

        ..     

        …

        Now DBA decide use transaction_backout with nocascade option to rollback that transaction

        exec dbms_flashback.transaction_backout(1,xid_array('0300080081090000'),dbms_flashback.nocascade);

        *

        ERROR at line 1:

        ORA-55504: Transaction conflicts in NOCASCADE mode

        The result show dependence, so for the reasonable option should be rollback all change by cascade                   option.

        exec dbms_flashback.transaction_backout(1,xid_array('0300080081090000'),dbms_flashback.cascade);

        PL/SQL procedure successfully completed.

        After run the flashback transaction, dba check the report and found both transaction are rollback

        SQL> select xid,dependent_xid,backout_mode from dba_flashback_txn_state;

        XID                    DEPENDENT_XID           BACKOUT_MODE

        ----------------                ----------------                        ----------------

        01001D00AE080000                                 CASCADE

        0300080081090000    01001D00AE080000 CASCADE

        Finally, DBA check Michael salary rollback to original.

- parallel DML enhance performance

 e.g.         alter session enable parallel dml;

                insert into emp select * from emp;

                commit;

                alter session disable parallel dml;

                        or

                select /*+ parallel */ count(*) from sys.all_objects;

OCP 11gR2: Administration II 練習筆記 (六)

OCP 11gR2: Administration II 練習筆記 (四)

OCP 11gR2: Administration II 練習筆記 (四)

Lesson 04

=========================================================================

01. RMAN to perform recovery

non-critical:

- recovery tablespace (complete recovery)

 When tbs lost, offline it, restore and recover tbs then online tbs

Critical:

- datafile (system, undo, sysaux)

 Shutdown DB, startup mount restore and recover tbs then open DB

02. Recover image copy

- incremental backup update image copy SCN;

 after perform backup and apply archivelog to copy for fast switch, below command:

        RMAN> recovery copy of datafile ['n' | 'file_name'];

        e.g.

                run {

                allocate channel ch1 device type disk format 'location/name';

                backup as copy tablespace 'name'; }

        to fast swtich

                run {

                sql 'alter tablespace 'name' offline immediate';

                set newname for datafile 'number' to 'location/image_name';

                switch datafile all;

                recover tablespace 'tbs_name';

                sql 'alter tablespace 'name' online'; }

        

03. restore & recover in noarchivelog mode

- in noarchivelog mode need restore entire database

- incremental backup to recover a database in noarchivelog mode

        e.g.

        startup force nomount;

        restore controlfile;

        alter database mount;

        restore database;

        recover database;

        alter database open resetlogs

04. Normal Restore Point

A normal restore point enables you to flash the database back to a restore point within the time period determined by the DB_FLASHBACK_RETENTION_TARGET initialization parameter. The database automatically manages normal restore points. When the maximum number of restore points is reached, according to the rules described restore_point, the database automatically drops the oldest restore point. However, you can explicitly drop a normal restore point using the DROP RESTORE POINT statement.

        Command:

                RMAN> list restore point all;

                RMAN> create restore point 'name';

                RMAN> create restore point 'name' as of SCN 'number';

                SQL> select * from v$restore_point;

05. Point in time recovery

- Define the restore clause by SCN / TIME / SEQUENCE NUMBER

- In run block set until, restore, recover

- Open database in readonly mode for verify and below command to open.

        SQL> alter database open resetlogs;

- After restlogs, database should perform backup immediate.

- Controlfile only can keep 7 days SCN by default

- If need to restore over 7 days, need to restore old controlfile from backup first.

06. Loss of SPfile and controlfile

SPfile can copy alert.log parameter to pfile or restore from autobackup

        e.g.

        restore spfile:

                SQL> startup force nomount

                RMAN> restore spfile from autobackup;

                SQL> startup force

        Restore controlfile:

                SQL> startup nomount

                RMAN> restore controlfile from autoback;

                SQL> startup mount;

                SQL> alter database open resetlogs;

07. RMAN monitoring and tuning

Monitor RMAN backup jobs by view

- v$process join v$session where client_info like 'rman%' or run block "set command id to 'name';"

- v$session_longops for progress (make sure statistics_level parameter not in basic)

- use debug option to trace the logs

        Command: "rman target / catalog username@rcat debug trace trace.log" (also for MML troubleshoot)

RMAN tuning

Tuning RMAN need to find the bottleneck and RMAN process with below three phases

- Read Phase: production files --> Read Buffer  <-- it limited by maxopenfiles

- Copy Phase: Read buffer copy to output buffer  <-- memory to memory, no tuning

- Write Phase: output buffer --> write to media  <-- it limited by filesperset

- Parallelization of Backup Sets

 For performanace, you can allocate muliple channels and assign datafiles to specific channels or auto assign

        Datafiles 1,3,5 to channel 1 to device

        Datafiles 2,4,6 to channel 2 to device

        Datafiles 7,8,9 to channel 3 to device

  You can also use filesperset option to limit the number for datafile in backupset.

- Multiplexing:

RMAN can at the same time read multiple files from disk and then write their blocks into the same backup set. For example, RMAN can read from two datafiles simultaneously, and then combine the blocks from these datafiles into a single backup piece.

 Each channel allocates 4 output buffers of size 1 MB each

Multiplexing Level	Allocation Rule
Level <= 4	1 MB buffers are allocated; the total buffer size for all input files is 16 MB.
4 < Level <= 8	512 KB are allocated, the total buffer size for all files is less than 16 MB.
Level > 8	RMAN allocates four 128 KB disk buffers per channel for each file, the total size is 512 KB per channel for each file

 Multiplexing = Min (Min (DATAFILES,FILEPERSET) , MAXOPENFILES)

 maxopenfiles in allocate channel, filesperset in backup command.

 Filesperset = number per datafile in same backupset or channel.

- Defaults: MAXOPENFILES=8, FILESPERSET=64,

a) Channel=1, Datafiles=6, MAXOPENFILES=8, FILESPERSET=64   | Level of multiplex is 6 -only 6 datafiles

For example, assume that you backup 6 datafiles in 1 channel. Set filesperset to 64 and maxopenfiles to 8, in this case the multiplexing is 6 = [Min(Min(DATAFILES=6,FILEPERSET=64),MAXOPENFILES=8)]. When RMAN backup from disk, Each channel allocates 4 output buffers of size 512KB each, if DBWR_IO_SLAVES is set to 0. The best recovery performance do not set filesperset to greater than 24 (6 x 4 ), and 12 MB per channel ( 6x512x4/1024 )

b) Channel=1, Datafiles=12, MAXOPENFILES=8, FILESPERSET=64   | Level of multiplex is 8 -limited by maxopenfiles

c) Channel=1, Datafiles=12, MAXOPENFILES=8, FILESPERSET=6     | Level of multiplex is 6 -limited by filesperset

d) Channel=1, Datafiles=12, MAXOPENFILES=8, FILESPERSET=10   | Level of multiplex is 8 -limited by maxopenfiles

command example:

        run { allocate channel ch1 device type disk format='location' maxopenfiles 1;

        backup database filesperset 64; }

       

        run { allocate channel ch1 device type disk format='location' maxopenfiles 20;

        backup database filesperset 5; }

- Allocating Tape buffers

From SGA := BACKUP_TAPE_IO_SLAVES is True = Asynchronous (faster, use more memory from large pool)

From PGA := BACKUP_TAPE_IO_SLAVES is False = Synchronous (default is false)

Oracle recommends BACKUP_TAPE_IO_SLAVES to true and run with parallelism and multi device.

        -> if BACKUP_TAPE_IO_SLAVES is True, also need to define DBWR_IO_SLAVES (it depend on channels)

        -> Check async bottleneck in v$backup_async_io (column: long_waits / io_count)
            ( type='INPUT' means froms database to memory)

            ( type='OUTPUT' means from memory to external destination (tape or disk))

        -> Check sync bottleneck on discrete_bytes_per_second from v$backup_sync_io


- Channel Tuning

Prevent RMAN to consuming too much disk bandwidth

        1. set by RATE=1500K (1.5MB/sec)

        2. set by allocate channel

                run { allocate channel ch1 device type sbt;

                        allocate channel ch2 device type sbt;

                        allocate channel ch3 device type sbt;

                        backup (datafile 1,2,5 channel ch1) (datafile 4,6 channel ch2) (datafile 3,7,8 channel ch3);

                        backup database not backed up;}

- Backup duration

e.g. RMAN> backup duration 04:00 partial minimize load database filesperset 1;

        [minimize time | minimize load] [partial] *if no partial keyword and not enough time backup, all backupset failed.

        Time = the backup runs as far as possible (may combine with set rate)

        Load = The backup attempts to use the full amount of time. (It reduces load on the system)

- Validate

Validate may cause bottleneck because read tapes

Backup validate rman command create no output. It scan the files for content or block corruption, if any block corruption is found, it populates to the v$database_block_corruption.

To verify a consistent backup by below command:
RESTORE DATABASE PREVIEW ;
RESTORE DATABASE VALIDATE;
RESTORE ARCHIVELOG FROM sequence xx UNTIL SEQUENCE yy THREAD nn VALIDATE;
RESTORE CONTROLFILE VALIDATE;
RESTORE SPFILE VALIDATE;


Delete older backup by days
RMAN> delete backup completed before 'sysdate - 6';

OCP 11gR2: Administration II 練習筆記 (五)

OCP 11gR2: Administration II 練習筆記 (三)