Sujeet APPS DBA

How to Enable Root Login on RHEL6.0/OEL6.3

If You want to login as a root from GUI in RHEL6 then you have
to edit something like some files which are located to /etc/pam.d/
Open your Terminal from Applications -> System Tools -> Terminal

Now Login as a root from your terminal

Step 1 :- [oracle]$ su – root
Password:-

Step 2:- Now go to your /etc/pam.d/ directory.

[root]# cd /etc/pam.d/
Then first take a backup of gdm file
cp gdm gdm.bkp ( always take backup if anything goes wrong you can correct it by original file)

Step 3 :- Now Open gdm file in your favourite editor. I am using vi as my editor.
[root]# vi gdm
Find and Comment or remove this line into your gdm file
auth required pam_succeed_if.so user != root quiet

Step 4 :- Save & Exit From that File.

Now Logout and Try to Login as a
root user. Now you are able to Login as a root user from GUI in RHEL6/OEL6.3.

ORA-01658

ORA-01658: unable to create INITIAL extent for segment in tablespace string

Cause

Failed to find sufficient contiguous space to allocate INITIAL extent for segment being created.

Solution

Use ALTER TABLESPACE ADD DATAFILE to add additional space to the tablespace or retry with a smaller value for INITIAL.

1 - Make the INITIAL extent size smaller

2 - Empty the recycle bin

3 - Add a datafile the tablespace to provide a larger contiguous chunk size.

4 - Reorganize the tables in the tablespace

Try this query for your tablespace maximum chunk size tsfree.sql.

This case of ORA-01658 concerns needing to make the tablespace bigger, which can be done by either extending your file, or adding another one. This is a good solution for ORA-01658 because repository tables need to be meticulously created keeping the extents of the table in mind.

How to Register Listener in the Database

How to Register Listener in the Database ?

The listener is a separate process that runs on the database server computer. It receives incoming client connection requests and manages the traffic of these requests to the database server. There are two methods by which a listener comes to know of a database instance. In Oracle terminology, this is referred as “Registering with the Listener” . The two methods are

1.) Static Instance Registration
2.) Dynamic Instance Registration or (service registration)

Static Instance Listener :

This is the very basic method to register listener .We can either add the entries in $ORACLE_HOME\NETWORK\ADMIN\listener.ora file or by using the GUI i.e, through Net Manager. The configuration inside the listener.ora file looks like :

SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(GLOBAL_DBNAME = idea )
(ORACLE_HOME = C:\app\sonu\product\11.2.0\dbhome_1)
(SID_NAME = idea )
)
(SID_DESC =
(GLOBAL_DBNAME = Airtel)
(ORACLE_HOME = C:\app\sonu\product\11.2.0\dbhome_1)
(SID_NAME = Airtel
)
)
LISTENER =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = oracle)(PORT = 1521))
)

and when we check the registration , it shows the status of UNKNOWN :

C:\>lsnrctl
LSNRCTL for 32-bit Windows: Version 11.2.0.1.0 - Production on 05-OCT-2011 15:26:27
Copyright (c) 1991, 2010, Oracle. All rights reserved.
Welcome to LSNRCTL, type "help" for information.

LSNRCTL> status
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=oracle)(PORT=1521)))
STATUS of the LISTENER
------------------------
Alias LISTENER
Version TNSLSNR for 32-bit Windows: Version 11.2.0.1.0 - Production
Start Date 28-Oct-2013 13:11:59
Uptime 3 days 1 hr. 27 min. 45 sec
Trace Level off
Security ON: Local OS Authentication
SNMP OFF
Listener Parameter File C:\app\sonu\product\11.2.0\dbhome_1\network\admin\listener.ora
Listener Log File c:\app\sonu\diag\tnslsnr\tech-199\listener\alert\log.xml
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=oracle)(PORT=1521)))
Services Summary...
Service "Airtel" has 1 instance(s).
Instance "Airtel", status UNKNOWN, has 1 handler(s) for this service...
Service "idea " has 1 instance(s).
Instance "idea ", status UNKNOWN, has 1 handler(s) for this service...
The command completed successfully
LSNRCTL>

The status is unknown because there is no mechanism to guarantee that the specified status even exists.Here the listener assumes that instance will be there whenever there will be any request. It donot have inforamtion about the status of the Current Instance.

Now, we will check the Dynamic Instance Listener :

Dynamic Instance Registration :

This dynamic registration feature is called service registration. The registration is performed by the PMON process an instance background process of each database instance that has the necessary configuration in the database initialization parameter file. Dynamic service registration does not require any configuration in the listener.ora file.

Service registration offers the following benefits :

1.) Simplified configuration : Service registration reduces the need for the SID_LIST_listener_name parameter setting, which specifies information about the databases served by the listener, in the listener.ora file.

Note : The SID_LIST_listener_name parameter is still required if we are using Oracle Enterprise Manager to manage the database.

2.) Connect-time failover : Because the listener always knows the state of the instances, service registration facilitates automatic failover of the client connect request to a different instance if one instance is down.

In a static configuration model, a listener would start a dedicated server upon receiving a client request. The server would later find out that the instance is not up, causing an "Oracle not available" error message.

3.) Connection load balancing : Service registration enables the listener to forward client connect requests to the least loaded instance and dispatcher or dedicated server. Service registration balances the load across the service handlers and nodes. To ensure service registration works properly, the initialization parameter file should contain the following parameters:

SERVICE_NAMES for the database service name
INSTANCE_NAME for the instance name
For example:
SERVICE_NAMES=idea .oracle
INSTANCE_NAME=idea

Let's have a Demo of Dynamic Listener.

The listener is quite capable of running without a listner.ora file at all. It will simply start and run with all default values.Here i have rename the listener.ora file and stop and start the listener and find that listener supports no services.Check the below:

LSNRCTL> stop
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=oracle)(PORT=1521)))
The command completed successfully.

Now start the listener

LSNRCTL> start
Starting tnslsnr: please wait...

TNSLSNR for 32-bit Windows: Version 11.2.0.1.0 - Production
Log messages written to c:\app\sonu\diag\tnslsnr\tech-199\listener\alert\log.xml
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=oracle)(PORT=1521)))
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=oracle)(PORT=1521)))
STATUS of the LISTENER
------------------------
Alias LISTENER
Version TNSLSNR for 32-bit Windows: Version 11.2.0.1.0 - Production
Start Date 05-Nov-2013 12:31:41
Uptime 0 days 0 hr. 0 min. 4 sec
Trace Level off
Security ON: Local OS Authentication
SNMP OFF
Listener Log File c:\app\sonu\diag\tnslsnr\tech-199\listener\alert\log.xml
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=oracle)(PORT=1521)))
The listener supports no services
The command completed successfully

Here, we find that listener donot support any services.Since it doesnot found the listener.ora file ,and if we try to connect to the Instance then it will throws the error i.e, ORA-12514 :

C:\> tnsping idea
TNS Ping Utility for 32-bit Windows: Version 11.2.0.1.0 - Production on 05-nov-2013 12:31:41
Copyright (c) 1997, 2010, Oracle. All rights reserved.
Used parameter files:
C:\app\sonu\product\11.2.0\dbhome_1\network\admin\sqlnet.ora

Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.100.0.98)(PORT = 1521))) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = idea )))
OK (40 msec)

Now, we try to connect with Instance "idea "

C:\> sqlplus sys/xxxx@idea as sysdba

SQL*Plus: Release 11.2.0.1.0 Production on Wed Oct 5 16:23:45 2011
Copyright (c) 1982, 2010, Oracle. All rights reserved.
ERROR:
ORA-12514: TNS:listener does not currently know of service requested in connect
descriptor

Since the tnsping proves that our tnsnames.ora resolution is correct, but it throws the error while connecting to database because the listener doesnot knows anything about the services "idea " . Let's start the instance and check again :

C:\> set ORACLE_SID=idea
SQL> startup
ORACLE instance started.

Total System Global Area 263639040 bytes
Fixed Size 1373964 bytes
Variable Size 222300404 bytes
Database Buffers 33554432 bytes
Redo Buffers 6410240 bytes
Database mounted.
Database opened.

Now check the listener status again :

LSNRCTL> status
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=oracle)(PORT=1521)))
STATUS of the LISTENER
------------------------
Alias LISTENER
Version TNSLSNR for 32-bit Windows: Version 11.2.0.1.0 - Production
Start Date 05-OCT-2011 16:21:30
Uptime 0 days 0 hr. 19 min. 21 sec
Trace Level off
Security ON: Local OS Authentication
SNMP OFF
Listener Log File c:\app\sonu\diag\tnslsnr\tech-199\listener\alert\log.xml
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=oracle)(PORT=1521)))
Services Summary...
Service "idea .TECH-199" has 1 instance(s).
Instance "idea ", status READY, has 1 handler(s) for this service...
Service "idea XDB.TECH-199" has 1 instance(s).
Instance "idea", status READY, has 1 handler(s) for this service...
Service "idea _DGB.TECH-199" has 1 instance(s).
Instance "idea ", status READY, has 1 handler(s) for this service...
The command completed successfully

Here we observe that once the instance is started , when we re-check the listener now knows of service “idea ”, with a status of READY . This obviously did not come from listener.ora as the file is renamed. Notice also that, unlike the static registration, this time the status is READY. The listener knows the instance is ready because the instance itself told the listener it was ready.

Now agian connecting to the Instance :

C:\>sqlplus sys/xxxx@idea as sysdba
SQL*Plus: Release 11.2.0.1.0 Production on Tue Oct 4 18:14:28 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>

Here by default, the PMON process registers service information with its local listener on the default local address of TCP/IP, port 1521. As long as the listener configuration is synchronized with the database configuration, PMON can register service information with a nondefault local listener or a remote listener on another node. During service registration PMON provides listener with the following information:

- Name of the associated instance
- Current load and maximum load on instance
- Names of DB services provided by database.

- Information about dedicated servers and dispatchers (depends on database server mode i.e dedicated/shared server mode) .

PMON process wakes up at every 60 seconds and provide information to the listener. If any problem arises and PMON process fails then it's not possible to register information to listener periodically. In this case we can do 'Manual service registration' using command:

SQL> ALTER SYSTEM REGISTER;

ORA-27123: unable to attach to shared memory segment

ORA-27123: unable to attach to shared memory segment

Linux-x86_64 Error: 22: Invalid argument Typically the failures begin at 4am after an Exadata patch has been applied because prelink is automatically run by cron at that time.

Affects: X2-8 or X3-8 systems with a database whose SGA is 207G. This is described in bug 16426985. In a future release the prelink rpm will be removed from the Exadata installation.

Solution: As root perform the following steps:

# rpm -e --nodeps prelink
# rm -Rf /var/log/prelink* /etc/cron.daily/prelink /etc/sysconfig/prelink /etc/prelink.conf /etc/prelink.conf.d;

Shrink Oracle Tablespace

Oracle has several commands to reclaim unused disk space for objects (tables and indexes). Using the "alter table table_name shrink space compact" command also has the benefit of making full-table scans run faster, as less block accesses are required. With standard Oracle tables, you can reclaim space with the "alter table shrink space" command:

SQL> alter table mytable enable row movement;
Table altered

SQL> alter table mytable shrink space;
Table altered

Example:-

Actually I have gigabytes and gigabytes of tablespaces but most of them are used partially.

I heard that there is possibility to shrink them, the size of the tb will be exactly the size of the data (automatically increase) till reach the maximum defined.

I explain better my question:

Tb1 (size 10 gb)

1) NOW - dbf file of 10 gb - Data inside 1 gb

2) MY DREAM - dbf file of 1 gb - Data inside 1 gb
- Automatically increase with the data till 10gb .. at that time
tb full.

Redo log

1. Adding A New redo log Group

sql> ALTER DATABASE ADD LOGFILE('/oracledata/test/redo3a.log','/test_multiplex/redo3b.log') SIZE 52429312;

The command above will add a new redo log group to your database. The group will have two members with size 52429312. A group number will automatically be assigned to your new group. If you want to set it explicitly you may use command below.

sql> ALTER DATABASE ADD LOGFILE THREAD 1 GROUP 15 ('/oracledata/test/redo3a.log','/test_multiplex/redo3b.log') SIZE 52429312;

In a RAC (Real Application Clusters) environment you have to specify which instance the redo log group will belong to by setting thread number. Every instance in a RAC database has a thread number. For ex: the command below will add the redo log group to instance with thread number 1.

sql> ALTER DATABASE ADD LOGFILE THREAD 1 GROUP 15 ('/oracledata/test/redo3a.log','/test_multiplex/redo3b.log') SIZE 52429312;

2. Dropping A redo log Group
Before dropping a redo log group, make sure that at the moment, logwriter process is not writing to the redo log file you plan to drop.

sql> SELECT GROUP#, THREAD#,ARCHIVED, STATUS FROM V$LOG ;

GROUP#	THREAD#	ARC	STATUS
1	1	YES	INACTIVE
2	1	NO	CURRENT
3	1	YES	INACTIVE

The value for "ARCHIVED" column must be "YES" and STATUS column must be "INACTIVE".
If the status is "CURRENT" it means that logwriter is currently using this group to store redo records. After the redo files in this group is filled it will switch to next group. You may wait till next log switch operation or you may manually trigger it by command below:

SQL> alter system switch logfile ;

If status is "ACTIVE" it means that there are some redo log records, which have not been applied to datafiles yet. The status is supposed to turn to INACTIVE soon after the next checkpoint operation. If you do not want to wait till then you may manually trigger checkpoint by command below.

sql> alter system checkpoint ;

If the status has turned to INACTIVE you may drop it by specifying the group number.

sql> ALTER DATABASE DROP LOGFILE GROUP 6;

This command will drop the redo log group, which is identified by number 6.
Logwriter writes to redo log files in a circular fashion. When a group is filled it moves to next group. After all groups in database are filled it returns back to first redo log group. Because of that, there has to be at least 2 groups in an instance. Oracle won't permit dropping a group if there are only two groups in the instance.
After dropping a group, you may delete relevant files from operating system.
3. Adding A New Member To An Existing Group
As mentioned at the beginning of article it is recommended to multiplex redo log members for fault tolerance. You can accomplish this by adding new members to an existing group.

sql> ALTER DATABASE ADD LOGFILE MEMBER '/u02/oradata/mydb/redo02.log' TO GROUP 2;

The above command adds a new redo log file to redo log group with number 2.
4. Dropping A Member From An Existing Group
As stated in subject 3 (Dropping A redo log Group) again the group, whose member you want to drop should be inactive.

sql> ALTER DATABASE DROP LOGFILE MEMBER '/u03/oradata/mydb/redo03.log'

5. Relocating A redo log Group
You may want to relocate your existing redo log group and change the path of redo log files. Here are the steps to accomplish it
Step 1: Shutdown database

sql> shutdown immediate ;

Step 2: Backup your existing control file and redo log file at operating system level.
Step 3: Move your redo log member to a new location. You may also change the file name if you want.

# mv /u02/oradata/mydb/redo02.log /u03/oradata/mydb/redo03.log

Step 4: Startup in nomount mode

sql> startup nomount;

Step 5: Path of redo log files is kept in control file. You have to update that record by command below:

sql> ALTER DATABASE RENAME FILE '/u02/oradata/mydb/redo02.log' TO '/u03/oradata/mydb/redo03.log'

Control file is modified and the path of redo log member is updated. Now you can open your database.

sql> alter database open;

As you see this way of relocating online redo log files requires downtime. You may also change the path of your redo log files by adding a new group at desired location and dropping the ones at former location. This approach requires no downtime.

*****************************************************************

Yesterday we found and realized that there has been too much log switching in one of our databases and it was impacting the database performance. This excessive log switching was happening because our Redo Log files were not enough large as per the the database activity.
We had 50 MB Redo Log Files, 1 Redo Thread, 2 Redo Log Groups and One member in each group.
We decided to increase the Redo Log size to 100MB.
The Redo Logs must be dropped and recreated for changing the redo log size. It can be done online without shutting down the database. However, you need to make sure that the Redo Group being dropped should be INACTIVE when you do this.
We have the following Log Groups and members:

SQL> select * from v$logfile;

 GROUP# STATUS                TYPE                  MEMBER                                                       IS_RECOVE
---------- --------------------- --------------------- ------------------------------------------------------------ ---------
 2                       ONLINE                /app01/oratest/oradata/BOTI/BOTI/redo02.log                  NO
 1                       ONLINE                /app01/oratest/oradata/BOTI/BOTI/redo01.log                  NO

