How to find what Shell being used on the server

 

siva@mydbsrvr1:/home/oracle $ echo $SHELL /usr/bin/ksh

You can find out what shell you are using by the command: echo $SHELL

Environment Variables in Unix
By prefixing $ to the variable name, you can evaluate it in any command

How to list hidden files in Unix

On ls command use the –a option.
-a Shows you all files, even files that are hidden (these files begin with a dot.)

ls -a

ORA-06512: at "DBSNMP.BSLN_INTERNAL", line 2073

 

Error Message on Trace file

*** SERVICE NAME:(SYS$USERS) 2011-07-24 00:00:18.201 *** MODULE NAME:(DBMS_SCHEDULER) 2011-07-24 00:00:18.201 *** ACTION NAME:(BSLN_MAINTAIN_STATS_JOB) 2011-07-24 00:00:18.201 ORA-12012: error on auto execute of job 11762 ORA-06502: PL/SQL: numeric or value error ORA-06512: at "DBSNMP.BSLN_INTERNAL", line 2073 ORA-06512: at line 1

Cause:

Table DBSNMP.BSLN_BASELINES contains inconsistent information. In this case, after database cloning, existing records in table "DBSNMP.BSLN_BASELINES" conflict with new baseline information inserted in the database.

The DBSNMP user needs to be dropped and re-created.

Solution:

SQL> @?/rdbms/admin/catnsnmp.sql -- drop user SQL> @?/rdbms/admin/catsnmp.sql -- create user

Reference: Oracle ID 736627.1

Flash Recovery Area – Size determination

Ideally, the flash recovery area should be large enough to hold a copy of all of your

1. data files
2. control files,
3. the online redo logs
4. the archived redo log files

needed to recover your database using the data file backups kept under your retention policy.
Setting the size of DB_FILE_RECOVERY_DEST_SIZE must be based on following factors.
1) your flash back retention target,
2) what all files you are storing in flashback and
3) if that includes backup then the retention policy for them or how often you move them to tape

Oracle Internal Space management mechanism for Falshback recovery area itself is designed in such way that if you define your db_recovery_file_dest_size and DB_FLASHBACK_RETENTION_TARGET at a optimal value, you wont need any further administration or management.

If a Flash Recovery Area is configured, then the database uses an internal algorithm to delete files from the Flash Recovery Area that are no longer needed because they are redundant, orphaned, and so forth. The backups with status OBSOLETE form a subset of the files deemed eligible for deletion by the disk quota rules.

When space is required in the Flash Recovery Area, then the following files are deleted:
a) Any backups which have become obsolete as per the retention policy.
b) Any files in the Flash Recovery Area which has been already backed up
to a tertiary device such as tape.
c) Flashback logs may be deleted from the Flash Recovery Area to make space available for other required files.

How to delete control file

 

Verify the file specification information for the current control file(s): SQL> show parameters control_files

If your are using pfile, change CONTROL_FILES to point to the remaining control files. Make sure that the full file specification is used. vi $ORACLE_HOME/dbs/init<SID>.ora. CONTROL_FILES = (/oradata/<sid>/data1/control_01.ctl, /oradata/<sid>/data3/control_02.ctl) If you are using spfile, you may modify the CONTROL_FILES parameter using the ALTER SYSTEM SET command. SQL> alter system set control_files = /oradata/<sid>/data1/control_01.ctl, /oradata/<sid>/data3/control_02.ctl scope=spfile;

Shutdown and startup the database.

Verify the file specification information for the current control file(s): SQL> show parameters control_files If desired, delete the control file from the O/S.

ORA-1652: unable to extend temp segment by 128 in tablespace TEMP

Error:
ORA-1652: unable to extend temp segment by 128 in tablespace TEMP Fri Jul 11 01:09:12 2010

What does that error means:
This error is fairly self explanatory - we cannot get enough space for a temporary segment. The size reported in the error message is the number of contiguous free Oracle blocks that cannot be found in the listed tablespace.

How to approach the ORA-1652 error

There are two views in the database that help keep track of the free space in the temporary tablespace: v$sort_usage and v$tempseg_usage (from 9i onwards).

Check the status of the sort segment utilization :

select TABLESPACE_NAME,TOTAL_BLOCKS,USED_BLOCKS,FREE_BLOCKS from v$sort_segment; TABLESPACE_NAME                 TOTAL_BLOCKS USED_BLOCKS FREE_BLOCKS ------------------------------- ------------ ----------- ----------- TEMP                                 1310592           0     1310592

If USED_BLOCKS = TOTAL_BLOCKS, find which user and statement is using the temporary sort segment by following;

How Do You Find Who And What SQL Is Using Temp Segments

SELECT a.username, a.sid, a.serial#, a.osuser, b.tablespace, b.blocks, c.sql_text FROM v$session a, v$tempseg_usage b, v$sqlarea c WHERE a.saddr = b.session_addr AND c.address= a.sql_address AND c.hash_value = a.sql_hash_value ORDER BY b.tablespace, b.blocks;

Resolution

There are two ways of solving this error:

1. Add more tempfiles, increase the size of the current ones or enable auto extend and the most important:

2. Tune the queries/statements so that the sort operations are done in memory and not on the disk.

ORA-21779: duration not active

SMON generated the following errors in the alert log file :

SMON: following errors trapped and ignored:
ORA-21779: duration not active

ORA-06512: at line 1
Drop transient type:  SYSTPfQMEpjI7QJ7gQ6wVAuBAng==ÿ��

As per Oracle : SMON: Following Errors Trapped And Ignored ORA-21779 [ID 988663.1]

Cause of the error:
This error is caused by SMON not able to clean up some transient types and this problem has been reported a few times in Oracle10gR2

Solution

Other then producing large trace files,this error has no impact,SMON is erroring whilst evaluating a SYSTEM trigger whilst executing cleanup of TYPEs which are no longer needed,so currently we have the following options:

Option 1 --Bounce the DB and see if the error is persistent
OR
Option 2--Also Dev in similar bugs suggested to set the following event at system level to delay the cleanup of these types:

alter system set events '22834 trace name context forever, level 1';

This do not affect database functionality and the only problem is the the alert.log is filling up with many error messages (also after restarting the instance the error should not occur again).

The command "alter system flush shared_pool" could also resolve the problem.

As transient types are created when compiling cursors into memory then it looks like a good idea to execute"alter system flush shared_pool" as this cannot create any problems.

Option 3: Drop the transient type

The existence of the Transient type can be verified by query;

SELECT * FROM dba_types WHERE type_name LIKE 'SYS%==' AND length(type_name) = 29

 

select 'drop type "'||s.username||'"."'||o.name||'" FORCE;' from obj$ o, type$ t, dba_users s where o.oid$ = t.tvoid and bitand(t.properties,8388608) = 8388608 and (sysdate-o.ctime) > 0.0007 and o.owner#=s.user_id

DROP TYPE "TRACS_USER"."SYSTPZd71k+XjoyfgQG8KQW8u9g=="

What is this transient type?

How was it created?

Why can't SMON drop it while I, a DBA user, can?

When selecting from a table function which has return type ANYDATASET oracle creates dynamically a type object which matches the actual return type. and sql will cast it automatically to this actual return type, no ANYDATASET output is returned by the select. this mechanism is introduced with oracle 10 in general the smon should cleanup those types.