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 練習筆記 (三)