Configure ACFS in Oracle 11.2

SQL> alter diskgroup data add volume acfsvol1 size 1G;
alter diskgroup data add volume acfsvol1 size 1G
*
ERROR at line 1:
ORA-15032: not all alterations performed
ORA-15477: cannot communicate with the volume driver

[root@vmxdb01 ~]# cd /u01/app/11.2.0/grid/bin/
[root@vmxdb01 bin]# ./acfsroot install
ACFS-9300: ADVM/ACFS distribution files found.
ACFS-9312: Existing ADVM/ACFS installation detected.
ACFS-9314: Removing previous ADVM/ACFS installation.
ACFS-9315: Previous ADVM/ACFS components successfully removed.
ACFS-9307: Installing requested ADVM/ACFS software.
ACFS-9308: Loading installed ADVM/ACFS drivers.
ACFS-9321: Creating udev for ADVM/ACFS.
ACFS-9323: Creating module dependencies - this may take some time.
ACFS-9154: Loading 'oracleoks.ko' driver.
ACFS-9154: Loading 'oracleadvm.ko' driver.
ACFS-9154: Loading 'oracleacfs.ko' driver.
ACFS-9327: Verifying ADVM/ACFS devices.
ACFS-9156: Detecting control device '/dev/asm/.asm_ctl_spec'.
ACFS-9156: Detecting control device '/dev/ofsctl'.
ACFS-9309: ADVM/ACFS installation correctness verified.

[root@vmxdb01 bin]# acfsload start <— run this in the /etc/rc.local to enable mount during the bootup
ACFS-9391: Checking for existing ADVM/ACFS installation.
ACFS-9392: Validating ADVM/ACFS installation files for operating system.
ACFS-9393: Verifying ASM Administrator setup.
ACFS-9308: Loading installed ADVM/ACFS drivers.
ACFS-9327: Verifying ADVM/ACFS devices.
ACFS-9156: Detecting control device '/dev/asm/.asm_ctl_spec'.
ACFS-9156: Detecting control device '/dev/ofsctl'.
ACFS-9322: completed

[root@vmxdb01 bin]# lsmod | grep oracle
oracleacfs           1976568  0
oracleadvm            243152  0
oracleoks             426742  2 oracleacfs,oracleadvm
oracleasm              53865  1

[root@vmxdb01 ~]# mkdir /acfs

[oracle@vmxdb01 stage]$ sqlplus / as sysasm

SQL*Plus: Release 11.2.0.4.0 Production on Mon Jun 30 22:07:21 2014

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

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Automatic Storage Management option

SQL> alter diskgroup data add volume acfsvol1 size 1G;

Diskgroup altered.

[oracle@vmxdb01 stage]$ /sbin/mkfs -t acfs -b 4k /dev/asm/acfsvol1-436
mkfs.acfs: version                   = 11.2.0.4.0
mkfs.acfs: on-disk version           = 39.0
mkfs.acfs: volume                    = /dev/asm/acfsvol1-436
mkfs.acfs: volume size               = 1073741824
mkfs.acfs: Format complete.

[oracle@vmxdb01 stage]$ /sbin/acfsutil registry -f -a /dev/asm/acfsvol1-436 /acfs
acfsutil registry: mount point /acfs successfully added to Oracle Registry

[root@vmxdb01 ~]# /bin/mount -t acfs /dev/asm/acfsvol1-436 /acfs
[root@vmxdb01 ~]# chown oracle:dba /acfs
[root@vmxdb01 ~]# df -h /acfs
Filesystem            Size  Used Avail Use% Mounted on
/dev/asm/acfsvol1-436
                      1.0G   41M  984M   4% /acfs

Mount All: (either command)

[root@vmxdb01 ~]# /bin/mount -t acfs -o all none none
[root@vmxdb01 ~]# /sbin/mount.acfs -o all

Umount All:   

[root@vmxdb01 ~]# /bin/umount -t acfs -a

 

Set ASM Disk_Repair_time for ASM Diskgroup in Oracle 11.2

SQL> col name for a20
SQL> col compatibility for a15
SQL> col database_compatibility for a25
SQL> select name, compatibility, database_compatibility from v$asm_diskgroup;

NAME                           COMPATIBILITY   DATABASE_COMPATIBILITY
------------------------------ --------------- -------------------------
DATA                           11.2.0.0.0      10.1.0.0.0

SQL> select name,value from v$asm_attribute where group_number=1 and name not like 'template%';

NAME                                               VALUE
-------------------------------------------------- --------------------
disk_repair_time                                   3.6h
au_size                                            1048576
sector_size                                        512
compatible.asm                                     11.2.0.0.0
compatible.rdbms                                   10.1.0.0.0
compatible.advm                                    11.2.0.0.0
cell.smart_scan_capable                            FALSE
access_control.enabled                             FALSE
access_control.umask                               066

9 rows selected.

SQL>  alter diskgroup data set attribute 'disk_repair_time' = '10h';
alter diskgroup data set attribute 'disk_repair_time' = '10h'
*
ERROR at line 1:
ORA-15032: not all alterations performed
ORA-15242: could not set attribute disk_repair_time
ORA-15283: ASM operation requires compatible.rdbms of 11.1.0.0.0 or higher

SQL> alter diskgroup data set attribute 'compatible.rdbms' = '11.2.0.0.0';

Diskgroup altered.

SQL> select name, compatibility, database_compatibility from v$asm_diskgroup;

NAME                 COMPATIBILITY   DATABASE_COMPATIBILITY
-------------------- --------------- -------------------------
DATA                 11.2.0.0.0      11.2.0.0.0

SQL> select name,value from v$asm_attribute where group_number=1 and name not like 'template%';

NAME                                               VALUE
-------------------------------------------------- --------------------
disk_repair_time                                   10h
au_size                                            1048576
sector_size                                        512
compatible.asm                                     11.2.0.0.0
compatible.rdbms                                   11.2.0.0.0
compatible.advm                                    11.2.0.0.0
cell.smart_scan_capable                            FALSE
access_control.enabled                             FALSE
access_control.umask                               066

9 rows selected.

Fallback is not possible:

SQL>  alter diskgroup data set attribute 'compatible.rdbms' = '10.1.0.0.0';
alter diskgroup data set attribute 'compatible.rdbms' = '10.1.0.0.0'
*
ERROR at line 1:
ORA-15032: not all alterations performed
ORA-15242: could not set attribute compatible.rdbms
ORA-15244: new compatibility setting less than current [11.2.0.0.0]

Install/Configure ASMLIB on Oracle Linux 6.5

 

Files to be installed:

• kmod-oracleasm
• oracleasm-support.x86_64
• oracleasmlib

[root@vmxdb01 ~]# uname -a
Linux vmxdb01.dbaglobe.com 2.6.39-400.215.3.el6uek.x86_64 #1 SMP Fri Jun 20 00:37:05 PDT 2014 x86_64 x86_64 x86_64 GNU/Linux

[root@vmxdb01 ~]# yum install kmod-oracleasm oracleasm-support.x86_64

[root@vmxdb01 ~]# wget http://download.oracle.com/otn_software/asmlib/oracleasmlib-2.0.4-1.el6.x86_64.rpm

[root@vmxdb01 ~]# rpm -ihv oracleasmlib-2.0.4-1.el6.x86_64.rpm

Configure the ASMLIB:

[root@vmxdb01 ~]# /etc/init.d/oracleasm configure
Configuring the Oracle ASM library driver.

This will configure the on-boot properties of the Oracle ASM library
driver.  The following questions will determine whether the driver is
loaded on boot and what permissions it will have.  The current values
will be shown in brackets ('[]').  Hitting <ENTER> without typing an
answer will keep that current value.  Ctrl-C will abort.

Default user to own the driver interface []: oracle
Default group to own the driver interface []: dba
Start Oracle ASM library driver on boot (y/n) [n]: y
Scan for Oracle ASM disks on boot (y/n) [y]: y
Writing Oracle ASM library driver configuration: done
Initializing the Oracle ASMLib driver:                     [  OK  ]
Scanning the system for Oracle ASMLib disks:               [  OK  ]

[root@vmxdb01 ~]# /etc/init.d/oracleasm createdisk VOL4G /dev/sdb1
Marking disk "VOL4G" as an ASM disk:                       [  OK  ]
[root@vmxdb01 ~]# /etc/init.d/oracleasm createdisk VOL12G /dev/sdc1
Marking disk "VOL12G" as an ASM disk:                      [  OK  ]

[root@vmxdb01 ~]# ls -l /dev/sd[bc]1 /dev/oracleasm/disks/
brw-rw----. 1 root disk 8, 17 Jun 29 22:29 /dev/sdb1
brw-rw----. 1 root disk 8, 33 Jun 29 22:26 /dev/sdc1

/dev/oracleasm/disks/:
total 0
brw-rw----. 1 oracle dba 8, 33 Jun 29 22:26 VOL12G
brw-rw----. 1 oracle dba 8, 17 Jun 29 22:29 VOL4G

[root@vmxdb01 ~d]# oracleasm querydisk -d /dev/sdb1
Device "/dev/sdb1" is marked an ASM disk with the label "VOL4G"
[root@vmxdb01 ~]# oracleasm querydisk -d VOL4G
Disk "VOL4G" is a valid ASM disk on device [8,17]

[root@vmxdb01 ~]# oracleasm-discover
Using ASMLib from /opt/oracle/extapi/64/asm/orcl/1/libasm.so
[ASM Library - Generic Linux, version 2.0.4 (KABI_V2)]
Discovered disk: ORCL:VOL12G [25163776 blocks (12883853312 bytes), maxio 512]
Discovered disk: ORCL:VOL4G [8385867 blocks (4293563904 bytes), maxio 512]

Create the DiskGroup:

SQL> CREATE DISKGROUP DATA EXTERNAL REDUNDANCY  DISK 'ORCL:VOL12G' SIZE 12287M  ATTRIBUTE 'compatible.asm'='11.2.0.0.0','au_size'='1M' /* ASMCA */
NOTE: Assigning number (1,0) to disk (ORCL:VOL12G)
NOTE: initializing header on grp 1 disk VOL12G
NOTE: initiating PST update: grp = 1
GMON updating group 1 at 1 for pid 17, osid 2509
NOTE: group DATA: initial PST location: disk 0000 (PST copy 0)
NOTE: PST update grp = 1 completed successfully
NOTE: cache registered group DATA number=1 incarn=0x9798f809
NOTE: cache began mount (first) of group DATA number=1 incarn=0x9798f809
NOTE: cache opening disk 0 of grp 1: VOL12G label:VOL12G
NOTE: cache creating group 1/0x9798F809 (DATA)
NOTE: cache mounting group 1/0x9798F809 (DATA) succeeded
NOTE: allocating F1X0 on grp 1 disk VOL12G
NOTE: Created Used Space Directory for 1 threads
NOTE: diskgroup must now be re-mounted prior to first use
NOTE: cache dismounting (clean) group 1/0x9798F809 (DATA)
NOTE: messaging CKPT to quiesce pins Unix process pid: 2509, image: oracle@vmxdb01.dbaglobe.com (TNS V1-V3)
NOTE: lgwr not being msg'd to dismount
NOTE: cache dismounted group 1/0x9798F809 (DATA)
GMON dismounting group 1 at 2 for pid 17, osid 2509
GMON dismounting group 1 at 3 for pid 17, osid 2509
NOTE: Disk VOL12G in mode 0x7e marked for de-assignment
SUCCESS: diskgroup DATA was created

Possible errors if oracleasmlib not installed:

1. oracleasm-discover is missing

2. /opt/oracle/extapi/64/asm/orcl/1/libasm.so is missing

3. Errors:ORA-15014 ORA-15031

Sun Jun 29 23:45:52 2014
SQL> CREATE DISKGROUP DATA EXTERNAL REDUNDANCY  DISK '/dev/oracleasm/disks/VOL12G' SIZE 12287M  ATTRIBUTE 'compatible.asm'='11.2.0.0.0','au_size'='1M' /* ASMCA */
NOTE: failed to discover disks from gpnp profile asm diskstring
Errors in file /u01/app/oracle/diag/asm/+asm/+ASM/trace/+ASM_rbal_20523.trc:
ORA-29786: SIHA attribute GET failed with error [Attribute 'ASM_DISKSTRING' sts[200] lsts[0]]
ORA-15018: diskgroup cannot be created
ORA-15031: disk specification '/dev/oracleasm/disks/VOL12G' matches no disks
ORA-15014: path '/dev/oracleasm/disks/VOL12G' is not in the discovery set
ERROR: CREATE DISKGROUP DATA EXTERNAL REDUNDANCY  DISK '/dev/oracleasm/disks/VOL12G' SIZE 12287M  ATTRIBUTE 'compatible.asm'='11.2.0.0.0','au_size'='1M' /* ASMCA */
Sun Jun 29 23:45:52 2014
GMON dismounting group 1 at 2 for pid 17, osid 20619
ERROR: diskgroup DATA was not created
Sun Jun 29 23:46:12 2014
NOTE: failed to discover disks from gpnp profile asm diskstring
Errors in file /u01/app/oracle/diag/asm/+asm/+ASM/trace/+ASM_rbal_20523.trc:
ORA-29786: SIHA attribute GET failed with error [Attribute 'ASM_DISKSTRING' sts[200] lsts[0]]
Sun Jun 29 23:46:12 2014

How Audit is working in distributed environment

On the Oracle database security documentation, there is a short paragraph on audit distributed environment:

Auditing is site autonomous. A database instance audits only the statements issued by directly connected users. A local Oracle Database node cannot audit actions that take place in a remote database.

What will be the audit output looks like if the session is via DBLINK?

Scenario preparation:

SQL> show user;
USER is "DONGHUA"

SQL> create user linkuser identified by ora123;

User created.

SQL> grant select any table to linkuser;

Grant succeeded.

SQL> grant create session to linkuser;

Grant succeeded.

SQL> audit all on t1;

Audit succeeded.

SQL> create database link orcl_link connect to linkuser identified by ora123 using 'orcl';

Database link created.

SQL> select count(*) from donghua.t1@orcl_link;

  COUNT(*)
----------
         1

Output:

Surprisingly, the session_user context kept through DBLink:

SQL> select sys_context('userenv','session_user') from dual@orcl_link;

SYS_CONTEXT('USERENV','SESSION_USER')
--------------------------------------------------------------------------------
DONGHUA

SQL> select sys_context('userenv','session_user') from dual;

SYS_CONTEXT('USERENV','SESSION_USER')
--------------------------------------------------------------------------------
DONGHUA

1z0-060 Upgrade to Oracle Database 12c - Missing tempfile will be recreated during the database startup

SMON: enabling tx recovery
Errors in file /u01/app/oracle/diag/rdbms/orclcdb/orclcdb/trace/orclcdb_dbw0_3896.trc:
ORA-01157: cannot identify/lock data file 201 - see DBWR trace file
ORA-01110: data file 201: '/u01/app/oracle/oradata/orclcdb/temp01.dbf'
ORA-27037: unable to obtain file status
Solaris-AMD64 Error: 2: No such file or directory
Additional information: 3
Errors in file /u01/app/oracle/diag/rdbms/orclcdb/orclcdb/trace/orclcdb_dbw0_3896.trc:
ORA-01186: file 201 failed verification tests
ORA-01157: cannot identify/lock data file 201 - see DBWR trace file
ORA-01110: data file 201: '/u01/app/oracle/oradata/orclcdb/temp01.dbf'
File 201 not verified due to error ORA-01157
Re-creating tempfile /u01/app/oracle/oradata/orclcdb/temp01.dbf
Starting background process SMCO
SMCO started with pid=32, OS id=3962

ORA-25152: TEMPFILE cannot be dropped at this time

SQL> alter tablespace temp add tempfile '/u01/app/oracle/oradata/orclcdb/temp02.dbf' size 10M autoextend on;

Tablespace altered.

SQL> alter tablespace temp drop tempfile '/u01/app/oracle/oradata/orclcdb/temp01.dbf';
alter tablespace temp drop tempfile '/u01/app/oracle/oradata/orclcdb/temp01.dbf'
*
ERROR at line 1:
ORA-25152: TEMPFILE cannot be dropped at this time

SQL> show parameter db_files

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_files                             integer     200

SQL> SELECT file#, name from v$tempfile;

     FILE# NAME
---------- ----------------------------------------------------------------------
         1 /u01/app/oracle/oradata/orclcdb/temp01.dbf
         2 /u01/app/oracle/oradata/orclcdb/pdbseed/pdbseed_temp01.dbf
         3 /u01/app/oracle/oradata/orclcdb/pdb1/temp01.dbf
         4 /u01/app/oracle/oradata/orclcdb/temp02.dbf

SQL> SELECT s.sid, s.username, s.status, u.tablespace, u.segfile#, u.contents, u.extents, u.blocks
  2   FROM v$session s, v$sort_usage u
  3  WHERE s.saddr=u.session_addr
  4  ORDER BY u.tablespace, u.segfile#, u.segblk#, u.blocks;

       SID USERNAME                       STATUS   TABLESPACE   SEGFILE# CONTENTS     EXTENTS     BLOCKS
---------- ------------------------------ -------- ---------- ---------- --------- ---------- ----------
        10 SYS                            ACTIVE   TEMP              201 TEMPORARY          1        128

SQL> select sid,serial# from v$session where sid = 10;

       SID    SERIAL#
---------- ----------
        10          3

SQL> alter system kill session '10,3';

SQL> alter tablespace temp drop tempfile '/u01/app/oracle/oradata/orclcdb/temp01.dbf';

Tablespace altered.

1z0-060 Upgrade to Oracle Database 12c - Move a data file online

oracle@solaris:~$ sqlplus donghua@orcl

SQL*Plus: Release 12.1.0.1.0 Production on Thu Jun 12 04:26:54 2014

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

Enter password:
Last Successful login time: Thu Jun 12 2014 04:26:43 +08:00

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

SQL> alter database move datafile '/u01/app/oracle/oradata/orcl/users2.dbf'
  2  to '/u01/app/oracle/oradata/orcl/users2_new.dbf';

Database altered.

SQL> ! ls -l /u01/app/oracle/oradata/orcl/users2*
-rw-r-----   1 oracle   oinstall 10493952 Jun 12 04:27 /u01/app/oracle/oradata/orcl/users2_new.dbf

SQL> alter database move datafile '/u01/app/oracle/oradata/orcl/users2_new.dbf'
  2   to '/u01/app/oracle/oradata/orcl/users2.dbf' keep;

Database altered.

SQL> ! date
Thursday, June 12, 2014 04:29:02 AM SGT

SQL> alter system checkpoint;

System altered.

SQL> ! ls -l /u01/app/oracle/oradata/orcl/users2*
-rw-r-----   1 oracle   oinstall 10493952 Jun 12 04:28 /u01/app/oracle/oradata/orcl/users2_new.dbf
-rw-r-----   1 oracle   oinstall 10493952 Jun 12 04:29 /u01/app/oracle/oradata/orcl/users2.dbf

SQL> alter database move datafile '/u01/app/oracle/oradata/orcl/users2.dbf'
  2  to '/u01/app/oracle/oradata/orcl/users2_new.dbf';
alter database move datafile '/u01/app/oracle/oradata/orcl/users2.dbf'
*
ERROR at line 1:
ORA-01119: error in creating database file
'/u01/app/oracle/oradata/orcl/users2_new.dbf'
ORA-27038: created file already exists
Additional information: 1

SQL> alter database move datafile '/u01/app/oracle/oradata/orcl/users2.dbf'
  2  to '/u01/app/oracle/oradata/orcl/users2_new.dbf' reuse;

Database altered.

SQL> ! ls -l /u01/app/oracle/oradata/orcl/users2*
-rw-r-----   1 oracle   oinstall 10493952 Jun 12 04:31 /u01/app/oracle/oradata/orcl/users2_new.dbf

Creating users in 12c - avoiding ORA-65096 or ORA-65049

For common user and common roles, must prefix with C## or c##

For example: create user c##guest identified by password;

DBMS_SQL SECURITY ERROR: ORA-29471!

"Ora-29471: Dbms_sql Access Denied" From Software Package After Upgrade (Doc ID 556301.1)

---

Applies to:

Oracle Server - Enterprise Edition - Version 11.1.0.6 and later
Information in this document applies to any platform.

Symptoms

Receiving error running DBMS_SQL in 11g.

ORA-29471: DBMS_SQL access denied
ORA-06512: at "SYS.DBMS_SQL", line 980
ORA-06512: at "SYS.BUILD_XVIEW", line 28
ORA-06512: at line 12 in table MakeXviews

This may have worked in 10g.

Changes

DBMS_SQL has been recoded with some security checks in 11g.   A new error has been introduced, ORA-29471, which indicates usage fails a security check.

Cause

Usage of DBMS_SQL has failed a security check.

Solution

The application must be recoded.  For a temporary work around, a new parameter security_level can be added into DBMS_SQL.OPEN_CURSOR.

security_level specifies the level of security protection to enforce on the opened cursor. Valid security level values are 0, 1, and 2. When a NULL argument value is provided to this overload, as well as for cursors opened using the overload of open_cursor without the security_level parameter, the default security level value 1 will be enforced on the opened cursor.

• Level 0 - allows all DBMS_SQL operations on the cursor without any security checks. The cursor may be fetched from, and even re-bound and re-executed, by code running with a different effective userid or roles than those in effect at the time the cursor was parsed. This level of security is off by default.

dbms_sql.open_cursor (
SECURITY_LEVEL in integer)
return integer;

• Level 1 - requires that the effective userid and roles of the caller to DBMS_SQL for bind and execute operations on this cursor must be the same as those of the caller of the most recent parse operation on this cursor.
• Level 2 - requires that the effective userid and roles of the caller to DBMS_SQL for all bind, execute, define, describe, and fetch operations on this cursor must be the same as those of the caller of the most recent parse operation on this cursor.

 

To completely turn off the security features in dbms_sql, you can set the following parameter :

alter system set "_dbms_sql_security_level" = 384 scope=spfile;

Please note that this setting is not recommended and that this workaround might cease to work in future versions.

Document reference:

Security model:
http://docs.oracle.com/cd/B28359_01/appdev.111/b28419/d_sql.htm#i1027587
Open cursor:
http://docs.oracle.com/cd/B28359_01/appdev.111/b28419/d_sql.htm#i997665

Oracle database Proxy Authentication Example

SQL> create user c##guest identified by password;

User created.

SQL> alter user c##donghua grant connect through c##guest;

User altered.

oracle@solaris:~$ sqlplus c##guest[c##donghua]/password@orclcdb

SQL*Plus: Release 12.1.0.1.0 Production on Mon Jun 9 23:37:29 2014

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

Last Successful login time: Mon Jun 09 2014 23:37:11 +08:00

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

SQL> show user;
USER is "C##DONGHUA"

SQL> col session_user for a20
SQL> col proxy_user for a20
SQL> select sys_context('USERENV','SESSION_USER') session_user,
  2  sys_context('USERENV','PROXY_USER') proxy_user from dual;

SESSION_USER         PROXY_USER
-------------------- --------------------
C##DONGHUA           C##GUEST

 

