Monthly Archives: January 2016

11g, 12c, Administration, storage

Oracle 12c – Use Direct NFS

Leave a comment

In Oracle 11g a native NFS client has been placed in Oracle Database kernel.  It improves performance when Network File Systems are in use.  Oracle NFS kernel optimizes and eliminates overhead of NFS, eliminating the need to tune NFS parameters at OS layer.

 

To use Direct NFS, the NFS file system must be mount and available by OS kernel .  The Oracle Direct NFS Client manages Oracles access to drives, by a configuration file call orafstab or mount tab file /etc/mtab on linux.  The file $ORACLE_HOME/dbs/orafstab would specify the direct NFS client settings for a single database.  Oracle looks for settings in /etc/oranfstab then Oracle reads /etc/mtab to identify available NFS mounts.

 

To use Direct NFS mount setting you have to replace standard Oracle Disk Manager (ODM) library with one supporting Direct NFS Client.

 

Example

 

[oracle@linux2 ~]$ $ORACLE_HOME/dbs/orafstab

server: MyNFSServer1

path: 192.168.1.1

path: 192.168.1.2

path: 192.168.1.3

path: 192.168.1.4

export: /vol/ oradata1

mount: /mnt/ oradata1

 

Once the file exists, the following commands will enable the Direct NFS Client ODM Library

 

[oracle@linux2 ~]$ cd $ ORACLE_HOME/ lib

[oracle@linux2 ~]$ cp libodm11. so libodm11. so_stub

[oracle@linux2 ~]$ ln –s libnfsodm11. so libodm11. so

 

 

 

Larry Catt

OCP

12c, Performance

Oracle 12c – Difference between ILM and Valid-Time Temporal

Leave a comment

Oracle 12c has both ILM and Temporal Validation which can be confused in their use.   The definitions of each are below along with their use:

 

  • ILM is used to improve the utilization of storage devices through compression and access tiering of data. Data accessed the least is compressed and placed on slow devices, while data accessed frequently is not compressed and stored on faster devices.
  • Temporal Validity allows you define a valid time dimension for each row of a table.

Larry Catt

OCP

12c, backup/recovery, CDB, Multitenant, RMAN

RMAN Backup of CDB in Oracle 12c Multitenant Database

Leave a comment

RMAN backup of a CDB in Oracle 12c Multitenant Database can be performed from the Recovery Manager utility or EM Cloud Control.    This procedure shows the steps of performing a CDB backup from RMAN utility.

 

  1. Logon to your oracle database server as the oracle software owner.

 

[root@linux2 larry]# su – oracle

Last login: Thu Jan  3 11:02:25 EDT 2016 on pts/1

[oracle@linux2 ~]$

 

  1. Enter the RMAN utility.

 

[oracle@linux2 ~]$ rman

 

Recovery Manager: Release 12.1.0.2.0 – Production on Tue Jan 15 12:26:22 2016

 

Copyright (c) 1982, 2014, Oracle and/or its affiliates.  All rights reserved.

 

RMAN>

 

  1. Connect to your target CDB database with SYSBACKUP privilege.

 

RMAN> connect target ‘”sys/@cdb1 as sysbackup”‘

 

target database Password:

connected to target database: CDB1 (DBID=898165058)

 

RMAN>

 

  1. Validate the database you are connected to with SQL command: select name from v$database.

 

RMAN> select name from v$database;

 

using target database control file instead of recovery catalog

NAME

———

CDB1

 

RMAN>

 

  1. Now that you are connected to the database you can perform the following backups:
    • Example of entire CDB backup and all PDBs:

RMAN> BACKUP DATABASE;

  • Example of CDB backup and all PDBs, switch online redo logs and include archivelog.

RMAN>  BACKUP DATABASE PLUS ARCHIVELOG;

  • Backup just the root.

RMAN>  BACKUP DATABASE ROOT;

 

  1. This completes backup of CDB using RMAN utility.

 

Larry Catt

OCP

 

 

12c, CDB, PDB, tablespaces

Managing tablespaces in CDB and PDB

Leave a comment

An Oracle 12c multitenant database consist of a CDB which houses one to many PDBs serving end user applications.    Thus objects such as tablespaces can become a little confusing to manage with questions of do they belong to CDB or PDB or both.   This article tries to clear this subject and give a brief tutorial in managing tablespaces in the multitenant architecture.

 

