ORA-19909: datafile 1 belongs to an orphan incarnation

 Problem :

RMAN> RECOVER DATABASE FROM TAG STANDBY_24062021; 
Starting recover at 26-JUN-21 allocated channel: 
ORA_DISK_1 channel ORA_DISK_1: SID=701 device type=DISK starting media recovery media recovery failed 
RMAN-00571: =========================================================== 
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS =============== RMAN-00571: =========================================================== RMAN-03002: failure of recover command at 06/26/2021 00:03:23 

ORA-00283: recovery session canceled due to errors 
RMAN-11003: failure during parse/execution of SQL statement: alter database recover if needed standby start 
ORA-00283: recovery session canceled due to errors 
ORA-19909: datafile 1 belongs to an orphan incarnation
ORA-01110: data file 1: '/oracle/EHS/sapdata1/system_1/system.data1'

Solution :

Find out incarnation details from both DC and DR databases. 

At Primary Database

        RMAN> list incarnation of database; 

List of Database Incarnations 

DB Key Inc Key DB Name DB ID STATUS Reset SCN Reset Time 

------- ------- -------- ---------------- --- ---------- ---------- 

1 1 PEP 2473586590 CURRENT 1 29-APR-14 

2 2 PEP 2473586590 ORPHAN 8252951530 25-JUN-17 

At Standby Database

    RMAN> list incarnation of database; 

List of Database Incarnations 

DB Key Inc Key DB Name DB ID STATUS Reset SCN Reset Time 

------- ------- -------- ---------------- --- ---------- ---------- 

1 1 EHS 2473586590 PARENT 1 29-APR-14 

2 2 EHS 2473586590 ORPHAN 8252951530 25-JUN-17 

3 3 EHS 2473586590 CURRENT 22946868559 09-AUG-20

You can see from above data that Primary and Standby database are on different incarnation (Statue: CURRENT). You have to change standby database incarnation to match DC incarnation. 

 In our case below command will work: 

At Standby database

        RMAN> reset database to incarnation 1; 

database reset to incarnation 1 

        RMAN> list incarnation of database; 

List of Database Incarnations 

DB Key Inc Key DB Name DB ID STATUS Reset SCN Reset Time 

------- ------- -------- ---------------- --- ---------- ---------- 

1 1 EHS 2473586590 CURRENT 1 29-APR-14 

2 2 EHS 2473586590 ORPHAN 8252951530 25-JUN-17 

3 3 EHS 2473586590 ORPHAN 22946868559 09-AUG-2

Oracle Standby database incremental recovery using RMAN

 Problem :

When oraarch from Primary Database (DC) got deleted or corrupted before apply to Standby Database (DR), because of this log apply has stopped and both the system are not in sync.

Solution :

1. Verify log sequence at both DC and DR Run below command at both DC and DR. 

Find out last log number generated and last log applied at DR. Verify if there is a gap. 

        select max(sequence#) from v$archived_log where applied='YES';

2.  Stop oraarch shipping from DC to DR.

Set log_archive_dest_state_2 parameter to ‘DEFER’ at DC database in order to stop log shipping. 

At Primary Database: 

    Verify Parameter value: 

        show parameter log_archive_dest_state_2; 
                log_archive_dest_state_2 string     ENABLE 

    Change Parameter value: 

        alter system set log_archive_dest_state_2='DEFER' scope=both; 

    Verify Parameter value: 

        show parameter log_archive_dest_state_2; 
                    log_archive_dest_state_2 string     DEFER

3. Find out current SCN number of DR Database 

At Standby Database: 

    Run below command to find out SCN number: 

        SELECT to_char(CURRENT_SCN) FROM V$DATABASE;

            TO_CHAR(CURRENT_SCN) 
            264020244345

4. Take incremental backup (through RMAN utility) of DC Database from SCN number captured in last step .

At Primary Database: 

            run 
            { 
             allocate channel ch1 type disk; 
             allocate channel ch2 type disk; 
             allocate channel ch3 type disk; 
             backup incremental from scn 264020244345 database tag='STANDBY_24092021' format '/media/database_%d_%u_%s'; 
            release channel ch1; 
            release channel ch2; 
            release channel ch3; 
            }

5. Create a standby control file to restore it in the standby database.

At Primary Database: 

Run below command to create standby control file. 

        alter database create standby controlfile as '/media/cntrlEHS.dbf'; 

6. Move the incremental backup files and control files from DC to DR through SCP/rsync

7. Stop the recovery process (MRP) in DR.

At Standby Database: 

    Run below command to stop MRP process at DR. 

        alter database recover managed standby database cancel;

8. Shutdown the database and replace control files 

At Standby Database: 

    Run below command to shut down the database

        SQL> shutdown immediate;    

    Take backup of current control files and then replace all control files

9. Add incremental backup files to RMAN catalog and start the recovery. 

At Standby Database: 

    Add backup files to catalog with below command. 

        RMAN> catalog backuppiece ‘/media/database_EHS_o902a1as_5897’;

Start RMAN recovery with below command. 

        RMAN> RECOVER DATABASE FROM TAG STANDBY_24092021;

10. After completion of restore start the log shipping from DC to DR At Primary Database: 

    Run below command to start log shipping. 

        alter system set log_archive_dest_state_2='ENABLE' scope=both;

11. Start the log shipping from DC to DR At Primary Database.

    Run below command to start log shipping. 

        alter system set log_archive_dest_state_2='ENABLE' scope=both;

12. Verify that logs are being shipped from DC to DR and getting applied at DR. Once all logs have been applied, check last log applied at both DC and DR.

Oracle Listener

 

Information:

Oracle Listener runs on database server to receive the incoming connection request and provide the connectivity with database server.

Listener can be configured on multiple listening address and its configuration can be stored in listener.ora

The default listener configuration with name LISTENER and listen on TCP/IP port 1521. 

(ADDRESS=(PROTOCOL=TCP)(HOST=hostname)(PORT=1521))

It can be configured in two ways statically and dynamically. Statically registration can be done in listener.ora file where as dynamic registration is called service registration. This dynamic registration has been performed by PMON process.

SERVER=

  (DESCRIPTION=

    (ADDRESS_LIST=

      (ADDRESS=(PROTOCOL=tcp)(HOST=doping)(PORT=1521))

      (ADDRESS=(PROTOCOL=ipc)(KEY=extproc) (queuesize=100))))

SID_LIST_LISTENER=

  (SID_LIST=

    (SID_DESC=

      (SID_NAME=KS1)

      (ORACLE_HOME=/usr/oracle/12202)

      (PROGRAM=extproc)))

  

This example shows that the listener is registered with name SERVER in listener.ora

Drop Tablespace for Oracle Database after Upgrade

Steps : 

1. Check whether the tablespace is empty with the following command

    SQL> select count(*) from dba_segments where tablespace_name='old_Tablespace';

If it show empty which indicates that all segments are stored in the new tablespace, then you can delete old tablespace. If it's not empty, you have to reorganize all the objects from the old tablespace to the new tablespace either manually or by brspace.

2. Check whether data class is assigned to correct tablespace. "SLDEF", "SLEXC", "SSDEF", "SSEXC" should be assigned to PSAPSR3<REL>.

    Tcode : SE16 - > Table Name: TAORA and IAORA

3. Check the objects which still exists in PSAPSR3<OLDREL> as follows:

SQL> select table_name from dba_tables where tablespace_name='PSAPSR3740X' ; 

TABLE_NAME 

------------------------

TTREE 

TTREED 

TTREEP 

TTREET 

TTREEN 

TTREE_APPL 

TTREEI 

TTREE_FLNK

SQL> select index_name from dba_indexes where tablespace_name='PSAPSR3740X';

INDEX_NAME

-------------------------

TTREE_APPL~0

TTREE_APPL~PAR

TTREED~0

TTREEN~0

TTREE~0

TTREE~I01

TTREE~TYP

TTREEI~0

TTREEI~NOD

TTREET~0

TTREEP~0

TTREEP~PAR

TTREET~ID

TTREET~TXT

TTREE_FLNK^0

4. Move the shown table above from old table space to new table space

Link

5. Now again check for objects as shown in step 3.

6. If the tablespace is empty, now it can be dropped.

SQL > drop tablespace PSAPSR3740X including contents and datafiles;

ORA-00201: control file version 18.0.0.0.0 incompatible with ORACLE version 11.2.0.0.0 - NetWeaver

 

Problem:

ORA-00201: control file version 18.0.0.0.0 incompatible with ORACLE version11.2.0.0.0

Solution:

a. Reset COMPATIBLE parameter by SQL> ALTER SYSTEM SET COMPATIBLE = '18.0.0' SCOPE=SPFILE;

b. Then restart database.

        SQL> SHUTDOWN IMMEDIATE

        SQL> STARTUP

Note : SAP Supported Values list all values that are supported for SAP for the given Oracle version.

Oracle Database Release SAP Default Value         SAP Supported Value(s)

11.2.0.1 '11.2.0' (*) '11.2.0' (*)

11.2.0.2 '11.2.0' (*) '11.2.0' (*)

'11.2.0.2' '11.2.0.2'

11.2.0.3 '11.2.0' (*) '11.2.0' (*)

'11.2.0.2' '11.2.0.2'

'11.2.0.3'

11.2.0.4 '11.2.0' (*) '11.2.0' (*)

'11.2.0.2' '11.2.0.2'

'11.2.0.3'

'11.2.0.4'

12.1.0.2 '12.1.0.2.0' '12.1.0.2'

'12.1.0.2.0'

12.2.0.1 '12.2.0.1.0' '12.2.0.1.0'

18.X.Y (18c) '18.0.0' '18.0.0'

19.X.Y (19c) '19.0.0' '19.0.0'

(*) Value COMPATIBLE='11.2.0' is only supported for databases on file system, not for databases in ASM

Brconnect or Brtools fails with error ORA - 01031

Error:

Procedure:

1. It can be resolved by giving the required privileges to the User

SQL > GRANT Select on "SAPSR3".DBDIFF to '<USER>';

or

2. Download the latest sapdba_roles.sql and copy it to /sapmnt/<SID>/exe/uc/linux_x86/

oracle > sqlplus /nolog @sapdba_roles.sql '<USER>';

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;

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.

Rebuild the Oracle 11g DR configuration for SAP using File System Copy

Procedure :

a. Standby Site

      1. Shutdown the database.

                $ su – ora<sid>
                SQL> shutdown immediate;

       2. Delete all files under /oracle/SID/sapdata<n>, /oracle/SID/mirrlog<n>, /oracle/SID/origlog<n>.


b. Primary Site

       3. Begin Backup mode

                SQL> alter database begin backup;

       4. Copy all the under /oracle/SID/sapdata<n>, /oracle/SID/mirrlog<n>, /oracle/SID/origlog<n>.

       5. Create the standby database control file for standby

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

       6. End Backup Mode

                 SQL> alter database end backup;


c. Standby Site

        7. Start the database in mount

                  SQL> startup mount;

        8. Check the database role

                  SQL> select status,instance_name,database_role,open_mode from v$database,v$Instance;

       
         9. Start the log replication and log apply

                   $ lsnrctl start
                   SQL> alter database recover managed standby database disconnect from session;

         10. Check the logs are getting applied

                   SQL>  select thread#,max(sequence#) from v$archived_log where applied='YES' group by thread#;

Rebuild the Oracle 11g DR configuration for SAP using RMAN

Procedure :

a. StandBy Site

1. Shutdown the database. 

su – ora<sid>
shutdown immediate;

2. Delete all files under /oracle/SID/sapdata<n>, /oracle/SID/mirrlog<n>, /oracle/SID/origlog<n> 


3. Execute the following command

SQL> startup nomount pfile=/oracle/AB1/11203/dbs/initAB1DR.ora
ORACLE instance started.

Total System Global Area 1.8774E+10 bytes
Fixed Size                  2236128 bytes
Variable Size            9596567840 bytes
Database Buffers         9126805504 bytes
Redo Buffers               48386048 bytes
SQL>

b. Primary Site

4. Execute the following commands

pgpbibpprddc2:oraAB1 17> rman target  sys/sap123@AB1_PRIMARY.WORLD auxiliary sys/sap123@AB1DR_STANDBY.WORLD

Recovery Manager: Release 11.2.0.3.0 - Production on Fri Oct 14 12:35:04 2016

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

connected to target database: AB1 (DBID=2377696196)
connected to auxiliary database: AB1 (not mounted)

RMAN>
run {
allocate channel ch1 type disk;
allocate channel ch2 type disk;
allocate channel ch3 type disk;
allocate channel ch4 type disk;
allocate channel ch5 type disk;
allocate channel ch6 type disk;
allocate channel ch7 type disk;
allocate channel ch8 type disk;
allocate auxiliary channel ch10 type disk;
allocate auxiliary channel ch11 type disk;
allocate auxiliary channel ch12 type disk;
allocate auxiliary channel ch13 type disk;
allocate auxiliary channel ch14 type disk;
allocate auxiliary channel ch15 type disk;
allocate auxiliary channel ch16 type disk;
allocate auxiliary channel ch17 type disk;

duplicate target database for standby from active database DORECOVER NOFILENAMECHECK
spfile
 set db_unique_name='AB1DR'
 set control_files='/oracle/AB1/origlogA/cntrl/cntrlAB1DR.dbf','/oracle/AB1/origlogB/cntrl/cntrlAB1DR.dbf','/oracle/AB1/sapdata1/cntrl/cntrlAB1DR.dbf'
 set log_archive_max_processes='10'
 set fal_client='AB1DR_STANDBY.WORLD'
 set fal_server='AB1_PRIMARY.WORLD'
 set standby_file_management='AUTO'
 set log_archive_config='dg_config=(AB1,AB1DR)'
 set local_listener='(ADDRESS = (PROTOCOL = TCP)(HOST = 10.102.1.168)(PORT =1521))'
 set log_archive_dest_1= 'LOCATION=/oracle/AB1/oraarch/AB1arch MANDATORY Valid_for=(all_logfiles,all_roles) db_unique_name=AB1DR'
 set log_archive_dest_2= 'SERVICE=AB1_PRIMARY.WORLD ARCH VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=AB1'
;
}

ORA-01111 - name for data file 72 is unknown - rename to correct file

Error : 

Managed Standby Recovery not using Real Time Apply
Media Recovery failed with error 1111
Slave exiting with ORA-283 exception
Errors in file /oracle/ABC/saptrace/diag/rdbms/abcdr/ABCDR/trace/ABCDR_pr00_9668.trc:
ORA-00283: recovery session canceled due to errors
ORA-01111: name for data file 72 is unknown - rename to correct file
ORA-01110: data file 72: '/oracle/ABC/112_64/dbs/UNNAMED00072'
ORA-01157: cannot identify/lock data file 72 - see DBWR trace file
ORA-01111: name for data file 72 is unknown - rename to correct file
ORA-01110: data file 72: '/oracle/ABC/112_64/dbs/UNNAMED00072'

Recovery Slave PR00 previously exited with exception 283

Procedure :

                  Primary

                  a.  select FILE#,NAME from v$datafile where file#=72;             

                  Standby 

                  a.  select * from v$recover_file where error like '%FILE%';

                  b.  alter system set standby_file_management='MANUAL';

                  c.  alter database create datafile '/oracle/ABC/112_64/dbs/UNNAMED00072' as '/oracle/ABC/sapdata<n>/sr3_59/sr3.data62';

                 d.   alter system set standby_file_management='AUTO';

                 e.   alter database recover managed standby database disconnect from session;

Oracle 12c Data Guard configuration for SAP

Procedure :

1. BASIC SETUP AND SETTINGS 

a. Primary Host

a.1. db_name = ABC
a.2. db_unique_name = ABCDC

b. Standby Host

b.1. db_name = ABC
b.2. db_uniquie_name = ABCDR


2.  SETUP ON PRIMARY 

a. Oracle Initialization Parameters to be added  in initABC.ora

*.db_unique_name='ABCDC' 
*.dg_broker_start=TRUE
*.log_archive_dest_1='LOCATION=" /oracle/ABC/oraarch/ABCarch", valid_for=(ONLINE_LOGFILE,ALL_ROLES)'
*.log_archive_dest_state_1='ENABLE' 
*.log_archive_format='%t_%s_%r.dbf' 
*.log_archive_max_processes=2 
*.log_archive_min_succeed_dest=1 
*.log_archive_trace=0 
*.log_file_name_convert='ABC','ABC' 
*.standby_file_management='AUTO
                *.fal_client='abc_primary.world'
                *.fal_server=' '
                *.log_archive_dest_2='service="abc_standby.world"','ASYNC NOAFFIRM delay=0 optional compression=disable max_failure=0 max_connections=1 reopen=300 db_unique_name="abcdr" net_timeout=30','valid_for=(online_logfile,all_roles)'
                *.log_archive_config='dg_config=(ABCDC,ABCDR)'

b. Configuration

b.1. Listener Setting

ADMIN_RESTRICTIONS_LISTENER = on
LISTENER =
  (ADDRESS_LIST =
        (ADDRESS =
          (PROTOCOL = IPC)
          (KEY = ABC.WORLD)
        )
        (ADDRESS=
          (PROTOCOL = IPC)
          (KEY = ABC)
        )
        (ADDRESS =
          (COMMUNITY = SAP.WORLD)
          (PROTOCOL = TCP)
          (HOST = hostname)
          (PORT = 1521)
        )
  )
STARTUP_WAIT_TIME_LISTENER = 0
CONNECT_TIMEOUT_LISTENER = 10
TRACE_LEVEL_LISTENER = OFF
SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
      (SID_NAME = ABC)
      (ORACLE_HOME = /oracle/ABC/122)
    )
  (SID_DESC =
        (SID_NAME = ABC)
        (GLOBAL_DBNAME=ABC_DGMGRL)
        (ORACLE_HOME = /oracle/ABC/122)
    )
  )
ADR_BASE_LISTENER = /oracle/ABC/saptrace

c. Tnsnames Settings

ABC_STANDBY.WORLD=
  (DESCRIPTION =
    (ADDRESS_LIST =
        (ADDRESS =
          (COMMUNITY = SAP.WORLD)
          (PROTOCOL = TCP)
          (HOST = DR_hostname)
          (PORT = 1521)
        )
    )
    (CONNECT_DATA =
        (INSTANCE_NAME = ABC)
        (UR=A)
        (SERVICE_NAME = ABC.WORLD)
    )
  )
ABC_PRIMARY.WORLD=
  (DESCRIPTION =
    (ADDRESS_LIST =
        (ADDRESS =
          (COMMUNITY = SAP.WORLD)
          (PROTOCOL = TCP)
          (HOST = DC_hostname)
          (PORT = 1521)
        )
    )
    (CONNECT_DATA =
        (INSTANCE_NAME = ABC)
        (UR=A)
        (SERVICE_NAME = ABC.WORLD)
    )
  )

d. Sqlnet Settings

AUTOMATIC_IPC = ON
TRACE_LEVEL_CLIENT = OFF
NAMES.DEFAULT_DOMAIN = WORLD
# 05.01.06 unsorported parameter now
#NAME.DEFAULT_ZONE = WORLD
# 05.01.06 set the default to 10
SQLNET.EXPIRE_TIME = 10
# 05.01.06 set to default
#TCP.NODELAY=YES
# 05.01.06 set to 32768
DEFAULT_SDU_SIZE=32768
NAMES.DIRECTORY_PATH= (TNSNAMES, ONAMES, HOSTNAME)

3. SETUP ON STANDBY 

a. Oracle Initialization Parameters to be added in initABC.ora

*.db_unique_name='ABCDR' 
*.dg_broker_start=TRUE
*.log_archive_dest_1='LOCATION=" /oracle/ABC/oraarch/ABCarch", valid_for=(ONLINE_LOGFILE,ALL_ROLES)'
*.log_archive_dest_state_1='ENABLE' 
*.log_archive_format='%t_%s_%r.dbf' 
*.log_archive_max_processes=2 
*.log_archive_min_succeed_dest=1 
*.log_archive_trace=0 
*.log_file_name_convert='ABC','ABC' 
*.standby_file_management='AUTO
                *.log_archive_dest_2='location=/oracle/ABC/oraarch/ABCarch','valid_for=(STANDBY_LOGFILE,STANDBY_ROLE)'
                *.fal_client='ABC_STANDBY.WORLD'
                *.fal_server='abc_primary.world'
                *.log_archive_config='dg_config=(ABCDR,ABCDC)'


b. Configuration

b.1. Listener Setting

ADMIN_RESTRICTIONS_LISTENER = on
LISTENER =
  (ADDRESS_LIST =
        (ADDRESS =
          (PROTOCOL = IPC)
          (KEY = ABC.WORLD)
        )
        (ADDRESS=
          (PROTOCOL = IPC)
          (KEY = ABC)
        )
        (ADDRESS =
          (COMMUNITY = SAP.WORLD)
          (PROTOCOL = TCP)
          (HOST = hostname)
          (PORT = 1521)
        )
  )
STARTUP_WAIT_TIME_LISTENER = 0
CONNECT_TIMEOUT_LISTENER = 10
TRACE_LEVEL_LISTENER = OFF
SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
      (SID_NAME = ABC)
      (ORACLE_HOME = /oracle/ABC/122)
    )
  (SID_DESC =
        (SID_NAME = ABC)
        (GLOBAL_DBNAME=ABC_DGMGRL)
        (ORACLE_HOME = /oracle/ABC/122)
    )
  )
ADR_BASE_LISTENER = /oracle/ABC/saptrace

c. Tnsnames Settings

ABC.WORLD=
  (DESCRIPTION =
    (ADDRESS_LIST =
        (ADDRESS =
          (COMMUNITY = SAP.WORLD)
          (PROTOCOL = TCP)
          (HOST = hostname)
          (PORT = 1521)
        )
    )
    (CONNECT_DATA =
        (SID = ABC)
        (GLOBAL_NAME = ABC.WORLD)
    )
  )
ABC_STANDBY.WORLD=
  (DESCRIPTION =
    (ADDRESS_LIST =
        (ADDRESS =
          (COMMUNITY = SAP.WORLD)
          (PROTOCOL = TCP)
          (HOST = hostname)
          (PORT = 1521)
        )
    )
    (CONNECT_DATA =
        (INSTANCE_NAME = ABC)
        (UR=A)
        (SERVICE_NAME = ABC.WORLD)
    )
  )
ABC_PRIMARY.WORLD=
  (DESCRIPTION =
    (ADDRESS_LIST =
        (ADDRESS =
          (COMMUNITY = SAP.WORLD)
          (PROTOCOL = TCP)
          (HOST = 10.101.1.196)
          (PORT = 1521)
        )
    )
    (CONNECT_DATA =
        (INSTANCE_NAME = ABC)
        (UR=A)
        (SERVICE_NAME = ABC.WORLD)
    )
  )

d. Sqlnet Settings

AUTOMATIC_IPC = ON
TRACE_LEVEL_CLIENT = OFF
NAMES.DEFAULT_DOMAIN = WORLD
# 05.01.06 unsorported parameter now
#NAME.DEFAULT_ZONE = WORLD
# 05.01.06 set the default to 10
SQLNET.EXPIRE_TIME = 10
# 05.01.06 set to default
#TCP.NODELAY=YES
# 05.01.06 set to 32768
DEFAULT_SDU_SIZE=32768
NAMES.DIRECTORY_PATH= (TNSNAMES, ONAMES, HOSTNAME)

4. COPY PRIMARY DATABASE TO STANDBY SITE

a. Primary Site

a.1. Begin Backup
SQL> alter database begin backup;
a.2. Copy Data Files and Redolog Files
scp -r /oracle/ABC/sapdata* oracle@pgpodrprd:/oracle/ABC/
  scp -r /oracle/ABC/mirrlogA/log* oracle@drprd:/oracle/ABC/mirrlogA scp -r /oracle/ABC/origlogA/log* oracle@drprd:/oracle/ABC/origlogA
                                scp -r /oracle/ABC/mirrlogB/log* oracle@drprd:/oracle/ABC/mirrlogB scp -r /oracle/ABC/origlogB/log* oracle@drprd:/oracle/ABC/origlogB

a.3. End Backup
SQL> alter database end backup;

 Note : Before copying the data from Primary Site to Secondary Site , Shutdown the application                      and database of  Standby Site and clear all the content of /oracle/ABC/sapdata*,                                    /oracle/ABC/mirrlog* , /oracle/ABC/origlog*

a.4  Shutdown the database
SQL>shutdown immediate;

a.5. Create backup Control File.
SQL> create spfile from pfile;
SQL> startup mount;
SQL> alter database create standby controlfile as '/oracle/ABC/stdby_control.dbf' reuse;

a.6. Copy Standby Control File to Standby Host
scp -r /oracle/ABC/stdby_control.dbf oracle@pgpodrprd:/oracle/ABC/ 

a.7. Distributing the Password file to Standby
scp $ORACLE_HOME/dbs/orapwABC oracle@pgpodrprd:$ORACLE_HOME/dbs/orapwABC

a.8. Creating Standby Redolog Files
mkdir /oracle/ABC/standbylog
SQL> alter database add standby logfile '/oracle/ABC/standbylog/srl1.dbf' size 209715200 reuse; 
SQL> alter database add standby logfile '/oracle/ABC/standbylog/srl2.dbf' size 209715200 reuse; 
SQL> alter database add standby logfile '/oracle/ABC/standbylog/srl3.dbf' size 209715200 reuse;
SQL> alter database add standby logfile '/oracle/ABC/standbylog/srl4.dbf' size 209715200 reuse;
SQL> alter database add standby logfile '/oracle/ABC/standbylog/srl5.dbf' size 209715200 reuse;

                 a.9. Opening Primary Database
SQL> alter database open;

b. Standby Site

b.1. Distributing Standby Control File
cd /oracle/ABC
cp stdby_controlf.dbf /oracle/ABC/origlogA/cntrl/cntrlABC.dbf
cp stdby_controlf.dbf /oracle/ABC/origlogB/cntrl/cntrlABC.dbf 
cp stdby_controlf.dbf /oracle/ABC/sapdata1/cntrl/cntrlABC.dbf

b.2. Mounting the Database
SQL> create spfile from pfile;
SQL> startup mount;

b.3. Creating Standby Redolog Files
mkdir /oracle/ABC/standbylog
SQL> alter database add standby logfile '/oracle/ABC/standbylog/srl1.dbf' size 209715200 reuse; 
SQL> alter database add standby logfile '/oracle/ABC/standbylog/srl2.dbf' size 209715200 reuse; 
SQL> alter database add standby logfile '/oracle/ABC/standbylog/srl3.dbf' size 209715200 reuse;
SQL> alter database add standby logfile '/oracle/ABC/standbylog/srl4.dbf' size 209715200 reuse;
SQL> alter database add standby logfile '/oracle/ABC/standbylog/srl5.dbf' size 209715200 reuse;

b.4. Mounting Standby Database
SQL> startup mount;

5. Configurating DataGaurd

a. Primary Site

a.1. Creating the Configuration on Primary
pgpABCrddc1:oracle 51> dgmgrl
DGMGRL> connect sys
DGMGRL> create configuration ABC as primary database is ABCDC connect identifier is ABC_PRIMARY.WORLD;

a.2. Adding the physical Standby Database
DGMGRL> add database ABCDR as connect identifier is ABC_STANDBY.WORLD;
DGMGRL> show configuration

a.3. Overview od DataGuard Properties
DGMGRL> show database ABCDC
DGMGRL> show database verbose ABCDC
DGMGRL> show database ABCDR
DGMGRL> show database verbose ABCDR

a.4. Enable configuration
DGMGRL> enable configuration;

Note: Check Alert.log for both primary and secondary Site for any error.


6. TESTING THE SWITCHOVER 

a.  Primary Site to Secondary Site
DGMGRL> switchover to ABCDR;

b.  Secondary Site to Primary Site
DGMGRL> switchover to ABCDC;

Manually Upgrade the time zone in Oracle Database 12c

Procedure:


Go the directory path /oracle/<SID>/12201/oracore/zoneinfo, check for the latest time file available .

SQL> shu immediate;

Database closed.

Database dismounted.

ORACLE instance shut down.

SQL> startup upgrade;

ORACLE instance started.

Total System Global Area 2885681152 bytes

Fixed Size                  8624840 bytes

Variable Size            1493173560 bytes

Database Buffers         1375731712 bytes

Redo Buffers                8151040 bytes

Database mounted.

Database opened.

SQL> EXEC DBMS_DST.BEGIN_UPGRADE (27);

PL/SQL procedure successfully completed.

SQL> shu immediate;

Database closed.

Database dismounted.

ORACLE instance shut down.

SQL> startup;

ORACLE instance started.

Total System Global Area 2885681152 bytes

Fixed Size                  8624840 bytes

Variable Size            1493173560 bytes

Database Buffers         1375731712 bytes

Redo Buffers                8151040 bytes

Database mounted.

Database opened.

SQL> select version from v$timezone_file;

   VERSION

----------

        27

Suggestion:

Maintain the proper backup of the system before proceeding fore the upgrade.

SSFS Configuration for Oracle Database for Linux Systems

Concept :

Earlier the connection from the SAP ABAP system worked in such a way that OPS$ connection
was authorized by the OS user to permit access to table OPS$.<SID>ADM.SAPUSER only. It contains the actual database user used for the connection with the encrypted password.

SSFS (Secure Storage File System) will contain the encrypted file store at OS level, to connect to database from SAP ABAP System.

SSFS connections are only applicable for the ABAP system only.


Solution :

1. Create the director under the path /usr/sap/<SID>/SYS/global/security/ with <SID>adm

• mkdir -p /usr/sap/<SID>/SYS/global/security/rsecssfs/data
• mkdir -p /usr/sap/<SID>/SYS/global/security/rsecssfs/key

2. Set the environment variable for the <SID>adm user (.sapenv_<hostname>.csh and .sapenv.csh)

• setenv RSEC_SSFS_DATAPATH  /usr/sap/<SID>/SYS/global/security/rsecssfs/data
• setenv RSEC_SSFS_KEYPATH  /usr/sap/<SID>/SYS/global/security/rsecssfs/key
• setenv rsdb_ssfs_connect  1

3. Set the profile parameter (DEFAULT.PFL)

• rsec/ssfs_datapath = $(DIR_GLOBAL)$(DIR_SEP)security$(DIR_SEP)rsecssfs$(DIR_SEP)data
• rsec/ssfs_keypath  = $(DIR_GLOBAL)$(DIR_SEP)security$(DIR_SEP)rsecssfs$(DIR_SEP)key
• rsdb/ssfs_connect = 1

4. Set the SSFS Credential

• rsecssfx put DB_CONNECT/DEFAULT_DB_USER SAPSR3 -plain
• rsecssfx put DB_CONNECT/DEFAULT_DB_PASSWORD <SCHEMA_PASSWORD>

5. Check the R3trans -d

6. Delete the table SAPUSER

                 > sqlplus
                SQL> connect system/<pwd>
                SQL> drop table ops$<sid>adm.sapuser;

7. Delete the REMOTE_AUTH parameter

              > sqlplus

                SQL> connect system/<pwd>

                SQL> alter system reset remote_os_authent scope=spfile;

Move table from one tablespace to another using BRTOOLS

Procedure :

BR*Tools main menu

 1 = Instance management
 2 - Space management
 3 - Segment management
 4 - Backup and database copy
 5 - Restore and recovery
 6 - Check and verification
 7 - Database statistics
 8 - Additional functions
 9 - Exit program

Standard keys: c - cont, b - back, s - stop, r - refr
-------------------------------------------------------------------------------
BR0662I Enter your choice:
3
BR0280I BRTOOLS time stamp: 2020-04-07 08:43:07
BR0663I Your choice: '3'

BR0280I BRTOOLS time stamp: 2020-04-07 08:43:07
BR0656I Choice menu 7 - please make a selection
-------------------------------------------------------------------------------
Database segment management

 1 = Reorganize tables
 2 - Rebuild indexes
 3 - Export tables
 4 - Import tables
 5 - Alter tables
 6 - Alter indexes
 7 - Additional segment functions
 8 - Reset program status

Standard keys: c - cont, b - back, s - stop, r - refr
-------------------------------------------------------------------------------
BR0662I Enter your choice:
1
BR0280I BRTOOLS time stamp: 2020-04-07 08:43:13
BR0663I Your choice: '1'

BR0280I BRTOOLS time stamp: 2020-04-07 08:43:13
BR0657I Input menu 91 - please enter/check input values
-------------------------------------------------------------------------------
BRSPACE options for reorganization of tables

 1 - BRSPACE profile (profile) ...... [initLDD.sap]
 2 - Database user/password (user) .. [/]
 3 ~ Reorganization action (action) . []
 4 ~ Tablespace names (tablespace) .. []
 5 ~ Table owner (owner) ............ []
 6 ~ Table names (table) ............ []
 7 ~ Table partitions (tabpart) ..... []
 8 - Confirmation mode (confirm) .... [yes]
 9 - Extended output (output) ....... [no]
10 - Scrolling line count (scroll) .. [20]
11 - Message language (language) .... [E]
12 - BRSPACE command line (command) . [-p initLDD.sap -s 20 -l E -f tbreorg]

Standard keys: c - cont, b - back, s - stop, r - refr
-------------------------------------------------------------------------------
BR0662I Enter your choice:
6
BR0280I BRTOOLS time stamp: 2020-04-07 08:43:22
BR0663I Your choice: '6'
BR0681I Enter string value for "table" []:
TTREE_FLNK
BR0280I BRTOOLS time stamp: 2020-04-07 08:43:37
BR0683I New value for "table": 'TTREE_FLNK'

BR0280I BRTOOLS time stamp: 2020-04-07 08:43:37
BR0657I Input menu 91 - please enter/check input values
-------------------------------------------------------------------------------
BRSPACE options for reorganization of tables

 1 - BRSPACE profile (profile) ...... [initLDD.sap]
 2 - Database user/password (user) .. [/]
 3 ~ Reorganization action (action) . []
 4 ~ Tablespace names (tablespace) .. []
 5 ~ Table owner (owner) ............ []
 6 ~ Table names (table) ............ [TTREE_FLNK]
 7 ~ Table partitions (tabpart) ..... []
 8 - Confirmation mode (confirm) .... [yes]
 9 - Extended output (output) ....... [no]
10 - Scrolling line count (scroll) .. [20]
11 - Message language (language) .... [E]
12 - BRSPACE command line (command) . [-p initLDD.sap -s 20 -l E -f tbreorg -t "TTREE_FLNK"]

Standard keys: c - cont, b - back, s - stop, r - refr
-------------------------------------------------------------------------------
BR0662I Enter your choice:
c
BR0657I Input menu 353 - please enter/check input values
-------------------------------------------------------------------------------
Options for reorganization of tables: SAPSR3.TTREE_FLNK (degree 1)

 1 * Reorganization action (action) ............ [reorg]
 2 - Reorganization mode (mode) ................ [online]
 3 - Create DDL statements (ddl) ............... [yes]
 4 ~ New destination tablespace (newts) ........ []
 5 ~ Separate index tablespace (indts) ......... []
 6 - Parallel threads (parallel) ............... [1]
 7 ~ Table/index parallel degree (degree) ...... []
 8 ~ Category of initial extent size (initial) . []
 9 ~ Sort by fields of index (sortind) ......... []
10 # Index for IOT conversion (iotind) ......... [FIRST]
11 - Compression action (compress) ............. [none]
12 # LOB compression degree (lobcompr) ......... [medium]
13 # Index compression method (indcompr) ....... []

Standard keys: c - cont, b - back, s - stop, r - refr
-------------------------------------------------------------------------------
BR0662I Enter your choice:
BR0280I BRSPACE time stamp: 2020-04-05 22:10:08
BR0663I Your choice: '4'
BR0681I Enter string value for "newts" []:
BR0280I BRSPACE time stamp: 2020-04-05 22:10:13
BR0683I New value for "newts": 'PSAPSR3740X'

BR0280I BRSPACE time stamp: 2020-04-05 22:10:13
BR0657I Input menu 353 - please enter/check input values
-------------------------------------------------------------------------------
Options for reorganization of tables: SAPSR3.TTREE_FLNK (degree 1)

 1 * Reorganization action (action) ............ [reorg]
 2 - Reorganization mode (mode) ................ [online]
 3 - Create DDL statements (ddl) ............... [yes]
 4 ~ New destination tablespace (newts) ........ [PSAPSR3740X]
 5 ~ Separate index tablespace (indts) ......... []
 6 - Parallel threads (parallel) ............... [1]
 7 ~ Table/index parallel degree (degree) ...... []
 8 ~ Category of initial extent size (initial) . []
 9 ~ Sort by fields of index (sortind) ......... []
10 # Index for IOT conversion (iotind) ......... [FIRST]
11 - Compression action (compress) ............. [none]
12 # LOB compression degree (lobcompr) ......... [medium]
13 # Index compression method (indcompr) ....... []

Standard keys: c - cont, b - back, s - stop, r - refr
-------------------------------------------------------------------------------
BR0662I Enter your choice:
BR0280I BRSPACE time stamp: 2020-04-05 22:10:14
BR0663I Your choice: 'c'
BR0259I Program execution will be continued...

BR0280I BRSPACE time stamp: 2020-04-05 22:10:14
BR1108I Checking tables for reorganization...

BR0280I BRSPACE time stamp: 2020-04-05 22:10:14
BR1112I Number of tables selected/skipLDD for reorganization: 1/0

BR0280I BRSPACE time stamp: 2020-04-05 22:10:14
BR0670I Enter 'c[ont]' to continue, 'b[ack]' to go back, 's[top]' to abort:
BR0280I BRSPACE time stamp: 2020-04-05 22:10:16
BR0257I Your reply: 'c'
BR0259I Program execution will be continued...

BR0370I Directory /oracle/LDD/sapreorg/sfdmwqwt created

BR0280I BRSPACE time stamp: 2020-04-05 22:10:16
BR1101I Starting 'online' table reorganization...
BR0280I BRSPACE time stamp: 2020-04-05 22:10:16
BR1124I Starting 'online' reorganization of table SAPSR3.TTREE_FLNK ...
BR0280I BRSPACE time stamp: 2020-04-05 22:10:17
BR1105I Table SAPSR3.TTREE_FLNK reorganized successfully

BR0280I BRSPACE time stamp: 2020-04-05 22:10:17
BR1141I 1 of 1 table processed - 3373 of 3373 rows done
BR0204I Percentage done: 100.00%, estimated end time: 22:10
BR0001I **************************************************

BR0280I BRSPACE time stamp: 2020-04-05 22:10:17
BR1102I Number of tables reorganized successfully: 1

BR1142I Tables with the longest duration of reorganization for owner SAPSR3

  Pos. Owner    Table                                 Rows  Duration
                                                              [m:s]
    1  SAPSR3   TTREE_FLNK                            3373     0:01

BR0280I BRSPACE time stamp: 2020-04-05 22:10:17
BR0670I Enter 'c[ont]' to continue, 'b[ack]' to go back, 's[top]' to abort:
BR0280I BRSPACE time stamp: 2020-04-05 22:10:33
BR0257I Your reply: 'c'
BR0259I Program execution will be continued...

BR0280I BRSPACE time stamp: 2020-04-05 22:10:33
BR1022I Number of tables processed: 1
BR1003I BRSPACE function 'tbreorg' completed

BR1008I End of BRSPACE processing: sfdmwqwt.tbr 2020-04-05 22:10:33
BR0280I BRSPACE time stamp: 2020-04-05 22:10:33
BR1005I BRSPACE completed successfully

Suggestion :
Always have full successfully backup of the system.
Make sure table your moving should have appropriate TABART association.

ORA-01012: not logged on for SAP System

Symptom :

While starting the database you may get the error ORA-01012 not logged on

Solution:

This error usually occur because of  orphaned shared memory segment with user oracle or ora<sid>.

In order to resolve the issue run the command ipcs or sysresv

To remove the these shared segement use command ipcrm -m <shmid>

Example :

ipcrm -m 1690043378

Suggestion:

Do not run this command when system is up and running that may lead to crash your database.