Important Oracle DBA Tips

1)Enabling and Disabling Automatic Extension for a Datafile

 alter database datafile  ‘/u01/app/oradata/ukdevdv1/system01.dbf’ autoextend off

2)Manually Resizing a Datafile

 ALTER DATABASE DATAFILE ‘/u01/app/oradata/ukdevdv1/system01.dbf’
   RESIZE 1560M;

3)Check Tablespace size

select b.tablespace_name, tbs_size SizeMb, a.free_space FreeMb
from  (select tablespace_name, round(sum(bytes)/1024/1024 ,2) as free_space
       from dba_free_space
       group by tablespace_name) a,
      (select tablespace_name, sum(bytes)/1024/1024 as tbs_size
       from dba_data_files
       group by tablespace_name) b
where a.tablespace_name(+)=b.tablespace_name;

Step By Step Guide On Creating Physical Standby Using RMAN(ASM)

This article explains steps to create physical standby database using Oracle Database 11g RMAN  FROM ACTIVE DATABASE capability to the DUPLICATE FOR STANDBY command.

Primary database Name:primdv1

Standby database Name:stbydv1

Step1)Make sure primary database is running in archive log mode.

2)Determine if FORCE LOGGING is enabled. If it is not enabled, enable FORCE LOGGING mode.
SQL> SELECT force_logging FROM v$database;

FOR
—
NO

SQL> ALTER DATABASE FORCE LOGGING;

Database altered.

SQL> SELECT force_logging FROM v$database;

FOR
—
YES

3)Configure the primary database to receive redo data, by adding the standby logfiles to the primary
It is highly recommended that you have one more standby redo log group than you have online redo log groups as the primary database.
The files must be the same size or larger than the primary database’s online redo logs.

ALTER DATABASE ADD STANDBY LOGFILE
‘+DATA1/primdv1/srl01.log’
SIZE 52428800
/

ALTER DATABASE ADD STANDBY LOGFILE
‘+DATA1/primdv1/srl02.log’
SIZE 52428800
/

ALTER DATABASE ADD STANDBY LOGFILE
‘+DATA1/primdv1/srl03.log’
SIZE 52428800
/

ALTER DATABASE ADD STANDBY LOGFILE
‘+DATA1/primdv1/srl04.log’
SIZE 52428800
/

4)Set primary database initialization parameters

ALTER SYSTEM SET log_archive_config = ‘dg_config=(primdv1,stbydv1)’;

ALTER SYSTEM SET log_archive_dest_2 =
‘service=stbydv1 async valid_for=(online_logfile,primary_role) db_unique_name=stbydv1′;

show parameter log_archive_config

show parameter log_archive_dest_2
If you get following error
ERROR at line 1:
ORA-02097: parameter cannot be modified because specified value is invalid
ORA-16019: cannot use LOG_ARCHIVE_DEST_1 with LOG_ARCHIVE_DEST or LOG_ARCHIVE_DUPLEX_DEST

Solution :

1. Reset the LOG_ARCHIVE_DEST to NULL value :

SQL> ALTER SYSTEM set log_archive_dest=”;

2. Set the LOG_ARCHIVE_DEST_1 to the value set to LOG_ARCHIVE_DEST before

SQL> ALTER SYSTEM set log_archive_dest_1=’LOCATION=/…’;

if you use this code, you dont need to restart database:

ALTER SYSTEM SET LOG_ARCHIVE_DEST_1=’LOCATION=h:\oradata\archive’ SCOPE=BOTH;

To check archival destination issue is ok:
show parameter LOG_ARCHIVE_DEST;
select DEST_ID, DEST_NAME,DESTINATION from v$archive_dest;

update tnsnames.ora file with the following entries on both hosts:

PRIMDV1 =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = dgprim0001.ukatru.com)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = primdv1.ukatru.com)
)
)

STBYDV1 =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = dgstby0001.ukatru.com)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = stbydv1.ukatru.com)
)
)
update the listner.ora with the following values on standby database:
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(GLOBAL_DBNAME = standby1.example.com)
(ORACLE_HOME = /u01/app/oracle/product/11.2.0/dbhome_1)
(SID_NAME = standby1)
)
)

copy the remote login password file (orapwprimdv1) from the primary database system to the $ORACLE_HOME/dbs directory on the
standby database system, renaming it to orapwstbydv1.
In the $ORACLE_HOME/dbs directory of the standby system, create an initialization parameter file named initstbydv1.ora
containing a single parameter: DB_NAME=stbydv1(i.e. stbydv1)

On the standby system, change to the /u01/app/oracle/admin directory. Create a directory with a name that matches your physical standby SID (i.e. stbydv1).
Change to the newly created directory (i.e. stbydv1) and create an adump directory.

now on the standby system log into database
export ORACLE_SID=stbydv1
dgstby0001:/u01/app/oracle/admin/stbydv1>export ORACLE_SID=stbydv1
dgstby0001:/u01/app/oracle/admin/stbydv1>sqlplus / as sysdba

SQL*Plus: Release 11.2.0.3.0 Production on Sat Apr 14 22:57:24 2012

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

Connected to an idle instance.

SQL>
SQL> startup nomount pfile=$ORACLE_HOME/dbs/initstbydv1.ora
ORACLE instance started.

Total System Global Area 238034944 bytes
Fixed Size 2227136 bytes
Variable Size 180356160 bytes
Database Buffers 50331648 bytes
Redo Buffers 5120000 bytes

RMAN> connect target sys

target database Password:
connected to target database: PRIMDV1 (DBID=1462743077)

RMAN> connect auxiliary sys@stbydv1

auxiliary database Password:
connected to auxiliary database: STBYDV1 (not mounted)

Below is the rman script to create standby database:
run {
allocate channel prmy1 type disk;
allocate channel prmy2 type disk;
allocate channel prmy3 type disk;
allocate channel prmy4 type disk;
allocate auxiliary channel stby type disk;
duplicate target database for standby from active database
spfile
parameter_value_convert ‘primdv1′,’stbydv1′
set db_unique_name=’stbydv1′
set db_file_name_convert=’/primdv1/’,’/stbydv1/’
set log_file_name_convert=’/primdv1/’,’/stbydv1/’
set control_files=’+DATA1′
set log_archive_max_processes=’5′
set fal_client=’stbydv1′
set fal_server=’primdv1′
set standby_file_management=’AUTO’
set log_archive_config=’dg_config=(primdv1,stbydv1)’
set log_archive_dest_2=’service=primdv1 ASYNC valid_for=(ONLINE_LOGFILE,PRIMARY_ROLE) db_unique_name=primdv1′
;
}

channel prmy3: SID=55 device type=DISK

allocated channel: prmy4
channel prmy4: SID=56 device type=DISK

allocated channel: stby
channel stby: SID=19 device type=DISK

Starting Duplicate Db at 14-APR-12

contents of Memory Script:
{
backup as copy reuse
targetfile ‘/u01/app/oracle/product/11.2.0.3/db_1/dbs/orapwprimdv1’ auxiliary format
‘/u01/app/oracle/product/11.2.0.3/db_1/dbs/orapwstbydv1’ targetfile
‘+DATA1/primdv1/spfileprimdv1.ora’ auxiliary format
‘/u01/app/oracle/product/11.2.0.3/db_1/dbs/spfilestbydv1.ora’ ;
sql clone “alter system set spfile= ”/u01/app/oracle/product/11.2.0.3/db_1/dbs/spfilestbydv1.ora””;
}
executing Memory Script

Starting backup at 14-APR-12
Finished backup at 14-APR-12

sql statement: alter system set spfile= ”/u01/app/oracle/product/11.2.0.3/db_1/dbs/spfilestbydv1.ora”

contents of Memory Script:
{
sql clone “alter system set audit_file_dest =
”/u01/app/oracle/admin/stbydv1/adump” comment=
”” scope=spfile”;
sql clone “alter system set dispatchers =
”(PROTOCOL=TCP) (SERVICE=stbydv1XDB)” comment=
”” scope=spfile”;
sql clone “alter system set log_archive_dest_1 =
”LOCATION=+DATA1/stbydv1” comment=
”” scope=spfile”;
sql clone “alter system set db_unique_name =
”stbydv1” comment=
”” scope=spfile”;
sql clone “alter system set db_file_name_convert =
”/primdv1/”, ”/stbydv1/” comment=
”” scope=spfile”;
sql clone “alter system set log_file_name_convert =
”/primdv1/”, ”/stbydv1/” comment=
”” scope=spfile”;
sql clone “alter system set control_files =
”+DATA1” comment=
”” scope=spfile”;
sql clone “alter system set log_archive_max_processes =
5 comment=
”” scope=spfile”;
sql clone “alter system set fal_client =
”stbydv1” comment=
”” scope=spfile”;
sql clone “alter system set fal_server =
”primdv1” comment=
”” scope=spfile”;
sql clone “alter system set standby_file_management =
”AUTO” comment=
”” scope=spfile”;
sql clone “alter system set log_archive_config =
”dg_config=(primdv1,stbydv1)” comment=
”” scope=spfile”;
sql clone “alter system set log_archive_dest_2 =
”service=primdv1 ASYNC valid_for=(ONLINE_LOGFILE,PRIMARY_ROLE) db_unique_name=primdv1” comment=
”” scope=spfile”;
shutdown clone immediate;
startup clone nomount;
}
executing Memory Script

sql statement: alter system set audit_file_dest = ”/u01/app/oracle/admin/stbydv1/adump” comment= ”” scope=spfile

sql statement: alter system set dispatchers = ”(PROTOCOL=TCP) (SERVICE=stbydv1XDB)” comment= ”” scope=spfile

sql statement: alter system set log_archive_dest_1 = ”LOCATION=+DATA1/stbydv1” comment= ”” scope=spfile

sql statement: alter system set db_unique_name = ”stbydv1” comment= ”” scope=spfile

sql statement: alter system set db_file_name_convert = ”/primdv1/”, ”/stbydv1/” comment= ”” scope=spfile

sql statement: alter system set log_file_name_convert = ”/primdv1/”, ”/stbydv1/” comment= ”” scope=spfile

sql statement: alter system set control_files = ”+DATA1” comment= ”” scope=spfile

sql statement: alter system set log_archive_max_processes = 5 comment= ”” scope=spfile

sql statement: alter system set fal_client = ”stbydv1” comment= ”” scope=spfile

sql statement: alter system set fal_server = ”primdv1” comment= ”” scope=spfile

sql statement: alter system set standby_file_management = ”AUTO” comment= ”” scope=spfile

sql statement: alter system set log_archive_config = ”dg_config=(primdv1,stbydv1)” comment= ”” scope=spfile

sql statement: alter system set log_archive_dest_2 = ”service=primdv1 ASYNC valid_for=(ONLINE_LOGFILE,PRIMARY_ROLE) db_unique_name=primdv1” comment= ”” scope=spfile

Oracle instance shut down

connected to auxiliary database (not started)
Oracle instance started

Total System Global Area 1068937216 bytes

Fixed Size 2235208 bytes
Variable Size 616563896 bytes
Database Buffers 444596224 bytes
Redo Buffers 5541888 bytes
allocated channel: stby
channel stby: SID=21 device type=DISK

contents of Memory Script:
{
sql clone “alter system set control_files =
”+DATA1/stbydv1/controlfile/current.256.780620989” comment=
”Set by RMAN” scope=spfile”;
backup as copy current controlfile for standby auxiliary format ‘+DATA1/stbydv1/controlfile/current.257.780620989’;
sql clone “alter system set control_files =
”+DATA1/stbydv1/controlfile/current.257.780620989” comment=
”Set by RMAN” scope=spfile”;
shutdown clone immediate;
startup clone nomount;
}
executing Memory Script

sql statement: alter system set control_files = ”+DATA1/stbydv1/controlfile/current.256.780620989” comment= ”Set by RMAN” scope=spfile

Starting backup at 14-APR-12
channel prmy1: starting datafile copy
copying standby control file
output file name=/u01/app/oracle/product/11.2.0.3/db_1/dbs/snapcf_primdv1.f tag=TAG20120414T230949 RECID=1 STAMP=780620989
channel prmy1: datafile copy complete, elapsed time: 00:00:03
Finished backup at 14-APR-12

sql statement: alter system set control_files = ”+DATA1/stbydv1/controlfile/current.257.780620989” comment= ”Set by RMAN” scope=spfile

Oracle instance shut down

connected to auxiliary database (not started)
Oracle instance started

Total System Global Area 1068937216 bytes

Fixed Size 2235208 bytes
Variable Size 616563896 bytes
Database Buffers 444596224 bytes
Redo Buffers 5541888 bytes
allocated channel: stby
channel stby: SID=23 device type=DISK

contents of Memory Script:
{
sql clone ‘alter database mount standby database’;
}
executing Memory Script

sql statement: alter database mount standby database
RMAN-05529: WARNING: DB_FILE_NAME_CONVERT resulted in invalid ASM names; names changed to disk group only.

contents of Memory Script:
{
set newname for tempfile 1 to
“+data1”;
switch clone tempfile all;
set newname for datafile 1 to
“+data1”;
set newname for datafile 2 to
“+data1”;
set newname for datafile 3 to
“+data1”;
set newname for datafile 4 to
“+data1”;
backup as copy reuse
datafile 1 auxiliary format
“+data1” datafile
2 auxiliary format
“+data1” datafile
3 auxiliary format
“+data1” datafile
4 auxiliary format
“+data1” ;
sql ‘alter system archive log current’;
}
executing Memory Script

executing command: SET NEWNAME

renamed tempfile 1 to +data1 in control file

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

Starting backup at 14-APR-12
channel prmy1: starting datafile copy
input datafile file number=00001 name=+DATA1/primdv1/datafile/system.261.780607599
channel prmy2: starting datafile copy
input datafile file number=00002 name=+DATA1/primdv1/datafile/sysaux.262.780607621
channel prmy3: starting datafile copy
input datafile file number=00003 name=+DATA1/primdv1/datafile/undotbs1.263.780607639
channel prmy4: starting datafile copy
input datafile file number=00004 name=+DATA1/primdv1/datafile/users.265.780607653
output file name=+DATA1/stbydv1/datafile/users.260.780621011 tag=TAG20120414T231009
channel prmy4: datafile copy complete, elapsed time: 00:00:08
output file name=+DATA1/stbydv1/datafile/undotbs1.261.780621011 tag=TAG20120414T231009
channel prmy3: datafile copy complete, elapsed time: 00:01:47
output file name=+DATA1/stbydv1/datafile/system.258.780621011 tag=TAG20120414T231009
channel prmy1: datafile copy complete, elapsed time: 00:02:28
output file name=+DATA1/stbydv1/datafile/sysaux.259.780621011 tag=TAG20120414T231009
channel prmy2: datafile copy complete, elapsed time: 00:02:27
Finished backup at 14-APR-12

sql statement: alter system archive log current

contents of Memory Script:
{
switch clone datafile all;
}
executing Memory Script

datafile 1 switched to datafile copy
input datafile copy RECID=1 STAMP=780621158 file name=+DATA1/stbydv1/datafile/system.258.780621011
datafile 2 switched to datafile copy
input datafile copy RECID=2 STAMP=780621158 file name=+DATA1/stbydv1/datafile/sysaux.259.780621011
datafile 3 switched to datafile copy
input datafile copy RECID=3 STAMP=780621158 file name=+DATA1/stbydv1/datafile/undotbs1.261.780621011
datafile 4 switched to datafile copy
input datafile copy RECID=4 STAMP=780621158 file name=+DATA1/stbydv1/datafile/users.260.780621011
Finished Duplicate Db at 14-APR-12
released channel: prmy1
released channel: prmy2
released channel: prmy3
released channel: prmy4
released channel: stby

RMAN> **end-of-file**
Perform a log switch on the primary database and redo will start being sent to the standby.
dgprim0001:/u01/app/oracle/wkdir>sqlplus / as sysdba

SQL*Plus: Release 11.2.0.3.0 Production on Sat Apr 14 23:14:57 2012

Copyright (c) 1982, 2011, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 – 64bit Production
With the Partitioning, Automatic Storage Management, OLAP, Data Mining
and Real Application Testing options

SQL> alter system switch logfile;

System altered.

dgstby0001:/u01/app/oracle/product/11.2.0.3/db_1/network/admin>sqlplus / as sysdba

SQL*Plus: Release 11.2.0.3.0 Production on Sat Apr 14 23:15:50 2012

Copyright (c) 1982, 2011, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 – 64bit Production
With the Partitioning, Automatic Storage Management, OLAP, Data Mining
and Real Application Testing options

SQL> alter database recover managed standby database
2 using current logfile disconnect;

Database altered.

Now Verify that the Physical Standby Database is Performing Correctly

SELECT sequence#, first_time, next_time, applied
FROM v$archived_log
ORDER BY sequence#
/

SQL> SELECT sequence#, first_time, next_time, applied
FROM v$archived_log
ORDER BY sequence#
/
2 3 4
SEQUENCE# FIRST_TIM NEXT_TIME APPLIED
———- ——— ——— ———
31 14-APR-12 14-APR-12 YES
32 14-APR-12 14-APR-12 YES
33 14-APR-12 14-APR-12 YES
34 14-APR-12 14-APR-12 YES
35 14-APR-12 14-APR-12 YES
36 14-APR-12 14-APR-12 IN-MEMORY

6 rows selected.

How to install oracle TimesTen 11g on RedHat Enterprise Linux

In this article we will learn how to install oracle TimesTen in memory-database on Redhat Enterprise Linux:

dbst8001:/etc>cat redhat-release
Red Hat Enterprise Linux Server release 5.5 (Tikanga)
dbst8001:/etc>id
uid=1000(oracle) gid=1000(oinstall) groups=1000(oinstall),1001(dba)
Create a directory /etc/TimesTen.
mkdir -p /etc/TimesTen
chown -R oracle:dba /etc/TimesTen
dbst8001:/u01/soft>ls -tlr
total 297408
drwxrwxrwx 5 root   root          4096 Feb  2  2011 linux8664
-rw-r--r-- 1 oracle oinstall 304236457 Nov  5 10:15 timesten112180.linux8664.tar.gz
run setup.sh to start installation.
dbst8001:/u01/soft/linux8664>./setup.sh

NOTE: Each TimesTen installation is identified by a unique instance name.
      The instance name must be a non-null alphanumeric string, not longer
      than 255 characters.

Please choose an instance name for this installation? [ tt1121 ]
Instance name will be 'tt1121'.
Is this correct? [ yes ] yes

Of the three components:

  [1] Client/Server and Data Manager
  [2] Data Manager Only
  [3] Client Only

Which would you like to install? [ 1 ] 1

Of the following options :

  [1] /home/oracle
  [2] /u01/soft
  [3] Specify a location
  [q] Quit the installation

Where would you like to install the tt1121 instance of TimesTen? [ 1 ] /u01/app
The directory /u01/app does not exist.
Do you want to create it? [ yes ] yes
Where would you like to create the daemon home directory? [ /u01/app/TimesTen/tt1121/info ]

The daemon logs will be located in /u01/app/TimesTen/tt1121/info
Would you like to specify a different location for the daemon logs? [ no ]
Installing into /u01/app/TimesTen/tt1121 ...
Uncompressing ...

NOTE: If you are configuring TimesTen for use with Oracle Clusterware, the
      daemon port number must be the same across all TimesTen installations
      managed within the same Oracle Clusterware cluster.

NOTE: All installations that replicate to each other must use the same daemon
      port number that is set at installation time. The daemon port number can
      be verified by running 'ttVersion'.

The default port number is 53388.

Do you want to use the default port number for the TimesTen daemon? [ yes ] yes
The daemon will run on the default port number (53388).

NOTE: For security, we recommend that you restrict access to the
      TimesTen installation to members of a single OS group. Only members of
      that OS group will be allowed to perform direct mode connections to
      TimesTen, and only members of that OS group will be allowed to perform
      operations that access TimesTen data stores, TimesTen files and shared
      memory. The OS group defaults to the primary group of the instance
      administrator. You can default to this group, choose another OS group
      or you can make this instance world-accessible. If you choose to make
      this instance world-accessible, all database files and shared memory
      are readable and writable by all users.

Restrict access to the the TimesTen installation to the group 'oinstall'? [ yes ]

NOTE: Enabling PL/SQL will increase the size of some TimesTen libraries.

Would you like to enable PL/SQL for this instance? [ yes ]

In order to use the 'In-Memory Database Cache' feature in any databases
created within this installation, you must set a value for the TNS_ADMIN
environment variable. It can be left blank, and a value can be supplied later
using <install_dir>/bin/ttModInstall.

Please enter a value for TNS_ADMIN (s=skip)? [  ] s


NOTE: It appears that you are running version 4.1 of the g++
      compiler. TimesTen ships with multiple sets of client libraries and server
      binaries : one built for compatibility with g++ 3.4.6 and one with
      g++ 4.1.0. The installer has created links to the 4.1.0 library in the
      <install_dir>/lib directory and to the 4.1.0 server binary in the
      <install_dir>/bin directory. If you want to use a different compiler,
      please modify the links to point to the desired library and server binary.

Installing server components ...
What is the TCP/IP port number that you want the TimesTen Server to listen on? [ 53389 ]
Do you want to install QuickStart and the TimesTen Documentation? [ no ]
Would you like to install the documentation (without QuickStart)? [ yes ]
Where would you like to create the doc directory (s=skip)? [ /u01/app/TimesTen/tt1121/doc ]
The TimesTen documentation has been installed in /u01/app/TimesTen/tt1121/doc.
Installing client components ...

Would you like to use TimesTen Replication with Oracle Clusterware? [ no ]

NOTE: The TimesTen daemon startup/shutdown scripts have not been installed.

Run the 'setuproot' script :
        cd /u01/app/TimesTen/tt1121/bin
        ./setuproot -install
This will move the TimesTen startup script into its appropriate location.

The startup script is currently located here :
  '/u01/app/TimesTen/tt1121/startup/tt_tt1121'.

The 11.2.1.8 Release Notes are located here :
  '/u01/app/TimesTen/tt1121/README.html'

Starting the daemon ...
TimesTen Daemon startup OK.
End of TimesTen installation.

Now run the ttenv script.
dbst8001:/u01/app/TimesTen/tt1121/bin>ls ttenv*
ttenv.csh  ttenv.sh
dbst8001:/u01/app/TimesTen/tt1121/bin>. ttenv.sh

LD_LIBRARY_PATH set to /u01/app/TimesTen/tt1121/lib:/u01/app/TimesTen/tt1121/ttoracle_home/instantclient_11_1

ANT_HOME set to /u01/app/TimesTen/tt1121/3rdparty/ant

PATH set to /u01/app/TimesTen/tt1121/bin:/u01/app/TimesTen/tt1121/quickstart/sample_code/oci:/u01/app/TimesTen/tt1121/quickstart/sample_code/odbc:/u01/app/TimesTen/tt1121/quickstart/sample_code/odbc/xla:/u01/app/TimesTen/tt1121/quickstart/sample_code/jdbc:/u01/app/TimesTen/tt1121/quickstart/sample_code/odbc_drivermgr:/u01/app/TimesTen/tt1121/quickstart/sample_code/proc:/u01/app/TimesTen/tt1121/quickstart/sample_code/ttclasses:/u01/app/TimesTen/tt1121/quickstart/sample_code/ttclasses/xla:/u01/app/TimesTen/tt1121/ttoracle_home/instantclient_11_1:/u01/app/TimesTen/tt1121/ttoracle_home/instantclient_11_1/sdk:/u01/app/TimesTen/tt1121/3rdparty/ant/bin:/usr/kerberos/bin:/usr/local/bin:/bin:/usr/bin:/home/oracle/bin:/u01/app/oracle/product/11.2.0.2/db_1/bin

CLASSPATH set to /u01/app/TimesTen/tt1121/lib/ttjdbc5.jar:/u01/app/TimesTen/tt1121/lib/orai18n.jar:/u01/app/TimesTen/tt1121/lib/timestenjmsxla.jar:/u01/app/TimesTen/tt1121/3rdparty/jms1.1/lib/jms.jar:.

dbst8001:/u01/app/TimesTen/tt1121/bin>ttversion
TimesTen Release 11.2.1.8.0 (64 bit Linux/x86_64) (tt1121:53388) 2011-02-02T02:20:46Z
  Instance admin: oracle
  Instance home directory: /u01/app/TimesTen/tt1121
  Group owner: oinstall
  Daemon home directory: /u01/app/TimesTen/tt1121/info
  PL/SQL enabled.
dbst8001:/u01/app/TimesTen/tt1121/bin>ttstatus
TimesTen status report as of Sat Nov  5 10:47:08 2011

Daemon pid 5391 port 53388 instance tt1121
TimesTen server pid 5405 started on port 53389
------------------------------------------------------------------------
Accessible by group oinstall
End of report

UDEV rules for device persistency-ownerships in REDHAT 6.1 for ASM

In linux ASMLib is used to make sure device persistency and ownership after a reboot.UDEV is another alternative to ASMlib to achieve device persistency and ownership on devices. This article outlines the steps that are required to establish device persistency and setup new device names with desired permissions and ownership in REDHAT6.1 using udev.

Universally Unique Identifiers (UUIDs) are a standardized method for identifying computers and devices in distributed computing environments. This section uses UUIDs to identify iSCSI, SRP, or Fibre Channel LUNs. UUIDs persist after restarts, disconnection and device swaps. The UUID is similar to a label on the device.

[root@dbsl2001 etc]# cat redhat-release
Red Hat Enterprise Linux Server release 6.1 (Santiago)

Single path configuration

Edit the /etc/scsi_id.config file.

Add the following line:

options=–whitelisted –replace-whitespace

To display the UUID for a given device run the scsi_id –whitelisted –replace-whitespace –device=/dev/sd* command.
example:
[root@dbsl2001 etc]# scsi_id –whitelisted –replace-whitespace –device=/dev/sdd
14f504e46494c45525971777468462d5a3277382d42727976

Verify the UUID output from the scsi_id –whitelisted –replace-whitespace –device=/dev/sd* command is correct and as expected.
Create a rule to name the device. Create a file named 20-names.rules in the /etc/udev/rules.d directory. Add new rules to this file. All rules are added to the same file using the same format. Rules follow this format:

KERNEL==”sd*”, SUBSYSTEM==”block”, PROGRAM=”/sbin/scsi_id –whitelisted –replace-whitespace /dev/$name”, RESULT==”UUID”, NAME=”devicename”,OWNER=”<OS user>”, GROUP=”,OS group>”, MODE=”0660?

Replace UUID and devicename with the UUID retrieved above, and a name for the device. This is an example for the rule above for three example iSCSI luns:

KERNEL==”sd*”, SUBSYSTEM==”block”, PROGRAM=”/sbin/scsi_id –whitelisted –replace-whitespace /dev/$name”, RESULT==”14f504e46494c45525971777468462d5a3277382d42727976″, NAME=”asm1″,OWNER=”grid”,GROUP=”dba”,MODE=”0660″

[root@dbsl2001 rules.d]# udevadm test /block/sdd

[root@dbsl2001 rules.d]# start_udev
Starting udev:                                             [  OK  ]

Check the names, ownerships and permissions of the devices.
[root@dbsl2001 rules.d]# ls -la /dev/asm*
brw-rw—-. 1 grid disk 8, 49 Sep  5 00:20 /dev/asm1

ora-01653 unable to extend table in tablespace

I got the below error message when i tried to insert a record into table.

ora-01653 unable to extend table in tablespace

Now we need to query the dba_data_files tables and see autoextend is off or not.

select file_name,tablespace_name,autoextensible from dba_data_files
where tablespace_name=’TBS_DS_OWNER’

If value of “AUTOEXTENSIBLE” column is NO, check how many bytes are used by Oracle and max size of the tables space by using following queries.

how many bytes used by oracle:

SELECT
tablespace_name,
SUM(bytes/1024/1024)
FROM
dba_segments
GROUP BY
tablespace_name;

Max  size of the tablespace:

select tablespace_name,sum(bytes)/1024/1024 “Max Size in MB” from dba_data_files
where tablespace_name=’TBS_DS_OWNER’
group by tablespace_name

If the difference between this value and max size value is small then we need to add datafile or increse the size of the the datafile to insert more records.

Thanks

oracle dataguard setup using enterprise manager grid control 11g

In this article i will explain steps used to create physical stand by database using Oracle enterprise manager grid control 11g.

Log into 11g grid control usign sysman.

Primary Database details:

Database Instance: ukdgpdv1.ukatru.com

Instance Name : ukdgsdv1

dg — dataguard

s–standby

Thanks

Uma

Raw Device Setup on linux– oracle 10gr2 and 11gr1 for ocr and voting disks

Raw Device Setup

This step is only necessary if you want ocr and voting disks on raw devices without using any cluster file system(OCFS2,GPS,NFS,..)

[root@oral7001 iscsi]# fdisk -l

Disk /dev/sda: 16.1 GB, 16106127360 bytes
255 heads, 63 sectors/track, 1958 cylinders
Units = cylinders of 16065 * 512 = 8225280 bytes

Device Boot      Start         End      Blocks   Id  System
/dev/sda1   *           1          13      104391   83  Linux
/dev/sda2              14        1958    15623212+  8e  Linux LVM

Disk /dev/sdb: 21.4 GB, 21474836480 bytes
255 heads, 63 sectors/track, 2610 cylinders
Units = cylinders of 16065 * 512 = 8225280 bytes

Device Boot      Start         End      Blocks   Id  System
/dev/sdb1               1        2610    20964793+  83  Linux

Disk /dev/sdc: 1073 MB, 1073741824 bytes
34 heads, 61 sectors/track, 1011 cylinders
Units = cylinders of 2074 * 512 = 1061888 bytes

Device Boot      Start         End      Blocks   Id  System
/dev/sdc1               1        1011     1048376+  83  Linux

Disk /dev/sdd: 1073 MB, 1073741824 bytes
34 heads, 61 sectors/track, 1011 cylinders
Units = cylinders of 2074 * 512 = 1061888 bytes

Device Boot      Start         End      Blocks   Id  System
/dev/sdd1               1        1011     1048376+  83  Linux

Disk /dev/sde: 1073 MB, 1073741824 bytes
34 heads, 61 sectors/track, 1011 cylinders
Units = cylinders of 2074 * 512 = 1061888 bytes

Device Boot      Start         End      Blocks   Id  System
/dev/sde1               1        1011     1048376+  83  Linux

Edit the /etc/sysconfig/rawdevices file, adding the following lines.I am using iscsi disks and hence used udev rules for persistent naming conventions.

[root@oral7001 iscsi]# ls -tlr
total 0
drwxr-xr-x 2 root root 80 Aug  6 12:06 oral7001_crs1
drwxr-xr-x 2 root root 80 Aug  6 12:09 oral7001_crs2
drwxr-xr-x 2 root root 80 Aug  6 12:10 oral7001_crs3

[root@oral7001 sysconfig]# cat rawdevices
# raw device bindings
# format:  <rawdev> <major> <minor>
#          <rawdev> <blockdev>
# example: /dev/raw/raw1 /dev/sda1
#          /dev/raw/raw2 8 5
/dev/raw/raw1   /dev/iscsi/oral7001_crs1/part1
/dev/raw/raw2   /dev/iscsi/oral7001_crs2/part1
/dev/raw/raw3  /dev/iscsi/oral7001_crs3/part1

[root@oral7001 sysconfig]# chkconfig –list | grep raw
rawdevices      0:off   1:off   2:off   3:off   4:off   5:off   6:off
[root@oral7001 sysconfig]# chkconfig –levels 345 rawdevices on

Start the rawdevices service using the following command.

[root@oral7001 sysconfig]# service rawdevices restart
Assigning devices:
/dev/raw/raw1  –>   /dev/iscsi/oral7001_crs1/part1
/dev/raw/raw1:  bound to major 8, minor 49
/dev/raw/raw2  –>   /dev/iscsi/oral7001_crs2/part1
/dev/raw/raw2:  bound to major 8, minor 65
/dev/raw/raw3  –>   /dev/iscsi/oral7001_crs3/part1
/dev/raw/raw3:  bound to major 8, minor 33
done

[root@oral7001 tmp]# cat raw_set_permissions.sh
chown oracle:oinstall /dev/raw/raw1
chown oracle:oinstall /dev/raw/raw2
chown oracle:oinstall /dev/raw/raw3
chmod 600 /dev/raw/raw1
chmod 600 /dev/raw/raw2
chmod 600 /dev/raw/raw3

Note : Installing Oracle Clusterware files on raw or block devices is no longer supported.

Thanks

Removing a node from Oracle RAC 11gR2

In this article we will see how to remove an unreachable or destroyed node from an existing 11gr2 cluster.

The example below is based on 11.2.0.2 Release.

I have 2 node oracle rac on esxi and i am powering off second node and you can see the following message in the first node log file.

cd /u01/app/root/product/11.2.0/grid/log/oral8001

[cssd(11407)]CRS-1601:CSSD Reconfiguration complete. Active nodes are oral8001 .
2011-08-05 10:54:11.301
[crsd(11662)]CRS-5504:Node down event reported for node ‘oral8002’.
2011-08-05 10:54:11.488
[crsd(11662)]CRS-2773:Server ‘oral8002’ has been removed from pool ‘Free’.

Assumption is node2 is completely dead and we are deleting it from the existing cluster.Now node2 is not reachable then use node1 to do the cleanup process.

$ ./olsnodes -s -t
oral8001        Active  Unpinned
oral8002        Inactive        Unpinned

[root@oral8001 bin]# ./crsctl delete node -n oral8002
CRS-4661: Node oral8002 successfully deleted.

$ pwd
/u01/app/root/product/11.2.0/grid/oui/bin

$ ls
addLangs.sh  attachHome.sh  filesList.bat         filesList.sh  resource      runInstaller     runSSHSetup.sh
addNode.sh   detachHome.sh  filesList.properties  lsnodes       runConfig.sh  runInstaller.sh

$ ./runInstaller -updateNodelist ORACLE_HOME=/u01/app/root/product/11.2.0/grid “CLUSTER_NODES={oral8001}” CRS=true
Starting Oracle Universal Installer…

Checking swap space: must be greater than 500 MB.   Actual 2391 MB    Passed
The inventory pointer is located at /etc/oraInst.loc
The inventory is located at /u01/app/oraInventory
‘UpdateNodeList’ was successful.

$ ./olsnodes -s -n
oral8001        1       Active

if you have any database instances on this node use dbca and select instance management and delete the instance.

 

Thanks

oracle data integrator 11g installation — linux

This article walks you through the steps to install oracle data integrator 11g

Os : Red Hat Enterprise Linux Server release 5.4 (Tikanga)

[root@odil8001 logs]# uname -r
2.6.18-164.el5

Prerequisites:

1)Have installed Oracle Database 11g. If not done before, start the services and components for Oracle Database 11g

2)Install Oracle WebLogic Server 11gR1 (10.3.5)

Download wls1035_generic.jar to install on 64 bit linux.

-rw-r–r–  1 oracle oinstall 1122139308 Jul 13 20:32 wls1035_generic.jar
$ export DISPLAY=192.168.2.154:0.0
$ /u01/jdk1.6.0_18/bin/java -d64 -jar wls1035_generic.jar
Extracting 0%……………………………………………………………………………………….100%

3)Down load repository creation utility and start creating repository for ODI 11g installation:

/u01/rcuHome/bin/rcu

4)Install odi 11g

$ ./runInstaller
Starting Oracle Universal Installer…

Checking if CPU speed is above 300 MHz.    Actual 3200 MHz    Passed
Checking Temp space: must be greater than 300 MB.   Actual 6380 MB    Passed
Checking swap space: must be greater than 512 MB.   Actual 2559 MB    Passed
Checking monitor: must be configured to display at least 256 colors.    Actual 16777216    Passed
Preparing to launch Oracle Universal Installer from /tmp/OraInstall2011-07-13_09-05-42PM. Please wait …
Please specify JDK location ( Ex. /home/jdk ), <location>/bin/java should exist :/u01/jdk1.6.0_18

Starting odi console:

/u01/app/oracle/Middleware/Oracle_ODI1/oracledi/client/$ ./odi.sh

Oracle Data Integrator 11g
Copyright (c) 1997, 2011, Oracle and/or its affiliates. All rights reserved.

Deploying and Configuring the ODI Agent as a Java EE Application :
$ pwd
/u01/app/oracle/Middleware/Oracle_ODI1/common/bin
$ ./config.sh

Deploying and Configuring the ODI Agent as a Java EE Application

In the Select Domain Source screen, just click Next.

$ pwd
/u01/app/oracle/Middleware/Oracle_ODI1/oracledi/client
$ ./odi.sh

Oracle Data Integrator 11g
Copyright (c) 1997, 2011, Oracle and/or its affiliates. All rights reserved

In the Logical architecture, create a new logical agent with the same name as your new physical agent

$ pwd
/u01/app/oracle/Middleware/Oracle_ODI1/common/bin

$ pwd
/u01/app/oracle/Middleware/user_projects/domains/odi_domain
$ ./startWebLogic.sh

script to startup single instance oracle database

#!/bin/ksh
#——————————————————————
# name : oradbstart.ksh
# This script is used to shudown oracle database
# Arguments $1 = ORACLE_SID(Required)
# Argument $2 = open,mount(optional)
# If argument 2 is not specified then default open
# usage: orashutdb.ksh <ora sid> [<startup type>]
#       Created By : Uma M Katru
#       Date : 07-08-2010
#——————————————————————-
dbstartUsage()
{
printf “\nError: $Me – incorrect number of arguments!\n\n”
printf “Usage: $Me <ora sid> [open | mount ].\n\n”
printf “where:\n\n”
printf ”  <ora sid>   : is the unique oracle instance identifier to shutdown non rac and rac instance on the local node required.\n”
printf ”   [<shutdown type>]      : startup type (Valid start up arguments are:  open,mount.\n”
printf “examples:\n”
printf ”  $Me oradv1  — startup database and open\n”
printf ”  $Me oradv1 mount — Starting an Instance and Mounting a Database\n”
exit 1
}
#—————————————————————————-
#Check init or file for the given database instance
#—————————————————————————-
dbstartCheckinitora(){
#check spfile first
spfile=$ORACLE_HOME/dbs/spfile$ORACLE_SID.ora
spfileexists=NO
fileexists=NO
pfile=$ORACLE_HOME/dbs/init$ORACLE_SID.ora
if [[ -f $spfile ]];then
spfileexists=YES
elif [[ -f $pfile ]];then
pfileexists=YES
else
echo “neither spfile nor pfile exists to start $ORACLE_SID database instance”
echo “Please check the issue and restart the process”
upLog “$Me aborted @$(\date)”
exit 1
fi
}
#############################################################################
# write log entries
# $1 is the actual message to log
# —————————————————————————–
upLog()
{
\echo “$1”
}
# —————————————————————————–
# main
#—————————————————————————–
Me=$(\basename $0)
mypath=$(\dirname $0)
export ORACLE_BASE=/u01/app/oracle
#set -vx

args=$#
if [[ $args = 1 ]]; then
ORACLE_SID=$1
STARTMODE=open
elif [[ $args = 2 ]];then
ORACLE_SID=$1
STARTMODE=$2
else
dbstartUsage
fi

#check sid exists in oratab
sidexists=$(grep -i “^$ORACLE_SID:” /etc/oratab |wc -l | awk ‘{print $1}’)
if [[ $sidexists -eq 1 ]];then
echo
ORACLE_HOME=$(grep -w ^”$ORACLE_SID” /etc/oratab | awk -F”:” ‘{print $2}’)
else
echo “oracle instance is not available  on this machine”
exit 1
fi
#Check spfile exists
export ORACLE_SID=$ORACLE_SID
export ORACLE_HOME=$ORACLE_HOME
upLog “$Me started @$(\date)”
upLog “ORACLE_BASE ………………:$ORACLE_BASE”
upLog “ORACLE_HOME ………………:$ORACLE_HOME”
upLog “DB_NAME ………………….:$ORACLE_SID”
dbstartCheckinitora

#Check if db is running or not
ChekDBRunning=`ps -ef | grep -v grep | grep ora_.*_${ORACLE_SID} | wc -l`
if [ $ChekDBRunning -lt 1 ]; then
echo “Info: Database $ORACLE_SID is not up,we can safely start the database .”
else
echo “$ORACLE_SID is already up and running,we can startup shutdown databases only”
exit 0
fi
LogDir=/home/oracle/admin/logs/$ORACLE_SID
if [[ ! -d /home/oracle/admin/logs/$ORACLE_SID ]];then
mkdir -p /home/oracle/admin/logs/$ORACLE_SID
fi
logFile=$LogDir/$(\echo $Me | \awk -F. ‘{print $1}’)$ORACLE_SID.blog.$(\date +%Y%m%d)

echo “Starting up $ORACLE_SID … Please wait…”
$ORACLE_HOME/bin/sqlplus “/ as sysdba”  << ! >> $logFile
startup $STARTMODE
exit
!
if [[ $rc -eq 0 ]] ; then
echo “”
echo “$Me: Database $ORACLE_SID started successfully.”
echo “$Me: Check $logFile logfile for complete details”
upLog “$Me finished @$(\date)”
echo “”
else
echo “”
echo “$Me: unable to startup $ORACLE_SID database instance,Please see log file for details.”
echo “$Me: Check $logFile logfile for complete details”
upLog “$Me failed to start $ORACLE_SID @$(\date)”
exit 1
fi
echo “—————————————————————-” >> $logFile
exit 0

 

Error: orastartdb.ksh – incorrect number of arguments!

Usage: orastartdb.ksh <ora sid> [open | mount ].

where:

<ora sid>   : is the unique oracle instance identifier to shutdown non rac and rac instance on the local node required.
[<shutdown type>]      : startup type (Valid start up arguments are:  open,mount.
examples:
orastartdb.ksh oradv1  — startup database and open
orastartdb.ksh oradv1 mount — Starting an Instance and Mounting a Database

 

====entry exisis in oratab but pfile or spfile is missing

./orastartdb.ksh uk10204

orastartdb.ksh started @Mon Jul 11 12:25:55 PDT 2011
ORACLE_BASE ………………:/u01/app/oracle
ORACLE_HOME ………………:/u01/app/oracle/product/10.2.0.5/db_1
DB_NAME ………………….:uk10204
neither spfile nor pfile exists to start uk10204 database instance
Please check the issue and restart the process
orastartdb.ksh aborted @Mon Jul 11 12:25:55 PDT 2011

======

./orastartdb.ksh uk10205

orastartdb.ksh started @Mon Jul 11 12:26:11 PDT 2011
ORACLE_BASE ………………:/u01/app/oracle
ORACLE_HOME ………………:/u01/app/oracle/product/10.2.0.5/db_1
DB_NAME ………………….:uk10205
Info: Database uk10205 is not up,we can safely start the database .
Starting up uk10205 … Please wait…

orastartdb.ksh: Database uk10205 started successfully.
orastartdb.ksh: Check /home/oracle/admin/logs/uk10205/orastartdbuk10205.blog.20110711 logfile for complete details
orastartdb.ksh finished @Mon Jul 11 12:26:29 PDT 2011

=====

./orastartdb.ksh uk10205

orastartdb.ksh started @Mon Jul 11 12:27:00 PDT 2011
ORACLE_BASE ………………:/u01/app/oracle
ORACLE_HOME ………………:/u01/app/oracle/product/10.2.0.5/db_1
DB_NAME ………………….:uk10205
uk10205 is already up and running,we can startup shutdown databases only

 

Thanks