Manage Distributed Materialized Views

Posted by:  Amit Kumar Srivastava - OCM11g

1.  From Oracle Documentation : Basic Materialized Views

2.  This objective requires us to know how to create a materialized in a distributed environment view, that is, create a separate MVIEW

# Create the DB LINK DB in TEST Database

# The first is to add the entry to the tnsnames.ora file

vi $ORACLE_HOME/network/admin/tnsnames.ora

# Add these lines

ORCL=

(DESCRIPTION=

  (ADDRESS=(PROTOCOL=tcp)(HOST=amit.example.com)(PORT=1521))

  (CONNECT_DATA=

     (SERVICE_NAME=ORCL)))

# Check that we have connectivity

tnsping ORCL

- Create the MVIEW LOG on the EMPLOYEES table

CREATE MATERIALIZED VIEW LOG ON EMPLOYEES;

- Create the DB LINK

CREATE PUBLIC DATABASE LINK ORCL CONNECT TO HR IDENTIFIED BY "hr" USING 'ORCL';

--- Tried connectivity

SELECT COUNT(*) FROM EMPLOYEES@ORCL;

- Create the EMP MVIEW pointing at table EMPLOYEES

CREATE MATERIALIZED VIEW EMP_MV REFRESH FAST AS

  SELECT * FROM EMPLOYEES@ORCL;

- Tried the Fast Refresh

                             

EXEC DBMS_MVIEW.REFRESH('EMP_MV','F');

3.       Materialized View Refresh Group: Create a group of materialized views and to make the data consistent between them.

- Create a MVIEW LOG in the DEPARTMENTS table (DATABASE ORCL)

CREATE MATERIALIZED VIEW LOG ON DEPARTMENTS;

- Create a second materialized view in the database of ORCL

CREATE MATERIALIZED VIEW DEP_MV REFRESH FAST AS

  SELECT * FROM DEPARTMENTS@ORCL;

Create the refresh group

- Information can be found in these two views
--   · DBA_REFRESH

--   · DBA_REFRESH_CHILDREN

BEGIN

DBMS_REFRESH.MAKE (

  NAME => 'REFRESH_GROUP_TEST',

  LIST => 'EMP_MV,DEP_MV',

  NEXT_DATE => SYSDATE,

  INTERVAL => 'SYSDATE+1/1440',

  IMPLICIT_DESTROY => TRUE);

END;

/

- Cleaning environment

EXEC DBMS_REFRESH.DESTROY('REFRESH_GROUP_TEST');

DROP MATERIALIZED VIEW EMP_MV;

DROP MATERIALIZED VIEW DEP_MV;

DROP PUBLIC DATABASE LINK ORCL;

-- Deleting the MVIEW LOG

DROP MATERIALIZED VIEW LOG ON EMPLOYEES;

DROP MATERIALIZED VIEW LOG ON DEPARTMENTS;

Thread 1 cannot allocate new log Sequence and Checkpoint not complete

Posted by Amit Kumar Srivastava - OCM11g

When you will see these messages, like Oracle wants to reuse the redo log file, but checkpoint position is still in the log, Oracle must wait until the checkpoint completes.

CAUSE:

In this situation either DBWR writes slowly or log switch happens before the log is completely full or log file is small.

Thread 1 advanced to log sequence 38379 (LGWR switch)

  Current log# 3 seq# 38379 mem# 0: +OCMDB/OCMDB/onlinelog/group_3.263.853784771

Thread 1 advanced to log sequence 38380 (LGWR switch)

  Current log# 1 seq# 38380 mem# 0: +OCMDB/OCMDB/onlinelog/group_1.261.853784763

Mon Dec 29 05:17:19 2014

Thread 1 cannot allocate new log, sequence 38381

Checkpoint not complete

  Current log# 1 seq# 38380 mem# 0: +OCMDB/OCMDB/onlinelog/group_1.261.853784763

Thread 1 advanced to log sequence 38381 (LGWR switch)

  Current log# 2 seq# 38381 mem# 0: +OCMDB/OCMDB/onlinelog/group_2.262.853784767

Mon Dec 29 05:17:40 2014

Thread 1 cannot allocate new log, sequence 38382

Checkpoint not complete

This occurred when large number of DML (updates) in the system, and required you might need more redo groups in your running instances.

