database_status
Differences
This shows you the differences between two versions of the page.
| Next revision | Previous revision | ||
| database_status [2018/12/06 21:05] – created 91.177.234.129 | database_status [2025/03/11 11:46] (current) – external edit 127.0.0.1 | ||
|---|---|---|---|
| Line 1: | Line 1: | ||
| - | ====== Database_Status ====== | + | * [[https:// |
| - | + | * [[https:// | |
| - | =====Check datafiles for their recovery status (compared with what is in the controlfile)===== | + | ==== Show database resource limits and current usage ==== |
| + | < | ||
| + | set lines 1000 pages 100 | ||
| + | select resource_name | ||
| + | , current_utilization | ||
| + | , max_utilization | ||
| + | , limit_value | ||
| + | from | ||
| + | / | ||
| + | </ | ||
| + | ==== 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. | 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# | ||
| + | , | ||
| + | , | ||
| + | ,case | ||
| + | when ((a.checkpoint_change# | ||
| + | when ((b.checkpoint_change# | ||
| + | when ((a.checkpoint_change# | ||
| + | when ((a.checkpoint_change# | ||
| + | else 'what the ?' | ||
| + | end datafile_status | ||
| + | from v$datafile a -- control file SCN for datafile | ||
| + | , | ||
| + | where a.file# = b.file# | ||
| + | order by a.file# | ||
| + | / | ||
| + | |||
| + | </ | ||
| - | =====How to stop and start a database on a Solaris Veritas cluster===== | + | ==== How to stop and start a database on a Solaris Veritas cluster ==== |
| Using command line... | Using command line... | ||
| - | < | + | < |
| + | Cluster logfile\t\t/ | ||
| + | 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... | or using a script... | ||
| - | < | + | < |
| - | *[[Tablespaces]] | + | cat cluster_database.ksh start|stop |
| + | - !/bin/ksh | ||
| + | - set -x | ||
| - | =====Following a database crash...===== | + | database=$2 |
| - | Check the status of the datafiles and tablespaces. They might need recovering.<br /> | + | PATH=$PATH:/ |
| + | export PATH | ||
| + | DATUM=`date +%Y%m%d` | ||
| + | LOGFILE="/ | ||
| + | typeset -i count | ||
| + | typeset -i seconds | ||
| + | count=1 | ||
| + | seconds=0 | ||
| + | |||
| + | echo "`date +%Y%m%d-%H%M`: | ||
| + | - #################################################################### | ||
| + | - Run this script as root to stop/start Oracle database incl. listeners # | ||
| + | - #################################################################### | ||
| + | - | ||
| + | case $1 in | ||
| + | start) | ||
| + | # | ||
| + | #if [[ -z " | ||
| + | #then | ||
| + | # | ||
| + | #fi | ||
| + | diskgroup=" | ||
| + | system=`hares -state ${diskgroup} | grep ONLINE | nawk ' | ||
| + | if [[ -z " | ||
| + | then | ||
| + | echo " | ||
| + | fi | ||
| + | status=`hares -state ${database}_ORA -sys ${system} | nawk ' | ||
| + | if [[ " | ||
| + | then | ||
| + | echo "`date +%Y%m%d-%H%M`: | ||
| + | hares -clear ${database}_ORA -sys ${system} 2>&1 | tee -a ${LOGFILE} | ||
| + | fi | ||
| + | echo "`date +%Y%m%d-%H%M`: | ||
| + | hares -online ${database}_ORA -sys ${system} 2>&1 | tee -a ${LOGFILE} | ||
| + | sleep 5 | ||
| + | temp=`hares -state ${database}_ORA | grep ONLINE | nawk ' | ||
| + | while [[ -z " | ||
| + | do | ||
| + | seconds=count*5 | ||
| + | echo "`date +%Y%m%d-%H%M`: | ||
| + | echo "`date +%Y%m%d-%H%M`: | ||
| + | hares -state ${database}_ORA 2>&1 | tee -a ${LOGFILE} | ||
| + | if [[ $seconds -gt 300 ]] | ||
| + | then | ||
| + | echo "`date +%Y%m%d-%H%M`: | ||
| + | exit 1 | ||
| + | fi | ||
| + | sleep 5 | ||
| + | temp=`hares -state ${database}_ORA | grep ONLINE | nawk ' | ||
| + | count=count+1 | ||
| + | done | ||
| + | echo "`date +%Y%m%d-%H%M`: | ||
| + | system=`hares -value ${database}_LSNR LastOnline` | ||
| + | if [[ " | ||
| + | then | ||
| + | echo "`date +%Y%m%d-%H%M`: | ||
| + | hares -clear ${database}_LSNR -sys ${system} | ||
| + | fi | ||
| + | echo "`date +%Y%m%d-%H%M`: | ||
| + | hares -online ${database}_LSNR -sys ${system} 2>&1 | tee -a ${LOGFILE} | ||
| + | count=1 | ||
| + | sleep 5 | ||
| + | temp=`hares -state ${database}_LSNR | grep ONLINE | nawk ' | ||
| + | while [[ -z " | ||
| + | do | ||
| + | seconds=count*5 | ||
| + | echo "`date +%Y%m%d-%H%M`: | ||
| + | echo "`date +%Y%m%d-%H%M`: | ||
| + | hares -state ${database}_LSNR 2>&1 | tee -a ${LOGFILE} | ||
| + | if [[ $seconds -gt 300 ]] | ||
| + | then | ||
| + | echo "`date +%Y%m%d-%H%M`: | ||
| + | exit 1 | ||
| + | fi | ||
| + | sleep 5 | ||
| + | temp=`hares -state ${database}_LSNR | grep ONLINE | nawk ' | ||
| + | count=count+1 | ||
| + | done | ||
| + | echo "`date +%Y%m%d-%H%M`: | ||
| + | resources=`hares -dep | grep " | ||
| + | for res in ${resources} | ||
| + | do | ||
| + | echo "`date +%Y%m%d-%H%M`: | ||
| + | hares -online ${res} -sys ${system} | ||
| + | sleep 5 | ||
| + | echo "`date +%Y%m%d-%H%M`: | ||
| + | hares -state ${res} 2>&1 | tee -a ${LOGFILE} | ||
| + | done | ||
| + | ;; | ||
| + | stop) | ||
| + | typeset -i count | ||
| + | count=0 | ||
| + | resources=`hares -dep | grep " | ||
| + | for res in ${resources} | ||
| + | do | ||
| + | echo "`date +%Y%m%d-%H%M`: | ||
| + | system=`hares -state ${res} | grep ONLINE | nawk ' | ||
| + | if [[ -n " | ||
| + | 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 ' | ||
| + | count=1 | ||
| + | while [[ -n " | ||
| + | do | ||
| + | seconds=count*5 | ||
| + | echo "`date +%Y%m%d-%H%M`: | ||
| + | echo "`date +%Y%m%d-%H%M`: | ||
| + | hares -state ${res} 2>&1 | tee -a ${LOGFILE} | ||
| + | if [[ $seconds -gt 300 ]] | ||
| + | then | ||
| + | echo "`date +%Y%m%d-%H%M`: | ||
| + | exit 1 | ||
| + | fi | ||
| + | sleep 5 | ||
| + | count=count+1 | ||
| + | temp=`hares -state ${res} | tail +2 | grep -v OFFLINE | grep -v FAULTED | nawk ' | ||
| + | done | ||
| + | FAULTED=`hares -state ${res} | grep FAULTED | nawk ' | ||
| + | if [[ -n " | ||
| + | then | ||
| + | hares -clear ${res} -sys ${system} 2>&1 | tee -a ${LOGFILE} | ||
| + | fi | ||
| + | echo "`date +%Y%m%d-%H%M`: | ||
| + | done | ||
| + | |||
| + | system=`hares -state ${database}_LSNR | grep ONLINE | nawk ' | ||
| + | if [[ -n " | ||
| + | then | ||
| + | echo "`date +%Y%m%d-%H%M`: | ||
| + | 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 ' | ||
| + | while [[ -n " | ||
| + | do | ||
| + | seconds=count*5 | ||
| + | echo "`date +%Y%m%d-%H%M`: | ||
| + | echo "`date +%Y%m%d-%H%M`: | ||
| + | hares -state ${database}_LSNR 2>&1 | tee -a ${LOGFILE} | ||
| + | if [[ $seconds -gt 300 ]] | ||
| + | then | ||
| + | echo "`date +%Y%m%d-%H%M`: | ||
| + | exit 1 | ||
| + | fi | ||
| + | sleep 5 | ||
| + | count=count+1 | ||
| + | temp=`hares -state ${database}_LSNR | tail +2 | grep -v OFFLINE | grep -v FAULTED | nawk ' | ||
| + | done | ||
| + | echo "`date +%Y%m%d-%H%M`: | ||
| + | system=`hares -state ${database}_ORA | grep ONLINE | nawk ' | ||
| + | if [[ -n " | ||
| + | then | ||
| + | echo "`date +%Y%m%d-%H%M`: | ||
| + | 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 ' | ||
| + | while [[ -n " | ||
| + | do | ||
| + | seconds=count*5 | ||
| + | echo "`date +%Y%m%d-%H%M`: | ||
| + | echo "`date +%Y%m%d-%H%M`: | ||
| + | hares -state ${database}_ORA 2>&1 | tee -a ${LOGFILE} | ||
| + | if [[ $seconds -gt 300 ]] | ||
| + | then | ||
| + | echo "`date +%Y%m%d-%H%M`: | ||
| + | exit 1 | ||
| + | fi | ||
| + | sleep 5 | ||
| + | count=count+1 | ||
| + | temp=`hares -state ${database}_ORA | tail +2 | grep -v OFFLINE | grep -v FAULTED | nawk ' | ||
| + | done | ||
| + | echo "`date +%Y%m%d-%H%M`: | ||
| + | |||
| + | ;; | ||
| + | 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 | 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: | ||
| + | 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: | ||
| + | Wed Apr 30 02:35:30 2014 | ||
| + | Errors in file e: | ||
| + | ORA-00603: ORACLE server session terminated by fatal error | ||
| + | ORA-00376: file 6 cannot be read at this time | ||
| + | ORA-01110: data file 6: ' | ||
| + | ORA-00376: file 6 cannot be read at this time | ||
| + | ORA-01110: data file 6: ' | ||
| + | |||
| + | 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: | ||
| + | |||
| + | Wed Apr 30 02:40:30 2014 | ||
| + | Errors in file e: | ||
| + | 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: | ||
| + | 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: | ||
| + | ORA-02103: PCC: inconsistent cursor cache (out-of-range cuc ref) | ||
| + | |||
| + | Wed Apr 30 02:40:32 2014 | ||
| + | Errors in file e: | ||
| + | ORA-02103: PCC: inconsistent cursor cache (out-of-range cuc ref) | ||
| + | |||
| + | Wed Apr 30 02:40:33 2014 | ||
| + | Errors in file e: | ||
| + | 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: | ||
| + | ORA-02103: PCC: inconsistent cursor cache (out-of-range cuc ref) | ||
| + | |||
| + | Wed Apr 30 02:40:33 2014 | ||
| + | Errors in file e: | ||
| + | ORA-02103: PCC: inconsistent cursor cache (out-of-range cuc ref) | ||
| + | |||
| + | Wed Apr 30 02:40:35 2014 | ||
| + | Errors in file e: | ||
| + | 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?===== | + | ==== How big is the database? ==== |
| Calculate the total database size | Calculate the total database size | ||
| - | < | + | < |
| - | =====How big are the tablespaces? | + | set lines 132 |
| + | col dbname for a9 heading " | ||
| + | col dbsize for a15 heading " | ||
| + | col dbused for a15 heading "Used space" | ||
| + | col dbfree for a15 heading "Free space" | ||
| + | with used_space as | ||
| + | ( | ||
| + | select | ||
| + | from v$datafile | ||
| + | union all | ||
| + | select | ||
| + | from v$tempfile | ||
| + | union all | ||
| + | select | ||
| + | from v$log | ||
| + | ) | ||
| + | , free_space as | ||
| + | ( | ||
| + | select sum(bytes) sum_bytes | ||
| + | from dba_free_space | ||
| + | ) | ||
| + | select vd.name | ||
| + | , round(sum(used_space.bytes)/ | ||
| + | , round(sum(used_space.bytes)/ | ||
| + | , round(free_space.sum_bytes/ | ||
| + | from | ||
| + | , 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 | Report the database size in terms of tablespace | ||
| - | < | + | < |
| + | set pages 100 lines 200 | ||
| + | select df.tablespace_name | ||
| + | , totalusedspace | ||
| + | , (df.totalspace - tu.totalusedspace) | ||
| + | , df.totalspace | ||
| + | , round( 100 * ( (df.totalspace - tu.totalusedspace) / df.totalspace) ) "Pct. Free" | ||
| + | from ( | ||
| + | | ||
| + | , | ||
| + | | ||
| + | | ||
| + | ) df | ||
| + | , ( | ||
| + | | ||
| + | , | ||
| + | | ||
| + | | ||
| + | ) tu | ||
| + | where df.tablespace_name | ||
| + | and df.totalspace | ||
| + | order by 5 | ||
| + | / | ||
| + | </ | ||
| - | =====Database Free space report===== | + | ==== 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)/ | ||
| + | from | ||
| + | group by owner | ||
| + | order by 2 | ||
| + | / | ||
| + | </ | ||
| + | |||
| + | ==== How to start a database after altering the spfile with a value that makes startup impossible ==== | ||
| + | From [[https:// | ||
| + | < | ||
| + | [email protected]> | ||
| + | System altered. | ||
| + | |||
| + | [email protected]> | ||
| + | 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, | ||
| + | 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= / | ||
| + | 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:// | ||
| + | < | ||
| + | SET LINESIZE 300 | ||
| + | SET SERVEROUTPUT ON | ||
| + | SET VERIFY OFF | ||
| + | |||
| + | DECLARE | ||
| + | CURSOR cu_tables IS | ||
| + | SELECT a.owner, | ||
| + | | ||
| + | FROM | ||
| + | WHERE a.table_name = Decode(Upper('&& | ||
| + | AND a.owner | ||
| + | AND a.partitioned=' | ||
| + | AND a.logging=' | ||
| + | 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(' | ||
| + | Dbms_Output.Put_Line(' | ||
| + | FOR cur_rec IN cu_tables LOOP | ||
| + | Dbms_Space.Unused_Space(cur_rec.owner, | ||
| + | Dbms_Output.Put_Line(RPad(cur_rec.table_name, | ||
| + | | ||
| + | | ||
| + | | ||
| + | END LOOP; | ||
| + | |||
| + | END; | ||
| + | / | ||
| + | </ | ||
| + | ==== Database Free space report ==== | ||
| From [[http:// | From [[http:// | ||
| - | *Tablespace Name: name of tablespace, leading ' | + | |
| - | *Mbytes: allocated space of the tablespace, sum of mbytes consumed by all datafiles associated with tablespace. | + | * 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. | + | * Used: space in the tablespace that is used by some segment. |
| - | *Free: space in the tablespace not allocated to any segment. | + | * Free: space in the tablespace not allocated to any segment. |
| - | *%Used: ratio of free to allocated space | + | * %Used: ratio of free to allocated space |
| - | *largest: mostly useful with dictionary managed tablespaces, | + | * largest: mostly useful with dictionary managed tablespaces, |
| - | *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) | + | * 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 | + | * %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 | ||
| + | column | ||
| + | column | ||
| + | column | ||
| + | column | ||
| + | column | ||
| + | column | ||
| + | column | ||
| + | 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,' | ||
| + | | ||
| + | from dba_tablespaces where tablespace_name = b.tablespace_name) || nvl(b.tablespace_name, | ||
| + | | ||
| + | | ||
| + | | ||
| + | | ||
| + | | ||
| + | mbytes_alloc)*100 pct_used, | ||
| + | | ||
| + | | ||
| + | | ||
| + | from ( select sum(bytes)/ | ||
| + | max(bytes)/ | ||
| + | tablespace_name | ||
| + | | ||
| + | group by tablespace_name ) a, | ||
| + | ( select sum(bytes)/ | ||
| + | sum(maxbytes)/ | ||
| + | tablespace_name | ||
| + | from sys.dba_data_files | ||
| + | group by tablespace_name | ||
| + | union all | ||
| + | select sum(bytes)/ | ||
| + | sum(maxbytes)/ | ||
| + | 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?===== | + | ==== Which schemas are taking up all of the space? ==== |
| + | < | ||
| set pages 999 | set pages 999 | ||
| col "size MB" format 999,999,999 | col "size MB" format 999,999,999 | ||
| Line 51: | Line 554: | ||
| , | , | ||
| , | , | ||
| - | =====Show the ten largest objects in the database===== | + | </ |
| - | | + | ==== Show the ten largest objects in the database ==== |
| - | | + | < |
| - | | + | colowner format a15 |
| - | colmb format 999, | + | colsegment_name format a30 |
| - | | + | colsegment_type format a15 |
| - | , | + | colmb format 999, |
| - | , | + | select owner |
| - | , | + | , segment_name |
| - | | + | , segment_type |
| - | select owner | + | , mb |
| - | , segment_name | + | from ( |
| - | , segment_type | + | |
| - | , bytes / 1024 / 1024 " | + | |
| - | from | + | |
| - | order by bytes desc | + | |
| - | ) | + | |
| - | | + | |
| - | | + | |
| - | =====Is java installed in the database?===== | + | where 1=1 |
| + | and rownum < 11; | ||
| + | </ | ||
| + | |||
| + | ==== Is java installed in the database? ==== | ||
| This will return 9000' | This will return 9000' | ||
| + | < | ||
| | | ||
| | | ||
| Line 77: | Line 585: | ||
| | | ||
| | | ||
| - | =====Show character set information===== | + | </ |
| + | ==== Show character set information ==== | ||
| + | < | ||
| | | ||
| - | =====Show all used features===== | + | </ |
| + | ==== Show all used features ==== | ||
| + | < | ||
| | | ||
| , | , | ||
| Line 85: | Line 597: | ||
| | | ||
| | | ||
| - | =====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.<br /> | + | ==== 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** | **On source server** | ||
| - | ====Get a text version of the controlfile==== | + | === Get a text version of the controlfile === |
| - | < | + | < |
| - | ====Grab a copy of the pfile==== | + | alter database backup controlfile to trace as '/ |
| - | < | + | </ |
| - | ====Send these 2 files to the destination server==== | + | === Grab a copy of the pfile === |
| - | < | + | < |
| - | ====Shutdown the database==== | + | create pfile='/ |
| - | < | + | </ |
| - | ====Copy the database datafiles to the new host (see in the controlfile creation script where they are)==== | + | === Send these 2 files to the destination server === |
| - | As we are going to reopen the database with resetlogs, no need to copy the redo logs.<br /> | + | < |
| + | scp / | ||
| + | </ | ||
| + | === 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. | 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 / | ||
| + | </ | ||
| **On destination server** | **On destination server** | ||
| - | ====Edit the files==== | + | === Edit the files === |
| - | * Change path names for new filesystem(s) | + | * Change path names for new filesystem(s) |
| - | * Create any directories that don't exist (archivelog, | + | * Create any directories that don't exist (archivelog, |
| - | * Modify the controlfile recreation script | + | * Modify the controlfile recreation script |
| - | * Remove Part #1 (use the resetlogs option) | + | * Remove Part #1 (use the resetlogs option) |
| - | * Change REUSE to SET | + | * Change REUSE to SET |
| - | * Remove the recover database and open statements | + | * Remove the recover database and open statements |
| - | * Comment out the alter tablespace temp statements. Might need it after opening. | + | * Comment out the alter tablespace temp statements. Might need it after opening. |
| - | ====Add database entry to /etc/oratab==== | + | === Add database entry to /etc/oratab === |
| - | ====Create or add entry to listener.ora==== | + | === Create or add entry to listener.ora === |
| - | ====Create or add entry to tnsnames.ora==== | + | === Create or add entry to tnsnames.ora === |
| - | ====Setup environment for database==== | + | === Setup environment for database === |
| - | < | + | < |
| - | ====Copy init file to where it can be used==== | + | . .oraenv |
| - | < | + | </ |
| + | === Copy init file to where it can be used === | ||
| + | < | ||
| + | cp / | ||
| + | </ | ||
| + | |||
| + | === Create the new controlfile | ||
| + | < | ||
| + | cd /tmp | ||
| + | sqlplus / as sysdba | ||
| + | @& | ||
| + | </ | ||
| + | === 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; | ||
| + | </ | ||
| - | ====Create the new controlfile==== | ||
| - | < | ||
| - | ====Mount the database==== | ||
| - | < | ||
| - | ====Open the database==== | ||
| - | < | ||
| - | ====Check the database==== | ||
| - | < | ||
database_status.1544130326.txt.gz · Last modified: 2018/12/06 21:05 by 91.177.234.129