Wednesday, August 21, 2013

ASM Diskgroup Not Compatible For Database Usage | Oracle RAC 11g

ASM Diskgroup Not Compatible For Database Usage

Environment Details:
OS: Linux 5.8
Database Version: 11.2.0.3.0
User: oracle

When we try to create a new database in a 11g RAC Environment, we are getting the error Diskgroup "+DATA_ " is not compatible for database usage. Database compatible attribute of diskgroup should be the same or lower than the database compatible initialization parameter value. Database compatible parameter is set to "11.2.0.0.0" and the diskgroup database compatibility attribute is set  to "11.2.0.2".




Check the value of the compatibility attribute from ASM instance by querying the V$ASM_DISKGROUP view.

SQL> SELECT INSTANCE_NAME FROM V$INSTANCE;

INSTANCE_NAME
----------------
+ASM1

SQL>SELECT GROUP_NUMBER, NAME, COMPATIBILITY, DATABASE_COMPATIBILITY FROM V$ASM_DISKGROUP;

GROUP_NUMBER NAME            COMPATIBIL DATABASE_C
------------ --------------- ---------- ----------
           1 DATA_LINUX1     11.2.0.3.0 11.2.0.2.0
           2 DBFS_DG         11.2.0.3.0 11.2.0.2.0
           3 RECO_LINUX1     11.2.0.3.0 11.2.0.2.0

To Resolve the same we need to go the location $ORACLE_HOME/assistants/dbca/templates and edit the template we were trying to use(In our case its General_Purpose.dbc)

Search for the word "compatible" in the file and than change the attribute from 11.2.0.0.0 to 11.2.0.3.0

<InitParamAttributes>
      <InitParams>
         <initParam name="db_name" value=""/>
         <initParam name="dispatchers" value="(PROTOCOL=TCP) (SERVICE={SID}XDB)"/>
         <initParam name="audit_file_dest" value="{ORACLE_BASE}/admin/{DB_UNIQUE_NAME}/adump"/>
         <initParam name="compatible" value="11.2.0.3.0"/>
 
Then run dbca to create the database.

Friday, August 16, 2013

Oracle 12c Pluggable Database Creation Method 2: from Non CDB to CDB

How to Create Oracle Pluggable database
Method 2: Create new PDB from by plugging non-CDB into CDB. We are using DBMS_PDB package in this post, which is applicable only to the databases created in 12C and higher versions.

Environment Details:
OS: Sun Solaris 5.10
Database Version: 12C
CDB Database Name: CDB12C
Root Database Name: CDB$ROOT
Source Database Name: ORCLDB
PDB Database Name: PDB2
User: oracle

1. Connect to the Non-CDB database and shut it down, then open it in READ ONLY Mode.



2. Execute the below procedure which will generate a xml file that will be used to create the new PDB Database.
EXEC DBMS_PDB.DESCRIBE ('/u01/app/oracle/pdb2.xml');


3. Connect to target CDB database with a user having "CREATE PLUGGABLE DATABASE" privilege.


4. Set the PDB_FILE_NAME_CONVERT parameter to the new location where the datafiles for the pdb database will be created. 


5. Use the function DBMS_PDB.CHECK_PLUG_COMPATIBILITY to check the compatibility and  query the pdb_lug_in_violations view to view the violations. If there is any major issues, proceed accordingly

BEGIN
  IF DBMS_PDB.CHECK_PLUG_COMPATIBILITY('/u01/app/oracle/pdb2.xml','pdb2') THEN
    dbms_output.put_line('TRUE');
  ELSE
    dbms_output.put_line('FALSE');
  END IF;
END;
/

SELECT MESSAGE, ACTION FROM PDB_PLUG_IN_VIOLATIONS;


6. Plug in the database orcldb as pluggable database pdb2, Once the database is created, it will be in MOUNTED Mode.
   CREATE PLUGGABLE DATABASE pdb2 USING '/u01/app/oracle/pdb2.xml';


7. Create a service using netca for the new database(pdb2) and connect to it as sys user using this service. Run the noncdb_to_pdb.sql script before opening the pdb database. This script will delete all the unnecessary metadata from the system tablespace of the newly created PDB database.


8. Finally OPEN the pluggable database.
  



Thursday, August 15, 2013

Oracle 12c Pluggable Database Creation Method 1: using command line

How to Create Oracle Pluggable database
Method 1: Create new PDB from PDB$SEED pluggable database using command line.

Environment Details:
OS: Sun Solaris 5.10
Database Version: 12C
User: oracle
CDB Database Name: CDB1
Root Database Name: CDB$ROOT
Seed Database Name: PDB$SEED (Template database, always in READ ONLY Mode)
PDB Database Name: PDB1


1. Connect to the root database with the user having "CREATE PLUGGABLE DATABASE" system privilege. In this case we have used SYS user.


2. Issue the below to create the pluggable database in the container database.
CREATE PLUGGABLE DATABASE pdb1
ADMIN USER pdbadmin IDENTIFIED BY pdbadmin1 ROLES=(CONNECT)
FILE_NAME_CONVERT =  ('/u01/oradata/ora12c/pdbseed','/u01/oradata/ora12c/pdb1');


3. When the pluggable database is created it is in MOUNTED Mode, we need to OPEN the database to use it.
ALTER PLUGGABLE DATABASE pdb1 OPEN;



   



Monday, August 12, 2013

ORA-4031: unable to allocate 32 bytes of shared memory | Sun Solaris


Environment Details:
OS: Sun Solaris 5.10
Database Version: 11gR2
User: oracle

Issue: When we try to create a database using dbca in Sun Solaris Server we get an ORA Error 
ORA-00604: Error occurred at recursive SQL level 1 
ORA-4031: unable to allocate 32 bytes of shared memory("shared pool","select obj#,type#,ctime,mtim...,","SQLA","tmp")




This issue is related to the amount of privileged memory allocated for use to the Oracle user, if the memory required exceeds the privileged limit, oracle throws error

Solution: To resolve this use the following

Login as oracle user on the server and issue the command (id -p)

bash-3.00$ id -p
uid=555(oracle) gid=1000(oinstall) projid=100(user.oracle)

Login as root/sudo to the server and check the memory allocated to the user.oracle project

# prctl -n project.max-shm-memory -i project user.oracle
project: 100: user.oracle
NAME    PRIVILEGE       VALUE    FLAG   ACTION                       RECIPIENT
project.max-shm-memory
        privileged       2.0GB      -   deny                                 -
        system          16.0EB    max   deny                                 -

Increase the memory of by issuing the below command

# prctl -n project.max-shm-memory -r -v 4G -i project user.oracle

Verify the memory changes

# prctl -n project.max-shm-memory -i project user.oracle
project: 100: user.oracle
NAME    PRIVILEGE       VALUE    FLAG   ACTION                       RECIPIENT
project.max-shm-memory
        privileged       4.0GB      -   deny                                 -
        system          16.0EB    max   deny                                 -

Now try to create the database it will go through.

Wednesday, August 7, 2013

Oracle Quiesce Database


If we want only DBA transactions to be allowed in the database for certain important activities, we QUIESCE a database by issuing the command ALETR SYSTEM QUIESCE DATABASE. The advantage of this command is, it will not terminate the sessions of non DBA users and if there are any active transactions for any user those will continue to run until they are completed but once those transaction becomes inactive, it will appear that the sessions are hung and will not allow any transactions at that point of time, only the DBAs can run the transactions. Once the session is UNQUIESCE by issuing the command ALTER SYSTEM UNQUIESCE, the normal users will be able to do the transactions.

In the example below, we have logged into a server and verified that we are able to query from a user called test before quiescing , and when we quiesce database, the session seems to be hung and as soon as we unquiesce the database, the command gets executed successfully.



Querying from test(non DBA user) users schema, since we have not quiesce the database the query ran successfully.

Now we have quiesced the database and tried to run a query from test users, here the sessions seems to be hung for the test users query.

Now we unquiesced the database and the query completed successfully.
Note: Utmost care should be taken while using quiesce/unquiesce since it will prevent any other users transactions.