Shrinking Data / Log Files on MSSQL

    

Objective - As a BASIS consultant, we do come at a situation where we need to shrink datafile / logfile of an MSSQL database and move to a different location due to space issue in the current location.

Steps to achieve this are listed below:

• Stop SAP application.
• Take a full backup of the database.
• Go to properties of the database and change the recovery model to SIMPLE under the tab Options:

• Create a checkpoint using below query:

• Take another backup of the system, now with the recovery model as SIMPLE

• After completion of the 2nd backup, revert the recovery model to the original and take another backup.

• Now, run the below query to shrink the datafile/logfile:
• DBCC SHRINKFILE (<database_file_name> , 1)

• After the successful execution of the above query, the datafile/logfile has been shrunk which can verified by checking the size of the file on the disk.

Movement of the datafile to a different location:

• Run the below query to check the current location of the data and log files:

• SELECT name, physical_name AS NewLocation, state_desc AS OnlineStatus

  FROM sys.master_files 

  WHERE database_id = DB_ID(N'<DB_SID>') 

  GO

• We need to move log file from F to L drive. Run the below query to update the location of the log file:

• ALTER DATABASE <db_sid>   

      MODIFY FILE ( NAME = <db_file_name>,  

                    FILENAME = '<new_location');

• Stop the database now using the below query:

• ALTER DATABASE ASP SET OFFLINE; 

  GO

• The database is now down. Move the log file from the old location to the new location e.g. I moved the log from F drive to L drive:

• After moving the file successfully, try starting the database using the below query:

• ALTER DATABASE ASP SET ONLINE; 

  GO

• Run below query again to check if all the datafiles and logfiles are online:

• SELECT name, physical_name AS NewLocation, state_desc AS OnlineStatus

  FROM sys.master_files 

  WHERE database_id = DB_ID(N'<DB_SID>') 

  GO

• Take a final backup of the system and bring up SAP.

DR Setup on Oracle using Data Guard Configuration

1.       Take a backup of the primary DB which will be restored on the standby server.

2.       Create a standby control file before you make any further changes to DB (like adding datafiles, renaming datafiles, etc.) for starting the standby db for recovery.

ALTER DATABASE CREATE STANDBY CONTROLFILE AS '/oracle/PER/controlfile.dbf';

3.       Restore the standby DB server with the backup taken instep 1.

brrestore -b <backupfile_name>

The parameter changes in the section below, can be performed individually on primary and standby db, or only on primary db and then you can create a pfile and use it in the standby db for generating spfile. In the second approach, you may need to do a few additional changes to the standby system. I had done the changes individually on both systems.

4.       Check db_name and db_unique_name on both primary and standby databases. The db_name of standby should be the same as that of primary, but db_unique_name should be different.

On primary database:

On secondary database:

5.       The db_unique_name (of both primary and standby) should be used in dg_config setting of the parameter log_archive_config of both primary and standby databases.

    ALTER SYSTEM SET LOG_ARCHIVE_CONFIG='DG_CONFIG=(PER,PER_NRDR)'; 

6.       Create suitable archive destinations. Use db_unique_name in the service to properly reference the standby db.

In primary database:

ALTER SYSTEM SET LOG_ARCHIVE_DEST_2='service="PER_NRDR", LGWR SYNC VALID_FOR=(ALL_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=PER_NRDR DELAY=0'; 

        In secondary database:

        ALTER SYSTEM SET LOG_ARCHIVE_DEST_2='service="PER", LGWR SYNC VALID_FOR=(ALL_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=PER DELAY=0';

7.       Check the below parameters and set the values accordingly if not set by default in both primary and secondary databases.

a.       LOG_ARCHIVE_FORMAT='%t_%s_%r.dbf'

b.       LOG_ARCHIVE_MAX_PROCESSES=30

c.       REMOTE_LOGIN_PASSWORDFILE=EXCLUSIVE

8.       It is recommended to ensure that the primary DB is ready to switch the role of standby. Need to set the below parameters for this.

On primary database:

a.       ALTER SYSTEM SET FAL_SERVER=PER_NDR;

b.       ALTER SYSTEM SET STANDBY_FILE_MANAGEMENT=AUTO;

c.       ALTER SYSTEM SET FAL_CLIENT=PER;

        On standby database:

            a.       ALTER SYSTEM SET FAL_SERVER=PER;

            b.       ALTER SYSTEM SET STANDBY_FILE_MANAGEMENT=AUTO;

            c.       ALTER SYSTEM SET FAL_CLIENT=PER_NRDR;

9.       After all the changes are done to the parameters, generate fresh pfile from spfile.

CREATE PFILE='\oracle\PER\11204\database\initPER.ora' FROM SPFILE;

10.   In addition to the existing entries, make the entries for standby and standby data guard services in tnsnames.ora file on primary DB ($ORACLE_HOME\NETWORK\admin) in primary database. A sample of the entry would look like something as below:

        PER_NRDR.WORLD=

                        (DESCRIPTION =

                                        (ADDRESS_LIST =

                                        (ADDRESS =

                                        (COMMUNITY = SAP.WORLD)

                                        (PROTOCOL = TCP)

                                        (HOST = <standby_db_host>)

                                        (PORT = 1521)

                                        )

                                        )

                                        (CONNECT_DATA =

                                        (SID = PER)

                                        (GLOBAL_NAME = PER)

                                        (SERVICE_NAME = PER)

                                        )

                                        )

       

        PER_NRDR_DGMGRL.WORLD=

                        (DESCRIPTION =

                                        (ADDRESS_LIST =

                                        (ADDRESS =

                                        (COMMUNITY = SAP.WORLD)

                                        (PROTOCOL = TCP)

                                        (HOST = <standby_db_host>)

                                        (PORT = 1521)

                                        )

                                        )

                        (CONNECT_DATA =

                                        (SID = PER)

                                        (GLOBAL_NAME = PER)

                                        (SERVICE_NAME = PER_DGMGRL)

                                        )

                                        )

11.   Ensure that telnet from primary to standby DB and vice-versa using the ports defined in the file tnsnames.ora is successful.

12. Copy the file pwd<SID>.ora from primary DB to standby DB (Location: $ORACLE_HOME/database).

13.   If all the configurations above have been done properly, at this point, you should be able to see the oraarch directory on standby DB to be in synch with the primary DB.

14.   Copy the standby control file created in step 2 in all the control file locations on the standby db. Keep a copy of the existing control file as a backup. You can find the control file location as below:

15.   Now, you should run the recovery of the DB using backup control file.

16.   Mount the standby database.

            ALTER DATABASE MOUNT STANDBY DATABASE;

17.  Start applying the logs.

            RECOVER STANDBY DATABASE USING BACKUP CONTROLFILE;

18.   After all the logs have been applied in the foreground, enable the log application in the background.

ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION;

19.   This is the final step in DR setup. Now you can check if the MRP0 process in standby DB is waiting for logs from primary DB:

        select process, thread#, sequence#, status from v$managed_standby where process='MRP0';

Finding list of all ports used in an Active SAP system

We often come to a situation where there is an overlapping of ports for an application. A single port would be used by an SAP service and any non-SAP application/service on the same server simultaneously. This will cause the SAP service to fail/stop. For avoiding this, we should know the list of all the ports which are being used by a running SAP application, so another service is not configured on any of these ports.

You can find the list of ports using the below steps:

1. Login to the system using <sid>adm and navigate to the profile directory.
2. Find all the instance number of all the instances present in the directory.
3. For every instance number, run the below command.

Command: sapcontrol -nr <instance_nr> -function GetAccessPointList

e.g. sapcontrol -nr 00 -function GetAccessPointList

Output: