Sunday, 24 November 2013

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

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.

Sunday, 17 November 2013

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. 

Friday, 8 November 2013

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;

Saturday, 2 November 2013

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.