Restore old oracle database on new server using old oracle database data

On new system:

1. Setup oracle

2. Stop all oracle services

3. Copy into new oracle location,

    a) ~/ admin
    b) ~/cfgtoollogs
    c) ~/checkpoints
    d) ~/diag
    e) ~/flash_recovery_area
    f) ~/oradata
    g) ~/tablespaces
    
4. Start all oracle services (you may restart the system instead)

5. Shutdown oracle

C:\Windows\system32>sqlplus / as sysdba

SQL> shutdown
ORA-01109: database not open

Database dismounted.
ORACLE instance shut down.

6. Change all files path,

Control files,

ALTER DATABASE RENAME FILE 'E:\ORACLE\CGSPL\ORADATA\ORCL\SYSTEM01.DBF' TO 'F:\ORACLE\ORADATA\ORCL\SYSTEM01.DBF';
ALTER DATABASE RENAME FILE 'E:\ORACLE\CGSPL\ORADATA\ORCL\SYSAUX01.DBF' TO 'F:\ORACLE\ORADATA\ORCL\SYSAUX01.DBF';
ALTER DATABASE RENAME FILE 'E:\ORACLE\CGSPL\ORADATA\ORCL\UNDOTBS01.DBF' TO 'F:\ORACLE\ORADATA\ORCL\UNDOTBS01.DBF';
ALTER DATABASE RENAME FILE 'E:\ORACLE\CGSPL\ORADATA\ORCL\USERS01.DBF' TO 'F:\ORACLE\ORADATA\ORCL\USERS01.DBF';
ALTER DATABASE RENAME FILE 'E:\ORACLE\CGSPL\ORADATA\ORCL\EXAMPLE01.DBF' TO 'F:\ORACLE\ORADATA\ORCL\EXAMPLE01.DBF';
ALTER DATABASE RENAME FILE 'E:\ORACLE\CGSPL\ORADATA\ORCL\TEMP01.DBF' TO 'F:\ORACLE\ORADATA\ORCL\TEMP01.DBF';

Log files,

ALTER DATABASE RENAME FILE 'E:\ORACLE\CGSPL\ORADATA\ORCL\REDO03.LOG' TO 'F:\ORACLE\ORADATA\ORCL\REDO03.LOG';
ALTER DATABASE RENAME FILE 'E:\ORACLE\CGSPL\ORADATA\ORCL\REDO02.LOG' TO 'F:\ORACLE\ORADATA\ORCL\REDO02.LOG';
ALTER DATABASE RENAME FILE 'E:\ORACLE\CGSPL\ORADATA\ORCL\REDO01.LOG' TO 'F:\ORACLE\ORADATA\ORCL\REDO01.LOG';

Tablespace,

ALTER DATABASE RENAME FILE 'E:\ORACLE\CGSPL\TABLESPACES\RECON.DBF' TO 'F:\ORACLE\TABLESPACES\RECON.DBF';

Basic structure of alter database rename file,

ALTER DATABASE RENAME FILE '<old>' TO '<new>';

Check data file path before and after for confirmation,

SELECT FILE#,NAME FROM V$DATAFILE;

Check log file path before and after for confirmation,

SELECT GROUP#,MEMBER FROM V$LOGFILE;

7. Change all directories path,

create or replace directory SUBDIR as 'F:\Oracle\product\11.2.0\dbhome_1\demo\schema\order_entry\/2002/Sep';
create or replace directory SS_OE_XMLDIR as 'F:\Oracle\product\11.2.0\dbhome_1\demo\schema\order_entry\';
create or replace directory LOG_FILE_DIR as 'F:\Oracle\product\11.2.0\dbhome_1\demo\schema\log\';
create or replace directory DATA_FILE_DIR as 'F:\Oracle\product\11.2.0\dbhome_1\demo\schema\sales_history\';
create or replace directory XMLDIR as 'c:\ade\aime_dadvfh0169\oracle/rdbms/xml';
create or replace directory MEDIA_DIR as 'F:\Oracle\product\11.2.0\dbhome_1\demo\schema\product_media\';
create or replace directory DATA_PUMP_DIR as 'F:\Oracle\admin/orcl/dpdump/';
create or replace directory ORACLE_OCM_CONFIG_DIR as 'F:\Oracle\product\11.2.0\dbhome_1/ccr/state';

Basic structure of create & replace directories,

create or replace directory SUBDIR as '<new>';

Check directories path before and after for confirmation,

select * from dba_directories;

8. Start oracle database,

SQL> startup
ORACLE instance started.

Total System Global Area 6764429312 bytes
Fixed Size                  2188568 bytes
Variable Size            3506440936 bytes
Database Buffers         3238002688 bytes
Redo Buffers               17797120 bytes
Database mounted.
Database opened.

9. Confirmation of oracle import success

SELECT USERNAME FROM ALL_USERS ORDER BY USERNAME;