And the status of the Log Groups is:

SQL> select * from v$log;

 GROUP#    THREAD#  SEQUENCE#      BYTES    MEMBERS ARCHIVED  STATUS                                           FIRST_CHANGE# FIRST_TIME
---------- ---------- ---------- ---------- ---------- --------- ------------------------------------------------ ------------- ---------------
 1          1         12   52428800          1 NO        CURRENT                                                1156736 27-JAN-11
 2          1         11   52428800          1 YES       ACTIVE                                                 1156732 27-JAN-11

Here we see that the Group# 1 is being used Currently and the Group# 2 though not being used Currently, however is ACTIVE (means if the Database crashes now, you will need this Group for recovery.) We need to make this group Inactive before proceeding ahead:
For this, execute a checkpoint:

SQL> alter system checkpoint;

System altered.

Now again check the status of the Redo Groups:

SQL> select * from v$log;

 GROUP#    THREAD#  SEQUENCE#      BYTES    MEMBERS ARCHIVED  STATUS                                           FIRST_CHANGE# FIRST_TIME
---------- ---------- ---------- ---------- ---------- --------- ------------------------------------------------ ------------- ---------------
 1          1         12   52428800          1 NO        CURRENT                                                1156736 27-JAN-11
 2          1         11   52428800          1 YES       INACTIVE                                               1156732 27-JAN-11

The group 2 is now Inactive. Hence we can proceed with dropping this group:

SQL> alter database drop logfile group 2;
alter database drop logfile group 2
*
ERROR at line 1:
ORA-01567: dropping log 2 would leave less than 2 log files for instance BOTI (thread 1)
ORA-00312: online log 2 thread 1: '/app01/oratest/oradata/BOTI/BOTI/redo02.log'

What went wrong ???
It’s a basic requirement in Oracle Database that there should always be a minimum 2 Redo Log Groups available with the Database. Hence we can not drop any Redo Group if there are only 2 Groups.
To overcome this issue, we need to add one more Redo group to the database.
Execute the following step:

SQL> alter database add logfile group 3 '/app01/oratest/oradata/BOTI/BOTI/redo03.log' size 100M;

Database altered.

Now check the logfiles:

SQL> select * from v$logfile;

 GROUP# STATUS                TYPE                  MEMBER                                                       IS_RECOVE
---------- --------------------- --------------------- ------------------------------------------------------------ ---------
 3                       ONLINE                /app01/oratest/oradata/BOTI/BOTI/redo03.log                  NO
 2                       ONLINE                /app01/oratest/oradata/BOTI/BOTI/redo02.log                  NO
 1                       ONLINE                /app01/oratest/oradata/BOTI/BOTI/redo01.log                  NO

… and the status of the Groups:

SQL> select * from v$log;

 GROUP#    THREAD#  SEQUENCE#      BYTES    MEMBERS ARCHIVED  STATUS                                           FIRST_CHANGE# FIRST_TIME
---------- ---------- ---------- ---------- ---------- --------- ------------------------------------------------ ------------- ---------------
 1          1         12   52428800          1 NO        CURRENT                                                1156736 27-JAN-11
 2          1         11   52428800          1 YES       INACTIVE                                               1156732 27-JAN-11
 3          1          0  104857600          1 YES       UNUSED                                                       0

The status of the new Group is UNUSED because it has never been used.
Now, we have 3 Redo Groups in our database. We can now proceed with Dropping Redo Group# 2.

SQL> alter database drop logfile group 2;

Database altered.

Also, delete the file ‘/app01/oratest/oradata/BOTI/BOTI/redo02.log’ from File system also.
Now add the Redo Group 2 back to the database with changed Redo size:

SQL> alter database add logfile group 2 '/app01/oratest/oradata/BOTI/BOTI/redo02.log' size 100M;

Database altered.

SQL> select * from v$logfile;

 GROUP# STATUS                TYPE                  MEMBER                                                       IS_RECOVE
---------- --------------------- --------------------- ------------------------------------------------------------ ---------
 3                       ONLINE                /app01/oratest/oradata/BOTI/BOTI/redo03.log                  NO
 2                       ONLINE                /app01/oratest/oradata/BOTI/BOTI/redo02.log                  NO
 1                       ONLINE                /app01/oratest/oradata/BOTI/BOTI/redo01.log                  NO

SQL> select * from v$log;

 GROUP#    THREAD#  SEQUENCE#      BYTES    MEMBERS ARCHIVED  STATUS                                           FIRST_CHANGE# FIRST_TIME
---------- ---------- ---------- ---------- ---------- --------- ------------------------------------------------ ------------- ---------------
 1          1         12   52428800          1 NO        CURRENT                                                1156736 27-JAN-11
 2          1          0  104857600          1 YES       UNUSED                                                       0
 3          1          0  104857600          1 YES       UNUSED                                                       0

Now we have to drop the 1st Redo Log Group. Before that, we need to change the status of this group:

SQL> alter system switch logfile;

System altered.

SQL> select * from v$log;

 GROUP#    THREAD#  SEQUENCE#      BYTES    MEMBERS ARCHIVED  STATUS                                           FIRST_CHANGE# FIRST_TIME
---------- ---------- ---------- ---------- ---------- --------- ------------------------------------------------ ------------- ---------------
 1          1         12   52428800          1 YES       ACTIVE                                                 1156736 27-JAN-11
 2          1         13  104857600          1 NO        CURRENT                                                1157376 27-JAN-11
 3          1          0  104857600          1 YES       UNUSED                                                       0

Still the Grpoup is in Active status. Issue a checkpoint:

SQL> alter system checkpoint;

System altered.

SQL> select * from v$log;

 GROUP#    THREAD#  SEQUENCE#      BYTES    MEMBERS ARCHIVED  STATUS                                           FIRST_CHANGE# FIRST_TIME
---------- ---------- ---------- ---------- ---------- --------- ------------------------------------------------ ------------- ---------------
 1          1         12   52428800          1 YES       INACTIVE                                               1156736 27-JAN-11
 2          1         13  104857600          1 NO        CURRENT                                                1157376 27-JAN-11
 3          1          0  104857600          1 YES       UNUSED                                                       0

Now we can drop the Redo Group# 1.

SQL> alter database drop logfile group 1;

Database altered.

SQL> select * from v$log;

 GROUP#    THREAD#  SEQUENCE#      BYTES    MEMBERS ARCHIVED  STATUS                                           FIRST_CHANGE# FIRST_TIME
---------- ---------- ---------- ---------- ---------- --------- ------------------------------------------------ ------------- ---------------
 2          1         13  104857600          1 NO        CURRENT                                                1157376 27-JAN-11
 3          1          0  104857600          1 YES       UNUSED                                                       0

SQL> select * from v$logfile;

 GROUP# STATUS                TYPE                  MEMBER                                                       IS_RECOVE
---------- --------------------- --------------------- ------------------------------------------------------------ ---------
 3                       ONLINE                /app01/oratest/oradata/BOTI/BOTI/redo03.log                  NO
 2                       ONLINE                /app01/oratest/oradata/BOTI/BOTI/redo02.log                  NO

Delete the file ‘/app01/oratest/oradata/BOTI/BOTI/redo01.log’ from OS.
Now we need to add this group back to the database with 100MB Redo Log file:

SQL> alter database add logfile group 1 '/app01/oratest/oradata/BOTI/BOTI/redo01.log' size 100M;

Database altered.

SQL> select * from v$log;

 GROUP#    THREAD#  SEQUENCE#      BYTES    MEMBERS ARCHIVED  STATUS                                           FIRST_CHANGE# FIRST_TIME
---------- ---------- ---------- ---------- ---------- --------- ------------------------------------------------ ------------- ---------------
 1          1          0  104857600          1 YES       UNUSED                                                       0
 2          1         13  104857600          1 NO        CURRENT                                                1157376 27-JAN-11
 3          1          0  104857600          1 YES       UNUSED                                                       0

Here we can see that all the Redo Groups are now showing 100MB size (column BYTES).

SQL> select * from v$logfile;

 GROUP# STATUS                TYPE                  MEMBER                                                       IS_RECOVE
---------- --------------------- --------------------- ------------------------------------------------------------ ---------
 3                       ONLINE                /app01/oratest/oradata/BOTI/BOTI/redo03.log                  NO
 2                       ONLINE                /app01/oratest/oradata/BOTI/BOTI/redo02.log                  NO
 1                       ONLINE                /app01/oratest/oradata/BOTI/BOTI/redo01.log                  NO

