Interview Questions Oracle DBA Interview Questions & Answers Explain about data and how do you store data? Data is any value which we store for future reference. There are different types of data... Senior Oracle DBA Interview Questions Put your learnings to test by trying to answer below Senior Oracle DBA Interview Questions. I am sure these will definitely challenge... Oracle Golden Gate Interview Questions Here are some of the frequently asked Oracle Golden Gate interview questions and answers. Enjoy! Q. What is the significance of Oracle... Oracle SQL Interview Questions Here are some of the frequently asked SQL interview questions. Enjoy! Q1. What is the difference between VARCHAR & VVARCHAR2? Both... Oracle 12c Multi-tenant Interview Questions Interview questions specific to Oracle 12c Multi-tenant architecture. Enjoy! Q. What are the major changes in architecture for 12c? From...

Oracle Data Guard Startup & Shutdown Steps In this article we will look at Oracle Data Guard startup and shutdown sequence. You must follow proper shutdown order to perform a graceful shutdown. Data Guard Shutdown Sequence Data Guard Startup Sequence Make sure you have permission from application owner / database architect to perform primary shutdown. Data Guard Shutdown Sequence Stop log apply service or MRP and shutdown the standby SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL; SQL> SHUT IMMEDIATE; Stop log shipping from primary and shutdown primary database SQL> ALTER SYSTEM SET log_archive_dest_state_2='DEFER'; SQL> SHUT IMMEDIATE; Data Guard Startup Sequence Startup primary database and enable log shipping SQL> STARTUP; SQL> ALTER SYSTEM SET log_archive_dest_state_2='ENABLE'; Startup standby and enable log apply service or MRP SQL> startup nomount; SQL> alter database mount standby database; SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION; Enjoy! Related Posts Heading 2 Add paragraph text. Click “Edit Text” to customize this theme across your site. You can update and reuse text themes.

Shell Script to Check File or Directory Linux shell script allows users to check and determine if the user input is a file or a directory. To achieve this we are using operators -f and -d. vi check_file_directory.sh # !/bin/bash echo "Enter the file name: " read file if [ -f $file ] then echo $file "---> It is a ORDINARY FILE." elif [ -d $file ] then echo $file "---> It is a DIRCTORY." else echo $file "---> It is something else." fi Here is the sample output of the above script More ways to use -f and -d Operators You can use it to check If a Directory Exists In a Shell Script [ -d "/path/to/dir" ] && echo "Directory /path/to/dir exists." You can also check if File Exists test -f /etc/resolv.conf && echo "$FILE exists." Related Posts Heading 2 Add paragraph text. Click “Edit Text” to customize this theme across your site. You can update and reuse text themes.

Shell Script for Multiple Choice Questions Linux shell script allows you to present a multiple choice question and gets the user’s answer, and reports back whether the answer is right, wrong, or not one of the choices. Here we will user case to match the answer which we accept using the read command. vi multi_choice_question.sh # !/bin/bash echo Shell script is echo a\) an Operating System echo b\) a media player echo c\) a command-line interpreter echo d\) all of the above read answer case $answer in a) echo Wrong - the answer is c ;; b) echo Wrong - the answer is c ;; d) echo Wrong - the answer is c ;; c) echo Right ;; *) echo Not one of the choices ;; esac Here is the sample output of the above script More way to use this case command You can use the case command to evaluate the argument. You can also use it to return the output according to the user inputs. Related Posts Heading 2 Add paragraph text. Click “Edit Text” to customize this theme across your site. You can update and reuse text themes.

Add ISO image storage repository on XenServer If you have used VMware ESXI, its very easy to upload ISO files to the data store and create VMs. With Citrix XenServer, you must create your own ISO repository to upload ISO files. Create ISO directory Create repository Create ISO directory Access your XenServer via ssh and create a directory that will hold ISO images. mkdir /var/opt/ISO Use FTP client like WinSCP to copy ISO files to above directory. Create repository Now its time to register our new directory as storage repository with xenserver xe sr-create name-label=ISO_IMAGES_LOCAL type=iso device-config:location=/var/opt/ISO device-config:legacy_mode=true content-type=iso The new storage repository was created. To list your XenServer storage repository run: xe sr-list Related Posts Heading 2 Add paragraph text. Click “Edit Text” to customize this theme across your site. You can update and reuse text themes.