Enabling Common Users to Access Data in Specific PDBs

 

Extracted from: http://docs.oracle.com/cd/E16655_01/network.121/e17607/authorization.htm#DBSEG806

To enable common users to access data about specific PDBs, you must run the ALTER USER statement in the root.

Example 4-6 shows how to run the ALTER USER statement to enable the common user c##hr_admin to view information pertaining to the CDB$ROOT, SALES_PDB, and hrpdb containers in all container data objects that he can access, using the V$SESSION view.

Example 4-6 Enabling a Common User to View Specific Object Data

CONNECT SYSTEM@root
Enter password: password
Connected.

ALTER USER c##hr_admin
SET CONTAINER_DATA = (CDB$ROOT, SALESPDB, HRPDB) 
FOR V$SESSION CONTAINER=CURRENT;

In this specification:

• 
  

  CDB$ROOT, SALES_PDB, hrpdb refer to the containers that must be accessible to user c##hr_admin. You must include CDB$ROOT.

  
  



• 
  

  FOR V$SESSION specifies the CONTAINER_DATA dynamic view, which common user c##hr_admin will query.

  
  



• 
  

  CONTAINER = CURRENT must be specified because when you are connected to the root, CONTAINER=ALL is the default for the ALTER USER statement, but modification of the CONTAINER_DATA attribute must be restricted to the root.

  
  

Oracle 11gR2 express edition on windows 64bit is out

Download from here: http://www.oracle.com/technetwork/database/database-technologies/express-edition/downloads/index.html

1z0-060 Upgrade to Oracle Database 12c - Create and configure a CDB

With the Database Configuration Assistant (DBCA), a graphical tool:

1z0-060 Upgrade to Oracle Database 12c - Explain root and multitenant architecture

Extracted from: http://docs.oracle.com/cd/E16655_01/server.121/e17636/cdb_intro.htm#ADMIN14145

A CDB includes the following components:

• Root

  The root, named CDB$ROOT, stores Oracle-supplied metadata and common users. An example of metadata is the source code for Oracle-supplied PL/SQL packages. A common user is a database user known in every container. A CDB has exactly one root.

• Seed

  The seed, named PDB$SEED, is a template that you can use to create new PDBs. You cannot add objects to or modify objects in the seed. A CDB has exactly one seed.

• PDBs

  A PDB appears to users and applications as if it were a non-CDB. For example, a PDB can contain the data and code required to support a specific application. A PDB is fully backward compatible with Oracle Database releases before Oracle Database 12c.

Each of these components is called a container. Therefore, the root is a container, the seed is a container, and each PDB is a container. Each container has a unique container ID and name within a CDB. Figure 36-1 shows a CDB with several PDBs.

You can easily plug a PDB into a CDB and unplug a PDB from a CDB. When you plug in a PDB, you associate the PDB with a CDB. When you unplug a PDB, you disassociate the PDB from a CDB. An unplugged PDB consists of an XML file that describes the PDB and the PDB's files (such as the data files and wallet file).

You can unplug a PDB from one CDB and plug it into a different CDB without altering your schemas or applications. A PDB can be plugged into only one CDB at a time.

Each PDB has a globally unique identifier (GUID). The PDB GUID is primarily used to generate names for directories that store the PDB's files, including both Oracle Managed Files directories and non-Oracle Managed Files directories.

1z0-060 Upgrade to Oracle Database 12c - Identify the benefits of the multitenant container database

Purpose of a Multitenant Environment

(Extracted from: http://docs.oracle.com/cd/E16655_01/server.121/e17636/cdb_intro.htm#ADMIN14147)

A multitenant environment enables the central management of multiple PDBs in a single installation. By using a multitenant environment, you can accomplish the following goals:

• Cost reduction

  By consolidating hardware and sharing database memory and files, you reduce costs for hardware, storage, availability, and labor. For example, 100 PDBs on a single server share one database instance and one set of database files, thereby requiring less hardware and fewer personnel.

• Easier and more rapid movement of data and code

  By design, you can plug a PDB into a CDB, unplug the PDB from the CDB, and then plug this PDB into a different CDB. Therefore, you can easily move an application's database back end from one server to another.

• Easier management and monitoring of the physical database

  The CDB administrator can attend to one physical database (one set of files and one set of database instances) rather than split attention among dozens or hundreds of non-CDBs. Backup strategies and disaster recovery are simplified.

• Separation of data and code

  Although consolidated into a single physical CDB, PDBs mimic the behavior of traditional non-CDBs. For example, if a user error causes data loss, then a PDB administrator can use point-in-time recovery to retrieve the lost data without affecting other PDBs.

• Ease of performance tuning

  It is easier to collect performance metrics for a CDB than for multiple non-CDBs. It is easier to size one SGA than several SGAs.

• Support for Oracle Database Resource Manager

  In a CDB, one concern is contention for system resources among the PDBs running on the same server. Another concern is limiting resource usage for more consistent, predictable performance. To address such resource contention, usage, and monitoring issues, you can use Oracle Database Resource Manager

• Fewer patches and upgrades

It is easier to apply a patch to one CDB than to multiple non-CDBs and to upgrade one CDB than to upgrade several non-CDBs.

A multitenant environment is especially useful when you have many non-CDBs deployed on different hardware in multiple Oracle Database installations. These non-CDBs might use only a fraction of the hardware resources dedicated to them, and each one might not require a full-time database administrator to manage it.

By combining these non-CDBs into a CDB, you can make better use of your hardware resources and database administrator resources. In addition, you can move a PDB from one CDB to another without requiring changes to the applications that depend on the PDB.

Script used to re-grant privilege after revoking from public

Below script is enhanced version to skip these already granted privilege. The original script could be found here: Be Cautious When Revoking Privileges Granted to PUBLIC (Doc ID 247093.1)

SQL> select unique statements from
  2  (
  3   select nvl2(p.grantee,'-- ','')||'grant execute on '|| d.referenced_name||' to '||d.owner||';' statements
  4   from dba_dependencies d left join dba_tab_privs p
  5  on d.owner=p.grantee and p.privilege='EXECUTE' and d.referenced_name=p.table_name
  6   where d.referenced_owner in ('SYS','PUBLIC')
  7  and d.referenced_type in ('PACKAGE','SYNONYM')
  8  and d.referenced_name in
  9     (
10     'DBMS_RANDOM',
11     'DBMS_EXPORT_EXTENSION',
12     'UTL_FILE',
13     'DBMS_JOB',
14     'DBMS_LOB',
15     'UTL_SMTP',
16     'UTL_TCP',
17     'UTL_HTTP'
18     )
19  and d.owner <> 'SYS'
20  and d.owner <> 'PUBLIC'
21  )
22  order by replace(statements,'-- ','')
23  /

STATEMENTS
--------------------------------------------------------------------------------
-- grant execute on DBMS_JOB to APEX_030200;
-- grant execute on DBMS_JOB to APEX_040200;
grant execute on DBMS_JOB to DBSNMP;
grant execute on DBMS_JOB to XDB;
-- grant execute on DBMS_LOB to APEX_030200;
-- grant execute on DBMS_LOB to APEX_040200;
grant execute on DBMS_LOB to CTXSYS;
grant execute on DBMS_LOB to MDSYS;
-- grant execute on DBMS_LOB to ORDPLUGINS;
-- grant execute on DBMS_LOB to ORDSYS;
-- grant execute on DBMS_LOB to WMSYS;
grant execute on DBMS_LOB to XDB;
-- grant execute on DBMS_RANDOM to APEX_030200;
-- grant execute on DBMS_RANDOM to APEX_040200;
grant execute on DBMS_RANDOM to DBSNMP;
grant execute on DBMS_RANDOM to MDSYS;
-- grant execute on UTL_FILE to APEX_030200;
grant execute on UTL_FILE to MDSYS;
-- grant execute on UTL_FILE to ORACLE_OCM;
-- grant execute on UTL_FILE to ORDPLUGINS;
-- grant execute on UTL_FILE to ORDSYS;
-- grant execute on UTL_FILE to WMSYS;
grant execute on UTL_FILE to XDB;
-- grant execute on UTL_HTTP to APEX_030200;
-- grant execute on UTL_HTTP to APEX_040200;
grant execute on UTL_HTTP to MDSYS;
-- grant execute on UTL_HTTP to ORDPLUGINS;
-- grant execute on UTL_SMTP to APEX_030200;
-- grant execute on UTL_SMTP to APEX_040200;

29 rows selected.

----------------------------------------

select unique statements from
(
select nvl2(p.grantee,'-- ','')||'grant execute on '|| d.referenced_name||' to '||d.owner||';' statements
from dba_dependencies d left join dba_tab_privs p
on d.owner=p.grantee and p.privilege='EXECUTE' and d.referenced_name=p.table_name
where d.referenced_owner in ('SYS','PUBLIC')    
and d.referenced_type in ('PACKAGE','SYNONYM') 
and d.referenced_name in
    (
    'DBMS_RANDOM',
    'DBMS_EXPORT_EXTENSION',
    'UTL_FILE',
    'DBMS_JOB',
    'DBMS_LOB',
    'UTL_SMTP',
    'UTL_TCP',
    'UTL_HTTP'
    )
and d.owner <> 'SYS'
and d.owner <> 'PUBLIC'
)   
order by replace(statements,'-- ','')

Cannot Access the Specified Path or File on the Server

Symptom:

Cannot Access the Specified Path or File on the Server on SQL Server Failover Clustering when attached database or create new filegoups

First troubleshooting attempt:

Verify whether disk is part of cluster resource and SQL Server shall have dependency on it. Yes, this is configured as part of Cluster Storage used by SQL Server Instance.

Second troubleshooting attempt:

Anything wrong with the disk? Check through “Computer Management” –> “Disk Management”, surprisingly, the disk status is “Active, Primary Partition”, but other clustered disks only listed as “Primary Partition”

What does active mean? It means the volume is capable of containing an operating system bootable image. But this should not be the case for a disk used purely for SQL Server Databases.

After turn it off, problem resolved.

Steps to turn it off:

Start Administrator Command Prompt:

DISKPART> list disk

  Disk ###  Status         Size     Free     Dyn  Gpt
  --------  -------------  -------  -------  ---  ---
  Disk 0    Online           100 GB     0 B
  Disk 1    Online           100 GB 1024 KB
  Disk 2    Reserved       5000 GB  1984 KB
  Disk 3    Reserved       5000 GB  1024 KB

DISKPART> select disk 3

Disk 3 is now the selected disk.

DISKPART> list partition

  Partition ###  Type              Size     Offset
  -------------  ----------------  -------  -------
  Partition 1    Primary           5000 GB  1024 KB

DISKPART> select partition 1

Partition 1 is now the selected partition.

DISKPART> inactive

DiskPart marked the current partition as inactive.

DISKPART> exit

Leaving DiskPart...

1z0-060 Upgrade to Oracle Database 12c - Use OUI, DBCA for installation and configuration (Manage Pluggable Databases)

Manage Pluggable Databases:

oracle@solaris:~$ dbca -createPluggableDatabase -help
Create a Pluggable Database by specifying following parameters:
        -createPluggableDatabase
                -sourceDB <Database unique name for RAC Database and SID for Single Instance Database>
                -pdbName <New Pluggable Database Name>
                [-createAsClone <true|false Create PDB as clone. When "true" is passed a new PDB GUID is generated for the plugged in PDB>]
                [-createPDBFrom <DEFAULT | FILEARCHIVE | RMANBACKUP | USINGXML>]
                        [-pdbArchiveFile    <Full path and name for PDB Archive file, required when creating new PDB using FILEARCHIVE>]
                        [-PDBBackUpfile    <Full path and name for PDB backup file, required when creating new PDB using RMANBACKUP>]
                        [-PDBMetadataFile    <Full path and name for PDB metadata file, required when creating new PDB using RMANBACKUP>]
                        [-pdbAdminUserName     <PDB Administrator user name, required only when creating new PDB>]
                        [-pdbAdminPassword     <PDB Administrator user Password, required only when creating new PDB>]
                        [-createNewPDBAdminUser     <Specify true if new PDB Administrator user need to be created after plugging in PDB from Archive or File Set.>]
                        [-sourceFileNameConvert     <This clause specifies how to locate files listed in PDB XML metadata file.>]
                        [-fileNameConvert     <This clause specifies how to generate names of files.>]
                        [-copyPDBFiles  <true|false  Specify true if PDB datafiles need to be copied.>]
                [-pdbDatafileDestination <PDB datafile area>]
                [-useMetaDataFileLocation <true|false  Specify true if datafile path defined in metadata file within PDB archive file is to be used when extracting datafiles.>]
                [-registerWithDirService <true | false>
                        -dirServiceUserName    <user name for directory service>
                        -dirServicePassword    <password for directory service >
                        -walletPassword    <password for database wallet >]
                [-lbacsysPassword   <LBACSYS user Password is required for configuring OLS with directory service.>]
                [-createUserTableSpace <true|false  Specify true if a default user tablespace need to be created in new PDB.>]

                [-dvConfiguration <true | false Specify "true" to configure and enable Database Vault
                        -dvUserName     <Specify Database Vault Owner user name>
                        -dvUserPassword     <Specify Database Vault Owner password>
                        -dvAccountManagerName     <Specify separate Database Vault Account Manager >
                        -dvAccountManagerPassword     <Specify Database Vault Account Manager password>]
oracle@solaris:~$ dbca -unplugDatabase -help
Unplug a Pluggable Database by specifying following parameters:
        -unplugDatabase
                -sourceDB <Database unique name for RAC Database and SID for Single Instance Database>
                -pdbName <Pluggable Database Name>
                        [-pdbArchiveFile    <Absolute file path and name for PDB Archive file>]
                        [-PDBBackUpfile    <Absolute file path and name for PDB backup file, required when archive type is RMAN>]
                        [-PDBMetadataFile    <Absolute file path and name for PDB metadata file, required when archive type is RMAN or NONE>]
                        [-archiveType       < TAR | RMAN | NONE >]
                [-unregisterWithDirService  <true | false>
                        -dirServiceUserName    <user name for directory service>
                        -dirServicePassword    <password for directory service >
                        -walletPassword    <password for database wallet >]
oracle@solaris:~$ dbca -deletePluggableDatabase -help
Delete a Pluggable Database specifying following parameters:
        -deletePluggableDatabase
                -sourceDB <Database unique name for RAC Database and SID for Single Instance Database>
                -pdbName <Pluggable Database Name>
oracle@solaris:~$ dbca -configurePluggableDatabase -help
Configure a Pluggable Database specifying following parameters:
        -configurePluggableDatabase
                -sourceDB <Database unique name for RAC Database and SID for Single Instance Database>
                -pdbName <Pluggable Database Name>
                [-dvConfiguration <true | false Specify "true" to configure and enable Database Vault
                        -dvUserName     <Specify Database Vault Owner user name>
                        -dvUserPassword     <Specify Database Vault Owner password>
                        -dvAccountManagerName     <Specify separate Database Vault Account Manager >
                        -dvAccountManagerPassword     <Specify Database Vault Account Manager password>]
                [-olsConfiguration <true | false Specify "true" to configure and enable Oracle Label Security >
                [-registerWithDirService|-unregisterWithDirService  <true | false>
                        -dirServiceUserName    <user name for directory service>
                        -dirServicePassword    <password for directory service >
                        -walletPassword    <password for database wallet >]
                [-lbacsysPassword   <LBACSYS user Password is required for configuring OLS with directory service.>]

1z0-060 Upgrade to Oracle Database 12c - Use OUI, DBCA for installation and configuration (Manage Templates)

 

Managing Templates with DBCA

DBCA templates are XML files that contain information required to create a database. Templates are used in DBCA to create new databases and duplicate existing databases. The information in templates includes database options, initialization parameters, and storage attributes (for data files, tablespaces, control files, and online redo log files).

Templates can be used just like scripts, but they are more powerful than scripts because you have the option of duplicating a database. Duplication saves time because you copy the files of an existing database, referred to as a seed database, to the correct locations.

Templates are stored in the following directory:

ORACLE_HOME/assistants/dbca/templates

Advantages of Using Templates

Using templates has the following advantages:

• Time saving. If you use a template, then you do not have to define the database.



• Easy duplication. By creating a template containing your database settings, you can easily create a duplicate database without specifying parameters twice.



• Easy editing. You can quickly change database options from the template settings.



• Easy sharing. Templates can be copied from one computer to another.

Types of Templates

Templates are divided into the following types:

• Seed templates



• Nonseed templates

 

Manage Templates:

 

oracle@solaris:~$ dbca -createTemplateFromDB -help

Create a template from an existing database by specifying the following parameters:


        -createTemplateFromDB


                -sourceDB    <Database unique name for RAC Database and SID for Single Instance Database>


                -templateName      <new template name>


                -sysDBAUserName     <user name  with SYSDBA privileges>


                -sysDBAPassword     <password for sysDBAUserName user name>


                [-maintainFileLocations <true | false>]


                [-connectionString   <EZCONNECT String for connecting to remote database for example "host:port/servicename">]

oracle@solaris:~$ dbca -createCloneTemplate -help

Create a clone template from an existing database by specifying the following parameters:


        -createCloneTemplate


                -sourceSID    <Database unique name for RAC Database and SID for Single Instance Database>


                -templateName      <new template name>


                [-sysDBAUserName     <user name  with SYSDBA privileges>


                 -sysDBAPassword     <password for sysDBAUserName user name>]


                [-maxBackupSetSizeInMB     <Maximum backup set size (MB)>]


                [-rmanParallelism  <Parallelism value>]


                [-datafileJarLocation       <directory to place the datafiles in a compressed format>]

1z0-060 Upgrade to Oracle Database 12c - Use OUI, DBCA for installation and configuration (Delete Database)

 

Delete Database:

oracle@solaris:~$ dbca -deleteDatabase -help
Delete a database by specifying the following parameters:
        -deleteDatabase
                -sourceDB    <source database sid>
                [-sysDBAUserName     <user name  with SYSDBA privileges>
                 -sysDBAPassword     <password for sysDBAUserName user name>]
                [-emConfiguration <DBEXPRESS|CENTRAL|BOTH|NONE>]
                        [-omsHost     <Enterprise Manager central agent home>
                        -omsPort     <Enterprise Manager central agent port>
                        -emUser     <Host user name for EM backup job>
                        -emPassword     <Host user password for EM backup job>]]

1z0-060 Upgrade to Oracle Database 12c - Use OUI, DBCA for installation and configuration (Configure Database Options)

 

Configure Database Options:

1z0-060 Upgrade to Oracle Database 12c - Use OUI, DBCA for installation and configuration (Create Database)

Reference URL: http://docs.oracle.com/cd/E16655_01/server.121/e17643/install.htm#ADMQS023

Create Database:

oracle@solaris:~$ dbca -help
dbca  [-silent | -progressOnly] {<command> <options> }  | { [<command> [options] ] -responseFile  <response file > } [-continueOnNonFatalErrors <true | false>]
<command> : -createDatabase | -configureDatabase | -createTemplateFromDB | -createCloneTemplate | -generateScripts | -deleteDatabase | -createPluggableDatabase | -unplugDatabase | -deletePluggableDatabase | -configurePluggableDatabase
Enter "dbca -<command> -help" for more option
oracle@solaris:~$ dbca -createDatabase -help
Create a database by specifying the following parameters:
        -createDatabase
                -templateName <name of an existing template in default location or the complete template path>
                [-cloneTemplate]
                -gdbName <global database name>
                [-ignorePreReqs] <ignore prerequisite checks for current operation>
                [-sid <database system identifier>]
                [-createAsContainerDatabase <true|false>
                        [-numberOfPDBs <Number of Pluggable databases to be created, default is 0>]
                        [-pdbName <New Pluggable Database Name>]
                [-sysPassword <SYS user password>]
                [-systemPassword <SYSTEM user password>]
                [-emConfiguration <DBEXPRESS|CENTRAL|BOTH|NONE>]
                        -dbsnmpPassword     <DBSNMP user password>
                        [-omsHost     <EM management server host name>
                        -omsPort     <EM management server port number>
                        -emUser     <EM Admin username to add or modify targets>
                        -emPassword     <EM Admin user password>]]
                [-dvConfiguration <true | false Specify "true" to configure and enable Database Vault
                        -dvUserName     <Specify Database Vault Owner user name>
                        -dvUserPassword     <Specify Database Vault Owner password>
                        -dvAccountManagerName     <Specify separate Database Vault Account Manager >
                        -dvAccountManagerPassword     <Specify Database Vault Account Manager password>]
                [-olsConfiguration <true | false Specify "true" to configure and enable Oracle Label Security >
                [-datafileDestination <destination directory for all database files> |
-datafileNames <a text file containing database objects such as controlfiles, tablespaces, redo log files and spfile to their corresponding raw device file names mappings in name=value format.>]
                [-redoLogFileSize <size of each redo log file in megabytes>]
                [-recoveryAreaDestination <destination directory for all recovery files>]
                [-datafileJarLocation  <location of the data file jar, used only for clone database creation>]
                [-storageType < FS | ASM >
                        [-asmsnmpPassword     <ASMSNMP password for ASM monitoring>]
                         -diskGroupName   <database area disk group name>
                         -recoveryGroupName       <recovery area disk group name>
                [-characterSet <character set for the database>]
                [-nationalCharacterSet  <national character set for the database>]
                [-registerWithDirService <true | false>
                        -dirServiceUserName    <user name for directory service>
                        -dirServicePassword    <password for directory service >
                        -walletPassword    <password for database wallet >]
                [-listeners  <list of listeners to configure the database with>]
                [-variablesFile   <file name for the variable-value pair for variables in the template>]]
                [-variables  <comma separated list of name=value pairs>]
                [-initParams <comma separated list of name=value pairs>]
                [-sampleSchema  <true | false> ]
                [-memoryPercentage <percentage of physical memory for Oracle>]
                [-automaticMemoryManagement ]
                [-totalMemory <memory allocated for Oracle in MB>]
                [-databaseType <MULTIPURPOSE|DATA_WAREHOUSING|OLTP>]]
oracle@solaris:~$ dbca -configureDatabase -help
Configure a database by specifying the following parameters:
        -configureDatabase
                -sourceDB    <Database unique name for RAC Database and SID for Single Instance Database>
                [-sysDBAUserName     <user name  with SYSDBA privileges>
                 -sysDBAPassword     <password for sysDBAUserName user name>]
                [-registerWithDirService|-unregisterWithDirService|-regenerateDBPassword <true | false>
                        -dirServiceUserName    <user name for directory service>
                        -dirServicePassword    <password for directory service >
                        -walletPassword    <password for database wallet >]
                [-addDBOption     <Specify any of the following DB Options as a comma separated list: JSERVER | ORACLE_TEXT | IMEDIA | CWMLITE | SPATIAL | OMS | APEX | DV>]
                [-dvConfiguration <true | false Specify "true" to configure and enable Database Vault
                        -dvUserName     <Specify Database Vault Owner user name>
                        -dvUserPassword     <Specify Database Vault Owner password>
                        -dvAccountManagerName     <Specify separate Database Vault Account Manager >
                        -dvAccountManagerPassword     <Specify Database Vault Account Manager password>]