Sunday, 31 July 2016

Control file corrupted

Scenarios in which control file to be backed up  in Oracle
It is very important to backup control file whenever there is a change in the physical structure of the database. Some of the examples of structural changes are
  • Adding or dropping or renaming of datafiles
  • Adding or dropping a tablespace
  • Altering the read/write state of the tablespace
  • Adding or dropping redolog files or groups

Control file backup procedure  in Oracle
ALTER DATABASE BACKUP CONTROLFILE statement is used to backup control files in Oracle.
There are 2 options to backup control files.
  1. Backup the control file to a binary file (duplicate of existing control file) using the below statement:
ALTER DATABASE BACKPUP CONTROLFILE  TO  ‘/oracle/backup/control.bkp’;

  1. Produce SQL statements that can later be used to re-create control file.

ALTER DATABASE BACKUP CONTROLFILE  TO TRACE;
Above command writes  a SQL script to the database trace file where it can be captured and edited to regenerate the control file.

Recovering an instance when one of the  control files got corrupted
Oracle recommends to have atleast 2 control files each stored on a different disk.
(Please note, for SAP applications , 3 control files are generally maintained in an oracle database at different locations)
If a control file got corrupted due to a disk failure the respective instance should be shut down. Once the disk drive is repaired, the corrupted control file can be restored using the copy of control file from other disk and the instance can be brought up. In these cases, media recovery is not required.

How multiple control files are managed in Oracle?

  • All the control files that are listed in the CONTROL_FILES parameter are updated by the database.
  • The database reads only the first file listed in the CONTROL_FILES parameter during database operation
  • If any of the control files got corrupted or become unavailable during database operation, the instance won’t function and it should be shutdown and recovered as mentioned earlier


Query to find current location of control files :

SQL > select name from v$controlfile;

After executing the above query, System outputs  the location of all control files in the system
Usually there will be 3 control files in oracle database( for SAP applications).

Can we rename or move control files location? If so, what is that parameter?

Yes. If required, control files location can changed with the control_files instance parameter.
Please execute the below query to display the current value of control_files parameter
SQL > show parameter control_files
The above command outputs the current value. This value can be changed with the ALTER SYSTEM command. In other words the current location of the control files can be changed using ALTER SYSTEM command.

Steps to be followed to rename or move control files location

1)      Alter the control_files parameter using the ALTER SYSTEM command
Syntax:
SQL> ALTER SYSTEM SET control_files=’<New controlfile1 path or newcontrol file name>’, <New controlfile2 path or newcontrol file name>’, <New controlfile13 path or newcontrol name>’ SCOPE=SPFILE;
 Eg: In the below command set the paths or names  for various control files  as per your requirement
SQL> ALTER SYSTEM SET control_files=’D:\ORACLE\..\..\..\Newname1.CTL’, 
’D:\ORACLE\..\..\..\Newname2.CTL’,  ’D:\ORACLE\..\..\..\Newname3.CTL’
SCOPE=SPFILE;

2)     Shutdown the database
SQL > shutdown immediate;

3)     Rename the physical file in the OS
SQL >  HOST  MOVE <old controlfile with path> <new controlfile with path >
 Eg:
SQL > HOST  MOVE  D:\ORACLE\ORADATA\CONTROL01.CTL D:\ORACLE\ORADATA\NEW_CONTROL01.CTL
4)     Start the database
SQL > startup

Control file in sap oracle database

What is control file in Oracle and what are its functions ?
Control file is a small binary file which is necessary for the database to start and operate successfully.

What are the functions of control file ?
Each control file is associated with only one database.  Oracle continuosly updates control file during database use so this file must be available for writing whenever the database is in the open state.  Control file will be created at the same time of database creation.
Whenever a datafile or a redolog file is added to , dropped or renamed in the database, the control file is updated to reflect the physical structural change.
These changes are recorded in control file which facilitates :

  • Oracle to identify the files that are required or available in case database recovery is needed
  • Oracle to identify the datafiles and redolog files to open during database startup.

Can database function properly without control file?
No. Database cannot function properly if  the control file is not available.

Can we edit control file ? Who can modify control file ?
Please note control file cannot be edited by database administrator or by any user. Control file information can only be modified by Oracle.

Contents of control files :

  • Control file contains information such as
  • Database name
  • Timestamp of database creation
  • Tablespace information
  • The log history
  • Archive log information
  • Current log sequence number
  • Checkpoint information
  • Names and locations of associated datafiles and redolog files
  • Datafile copy information
  • Datafile offline ranges
  • Backup set and backup related information
  • Backup datafile and redolog information.

What is PFILE and SPFILE

Use of PFILE or SPFILE

When an Oracle instance is started, its characteristics are set by reading some initialization parameters. These parameters are specified either through PFILE or SPFILE.  (0r)

When an oracle instance is being started, it reads either PFILE or SPFILE to set its characteristics.


What is PFILE ?

A PFILE is a static, client side text file. This file normally resides on the server. However in case you would like to start oracle from a remote machine, you will need a local copy of the PFILE in that machine.

This file can be edited with a standard text editor like notepad or vi editor. This file will be commonly referred as INIT.ORA file.

What is SPFILE ?

SPFILE (Server parameter file) is a persistent server side binary file. SPFILE should only be modified through “ALTER SYSTEM SET” command . Editing an SPFILE directly will corrupt the file and the start up of the database or instance will not be possible.

As SPFILe is a server side binary file,  local copy of the PFILE is not required to start oracle from a remote machine.


Advantages of SPFILE compared to PFILE

  • A SPFILE doesnot need a local copy of  the pfile to start  oracle from a remote machine.  Thus eliminates configuration problems.

  • SPFILE  is a binary file and modications to that can only be done through ALTER SYSTEM SET command.

  • As SPFILE is maintained by the server, human errors can be eliminated as the parameters are checked before modification in SPFILE

  • It is easy  to locate SPFILE as it is stored in a central location

  • Changes to the parameters in SPFILE will take immediate effect without restart of the instance i.e Dynamic change of parameters is possible

  • SPFILE can be backed up by RMAN


Difference between PFILE and SPFILE in Oracle :


SNO
PFILE
SPFILE
1
Static, client side text file
Persistent server side binary file
2
Local copy of pfile required to start database from a remote machine
local copy is not required
3
Can be edited through notepad or vi editor
Editing directly will corrupt the file. It should be modified only through ALTER SYSTEM SET command
4
Is available in earlier versions of Oracle 9i
Available from Oracle 9i and above
5
Prone to human errors while modification
Eliminates human errors as parameters are checked before modification
6
Cannot be backed up by RMAN
Can be backed up by RMAN
7
Parameters in pfile cannot be changed dynamically and system needs to be bounced for the new changes to take effect
Dynamic change of some parameters is possible. Need not restart server for the changes to take effect


Query to findout, if database is running with PFILE or SPFILE

Goto SQL prompt and execute the following command to figure out if database is started with a PFILE or SPFILE.


SQL > SELECT DECODE(value, NULL, ‘PFILE’, ‘SPFILE’) “Init File” FROM sys.v_$parameter WHERE name = ‘spfile’;

Tablespace sizes in SAP

How to check the database size ?

Goto transaction DB02  and navigate to Space -> Space overview to view

  • Database name
  • Database system
  • Size
  • Free size
  • Used percentage
  • Number of database users
  • Number of tablespaces
  • Number of tables and size
  • Number of indexes and size

In DB02, navigate to Space -> Space overview to find all the above mentioned details
























      How to check  the users that are present in database at SAP level ?
              In DB02, navigate to Space -> Users -> Overview as below




















              How to check various tablespaces in SAP and their sizes ?

              In DB02, navigate to Space -> Table spaces -> Overview to view tablespaces details as below.

                    Database parameters at SAP level

                    How to view database active parameters at SAP level ?


                    Goto transaction DB02 and navigate to Additional Functions -> Database parameters as shown below  :


                    Click on Active parameters tab to view active database parameters






                    How to view SPFILE parameters at SAP level ?


                    Click on SPFILE tab to view SPFILE parameters






                    How to view database Parameters history at SAP level ?


                    Click on parameters history tab to view the parameters that are changed in the recent past.






                    In the above screen click on Show parameters history. It displays window as below


                    Provide the date from which you would like to view the database parameter changes.


                    And then click on tick mark to view the database parameter changes from the selected date.

                    How to export the database parameters to an excel sheet ?


                    Click on export button (highlighted below) and select table as the output as shown in below screen and the parameters can be exported to an excel or word as per the selection by you.







                    Saturday, 30 July 2016

                    Work process





                    A. All the process which handles by the use task based on the type of the user request.
                    B. Types of work process
                    1) Dialog work process 
                    2) Background work process 
                    3) Update work process 
                    4) Message server 
                    5) Enqueue server 
                    6) Gateway 
                    7) Spool 
                    C. What are the different types of work process in R/3?

                    • Dialog (D), 
                    • Update (V), 
                    • Enqueue (E), 
                    • Background (B) and 
                    • Spool(S)


                    Dialog work process (D):

                    A. Dialog work process is used to handle by the user request only 
                    B. Dialog work process parameter name is – rdisp\wp_no_dia=2 
                    C. Dialog work processes are minimum 2 One is user request running another one is distributing the work process 
                    D. Each request occupies 75mb to 150mb 
                    E. Dial work process is multiplexive Multiplexive means at a time one (or) more use request is running 
                    F. The user request handle by the dialog work process maximum up to 1800 seconds 
                    G. If the user request 600 seconds default in that time data is not completed then user request session is terminated 
                    H. No of Dialog work process >= No of non Dialog work process 
                    I. Dialog work process time out parameter is rdisp\max_wp_run_timeout=600 to 1800 
                    J. One dialog work process maximum up to 32 users are we can create 
                    K. One application server maximum up to 100 work process are we can create 
                    L. Work process statuses are 
                    1) Waiting 
                    2) Running 
                    3) Error 
                    4) Stopped
                     5) Private mode: Dedicated to the particular user 
                    6) Sleep: Work process is running but not responding 
                            Why: Waiting for resources 
                    M. In sap level work process overview – SM50 
                    N. In sap level global work process overview – SM66
                    O. In O.S level work process overview – dpmon Dpmon: when the user could not login to the sap            system
                     P. In sap level display a list of application server – SM51 
                    Q. In sm51 transaction to identify the Relesenote, the R/3 kernel, D/B kernel, O.S. Kernel and support packages information 
                    R. In sap level version statuses are – system ---> status 
                    S. In O.S level version we can see – disp+work How to user request flow? User request ---> dispatcher ---> work process ---> dialog work process ---> update the tables .




                    Background work process (B): 
                    A. The background work process handle by the long running jobs. The background work process that       process can be executed without the user interaction. Background jobs are programs, printing             specification and interactively can be run automatically by the background processing system 
                    B. Background work process parameter name is – rdisp\wp_no_btc=2 
                    C. During the installation minimum of 2 background work process One is defining and another one is      executing 
                    D. Scheduling the background job is – SM36 Job name should start either z or y. Maximum 32                 characters are allowed in the name.
                     E. Start conditions 1) Immediate 2) Date/time 3) After job 4) After event 5) At operation mode 
                    F.  Background job steps 
                         1) ABAP program: It is a standard program (or) customized program which      will execute by                variant. 

                              Variant: It is a program selection criterion to provide the inputs during the runtime (or)                                       execution of the program.  
                                              Variants are stored in the table is TVARV 
                                              We can create the variant from SE38

                          2) External command: It is used for pre defined input by system administrator. 
                                  External commands are defined in SM49 (or) SM69 
                                  External commands & external program are executed by SAPXG program 
                           3) External program: It is used for direct command input by system administrator 

                    G. Background job is every 60 seconds default time is – rdisp\btctime=60sec 
                         It stored in the default profile 
                    H. What is the background job scheduler every 60seconds? SAPMSSYS 
                    I. In O.S level background work process stored in global directory usr\sap\sys\global 
                    J. Simple job selection is SM37 
                    K. Job statuses are
                       1) Scheduled: Whenever job is defined in that time job status scheduled. 
                       2) Released: Whenever we specify date & time to schedule job.
                       3) Ready: When the time is elapsed. 
                       4) Active: Job status is running. 
                       5) Finished: All the job steps are complete successfully. 
                       6) Cancelled: There was an error and job has been terminated, as mentioned in the job log.
                     L. Background job table is TBTC* TBTCT table is used for job time table TBTCS table is used for         job scheduler.
                     M. Background job status from release to ready program is – SAPMSSYZF 
                     N. What is the difference between SA38 and SE38? SA38 is only we can execute a program and             SE38 is we can create; edit a program, changing attributes and documents for the program 
                    O. If we want suspend all the background jobs are using BTCTRANS1 report 
                    P. If we want resume all the background jobs are using BTCTRANS2 report To execute these reports     and programs from SA38 (or) SE38 
                    Q. If you have a long running job, how to you analysis? (Or) How to analyze long running job? 
                         Using the transaction code is – SE30 
                    R. Standard background jobs (or) Housekeeping jobs are sap_reorg* 
                       1) Sap_reorg_abapdumps: Program: RSSNAPDL 
                       2) Sap_reorg_batchinput: This job is used for delete batch input files Program: RSBDCREO 
                       3) Sap_reorg_jobs: This job is used for delete old background jobs Program: RSBTCDEL 
                       4) Sap_reorg_statististics: This job is used for run the statistics of sap tables Program: RSBPSTDE      5) Sap_reorg_spool: This job is used for delete the old print request. Program: RSPO0041 
                       6) Sap_reorg_updaterecords: this job is used for delete the old update records Program: RSM13002 S. Background job issues (or) Why background job is cancelled? 
                       1) File system is not accessible. 
                       2) User id & password may be expired. 
                       3) Dependent job is may be failed.
                       4) RFC connection is failed. 
                       5) Incorrect in puts. 
                       6) May be files corrupted at O.S level. 
                       7) Space issues in database level. 
                       8) Memory issues. 
                       9) Ora-arch director is may be full. 
                      10)Invalid file formats. Update 



                    Update work process (V):
                    A. It is used to update the database by reading from the temporary tables. It can’t communicate with        user directly 
                    B. There are 2 types of update work process. They are v1 and v2 V1 update is used for critical jobs          and V2 update is used for non critical jobs 
                    C. We need at least one update work process per sap system and we have more than per dispatcher 
                    D. The update profile parameter name is rdisp\wp_no_vb, rdisp\wp_no_vb2 
                    E. Update monitoring is – SM13 Here we can check critical and non critical updates. We can specify        date and time
                    F. Update program administration – SM14
                       This transaction code is used for active to deactivate & deactivates to activate update request and         you configure the update servers, update groups and display & monitoring update parameters. 
                    G. Update temporary table is VB* 
                       1) Vbmod – update function module 
                       2) Vbdata – update data 
                       3) Vberror – update error information 
                       4) Vbhdr – update header 
                       5) Vbwrk – work list for mass processing at a time have update 
                    H. Update status are 
                          1) Init – the status will be initial. 
                          2) Run – update is started in main database. 
                          3) Error – update is thrown into error. 
                          4) Auto – update is thrown into error due to problem in database space. 
                    I. Update tables are stored in TBLOD 
                    J. SAPMV45A: which update work process is going on? 
                    K. Update types are 3: they are 
                         1. Local update: Dialog work processes update the directly in Database that is called local update.      2. Synchronous: Dialog work processes update goes to the temporary table that is called                           synchronous. 
                        3. Asynchronous: temporary table goes to database that is called asynchronous 
                    L. Update statistics are 4: they are Read, Write, Execute and Delete 
                    M. Update parameters 
                       1) rdisp\vb_stop_acive: Set to “0” so that update can be deactivated. If the value is set to be “1”              update can be activated.
                       2) rdisp\vbdelete: This parameter is used to delete the old update requests based on the no. of days.       It will delete default 50 days. 
                       3) rdisp\vbmail: It is used to send an email. It update thrown an error which can be viewed in                    “SBWP” (“sap business workplace”) based on your user (set to “0” or “1”). 
                       4) rdisp\vbname : Name of the server running where update are processed 
                       5) rdisp\vbreorg: It is used to delete the incomplete update request.
                                 1=delete, 0=no, we can also schedule a background job “RSM13002” but it will delete the                  update request which are in completed it will be deleted after restarting. 
                    6) rdisp\vb_delete_after_execution: It is used to delete the update request soon after the execute of the update. Set it to”1” to delete the record (or) “2” to the record will not be deleted. If it is set to “1” the background job “RSM13002” is not required. If not schedule periodically daily during off peak hours.

                    Enqueue server (E) :
                    A. Enqueue server handles by the locking and unlocking mechanism 
                    B. The Enqueue server parameter name is – rdisp\wp_no_enq 
                    C. Enqueue server table size is minimum of 4MB 
                    D. We can increase the maximum of size is 100MB 
                    E. Enqueue table size is enq\table_size=4mb to 100mb
                    F. Enqueue server log files will be stored in O.S level Usr\sap\instance\log 
                    G. The Enqueue server work process administrators a lock table in the shared memory area 
                    H. Enqueue server 4 types they are 
                         1) Shared locks: one user can read the tables and another one user can write the tables 
                         2) Exclusive locks: only a particular user can read it, nobody else can access it 
                         3) Cumulative locks: The same user with different transaction accessing the same table then the               lock will be shared with user transactions 
                         4) Optimistic locks: One user lock the table and another user doesn’t have read & access the table              permission 
                    I. Select lock entries – SM12 You can check (or) released the locked entries using transaction is sm12. If there is a sudden power failure. Some of the users update entry might still be locked. If possible asked the user to logoff first, before deleting the lock entries 
                    J. Transaction codes: locks and unlocks – SM01 
                    K. Enqueue server lock tables are stored in TLOCK.

                    Message server (M):

                    A. Message servers handle by the load balancing. It distributes the dispatcher where the load balance       is less. 
                    B. Message server log files are stored in O.S level DEV_MS Path is – usr\sap\\work\dev_ms 
                    C. In O.S level message server monitoring is – msmon ->cmd 
                    D. Message server port number is – 36 
                    E. Internal port number is – 39 
                    F. NOTE: sap server has only one message server and enqueue server. 
                    G. NOTE: application server or dialog instance are same. And central instance is different.



                    Gate way (G):
                    A. It is used to communication between the sap system to non sap system 
                    B. There will be only 1 gate way for each instance. 
                    C. In sap level gateway monitoring is - SMGW 
                    D. In O.S level gate way monitoring is – DEV_RD Path is – usr\sap\\work\dev_rd 
                    E. We can check also DEV_DISP, i.s dispatcher for log files
                    F. usr\sap\\instance\work ----> work directory stores log of the work process information in the format of DEV_W* 


                    Spool (S):

                    A. It is used to print the documents to a printer
                    B. There should be at least 1 spool process in the entire system 
                    C. The spool profile parameter name is – rdisp\wp_no_spo 
                    D. Dialog work process (or) background work process are creates a spool request. i.e. to print the documents 
                    E. In O.S level spool request are stored in global directory usr\sap\\sys\global 
                    F. The storage location specific parameter name is – rdisp\store_location 
                    G. This parameter has 2 values they are global_G and database_DB. 
                    H. G means it stored in global directory DB means it stored in database tables are “TST01” and                “TST03” 
                    I. The spool request is also referred as TEMSE Temse is used for temporary sequential objects 
                    J. Access method: It is connection to spool server to O.S spool. Access methods are 3 types 
                        1) Local access method: the spool process and the spool host (printer spool) reside on the same                system. Access method type L is used for UNIX O.S and C is used for windows O.S 
                        2) Remote access method: the spool process and the spool host reside on two different systems.                  Access method type U is used for UNIX O.S and S is used for windows O.S 
                         3) Front end method: access method p is the printers are connected to end users desktop do not                 configured to many front and printers F and G 
                    K. We can see all the clients and users spool request T_Code is – SP01
                    L. We can see specific client and user spool request T_Code is – SP02 
                    M. Spool administration is - SPAD 
                    N. Default printers are – SWIN, SAPWIN