Rules to tablespaces in CDB and PDB:

 

  1. Permanent tablespace can only be associated with a single container (CDB or PDB).
  2. When you create a tablespace in a container, it belongs to that container.
  3. CDB can have only one active undo tablespace or one active undo tablespace for each instance of RAC.
  4. Only one default temporary tablespace for CDB.
  5. PDBs can use the CDB’s temporary tablespace.
  6. Each PDB can have its own default temporary tablespace.
  7. PDBs can have their own tablespaces.
  8. Permanent Default tablespace in PDB can still use the ALTER DATABASE command, but it is preferred to use the command:

ALTER PLUGGABLE DATABASE DEFAULT TABLESPACE tbs_user_pdb;

  1. CDB will have only one temporary tablespace.
  2. To modify the temporary tablespace for CDB, the current container must be root.
  3. You can have other temporary tablespaces in CDB with specific users assigned to them, but only one default.
  4. Each PDB can have only one temporary tablespace and when PDB is unplugged so is its temporary tablespace.

 

 

 

Managing tablespaces in Multitenant architecture.

 

  1. Logon as the oracle software owner.

[larry@linux2 ~]$ su – oracle

Password:

Last login: Tue Oct 25 09:29:48 EDT 2015 on pts/1

[oracle@linux2 ~]$

 

  1. Connect to your CDB with sqlplus and show current container.

 

[oracle@linux2 ~]$ sqlplus / as sysdba

 

SQL*Plus: Release 12.1.0.2.0 Production on Wed Oct 26 10:02:53 2015

 

Copyright (c) 1982, 2014, Oracle.  All rights reserved.

 

 

Connected to:

Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 – 64bit Production

With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

 

SQL> show con_name

 

CON_NAME

——————————

CDB$ROOT

SQL>

 

  1. Create a new permanent tablespace in the root container of cdb1_user, set it as the default tablespace for root.

 

SQL> create tablespace cdb1_users

datafile ‘/opt/app/oradata/cdb1_users.dbf’ size 10m;  2

 

Tablespace created.

 

SQL> ALTER DATABASE DEFAULT TABLESPACE cdb1_users;

 

Database altered.

 

SQL>

 

  1. View tablespaces from the root container and default tablespaces.

 

SQL> select tablespace_name from dba_tablespaces;

 

TABLESPACE_NAME

——————————

SYSTEM

SYSAUX

UNDOTBS1

TEMP

USERS

CDB1_USERS

 

6 rows selected.

 

SQL> select distinct(default_tablespace) from dba_users;

 

DEFAULT_TABLESPACE

——————————

SYSAUX

CDB1_USERS

SYSTEM

 

 

  1. Change to one of your pluggable database and note that you cannot see the newly create tablespace cdb1_users.

 

SQL> alter session set container=pdb1;

 

Session altered.

 

SQL> select tablespace_name from dba_tablespaces;

 

TABLESPACE_NAME

——————————

SYSTEM

SYSAUX

TEMP

USERS

 

SQL>

 

  1. Create a tablespace tablespace in your PDB and move back to your root container.

 

SQL> create tablespace pdb1_users

datafile ‘/opt/app/oradata/pdb1_users.dbf’ size 10m;  2

 

Tablespace created.

 

SQL> select tablespace_name from dba_tablespaces;

 

TABLESPACE_NAME

——————————

SYSTEM

SYSAUX

TEMP

USERS

PDB1_USERS

 

SQL> alter session set container=CDB$ROOT;

 

Session altered.

 

SQL> select tablespace_name from dba_tablespaces;

 

TABLESPACE_NAME

——————————

SYSTEM

SYSAUX

UNDOTBS1

TEMP

USERS

CDB1_USERS

 

6 rows selected.

 

SQL>

 

 

  1. This completes the management of tablespaces within the multitenant database architecture.

 

Larry Catt

OCP

12c, CDB, Multitenant, PDB

Benefits of Oracle CDB and PDB

Leave a comment

New to Oracle 12c is the feature of multitenant databases or CBD/PDB implementation to add a form of Virtualization to the Oracle RDBMS world.    This new feature is designed to reduce the number of physical server machines currently required to house normal database suites.    It is fairly common to have a large number of small data stores in an organization to server various system needs and though the resource requirements of these data stores is relatively low, they still exist on a single VM or physical server.   CDB and PDB technology looks to resolve this issue by combining these databases into a single instance which appears to be stand-alone to the outside world.   This article lists and describes the benefits of a CDB/PDB implementation.

 

  1. Database Consolidation – Multitenant Databases implementations can consolidate several small databases on separate servers into a CDB using one server. Allowing the organization to minimize the number of physical or virtual servers being used without reducing the number of application data stores being accessed.

 

  1. Reduced Cost – Reduction in the number of physical and virtual machines in use saves money in hardware, labor costs, floor space, and a multitude of other ways.

 

 

  1. Rapid Implementation – Implementation of a new data store or the movement of existing database is significantly reduced both in complexity and resource requirements. New PDB can be created on the fly to test various applications and removed when no longer needed.

 

  1. Simplified Management – Reduced number of servers and database instances easies the burden placed on administrators and management to ensure proper operations of a data center. Thus allowing of an increased level of performance to end users.

 

  1. Separation of administrative duties – Administration of the CDB and PDB are branched along resource lines where CDB control memory and processes and allocate them to various PDBs based on needs. CDB administration consists of managing performance related to OS and internal processes.  PDB administration is focused on performance related to application using the data store.   Thus creating a well-defined separation of administrative duties between CDB and PDB.   DBA responsible for CDB administration are focused on system/processes performance improvements and DBA responsible for individual PDB are focused on application implementation performance.

 

  1. Simplified Patching – Patching/upgrade single CDB database which contains multiple PDBs and not multiple separate databases.

 

 

Larry Catt

OCP

12c, Administration, storage

Oracle 12c Moving or renaming Datafile online

Leave a comment

In oracle 12c you can now move a datafile while it is online.   This procedure will list the rules of moving or renaming a datafile online and show some examples.

 

Rules

  1. If a file of the same name already exists the ALTER DATABASE statement will fail with an error.
  2. If the REUSE clause is used with the ALTER DATABASE statement and the file already exists, the existing file will be overwritten.
  3. When the ALTER DATABASE MOVE DATAFILE statement is used a copy of the file is made during the process and adequate space must be available for both the original and the copy for operation to succeed.
  4. At the end of the operation, the original copy is deleted. Unless the KEEP clause is used.

 

 

Examples

  1. Logon as the oracle software owner and logon to sqlplus.

 

  1. Display all datafiles with command: select file_name from dba_data_files;

 

SQL> select file_name from dba_data_files;

FILE_NAME

——————————————————————————–

/u01/oradata/orcl/orcl/system01.dbf

/u01/oradata/orcl/orcl/sysaux01.dbf

/u01/oradata/orcl/orcl/users01.dbf

/u01/oradata/orcl/orcl/undotbs01.dbf

SQL>

 

  1. Move the sysaux tablespace datafile from sysaux01.dbf to sysaux01_test.dbf.

 

SQL> ALTER DATABASE MOVE DATAFILE ‘/u01/oradata/orcl/orcl/sysaux01.dbf’

TO ‘/u01/oradata/orcl/orcl/sysaux01_test.dbf’;  2

Database altered.

SQL>

 

  1. Display all datafiles with command:  select file_name from dba_data_files;   NOTE:  dbf has been replaced with sysaux01_test.dbf.

 

SQL> select file_name from dba_data_files;

FILE_NAME

——————————————————————————–

/u01/oradata/orcl/orcl/system01.dbf

/u01/oradata/orcl/orcl/sysaux01_test.dbf

/u01/oradata/orcl/orcl/users01.dbf

/u01/oradata/orcl/orcl/undotbs01.dbf

SQL>

 

  1. To overwrite an existing file, add the REUSE clause.

 

ALTER DATABASE MOVE DATAFILE ‘/u01/oradata/orcl/orcl/sysaux01.dbf’

TO ‘/u01/oradata/orcl/orcl/sysaux01_test.dbf’  REUSE; 

 

 

  1. To keep the old file, add the KEEP clause.

 

ALTER DATABASE MOVE DATAFILE ‘/u01/oradata/orcl/orcl/sysaux01.dbf’

TO ‘/u01/oradata/orcl/orcl/sysaux01_test.dbf’ KEEP; 

 

  1. This completes movment of online datafiles.

 

 

Larry Catt

OCP

