Wednesday, December 7, 2011

HOWTO reset root password in linux redhat

Today when i trying to connect to our development database on linux server with root user, but i forget the correct password. :) because i was logging after a 10 months. after that i need to reset root password and this is the following step.

linux machine :
Red Hat Enterprise Linux AS release 4 (Nahant Update 3)
and grub loader.

step.
1. reboot machine
2. wait for GRUB splash screen appear
3. then click on "e" keyword in keyboard (hit enter)
4. after that another window will open again click on "e" keyword in keyword to select "KERNAL ... " from list of value. (hit enter)
5. then type "single" included space (hit enter)
6. then type "b" from keyboard to boot (hit enter)
7. system boot up as single user mode
8. you will see # prompt , here type "passwd" command to change root password
9. reboot
10. enjoy to logging with new user

reference: http://www.wallpaperama.com/forums/how-to-reset-linux-root-forgotten-passwords-get-retrieve-root-password-t956.html

Friday, November 4, 2011

How to Get the Locked Tables List In Oracle

Get the Locked Tables List

SELECT l.inst_id,

    SUBSTR (L.ORACLE_USERNAME, 1, 8) ORA_USER,

    SUBSTR (L.SESSION_ID, 1, 3) SID, S.serial#,

    SUBSTR (O.OWNER || '.' || O.OBJECT_NAME, 1, 40) OBJECT,

    P.SPID OS_PID,

    DECODE (L.LOCKED_MODE,

           0, 'NONE',

           1, 'NULL',

           2, 'ROW SHARE',

           3, 'ROW EXCLUSIVE',

           4, 'SHARE',

           5, 'SHARE ROW EXCLUSIVE',

           6, 'EXCLUSIVE',

           NULL)

      LOCK_MODE

FROM sys.GV_$LOCKED_OBJECT L,

    DBA_OBJECTS O,

    sys.GV_$SESSION S,

    sys.GV_$PROCESS P

WHERE L.OBJECT_ID = O.OBJECT_ID

    AND l.inst_id = s.inst_id

    AND L.SESSION_ID = S.SID

    AND s.inst_id = p.inst_id

    AND S.PADDR = P.ADDR(+)

ORDER BY   l.inst_id;

And to get the details of a particular session given by the sid in the above query use this query

select STATUS , PROCESS , PROGRAM , LOGON_TIME from v$session where sid=

This will give you queries currently running for more than 60 seconds. Note that it prints multiple lines per running query if the SQL has multiple lines. Look at the sid,serial# to see what belongs together.

SELECT s.username,

    s.sid,

    s.serial#,

    s.last_call_et / 60 mins_running,

    q.sql_text

FROM v$session s

JOIN v$sqltext_with_newlines q ON s.sql_address = q.address

WHERE status = 'ACTIVE' AND TYPE <> 'BACKGROUND' AND last_call_et > 60
ORDER BY sid, serial#, q.piece

Tuesday, November 1, 2011

Setting ORACLE DB into noarchivelog mode

Steps when the database state is down:
1. Login to sqlplus

  - sqlplus /nolog

  - connect / as sysdba

2. The db must be mounted EXCLUSIVE and not open for operation
- startup mount;
3. Check the log mode status of the database
- select log_mode from v$database;
4. Setting it to noarchivelog mode
- alter database noarchivelog;
5. Setting db open for user operation
- alter database open;
6. Reverting back to archivelog mode
- alter database archivelog;

Friday, October 21, 2011

Monday, July 18, 2011

Chuyển đổi file Excel xls sang file csv vẫn giữ font tiếng Việt unicode

Mấy hôm nay gặp vấn đề về font tiếng Việt với file csv. Vấn đề chi tiết như sau.
Mình có file dữ liệu là excel *.xls . Vì nhu cầu công việc mình cần phải chuyển nó sang file có định dạng *.csv để import vào một ứng dụng của công ty. Mặc dù trong Excel có chức năng Save As, chọn type là *.csv; nhưng Excel lại không hỗ trợ Unicode. Do đó file *.csv sau khi tạo bằng chức năng Save As của Excel bị mất font tiếng Việt.
Lên net tìm một một buổi chẳng có chỉ thấy ai làm được. Chỉ có cách của Anhgolden's Blog! . Nhưng cách này thì rườm rà, không hay.
Thế là mình quyết định viết một macro ngay trên Excel để chuyển font Unicode sang UTF8. Rồi sau đó dùng chức năng Save As của Excel để lưu ra file *.csv. Vì đặc tính của UTF8 là dùng nhiều ký tự 1byte để biểu diễn cho một ký tự Unicode nhiều bytes. Nên file *.csv tạo ra vẫn có tiếng Việt. Và có thể import vào ứng dụng của công ty. Hiện nay, hầu hết các ứng dụng đều hỗ trợ UTF8(nhưng Excel không hiễn thị được tiếng Việt UTF8 nhé).
Giờ thì chỉ mỗi việc mở file excel có macro (Link download) run macro để chuyển font Unicode sang UTF8, và Save As là có file *.csv với font Tiếng Việt. Bài viết giúp được bạn comment cảm ơn 1 tiếng nhé.