By adding more redo group in your running database its can help you to get rid into this.

ADDING REDO LOGS

SQL> ALTER DATABASE ADD LOGFILE GROUP …. ;

If you have smaller redo log and if you see many log switches then increasing the redo size might help.

Step1: Switching logfile to make group 1 ‘INACTIVE’

SQL> Alter system switch logfile;
SQL> select group#, status from v$log;

GROUP# STATUS
---------- ----------------
1 INACTIVE
2 ACTIVE
3 CURRENT 

Step2:- Drop and recreate redo with size greater than earlier.

SQL> alter database drop logfile group 1;

SQL> alter database add logfile group 1 <…………..>   size 100M reuse;

Repeat step 1 and 2 until you drop and recreate all redo logs with bigger size.

It is a recommended to have 4-5 log switches per hour. You can use below Script to find the log switches on hourly basis.

SCRIPTS FOR CHECKING ARCHIVELOG GENERATION

SQL> set lines 200 pages 2000

SQL> SELECT to_char(first_time,'YYYY-MON-DD') day,
to_char(sum(decode(to_char(first_time,'HH24'),'00',1,0)),'99') "00",
to_char(sum(decode(to_char(first_time,'HH24'),'01',1,0)),'99') "01",
to_char(sum(decode(to_char(first_time,'HH24'),'02',1,0)),'99') "02",
to_char(sum(decode(to_char(first_time,'HH24'),'03',1,0)),'99') "03",
to_char(sum(decode(to_char(first_time,'HH24'),'04',1,0)),'99') "04",
to_char(sum(decode(to_char(first_time,'HH24'),'05',1,0)),'99') "05",
to_char(sum(decode(to_char(first_time,'HH24'),'06',1,0)),'99') "06",
to_char(sum(decode(to_char(first_time,'HH24'),'07',1,0)),'99') "07",
to_char(sum(decode(to_char(first_time,'HH24'),'08',1,0)),'99') "0",
to_char(sum(decode(to_char(first_time,'HH24'),'09',1,0)),'99') "09",
to_char(sum(decode(to_char(first_time,'HH24'),'10',1,0)),'99') "10",
to_char(sum(decode(to_char(first_time,'HH24'),'11',1,0)),'99') "11",
to_char(sum(decode(to_char(first_time,'HH24'),'12',1,0)),'99') "12",
to_char(sum(decode(to_char(first_time,'HH24'),'13',1,0)),'99') "13",
to_char(sum(decode(to_char(first_time,'HH24'),'14',1,0)),'99') "14",
to_char(sum(decode(to_char(first_time,'HH24'),'15',1,0)),'99') "15",
to_char(sum(decode(to_char(first_time,'HH24'),'16',1,0)),'99') "16",
to_char(sum(decode(to_char(first_time,'HH24'),'17',1,0)),'99') "17",
to_char(sum(decode(to_char(first_time,'HH24'),'18',1,0)),'99') "18",
to_char(sum(decode(to_char(first_time,'HH24'),'19',1,0)),'99') "19",
to_char(sum(decode(to_char(first_time,'HH24'),'20',1,0)),'99') "20",
to_char(sum(decode(to_char(first_time,'HH24'),'21',1,0)),'99') "21",
to_char(sum(decode(to_char(first_time,'HH24'),'22',1,0)),'99') "22",
to_char(sum(decode(to_char(first_time,'HH24'),'23',1,0)),'99') "23"
from
v$log_history
GROUP by to_char(first_time,'YYYY-MON-DD'); 

Archivelog generation on a daily basis:

SQL> select trunc(COMPLETION_TIME,'DD') Day,

thread#,

round(sum(BLOCKS*BLOCK_SIZE)/1048576) MB,

round((sum(BLOCKS*BLOCK_SIZE)/1048576)/1024

) GB,

count(*) Archives_Generated from v$archived_log

group by trunc(COMPLETION_TIME,'DD'),thread# order by 1;

Archive log generation on an hourly basis:

SQL> select trunc(COMPLETION_TIME,'HH') Hour,

thread# ,

round(sum(BLOCKS*BLOCK_SIZE)/1048576) MB,

round(sum(BLOCKS*BLOCK_SIZE)/1048576)/1024 GB,

count(*) Archives

from  v$archived_log

group by trunc(COMPLETION_TIME,'HH'),thread#  order by 1 ;

Change ADDM/AWR Snapshot interval to 2 minute

sqlplus -s /NOLOG

set echo on

connect / as sysdba

exec DBMS_WORKLOAD_REPOSITORY.CREATE_SNAPSHOT();

grant dba to SH;

rem -- event to allow setting very short Flushing interval

alter session set events '13508 trace name context forever, level 1';

rem -- change INTERVAL setting to 2 minutes

rem -- change RETENTION setting to 6 hours (total of 180 snapshots)

execute dbms_workload_repository.modify_snapshot_settings(interval => 2,retention => 360);

rem -- play with ADDM sensitiveness

exec dbms_advisor.set_default_task_parameter('ADDM','DB_ACTIVITY_MIN',30);

alter user sh account unlock;

alter user sh identified by sh;

Automatic Maintaining SQL Tuning Advisor.

Posted by : Amit Kumar Srivastava – OCM11g

      It is always enabled by default, but the SQL Profiles are enabled unless that is ACCEPT_SQL_PROFILES modify the parameter.

- We can see the report and change the parameter through ACCEPT_SQL_PROFILES DBMS_AUTO_SQLTUNE package

- Reviewed the report

VARIABLE my_rept CLOB;

BEGIN

  :my_rept :=DBMS_AUTO_SQLTUNE.REPORT_AUTO_TUNING_TASK(

    begin_exec   => NULL,

    end_exec     => NULL,

    type         => 'TEXT',

    level        => 'TYPICAL',

    section      => 'ALL',

    object_id    => NULL,

    result_limit => NULL);

END;

/

PRINT :my_rept

- Enable  SQL Profiles automatically

- Before implementation in production you have to insure about Pro.. and Cons...

- Beware of.. doing it on production

BEGIN

  DBMS_AUTO_SQLTUNE.SET_AUTO_TUNING_TASK_PARAMETER

(parameter => 'ACCEPT_SQL_PROFILES',

value => 'TRUE'

);

END;

/

- For disable it,  use below command

BEGIN

  DBMS_AUTO_SQLTUNE.SET_AUTO_TUNING_TASK_PARAMETER

(parameter => 'ACCEPT_SQL_PROFILES',

 value => 'FALSE'

);

END;

/

Oracle 11gR2 ASM Installation

Introduction:

Posted by : Amit Kumar Srivastava (OCM11g)

This is a series of articles which contains several important enhancements to Automatic Storage Management in Oracle 11gR2.

In this ASM inside story articles, we are going to use Oracle Linux 5 as the guest operating system, Oracle 11gR2 as database version.

Oracle VirtualBox is our virtualization software.

Oracle Linux Installation

Download and install Oracle Linux.

The following rpm packages must be installed in your system:

binutils-2.17.50.0.6
compat-libstdc++-33-3.2.3
elfutils-libelf-0.125
elfutils-libelf-devel-0.125
elfutils-libelf-devel-static-0.125
gcc-4.1.2
gcc-c++-4.1.2
glibc-2.5-24
glibc-common-2.5
glibc-devel-2.5
glibc-headers-2.5
kernel-headers-2.6.18
ksh-20060214
libaio-0.3.106
libaio-devel-0.3.106
libgcc-4.1.2
libgomp-4.1.2
libstdc++-4.1.2
libstdc++-devel-4.1.2
make-3.81
sysstat-7.0.2
unixODBC-2.2.11
unixODBC-devel-2.2.11

Example:

For Automatic Memory Manager, make the shared memory file system big enough and add the “tmpfs” setting in the “/etc/fstab” file:

Add the following lines in the /etc/sysctl.conf file:

Run the following command to change the current kernel parameters:

Add the following lines to the “/etc/security/limits.conf” file:

Make sure the SELINUX=disabled in the “/etc/selinux/config” file.

System Users and Groups creation

Prepare Oracle binaries and software directories

Set environment variables for Oracle system user
Login as Oracle system user. Then, edit the file .bash_profile and add the following lines. After that, save the changes and run the command “source .bash_profile”:

Configure devices for ASM using file system files and loop devices

In this step we are going to:

1-      Create a directory to hold the files to be used as ASM devices, as Oracle system user.

2-      Create 4 files to be used as Asm devices, 1GB of size for each, as root system user.

3-      Check the created files, as root system user.

4-      Create the loop devices, as root system user.

5-      Bind the loop devices to raw devices, as root system user.

6-      Setup raw devices permissions and ownership, as root system user.

7-      Edit the “/etc/rc.local” file and add the same commands mentioned before. why? Because after a reboot, the ownership and permisions will change back to root and “-rw-r–r–” files attributes.

or

Else Use asmlib for Linux to avoid below configurations.

[oracle@amit.example.com]$ mkdir asmdisk

[root@amit.example.com]#  dd if=/dev/zero

of=/u01/app/oracle/product/11.2.0/amit.example.com/asmdisk/disk1 bs=1024k

count=1000

1000+0 records in

1000+0 records out

1048576000 bytes (1.0 GB) copied, 23.5853 seconds, 44.5 MB/s

[root@amit.example.com]#  dd if=/dev/zero

of=/u01/app/oracle/product/11.2.0/amit.example.com/asmdisk/disk2 bs=1024k

count=1000

1000+0 records in

1000+0 records out

1048576000 bytes (1.0 GB) copied, 22.9174 seconds, 45.8 MB/s

[root@amit.example.com]#  dd if=/dev/zero

of=/u01/app/oracle/product/11.2.0/amit.example.com/asmdisk/disk3 bs=1024k

count=1000

1000+0 records in

1000+0 records out

1048576000 bytes (1.0 GB) copied, 32.2912 seconds, 32.5 MB/s

[root@amit.example.com]#  dd if=/dev/zero

of=/u01/app/oracle/product/11.2.0/amit.example.com/asmdisk/disk4 bs=1024k

count=1000

1000+0 records in

1000+0 records out

1048576000 bytes (1.0 GB) copied, 36.1176 seconds, 29.0 MB/s

[root@amit.example.com]# /sbin/losetup /dev/loop1

/u01/app/oracle/product/11.2.0/amit.example.com/asmdisk/disk1

[root@amit.example.com]# /sbin/losetup /dev/loop2

/u01/app/oracle/product/11.2.0/amit.example.com/asmdisk/disk2

[root@amit.example.com]# /sbin/losetup /dev/loop3

/u01/app/oracle/product/11.2.0/amit.example.com/asmdisk/disk3

[root@amit.example.com]# /sbin/losetup /dev/loop4

/u01/app/oracle/product/11.2.0/amit.example.com/asmdisk/disk4

[root@amit.example.com]# raw /dev/raw/raw1 /dev/loop1

/dev/raw/raw1:  bound to major 7, minor 1

[root@amit.example.com]# raw /dev/raw/raw2 /dev/loop2

/dev/raw/raw2:  bound to major 7, minor 2

[root@amit.example.com]# raw /dev/raw/raw3 /dev/loop3

/dev/raw/raw3:  bound to major 7, minor 3

[root@amit.example.com]# raw /dev/raw/raw4 /dev/loop4

/dev/raw/raw4:  bound to major 7, minor 4

[root@amit.example.com]# cd /dev/raw

[root@amit raw]# ls -ltr

total 0

crw------- 1 root root 162, 1 Feb 28 20:27 raw1

crw------- 1 root root 162, 2 Feb 28 20:27 raw2

crw------- 1 root root 162, 3 Feb 28 20:28 raw3

crw------- 1 root root 162, 4 Feb 28 20:28 raw4

[root@amit raw]# ---change owner raw devices

[root@amit raw]# chown -R oracle:oinstall *

[root@amit raw]# chmod 660 *

[root@amit raw]# ls -ltr

total 0

crw-rw---- 1 oracle oinstall 162, 1 Feb 28 20:27 raw1

crw-rw---- 1 oracle oinstall 162, 2 Feb 28 20:27 raw2

crw-rw---- 1 oracle oinstall 162, 3 Feb 28 20:28 raw3

crw-rw---- 1 oracle oinstall 162, 4 Feb 28 20:28 raw4

[root@amit tmp]# chown -R oracle:oinstall

/u01/app/oracle/product/11.2.0/amit.example.com/*

[root@amit raw]# vi /etc/rc.local

Also add in the “/etc/rc.local” file “chown -R oracle:oinstall *” and “chmod 660 *” lines:

Note that to delete a loop device, you can use the following sintax: “losetup –d /dev/loopX”

Oracle binary files installation

This is my own method I use; I first install the Oracle binaries, install the grid infrastructure for standalone server and then use the dbca to install the Oracle database.

To install the Oracle binaries, you can follow the screen shots below.

[oracle@amit database]$ ./runInstaller

Insert you Oracle metalink credentials: (for testing or demonstration you can skip this)

Choose to install the Oracle software only:

Choose a single instance installation against Real Application Clusters installation method:

Select the Oracle software languages:

Choose enterprise edition:

Confirm the Oracle base and software locations:

Choose your OSDBA and OSOPER groups:

Automatic verification of the configuration requirements:

Confirm the global settings resume:

The installation progress:

Open a new terminal window, login as root and run the “*.root.sh” scripts. After that click on “ok”.

Now the Oracle binaries are installed. Let’s configure ASM when installing the Oracle grid infrastructure for standalone server.

Grid Infrastructure Installation for standalone server

To configure ASM, we are going to install the grid infrastructure for standalone server.
The following screen shots best describe the steps you need to perform.

[oracle@amit grid]$ ./runInstaller

Choose to install Grid Infrastructure for standalone server option:

Select the software languages:

Select the ASM disk files we already created in the previous section:

Choose the sys password for SYSASM new privilege appeared with Oracle 11g version.

Choose the operating system groups for OSDBA, OSOPER and OSASM.

Confirm the Oracle base and the grid infrastructure software locations:

Configuration requirements verification status:

Confirm the Oracle grid infrastructure resume:

Installation progress:

Open a new terminal window, login as root and run the “root.sh” script. After that click on “ok”.

[root@amit ~]# /u01/app/oracle/product/11.2.0/grid/root.sh

Running Oracle 11g root.sh script...

The following environment variables are set as:

   ORACLE_OWNER= oracle

   ORACLE_HOME=  /u01/app/oracle/product/11.2.0/grid

Enter the full pathname of the local bin directory: [/usr/local/bin]:

The file "dbhome" already exists in /usr/local/bin.  Overwrite it? (y/n)

[n]:

The file "oraenv" already exists in /usr/local/bin.  Overwrite it? (y/n)

[n]:

The file "coraenv" already exists in /usr/local/bin.  Overwrite it? (y/n)

[n]:

Entries will be added to the /etc/oratab file as needed by

Database Configuration Assistant when a database is created

Finished running generic part of root.sh script.

Now product-specific root actions will be performed.

2014-03-01 11:29:22: Checking for super user privileges

2014-03-01 11:29:22: User has super user privileges

2014-03-01 11:29:22: Parsing the host name

Using configuration parameter file:

/u01/app/oracle/product/11.2.0/grid/crs/install/crsconfig_params

Creating trace directory

/home/oracle/.bash_profile: line 1: racle: command not found

LOCAL ADD MODE

Creating OCR keys for user 'oracle', privgrp 'oinstall'..

Operation successful.

CRS-4664: Node amit successfully pinned.

Adding daemon to inittab

CRS-4123: Oracle High Availability Services has been started.

ohasd is starting

amit     2014/03/01 11:33:09

/u01/app/oracle/product/11.2.0/grid/cdata/amit/backup_20110301_113309.olr

Successfully configured Oracle Grid Infrastructure for a Standalone Server

Updating inventory properties for clusterware

Starting Oracle Universal Installer...

Checking swap space: must be greater than 500 MB.   Actual 1702 MB    Passed

The inventory pointer is located at /etc/oraInst.loc

The inventory is located at /u01/app/oraInventory

'UpdateNodeList' was successful.

Installation progress:

The installation confirmation message:

Database installation

Using dbca, we are going to install the Oracle 11gR2 database.
The screen shots below best describe the installation steps.

[oracle@amit database]$ dbca

Click next:

Choose to create a database against a tempalte:

Choose your database template:

Insert your Global database name, SID identifier:

Choose to configure Enterprise manager, database control:

Insert and confirm the user accounts password:

Choose ASM for storage management type and +DATA for Oracle managed files database area:

Specify ASMSNMP password:

In the recovery configuration screen click next:

Choose or not to add the sample schemas in your database:

Specify the global memory size and the database character Set:

Confirm and click next:

Choose to create a database and generate a creation database script for future quick database installation:

Confirm the next window:

Verify your database installation using ASM to manage the storage: