Introduction
For last a few years, I was working with MS SQL Server and
Recently I got a project which was using the oracle database, I faced a lot of difficulty to install and restore oracle database to a single test server
After searching for a simple solution I did not find it. So I thought to write a very simple step by step solution of beginners
Here are the steps for installation and logical restoration of latest backup,
Steps
I am using Windows 7 - 64Bit operating system.
You can download oracle 12c version from the following link
http://www.oracle.com/technetwork/database/enterprise-edition/downloads/database12c-win64-download-2297732.html
Main Steps
- Decrypt both download files to a single folder
- Double Click on Setup
- Loads Oracle Universal Installer
- Then, load First Window of Oracle Installation, Enter your email if needed, else leave it as blank and Remove Tick from check box of “I wish to receive security….” and click “Next” button.
- The ollowing warning message will display and click on “Yes” button.
- Select the second option “Install database software only” from Installation option of loaded window. And click “Next” button.
7. Select the first option “Single Instance database Installation” if you are using single standalone machine. Then click “Next” button.
- Select the product languages you needed, by default English will be selected. Click “Next” button
- By default Enterprise edition will be selected, just click “Next” button
- You can create a user for oracle or just select “Use Windows Built –in Account” option and click “Next” button.
- Click “Yes” and continue if you get the following message Box.
- Click “Next” If the default location has enough space for installation else change the path.
- Starts checking the prerequisites
- Loads the summary for installation details, Click “Install” button to start installation of the product.
- Installation starts and shows the progress of installation.
- Once installation finishes successfully click “Close” button.
That’s it you are installed Oracle 12c in your windows operating system.
Creating a database
Now we will create global database in the installed server.
For that, do the following steps
- Go to Start -> All Programs -> Oracle-OraDB12Home -> Configuration and Migration Tools ->
Database Configuration Assistant (right click on it and Run as Administrator)
- Select “Create Database” option and click “Next” button.
- Enter database name, password and select database character Set. And then click “Next” button.
- Loads Database Configuration Summary, click on “Finish” button
- Starts creating database file and loads the following window.
- Click “finish” button once database is created.
Yes..!! Now the database is created
Oracle Listener
Oracle Net Listener configuration, stored in the listener.ora
file, consists of the following elements:
- Name of the listener
- Protocol addresses that the listener is accepting connection requests on
- Database services
- Control parameters
Now let us create listener.. Do the following steps for it.
1. Go to Start -> All Programs -> Oracle-OraDB12Home -> Configuration and Migration Tools -> Net Configuration Assistant.
2. Select first option “Listener configuration” and click “Next” button.
3. Select first option “Add” then click “Next” button.
- By default Listener name will be “LISTENER” will be there, just click on “Next” button
- Select needed protocols and click “Next” button
- Use the standard port no 1521 or enter your custom port no. then click “Next” button.
- If it alerts by asking of configuration another listener select “No” and click “Next” button
- Loads Listener configuration complete message , click “Next” button
- Click “Finish” button
You did it…!!
Restoring a database
Now if you want to restore your logical database you have to create Tablespaces same as like in your source database
Check available table space in your source database using following query
SELECT * FROM DBA_DATA_FILES
Log in to your created database from SQL plus here is the steps
- Go to Start -> All Programs -> Oracle-OraDB12Home -> Application Development -> SQL Plus
- Login using SYS and password provided while creating database.
Enter user-name: SYS AS SYSDBA
Password: (Administrative password entered while installing)
Then press Enter…
- Run script ("SELECT * FROM DBA_DATA_FILES") to knowthe available TABLESPACES in new database.
- Then create remaining TABLESPACES same as like in source server using following queries
CREATE TABLESPACE 'tablespace name' DATAFILE 'path - check the path of listed tablespaces' SIZE ? G
- If source contains two TABLESPACE files for one TABLESPACE alter the TABLESPACE using following script
ALTER TABLESPACE 'TABLEspace name' ADD DATAFILE 'path' SIZE ?G;
Once you creates all tablespaces same as like in source server then create user in new server.
Create User using following query
CREATE USER USERNAME IDENTIFIED BY PASSWORD
GRANT DBA TO USERNAME
If you get ORA-65096 Error
RUN THE FOLLOWING Script
ALTER SESSION SET "_ORACLE_SCRIPT"=true;
Session will get altered then again run script for creating user and grand user as dba
Importing
Now… You can import / restore your logical database to new database
Do the following steps
- go to start-> cmd as administrator
- write "imp" press enter
- enter created user name and password
- Then follow the lines.
- Connected to: Oracle9i Enterprise Edition Release 9.2.0.6.0 - Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.6.0 - Production
- Import file: expdat.dmp > /mention/path/of/dumpFile/includingFileName.dmp without single quote
- Enter insert buffer size (minimum is 8192) 30720> (press enter to accept default)
- Export file created by EXPORT:V09.02.00 via conventional path
import done in US7ASCII character set and AL16UTF16 NCHAR character set
import server uses AL32UTF8 character set (possible charset conversion)
- List contents of import file only (yes/no): no > press enter
- Ignore create error due to object existence (yes/no): no > press enter
- Import grants (yes/no): yes > press enter
- Import table data (yes/no): yes > press enter
- Import entire export file (yes/no): no > press enter or type no
- Username: give the userName for which you want the data to be imported
- Enter table(T) or partition(T:P) names. Null list means all tables for user
- Enter table(T) or partition(T:P) name or . if done: press enter
- . importing TST_001_V2's objects into TST_001_V2
Once it is completed, you can connect to you database and use ….