Dưới đây là demo:

Tuesday, May 10, 2011

MySQL my.cnf configuration for a large Drupal site

For a large web site with hundreds of thousands of page views per day, it is important to increase certain parameters, such as the query cache, join buffers, key buffer, ..etc.
The following my.cnf file is from an Ubuntu server (Debian derived) with dual CPUs, and 2 GB of RAM. It assumes that you are using MyISAM tables only and not InnoDB.
Depending on what modules you have, you may need to adjust some of those parameters, but this is definitely a good start.

[client]

port            = 3306

socket          = /var/run/mysqld/mysqld.sock

[mysqld_safe]

socket          = /var/run/mysqld/mysqld.sock

nice            = 0

[mysqld]

user            = mysql

pid-file        = /var/run/mysqld/mysqld.pid

socket          = /var/run/mysqld/mysqld.sock

port            = 3306

basedir         = /usr

datadir         = /var/lib/mysql

tmpdir          = /tmp

language        = /usr/share/mysql/english

skip-external-locking

old_passwords   = 1

bind-address            = 127.0.0.1

key_buffer              = 16M

key_buffer_size         = 32M

max_allowed_packet      = 16M

thread_stack            = 128K

thread_cache_size       = 64

query_cache_limit       = 8M

query_cache_size        = 64M

query_cache_type        = 1

join_buffer_size        = 512K

max_connections         = 150

log_slow_queries        = /var/log/mysql/mysql-slow.log

skip-bdb

skip-innodb

[mysqldump]

quick

quote-names

max_allowed_packet      = 16M

[mysql]

#no-auto-rehash # faster start of mysql but no tab completition

[isamchk]

key_buffer              = 16M

Wednesday, April 27, 2011

Duplication Fails with Multiple RMAN-06023 Errors: Scenario

In this scenario, you back up the database, then run the DUPLICATE command. You receive the following error stack:

RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of Duplicate Db command at 09/04/2001 13:55:11
RMAN-03015: error occurred in stored script Memory Script
RMAN-06026: some targets not found - aborting restore
RMAN-06023: no backup or copy of datafile 8 found to restore
RMAN-06023: no backup or copy of datafile 7 found to restore
RMAN-06023: no backup or copy of datafile 6 found to restore
RMAN-06023: no backup or copy of datafile 5 found to restore
RMAN-06023: no backup or copy of datafile 4 found to restore
RMAN-06023: no backup or copy of datafile 3 found to restore
RMAN-06023: no backup or copy of datafile 2 found to restore
RMAN-06023: no backup or copy of datafile 1 found to restore

Duplication Fails with Multiple RMAN-06023 Errors: Diagnosis

The DUPLICATE command recovers to archived redo logs, but cannot recover into online redo logs. Thus, if the restored backup cannot be made consistent without applying the online redo logs, then duplication fails with RMAN-06023 errors because RMAN is looking for backups created before the most recent archived log.

Duplication Fails with Multiple RMAN-06023 Errors: Solution

After backing up the source database, archive and back up the current redo log:

RMAN> SQL 'ALTER SYSTEM ARCHIVE LOG CURRENT';
RMAN> BACKUP ARCHIVELOG ALL;

ref: Oracle® Database Backup and Recovery Advanced User's Guide

Friday, April 22, 2011

Oracle 10g Data Dictionary

Wednesday, April 20, 2011

Shell script schedule backup MySQL Database

There are many software that can help you schedule backup MySQL Database using user interface. But with MySQL Database are installed on Linux host, you write Shell script to schedule backup MySQL Database without any additional software. Below I will show the script:

#!/bin/bash

MyUSER="root"
MyPASS="rootpassword"
MyHOST="localhost"

