What is a Privilege? A privilege is the right to execute a particular type of SQL statement. It can allow a user to access database objects or execute stored programs that are owned by another user or to perform system level actions. There are two types of privileges: system privileges schema object privileges What are system privileges? The system privileges are not related to a certain object. They control the ability of a user to perform system level actions such as connecting to … [Read more...] about Oracle Database interview questions – Privileges And Roles
oracle
What are Oracle Data Guard Protection Modes (redo transport rules) and how to configure them
A Data Guard configuration always runs in one of three data protection modes (also called as redo transport rules): Maximum Protection Maximum Availability Maximum Performance (default mode of operation) All three modes provide a high degree of data protection, but they differ in terms of the effect that each has on the availability and performance of the primary database. Data Guard Protection modes The supported redo transport configurations for the 3 Data Guard Protection Modes … [Read more...] about What are Oracle Data Guard Protection Modes (redo transport rules) and how to configure them
How to install and configure Oracle ASMLIB on Native Linux multipath mapper devices
The post describes the process about how to setup ASM & ASMLIB disks using Mapper Multipath disks. The process involves : Installing and configuring the ASMLIB software Creating partitions on multipath devices using kpartx Creating ASM disks on new partitions updating the configuration file /etc/sysconfig/oracleasm Creating ASM disk groups 1. Install and configure ASMLIB software Install the ASMLIB (on each node for RAC environments) and configure it. ASMLib consists of the … [Read more...] about How to install and configure Oracle ASMLIB on Native Linux multipath mapper devices
How to trace asmcmd command on UNIX/Linux
Question : The asmcmd command execution is slow. How to trace the asmcmd command? Answer: You are seeing some errors or slow performance issue with command execution on asmcmd. And you wish to get more information on the command execution. The exception raised from asmcmd is not very descriptive and more of generic errors are reported for different issues. Below are some of the ways in which you can trace the asmcmd command to troubleshoot the performance issues with the execution of the … [Read more...] about How to trace asmcmd command on UNIX/Linux
CentOS / RHEL : Installing and Configuring ASMLib
Oracle ASM (Automated Storage Management) is a data volume manager for Oracle databases. ASMLib is an optional utility that can be used on Linux systems to manage Oracle ASM devices. ASM assists users in disk management by keeping track of storage devices dedicated to Oracle databases and allocating space on those devices according to the requests from Oracle database instances. ASMLib consists of the following components: An open source (GPL) kernel module package: kmod-oracleasm An open … [Read more...] about CentOS / RHEL : Installing and Configuring ASMLib
Oracle Database : script to create a “CREATE SYNONYM Script”
The following is a script that once run will generate another script that will include all the create synonym statements for all those in the database, both private and public. Pre-requisites 1. This script must be run by a user with the DBA role. The script Running this script will in turn create a script to build all the synonyms in the database. This created script, create_synonyms.sql, can be run by any user with the DBA role or with the 'CREATE ANY SYNONYM' and 'CREATE PUBLIC … [Read more...] about Oracle Database : script to create a “CREATE SYNONYM Script”
How to create password file for Database on 12c ASM diskgroup
The compatible.asm disk group attribute must be set to 12.1 or higher for the disk group where the password is to be placed. Steps 1. Create the password file using the following command. Go to the asmcmd command prompt and run : $ asmcmd ASMCMD> pwcreate --dbuniquename [dbuniquename] [file_path] [sys-password] Here, The --dbuniquename string option identifies the database unique name associated with the password file. The file_path value identifies the location where the password … [Read more...] about How to create password file for Database on 12c ASM diskgroup
How to Install and configure OSWatcher Black Box (OSWbb)
The Oracle OSWatcher Black Box (OSWbb) product is a collection of shell scripts intended to collect and archive operating system and network metrics to aid in diagnosing performance issues. OSWbb operates as a set of background processes on the server and gathers data on a regular basis, invoking such UNIX utilities as vmstat, netstat, iostat, top, and others. Beginning with release 4.0.0, OSWbb includes a built-in analyzer called OSWbba, which analyzes the data that OSWbb collects. It … [Read more...] about How to Install and configure OSWatcher Black Box (OSWbb)
How to Move a Datafile from Filesystem to ASM Using ASMCMD CP Command
Question : A datafile was wrongly added on file system instead of ASM diskgroup. How to move it to the ASM disk group again? Answer : 1) Make sure the datafile to be moved is OFFLINE before proceeding : SQL> alter system switch logfile; System altered. SQL> select file_name, file_id from dba_data_files; FILE_NAME FILE_ID --------------------- /u01/oracle/oradata/test1.dbf 6 SQL> alter database datafile 6 offline; Database altered. SQL> select file_name, file_id, online_status from … [Read more...] about How to Move a Datafile from Filesystem to ASM Using ASMCMD CP Command
How to backup and delete archivelogs older than # number of days
Question : How can one achieve the following while using RMAN to backup the archived logs? Back up all archived redo logs until specified time and delete them after backing up, and also back up all the other archived redo logs that were not backed up before - all this with a single RMAN command. Solution Backup archivelogs older than 10 days and delete them First let us look at the command to backup all archived logs older than 10 days and delete them: RMAN> run { allocate … [Read more...] about How to backup and delete archivelogs older than # number of days