Month: May 2014

reset sequence value

arcsdegeoLeave a comment

to reset your current sequence to value 1 follow the following steps:

we will assume the sequence name is SQ_CURR

1. Search for the current sequence value via the command:

SQL> select SQ_CURR.currval from dual;  

 2. Then change the sequence by adding the negative value of your previous result but subtract and addition one so for example if the value was 133389 you will increment it by -133388

SQL>  ALTER SEQUENCE SQ_CURR  INCREMENT by -133388; 

 

3. Then run the command that will reset the sequence:

SQL> SQ_CURR.NEXTVAL SELECT FROM DUAL; 

 

4. To restore the increment of the sequence:

SQL> ALTER SEQUENCE SQ_CURR  INCREMENT by 1;

 

hope this helps ….cheers!

😉

 

 

RESOURCE ROLE in ORACLE

arcsdegeoLeave a comment

the “resource” is a pre-defined oracle database system role that consists of the following permissions:

CREATE CLUSTER
CREATE INDEXTYPE
CREATE OPERATOR
CREATE PROCEDURE
CREATE SEQUENCE
CREATE TABLE
CREATE TRIGGER
CREATE TYPE

starting from Oracle 12cR1 this role doesn’t have “UNLIMITED TABLESPACE” system privilege, which is considered a security enhancement so the database user account won’t have any unlimited access to all tablespaces within the database. The DBA will be required to manually grant the specific tablespace quota to the user.

 

 

revoking a tablespace quota from user

arcsdegeoLeave a comment

to revoke a tablespace quota from an oracle database user, you can use the command as follows:

 

ALTER USER DB_USER QUOTA 0 ON TABLESPACE_NAME;

 

where

DB_USER is the oracle database user.

TABLESPACE_NAME is the tablespace name that you want to revoke quota from.

 

Hope This Helps !   🙂

 

how to find installed patches in Oracle Database 12c

arcsdegeoLeave a comment

In oracle database 12c release, you can use the package DBMS_QOPATCH to find information about patches currently applied in your database.

 

this package can only be executed by “sys” user account.

 

examples of the package sub-functions used:

DBMS_QOPATCH.GET_OPATCH_DATA (
patchnum IN VARCHAR2);
RETURN XMLYPE;

*.* returns information about Patch ID, patch creation time.

DBMS_QOPATCH.GET_OPATCH_INSTALL_INFO
RETURNS XMLTYPE;

*.* information about th patch and oracle inventory location.

DBMS_QOPATCH.GET_OPATCH_LSINVENTORY
RETURN XMLYPE;

*.* returns full patch inventory in XML format

 

Also, you can use the query:

SQL> select dbms_qopatch.get_opatch_lsinventory() from dual;

 

Reference: http://docs.oracle.com/cd/E16655_01/appdev.121/e17602/d_qopatch.htm#ARPLS74923

 

 

 

 

 

 

Oracle Database 12c Administrative System Accounts

arcsdegeoLeave a comment

in Oracle Database 12c, the following are the list of system administration accounts:

Image

 

and the following is the list of Operating System Accounts (OS Accounts) and its corresponding Oracle 12c System Admin Accounts:

 

Image

 

to view all accounts in your database and their status, use the following query:

SELECT
username
,account_status
,lock_date
,created
FROM dba_users
ORDER BY username;

 

you can also use this query, from root container in 12c:

SELECT
c.name
,u.username
,u.account_status
,u.lock_date
,u.created
FROM cdb_users u
,v$containers c
WHERE u.con_id = c.con_id
ORDER BY c.name, u.username;

 

 

Oracle Database 12c Multitenant Architecture – CDB & PDB

arcsdegeoLeave a comment

The New Oracle Database release named “12c” is based on Multitenant Architecture. Moreover, you have the option either to upgrade with this architecture with CDB (Container Database) and PDB (Pluggable Databases) OR you can you use the traditional Oracle Database Architecture.

In this article I will talk about the overview of the architecture.

The 12c CDB database will consists of the following major elements:

CDB$ROOT, which has the data dictionary views for the root and all pluggable databases.

PDB$SEED is a template database that a DBA can use to create other databases.

PDB is a pluggable database, which you can have zero,one, or more within a CDB. The maximum number of PDB in one CDB is “252 pluggable databases”. Each pluggable database has “CON_ID” which is a unique container identification.

High level architecture overview:

 Image

CDB level Architecture Image:

 Image

The Following Points will describe different 12c CDB architecture information:

  • Container Database(CDB$ROOT) will host all metadata for all PDB within CDB.

 

  • Each PDB will have the following tablespaces: SYSTEM, SYSAUX.  TEMP tablespace can be “optionally” created under a PDB, otherwise PDB will use the TEMP tablespace for the root container.

 

  • In 12c Character Set is set at CDB level, and will be applied to all associated PDB.

 

  • Using SYS to access root CDB, the DBA can stop/start CDB instance. This is not applicable if connecting to PDB.

 

  • All pluggable databases use a common UNDO tablespace under root CDB.

 

  • There is a centralized (common) CDB alert log, so all error/informative messages for each PDB are being written to one common alert log and set of trace files.

 

Final Remark: what I like about Multitenant Architecture from “Security” perspective is that applications will be deployed on database-level instead of a schema-level which gives you more “data isolation”, freedom of using public synonyms, provisioning, and portability.

In 11g if you use an excessive permission such as “SELECT ANY TABLE” the user can access the data from any schema within the database, which means other applications data. So when you deploy your application on database-level this is not applicable since all objects within the database is primarily owned by your application.

 

 

 

 

 

References: http://docs.oracle.com/cd/E16655_01/server.121/e17633/cdbovrvw.htm#CNCPT89340

Oracle Database Initialization File

arcsdegeoLeave a comment

Oracle initialization file existence is a “mandatory” pre-requisite before starting up the database, otherwise you won’t be able to startup the database.

The initialization file is a configuration file used for example to adjust memory parameters and control file locations.

There are 2 types of initialization files:

  • Server parameter file (spfile)
  • Init.ora text file

 

The first type (spfile) can be used if you require to change the contents of the file using sql statement (ALTER SYSTEM) command. Also, if you use spfile you can remotely start the database from your client machine.

The second type (init.ora) text file can be edited using OS text editor, and you can add hash comments while performing modifications. I personally prefer using this type of initialization file.

The location of initialization of file is under $ORACLE_HOME/dbs  directory

The init file would like the following:

spfile<SID>.ora       OR    spfile.ora     OR    int<SID>.ora

Where <SID> is the oracle database instance system id

Recommendation:

In 11g and 12c set the memory_target and memory_max_target initialization parameters, this setting will enable automatic memory management.