Saucepan

This page is read only. You can view the source, but not change it. Ask your administrator if you think this is wrong.
======Useful Oracle Commands======
This page contains a bunch of useful tips for using Oracle.
=====Change Hostname and IP=====
For this example, we assume the database is up and running and that it has full network connectivity. In other words, before starting this process, you should be able to access the EM console on the database server from your workstation (https://templatedb.example.com:1158/em/).
'''We assume there is only one network adapter and that it is eth0. If this is not true, change ifcfg-eth0 below as appropriate'''.

'''Run the following as root'''.
<code>NEW_HOSTNAME=change-me-change-me-change-me-change-me
NEW_IP="172\\.16\\.1\\.change-me-change-me"

CURRENT_HOSTNAME=`hostname -s`
CURRENT_IP="172\\.16\\.1\\.32"
sed -i "s/$CURRENT_HOSTNAME/$NEW_HOSTNAME/g" /etc/sysconfig/network
sed -i "s/$CURRENT_IP/$NEW_IP/g" /etc/sysconfig/network-scripts/ifcfg-eth0


/bin/su - oracle -c "emctl stop dbconsole"
sleep 5;
/bin/su - oracle -c "emca -deconfig dbcontrol db -silent -SID sidney"
sleep 5;
ORACLE_HOME_DIR=$(/bin/su - oracle -c "echo \$ORACLE_HOME")
rm -rf ${ORACLE_HOME_DIR}/*example.com*
rm -f /var/lock/subsys/oracle
/bin/su - oracle -c "lsnrctl stop"
sleep 5;
/bin/su - oracle -c "dbshut"


sed -i "s/$CURRENT_HOSTNAME/$NEW_HOSTNAME/g" ${ORACLE_HOME_DIR}/network/admin/tnsnames.ora
sed -i "s/$CURRENT_HOSTNAME/$NEW_HOSTNAME/g" ${ORACLE_HOME_DIR}/network/admin/listener.ora
sed -i "s/$CURRENT_HOSTNAME/$NEW_HOSTNAME/g" ${ORACLE_HOME_DIR}/sysman/config/emd.properties
chkconfig oracle off
init 6</code>
After the machine restarts '''log in as root to run the following'''
<code>/bin/su - oracle -c "dbstart"
sleep 5;
/bin/su - oracle -c "lsnrctl start"

# We need some time to elapse between the previous command and this command. Not sure why but it falls over if you run it straight away
sleep 30;
/bin/su - oracle -c "emca -config dbcontrol db -repos recreate"
# try
# DBPASSWD=change-me-change-me-change-me 
# /bin/su - oracle -c "emca -config dbcontrol db -repos recreate -silent -SID sidney -PORT 1521 -ORACLE_HOME /oracle/base/home -EMAIL_ADDRESS dba@example.com -MAIL_SERVER_NAME mail.example.com -DBSNMP_PWD $DBPASSWD -SYSMAN_PWD $DBPASSWD -SYS_PDW $DBPASSWD"</code>
Recreate the EM environment SID=sidney, listener port=1521, listener $ORACLE_HOME=default, email address=dba@example.com, mailserver=mail.example.com, passwords are the normal database ones (not the same as normal system admin passwords). This step will take about nine minutes to complete. If frequently fails the first time. I believe this is because the system needs to take some time to itself after starting the listener. Just wait a minute and then run the command again. If it fails quickly, you probably mistyped one of the passwords.
<code>chkconfig oracle on
init 6</code>
Now test at https://server_name.example.com:1158/em/
=====Connect to Oracle=====
<code>su - oracle
sqlplus /nolog
connect / as sysdba</code>
=====Oracle Version Numbering=====
v.w.x.y.z
* v - Major database release number
* w - Database maintenance release number
* x - Application server release number
* y - Component specific release number
* z - Platform specific release number

'''Major Database Release Number'''<br />The first digit is the most general identifier. It represents a major new version of the software that contains significant new functionality.

'''Database Maintenance Release Number'''<br />The second digit represents a maintenance release level. Some new features may also be included.

'''Application Server Release Number'''<br />The third digit reflects the release level of the Oracle Application Server (OracleAS).

'''Component-Specific Release Number'''<br />The fourth digit identifies a release level specific to a component. Different components can have different numbers in this position depending upon, for example, component patch sets or interim releases.

'''Platform-Specific Release Number'''<br />The fifth digit identifies a platform-specific release. Usually this is a patch set. When different platforms require the equivalent patch set, this digit will be the same across the affected platforms.

To get the version number, run the following in sqlplus<code>COL PRODUCT FORMAT A35
COL VERSION FORMAT A15
COL STATUS FORMAT A15 
SELECT * FROM PRODUCT_COMPONENT_VERSION;</code>

=SQLPlus to a Remote Database=
<code>sqlplus /nolog
connect sys@db.example.com as sysdba</code>
=Clear Archive Logs=
Occasionally the Oracle database servers run out of space. To resolve this, the usual method is to remove the archive logs however this should '''never''' be done manually as this can cause the database to fail. To resolve properly do the following:

# Log on to the database server as administrator
# Open a command prompt
# Run the following commands (commands repeated is deliberate):
:# rman
:# connect target/
:# delete archivelog all;
:# crosscheck archivelog all;
:# delete archivelog all;
:# crosscheck archivelog all;
:# exit
=====Find ORACLE_SID=====
<code>su - oracle
sqlplus /nolog
connect / as sysdba
select instance from v$thread;</code>
=Find DB_NAME=
<code>su - oracle
sqlplus /nolog
connect / as sysdba
select instance_name from v$instance;</code>

=====Show Oracle Control File=====
<code>su - oracle
sqlplus "/ as sysdba"
SHOW PARAMETER CONTROL_FILES;</code>
=Free Up Space=
SSH into the server as the user 'oracle' using PuTTY and run the following command to free space.
<code>~/freediskspace.sh</code>

This will run the following in a script.

If you find that /oracle/base is filling up, check the "/oracle/base/diag/tnslsnr" directory.

Remove all subdirectories that do not match the host name of the database server.

For the subdirectory that matches the hostname of the database server, cd into it and then the 'listener' subdirectory.

The subdirectories you are now interested in are 'alert' and 'trace'. E.G. on a machine called testdbz, you would look at
<code>HSTNM=`hostname -s`
rm -rf /oracle/base/diag/tnslsnr/$HSTNM/listener/alert/*
rm -rf /oracle/base/diag/tnslsnr/$HSTNM/listener/trace/*
rm -rf /oracle/base/diag/rdbms/database/sidney/trace/*
rm -rf /oracle/base/diag/rdbms/database/sidney/incident/*
rm -rf /oracle/base/diag/rdbms/database/sidney/alert/*</code>

To clear space, remove the trace/listener.log file

To clear space, remove the alert/log_**.xml files

Remove subdirectories not named after the local hostname in this directory
<code>/oracle/base/home/log</code>

=====Oracle Wallet Manager=====
To launch the Oracle Wallet Manager, log in to the server in the GUI as the user oracle, open a terminal and run <code>owm</code>
On Linux, the default wallet directory is <code>/oracle/base/home/owm/wallets/oracle</code>
More information [https://oracle-base.com/articles/misc/utl_http-and-ssl here]

=====Change SID=====
Old notes
<code>http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:318216852435
 

Modifying a database to run under a new ORACLE_SID:  
===================================================
  
1.  Shutdown the instance  
su - oracle
emctl stop dbconsole
exit
rm -f /var/lock/subsys/oracle
su - oracle
lsnrctl stop
dbshut


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.  

vi ~oracle/.bash_profile
ORACLE_UNQNAME=dummydb; export ORACLE_UNQNAME
ORACLE_SID=dummydb; 

vi $ORACLE_HOME/network/admin/tnsnames.ora
SERVICE_NAME = templatedb.example.com

vi /etc/oratab
dummydb:/oraclebase/app/oracle/product/11.2.0/dbhome_1:Y





 For example, search through disks and do a grep ORACLE_SID *  
 
4.  Change locations to the "dbs" directory 
cd $ORACLE_HOME/dbs   
rename the following files:  
 - init<sid>.ora  (or use pfile to point to the init file.)  
 - 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<SID>.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.  
 - crdb<sid>.sql" & "crdb2<sid>.sql",  This is optional.  These are  only used at database creation.  
 
5.  Change locations to the "rdbms/admin" directory 
cd $ORACLE_HOME/rdbms/admin    
 rename the file:  
 - startup<sid>.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<SID>.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<OLD_SID>" will exist and a new password file for the new SID must be created  (renaming the old file will not work).  If "orapw<OLD_SID>" does not exist, skip to step 9.  To create a new password file, issue the following command as oracle owner: 
       orapwd file=orapwdummydb password=dbpassword
       orapwd file=orapw<NEWSID> password=?? entries=<number of users to be granted permission to start the database instance> 
 
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.  
   

Changing the "db_name" for a Database:  
====================================== 
  
1.  Login to Server Manager 
su - oracle
sqlplus /nolog
connect / as sysdba
connect internal  

2.  Type the following to force a checkpoint.
alter system switch logfile;

3.  Type the following. This will create a trace file containing the "CREATE CONTROLFILE" command to recreate the controlfile in its current form.  
alter database backup controlfile to trace resetlogs;  

4.  Shutdown the database and exit SVRMGR. The database must be shutdown with SHUTDOWN NORMAL or SHUTDOWN IMMEDIATE. It must not 
be shutdown abnormally using SHUTDOWN ABORT.
shutdown
mirexit 

    
 
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<SID>.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.  Get the "CREATE CONTROLFILE" command from 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" NORESETLOGS ...  
      TO: CREATE CONTROLFILE set DATABASE "newdbname"  RESETLOGS ...  

    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

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<SID>.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;

    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. 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 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. 
 
    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

    The last command in ccf.sql should be:
 
    SVRMGR> alter database open resetlogs;  
 
13. You may also need to change the global database name:

    alter database rename global_name to <newdb_name>.<domain>

    See <Note:1018634.102> for further detail.

14. Make sure the database is working.   

15. Shutdown and backup the database. 

</code>
=====Get Account Status=====
<code>select ACCOUNT_STATUS from dba_users where username = 'dstead';</code>
=====Restart Database from SQLPLUS=====
<code>Restart database:
SQL>shutdown immediate
SQL>startup</code>
=====Start Idle Instance=====
If you get "Connected to an idle instance." type the following
<code>startup nomount</code>
=====List Users=====
<code>select * from all_users;</code>
OR
<code>select username from dba_users;</code>

=====Create User=====
<code>create user USERNAME identified by PASSWORD quota unlimited on users;</code>
If you want to grant them permission to login and create tables and procedures, run the following.
<code>grant create session, create table, create procedure to USERNAME;</code>

=====Change User Password=====
<code>ALTER USER myuser IDENTIFIED BY new_password;</code>

=====Lock/Unlock Accounts=====
<code>ALTER USER username ACCOUNT LOCK;</code>
<code>ALTER USER username ACCOUNT UNLOCK;</code>
To view all user accounts and their status
<code>SELECT username, account_status FROM dba_users;</code>
To view all locked accounts
<code>SELECT username,account_status FROM dba_users WHERE lock_date is not null;</code>
To view just a specific account
<code>SELECT username, account_status FROM dba_users WHERE username='EXTRANETUSER';</code>

=====Reconfigure EM Ports=====
If you want to reconfigure the ports that EM uses to run on, use the following command as the oracle user. This example sets the ports to their default value.
<code>emca -reconfig ports -DBCONTROL_HTTP_PORT 1158 -RMI_PORT 5540 -JMS_PORT 5520</code>
=====Get Database Info=====
<code>To List or view database list under Linux / UNIX oracle use /etc/oratab file using cat command:
cat /etc/oratab

To just get database list, enter:
cat /etc/oratab|grep -v "^#"|grep -v "N$"|cut -f1 -d: -s</code>
=====Remove Password Expiry and Login Attempts=====
<code>
Turn off password expiry on oracle and set number of login attemts to unlimited:
ALTER PROFILE DEFAULT LIMIT FAILED_LOGIN_ATTEMPTS UNLIMITED PASSWORD_LIFE_TIME UNLIMITED;

Then you can see if the DB is open.
SQL>"select status from v$instance;"
The result should be MOUNTED. If so, try
SQL>"alter database open;"</code>
=====Show All Users=====
<code>select * from all_users;</code>
OR
<code>select username from dba_users;</code>
=====Create Users=====
<code>Create user <username> identified by <password> quota unlimited on users;
grant create session, create table, create procedure to <username>;</code>
=====Lock Accounts=====
<code>Here's how to lock or unlock Oracle database user accounts.
SQL> ALTER USER username ACCOUNT LOCK;
SQL> ALTER USER username ACCOUNT UNLOCK;</code>

=====Recover Database=====
The assumed scenario here is that we have had a disaster and we need to recover the databases from backup. The entire filesystem should have been restored except for the contents of /oracle/data.

First fix the hosts file <code>su -
vi /etc/hosts</code>
On DB'''P'''<code>cat > /etc/hosts <<'END_OF_TEXT'
127.0.0.1   localhost dbp dbp.example.com localhost.localdomain localhost4 localhost4.localdomain4
::1         localhost dbp dbp.example.com localhost.localdomain localhost6 localhost6.localdomain6
172.16.1.110 dbp production
172.16.1.111 dbz productionz
END_OF_TEXT</code>
On DB'''Z'''<code>su -
vi /etc/hosts
cat > /etc/hosts <<'END_OF_TEXT'
127.0.0.1   localhost dbz dbz.example.com localhost.localdomain localhost4 localhost4.localdomain4
::1         localhost dbz dbz.example.com localhost.localdomain localhost6 localhost6.localdomain6
172.16.1.110 dbp production
172.16.1.111 dbz productionz
END_OF_TEXT</code>

Now change to oracle and created a needed data directory
<code>su - oracle
mkdir -p /oracle/data/database/prod</code>

Now load RMAN
<code>rman
> connect target                    (connected to the default database. Assuming we only have one, this means we connect to the database we want).
> shutdown immediate;               (it may already be shutdown but play it safe)
> startup nomount;                  (start the instance without mounting it - it can't be renamed while it is mounted)
> exit;                             (now we have got it in the state we want, we need to connect from a new session of rman so we exit this one)</code>                  
<code>rman
> connect auxiliary 'database';
 <enter the sys password>
> duplicate target database to 'database' backup location '/oracle/backup' nofilenamecheck</code>
It will take a long time to recover (an hour or so).

To test the connection from another machine to DB'''P'''
<code>sqlplus /nolog
connect sys@production.example.com as sysdba</code>
To test the connection from another machine to DB'''Z'''
<code>sqlplus /nolog
connect sys@db.example.com as sysdba</code>

=====List Size Of All Tablespaces=====
Log in as sysdba. The resulting information is given in bytes
<code>select s.tablespace_name, sum(s.bytes) from dba_segments s group by s.tablespace_name;</code>

=====Start Oracle=====
On Linux:
# Switch to the the user 'oracle'<code>su - oracle</code>
# Start the Oracle database<code>dbstart</code>
# Start the Oracle listeners<code>lsnrctl start</code>
# Start the Oracle Enterprise Manager<code>emctl start dbconsole</code>
# Become root and create a lock file<code>touch /var/lock/subsys/oracle</code>
Example:
<code>[oracle@templatedb ~]$ dbstart
ORACLE_HOME_LISTNER is not SET, unable to auto-start Oracle Net Listener
Usage: /oraclebase/app/oracle/product/11.2.0/dbhome_1/bin/dbstart ORACLE_HOME
Processing Database instance "templatedb": log file /oraclebase/app/oracle/product/11.2.0/dbhome_1/startup.log
[oracle@templatedb ~]$ lsnrctl start

LSNRCTL for Linux: Version 11.2.0.3.0 - Production on 17-JUN-2013 13:14:49

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

Starting /oraclebase/app/oracle/product/11.2.0/dbhome_1/bin/tnslsnr: please wait...

TNSLSNR for Linux: Version 11.2.0.3.0 - Production
System parameter file is /oraclebase/app/oracle/product/11.2.0/dbhome_1/network/admin/listener.ora
Log messages written to /oraclebase/app/oracle/diag/tnslsnr/templatedb/listener/alert/log.xml
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=templatedb.example.com)(PORT=1521)))

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC1521)))
STATUS of the LISTENER
- - - - - 
Alias                     LISTENER
Version                   TNSLSNR for Linux: Version 11.2.0.3.0 - Production
Start Date                17-JUN-2013 13:14:49
Uptime                    0 days 0 hr. 0 min. 0 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=templatedb.example.co.uk)(PORT=1521)))
The listener supports no services
The command completed successfully
[oracle@templatedb ~]$ emctl start dbconsole
Oracle Enterprise Manager 11g Database Control Release 11.2.0.3.0
Copyright (c) 1996, 2011 Oracle Corporation.  All rights reserved.
https://templatedb.example.co.uk:1158/em/console/aboutApplication
Starting Oracle Enterprise Manager 11g Database Control . started.
- - - - - - - - - - - - - - - - - - - - 
Logs are generated in directory /oraclebase/app/oracle/product/11.2.0/dbhome_1/templatedb.example.co.uk_templatedb/sysman/log
[oracle@templatedb ~]$ exit
[root@templatedb ~]# touch /var/lock/subsys/oracle</code>

=====Stop Oracle=====
On Linux:
# Switch to the the user 'oracle'<code>su - oracle</code>
# Stop the Oracle Enterprise Manager<code>emctl stop dbconsole</code>
# Become root (for this command only) and remove the lock file<code>rm -f /var/lock/subsys/oracle</code>
# Stop the Oracle listeners<code>lsnrctl stop</code>
# Stop the Oracle database<code>dbshut</code>

Example:
<code>[root@templatedb ~]# su - oracle
[oracle@templatedb ~]$ emctl stop dbconsole
Oracle Enterprise Manager 11g Database Control Release 11.2.0.3.0
Copyright (c) 1996, 2011 Oracle Corporation.  All rights reserved.
https://templatedb.example.com:1158/em/console/aboutApplication
Stopping Oracle Enterprise Manager 11g Database Control ...
 ...  Stopped.
[oracle@templatedb ~]$ exit
[root@templatedb ~]# rm -f  /var/lock/subsys/oracle
[root@templatedb ~]# su - oracle
[oracle@templatedb ~]$ lsnrctl stop

LSNRCTL for Linux: Version 11.2.0.3.0 - Production on 17-JUN-2013 12:54:37

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

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC1521)))
The command completed successfully
[oracle@templatedb ~]$ dbshut
ORACLE_HOME_LISTNER is not SET, unable to auto-stop Oracle Net Listener
Usage: /oraclebase/app/oracle/product/11.2.0/dbhome_1/bin/dbshut ORACLE_HOME
Processing Database instance "templatedb": log file /oraclebase/app/oracle/product/11.2.0/dbhome_1/shutdown.log</code>

=====Generate Random Password=====
<code>select extranet.usertools.getpassword from dual;</code>