# Linux bin paths, change this if it can't be autodetected via which command
MYSQL="$(which mysql)"
MYSQLDUMP="$(which mysqldump)"
CHOWN="$(which chown)"
CHMOD="$(which chmod)"
GZIP="$(which gzip)"

# Backup Dest directory, change this if you have someother location
DEST="/backup/database"

# Main directory where backup will be stored
MBD="$DEST/mysql"

# Get hostname
HOST="$(hostname)"

# Get data in dd-mm-yyyy format
NOW="$(date +"%d-%m-%Y")"

# DO NOT BACKUP these databases
IGGY="test"

[ ! -d $DEST ] && mkdir -p $DEST || :
[ ! -d $MBD ] && mkdir -p $MBD || :

# Only root can access it!
$CHOWN 0.0 -R $DEST
$CHMOD 0600 $DEST

# Get all database list first
DBS="$($MYSQL -u $MyUSER -h $MyHOST -p$MyPASS -Bse 'show databases')"
echo "==>List of Database:" $DBS

for db in $DBS
do
skipdb=-1
if [ "$IGGY" != "" ]; then
    for i in $IGGY
do
[ "$db" == "$i" ] && skipdb=1 || :
done
fi

if [ "$skipdb" == "-1" ] ; then
    FILE="$MBD/$db-$HOST-$NOW.gz"
    echo "==>dump database" $db "to file" $FILE
    # do all inone job in pipe,
    # connect to mysql using mysqldump for select mysql database
    # and pipe it out to gz file in backup dir
    $MYSQLDUMP -u $MyUSER -h $MyHOST -p$MyPASS $db | $GZIP -9 > $FILE
fi
done

Tuesday, March 29, 2011

How Change ORACLE_SID & DB_NAME

Overview

This month, I will describes how to find and change the "db_name" for a database, or the ORACLE_SID for an instance, without recreating the database.

Query the data dictionary to find the current DB_NAME and ORACLE_SID

Query the views v$database and v$thread.

V$DATABASE gives DB_NAME

V$THREAD gives ORACLE_SID

ORACLE_SID = DB_SID and db_name = DBNAME: To find the current value of ORACLE_SID:
SVRMGR> select instance from v$thread;

     INSTANCE
     ----------------
     DB_SID
To find the current value of DB_NAME:
SVRMGR> select name from v$database;

     NAME
     ---------
     DBNAME

How to modify the database to run under a new ORACLE_SID

1.  Shutdown the instance  
 
2.  Backup all control, redo, and data files.  
 
3.  Go through the .profile, .cshrc, .login, oratab, tnsnames.ora,  
    (for SQL*Net version 2), and redefine the ORACLE_SID environment 
    variable to a new value.  
 
    For example, search through disks and do a grep ORACLE_SID *  
 
4.  Change locations to the "dbs" directory 
 
       % cd $ORACLE_HOME/dbs   
 
    and rename the following files:  
 
    o   init.ora  (or use pfile to point to the init file.)  
    o   control file(s). This is optional if you do not rename any 
        of the controlfiles, and the control_files parameter is used.  
        The "control_files" parameter is set in the "init.ora" file 
        or in a file it references with the ifile parameter.  Make  
        sure that the control_files parameter does not point to old 
        file names, if you have renamed them.  
    o   "crdb.sql" & "crdb2.sql",  This is optional.  These are  
        only used at database creation.  
 
5.  Change locations to the "rdbms/admin" directory 
 
       % cd $ORACLE_HOME/rdbms/admin    
    
    and rename the file:  
 
    o   startup.sql.  This is optional.  On some platforms,  
        this file may be in the "$ORACLE_HOME/rdbms/install" directory. 
        Make sure that the contents of this file do not reference old  
        init.ora files that have been renamed.  This file  
        simplifies the "startup exclusive" process to start your database.  
 
6.  To rename the database files and redo log files, you would follow the  
    instructions in [NOTE:9560.1]. 
 
7.  Change the ORACLE_SID environment variable to the new value. 
 
8.  Check in the "$ORACLE_HOME/dbs" directory to see if the password  
    file has been enabled.  If enabled, the file "orapw" will 
    exist and a new password file for the new SID must be created  
    (renaming the old file will not work).  If "orapw" does not 
    exist, skip to step 9.  To create a new password file, issue 
    the following command as oracle owner: 
  
       orapwd file=orapw password=?? entries= 
 
9.  Start up the database and verify that it works.  Once you have done  
    this, shutdown the database and take a final backup of all control,  
    redo, and data files.  
 
10. When the instance is started, the control file is updated with the  
    current ORACLE_SID.

How to the "db_name" for a Database

1.  Login to Server Manager 
 
        % svrmgrl 
        SVRMGR> connect internal  

2.  Type

        SVRMGR> alter system switch logfile;

    to force a checkpoint.
  
3.  Type 
 
        SVRMGR> alter database backup controlfile to trace resetlogs;  
      
    This will create a trace file containing the "CREATE CONTROLFILE" 
    command to recreate the controlfile in its current form.  
 
4.  Shutdown the database and exit SVRMGR 
 
        SVRMGR> shutdown

        SVRMGR> exit 

    The database must be shutdown with SHUTDOWN NORMAL or SHUTDOWN IMMEDIATE.
    It must not be shutdown abnormally using SHUTDOWN ABORT.
 
5.  Change locations to the directory where your trace files are located.  
    They are usually in the "$ORACLE_HOME/rdbms/log" directory.  If  
    "user_dump_dest" is set in the "init.ora" file, then go to the  
    directory listed in the "user_dump_dest" variable.  The trace file will 
    have the form "ora_NNNN.trc with NNNN being a number.   

6.  Copy the contents of the trace file starting from the line with
    STARTUP NOMOUNT down to the end of the trace file and put it in
    a new file called something like "ccf.sql".

7.  Edit the "ccf.sql" file   
 
    FROM: CREATE CONTROLFILE REUSE DATABASE "olddbname" RESETLOGS ...  
      TO: CREATE CONTROLFILE set DATABASE "newdbname"  RESETLOGS ...  

    Change the word 'REUSE' to 'set' and the 'olddbname' to 'newdbname'.

    It is possible to recreate the controlfile using the syntax:
          CREATE CONTROLFILE REUSE set DATABASE "newdbname" RESETLOGS ...

    But this syntax will allow the existing controlfiles to be overwritten
    without giving an error.

    FROM:
    # Recovery is required if any of the datafiles are restored backups,
    # or if the last shutdown was not normal or immediate.
    RECOVER DATABASE USING BACKUP CONTROLFILE
    TO:
    # Recovery is required if any of the datafiles are restored backups,
    # or if the last shutdown was not normal or immediate.
    # RECOVER DATABASE USING BACKUP CONTROLFILE

    The last command in ccf.sql should be:
    alter database open resetlogs

8.  Save and exit the "ccf.sql" file 
  
9.  Rename the old control files for backup purposes and so that they do
    not exist when creating the new ones.  
 
10. Edit the "init.ora" file so that db_name="newdb_name" .  
 
11. Login to Server Manager 
 
        % svrmgrl
        SVRMGR> connect internal
 
12. Run the "ccf.sql" script 
 
        SVRMGR> @ccf  

    This will issue a startup nomount, and then recreate the controlfile.

    If, at this point, you receive the error that a file needs media recovery, 
    the database was not shutdown normally as specified in step 4. You can
    try recovering the database using the redo in the current logfile, by
    issuing:

    SVRMGRL>  recover database using backup controlfile until cancel;

    This will prompt for an archived redologfile. It may be possible to 
    open the database after applying the current logfile. BUT this is not 
    guaranteed. 

    To apply the necessary redo, you need to check the online logfiles 
    and apply the one with the same sequence number as reported in the
    message. This usually is the logfile with status=CURRENT. If not apply,
    the logfiles in turn until the logfile with status=CURRENT has been 
    applied.  If, after applying the current logfile, the database will 
    not open then it is highly likely that the operation must be restarted 
    having shutdown the database normally.
 
    To find a list of the online logfiles:

    SVRMGR> select group#, seq#, status from v$log;
    GROUP#     SEQUENCE#   STATUS
    ---------- ---------   ----------------
             1 123         CURRENT     <== this redo needs to be applied
             2 124         INACTIVE
             3 125         INACTIVE
             4 126         INACTIVE
             5 127         INACTIVE
             6 128         INACTIVE
             7 129         INACTIVE

    7 rows selected.
 
    SVRMGR> select member
              from v$logfile
             where GROUP# = 1;

    Member
    ------------------------------------
    /u02/oradata/V815/redoV81501.log
   
    After applying the current online log file the following prompt should
    be displayed:

    Log Applied
    Media Recovery Complete

    At this point the database can be opened with:
 
    SVRMGR> alter database open resetlogs;  
 
13. You may also need to change the global database name:

    alter database rename global_name to .

14. Make sure the database is working.   

15. Shutdown and backup the database.

by Jeff Hunter, Sr. Database Administrator
Source: http://www.idevelopment.info/data/Oracle/DBA_tips/Database_Administration/DBA_7.shtml

Thursday, March 24, 2011

NID-00111: Oracle error reported from target database

I was restoring an Oracle 10g database using RMAN from our production environment to a development environment. I had completed most of the necessary steps, and the database was restored and recovered up to the point in time that I wanted.

The next step was to run the NID command in order to change the name of the database. I made sure that I was using the Oracle 10g NID utility and not the 9i version and ran it. I got the following error:

NID-00111: Oracle error reported from target database while executing
begin dbms_backup_restore.nidprocessdf(:fno, :istemp, :skipped, :idchged, :nmchged); end;
ORA-01116: error in opening database file R:\ORACLE\DATA\TEMP01.DBF
ORA-27041: unable to open file
OSD-04002: unable to open file
O/S-Error: (OS 3) The system cannot find the path specified.
ORA-06512: at "SYS.X$DBMS_BACKUP_RESTORE", line 6185
ORA-06512: at line 1

In order to get the NID utility to run successfully, I had to log into the database and manually drop the TEMP files, then add TEMP files again using the following commands:

SQL> ALTER DATABASE TEMPFILE 'R:\ORACLE\DATA\TEMP01.DBF' DROP;
SQL> !rm 'R:\ORACLE\DATE\TEMP01.DBF'
SQL> ALTER TABLESPACE TEMP1 ADD TEMPFILE 'R:\ORACLE\DATA\TEMP01.DBF' SIZE 100M;

You can also read my article on how to drop TEMP files for more details on dropping TEMP files.

It seemed very odd that I had to do this because I’ve never had to do it with a 9i database, and I didn’t think that the TEMP files were needed. RMAN certainly doesn’t back them up. So, if you have a NID command scripted into anywhere and you come across this, make sure that you put the extra step in of dropping the TEMP files first.

Source: http://www.ora00600.com/scripts/databaseconfig/NID-00111.html

DBNEWID Utility - Change DBID and DBNAME

Prior to the introduction of the DBNEWID utility alteration of the internal DBID of an instance was impossible and alteration of the DBNAME required the creation of a new controlfile. The DBNEWID utility allows the DBID to be altered for the first time and makes changing the DBNAME simpler. Changing the DBID is necessary when you want to use an RMAN catalog to backup a cloned instance. RMAN identifies instances using the DBID, preventing the original and cloned instance being managed by the same catalog. Alteration of the BID in the cloned instance removes this restriction.

DBID And DBNAME

Backup the database.
Create password file if not exist.
Mount the database after a clean shutdown:
```
SHUTDOWN IMMEDIATE
STARTUP MOUNT
```

Invoke the DBNEWID utility (nid) specifying the new DBNAME from the command line using a user with SYSDBA privilege:

nid TARGET=sys/password@TSH1 DBNAME=TSH2

Assuming the validation is successful the utility prompts for confirmation before performing the actions. Typical output may look something like:

C:\oracle\920\bin>nid TARGET=sys/password@TSH1 DBNAME=TSH2
DBNEWID: Release 9.2.0.3.0 - Production
Copyright (c) 1995, 2002, Oracle Corporation.  All rights reserved.

Connected to database TSH1 (DBID=1024166118)

Control Files in database:
    C:\ORACLE\ORADATA\TSH1\CONTROL01.CTL
    C:\ORACLE\ORADATA\TSH1\CONTROL02.CTL
    C:\ORACLE\ORADATA\TSH1\CONTROL03.CTL

Change database ID and database name TSH1 to TSH2? (Y/[N]) => Y

Proceeding with operation
Changing database ID from 1024166118 to 1317278975
Changing database name from TSH1 to TSH2
    Control File C:\ORACLE\ORADATA\TSH1\CONTROL01.CTL - modified
    Control File C:\ORACLE\ORADATA\TSH1\CONTROL02.CTL - modified
    Control File C:\ORACLE\ORADATA\TSH1\CONTROL03.CTL - modified
    Datafile C:\ORACLE\ORADATA\TSH1\SYSTEM01.DBF - dbid changed, wrote new name
    Datafile C:\ORACLE\ORADATA\TSH1\UNDOTBS01.DBF - dbid changed, wrote new name
    Datafile C:\ORACLE\ORADATA\TSH1\CWMLITE01.DBF - dbid changed, wrote new name
    Datafile C:\ORACLE\ORADATA\TSH1\DRSYS01.DBF - dbid changed, wrote new name
    Datafile C:\ORACLE\ORADATA\TSH1\EXAMPLE01.DBF - dbid changed, wrote new name
    Datafile C:\ORACLE\ORADATA\TSH1\INDX01.DBF - dbid changed, wrote new name
    Datafile C:\ORACLE\ORADATA\TSH1\ODM01.DBF - dbid changed, wrote new name
    Datafile C:\ORACLE\ORADATA\TSH1\TOOLS01.DBF - dbid changed, wrote new name
    Datafile C:\ORACLE\ORADATA\TSH1\USERS01.DBF - dbid changed, wrote new name
    Datafile C:\ORACLE\ORADATA\TSH1\XDB01.DBF - dbid changed, wrote new name
    Datafile C:\ORACLE\ORADATA\TSH1\TEMP01.DBF - dbid changed, wrote new name
    Control File C:\ORACLE\ORADATA\TSH1\CONTROL01.CTL - dbid changed, wrote new name
    Control File C:\ORACLE\ORADATA\TSH1\CONTROL02.CTL - dbid changed, wrote new name
    Control File C:\ORACLE\ORADATA\TSH1\CONTROL03.CTL - dbid changed, wrote new name

Database name changed to TSH2.
Modify parameter file and generate a new password file before restarting.
Database ID for database TSH2 changed to 1317278975.
All previous backups and archived redo logs for this database are unusable.
Shut down database and open with RESETLOGS option.
Succesfully changed database name and ID.
DBNEWID - Completed succesfully.

Shutdown the database:
```
SHUTDOWN IMMEDIATE
```
Modify the DB_NAME parameter in the initialization parameter file. The startup will result in an error but proceed anyway.

STARTUP MOUNT / NOMOUNT
ALTER SYSTEM SET DB_NAME=TSH2 SCOPE=SPFILE;
SHUTDOWN IMMEDIATE

Create a new password file:

orapwd file=c:\oracle\920\database\pwdTSH2.ora password=password entries=10

Rename the SPFILE to match the new DBNAME.
If you are using Windows you must recreate the service so the correct name and parameter file are used:
```
oradim -delete -sid TSH1
oradim -new -sid TSH2 -intpwd password -startmode a -pfile c:\oracle\920\database\spfileTSH2.ora
```
If you are using UNIX/Linux simply reset the ORACLE_SID environment variable:
```
ORACLE_SID=TSH2; export ORACLE_SID
```
Alter the listener.ora and tnsnames.ora setting to match the new database name and restart the listener:
```
lsnrctl reload
```

Open the database with RESETLOGS:

STARTUP MOUNT
ALTER DATABASE OPEN RESETLOGS;

Backup the database.

DBNAME Only

Repeat the process as before except use the following command to start the DBNEWID utility:

nid TARGET=sys/password@TSH2 DBNAME=TSH3 SETNAME=YES

The SETNAME parameter tells the DBNEWID utility to only alter the database name.

When opening the database the RESETLOGS option is not needed so the database can be started using the STARTUP command.

DBID Only

Backup the database.
Mount the database after a clean shutdown:
```
SHUTDOWN IMMEDIATE
STARTUP MOUNT
```
Invoke the DBNEWID utility (nid) from the command line using a user with SYSDBA privilege. Do not specify a new DBNAME:
```
nid TARGET=sys/password@TSH3
```

Shutdown and open the database with RESETLOGS:

SHUTDOWN IMMEDIATE
STARTUP MOUNT
ALTER DATABASE OPEN RESETLOGS;

Backup the database.

For further information see:

DBNEWID Utility

Hope this helps. Regards Tim...

Back to the Top.

Source: http://www.oracle-base.com/articles/9i/DBNEWID.php
reference: http://download.oracle.com/docs/cd/B10500_01/server.920/a96652/ch14.htm

Thursday, March 17, 2011

Create and configure Listener for Oracle 10g

When working with Oracle10g you often have problem in LITENER. Especially the beginners often have the error ORA-12505: TNS:listener does not currently know of SID. To day I will guide the way to create and configure the Oracle 10g Listener manually on Linux host.

Assuming you have completed the installation Oracle database on your server. And now you want to create new LITENER for connecting to your Database server.

The first you must check the /etc/hosts file to contain a fully qualified name for the server. The importan is that you have to add this before the default IP(127.0.0.1 localhost). And then your /etc/hosts file like this:

168.10.10 oracletest.mycompany.com oracletest localhost.localdomain localhost

0.0.1     oracletest.mycompany.com oracletest localhost.localdomain   localhost

The second you must set the environment variable TNS_ADMIN to a directory where your LITENER configuration file will be located. You should do this by adding the command into the /home/oracle/.bash_profile file. For example:

export TNS_ADMIN=$ORACLE_HOME/network/admin

The next you navigate to directory in $TNS_ADMIN and create these following files:
1) listener.ora

# listener.ora Network Configuration File: /u01/app/oracle/product/10.2.0/db_1/network/admin/listener.ora

# Generated by Oracle configuration tools.

LISTENER =

  (DESCRIPTION_LIST =

    (DESCRIPTION =

      (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.10.10)(PORT = 1521))

    )

  )

2) sqlnet.ora

# sqlnet.ora Network Configuration File: /u01/app/oracle/product/10.2.0/db_1/network/admin/sqlnet.ora

# Generated by Oracle configuration tools.

NAMES.DIRECTORY_PATH= (TNSNAMES, EZCONNECT)

3) tnsnames.ora

# tnsnames.ora Network Configuration File: /u01/app/oracle/product/10.2.0/db_1/network/admin/tnsnames.ora

# Generated by Oracle configuration tools.

TEST =

  (DESCRIPTION =

    (ADDRESS_LIST =

      (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.10.10)(PORT = 1521))

    )

    (CONNECT_DATA =

      (SID = TEST)

      (SERVER = DEDICATED)

    )

  )

The last starting your listener and connecting to your database server.

Thursday, March 3, 2011

Summary shell script for DBA

*Arithmetic Operators*
Operator	Description
-eq	True if two integers are equal
-ne	True if two integers are not equal
-lt	True if operand1 is less than operand2
-le	True if operand1 is less than or equal to operand2
-gt	True if operand1 is greater than operand2
-ge	True if operand1 is greater than or equal to operand2

*String Operators*
String Operator	Description
-z string	True if the string is empty
-n string	True if the string is not empty
string1 = string2	True if the strings are equal
string1 != string2	True if the strings are not equa
string1 < string2	True if string1 sorts before string2
string1 > string2	True if string1 sorts after string2

*Files Operators*
File Operator	Description
-a	True if file exists
-b	True if file is a block device file
-c	True if file is a character device file
-d	True if file is a directory
-e	True if file exists
-f	True if file exists and is a regular file
-g	True if file has set-group-id permission set
-h	True if file is a symbolic link
-L	True if file is a symbolic link
-k	True if file’s sticky bit is set
-p	True if file is a named pipe
-r	True if the file is readable (by current user)
-s	True if file exists and is not empty
-S	True if file is socket
-u	True if file is set-user-id
-w	True if file is writable (by current user)
-x	True if file is executable
-O	True if file is effectively owned by current user
-G	True if file is effectively owned by current user’s group
-N	True if file has been modified since it was last read
file1 -nt file2	True if operand1 is greater than or equal to operand2
file1 -ot file2	True if operand1 is greater than or equal to operand2
file1 -ef file2	True if file1 is a hard link to file2

*Common Character Matching Patterns*
Parttern	Description
a\|b	Matches either an a or a b
*	Matches any string of characters, often used for a catchall
[abc]	Matches any character a, b, or c
[a-c]	Matches any character a, b, or c
[0-9]	Matches any character 0 through 9
"<string>"	Matches the string enclosed in the quotes

*Special Shell Variables*
Name	Description
$1 - $n	Positional parameters that hold values for parameters passed to the script.
$?	The exit status of the last command. Contains a 0 for successfully executed commands. Contains a nonzero value for commands that failed. This nonzero value depends on what the command actually returned.
$0	Within a shell script, contains the name of the shell script being executed.
$#	The number of positional parameters passed to a script.
$$	The process number of the shell. Can be used to generate unique file names.
$!	The process number of the most recently executed background process.
$*	Contains all the positional parameters passed to the script.

Testing a Condition

 [ operand1 operator operand2 ]

