By Sriram Sanka

  • $riram $anka


    The experiences, Test cases, views, and opinions etc expressed in this website are my own and does not reflect the views or opinions of my employer. This site is independent of and does not represent Oracle Corporation in any way. Oracle does not officially sponsor, approve, or endorse this site or its content.Product and company names mentioned in this website may be the trademarks of their respective owners.

  • Likes So far

    • 93,340 hits
  • Enter your email address to follow this blog and receive notifications of new posts by email.

Error Opening 12.2.6 EBS URL

Posted by Sriram Sanka on May 22, 2017


 

There was an error while Re-Starting Our Newly Configured Oracle R12.2.6 Application Instance after a Hard reboot. I was unable to start some of the Server Components like oacore, oafm and forms.   When I tried to start them Manually, There was No error and everything seems to be started Successfully, But I was unable to access the URL with Below Error.

 

 

” Error 404–Not Found From RFC 2068 Hypertext Transfer Protocol — HTTP/1.1: 10.4.5 404 Not Found The server has not found anything matching the Request-URI. No indication is given of whether the condition is temporary or permanent. If the server does not wish to make this information available to the client, the status code 403 (Forbidden) can be used instead. The 410 (Gone) status code SHOULD be used if the server knows, through some internally configurable mechanism, that an old resource is permanently unavailable and has no forwarding address. ”

This  is due to the failure of  oafm_server,Forms_server and oacore_server using EBS Startup scripts .Starting Using Web Logic Server Deployments as  below endup with Java Null Pointer Issues with state as”ADMIN”

“<Server ‘forms_server1’ in cluster ‘forms_cluster1′ is being brought up in administration state due to failed deployments.>

“(self-tuning)’> <<WLS Kernel>> <> <> <1417622780708> <BEA-149205> <Failed to initialize the application ‘oafm [Version=1.0.0]’ due to error java.lang.NullPointerException.
java.lang.NullPointerException
at java.io.File.<init>(File.java:251)
at oracle.apps.ad.util.AppStartupPropertyListener.preStart(AppStartupPropertyListener.java:89)
at weblogic.application.internal.flow.BaseLifecycleFlow$PreStartAction.run(BaseLifecycleFlow.java:290) ”

 

 

 

 

This is Due to the recent Change in Hosts file. My Database Lister Started with an Alias Name defined in the Host file and these Server Components were unable to start/connect to the Actual Host Defined.   Changing the Host Entry and restart of these Components  resolved the issue.

For More info Refer Below Oracle Metalink Document

After Cloning Unable To Start The Managed Servers In 12.2.4 (Doc ID 1952853.1)

Posted in Uncategorized | 3 Comments »

ORA-600:[kpdbModAdminPasswdInRoot: Not CDB] Oracle v 12.1.0.1

Posted by Sriram Sanka on April 22, 2016


While Attempting to use the SQLPLUS PASSWORD command to change password of an Oracle-supplied user in a non-CDB can result ORA-600 as below.

ora-600

There is a patch available for Linux Platform. Oracle Meta link Doc ID 1583225.1 has more info on the Patch.

 

Posted in Uncategorized | Tagged: | 2 Comments »

Mega Oracle Walk-In Drive on 17th May

Posted by Sriram Sanka on May 9, 2014


http://www.careers.tcs.com/CareersDesign/Jsps/OracleDrive/AboutTCS.html

 

OracleDrive17May2014-WalkinMicrositeBanner

Posted in Uncategorized | Leave a Comment »

ORA-00600: Internal error code, arguments: [kcratr_nab_less_than_odr]

Posted by Sriram Sanka on March 19, 2014


Today,  I had an Issue with My Oracle DB V 11.2.0.1  and It was Corrupted due to unexpected power failures More than 10 times 😉 !.

As Its a Test DB, I don`t have  regular backups.  It was an Internal Error with ORA-00600: internal error code, arguments: [kcratr_nab_less_than_odr]   As I don`t have complete backup to recover , I can recover using  ICR ( Incomplete recovery)

image1 image2

image3

 

Thanks for your time , Happy reading & Have a Good day 😉

Posted in Oracle Server Administration | Tagged: | 13 Comments »

HP Vertica – an analytic database management software – Installation

Posted by Sriram Sanka on November 5, 2013


vertica-hp-platform

