Sap Database Notes -4

Sap Database Notes 4:-

BR Tools:
1. Login to ORA<SID> using putty
2. Type BRTOOLS
3. There are totally 9 option in BR tools
a. Select Instant management, it is option 1
b. In Database instance management select option 2 to shutdown the database.
c. Type ‘C’ and click enter to continue
d. In Database instance shutdown main menu select option 1 shutdown DB.
e. Under options for shutting down the DB instance we have to choose option 1, that is close mode(Default mode is immediate)
f. Select option 1 and enter string value for ‘mode’ (Immediate|normal|transcations|abort).
Note: if the users are logged in to the SAP system then I cannot use immediate, normal, transactional modes, using abort mode will forcefully shutdown and will result to data loss hence never use this option so to be on the safest side always shutdown using normal mode.

Alter DB Instance (Switching off archive mode):
1. Shut down SAP -> Stop SAP [SID<adm>]
2. Log on to ORA<SID> user and start BR tools
3. In BR tools -> Select option 1 (Instance Management)
4. Start up database -> Select option 1
5. Alter DB instance -> Option 3
6. Enter ‘c’ to continue
7. Enter ‘c’ to continue
8. Select option 4 for set non archive mode
9. Enter ‘c ‘to continue and select option 5 to show instance status
Note: while switching to archive mode and non-archive mode, it will shutdown the DB instance first and then starts the DB instance. In each of these cases the time stamp is recorded that is data and time. Once the DB is up and running always check the status before performing any action.

(Q) If SAP started and I am trying to switch to non-archive mode what will happen.
(A) It will show an error showing that SAP instance is running. Please showdown first or use force option.

(Q) If SAP is running and I try to shutdown the DB using BR tools what will happen.
(A) It through an error saying that SAP is running please shutdown the SAP first or force option and then continue.

Table space administration:
1. Oracle stores data in table spaces, each table space consists of one or more data files.
2. Data files are plain files stored on local system
3. Oracle has 4 segment types
a. Data -> This segment contains table data in rows
b. Index -> Each table has one primary index and ‘n’ number of secondary indexes (optional). This index is used for faster access to table data and to enforce unique constrains.
c. Temp Segment -> This segment is used for sorts and to create indexes.
d. Roll back/undo segment -> this segment is used to provide read consistency that is ability to roll back changed to tables for recovery.
4. To meet the demand of large DB, DB designers creates partition tables and indexes.
5. An index segment in oracle DB used in SAP holds either all data for take that is not partitioned or all data for a partition of partitioned table.

Common table spaces:
1. System -> Oracle data dictionary
2. PSAP ROLL -> Roll back segment
Note: From WAS 6.1 version we have SAP undo as roll back segment.
3. PSAP TEMP -> Temporary segment.

(Q) If table space is full then what are the possibility to extend the table spaces ?
(A) Option 1: Add another data file to table space
           2: Existing data file can be manually resized
           3: Properties of existing data file can be changed to auto extendable

(Q) What id the formula to increase the data files size ?
(A) Data file size = Expected DB/100

(Q) How many number of data files will be there by default ?
(A) Default there are 100 data files

(Q) Expected DB size and Data file size
    Expected DB Size     Data File Size
    Up to 200Gb               2Gb
    200 to 400Gb              4Gb
    400 to 800Gb             8Gb
    Greater than 800Gb    60Gb

(Q) What is the error related with table flow ?
(A) For table ORA1653, ORA1654 for indexes.

(Q) What will happen if max extents are reached ?
(A) ORA1533 is the error forms extent reached. If max extent is reaching it limits, then increase next extent. When extents are dripped they are marked as free and their blocks can be used by new extents, but adjacent blocks are not combined. The DBA must use “COALEXE” free extent into one large extent. There are two options for “COALEXE” extent.
     1. BRCONNECT –f check -> COALEXE free extent automatically
    2. BRSPACE –f check -> COALEXE free extent use locally managed table spaces.

To solve above problem with extent we must use locally managed table spaces.
    Segment Sizes           Next segment Size    Max.no.of Extent
    Less than 1Mb          Less than 64Mb               16
    1 to 64Mb                1Mb                                 63
    64Mb to 1Gb           8Mb                                 126
    Greater than 1Gb     64Mb                                Unlimited

Advantage of LMTS (locally managed table spaces) is “ORA1533” error eill no longer occur. The only disadvantage of LMTS is, always it checks for used and free space.

Increase the Table space:
1. Log on to ORA<SID> and enter into BR tools.
2. Space management (option 2)
3. Extent table space (option 1)
4. Enter ‘c’ to continue
5. Enter ‘c’ to continue
It will give “Table space extension main menu”
Note: First use option 2 to show the table spaces and percentage full and make a note of a table space which is 80% and above fill and then add a data file as per the specification using the option 1 that is “extent table space”.
6. Extend table space (option 1)
7. This will list all table spaces and percentage used
Example Table: “PSAPR3700”
8. Select the table space that is ‘pos’ position
9. Enter 2 to select above example table
Note: options for extension of table space
a. Last added file name
b. Last added file size in MB
c. New file to be added
d. Raw disk/link target
e. Size of the new file in MB
f. File auto extend mode = YES
g. Max file size in MB = [10000]
h. File increment size in MB = [20]
i. SQL Command = [alter table space name]

Note: the last added data file name and new file to be added will show the exact location where the data file is residing that is Oracle/<sid>/sapdata 1 to n/

10. Enter ‘c’ to continue
11. Enter option 5 to change the size of new file in MB
12. Press ‘c’ to continue
13. Select ‘NO’ to continue with the current data file addition.
14. Select ‘YES’ to add a new data file to the current table or add new data file to a new table.

Note: this action will update the time stamp in co-file that is, it created a copy of co-file in the location /oracle/<SID>/SAPREORA|[CNTRL<SID>.old]
Once co-file is created, extending of table space is done, one successfully completed it switches to next online redo log file for database instance and finally creates a copy of co-file with new time stamp that is CMTRL<SID>.news

Top 10 Oracle errors:
1. ORA1631 and ORA1632 -> Max extent full
2. ORA1653 -> Table space full
3. ORA1654 -> Index full
4. ORA1113 -> When backup is aborted
5. ORA1144 -> When back is shutdown immediately
6. ORA1578 -> Data block corrupted
7. ORA0255 -> Database struck
8. ORA1555 -> Buffer mode is OFF
9. ORA272 and ORA255 -> Archive struck
10. ORA600 -> Hardware Failure

Note: option 4 and 5 are also called as missing end backup.

Changing Oracle Parameters

Q) Create server parameter file from init<sid>.ora
A) -> Login to oracle user (ora<sid>)