Pages

Sunday, August 28, 2016

How to check NLS parameter value of Oracle Database.

How to check NLS parameter value of Oracle Database.


SQL> select * from nls_database_parameters;

PARAMETER                      VALUE
------------------------------ ----------------------------------------
NLS_LANGUAGE                   AMERICAN
NLS_TERRITORY                  AMERICA
NLS_CURRENCY                   $
NLS_ISO_CURRENCY               AMERICA
NLS_NUMERIC_CHARACTERS         .,
NLS_CHARACTERSET               AR8ISO8859P6
NLS_CALENDAR                   GREGORIAN
NLS_DATE_FORMAT                DD-MON-RR
NLS_DATE_LANGUAGE              AMERICAN
NLS_SORT                       BINARY
NLS_TIME_FORMAT                HH.MI.SSXFF AM

PARAMETER                      VALUE
------------------------------ ----------------------------------------
NLS_TIMESTAMP_FORMAT           DD-MON-RR HH.MI.SSXFF AM
NLS_NCHAR_CHARACTERSET         AL16UTF16
NLS_TIME_TZ_FORMAT             HH.MI.SSXFF AM TZH:TZM
NLS_TIMESTAMP_TZ_FORMAT        DD-MON-RR HH.MI.SSXFF AM TZH:TZM
NLS_DUAL_CURRENCY              $
NLS_COMP                       BINARY
NLS_LENGTH_SEMANTICS           BYTE
NLS_NCHAR_CONV_EXCP            FALSE
NLS_RDBMS_VERSION              11.1.0.7.0

20 rows selected.

Saturday, August 27, 2016

11g R2 RAC : Oracle Local Registry (OLR)

11g R2 RAC : Oracle Local Registry (OLR) 


In Oracle Clusterware 11g Release 2 an additional component related to the OCR called the Oracle Local Registry (OLR) is installed on each node in the cluster. The OLR is a local registry for node specific resources. THe OLR is not shared by other nodes in the cluster. It is installed and configured when Oracle clusterware is installed.

Purpose of OLR:

It is the very first file that is accessed to startup clusterware when OCR is stored on ASM. OCR should be accessible to find out the resources which need to be started on a node. If OCR is on ASM, it can’t be read until ASM (which itself is a resource for the node and this information is stored in OCR) is up. To resolve this problem, information about the resources which need to be started on a node is stored in an operating system  file which is called Oracle Local Registry or OLR. Since OLR is a file an operating system file, it can be accessed by various processes on the node for read/write irrespective of the status of the clusterware (up/down). Hence, when  a node joins the cluster,  OLR on that node is read, various resources ,including ASM  are started on the node  . Once ASM is up , OCR is accessible and is used henceforth to manage all the clusterware resources. If OLR is missing or corrupted, clusterware can’t be started on that node!

Where is OLR located?

The OLR file is located in the grid_home/cdata/<hostname>.olr . The location of OLR is stored in /etc/oracle/olr.loc.and used by OHASD .

What does OLR contain?

The OLR stores data about
ORA_CRS_HOME
localhost version
active version
GPnP details
OCR latest backup time and location
information about OCR daily, weekly backup location
node name etc.

This information stored in the OLR is needed by OHASD to start or join a cluster.
A quick  peek at the backup of the olr shows the resources that are being maintained.

root@host01 ~]# ocrconfig -local -manualbackup

host01     2013/01/18 01:20:27     /u01/app/11.2.0/grid/cdata/host01/backup_20130118_012027.olr

root@host01 ~]# strings /u01/app/11.2.0/grid/cdata/host01/backup_20130118_012027.olr |grep -v type |grep ora!

ora!drivers!acfs
ora!crsd
ora!asm
ora!evmd
ora!ctssd
ora!cssd
ora!cssdmonitor
ora!diskmon
ora!gpnpd
ora!gipcd
ora!mdnsd

OLR administration:

You can view the status of the OLR file on each node by using the ocrcheck command with the –local parameter as seen here:

#ocrcheck -local
ocrdump can be used to  dump the contents of the OLR to tthe text terminal:

#ocrdump -local -stdout
You can use the ocrconfig command to export and import the OLR as seen in these examples:

#ocrconfig -local -export <export file name >

#ocrconfig -local -import <file name>

And you can repair the OLR file should it become corrupted with the ocrconfig command as seen in this example:

#ocrconfig -local -repair olr <file name>

The OLR is backed up at the end of an installation or an upgrade. After that time, you can only manually back up the OLR. Automatic backups are not supported for the OLR.

To manually back up OLR:
# ocrconfig –local –manualbackup

To view the contents of the OLR backup file:
#ocrdump -local -backupfile olr_backup_file_name

To change the OLR backup location
#ocrconfig -local -backuploc new_olr_backup_path

To restore OLR:
# crsctl stop crs
# ocrconfig -local -restore file_name
# ocrcheck -local
# crsctl start crs
$ cluvfy comp olr

Thursday, August 25, 2016

what is oracle database ?


It is a very common question. Before starting Oracle Database architecture, you should know what exactly the database server is.
I have came across a lot of answers, and examples. Combining everything I am sharing this. Hope u will find it useful…

let’s imagine a scenario first. Consider YOURSELF as a person who has some special capability.
You can listen very fast, you can see anything very easily, you can interact with others very quickly.
Of course as a human being you have couple of hands, eyes , ears , legs and so on. And also you have got certain amount of MEMORY inside your BRAIN.
But also you have got a big PROBLEM. You CAN NOT remember those things for a long time.

So one of your well wishers give you some NOTE BOOKS to write whatever you listen or see.

NOW, if you relate above mentioned things with ORACLE. Then YOU are basically an Oracle INSTANCE, consists of some MEMORY (RAM) which is like your brain. and some BACKGROUND PROCESSES like your eyes, hands, ears etc, using which YOU can do something..
And those NOTE BOOKS in which you write everything.. basically those are DATAFILES.

So all together this Oracle Instance and Data files are known as ORACLE DATABASE SERVER.

Sunday, August 21, 2016

Gather Schema and Tables Statistics

Gather Schema and Tables Statistics



Gather Schema Statistics – Concurrent Program

Connect as System Administrator

Concurrent - Request – Run

Select - Gather Schema Statistics















Click OK button

Estimate Percent: Using any value larger than 50 will force a compute statistics to be gathered; any value less than 50 only provide estimated statistics. Computed statistics in some cases could provide a significant performance improvement for Application modules.

Click the Schedule button to schedule as per your company needs, As a general rule, schedule the Gather Schema Statistics concurrent program to run once a week, during off hours, for your entire database


Gather Table Statistics Concurrent Program

If you have volatile tables that are updated, inserted into or deleted from frequently, then you should consider running Gather Table Statistics for those tables more frequently, perhaps nightly during off hours. In the following figure, we’ve chosen a particular table, FND_CONCURRENT_REQUESTS, and selected 99 for the percent to analyze to ensure that the table is analyzed using compute, rather than estimate.


Note- Using these two Concurrent Programs also generates statistics on the associated indexes.









Thursday, August 18, 2016

How to Clear Oracle Application Cache from front end without bounce the application in Oracle Applications.

How to Clear Oracle Application Cache from front end without bounce the application in Oracle Applications.


Navigation:- Functional Administrator > Home >
It will open Webpage

Click on tab Core Services

Click on sub tab Caching Framework

Then click on Left side Menu Global Configuration

Click on Clear All Cache

Click Yes on confirmation.

Wednesday, August 10, 2016

Suspending MMON slave action kewrmafsa_ for 82800 seconds

Suspending MMON slave action kewrmafsa_ for 82800 seconds


One of our database environment we noticed that AWR report is not available for particular time interval. In further investigating we found one MMON related error in the alert log.

Error:

Suspending MMON slave action kewrmapsa_ for 82800 seconds

Cause:

While MMON activity is important in maintaining information in the database, if the cpu utilization and database activities  exceeds cpu or run time policies, then MMON activity can be temporarily suspended. This is done so that more important activity can continue.  The "Suspending MMON ... for 82800 seconds" warning messages indicate that you should look at the overall performance of your system to check if there is anything that could be causing MMON activities to exceed their resource allocation such that they are suspended.

Solution:

Increase the Server configuration add RAM and more CPUs.

Tuesday, August 9, 2016

ORA-12012: error on auto execute of job "SYS"."BSLN_MAINTAIN_STATS_JOB"

ORA-12012: error on auto execute of job "SYS"."BSLN_MAINTAIN_STATS_JOB"

ORA-06550: line 1, column 807:

PLS-00201: identifier 'DBSNMP.BSLN_INTERNAL' must be declared


Cause:

You might get this error when database is created using template from old database or by cloning from another database.  Therefore, table DBSNMP.BSLN_BASELINES contains inconsistent information that is causing the job to fail.



Solution:

If you delete the inconsistent rows from table DBSNMP.BSLN_BASELINES, it will fix the problem but you can fix this easiest way by following the below guidelines.

SQL> select log_date,status from dba_scheduler_job_run_details where job_name='BSLN_MAINTAIN_STATS_JOB';

LOG_DATE                                                                    STATUS

--------------------------------------------------------------------------- ------------------------------

13-AUG-14 09.45.36.831010 AM +02:00                                         FAILED

