1. Install Oracle without database installation in Destination system if it’s a new system
2. Install SAP Central Instance only in the Destination system. (Database instance installation not required.
3. To create a trace file in the source system execute the following command in sql prompt.
Alter database backup controlfile to trace;
(control file will be created)
Go to \oracle\<SID>\saptrace - sort with date (take the latest file)
Open the file, in the middle of the file copy from this starting lines to the end of the below line.
STARTUP NOMOUNT
CREATE CONTROLFILE REUSE DATABASE "PRD" NORESETLOGS ARCHIVELOG
……..
…………..
CHARACTER SET UTF8
;
4. Paste the above information in a notepad file and save as “control.sql” (sql script file) and copy to the c:\ drive of the destination system.
5. Copy the offline backup files to the destination system by creating the folder and subfolder and copy the files to the same folder and subfolder mentioned in the trace file (control.sql).
Dos command mk dir <folder name>, <subfolder name>, <subfolder name>
The above DOS commands will create the folder and subfolder in a single shot.
6. Copy the init<SID>.prd, init<SID>.ora, init<SID>.sap from the source system (oracle\ora92\database) and paste to the same folder in the destination system. (if the system id is same and control file location are same no need to use the next two point)
7. and rename the file name by changing the <SID> to be the same as Destination system.
8. Open the initTST.ora file and change the values or (disable by #) accordingly for host name, drive change of control file location of destination server etc. e.g
db_name='TST'
*.control_files='F:\oracle\PRD
*.fal_client='TST'
*.fal_server='PRDSTDY'
9. Copy the tnsname.ora, sqlnet.ora, listener.ora from \ ora92\network\admin and save it to destination system \ ora92\network\admin.
Open tnsnames.ora file in notepad and change the <sid>.world to current <sid>.world
Open listner.ora file in notepad and change the <sid> to current <sid> and the path of oracle.
(If required by the destination system of any port change in oracle, kindly changes the port no.)
10. Open the control file from the destination system c:\ in notepad and edit the following lines
Remove the first line “STARTUP NOMOUNT”
CREATE CONTROLFILE set DATABASE "TST" RESETLOGS ARCHIVELOG (this will set the database as “TST” and reset the logs newly (with this the archive logs are not able to apply from the source system. new logs will be created in the destination system from no.1).
If you want to apply archive logs from source system then you have to issue “noresetlogs”
11. Login to SQL prompt and issue
Oradim –NEW –SID TST –INTPWD ORACLE –STARTMODE a;
@c:\control.sql
Startup nomount pf=D:oracle\ora92\database\initTST.ora (this is the path of your init<SID>.ora
Sqlplus “/as sysdba”
Oradim –delete –sid tst;
Oradim –new –sid tst –intpwd oracle –startmode a;
Open the database with Startup nomount
And check the tables for oracle is working or not. (If working)
12. Change the schemas objects (OPS$, SID users etc) in Oracle by issue the following command
C:\Documents and Settings\tstadm>set oracle_sid=tst
C:\Documents and Settings\tstadm>sqlplus "/as sysdba"
SQL> desc all_users;
Name Null? Type
-
-
-
USERNAME NOT NULL VARCHAR2(30)
USER_ID NOT NULL NUMBER
CREATED NOT NULL DATE
SQL> select username from all_users;
USERNAME
-
SYS
SYSTEM
OUTLN
DBSNMP
OPS$JMPRD\PRDADM
SAPPRD
OPS$JMPRD\SAPSERVICEPRD
VERIT
OPS$JMDR\PRDADM
OPS$JMDR\SAPSERVICEPRD
SQL> select owner from dba_tables where table_name='SAPUSER';
OWNER
-
OPS$JMPRD\PRDADM
OPS$JMDR\PRDADM
SQL> drop table "OPS$JMPRD\PRDADM".SAPUSER;
Table dropped.
SQL> drop table "OPS$JMDR\PRDADM".sapusEr;
Table dropped.
SQL> select owner from dba_tables where table_name='SAPUSER';
no rows selected
SQL> DESC V$TABLESPACE;
Name Null? Type
-
-
-
TS# NUMBER
NAME VARCHAR2(30)
INCLUDED_IN_DATABASE_BACKUP VARCHAR2(3)
SQL> SELECT NAME FROM v$TABLESPACE;
NAME
-
SYSTEM
PSAPUNDO
PSAPPRD
PSAPPRD640
PSAPPRDUSR
PSAPUNDO1
PSAPTEMP
7 rows selected.
SQL> CREATE USER "OPS$TSTADM" DEFAULT TABLESPACE PSAPPRDUSR TEMPORARY TABLESPACE PSAPTEMP IDENTIFIED EXTERNALLY;
User created.
SQL> SELECT USERNAME FROM ALL_USERS;
USERNAME
-
SYS
SYSTEM
OUTLN
DBSNMP
OPS$JMPRD\PRDADM
SAPPRD
OPS$JMPRD\SAPSERVICEPRD
VERIT
OPS$JMDR\PRDADM
OPS$JMDR\SAPSERVICEPRD
OPS$TSTADM
11 rows selected.
SQL> DROP USER "OPS$JMDR\PRDADM";
User dropped.
SQL> DROP USER "OPS$JMPRD\PRDADM";
User dropped.
SQL> GRANT CONNECT ,RESOURCE TO "OPS$TSTADM";
Grant succeeded.
SQL> create table "OPS$TSTADM".SAPUSER ( USERID VARCHAR2(256), PASSWD VARCHAR2 (
256));
Table created.
SQL> ALTER USER SAPPRD IDENTIFIED BY SAP;
User altered.
SQL> insert into "OPS$TSTADM".SAPUSER values ('SAPPRD', 'sap');
1 row created.
SQL> SELECT OWNER, TABLE_OWNER, TABLE_NAME FROM DBA_SYNONYMS WHERE SYNONYM_NAME=
'SAPUSER';
OWNER TABLE_OWNER
-
-
TABLE_NAME
-
OPS$JMPRD\SAPSERVICEPRD OPS$JMPRD\PRDADM
SAPUSER
OPS$JMDR\SAPSERVICEPRD OPS$JMDR\PRDADM
SAPUSER
SQL> create public synonym sapuser for "OPS$TSTADM".SAPSUER;
Synonym created.
SQL> SELECT OWNER, TABLE_OWNER, TABLE_NAME FROM DBA_SYNONYMS WHERE SYNONYM_NAME=
'SAPUSER';
OWNER TABLE_OWNER
-
-
TABLE_NAME
-
OPS$JMPRD\SAPSERVICEPRD OPS$JMPRD\PRDADM
SAPUSER
OPS$JMDR\SAPSERVICEPRD OPS$JMDR\PRDADM
SAPUSER
PUBLIC OPS$TSTADM
SAPSUER
SQL> CREATE USER "OPS$ORATST" DEFAULT TABLESPACE PSAPPRDUSR TEMPORARY TABLESPACE
PSAPTEMP IDENTIFIED EXTERNALLY;
User created.
SQL> GRANT CONNECT ,RESOURCE TO "OPS$ORATST";
Grant succeeded.
SQL> CREATE SYNONYM "OPS$ORATST".SAPUSER FOR "OPS$TSTADM".SAPUSER;
Synonym created.
13. Only change the above user and passwords only.
14. Go to My computer \ properties \advanced\environment variables\ user variables\
Change theVARIABLE = “DBS_ORA_SCHEMA” and VALUE = <old value of the source sys> SAPPRD
Why we are changing these values is because the schemas are in the form of old system only.
(not changed). So the new value has to be changed to source value.
15. Try to start the listener by issuing “lsnrctl” command from sql prompt, then
Status
Start
Stop
TNSPING TST
Exit
These above commands are to know the listener status.
16. Then supply this command in SQL prompt
sapprd /sap@TST here sapprd is the old schema object and TST is the destination SID
17. Schema names will be always old sid names
18. Start the SAPMMC.
The above procedure for Oracle 9. You can user the same procedure for Oracle 10 too.
rgds,
JP.
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.