RMAN Duplicate from Active Database Active database duplication does not require backup of the source database. It duplicates the live source database to the destination host by copying the database files over the network to the auxiliary (clone) instance. RMAN duplicate database can copy the required files as image copies or backup sets. Prerequisites RMAN duplicate database to same SID RMAN Duplicate to different SID Duplicate database is also known as rman database refresh Prerequisites Before you begin with this activity, make sure below items are addressed Source database is running in Archive log mode Check for space requirements on target server Servers are able to ping each other RMAN Duplicate Database to Same SID We are going to clone prod database which is running on prod.dbagenesis.com host. We have another server clone.dbagenesis.com where only oracle software is installed and no database created. We will be using RMAN duplicate from active database to copy prod database on clone server. Clone database SID will be same as source database Configure Listener on clone server to accept incoming connections LISTENER = (DESCRIPTION_LIST = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = clone.dbagenesis.com)(PORT = 1521)) ) ) SID_LIST_LISTENER = (SID_LIST = (SID_DESC = (ORACLE_HOME = /u01/app/oracle/product/12.2.0/db_home) (SID_NAME = prod) ) ) Start the listener on clone server lsnrctl start listener Configure tns entries on the prod server prod_clone = (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = clone.dbagenesis.com)(PORT = 1521)) ) (CONNECT_DATA = (SERVICE_NAME = prod) ) ) Make sure to test the tnsping is working from prod to clone server tnsping prod_clone On prod server, create pfile from spfile and copy to clone server SQL> create pfile from spfile; scp $ORACLE_HOME/dbs/initprod.ora oracle@clone.dbagenesis.com:$ORACLE_HOME/dbs Also copy the password file from prod to clone server scp $ORACLE_HOME/dbs/orapwprod oracle@clone.dbagenesis.com:$ORACLE_HOME/dbs If no password file exists, create one via below command and then copy orapwd file=$ORACLE_HOME/dbs/orapwprod force=y On the clone server, open the pfile that you copied from prod server and create directory locations cat $ORACLE_HOME/dbs/initprod.ora In my case I had to create below two main locations mkdir -p mkdir -p Also create data files and redo log files locations from source on clone server mkdir -p mkdir -p Take clone server database to nomount stage export ORACLE_SID=prod sqlplus / as sysdba SQL> startup nomount; SQL> exit; Cloning will fail if you do not exit from sqlplus Connect to rman on prod server to both prod database and also the auxiliary (clone) database rman target sys auxiliary sys@prod_clone --> give same sys password for both target and auxiliary Lets duplicate the database via RMAN duplicate from active database RMAN> duplicate target database to 'prod' from active database nofilenamecheck; Once cloning is done, you should see below On the clone server, check if the cloned database is open and running fine RMAN Duplicate to different SID We are going to clone prod database which is running on prod.dbagenesis.com host. We have another server clone.dbagenesis.com where only oracle software is installed and no database created. We will be using RMAN duplicate from active database to copy prod database on clone server with a different name (SID). Clone database SID will be different from source database Configure Listener on clone server to accept incoming connections LISTENER = (DESCRIPTION_LIST = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = clone.dbagenesis.com)(PORT = 1521)) ) ) SID_LIST_LISTENER = (SID_LIST = (SID_DESC = (ORACLE_HOME = /u01/app/oracle/product/12.2.0/db_home) (SID_NAME = clone) ) ) Start the listener on clone server lsnrctl start listener Configure tns entries on the prod server clone = (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = clone.dbagenesis.com)(PORT = 1521)) ) (CONNECT_DATA = (SERVICE_NAME = clone) ) ) Make sure to test the tnsping is working from prod to clone server tnsping clone On prod server, create pfile from spfile and copy to clone server SQL> create pfile from spfile; scp $ORACLE_HOME/dbs/initprod.ora oracle@clone.dbagenesis.com:$ORACLE_HOME/dbs/initclone.ora Also copy the password file from prod to clone server scp $ORACLE_HOME/dbs/orapwprod oracle@clone.dbagenesis.com:$ORACLE_HOME/dbs/orapwclone If no password file exists, create one via below command and then copy orapwd file=$ORACLE_HOME/dbs/orapwclone force=y On the clone server, open the pfile that you copied from prod server. Replace prod with clone SID vi $ORACLE_HOME/dbs/initclone.ora :%s/prod/clone --> replace prod with clone Add below two parameters to change data files and log files locations while cloning database *.db_file_name_convert='/u01/app/oracle/oradata/prod','/u01/app/oracle/oradata/clone' *.log_file_name_convert='/u01/app/oracle/oradata/prod','/oradb/app/oracle/oradata/clone' Save & exit Create respective directories from the clone pfile. In my case, I had to create below directories mkdir -p mkdir -p mkdir -p mkdir -p /u01/app/oracle/oradata/clone Also create data files and redo log files locations from source on clone server mkdir -p mkdir -p Take clone server database to nomount stage export ORACLE_SID=clone sqlplus / as sysdba SQL> startup nomount; SQL> exit; Connect to rman on prod server to both prod database and also the auxiliary (clone) database rman target sys auxiliary sys@clone --> give same sys password for both target and auxiliary Let's refresh or duplicate the database via RMAN duplicate from active database RMAN> duplicate target database to 'clone' from active database nofilenamecheck; Further Read Oracle active database cloning error Configure RMAN Default Channels Related Posts Heading 2 Add paragraph text. Click “Edit Text” to customize this theme across your site. You can update and reuse text themes.

Oracle 12c Installation on Oracle Linux 7 In this article, we will be looking at Oracle 12cR2 installation on Oracle Linux 7.7 version. The steps are exactly same for OEL 6 and all other versions of OEL 7.x Software Requirements Install Oracle Linux 7 Oracle Prerequisites Oracle 12cR2 GUI Install Update bash_profile Note: I prefer silent mode installation as it is quick and fast. Read more about Oracle 12c silent installation on Oracle Linux 7 Software Requirements We are going to setup OEL 7.7 virtual machine and install Oracle 12cR2 via silent method Oracle Enterprise Linux 7.7 Oracle Database 12cR2 Install Oracle Linux 7 For this demonstration purpose, I have used virtualbox VM with 4 GB RAM and 120 GB hard disk. I have setup the virtual machine with exact same steps described in the following article Install Oracle Linux on VirtualBox (OEL 7.7) Oracle Prerequisites Use the YUM repository to perform all the pre-install steps. Make sure your VM is able to ping google.com before executing below command yum install -y oracle-database-server-12cR2-preinstall Set password for the oracle user passwd oracle Create directories which will hold Oracle software installation mkdir -p /u01/app/oracle/product/12.2.0.1 chown -R oracle:oinstall /u01 chmod -R 775 /u01 Copy the 12cR2 software files under /u01 and unzip it su - oracle cd /u01 unzip linuxx64_12201_database.zip At this stage, you have two options to install Oracle software: GUI Mode & Silent Mode I will be walking you through GUI method of Oracle software installation. As mentioned earlier, read oracle 12cR2 silent installation article in case you would like to go with silent installation. Oracle 12cR2 GUI Install Start the runInstaller and follow the below screens to install Oracle 12cR2 software cd /u01/database ./runinstaller Uncheck to disable receiving security updates and click on next. You will get a warning, just click OK to proceed Select installation option as Install Database Software Only. Select database installation option as Single Instance database installation Go with Enterprise Edition Specify the Oracle installation location which is also known as ORACLE_HOME Go with default groups and just click on Next Install will perform pre-requisites check. If everything is good, then you should be presented with installation summary Once the installation is done, you will be prompted to run root scripts. Copy those scripts and run as root user on the same OEL machine. Update bash_profile At this stage, its a good idea to update Oracle user bash_profile to reflect ORACLE_HOME. Make sure you are at the Oracle user home location as oracle user ============== cd --> this command will take you to oracle user home location Open .bash_profile vi .bash_profile Delete everything and put below contents. Make sure to change ORACLE_HOME location as per your installation # .bash_profile # Get the aliases and functions if [ -f ~/.bashrc ]; then . ~/.bashrc fi # User specific environment and startup programs export ORACLE_HOME=/u01/app/oracle/product/12.2.0.1 export ORACLE_SID=testdb PATH=$PATH:$HOME/.local/bin:$ORACLE_HOME/bin export PATH Notice I have already put the ORACLE_SID variable. You must change it to the name of the database that you are going to create further. Further Read DBCA Silent Mode Oracle 12c Silent Installation on Linux Oracle 12cR2 Installation on Linux with ASM Related Posts Heading 2 Add paragraph text. Click “Edit Text” to customize this theme across your site. You can update and reuse text themes.

Oracle Golden Gate Interview Questions Here are some of the frequently asked Oracle Golden Gate interview questions and answers. Enjoy! Q. What is the significance of Oracle GoldenGate Manager? To give users control over Oracle GoldenGate processes, Manager provides a command line interface to perform a variety of administrative, housekeeping, and reporting activities, including Setting parameters to configure and fine-tune Oracle GoldenGate processes Starting, stopping, and monitoring capture and delivery modules Critical, informational event, and threshold reporting Resource management Trail File management Q. Why it is highly desirable that tables that you want to replicate should have primary key? In simple words, to uniquely identify a record GoldenGate requires a primary key. If the primary key does not exist on the source table, GoldenGate will create its own unique identifier by concatenating all the table columns together. This will certainly prove inefficient as volume of data that needs to be extracted from the redo logs will increase exponentially. In normal scenario, when a table has primary key, GoldenGate process will fetch only the primary key and the changed data (before and after images in the case of an update statement). GoldenGate process will also warn you that primary key does not exist on the target table and you may receive the following warning in the GoldenGate error log WARNING OGG-xxxx No unique key is defined for table ‘TARGET_TABLE_NAME’. All viable columns will be used to represent the key, but may not guarantee uniqueness. KEYCOLS may be used to define the key Having primary key also insure fast data lookup when the Replicat recreates and applies the DML statements against the target database. But keep in mind that it is not “mandatory” that primary key must be present for the table. Q. Is it MUST that the source database should be in archivelog mode? It is NOT must that the source database is in the archivelog mode but for any serious, mission-critical GoldenGate system it is almost mandatory to have source system in Archive Log mode. Q. Without going into details, explain high level steps of setting up GoldenGate. Below are the key steps to install and configure the Golden Gate Download the software from the Oracle website and upload to server Unpack/Unzip the installation zip file Prepare source and target system Install the software on the source and target system (for 12c use OUI) Prepare the source database (some DB parameters need to be adjusted) Configure the Manager process on the source and target system Configure the Extract process on the source system Configure the data pump process on the source system Configure the Replicat process on the target system Start the Extract process Start the data pump process Start the Replicat process Q. When creating GoldenGate database user for database 12c, what special precaution you need to take? You must grant the GoldenGate admin user access to all database containers on the source side so that GoldenGate can access the redo logs for all the databases (container and pluggable) You must also grant the DBA role with the container=all option. SQL> GRANT DBA TO C##GOLDENADMIN CONTAINER=ALL Q. What is Downstream capture mode of GoldenGate? Traditionally log mining work for the source data happens on Source database side but in Downstream capture mode Oracle Data Guard redo transport mechanism is used. This enables continuous log shipping to the target database’s standby redo logs in real time. Log mining work to fetch DDL/DML transactions happens on the target side. Q. How do you take backup of GoldenGate? Your source/database you can backup easily using backup tools like Oracle Recovery Manager (RMAN) but to backup the GoldenGate you will need to back up the GoldenGate home and subdirectories that contain the trail files, checkpoint files etc. Without these key files, GoldenGate will not be able to recover from the last checkpoint. It means that if somehow you lose all these key GoldenGate files then you will have no option but to go for a new initial load. RMAN simply do not have capability to backup the OS or no database files. So either you keep all your GoldenGate related files on some kind of SAN setup which gets backed up daily at storage level or use Unix shell commands etc in cron job to take filesystem backups. Q. What is checkpoint table? In which capture mode it is used: classic or integrated? Oracle GoldenGate extract and replicat processes perform checkpoint operations. Now in the event of some unexpected failure, the checkpoint file or database table ensures extract and replicat re-start from the point of failure and avoid re-capture and re-apply of transactions. So, Checkpoint table enables the checkpoint to be included within Replicat’s transaction, ensuring complete recovery from all failure scenarios. You use the GGSCI add checkpoint table command to create the checkpoint table. Checkpoint table is used for Classic capture/replicate mode. For Integrated mode, the Checkpoint table is not required and should not be created. Q. What transaction types does Golden Gate support for Replication? Goldengate supports both DML and DDL Replication from the source to target. Q. What are the supplemental logging pre-requisites? The following supplemental logging is required. Database supplemental logging Object level logging Q. Why is Supplemental logging required for Replication? Integrated Capture (IC) In the Integrated Capture mode, GoldenGate works directly with the database log mining server to receive the data changes in the form of logical change records (LCRs). IC mode does not require any special setup for the databases using ASM, transparent data encryption, or Oracle RAC. This feature is only available for oracle databases in Version 11.2.0.3 or higher. It also supports various object types which were previously not supported by Classic Capture. This Capture mode supports extracting data from source databases using compression. Integrated Capture can be configured in an online or downstream mode. Q. List the minimum parameters that can be used to create the extract process? The following are the minimum required parameters which must be defined in the extract parameter file. EXTRACT NAME USERID EXTTRAIL TABLE Q. I want to configure multiple extracts to write to the same exttrail file? Is this possible? Only one Extract process can write to one exttrail at a time. So, you can’t configure multiple extracts to write to the same exttrail. Q. What type of Encryption is supported in Goldengate? Oracle Goldengate provides 3 types of Encryption. Data Encryption using Blow fish. Password Encryption. Network Encryption. Q. What are some of the key features of GoldenGate 12c? The following are some of the more interesting features of Oracle GoldenGate 12c: Support for Multitenant Database Coordinated Replicat Integrated Replicat Mode Use of Credential store Use of Wallet and master key Trigger-less DDL replication Automatically adjusts threads when RAC node failure/start Supports RAC PDML Distributed transaction RMAN Support for mined archive logs Q. If have created a Replicat process in OGG 12c and forgot to specify DISCARDFILE parameter. What will happen? Starting with OGG 12c, if you don’t specify a DISCARDFILE OGG process now generates a discard file with default values whenever a process is started with START command through GGSCI. Q. Is it possible to start OGG EXTRACT at a specific CSN? Yes, Starting with OGG 12c you can now start Extract at a specific CSN in the transaction log or trail. Example: START EXTRACT fin ATCSN 12345 START EXTRACT finance AFTERCSN 67890 Q. List a few parameters which may help improve the replicat performance? Below are the parameters below can be used to improve the replicat performance: BATCHSQL GROUPTRANSOPS INSERTAPPEND Q. What are the most common reasons of an Extract process slowing down? Some of the possible reasons are Long running batch transactions on a table. Insufficient memory on the Extract side. Uncommitted, long running transactions can cause writing of a transaction to a temporary area (dirtmp) on disk. Once the transaction is committed it is read from the temporary location on the file system and converted to trail files. Slow or overburdened Network. Q. What are the most common reasons of the Replicat process slowing down? Some of the possible reasons are Large amount of transactions on a particular table. Blocking sessions on the destination database where non-Goldengate transactions are also taking place on the same table as the replicat processing. If using DBFS, writing & reading of trail files may be slow if SGA parameters are not tuned. For slow Replicat’s, latency may be due to missing indexes on target. Replicat having to process Update, delete of rows in very large tables. Q. My extract was running fine for a long time. All of a sudden it went down. I started the extract processes after 1 hour. What will happen to my committed transactions that occurred in the database during last 1 hour? OGG checkpoint provides the fault tolerance and make sure that the transaction marked for committed is capture and captured only once. Even if the extract went down abnormally, when you start the process again it reads the checkpoint file to provide the read consistency and transaction recovery. Q. I have configured Oracle GoldenGate integrated capture process using the default values. As the data load increases I see that extract starts lagging behind by an hour (or more) and database performance degrades. How you will resolve this performance issue? When operating in integrated capture mode, you must make sure that you have assigned sufficient memory to STREAMS_POOL_SIZE. An undersized STREAMS_POOL_SIZE or limiting the streams pool to use a specific amount of memory can cause troubles. The best practice is to allocate STREAMS_POOL_SIZE at the instance level and allocate the MAX. SGA at GG process level as below SQL> alter system set STREAMS_POOL_SIZE=3G TRANLOGOPTIONS INTEGRATEDPARAMS (MAX_SGA_SIZE 2048, PARALLELISM 4) Q. Why would you segregate the tables in a replication configuration? How would you do it? In OGG you can configure replicat at the data at the schema level or at the table level using TABLE parameter of extract and MAP parameter of replicat. For replicating the entire database, you can list all the schemas in the database in the extract/replicat parameter file. Depending the amount of redo generation, you can split the tables in a schema in multiple extracts and replicats to improve the performance of data replication. Alternatively, you can also group a set of tables in the configuration by the application functionality. Alternatively, you may need to remove tables which have long running transactions in a separate extract process to eliminate lag on the other tables. Let’s say that you have a schema named SCOTT and it has 100 hundred tables. Out of these hundred tables, 50 tables are heavily utilized by application. To improve the overall replication performance, you create 3 extracts and 3 replicats as follows: Ext_1/Rep_1 –> 25 tables Ext_2/Rep_2 –> 25 tables Ext_3/Rep_3 –> 50 tables Ext_1/Rep_1 and Ext_2/Rep_2 contains 25 table each which are heavily utilized or generate more redo. Ext_3/Rep_3 contains all the other 50 tables which are least used. Q. How do you view the data which has been extracted from the redo logs? The logdump utility is used to open the trail files and look at the actual records that have been extracted from the redo or the archive log files. Q. Why should I upgrade my GoldenGate Extract processes to Integrated Extract? Oracle is able to provide faster integration of the new database features by moving the GoldenGate Extraction processes into the database. Due to this, the GoldenGate Integrated Extract has a number of features like Compression which are not supported in the traditional Extract. You can read more about how to upgrade to Integrated Extract and more about Integrated Delivery. Going forward, preference should be give to create new extracts as Integrated Extracts and also to upgrade existing traditional Extracts . Q. What is the minimum Database version which supports Integrated Delivery? Oracle 11.2.0.4 is the minimum required database version that supports both Integrated extract and Integrated Reaplicat. Q. What databases supports GoldenGate Integrated Delivery? Oracle Integrated Delivery is only available for Oracle Databases. Q. With Integrated Delivery, where can we look for the performance stats? Yes with 12c, performance statistics are collected in the AWR repository and the data is available via the normal AWR reports. Q. What are the steps required to add a new table to an existing replication setup? The steps to be executed would be the following Include the new table to the Extract & pump process. Obtain starting database SCN and Copy the source table data to the target database Start Replicat on target at the source SCN database point. Q. What is the purpose of the DEFGEN utility? When the source and the target schema objects are not the same (different DDL’s) the Replicat process needs to know the source definition of the objects . The output from the DEFGEN utility is used in conjunction with the trail data to determine which column value in the trail belongs to which column. Q. We want to setup one-way data replication for my online transaction processing application. However, there are compressed tables in the environment. Please suggest how I can achieve it. You must use Oracle Golden Gate 11.2 and configure Golden Gate Integrated Capture process to extract data from compressed tables. Note: Pre OGG 11.2 doesn’t support extracting data from compressed tables Q. What are the different OGG Initial load methods available? OGG has 2 functionalities, one it is used for Online Data Replication and second for Initial Loading . If you are replicating data between 2 homogeneous databases then the best method is to use database specific method (Exp/Imp, RMAN, Transportable tablespaces, Physical Standby and so on). Database specific methods are usually faster than the other methods. —If you are replicating data between 2 heterogeneous databases or your replicat involves complex transformations, then the database specific method can’t be used. In those cases you can always use Oracle GoldenGate to perform initial load. Within Oracle GoldenGate you have 4 different ways to perform initial load. Direct Load – Faster but doesn’t support LOB data types (12c include support for LOB) Direct Bulk Load – Uses SQL*LOAD API for Oracle and SSIS for MS SQL SERVER File to replicat – Fast but the rmtfile limit is 2GB. If the table can’t be fit in 1 rmtfile you can use maxfiles but the replicat need to be registered on the target OGG home to read the rmtfiles from source. File to Database utility – depending on the target database, use SQL*LOAD for Oracle and SSIS for MS SQL SERVER and so on. Q. I have a table called ‘TEST’ on source and target with same name, structure and data type but in a different column order. How can you setup replication for this table? OGG by default assumes that the sources and target tables are identical. A table is said to be identical if and only if the table structure, data type and column order are the same on both the source and the target. If the tables are not identical you must use the parameter ‘SOURCEDEFS’ pointing to the source table definition and COLMAP parameter to map the columns from source to target. Q. What is the best practice to delete the extract files in OGG? Use the manager process to delete the extract files after they are consumed by the extract/replicat process PURGEOLDEXTRACTS /u01/app/oracle/dirdat/et*, USECHECKPOINTS, MINKEEPHOURS 2 Q. I have a one-way replication setup. The system administration team wants to apply an OS patch to both the OGG source host and the target servers. Provide the sequence of steps that you will carry before and after applying this patch. Check to make sure that the Extract has processed all the records in the data source (Online Redo/archive logs) GGSCI> send extract , logend (The above command should print YES) Verify the extract, pump and replicat has zero lag. GGSCI> send extract , getlag GGSCI> send extract , getlag GGSCI> send replicat , getlag (The above command should pring “At EOF, no more records to process.”) Stop all application and database activity. Make sure that the primary extract is reading the end of the redolog and that there is no LAG at all for the processes. Now proceed with stopping the processes on Source Stop the primary extract Stop the pump extract Stop the manager process Make sure all the processes are down On Target Stop replicat process Stop mgr Make sure that all the processes are down. Proceed with the maintenance After the maintenance, proceed with starting up the processes on Source Start the manager process Start the primary extract Start the pump extract (Or simply all the extract processes as GGSCI> start extract *) Make sure all that the processes are up. On Target Start the manager process Start the replicat process Make sure that all the processes are up Q. What are the basic resources required to configure Oracle GoldenGate high availability solution with Oracle Clusterware? There are 3 basic resources required Virtual IP Shared storage Action script Q. How can you determine if the parameters for a process was recently changed? Whenever a process is started, the parameters in the .prm file for the process is written to the process REPORT. You can look at the older process reports to view the parameters which were used to start up the process. By comparing the older and the current reports you can identify the changes in the parameters. Q. Is there a way to check the syntax of the commands in the parameter file without actually running the GoldenGate process? Yes, you can place the SHOWSYNTAX parameter in the parameter file and try starting. If there is any error you will see it. Q. What are macros? Macro is an easier way to build your parameter file. Once a macro is written it can be called from different parameter files. Common parameters like username/password and other parameters can be included in these macros. A macro can either be another parameter file or a library. Related Posts Heading 2 Add paragraph text. Click “Edit Text” to customize this theme across your site. You can update and reuse text themes.

Tablespace Point-in-time Recovery If you already know about database point in time recovery, the issue with the database point in time recovery (DBPITR) is that the database is not accessible to users. Why Tablespace PITR Recovery? How it works? RMAN TSPITR Lab Activity CREATE TEST TABLESPACE & USER FOR ACTIVITY DROP THE TABLESPACE AND START TBPIT PROCESS CONNECT TO RMAN AND START RECOVERY Why Tablespace PITR Recovery? Let us assume that you have a problem only with one user and the transaction that the user executed has impacted one table that reside under one tablespace or one data file. So rather than performing the entire database point in time recovery, We can perform single tablespace point in time recovery just before the transactions were issued. In this way, only the affected tablespace will not be available for the users. Rest all database will still be up and running. How it works? TBPITR is different from DBPITR In DBPITR, the entire database is restored to back in time. In TBPITR, we take the tablespace back in time or before the wrong transactions are issued. It’s not straight restore and recover Once tablespace is dropped, you cannot restore at via RMAN as details of the tablespace are removed from the control file. Let us say somebody dropped the entire tablespace and then you try to issue the command restore tablespace from RMAN. The command will not work because once you drop the tablespace, control files will update that this tablespace does not exist. This means even if you try to restore it from RMAN, the tablespace will not be restored. TSPITR Recovery Methods There are three methods which you can use to recover dropped tablespace: Create DB clone with PITR before tablespace drop, export tablespace from clone DB and import into original database Perform entire database Point In Time recovery. This lead to downtime + loss of data Use automated RMAN TSPITR method RMAN TSPITR Its completely automated method. You just have to run the command TBPITR and everything will be taken care. To perform RMAN TSPITR, you need following Timestamp or SCN when tablespace was dropped → You can get this information form alert log A location with same space as your database size On the same server you need another Mount point where a database of the same size will be cloned. Once you run the TSPITR, it will Create a clone of the database in the location that you specify and then it will export the tablespace, import it into original database and then clean the cloned instance. And all the steps are automatically done by RMAN. Note: Make sure the location that you choose for the auxiliary instance of the cloned instance should have the same space or required space as an prod DB. Lab Activity In this activity, we will be doing the tablespace point in time recovery. Let us start. CREATE TEST TABLESPACE & USER FOR ACTIVITY create tablespace TBPITR datafile '/u01/app/oracle/oradata/proddb/tbpitr.dbf' size 10M; create user tbtest identified by tbtest; alter user tbtest default tablespace TBPITR; grant connect, resource to tbtest; grant select on scott.emp to tbtest; Now connect sqlplus with tbtest user and create a new emp table by selecting scott.emp table SQL> conn tbtest/tbtest SQL> create table emp as select * from scott.emp; Take RMAN backup for the activity. I am taking database backup in a specific location backup database plus archivelog format '/u02/rman_bkp/proddb/proddb_%U'; Check the database size and make sure you have a destination with same or more free space. Query to check database size col "Database Size" format a20 col "Free space" format a20 col "Used space" format a20 select round(sum(used.bytes) / 1024 / 1024 / 1024 ) || ' GB' "Database Size" , round(sum(used.bytes) / 1024 / 1024 / 1024 ) - round(free.p / 1024 / 1024 / 1024) || ' GB' "Used space" , round(free.p / 1024 / 1024 / 1024) || ' GB' "Free space" from (select bytes from v$datafile union all select bytes from v$tempfile union all select bytes from v$log) used , (select sum(bytes) as p from dba_free_space) free group by free.p / DROP THE TABLESPACE AND START TBPIT PROCESS Now we will drop the tablespace and then we will see that we can recover it via RMAN or not. drop tablespace TBPITR including contents and datafiles; If you connect to tbtest user and query emp table, you will get below error SQL> conn tbtest/tbtest SQL> select * from emp: select * from emp * ERROR at line 1: ORA-00942: table or view does not exist Let us try to restore the tablespace using RMAN. Connect to RMAN and issue below command RMAN> restore tablespace TBPITR; ERROR: RMAN-20202: Tablespace not found in recovery catalog Reason behind the above error is that you control file is already been updated that this tablespace does not belong. Even though you have the backup, still you will not be able to restore as it is cleared from your control file. Open the alert log and check the time when the tablespace was dropped. In my activity, the timestamp is Mon Jan 16 11:01:542017 We will change this time as Mon Jan 16 11:01: 53 2017, One second before the time when the tablespace is dropped. Now we will restore this tablespace till this time. CONNECT TO RMAN AND START RECOVERY Before you fire below command, make sure you have same or more space in auxiliary destination as your original database. The auxiliary destination is the location where RMAN will automatically create the destination for the clone instance. Then export the tablespace from the cloned instance. It will put it back to the original location and then RMAN will clean this auxiliary destination also. run{ recover tablespace TBPITR until time "to_date('16-jan-17 11:01:53','dd-mon-rr hh24:mi:ss')" auxiliary destination '/u02/auxdest'; } When you fire the above command, RMAN will automatically decide one dummy instance name and start that instance. Then it will restore all the data files. It will create a database. DB will be created as per the given time which is just before the tablespace was dropped. So the tablespace will be there inside the DB. Then it will export the tablespace and put it into the production database. Once the DB clone is done. Then it will start the export of the tablespace. Once the export is done, it will start to import the tablespace into the production DB. Then it will delete the cloned files and instances. Check the original database if the tablespace restore is done or not select status,tablespace_name from dba_tablespaces; Once tablespace restore is done, it will be in offline mode. Make it online alter tablespace TBPITR online; select status,tablespace_name from dba_tablespaces; Now you can check whether you are able to query from the emp table as tbtest user. You will able to do it. Related Posts Heading 2 Add paragraph text. Click “Edit Text” to customize this theme across your site. You can update and reuse text themes.