17-AUG-14 09.00.00.980275 AM +02:00                                         FAILED


SQL> sqlplus / as sysdba

-- Drop the DBSNMP user by executing catnsnmp.sql script.

SQL> @$ORACLE_HOME/rdbms/admin/catnsnmp.sql

-- Create the DBSNMP user by executing catsnmp.sql

SQL> @$ORACLE_HOME/rdbms/admin/catsnmp.sql

-- Re-execute the job

SQL> exec dbms_scheduler.run_job('BSLN_MAINTAIN_STATS_JOB',false);

PL/SQL procedure successfully completed.

SQL> select log_date,status from dba_scheduler_job_run_details where job_name='BSLN_MAINTAIN_STATS_JOB';

LOG_DATE                                                                    STATUS

--------------------------------------------------------------------------- ------------------------------

13-AUG-14 09.45.36.831010 AM +02:00                                         FAILED

17-AUG-14 09.00.00.980275 AM +02:00                                         FAILED

20-AUG-14 02.50.36.799841 PM +02:00                                         SUCCEEDED

Reference metalink Doc ID 1413756.1

Wednesday, August 3, 2016

ORA-609 : opiodr aborting process unknown ospid (8327_47148946930848)

ORA-609 : opiodr aborting process unknown ospid (8327_47148946930848)

As a general error, the ORA-609 error indicates that a client connection failed to complete.  This can be an ORA-609 from an abort or killing an Oracle session.

To diagnose any error, you start by using the OERR UTILITY to display the ORA-609 error:

Example :

bash-3.2$ oerr ora 609
00609, 00000, "could not attach to incoming connection"
// *Cause:  Oracle process could not answer incoming connection
// *Action: If the situation described in the next error on the stack
// can be corrected, do so; otherwise contact Oracle Support.

Cause:

The ORA-609 error is thrown when a client connection of any kind failed to complete or aborted the connection
process before the server process was completely spawned.
Beginning with 10gR2, a default value for inbound connect timeout has been set at 60 seconds.

This is also triggered, when a DB session is killed/aborted manually from the OS prompt.

Solution:

Increase the values for INBOUND_CONNECT_TIMEOUT at both listener and server side sqlnet.ora file as a preventive measure.
If the problem  is due to connection timeouts,an increase in the following parameters should eliminate or reduce the occurrence of the ORA-609s.
Sqlnet.ora: SQLNET.INBOUND_CONNECT_TIMEOUT=180
Listener.ora: INBOUND_CONNECT_TIMEOUT_listener_name=120

Reference metalink Doc ID 1121357.1

Wednesday, July 27, 2016

Forms Process (FRMWEB) Consumes 100% of CPU in Oracle Applications

Forms Process (FRMWEB) Consumes 100% of CPU in Oracle Applications 

Cause:

The root cause of the issue is that returning rows from LOVs in core forms causes the forms process to grow up into memory depending on the number of rows returned.

When an end user login to forms and start working with LOV within core forms sometimes and according to the search criteria that the user will provide to filter the results in LOV, it may fetch huge numbers of records in which causes the frmweb process to grow very large, and in extreme cases this can even lock up the current process or even the whole machine.

So when executing a LOV query, every row is fetched into memory on the middle tier, the frmweb process can get extremely large, and the larger it gets the more likely it is to start paging.
Eventually it starts consuming excessive CPU just paging the process in and out of memory, which is probably what you can see here in this case as the amount of memory consumed when the LOV records are fetched into memory obviously depends on the amount of data in each record.

Please be aware:
This solution has been identifed for a Forms Configuration using the SOCKET mode, which is not the default mode in EBS R12. You can use the solution also for the SERVLET mode - in this case you have to add the FORMS_RECORD_GROUP_MAX to the default.env file.

Solution:

To implement the solution, please execute the following steps:

1. Stop all services on the middle tier.

2. Set following forms environment variables:

FORMS_RECORD_GROUP_MAX to 10000 or if that proves too restrictive, increase it to 20000 or 30000.
FORMS_CATCHTERM=0

In order to set the above forms variables so next time autoconfig run does not override those values, do the following steps.

1- For Forms Variable "FORMS_CATCHTERM" the context vairable name is: "s_forms_catchterm" and you can update the context file located in ($INST_TOP/appl/admin/)

2- For other forms variable "FORMS_RECORD_GROUP_MAX" there is no variable defined in Autoconfig for that one and have to customize the autoconfig for the forms variables to set that environment as following:

a) Go to the autoconfig Template folder:
$cd $AD_TOP/admin/template

b) Create new directory named (custom)
$ mkdir custom

c) Make sure that new directory has same file permissions as ($AD_TOP/admin/template)

d) Copy the following autoconfig template to the new custom directory:
$cp $AD_TOP/admin/template/APPLSYS_ux.env $AD_TOP/admin/template/custom/APPLSYS_ux.env

e) Edit the file copied file under custom directory and add the following 2 lines at the end of section:

####################################
# Oracle Forms environment variables
####################################

FORMS_RECORD_GROUP_MAX=10000
export FORMS_RECORD_GROUP_MAX

f) Save and exit from the file.

g) Next time autoconfig run, it will read the custom directory and check for any customizations there.

3. Run Autoconfig on the middle tier and make sure it is completed successfully.

4. Startup all services.

5. Monitor the forms process to see its CPU usage, and you will see that form process usage is reduced and not causing any more CPU consumption up to 100% as before.

6. Migrate the solution as appropriate to other environments.

Reference metalink Doc ID 1382442.1

Sunday, July 10, 2016

Scripts to check Long Running Sessions

Scripts to check Long Running Sessions

Summary:

This one shows SQL that is currently "ACTIVE"

select S.USERNAME, s.sid, s.osuser, t.sql_id, sql_text from v$sqltext_with_newlines t,V$SESSION s where t.address =s.sql_address and t.hash_value = s.sql_hash_value and s.status = 'ACTIVE'
and s.username <> 'SYSTEM' order by s.sid,t.piece;

Saturday, July 9, 2016

RMAN – crosscheck archivelog – validation failed for archived log

RMAN – crosscheck archivelog – validation failed for archived log 

Error:

name=/u01/oracle/archives/LOG0000007225_0705278833_0001.ARC RECID=7216 STAMP=896284282
validation failed for archived log

Cause:

Cause of this message is because archive log file destination was changed or someone deleted/moved the archive log files manually using OS commands.
Use “DELETE EXPIRED ARCHIVELOG ALL” will alleviate the VALIDATION FAILED messages.

Solution:

RMAN> crosscheck archivelog all;
RMAN> delete expired archivelog all;

(or)

run {
allocate channel d1 device type disk;
crosscheck archivelog all;
delete noprompt expired archivelog all;
release channel d1;
}

EXIT;

Monday, July 4, 2016

Start / Stop Mobile Application Services in Oracle Apps R12

Start / Stop Mobile Application Services in Oracle Apps R12

Login as applmgr user :

Go to $ADMIN_SCRIPTS_HOME

Stop Mobile Application Services :

./mwactl.sh -login apps/apps_password stop_force
./mwactlwrpr.sh stop apps/apps_password

Start Mobile Application Services :

./mwactl.sh -login apps/apps_password start
./mwactlwrpr.sh start apps/apps_password

Check Mobile Services are up and Runing:

ps -ef | grep mwa

Find port Number of Mobile Services :

grep mwa $CONTEXT_FILE

Connect to Mobile Services :

telnet hostname.domainname portnumber(mobile application service port_number)

Troubleshooting Workflow Notification Mailer Issues

Troubleshooting Workflow Notification Mailer Issues

Find Workflow Notification Mailer is up and Running?

SELECT component_name, component_status
FROM fnd_svc_components
WHERE component_type = 'WF_MAILER';

Workflow log’s: FNDCPGSC*.txt under $APPLCSF/$APPLOG directory

Find the Failed One’s?

Select NOTIFICATION_ID, MESSAGE_TYPE, MESSAGE_NAME, STATUS, MAIL_STATUS, FROM_USER, TO_USER from wf_notifications where MAIL_STATUS='FAILED';

Check pending e-mail notification that was pending for process.

Sql> SELECT COUNT(*), message_name FROM wf_notifications
WHERE STATUS='OPEN'
AND mail_status = 'MAIL'
GROUP BY message_name;

Sql> SELECT * FROM wf_notifications
WHERE STATUS='OPEN'
AND mail_status = 'SENT'
ORDER BY begin_date DESC


Check the Workflow notification has been sent or not?

select mail_status, status from wf_notifications where notification_id=<notification_id>

--If mail_status is MAIL, it means the email delivery is pending for workflow mailer to send the notification
--If mail_status is SENT, its means mailer has sent email
--If mail_status is Null & status is OPEN, its means that no need to send email as notification preference of user is "Don't send email"
--Notification preference of user can be set by user by logging in application + click on preference + the notification preference

1. Verify whether the message is processed in WF_DEFERRED queue

select * from applsys.aq$wf_deferred a where a.user_data.getEventKey()= '<nid>'
<nid> - notification id

2. If the message is processed successfully message will be enqueued to WF_NOTIFICATION_OUT queue, if it errored out it will be enqueued to WF_ERROR queue

