Archive for the ‘Oracle Queries’ Category

« Older Entries
Newer Entries »

How To Find Out Which Users Are Logged On To An Apps Instance

June 6th, 2016, posted in Oracle Queries
Share

FND_USER table stores the details of all the end users. If we give this query:

select user_name,to_char(last_logon_date,'DD-MON-YYYY HH24:MI:SS')
from apps.fnd_user
where to_char(last_logon_date,'DD-MON-YYYY')=to_char(sysdate,'DD-MON-YYYY');

USER_NAME TO_CHAR(LAST_LOGON_DATE,'D
------------------------------------
GUEST 05-FEB-2008 16:01:47
SYSADMIN 05-FEB-2008 16:02:06
USER1 05-FEB-2008 07:31:19
USER2 05-FEB-2008 04:58:12
USER3 05-FEB-2008 09:46:00
USER4 05-FEB-2008 05:00:38
USER5 05-FEB-2008 08:45:07
USER6 05-FEB-2008 09:09:17

The above query can give you a good idea who is logged on.

For a more accurate result, refer to metalink note 269799.1 which says:

You can run the Active Users Data Collection Test diagnostic script to get information about all active users currently logged into Oracle Applications.This diagnostic test will list users logged into Self Service Web Application, users logged into forms, and users running concurrent programs. Please note that to collect the audit information about the users who have logged into Forms, the “Sign-On:Audit Level” profile option should be set to Form. You can access this Diagnostic test via Metalink Note# 233871.1.

Share
read more no comments

ORA-00265: Instance recovery required cannot set ARCHIVELOG mode

June 3rd, 2016, posted in Oracle Queries
Share

Today, I came across the “ORA-00265: instance recovery required, cannot set ARCHIVELOG mode” while
converting database into archive log mode.Problem sys@standby> startup mount; ORACLE instance started. Total System Global Area 835104768 bytes Fixed Size 2217952 bytes Variable Size 490735648 bytes Database Buffers 335544320 bytes Redo Buffers 6606848 bytes Database mounted. sys@standby> alter database recover managed standby database using current logfile disconnect; alter database recover managed standby database using current logfile disconnect * ERROR at line 1: ORA-01153: an incompatible media recovery is active Cause This indicates a currently running media recovery process. Action sys@standby> alter database recover managed standby database cancel; sys@standby> alter database recover managed standby database using current logfile disconnect; Note When shutting down physical standby database, firstly turn off media recovery process. Otherwise the next time when starting up redo apply again, you will encounter error ORA-01153.

This error usually caused when database crashed unfortunately or we shutdown database with the help of database shutdown command as: shutdown abort, startup force mount or shutdown abort. These types of command will required instance recovery in next startup. In short it need clean database startup.

 

SQL> alter database archivelog;
alter database archivelog
*
ERROR at line 1:
ORA-00265: instance recovery required, cannot set ARCHIVELOG mode

Reason :

ORA-00265: instance recovery required, cannot set ARCHIVELOG mode
 *Cause:  The database either crashed or was shutdown with the ABORT
          option. Media recovery cannot be enabled because the online
          logs may not be sufficient to recover the current datafiles.
 *Action: Open the database and then enter the SHUTDOWN command with the
          NORMAL or IMMEDIATE option.


ORA-00265: Instance recovery required cannot set ARCHIVELOG mode,ORA-00265,Instance recovery required, cannot set ARCHIVELOG mode,ARCHIVELOG mode,ARCHIVELOG Error,Oracle DBA,Oracle Database,Oracle Archive issue,Oracle Ora,


Solution:

SQL> select status from v$instance;
STATUS
————————————
MOUNTED

SQL> alter database open;
Database altered.

SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount;
ORACLE instance started.
Total System Global Area 293601280 bytes
Fixed Size 1248600 bytes
Variable Size 88081064 bytes
Database Buffers 197132288 bytes
Redo Buffers 7139328 bytes
Database mounted.
SQL> alter database archivelog;
Database altered.
SQL> alter database open;
Database altered.
Share
read more no comments

How to check Oracle EBS/APPS Version

May 22nd, 2016, posted in Oracle Queries
Share

To check Oracle EBS/APPS Version

Run following SQL from apps user :Problem sys@standby> startup mount; ORACLE instance started. Total System Global Area 835104768 bytes Fixed Size 2217952 bytes Variable Size 490735648 bytes Database Buffers 335544320 bytes Redo Buffers 6606848 bytes Database mounted. sys@standby> alter database recover managed standby database using current logfile disconnect; alter database recover managed standby database using current logfile disconnect * ERROR at line 1: ORA-01153: an incompatible media recovery is active Cause This indicates a currently running media recovery process. Action sys@standby> alter database recover managed standby database cancel; sys@standby> alter database recover managed standby database using current logfile disconnect; Note When shutting down physical standby database, firstly turn off media recovery process. Otherwise the next time when starting up redo apply again, you will encounter error ORA-01153.

select RELEASE_NAME from fnd_product_groups;

You should see output like :

RELEASE_NAME
-----------------------
11.5.10.2
Share
read more no comments

ORA-06502 : PL/SQL: numeric or valur error: Character string buffer too small

May 1st, 2016, posted in Oracle Queries
Share
ORA-06502 ,Ora Error,Oracle Error,numeric or value error,Oracle numeric or value error,Oracle DBA,Oracle Developer,Oracle Solution,Solution for ORA-06502,Character string buffer too small,Oracle numeric,Oracle value error,ORA-06502 : PL/SQL
Problem
ORA-06502: PL/SQL: numeric or value error: character string buffer too small
ORA-06512: at line 23

 

Cause
a varchar2(2000);


Action

FIXED by changing how I declared the variable “a” to:

a varchar2(9000);








Note








Change the declared variable according to your requirements.


 








 


Share


				

					read more
					no comments				

			




					

				
Steps To Configure Archive Log Mode in Oracle Database 11g

				April 24th, 2016, posted in Oracle Queries
				
Share
Mode of Logging


There are two types of logging modes in Oracle Database :


ARCHIVELOG : 

In this type of logging whatever oracle writes in a redo log file related to transactions in database, saved to another location after a log file has been filled . This location is called Archive location. if database is in Archive log mode then in case of any disaster, we can recover our database upto the last commit and user don’t have to reenter their data. Until a redo log file is not written to the Archive location it cannot be reused by oracle to write redo related data.


NOARCHIVELOG :

In this type of logging whatever oracle writes in a redo log file related to transactions in database must be overwritten when all the log files have been filled. In this type of logging we can recover our database upto the last consistent backup we have with us, after that users have to reenter their data.


How to check log mode in Oracle Database 10g/11g :


[cognos@rac1 u02]$ sqlplus
 
 SQL*Plus: Release 11.2.0.1.0 Production on Thu Oct 25 23:03:44 2012
 
 Copyright (c) 1982, 2009, Oracle.  All rights reserved.
 
 Enter user-name: /as sysdba
 
 Connected to:
 Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
 With the Partitioning, Automatic Storage Management, OLAP, Data Mining
 and Real Application Testing options
 
 SQL> archive log list
 Database log mode              No Archive Mode
 Automatic archival             Disabled
 Archive destination            /backup/orcl/
 Oldest online log sequence     1
 Current log sequence           1



 SQL> select name,log_mode from v$database;
 NAME      LOG_MODE
 --------- -    -----------
 ORCL      NOARCHIVELOG
 
 Currently the ORCL database is in NOARCHIVELOG mode


To change the Oracle database in ARCHIVELOG mode. Below mentioned steps :


1. If needed set the archive log destination where you want to save your archive logs whether to a single location or to multiple location. If this is not set then Oracle save archive log files in DB_RECOVERY_FILE_DEST location if set. If you have not set your DB_RECOVERY_FILE_DEST location then you have to set your archive location before changing your database to ARCHIVELOG mode.


SQL> alter system set log_archive_dest_1='LOCATION=/u02/archive' scope=spfile;
System altered.
 
Note -- To change this parameter while database is open, your database has to run with SPFILE, if running through PFILE then shut down your database and make changes in your PFILE and then start the database in MOUNT mode using that changed PFILE




2. After this you need to shut down your database and start again in MOUNT mode






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


SQL> startup mount;
ORACLE instance started.


 


Total System Global Area 1025298432 bytes
Fixed Size                  1341000 bytes
Variable Size             322963896 bytes
Database Buffers          696254464 bytes
Redo Buffers                4739072 bytes
Database mounted.


SQL> alter database archivelog;
Database altered.


SQL> alter database open;


Database altered.


SQL> archive log list




Database log mode              Archive Mode
 Automatic archival             Enabled
 Archive destination            /u02/archive
 Oldest online log sequence     1
 Next log sequence to archive   1
 Current log sequence           1


SQL> select name,log_mode from v$database;

NAME      LOG_MODE
---------      ------------
ORCL      ARCHIVELOG


Database changed to ARCHIVELOG mode.


 


 


 Note :- After you changed your database to ARCHIVELOG mode, take a backup of your database immediately because in recovery scenarios you can recover your database from the last backup taken in this mode.  #############################################################################


 To change the Oracle database in NOARCHIVELOG mode. Below mentioned steps:


 


 1.Shutdown your running database.



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






2. Start your database in MOUNT mode.




SQL> startup mount
ORACLE instance started.




Total System Global Area 1025298432 bytes
Fixed Size                  1341000 bytes
Variable Size             322963896 bytes
Database Buffers          696254464 bytes
Redo Buffers                4739072 bytes
Database mounted.




SQL> alter database noarchivelog;
Database altered.




SQL> alter database open;
Database altered.




SQL> archive log list
Database log mode              No Archive Mode
Automatic archival             Disabled
Archive destination            /u02/archive
Oldest online log sequence     1
Current log sequence           1




SQL> select name,log_mode from v$database;


NAME      LOG_MODE
---------      ------------
ORCL      NOARCHIVELOG






Database changed to NOARCHIVELOG mode !!




Share


				

					read more
					no comments				

			




		
		

			
« Older Entries

			
Newer Entries »