Tablespace Creation Goodies

Back in the day. The method I used for a database clone would be to

1. Create a shell database


2. create the schema owner


3. grant connect, session etc sometimes all privledges


4. Create the table spaces


5. then import the database using the Fromuser=Sysadm toUser=sysadm function from an existing export.

Now assuming the tablespaces were already there, this would be cake. But if it was a new database this tends to be a pain if you dont already have a script. I found a new package that helps to create the DDL for tablespace creation. So I made a script:

set heading off;
set echo off;
Set pages 999;
set long 90000;

spool ddl_list.sql

select dbms_metadata.get_ddl('TABLESPACE','HRLARGE') from dual;
spool off;

here is what it returns:

CREATE TABLESPACE "HRLARGE" DATAFILE '/u03/oradata/fs8prd/hrlarge.dbf' SIZE 1048576 REUSE AUTOEXTEND ON NEXT 1048576 MAXSIZE UNLIMITED LOGGING ONLINE PERMANENT BLOCKSIZE 8192 EXTENT MANAGEMENT LOCAL AUTOALLOCATE SEGMENT SPACE MANAGEMENT AUTO X
1 row selected.

You could probably use sql to make sql to select all tablespaces then combine it with the package name. Anyone done this?

10g Control File move-Multiplex-I'm so retarded

Ok I'm still brushing up on my Oracle 10g stuff. So I thought I'd take a stab at moving a control file to a different disk. So If one control file gets messed up I can make copies and rename and I'm up and running(as if its ever like this).

So I login
c:\>sqlplus / as sysdba

SQL> show parameters control_files;
NAME TYPE VALUE
-------- ----------- ------------------------------
control_files string C:\ORACLE\PRODUCT\10.1.\ORADATA\ORCL_1\CONTROL01.CTL,
C:\ORACLE\PRODUCT\10.1.0\ORADATA\ORCL_1\CONTROL02.CTL,
C:\ORACLE\PRODUCT\10.1.0\ORADATA\ORCL_1\CONTROL03.CTL

Then I copy the paths from the output I got from the SQL window.
I issue the following statement:

SQL>Alter system set control_files='C:\ORACLE\PRODUCT\10.1.0\ORADATA\ORCL_1\control01.ctl,C:\ORACLE\PRODUCT\10.1.0\ORADATA\ORCL_1\control02.ctl,D:\ORACLE\PRODUCT\10.1.0\ORADATA\ORCL_1\control03.ctl' Scope=spfile;

system altered

SQL>shutdown immediate;
database closed

Then I move my control file to the D:\oracle\control03.ctl location then issue this statement:
SQL> startup

ORACLE instance started.
Total System Global Area 88080384 bytes
Fixed Size 777812 bytes
Variable Size 87040428 bytes
Database Buffers 0 bytes
Redo Buffers 262144 bytes
ORA-00205: error in identifying controlfile, check alert log for more info


DAMNIT!!!

now what do I do? I can't mount the database So I guess the the only option to do is:

SQL>startup nomount;


SQL> show parameters control_files;
NAME TYPE VALUE -------- ----------- ------------------------------ control_files string C:\ORACLE\PRODUCT\10.1.\ORADATA\ORCL_1\CONTROL01.CTL, C:\ORACLE\PRODUCT\10.1.0\ORADATA\ORCL_1\CONTROL02.CTL, D:\ORACLE\PRODUCT\10.1.0\ORADATA\ORCL_1\CONTROL03.CTL

Path looks good to me. What the heck could be wrong?

(3 hrs later)

Then I checked my syntax of my alter statement. The red is some of what I missed. I ran again an POOF! Up and Running!

SQL>Alter system set control_files='C:\ORACLE\PRODUCT\10.1.0\ORADATA\ORCL_1\control01.ctl','C:\ORACLE\PRODUCT\10.1.0\ORADATA\ORCL_1\control02.ctl','D:\ORACLE\PRODUCT\10.1.0\ORADATA\ORCL_1\control03.ctl' Scope=spfile;

System Altered


SQL>Shutdown immediate;
database closed


SQL>startup;
database opened.
SQL>

Oracle 10g Database clone via Data file move

Recently I had an oppertunity to do some database work for a company in Michigan. To prepare for this I found out the company was using Oracle's 10g database product. To be honest I had never done a database clone before on a 10g system. So for the Noobs out there I think I'll include step by step instructions of what I did.

There are some PROBLEMS WITH 10g CLONE see optional steps

Source Database: HRPRD
Target Database: HRTEST

Step 1.
Log in to sql*plus:
c:\oracle>set ORACLE_SID=HRPRD
c:\oracle>sqlplus / as sysdba

Step 2.
Issue Command to backup control file to trace.
SQL>alter database backup controlfile to trace;

Step 3.
Shutdown the database
SQL>shutdown immediate;


Step4.
Navigate to the location of your .trc files. Sort by date and copy the most current out somewhere to be edited. Then cut out the following section and save it as createnewdb.sql:

STARTUP
NOMOUNTCREATE
CONTROLFILE REUSE DATABASE "OLDLSQ" NORESETLOGS

NOARCHIVELOG
MAXLOGFILES 16
MAXLOGMEMBERS 2
MAXDATAFILES 240
MAXINSTANCES 1
MAXLOGHISTORY 113
LOGFILEGROUP 1 ('/u03/oradata/oldlsq/log1a.dbf','/u03/oradata/olslsq/log1b.dbf') SIZE 30M,GROUP 2 ('/u04/oradata/oldlsq/log2a.dbf','/u04/oradata/oldlsq/log2b.dbf') SIZE 30MDATAFILE'/u01/oradata/oldlsq/system01.dbf','/u01/oradata/oldlsq/mydatabase.dbf';
# Recovery is required if any of the datafiles are restored
# backups, or if the last shutdown was not normal or immediate.
RECOVER DATABASE# Database can now be opened normally.

ALTER DATABASE OPEN;

Step5.
Modify lines from the "createnewdb.sql" we just created:
Remove the lines following the characterset lines including:
# Recovery is required if any of the datafiles are restored
# backups, or if the last shutdown was not normal or immediate.
RECOVER DATABASE# Database can now be opened normally.

ALTER DATABASE OPEN;

Step6.
Do a global replace on the old Database name "HRPRD" with "HRDEV" (without quotes-lol) within the createnewdb.sql file. Also change the following like to read

before changes:
CREATE CONTROLFILE REUSE DATABASE "HRPRD" NORESETLOGS

after changes:
CREATE CONTROLFILE SET DATABASE "HRDEV" RESETLOGS

note: The instructions I've read everywhere say that you choose the "NORESETLOGS" option when using the "SET" operator. In 10g it explicitly tells me that in order to change the database name you have to use the "RESETLOGS" Option.

Disclaimer: I'm no know it all. I do what works :)

Step7.
On your Destination machine(HRDEV). Create a shell database (using your new DB name "HRDEV") using the Database configuration assistant.

Step8.
Copy all of the datafiles from the source machine to the target machine (yes the clone is on a separate box)using the "windows copy" option or "cp" for UNIX. Put the files in the same location as where you got them on the Production box. Or if you want them in a different location change the createnewdb.sql we just created.

Step9.
log in to the HRDEV machine as sys
c:\>set ORACLE_SID=HRDEV
c:\>sqlplus / as sysdba
Connected to an idle instance
SQL>@c:\createnewdb.sql
SQL>Alter database open;

YOU'RE GOOD TO GO!!

Step10.
I got an error the first time I did this because I copied over the control files. It can't recreate the control files if they are there already. So move them out then run the script again.


Problems with my new clone:
Enterprise Manager on clone corrupt or not in Sync
RMAN Backup problems.

Optional Steps.
(some people don't need a GUI interface but with all the new tools in OEM you'll want to fix this)

• Enterprise manager gives me an error saying invalid password for sys. It also says my database is down. It makes sense since it's pointing to the old shell database. I'm sure there are other solutions to fix this but this is what I did.
• If using an RMAN backup repository and backing up all database you will have problems due to the clone having the same DBID as the production database.

To fix both problems:

to issue a new DBID (database Id) run:

c:\>nid TARGET=sys/password@HRDEV

Follow the prompts, supplying the sys password, etc

To change the enterprise manager issue I had to drop and recreate the console.

To drop old Database console

• c:\> emca -deconfig dbcontrol db

To recreateDatabase console

• c:\> emca -config dbcontrol db

Enjoy!

--Jdog

-Let me know of your comments. I Don't know everything.

reference material:
DBA Tips on Cloning