select wf.user_data.event_name Event_Name, wf.user_data.event_key Event_Key,
wf.user_data.error_stack Error_Stack, wf.user_data.error_message Error_Msg
from wf_error wf where wf.user_data.event_key = '<nid'>
To check what all mails have went and which all failed ?

Select from_user,to_user,notification_id, status, mail_status, begin_date
from WF_NOTIFICATIONS where status = 'OPEN';

Select from_user, to_user, notification_id, status, mail_status,begin_date,USER_KEY,ITEM_KEY,MESSAGE_TYPE,MESSAGE_NAME begin_date
from WF_NOTIFICATIONS where status = 'OPEN';

Users complain that notifications are stuck ?

Use the following query to check to see whatever the users are saying is correct

SQL> select message_type, count(1) from wf_notifications
where status='OPEN' and mail_status='MAIL' group by message_type;

E.g o/p of query -

MESSAGE_Type      COUNT(1)
--------        ----------
POAPPRV                     11           --- 11 mails of Po Approval not sent ---
INVTROAP                    12
REQAPPRV                  9
WFERROR                     45             --- 45 mails have error


If Mail not received by User ?

select Name,DISPLAY_NAME,EMAIL_ADDRESS,NOTIFICATION_PREFERENCE,STATUS
from wf_users where DISPLAY_NAME=’xxx,yyy’ ;

Status – Active
Notification_preference-> Mailtext
Email Address should not be null

Notification not sent waiting to be mailed ?

SQL> select notification_id, status, mail_status, begin_date from WF_NOTIFICATIONS
where status = ‘OPEN’ and mail_status = ‘MAIL’;
To debug the notification id ?

$FND_TOP/sql
run wfmlrdbg.sql

Concurrent Programs with trace enabled

Concurrent Programs with trace enabled

Summary:

Shows which programs are defined with Trace enabled.

SELECT A.CONCURRENT_PROGRAM_NAME "Program Name",
SUBSTR(A.USER_CONCURRENT_PROGRAM_NAME,1,40) "User Program Name",
SUBSTR(B.USER_NAME,1,15) "Last Updated By",
SUBSTR(B.DESCRIPTION,1,25) DESCRIPTION
FROM APPS.FND_CONCURRENT_PROGRAMS_VL A, APPLSYS.FND_USER B
WHERE A.ENABLE_TRACE='Y'
AND A.LAST_UPDATED_BY=B.USER_ID;

How to enable trace for Concurrent Requests

How to enable trace for Concurrent Requests

It is very common to enable trace for concurrent requests, the main reason for this is to troubleshooting performance problems. Here is a simple guide how to enable oracle session tracing for the request.

1. Login as sysadmin
2. Go to -> System Administrator -> Concurrent : Program -> Define and Press F11
3. Enter the Concurrent Program Name (Eg. Journal Import) -> Press Ctrl + F11
4. Enable the check box "Enable Trace" above "Copy To" button. Click on Save and close the window.
5. Select Requests -> Run->Submit the concurrent request!
6. The trace file will be created in the Database Server under the location of "udump"
sql>select value from v$parameter where name like '%user_dump_dest%'; 

HP-UX Server & Unix Shutdown Command.

HP-UX Server & Unix Shutdown Command.

To shut down HP-UX for power-off, you can do any of the following:

# init 0 -- (Unix or Red hat linux )
# shutdown -h -y now

To shut down and reboot HP-UX:
# init 6 -- (Unix or Red hat linux )
# reboot
# shutdown -r -y now

To shut down HP-UX to single-user mode:
# init S
# shutdown -y now
# shutdown 0

The -h option to the shutdown command halts the system completely but
will prompt you for a message to issue users. The -y option completes
the shutdown without asking you any of the questions it would normally ask.

Thursday, June 30, 2016

How to find if a Sun Solaris OS is 32-Bit or 64-Bit

How to find if a Sun Solaris OS is 32-Bit or 64-Bit 

Type "isainfo -v" at the OS prompt.

If the output shows 2 entries, then its 64bit.

$ isainfo -v
64-bit sparcv9 applications
32-bit sparc applications

Else if it shows only one entry then its 32 bit.

$ isainfo -v
32-bit sparc applications

Find Out Concurrent Users In A Instance

Find Out Concurrent Users In A Instance

Calculate number of concurrent users of an existing instance.
The view v$license keeps track of concurrent sessions and users.

SQL> desc v$license
Name Null? Type
----------------------------------------- -------- ----------------
SESSIONS_MAX NUMBER
SESSIONS_WARNING NUMBER
SESSIONS_CURRENT NUMBER
SESSIONS_HIGHWATER NUMBER
USERS_MAX NUMBER
CPU_COUNT_CURRENT NUMBER
CPU_CORE_COUNT_CURRENT NUMBER
CPU_SOCKET_COUNT_CURRENT NUMBER
CPU_COUNT_HIGHWATER NUMBER
CPU_CORE_COUNT_HIGHWATER NUMBER
CPU_SOCKET_COUNT_HIGHWATER NUMBER

select sessions_current from v$license;

The above query will give you the number of concurrent users right now.

You can write a small job which will capture this information every hour for a week. Once you have this data, you can take an average of this data to get the number of concurrent users.

select SESSIONS_CURRENT,SESSIONS_HIGHWATER,CPU_COUNT_CURRENT,CPU_COUNT_HIGHWATER from v$license;

Sunday, June 26, 2016

RAC Interview Questions

RAC Interview Questions


What is cache fusion?

In a RAC environment, it is the combining of data blocks, which are shipped across the interconnect from remote database caches (SGA) to the local node, in order to fulfill the requirements for a transaction (DML, Query of Data Dictionary).

What is split brain?

When database nodes in a cluster are unable to communicate with each other, they may continue to process and modify the data blocks independently. If the
same block is modified by more than one instance, synchronization/locking of the data blocks does not take place and blocks may be overwritten by others in the cluster. This state is called split brain.


What is the difference between Crash recovery and Instance recovery?

When an instance crashes in a single node database on startup a crash recovery takes place. In a RAC enviornment the same recovery for an instance is performed by the surviving nodes called Instance recovery.

What is the interconnect used for?

It is a private network which is used to ship data blocks from one instance to another for cache fusion. The physical data blocks as well as data dictionary blocks are shared across this interconnect.


How do you determine what protocol is being used for Interconnect traffic?

One of the ways is to look at the database alert log for the time period when the database was started up.

What methods are available to keep the time synchronized on all nodes in the cluster?

Either the Network Time Protocol(NTP) can be configured or in 11gr2, Cluster Time Synchronization Service (CTSS) can be used.


What files components in RAC must reside on shared storage?

Spfiles, ControlFiles, Datafiles and Redolog files should be created on shared storage.

Where does the Clusterware write when there is a network or Storage missed heartbeat?

The network ping failure is written in $CRS_HOME/log

How do you find out what OCR backups are available?

The ocrconfig -showbackup can be run to find out the automatic and manually run backups.

If your OCR is corrupted what options do have to resolve this?

You can use either the logical or the physical OCR backup copy to restore the Repository.

How do you find out what object has its blocks being shipped across the instance the most?

You can use the dba_hist_seg_stats.


What is a VIP in RAC use for?

The VIP is an alternate Virtual IP address assigned to each node in a cluster. During a node failure the VIP of the failed node moves to the surviving node and relays to the application that the node has gone down. Without VIP, the application will wait for TCP timeout and then find out that the session is no longer live due to the failure.


How do we know which database instances are part of a RAC cluster?

You can query the V$ACTIVE_INSTANCES view to determine the member instances of the RAC cluster.


What is OCLUMON used for in a cluster environment?

The Cluster Health Monitor (CHM) stores operating system metrics in the CHM repository for all nodes in a RAC cluster. It stores information on CPU, memory, process, network and other OS data, This information can later be retrieved and used to troubleshoot and identify any cluster related issues. It is a default component of the 11gr2 grid install. The data is stored in the master repository and replicated to a standby repository on a different node.


What would be the possible performance impact in a cluster if a less powerful node (e.g. slower CPU’s) is added to the cluster?

All processing will show down to the CPU speed of the slowest server.


What is the purpose of OLR?

Oracle Local repository contains information that allows the cluster processes to be started up with the OCR being in the ASM storage ssytem. Since the ASM file system is unavailable until the Grid processes are started up a local copy of the contents of the OCR is required which is stored in the OLR.


What is the default memory allocation for ASM?

In 10g the default SGA size is 1G in 11g it is set to 256M and in 12c ASM it is set back to 1G.


How do you backup ASM Metadata?

You can use md_backup to restore the ASM diskgroup configuration in-case of ASM diskgroup storage loss.


What files can be stored in the ASM diskgroup?

In 11g the following files can be stored in ASM diskgroups.

Datafiles
Redo logfiles
Spfiles
In 12c the files below can also new be stored in the ASM Diskgroup

Password file
What it the ASM POWER_LIMIT?

This is the parameter which controls the number of Allocation units the ASM instance will try to rebalance at any given time. In ASM versions less than 11.2.0.3 the default value is 11 however it has been changed to unlimited in later versions.


What is a rolling upgrade?

A patch is considered a rolling if it is can be applied to the cluster binaries without having to shutting down the database in a RAC environment. All nodes in the cluster are patched in a rolling manner, one by one, with only the node which is being patched unavailable while all other instance open.


What are some of the RAC specific parameters?