The grid-based, column-oriented, Vertica Analytics Platform is designed to manage large, fast-growing volumes of data and provide very fast query performance when used for data warehouses and other query-intensive applications. The product claims to drastically improve query performance over traditional relational database systems, provide high-availability, and petabyte scalability on commodity enterprise servers.

Its design features include:

  • Column-oriented storage organization, which increases performance of sequential record access at the expense of common transactional operations such as single record retrieval, updates, and deletes.[6]
  • Standard SQL interface with many analytics capabilities built-in, such as time series gap filing/interpolation, event-based windowing and sessionization, pattern matching, event series joins, statistical computation (e.g., regression analysis), and geospatial analysis.
  • Out-of-place updates and hybrid storage organization, which increase the performance of queries, insertions, and loads, but at the expense of updates and deletes.
  • Compression, which reduces storage costs and I/O bandwidth. High compression is possible because columns of homogeneous datatype are stored together and because updates to the main store are batched.[7]
  • Shared nothing architecture, which reduces system contention for shared resources and allows gradual degradation of performance in the face of hardware failure.
  • Easy to use and maintain through automated data replication, server recovery, query optimization, and storage optimization.
  • Support for standard programming interfaces ODBCJDBC, and ADO.NET.

Key Features of the Vertica Analytics Platform

Today ..I am going to install  HP vertica  On one of my test server……

A proper & complete Installation guide can be found @ https://my.vertica.com/docs/6.1.x/PDF/HP_Vertica_6.1.x_InstallGuide.pdf

Lets Download and install vertica from https://my.vertica.com  on  RHEL5 64 Bit

[root@sriram ~]# rpm – iv /media/20131105_1301/vertica-6.1.3-0.x86_64.RHEL5.rpm
Preparing packages for installation…
vertica-6.1.3-0

Vertica Analytic Database V6.1.3-0 successfully installed on host sriram.localdomain

———————————————————————————-
Important Information
———————————————————————————-
If you are upgrading from a previous version, you must backup your database before
continuing with this install. After restarting your database, you will be unable
to revert to a previous version of the software.
———————————————————————————-

To download the latest Vertica documentation in zip or tar format please visit the
myvertica web site.

To complete installation and configuration of the cluster,
run: /opt/vertica/sbin/install_vertica

[root@sriram ~]# /opt/vertica/sbin/install_vertica

Vertica Analytic Database 6.1.3-0 Installation Tool
Upgrading admintools meta data format..
scanning /opt/vertica/config/users
WARNING: No hostname list provided. Installing to localhost
Starting installation tasks…
Getting system information for cluster (this may take a while)….
backing up admintools.conf on 127.0.0.1
Default shell on nodes:
127.0.0.1 /bin/bash
Checking/fixing OS parameters…..

Setting vm.min_free_kbytes to 51200 …
Info! The maximum number of open file descriptors is less than 65536
Setting open filehandle limit to 65536 …
Info! The session setting of pam_limits.so is not set in /etc/pam.d/su
Setting session of pam_limits.so in /etc/pam.d/su …
Info! Parameter vm.max_map_count is less than 253086
Setting vm.max_map_count to 253086 …
Detected cpufreq module loaded on 127.0.0.1
CPU frequency scaling is enabled. This may adversely affect the performance of your database.
Vertica recommends that cpu frequency scaling be turned off or set to ‘performance’

Creating/Checking Vertica DBA group

Creating/Checking Vertica DBA user

Password for dbadmin:
Installing/Repairing SSH keys for dbadmin

Creating Vertica Data Directory…

Updating spread configuration…
Verifying spread configuration on whole cluster.
Creating node node0001 definition for host 127.0.0.1
… Done
Error Monitor 0 errors 2 warnings
Installation completed with warnings.
Installation complete.

To create a database:
1. Logout and login as dbadmin.**
2. Run /opt/vertica/bin/adminTools as dbadmin
3. Select Create Database from the Configuration Menu

** The installation modified the group privileges for dbadmin.
If you used sudo to install vertica as dbadmin, you will
need to logout and login again before the privileges are applied.

[root@sriram ~]#

To create a database:
1. Logout and login as dbadmin.**
2. Run /opt/vertica/bin/adminTools as dbadmin
3. Select Create Database from the Configuration Menu

Now login as dbadmin and accept the license agreement ! Once after that now a window  will appear, using which  you can create & configure Vertica DB.

Image

After Database creation you can see the below Interactive window for Regular operations !

vertica_started

vertica_insert

You can See the documentation Guide @ http://www.vertica.com/documentation/documentation-6-1/

Thanks for reading this ! 

Sriram Sanka|Sr.Oracle DBA

Posted in Vertica | 10 Comments »

Oracle Database 12c Interactive Quick Reference Guide

Posted by Sriram Sanka on August 8, 2013


Downloadable Interactive Quick Reference for Oracle Database 12c available @

http://www.oracle.com/go/?&Src=7875565&Act=4&pcode=WWOU13045613MPP009

 

12c__arch_diag

Posted in Uncategorized | 5 Comments »

ORA-07445: exception encountered: core dump [kttuser0()+272] [SIGFPE] [Integer divide by zero] [0x8949718] [] []

Posted by Sriram Sanka on February 18, 2013


Today, we were encountered with this internal error on One of our test environment.

On Our Database ,we have 2 temporary table-spaces with a Temporary table space group.
While we are performing Data pump Import (IMPDP) into another machine , The job was failed multiple times with following error on screen ….
Screen Error :

Processing object type DATABASE_EXPORT/SCHEMA/TYPE/TYPE_SPEC
ORA-39014: One or more workers have prematurely exited.
ORA-39029: worker 1 with process name “DW01” prematurely terminated
ORA-31672: Worker process DW01 died unexpectedly.

When I verified for the cause, on alert log,  found an Internal error only after Temporary tablespace creation ..

Alert log Content :

CREATE TEMPORARY TABLESPACE “TEMP_TWO” TEMPFILE ‘/u01/app/oracle/oracle/product/10.2.0/oradata/*****/temp02.dbf’SIZE 844103680 AUTOEXTEND ON NEXT 8192 MAXSIZE 32767M TABLESPACE GROUP TBS_GROUP EXTENT MANAGEMENT LOCAL UNIFORM SIZE 1048576

ORA-1119 signalled during CREATE TEMPORARY TABLESPACE “TEMP_TWO” TEMPFILE ‘/u01/app/oracle/oracle/product/10.2.0/oradata/********/temp02.dbf’ SIZE 844103680 AUTOEXTEND ON NEXT 8192 MAXSIZE 32767M TABLESPACE GROUP TBS_GROUP EXTENT MANAGEMENT LOCAL UNIFORM SIZE 1048576 …

Sun Feb 17 13:18:26 PST 2013

Thread 1 advanced to log sequence 47 (LGWR switch)

Current log# 2 seq# 47 mem# 0: /u01/app/oracle/*****/*****/redo02.log

