Setting up a test enviornment

[DBAMJA]

Let me just start this out by saying I am a complete newbie to oracle. The DBA at my place of employement isn't willing to try things so I have to come up with solutions in the background.

With that said... We have an application that uses 9i as the backend. The company that we got this from didn't set up a test environment and wants to charge us a good sum of money to set one up. They have said that we can set it up ourselves if we want so that's what the upper management has decided but our "DBA" has no idea on what to do.

What would be the easiest way to create a duplicate environment of our production side to use as a test side?



[flush]

DBAMJA

It is said that God will give you no more than you can handle. I just wish God didn't have so much faith in me.

 

[Thargy]

dbamja,

Get a server that's completely blank, and punier than your production server, install the relevant operating system and matching version of Oracle.

On your production system, create a template of the database in question and its data, using the dbca.

Copy the template across to your test box, and using the dbca, use it to create a database.


Regards,

Tharg

Grinding away at things Oracular

 

[SantaMufasa]

DBAMJA,

I have a family of scripts and other helpful DBA utilities for setting up both test and production environments that you are welcome to have/use.

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]

 

[DBAMJA]

Santa:

Where can I get these scripts???



[flush]

DBAMJA

It is said that God will give you no more than you can handle. I just wish God didn't have so much faith in me.

 

[SantaMufasa]

The easiest method is via my signature.

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]

 

[DBAMJA]

Tharg

Tried your suggestion but I have a few less hairs because of me ripping them out.

After creating the template, I run the database creation using the template and it crashes at about 22%. The error I am getting is invalid entry size. One of the .dbf files that is copying is 4G in size and it appears that in 9i, that makes the dbca puke. I'm giving up for tonight!!! Hopefully a bit of sleep will put a new spin on things.



[flush]

DBAMJA

It is said that God will give you no more than you can handle. I just wish God didn't have so much faith in me.

 

[Thargy]

dbamja,

I was unaware of that limitation - you have furthered my knowledge with your test - thank you.

I therefore drop back to Santa's suggestion that you look into scripted solutions, and/or use exp/imp to move such large volumes of data.

You could still use the dbca, by copying structure only and then moving data etc., but I believe that Santa's proposed scripts will do a better and easier job.

I'd be interested to hear how this one turns out.

Regards

Tharg

Grinding away at things Oracular

 

[DBAMJA]

It appears that the dbf in question is just a temp (temp1.dbf). I've been trying to use this:

alter database datafile '/focus2k/database/ghs/temp1.dbf' resize 1500M;

I get an error that says it can't find the dbf. What should the path be??



[flush]

DBAMJA

It is said that God will give you no more than you can handle. I just wish God didn't have so much faith in me.

 

[SantaMufasa]

DBAMJA,

I handy script that will help you monitor your current and future database disk consumption is my "freespace.sql" script:

set echo on
REM **************************************************************
REM David L. Hunt (file author) distributes this and other
REM files/scripts for educational purposes only, to illustrate the
REM use or application of various computing techniques. Neither the
REM author nor Dasages, LLC, makes any warranty regarding this
REM script's fitness for any industrial application or purpose nor is
REM there any claim that this or any similarly-distributed scripts
REM are error free or should be used for any purpose other than
REM illustration.
REM **************************************************************
set echo off
set linesize 165
set pagesize 40
set heading on
col fname heading "Filename" format a60
col fnum heading "#" format 999
col ts heading "Tablespace|Name" format a15
col tb heading "Total|Potential|File Size" format 999,999,999,999
col cb heading "Total|Current|File Size" like tb
col used heading "Bytes Used" like tb
col free heading "Potential|Bytes Free" like tb
col autoext heading "Auto|Ext." format a4
col percentfree heading "% Free|of|Pot.|Total|Bytes" format 999
break on report
compute sum of tb cb used free on report
spool TablespaceUsage.txt
select     substr(tablespace_name,1,15) ts
   ,d.file_id fnum
   ,decode(e.file#,null,d.bytes,(e.maxextend * blksize)) tb
   ,d.bytes cb
   ,decode(d.bytes,0,0,d.bytes-nvl(freebytes,0)) used
   ,decode(e.file#,null,d.bytes,(e.maxextend * blksize))
    -decode(d.bytes,0,0,d.bytes-nvl(freebytes,0)) free
   ,trunc(((decode(e.file#,null,d.bytes,(e.maxextend * blksize))
     -decode(d.bytes,0,0,d.bytes-nvl(freebytes,0)))/
      decode(e.file#,null,d.bytes,(e.maxextend * blksize))
         ),2) * 100 percentfree
   ,decode(e.file#,null,'No','Yes') autoext
   ,''''||substr(file_name,1,55)||'''' fname
from       sys.dba_data_files d
   ,(select file_id,sum(bytes) freebytes
           from sys.dba_free_space
           group by file_id) f
   ,sys.filext$ e
   ,v$datafile v
   ,(select value blksize from v$parameter
           where name = 'db_block_size') b
where      d.file_id=f.file_id(+)
  and      d.file_id=e.file#(+)
  and      v.file#=d.file_id
order by tablespace_name,creation_time
/
spool off
prompt
prompt Note: Physical disk limitations may prevent an AUTOEXTEND-ing file from reaching its potential
prompt
prompt Wrote spool file "TablespaceUsage.txt".

Save this (as "freespace.sql") out to your default SQL*Plus script path, log into SQL*Plus, then run the script from the SQL*Plus prompt with:

@freespace

Once you run "freespace", it will confirm many things for you, include the current space availability for each file supporting each tablespace. Also, it will tell you whether or not each file is in AUTOEXTEND mode, which I suggest as a "just-in-time" disk-space-allocation resolution instead of trying to guess a re-size value that works for you.

Presuming that the datafile name you posted, above, is good (which it might not be since you received an error), here is the code to transform the file into an autoextending file that grows in 10MB increments to a maximum size of 2000MB:

ALTER DATABASE DATAFILE '/focus2k/database/ghs/temp1.dbf'
AUTOEXTEND ON NEXT 10M MAXSIZE 2000M;

Following this database-datefile modification, re-run my "freespace" script and see the effect of the "ALTER DATABASE..." command.

Let us know your findings.

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]

 

[DBAMJA]

I haven't had a chance to do the above yet but I thought I would let you know about some updates. I was finally able to get in touch with one of the vendors DBAs and he help me a bit. Our lead "DBA" was using "ALTER DATABASE DATAFILE ..." command when it should have been "ALTER DATABASE TEMPFILE..." since the table we are trying to resize is a temp file.

Hopefully when I get in the office this morning, I can get all of the users out of the database and give this a try.



[flush]

DBAMJA

It is said that God will give you no more than you can handle. I just wish God didn't have so much faith in me.

 

[DrSeussFreak]

SantaMufasa,

I am on your site, but unable to find any of those scripts. Any ideas?

 

[SantaMufasa]

Reach me via the site and we'll get you what you need.

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]

 

[DrSeussFreak]

Is there a charge for the scripts, or are they free?

 

[SantaMufasa]

There is no charge for my scripts (as Tek-Tipsters have found with the hundreds I have posted here). If a user wants/needs a bunch of my time/other services, it might involve charge(s), depending entirely upon the situation.

Get in touch, we'll talk/determine what you need, then you can decide, all with no obligation.

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]

 

[DrSeussFreak]

done, but as for services, we are not allowed here. I would for a government funded hospital, I am just the Jr. Oracle DB Analyst. I used to be a MS SQL DBA, so I am just getting familiar to the architecture of Oracle, and want some cool scripts.

 

[SantaMufasa]

All of that sounds reasonable, and yours are righteous desires. I've responded, via e-mail, to your enquiry.

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]