Install Oracle patch set 4 and bundle 10 on RAC environment

Install Oracle 10g R2 Patch Set 4 (10.2.0.5) and Patch bundle 10.

Oracle database throw ORA-4031 errors and restarting the database resolve the issue. I analyzed oracle database alert log. I found the error caused by a bug in oracle database so I installed the latest service packs Patch Set 4 (10.2.0.5) and Patch bundle 10.

Bug 8211733  Shared pool latch contention when shared pool is shrinking

Shared Pool Affected

The shared pool may be affected. Issues which affect the shared pool may lead to wider problems for other users such as ORA-4031 errors, very slow performance, increased CPU etc

Our environment is Oracle 1og R2 patch set 3(10.2.0.4) RAC with two nodes on windows 2003 64 bit, I downloaded patch set 4 and bundle 10 and I read the install instructions and I summarized the steps to install patch set 4/bundle 10 as below:

 Preinstall

  • Delete the log files on C:\oracle\product\10.2.0\asm\NETWORK\log
  • Delete the log files on C:\oracle\product\10.2.0\db\NETWORK\log
  • Perform Full backup of oracle home folder and the database
  • Create Snapshot on the SAN using iscsi software if we can

Install Patch set 4 on Oracle CRS(cluster ready services)

  • Perform clean shutdown to the database in Node 1 and Node 2
  • Shutdown ASM services on Node 1 and Node 2
  • Shutdown CRS services on Node 1 and Node 2
  • Navigate to patch set 3 (8202632) on Node 1
  • Launch oracle universal  installer
  • On the destination page select Oracle CRS home path
  • Click Next….…………
  • After the installer complete the task and you will see info message
  •  Run the batch file located at: C:\oracle\product\10.2.0\crs\install\patch102.bat on node 1 and Node 2 and this batch file will automatically start the CRS services on the patched node upon completion

Install Patch set 4 on Oracle ASM(automatic storage management)

  • Perform clean shutdown to the database in Node 1 and Node 2
  • Shutdown ASM services on Node 1 and Node 2
  • Shutdown CRS services on Node 1 and Node 2
  • Navigate to patch set 3 (8202632) on Node 1
  • Launch oracle universal installer
  • On the destination page select oracle ASM home path
  • Click Next……………..

Install Patch set 4 on Oracle database

  • Perform clean shutdown to the database in Node 1 and Node 2
  • Shutdown ASM services on Node 1 and Node 2
  • Shutdown CRS services on Node 1 and Node 2
  • Navigate to patch set 3 (8202632) on Node 1
  • Launch oracle universal installer
  • On the destination page select oracle Database home path
  • Click Next……………..
  • Set the CLUSTER_DATABASE initialization parameter to FALSE
SQL> STARTUP NOMOUNT

SQL> ALTER SYSTEM SET CLUSTER_DATABASE=FALSE SCOPE=spfile;

  • Shut down the database:

SQL> SHUTDOWN Immediate

  • Startup the database in upgrade mode and run the upgrade script
SQL> STARTUP UPGRADESQL> @C:\oracle\product\10.2.0\db\RDBMS\ADMIN\utlu102i.sqlSQL> SPOOL patch.logSQL> @C:\oracle\product\10.2.0\db\RDBMS\ADMIN\catupgrd.sqlSQL> SPOOL OFF
  • Restart  the database
  • recompile all invalid PL/SQL packages
SQL> SELECT OBJECT_NAME FROM DBA_OBJECTS WHERE STATUS = 'INVALID'SQL> @C:\oracle\product\10.2.0\db\RDBMS\ADMIN\utlrp.sql
  • Run the following command to check the status of all the components after the upgrade

SQL> SELECT COMP_NAME, VERSION, STATUS FROM SYS.DBA_REGISTRY;

  • Set the CLUSTER_DATABASE initialization parameter to TRUE
SQL> STARTUP NOMOUNT

SQL> ALTER SYSTEM SET CLUSTER_DATABASE=True SCOPE=spfile;

SQL> SHUTDOWN Immediate

  • Start  the database
  • configure and secure Enterprise Manager

Ensure the database and Listener are operational and run emca -upgrade db –cluster

Install the latest OPatch patch 6880880

  • Backup OPatch folder on Oracle home for CRS, ASM and the database
  • copy Opatch folder from patch 6880800 to Oracle home for CRS, ASM and the database
  • Stop CRS, ASM and database services in Node 1 and Node 2

Install bundle 10(12429524) on Oracle CRS(cluster ready services)

  • set Oracle_Home to C:\oracle\product\10.2.0\crs
  • opatch  apply

Install Bundle 10(12429524) on Oracle ASM ( automatic storage management)

  • set Oracle_Home to C:\oracle\product\10.2.0\asm
  • opatch  apply

Install bundle 10(12429524) on Oracle database

  • set Oracle_Home to C:\oracle\product\10.2.0\db
  • opatch  apply
  • restart Node1 and Node 2
  • run the upgrade script
SQL> @C:\oracle\product\10.2.0\db\bundle\Patch10\catcpu.sql
  • run compilation script
SQL> @C:\oracle\product\10.2.0\db\RDBMS\ADMIN\utlprp.sql 0
  • check for invalid objects
SQL> SELECT OBJECT_NAME FROM DBA_OBJECTS WHERE STATUS = 'INVALID'
  • Set the CLUSTER_DATABASE initialization parameter to FALSE
SQL> STARTUP NOMOUNT

SQL> ALTER SYSTEM SET CLUSTER_DATABASE=FALSE SCOPE=spfile;

SQL> Shutdown Immediate
  • Recompiling Views in the Database
SQL> STARTUP UPGRADESQL> @C:\oracle\product\10.2.0\db\bundle\view_recompile\view_recompile_jan2008cpu.sql
  • Set the CLUSTER_DATABASE initialization parameter to TRUE
SQL> STARTUP NOMOUNTSQL> ALTER SYSTEM SET CLUSTER_DATABASE=True SCOPE=spfileSQL> Shutdown
  • Start the Database
  • Verify that the view recompilation has been performed for the database

SELECT * FROM registry$history where ID = ‘12429524’;

  • If any invalid objects were reported

SQL>@C:\oracle\product\10.2.0\db\RDBMS\ADMIN\utlrp.sql

  • manually recompile any invalid objects

SQL>alter <object_type> schemaname.<object_name> compile;

Advertisements
Posted in Oracle Database | Tagged , , , | Leave a comment

Moving SQL server 2005 cluster to new hardware

Moving cluster SQL server 2005 to new hardware:

The hardware of the cluster SQL server 2005 two servers out of warranty and I developed a plan to move to the new hardware with minimum downtime for the end user.

The old cluster were running on windows 2003 64bit, I decided to move it to the new hardware on windows 2008 R2 64bit instead of window 2003 to advantage windows new features and install SQL 2005 so we can utilize the old license. It would have been good idea to upgrade to SQL 2008 R2 but it would cost more money to buy license for SQL 2008.

The plan to move the SQL server to the new hardware is build Windows 2008 R2 cluster and install SQL 2005 as cluster in the new hardware (two nodes) then install the latest SQL service pack (Sp4) then use SQL script to move the databases to the new SQL cluster. After the database moved successfully rename the new cluster name and with the old SQL cluster name (virtual ip name) and all the applications will connect to the new hardware without any problem.

Steps to move SQL server cluster to the new hardware: 

  1. Install windows 2008 enterprise edition on Node 1& 2
  2. Install/configure Windows 2008 cluster
  3. Add web server role (IIS)
  4. Install SQL 2005 on top of the windows 2008 cluster 
  5. Install SQL 2005 SP4
  6. Move user accounts from the old cluster company-db2 to the new  database cluster
  7. Perform full backup of old cluster company-db databases
  8. Restore databases in the new cluster with no recovery so additional log file or differential backup can be restored using SQL script:

--*********************************************************************************
-- Written by : Tarek Mohamed Date: July 14, 2011
-- Updated by: Date:
-- Updated by: Date:
-- Updated by: Date:
-- Version:1.0
--*********************************************************************************
--
-- This script will restore databases in folder which each database has folder and the folder name will be
-- the database name so you can use this script to restore one database or more
-- The functnality of the script:
-- 1-Locate the database folder in the source folder
-- 2-Sort the files in in ecach database folder
-- 3-Locate the latest full backup and restore it to the new location

-- 4-Locate the Transaction log files and restore them
-- Please note this script desgined to work with backups form SQL maintenance plans
--
--*********************************************************************************
-- User inputs
-- 1-@SourceDirBackupFiles, this parameter with the path of the backup files
-- 2-@DetDirDbfiles, this paramter will have the path will be used to restore the data and log file
-- for each database
--*********************************************************************************
SET NOCOUNT ON
Declare @SourceDirBackupFiles varchar(500)
Declare @DestDirDbFiles varchar(500)
-- set this paramater with the path of the backups folder
Set @SourceDirBackupFiles='E:\DBsbackup'
-- set this paramater with the destnation of the data and log files
Set @DestDirDbFiles='E:\Microsoft SQL Server\MSSQL.1\MSSQL\Data'
Declare @SourceDirBackupfolder varchar(500)
Declare @dbname Varchar(200)
declare @NoOfRestorefiles int
Declare @dirfile varchar(900)
DECLARE @fname varchar(200)
DECLARE @LogicalName nvarchar(128)
DECLARE @PhysicalName nvarchar(260)
Declare @BaseRestoredone int
DECLARE @sql nvarchar(2000)
DECLARE @type char(1)
--*********************************************************************************
-- Initliazation
--*********************************************************************************
-- create temp table to hold all the the folders has the database files to restore
CREATE TABLE ##folders(fname varchar(200),depth int, file_ int)
-- create temp table to hold all the backup files for each database
CREATE TABLE #files(fname varchar(200),depth int, file_ int)
-- create temp table will save backup file info
CREATE TABLE #dbfiles(
LogicalName nvarchar(500)
,PhysicalName nvarchar(500)
,Type char(1)
,FileGroupName nvarchar(500)
,Size numeric(20,0)
,MaxSize numeric(20,0),
filed sql_variant,
createlsn sql_variant,
droplsn sql_variant,
uniqueld sql_variant,
readonlylsn sql_variant,
readwritelsn sql_variant,
backupsizeinbytes sql_variant,
sourceblocksize sql_variant,
filedgroup sql_variant,
loggroupguid sql_variant,
diff sql_variant,
diff1 sql_variant,
isreadonly sql_variant,
ispresent sql_variant)
--*********************************************************************************
-- script body
--********************************************************************************* --get all the folders from the source backup folder and each folder will be the database name
INSERT ##folders
EXECUTE master.dbo.xp_dirtree @SourceDirBackupFiles, 1, 1
--Cursor to process each folder(database backup files)
DECLARE folders CURSOR FOR SELECT fname FROM ##folders where fname not in ('master', 'tempdb','model','msdb')
open folders
fetch next from folders into @dbname
WHILE @@FETCH_STATUS = 0
BEGIN
--get all the files from the Directory contains the SQL backup
set @SourceDirBackupfolder =@SourceDirBackupFiles+'\'+@dbname
-- get the backup file to restore
INSERT #files
EXECUTE master.dbo.xp_dirtree @SourceDirBackupfolder, 1, 1
-- Select the full backuo file
SELECT @fname=fname from #files where substring(fname,len(fname)-2,3)='bak'
print @fname
--Construct the beginning for the RESTORE DATABASE command
set @dirfile=@SourceDirBackupFiles+'\'+@dbname+'\'+@fname
print @dirfile
SET @sql = 'RESTORE DATABASE [' + @dbname + '] FROM DISK = ''' + @dirfile + ''' WITH MOVE '
TRUNCATE TABLE #dbfiles -- delete the records in this temp table
Insert #dbfiles
EXEC('RESTORE FILELISTONLY FROM DISK = ''' + @dirfile + '''')
-- Declare Cursor for the files for each database
DECLARE dbfiles CURSOR FOR
SELECT LogicalName, PhysicalName, Type FROM #dbfiles
OPEN dbfiles
FETCH NEXT FROM dbfiles INTO @LogicalName, @PhysicalName, @type
--For each database file that the database uses
WHILE @@FETCH_STATUS = 0
BEGIN
SET @sql = @sql + '''' + @LogicalName + ''' TO ''' + @DestDirDbFiles + '\' + @dbname
IF @type = 'D'
SET @sql = @sql + '.mdf'', MOVE '
ELSE IF @type = 'L'
SET @sql = @sql + '_log.ldf'''
FETCH NEXT FROM dbfiles INTO @LogicalName, @PhysicalName, @type
END
CLOSE dbfiles
DEALLOCATE dbfiles
set @sql=@sql+', NORECOVERY, REPLACE '
print @sql
exec (@sql)
truncate table #files
fetch next from folders into @dbname
End
--**************************************************
close folders
deallocate folders
drop table ##folder

  1. restore Analysis services cubes on the new cluster
  2. Force the databases in the old cluster company-db to be readonly using SQL script:

Declare @dbname Varchar(200)
DECLARE @sql nvarchar(2000)
DECLARE DBs CURSOR FOR SELECT name from sys.databases where name not in ('master', 'model','msdb','tempdb')
Open DBs
fetch next from Dbs into @dbname
WHILE @@FETCH_STATUS = 0
BEGIN
SET @sql = 'ALTER DATABASE [' + @dbname + '] SET READ_ONLY WITH ROLLBACK IMMEDIATE'
exec (@sql)
SET @sql = 'ALTER DATABASE [' + @dbname + '] SET READ_ONLY'
Exec(@sql)
fetch next from Dbs into @dbname
END
close DBs
deallocate DBs

  1. Perform differential backups
  2. Restore differential backups to databases in step 8 with recovery using SQL script:

