1) What are general considerations while upgrading from 11g to 12c?
While upgrading from Oracle database 11g to 12c the latest multi-tenant system it makes real good sense to determine the upgrade method to be made use of. When we choose to upgrade we can use any one of the following methods:
1) Upgrade using Database Upgrade Assistant
2) Upgrade using manual method
3) Upgrade using Datapump- Install oracle software and database, use transportable tablespace feature to upgrade database. If the database version is 220.127.116.11 the latest datapump feature can be used
2) How to deal with ORA-28000: the account is locked?
An user account should be unlocked and be in a valid state to log onto database. An user account can be locked due to one of the following reasons :
1) FAILED_LOGIN_ATTEMPTS – This profile parameter limit has been reached. In this case we need to wait until PASSWORD_LOCK_TIME. If it is immediate contact the database administrator (DBA)
2) Account has been locked due to security reasons – Contact DBA in this case
SQL> connect username/password
ORA-28000: the account is locked
Warning: You are no longer connected to ORACLE.
SQL> connect username/password
ORA-28000: the account is locked
SQL> connect sys/password as sysdba
SQL> alter user username identified by password account unlock;
SQL> connect username/password
SQL> show user
USER is “username”
3) What variables are essential and need to be properly set before creation of Oracle database?
Oracle datbaase stores data and can be created using Oracle supplied dbca utility (or) SQL*PLUS create database command. In either case it becomes essential to set the following variables
ORACLE_HOME – The location on which Oracle binaries are installed
ORACLE_SID – the system identifier
LD_LIBRARY_PATH – $ORACLE_HOME/lib
PATH – the location of oracle home,binary etc
4) What are oracle supplied scripts related to setting environment variables?
oraenv, oratab for ksh,bash,bourne shell. For csh,tcsh coraenv,coratab are used
5) Where is oraenv/coraenv stored?
These files are created as part of Oracle binary installation. They are stored onto /etc in Linux. In case of solaris they are created in /var/opt/oracle folder. Note that if they don’t exist we can create and update these folders and files
6) Which Oracle utility is used to detect corruption?
Oracle supplied utility dbv reports corruption when run against Oracle data files
7) what are commonly used Oracle supplied utilities?
Oracle SQL*PLUS, RMAN, Dataump, SQL*Loader,dbshut,dbstart to name a few
8) What data is stored in oratab/coratab file?
oracle isd:oracle home:autoamted startup Y|N
This information is being made use of by oraenv while setting environment variables
9) What best practices can be adopted during import approach?
Import using impdp in general is faster than traditional import. Adopt following steps to expedite process
1) Disable archivelog
2) Set the datfile size in accordance with source
3) Enable archivelog back
10) What is default location of initialization parameter file in Oracle?
Oracle stores the initialization parameter file inside oracle_home/dbs (or) oracle_homedatabase. In either case the startup command looks for this and if found missing will not be able to start oracle instance
11) What is the order in which initialization parameter file is searched for?
Oracle looks for initialization parameter file in following order
Oracle looks for files by names mentioned in above order. If it finds nothing, it throws an error
12) What if the initialization parameter file is stored in non-default location?
Start the instance using the following command
13) What is reason behind Linux Error: 28: No space left on device
This issue occurs when the mount point containing Oracle software, fills up. To look for space availability issue
df -h command
a quick fix would be to remove the old trace files from this location to save space
14) Oracle database connectivity issues and steps to take
Oracle database server hosts different clients and might be unavailable for various reasons
1) Oracle database server is down. Follow the steps in this scenario
14.1) Try to ping and see if remote server is available
14.2) If ping returns okie, use telnet command
telnet serverip portnumber
14.3) If both the above works fine, see if listener is up and running
14.4) If tnsping returns okie, look for Oracle database service availability
Log into server and look for processes – ps -ef in unix
In case of windows launch service manager, see OracleSIDService is up and running
14.5) Navigate to alert_sid.log and determine current status of database
14.6) Determine if the issue has something to do with space filling up in database folder
14.7) There are cases wherein look for logs in ORACLE_HOMEnetworkadminlog folder
if listener_logging is set off , then we dont see any logs here
15) Where do we store details on threshold metrics in oracle database?
SQL> desc dba_thresholds;
Name Null? Type
—————————————– ——– ———————-
16) What is the usage of dba_outstanding_alerts?
When a metric exceeds threshold an alert is generated
In Oracle database control an alert gets created when such trigger happens
17) What is Lost Updates Problem?
This problem is caused by a transaction trying to read a data while it is being updated by other transactions.Say transaction A is reading data from a table while it is being updated by transaction B, transaction B completes successfully and it is committed. If A has read the data before B is fully complete, it might end up with intermediate data.This results in lost update anomaly.Allowing transactions to read and update a table before the completion of another transaction causes the problem
18) Give details on Oracle Database Non-repeatable Reads Problem:-
Also known as fuzzy-read problem.When a transaction finds that it has read previously has been modified by some other transaction
Say we access a table’s data at a certain point in time and then we try to access the same data a little later, only to find that the values are different the second time
This inconsistent data during the same transaction causes a non repeatable-read problem
19) What is Phantom Read Problem Oracle Transaction Management?
Phantom problems are caused by the appearance of new data in between two database operations in a transaction.Say we’re reading from a table using SELECT statement
We re-execute the query after some time elapses and in meantime some user has inserted new data into the table.These extra rows are termed as “phanthoms” and this problem is referred to as phantom-read
20) What pl/sql is used for converting dictionary-managed to locally-managed tablespace in oracle database?
Oracle database has PL/SQL program that is used to convert dictionary-managed tablespaces to locally-managed tablespaces. Migration is simple and easy for all tablespaces except SYSTEM tablespace. In case of SYSTEM tablespace some extra steps are needed.
SQL> exec dbms_space_admin.tablespace_migrate_to_local(‘tablespacename’);
This is used to perform conversion and is most helpful when we migrate from earlier version of Oracle database to most recent version oracle database. It is to be noted that in recent versions of Oracle database all the tablespaces are locally managed.
21) How to fix ORA-00001: unique constraint (SYS.SYS_C003996) violated?
I created a table with PRIMARY KEY constraint on one of its column. A PRIMARY KEY is UNIQUE, NOT NULL. I tried inserting duplicate values into the PRIMARY KEY column and got the following error
SQL> create table dept(manager_id int primary key, dep_name varchar2(10));
SQL> insert into dept values(3,’marketing’);
1 row created.
SQL> insert into dept values(3,’IT’);
insert into dept values(3,’IT’)
ERROR at line 1:
ORA-00001: unique constraint (SYS.SYS_C003996) violated
22) What are Oracle Database undocumented initialization parameters?
Oracle Database undocumented initialization parameters are a set of parameters not documented by oracle corporation. These parameters should not be altered.If the system crash is due to making changes to undocumented parameters, we may not get support from oracle corporation.In other way undocumented initialization parameters are unsupported initialization parameters. We can find the list of undocumented parameters by using the following query :
SQL> select a.ksppinm parameter, a.ksppdesc description, b.ksppstvl sessionvalue, c.ksppstvl instancevalue from x$ksppi a, x$ksppcv b, x$ksppsv c where a.indx = b.indx and a.indx = c.indx and substr(a.ksppinm,1,1) = ‘_’ order by a.ksppinm;
23) What is Oracle Database Flashback Recovery Area in Single-instance and RAC databases?
Flashback recovery area is a disk based recovery area introduced in Oracle 10.1. This is called fast recovery area in recent versions. It can store files for backup and recovery operations. This can be configured in regular file system as well as ASM in both single instance and RAC environment. In a RAC environment, flashback recovery area must be shared between all instances. It can be located on an ASM, a raw volume, a CFS (clustered-file system) like ACFS, shared directory mounted through NFS for each instance etc
A single flashback recovery area can be shared by multiple databases. To use a shared area for multiple databases, each database must have a database name specified using DB_NAME or DB_UNIQUE_NAME initialization parameter
Within the flashback recovery area, disk space is automatically managed by Oracle.Older backup files can be automatically deleted when they have been superseded or archived to offline storage devices
Flashback recovery area can be used to store all Oracle recovery files – transient and permanent recovery files