Pages

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.

About Alert Log File In Oracle

About Alert Log File In Oracle

The alert log file is a chronological log of messages and errors written out by an Oracle Database. Typical messages found in this file is: database startup, shutdown, log switches, space errors, etc. This file should constantly be monitored to detect unexpected messages and corruptions.Oracle will automatically create a new alert log file whenever the old one is deleted.

When an internal error is detected by a process, it dumps information about the error to its trace file. Some of the information written to a trace file is intended for the database administrator, while other information is for Oracle Worldwide Support. Trace file information is also used to tune applications and instances.

The alert log of a database includes the following information :

1) All internal errors (ORA-00600), block corruption errors (ORA-01578), and deadlock errors (ORA-00060) that occur.

2) Administrative operations, such as CREATE, ALTER, and DROP statements and STARTUP,
SHUTDOWN, and ARCHIVELOG statements.

3) Messages and errors relating to the functions of shared server and dispatcher processes.

4) Errors occurring during the automatic refresh of a materialized view.

5) The values of all initialization parameters that had non-default values at the time the database and instance startup .

which process writes to alert log file:

Not "one" but all the background processes can/do write to it. The archiver writes to it. LogWriter can write (if we have log_checkpoints_to_alert). When a background process detects that another has died, the former writes to the alert log before panicking the instance and killing it.  Similarly an ALTER SYSTEM command issued by the server process for our database session will also write to the alert.log .

To find the location of alert log file we can find by below command

SQL> select value from v$parameter where name = 'background_dump_dest' ;   OR
SQL> show parameter background
SQL> select * from v$diag_info;

If the background_dump_dest parameter is not specified, Oracle will write the alert.log into the $ORACLE_HOME/RDBMS/trace directory.