• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 3736
  • Last Modified:

Create Tablespace using existing Datafile

Hi People

I have a major problem and I could really use some help. I dont know much about Oracle so I am not sure if this is even possible on Oracle 8i Enterprise Edition.

We used this script to create 2 tablespaces.
"CREATE TABLESPACE PEO_DATA  DATAFILE                                          
     'C:\ORACLE\ORADATA\DEMO\PEO_DATA.dbf' SIZE 500M                                      
    DEFAULT STORAGE  (                                                        
          initial          60K                                                
          next             60K                                                
          pctincrease      1                                                  
          minextents       1                                                  
          );                                                                  

CREATE TABLESPACE PEO_INDEXES  DATAFILE                                        
     'C:\ORACLE\ORADATA\DEMO\PEO_INDEXES.dbf' SIZE 200M                                    
    DEFAULT STORAGE  (                                                        
          initial          40K                                                
          next             40K                                                
          pctincrease      1                                                  
          minextents       1                                                  
          );                                                                  

create user sysadm identified by sysadm
       default   tablespace peo_data
       temporary tablespace temp
       account   unlock;
"

OK, so heres the problem, we have been taking backups of ''C:\ORACLE\ORADATA" instead of using the exp application and now our Server had crashed. We have a new Server Installed and we would like to restore these 2 datafiles to the database, there is about 2000 tables in it.

Can someone please explain to me in detail how is this possible?
0
Deshin13
Asked:
Deshin13
  • 5
  • 4
1 Solution
 
schwertnerCommented:
If I am correct your database has crashed and you have created a new one.
Seems you have made cold backups of the database files.
If in that directory are also the controlfiles, the password file and the init<SID>.ora file
after the installation of the new system (trying to keep the same directory structure, ORACLE_SID,
etc.) try to restore the backup files. Try to startup Oracle.
Use the same version of Oracle.
0
 
Deshin13Author Commented:
Hi schwertner

OK, we have the init.ora and all the redo logs and control files and those 2 datafiles.

So what we have done so far is we installed and created a new instance with the same SID as the previous database. The directory structure and naming is the exact same as it was on the previous server. The problem that I have is that I dont actually know how to restore the data files.
I was being quite wishful in my first attempt, I created the exact same DB Name and then I just copied all the files from the backup into the directory, including the init.ora file. I then rebooted the machine and tried to start the database from Control Panel--> Services. It started up ok but when I try to log into SQLPlus, I get an Oracle error :ORA-01033: ORACLE initialization or shutdown in progress. Even if I wait about 1 hour, I still get the same error.

I dont know much about Oracle Administration, I am just a VB Developer so please explain in full detail of what I need to do.

Thanks
0
 
Deshin13Author Commented:
I am not sure if this will work but this is what I got so far....

Your target machine must be the same platform as the source machine. You won't be able to take a backup of a database running on Unix and move that to Windows, for instance. The next thing to do to make your life easier is to use the exact directory structure on both servers for your Oracle datafiles. Restore your backup to the target server (don't forget control files, your parameter file, and your password file). Then, use ORADIM to create the database service. This is the basics and everything should come up just fine.
0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
schwertnerCommented:
You have to start the database so:

c:>lsnrctl start
c:>sqlplus /nolog
SQL>startup

0
 
Deshin13Author Commented:
OK, when I done the copy and created a new service, I restarted the listener and database and I am getting a Shared Realm Error and it says that  Oracle is not available.

Any help on that?
0
 
Deshin13Author Commented:
The exact error is

ORA-01034: ORACLE not available
ORA-27101: shared memory realm does not exist

0
 
schwertnerCommented:
have you in the Registry ---> Oracle the needed values for
ORACLE_BASE, ORACLE_HOME, ORACLE_SID environment variables.
Have you in PATH entry for ORACLE_HOME/bin?
Can you start using Processes the Oracle service?
0
 
Deshin13Author Commented:
OK, I found the problem...

Not too sure if the way I fixed it was the correcy way.

I copied the init.ora file out of the PFILE Folder and then copied into Ora81\Database\ and then rename it to init<SID>.ora and the database mounted successfully.
0
 
schwertnerCommented:
Alternativelly use also
SQL>startup PFILE=c:\........\init.ora
from any location where your init.ora resides.
0

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

  • 5
  • 4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now