* [[https://www.doag.org/formes/pubfiles/9199370/2017-NN-Frits_Hoogland-Who_shut_my_database_down__A_security_post_mortem_-Praesentation.pdf|Who shutdown my database - Frits Hoogland]] * [[https://fritshoogland.wordpress.com/wp-content/uploads/2010/01/oracle-security-done-right.pdf|Secure Oracle database access on the (unix and linux) operating system level.]] ==== Show database resource limits and current usage ==== set lines 1000 pages 100 select resource_name , current_utilization , max_utilization , limit_value from v$resource_limit / ==== Check datafiles for their recovery status (compared with what is in the controlfile) ==== Depending on how the database was last shut down (or recovered), this will show if data files are to of sync with the control file. set lines 1000 pages 100 numwidth 15 col name for a60 col stats for a6 col datafile_status for a18 select a.name ,a.status ,a.file# ,a.checkpoint_change# control_file_SCN ,b.checkpoint_change# datafile_SCN ,case when ((a.checkpoint_change# - b.checkpoint_change#) = 0) THEN 'Startup Normal' when ((b.checkpoint_change#) = 0) THEN 'File Missing?' when ((a.checkpoint_change# - b.checkpoint_change#) > 0) THEN 'Media Rec. Req.' when ((a.checkpoint_change# - b.checkpoint_change#) < 0) THEN 'Old Control File' else 'what the ?' end datafile_status from v$datafile a -- control file SCN for datafile ,v$datafile_header b -- datafile header SCN where a.file# = b.file# order by a.file# / ==== How to stop and start a database on a Solaris Veritas cluster ==== Using command line... Cluster logfile\t\t/var/VRTSvcs/log/engine_A.log Cluster status\t\thastatus -sum Clear failed resource\thares -clear $SID_ORA -sys $host  Start database\t\thares -online $SID_ORA -sys $host  Start listener\t\thares -online $SID_LSNR -sys $host or using a script... cat cluster_database.ksh start|stop - !/bin/ksh - set -x database=$2 PATH=$PATH:/opt/VRTSvcs/bin export PATH DATUM=`date +%Y%m%d` LOGFILE="/oracle/logs/cluster_scripts.${DATUM}.log" typeset -i count typeset -i seconds count=1 seconds=0 echo "`date +%Y%m%d-%H%M`: we will $1 database ${database}" | tee -a ${LOGFILE} - #################################################################### - Run this script as root to stop/start Oracle database incl. listeners # - #################################################################### - case $1 in start) #system=`hares -value ${database}_ORA LastOnline` #if [[ -z "${system}" ]] #then #system=`uname -n` #fi diskgroup="`echo $database | tr [[A-Z]] [[a-z]]`dg" system=`hares -state ${diskgroup} | grep ONLINE | nawk '{print $3}'` if [[ -z "${system}" ]] then echo "resource group ${database} has not been started on any system in the cluster. Please request administrator to start resourcegroup" | tee -a ${LOGFILE} fi status=`hares -state ${database}_ORA -sys ${system} | nawk '{print $NF}'` if [[ "${status}" = "FAULTED" ]] then echo "`date +%Y%m%d-%H%M`: Clearing faulted resource ${database}_ORA on system ${system}" | tee -a ${LOGFILE} hares -clear ${database}_ORA -sys ${system} 2>&1 | tee -a ${LOGFILE} fi echo "`date +%Y%m%d-%H%M`: Onlining resource ${database}_ORA on system ${system}" | tee -a ${LOGFILE} hares -online ${database}_ORA -sys ${system} 2>&1 | tee -a ${LOGFILE} sleep 5 temp=`hares -state ${database}_ORA | grep ONLINE | nawk '{print $3}'` while [[ -z "${temp}" ]] do seconds=count*5 echo "`date +%Y%m%d-%H%M`: Database resource ${database}_ORA not yet online after ${seconds} seconds" | tee -a ${LOGFILE} echo "`date +%Y%m%d-%H%M`: Status of resource ${database}_ORA" | tee -a ${LOGFILE} hares -state ${database}_ORA 2>&1 | tee -a ${LOGFILE} if [[ $seconds -gt 300 ]] then echo "`date +%Y%m%d-%H%M`: Database resource ${database}_ORA still not online after ${seconds} seconds. We are exiting the script. Please check the Oracle alert log" | tee -a ${LOGFILE} exit 1 fi sleep 5 temp=`hares -state ${database}_ORA | grep ONLINE | nawk '{print $3}'` count=count+1 done echo "`date +%Y%m%d-%H%M`: Database Resource ${database}_ORA is online on system ${system}" | tee -a ${LOGFILE} system=`hares -value ${database}_LSNR LastOnline` if [[ "${status}" = "FAULTED" ]] then echo "`date +%Y%m%d-%H%M`: Clearing faulted resource ${database}_LSNR on system ${system}" | tee -a ${LOGFILE} hares -clear ${database}_LSNR -sys ${system} fi echo "`date +%Y%m%d-%H%M`: Onlining resource ${database}_LSNR on system ${system}" | tee -a ${LOGFILE} hares -online ${database}_LSNR -sys ${system} 2>&1 | tee -a ${LOGFILE} count=1 sleep 5 temp=`hares -state ${database}_LSNR | grep ONLINE | nawk '{print $3}'` while [[ -z "${temp}" ]] do seconds=count*5 echo "`date +%Y%m%d-%H%M`: Listener resource ${database}_LSNR not yet online after ${seconds} seconds" | tee -a ${LOGFILE} echo "`date +%Y%m%d-%H%M`: Status of resource ${database}_LSNR" | tee -a ${LOGFILE} hares -state ${database}_LSNR 2>&1 | tee -a ${LOGFILE} if [[ $seconds -gt 300 ]] then echo "`date +%Y%m%d-%H%M`: Listener resource ${database}_LSNR still not online after ${seconds} seconds. We are exiting the script. Please check the Listener alert log" | tee -a ${LOGFILE} exit 1 fi sleep 5 temp=`hares -state ${database}_LSNR | grep ONLINE | nawk '{print $3}'` count=count+1 done echo "`date +%Y%m%d-%H%M`: Listener Resource ${database}_LSNR is online on system ${system}" | tee -a ${LOGFILE} resources=`hares -dep | grep "${database}_LSNR$" | nawk '{print $2}'` for res in ${resources} do echo "`date +%Y%m%d-%H%M`: Onlining resource ${res} on system ${system}" | tee -a ${LOGFILE} hares -online ${res} -sys ${system} sleep 5 echo "`date +%Y%m%d-%H%M`: Status of resource ${res}" | tee -a ${LOGFILE} hares -state ${res} 2>&1 | tee -a ${LOGFILE} done ;; stop) typeset -i count count=0 resources=`hares -dep | grep "${database}_LSNR$" | nawk '{print $2}'` for res in ${resources} do echo "`date +%Y%m%d-%H%M`: Offlining resource ${res} on system ${system} which depends on resource ${database}_LSNR" | tee -a ${LOGFILE} system=`hares -state ${res} | grep ONLINE | nawk '{print $3}'` if [[ -n "${system}" ]] then hares -offline ${res} -sys ${system} 2>&1 | tee -a ${LOGFILE} fi sleep 5 temp=`hares -state ${res} | tail +2 | grep -v OFFLINE | grep -v FAULTED | nawk '{print $3}'` count=1 while [[ -n "${temp}" ]] do seconds=count*5 echo "`date +%Y%m%d-%H%M`: resource ${res} not yet offline after ${seconds} seconds" | tee -a ${LOGFILE} echo "`date +%Y%m%d-%H%M`: Status of resource ${res}" | tee -a ${LOGFILE} hares -state ${res} 2>&1 | tee -a ${LOGFILE} if [[ $seconds -gt 300 ]] then echo "`date +%Y%m%d-%H%M`: resource ${res} still not offline after ${seconds} seconds. We are exiting the script. Please check the alert log" | tee -a ${LOGFILE} exit 1 fi sleep 5 count=count+1 temp=`hares -state ${res} | tail +2 | grep -v OFFLINE | grep -v FAULTED | nawk '{print$3}'` done FAULTED=`hares -state ${res} | grep FAULTED | nawk '{print $3}'` if [[ -n "${FAULTED}" ]] then hares -clear ${res} -sys ${system} 2>&1 | tee -a ${LOGFILE} fi echo "`date +%Y%m%d-%H%M`: Resource ${res} is offline on system ${system}" | tee -a ${LOGFILE} done system=`hares -state ${database}_LSNR | grep ONLINE | nawk '{print$3}'` if [[ -n "${system}" ]] then echo "`date +%Y%m%d-%H%M`: Stopping ${database}_LSNR on ${system}" | tee -a ${LOGFILE} hares -offline ${database}_LSNR -sys ${system} 2>&1 | tee -a ${LOGFILE} fi sleep 5 count=1 temp=`hares -state ${database}_LSNR | tail +2 | grep -v OFFLINE | grep -v FAULTED | nawk '{print$3}'` while [[ -n "${temp}" ]] do seconds=count*5 echo "`date +%Y%m%d-%H%M`: Listener resource ${database}_LSNR not yet offline after ${seconds} seconds" | tee -a ${LOGFILE} echo "`date +%Y%m%d-%H%M`: Status of resource ${database}_LSNR" | tee -a ${LOGFILE} hares -state ${database}_LSNR 2>&1 | tee -a ${LOGFILE} if [[ $seconds -gt 300 ]] then echo "`date +%Y%m%d-%H%M`: Listener resource ${database}_LSNR still not offline after ${seconds} seconds. We are exiting the script. Please check the listener alert log" | tee -a ${LOGFILE} exit 1 fi sleep 5 count=count+1 temp=`hares -state ${database}_LSNR | tail +2 | grep -v OFFLINE | grep -v FAULTED | nawk '{print$3}'` done echo "`date +%Y%m%d-%H%M`: Listener Resource ${database}_LSNR is offline on system ${system}" | tee -a ${LOGFILE} system=`hares -state ${database}_ORA | grep ONLINE | nawk '{print$3}'` if [[ -n "${system}" ]] then echo "`date +%Y%m%d-%H%M`: Stopping ${database}_ORA on ${system}" | tee -a ${LOGFILE} hares -offline ${database}_ORA -sys ${system} 2>&1 | tee -a ${LOGFILE} fi sleep 5 count=1 temp=`hares -state ${database}_ORA | tail +2 | grep -v OFFLINE | grep -v FAULTED | nawk '{print$3}'` while [[ -n "${temp}" ]] do seconds=count*5 echo "`date +%Y%m%d-%H%M`: Database resource ${database}_ORA not yet offline after ${seconds} seconds" | tee -a ${LOGFILE} echo "`date +%Y%m%d-%H%M`: Status of resource ${database}_ORA" | tee -a ${LOGFILE} hares -state ${database}_ORA 2>&1 | tee -a ${LOGFILE} if [[ $seconds -gt 300 ]] then echo "`date +%Y%m%d-%H%M`: Database resource ${database}_ORA still not offline after ${seconds} seconds. We are exiting the script. Please check the database alert log" | tee -a ${LOGFILE} exit 1 fi sleep 5 count=count+1 temp=`hares -state ${database}_ORA | tail +2 | grep -v OFFLINE | grep -v FAULTED | nawk '{print$3}'` done echo "`date +%Y%m%d-%H%M`: Database Resource ${database}_ORA is offline on system ${system}" | tee -a ${LOGFILE} ;; esac * [[Tablespaces]] ==== Following a database crash... ==== Check the status of the datafiles and tablespaces. They might need recovering. Restarted and checked alert log. datafile 6 was in RECOVER status SQL> recover datafile 6; Checked dba_datafiles. File is now in OFFLINE status SQL> alter database datafile 6 online; ... Errors in file e:\\oracle\\product\\admin\\arisppmp\\bdump\\arisppmp_lgwr_5104.trc: ORA-00494: enqueue [[CF]] held for too long (more than 900 seconds) by 'inst 1, osid 4956' Wed Apr 30 02:35:29 2014 System State dumped to trace file e:\\oracle\\product\\admin\\arisppmp\\bdump\\arisppmp_lgwr_5104.trc Wed Apr 30 02:35:30 2014 Errors in file e:\\oracle\\product\\admin\\arisppmp\\udump\\arisppmp_ora_13032.trc: ORA-00603: ORACLE server session terminated by fatal error ORA-00376: file 6 cannot be read at this time ORA-01110: data file 6: 'G:\\ORADATA\\ARISPPMP\\ARISPPMP01.DBF' ORA-00376: file 6 cannot be read at this time ORA-01110: data file 6: 'G:\\ORADATA\\ARISPPMP\\ARISPPMP01.DBF' Killing enqueue blocker (pid=4956) on resource CF-00000000-00000000 by killing session 167.1 Wed Apr 30 02:35:38 2014 Errors in file e:\\oracle\\product\\admin\\arisppmp\\bdump\\arisppmp_dbw0_4956.trc: Wed Apr 30 02:40:30 2014 Errors in file e:\\oracle\\product\\admin\\arisppmp\\bdump\\arisppmp_lgwr_5104.trc: ORA-00494: enqueue [[CF]] held for too long (more than 900 seconds) by 'inst 1, osid 4956' Wed Apr 30 02:40:30 2014 System State dumped to trace file e:\\oracle\\product\\admin\\arisppmp\\bdump\\arisppmp_lgwr_5104.trc Killing enqueue blocker (pid=4956) on resource CF-00000000-00000000 by terminating the process LGWR: terminating instance due to error 2103 Wed Apr 30 02:40:32 2014 Errors in file e:\\oracle\\product\\admin\\arisppmp\\bdump\\arisppmp_arc1_6920.trc: ORA-02103: PCC: inconsistent cursor cache (out-of-range cuc ref) Wed Apr 30 02:40:32 2014 Errors in file e:\\oracle\\product\\admin\\arisppmp\\bdump\\arisppmp_arc0_6912.trc: ORA-02103: PCC: inconsistent cursor cache (out-of-range cuc ref) Wed Apr 30 02:40:33 2014 Errors in file e:\\oracle\\product\\admin\\arisppmp\\bdump\\arisppmp_arc0_6912.trc: ORA-02103: PCC: inconsistent cursor cache (out-of-range cuc ref) Wed Apr 30 02:40:33 2014 Master background archival failure: 2103 Wed Apr 30 02:40:33 2014 Errors in file e:\\oracle\\product\\admin\\arisppmp\\bdump\\arisppmp_psp0_4848.trc: ORA-02103: PCC: inconsistent cursor cache (out-of-range cuc ref) Wed Apr 30 02:40:33 2014 Errors in file e:\\oracle\\product\\admin\\arisppmp\\bdump\\arisppmp_dbw2_5056.trc: ORA-02103: PCC: inconsistent cursor cache (out-of-range cuc ref) Wed Apr 30 02:40:35 2014 Errors in file e:\\oracle\\product\\admin\\arisppmp\\bdump\\arisppmp_smon_5208.trc: ORA-02103: PCC: inconsistent cursor cache (out-of-range cuc ref) Wed Apr 30 02:40:36 2014 Instance terminated by LGWR, pid = 5104 ... ==== How big is the database? ==== Calculate the total database size set lines 132 col dbname for a9 heading "Database" col dbsize for a15 heading "Size" col dbused for a15 heading "Used space" col dbfree for a15 heading "Free space" with used_space as ( select bytes from v$datafile union all select bytes from v$tempfile union all select bytes from v$log ) , free_space as ( select sum(bytes) sum_bytes from dba_free_space ) select vd.name dbname , round(sum(used_space.bytes)/1024/1024/1024 ) || ' GB' dbsize , round(sum(used_space.bytes)/1024/1024/1024 ) - round(free_space.sum_bytes/1024/1024/1024) || ' GB' dbused , round(free_space.sum_bytes/1024/1024/1024) || ' GB' dbfree from free_space , used_space , v$database vd group by vd.name , free_space.sum_bytes / ==== How big are the tablespaces? ==== Report the database size in terms of tablespace set pages 100 lines 200 select df.tablespace_name "Tablespace" , totalusedspace "Used MB" , (df.totalspace - tu.totalusedspace) "Free MB" , df.totalspace "Total MB" , round( 100 * ( (df.totalspace - tu.totalusedspace) / df.totalspace) ) "Pct. Free" from ( select tablespace_name , round(sum(bytes) / 1048576) totalspace from dba_data_files group by tablespace_name ) df , ( select round(sum(bytes)/(1024*1024)) totalusedspace , tablespace_name from dba_segments group by tablespace_name ) tu where df.tablespace_name = tu.tablespace_name and df.totalspace != 0 order by 5 / ==== How big are the schemas? ==== set lines 1000 pages 100 break on report compute sum of schema_size_gig on report select owner , sum(bytes)/1024/1024/1024 schema_size_gig from dba_segments group by owner order by 2 / ==== How to start a database after altering the spfile with a value that makes startup impossible ==== From [[https://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:2200190221847]] sys@ORA920.US.ORACLE.COM> alter system set processes=5000 scope=spfile; System altered. sys@ORA920.US.ORACLE.COM> shutdown Database closed. Database dismounted. ORACLE instance shut down. ... Connected to an idle instance. idle> startup ORA-00064: object is too large to allocate on this O/S (1,4720000) idle> Disconnected I'm hosed! cannot start. processes=5000 too big. So, using Kevins approach and the fact that the LAST parameter value will be used, I just: [ora920@tkyte-pc-isdn dbs]$ cat temp.ora spfile= /usr/oracle/ora920/OraHome1/dbs/spfileora920.ora processes = 150 Connected to an idle instance. idle> startup pfile=temp.ora ORACLE instance started. Total System Global Area 143725064 bytes Fixed Size 451080 bytes Variable Size 109051904 bytes Database Buffers 33554432 bytes Redo Buffers 667648 bytes Database mounted. Database opened. idle> show parameter processes NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ ... processes integer 150 idle> alter system set processes=150 scope=spfile; System altered. ==== Show the High Water Mark for a given table, or all tables if ALL is specified for Table_Name ==== From [[http://www.oracle-wiki.net/startsqlshowhwmtab]] SET LINESIZE 300 SET SERVEROUTPUT ON SET VERIFY OFF DECLARE CURSOR cu_tables IS SELECT a.owner, a.table_name FROM all_tables a WHERE a.table_name = Decode(Upper('&&Table_Name'),'ALL',a.table_name,Upper('&&Table_Name')) AND a.owner = Upper('&&Table_Owner') AND a.partitioned='NO' AND a.logging='YES' order by table_name; op1 NUMBER; op2 NUMBER; op3 NUMBER; op4 NUMBER; op5 NUMBER; op6 NUMBER; op7 NUMBER; BEGIN Dbms_Output.Disable; Dbms_Output.Enable(1000000); Dbms_Output.Put_Line('TABLE UNUSED BLOCKS TOTAL BLOCKS HIGH WATER MARK'); Dbms_Output.Put_Line('------------------------------ --------------- --------------- ---------------'); FOR cur_rec IN cu_tables LOOP Dbms_Space.Unused_Space(cur_rec.owner,cur_rec.table_name,'TABLE',op1,op2,op3,op4,op5,op6,op7); Dbms_Output.Put_Line(RPad(cur_rec.table_name,30,' ') || LPad(op3,15,' ') || LPad(op1,15,' ') || LPad(Trunc(op1-op3-1),15,' ')); END LOOP; END; / ==== Database Free space report ==== From [[http://asktom.oracle.com/pls/asktom/ASKTOM.download_file?p_file=6551402703363001567|asktom]] * Tablespace Name: name of tablespace, leading '*' indicates a good locally managed tablespace, leading blank means it is a bad dictionary managed tablespace. Second character of A implies ASSM managed storage, second character of M implies manually managed (pctused, freelists, etc are used to control space utilization) * Mbytes: allocated space of the tablespace, sum of mbytes consumed by all datafiles associated with tablespace. * Used: space in the tablespace that is used by some segment. * Free: space in the tablespace not allocated to any segment. * %Used: ratio of free to allocated space * largest: mostly useful with dictionary managed tablespaces, the size of the largest contiguous set of blocks available. If this number in a dictionary managed tablespace is smaller than the next extent for some object, that object could fail with "out of space" even if the FREE column says there is lots of free space. * MaxPoss Kbytes: the autoextend max size (note CAN be smaller than the allocated size!!!! you can set the maxsize to be less than the current size of a file) * %Max Used: how much of the maximum autoextend size has been used so far set linesize 132 ------------------------------------------------------- -- This SQL Plus script lists freespace by tablespace -------------------------------------------------------- column dummy noprint column pct_used format 999.9 heading "%|Used" column name format a19 heading "Tablespace Name" column Mbytes format 999,999,999 heading "MBytes" column used format 999,999,999 heading "Used" column free format 999,999,999 heading "Free" column largest format 999,999,999 heading "Largest" column max_size format 9,999,999,999,999 heading "MaxPoss|Mbytes" column pct_max_used format 999.9 heading "%|Max|Used" break on report compute sum of mbytes on report compute sum of free on report compute sum of used on report select (select decode(extent_management,'LOCAL','*',' ') || decode(segment_space_management,'AUTO','a ','m ') from dba_tablespaces where tablespace_name = b.tablespace_name) || nvl(b.tablespace_name, nvl(a.tablespace_name,'UNKOWN')) name, mbytes_alloc mbytes, mbytes_alloc-nvl(mbytes_free,0) used, nvl(mbytes_free,0) free, ((mbytes_alloc-nvl(mbytes_free,0))/ mbytes_alloc)*100 pct_used, nvl(largest,0) largest, nvl(mbytes_max,mbytes_alloc) Max_Size, decode( mbytes_max, 0, 0, (mbytes_alloc/mbytes_max)*100) pct_max_used from ( select sum(bytes)/1024/1024 mbytes_free, max(bytes)/1024/1024 largest, tablespace_name from sys.dba_free_space group by tablespace_name ) a, ( select sum(bytes)/1024/1024 mbytes_alloc, sum(maxbytes)/1024/1024 mbytes_max, tablespace_name from sys.dba_data_files group by tablespace_name union all select sum(bytes)/1024/1024 mbytes_alloc, sum(maxbytes)/1024/1024 mbytes_max, tablespace_name from sys.dba_temp_files group by tablespace_name )b where a.tablespace_name (+) = b.tablespace_name order by 8 / ==== Which schemas are taking up all of the space? ==== set pages 999 col "size MB" format 999,999,999 col "Objects" format 999,999,999 select obj.owner "Owner" , obj_cnt "Objects" , decode(seg_size, NULL, 0, seg_size) "size MB" from (select owner, count(*) obj_cnt from dba_objects group by owner) obj , (select owner, ceil(sum(bytes)/1024/1024) seg_size from dba_segments group by owner) seg where obj.owner = seg.owner(+) order by 3 desc , 2 desc , 1; ==== Show the ten largest objects in the database ==== colowner format a15 colsegment_name format a30 colsegment_type format a15 colmb format 999,999,999 select owner , segment_name , segment_type , mb from ( select owner , segment_name , segment_type , bytes / 1024 / 1024 "MB" from dba_segments order by bytes desc ) where 1=1 and rownum < 11; ==== Is java installed in the database? ==== This will return 9000'ish if it is select count(*) from all_objects where 1=1 and object_type like '%JAVA%' and owner = 'SYS'; ==== Show character set information ==== select * from nls_database_parameters; ==== Show all used features ==== select name , detected_usages from dba_feature_usage_statistics where 1=1 and detected_usages > 0; ==== Move a database from one host to another without RMAN ==== Once upon a time, this was the only way to move a database to a different server. Now it's not common as RMAN can do it so well.\\ **On source server** === Get a text version of the controlfile === alter database backup controlfile to trace as '/tmp/&database_name._cf.sql'; === Grab a copy of the pfile === create pfile='/tmp/init_&database..ora' from spfile; === Send these 2 files to the destination server === scp /tmp/init*ora /tmp/*_cf.sql oracle@${dest_host}:/tmp/ === Shutdown the database === shu immediate === Copy the database datafiles to the new host (see in the controlfile creation script where they are) === As we are going to reopen the database with resetlogs, no need to copy the redo logs.\\ As a new did will be created, no need to copy the archivelogs. If we need the old database back, we can just restart it on this server. scp /cln/exp/ora_data3/cocpit/*dbf oracle@hn512:/cln/exp/ora_data2/cocpit/ **On destination server** === Edit the files === * Change path names for new filesystem(s) * Create any directories that don't exist (archivelog, audit, ...) * Modify the controlfile recreation script * Remove Part #1 (use the resetlogs option) * Change REUSE to SET * Remove the recover database and open statements * Comment out the alter tablespace temp statements. Might need it after opening. === Add database entry to /etc/oratab === === Create or add entry to listener.ora === === Create or add entry to tnsnames.ora === === Setup environment for database === . .oraenv === Copy init file to where it can be used === cp /tmp/init${ORACLE_SID}.ora ${ORACLE_HOME}/dbs/ === Create the new controlfile === cd /tmp sqlplus / as sysdba @&database_name._cf === Mount the database === alter database mount === Open the database === alter database open resetlogs === Check the database === set lines 2000 pages 100 col file_name for a80 select file_name, status, online_status from dba_data_files; select file_name, status, online_status from dba_temp_files;