Oracle n Oracle

These oracle stuffs might come in handy...

Monday, March 17, 2008

How to rename the listener.log file


The listener.log file might keep growing due to the messages logged. We cannot rename the log file on the fly as it will be always accessed by the tnslsnr process.

$ cd /u01/app/oracle/product/10.2.0/db_1/network/log

$ ls -ltr
-rw-r----- 1 oraprd oinstall 223258461 Mar 17 15:02 listener.log

$ fuser listener.log
listener.log: 27293

$ ps -ef grep 27293
oraprd 27293 1 0 Dec 02 ? 72:09 /u01/app/oracle/product/10.2.0/db_1/bin/tnslsnr LISTENER -inher

Fuser shows that its being used by the process 27293, which is the tnslsnr.

So,
$ lsnrctl
LSNRCTL>set Log_status off
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.169.198)(PORT=1523)(IP=FIRST))) listener parameter "log_status" set to ON
The command completed successfully
LSNRCTL>exit

$ mv listener.log to listener_old.log (from the command prompt)$ touch listener.log

$ lsnrctl
LSNRCTL>set Log_Status onConnecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.169.198)(PORT=1523)(IP=FIRST)))listener parameter "log_status" set to ON
The command completed successfully
LSNRCTL>exit

Or

If you do not want the messages to be logged set LOGGING_LISTENER=OFF in the listener.ora file

Wednesday, February 21, 2007

Oralce Backup and Recovery

Check the below links for the details

http://webhost1.njit.edu/~hoskins/oracle-hot-backup/oraclehotbackup-hoskins.pdf

Documentation:
http://oraclesvca2.oracle.com/docs/cd/B19306_01/backup.102/b14192/toc.htm


The below is an awesome link that comes out with the misconceptions that linger in the oracle ocean

http://www.ora-600.net/published.html

Setting a Standby DB

Setting a standby DB on the same box as that of the primary db.
=============================================

Physical Standby Database on the same machine as of Primary Database
==================================================================
1.Shutdown the Primary database

2.Copy all the datafiles,initPRI.ora and logs to the Standby destination.

3.mv initPRI.ora initSTBY.ora

4.In the initPRI.ora

LOG_ARCHIVE_DEST_1='LOCATION=/bankaus/db/TEST/dump/a'
LOG_ARCHIVE_DEST_2='SERVICE=STBYt'
#the service name should be there in the listener.ora and tnsnames.ora

5.In the initSTBY.ora DB_NAME= # should be primary db name if on the same machine
LOG_ARCHIVE_DEST=/bankaus/db/STBYt/dump/a/ standby_archive_dest=/bankaus/db/STBYt/dump/a/ #it will be in this path PRIMARY will keep the arch log file
db_file_name_convert="/u2/pri","/u2/stby" #this will convert the path for the with respect to the standby
log_file_name_convert="/u2/pri","/u2/stby"#if these convert parameters are not there, since the DB_NAME is same as the PRI, while applying archive logs the system will try to write to the Primary's Datafiles
LOCK_NAME_SPACE= #any thing other than primary SID, without this parameter it is not possible to mount Standby db as DB_NAME conflict will happen with the Primary. Also if still problem persists delete the file lk / LK in the $ORALCE_HOME/dbs/

6.Create the standby control file $$export ORACLE_SID=PRI $sqlplus "/ as sysdba" $sql>alter database create standby controlfile as '/dir1/dir2' Copy the control files to the stanby path witch is mentioned in the initSTBY.ora

7. Restart the listener service

8.Start the Primary db
$export ORACLE_SID=PRI
$sqlplus "/ as sysdba"
$sql>startup

9.Start the Standby in mounted state
$export ORACLE_SID=STBYt
$sqlplus "/ as sysdba"
$sql>startup nomount
$sql>alter database mount standby database
$sql>recover standby database #this applies the archive log created.
or
$sql>ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION # will happen in backgroud
$sql>ALTER DATABASE STANDBY DATABASE CANCEL
To keep the database in recovery mode $recover managed standby database; #this will keep on applying the logs as soon as it is generated in the standby archive path.

10.Switch a log file in the Primary
$sql>alter system archive log current;
$sql>archive log list

11.Check in the Standby path whether the file is created or not
$sql>select max(sequence#) from v$log_history;

Notes
======
We can 've 10 LOG_ARCHIVE_DEST_n destinations where n ranges from 1-10. The view V$ARCHIVE_DEST contains details of all the destinations.When an error occurs while writing to a particular location, the status of that LOG_ARCHIVE_DEST_n will be ERROR, once the problem is rectified we need to ALTER SYSTEM SET LOG_ARCHIVE_DEST_2='SERVICE=STBYt'; and now the status will be ACTIVE.

Reference: http://dba.shilpatech.com/stdby.html

Oracle User Adminstration

User Adminstration
===================

Check the below links for detailed user adminstration in Oracle Database.

http://orafaq.com/faqdbase.htm

http://www.psoug.org/reference/user.html

Friday, February 9, 2007

NLS Parameters

To get the list of all possible nls parameters
sql>select parameter,value from x$ksulv;

The view for all nls parameters set on the DB
v$nls_parameters

To set nls_lang in Windows Clients : http://www.lazydba.com/oracle/0__30008.html

The priority for nls vaues
http://forums.oracle.com/forums/thread.jspa;jsessionid=8d92200830de6ace293677e74410ba14b54a0a1566d4.e34QbhuKaxmMai0MaNeMb3eKaN90?messageID=1499595?

Check out this link for more abt Oracle NLS Parameters
http://www.databaseanswers.org/ora_nls_support.htm

Its said in metalink as
================
Subject: Character Sets & Conversion - Frequently Asked Questions

Doc ID: Note:227330.1