--*********************************************************************************
-- Written by : Tarek Mohamed Date: July 14, 2011
-- Updated by: Date:
-- Updated by: Date:
-- Updated by: Date:
-- Version:1.0
--*********************************************************************************
--
-- This script will restore databases in folder which each database has folder and the folder name will be
-- the database name so you can use this script to restore one database or more
-- The functnality of the script:
-- 1-Locate the database folder in the source folder
-- 2-Sort the files in in ecach database folder
-- 3-Locate the latest full backup and restore it to the new location
-- 4-Locate the Transaction log files and restore them
-- Please note this script desgined to work with backups form SQL maintenance plans
--
--*********************************************************************************
-- User inputs
-- 1-@SourceDirBackupFiles, this parameter with the path of the backup files
-- 2-@DetDirDbfiles, this paramter will have the path will be used to restore the data and log file
-- for each database
--*********************************************************************************
SET NOCOUNT ON
Declare @SourceDirBackupFiles varchar(500)
Declare @DestDirDbFiles varchar(500)
-- set this paramater with the path of the backups folder
Set @SourceDirBackupFiles='E:\DBsbackup'
-- set this paramater with the destnation of the data and log files
Set @DestDirDbFiles='E:\Microsoft SQL Server\MSSQL.1\MSSQL\Data'
Declare @SourceDirBackupfolder varchar(500)
Declare @dbname Varchar(200)
declare @NoOfRestorefiles int
Declare @dirfile varchar(900)
DECLARE @fname varchar(200)
DECLARE @LogicalName nvarchar(128)
DECLARE @PhysicalName nvarchar(260)
Declare @BaseRestoredone int
DECLARE @sql nvarchar(2000)
DECLARE @type char(1)
--*********************************************************************************
-- Initliazation
--*********************************************************************************
-- create temp table to hold all the the folders has the database files to restore
CREATE TABLE ##folders(fname varchar(200),depth int, file_ int)
-- create temp table to hold all the backup files for each database
CREATE TABLE #files(fname varchar(200),depth int, file_ int)
-- create temp table will save backup file info
CREATE TABLE #dbfiles(
LogicalName nvarchar(500)
,PhysicalName nvarchar(500)
,Type char(1)
,FileGroupName nvarchar(500)
,Size numeric(20,0)
,MaxSize numeric(20,0),
filed sql_variant,
createlsn sql_variant,
droplsn sql_variant,
uniqueld sql_variant,
readonlylsn sql_variant,
readwritelsn sql_variant,
backupsizeinbytes sql_variant,
sourceblocksize sql_variant,
filedgroup sql_variant,
loggroupguid sql_variant,
diff sql_variant,
diff1 sql_variant,
isreadonly sql_variant,
ispresent sql_variant)
--*********************************************************************************
-- script body
--*********************************************************************************
--get all the folders from the source backup folder and each folder will be the database name
INSERT ##folders
EXECUTE master.dbo.xp_dirtree @SourceDirBackupFiles, 1, 1
--Cursor to process each folder(database backup files)
DECLARE folders CURSOR FOR SELECT fname FROM ##folders where fname not in ('master', 'tempdb','model','msdb')
open folders
fetch next from folders into @dbname
WHILE @@FETCH_STATUS = 0
BEGIN
--get all the files from the Directory contains the SQL backup
set @SourceDirBackupfolder =@SourceDirBackupFiles+'\'+@dbname
-- get the backup file to restore
INSERT #files
EXECUTE master.dbo.xp_dirtree @SourceDirBackupfolder, 1, 1
-- Select the full backuo file
SELECT @fname=fname from #files where substring(fname,len(fname)-2,3)='dif'
print @fname
--Construct the beginning for the RESTORE DATABASE command
set @dirfile=@SourceDirBackupFiles+'\'+@dbname+'\'+@fname
print @dirfile
SET @sql = 'RESTORE DATABASE [' + @dbname + '] FROM DISK = ''' + @dirfile + ''''
print @sql
exec (@sql)
truncate table #files
fetch next from folders into @dbname
End
--**************************************************
close folders
deallocate folders
drop table ##folders
drop table #files
drop table #dbfiles

  1. After restoring the differential backup the databases status will be ready and must be changed to read write using SQL script:

Declare @dbname Varchar(200)
DECLARE @sql nvarchar(2000)
DECLARE DBs CURSOR FOR SELECT name from sys.databases where name not in ('master', 'model','msdb','tempdb')
Open DBs
fetch next from Dbs into @dbname
WHILE @@FETCH_STATUS = 0
BEGIN
SET @sql = 'ALTER DATABASE [' + @dbname + '] SET READ_WRITE WITH ROLLBACK IMMEDIATE'
exec (@sql)
SET @sql = 'ALTER DATABASE [' + @dbname + '] SET READ_WRITE'
Exec(@sql)
fetch next from Dbs into @dbname
END
close DBs
deallocate DBs

  1. Shutdown the old cluster company-db( Node1 & Node2)
  2. Delete DNS entries for company-db on DNS Servers
  3. Stop database cluster resources on the new Cluster
  4. Rename Database cluster name to company-db as below:
  1. Restart the cluster( the new company-db) and make sure the SQL resources are online
  2. wait till you see machine name company-db in active directly (AD)and DNS server will have entry for company-db this might take around 15 minutes
  3. Verify connectivity for company-db from outside (PC or smoothing)
Posted in Microsft SQL Server | Tagged , , | 2 Comments

Disaster Recovery plan for SQL Server 2005

Recover SQL Server 2005 Server:

This posting describes the steps to recover SQL Server 2005/2008 at disaster recovery site in case of major disaster in the data center.  The disaster recovery plan assumes a regular backup of SQL databases done, also the production server has E Partition contain data/log files of SQL databases. If there is a major disaster of the SQL database server locate the hardware replacement of the server and the latest backups of the databases which have at least one full backup, Install windows, Install SQL 2005, restore the master database and user databases using SQL script as below.

The same disaster recovery plan can be used with SQL Server 2008 since there are no major changes between SQL 2005 and SQL 2008.

1. Build Windows 2003 Server 64bit

1.1        The server should have 16GB of RAM and 4 Xeon CPU at 3.0GHZ

1.2        Operating system Partitions

Partition Description Size
C:\ OS and Oracle will be installed in this partition 30 GB
E:\ Oracle data files will be stored in this partition 300GB

1.3        Install Windows 2003 64bit with sp2

1.4        Change the machine Name

2. Install SQL 2005 Enterprise Edition 64bit

2.1        Create Active directory account will be used to run SQL server Services(Company\db2recovery_user)

You don’t have to create this account if you will use local system account to run SQL services

2.2        Install SQL 2005

      • Download SQL 2005 installation from Microsoft website 
      • Start the setup
      • Install Prerequisite will start and once it is completed Click Next
      • Welcome to Microsoft SQL server Installation Wizard will show up
      • Click Next
      • System Configuration check will start
      • Verify all the checks completed successfully and Click Next
      • Click Next
      • Check the following components:
          1. SQL server Database services
          2. Analysis Services
          3. Integration Services
          4. Work component, books online and development
      • See Fig. 1 for Details 

Fig. 1

      • Click Advanced >> Database Services>> Data Files
      • Click Browse and Set the data files path to E:\Microsoft SQL Server\ as in Fig. 2

Please note that you have to set the data files path to E:\Microsoft SQL Server\ otherwise the recovery plan will not work

Fig. 2

    • Click Ok >> Click Next
    • Leave the default instance checked and Click Next
    • Service Account form will show up as in Fig. 3, please note you can use built-in system account or AD account created earlier

Fig. 3

      • Click Next
      • Select Mixed Mode, set SA password and Click Next
      • Click Next
      • Click Next
      • Ready to Install Screen will show up
      • Click Install
      • The installation process might take 45 minutes
      • Verify the install completed successfully without errors

2.3        Install Service pack 4

      • Download it from Company file server or from Microsoft
      • Start the installer
      • Leave the default settings and Click next
      • Double click to install

3. SQL databases Recovery Steps

3.1        Restore databases backup from the backup system to the new server, you need to restore the latest full and differential backups to E:\dbsbackup

3.2        Restore OLAP backups from the backup system to the new server, you should restore the latest backups to E:\OLAPbackups

3.3        Recover the database engine

      • Restore the master database

Click Start>>All Programs>>Microsoft SQL Server>> Configuration Tools>> SQL Server configuration Manager
Right Click SQL Server(MSSQLSERVER) >>Properties>> Advanced
Edit Startup Parameters and add “;-c; -m” as in Fig. 4

Fig. 4

Click OK, warning message will show up, Click OK
Right Click SQL Server(MSSQLSERVER) >>Restart
Click Start>>ALL Programs>>Microsoft SQL Server >>SQL Server Management Studio 
Set the Server type to database engine
Set the Server name to the database Server name
Click Connect
Click Databases>> System databases
Right Click Master>> Tasks >> Restore >> Database
Select From Device and add backup file
Click Option and Select “Overwrite the existing database”
Set the file path for the data and log files as in Fig. 5

Fig. 5

Click OK  and wait for the restore to be completed
You might get error message after the restore, don’t worry about it. When you restore the master database, the database engine restart and that is why you get this error message
Click OK>>Click Cancel
Click Start>>All Programs>>Microsoft SQL Server>> Configuration Tools>> SQL Server configuration Manager
Right Click SQL Server(MSSQLSERVER) >>Properties>> Advanced
Edit Startup Parameters and remove “;-c; -m”
Click OK, warning message will show up, Click OK
Right Click SQL Server(MSSQLSERVER) >>Restart
Close SQL Server Management Studio if it is open
Click Start>>All Programs>>Microsoft SQL Server >>SQL Server Management Studio 
Set the Server type to database engine
Set the Server name to the database Server name
Change the authentication mode to “SQL Server authentication”
Login as sa
Sa password will be the password of the old server
Click Connect
Verify the users are showing up
Verify the linked servers are showing up 

      • Restore MSDB database

Stop SQL agent>> Click Databases>> System databases
Right Click msdb>> Tasks >> Restore >> Database
Select From Device and add backup file
Click Option and Select “Overwrite the existing database”
Set the file path for the data and log files as in Fig. 6

Fig. 6

Click OK  and wait for the restore to be completed
Click OK
Verify the jobs show up>> Click SQL Server Agent>>Jobs  
Verify the SSIS packages, Click Connect>> Integration services >> Click Connect>> Stored Packages >> MSDB

      • Restore user databases  
          1. In backup folder (the folder you restored earlier in 3.1) move or delete the backups for master, msdb and model database. The script will restore any user database in the backup folder and don’t want restore any system database so the restore folder shouldn’t have master, msdb and model folders. the backup folder have a folder for every user database and the folder name the same as the database name 
          2. SQL Server Management Studio >>Click New Query
          3. Copy the script below in step f.
          4. Set the Two parameters @SourceDirBackupFiles and @DestDirDbFiles, please read the script comments for details below
          5. Click Execute
          6. The script will run for maybe five hours(it depends on the number of database you will restore and the size of each database:

--*********************************************************************************
-- Written by : Tarek Mohamed Date:May 10, 2008
-- Updated by: Date:
-- Updated by: Date:
-- Updated by: Date:
-- Version:1.0
--*********************************************************************************
--
-- This script will restore databases in folder which each database has folder and the folder name will be
-- the database name so you can use this script to restore one database or more
-- The functnality of the script:
-- 1-Locate the database folder in the source folder
-- 2-Sort the files in in ecach database folder
-- 3-Locate the latest full backup and restore it to the new location
-- 4-Locate the Transaction log files and restore them
-- Please note this script desgined to work with backups form SQL maintenance plans
--
--*********************************************************************************
-- User inputs
-- 1-@SourceDirBackupFiles, this parameter with the path of the backup files
-- 2-@DetDirDbfiles, this paramter will have the path will be used to restore the data and log file
-- for each database
--*********************************************************************************
SET NOCOUNT ON
Declare @SourceDirBackupFiles varchar(500)
Declare @DestDirDbFiles varchar(500)
-- set this paramater with the path of the backups folder
Set @SourceDirBackupFiles='E:\DBsbackup'
-- set this paramater with the destnation of the data and log files
Set @DestDirDbFiles='E:\Microsoft SQL Server\MSSQL.1\MSSQL\Data'
Declare @SourceDirBackupfolder varchar(500)
Declare @dbname Varchar(200)
declare @NoOfRestorefiles int
Declare @dirfile varchar(900)
DECLARE @fname varchar(200)
DECLARE @LogicalName nvarchar(128)
DECLARE @PhysicalName nvarchar(260)
Declare @BaseRestoredone int
DECLARE @sql nvarchar(2000)
DECLARE @type char(1)
--*********************************************************************************
-- Initliazation
--*********************************************************************************
-- create temp table to hold all the the folders has the database files to restore
CREATE TABLE ##folders(fname varchar(200),depth int, file_ int)
-- create temp table to hold all the backup files for each database
CREATE TABLE #files(fname varchar(200),depth int, file_ int)
-- create temp table will save backup file info
CREATE TABLE #dbfiles(
LogicalName nvarchar(500)
,PhysicalName nvarchar(500)
,Type char(1)
,FileGroupName nvarchar(500)
,Size numeric(20,0)
,MaxSize numeric(20,0),
filed sql_variant,
createlsn sql_variant,
droplsn sql_variant,
uniqueld sql_variant,
readonlylsn sql_variant,
readwritelsn sql_variant,
backupsizeinbytes sql_variant,
sourceblocksize sql_variant,
filedgroup sql_variant,
loggroupguid sql_variant,
diff sql_variant,
diff1 sql_variant,
isreadonly sql_variant,
ispresent sql_variant)
--*********************************************************************************
-- script body
--*********************************************************************************
--get all the folders from the source backup folder and each folder will be the database name
INSERT ##folders
EXECUTE master.dbo.xp_dirtree @SourceDirBackupFiles, 1, 1
--Cursor to process each folder(database backup files)
DECLARE folders CURSOR FOR SELECT fname FROM ##folders
open folders
fetch next from folders into @dbname
WHILE @@FETCH_STATUS = 0
BEGIN
--get all the files from the Directory contains the SQL backup
set @SourceDirBackupfolder =@SourceDirBackupFiles+'\'+@dbname
INSERT #files
EXECUTE master.dbo.xp_dirtree @SourceDirBackupfolder, 1, 1
DECLARE files SCROLL CURSOR FOR SELECT fname from #files order by fname desc
open files
fetch next from files into @fname
set @NoOfRestorefiles=0
set @BaseRestoredone=0
WHILE (@@FETCH_STATUS = 0 and @BaseRestoredone=0)
BEGIN
--print substring(@fname,len(@fname)-2,3)
if substring(@fname,len(@fname)-2,3)='bak'
begin
set @BaseRestoredone=1
--Construct the beginning for the RESTORE DATABASE command
set @dirfile=@SourceDirBackupFiles+'\'+@dbname+'\'+@fname
print @dirfile
SET @sql = 'RESTORE DATABASE [' + @dbname + '] FROM DISK = ''' + @dirfile + ''' WITH MOVE '
TRUNCATE TABLE #dbfiles -- delete the records in this temp table
Insert #dbfiles
EXEC('RESTORE FILELISTONLY FROM DISK = ''' + @dirfile + '''')
-- Declare Cursor for the files for each database
DECLARE dbfiles CURSOR FOR
SELECT LogicalName, PhysicalName, Type FROM #dbfiles
OPEN dbfiles
FETCH NEXT FROM dbfiles INTO @LogicalName, @PhysicalName, @type
--For each database file that the database uses

WHILE @@FETCH_STATUS = 0
BEGIN
SET @sql = @sql + '''' + @LogicalName + ''' TO ''' + @DestDirDbFiles + '\' + @dbname
--print @sql
IF @type = 'D'
SET @sql = @sql + '.mdf'', MOVE '
ELSE IF @type = 'L'
SET @sql = @sql + '_log.ldf'''
FETCH NEXT FROM dbfiles INTO @LogicalName, @PhysicalName, @type
END
CLOSE dbfiles
DEALLOCATE dbfiles
set @sql=@sql+', NORECOVERY, REPLACE '
print @sql
exec (@sql)
-- ****************************************************************************
-- restore the log files
--****************************************************************************
if @NoOfRestorefiles>0
begin
WHILE @NoOfRestorefiles <> 0
BEGIN
fetch PRIOR from files into @fname
set @dirfile=@SourceDirBackupFiles+'\'+@dbname+'\'+@fname
set @NoOfRestorefiles=@NoOfRestorefiles-1
if @NoOfRestorefiles=0
begin
set @sql='RESTORE LOG ['+@dbname+'] FROM DISK ='''+ @dirfile+''' with recovery'
end
else
begin
set @sql='RESTORE LOG ['+@dbname+'] FROM DISK ='''+ @dirfile+''' with norecovery'
end
print @sql
exec (@sql)
End
end
end
set @NoOfRestorefiles=@NoOfRestorefiles+1
fetch next from files into @fname
End
close files
deallocate files
truncate table #files
fetch next from folders into @dbname
End
--**************************************************
close folders
deallocate folders
drop table ##folders
drop table #files
drop table #dbfiles

3.4        Recover SQL Integration Services(SSIS)

Once you restore the MSDB database as part of the database engine recovery all the packages will show up in SSIS and you don’t need to do anything

3.5        Recover analysis Services

        • Click Start>>All Programs>>Microsoft SQL Server >>SQL Server Management Studio 
        • Set the Server type to Analysis Services
        • Set the Server name to the database Server name
        • Click Connect
        • Right Click databases >>Restore
        • Set restore database name to Cube name
        • Set from backup file to the latest backup file for the cube
        •  The backup file path will be something like this E:\OLAPBackup\cubename_backup_2008_5_13.abf
        • Click OK
        • Restore the remaining Cubes as as described earlier
Posted in Microsft SQL Server | Tagged , , , , | Leave a comment

Recover Oracle database

Recover Oracle database:

This posting describes the steps to recover Oracle database at disaster recovery site in case of major disaster in the data center.  The disaster recovery plan assumes a regular backup of oracle database and it is control file done, also the production server is windows server. If there is a major disaster of the Oracle database server locate the hardware replacement of the server and the latest Oracle backup set which have at least one full backup, Install windows, Install Oracle 10g, restore the latest control to generate a SQL trace which will be used to restore the database using RMAN. the reason to create SQL trace from the control file is the ability to edit the file and change the path of oracle data file since the original path might be different for the path in the new server and use it to restore the database.

1. Build Windows 2003 Server 64bit

1.1        The server should have 16GB of RAM and 4 Xeon CPU at 3.0GHZ

1.2        Operating system Partitions

Partition Description Size
C:\ OS and Oracle will be installed in this partition 30 GB
E:\ Oracle data files will be stored in this partition 300GB

1.3        Install Windows 2003 64bit with sp2

1.4        Change the machine Name

2. Install Oracle Standard Edition 64bit

2.1        Install Oracle 1og

    • Login to Company-ifsdb1 with admin rights
    • Download Oracle Standard Edition installation files from Oracle website
    • Start the setup
    • Oracle universal installer will show up
    • Set installation type to Standard Edition
    • Click Next as in Fig. 1

Fig. 1

  • Verify all the checks completed successfully and Click Next
  • Summary page will show up
  • Click Install and wait, the installer might take 45 minutes
  • The installer will install the database engine
  • End of the installation page will show up
  • Click Exit >> Click Yes
  • Verify the install completed successfully without errors

2.2        Install Patch Set 3 (6810189)

  • Download patch set 3 from Oracle website to folder p6810189_10204_MSWIN-x86-64
  • Navigate to Disk1 Folder and Double click setup
  • Once the welcome screen Click Next
  • Click Next as in Fig. 2

Fig. 2

  • Click Install
  • Click Exit and Click Yes
  • Restart the machine

2.3        Apply Bundle Patch(10.2.0.4 Patch 35) 9481771 to DB home

  • Download the patch from Oracle websie then Apply it

Start>>Run>>cmd
Set ORACLE_HOME= C:\oracle\product\10.2.0\db_1
Set path= C:\oracle\product\10.2.0\db_1\OPatch
C:\disks\9481771>opatch apply

  • For more details see readme file

2.4        Create new oracle database

    • Start>>All Programs>>Oracle – OraDb10g_home>>Configuration and Migration Tools>>Database Configuration Assistant
    • Welcome screen will show up and Click Next
    • Select Create database and Click Next
    • Select general database purpose and Click Next
    • Set the database name as in Fig. 3

Fig. 3

  • Click Next
  • Leave the default setting and Click Next
  • Set the password as in Fig. 4

 Fig. 4

  • Leave the default settings and Click next
  • Set the database files path as in Fig. 5

 Fig. 5

  • Set recovery options for the database as in Fig. 6

Fig.6

  • Leave the default settings and Click next
  • Leave the default settings and Click next
  • Leave the default settings and Click next
  • Leave the default settings and Click next
  • Leave the default settings and Click Finish
  • Leave the default settings and Click OK
  • The database creation might take 45 minutes
  • Click Exit

2.5        Verify connectivity to the database server from outside  

3. Oracle database Recovery Steps

3.1        Restore backup folder from backup system to the new server, you should restore the latest full and differential backup for the folder to E:\orabkp\DatabaseName  

3.2        Restore the control file

  • Navigate to the backup folder at  E:\orabkp\DatabaseName and sort the files by date as in Fig. 6 

Fig. 6

  • Locate the latest control file; the file format will be like this C-DBID-Date-XX where DBID is the database id number. in Fig. 6 the latest control file was C-425570525-20080515-0E
  • Restore the control file using command line

Start>>Run>>cmd
C:\>rman target/ nocatalog
Recovery Manager: Release 10.2.0.3.0 – Production on Fri May 16 15:42:13 2008
Copyright (c) 1982, 2005, Oracle.  All rights reserved.
connected to target database (not started)
RMAN> startup force nomount;
RMAN> restore controlfile from ‘E:\oradbkp\DatabaseName\C-425570525-20080515-0E’;
Starting restore at 16-MAY-08
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=156 devtype=DISK
channel ORA_DISK_1: restoring control file
channel ORA_DISK_1: restore complete, elapsed time: 00:00:04
output filename=E:\ORADATA\DATABASENAME\CONTROL01.CTL
output filename=E:\ORADATA\DATABASENAME\CONTROL02.CTL
output filename=E:\ORADATA\DATABASENAME\CONTROL03.CTL
Finished restore at 16-MAY-08
RMAN> alter database mount;

3.3        Create Oracle database restore script

      • Get the database structure from the control file

 C:\>sqlplus / as sysdba
SQL*Plus: Release 10.2.0.3.0 – Production on Fri May 16 16:22:52 2008
Copyright (c) 1982, 2006, Oracle.  All Rights Reserved.
SQL> startup force mount
SQL> alter database backup control file to trace as ‘E:\DatabaseNametrace.sql’;

      • Generate restore script

Open the file at E:\DatabaseNametrace.sql , this file will have the database file structure, datafiles and logfile. You need to change the path for all the data files in the restore script to reflect the new path. You can use copy/paste/replace in notepad to generate the restore script

      • Restore script template

Please note the script is just a template, in my case I have to change the path of the data file since production server were using ASM and recovery server use File system to store the data files and your database will be different.  Anyways verify the database structure using E:\DatabaseNametrace.sql file:

RMAN> Run {
  set newname for datafile '+DB_GROUP/DatabaseName/datafile/system01.dbf' to 'E:\oradata\DATABASENAME\system01.dbf';
  set newname for datafile '+DB_GROUP/DatabaseName/datafile/undotbs1_01.dbf'  to 'E:\oradata\DATABASENAME\undotbs1_01.dbf';
  set newname for datafile '+DB_GROUP/DatabaseName/datafile/sysaux01.dbf'  to 'E:\oradata\DATABASENAME\sysaux01.dbf';
  set newname for datafile '+DB_GROUP/DatabaseName/datafile/undotbs2_01.dbf'  to 'E:\oradata\DATABASENAME\undotbs2_01.dbf';
  set newname for datafile '+DB_GROUP/DatabaseName/datafile/users01.dbf'  to 'E:\oradata\DATABASENAME\users01.dbf';
  set newname for datafile '+DB_GROUP/DatabaseName/datafile/ial_data.dbf'  to 'E:\oradata\DATABASENAME\ial_data.dbf';
  set newname for datafile '+DB_GROUP/DatabaseName/datafile/ial_index.dbf'  to 'E:\oradata\DATABASENAME\ial_index.dbf';
  set newname for datafile '+DB_GROUP/DatabaseName/datafile/ifsapp_archive_data.dbf'  to 'E:\oradata\DATABASENAME\ifsapp_archive_data.dbf';
  set newname for datafile '+DB_GROUP/DatabaseName/datafile/ifsapp_archive_index.dbf'  to 'E:\oradata\DATABASENAME\ifsapp_archive_index.dbf';
  set newname for datafile '+DB_GROUP/DatabaseName/datafile/ifsapp_data.dbf'  to 'E:\oradata\DATABASENAME\ifsapp_data.dbf';
  set newname for datafile '+DB_GROUP/DatabaseName/datafile/ifsapp_data02.dbf'  to 'E:\oradata\DATABASENAME\ifsapp_data02.dbf';
  set newname for datafile '+DB_GROUP/DatabaseName/datafile/ifsapp_data03.dbf'  to 'E:\oradata\DATABASENAME\ifsapp_data03.dbf';
  set newname for datafile '+DB_GROUP/DatabaseName/datafile/ifsapp_index.dbf'  to 'E:\oradata\DATABASENAME\ifsapp_index.dbf';
  set newname for datafile '+DB_GROUP/DatabaseName/datafile/ifsapp_index02.dbf'  to 'E:\oradata\DATABASENAME\ifsapp_index02.dbf';
  set newname for datafile '+DB_GROUP/DatabaseName/datafile/ifsapp_index03.dbf'  to 'E:\oradata\DATABASENAME\ifsapp_index03.dbf';
  set newname for datafile '+DB_GROUP/DatabaseName/datafile/ifsapp_lob.dbf'  to 'E:\oradata\DATABASENAME\ifsapp_lob.dbf';
  set newname for datafile '+DB_GROUP/DatabaseName/datafile/ifsapp_report_data.dbf'  to 'E:\oradata\DATABASENAME\ifsapp_report_data.dbf';
  set newname for datafile '+DB_GROUP/DatabaseName/datafile/ifsapp_report_index.dbf'  to 'E:\oradata\DATABASENAME\ifsapp_report_index.dbf';
  set newname for datafile '+DB_GROUP/DatabaseName/datafile/ifsapp_data04.dbf'  to 'E:\oradata\DATABASENAME\ifsapp_data04.dbf';
  set newname for datafile '+DB_GROUP/DatabaseName/datafile/ifsapp_index04.dbf'  to 'E:\oradata\DATABASENAME\ifsapp_index04.dbf';
  set newname for datafile '+DB_GROUP/DatabaseName/datafile/migration_data.dbf'  to 'E:\oradata\DATABASENAME\migration_data.dbf';
  set newname for datafile '+DB_GROUP/DatabaseName/datafile/migration_index.dbf'  to 'E:\oradata\DATABASENAME\migration_index.dbf';
  set newname for datafile '+DB_GROUP/DatabaseName/datafile/radley.dbf'to 'E:\oradata\DATABASENAME\radley.dbf';
restore database;switch datafile all;recover database;}  

Finalize the restore script then run it RMAN:
C:\>rman target sys/DbPassword@Oracledbname
Recovery Manager: Release 10.2.0.3.0 – Production on Fri Apr 18 17:27:51 2008
Copyright (c) 1982, 2005, Oracle.  All rights reserved.
connected to target database: LINPROD (DBID=425570525)
RMAN>startup force mount;
RMAN>Paste the restore script generate in the earlier step 

Please note that the restore might long time based on of the size of the database you are restoring

You might get warning message like the one below so don’t worry about it since it will be fixed by renaming the redologs

starting media recovery
unable to find archive log
archive log thread=1 sequence=13550
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of recover command at 02/04/2010 08:32:30
RMAN-06054: media recovery requesting unknown log: thread 1 seq 13550 lowscn 102
8415872

3.4        Rename the online redolog files before opening the database

C:\>sqlplus / as sysdba
SQL*Plus: Release 10.2.0.3.0 – Production on Fri May 16 16:22:52 2008
Copyright (c) 1982, 2006, Oracle.  All Rights Reserved.
 SQL>  alter database rename file ‘+DB_GROUP/DatabaseName/redo01.log’ to ‘E:\oradata\DATABASENAME\redo01.log’;
 alter database rename file ‘+DB_GROUP/DatabaseName/redo02.log’ to ‘E:\oradata\DATABASENAME\redo02.log’;
 alter database rename file ‘+DB_GROUP/DatabaseName/redo03.log’ to ‘E:\oradata\DATABASENAME\redo03.log’;
 alter database rename file ‘+DB_GROUP/DatabaseName/redo04.log’ to ‘E:\oradata\DATABASENAME\redo04.log’;
 alter database rename file ‘+DB_GROUP/DatabaseName/redo05.log’ to ‘E:\oradata\DATABASENAME\redo05.log’;
 alter database rename file ‘+DB_GROUP/DatabaseName/redo06.log’ to ‘E:\oradata\DATABASENAME\redo06.log’;
 alter database rename file ‘+DB_GROUP/DatabaseName/redo07.log’ to ‘E:\oradata\DATABASENAME\redo07.log’;
 alter database rename file  ‘+DB_GROUP/DatabaseName/redo08.log’ to ‘E:\oradata\DATABASENAME\redo08.log’;
SQL>  alter database open resetlogs

Posted in Oracle Database | Tagged , , , | Leave a comment

Backup/restore Oracle database 10g

Purpose:

The first time I have tried to restore Oracle database to a certain point it wasn’t easy like Microsoft SQL server, I wanted to patch the database with ERP system patch then restore to point before I applied the patch in case of something happened. I did recover oracle database however it restored everything after the point I want recover, finally I realized I have to perform point in time recovery.

  1. Perform full backup using rman command line

 Log on to Oracle database server using a user member ora_dba local group
 In command prompt:
C:\>rman target sys/DbPassword@Oracledbname  

Recovery Manager: Release 10.2.0.3.0 – Production on Fri Apr 18 17:27:51 2008  

Copyright (c) 1982, 2005, Oracle.  All rights reserved. 

connected to target database: Oracledbname (DBID=425570525) 

RMAN> RUN {
  ALLOCATE CHANNEL disk1 DEVICE TYPE DISK
  FORMAT ‘\\local or shared disk\oracle_bkp\18042008/%U’;
  BACKUP DATABASE } 

  1. Restore the database to point of time 

    1. Shutdown the database

      Log on to Oracle database server using a user member ora_dba local group In command prompt:

      Microsoft Windows [Version 5.2.3790]

      (C) Copyright 1985-2003 Microsoft Corp.

      C:\>Sqlplus sys/DbPassword@Oracledbname as sysdba

      SQL*Plus: Release 10.2.0.3.0 – Production on Fri Apr 18 17:45:07 2008

      Copyright (c) 1982, 2006, Oracle.  All Rights Reserved.

      Connected to: 

      Oracle Database 10g Release 10.2.0.3.0 – 64bit Production

      SQL> shutdown immediate

    2. Restore to Point of time

      Log on to Oracle database server using a user member ora_dba local group  In command prompt:

      C:\>rman target sys/DbPassword@Oracledbname
      Recovery Manager: Release 10.2.0.3.0 – Production on Fri Apr 18 17:27:51 2008
      Copyright (c) 1982, 2005, Oracle.  All rights reserved.
      connected to target database: LINPROD (DBID=425570525)
      RMAN>startup force mount;
      RMAN> RUN {set until time “to_date(’18-04-08 15:35:00′, ‘dd-mm-yy hh24:mi:ss’)”;restore database;recover database;} 

    1. Start the database

Log on to Oracle database server using a user member ora_dba local group

In command prompt:

Microsoft Windows [Version 5.2.3790]

(C) Copyright 1985-2003 Microsoft Corp.

C:\>Sqlplus sys/DbPassword@Oracledbname as sysdba

SQL>startup

 

Posted in Oracle Database | Tagged , , | Leave a comment

Install BusinessObjects Planning and Consolidation (BPC) Clients

Purpose:

This posting describes the steps to Install BusinessObjects Planning and Consolidation (BPC) office client and Administration client

Install the Office Client:

Office client is required for end user who connect to SAP BPC to perform day to day activities like submitting data and running reports so every system user must have it installed  

 Prerequisites

  •  Microsoft Internet Explorer 7.0, 8.0, or later
  • Microsoft Office 2003 or 2007
  • SP04 or later: Microsoft Office 2010 (32‒bit only)
  • .NET Framework 2.0 SP1
  • XML 4.0 SP2 (included with the Internet Explorer software)
  • Pentium IV (1 GHz CPU)
  • 512 MB RAM
  • 100 MB of free hard disk space in the My Documents folder
  • 30 MB of free hard disk space for the installed Client files
  • Adobe Reader (free from Adobe)

Install the Office client: 

  • Log on to a machine on which the user is part of the Administrators group
  • Close any office application (Excel, word..etc)
  • Uninstall any previous versions of the Planning and Consolidation for Office clients. (Use Add/Remove Programs in the Windows Control Panel)
  •  Point your browser to Planning and Consolidation Web (http://<PC_server:port>/osoft), where <PC_server:port> is the name or IP address and port of the server where you installed Planning and Consolidation and you will be asked to install SAP plug-in three times please Click Install. For Company development environment the link is http://company-bpcdev/osoft/

If your machine is running windows 7, when you open the browser (IE) you must run as administrator as below otherwise the client installs will fail

  • From the Business Planning and Consolidation Launch page, choose the Client Software Center link or icon.

 

  • Click Ok when you see Select an Application Set
  • Select Client for Office Installation. Wait while the installation wizard verifies your prerequisites, then select Next.
  • From the installation wizard, select Next, then select I accept the terms of the license agreement and click Next.
  • Select the language in which you want to see the user interface, then select Next.
  • Select Next to install the program to the default location.
  • When the installation is complete, choose Finish

 Install the Administration Client:

Administration client is required for system administrator users who connect to SAP BPC to perform tasks like taking system offline and manage system applications 

Prerequisites:

  •  Microsoft Internet Explorer 7.0, 8.0, or later
  •  Microsoft Office 2003 or 2007
  • SP04 or later: Microsoft Office 2010 (32‒bit only)
  • .NET Framework 2.0 SP1
  • XML 4.0 SP2 (included with the Internet Explorer software)
  • Pentium IV (1 GHz CPU)
  • 512 MB RAM
  • 100 MB of free hard disk space in the My Documents folder
  • 20 MB of free hard disk space for the installed Administration files

 Install Administration Client

  •  Log on to a machine on which the user is part of the Administrators group
  • Close any office application (Excel, word..etc)
  • Uninstall any previous versions of the Planning and Consolidation for Office clients. (Use Add/Remove Programs in the Windows Control Panel)
  • Point your browser to Planning and Consolidation Web (http://<PC_server:port>/osoft), where <PC_server:port> is the name or IP address and port of the server where you installed Planning and Consolidation. For Company development environment the link is http://company-bpcdev/osoft/
  • From the Business Planning and Consolidation Launch page, choose the Client Software Center link or icon.
  • Click Ok If you see Select an Application Set
  • Select Administration Client Installation. Wait while the installation wizard verifies your prerequisites, then select Next
  •  From the installation wizard, select Next, then select I accept the terms of the license agreement and click Next.
  • Select the language in which you want to see the user interface, then select Next.
  • Select Next to install the program to the default location.
  • When the installation is complete, choose Finish
Posted in SAP BPC | Tagged , | Leave a comment

Install SAP BusinessObjects Planning and Consolidation (BPC) 7.5

Purpose                                                                                      

The posting describes the steps to Install SAP BusinessObjects Planning and Consolidation (BPC) 7.5 .  The installation will be done in two servers, a database server and SAP BPC server which will have application server/web server component for SAP BPC.  

I have used SAP BPC documentation however it took me sometime to complete the installation successfully so I wrote the instructions below based in my experience

1. Build a Database Server

1.1        Install windows 2008 R2 64 bit and install the latest service pack/updates

1.2        Install SQL 2008 R2 Enterprise edition 64bit for production environment  or developer edition for test environment 

  • Create Active directory account will be used to run SQL server Services (Company\dbuser_xxxx) where xxxx are the suffix of the server name for example Company-devdb3 should use dbuser_devdb3 
  • Grant the dbuser_xxxx right to lock pages in memory as below  
  • Start SQL 2008 R2 setup program >> Installation >> New installation or add to an existing installation   
  • Setup support Rules  from will showup so Click OK
  • Enter Product key then Click Next
  • Check I accept License Terms >> Click Next
  • Setup support files form will showup >> Click Install
  • Click Next>> Select Server feature installation then Click Next
  • Select the following features:
            1. Database Engine services
            2. Full-Text Search
            3.  Analysis services
            4. Reporting Services
            5. Business Intelligence development studio
            6. Client Tools connectivity
            7. Integration services
            8. Client tool backwards compatibility
            9. Management Tool – Basic

 

        • Click Next >> Click Next
        • Specify the Instance ID >> Click Next >> Click Next
        • Click Use the same account for all SQL server services and enter AD user/password created earlier
        • Set SQL Server Agent Startup type to Automatic
        • Click Next
        • In Database engine configuration specify SQL server administrator >> Click Add and select the AD group will administer database engine >> Select Mixed mode and Sa password >> Click Next
        • In Analysis service configuration specify users have administrative permission  Analysis service >> Click Add and select the AD group will administer Analysis service >> Click Next
        • Click Next >> Click Next >>  Click Next >> Click Next
        • Click Install ( installation will take around 20 minutes)
        • Install the latest SQL service pack/updates

1.3        Enable TCP/IP >> Open SQL Server Configuration Manager >>SQL Network Configuration >> Protocol for Microsoft SQL Server >>  TCP/IP >> Set Enable to Yes >> OK

1.4        Navigate to C:\Program Files\Microsoft SQL Server\MSRS10_50.MSSQLSERVER\Reporting Services\ReportServer >> Edit rsreportserver.xml file >>  change set “SecureConnectionLevel” Value=”0″ instead of  2

1.5        Grant the SQL services AD account that run Analysis service sysadmin role in the database engine, this really important because if you don’t do that the installation of SAP BPC will fail. 

During the installation of SAP BPC it create database called appshell as  part of the sample application so  Analysis service will connect to appshell database to create the cube so if  SQL services AD account (Company\dbuser_xxx) doesn’t have access to appshell  database the process will fail and you will end up with having incomplete appshell

2. Install SAP BusinessObjects Planning and Consolidation (BPC)

SAP BPC application/web server are in the same server  

2.1        Install windows 2008 R2 64 bit and install the latest service pack/updates

2.2        Prerequisites:

      • Install SQL 2008 R2 Shared features:

Start SQL 2008 R2 Setup program and install the following features:

          1.       Business Intelligence development studio
          2.       Client Tools connectivity
          3.       Integration services
          4.       Client tool backwards compatibility
          5.       Management Tool – Basic 

You must install these SQL features otherwise you will get odd error message at the end of installing SAP BPC

      • Install MSXML 4.0 SP2 or later
      • Install ADOMD.NET 9.0
      • Install Microsoft Report Viewer 2008 SP1
      • Install Microsoft SQL Server 2005 backward compatibility  
      • Install .Net FrameWork 3.5 and IIS:

In Windows 2008 R2 Open Server manager >> Roles >> Add roles >> Click Next >> Select Application Server >> Click Add required features >> Click Next >> Click Next >> Select Web Server (IIS) supports >> Click Add required features >> Click Next >> Click Next >> Check Custom Logging and ODBC Logging, ASP Then Check IIS 6 Metabase compatibility >> Click Next >> Click Install

      • Install the Anti-Cross Site Scripting Library:

Download the Anti-Cross Site Scripting Library file AntiXSSLibrary.DLL from the SAP Cryptographic Software download area Save the extracted file (AntiXSSLibrary.DLL) locally to c:\BPC_MS\Library

2.3        Service-Level Accounts

Service accounts are Windows domain IDs with access to all Planning and Consolidation servers with varying levels of privileges:

  • Create System Admin ID AD account something Like BPC_sysadmin_svcTst for Development environment, add it local administrators group in the BPC server(application/Web server), grant it sysadmin role to MS SQL Server,  add to administrator role on analysis services(OLAP),  grant it admin access to reporting services home folder(Browser, Content Manager, My Reports, OutlookSoft Report Viewer, Publisher, Report Builder)
  • Create Admin ID AD account something Like BPC_dmin_Tst for Development environment, add it local Power users group in the BPC server(application/Web server), grant it public role to MS SQL Server, 
  • Create User ID something Like BPC_user_Tst for Development environment, grant it public role to MS SQL Server.

2.4        Install SAP BPC

  • Log on with a domain user ID (System Admin ID created in 2.2.3) to the server where you are installing SAP BPC server.
  • Download BPC Setup to local disk
  • Launch startup from BPC setup folder
  • Click Install Server on SQL 2008
  • Click Next >> accept the license agreement then Click Next
  • Select Custom Multi-server >> Set SLD in System ID >> Click Next
  • Select Microsoft Windows in authentication method>> Click Next
  • Set Services accounts >> Click Next
  •   Enter the required server info, set SQL Server name, OLAP, Insight OLAP and reporting services to the database server name prepared in step 2.1. set Local data path to C:\BPC_MS\Data >> Click Next >>
  • Enter the path and file name for anti-cross site scripting Library, set it to C:\BPC_MS\Library\AntiXSSLibrary.dll >> Click Next
  • Set Program Path to  C:\BPC_MS >> Click Next
  • The installer will start install SAP BPC and will take around 20 minutes

2.5       Required Post-Installation Steps

    • Config XMLA to use dedicated IIS application with 32 bit enabled
        1. Add application Pool>> set the name to BPC_XMLA>> Set Enable 32-bit application to True
        2. Navigate to Sites >> Default Web site >> OSoft >> Advanced Settings >> set application pool to BPC_APP and Click OK
        3. Navigate to Sites >> Default Web site >> XMLA >> Advanced Settings >> set application pool to BPC_XMLA and Click OK
    • Removing Default Access to the BPC Management Console(http://servername/ManagementConsole), by default every user in Active directory have access so we want limit access BPC administer
        1. Create Local group in BPC server, the name of the group name should be BPC_ManagementConsole
        2. Add AD users or group you want grant access to the BPC management console to BPC_ManagementConsole local group in the server
        3. Remove inherited permissions from managementConsole folder , Open IIS manager >> Default Website >> ManagementConsole >> Right Click >> Edit Permissions>> Security >> Advanced >> Change Permission >> uncheck Include inheritable permissions from this object’s parent >> Click Add >> Click OK >> Click OK >> Click OK
        4. Grant Management Console IIS Identity pool access to ManagementConsole Folder. Open IIS manager >> Default Website >> ManagementConsole >> Right Click >> Edit Permissions>> Security >> Edit>> Add “IIS AppPool\BPC_ManagementAP” >> Click OK >> Click OK >> Click OK  If you don’t grant the Management console pool you get error message when you access it throw  http://BPCServerName/ManagementConsole
        5. Remove the group shouldn’t have access management Console, Open IIS manager >> Default Website >> ManagementConsole >> Right Click >> Edit Permissions>> Security >> Edit >> Select Users >>Click remove >> Click Add>> add BPC_ManagementConsole local group >> Click OK >> Click >> Click OK
    • Setting Up ODBC Logging in IIS for the Default Web Site
        1. Create a Microsoft SQL Server user to access AppServer as the default database. Grant it db_Owner database role on AppServer database.
        2. Create ODBC datasource(BPC_ManagementDSN system DSN)  Enter the Microsoft SQL Server user information
        3. Enable the default website to use ODBCLogging by running Command Prompt as administrator:

C:\Windows\system32>cd  C:\windows\system32\inetsrv
appcmd set config -section:ODBCLogging -datasource:BPC_ManagementDSN -tableName:BPCLog -username:BPC_ConsoleUsr -password:Test321
Applied configuration changes to section “system.webServer/odbcLogging” for “MAC
HINE/WEBROOT/APPHOST” at configuration commit path “MACHINE/WEBROOT/APPHOST”
C:\Windows\System32\inetsrv> appcmd set sites “Default Web Site” -logFile.logFormat:Custom -logFile.customLogPluginClsid:{FF16065B-DE82-11CF-BC0A-00AA006111E0}
SITE object “Default Web Site” changed
C:\Windows\System32\inetsrv>

    • Set COM+ Object Application Pooling Pool Size

      Set the application pooling pool size for the COM+ components Everest Update, OSoftSystemConfig, K2Processing, and OsoftDataService as below:
      Choose Start Administrative Tools Component Services COM+ applications, Right-click the application, Choose Properties, then on the Pooling & Recycling tab, change the application pool size to 5 

    • Setting Memory Recycling in the IIS Application Pool

To run large requests, you configure Internet Information Services (IIS) to restart a worker process in an application pool so that it is recycled after using a set amount of memory. To configure a worker process to be recycled, take the following steps:

        1. Start Internet Information Services (IIS) Manager on the Application server.
        2. Expand the local computer, and expand Application pools.
        3. Right-click the application pool that contains the Osoft virtual directory (BPC_APP), then select advanced settings.
        4.  From the Recycling, Virtual memory limit (in KB), enter 1843200 (the maximum amount), and then select OK.
    • Set 3GB Support for Memory on the COM+ Object
        1. Open Component Services on the application server.
        2.  Choose the Everest Update component in COM+ Applications tree.
        3.  Open the Properties menu using the right-click menu.
        4.  Open the Advanced tab.
        5.  Select the Enable 3GB support option, then click OK.

Changing this setting is a potential solution if you receive an Out of memory exception error while running logic.

3. Install SAP BPC Language Package

3.1        Download BPC Language Pack from http://service.sap.com/swdc  

3.2        Navigate to BPC_LanguagePack on the BPC server, click on “Setup.exe” to start the installation >> Click Next>> accept the license agreement and click on Next >> Click Next>> The installation starts >> The installation is finished

Posted in SAP BPC | Tagged | 1 Comment