Tuesday, 18 August 2015

Unable to view Concurrent Requests log and output files in R12.2.4

The reason I say it is not a POPup issue because I have added the domain name in 'Always allow popups from' setting.
I also tried disabling the 'Popup blocker'.

As per metalink note 888932.1, checked the following 

Internet Explorer>Security Options>Tools>Internet Options> Security> Custome Level

1. Automatic Prompt for File Download- Should be Enable
2. File Download-Should be Enable

These options were already enabled.


Then I found the below solution to make it work.

1. Tool--> Internet Options --> Security --> Custom Level --> Dowloads --> Automatic prompting for file downloads --> Enable
2. Close the browser.
3. Open a new browser.
4. Retest the issue.


Check this profile values at site level
Navigation to chk : The System Administrator Responsibility >> PROFILE > SYSTEM
CONCURRENT: Report Access Level (this must be set to USER or RESPONSIBILITY)
RRA: Service Prefix (this should be BLANK)
RRA: Enabled (this must be set to YES)
VIEWER: Text (this should be BLANK to view with the default text viewer)
– Viewer: Text can Be set to “browser” to view with Netscape or IE

--Restar Concurrent Manager..


Steps to debug “Not able to view Concurrent request Output ”

When we try to view the concurrent request output or log files we would get the following Error:

” An error occurred while attempting to establish an Applications File
Server connection. There may be a network configuration problem, or
the TNS listener may not be running on <nodename> ”

1. Run a report, is there actually a Report Output and Log file being produced at
the Operating System level?
– Check the $APPLCSF/out and the $APPLCSF/log directories
for the Request_ID out and log file
– If there are no log and output file being produces there,
Check the permissions on those directories (APPLMGR should have write)
– If $APPLCSF is not set, check the $FND_TOP/log and out for output and logs

2. Confirm that there are 2 listener processes actively running for the Instance that
you cannot view reports on — 1 from 8.0.6 and from Database directory structures
– type the following:
ps -ef | grep tnslsnr
– If there is only 1 listener running from the Database directory structure
That is the Database Listener
The FNDFS listener needs to be started separately from the Database Listener
– To start the FNDFS Listener:
a. Log the Operating System Level as the APPLMGR account user
b. Set the environment for the $APPL_TOP & 8.0.6 $ORACLE_HOME (APPSORA.env)
c. Go to the $OAD_TOP/admin/scripts (Common_top area)
d. Run the following script:

adalnctl.sh start APPS_<SID name>

(you can confirm the FNDFS Listener name from viewing the
8.0.6 ORACLE_HOME/network/admin/listener.ora file)

3. Check the actual machine name for the Concurrent Processor Server
– type the following:
uname -n

4. Go to the Applications and check the CONCURRENT > MANAGER > ADMINISTER screen
in the System Administrator Responsibility
The INTERNAL MANAGER NODE NAME; is it the same as what was returned from question 4?
– If it is the same then continue with next step
– If it is not the same,
Shutdown Concurrent Managers and update the FND table:
a. Go to the $OAD_TOP/admin/scripts
b. adcmctl.sh apps/apps stop
c. Connect to SQL*PLUS apps/apps

d. SQL > select target_node
This may return the name of an old machine

set target_node='<newmachinename>’
where target_node='<oldmachinename>’;

f. SQL > commit;
g. SQL > exit
h. adcmctl.sh apps/apps start

5. In the 8.0.6 TNSNAMES.ora file, look at the FNDFS_<hostname> connection string entry.
Is the hostname value the same as is in questions 4?
– The Rapid Install creates 2 or sometimes 3 FNDFS entries in the TNSNAMES.ora
a. 1 with the FNDFS_<hostname>
b. 1 with the FNDFS_<SID>
c. 1 with the FNDFS_<hostname.domain>
– The one that is the correct entry is FNDFS_<hostname>
(hostname being what is returned from a “uname -n”)
– If this file does not contain the correct Hostname value
Make a backup of it and edit it to change the FNDFS entry.

6. Check the Network Connection for the FNDFS entry that was defined by the Rapid Install.
– At the Operating System Level
a. tnsping FNDFS_<hostname>
– This should return the Hostname name and Port information
identical to the FNDFS entry in the TNSNAMES.ora file

7. Check the Following PROFILE > SYSTEM Options in The System Administrator Responsibility

CONCURRENT: Report Access Level (this must be set to USER or RESPONSIBILITY)
RRA: Service Prefix (this should be BLANK)
RRA: Enabled (this must be set to YES)
VIEWER: Text (this should be BLANK to view with the default text viewer)
– Viewer: Text can Be set to “browser” to view with Netscape or IE

8. This step should be performed in a case where there might have been patches applied
and possibly the FNDFS executable was not Relinked, or it may be
missing from the file system or corrupted.

– Shutdown the FNDFS Listener
[applmgr@Test bin]$ cd /clone_15_Aug/apps/fs1/inst/apps/TEST_Test/admin/scripts
[applmgr@Test scripts]$ ./adalnctl.sh start APPS_TEST

– Rename or Move the Current FNDFS executable before relinking
a. $FND_TOP/bin

b. As APPLMGR account user
[applmgr@Test bin]$ pwd

[applmgr@Test bin]$ adrelink.sh force=y "fnd FNDFS"

– Start the FNDFS Listener

[applmgr@Test bin]$ cd /clone_15_Aug/apps/fs1/inst/apps/TEST_Test/admin/scripts
[applmgr@Test scripts]$ ./adalnctl.sh start APPS_TEST

Friday, 14 August 2015

Managing Control Files in 11g

How to Managing Control Files in 11g?

What Is a Control File?
Every Oracle Database has a control file, which is a small binary file that
records the physical structure of the database. The control file includes:

The database name
Names and locations of associated data files and redo log files
The timestamp of the database creation
The current log sequence number

Checkpoint information
The control file must be available for writing by the Oracle Database server whenever
the database is open. Without the control file, the database cannot be mounted and recovery is difficult.

The control file of an Oracle Database is created at the same time as the database.
By default, at least one copy of the control file is created during database creation.
On some operating systems the default is to create multiple copies. You should create two
or more copies of the control file during database creation. You can also create control files later,
if you lose control files or want to change particular settings in the control files.

Guidelines for Control Files
This section describes guidelines you can use to manage the control files for a database,
and contains the following topics:

Provide Filenames for the Control Files
Multiplex Control Files on Different Disks
Back Up Control Files
Manage the Size of Control Files
Provide Filenames for the Control Files
You specify control file names using the CONTROL_FILES initialization parameter in the database
initialization parameter file (see "Creating Initial Control Files"). The instance recognizes and
opens all the listed file during startup, and the instance writes to and maintains all listed control files during database operation.

If you do not specify files for CONTROL_FILES before database creation:

If you are not using Oracle Managed Files, then the database creates a control file and uses a
default filename. The default name is operating system specific.

If you are using Oracle Managed Files, then the initialization parameters you set to enable that
 feature determine the name and location of the control files, as described in Chapter 17, "Using Oracle Managed Files".

If you are using Oracle Automatic Storage Management (Oracle ASM), you can place incomplete
Oracle ASM filenames in the DB_CREATE_FILE_DEST and DB_RECOVERY_FILE_DEST initialization parameters.
 Oracle ASM then automatically creates control files in the appropriate places. See the sections "About Oracle ASM Filenames"
 and "Creating a Database That Uses Oracle ASM" in Oracle Automatic Storage Management Administrator's Guide for more information.

Multiplex Control Files on Different Disks
Every Oracle Database should have at least two control files, each stored on a different physical disk.
If a control file is damaged due to a disk failure, the associated instance must be shut down. Once the disk drive is repaired,
 the damaged control file can be restored using the intact copy of the control file from the other disk and the instance can be restarted.
In this case, no media recovery is required.

The behavior of multiplexed control files is this:

The database writes to all filenames listed for the initialization parameter CONTROL_FILES in the database initialization parameter file.

The database reads only the first file listed in the CONTROL_FILES parameter during database operation.

If any of the control files become unavailable during database operation, the instance becomes inoperable and should be aborted.

Oracle strongly recommends that your database has a minimum of two control files and that they are located on separate physical disks.
One way to multiplex control files is to store a control file copy on every disk drive that stores members of redo log groups,
 if the redo log is multiplexed. By storing control files in these locations,
 you minimize the risk that all control files and all groups of the redo log will be lost in a single disk failure.

Back Up Control Files
It is very important that you back up your control files. This is true initially, and every time you change the physical structure of your database.
 Such structural changes include:

Adding, dropping, or renaming data files

Adding or dropping a tablespace, or altering the read/write state of the tablespace

Adding or dropping redo log files or groups

The methods for backing up control files are discussed in "Backing Up Control Files".

Manage the Size of Control Files
The main determinants of the size of a control file are the values set for the MAXDATAFILES, MAXLOGFILES, MAXLOGMEMBERS, MAXLOGHISTORY,
 and MAXINSTANCES parameters in the CREATE DATABASE statement that created the associated database. Increasing the values of these parameters
increases the size of a control file of the associated database.

See Also:
Your operating system specific Oracle documentation contains more information about the maximum control file size.

Oracle Database SQL Language Reference for a description of the CREATE DATABASE statement

Creating Control Files
This section describes ways to create control files, and contains the following topics:

Creating Initial Control Files
Creating Additional Copies, Renaming, and Relocating Control Files
Creating New Control Files
Creating Initial Control Files
The initial control files of an Oracle Database are created when you issue the CREATE DATABASE statement. The names of the
 control files are specified by the CONTROL_FILES parameter in the initialization parameter file used during database creation.
The filenames specified in CONTROL_FILES should be fully specified and are operating system specific. The following is an example of
a CONTROL_FILES initialization parameter:

CONTROL_FILES = (/u01/oracle/prod/control01.ctl,
If files with the specified names currently exist at the time of database creation, you must specify the CONTROLFILE REUSE clause
in the CREATE DATABASE statement, or else an error occurs. Also, if the size of the old control file differs from the SIZE parameter
of the new one, you cannot use the REUSE clause.

The size of the control file changes between some releases of Oracle Database, as well as when the number of files specified in the
control file changes. Configuration parameters such as MAXLOGFILES, MAXLOGMEMBERS, MAXLOGHISTORY, MAXDATAFILES, and MAXINSTANCES affect control file size.

You can subsequently change the value of the CONTROL_FILES initialization parameter to add more control files or to change the names or locations of existing control files.

See Also:
Your operating system specific Oracle documentation contains more information about specifying control files.
Creating Additional Copies, Renaming, and Relocating Control Files
You can create an additional control file copy for multiplexing by copying an existing control file to a new location and adding the file name to the list of control files. Similarly, you rename an existing control file by copying the file to its new name or location, and changing the file name in the control file list. In both cases, to guarantee that control files do not change during the procedure, shut down the database before copying the control file.

To add a multiplexed copy of the current control file or to rename a control file:

Shut down the database.

Copy an existing control file to a new location, using operating system commands.

Edit the CONTROL_FILES parameter in the database initialization parameter file to add the new control file name,
or to change the existing control filename.

Restart the database.

Creating New Control Files
This section discusses when and how to create new control files.

When to Create New Control Files
It is necessary for you to create new control files in the following situations:

All control files for the database have been permanently damaged and you do not have a control file backup.

You want to change the database name.

For example, you would change a database name if it conflicted with another database name in a distributed environment.

You can change the database name and DBID (internal database identifier) using the DBNEWID utility.

See Oracle Database
Utilities for information about using this utility.
The compatibility level is set to a value that is earlier than 10.2.0, and you must make a change to an area of database
 configuration that relates to any of the following parameters from the CREATE DATABASE
If compatibility is 10.2.0 or later, you do not have to create new control files when you make such a change;
 the control files automatically expand, if necessary, to accommodate the new configuration information.

For example, assume that when you created the database or re-created the control files, you set MAXLOGFILES to 3.
 Suppose that now you want to add a fourth redo log file group to the database with the ALTER DATABASE command.
If compatibility is set to 10.2.0 or later, you can do so and the control files automatically expand to accommodate
the new log file information. However, with compatibility set earlier than 10.2.0, your ALTER DATABASE command would generate an error,
and you would have to first create new control files.

For information on compatibility level, see "About The COMPATIBLE Initialization Parameter".

You can create a new control file for a database using the CREATE CONTROLFILE statement.
 The following statement creates a new control file for the prod database (a database that formerly used a different database name):

   LOGFILE GROUP 1 ('/u01/oracle/prod/redo01_01.log',
           GROUP 2 ('/u01/oracle/prod/redo02_01.log',
           GROUP 3 ('/u01/oracle/prod/redo03_01.log',
   DATAFILE '/u01/oracle/prod/system01.dbf' SIZE 3M,
            '/u01/oracle/prod/rbs01.dbs' SIZE 5M,
            '/u01/oracle/prod/users01.dbs' SIZE 5M,
            '/u01/oracle/prod/temp01.dbs' SIZE 5M
The CREATE CONTROLFILE statement can potentially damage specified data files and redo log files.
 Omitting a filename can cause loss of the data in that file, or loss of access to the entire database.
Use caution when issuing this statement and be sure to follow the instructions in "Steps for Creating New Control Files".

If the database had forced logging enabled before creating the new control file,
and you want it to continue to be enabled, then you must specify the FORCE LOGGING
clause in the CREATE CONTROLFILE statement. See "Specifying FORCE LOGGING Mode".

See Also:
Oracle Database SQL Language Reference describes the complete syntax of the CREATE CONTROLFILE statement
Steps for Creating New Control Files
Complete the following steps to create a new control file.

Make a list of all data files and redo log files of the database.

If you follow recommendations for control file backups as discussed in "Backing Up Control Files" ,
 you will already have a list of data files and redo log files that reflect the current structure of the database.
However, if you have no such list, executing the following statements will produce one.

If you have no such lists and your control file has been damaged so that the database cannot be opened,
 try to locate all of the data files and redo log files that constitute the database.
Any files not specified in step 5 are not recoverable once a new control file has been created.
 Moreover, if you omit any of the files that comprise the SYSTEM tablespace, you might not be able to recover the database.

Shut down the database.

If the database is open, shut down the database normally if possible. Use the IMMEDIATE or ABORT clauses only as a last resort.

Back up all data files and redo log files of the database.

Start up a new instance, but do not mount or open the database:

Create a new control file for the database using the CREATE CONTROLFILE statement.

When creating a new control file, specify the RESETLOGS clause if you have lost any redo log groups in addition to control files.
 In this case, you will need to recover from the loss of the redo logs (step 8).
You must specify the RESETLOGS clause if you have renamed the database. Otherwise, select the NORESETLOGS clause.

Store a backup of the new control file on an offline storage device. See "Backing Up Control Files" for instructions for creating a backup.

Edit the CONTROL_FILES initialization parameter for the database to indicate all of the control files now
part of your database as created in step 5 (not including the backup control file). If you are renaming the database,
 edit the DB_NAME parameter in your instance parameter file to specify the new name.

Recover the database if necessary. If you are not recovering the database, skip to step 9.

If you are creating the control file as part of recovery, recover the database. If the new control file was created using the
NORESETLOGS clause (step 5), you can recover the database with complete, closed database recovery.

If the new control file was created using the RESETLOGS clause, you must specify USING BACKUP CONTROL FILE. If you have lost
online or archived redo logs or data files, use the procedures for recovering those files.

If you did not perform recovery, or you performed complete, closed database recovery in step 8, open the database normally.

If you specified RESETLOGS when creating the control file, use the ALTER DATABASE statement, indicating RESETLOGS.

The database is now open and available for use.

Troubleshooting After Creating Control Files
After issuing the CREATE CONTROLFILE statement, you may encounter some errors. This section describes the most common control file errors:

Checking for Missing or Extra Files
Handling Errors During CREATE CONTROLFIL

Checking for Missing or Extra Files
After creating a new control file and using it to open the database, check the alert log to see if the database has detected inconsistencies between the data dictionary and the control file, such as a data file in the data dictionary includes that the control file does not list.

If a data file exists in the data dictionary but not in the new control file, the database creates a placeholder entry in the control file under the name MISSINGnnnn, where nnnn is the file number in decimal. MISSINGnnnn is flagged in the control file as being offline and requiring media recovery.

If the actual data file corresponding to MISSINGnnnn is read-only or offline normal, then you can make the data file accessible by renaming MISSINGnnnn to the name of the actual data file. If MISSINGnnnn corresponds to a data file that was not read-only or offline normal,
then you cannot use the rename operation to make the data file accessible, because the data file requires media recovery that is precluded by the results of RESETLOGS. In this case, you must drop the tablespace containing the data file.

Conversely, if a data file listed in the control file is not present in the data dictionary, then the database removes references to it from the new control file. In both cases, the database includes an explanatory message in the alert log to let you know what was found.

Handling Errors During CREATE CONTROLFILE
If Oracle Database sends you an error (usually error ORA-01173, ORA-01176, ORA-01177, ORA-01215, or ORA-01216) when you attempt to
mount and open the database after creating a new control file, the most likely cause is that you omitted a file from the CREATE CONTROLFILE
statement or included one that should not have been listed. In this case, you should restore the files you backed up in

step 3 and repeat the procedure from step 4, using the correct filenames.

Backing Up Control Files
Use the ALTER DATABASE BACKUP CONTROLFILE statement to back up your control files. You have two options:

Back up the control file to a binary file (duplicate of existing control file) using the following statement:

ALTER DATABASE BACKUP CONTROLFILE TO '/oracle/backup/control.bkp';
Produce SQL statements that can later be used to re-create your control file:

This command writes a SQL script to a trace file where it can be captured and edited to reproduce the control file.
View the alert log to determine the name and location of the trace file.

See Also:
Oracle Database Backup and Recovery User's Guide for more information on backing up your control files

"Viewing the Alert Log"

Recovering a Control File Using a Current Copy
This section presents ways that you can recover your control file from a current backup or from a multiplexed copy.

Recovering from Control File Corruption Using a Control File Copy
This procedure assumes that one of the control files specified in the CONTROL_FILES parameter is corrupted, that the control file directory is still accessible, and that you have a multiplexed copy of the control file.

With the instance shut down, use an operating system command to overwrite the bad control file with a good copy:

% cp /u03/oracle/prod/control03.ctl  /u02/oracle/prod/control02.ctl
Start SQL*Plus and open the database:

Recovering from Permanent Media Failure Using a Control File Copy
This procedure assumes that one of the control files specified in the CONTROL_FILES parameter is inaccessible due to a permanent media failure and that you have a multiplexed copy of the control file.With the instance shut down, use an operating system command to copy the current copy of the control file to a new, accessible location:

% cp /u01/oracle/prod/control01.ctl  /u04/oracle/prod/control03.ctl
Edit the CONTROL_FILES parameter in the initialization parameter file to replace the bad location with the new location:

CONTROL_FILES = (/u01/oracle/prod/control01.ctl,
Start SQL*Plus and open the database:

If you have multiplexed control files, you can get the database started up quickly by editing the CONTROL_FILES initialization parameter.
Remove the bad control file from CONTROL_FILES setting and you can restart the database immediately. Then you can perform the reconstruction of the bad control file and at some later time shut down and restart the database after editing the CONTROL_FILES initialization parameter to include the recovered control file.

Dropping Control Files
You want to drop control files from the database, for example, if the location of a control file is no longer appropriate.
 Remember that the database should have at least two control files at all times.

Shut down the database.

Edit the CONTROL_FILES parameter in the database initialization parameter file to delete the old control file name.

Restart the database.

This operation does not physically delete the unwanted control file from the disk. Use operating system commands to delete the unnecessary
file after you have dropped the control file from the database.
Control Files Data Dictionary Views
The following views display information about control files:

View Description
V$DATABASE Displays database information from the control file
V$CONTROLFILE Lists the names of control files
V$CONTROLFILE_RECORD_SECTION Displays information about control file record sections
V$PARAMETER Displays the names of control files as specified in the CONTROL_FILES initialization parameter
This example lists the names of the control files.



Big tablespace in 11g.

Why Use Oracle Bigfile Tablespaces?

Oracle 10g (and continuing into 11g) introduced a new locally managed tablespace type for extreme-size databases: Bigfile tablespaces allow for the creation of tablespaces with one file where the size of that datafile fully incorporates the power of 64-bit systems. When implemented with Oracle Managed Files or Automatic Storage Management (ASM), bigfile tablespaces can greatly simplify the management of your storage system. Additionally, because you should have fewer datafiles, performance of database management operations such as checkpoints should improve, but be aware that recovery operation times are likely to increase in the event of datafile corruption.

Now you be may asking, “Then what is the benefit of bigfile tablespaces?” A bigfile tablespace with a typical 8K block can contain a single 32-terabyte datafile. If you’re using a 32K block, it can contain a 128-terabyte datafile. This is achieved by changing the way ROWIDs are managed within the tablespace. In a traditional tablespace, three positions in the ROWID are used to identify the relative file number of the row. Because you only have one datafile in bigfile tablespaces, these three positions are instead used to lengthen the data block number for the row, thereby allowing for a much larger number of ROWIDs from traditional smallfile tablespaces.


Oracle 10g feature that is interesting is a new type of tablespace called a Bigfile Tablespace. When you read the 10g New Features, you will find out that a DBA can create a terabyte-sized datafile using the Bigfile option. Oracle uses the term "Smallfile" to designate the tablespaces traditionally used for long time. A Bigfile Tablespace is a tablespace containing a single very large data file. A single Bigfile tablespace file, either data or temp file, can be up to 128 terabytes for a 32K block tablespace and 32 terabytes for an 8K block tablespace. Bigfile tablespace contains only one file, whereas a traditional tablespace (smallfile type) can contain up to 1,022 files. Let's take a look at the Bigfile tablespaces feature in this article and learn about some of the benefits that they offer versus smallfile type tablespaces.

Bigfile tablespace Benefits:

  • A DBA can use bigfile tablespaces to create extremely large databases and minimize the number of datafiles a database must manage, which has the advantage of reducing your system global area (SGA) memory requirements with a lower value of DB_Files Init. Parameter and also lower Controlfile space requirements or size.
  • Bigfile tablespace simplifies database management with the ALTER TABLESPACE command to allow the operations at TABLESPACE level, which will help to modify the size and auto extend functionality for all of the datafiles in one shot, rather than doing at datafile level for each file.For example:
  • Bigfile Tablespace can be used with:
    • ASM (Automatic Storage Management)
    • a logical volume manager supporting striping/RAID
    • Dynamically extensible logical volumes
    • Oracle Managed Files (OMF)

Bigfile tablespace Limitations:

  • Bigfile data tablespaces must be created as locally managed, with automatic segment space management. These are the default specifications. Oracle will return an error if either EXTENT MANAGEMENT DICTIONARY or SEGMENT SPACE MANAGEMENT MANUAL is specified. But there are two exceptions when bigfile tablespace segments are manually managed:
    • Locally managed undo tablespace
    • Temporary tablespace
  • Bigfile tablespaces should be striped so that parallel operations are not adversely affected. Oracle expects bigfile tablespace to be used with Automatic Storage Management (ASM) or other logical volume managers that support striping or RAID.
  • Bigfile tablespaces should not be used on platforms with filesize restrictions, which would limit tablespace capacity.
  • Avoid using bigfile tablespaces if there could possibly be no free space available on a disk group, and the only way to extend a tablespace is to add a new datafile on a different disk group.

Creating a Bigfile Tablespace...versus smallfile...

To create a bigfile tablespace, you have to specify the BIGFILE keyword of the CREATE TABLESPACE statement. Oracle creates a locally managed tablespace with automatic segment-spec management. You need not specify EXTENTMANAGEMENT LOCAL and SEGMENT SPACE MANAGEMENT AUTO in the statement. If you specify EXTENT MANAGEMENTLOCAL and SEGMENT SPACE MANAGEMENT AUTO in this statement the database returns an error. The remaining syntax of the statement is the same as for the CREATE TABLESPACE statement.
For example:
    DATAFILE '/u01/oradata/bigtbs_data_01.dbf' SIZE 50G
Note that the above size attribute is larger than the traditional ordinary smallfile tablespace size. This is due to a new addressing scheme Oracle uses internally. Oracle ROWID, addressing a database object stored in a traditional SMALLFILE tablespace, divides the 12 bytes thusly: 3 bytes for the Relative File#, 6 bytes for the Block# and 3 bytes for the object. The same rowid addressing an object stored in a new BIGFILE tablespace uses the 9 bytes to store the Block# within the unique file, as there is no reason to use the 3 bytes for the Relative File# since there is only one file in that tablespace. Thus the new addressing scheme permits up to 4Gblocks in a single data file and the maximum file size can reach 8 TB for a blocksize of 2K and 128 TB for a blocksize of 32K
If the default tablespace type is set to BIGFILE at database creation, but you want to create a traditional (smallfile) tablespace, then specify a CREATE SMALLFILE TABLESPACE statement to override the default tablespace type for the tablespace that you are creating.

Adding a file to Bigfile tablespace...

           ADD DATAFILE '/u01/oradata/bigtbs_data_02.dbf' size 50G;
      ERROR at line 1:
      ORA-32771: cannot add file to bigfile tablespace

Creating a Bigfile Temporary Tablespace

Just as for regular tablespaces, you can create single-file (bigfile) temporary tablespaces. Use the CREATE BIGFILE TEMPORARY TABLESPACE statement to create a single-tempfile tablespace.

Finding out a Bigfile Tablespace

From the following views, you can identify if the database has any bigfile tablespaces. The following views contain aBIGFILE column that identifies a tablespace as a bigfile tablespace:
You can also identify a bigfile tablespace by the relative file number of its single datafile.

Specifying the Default Tablespace Type

The SET DEFAULT ... TABLESPACE clause of the CREATE DATABASE statement determines the default type of tablespace for this database in subsequent CREATE TABLESPACE statements. Specify either SET DEFAULT BIGFILE TABLESPACE or SET DEFAULT SMALLFILE TABLESPACE. If you omit this clause, the default is a smallfile tablespace, which is the traditional type of Oracle Database tablespace. A smallfile tablespace can contain up to 1022 files with up to 4M blocks each.
The use of bigfile tablespaces further enhances the Oracle-managed files feature, because bigfile tablespaces make datafiles completely transparent for users. SQL syntax for the ALTER TABLESPACE statement has been extended to allow you to perform operations on tablespaces, rather than the underlying datafiles.
The CREATE DATABASE statement shown can be modified as follows to specify that the default type of tablespace is a bigfile tablespace:
      UNDO TABLESPACE undotbs
To dynamically change the default tablespace type after database creation, use the SET DEFAULT TABLESPACE clause of the ALTER DATABASE statement:
You can change the TYPE of tablespace used when creating new tablespaces:
 SQL> select * from database_properties 
          Where property_name='DEFAULT_TBS_TYPE';
     -------------------- --------------- ------------------------
     DEFAULT_TBS_TYPE     SMALLFILE       Default tablespace type
     Database altered.
          DATAFILE '/us01/oradata/bigtbs_02.dbf' size 1M;
     Tablespace created.
     TABLESPACE_NAME                BIGFILE
     ------------------------------ ---------
     USERS                          SMALLFILE
     BIGTBS                        BIGFILE
Even the default tablespace for the database is either Bigfile or smallfile tablespace; both Bigfile tablespace and smallfile tables can co-exist in the database.
      TABLESPACE_NAME                BIGFILE
      ------------------------------ ---------
      SYSTEM                         SMALLFILE
      UNDOTBS1                       SMALLFILE
      SYSAUX                         SMALLFILE
      TEMP                           SMALLFILE
      USERS                          SMALLFILE
      BIGTBS                         BIGFILE

Working with Bigfile Tablespace:

To illustrate this, first let us create a bigfile tablespace called bigtbs.
DATAFILE ‘/u01/oradata/big_tbs_data_01.dbf’ SIZE 1024 M;
  • If the accounts table is in traditional smallfile tablespace,.it can be moved to bigfile tablespace.
    SQL > ALTER TABLE accounts MOVE TABLESPACE bigtbs;
  • Bigfile tablespace can be resized by issuing alter tablespace.
  • With a bigfile tablespace, you can use the AUTOEXTEND clause outside of the ADD DATAFILE clause. For example:
In the earlier releases of Oracle, K and M were used to specify storage size. Please note in the above statement, you can specify size in gigabytes and terabytes using G and T respectively.
  • Using the DBVERIFY utility: With smallfile tablespace, you can run multiple instances of DBVERIFY, in parallel on multiple datafiles, to speed up integrity checking for a tablespace. You can achieve integrity checking parallelism with Bigfile tablespaces by starting multiple instances of DBVERIFY on parts of the single big file by specifying the start block and end block.
                            $dbv FILE=bigfile01.dbf START=1 END=10000
                            $dbv FILE=bigfile01.dbf START=10001

Bigfile Tablespace

A bigfile tablespace (BFT) is a tablespace containing a single file that can have a very large size.

Bigfile Tablespace Overview

The traditional tablespace is referred to as a smallfile tablespace (SFT). A smallfile tablespace contains multiple, relatively small files. The bigfile tablespace has the following characteristics:
  • An Oracle database can contain both bigfile and smallfile tablespaces. 
  • System default is to create the traditional smallfile tablespace. 
  • The SYSTEM and SYSAUX tablespaces are always created using the system default type.
  • Bigfile tablespaces are supported only for locally managed tablespaces with automatic segment-space management.
There are two exceptions when bigfile tablespace segments are manually managed:
  • Locally managed undo tablespace 
  • Temporary tablespace
Bigfile tablespaces are intended to be used with Automated Storage Management (ASM) (see Chapter 1) or other logical volume managers that support RAID.
However, you can also use The bigfile tablespace without ASM.

Bigfile Tablespace Benefits

Bigfile tablespace has the following benefits:
  • The bigfile tablespace simplifies large database tablespace management by reducing the number of datafiles needed.
  • The bigfile tablespace simplifies datafile management with Oracle-managed files and Automated Storage Management (ASM) by eliminating the need for adding new datafiles and dealing with multiple files.
  • The bigfile tablespace allows you to create a bigfile tablespace of up to eight exabytes (eight million terabytes) in size, and significantly increase the storage capacity of an Oracle database.
  • The bigfile tablespace follows the concept that a tablespace and a datafile are logically equivalent.

Maximum Database Size

The bigfile tablespace extended the maximum size of tablespace and database. Let's take a look at the two formulas that calculate the maximum size of data file and database.
The maximum bigfile (data file) size is calculated by:
     Maximum datafile size = db_block_size * maximum number of blocks
The maximum amount of data in an Oracle database is calculated by:
     Maximum database size = maximum datafile size * maximum number of datafile
The maximum number of datafiles in Oracle9i and Oracle 10g Database is 65,536. However, the maximum number of blocks in a data file increase from 4,194,304 (4 million) blocks to 4,294,967,296 (4 billion) blocks.
The maximum amount of data for a 32K block size database is eight petabytes (8,192 Terabytes) in Oracle9i.
32 K 128 GB 8,388,608 GB
16 K64 GB 4,194,304 GB
 8 K 32 GB 2,097,152 GB
4 K 16 GB1,048,579 GB
2 K 8 GB 524,288 GB

 Table 3.1: Maximum database size in Oracle9i.
The maximum amount of data for a 32K block size database is eight exabytes (8,388,608 Terabytes) in Oracle 10g.
32 K 131,072 GB 8,589,934,592 GB
16 K 65,536 GB 4,294,967,296 GB
8 K32,768 GB2,147,483,648 GB
4 K16,384 GB1,073,741,824 GB
2 K8,192 GB536,870,912 GB
Table 3.2: Maximum database size in Oracle 10g.
As you can see, with the new bigfile tablespace addressing scheme, Oracle 10g can contain astronomical amounts of data within a single database.
With a few exceptions, you need to use an alter database resize datafile command to resize a datafile.
One exception is the 'bigfile tablespace'.
 Oracle will allows you to use the "alter tablespace xxx resize datafile" syntax.
This is because you are not allowed to add a datafile to a bigfile tablespace.  Remember, there is no alter tablespace resize datafile syntax.
Rowids of Rows that belong to BFTs do not contain the relative file number.
  • Smallfile tablespace has a four-pieces format, OOOOOOFFFBBBBBBRRR, in which:
  • OOOOOO is the data object number of the segment.
  • FFF is the tablespace-relative datafile number of the datafile that contains the row.
  • BBBBBB is the data block that contains the row.
  • RRR is the slot number identifying the row inside a particular block.
In a bigfile tablespace, there is only one file that always has a relative file number of 1024.  A bigfile tablespace has a three-piece format, OOOOOOLLLLLLLLLRRR, in which LLLLLLLLL is used to denote the block number.  The concatenation of FFF and BBBBBB makes the encoded block number.
For BFTs, the only supported way of getting components of extended rowids is to use the DBMS_ROWID package.
Here is an example on how to use the dbms_rowid package to retrieve rowid information:
select dbms_rowid.rowid_relative_fno(rowid, 'BIGFILE') 
       dbms_rowid.rowid_relative_fno(rowid, 'SMALLFILE')
       first_name, last_name
FROM   hr.employees where  rownum < 3;

Bigfile Tablespace Rowid Format

------------- --------------- -------------------- ----------
         1024               4 Mike                 Ault
         1024               4 Madhu                Tumma
Data Dictionary Views Enhancement
A new column is added to both dba_tablespaces  and v$tablespace views to indicate whether a particular tablespace is bigfile or smallfile:
SQL> select name, bigfile
     from v$tablespace;
 NAME                           BIGFILE
------------------------------ -------
SYSTEM                         NO
UNDOTBS01                      NO
SYSAUX                         NO
TEMP                           NO
EXAMPLE                        NO
USERS                          NO
BIG_TBS                        YES

SQL> select tablespace_name,bigfile
     from   dba_tablespaces;

------------------------------ ---------
SYSTEM                         SMALLFILE
UNDOTBS01                      SMALLFILE
SYSAUX                         SMALLFILE
TEMP                           SMALLFILE|
EXAMPLE                        SMALLFILE
USERS                          SMALLFILE
BIG_TBS01                      BIGFILE

Bigfile Tablespace Examples
Example 1: Create a database with default bigfile tablespace.
DATAFILE '/u02/oradata/grid/system01.dbf' SIZE 500 M,
SYSAUX DATA FILE '/u02/oradata/grid/sysaux01.dbf' SIZE 500 M
TEMPFILE '/u02/oradata/grid/temp01.dbf' SIZE 1024 M
DATAFILE '/u02/oradata/grid/undo01.dbf' SIZE 1024 M;
Example 2: Moving data between smallfile and bigfile tablespaces.
ALTER TABLE employee MOVE TABLESPACE bigfile_tbs;
Example 3: Create a bigfile tablespace and change its size.
DATAFILE '/u02/oradata/grid/user_tbs01.dbf' SIZE 1024 M;
In the previous release of Oracle server, K and M were used to specify storage size. Notice in this DDL statement, a user can specify size in gigabytes and terabytes using G and T respectively.
Example 4: Use DBVERIFY utility with bigfile. With small file tablespace, you can run multiple instances of DBVERIFY in parallel on multiple datafiles to speed up integrity checking for a tablespace.  You can achieve integrity checking parallelism with BFTs by starting multiple instances of DBVERIFY on parts of the single big file.

$dbv FILE=bigfile01.dbf  START=1 END=10000
$dbv FILE=bigfile01.dbf  START=10001

Note: START = Start Block; END = End Block

Wednesday, 5 August 2015

Change Weblogic admin password in Oracle EBS R12.2

How to change the weblogic server administrator password in R12.2??

1. Login to weblogic to server console:-

User-is:- weblogic
Password:- ******

2.Choose the Lock and Edit button.

3. Select the ‘Domain’ link under the Domain Structure section.

4. Select the Security tab.

5. Select Advanced.

6. Enter the new Node Manager/Weblogic password.
Click on save..

 Verify settings updated successfully and then click on "Activate changes"

7. Choose the ‘Security Realms’ link on the Oracle E-Business Suite 12.2 domain configuration, and then select the ‘myrealm’ link.

click on "Security Realms"

8. Select the Users and Groups tab and choose the ‘weblogic’ link.

9. Provide same password as we have provided above for node manager.

Click on Save >>>

Settings has been updated successfully.

10. we need to edit the configuration file "boot.properties" for all managed and admin server  using text editor and update the password field with the above configured password ( new password)

Take  backup of the following files:

"$FMW_HOME/user_projects/domains/EBS_domain_" is the absolute PATH for your EBS weblogic domain.

PATH ="/d01/appl_prod/appl/PROD/fs1/FMW_Home/user_projects/domains/EBS_domain_PROD/"

[applmgr@test nodemanager]$ cat boot.properties 
#mon july 22 05:16:35 AST 2015

[applmgr@test nodemanager]$ cat orig.boot.properties 
#mon july 22 05:16:35 AST 2015

The above is an example of boot.properties file of one the managed server. "orig.boot.properties" is an original file and the password in that file is encrypted. We have to change the password using text editor and provide the one configured in previous steps.

After editing all the above mentioned files we need to restart the application services using adstpall.sh and adstrtal.sh scripts.

After restarting of services make sure all application services are started successfully. Verify that you are able to access weblogic server console and access ERP application.


Monday, 3 August 2015

ora-00020 maximum number of processes 200 exceeded

ora-00020 maximum number of processes 200 exceeded

I've got a database instance that has apparently run out of processes, however, I cannot even seem to log on as sys to fix things..!!

ora.sujeet # sqlplus / as sysdba

ORA-00020: maximum number of processes (%s) exceeded

The ORA-00020 is caused by two things:  
  1. Disconnected processes:  sonu “sujeet” connections  to Oracle that are idle (not working).  To fix this, use the ALTER SYSTEM KILL command.  You may also need to kill session at the OS level with the KILL -9  command.  
  2. Too few process buckets:  Oracle limits the number of connected processes with the processes parameter, and you may get the ORA-00020 error as the natural result in growth of system usage. 


If your database is running on a UNIX system, and if you can kill session without disturbing (too much) the application. 
You can identify server processes that are connected to the instance, for example, if your instance name is "PRODDB", identify oracle server processes that are connected : 

ora.sujeet $ ps -ef | grep oracle PRODDB

 kill one & more process (with kill -9 <pid>), After that try to connect you to the instance.


Cause: All process state objects are in use.  

Action: Increase the value of the PROCESSES initialization parameter
Simply increase your processes parameter and you are all set!


Checked test db process count it was only 200 as the error says…
SQL> Alter system set processes=800 scope=spfile;
System altered.
shutdown immediate;
How to increase PROCESSES initialization parameter: 
1. Login as sysdba 
sqlplus / as sysdba 
2. Check Current Setting of Parameters 
sql> show parameter sessions 
sql> show parameter processes 
sql> show parameter transactions 
3. If you are planning to increase "PROCESSES" parameter you should also plan to increase "sessions and "transactions" parameters
A basic formula for determining these parameter values is as follows: 
4. These paramters can't be modified in memory. You have to modify the spfile only (scope=spfile) and bounce the instance. 
sql> alter system set processes=500 scope=spfile; 
sql> alter system set sessions=555 scope=spfile; 
sql> alter system set transactions=610 scope=spfile; 
sql> shutdown abort 
sql> startup