Sun Feb 17 13:18:32 PST 2013
Errors in file /u01/app/oracle/******/admin/******/bdump/*****_dw01_24538.trc:

ORA-07445: exception encountered: core dump [kttuser0()+272] [SIGFPE] [Integer divide by zero] [0x8949718] [] []

Creating the temp tablespaces manually before IMPDP process will avoid such errors.

For More info please read metalink document ID 1259393.1

Posted in Uncategorized | 2 Comments »

ORA-27102: out of memory” While Creating a Database

Posted by Sriram Sanka on September 24, 2012


Today ,While Installing Oracle V 10g Using DBCA On a 32 bit Linux machine with 16 GB of RAM,(allocated 5GB as SGA ) We were encountered with this Error and unable to go further.

[oracle@db ~]$ oerr ORA 27102
27102, 00000, “out of memory”
// *Cause: Out of memory
// *Action: Consult the trace file for details
[oracle@db ~]$

 

This is Because of  On 32 Bit version,It cannot allocate more than 4 GB of Memory  to a process Directly.For this either we need to upgrade to 64 bit and start the Installation or decrease the Memory total value.

So we decreased the total memory size to 2.5 GB and It went successful without any issues.

Read Oracle Meta link Document ID 430492.1  to get  More Information on this.

 

Happy reading 😉

 

 

Posted in Oracle Server Administration | Tagged: | 6 Comments »

Microsoft__SQL_Server__2012_Pocket_Consultant By William R. Stanek

Posted by Sriram Sanka on May 8, 2012


Image

Well written and Well Organised  resource for DB Admins By William R. Stanek

 This Book really suits for 

  • Current SQL Server database administrators.
  • Accomplished users who have some administrator responsibilities.
  •  Administrators migrating to SQL Server 2012 from previous versions.
  •  Administrators transitioning from other database architectures.

I like the way author Presented the topics in a step-by-step manner and well categorized topics.

  •  Managing Your SQL Servers 
  •  Managing SQL Server Services and Clients 
  •  Implementing Policy-Based Management 
  •  Configuring and Tuning Your SQL Servers 
  •  Tuning and Linking Your SQL Servers 
  •  Database Administration Essentials
  •  Implementing SQL Server 2012 Security 
  •  Manipulating Schemas, Tables,and Views 
  •  Using Indexes, Constraints, and Partitions 
  •  Automating and Maintaining SQL Server 2012 
  •  SQL Server 2012 Backup and Recovery 
  •  SQL Server 2012 Profiling and Monitoring
 
 

Posted in Books | Leave a Comment »

SCN – What, why, and how?

Posted by Sriram Sanka on January 20, 2012


Here is a good article about SCN see the link below

SCN – What, why, and how?

Posted in Oracle Server Administration | Tagged: | Leave a Comment »

Article From Infoworld about Oracle Code flaw

Posted by Sriram Sanka on January 19, 2012


Yesterday, there was an interesting article was posted/published On InfoWorld  By .

http://www.infoworld.com/d/security/fundamental-oracle-flaw-revealed-184163
http://www.infoworld.com/t/data-management/calling-all-oracle-customers-184103?source=fssr

Oracle has published this On Its MOS Bug 12371955  Backup task can cause increased SCN growth rate leading to ORA-600 [2252] errors with Document ID [ID 12371955.8] and Old existing Document 253977.1 will also give some information on it.

for this to be fixed , One should apply the latest CPU patch released on 18-Jan-2012 by Oracle.

Oracle has published an article on MOS yesterday: note 1376995.1 with an associated script “scnhealthcheck.sql” you can find and download in note 1393363.1.which are very helpful.

Posted in Uncategorized | Tagged: , , | Leave a Comment »

SQL and Relational Theory, 2nd Edition by C.J. Date

Posted by Sriram Sanka on January 19, 2012


C.J. Date, is an independent author, lecturer, researcher, and consultant, specializing in relational database technology. He is best known for his book An Introduction to Database Systems.This book does n`t deal with SQL statements .. But  is all about between SQL and the Relational concepts theory  in a mathematical way.How SQL departs from relational theory, the basic principles of relational theory etc…

This Book contains 12 Chapters .. you can find Exercises  at the end of each chapter.I like the Appendix section  of this Book.

Appendix A:  The Relational Model
Appendix B : SQL Departures from the Relational Model
Appendix C:  A Relational Approach to Missing Information
Appendix D:  A Tutorial D Grammar
Appendix E:  Summary of Recommendations
Appendix F: Answers to Exercises
Appendix G: Suggestions for Further Reading.
I recommend this book  for all  who  works with SQL.
you can find his books and videos @ http://www.oreillynet.com/pub/au/2136

Posted in Books | 1 Comment »

New My Oracle Support interface….Soon

Posted by Sriram Sanka on January 19, 2012


On 17-Jan-2012,Oracle Announced,That its going to upgrade  (New)  My Oracle Support User interface, which Oracle ADF.

For More details Please refer The New My Oracle Support User Interface [ID 1385682.1]

http://www.dba-village.com/village/dvp_news.NewsDetails?NewsIdA=3874

Posted in Uncategorized | Leave a Comment »

ORA-06544: PL/SQL: internal error, arguments: [ph2_cly out of bounds in PH2POP.], [0], [1240], [], [], [], [], []

Posted by Sriram Sanka on January 11, 2012


Recently we upgraded Our Development Database  from Oracle V10.2.0.1 to V10.2.0.5.
Today While I was  Compiling an Invalid procedure,

ORA-06544: PL/SQL: internal error, arguments: [ph2_cly out of
bounds in PH2POP.], [0], [1240], [], [], [], [], []
Encountered.

No clues available On “alert-log” file.
Verified with Oracle Support about it and found that its a Bug{Bug 10400244} on 10.2.0.5.
Oracle Meta-link Document [ID 1358114.1] Will give some clues about it.
Fixing errors other than this Internal error will resolved this issue.

Posted in Oracle Server Administration | Tagged: , , | 11 Comments »

“racgmain” Processes

Posted by Sriram Sanka on October 3, 2011


Some good Experience for Me today.Early in the Morning ……

One of Our RAC node  filled up with 100% CPU usage.As part of Investigation We found that this was because of  racgmain  Process.

It was filled-up with more than 250 racgmain process which are consuming  CPU a lot.Due this Our System got Very slow.

To avoid this, we should apply latest patch which is > 10.2.0.5 and CRS patch too.There was a temporary workaround for this ..you can see that in Metalink Doc ID 732086.1 and ID 6196746.8

 

Posted in Oracle Server Administration, Uncategorized | Leave a Comment »

Protected:

Posted by Sriram Sanka on July 29, 2011


This content is password protected. To view it please enter your password below:

Posted in Uncategorized | Enter your password to view comments.

How not to Play With Oracle Dual-Magical Dual

Posted by Sriram Sanka on July 18, 2011


Here is an Interesting topic about FAST DUAL and Full Table Scan on Dual table with some example about how not to play with Oracle Data Dictionary Objects.

http://www.orafaq.com/forum/mv/msg/173068/515910/136607/#msg_515910

Happy reading….

Sriram 🙂

Posted in Uncategorized | 2 Comments »

Password Hash In Oracle 11g

Posted by Sriram Sanka on June 7, 2011


Let `s discuss about The Changes Of  DBA_USERS Especially In PASSWORD Column In Oracle 11g and 10g.

DBA_USERS   Gives Us Information about all users of the database.

And it contains password hash value In its PASSWORD Column

ind>  col TABLE_NAME format a10
ind> col COLUMN_NAME format a10
ind> col COMMENTS format a25
ind>  select TABLE_NAME,COLUMN_NAME, COMMENTS
  2   from dict_columns
  3   where TABLE_NAME='DBA_USERS' and COLUMN_NAME='PASSWORD';

TABLE_NAME COLUMN_NAM COMMENTS
---------- ---------- -------------------------
DBA_USERS  PASSWORD   Encrypted password

1 row selected.

Yes..It contains the encrypted value Based On Concatenation of Username and Password
This Is How/why two Users with same password can have different Encrypted hash values.

ind> create user satya identified by satya;

User created.

ind>  create user kalyani identified by satya;

User created.

ind> select username,password
  2  from dba_users
  3  where USERNAME in ('KALYANI','SATYA');

USERNAME                       PASSWORD
------------------------------ ------------------------------
KALYANI                        E81F7CB996A56BA9
SATYA                          218ED5615AAE5F6B

2 rows selected.

ind> drop user satya;

User dropped.

ind> drop user satyab;
drop user satyab
          *
ERROR at line 1:
ORA-01918: user 'SATYAB' does not exist

ind> create user satya identified by backpain;

User created.

ind> create user satyab identified by ackpain;

User created.

ind> select username,password
  2  from dba_users
  3  where username like 'SATYA%';

USERNAME                       PASSWORD
------------------------------ ------------------------------
SATYA                          458070F68E74206E
SATYAB                         458070F68E74206E

2 rows selected.

Here You can Observe that User SATYA and SATYAB with same hash value,
as the Concatenation of USERNAME and PASSWORD are Same.

But In Oracle 11g Oracle Wont store any password hash values in DBA_USERS.PASSWORD column.As Per the Document

http://download.oracle.com/docs/cd/E11882_01/server.112/e17110/statviews_5081.htm#REFRN23302

Password Column  is deprecated in favor of   the AUTHENTICATION_TYPE column

But You can get the password from “User$“.

SQL> create User a identified by "a";

User created.

SQL> create User b identified by "a";

User created.

SQL> select dbms_metadata.get_ddl('USER','A') from dual;

DBMS_METADATA.GET_DDL('USER','A')
--------------------------------------------------------------------------------

   CREATE USER "A" IDENTIFIED BY VALUES 'S:298EDEE1721E71B950D55CCB9ABA7EE5C596E
A6B0CCFF098E88889B98BD5;AFCC9478DFBF9029'
      DEFAULT TABLESPACE "USERS"
      TEMPORARY TABLESPACE "TEMP"

SQL> select dbms_metadata.get_ddl('USER','B') from dual;

DBMS_METADATA.GET_DDL('USER','B')
--------------------------------------------------------------------------------

   CREATE USER "B" IDENTIFIED BY VALUES 'S:25E85C1466288EE377681D131DF1920B33448
CD4108F8DDFC580A3315A39;9017AAA5BF2D9732'
      DEFAULT TABLESPACE "USERS"
      TEMPORARY TABLESPACE "TEMP"

SQL> select name, password, spare4 from sys.user$ where name ='A'
  2  ;

NAME                           PASSWORD
------------------------------ ------------------------------
SPARE4
--------------------------------------------------------------------------------
A                              AFCC9478DFBF9029
S:298EDEE1721E71B950D55CCB9ABA7EE5C596EA6B0CCFF098E88889B98BD5

SQL> select password,username from dba_users
  2  where length(username)=1;

PASSWORD                       USERNAME
------------------------------ ------------------------------
                               A
                               B

SQL>

You can Find My Post here at Orafaq.
http://www.orafaq.com/forum/mv/msg/171754/510127/136607/#msg_510127
For more reference
http://download.oracle.com/docs/cd/E11882_01/server.112/e17110/statviews_5081.htm#REFRN23302
Hope You enjoyed It 🙂
Sriram

Posted in Oracle Server Administration | 103 Comments »

ORA-00059: maximum number of DB_FILES exceeded

Posted by Sriram Sanka on May 27, 2011


This is all about the Relation B/W  MAXDATAFILES and DB_FILES In Oralce Database.

–This will Give Us db_files value. max no of data files that we can add.

select value from v$parameter where name = ‘db_files’;

This will give the MAXDATAFILES specified at Control file level while creating Database.

select records_total from v$controlfile_record_section where type = ‘DATAFILE’;

Lets Start with a small case. I have Created My database with DB_FILES   as 10.

C:\Windows\System32>sqlplus system/tejajun20

SQL*Plus: Release 11.2.0.1.0 Production on Fri May 27 20:20:19 2011

Copyright (c) 1982, 2010, Oracle.  All rights reserved.

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> select value from v$parameter where name = 'db_files';

VALUE
--------------------------------------------------------------------------------
10

SQL> select records_total from v$controlfile_record_section where type = 'DATAFILE';

RECORDS_TOTAL
-------------
          100

Let us have a look at the no of data files I have.

SQL> select file_name from dba_data_files;

FILE_NAME
--------------------------------------------------------------------------------
D:\APP1\SRIRAM\ORADATA\ORAFAQ\USERS01.DBF
D:\APP1\SRIRAM\ORADATA\ORAFAQ\UNDOTBS01.DBF
D:\APP1\SRIRAM\ORADATA\ORAFAQ\SYSAUX01.DBF
D:\APP1\SRIRAM\ORADATA\ORAFAQ\SYSTEM01.DBF
D:\APP1\SRIRAM\ORADATA\ORAFAQ\EXAMPLE01.DBF</pre>

Let me add some data files to users tables

SQL> alter tablespace users add datafile 'D:\APP1\SRIRAM\ORADATA\ORAFAQ\User2.dbf' size 100m ;

Tablespace altered.

SQL> alter tablespace users add datafile 'D:\APP1\SRIRAM\ORADATA\ORAFAQ\User3.dbf' size 100m ;

Tablespace altered.

SQL> alter tablespace users add datafile 'D:\APP1\SRIRAM\ORADATA\ORAFAQ\User4.dbf' size 100m ;

Tablespace altered.

SQL> select count(*) from v$datafile;

  COUNT(*)
----------
         8

SQL> alter tablespace users add datafile 'D:\APP1\SRIRAM\ORADATA\ORAFAQ\User5.dbf' size 100m ;

Tablespace altered.

SQL> alter tablespace users add datafile 'D:\APP1\SRIRAM\ORADATA\ORAFAQ\User6.dbf' size 100m ;

Tablespace altered.

SQL> select count(*) from v$datafile;

  COUNT(*)
----------
        10

SQL> select value from v$parameter where name = 'db_files';

VALUE
--------------------------------------------------------------------------------
10

SQL> select records_total from v$controlfile_record_section where type = 'DATAFILE';

RECORDS_TOTAL
-------------
          100

As I have already reached the max limit 10,Adding one more file will raise an Error.

SQL> alter tablespace users add datafile 'D:\APP1\SRIRAM\ORADATA\ORAFAQ\User7.dbf' size 100m ;
alter tablespace users add datafile 'D:\APP1\SRIRAM\ORADATA\ORAFAQ\User7.dbf' size 100m
*
ERROR at line 1:
ORA-00059: maximum number of DB_FILES exceeded

ORA-00059:

maximum number of DB_FILES exceeded
Cause: The value of the DB_FILES initialization parameter was exceeded.
Action: Increase the value of the DB_FILES parameter and warm start.

Lets Try to increase the value to a small number.

SQL> alter system set db_files=20 scope=spfile;

System altered.

SQL> conn sys as sysdba
Enter password:
Connected.
SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup
ORACLE instance started.

Total System Global Area  401743872 bytes
Fixed Size                  1374892 bytes
Variable Size             268436820 bytes
Database Buffers          125829120 bytes
Redo Buffers                6103040 bytes
Database mounted.
Database opened.
SQL> select value from v$parameter where name = 'db_files';

VALUE
--------------------------------------------------------------------------------
20

SQL> select records_total from v$controlfile_record_section where type = 'DATAFILE';

RECORDS_TOTAL
-------------
          100

SQL> alter tablespace users add datafile 'D:\APP1\SRIRAM\ORADATA\ORAFAQ\User7.dbf' size 100m ;

Tablespace altered.

SQL>

See now it allowing us to add datafiles.what about Exceeding MAXDATAFILES? (i.e more than 100 data files).

Lets do that And verify what `ll happen.


SQL> alter system set db_files=150 scope=spfile;

System altered.

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

Total System Global Area  401743872 bytes
Fixed Size                  1374892 bytes
Variable Size             268436820 bytes
Database Buffers          125829120 bytes
Redo Buffers                6103040 bytes
Database mounted.
Database opened.
SQL> select value from v$parameter where name = 'db_files';

VALUE
--------------------------------------------------------------------------------
150

SQL> select records_total from v$controlfile_record_section where type = 'DATAFILE';

RECORDS_TOTAL
-------------
          100

SQL> alter tablespace users add datafile 'D:\APP1\SRIRAM\ORADATA\ORAFAQ\User8.dbf' size 1m ;

Tablespace altered.

SQL> alter tablespace users add datafile 'D:\APP1\SRIRAM\ORADATA\ORAFAQ\User9.dbf' size 1m ;

Tablespace altered.

SQL> alter tablespace users add datafile 'D:\APP1\SRIRAM\ORADATA\ORAFAQ\User10.dbf' size 1m ;

Tablespace altered.

SQL> select count(*) from v$datafile;

  COUNT(*)
----------
        14
--- In another window I am adding datafiles upto the limit "100"
SQL> /

  COUNT(*)
----------
        48

SQL> /

  COUNT(*)
----------
        55

SQL> /

  COUNT(*)
----------
        82

SQL> /

  COUNT(*)
----------
        98

SQL> /

  COUNT(*)
----------
        98

SQL> /

  COUNT(*)
----------
        98

SQL> alter tablespace users add datafile 'D:\APP1\SRIRAM\ORADATA\ORAFAQ\User95.dbf' size 1m ;

Tablespace altered.

SQL> alter tablespace users add datafile 'D:\APP1\SRIRAM\ORADATA\ORAFAQ\User96.dbf' size 1m ;

Tablespace altered.

SQL>
SQL> select value from v$parameter where name = 'db_files';

VALUE
--------------------------------------------------------------------------------
150

SQL> select records_total from v$controlfile_record_section where type = 'DATAFILE';

RECORDS_TOTAL
-------------
          100

SQL> alter tablespace users add datafile 'D:\APP1\SRIRAM\ORADATA\ORAFAQ\User97.dbf' size 1m ;

Tablespace altered.

SQL> select value from v$parameter where name = 'db_files';

VALUE
--------------------------------------------------------------------------------
150

SQL> select records_total from v$controlfile_record_section where type = 'DATAFILE';

RECORDS_TOTAL
-------------
          200

----Observe it Automatically changed..

SQL>

As Per Oracle Documents :

Consider Possible Limitations When Adding Datafiles to a Tablespace:

  1. You can add datafiles to traditional smallfile tablespaces, subject to the following limitations:
  2. Operating systems often impose a limit on the number of files a process can open simultaneously. More datafiles cannot be created when the operating system limit of open files is reached.
  3. Operating systems impose limits on the number and size of datafiles.
  4. The database imposes a maximum limit on the number of datafiles for any Oracle Database opened by any instance. This limit is operating system specific.

You cannot exceed the number of datafiles specified by the DB_FILES initialization parameter.

When you issue CREATE DATABASE or CREATE CONTROLFILE statements, the MAXDATAFILES parameter specifies an initial size of the datafile portion of the control file. However,if you attempt to add a new file whose number is greater than MAXDATAFILES, but less than or equal to DB_FILES, the control file will expand automatically so that the datafiles section can accommodate more files.

DB_FILES specifies the maximum number of database files that can be opened for this database.The maximum valid value is the maximum number of files, subject to operating system constraint,that will ever be specified for the database, including files to be added by ADD DATAFILE statements.If you increase the value of DB_FILES, then you must shut down and restart all instances accessing the database before the new value can take effect. If you have a primary and standby database,then they should have the same value for this parameter.

For More Information Please Read:

http://download.oracle.com/docs/cd/B19306_01/server.102/b14231/dfiles.htm#sthref1343

http://download.oracle.com/docs/cd/B19306_01/server.102/b14231/dfiles.htm#sthref1343

Posted in Oracle Server Administration | 98 Comments »

Spell number In Indian Currency System Using Function (Number into words)

Posted by Sriram Sanka on May 20, 2011


As we all know We can convert number into words Using ‘Jsp’ format string in to_char function.

ind> select to_char(to_date(‘250′,’J’),’JSP’) from dual;

TO_CHAR(TO_DATE(‘
—————–
TWO HUNDRED FIFTY

1 row selected.

ind> select to_char(to_date(‘259′,’J’),’JSP’) from dual;

TO_CHAR(TO_DATE(‘259’,
———————-
TWO HUNDRED FIFTY-NINE

1 row selected.

ind> select to_char(to_date(‘9999′,’J’),’JSP’) from dual;

TO_CHAR(TO_DATE(‘9999′,’J’),’JSP’)
————————————–
NINE THOUSAND NINE HUNDRED NINETY-NINE

1 row selected.

But It has some limitations….See
ind> select to_char(to_date(‘250.25′,’J’),’JSP’) from dual;
select to_char(to_date(‘250.25′,’J’),’JSP’) from dual
*
ERROR at line 1:
ORA-01830: date format picture ends before converting entire input string

ind> select to_char(to_date(‘9999999999.99′,’J’),’JSP’) from dual;
select to_char(to_date(‘9999999999.99′,’J’),’JSP’) from dual
*
ERROR at line 1:
ORA-01854: julian date must be between 1 and 5373484

We can avoid this By creating one Function .
This is similar to Ask Tom Spell number

ind> select spell_number(‘9999999999.99’) from dual;

SPELL_NUMBER(‘9999999999.99’)
——————————————————————————–
nine billion nine hundred ninety-nine million nine hundred ninety-nine thousand
nine hundred ninety-nine

1 row selected.

ind> select into_rupees(‘9999999999.99’) from dual;

INTO_RUPEES(‘9999999999.99’)
——————————————————————————–
Nine Arab Ninety-Nine Crore Ninety-Nine Lakh Ninety-Nine Thousand Nine Hundred N
inety-Nine and Paise Ninety-Nine.

You can find this into_rupees script at Indian Rupees

Thank you 🙂

Posted in Functions In oracle | 7 Comments »

 
Tales From A Lazy Fat DBA

Its all about Databases & their performance, troubleshooting & much more .... ¯\_(ツ)_/¯

Thinking Out Loud

Michael T. Dinh, Oracle DBA

Notes On Oracle

by Mehmet Eser

Oracle Diagnostician

Performance troubleshooting as exact science

deveshdba

get sum oracle stuffs

Know-Oracle

Oracle Cloud ERP Blog by Team CLT

Data Warehousing with Oracle

Dani Schnider's Blog

ORASteps

Oracle DBA's Daily Work

DBAspaceblog.com

Welcome everyone!! The idea of this blog is to help the DBA in their daily tasks. Enjoy.

Anand's Data Stories

Learn. Share. Repeat.

Tanel Poder's blog: Core IT for geeks and pros

Oracle Performance Tuning, Troubleshooting, Internals

Yet Another OCM

Journey as an Oracle Certified Master

%d bloggers like this: