Start a Oracle Physical Standby Database

Procedure:

"STARTUP" starts the database, mount it as a Physical standby but opens the database in read-only access.

"STARTUP MOUNT" starts the database mount it as a Physical standby , but does not open the database.

Start and mount the database:

SQL> STARTUP MOUNT;

To start Redo Apply, issue the following statement:

SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION;

To start real-time apply, issue the following statement:

SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE;

ORA-00210 Recover system from loss of a Standby Control File

Procedure:

Oracle allow multiplexing of standby control files. Check Parameter Control_Files to get the exact of control file

SQL> SHOW PARAMETER CONTROL_FILES
NAME                                 TYPE        VALUE
 ------------------------------------ ----------- ------------------------------
control_files                        string      /oracle/<SID>/sapdata1/cntrl/cntrl<sid>.dbf,

                                                 /oracle/<SID>/origlogA/cntrl/cntrl<sid>.dbf,

                                                 /oracle/<SID>/origlogB/cntrl/cntrl<sid>.dbf

if one of the multiplexed standby control files is lost or not accessible or all three stand by control file has been lost.Following error occurred in alert.log

ORA-00210: cannot open the specified controlfile
ORA-00202: controlfile: '/oracle/<SID>/sapdata1/cntrl/cntrl<sid>.dbf'
ORA-27041: unable to open file

If only one file has been lost.You can copy an intact copy of the control file over the lost copy

oracle > cp -rp /oracle/<SID>/origlogA/cntrl/cntrl<sid>.dbf /oracle/<SID>/sapdata1/cntrl/

SQL > startup mount;

SQL > ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION;

If all the files has been then generate the standby file from primary and copy it to the  standby

Primary

SQL > alter database begin backup;

SQL > alter database create standby controlfile as '/oracle/<SID>/<SID>_CNTRL_NEW.dbf' reuse;

Move and rename created standby file to Standby database

Standby

SQL > startup mount;
SQL > ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION;

Delete ST03/ST03N old system data

Procedure:

1. Run the Tcode : SE37 and execute the functional module SWNC_COLLECTOR_CLEAN_SWNCMONI

2. Set the value of reset 'X' and Execute

3. Run the Tcode ST03 or ST03N , you will get the message "NO SYSTEM LOAD DATA AVAILABLE"

4. Now schedule the Job "SAP_COLLECTOR_FOR_PERFMONITOR" from SM36.

5. After the successful job finish , you will get the data in ST03

List of Available icon in SAP

Procedure :

1. Tcode : SE38 --> RSTXICON -->EXECUTE

2. Choose Icon as ABAP List --> EXECUTE

3. Icon list is displayed, to use the icon in SAP GUI logon screen . User code @08@

Change the logon screen message in SAP GUI

Procedure :

1. Check the current message on logon screen

2.  TCODE: SE61 --> Select Document Class "General text" --> select  language --> Document               Name as "ZLOGIN_SCREEN_INFO". Create a new one if it not exist or select Change to modified

3. Create a message as required

4. Save and Exit

How to activate SAP* in ABAP system

Procedure:

1. To Activate the SAP* in abap system set the parameter login/no_automatic_user_sapstar=0 in RZ10.

2. Take the restart of the application server.

If  SAP* is enable but still unable to login fllow the below procedure.

1. Logon to database server and switch to database user.

2. Start the sqlplus with the command:
        root > sqlplus / as sysdba

3. View the entries of the SAP* with following command:
   SQL > select * from "<SCHEMA_NAME>".usr02 where mandt = '<CLIENT_NO>' and bname = 'SAP*';

4. Delete the SAP* user
   SQL > delete from "<SCHEMA_NAME>".usr02 where mandt = '<CLIENT_NO>' and bname = 'SAP*';
   SQL > commit;

Note :

<SCHEMA_NAME> = SAPSR3 in Oracle

<SCHEMA_NAME> = SAPABAP1 or SAPHANADB in HANA

ORA-00060: deadlock detected while waiting for resource

Error :

DEADLOCK DETECTED ( ORA-00060 )

[Transaction Deadlock]
The following deadlock is not an ORACLE error. It is a
deadlock due to user error in the design of an application
or from issuing incorrect ad-hoc SQL. The following
information may aid in determining the deadlock:
Deadlock graph:

                 ---------Blocker(s)--------      ---------Waiter(s)---------
Resource Name process session holds waits         process session holds waits
TX-0013000e-00183dd3 302        888       X     121           51       X
TX-0028000e-000b3549 121        51         X     302      888 X

session 888: DID 0001-012E-00002340 session 51: DID 0001-0079-0000040C
session 51: DID 0001-0079-0000040C session 888: DID 0001-012E-00002340

Rows waited on:
Session 888: obj - rowid = 001B352D - AAGzUtADgAABXGaAAC
(dictionary objn - 1783085, file - 224, block - 356762, slot - 2)
Session 51: obj - rowid = 001B6BF2 - AAHDULACaAAEIR7AAH
(dictionary objn - 1797106, file - 154, block - 1082491, slot - 7)

----- Information for the OTHER waiting sessions -----
Session 51:

sid: 51 ser: 15593 audsid: 907952455 user: 34/SAPSR3
flags: (0x1000041) USR/- flags_idl: (0x1) BSY/-/-/-/-/-
flags2: (0x40009) -/-/INC
pid: 121 O/S info: user: oraBHP, term: UNKNOWN, ospid: 36985
image: oracle@oraclehost
client details:
O/S info: user: BHPadm, term: , ospid: 9188
machine: oraclehost program: dw.sapBHP_DVEBMGS01@oraclehost (TNS V1-V3)
client info: 0:ML81N:SAPLMLSR
application name: SAPLXMLU, hash value=1026532593
action name: 979, hash value=3010390085
current SQL:
UPDATE "REPOLOAD" SET "UNAM"=:A0,"UDAT"=:A1,"UTIME"=:A2,"L_DATALG"=:A3,"MINOR_VERS"=:A4,"MAJOR_VERS"=:A5 WHERE "PROGNAME"=:A6 AND "R3STATE"=:A7 AND "MACH"=:A8 AND "MAJOR_VERS"=:A9
----- End of information for the OTHER waiting sessions -----

Information for THIS session:
----- Current SQL Statement for this session (sql_id=c36b6kkw57c3t) -----
UPDATE "REPOSRC" SET "SDATE"=:A0,"STIME"=:A1 WHERE "PROGNAME"=:A2 AND "R3STATE"=:A3 AND ("SDATE"<:A4 OR ("SDATE"=:A5 AND "STIME"<:A6) )
*** 2020-06-09 20:52:47.893
Attempting to break deadlock by signaling ORA-00060


Solution :

Find out if it is an APPLICATION or DATABASE deadlock as shown below

Application deadlocks :

                                                 ---------Blocker(s)--------            ---------Waiter(s)---------
Resource Name  process session holds waits         process session holds waits
TX-0013000e-00183dd3  302        888       X               121           51       X
TX-0028000e-000b3549  121        51         X                 302       888  X

Shutdown the SAP Instance and Database, Clean all the application running process,make sure no process
running and then restart the system.

Oracle deadlocks :

                                                ---------Blocker(s)--------            ---------Waiter(s)---------
Resource Name  process session holds waits         process session holds waits
TX-0013000e-00183dd3  302        888       X               121           51       S
TX-0028000e-000b3549  121        51         X                 302       888  S

Follow SAP Note : 84348

Oracle Datapump table import/export for Oracle Database

Procedure :


1. Create the database user

SQL > sqlplus / as sysdba
SQL > Alter User john Identified  By penguin Account Unlock;

2. Create the alias for the import/export directory

root > mkdir /oracle/PUP/export_table
root > chown oracle:oinstall export_table
root > chmod 775 export_table
SQL > CREATE OR REPLACE DIRECTORY export_table AS '/oracle/PUP/sapdata1/exp_table';
SQL > GRANT READ, WRITE ON DIRECTORY export_table TO john;



3. Export / import the table using below command

Table Export:

root > expdp john/penguin@PUP tables="SAPSR3".CCCFLOW directory=export_table dumpfile=CCCFLOW.dmp logfile=expdpCCCFLOW.log;

Table Import:

root > impdp john/penguin@PUP tables="SAPSR3".CCCFLOW directory=export_table dumpfile=CCCFLOW.dmp logfile=impdpCCCFLOW.log;

Note :
Always make sure while taking the export no sql connection or user connection are available

ORA-31993: cannot overwrite parameter file

Error :

Procedure:

Rename the spfile<SID>.ora and let the brtools create the spfile by
command brconnect -u /-c -f check

OR

If you are using BRTOOLS 740 and PATCH (26)< 32 then do the DBA Tool patching with latest.

On-Premise SAP VM Migration to AWS Cloud Using Cloud Endure

How to connect and transfer data (WINSCP) with other user on AWS EC2 Instance

Procedure :

1. Download and Install the PuttyGen link.

2. Open the PuttyGen and Click on Generate

3. Keep hovering in the KEY blank area until the status bar gets complete.


4. Copy the Public key till end

5. Paste the public key in ~/.ssh/authorized_keys of the EC2 Instance user

6. Restart the ssh service

7. Click on Save private key and save it on safe location.

8. Now open the WINSCP provide the IP and the private key of the EC2 Instance.

9. Provide the Username

10. Now you can copy the data

How to SSH with other user on AWS EC2 Instance

Procedure :

1. Download and Install the PuttyGen link.

2. Open the PuttyGen and Click on Generate

3. Keep hovering in the KEY blank area until the status bar gets complete.

4. Copy the Public key till end

5. Paste the public key in ~/.ssh/authorized_keys of the EC2 Instance user

6. Restart the ssh service

7. Click on Save private key and save it on safe location.

8. Now open the PUTTY provide the IP and the private key of the EC2 Instance

9. Now provide the user on the login screen and you will be connected to the instance.