Database Technologies...!

DB - OpenEdge 11.2  OS - Windows  Abbreviations - MT -> Multi-Tenancy/Tenant,  OE -> OpenEdge  Recently we had to go through on MT architecture and had to use OpenEdge for the implementation. From this post I am hoping to discuss the summary of MT architecture and a very simple use case for demonstrating multi-tenancy support in db level using OpenEdge 11.2.  Multy-Tenancy is a resource sharing concept where deferent tenants (Logical set of users – eg. different clients of a cloud service provider) are accessing same service despite having well differentiated data access, security and resource allocations. Multi Tenancy support was there for infrastructure layer (IaaS providers) and also for application layer. Now database vendors provide MT support built in to the database layer as well so the users can access fully shared applications without even knowing it.  Below image demonstrate the shared tenant access where all entities (Infras...

DB - Oracle 11.2.0.3 OS - Windows server 2008 In the process of migrating a database from 10g to 11g on windows, it was required for us to create rman backup schedule  using DBMS_SCHEDULER. In 10g also, we were using DBMS_SCHEDULER so we were hoping to use same job definition in 11g, but with 11g there has been changes in the dbms_scheduler. In 10g we have used a .bat script which will run a rman script inside it to do a full backup of database. So in 10g job defined with job_type “EXCUTABLE” and job_action pointing to the .bat file did the trick. In 11g also we needed to do the same. But mainly two differences were there. 1 – We can not directly provide .bat file for the job_action. 2    If OracleJobScheduler<DBID>  windows service is not started, Then “Credentials” must be used So the solution was the below job definition with use of credentials and also using arguments for providing the location of bat file. begin dbms_scheduler.creat...

DB - Oracle 9 OS - AIX Tools - Netbackup We have a 9i database which acts as a BCV and kept in read only mode. As per our client agreements we need to do restoration testing for each of the database in every 3 months time. Below is the process we used when restoring raw device based read only database (With Stand by Control file) using netbackup. Step 1- Restore oracle home to target database and do the needed changes in parameter file accordingly Restore the oracle home to target server (If you have same oracle_home as the source in target already this is not required). Then edit the parameter file accordingly. Follow ( http://dbtechno.blogspot.com/2012/11/restore-rman-full-backup-on-different.html - step3) Then mount the database. SQL>alter database mount standby database Standby since we are using a standby control file. Step 2 – Create required raw devices in target host Here you need to identify the required tablespaces/logs you are going to restore and the...

DB2 v9.1 fixpsck 10 - Single Instance on AIX Issue - Can not connect to db2 database directly from server it self. (Remote connection were allowed) Error returned was as below. Any db2 command failes with same error. Since the remote connection are allowed, and only local connections were refused it was obvious that this was not caused by network error/port mismatch etc.. So we were checking for any server changes made and found that the OS admin has changed the "/etc/service" file in the process of SHC gap closing. Cause - While the SHC OS admin has removed the db2 FCM (Fast communications manager) related entries from /etc/services. Which was causing the error. What is FCM - FCM is the mechanism used by DB2 to communicate within database partitions in a DPF environment. Solution - Add the FCM port entries in  "/etc/service". This entries should have port range defined as in the below example. ( cat /etc/services | grep -i db2 ) The difference bet...

In the previous part 1   we restored the database to different server. Now we will do the recovery of this database. Below is the command used for roll forwarding the database we restored in the part 1. This will put the database on consistent state using the logs that were included with the backup file from original host db2 “rollforward database ebppres to end of logs and complete overflow log path (‘/db2_main_data2/ebpplog’)”  “end of logs” – This indicates that we are doing a full recovery instead of Point in time recovery. If point in time recovery is needed “using local time” clause need to be used also specifying the time which the recovery will end. “complete” – This will stop the roll forward process and undo any uncompleted transactions to put the database on consistent(accessible) state. Here the use of “and” is to combine two commands. As an example above single command is same as issuing below two commands. db2 “rollforward database ebppres to en...

DB2 restore/recover database to different server Db2 version - DB2 v9.1.0.10 LUW, no DPF OS – AIX 5.3 Below  is the part 1 of 2 which descibes the process of restoring db2 full online database backup to different server with different mount point definition. So this will be a redirected restore with redefining database containers. BackupProcess Database name – ebpp db2 “backup database ebpp online to /rsair_dbbackup/bifinst1_backup/EBPP compress include logs”; LST -01 Restore Process  Assuming same instance level as the backup host is there on the restoration host, Step 1 – Initiate a redirect restore start Below is the command used.. Explanation follows, db2 terminate db2 "restore database ebpp dbpath on '/db2_main_temp1' into ebppres logtarget '/db2_main_data2/ebpplog' redirect" LST -02 dbpath will be the database path for newly created database. *** This need to be run from the directory which the ba...

This is to discuss the behavior of db2 when users (Who are granted database authorities) are dropped from OS level. Check the current users and their authorities granted.   Add a new user and grant some authorities then drop the user Now recheck the authorities still the user is defined in database and the authorities are there. In most cases this will not be an issue practically as default authentication can not happen since the user is dropped from OS level. Anyways you will have to revoke the authorities manually if you want to remove them. And if you ever needed to remove this type of entries from database (Happened to me.. Didn’t work saying “it will not affect anything L ”) below script will find those users and any objects that they have the ownership. On UNIX, DB2 => 9 . ~/.profile cat /etc/passwd | awk 'BEGIN { FS = ":" } ; { prin...