DBA Blogs

Personal Edition for Linux - 11gR2 and 12c

Hans Forbrich - Tue, 2013-08-06 12:13
See MOS Note "How to Install Oracle Database Personal Edition on Linux?" (Doc ID 1574396.1)
 
Thank you Oracle.
Categories: DBA Blogs

My book: Oracle Database 12c Backup and Recovery Survival Guide

Oracle NZ - Mon, 2013-08-05 18:15

It was a long journey of hard work and team work that now has finished, as a result you will find my book Oracle Database 12c Backup and Recovery Survival Guide that I wrote with the help of my good friend Aman Sharma.

I would also take this opportunity to recognize the great work done by many good friends involved in the technical review of the book:

  • Tom Kyte
  • Arup Nanda
  • Robert Freeman
  • Laurent Schneider
  • Gokhan Atil
  • Wissem El Khlifi
  • Bjorn Naessens
  • Alessandro Parisi

Thank you so much my friends for all comments, suggestions, corrections and support during this long journey!

You can pre-order the book here: http://www.packtpub.com/oracle-database-12c-backup-recovery-survival-guide/book

SnapCrab_NoName_2013-9-11_21-47-0_No-00

Here is some information about the book:

The three main responsibilities of a successful DBA are to ensure the availability, recoverability, and performance of any database. To ensure the recoverability of any database, a DBA needs to have a strong backup and recovery skills set. Every DBA is always looking for a reference book that will help them to solve any possible backup and recovery situation that they can come across in their professional life.

Oracle Database 12c Backup and Recovery Survival Guide has the unique advantage to be a reference to all Oracle backup and recovery options available, making it essential for any DBA in the world. If you are new to Oracle Database, this book will introduce you to the fantastic world of backup and recovery that is vital to your success. If you are an experienced DBA, this book will become a reference guide and will also help you to learn some possible new skills, or give you some new ideas you were never aware about. It will also help you to easily find the solution to some of the most well known problems you could find during your career as a DBA. This book contains useful screenshots, scripts, and examples that you will find more than useful.

Most of the books currently available in the market concentrate only on the RMAN utility to backup and recovery. This book will be an exception to the rule and will become a must-have reference, allowing you to design a real and complete backup and recovery strategy. It covers the most important topics on Oracle database such as backup strategies, Nologging operations, new features in 12c, user managed backups and recoveries, RMAN (including reporting, catalog management, troubleshooting, and performance tuning), advanced data pump, Oracle Enterprise Manager 12c and SQL Developer.

“Oracle Database 12c Backup and Recovery Survival Guide” contains everything a DBA needs to know to keep data safe and recoverable, using real-life scenarios.



Tags:  

Del.icio.us
Facebook
TweetThis
Digg
StumbleUpon

Copyright © OracleNZ by Francisco Munoz Alvarez [My book: Oracle Database 12c Backup and Recovery Survival Guide], All Right Reserved. 2016.
Categories: DBA Blogs

Oracle Database 12c - notes 1

Hans Forbrich - Sat, 2013-07-06 21:02
I now have a functional Oracle Database 12c Lab environment which consists of a bunch of x86_64 computers networked together.  (And some that are still pending, for the RAC exercise.)

Configuration:

SAN:
  1x AMD-based homegrown computer running OpenFiler 2.99 as iSCSI server

Monitoring and Support (licenses includes with Database):
  1x Oracle Linux 5u6 for OEM 12c Cloud Control
   - DB 11gR2
   - OEM 12.1.0.3
   - Cloud Control agent pushed to all machines BEFORE any additional s/w set up

  1x Oracle Linux 5u8 for Oracle Internet Directory
  - DB 11gR2,
  - RCU 11.1.1.7.0
  - WebLogic Server 10.3.6
  - Oracle Identity Management Suite 11.1.1.7.0 selected for OID
  - all targets displaying on Cloud Control

Test Machine 1
  - Oracle Linux 5u8, 48GB RAM
  - Standalone Grid installed for ASM
  - ASM 12.1.0.1
  - 1x DB 12.1.0.1 EE Container using ASM with 1 Pluggable DB 
  - 1x DB 12.1.0.1 EE Container using File System with 1 Pluggable DB
  - all targets displaying on Cloud Control

Test Machine 2
  - Oracle Linux 5u8, 4GB RAM
  - 1x DB 12.1.0.1 EE Container using File System with 1 Pluggable DB
  - all targets displaying on Cloud Control and EM Express

Test Machine 3
  - Oracle Linux 6u3, 4GB RAM
  - 1x DB 12.1.0.1 SE Container using File System with 1 Pluggable DB
  - 1x DB 12.1.0.1 EE non-Container using File System
  - all targets displaying on Cloud Control and EM Express

(Still to come:
   Test machine 4 will host GSM.
   Test machines 5-7 will have OVM and RAC 12c.
)

Results:

     84 Targets in OEM Cloud Control, all showing Green!

Initial Notes: 
  1. I needed to upgrade to OEM 12c Cloud Control 12.1.0.3 as 12.1.0.2 did not have the plugins to monitor Database 12c (production release). 
  2. Standard Edition supports 1 (one) pluggable database, if you chose the container architecture.
  3. Selecting both Cloud Control AND EM-Express during DBCA screws up EM-Express
    1. You need to install using EM-Express ONLY and discover manually with CC later.
  4. Separation of duty configuration works - almost correctly.

Categories: DBA Blogs

OpenFiler - lost iSCSI disks after reboot. How To Restore

Hans Forbrich - Sat, 2013-07-06 20:40
I've been using OpenFiler 2.99 as a NAS and iSCSI server for some tests.  

A few days ago I had a power outage and that caused me some problems, so I got another UPS and added the OpenFiler system to the UPS.  Unfortunately, even though  I cleanly shut the filer down, it still caused the same problems as before.

This blog is more of a record for me, when I need to redo this exercise.  

Environment:
  OpenFiler 2.99  x86_64 NAS Appliance
    - obtained from http://openfiler.com/community/download
    - built on a home-brew system with 1 500GB base drive &  4x1.5TB Seagate SATA drives

Symptoms:
  After reboot, iSCSI disks were not presented.

Detailed symptoms & steps, as root:





1)  The 4 SATA drives were visible, and the partitions were listed in /dev
      (/dev/sdb1 -> /dev/sde1).  However, pvdisplay could not see the partitions.

     ran fdisk for all disks and rewrote the partition table, and reran pvdisplay

 # fdisk /dev/sdb
        ...
 Command (m for help):  w
 # pvdisplay

2) The Logical volumes were there but 'Status not available'

  ran the vgchange command.

# vgchange -a y
# lvdisplay

3) The iSCSI partitions still not being presented, apparently because the LUNs were stuck.

Command Line, as root
  went to /opt/openfiler/iscsi/targets
  made a backup of iscsi_settings.xml
  removed all the
 
OpenFiler admin web site (https://myhost:446/)
  Services > Services Section : Manage Services > iSCSI Target ... stopped and started
  Volumes > Volumes Section : iSCSI Targets > LUN Mapping ... remapped all LUNs

After remapping, all were visible again.

Categories: DBA Blogs

Why use OVM for Oracle Databases

Oracle NZ - Tue, 2013-07-02 23:32

Some time ago I made a benchmark exercise to compare the performance of an Oracle Database running in a bare metal environment versus a virtualized environment to clear some of the main questions our team and clients in Revera have, such as:

  • Does an Oracle Database performs well on a virtualized environment?
  • What virtualization technology is more stable and allows an Oracle database to perform faster?
  • What is the performance difference between using a bare metal and a virtualized guest?
  • Is it safe to run a production database in a virtualized environment?

Here you can find the results and the answers to the questions above: http://oraclenz.org/wp-content/uploads/2013/07/Why-use-OVM-Revera.pdf

 

Regards,

 

Francisco Munoz Alvarez



Tags:  , , ,

Del.icio.us
Facebook
TweetThis
Digg
StumbleUpon

Copyright © OracleNZ by Francisco Munoz Alvarez [Why use OVM for Oracle Databases], All Right Reserved. 2016.
Categories: DBA Blogs

Playing with VirtualBox, Oracle 12c (12.1.0.1) and OEL 6.4 – Part 1

Oracle NZ - Mon, 2013-07-01 23:22

Now that 12c was released it’s time to install in on OEL 6.4 64 bits OS. Let’s start playing with this.

Before we start with the creation of the VM we will need to download some software first, such as:

Now that we have all the required software, let’s use the following steps to create our virtual machine using VirtualBox.

1 – Start VirtualBox and click on the option [New].

2 – Name the Virtual Machine and select the type of OS (Oracle Linux) and click [Next].

SnapCrab_NoName_2013-6-28_12-20-49_No-00

3 – Enter the amount of RAM for the VM. For this example I’m entering 4096MB if you do not have this amount of memory to use, please try with 2048 MB. An click [Next].

SnapCrab_NoName_2013-6-28_12-21-55_No-00

4 – Now is time to create the virtual disk for the VM. Select Create a Virtual Hard Drive now and click [Create].

SnapCrab_NoName_2013-6-28_12-22-30_No-00

5 – Select a hard drive type to be created, select VMDK and click [Next].

SnapCrab_NoName_2013-6-28_12-22-51_No-00

6 – Select the Dynamically allocated option and click [Next].

SnapCrab_NoName_2013-6-28_12-23-11_No-00

7 – Now let’s select the file allocation and size for our disk.  Let’s enter 50 GB for the disk Data and click [CREATE] to create the VM.

SnapCrab_NoName_2013-6-28_12-23-43_No-00

8 – Select the VM just created, than click [Settings] , select [Storage] , [ADD CD/DVD] , add the OEL 6.4 64 bit ISO image and click [OK].

SnapCrab_NoName_2013-6-28_12-27-18_No-00

9 – Due that I have a god number of CPUs available on my laptop, I will edit the VM to have 2 vCPUs. (This is an optional step)

SnapCrab_NoName_2013-6-28_12-28-47_No-00

10 – Start  the VM and click on the option [Install or upgrade an existing system].

SnapCrab_NoName_2013-6-28_12-29-56_No-00

11 – Select [Skip] the Disk Test.

SnapCrab_NoName_2013-6-28_12-30-48_No-00

12 – The installation Welcome Screen will appear. Please click [Next].

SnapCrab_NoName_2013-6-28_12-31-31_No-00

13 – Select the installation Language. For this example we will select [English] and click [Next].

SnapCrab_NoName_2013-6-28_12-31-57_No-00

14 – Select the VM Keyboard to be use [U.S. English] and click [Next].

SnapCrab_NoName_2013-6-28_12-32-23_No-00

15 – Select [Basic Storage Devices] and click [Next].

SnapCrab_NoName_2013-6-28_12-32-53_No-00

16 – You will receive a warning message that the device bellow may contain data. Click [Yes, discard any data].

SnapCrab_NoName_2013-6-28_12-33-28_No-00

17 – Enter the Host name of the VM, For this example I will use oracle12c and click [Next].

SnapCrab_NoName_2013-6-28_12-34-11_No-00

18 – Select your time zone and click [Next].

SnapCrab_NoName_2013-6-28_12-34-45_No-00

19 – Enter the Root use password. For this example I used “oracle” and click [Next].

SnapCrab_NoName_2013-6-28_12-35-20_No-00

20 – For the type of installation select [Replace Existing Linux System(s)] and click [Next].

SnapCrab_NoName_2013-6-28_12-36-31_No-00

21 – Once again you will receive a warning message, this time saying that  the partitions will be written to disk. Please click on [Write changes to disk].

SnapCrab_NoName_2013-6-28_12-37-9_No-00

22 – In the next screen select [Basic Server], [Customize now]  and click [Next]

SnapCrab_NoName_2013-6-28_12-38-39_No-00

23 -  Now we will select the following packages group on our installation:

  • Base System > Base
  • Base System > Compatibility libraries
  • Base System > Hardware monitoring utilities
  • Base System > Large Systems Performance
  • Base System > Network file system client
  • Base System > Performance Tools
  • Base System > Perl Support
  • Servers > Server Platform
  • Servers > System administration tools
    • Select the Package oracle-rdbms-server-11gR2-preinstall due that the 12c package still not available yet and this will work just fine for this installation.
  • Desktops > Desktop
  • Desktops > Desktop Platform
  • Desktops > Fonts
  • Desktops > General Purpose Desktop
  • Desktops > Graphical Administration Tools
  • Desktops > Input Methods
  • Desktops > X Window System
  • Development > Additional Development
  • Development > Development Tools
  • Applications > Internet Browser

SnapCrab_NoName_2013-6-28_12-41-59_No-00

24 – Click [Next] and the Installation Process will start.

SnapCrab_NoName_2013-6-28_12-47-40_No-00

25 – The Congratulations screen will appear and you will need to click [Reboot].

SnapCrab_NoName_2013-6-28_13-7-49_No-00

26 – On the OEL 6 Welcome page click [Forward].

SnapCrab_NoName_2013-6-28_13-8-51_No-00

27 – Review the License Information, select [Yes, I agree to the License Agreement] and click [Forward].

SnapCrab_NoName_2013-6-28_13-9-17_No-00

28 – In the Set Up Software Updates page just click [Forward].

SnapCrab_NoName_2013-6-28_13-9-51_No-00

29 – In the Create User page just click [Forward].

SnapCrab_NoName_2013-6-28_13-10-26_No-00

30 – Enter the Date and Time for your system and click [Forward].

SnapCrab_NoName_2013-6-28_13-10-58_No-00

31 – Do not select Enable Kdump and click [Finish] to reboot the machine once again.

SnapCrab_NoName_2013-6-28_13-11-33_No-00

32 – After the reboot connect to the machine using the root user.

SnapCrab_NoName_2013-6-28_13-12-14_No-00

33 – Disable the Firewall on [Administration] –> [Firewall].

SnapCrab_NoName_2013-6-28_13-13-15_No-00

34 – Select [Devices] –> [Install Guest Additions] to install the Virtual Box Guest Additions.

SnapCrab_NoName_2013-6-28_13-14-20_No-00

35 – Transfer the Database files to the virtual machine and unzip them.

  • $ unzip linuxamd64_12c_database_1of2.zip
  • $ unzip linuxamd64_12c_database_2of2.zip

36 – Configure the Host file as per the screenshot bellow.

SnapCrab_NoName_2013-6-28_14-43-26_No-00

37 – Use YUM (public yum)  to automatically setup the Oracle prerequisites if not used when installing the OS.

SnapCrab_NoName_2013-6-28_13-38-56_No-00

*** As you can see above in the screen shot, the oracle-rdbms-server-12cR1-preinstall package still not available. For this scenario we will use the oracle-rdbms-server-11gR2-preinstall package that will do the trick for us.

38 – Now we will need to setup the user oracle password .

$ passwd oracle (for this lab we will user the password oracle)

39 – Create the directories in which the Oracle software will be installed.

mkdir -p /u01/app/oracle/product/12.1.0/db_1
chown -R oracle:oinstall /u01
chmod -R 775 /u01

40 – Login as the oracle user and add the following lines at the end of the “.bash_profile” file.

# Oracle Settings
TMP=/tmp; export TMP
TMPDIR=$TMP; export TMPDIR

ORACLE_HOSTNAME=oracle12c; export ORACLE_HOSTNAME
ORACLE_UNQNAME=DB12G; export ORACLE_UNQNAME
ORACLE_BASE=/u01/app/oracle; export ORACLE_BASE
ORACLE_HOME=$ORACLE_BASE/product/12.1.0/db_1; export ORACLE_HOME
ORACLE_SID=DB12G; export ORACLE_SID

PATH=/usr/sbin:$PATH; export PATH
PATH=$ORACLE_HOME/bin:$PATH; export PATH

LD_LIBRARY_PATH=$ORACLE_HOME/lib:/lib:/usr/lib; export LD_LIBRARY_PATH
CLASSPATH=$ORACLE_HOME/jlib:$ORACLE_HOME/rdbms/jlib; export CLASSPATH

41 – On the unziped folder created, go to database/rpm and install the cvuqdisk-1.0.9-1.rpm package.

$ rpm –Uvh cvuqdisk-1.0.9-1.rpm

42 – run xhost +

$ xhost +

access control disabled, clients can connect from any host

43 – Stop the Virtual Machine and Remove the OS disk from the [Storage] section and unselect the CD/DVD-ROM from the Boot Order and Start the VM.

SnapCrab_NoName_2013-6-28_13-24-51_No-00

Next, on part 2 of this lab, we will install the Oracle Database software and take a look in the Oracle Enterprise Manager Database Express.

 

Regards,

 

Francisco Munoz Alvarez

//



Tags:  , , , , ,

Del.icio.us
Facebook
TweetThis
Digg
StumbleUpon

Copyright © OracleNZ by Francisco Munoz Alvarez [Playing with VirtualBox, Oracle 12c (12.1.0.1) and OEL 6.4 – Part 1], All Right Reserved. 2016.
Categories: DBA Blogs

Oracle 12c New Features - Convert a database to be Pluggable

ContractOracle - Mon, 2013-07-01 22:08
In a previous blog post I demonstrated creating a Pluggable Database (PDB) from the PDB$SEED database  which is created at the same time as the Container Database (CDB).  That process was quick and easy, but is only useful for creating new empty databases.  

If we want to migrate existing databases which contain data (e.g upgraded from 11g) to the CDB/PDB multitenant architecture we need to convert them to be PDBs so we can plug them in.

For this example I created a stand-alone database called NONCDB.

[oracle@rac1 T12]$ export ORACLE_SID=NONCDB
[oracle@rac1 T12]$ sqlplus / as sysdba

SQL*Plus: Release 12.1.0.1.0 Production on Tue Jul 2 10:31:54 2013

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


Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics, Real Application Testing
and Unified Auditing options

We can see in v$database that it is not a CDB or PDB.

SQL> select CDB from v$database;

CDB
---
NO

To convert it to be a PDB we first need to get the database in a consistent state and run DBMS_PDB.DESCRIBE to create an XML file to describe the database.


SQL> shutdown immediate;

Database closed.

Database dismounted.
ORACLE instance shut down.
SQL> startup mount;
ORACLE instance started.

Total System Global Area 1235959808 bytes
Fixed Size                  2287816 bytes
Variable Size             452986680 bytes
Database Buffers          771751936 bytes
Redo Buffers                8933376 bytes
Database mounted.
SQL> alter database open read only;

Database altered.

SQL> BEGIN
 DBMS_PDB.DESCRIBE(
  pdb_descr_file => '/u01/app/oracle/oradata/NONCDB/noncdb.xml');
 END;

PL/SQL procedure successfully completed.

SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> exit

Now we can plug NONCDB into a existing CDB database T12.

[oracle@rac1 T12]$ export ORACLE_SID=T12
[oracle@rac1 T12]$ sqlplus / as sysdba

SQL*Plus: Release 12.1.0.1.0 Production on Tue Jul 2 10:42:01 2013

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


Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics, Real Application Testing
and Unified Auditing options

SQL> select CDB from v$database;

CDB
---
YES

I am plugging the database in to a CDB on the same server as the original database so I will create the PDB with NOCOPY TEMPFILE REUSE.  If you are changing directory structures then you would need to use FILE_NAME_CONVERT.

SQL> CREATE PLUGGABLE DATABASE NONCDB USING '/u01/app/oracle/oradata/NONCDB/noncdb.xml' NOCOPY tempfile reuse;

Pluggable database created.

Now we need to update the data dictionary in the new PDB by running noncdb_to_pdb.sql

SQL> alter session set container=NONCDB;

Session altered.

SQL> @$ORACLE_HOME/rdbms/admin/noncdb_to_pdb.sql

This script has a lot of output which I will not show but unfortunately it ended with an error :-

SQL> -- get rid of idl_ub1$ rows for MDL java objects
SQL> delete from sys.idl_ub1$ where obj# in (select obj# from sys.obj$ where bitand(flags, 65536)=65536 and type# in (28,29,30,56));
^Cdelete from sys.idl_ub1$ where obj# in (select obj# from sys.obj$ where bitand(flags, 65536)=65536 and type# in (28,29,30,56))
                *
ERROR at line 1:
ORA-01013: user requested cancel of current operation

The new PDB was left in READ WRITE state after the script exited and seems usable, but due to the error I can't be sure everything completed OK so it would be worth checking with Oracle Support.


SQL> select name, open_mode from v$pdbs;



NAME                           OPEN_MODE
------------------------------ ----------
PDB$SEED                       READ ONLY
NONCDB                         READ WRITE

SQL> alter session set container=NONCDB;

Session altered.

SQL> create user test identified by test;

User created.

We can see that by converting the existing database to a PDB it only kept the SYSTEM, SYSAUX, USERS tablespaces, and has dropped  the UNDO datafiles along with the original REDO logs and control files.

SQL> select file_name from dba_data_files;

FILE_NAME
-----------------------------------------------------------------
/u01/app/oracle/oradata/NONCDB/system01.dbf
/u01/app/oracle/oradata/NONCDB/sysaux01.dbf
/u01/app/oracle/oradata/NONCDB/users01.dbf


[oracle@rac1 NONCDB]$ pwd
/u01/app/oracle/oradata/NONCDB
[oracle@rac1 NONCDB]$ ls -lrt
total 1712016
-rw-r----- 1 oracle oinstall  52429312 Jul  2 10:30 redo03.log
-rw-r----- 1 oracle oinstall  52429312 Jul  2 10:30 redo02.log
-rw-r----- 1 oracle oinstall  52429312 Jul  2 10:36 redo01.log
-rw-r----- 1 oracle oinstall  57679872 Jul  2 10:37 undotbs01.dbf
-rw-r--r-- 1 oracle oinstall      3986 Jul  2 10:38 noncdb.xml
-rw-r----- 1 oracle oinstall  10043392 Jul  2 10:39 control02.ctl
-rw-r----- 1 oracle oinstall  10043392 Jul  2 10:39 control01.ctl
-rw-r----- 1 oracle oinstall  62922752 Jul  2 10:45 temp01.dbf
-rw-r----- 1 oracle oinstall   5251072 Jul  2 11:03 users01.dbf
-rw-r----- 1 oracle oinstall 807411712 Jul  2 11:03 system01.dbf
-rw-r----- 1 oracle oinstall 702554112 Jul  2 11:03 sysaux01.dbf
Categories: DBA Blogs

Oracle 12c New Features - Query CDB_PDB_HISTORY for Pluggable Database History

ContractOracle - Mon, 2013-07-01 03:45
If you are interested in checking the history of PDBs, then view CDB_PDB_HISTORY is a good place to start.

SQL> SELECT DB_NAME, CON_ID, PDB_NAME, OPERATION, OP_TIMESTAMP, CLONED_FROM_PDB_NAME
FROM CDB_PDB_HISTORY WHERE CON_ID > 2 ORDER BY 5;  

DB_NAME    CON_ID PDB_NAME        OPERATION        OP_TIMESTA CLONED_FROM_PDB
---------- ------ --------------- ---------------- ---------- ---------------
SEEDDATA        5 PDB$SEED        UNPLUG           24-MAY-13
SEEDDATA        3 PDB$SEED        UNPLUG           24-MAY-13
SEEDDATA        4 PDB$SEED        UNPLUG           24-MAY-13
T12             5 PDB$SEED        PLUG             01-JUL-13  PDB$SEED
T12             3 PDB$SEED        PLUG             01-JUL-13  PDB$SEED
T12             4 PDB$SEED        PLUG             01-JUL-13  PDB$SEED
T12             5 PDB1            CREATE           01-JUL-13  PDB$SEED
T12             3 PDB1            CREATE           01-JUL-13  PDB$SEED
T12             4 PDB1            CREATE           01-JUL-13  PDB$SEED
T12             5 PDB1            UNPLUG           01-JUL-13
T12             3 PDB1            UNPLUG           01-JUL-13
T12             4 PDB1            UNPLUG           01-JUL-13
T12             5 PDB1            PLUG             01-JUL-13  PDB1
T12             3 PDB1            PLUG             01-JUL-13  PDB1
T12             4 PDB1            PLUG             01-JUL-13  PDB1
T12             5 PDB1            UNPLUG           01-JUL-13
T12             3 PDB1            UNPLUG           01-JUL-13
T12             4 PDB1            UNPLUG           01-JUL-13
T12             5 PDB1            PLUG             01-JUL-13  PDB1
T12             3 PDB1            PLUG             01-JUL-13  PDB1
T12             4 PDB1            PLUG             01-JUL-13  PDB1
T12             5 PDB1            UNPLUG           01-JUL-13
T12             3 PDB1            UNPLUG           01-JUL-13
T12             4 PDB1            UNPLUG           01-JUL-13
T12             4 PDB2            PLUG             01-JUL-13  PDB1
T12             5 PDB1            PLUG             01-JUL-13  PDB1
T12             3 PDB1            PLUG             01-JUL-13  PDB1
T12             5 PDB3            CLONE            01-JUL-13  PDB1

Categories: DBA Blogs

Oracle 12c New Features - TEMP_UNDO_ENABLED

ContractOracle - Mon, 2013-07-01 01:12
Oracle 12c introduces new parameter TEMP_UNDO_ENABLED which can be set at database and session level.  If this parameter is enabled, then undo for temporary objects (e.g global temporary tables) is written to the TEMP tablespace, compared to the default of writing to the UNDO tablespace.  This can help improve performance and reduce UNDO and REDO.

SQL> connect test/test@pdb1
Connected.

SQL> CREATE GLOBAL TEMPORARY TABLE my_temp_table (id integer) ON COMMIT PRESERVE ROWS;

Table created.

SQL> show parameter temp_undo_enabled

NAME                                 TYPE        VALUE
------------------------------------ ----------- ----------------
temp_undo_enabled                    boolean     FALSE

Now run an insert to the global temporary table with the parameter set to FALSE. 

SQL> set autotrace on statistics
SQL> insert into my_temp_table values (1);

1 row created.


Statistics
----------------------------------------------------------
          1  recursive calls
          8  db block gets
          1  consistent gets
          0  physical reads
        312  redo size
        853  bytes sent via SQL*Net to client
        837  bytes received via SQL*Net from client
          3  SQL*Net roundtrips to/from client
          1  sorts (memory)
          0  sorts (disk)
          1  rows processed

According to Autotrace statistics this generates redo of 312.

SQL> connect test/test@pdb1
Connected.
SQL> alter session set temp_undo_enabled=true;

Session altered.

Now run the insert again with the parameter set to TRUE.  

SQL> set autotrace on statistics
SQL> insert into my_temp_table values (1);

1 row created.


Statistics
----------------------------------------------------------
          3  recursive calls
         13  db block gets
          1  consistent gets
          0  physical reads
        280  redo size
        850  bytes sent via SQL*Net to client
        837  bytes received via SQL*Net from client
          3  SQL*Net roundtrips to/from client
          1  sorts (memory)
          0  sorts (disk)
          1  rows processed

According to Autotrace statistics this generates redo of 280 (compared to 312).  Reduced UNDO and REDO from temporary transactions can help the performance of the database and reduce disk space for UNDO tablespaces, archivelogs, and backups. The Oracle documentation says "If database applications make use of temporary objects (using global temporary tables or temporary table transformations), it is advisable to set this parameter's value to true."  

Statistics on TEMP UNDO are available via V$TEMPUNDOSTAT

SQL> desc V$TEMPUNDOSTAT
 Name                                      Null?    Type
 ----------------------------------------- -------- -------------
 BEGIN_TIME                                         DATE
 END_TIME                                           DATE
 UNDOTSN                                            NUMBER
 TXNCOUNT                                           NUMBER
 MAXCONCURRENCY                                     NUMBER
 MAXQUERYLEN                                        NUMBER
 MAXQUERYID                                         VARCHAR2(13)
 UNDOBLKCNT                                         NUMBER
 EXTCNT                                             NUMBER
 USCOUNT                                            NUMBER
 SSOLDERRCNT                                        NUMBER
 NOSPACEERRCNT                                      NUMBER
 CON_ID                                             NUMBER

More details here :- http://docs.oracle.com/cd/E16655_01/server.121/e17615/refrn10326.htm#REFRN10326



Categories: DBA Blogs

parameter ENABLE_DDL_LOGGING

ContractOracle - Sun, 2013-06-30 23:43
If Oracle parameter ENABLE_DDL_LOGGING is enabled DDL records are written to the ADR.

SQL> show parameter enable_ddl_logging

NAME                                 TYPE        VALUE
------------------------------------ ----------- ----------------
enable_ddl_logging                   boolean     FALSE

SQL> alter system set enable_ddl_logging=true;

System altered.

SQL> connect c##test/test@pdb1
Connected.
SQL> create view x as select * from user_views;

View created.

SQL> drop view x;

View dropped.

SQL> exit
Disconnected from Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics, Real Application Testing
and Unified Auditing options

[oracle@rac1 log]$ pwd
/u01/app/oracle/diag/rdbms/t12/T12/log
[oracle@rac1 log]$ ls
ddl  ddl_T12.log  debug  test
[oracle@rac1 log]$ more *.log
Mon Jul 01 12:35:54 2013
diag_adl:create view x as select * from user_views
diag_adl:drop view x

[oracle@rac1 log]$ cd ddl
[oracle@rac1 ddl]$ more *.xml
 msg_id='kpdbLogDDL:15115:2946163730' type='UNKNOWN' group='diag_adl'
 level='16' host_id='rac1.test.com' host_addr='192.168.1.205'
 version='1'>
 create view x as select * from user_views
 msg_id='kpdbLogDDL:15115:2946163730' type='UNKNOWN' group='diag_adl'
 level='16' host_id='rac1.test.com' host_addr='192.168.1.205'>
 drop view x

Categories: DBA Blogs

Oracle 12c New Features - Clone a Plugged In Database

ContractOracle - Sun, 2013-06-30 23:21
One of the benefits of the CDB/PDB model in Oracle 12c is that it allows rapid cloning of Pluggable Databases (PDB).  To create a clone database in previous versions of Oracle the DBA would have needed to create a new database instance with a new set of parameters and then clone the source database files using rman.  

In the following example I will clone database PDB1 to a new database PDB3 using only the "create pluggable database" command.  First the source database needs to be open read-only.

SQL> alter pluggable database pdb1 close immediate;

Pluggable database altered.

SQL> alter pluggable database pdb1 open read only;

Pluggable database altered.

Then clone the PDB1 to PDB3.

SQL> create pluggable database PDB3 from PDB1
FILE_NAME_CONVERT=('/u01/app/oracle/oradata/T12/pdb1','/u01/app/oracle/oradata/T12/pdb3');  

Pluggable database created.

And open both databases read-write.

SQL> alter pluggable database PDB3 open;

Pluggable database altered.

SQL> alter pluggable database PDB1 close immediate;

Pluggable database altered.

SQL> alter pluggable database PDB1 open;

Pluggable database altered.

SQL> select name, open_mode from v$pdbs;

NAME                           OPEN_MODE
------------------------------ ----------
PDB$SEED                       READ ONLY
PDB1                           READ WRITE
PDB2                           READ WRITE
PDB3                           READ WRITE


Categories: DBA Blogs

Oracle 12c New Features - Plugging and Unplugging Databases

ContractOracle - Sun, 2013-06-30 22:35
In a previous blog post I demonstrated creating Pluggable Databases (PDB) in an Oracle 12c Container Database (CDB).  In this test I will demonstrate how easy it is to unplug a PDB from a CDB, and then plugin again.

We currently have one PDB with name PDB1.  We will shutdown, unplug it, and drop it.

SQL> select name from v$pdbs;

NAME
------------------------------
PDB$SEED
PDB1

SQL> alter pluggable database pdb1 close immediate;

Pluggable database altered.

SQL> alter pluggable database pdb1 unplug into '/u01/app/oracle/oradata/T12/pdb1/pdb1.xml';

Pluggable database altered.

SQL> drop pluggable database pdb1 keep datafiles;

Pluggable database dropped.

SQL> select name from v$pdbs;

NAME
------------------------------
PDB$SEED

We can now backup the database to tape for later restore, or copy the datafiles and xml file for the pluggable database to another CDB on another server and plugin.  In this example I will just plug the database back into the original CDB.  

Before we plugin we first need to run DBMS_PDB.CHECK_PLUG_COMPATIBILITY to check that the PDB is compatible with the new CDB.

SQL> set serveroutput on
SQL> DECLARE
   compatible BOOLEAN := FALSE;
  2    3  BEGIN
  4     compatible := DBMS_PDB.CHECK_PLUG_COMPATIBILITY(
  5          pdb_descr_file => '/u01/app/oracle/oradata/T12/pdb1/pdb1.xml');
  6     if compatible then
  7        DBMS_OUTPUT.PUT_LINE('Is pluggable PDB2 compatible? YES');
  8     else DBMS_OUTPUT.PUT_LINE('Is pluggable PDB2 compatible? NO');
  9     end if;
 10  END;
 11  /
Is pluggable PDB2 compatible? YES

PL/SQL procedure successfully completed.

As the PDB is compatible with the CDB we can proceed to plug it in. 

SQL> create pluggable database PDB1 using '/u01/app/oracle/oradata/T12/pdb1/pdb1.xml' NOCOPY TEMPFILE REUSE;

Pluggable database created.

SQL> select name, open_mode from v$pdbs;

NAME                           OPEN_MODE
------------------------------ ----------
PDB$SEED                       READ ONLY
PDB1                           MOUNTED

SQL> alter session set container=PDB1;

Session altered.

SQL> alter database open;

Database altered.

SQL> connect test/test@pdb1;
Connected.


SQL> show con_name



CON_NAME

------------------------------
PDB1

We are now able to login to the plugged in database.

The Alert log entries for these operations are as follows :-

Mon Jul 01 11:14:31 2013
alter pluggable database pdb1 close immediate
Mon Jul 01 11:14:31 2013
ALTER SYSTEM: Flushing buffer cache inst=0 container=3 local
Pluggable database PDB1 closed
Completed: alter pluggable database pdb1 close immediate
alter pluggable database pdb1 unplug into '/u01/app/oracle/oradata/T12/pdb1/pdb1.xml'
ALTER SYSTEM: Flushing buffer cache inst=0 container=3 local
ALTER SYSTEM: Flushing buffer cache inst=0 container=3 local
Completed: alter pluggable database pdb1 unplug into '/u01/app/oracle/oradata/T12/pdb1/pdb1.xml'
drop pluggable database pdb1 keep datafiles
Mon Jul 01 11:15:02 2013
Deleted file /u01/app/oracle/oradata/T12/pdb1/pdbseed_temp01.dbf
Completed: drop pluggable database pdb1 keep datafiles
create pluggable database PDB1 using '/u01/app/oracle/oradata/T12/pdb1/pdb1.xml' NOCOPY TEMPFILE REUSE
Mon Jul 01 11:20:45 2013
****************************************************************
Pluggable Database PDB1 with pdb id - 3 is created as UNUSABLE.
If any errors are encountered before the pdb is marked as NEW,
then the pdb must be dropped
****************************************************************
Deleting old file#10 from file$
Deleting old file#11 from file$
Adding new file#12 to file$(old file#10)
Adding new file#13 to file$(old file#11)
Successfully created internal service pdb1 at open
ALTER SYSTEM: Flushing buffer cache inst=0 container=3 local
****************************************************************

Post plug operations are now complete.
Pluggable database PDB1 with pdb id - 3 is now marked as NEW.
****************************************************************
Completed: create pluggable database PDB1 using '/u01/app/oracle/oradata/T12/pdb1/pdb1.xml' NOCOPY TEMPFILE REUSE
Mon Jul 01 11:29:00 2013
alter database open
Mon Jul 01 11:29:00 2013
Pluggable database PDB1 dictionary check beginning
Pluggable Database PDB1 Dictionary check complete
Opening pdb PDB1 (3) with no Resource Manager plan active

XDB installed.

XDB initialized.
Pluggable database PDB1 opened read write
Completed: alter database open






Categories: DBA Blogs

Oracle 12c New Features - Container and Pluggable Databases

ContractOracle - Sun, 2013-06-30 20:30
Oracle 12c introduces "Multitenant Architecture" which allows consolidation of databases via Container Databases (CDB) and Pluggable Databases (PDB)

The CDB database owns the SGA and running processes, and the PDB databases are serviced by those resources.  This new architecture will be a big change for DBAs experienced in managing earlier versions of the Oracle database, so it is worth taking the time to read the documentation and testing extensively before using these new features.  The theory is that many databases sharing one SGA and set of processes should be more efficient that multiple individually managed memory segments, so this feature is specifically aimed at clouds and large companies.


I used the DBCA utility to create a CDB called T12, which also created a small PDB$SEED database.  DBCA is easy to run, and similar to previous versions, so I won't show screen shots here.  It is also possible to create a CDB database using the CREATE DATABASE statement along with the new ENABLE PLUGGABLE DATABASE clause.  


When managing CDBs and PDBs it is important to ensure you know what container you are currently working on.  By default when you login you will end up in CDB$ROOT.  


[oracle@rac1 admin]$ ps -ef | grep pmon
oracle    7830     1  0 09:08 ?        00:00:00 ora_pmon_T12

[oracle@rac1 admin]$ echo $ORACLE_SID

T12

[oracle@rac1 admin]$ sqlplus / as sysdba

SQL*Plus: Release 12.1.0.1.0 Production on Mon Jul 1 09:11:11 2013
Copyright (c) 1982, 2013, Oracle.  All rights reserved.

Connected to:

Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics, Real Application Testing
and Unified Auditing options

SQL> show con_name

CON_NAME
------------------------------
CDB$ROOT

SQL> show con_id

CON_ID
------------------------------
1

By selecting from v$database we can see that we are currently in a CDB.

SQL> select DBID, name, CDB, CON_ID, CON_DBID from v$database;

      DBID NAME      CDB     CON_ID   CON_DBID
---------- --------- --- ---------- ----------
1216820329 T12       YES          0 1216820329

We can select PDB from dba_services to check what PDBs exist.  In this case we have not created any PDBs, so only the CDB T12 is listed against CDB$ROOT.

SQL> select name, pdb from dba_services;

NAME                PDB
----------------------------------------------------------------
SYS$BACKGROUND      CDB$ROOT
SYS$USERS           CDB$ROOT
T12XDB              CDB$ROOT
T12                 CDB$ROOT

SQL> select name, con_id from v$active_services;

NAME                                                  CON_ID
---------------------------------------------------------------- 
T12XDB                                                1
T12                                                   1
SYS$BACKGROUND                                        1
SYS$USERS                                             1

Create a tnsnames.ora entry for connecting to CDB service T12.

T12 =
 (DESCRIPTION =
 (ADDRESS_LIST =
 (ADDRESS = (PROTOCOL = TCP)(HOST = rac1.test.com)(PORT = 1521)))
 (CONNECT_DATA =
 (SERVICE_NAME = T12)
 )
  )

When we try to create a user we find that in a CDB we are are not allowed to create "local" users, but can only create "common" users with usernames starting with "C##"

SQL> create user test identified by test;
create user test identified by test
            *
ERROR at line 1:
ORA-65096: invalid common user or role name

SQL> !oerr ora 65096
65096, 00000, "invalid common user or role name"
// *Cause:  An attempt was made to create a common user or role with a name
//          that wass not valid for common users or roles.  In addition to
//          the usual rules for user and role names, common user and role
//          names must start with C## or c## and consist only of ASCII
//          characters.
// *Action: Specify a valid common user or role name.
//

SQL> create user test identified by test container=current;
create user test identified by test container=current
                               *
ERROR at line 1:
ORA-65049: creation of local user or role is not allowed in CDB$ROOT

SQL> create user c##test identified by test;

User created.

There are now additional data dictionary views to help manage the PDB and CDB databases (names include PDB, CDB).  You will also notice that many data dictionary views now contain a column CON_ID which allows DBAs to check details for a specific CDB or PDB.  

If we select from v$datafile in the new container database we can see that in addition to the datafiles for the CDB T12, there are datafiles listed for database "pdbseed".  The PDB$SEED database is created at the same time as the CDB and can be used as a source to create PDB databases.


SQL> select name, con_id, plugged_in from v$datafile order by 2;

NAME                                           CON_ID PLUGGED_IN
-------------------------------------------------- ---------- ---
/u01/app/oracle/oradata/T12/system01.dbf            1          0
/u01/app/oracle/oradata/T12/sysaux01.dbf            1          0
/u01/app/oracle/oradata/T12/undotbs01.dbf           1          0
/u01/app/oracle/oradata/T12/users01.dbf             1          0
/u01/app/oracle/oradata/T12/pdbseed/system01.dbf    2          0
/u01/app/oracle/oradata/T12/pdbseed/sysaux01.dbf    2          0

6 rows selected.

The PDB$SEED database is mounted read only, so it is possible to explore it, but there are limits to what you can do with this DB.

SQL> select con_id, name, open_mode from v$pdbs;

    CON_ID NAME                                        OPEN_MODE

---------- ------------------------------------------- ---------
         2 PDB$SEED                                    READ ONLY

SQL> alter session set container=PDB$SEED;

Session altered.

SQL> select name from v$database;

NAME
---------
T12

SQL> show con_name

CON_NAME
------------------------------
PDB$SEED

SQL> show con_id

CON_ID
------------------------------
2

SQL> select open_mode from v$database;

OPEN_MODE
--------------------
READ ONLY

SQL> shutdown;
ORA-65017: seed pluggable database may not be dropped or altered

To create our own read-write PDB as a copy of the PDB$SEED database we just need to execute the "create pluggable database" command.

SQL> create pluggable database PDB1 admin user pdb1_admin identified by password roles = (DBA) FILE_NAME_CONVERT=('/u01/app/oracle/oradata/T12/pdbseed','/u01/app/oracle/oradata/T12/pdb1');

Pluggable database created.


SQL> select pdb_name, status from cdb_pdbs;

PDB_NAME      STATUS
----------------------------------------------------------------
PDB$SEED      NORMAL
PDB1          NEW

SQL> select name, con_id from v$active_services order by 1;

NAME                                           CON_ID
---------------------------------------------------------------- 
SYS$BACKGROUND                                 1
SYS$USERS                                      1
T12                                            1
T12XDB                                         1
pdb1                                           3

SQL> select name from v$datafile where con_id=3;

NAME
-----------------------------------------------------------------
/u01/app/oracle/oradata/T12/pdb1/system01.dbf
/u01/app/oracle/oradata/T12/pdb1/sysaux01.dbf

SQL> select name, open_mode from v$pdbs;

NAME                           OPEN_MODE
------------------------------ ----------
PDB$SEED                       READ ONLY
PDB1                           MOUNTED

We can see from the above that the new PDB was created in MOUNTED state.  We will need to open it if we want to use it.  When we look in the CDB alert log we can see the following :-

Mon Jul 01 10:02:30 2013
create pluggable database PDB1 admin user pdb1_admin identified by * roles = (DBA) FILE_NAME_CONVERT=('/u01/app/oracle/oradata/T12/pdbseed','/u01/app/oracle/oradata/T12/pdb1')
Mon Jul 01 10:02:53 2013
****************************************************************
Pluggable Database PDB1 with pdb id - 3 is created as UNUSABLE.
If any errors are encountered before the pdb is marked as NEW,
then the pdb must be dropped
****************************************************************
Deleting old file#5 from file$
Deleting old file#7 from file$
Adding new file#10 to file$(old file#5)
Adding new file#11 to file$(old file#7)
Successfully created internal service pdb1 at open
ALTER SYSTEM: Flushing buffer cache inst=0 container=3 local
****************************************************************
Post plug operations are now complete.
Pluggable database PDB1 with pdb id - 3 is now marked as NEW.
****************************************************************

Completed: create pluggable database PDB1 admin user pdb1_admin identified by * roles = (DBA) FILE_NAME_CONVERT=('/u01/app/oracle/oradata/T12/pdbseed','/u01/app/oracle/oradata/T12/pdb1')

We can open databases individually as follows, or open all PDBs using "alter pluggable database all open;"

SQL> alter session set container=PDB1;

Session altered.

SQL> show con_name

CON_NAME
------------------------------
PDB1

SQL> alter database open;

Database altered.

SQL> select name, open_mode from v$pdbs;

NAME                           OPEN_MODE
------------------------------ ----------
PDB1                           READ WRITE

We can see the following in the CDB alert log.

alter database open
Mon Jul 01 10:12:50 2013
Pluggable database PDB1 dictionary check beginning
Pluggable Database PDB1 Dictionary check complete
Opening pdb PDB1 (3) with no Resource Manager plan active

XDB installed.


XDB initialized.

Pluggable database PDB1 opened read write

Completed: alter database open

Create a tnsnames.ora entry for the new PDB using the default service PDB1.

PDB1 =
 (DESCRIPTION =
 (ADDRESS_LIST =
 (ADDRESS = (PROTOCOL = TCP)(HOST = rac1.test.com)(PORT = 1521)))
 (CONNECT_DATA =
 (SERVICE_NAME = PDB1)
 )
  )

Now that we have created a PDB we can create "local" users.

SQL> show con_name

CON_NAME
------------------------------
PDB1

SQL> create user test identified by test;

User created.

SQL> grant create session to test;

Grant succeeded.

It is also possible to grant privileges in the PDB for the "common" users that exist in the CDB.

SQL> grant create session to C##TEST container=ALL;


Grant succeeded.

We can now connect directly to the PDB1 pluggable database using both the "local" and "common" users.

SQL> connect test/test@PDB1
Connected.
SQL> show con_name

CON_NAME

------------------------------
PDB1

SQL> connect C##TEST/test@PDB1
Connected.

SQL> show con_name

CON_NAME
------------------------------
PDB1

When logged into PDBs many data dictionary views will restrict our view of the world via CON_ID so that we can't see records relating to other PDBs.  We can see from the following selects that PDB1 uses the UNDO and REDO files from the container database, but has its own SYSTEM, SYSAUX, TEMP files.

SQL> select name from v$datafile;

NAME
-----------------------------------------------------------------
/u01/app/oracle/oradata/T12/undotbs01.dbf
/u01/app/oracle/oradata/T12/pdb1/system01.dbf
/u01/app/oracle/oradata/T12/pdb1/sysaux01.dbf

SQL> select name from v$tempfile;

NAME
-----------------------------------------------------------------
/u01/app/oracle/oradata/T12/pdb1/pdbseed_temp01.dbf

SQL> select member from v$logfile;

MEMBER
-----------------------------------------------------------------
/u01/app/oracle/oradata/T12/redo03.log
/u01/app/oracle/oradata/T12/redo02.log
/u01/app/oracle/oradata/T12/redo01.log

PDB databases can be created from a seed database as demonstrated above, cloned from other PDB databases, or plugged in from previously unplugged PDBs or converted non-CDB databases.  In addition to creating new PDB databases we can also drop, rename, clone, unplug, plug backup, restore, and duplicate.  Check the Oracle documentation for details.

More details here :- Introduction to the Multitenant Architecture
Categories: DBA Blogs

Playing with VirtualBox, Oracle 12c (12.1.0.1) and OEL 6.4 – Part 2

Oracle NZ - Sun, 2013-06-30 19:24

We will complete our lab by installing the Oracle Database software, create a Pluggable database and take a look in the Oracle Enterprise Manager Database Express.

The steps to install and create an Oracle Database 12c are:

1 – Go to the database directory created when unzipped the 2 Oracle Database Files and run /runInstaller .

SnapCrab_NoName_2013-7-1_20-33-26_No-00

2 – Unselect the option [I wish to receive security updates via My Oracle Support] and click [Next]. Of course, if you want to receive updates all you need to do is to leave this option marked and enter your My Oracle Support email and password.

.SnapCrab_NoName_2013-7-1_20-34-0_No-00

3 – You will receive a warning message that you have not provided an email address. Just click on [Yes].

SnapCrab_NoName_2013-7-1_20-34-27_No-00

4 – Select [Skip software updates] and click [Next].

SnapCrab_NoName_2013-7-1_20-34-46_No-00

5 – Select [Create and Configure a Database] and click [Next].

SnapCrab_NoName_2013-7-1_20-35-8_No-00

6 – Select [Server Class] and click [Next]. *** If you do not have enough resources on your laptop, please choose Desktop Class instead of Server Class.

 SnapCrab_NoName_2013-7-1_20-35-30_No-00

7 – Select the type of Database to be installed, choose [Single instance database installation] and click [Next].

 SnapCrab_NoName_2013-7-1_20-35-49_No-00

8 – Select the Install type. For this lab I will choose [Advanced Install] and click [Next].

 SnapCrab_NoName_2013-7-1_20-36-7_No-00

9 – Select the product Language, here all you need to to is leave English as the default and click [Next].

 SnapCrab_NoName_2013-7-1_20-36-24_No-00

10 – Select the Database Edition you want to install. Select [Enterprise Edition] and click [Next[.

 SnapCrab_NoName_2013-7-1_20-36-38_No-00

11 – Specify the Installation location and click [Next].

 SnapCrab_NoName_2013-7-1_20-36-53_No-00

12 – Create the Inventory and click [Next].

 SnapCrab_NoName_2013-7-1_20-37-15_No-00

13 – Select the type of database you want to create. Select [General Purpose] and click [Next].

 SnapCrab_NoName_2013-7-1_20-37-28_No-00

14 – Specify the database identifiers, enter “orcl” as the SID and here you can decide if you want to create a Container Database or as early releases a non-Container Database (by not selecting [Create as Container database]. For this example we will select Container database and create a Pluggable Database called “pdborcl” and click [Next].

 SnapCrab_NoName_2013-7-1_20-37-48_No-00

15 – Specify the configurations options, by default the Automatic Memory Management is enabled, click [Next].

 SnapCrab_NoName_2013-7-1_20-38-3_No-00

16 – Specify the storage to be used. Select [File System] and click [Next].

 SnapCrab_NoName_2013-7-1_20-38-26_No-00

17 – Management options. If you have an EM Cloud Control running on your environment, here is where you specify the EM details to manage this database. Leave as the default and click [Next].

 SnapCrab_NoName_2013-7-1_20-38-40_No-00

18 – Enable Recovery and click [Next].

 SnapCrab_NoName_2013-7-1_20-39-14_No-00

19 – Specify Passwords. For this scenario we use the password “oracle” to all accounts. Click [Next].

 SnapCrab_NoName_2013-7-1_20-39-38_No-00

20 – A warning will appear due that we are using a easy password. Click [Yes].

 SnapCrab_NoName_2013-7-1_20-39-53_No-00

21 – Click [Next] on Privileged Operating Systems groups.

 SnapCrab_NoName_2013-7-1_20-40-7_No-00

22 – Review the Summary page and click [install].

 SnapCrab_NoName_2013-7-1_20-40-35_No-00

 SnapCrab_NoName_2013-7-1_20-41-28_No-00

23 – Execute the configuration scripts as root and click [Ok] to continue the installation.

 SnapCrab_NoName_2013-7-1_20-43-51_No-00

 SnapCrab_NoName_2013-7-1_20-44-33_No-00

 SnapCrab_NoName_2013-7-1_20-45-15_No-00

 SnapCrab_NoName_2013-7-1_20-45-52_No-00

 SnapCrab_NoName_2013-7-1_20-51-35_No-00

24 – In the Finish page click [Close].

 SnapCrab_NoName_2013-7-1_20-52-3_No-00

25 – Edit /etc/oratab as follows.

 SnapCrab_NoName_2013-7-1_20-52-59_No-00

26 – Check the listener status.

 SnapCrab_NoName_2013-7-1_20-53-29_No-00

27 – Check if the Container Database is running.

 SnapCrab_NoName_2013-7-1_20-53-46_No-00

28 – Connect to the Enterprise Manager Database Express. It will first ask you to add a Security Exception in Firefox.

SnapCrab_NoName_2013-7-1_20-57-17_No-00

 SnapCrab_NoName_2013-7-1_20-57-30_No-00

29 – The Next step will be to download the Adobe Flash Player rpm and install it.

SnapCrab_NoName_2013-7-2_12-58-39_No-00

30 – Enter your database username and password. SYS and oracle and click [Login]

 SnapCrab_NoName_2013-7-2_13-1-6_No-00

31 – This is the First Screen of the new Enterprise Manager Database Express 12c. Here you are not able to use monitoring, set alertings or even execute backup and recovery operations. But you can do a lot of other stuff such  such as per example: Manage Storage such as: Undo, Redo Log Files, and Control Files, Configure Initialization Parameters, Memory  and Database Features and finally Manage  Performance, SQL Tuning and Users. and Roles (Security).

 SnapCrab_NoName_2013-7-2_13-47-36_No-00

SnapCrab_NoName_2013-7-2_13-44-55_No-00

SnapCrab_NoName_2013-7-2_13-45-27_No-00

SnapCrab_NoName_2013-7-2_13-45-56_No-00

SnapCrab_NoName_2013-7-2_13-44-21_No-00

 SnapCrab_NoName_2013-7-2_13-46-24_No-00

 SnapCrab_NoName_2013-7-2_13-47-3_No-00

 

Hope you enjoyed this tutorial and soon many more will come.

 

Regards,

 

Francisco Munoz Alvarez

//



Tags:  , , , , , ,

Del.icio.us
Facebook
TweetThis
Digg
StumbleUpon

Copyright © OracleNZ by Francisco Munoz Alvarez [Playing with VirtualBox, Oracle 12c (12.1.0.1) and OEL 6.4 – Part 2], All Right Reserved. 2016.
Categories: DBA Blogs

DataDirect Access your favorite SaaS app with SQL

Kubilay Çilkara - Fri, 2013-06-28 08:24
Database driver company +Progress DataDirect  are writing the next generation of data access. They are working on a platform called Datadirect which will enable you to access loads of data sources in the cloud, from the cloud, and some of them with SQL

Yes you have read correct, SQL!

All you have to do is visit their website and sign up for a trial account and access your favorite SaaS app - i.e Salesforce  or any other provided Cloud app with SQL.

Here is how their availability of connectors looks like






 










To sign up just follow this link.

I very much like this noble idea. SQL is the universal language for data manipulation and access. And things like Big Data, Hadoop in the horizon they are pro-active and seems like they are already working on it.

See some list of data sources they can/will SQL:

  • Twitter
  • Facebook
  • Hive
  • Eloqua
  • force.com
  • Salesforce
  • SQLAzure
  • Microsoft Dynamics CRM
  • and many more...

This is on my hot list of technology to watch. Well done DataDirect. 
Categories: DBA Blogs

Oracle 12c New Features - multiple indexes on the same set of columns

ContractOracle - Fri, 2013-06-28 00:29
With Oracle 12c it is now possible to have multiple indexes on the same set of columns as long as there is a difference between the indexes (index type, partitioning etc), and one is invisible.

This makes it possible to quickly change index strategies with minimum impact to applications.

For this example I will first create a test partitioned table.

SQL> CREATE TABLE test_range
(id  NUMBER(5),
att1 char(1),
att2 char(1),
att3 char(1))
PARTITION BY RANGE(id)
(
PARTITION id_10 VALUES LESS THAN(10),
PARTITION id_20 VALUES LESS THAN(20)
);  

Table created.

Now create a global index on ATT1.

SQL> create index att1_global on test_range(att1) global;

Index created.

Try to create another index on ATT1 with local partitioning, and it fails because the existing index is still visible.

SQL> create index att1_local on test_range(att1) local;
create index att1_local on test_range(att1) local
                                      *
ERROR at line 1:
ORA-01408: such column list already indexed

Try to create an invisible global index on ATT1 and it fails because the structure is the same as the existing index.

SQL> create index att1_global2 on test_range(att1) global invisible;
create index att1_global2 on test_range(att1) global invisible
                                        *
ERROR at line 1:
ORA-01408: such column list already indexed

Try to create an invisible index with local partitioning and it is successful.

SQL> create index att1_local on test_range(att1) local invisible;

Index created.

Try to make the locally partitioned index visible, and it fails because there is another visible index with the same columns.

SQL> alter index att1_local visible;
alter index att1_local visible
*
ERROR at line 1:
ORA-14147: There is an existing VISIBLE index defined on the same set of
columns.

We need to make the existing index invisible first, then make the new index visible.  With this method we can easily test multiple indexing strategies without needing long outages to drop and recreate indexes.

SQL> alter index att1_global invisible;

Index altered.

SQL> alter index att1_local visible;

Index altered.

Categories: DBA Blogs

Oracle 12c New Features - SQL*Loader Express

ContractOracle - Thu, 2013-06-27 23:06
Oracle 12c introduces Sql*Loader Express features, which allow users to run sqlldr with minimum configuration.  

The following example shows loading records into table EMPLOYEE from CSV file EMPLOYEE.dat without having to create a control file.


SQL> create table EMPLOYEE (id integer primary key, name varchar2(10));

Table created.

SQL> exit
Disconnected from Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics, Real Application Testing
and Unified Auditing options

[oracle@rac1 admin]$ more EMPLOYEE.dat
1,Adam
2,Ben
3,Colin
4,Dean
5,Evan
6,Frank
7,Greg
8,Hank
9,Ian
10,Jack
[oracle@rac1 admin]$ sqlldr test/test TABLE=EMPLOYEE

SQL*Loader: Release 12.1.0.1.0 - Production on Fri Jun 28 11:58:11 2013

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

Express Mode Load, Table: EMPLOYEE
Path used:      External Table, DEGREE_OF_PARALLELISM=AUTO
SQL*Loader-816: error creating temporary directory object SYS_SQLLDR_XT_TMPDIR_00000 for file EMPLOYEE.dat
ORA-01031: insufficient privileges
SQL*Loader-579: switching to direct path for the load
SQL*Loader-583: ignoring trim setting with direct path, using value of LDRTRIM
SQL*Loader-584: ignoring DEGREE_OF_PARALLELISM setting with direct path, using value of NONE
Express Mode Load, Table: EMPLOYEE
Path used:      Direct

Load completed - logical record count 10.

Table EMPLOYEE:
  10 Rows successfully loaded.

Check the log file:
  EMPLOYEE.log
for more information about the load.

[oracle@rac1 admin]$ ls EMPLOYEE*
EMPLOYEE.dat  EMPLOYEE.log

[oracle@rac1 admin]$ more EMPLOYEE.log

SQL*Loader: Release 12.1.0.1.0 - Production on Fri Jun 28 11:58:11 2013

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

Express Mode Load, Table: EMPLOYEE
Data File:      EMPLOYEE.dat
  Bad File:     EMPLOYEE_%p.bad
  Discard File:  none specified

 (Allow all discards)

Number to load: ALL
Number to skip: 0
Errors allowed: 50
Continuation:    none specified
Path used:      External Table

Table EMPLOYEE, loaded from every logical record.
Insert option in effect for this table: APPEND

Column Name                Position   Len   Term Encl Datatype
-------------------------- ---------- ----- ---- ---- ---------
ID                         FIRST      *     ,         CHARACTER
NAME                       NEXT       *     ,         CHARACTER

Generated control file for possible reuse:
OPTIONS(EXTERNAL_TABLE=EXECUTE, TRIM=LRTRIM)
LOAD DATA
INFILE 'EMPLOYEE'
APPEND
INTO TABLE EMPLOYEE
FIELDS TERMINATED BY ","
(
  ID,
  NAME
)
End of generated control file for possible reuse.

SQL*Loader-816: error creating temporary directory object SYS_SQLLDR_XT_TMPDIR_00000 for file EMPLOYEE.dat
ORA-01031: insufficient privileges

----------------------------------------------------------------
SQL*Loader-579: switching to direct path for the load
SQL*Loader-583: ignoring trim setting with direct path, using value of LDRTRIM
SQL*Loader-584: ignoring DEGREE_OF_PARALLELISM setting with direct path, using value of NONE
----------------------------------------------------------------

Express Mode Load, Table: EMPLOYEE
Data File:      EMPLOYEE.dat
  Bad File:     EMPLOYEE.bad
  Discard File:  none specified

 (Allow all discards)

Number to load: ALL
Number to skip: 0
Errors allowed: 50
Continuation:    none specified
Path used:      Direct

Table EMPLOYEE, loaded from every logical record.
Insert option in effect for this table: APPEND

   Column Name        Position   Len   Term Encl Datatype
--------------------- ---------- ----- ---- ---- ----------------
ID                    FIRST      *     ,         CHARACTER
NAME                  NEXT       *     ,         CHARACTER

Generated control file for possible reuse:
OPTIONS(DIRECT=TRUE)
LOAD DATA
INFILE 'EMPLOYEE'
APPEND
INTO TABLE EMPLOYEE
FIELDS TERMINATED BY ","
(
  ID,
  NAME
)
End of generated control file for possible reuse.

The following index(es) on table EMPLOYEE were processed:
index TEST.SYS_C009860 loaded successfully with 10 keys

Table EMPLOYEE:
  10 Rows successfully loaded.
  0 Rows not loaded due to data errors.
  0 Rows not loaded because all WHEN clauses were failed.
  0 Rows not loaded because all fields were null.

Bind array size not used in direct path.
Column array  rows :    5000
Stream buffer bytes:  256000
Read   buffer bytes: 1048576

Total logical records skipped:          0
Total logical records read:            10
Total logical records rejected:         0
Total logical records discarded:        0
Total stream buffers loaded by SQL*Loader main thread:        1
Total stream buffers loaded by SQL*Loader load thread:        0

Run began on Fri Jun 28 11:58:11 2013
Run ended on Fri Jun 28 11:58:12 2013

Elapsed time was:     00:00:01.27
CPU time was:         00:00:00.02


[oracle@rac1 admin]$ sqlplus test/test

SQL*Plus: Release 12.1.0.1.0 Production on Fri Jun 28 12:05:49 2013

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

Last Successful login time: Fri Jun 28 2013 11:58:11 +08:00

Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics, Real Application Testing
and Unified Auditing options

SQL> select * from employee;

        ID NAME
---------- ----------
         1 Adam
         2 Ben
         3 Colin
         4 Dean
         5 Evan
         6 Frank
         7 Greg
         8 Hank
         9 Ian
        10 Jack


10 rows selected.


Categories: DBA Blogs

Oracle 12c New Features - FETCH FIRST ROWS, OFFSET

ContractOracle - Thu, 2013-06-27 22:20
Oracle 12c introduces sql syntax for row limiting.  This makes it easier to retrieve records in sets for display or processing.

Example :-

create table employee (id integer primary key, name varchar2(10));
insert into employee values (1,'Adam');
insert into employee values (2,'Ben');
insert into employee values (3,'Colin');
insert into employee values (4,'Dean');
insert into employee values (5,'Evan');
insert into employee values (6,'Frank');
insert into employee values (7,'Greg');
insert into employee values (8,'Hank');
insert into employee values (9,'Ian');
insert into employee values (10,'Jack');
commit;

SQL> select * from employee order by id fetch first 3 rows only;

        ID NAME
---------- ----------
         1 Adam
         2 Ben
         3 Colin

SQL> select * from employee order by id offset 3 rows fetch next 3 rows only;

        ID NAME
---------- ----------
         4 Dean
         5 Evan
         6 Frank

SQL> select * from employee order by id fetch first 50 percent rows only;

        ID NAME
---------- ----------
         1 Adam
         2 Ben
         3 Colin
         4 Dean
         5 Evan



Categories: DBA Blogs

Oracle 12c New Features - Extended Datatypes VARCHAR2 32767 bytes

ContractOracle - Thu, 2013-06-27 21:55
In Oracle 12c it is now possible to create VARCHAR2, NVARCHAR2, and RAW attributes of size 32767 bytes.

This is controlled by new parameter MAX_STRING_SIZE.  The value of the parameter defaults to STANDARD, but if you set it to EXTENDED you can use the expanded variable size.  Consider this carefully, as increased column sizes will have effects on any applications that use the data in variables and parameters and can limit the use of partitions and indexes on the extended datatypes.

In addition to setting MAX_STRING_SIZE=EXTENDED you also need to start the database in "upgrade" mode and run utl32k.sql  Also be aware that if you are running CDB / PDB you may need to upgrade them individually, including the SEED DB.

SQL> alter system set max_string_size=EXTENDED scope=spfile;

System altered.

SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup upgrade
ORACLE instance started.

Total System Global Area 1636814848 bytes
Fixed Size                  2288968 bytes
Variable Size            1056965304 bytes
Database Buffers          570425344 bytes
Redo Buffers                7135232 bytes
Database mounted.
Database opened.
SQL> @utl32k.sql

Session altered.

DOC>#######################################################################
DOC>#######################################################################
DOC>   The following statement will cause an "ORA-01722: invalid number"
DOC>   error if the database has not been opened for UPGRADE.
DOC>
DOC>   Perform a "SHUTDOWN ABORT"  and
DOC>   restart using UPGRADE.
DOC>#######################################################################
DOC>#######################################################################
DOC>#

no rows selected

DOC>#######################################################################
DOC>#######################################################################
DOC>   The following statement will cause an "ORA-01722: invalid number"
DOC>   error if the database does not have compatible >= 12.0.0
DOC>
DOC>   Set compatible >= 12.0.0 and retry.
DOC>#######################################################################
DOC>#######################################################################
DOC>#

PL/SQL procedure successfully completed.


Session altered.


0 rows updated.


Commit complete.


System altered.


PL/SQL procedure successfully completed.


Commit complete.


System altered.


Session altered.


PL/SQL procedure successfully completed.

No errors.

Session altered.


PL/SQL procedure successfully completed.


Commit complete.


Package altered.


TIMESTAMP
--------------------------------------------------------------------------------
COMP_TIMESTAMP UTLRP_BGN  2013-06-28 10:47:30

DOC>   The following PL/SQL block invokes UTL_RECOMP to recompile invalid
DOC>   objects in the database. Recompilation time is proportional to the
DOC>   number of invalid objects in the database, so this command may take
DOC>   a long time to execute on a database with a large number of invalid
DOC>   objects.
DOC>
DOC>   Use the following queries to track recompilation progress:
DOC>
DOC>   1. Query returning the number of invalid objects remaining. This
DOC>      number should decrease with time.
DOC>         SELECT COUNT(*) FROM obj$ WHERE status IN (4, 5, 6);
DOC>
DOC>   2. Query returning the number of objects compiled so far. This number
DOC>      should increase with time.
DOC>         SELECT COUNT(*) FROM UTL_RECOMP_COMPILED;
DOC>
DOC>   This script automatically chooses serial or parallel recompilation
DOC>   based on the number of CPUs available (parameter cpu_count) multiplied
DOC>   by the number of threads per CPU (parameter parallel_threads_per_cpu).
DOC>   On RAC, this number is added across all RAC nodes.
DOC>
DOC>   UTL_RECOMP uses DBMS_SCHEDULER to create jobs for parallel
DOC>   recompilation. Jobs are created without instance affinity so that they
DOC>   can migrate across RAC nodes. Use the following queries to verify
DOC>   whether UTL_RECOMP jobs are being created and run correctly:
DOC>
DOC>   1. Query showing jobs created by UTL_RECOMP
DOC>         SELECT job_name FROM dba_scheduler_jobs
DOC>            WHERE job_name like 'UTL_RECOMP_SLAVE_%';
DOC>
DOC>   2. Query showing UTL_RECOMP jobs that are running
DOC>         SELECT job_name FROM dba_scheduler_running_jobs
DOC>            WHERE job_name like 'UTL_RECOMP_SLAVE_%';
DOC>#

PL/SQL procedure successfully completed.


TIMESTAMP
--------------------------------------------------------------------------------
COMP_TIMESTAMP UTLRP_END  2013-06-28 10:47:32

DOC> The following query reports the number of objects that have compiled
DOC> with errors.
DOC>
DOC> If the number is higher than expected, please examine the error
DOC> messages reported with each object (using SHOW ERRORS) to see if they
DOC> point to system misconfiguration or resource constraints that must be
DOC> fixed before attempting to recompile these objects.
DOC>#

OBJECTS WITH ERRORS
-------------------
                  0

DOC> The following query reports the number of errors caught during
DOC> recompilation. If this number is non-zero, please query the error
DOC> messages in the table UTL_RECOMP_ERRORS to see if any of these errors
DOC> are due to misconfiguration or resource constraints that must be
DOC> fixed before objects can compile successfully.
DOC>#

ERRORS DURING RECOMPILATION
---------------------------
                          0


Function created.


PL/SQL procedure successfully completed.


Function dropped.

...Database user "SYS", database schema "APEX_040200", user# "98" 10:47:42
...Compiled 0 out of 2998 objects considered, 0 failed compilation 10:47:43
...263 packages
...255 package bodies
...453 tables
...11 functions
...16 procedures
...3 sequences
...458 triggers
...1322 indexes
...207 views
...0 libraries
...6 types
...0 type bodies
...0 operators
...0 index types
...Begin key object existence check 10:47:43
...Completed key object existence check 10:47:43
...Setting DBMS Registry 10:47:43
...Setting DBMS Registry Complete 10:47:43
...Exiting validate 10:47:43

PL/SQL procedure successfully completed.

SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup
ORACLE instance started.

Total System Global Area 1636814848 bytes
Fixed Size                  2288968 bytes
Variable Size            1056965304 bytes
Database Buffers          570425344 bytes
Redo Buffers                7135232 bytes
Database mounted.
Database opened.
SQL> show parameter max_string_size

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
max_string_size                      string      EXTENDED


SQL> connect test/test
Connected.
SQL> create table employee (emp_id integer, emp_name varchar2(20), life_story varchar2(32000));

Table created.

SQL> desc employee
 Name                                      Null?  Type
 ----------------------------------------- ------ ---------------
 EMP_ID                                           NUMBER(38)
 EMP_NAME                                         VARCHAR2(20)
 LIFE_STORY                                       VARCHAR2(32000)

SQL> select segment_name, segment_type from user_segments;

no rows selected

SQL> insert into employee values (1,'John','Wage Slave');

1 row created.

SQL> commit;

Commit complete.

Looking at the segments that exist we can see that by creating a TABLE with an extended VARCHAR2 column this was actually implemented using a TABLE, LOBSEGMENT, and associated LOBINDEX.

SQL> select segment_name, segment_type from user_segments;

SEGMENT_NAME                          SEGMENT_TYPE
------------------------------------- ---------------------------
EMPLOYEE                              TABLE
SYS_IL0000092103C00003$$              LOBINDEX
SYS_LOB0000092103C00003$$             LOBSEGMENT




Categories: DBA Blogs

Oracle 12c New Features - DBMS_UTILITY.EXPAND_SQL_TEXT

ContractOracle - Thu, 2013-06-27 20:34
As a DBA you occasionally get handed SQL statements many hundreds of lines long, and asked to help tune it.  Often the SQL selects from views based on views based on views, which can force the database to access the same table multiple times and join to itself, leading to poor performance.  To work out what the sql is actually doing the DBA needs to extract the sql from every view, merge it, then try to work out if it can be improved.  This can be time consuming, but 12c has introduced DBMS_UTILITY.EXPAND_SQL_TEXT to help.

The following gives and example of expanding a simple sql statement based on a view.


SQL> create table employee (emp_id integer, emp_name varchar2(20));

Table created.

SQL> insert into employee values (1,'John');

1 row created.

SQL> insert into employee values (2,'David');

1 row created.

SQL> commit;

Commit complete.

SQL> create view v1 as select * from employee;

View created.

SQL> create view v2 as select * from employee;

View created.

If we were just given the following piece of SQL we could run it through dbms_utility.expand_sql_text to get a better idea of what the logic in v1 and v2 is.  

SQL> select * from v1 union select * from v2;

    EMP_ID EMP_NAME
---------- --------------------
         1 John
         2 David

SQL> set linesize 32000 pagesize 0 serveroutput on
SQL> declare
   original_sql clob :='select * from v1 union select * from v2';
   expanded_sql clob := empty_clob();
begin
    dbms_utility.expand_sql_text(original_sql,expanded_sql);
    dbms_output.put_line(expanded_sql);
end;
/  
(SELECT "A3"."EMP_ID" "EMP_ID","A3"."EMP_NAME" "EMP_NAME" FROM  (SELECT "A4"."EMP_ID" "EMP_ID","A4"."EMP_NAME" "EMP_NAME" FROM TEST."EMPLOYEE" "A4") "A3")UNION (SELECT "A2"."EMP_ID" "EMP_ID","A2"."EMP_NAME" "EMP_NAME" FROM  (SELECT "A5"."EMP_ID" "EMP_ID","A5"."EMP_NAME" "EMP_NAME" FROM TEST."EMPLOYEE" "A5") "A2")

PL/SQL procedure successfully completed.

So we can see from the expanded query that the original sql was just doing a union of two identical selects from table TEST.EMPLOYEE, which we can easily simplify to a single query with better performance.

# I found the following reference from Jonathan Lewis which indicates this procedure previously existed in package DBMS_SQL2
Categories: DBA Blogs

Pages

Subscribe to Oracle FAQ aggregator - DBA Blogs