12c, ASM

Oracle 12c – Manage ASM disks and disk groups

Leave a comment

An ASM disk group consists of multiple disks and is the base object ASM manages.  Disk Groups contain information for ASM to manage drive space.  Sub-components of a Disk Group are Disks, files, and allocation units.  A disk group can contain files from several different databases and a single database can use files from multiple disk groups.

Disk Group attributes are parameters that belong to disk group and not the ASM instance.  Common ASM disk group attributes are:

  • ENABLED – Can be TRUE or FALSE – determines if access control is possible. Default is false and have to use alter disk group to change.
  • UMASK – determine the permission mask on created oracle ASM user file. Permission for owner, users in same user group, and users not in users group.  Applies to all files on a disk group.
  • AU_SIZE – File extent consists of one or more allocation units. Oracle ASM file consists of one or more file extents.   When creating disk group, you can set the Oracle ASM allocation unit size, value can be 1, 2, 4, 8,16,32,64 MB, depending on the specific disk group compatibility level.
  • ASM – The attribute defines the version used. ASM version must be greater or equal to COMPATIBLE.RDBS version to access disks.  If COMPATIBLE.ASM is 11.0 any database instance with version of 10.1 or higher can access, but to manage disk group it must be 11 or higher.
  • RDBMS – controls the format of message between ASM instance and database instance. Defines the minimum database client release that can access a disk group.
  • TYPE – Id disk group type: data, recovery, or system. Determines the distance to nearest neighbor disk in failure group mirror.   Default is data giving value of 1, recovery – 3, and system – 5 to nearest disk group.
  • DISK_REPAIR_TIME – Amount of time a disk can be unavailable to failure before it is dropped from disk group.  To use both COMPATIBLE.ASM and COMPATIBLE.RDBMS must be set to 11.1 or higher.  Cannot set during disk group creation, but use ALTER DISKGROUP… SET ATTRIBUTE.    Default is 3.6 hours.  If either ASM or RDBMS are set to less then 11.1, disk is immediately dropped from disk group.

 

CREATE DISKGROUP SQL command is used to create diskgroups and specifies the following:

  1. Unique diskgroup name.
  2. Redundancy level:
    1. NORMAL REDUNDANCY – 2 way mirroring is the default
    2. HIGH REDUNDANCY – 3 way mirroring
    3. EXTENAL REDUNDANCY – no mirroring
  3. Disks to format for diskgroup.
  4. Optional – specify disk to specific failure groups.
  5. Optional – specify type of failure group.
  6. Optional – specify diskgroup attributes software compatibility or allocation unit size.

 

Example:

CREATE DISKGROUP data NORMAL REDUNDANCY

FAILGROUP fg1

DISK ‘/u01/ diska1’ NAME diska1,

‘/u01/ diska2’ NAME diska2,

‘/u01/ diska3’ NAME diska3

FAILGROUP fg2

DISK ‘/u01/ diskb1’ NAME diskb1,

‘/u01/ diskb2’ NAME diskb2,

‘/u01/ diskb3’ NAME diskb3

ATTRIBUTE ‘au_size’ =’ 2M’, ‘compatible.asm’ = ‘11.2’, ‘compatible.rdbms’ = ‘11.2’;

 

The ALTER DISKGROUP SQL statement enables you to alter a disk group configuration. It is possible to add, resize, or drop disks while the database remains online. Multiple operations in a single ALTER DISKGROUP statement are both possible and recommended. Grouping operations in a single ALTER DISKGROUP statement can reduce rebalancing operations. Oracle ASM automatically rebalances a disk group when its configuration changes. The V$ASM_OPERATION view allows you to monitor the status of rebalance operations. The following command adds two more disks to the data diskgroup.

 

ALTER DISKGROUP data ADD DISK ‘/u01/ diska4’ NAME diska4, ‘/u01/ diska5’ NAME diska5;

 

When rebalancing a disk group, if the POWER clause is not specified in an ALTER DISKGROUP statement, or if a rebalance is executed implicitly because a disk has been added or dropped, the ASM_POWER_LIMIT initialization parameter determines the power used. The value of this parameter can be adjusted dynamically. Higher power values will cause a rebalance operation to complete faster, but consumes more processing and I/ O resources. The default value of 1 minimizes disruption to other applications.

 

 

Larry Catt

OCP