Control Structures

if/then/else statement

 Syntax Examples
 if condition ; then

    commands

else

    commands

if

if condition ; then

    commands

elsif condition

    commands

elsif condition

    commands

if #!/bin/bash

thresh=1048576

totMem=$(grep MemTotal /proc/meminfo | awk '{print $2}')

if [ $totMem -lt $thresh ]; then

  echo "Total Memory $totMem is less than: $thresh"

  exit 1

else

  echo "Total Memory is: $totMem"

fi

case statement

 Syntax Examples
 case expression in

    pattern1)

        commands ;;

    pattern2)

        commands ;;

esac #!/bin/bash

archLoc=/dev/sdb2

usedSpc=$(df -h $archLoc | awk '{print $5}' | grep -v Use | cut -d "%" -f1 - )

case $usedSpc in

  [0-9])

    arcStat="relax, lots of disk space: $usedSpc"

  ;;

  [1-7][0-9])

    arcStat="disk space okay: $usedSpc"

  ;;

  [8][0-9])

    arcStat="gulp, space getting low: $usedSpc"

  ;;

  [9][0-9])

    arcStat="red alert, running out of space: $usedSpc"

  ;;

  [1][0][0])

    arcStat="update resume, no space left; $usedSpc"

  ;;

  *)

    arcStat="huh?: $usedSpc"

esac

BOX=$(uname -a | awk '{print $2}')

echo "archive space on: $BOX $arcStat"

exit 0 

for loop

 Syntax Examples
 for name [in list]

do

  commands can use $name

done #!/bin/bash

LOC_LIST="/backup /u01 /u02 /u03"

msg=""

for curLoc in $LOC_LIST

do

  usedSpc=$(df -h $curLoc | awk '{print $5}' | grep -v Use | cut -d "%" -f1 - )

  case $usedSpc in

    [0-8][0-9])

      msg="Space getting low: $curLoc on $(uname -a | awk '{print $2}') is used ${usedSpc}%"

    ;;

    [9][0-9])

      msg="Running out of space: $curLoc on $(uname -a | awk '{print $2}') is used ${usedSpc}%"

    ;;

    [1][0][0])

      msg="No space left: $curLoc on $(uname -a | awk '{print $2}') is used ${usedSpc}%"

    ;;

  esac

done

if [ -n "$msg" ] ; then

  echo $msg

fi

exit 0

Command substitution

There are two techniques for archiving command substitution in shell script:

variable=$(shell commands)

variable=`shell commands`

$BOX=$(uname -a | awk '{print$2}')

$BOX=`uname -a | awk '{print$2}'`

Either technique is a valid method. Depending on which shell you use, the $(command) command substitution might not be available.

Other example:
 Pgm=$(basename $0)

if [ $# -ne 1 ]

then

  echo "Wrong number of parameters passed to script."

  echo "Usage: $Pgm ORACLE_SID"

  exit 1

fi

Syntax	Examples
if condition ; then commands else commands if if condition ; then commands elsif condition commands elsif condition commands if	#!/bin/bash thresh=1048576 totMem=$(grep MemTotal /proc/meminfo \| awk '{print $2}') if [ $totMem -lt $thresh ]; then echo "Total Memory $totMem is less than: $thresh" exit 1 else echo "Total Memory is: $totMem" fi

Search This Blog

Wednesday, December 7, 2011

Friday, November 4, 2011

Tuesday, November 1, 2011

Friday, October 21, 2011

Monday, July 18, 2011

Tuesday, May 10, 2011

Wednesday, April 27, 2011

Duplication Fails with Multiple RMAN-06023 Errors: Scenario

Duplication Fails with Multiple RMAN-06023 Errors: Scenario

Duplication Fails with Multiple RMAN-06023 Errors: Diagnosis

Duplication Fails with Multiple RMAN-06023 Errors: Solution

Friday, April 22, 2011

Oracle 10g Data Dictionary

Wednesday, April 20, 2011

Shell script schedule backup MySQL Database

Tuesday, March 29, 2011

How Change ORACLE_SID & DB_NAME

Thursday, March 24, 2011

NID-00111: Oracle error reported from target database

DBNEWID Utility - Change DBID and DBNAME

DBID And DBNAME

DBNAME Only

DBID Only

Thursday, March 17, 2011

Create and configure Listener for Oracle 10g

Thursday, March 3, 2011

Summary shell script for DBA