======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'''. 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 After the machine restarts '''log in as root to run the following''' /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" 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. chkconfig oracle on init 6 Now test at https://server_name.example.com:1158/em/ =====Connect to Oracle===== su - oracle sqlplus /nolog connect / as sysdba =====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'''
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'''
The second digit represents a maintenance release level. Some new features may also be included. '''Application Server Release Number'''
The third digit reflects the release level of the Oracle Application Server (OracleAS). '''Component-Specific Release Number'''
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'''
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 sqlplusCOL PRODUCT FORMAT A35 COL VERSION FORMAT A15 COL STATUS FORMAT A15 SELECT * FROM PRODUCT_COMPONENT_VERSION; =SQLPlus to a Remote Database= sqlplus /nolog connect sys@db.example.com as sysdba =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===== su - oracle sqlplus /nolog connect / as sysdba select instance from v$thread; =Find DB_NAME= su - oracle sqlplus /nolog connect / as sysdba select instance_name from v$instance; =====Show Oracle Control File===== su - oracle sqlplus "/ as sysdba" SHOW PARAMETER CONTROL_FILES; =Free Up Space= SSH into the server as the user 'oracle' using PuTTY and run the following command to free space. ~/freediskspace.sh 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 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/* 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 /oracle/base/home/log =====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 owm On Linux, the default wallet directory is /oracle/base/home/owm/wallets/oracle More information [https://oracle-base.com/articles/misc/utl_http-and-ssl here] =====Change SID===== Old notes 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.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.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.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 rename the file: - 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 . 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=orapwdummydb password=dbpassword 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. 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.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.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 . See for further detail. 14. Make sure the database is working. 15. Shutdown and backup the database. =====Get Account Status===== select ACCOUNT_STATUS from dba_users where username = 'dstead'; =====Restart Database from SQLPLUS===== Restart database: SQL>shutdown immediate SQL>startup =====Start Idle Instance===== If you get "Connected to an idle instance." type the following startup nomount =====List Users===== select * from all_users; OR select username from dba_users; =====Create User===== create user USERNAME identified by PASSWORD quota unlimited on users; If you want to grant them permission to login and create tables and procedures, run the following. grant create session, create table, create procedure to USERNAME; =====Change User Password===== ALTER USER myuser IDENTIFIED BY new_password; =====Lock/Unlock Accounts===== ALTER USER username ACCOUNT LOCK; ALTER USER username ACCOUNT UNLOCK; To view all user accounts and their status SELECT username, account_status FROM dba_users; To view all locked accounts SELECT username,account_status FROM dba_users WHERE lock_date is not null; To view just a specific account SELECT username, account_status FROM dba_users WHERE username='EXTRANETUSER'; =====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. emca -reconfig ports -DBCONTROL_HTTP_PORT 1158 -RMI_PORT 5540 -JMS_PORT 5520 =====Get Database Info===== 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 =====Remove Password Expiry and Login Attempts===== 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;" =====Show All Users===== select * from all_users; OR select username from dba_users; =====Create Users===== Create user identified by quota unlimited on users; grant create session, create table, create procedure to ; =====Lock Accounts===== Here's how to lock or unlock Oracle database user accounts. SQL> ALTER USER username ACCOUNT LOCK; SQL> ALTER USER username ACCOUNT UNLOCK; =====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 su - vi /etc/hosts On DB'''P'''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 On DB'''Z'''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 Now change to oracle and created a needed data directory su - oracle mkdir -p /oracle/data/database/prod Now load RMAN 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) rman > connect auxiliary 'database'; > duplicate target database to 'database' backup location '/oracle/backup' nofilenamecheck It will take a long time to recover (an hour or so). To test the connection from another machine to DB'''P''' sqlplus /nolog connect sys@production.example.com as sysdba To test the connection from another machine to DB'''Z''' sqlplus /nolog connect sys@db.example.com as sysdba =====List Size Of All Tablespaces===== Log in as sysdba. The resulting information is given in bytes select s.tablespace_name, sum(s.bytes) from dba_segments s group by s.tablespace_name; =====Start Oracle===== On Linux: # Switch to the the user 'oracle'su - oracle # Start the Oracle databasedbstart # Start the Oracle listenerslsnrctl start # Start the Oracle Enterprise Manageremctl start dbconsole # Become root and create a lock filetouch /var/lock/subsys/oracle Example: [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 =====Stop Oracle===== On Linux: # Switch to the the user 'oracle'su - oracle # Stop the Oracle Enterprise Manageremctl stop dbconsole # Become root (for this command only) and remove the lock filerm -f /var/lock/subsys/oracle # Stop the Oracle listenerslsnrctl stop # Stop the Oracle databasedbshut Example: [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 =====Generate Random Password===== select extranet.usertools.getpassword from dual;