Live Virtual Class Training from Oracle University

As part of OCM Requirement,

I have been completed 3 days LVC training workshop from Oracle University

Oracle Database 11g: Backup and Recovery Workshop 🙂

COC

 

 

Advertisements

controlfile autobackup in 11.2.0.1

rman target /
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 REDUNDANCY 1; # default
CONFIGURE BACKUP OPTIMIZATION OFF; # default
CONFIGURE DEFAULT DEVICE TYPE TO DISK; # default
CONFIGURE CONTROLFILE AUTOBACKUP ON;
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '%F'; # default
CONFIGURE DEVICE TYPE DISK PARALLELISM 1 BACKUP TYPE TO BACKUPSET; # default
CONFIGURE DATAFILE BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
CONFIGURE ARCHIVELOG BACKUP COPIES FOR DEVICE TYPE DISK 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/db_1/dbs/snapcf_orcl.f'; # default
sqlplus / as sysdba
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> ALTER TABLESPACE HR
  2  ADD DATAFILE;

Tablespace altered.

-bash-3.2$ pwd
/u01/app/oracle/flash_recovery_area/ORCL/autobackup/2014_07_12

-bash-3.2$ ls -ltr
total 56160
-rw-r----- 1 oracle oinstall 9568256 Jul 12 16:17 o1_mf_s_852740246_9w24l0hv_.bkp
-rw-r----- 1 oracle oinstall 9568256 Jul 12 16:38 o1_mf_s_852741517_9w25ss1s_.bkp
-rw-r----- 1 oracle oinstall 9568256 Jul 12 16:52 o1_mf_s_852742363_9w26n78n_.bkp
-rw-r----- 1 oracle oinstall 9568256 Jul 12 18:09 o1_mf_s_852746957_9w2c3s9h_.bkp
-rw-r----- 1 oracle oinstall 9568256 Jul 12 18:32 o1_mf_s_852748332_9w2dgoqq_.bkp
-rw-r----- 1 oracle oinstall 9568256 Jul 12 18:56 o1_mf_s_852749758_9w2fv831_.bkp
-bash-3.2$
-bash-3.2$ ls -ltr | wc -l
7

“Within few minutes…”

-bash-3.2$ ll
total 65520
-rw-r----- 1 oracle oinstall 9568256 Jul 12 16:17 o1_mf_s_852740246_9w24l0hv_.bkp
-rw-r----- 1 oracle oinstall 9568256 Jul 12 16:38 o1_mf_s_852741517_9w25ss1s_.bkp
-rw-r----- 1 oracle oinstall 9568256 Jul 12 16:52 o1_mf_s_852742363_9w26n78n_.bkp
-rw-r----- 1 oracle oinstall 9568256 Jul 12 18:09 o1_mf_s_852746957_9w2c3s9h_.bkp
-rw-r----- 1 oracle oinstall 9568256 Jul 12 18:32 o1_mf_s_852748332_9w2dgoqq_.bkp
-rw-r----- 1 oracle oinstall 9568256 Jul 12 18:56 o1_mf_s_852749758_9w2fv831_.bkp
-rw-r----- 1 oracle oinstall 9568256 Jul 12 21:34 o1_mf_s_852759231_9w2q3bdk_.bkp
-bash-3.2$ ls -ltr | wc -l
8

http://download.oracle.com/docs/cd/E11882_01/backup.112/e10643/rcmsynta010.htm#RCMRF113

Starting with Oracle 11g Release 2, RMAN creates a single autobackup file encompassing all of the structural changes that have occurred within a few minutes of each other rather than creating a new backup of the controlfile on each structural change to the database.

In 10g, as well, when you add a datafile to a tablespace, CONTROLFILE AUTOBACKUP creates a backup of the controlfile automatically when you alter the database structure (e.g. adding new datafile(s)).
However, if you are creating a tablespace with 10 datafiles (eg. “CREATE TABLESPACE x datafile ..” and then followed by 9 “ALTER TABLESPACE x ADD DATAFILE …” commands), you would find 10 distinct backups of the controlfile being “autocreated”.
So the “improvement” in 11g is that Oracle will wait “a few minutes” to see if you have multiple changes to the database structure before deciding on a controlfile autobackup. That way, you will likely see only 1 controlfile autobackup after having added all 10 datafiles.

Oracle introduced the SKIP READONLY option

Until 11gr1 by default, the restore command skipped datafiles associated with read-only tablespaces. If you needed read-only tablespaces restored, then you had to use the “check readonly” command or restore each read-only tablespace individually.
Have a look at the restore syntax on “Oracle Database Backup and Recovery Reference 10g Release 2 (10.2)” Part Number B14194-03  and compare it with the syntax used on “Oracle Database Backup and Recovery Reference 11g Release 1 (11.1) Part Number B28273-03” .

In the latter syntax Oracle introduced the SKIP READONLY option, inverting the previous logic: the SKIP READONLY does not restore read-only files.

Oracle Database Smart Flash Cache

In case you don’t have budget to buy Exadata you can still buy huge number of flash disks and put on them part of your database. But what should be stored on flash disks(very fast) and what on magnetic disks(very slow) ?

It’s not your businesses to know let decide database 🙂

Introduction

DB Smart Flash Cache is new extension for buffer cache area. This extra area should be defined on solid state disks (SSD) and has following features:

  • make performance improvements at moderate cost(cheaper than DRAM)
  • low latency compared to magnetic disks
  • higher throughput compared to magnetic disks
  • easy to setup
  • easy to control
  • can be used for RAC cache fusion keeps consistency
  • direct I/O bypasses buffer cache so as well bypasses DB smart flash cache
  • can cache only clean blocks from buffer cache
  • flash cache is not auto-tuned
  • only blocks from standard buffer pool are cached in DB smart flash cache

Oracle recommends:

flash disks should have comparable read IOPs and IOPs write
this new layer should be at least 2-10 times bigger than buffer cache in DRAM
mainly for OLTP systems

Architecture

00

if a oracle server process needs to read a block from database at first it must read it from magnetic disk(physical read). Then the block is stored in buffer cache memory and added to standard “LRU chain” list.

When “in memory buffers area” is getting full Oracle must decide which blocks needs to be removed from cache. If you have DB Smart Flash Cache enabled “clean” blocks are written to “Flash cache” by DBWR process so next time they can be read into memory from Flash Cache and improve your performance.

NOTE: “Dirty” blocks are never stored in Flash Cache
List of blocks cached in DB smart flash cache are stored in buffer cache area on two dedicated flash “LRU lists” depending on object attribute FLASH_CACHE:

DEFAULT – standard last recently used algorithm decides how long such blocks are cached in flash cache. It’s default value assigned to each object in database.
KEEP – such blocks are not removed from flash cache as long as the flash cache is large enough

alter|create table|index objectname
storage
(
buffer_pool { keep | recycle | default }
flash_cache { keep | none | default }
);

NONE value for FLASH_CACHE is blocking flash caching for a given object.

Statistics

All I/O operations from DB smart flash cache are counted as physical I/O however Oracle also collects such informations in new columns.

V$SQL – OPTIMIZED_PHY_READ_REQUESTS
V$SQLAREA – OPTIMIZED_PHY_READ_REQUESTS
V$FILESTAT – OPTIMIZED_PHYBLKRD

select name from v$statname where name like 'physical%optimized%';

NAME
----------------------------------------------------------------
physical read requests optimized
physical read total bytes optimized

You can see such stats in V$SESSTAT and V$SYSSTAT

Setup

Two parameters must be set on database level to turn on DB smart flash cache:

DB_FLASH_CACHE_FILE – defines (OS disk path or ASM disk group) and file name to store this data
DB_FLASH_CACHE_SIZE – defines size of the flash cache

DB_FLASH_CACHE_FILE='/os path/flash_cache_file.dbf'
DB_FLASH_CACHE_FILE='+FLASH_DISK_GROUP/flash_cache_file.dbf'
DB_FLASH_CACHE_SIZE=200m

After setting both parameters you need to restart database.
DB_FLASH_CACHE_FILE

can’t be shared between many databases or instances

DB_FLASH_CACHE_SIZE

can’t be dynamically resized
can be set to 0 to disable DB smart flash cache
can be set to original size to re-enable DB smart flash cache

Performance improvements

Oracle conducted interesting test for a OLTP database 70GB size with 8GB SGA.
From below picture you can see improvements for Transactions versus size of DB smart cache size.
R01

Following picture shows improvement in transaction response time versus DB smart cache size

R001

Example

I simulate SSD disk by creation ramdisk – disk based in memory using following steps:

1. create directory to mount ramdisk and change owner to oracle and group dba

[root@oel5 /]mkdir /ramdisk
[root@oel5 /]chown oracle:dba -R /ramdisk

2. mount ramdisk and check it

[root@oel5 /]# mount -t tmpfs none /ramdisk -o size=256m
[root@oel5 /]# mount | grep ramdisk
none on /ramdisk type tmpfs (rw,size=256m)

3. set parameters for database and restart it as user oracle

SQL> alter system set db_flash_cache_file='/ramdisk/ram.dbf'
SQL> scope=spfile;

System altered.

SQL> alter system set db_flash_cache_size=200M scope=spfile;

System altered.

SQL> startup force
ORACLE instance started.

Total System Global Area 835104768 bytes
Fixed Size 2232960 bytes
Variable Size 507514240 bytes
Database Buffers 322961408 bytes
Redo Buffers 2396160 bytes
Database mounted.
Database opened.
SQL> show parameter flash_cache

NAME TYPE VALUE
———————– ———– ——————————
db_flash_cache_file string /ramdisk/ram.dbf
db_flash_cache_size big integer 200M

4. Check new file exists in /ramdisk

[root@oel5 ramdisk]# ll
total 8
-rw-r—– 1 oracle asmadmin 209715200 Feb 24 22:54 ram.dbf

5. Let’s create tables with flash_cache keep reference in storage clause so Oracle will try to keep the blocks in DB smart cache as long as possible.

create table test_tbl1
(id number,
id1 number,
id2 number)
storage(flash_cache keep);

begin
for i in 1..1000000
loop
insert into test_tbl1 values(i, i, i);
end loop;
commit;
end;
/

6. Eventually after some time you can see some data in flash cache – v$bh view.

select status, count(*) from v$bh
group by status;

STATUS COUNT(*)
———- ———-
xcur 36915
flashcur 25583
cr 13

7. If you clean buffer cache as well db smart flash cache is purged

alter system flush buffer_cache;
system FLUSH altered.

STATUS COUNT(*)
———- ———-
xcur 520
free 36411

Have a fun 🙂


Oracle Database 11g Release 2 introduced a new database feature:
Database Smart Flash Cache. This feature is available on Solaris and
Oracle Enterprise Linux and allows customers to increase the effective size
of the Oracle database buffer cache without adding more main memory to the
system. For transaction-based workloads, Oracle database blocks are normally
loaded into a dedicated shared memory area in main memory called the System
Global Area (SGA). Database Smart Flash Cache allows the database buffer
cache to be expanded beyond the SGA in main memory to a second
level cache on flash memory. The Sun Storage F5100 Flash Array and the
Sun Flash Accelerator F20 PCIe Card provide a natural fit for Oracle Database
Smart Flash Cache and offer an excellent opportunity for end users to take
advantage of this new functionality. :)

0

http://www.oracle.com/technetwork/articles/systems-hardware-architecture/oracle-db-smart-flash-cache-175588.pdf

1

http://www.fujitsu.com/global/Images/fj-gc-dbsfc.pdf

2       3

http://www.oracle.com/us/technologies/linux/oracle-linux-with-flash-2004731.pdf

4

Create an Incident Package with ADRCI and IPS Commands

Oracle Automatic Diagnostic Repository (ADR).

adrci> show homes
ADR Homes:
diag/clients/user_oracle/host_772540587_80
diag/clients/user_oracle/host_3968884957_80
diag/clients/user_tkyte/host_3968884957_80
diag/rdbms/orcl/orcl
diag/rdbms/nvtxdb/nvtxdb
diag/rdbms/ora11g/ora11g
diag/tnslsnr/oracle_home/listener
diag/tnslsnr/oraclehome/listener
diag/tnslsnr/localhost/listener
adrci>
adrci>
adrci>  set homepath diag/rdbms/nvtxdb/nvtxdb
adrci>
adrci>
adrci> show home
ADR Homes:
diag/rdbms/nvtxdb/nvtxdb
adrci>
adrci> show incident

ADR Home = /home/oracle/u01/app/oracle/diag/rdbms/nvtxdb/nvtxdb:
*************************************************************************
INCIDENT_ID          PROBLEM_KEY                                                 CREATE_TIME
-------------------- ----------------------------------------------------------- ----------------------------------------
55713                ORA 7445 [kglic0()+1086]                                    2013-11-16 01:30:37.246000 +05:30
1 rows fetched

adrci>
adrci>
adrci> ips pack incident 55713 in /home/oracle
Generated package 1 in file /home/oracle/ORA7445kg_20140624172237_COM_1.zip, mode complete
adrci>

-bash-3.2$ du -ksh ORA7445kg_20140624172237_COM_1.zip
32M     ORA7445kg_20140624172237_COM_1.zip

This statement lists the names of all trace files related to incident number 55713:

adrci> SHOW TRACEFILE -I 55713
diag/rdbms/nvtxdb/nvtxdb/incident/incdir_55713/nvtxdb_m000_20985_i55713.trc

http://www.dba-oracle.com/t_11g_new_ADRCI_IPS.htm

How to monitor alert logs using adrci?

SHOW ALERT
SHOW ALERT -TAIL
SHOW ALERT -TAIL -F

http://docs.oracle.com/cd/B28359_01/server.111/b28319/adrci.htm#BABBHGFC