Though it’s suggested that you should keep 3 groups, if you want you can drop the Redo Group 3 which we added for this exercise.

SQL> alter database drop logfile group 3;

Database altered.

SQL> select * from v$logfile;

 GROUP# STATUS                TYPE                  MEMBER                                                       IS_RECOVE
---------- --------------------- --------------------- ------------------------------------------------------------ ---------
 2                       ONLINE                /app01/oratest/oradata/BOTI/BOTI/redo02.log                  NO
 1                       ONLINE                /app01/oratest/oradata/BOTI/BOTI/redo01.log                  NO

SQL> select * from v$log;

 GROUP#    THREAD#  SEQUENCE#      BYTES    MEMBERS ARCHIVED  STATUS                                           FIRST_CHANGE# FIRST_TIME
---------- ---------- ---------- ---------- ---------- --------- ------------------------------------------------ ------------- ---------------
 1          1          0  104857600          1 YES       UNUSED                                                       0
 2          1         13  104857600          1 NO        CURRENT                                                1157376 27-JAN-11

SQL> alter system switch logfile;

System altered.

SQL> select * from v$log;

 GROUP#    THREAD#  SEQUENCE#      BYTES    MEMBERS ARCHIVED  STATUS                                           FIRST_CHANGE# FIRST_TIME
---------- ---------- ---------- ---------- ---------- --------- ------------------------------------------------ ------------- ---------------
 1          1         14  104857600          1 NO        CURRENT                                                1157584 27-JAN-11
 2          1         13  104857600          1 YES       ACTIVE                                                 1157376 27-JAN-11

You have changed the Redo Log file size from 50M to 100M.

Find Oracle Applications login URL

How to find Oracle Applications Login URL from database

Logging into Oracle Applications database from "APPS" user:-
Scripts:-1 SELECT home_url
FROM icx_parameters;

Scripts:-2

SELECT profile_option_value

FROM fnd_profile_option_values

WHERE profile_option_id=

(SELECT profile_option_id

FROM fnd_profile_options WHERE profile_option_name = 'APPS_FRAMEWORK_AGENT')

AND level_value = 0;

How to check Oracle Application URL in 11i and R12 on UNIX prompt

In Applications 11i:









Sujeet$ cd $APPL_TOP/admin



Sujeet$ pwd

/appl/apps/prodnewappl/admin



Sujeet$ ls -l *.xml



-rw-r--r--   1 appltest dbatest    84223 Oct  2 12:54 PRODTEST_testappl.xml



Sujeet$ cat PREPROD_testappl.xml | grep http | grep login

         login_page oa_var="s_login_page" http://testappl.orasol.com:8040/oa_servlets/AppsLogin/login_page



Sujeet$

Here URL is http://testappl.orasol.com:8040

In Applications R12:









Sujeet$ cd $INST_TOP



Sujeet$ ls

admin             appltmp           conf_ERPTEST.txt  ora               pids              rgf               temp

appl              certs             logs              out               portal            soa

Sujeet$ cd appl



Sujeet$ ls



admin  fnd



Sujeet$ cd admin



Sujeet$ ls -l

total 228

-rwxr-xr-x   1 applmgr  appsdba     90899 Nov  9 12:18 ERPTEST_orasolerp.xml

-rw-r--r--   1 applmgr  appsdba     10392 Nov  9 12:10 adovars_ERPTEST_orasolerp.env

-rw-------   1 applmgr  appsdba      1016 Nov  9 12:10 oamextensions.xml

-rw-------   1 applmgr  appsdba      3340 Nov  9 12:10 ojspCompile.conf

-rw-------   1 applmgr  appsdba      7476 Nov  9 12:10 ojspCompile.properties



Sujeet$ cat ERPTEST_orasolerp.xml | grep http | grep login

         login_page oa_var="s_login_page"http://orasolerp.orasol.com:8010/OA_HTML/AppsLogin/login_page



Sujeet$ pwd

/OrApps/appsR12/inst/apps/ERPTEST_orasolerp/appl/admin

Here URL is http://orasolerp.orasol.com:8010