Database Status - Revision history

Stuart: /* Show the ten largest objects in the database */

2018-12-09T14:41:28Z

‎Show the ten largest objects in the database

Stuart: /* Open the database */

2018-06-25T13:55:53Z

‎Open the database

Stuart: /* Copy init file to where it can be used */

2018-06-25T13:50:58Z

‎Copy init file to where it can be used

Stuart: /* Copy init file to where it can be used */

2018-06-25T13:49:43Z

‎Copy init file to where it can be used

Stuart: /* Move a database from one host to another without RMAN */

2018-06-25T13:48:16Z

‎Move a database from one host to another without RMAN

Stuart at 13:47, 25 June 2018

2018-06-25T13:47:11Z

Stuart: /* Check datafiles for their recovery status (compared with what is in the controlfile) */

2018-02-15T15:59:04Z

‎Check datafiles for their recovery status (compared with what is in the controlfile)

Stuart: /* Check datafiles for their recovery status (compared with what is in the controlfile) */

2018-02-15T15:56:58Z

‎Check datafiles for their recovery status (compared with what is in the controlfile)

Stuart: /* Check datafiles for their recovery status (compared with what is in the controlfile) */

2018-02-15T15:55:13Z

‎Check datafiles for their recovery status (compared with what is in the controlfile)

Stuart: /* How to stop and start a database on a Solaris Veritas cluster */

2018-02-15T15:54:16Z

‎How to stop and start a database on a Solaris Veritas cluster

@@ Line 439: / Line 439: @@
   ,      1;
 ===Show the ten largest objects in the database===
- colowner format a15
+<pre>
- colsegment_name format a30
+colowner format a15
- colsegment_type format a15
+colsegment_name format a30
- colmb format 999,999,999
+colsegment_type format a15
- select owner
+colmb format 999,999,999
- ,      segment_name
+select owner
- ,      segment_type
+,      segment_name
- ,      mb
+,      segment_type
- from   (
+,      mb
-        select owner
+from   (
-        ,      segment_name
+       select owner
-        ,      segment_type
+       ,      segment_name
-        ,      bytes / 1024 / 1024 "MB"
+       ,      segment_type
-        from   dba_segments
+       ,      bytes / 1024 / 1024 "MB"
-        order  by bytes desc
+       from   dba_segments
+       order  by bytes desc
- where  1=1
- and    rownum < 11;
+where  1=1
 ===Is java installed in the database?===
 This will return 9000'ish if it is

@@ Line 531: / Line 531: @@
 <pre>
 alter database open resetlogs
 </pre>

@@ Line 515: / Line 515: @@
 ====Copy init file to where it can be used====
 <pre>
-cp /tmp/init${ORACLE_SID} ${ORACLE_HOME}/dbs/
+cp /tmp/init${ORACLE_SID}.ora ${ORACLE_HOME}/dbs/
 </pre>

@@ Line 515: / Line 515: @@
 ====Copy init file to where it can be used====
 <pre>
-cp /tmp/init$ $ORACLE_HOME/dbs/
+cp /tmp/init${ORACLE_SID} ${ORACLE_HOME}/dbs/
 </pre>
 ====Create the new controlfile====
 <pre>

@@ Line 473: / Line 473: @@
   and    detected_usages > 0;
 ===Move a database from one host to another without RMAN===
-Once upon a time, it was the only way to do it. Now it's not common as the database has to be shut down.<br />
+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 />
 '''On source server'''
 ====Get a text version of the controlfile====

← Older revision		Revision as of 13:47, 25 June 2018
Line 472:		Line 472:
	where 1=1		where 1=1
	and detected_usages > 0;		and detected_usages > 0;
		+	===Move a database from one host to another without RMAN===
		+	Once upon a time, it was the only way to do it. Now it's not common as the database has to be shut down.<br />
		+	'''On source server'''
		+	====Get a text version of the controlfile====
		+	<pre>
		+	alter database backup controlfile to trace as '/tmp/&database_name._cf.sql';
		+	</pre>
		+	====Grab a copy of the pfile====
		+	<pre>
		+	create pfile='/tmp/init_&database..ora' from spfile;
		+	</pre>
		+	====Send these 2 files to the destination server====
		+	<pre>
		+	scp /tmp/initora /tmp/_cf.sql oracle@${dest_host}:/tmp/
		+	</pre>
		+	====Shutdown the database====
		+	<pre>
		+	shu immediate
		+	</pre>
		+	====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.<br />
		+	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.
		+	<pre>
		+	scp /cln/exp/ora_data3/cocpit/*dbf oracle@hn512:/cln/exp/ora_data2/cocpit/
		+	</pre>
		+	'''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====
		+	<pre>
		+	. .oraenv
		+	</pre>
		+	====Copy init file to where it can be used====
		+	<pre>
		+	cp /tmp/init$ $ORACLE_HOME/dbs/
		+	</pre>
		+	====Create the new controlfile====
		+	<pre>
		+	cd /tmp
		+	sqlplus / as sysdba
		+	@&database_name._cf
		+	</pre>
		+	====Mount the database====
		+	<pre>
		+	alter database mount
		+	</pre>
		+	====Open the database====
		+	<pre>
		+	alter database open resetlogs
		+	</pre>

@@ Line 2: / Line 2: @@
 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.
 <pre>
-set lines 1000 pages 100
+set lines 1000 pages 100 numwidth 15
 col name for a60
 col stats for a6

@@ Line 2: / Line 2: @@
 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.
 <pre>
 select
 a.name
@@ Line 22: / Line 24: @@
 </pre>
 ===How to stop and start a database on a Solaris Veritas cluster===
 Using command line...

← Older revision		Revision as of 15:54, 15 February 2018
Line 1:		Line 1:
		+	===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.
		+	<pre>
		+	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#
		+	/
		+
		+	</pre>
	===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...