Showing posts with label oracle. Show all posts
Showing posts with label oracle. Show all posts

Thursday, 4 June 2020

Script to check free space and used space in Oracle Database tablespaces

select a.tablespace_name,
       a.bytes_alloc/(1024*1024) "TOTAL ALLOC (MB)",
 a.physical_bytes/(1024*1024) "TOTAL PHYS ALLOC (MB)",
       nvl(b.tot_used,0)/(1024*1024) "USED (MB)",
       (nvl(b.tot_used,0)/a.bytes_alloc)*100 "% USED"
from ( select tablespace_name,
       sum(bytes) physical_bytes,
sum(decode(autoextensible,'NO',bytes,'YES',maxbytes)) bytes_alloc
       from dba_data_files
       group by tablespace_name ) a,
     ( select tablespace_name, sum(bytes) tot_used
       from dba_segments
group by tablespace_name ) b
where a.tablespace_name = b.tablespace_name (+)
--and   (nvl(b.tot_used,0)/a.bytes_alloc)*100 > 10
and   a.tablespace_name not in (select distinct tablespace_name from dba_temp_files)
and   a.tablespace_name not like 'UNDO%'
order by 1
 --order by 5

How to Enable Database Vault in Oracle database Version 11gR1?

Steps to enable Database Vault in Oracle Database 11gR1:

1. In the command prompt, use DVCA utility to enable Oracle Database Vault.


dvca -action enable 
  -oh /u01/app/oracle/product/11.1.0/db_1
  -service conn_alias 
  -instance orcl 
  -dbname orcl 
  -owner_account lbrown_dvowner 
  -logfile dvcalog.txt 

Enter SYS password: sys_password
Enter owner password: owner_password

Parameter Description:

-service is the name of the database specifier. The specifier can be a connect descriptor or net service name. 

-instance is the name of the database instance.

-dbname is the database name.

-sys_passwd is the SYS password. If you use a cleartext password on the command line, you must include the nodecrypt option. If you omit the password, DVCA prompts you for it. Preferably, omit the password and then enter it interactively when prompted.

-owner_account is the Oracle Database Vault Owner account name.

-owner_passwd is the Oracle Database Vault Owner account password. If you use a cleartext password on the command line, you must include the nodecrypt option. If you omit the password, DVCA prompts you for it. Preferably, omit the password and then enter it interactively when prompted.

-logfile is an optional flag to specify a log file name and location. You can enter an absolute path, or enter a path that is relative to the location of the $ORACLE_HOME/bin directory.

-silent is the option to run in command line mode. This option is required if you are not running DVCA in an xterm window.

-nodecrypt is the option to read plaintext passwords.

-lockout is the flag to use to disable SYSDBA operating system authentication.

2. Stop the database, Database Control console process, and listener.

Enable the Oracle Database Vault option as follows:
 The make command enables both Oracle Database Vault (dv_on) and Oracle Label Security (lbac_on).
 You must enable Oracle Label Security before you can use Database Vault.

cd $ORACLE_HOME/rdbms/lib
make -f dv_on lbac_on

relink all

3.Restart the database, Database Control console process, and listener.

4. Verify Oracle Database Vault and Oracle Label Security are enabled.



How to change default start Page in Oracle EBS

Application start page can changed for a Oracle EBS user in two ways.

1. Change settings in Preferences:

We can set the Application Start Page in Preferences. Click on Preferences and set the start page to preferred responsibility/page. After setting this preference, user will be taken to the above set page on successful login.

To reset the Responsibility Start Page, we can modify using same navigation.

2.  Change Profile option "Application Start Page"
Login as SYSADMIN user . Navigate to system adminstrator's reponsibility > Profile > System

QUery for Profile Option name 'Application Start Page" and User, then change or remove the value at User Level.

What are the features of BIGFILE Tablespace in Oracle Database?

Bigfile Tablespace is introduced from Oracle 10g.A bigfile tablespace is a tablespace with a single, but very large (up to 4G blocks) datafile.

The benefits of bigfile tablespaces are the following:

A bigfile tablespace with 8K blocks can contain a 32 terabyte datafile. A bigfile tablespace with 32K blocks can contain a 128 terabyte datafile. The maximum number of datafiles in an Oracle Database is limited (usually to 64K files). Therefore, bigfile tablespaces can significantly enhance the storage capacity of an Oracle Database.

Bigfile tablespaces can reduce the number of datafiles needed for a database. 
An additional benefit is that the DB_FILES initialization parameter and MAXDATAFILES parameter of the CREATE DATABASE and CREATE CONTROLFILE statements can be adjusted to reduce the amount of SGA space required for datafile information and the size of the control file.

Bigfile tablespaces simplify database management by providing datafile transparency. 
SQL syntax for the ALTER TABLESPACE statement lets you perform operations on tablespaces, 
rather than the underlying individual datafiles.

Bigfile tablespaces are intended to be used with Automatic Storage Management (ASM) or other logical volume managers that supports striping or RAID, and dynamically extensible logical volumes.

Avoid creating bigfile tablespaces on a system that does not support striping because of negative implications for parallel query execution and RMAN backup parallelization.

Creating a Bigfile Tablespace

To create a bigfile tablespace, specify the BIGFILE keyword of the CREATE TABLESPACE statement (CREATE BIGFILE TABLESPACE ...)

    DATAFILE '/u02/oracle/data/bigtbs01.dbf' SIZE 50G

Identifying a Bigfile Tablespace

The following views contain a BIGFILE column that identifies a tablespace as a bigfile tablespace:




Wednesday, 3 June 2020

How to enable ArchiveLog mode in Oracle Database RAC Environment?

Steps to enable Archivelog mode in RAC environment:

The following steps need to be taken to enable archive logging in a RAC database environment:

1. Shutdown immediate all database instances
$ srvctl stop database -d <db_unique_name>

2. Startup database in mount mode
$ srvctl start database -d <db_unique_name> -o mount

3. Enable archive logging
$ sqlplus / as sysdba
sql> alter database archivelog;
sql> exit;

4. Stop database
$ srvctl stop database -d <db_unique_name>

5.Start all database instances
$ srvctl start database -d <db_unique_name>

6.Verify archiving is enabled/disabled 
sql> archive log list;

How to mirror database Privileges of a Oracle database user to another database user?

Script for Mirroring Privileges of a user:

Scenario: A Database schema named "TEST2" is created and business wants to grant the privileges for the user same as already existing Database user "TEST1".

Run the below queries as SYS or any Privileged user to get the DDL commands in Oracle for TEST1 user.




 The output will be a sequence of grant commands related to privileges assigned to TEST1 user. Now replace the string TEST1 with new user name "TEST2" and run the commands.

 This will grant all privileges same as TEST1 user to TEST2 user in Oracle Database.

Change Oracle Database to ArchiveLog Mode

Steps to change the database to Archivelog Mode:

1. Login to the database server. Connect to sqlplus as sysdba

$sqlplus / as sysdba

2. Shutdown the database

SQL> shutdown immediate

3. Take a full database Backup (Cold Backup in this case)

4. Startup the database in mount stage

SQL> startup mount

5. Enable Archivelog mode

SQL> alter database archivelog;

6. Open the Database.

SQL> Alter database open;

7. Verify the changes.

SQL> archive log list;