Some of the RAC parameters are:

CLUSTER_DATABASE
CLUSTER_DATABASE_INSTANCE
INSTANCE_TYPE (RDBMS or ASM)
ACTIVE_INSTANCE_COUNT
UNDO_MANAGEMENT

What is the future of the Oracle Grid?
The Grid software is becoming more and more capable of not just supporting HA for Oracle Databases but also other applications including Oracle’s applications. With 12c there are more features and functionality built-in and it is easier to deploy these pre-built solutions, available for common Oracle applications.


What components of the Grid should I back up?

The backups should include OLR, OCR and ASM Metadata.

Is there an easy way to verify the inventory for all remote nodes
You can run the opatch lsinventory -all_nodes command from a single node to look at the inventory details for all nodes in the cluster.

Exadata Interview Questions

Exadata Interview Questions

What environment is a good fit for Exadata?

Exadata was originally designed for the warehouse environment. Later it was enhanced for use in the OLTP databases as well.

What are the advantages of Exadata?

The Exadata cluster allows for consistent performance while allowing for increased throughput. As load increases on the cluster the performance remains consistent by utilizing inter-instance and intra-instance parallelism.
It should not be expected that just moving to Exadata will improve performance. In most cases it will especially if the current database host is overloaded.

What is the secret behind Exadata’s higher throughput?

Exadata ships less data through the pipes between the storage and the database nodes and other nodes in the RAC cluster.

Also it’s ability to do massive parallelism by running parallel processes across all the nodes in the cluster provides it much higher level of throughput.

It also has much bigger pipes in the cluster using Infiniband interconnect for inter-instance data block transfers as high as 5X of fiberchannel networks.

What are the different Exadata configurations?

The Exadata Appliance configuration comes as a Full Rack, Half Rack, Quarter Rack or 1/8th rack.

The Full Rack X2-2 has 6 CPU’s per node with Intel Xeon 5670 processors and a total of 8 Database Server nodes. These servers have 96GB of memory on each node. A total of 14 Storage server cells communicate with the storage and push the requested data from the storage to the compute nodes.

The Half Rack has exactly half the capacity. It has 6 CPU’s per node with core Intel Xeon 5670 processors and a total of 4 Database Server nodes. It has 96GB of memory per database server node with a total of 7 Storage server cells.

The Exadata is also available in the 1/8th Rack configuration.

What are the key Hardware components?

DB Server
Storage Server Cells
High Speed Infiniband Switch
Cisco Switch

What are the Key Software Features?

Smart Scan,
Smart Flash Cache
Storage Index
Exadata Hybrid Columnar Compression (EHCC)
IORM (I/O Resource Manager)

What is a Cell and Grid Disk?

Cell and Grid Disk are a logical component of the physical Exadata storage. A cell or Exadata Storage server cell is a combination of Disk Drives put together to store user data. Each Cell Disk corresponds to a LUN (Logical Unit) which has been formatted by the Exadata Storage Server Software. Typically, each cell has 12 disk drives mapped to it.

Grid Disks are created on top of Cell Disks and are presented to Oracle ASM as ASM disks. Space is allocated in chunks from the outer tracks of the Cell disk and moving inwards. One can have multiple Grid Disks per Cell disk.

What is IORM?

IORM stands for I/O Resource Manager.

It manages the I/O demand based on the configuration, with the amount of resources available. It ensures that none of the I/O cells become oversubscribed with the I/O requests. This is achieved by managing the incoming requests at a consumer group level.

Using IORM, you can divide the I/O bandwidth between multiple databases.

To implement IORM resource groups, consumers and plans need to be created first.


What is hybrid columnar compression?

Hybrid Columnar compression, also called HCC, is a feature of Exadata which is used for compressing data at column level for a table.

It creates compression data units which consist of logical grouping of columns values typically having several data blocks in it. Each data block has data from columns for multiple rows.

This logarithm has the potential to reduce the storage used by the data and reduce disk I/O enhancing performance for the queries.

The different types of HCC compression include:

Query Low
Query High
Archive High
Archive Low

What is Flash cache?

Four 96G PCIe flash memory cards are present on each Exadata Storage Server cell which provide very fast access to the data stored on it.

This is further achieved by also provides mechanism to reduces data access latency by retrieving data from memory rather than having to access data from disk. A total flash storage of 384GB per cell is available on the Exadata appliance.


What is Smart Scan?

It is a feature of the Exadata Software which enhances the database performance many times over. It processes queries in an intelligent way, retrieving specific rows rather than the complete blocks.

It applies filtering criteria at the storage level based on the selection criteria specified in the query.

It also performs column projection which is a process of sending only required columns for the query back to the database host/instance.


What are the Parallelism instance parameter used in Exadata?

The parameter PARALLEL_FORCE_LOCAL can be specified at the session level for a particular job.


How do you Test performance of Exadata?

You can use the “calibrate” commands at the cellcli command line.


What are the ways to migrate onto Exadata?

Depending on the downtime allowed there are several options:

Oracle DataGuard
Traditional Export/Import
Tablespace transportation
Goldengate Replication after a data restore onto Exadata.


What types of operations does Exadata “offload”?

Some of the operations that are offloaded from the database host to the cell servers are:

Predicate filtering
Column project filtering
Join processing
Backups


What is cellcli?

This is the command line utility used to managed the cell storage.


How do you create obtain info on the Celldisks?

At the cellcli command line you can issue the “list celldisk” command.


How would you create a grid disk?

At the cellcli command you would need to issue the “create grididsk all ..” command.


What are the cellinit.ora and the cellip.ora files used for?

These files have the hostnames and the ip address of all the nodes in the cluster. They are used to run commands on remote database and cellserver nodes from a local host.

Example:
cat /etc/oracle/cell/network-config/cellinit.ora
ipaddress1=192.168.47.21/24

$ cat /etc/oracle/cell/network-config/cellip.ora
cell=”192.168.47.21:5042″
cell=”192.168.47.22:5042″
cell=”192.168.47.23:5042″


What operating systems does Exadata support?

Exadata has traditionally run Oracle Linux OS. Recently, Solaris has also been made available on this engineered system.

To prepare for your Oracle DBA interview here are some additional questions focusing on other database areas.

Concurrent Processing - No Concurrent Manager is Defined to Process This Request on Shutdown of Managers

Concurrent Processing - No Concurrent Manager is Defined to Process This Request on Shutdown of Managers

Error:

When shutting down the concurrent manager with adcmctl.cmd, the Abort Concurrent Manager request goes into Pending Standby status.  Checking the request details for the Abort Concurrent Manager request, shows a warning message as detailed below:

"No concurrent manager is defined to process this request"

Cause:

The issue was caused as time given for all FNDLIBR processes to shutdown gracefully was insufficient.

The Abort Concurrent Manager request completes successfully after a couple of minutes.
When the request details were checked immediately after adcmctl.cmd was executed, the warning
message 'No concurrent manager is defined to process this request' did appear and request was in Pending
Standby status.

When checking the request details again, it showed that the request completed Normal after waiting a few more minutes to view the details.

Solution:

To implement the solution, please execute the following steps:

1. Stop managers (adcmctl.cmd)

2. Open Windows Task Manager to view processes

3. Open View Concurrent Requests form and refresh screen often and wait for
the Abort Concurrent Manager request to "Complete" "Normal".

4. Refresh the Task Manager to verify FNDLIBR and all other concurrent manager
processes are gone.

5. For routine maintenance, use Note.134007.1 CMCLEAN.SQL - Non Destructive Script to Clean Concurrent Manager Tables to clean the tables while the managers are down and then restart managers.

6.  Check managers for requests in Pending status via View Concurrent Requests.

7.  If after cmclean.sql, pending requests remain, the requests must be manually cancelled.  This does not  require shutting down of the managers.  Perform the following in SQL*Plus as the APPS user:

    UPDATE  fnd_concurrent_requests
    SET     phase_code = 'C', status_code = 'E'
    WHERE request_id = '<request_id>';
    COMMIT;

8.  After manually killing request, the job(s) go to Completed Error status and managers should start gracefully without any pending requests.

Concurrent manager shutdown can take a few minutes to complete, wait a few minutes and confirm that it shuts down.  Concurrent Processing development team have confirmed that the concurrent manager does not terminate OS/Database processes, this must be done through some other mechanism such as DCD (Dead Connection Detection via Sqlnet) or some OS method.

Reference metalink Doc ID 342932.1

Thursday, June 23, 2016

Goldengate Interview Questions

Goldengate Interview Questions

What type of Topology does Goldengate support?

GoldenGate supports the following topologies.

Unidirectional
Bidirectional
Peer-to-peer
Broadcast
Consolidation
Cascasding

What are the main components of the Goldengate replication?

The replication configuration consists of the following processes.

Manager
Extract
Pump
Replicate

What database does GoldenGate support for replication?

Oracle Database
TimesTen
MySQL
IBM DB2
Microsoft SQL Server
Informix
Teradata
Sybase
Enscribe
SQL/MX

What transaction types does Goldengate support for Replication?

Goldengate supports both DML and DDL Replication from the source to target.

What are the supplemental logging pre-requisites?

The following supplemental logging is required.

Database supplemental logging
Object level logging

Why is Supplemental logging required for Replication?

When a transaction is committed on the source database, only new data is written to the Redo log. However for Oracle to apply these transactions on the destination database, the before image key values are required to identify the effected rows. This data is also placed in the trail file and used to identify the rows on the destination, using the key value the transactions are executed against them.

List important considerations for bi-directional replication?

The customer should consider the following points in an active-active replication environment.

Primary Key: Helps to identify conflicts and Resolve them.
Sequences: Are not supported. The work around is use to use odd/even, range or concatenate sequences.
Triggers: These should be disabled or suppressed to avoid using uniqueness issue
Data Looping: This can easy avoided using OGG itself
LAG: This should be minimized. If a customer says that there will not be any LAG due to network or huge load, then we don’t need to deploy CRDs. But this is not the case always as there would be some LAG and these can cause Conflicts.
CDR (Conflict Detection & Resolution): OGG has built in CDRs for all kind of DMLs that can be used to detect and resolve them.
Packaged Application: These are not supported as it may contain data types which are not support by OGG or it might not allow the application modification to work with OGG.

Are OGG binaries supported on ASM Cluster File System (ACFS)?

Yes, you can install and configure OGG on ACFS.

Are OGG binaries supported on the Database File System (DBFS)? What files can be stored in DBFS?

No, OGG binaries are not supported on DBFS. You can however store parameter files, data files (trail files), and checkpoint files on DBFS.

What is the default location of the GLOBALS file?

A GLOBALS file is located under Oracle GoldenGate installation directory (OGG HOME)

Where can filtering of data for a column be configured?

Filtering of the columns of a table can be set at the Extract, Pump or Replicat level.

Is it a requirement to configure a PUMP extract process in OGG replication?

A PUMP extract is an option, but it is highly recommended to use this to safe guard against network failures. Normally it is configured when you are setting up OGG replication across the network.

What are the differences between the Classic and integrated Capture?

Classic Capture:

The Classic Capture mode is the traditional Extract process that accesses the database redo logs (optionally archive logs) to capture the DML changes occurring on the objects specified in the parameter files.
At the OS level, the GoldenGate user must be a part of the same database group which owns the database redo logs.
This capture mode is available for other RDBMS as well.
There are some data types that are not supported in Classic Capture mode.
Classic capture can’t read data from the compressed tables/tablespaces.
Integrated Capture (IC):

In the Integrated Capture mode, GoldenGate works directly with the database log mining server to receive the data changes in the form of logical change records (LCRs).
IC mode does not require any special setup for the databases using ASM, transparent data encryption, or Oracle RAC.
This feature is only available for oracle databases in Version 11.2.0.3 or higher.
 It also supports various object types which were previously not supported by Classic Capture.
This Capture mode supports extracting data from source databases using compression.
Integrated Capture can be configured in an online or downstream mode.

List the minimum parameters that can be used to create the extract process?

The following are the minimium required parameters which must be defined in the extract parameter file.

EXTRACT NAME
USERID
EXTTRAIL
TABLE

What are macros?

Macro is an easier way to build your parameter file. Once a macro is written it can be called from different parameter files. Common parameters like username/password and other parameters can be included in these macros. A macro can either be another parameter file or a library.

Where can macros be invoked?

The macros can be called from the following parameter files.

Manager
Extract
Replicat
Gobals

How is a macro defined?

A macro statement consists of the following.

Name of the Macro
Parameter list
Macro body
Sample:
MACRO #macro_name
PARAMS (#param1, #param2, …)
BEGIN
< macro_body >
END;

I want to configure multiple extracts to write to the same exttrail file? Is this possible?

Only one Extract process can write to one exttrail at a time. So you can’t configure multiple extracts to write to the same exttrail.

What type of Encryption is supported in Goldengate?

Oracle Goldengate provides 3 types of Encryption.

Data Encryption using Blow fish.
Password Encryption.
Network Encryption.

What are the different password encrytion options available with OGG?

You can encrypt a password in OGG using

Blowfish algorithm and
Advance Encryption Standard (AES) algorithm

What are the different encryption levels in AES?

You can encrypt the password/data using the AES in three different keys

a) 128 bit
b) 192 bit and
c) 256 bit

Is there a way to check the syntax of the commands in the parameter file without actually running the GoldenGate process
Yes, you can place the SHOWSYNTAX parameter in the parameter file and try starting. If there is any error you will see it.

How can you increase the maximum size of the read operation into the buffer that holds the results of the reads from the transaction log?

If you are using the Classical Extract you may use the TRANSLOGOPTION ASMBUFSIZE parameter to control the read size for ASM Databases.

What information can you expect when there us data in the discard file?

When data is discarded, the discard file can contain:
1. Discard row details
2. Database Errors
3. Trail file number

What command can be used to switch writing the trail data to a new trail file?

You can use the following command to write the trail data to a new trail file.
SEND EXTRACT ext_name, ROLLOVER

How can you determine if the parameters for a process was recently changed?

Whenever a process is started, the parameters in the .prm file for the process is written to the process REPORT. You can look at the older process reports to view the parameters which were used to start up the process. By comparing the older and the current reports you can identify the changes in the parameters.

Monday, June 20, 2016

ORA-19802 Error: "Cannot Use Db_recovery_file_dest Without DB_RECOVERY_FILE_DEST_SIZE

ORA-19802 Error: "Cannot Use Db_recovery_file_dest Without DB_RECOVERY_FILE_DEST_SIZE

Error:

ORA-19802 error: "cannot use DB_RECOVERY_FILE_DEST without DB_RECOVERY_FILE_DEST_SIZE."

Cause:

The problem is caused by the fact that these two settings are interdependent and they must both be set to valid values.

Solution:

Ensure both parameters are properly set.

Use:

ALTER SYSTEM set DB_RECOVERY_FILE_DEST_SIZE

and/or

ALTER SYSTEM set DB_RECOVERY_FILE_DEST

Reference metalink (Doc ID 749595.1) 

Sunday, June 12, 2016

SP2-0734: unknown command beginning when recreate controlfile

SP2-0734: unknown command beginning when recreate controlfile

Create backup for controlfile

SQL> alter database backup controlfile to trace as ‘d:\control\test.trc’;

Database altered.

Recreate Controlfile, Edit the test.trc file and when i try to recreate the control file , it showing SP-0734

SQL> SHUTDOWN ABORT
ORACLE instance shut down.
SQL> @D:\control\CONTROL.TRC;
ORACLE instance started.

Total System Global Area  535662592 bytes
Fixed Size                  1375792 bytes
Variable Size             331350480 bytes
Database Buffers          197132288 bytes
Redo Buffers                5804032 bytes
SP2-0042: unknown command “DATAFILE” – rest of line ignored.
SP2-0734: unknown command beginning “‘D:\CONTRO…” – rest of line ignored.
SP2-0734: unknown command beginning “‘D:\CONTRO…” – rest of line ignored.
SP2-0734: unknown command beginning “‘D:\CONTRO…” – rest of line ignored

This happened due to blank line before the DATAFILE clause and after — STANDBY LOGFILE and  remove that space line as well as remove line — STANDBY LOGFILE and re run the script,

STARTUP NOMOUNT
CREATE CONTROLFILE REUSE DATABASE “AZAR” NORESETLOGS  ARCHIVELOG
    MAXLOGFILES 16
    MAXLOGMEMBERS 3
    MAXDATAFILES 8
    MAXINSTANCES 8
    MAXLOGHISTORY 292
LOGFILE
  GROUP 1 ‘C:\DATAFILES\AZAR\REDO01.LOG’  SIZE 50M BLOCKSIZE 512,
  GROUP 2 ‘C:\DATAFILES\AZAR\REDO02.LOG’  SIZE 50M BLOCKSIZE 512,
  GROUP 3 ‘C:\DATAFILES\AZAR\REDO03.LOG’  SIZE 50M BLOCKSIZE 512
— STANDBY LOGFILE                                                             [ REMOVE THIS SPACE LINE ]

DATAFILE
  ‘C:\DATAFILES\AZAR\SYSTEM01.DBF’,
  ‘C:\DATAFILES\AZAR\SYSAUX01.DBF’,
  ‘C:\DATAFILES\AZAR\UNDOTBS01.DBF’,
  ‘C:\DATAFILES\AZAR\USERS01.DBF’,
  ‘C:\TEST01.DBF’
CHARACTER SET AR8ISO8859P6
;

After removed space

STARTUP NOMOUNT
CREATE CONTROLFILE REUSE DATABASE “AZAR” NORESETLOGS  ARCHIVELOG
    MAXLOGFILES 16
    MAXLOGMEMBERS 3
    MAXDATAFILES 8
    MAXINSTANCES 8
    MAXLOGHISTORY 292
LOGFILE
  GROUP 1 ‘C:\DATAFILES\AZAR\REDO01.LOG’  SIZE 50M BLOCKSIZE 512,
  GROUP 2 ‘C:\DATAFILES\AZAR\REDO02.LOG’  SIZE 50M BLOCKSIZE 512,
  GROUP 3 ‘C:\DATAFILES\AZAR\REDO03.LOG’  SIZE 50M BLOCKSIZE 512
DATAFILE
  ‘C:\DATAFILES\AZAR\SYSTEM01.DBF’,
  ‘C:\DATAFILES\AZAR\SYSAUX01.DBF’,
  ‘C:\DATAFILES\AZAR\UNDOTBS01.DBF’,
  ‘C:\DATAFILES\AZAR\USERS01.DBF’,
  ‘C:\TEST01.DBF’
CHARACTER SET AR8ISO8859P6
;

SQL> shutdown abort
ORACLE instance shut down.
SQL> @D:\control\CONTROL.TRC;
ORACLE instance started.

Total System Global Area  535662592 bytes
Fixed Size                  1375792 bytes
Variable Size             331350480 bytes
Database Buffers          197132288 bytes
Redo Buffers                5804032 bytes

Control file created.

SQL> select status from v$instance;

STATUS
————
MOUNTED

SQL> alter database open;

Database altered.

SQL> select status from v$instance;

STATUS
————
OPEN 

Thursday, June 9, 2016

How To Check GSM Is Enabled Or Not

How To Check GSM Is Enabled Or Not

SQL> select DECODE(b.profile_option_value, 'Y', 'Enabled', 'Disabled') DETAILS from fnd_profile_options a, fnd_profile_option_values b where a.APPLICATION_ID = b.APPLICATION_ID and a.PROFILE_OPTION_ID = b.PROFILE_OPTION_ID
and a.PROFILE_OPTION_NAME = 'CONC_GSM_ENABLED';

DETAILS
--------
Enabled

SQL> select CONCURRENT_QUEUE_NAME, ENABLED_FLAG, MAX_PROCESSES, RUNNING_PROCESSES from FND_CONCURRENT_QUEUES where CONCURRENT_QUEUE_NAME like 'FNDSM%';

CONCURRENT_QUEUE_NAME          E MAX_PROCESSES RUNNING_PROCESSES
------------------------------ - ------------- -----------------
FNDSM_AUOHSGECA78              Y             1                 1

Note: Here Flag "Y"  means GSM Enabled.

Saturday, June 4, 2016

Understanding Top Command

Understanding Top Command

1 Row — top

topr1
 This first line indicates in order:
  • current time (11:37:19)
  • uptime of the machine (up 1 day, 1:25)
  • users sessions logged in (3 users)
  • average load on the system (load average: 0.02, 0.12, 0.07) the 3 values refer to the last minute, five minutes and 15 minutes.

2 Row – task

topr2
 The second row gives the following information:
  • Processes running in totals (73 total)
  • Processes running (2 running)
  • Processes sleeping (71 sleeping)
  • Processes stopped (0 stopped)
  • Processes waiting to be stoppati from the parent process (0 zombie)

3 Row – cpu

topr3
 The third line indicates how the cpu is used. If you sum up all the percentages the total will be 100% of the cpu. Let’s see what these values indicate in order:
  • Percentage of the CPU for user processes (0.3%us)
  • Percentage of the CPU for system processes (0.0%sy)
  • Percentage of the CPU processes with priority upgrade nice (0.0%ni)
  • Percentage of the CPU not used (99,4%id)
  • Percentage of the CPU processes waiting for I/O operations(0.0%wa)
  • Percentage of the CPU serving hardware interrupts (0.3% hi — Hardware IRQ
  • Percentage of the CPU serving software interrupts (0.0% si — Software Interrupts
  • The amount of CPU ‘stolen’ from this virtual machine by the hypervisor for other tasks (such as running another virtual machine) this will be 0 on desktop and server without Virtual machine. (0.0%st — Steal Time)
4 and 5 Rows – memory usage
topr45The fourth and fifth rows respectively indicate the use of physical memory (RAM) and swap. In this order: Total memory in use, free, buffers cached. On this topic you can also read the following article

Following Rows — Processes list

topr6And as last thing ordered by CPU usage (as default) there are the processes currently in use. Let’s see what information we can get in the different columns:
  • PID – l’ID of the process(4522)
  • USER – The user that is the owner of the process (root)
  • PR – priority of the process (15)
  • NI – The “NICE” value of the process (0)
  • VIRT – virtual memory used by the process (132m)
  • RES – physical memory used from the process (14m)
  • SHR – shared memory of the process (3204)
  • S – indicates the status of the process: S=sleep R=running Z=zombie (S)
  • %CPU – This is the percentage of CPU used by this process (0.3)
  • %MEM – This is the percentage of RAM used by the process (0.7)
  • TIME+ –This is the total time of activity of this process (0:17.75)
  • COMMAND – And this is the name of the process (bb_monitor.pl)

Thursday, June 2, 2016

ORA-28000 ,ORA-28001, ORA-28002 : The Account locked ,expired or password will expire within xx days

ORA-28000 ,ORA-28001, ORA-28002 : The Account locked ,expired or password will expire within xx days

ORA-28000 specifies the user's account is locked .The common reason of occurring this error is when it gets locked internally based on the profile resource limit. This error may also occur when the user has entered wrong password consequently for maximun no. of times as specified by the user's profile parameter i.e, Failed_Login_Attempts. To solve this error either wait for the Password_lock_time or the DBA can fire the below command to solve this issue :

SQL> alter user xyz identified by password account unlock ;

ORA-28001 specifies the user account is expired . This error commonly occurs when the expiry time is reached . By default the expiry date for a newly created user is of 180 days . Hence to solve this issue, increase the limit of the password expiry date. For this check the profile assigned to the user and then limit the password expiry date. To solve this issue increase the password expiry periods .

SQL>select username,profile from dba_users where username='TEST' ;

SQL> alter profile  profile_name limit  PASSWORD_LIFE_TIME  UNLIMITED;

ORA-28002 specifies that  the user's account is about to about to expire and the password needs to be changed. This can be solved either by changing the password or by changing the user profile. If we do want this behavior, we need to do the following:

1) Logon to the product database as the SYSTEM user (not the application administration user).

2) Find the profile that has the PASSWORD_LIFE_TIME set to anything but UNLIMITED.

SQL> select * from dba_profiles where RESOURCE_NAME LIKE  'PASSWORD_LIFE_TIME';

If the user name say "test" and password is also "test" then check the profile assign to the user as

SQL>select username,profile from dba_users where username='TEST' ;

Once ,we have profile, we alter the profile and password .

3) Alter the profiles with the following statement:

SQL> alter user test identified by test  account unlock ;

SQL> alter profile  profile_name limit  PASSWORD_LIFE_TIME  UNLIMITED;

where profile_name is the name of the profile where wer need to set the password life to UNLIMITED. This should remove the password life message.

Wednesday, June 1, 2016

Drop Database Using RMAN

Drop Database Using RMAN

Most  of the time  we  generally  use  DBCA  to  delete  the  database  because  it  is easy  and  simple . But , there are  some  scenario's  where we  have to drop  database  without using  the graphics  i,e, without DBCA . In such case  , we can  delete  or drop  the  database either by   manually  or  by using  sql*plus  or  RMAN prompt . As  compare  to  sql*plus , rman  is  much  more effective  because  it  consume  less   time  and secondly  we  can   delete  the  archivelogs  and backups  also . Starting  with Oracle  10gR1 onwards,  we  can  drop  a database  and  remove all  its records  from  the  rman catalog .

There are basically 4 syntax available to drop the database using RMAN

1) Drop Database : This command  deletes the datafiles, logfiles, Controlfiles and Spfile. If we do not want to delete backups then we can use this command.

2) Drop Database Noprompt : When “NOPROMPT”  is specified RMAN does not prompt for the confirmation before deleting the database. It delete the above files as in case of drop database.

3)DROP DATABASE INCLUDING BACKUPS : This command delete the datafiles, ogfiles,Controlfiles and Spfile plus the archivelogs and backup pieces  generated by RMAN.

4) DROP DATABASE INCLUDING BACKUPS NOPROMPT  : When “NOPROMPT” is specified RMAN does not prompt for the confirmation before deleting the database.

Let's have a look on the following steps to drop the Database using RMAN

SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.

SQL> startup mount exclusive
ORACLE instance started.
Total System Global Area   285212672 bytes
Fixed Size                          1218992 bytes
Variable Size                      100664912 bytes
Database Buffers                180355072 bytes
Redo Buffers                      2973696 bytes
Database mounted.

SQL> alter system enable restricted session;
System altered.
SQL> exit
C:\> rman target /

RMAN> DROP DATABASE INCLUDING BACKUPS;
database name is "ORACLE" and DBID is 1574601275

Do you really want to drop all backups and the database (enter YES or NO)? yes   // (by defaults it prompts)
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=155 devtype=DISK

List of Backup Pieces
BP Key  BS Key  Pc# Cp# Status      Device Type Piece Name
------- ------- --- --- ----------- ----------- ----------
18      18      1   1   AVAILABLE   DISK        D:\RMAN\ORACLE_1
19      19      1   1   AVAILABLE   DISK        D:\RMAN\ARCH_ORACLE_1
deleted backup piece
backup piece handle=D:\RMAN\ORACLE_1 recid=18 stamp=711242843
deleted backup piece
backup piece handle=D:\RMAN\ARCH_ORACLE_1 recid=19 stamp=711243739
Deleted 2 objects

released channel: ORA_DISK_1
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=155 devtype=DISK

List of Control File Copies
Key     S Completion Time Ckp SCN    Ckp Time        Name
------- - --------------- ---------- --------------- ----
2       A 27-DEC-09       2754635    27-DEC-09       D:\ORACLE\PRODUCT\10.2.0\STANDBY\STANDBYCTL01.CTL

List of Archived Log Copies
Key     Thrd Seq     S Low Time  Name
------- ---- ------- - --------- ----
202     1    132     A 16-JAN-10 D:\ORACLE\PRODUCT\10.2.0\ARCHIVE\ORACLE\ORACLE_ARCH_132_701523840_1
203     1    133     A 16-JAN-10 D:\ORACLE\PRODUCT\10.2.0\ARCHIVE\ORACLE\ORACLE_ARCH_133_701523840_1
204     1    134     A 16-JAN-10 D:\ORACLE\PRODUCT\10.2.0\ARCHIVE\ORACLE\ORACLE_ARCH_134_701523840_1
205     1    135     A 16-JAN-10 D:\ORACLE\PRODUCT\10.2.0\ARCHIVE\ORACLE\ORACLE_ARCH_135_701523840_1
206     1    136     A 16-JAN-10 D:\ORACLE\PRODUCT\10.2.0\ARCHIVE\ORACLE\ORACLE_ARCH_136_701523840_1
207     1    137     A 16-JAN-10 D:\ORACLE\PRODUCT\10.2.0\ARCHIVE\ORACLE\ORACLE_ARCH_137_701523840_1
208     1    138     A 16-JAN-10 D:\ORACLE\PRODUCT\10.2.0\ARCHIVE\ORACLE\ORACLE_ARCH_138_701523840_1
209     1    139     A 16-JAN-10 D:\ORACLE\PRODUCT\10.2.0\ARCHIVE\ORACLE\ORACLE_ARCH_139_701523840_1
210     1    140     A 16-JAN-10 D:\ORACLE\PRODUCT\10.2.0\ARCHIVE\ORACLE\ORACLE_ARCH_140_701523840_1
211     1    141     A 16-JAN-10 D:\ORACLE\PRODUCT\10.2.0\ARCHIVE\ORACLE\ORACLE_ARCH_141_701523840_1
212     1    142     A 17-JAN-10 D:\ORACLE\PRODUCT\10.2.0\ARCHIVE\ORACLE\ORACLE_ARCH_142_701523840_1
213     1    143     A 17-JAN-10 D:\ORACLE\PRODUCT\10.2.0\ARCHIVE\ORACLE\ORACLE_ARCH_143_701523840_1
214     1    144     A 17-JAN-10 D:\ORACLE\PRODUCT\10.2.0\ARCHIVE\ORACLE\ORACLE_ARCH_144_701523840_1
215     1    145     A 17-JAN-10 D:\ORACLE\PRODUCT\10.2.0\ARCHIVE\ORACLE\ORACLE_ARCH_145_701523840_1
216     1    146     A 17-JAN-10 D:\ORACLE\PRODUCT\10.2.0\ARCHIVE\ORACLE\ORACLE_ARCH_146_701523840_1
217     1    147     A 17-JAN-10 D:\ORACLE\PRODUCT\10.2.0\ARCHIVE\ORACLE\ORACLE_ARCH_147_701523840_1
218     1    148     A 17-JAN-10 D:\ORACLE\PRODUCT\10.2.0\ARCHIVE\ORACLE\ORACLE_ARCH_148_701523840_1
219     1    149     A 17-JAN-10 D:\ORACLE\PRODUCT\10.2.0\ARCHIVE\ORACLE\ORACLE_ARCH_149_701523840_1
220     1    150     A 17-JAN-10 D:\ORACLE\PRODUCT\10.2.0\ARCHIVE\ORACLE\ORACLE_ARCH_150_701523840_1
221     1    151     A 17-JAN-10 D:\ORACLE\PRODUCT\10.2.0\ARCHIVE\ORACLE\ORACLE_ARCH_151_701523840_1
222     1    152     A 17-JAN-10 D:\ORACLE\PRODUCT\10.2.0\ARCHIVE\ORACLE\ORACLE_ARCH_152_701523840_1
223     1    153     A 17-JAN-10 D:\ORACLE\PRODUCT\10.2.0\ARCHIVE\ORACLE\ORACLE_ARCH_153_701523840_1
224     1    154     A 17-JAN-10 D:\ORACLE\PRODUCT\10.2.0\ARCHIVE\ORACLE\ORACLE_ARCH_154_701523840_1
225     1    155     A 17-JAN-10 D:\ORACLE\PRODUCT\10.2.0\ARCHIVE\ORACLE\ORACLE_ARCH_155_701523840_1
226     1    156     A 17-JAN-10 D:\ORACLE\PRODUCT\10.2.0\ARCHIVE\ORACLE\ORACLE_ARCH_156_701523840_1
227     1    157     A 17-JAN-10 D:\ORACLE\PRODUCT\10.2.0\ARCHIVE\ORACLE\ORACLE_ARCH_157_701523840_1
228     1    158     A 17-JAN-10 D:\ORACLE\PRODUCT\10.2.0\ARCHIVE\ORACLE\ORACLE_ARCH_158_701523840_1
229     1    159     A 17-JAN-10 D:\ORACLE\PRODUCT\10.2.0\ARCHIVE\ORACLE\ORACLE_ARCH_159_701523840_1
230     1    160     A 17-JAN-10 D:\ORACLE\PRODUCT\10.2.0\ARCHIVE\ORACLE\ORACLE_ARCH_160_701523840_1
231     1    161     A 17-JAN-10 D:\ORACLE\PRODUCT\10.2.0\ARCHIVE\ORACLE\ORACLE_ARCH_161_701523840_1
232     1    162     A 17-JAN-10 D:\ORACLE\PRODUCT\10.2.0\ARCHIVE\ORACLE\ORACLE_ARCH_162_701523840_1
233     1    163     A 17-JAN-10 D:\ORACLE\PRODUCT\10.2.0\ARCHIVE\ORACLE\ORACLE_ARCH_163_701523840_1
235     1    164     A 17-JAN-10 D:\ORACLE\PRODUCT\10.2.0\ARCHIVE\ORACLE\ORACLE_ARCH_164_701523840_1
234     1    165     A 18-JAN-10 D:\ORACLE\PRODUCT\10.2.0\ARCHIVE\ORACLE\ORACLE_ARCH_165_701523840_1
236     1    166     A 18-JAN-10 D:\ORACLE\PRODUCT\10.2.0\ARCHIVE\ORACLE\ORACLE_ARCH_166_701523840_1
237     1    167     A 18-JAN-10 D:\ORACLE\PRODUCT\10.2.0\ARCHIVE\ORACLE\ORACLE_ARCH_167_701523840_1
238     1    168     A 26-JAN-10 D:\ORACLE\PRODUCT\10.2.0\ARCHIVE\ORACLE\ORACLE_ARCH_168_701523840_1
239     1    169     A 26-JAN-10 D:\ORACLE\PRODUCT\10.2.0\ARCHIVE\ORACLE\ORACLE_ARCH_169_701523840_1
240     1    170     A 27-JAN-10 D:\ORACLE\PRODUCT\10.2.0\ARCHIVE\ORACLE\ORACLE_ARCH_170_701523840_1
241     1    171     A 28-JAN-10 D:\ORACLE\PRODUCT\10.2.0\ARCHIVE\ORACLE\ORACLE_ARCH_171_701523840_1
242     1    172     A 30-JAN-10 D:\ORACLE\PRODUCT\10.2.0\ARCHIVE\ORACLE\ORACLE_ARCH_172_701523840_1
243     1    173     A 31-JAN-10 D:\ORACLE\PRODUCT\10.2.0\ARCHIVE\ORACLE\ORACLE_ARCH_173_701523840_1
244     1    174     A 01-FEB-10 D:\ORACLE\PRODUCT\10.2.0\ARCHIVE\ORACLE\ORACLE_ARCH_174_701523840_1
245     1    175     A 03-FEB-10 D:\ORACLE\PRODUCT\10.2.0\ARCHIVE\ORACLE\ORACLE_ARCH_175_701523840_1
246     1    176     A 04-FEB-10 D:\ORACLE\PRODUCT\10.2.0\ARCHIVE\ORACLE\ORACLE_ARCH_176_701523840_1
247     1    177     A 04-FEB-10 D:\ORACLE\PRODUCT\10.2.0\ARCHIVE\ORACLE\ORACLE_ARCH_177_701523840_1
248     1    178     A 04-FEB-10 D:\ORACLE\PRODUCT\10.2.0\ARCHIVE\ORACLE\ORACLE_ARCH_178_701523840_1
249     1    179     A 04-FEB-10 D:\ORACLE\PRODUCT\10.2.0\ARCHIVE\ORACLE\ORACLE_ARCH_179_701523840_1

deleted control file copy
control file copy filename=D:\ORACLE\PRODUCT\10.2.0\STANDBY\STANDBYCTL01.CTL recid=2 stamp=706679151
deleted archive log
archive log filename=D:\ORACLE\PRODUCT\10.2.0\ARCHIVE\ORACLE\ORACLE_ARCH_132_701523840_1 recid=202 stamp=708474283
deleted archive log
archive log filename=D:\ORACLE\PRODUCT\10.2.0\ARCHIVE\ORACLE\ORACLE_ARCH_133_701523840_1 recid=203 stamp=708475725
deleted archive log
archive log filename=D:\ORACLE\PRODUCT\10.2.0\ARCHIVE\ORACLE\ORACLE_ARCH_134_701523840_1 recid=204 stamp=708475725
deleted archive log
archive log filename=D:\ORACLE\PRODUCT\10.2.0\ARCHIVE\ORACLE\ORACLE_ARCH_135_701523840_1 recid=205 stamp=708477082
deleted archive log
archive log filename=D:\ORACLE\PRODUCT\10.2.0\ARCHIVE\ORACLE\ORACLE_ARCH_136_701523840_1 recid=206 stamp=708477082
deleted archive log
archive log filename=D:\ORACLE\PRODUCT\10.2.0\ARCHIVE\ORACLE\ORACLE_ARCH_137_701523840_1 recid=207 stamp=708477174
deleted archive log
archive log filename=D:\ORACLE\PRODUCT\10.2.0\ARCHIVE\ORACLE\ORACLE_ARCH_138_701523840_1 recid=208 stamp=708477177
deleted archive log
archive log filename=D:\ORACLE\PRODUCT\10.2.0\ARCHIVE\ORACLE\ORACLE_ARCH_139_701523840_1 recid=209 stamp=708477957
deleted archive log
archive log filename=D:\ORACLE\PRODUCT\10.2.0\ARCHIVE\ORACLE\ORACLE_ARCH_140_701523840_1 recid=210 stamp=708478193
deleted archive log
archive log filename=D:\ORACLE\PRODUCT\10.2.0\ARCHIVE\ORACLE\ORACLE_ARCH_141_701523840_1 recid=211 stamp=708523128
deleted archive log
archive log filename=D:\ORACLE\PRODUCT\10.2.0\ARCHIVE\ORACLE\ORACLE_ARCH_142_701523840_1 recid=212 stamp=708523129
deleted archive log
archive log filename=D:\ORACLE\PRODUCT\10.2.0\ARCHIVE\ORACLE\ORACLE_ARCH_143_701523840_1 recid=213 stamp=708523194
deleted archive log
archive log filename=D:\ORACLE\PRODUCT\10.2.0\ARCHIVE\ORACLE\ORACLE_ARCH_144_701523840_1 recid=214 stamp=708527072
deleted archive log
archive log filename=D:\ORACLE\PRODUCT\10.2.0\ARCHIVE\ORACLE\ORACLE_ARCH_145_701523840_1 recid=215 stamp=708527201
deleted archive log
archive log filename=D:\ORACLE\PRODUCT\10.2.0\ARCHIVE\ORACLE\ORACLE_ARCH_146_701523840_1 recid=216 stamp=708527278
deleted archive log
archive log filename=D:\ORACLE\PRODUCT\10.2.0\ARCHIVE\ORACLE\ORACLE_ARCH_147_701523840_1 recid=217 stamp=708527459
deleted archive log
archive log filename=D:\ORACLE\PRODUCT\10.2.0\ARCHIVE\ORACLE\ORACLE_ARCH_148_701523840_1 recid=218 stamp=708527641
deleted archive log
archive log filename=D:\ORACLE\PRODUCT\10.2.0\ARCHIVE\ORACLE\ORACLE_ARCH_149_701523840_1 recid=219 stamp=708527711
deleted archive log
archive log filename=D:\ORACLE\PRODUCT\10.2.0\ARCHIVE\ORACLE\ORACLE_ARCH_150_701523840_1 recid=220 stamp=708531955
deleted archive log
archive log filename=D:\ORACLE\PRODUCT\10.2.0\ARCHIVE\ORACLE\ORACLE_ARCH_151_701523840_1 recid=221 stamp=708532250
deleted archive log
archive log filename=D:\ORACLE\PRODUCT\10.2.0\ARCHIVE\ORACLE\ORACLE_ARCH_152_701523840_1 recid=222 stamp=708532331
deleted archive log
archive log filename=D:\ORACLE\PRODUCT\10.2.0\ARCHIVE\ORACLE\ORACLE_ARCH_153_701523840_1 recid=223 stamp=708537657
deleted archive log
archive log filename=D:\ORACLE\PRODUCT\10.2.0\ARCHIVE\ORACLE\ORACLE_ARCH_154_701523840_1 recid=224 stamp=708537715
deleted archive log
archive log filename=D:\ORACLE\PRODUCT\10.2.0\ARCHIVE\ORACLE\ORACLE_ARCH_155_701523840_1 recid=225 stamp=708537936
deleted archive log
archive log filename=D:\ORACLE\PRODUCT\10.2.0\ARCHIVE\ORACLE\ORACLE_ARCH_156_701523840_1 recid=226 stamp=708538012
deleted archive log
archive log filename=D:\ORACLE\PRODUCT\10.2.0\ARCHIVE\ORACLE\ORACLE_ARCH_157_701523840_1 recid=227 stamp=708538174
deleted archive log
archive log filename=D:\ORACLE\PRODUCT\10.2.0\ARCHIVE\ORACLE\ORACLE_ARCH_158_701523840_1 recid=228 stamp=708538195
deleted archive log
archive log filename=D:\ORACLE\PRODUCT\10.2.0\ARCHIVE\ORACLE\ORACLE_ARCH_159_701523840_1 recid=229 stamp=708538201
deleted archive log
archive log filename=D:\ORACLE\PRODUCT\10.2.0\ARCHIVE\ORACLE\ORACLE_ARCH_160_701523840_1 recid=230 stamp=708539593
deleted archive log
archive log filename=D:\ORACLE\PRODUCT\10.2.0\ARCHIVE\ORACLE\ORACLE_ARCH_161_701523840_1 recid=231 stamp=708555515
deleted archive log
archive log filename=D:\ORACLE\PRODUCT\10.2.0\ARCHIVE\ORACLE\ORACLE_ARCH_162_701523840_1 recid=232 stamp=708555516
deleted archive log
archive log filename=D:\ORACLE\PRODUCT\10.2.0\ARCHIVE\ORACLE\ORACLE_ARCH_163_701523840_1 recid=233 stamp=708555536
deleted archive log
archive log filename=D:\ORACLE\PRODUCT\10.2.0\ARCHIVE\ORACLE\ORACLE_ARCH_164_701523840_1 recid=235 stamp=708648593
deleted archive log
archive log filename=D:\ORACLE\PRODUCT\10.2.0\ARCHIVE\ORACLE\ORACLE_ARCH_165_701523840_1 recid=234 stamp=708648592
deleted archive log
archive log filename=D:\ORACLE\PRODUCT\10.2.0\ARCHIVE\ORACLE\ORACLE_ARCH_166_701523840_1 recid=236 stamp=708648853
deleted archive log
archive log filename=D:\ORACLE\PRODUCT\10.2.0\ARCHIVE\ORACLE\ORACLE_ARCH_167_701523840_1 recid=237 stamp=709329114
deleted archive log
archive log filename=D:\ORACLE\PRODUCT\10.2.0\ARCHIVE\ORACLE\ORACLE_ARCH_168_701523840_1 recid=238 stamp=709330932
deleted archive log
archive log filename=D:\ORACLE\PRODUCT\10.2.0\ARCHIVE\ORACLE\ORACLE_ARCH_169_701523840_1 recid=239 stamp=709423232
deleted archive log
archive log filename=D:\ORACLE\PRODUCT\10.2.0\ARCHIVE\ORACLE\ORACLE_ARCH_170_701523840_1 recid=240 stamp=709515047
deleted archive log
archive log filename=D:\ORACLE\PRODUCT\10.2.0\ARCHIVE\ORACLE\ORACLE_ARCH_171_701523840_1 recid=241 stamp=709678641
deleted archive log
archive log filename=D:\ORACLE\PRODUCT\10.2.0\ARCHIVE\ORACLE\ORACLE_ARCH_172_701523840_1 recid=242 stamp=709728779
deleted archive log
archive log filename=D:\ORACLE\PRODUCT\10.2.0\ARCHIVE\ORACLE\ORACLE_ARCH_173_701523840_1 recid=243 stamp=709857486
deleted archive log
archive log filename=D:\ORACLE\PRODUCT\10.2.0\ARCHIVE\ORACLE\ORACLE_ARCH_174_701523840_1 recid=244 stamp=710029358
deleted archive log
archive log filename=D:\ORACLE\PRODUCT\10.2.0\ARCHIVE\ORACLE\ORACLE_ARCH_175_701523840_1 recid=245 stamp=710118379
deleted archive log
archive log filename=D:\ORACLE\PRODUCT\10.2.0\ARCHIVE\ORACLE\ORACLE_ARCH_176_701523840_1 recid=246 stamp=710118402
deleted archive log
archive log filename=D:\ORACLE\PRODUCT\10.2.0\ARCHIVE\ORACLE\ORACLE_ARCH_177_701523840_1 recid=247 stamp=710118425
deleted archive log
archive log filename=D:\ORACLE\PRODUCT\10.2.0\ARCHIVE\ORACLE\ORACLE_ARCH_178_701523840_1 recid=248 stamp=710118565
deleted archive log
archive log filename=D:\ORACLE\PRODUCT\10.2.0\ARCHIVE\ORACLE\ORACLE_ARCH_179_701523840_1 recid=249 stamp=710248827
Deleted 49 objects

Database name is "ORACLE" and DBID is 1574601275
Database dropped.