SAP Oracle system refresh

1. STOP SAP & Oracle SERVICES AND EMPTY FOLLOWING DIRECTORIES :

Go to Target Server & Stop all services (oracle & SAP both).

Now Delete the All files inside the following Directories.

SAPDATA_HOME\sapdata1\sysaux

SAPDATA_HOME\sapdata1\system

SAPDATA_HOME\sapdata1\temp

SAPDATA_HOME\sapdata1\undo

SAPDATA_HOME\sapdata1\cntrl

SAPDATA_HOME\sapdata2\ sr3_1 ………sr3_n

SAPDATA_HOME\sapdata3\ sr3702_1 ……… sr3702_n

SAPDATA_HOME\sapdata4\sr3usr_1

SAPDATA_HOME\origlogA\cntrl

SAPDATA_HOME\origlogB\cntrl

SAPDATA_HOME\sapdata1\cntrl

2. Modifications in Backup File( Replace path as per Target system), Place the xxxxxxxx.and File In Sap backup Folder

SAPDATA_HOME \sapbackup

The File Name Will Be Like This belerzpm.and

Rename prd to SID file & H:\online to f:\

3. Run Below command.

Brrestore –m full –d disk –b betwoyae.and

1. RESTORE complete.

2. Again Delete Oracle Control Files under these directories that comes after restoration

SAPDATA_HOME \origlogA\cntrl

SAPDATA_HOME \origlogB\cntrl

SAPDATA_HOME \sapdata1\cntrl

1. Create trace file in source server using this command:

SQL>Alter database backup controlfile to trace;

Copy this trace file to target server and rename to control.sql.

Now modify this control.sql

Delete all before “startup mount” & after “CHARACTER SET UTF8;” in control.sql

Change

• <source SID> to <target SID>

• REUSE –> SET

• NORESETLOGS --> RESETLOGS

1. Trace file Location: ( F:\ORACLE\PRD\SAPTRACE\diag\rdbms\prd\prd\trace\prd_ora_51532.trc)

2. Now, Put this Control.sql to C drive on <TARGET SID> SYSTEM.

Start Oracle Services

SQL> @C:\CONTROL.SQL;

1. Copy all archive logs to SAPDATA_HOME\oraarch & Run these commands

SQL> RECOVER DATABASE USING BACKUP CONTROLFILE UNTIL CANCEL;

SQL>ALTER DATABASE OPEN RESETLOGS;

1. Create Users as per note 50088.

Post Restore commands:

CREATE USER "OPS$SANDBOX-2\SNDADM" DEFAULT TABLESPACE PSAPSR3 TEMPORARY TABLESPACE PSAPTEMP IDENTIFIED EXTERNALLY;

GRANT CONNECT, RESOURCE TO "OPS$SANDBOX-2\SNDADM";

CREATE USER "OPS$SANDBOX-2\SAPSERVICESND" DEFAULT TABLESPACE PSAPSR3USR TEMPORARY TABLESPACE PSAPTEMP IDENTIFIED EXTERNALLY;

GRANT CONNECT, RESOURCE TO "OPS$SANDBOX-2\SAPSERVICESND";

SELECT OWNER FROM DBA_TABLES WHERE TABLE_NAME = 'SAPUSER';

CREATE TABLE "OPS$SANDBOX-2\SNDADM".SAPUSER (USERID VARCHAR2(256), PASSWD VARCHAR2(256));

INSERT INTO "OPS$SANDBOX-2\SNDADM".SAPUSER VALUES ('SAPSR3', 'MASTERSND1');

SELECT OWNER, TABLE_OWNER, TABLE_NAME FROM DBA_SYNONYMS WHERE SYNONYM_NAME= 'SAPUSER';

SELECT OWNER FROM DBA_TABLES WHERE TABLE_NAME = 'SAPUSER';

CREATE SYNONYM "OPS$SANDBOX-2\SAPSERVICESND".SAPUSER FOR "OPS$SANDBOX-2\SNDADM".SAPUSER;

GRANT SELECT, UPDATE ON "OPS$SANDBOX-2\SNDADM".SAPUSER TO "OPS$SANDBOX-2\SAPSERVICESND";

select username, account_status from dba_users;

shutdown

startup

brconnect -c -u / -f chpass -o SAPSR3 -p MASTERSND1

brconnect -c -u / -f chpass -o sys -p MASTERSND1

brconnect -c -u / -f chpass -o system -p MASTERSND1

brconnect -c -u / -f chpass -o ops$SANDBOX-2\SNDADM -p MASTERSND1

brconnect -c -u / -f chpass -o ops$SANDBOX-2\SAPSERVICESND -p MASTERSND1

brconnect -c -u / -f chpass -o ops$SAPPRD\PRDADM -p MASTERSND1

brconnect -c -u / -f chpass -o ops$SAPPRD\SAPSERVICEPRD -p MASTERSND1

if error no privilages in table

ALTER USER "OPS$SANDBOX-2\SNDADM" QUOTA 100M ON PSAPSR3;

GRANT UNLIMITED TABLESPACE TO "OPS$SANDBOX-2\SNDADM";