Neo4j

Setup glogin.sql in Oracle Let's take you want to execute some sql commands every time you login to SQL*PLUS. It would be so hard to run those commands manually whenever you start SQL*PLUS. With glogin.sql you can specify the commands you want to run every time you invoke SQL*PLUS. Any query, script, parameter, PL/SQL code etc, you can simply put under glogin.sql file and SQL*PLUS will first execute glogin.sql before allowing you to query database. I would like to display below message every time a user logs into the database Open the glogin.sql file vi $ORACLE_HOME/sqlplus/admin/glogin.sql Put below query at the end of the file select 'Welcome, you are connected to '||name||' database' "Hello" from v$database; Save and close. That's it, now whenever you login to SQL*PLUS, you will be greeted with a nice welcome message! Related Posts Heading 2 Add paragraph text. Click “Edit Text” to customize this theme across your site. You can update and reuse text themes.

Wait events vs wait classes Did you know that all the wait events inside Oracle database are categorized into wait classes? Every wait event always belongs to a wait class. Even before you learn more about each and every wait event, you must be aware of wait classes. Wait Classes in Oracle Find Top Wait Classes Find Top Wait Events in a Wait Class Oracle wait events are time where a sessions is waiting for something to happen Wait Classes in Oracle All the wait events are grouped under wait classes and here are the most important wait classes you must know. Administrative: Waits resulting from DBA commands that cause users to wait (for example, an index rebuild) Application: Waits resulting from user application code (for example, lock waits caused by row level locking or explicit lock commands) Cluster: Waits related to Real Application Cluster resources (for example, global cache resources such as gc cr block busy Commit: This wait class only comprises one wait event – wait for redo log write confirmation after a commit (that is, ‘log file sync’) Concurrency: Waits for internal database resources (for example, latches) Configuration: Waits caused by inadequate configuration of database or instance resources (for example, undersized log file sizes, shared pool size) Idle: Waits that signify the session is inactive, waiting for work (for example, ‘SQL*Net message from client’) Network: Waits related to network messaging (for example, ‘SQL*Net more data to dblink’) Other: Waits which should not typically occur on a system (for example, ‘wait for EMON to spawn’) Scheduler: Resource Manager related waits (for example, ‘resmgr: become active’) System I/O: Waits for background process IO (for example, DBWR wait for ‘db file parallel write’) User I/O: Waits for user IO (for example ‘db file sequential read’) Find Top Wait Classes User below query to get the top wait classes in Oracle database Select wait_class, sum(time_waited), sum(time_waited)/sum(total_waits) Sum_Waits From v$system_wait_class Group by wait_class Order by 3 desc; Find Top Wait Events in a Wait Class From the above query, supply each wait class into below query to get the top wait events in database with respect to particular wait class Select a.event, a.total_waits, a.time_waited, a.average_wait From v$system_event a, v$event_name b, v$system_wait_class c Where a.event_id=b.event_id And b.wait_class#=c.wait_class# And c.wait_class = '&Enter_Wait_Class' order by average_wait desc; Related Posts Heading 2 Add paragraph text. Click “Edit Text” to customize this theme across your site. You can update and reuse text themes.