top of page

Results found for ""

  • Oracle Data Guard Physical Standby Configuration

    Oracle Data Guard Physical Standby Configuration There are various steps in which you can configure physical standby database . We need to make several changes to the primary database before we can even setup the standby database. In this article, we will set up physical standby on Oracle Linux 7.6 server. Physical Standby Configuration Overview Primary database changes Configure network Configure redo transport Build standby Verify standby configuration This article applies to Oracle 12c R2 database version Physical Standby Configuration Overview Primary details SID: ip7 ORACLE_HOME: /u01/app/oracle/product/12.2.0.1 Host Name: srv1.dbagenesis.com Standby details SID: ip7 ORACLE_HOME: /u01/app/oracle/product/12.2.0.1 Host Name: srv2.dbagenesis.com Assumption: we assume that primary server has a database (SID=ip7) up and running. The standby database has Oracle 12cR2 installation done in the same oracle home location as primary. Primary database changes Primary must run in archive log mode. Check the archive log mode SELECT log_mode FROM v$database; LOG_MODE ------------ NOARCHIVELOG If it is not running in archive log mode, then enable it SQL> shutdown immediate SQL> startup mount SQL> alter database archivelog; SQL> alter database open; SQL> archive log list; Enable force logging on primary : In oracle, users can restrict redo generation for SQL by using NOLOGGING clause. This NOLOGGING transaction will be a problem for physical standby. Hence, we force logging so even user uses NOLOGGING clause, every SQL will be logged on to redo SQL> alter database force logging; SQL> select name, force_logging from v$database; Standby file management: We need to make sure whenever we add/drop datafile in primary database, those files are also added / dropped on standby SQL> alter system set standby_file_management = 'AUTO'; Create standby log files: You must create standby log files on primary. These files are used by a standby database to store redo it receives from primary database. Our primary may become standby later and we would need them, so better to create it. First check the current log groups SQL> select GROUP#, THREAD#, bytes/1024/1024, MEMBERS, STATUS from v$log; GROUP# THREAD# BYTES/1024/1024 MEMBERS STATUS ---------- ---------- --------------- ---------- ---------------- 1 1 200 1 INACTIVE 2 1 200 1 CURRENT 3 1 200 1 INACTIVE SQL> select member from v$logfile; MEMBER --------------------------------------------------- /u01/data/db_files/ip7/redo03.log /u01/data/db_files/ip7/redo02.log /u01/data/db_files/ip7/redo01.log Add the standby logfiles, make sure group number should be from a different series like in this case we choose to start with 11 and above. This helps in easy differentiation. Make sure to keep the thread# and logfile size exactly same. Oracle also recommends to always create n+1 standby log files. Where n is the total number of logfiles ALTER DATABASE ADD STANDBY LOGFILE THREAD 1 GROUP 11 '/u01/data/db_files/ip7/stb_redo1.log' SIZE 200M; ALTER DATABASE ADD STANDBY LOGFILE THREAD 1 GROUP 12 '/u01/data/db_files/ip7/stb_redo2.log' SIZE 200M; ALTER DATABASE ADD STANDBY LOGFILE THREAD 1 GROUP 13 '/u01/data/db_files/ip7/stb_redo3.log' SIZE 200M; ALTER DATABASE ADD STANDBY LOGFILE THREAD 1 GROUP 14 '/u01/data/db_files/ip7/stb_redo4.log' SIZE 200M; Check the standby log files via below query SQL> SELECT GROUP#,THREAD#,SEQUENCE#,ARCHIVED,STATUS FROM V$STANDBY_LOG; Enable flashback on primary: Flashback database is highly recommended because in case of failover, you need not re-create primary database from scratch SQL> alter system set db_recovery_file_dest_size=45g; SQL> alter database flashback on; SQL> select flashback_on from v$database; If flashback parameters are not set properly, use below commands SQL> show parameter recovery; SQL> alter system set db_recovery_file_dest='/u01/app/oracle/fast_recovery_area'; SQL> alter system set db_recovery_file_dest_size=45g; SQL> alter database flashback on; Check DB Unique name parameter on primary: Make sure your primary database has DB_UNIQUE_NAME parameter set for consistency. If it’s not set properly, use ALTER SYSTEM SET command SQL> show parameter db_name NAME TYPE VALUE ------------------------------------ ----------- ------------- db_name string ip7 SQL> show parameter db_unique_name NAME TYPE VALUE ------------------------------------ ----------- ------------- db_unique_name string ip7 Configure network Use below tns entries and put them under ORACLE user HOME/network/admin/tnsnames.ora. Change host as per your environment and execute on both primary and standby. Notice the use of the SID, rather than the SERVICE_NAME in the entries. This is important as the broker will need to connect to the databases when they are down, so the services will not be present. vi $ORACLE_HOME/network/admin/tnsnames.ora ip7 = (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = srv1.dbagenesis.com)(PORT = 1521)) ) (CONNECT_DATA = (SID = ip7) ) ) ip7_stb = (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = srv2.dbagenesis.com)(PORT = 1521)) ) (CONNECT_DATA = (SID = ip7) ) ) Configure listener on primary database. Since the broker will need to connect to the database when it’s down, we can’t rely on auto-registration with the listener, hence the explicit entry for the database. vi $ORACLE_HOME/network/admin/listener.ora LISTENER = (DESCRIPTION_LIST = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = srv1.dbagenesis.com)(PORT = 1521)) ) ) SID_LIST_LISTENER = (SID_LIST = (SID_DESC = (GLOBAL_DBNAME = ip7_DGMGRL) (ORACLE_HOME = /u01/app/oracle/product/12.2.0.1) (SID_NAME = ip7) ) ) ADR_BASE_LISTENER = /u01/app/oracle Configure listener on standby. Since the broker will need to connect to the database when it’s down, we can’t rely on auto-registration with the listener, hence the explicit entry for the database. vi $ORACLE_HOME/network/admin/listener.ora LISTENER = (DESCRIPTION_LIST = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = srv2.dbagenesis.com)(PORT = 1521)) ) ) SID_LIST_LISTENER = (SID_LIST = (SID_DESC = (GLOBAL_DBNAME = ip7_stb_DGMGRL) (ORACLE_HOME = /u01/app/oracle/product/12.2.0.1) (SID_NAME = ip7) ) ) ADR_BASE_LISTENER = /u01/app/oracle Once the listener.ora changes are in place, restart the listener on both servers lsnrctl stop lsnrctl start Configure redo transport Note: if you plan to use Oracle Data Guard broker, then you can skip this section “configure redo transport” and jump to “Build Standby” section. Configure redo transport from primary to standby: The below statement says that if the current database is in primary role, then transport logs to standby. We need to change service and db_unique_name for same parameter on standby server On Primary Server ================= SQL> alter system set log_archive_dest_2 = 'service=ip7_stb async valid_for=(online_logfiles,primary_role) db_unique_name=ip7_stb'; Set FAL_SERVER: Fetch Archive Log parameter tells primary as to where it will get archives from On Primary Server ================= SQL> alter system set fal_server = 'ip7_stb'; Set dg_config parameter: This parameter defines which databases are in data guard configuration On Primary Server ================= SQL> alter system set log_archive_config = 'dg_config=(ip7,ip7_stb)'; Build standby Create pfile on primary, open it and create the necessary directories on the standby server On Primary Server ================= SQL> create pfile from spfile; exit $ cd $ORACLE_HOME/dbs $ cat initip7.ora On Standby Server ================= Create directories as you find in the initip7.ora file mkdir -p /u01/app/oracle/admin/ip7/adump mkdir -p /u01/data/db_files/ip7 mkdir -p /u01/FRA/ip7 On standby server, create parameter file with just db_name parameter and start the instance in nomount mode On standby server ================= vi $ORACLE_HOME/dbs/initip7.ora *.db_name='ip7' $ export ORACLE_SID=ip7 $ sqlplus / as sysdba SQL> STARTUP NOMOUNT; SQL> exit; --you must exit from sqlplus, else cloning will fail Copy password file: Copy the password file from primary to standby server $ scp orapwip7 oracle@srv2:$ORACLE_HOME/dbs If no password file exists, create one via below command and then copy orapwd file=$ORACLE_HOME/dbs/orapwip7 force=y Duplicate primary database via RMAN: On primary, connect to RMAN, specifying a full connect string for both the TARGET and AUXILIARY instances. Do not attempt to use OS authentication else, the cloning will fail To DORECOVERY option starts recovery by applying all available logs immediately after restore On primary server ================= rman target sys@ip7 auxiliary sys@ip7_stb RMAN> DUPLICATE TARGET DATABASE FOR STANDBY FROM ACTIVE DATABASE DORECOVER SPFILE SET db_unique_name='ip7_stb' SET fal_server='ip7' SET log_archive_dest_2='service=ip7 async valid_for=(online_logfiles,primary_role) db_unique_name=ip7' NOFILENAMECHECK; Once cloning is done, you should see below at RMAN prompt Finished Duplicate Db at 07-DEC-2015 Enable flashback on standby: As we know the importance of flashback in data guard, we must enable it on standby as well On Standby Server ================= SQL> alter database flashback on; Bounce database & start MRP (Apply Service): It's good to bounce standby, put it in mount mode and start MRP process On Standby Server ================= SQL> shut immediate; SQL> startup mount; SQL> alter database recover managed standby database disconnect; Verify standby configuration Once MRP is started, we must verify which archive log number MRP is applying on standby On standby: =========== select process, status, sequence# from v$managed_standby; select sequence#, applied, first_time, next_time, name filename from v$archived_log order by sequence#; Below queries will help you identify issues when your data guard setup is out of sync On both primary & standby: ========================== set lines 999; select * from v$dataguard_status order by timestamp; select dest_id, status, destination, error from v$archive_dest where dest_id<=2; IF you see ORA-16058, do this on primary: ========================================= SQL> alter system set log_archive_dest_state_2='DEFER'; SQL> alter system set log_archive_dest_state_2='ENABLE'; SQL> select dest_id, status, destination, error from v$archive_dest where dest_id<=2; On primary: =========== select sequence#, first_time, next_time, applied, archived from v$archived_log where name = 'ip7_stb' order by first_time; select STATUS, GAP_STATUS from V$ARCHIVE_DEST_STATUS where DEST_ID = 2; archive log list; Configure Archive deletion policy: We must set this policy in order to prevent accidental deletion of archive logs on primary database On Primary: =========== rman target / configure archivelog deletion policy to applied on all standby; Related Posts Heading 2 Add paragraph text. Click “Edit Text” to customize this theme across your site. You can update and reuse text themes.

  • Linux, Database, Cloud articles

    Get Instant Database Support Oracle 19c Installation on Linux Oracle 19c adds more features to Oracle database and this article demonstrates Oracle 19c installation on Linux 7. We will be using Oracle Linux 7 and Oracle Database 19.3 softwares for this activity. We will also be looking at database creation via DBCA. FEATURED ARTICLES Most Popular Articles New to DBA Genesis Articles? Start with our most-viewed guides and resources. I am a title 01 I am a title 02 I am a title 03 View all Performance Tuning Articles Tune your Oracle database for high performance and low latency I am a title 01 I am a title 02 I am a title 03 View all Oracle Data Guard Articles Keep you primary in sync with standby always I am a title 01 I am a title 02 I am a title 03 View all Oracle RAC Articles Oracle RAC enables high availability and scalability with seamless failover across multiple servers. I am a title 01 I am a title 02 I am a title 03 View all Oracle Golden Gate Articles Oracle GoldenGate ensures real-time data integration and replication for heterogeneous systems I am a title 01 I am a title 02 I am a title 03 View all Stay Ahead in the World of Database and Cloud Script to create JUSTLEE schema in Oracle Here is the script to create JUSTLEE schema in Oracle Connect Linux Instance with SSH Keys This article provides a step-by-step guide on establishing a secure connection to a Linux server using SSH keys. It covers the process for both Windows using PuTTY, and MacOS using the Terminal. SQL Project - Library Management The goal of the project is to create a database for a library management system that allows users to efficiently manage customer information, book inventory & book borrowings SQL Project - Supermarket Billing The goal of the project is to create a database for a supermarket to efficiently manage inventory, sales, and customer information. Linux Project - Monitor Server Disk Space In this Linux project we will write a shell script that will monitor the disk space and put the info in a file every 20 mints. Setting up... Install Oracle 19c with ASM In this article we would be looking at installing Oracle 19c with ASM on Linux. This will allow us to create 19c databases on ASM disks.... Oracle 21c Installation on Linux Oracle 21c is an innovation release that provides early insights to latest developments and features. Move Spfile to ASM With ASM configured for RAC or NON-RAC systems, it is a good idea to move the spfile to ASM. The PFILE under $ORACLE_HOME/dbs location... Oracle Database 19c RPM-based Installation On Oracle Linux 7 Oracle Database 19c RPM-based installation on Oracle Linux 7 allows customers to quickly and easily install and configure the Oracle... MySql 8.0 on OEL 7.9 This article demonstrates MySQL 8.0.31 installation on OEL 7.9 version. Now, we will use WGET utility to download the RPM. wget... PostgreSQL15 on CentOS 7 PostgreSQL is a powerful, open-source object-relational database system with over 30 years of active development that has earned it a... MySQL 8.0 on CentOS 7 This article demonstrates MySQL installation on Cent OS Linux 7 version. Installation of MySQL Install wget to download MySQL repository...

  • Oracle Export Import Utility

    Oracle Export Import Utility In this article, we will learn about the traditional Oracle Export and Import utility Oracle Export Oracle Import Oracle Export To know options of export $ exp help=y To take full database level export $ exp file=/u01/fullbkp_prod.dmp log=/u01/fullbkp_prod.log full=y To take schema level export $ exp file=/u01/scott_bkp.dmp log=/u01/scott_bkp.log owner='SCOTT' To take table level export $ exp file=/u01/emp_bkp.dmp log=/u01/emp_bkp.log tables='SCOTT.EMP' To take row level export $ exp file=/u01/emp_rows_bkp.dmp log=/u01/emp_rows.log tables='SCOTT.EMP' query=\"where deptno=10\" Oracle Import To know options of import $ imp help=y To import full database $ imp file=/u01/fullprod.dmp log=/u01/imp_fullprod.log full= To import a schema $ imp file=/u01/scott_bkp.dmp log=/u01/imp_schema.log fromuser='SCOTT' touser='SCOTT' To import a table $ imp file=/u01/emp_bkp.dmp log=/u01/imp_emp.log fromuser='SCOTT' touser='SCOTT' tables='EMP' To import a table to another user $ imp file=/u01/emp_bkp.dmp log=/u01/imp_emp.log fromuser='SCOTT' touser='SYSTEM' tables='EMP' 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 Database Auditing

    Oracle Database Auditing Oracle database auditing allows you to monitor certain database actions happening inside the database. Auditing also helps in looking at the actions performed against a particular table, schema or certain specific rows. You can check database auditing is enabled via SHOW PARAMETER command Show parameter audit; The AUDIT_TRAIL parameter defines database auditing status. It can take any of the following values none - Database auditing is disabled os - Enabled, audit logs are stored at OS level, not inside the database db - Enabled, audit records are stored inside database (SYS.AUD$ table) db,extended - Same as db but populates SQL_BIND & SQL_TEXT too xml - Enabled, audit records are stored at OS level in XML format xml,extended - Same as xml but populates SQL_BIND & SQL_TEXT too Default is DB if you create database via DBCA, else its NONE The AUDIT_FILE_DEST parameter defines the OS level location of the audit trail files. By default it is set to adump location. The AUDIT_SYS_OPERATIONS parameter defines auditing is enabled or disabled for any user connecting to database as SYSDBA. This is enabled by default. All the SYS operations audit records goes to OS level into AUDIT_FILE_DEST location. Move AUD$ Table to Another Tablespace By default the SYS.AUD$ (stores db audit records) and SYS.FGA_LOG$ (stores fine grain audit records) tables resides under SYSTEM tablespace col owner for a10; col segment_name for a10; col tablespace_name for a15; select owner, segment_name, segment_type, tablespace_name, bytes/1024/1024 as MB from dba_segments where segment_name in ('AUD$','FGA_LOG$'); The DBMS_AUDIT_MGMT package allows you to change the default tablespace. Below query moves AUD$ & FGA_LOG$ tables to USERS tablespace BEGIN DBMS_AUDIT_MGMT.SET_AUDIT_TRAIL_LOCATION( audit_trail_type => DBMS_AUDIT_MGMT.AUDIT_TRAIL_DB_STD, audit_trail_location_value => 'USERS'); END; / If you want to only move AUD$ table then replace audit_trail_type with audit_trail_type => DBMS_AUDIT_MGMT.AUDIT_TRAIL_AUD_STD If you want to only move FGA_LOG$ table then replace audit_trail_type with audit_trail_type => DBMS_AUDIT_MGMT.AUDIT_TRAIL_FGA_STD 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.

  • Oracle 21c Installation on Linux

    Oracle 21c Installation on Linux With Oracle 12c, 18c and 19c, you are allowed to create single instance and multitenant databases. Starting from Oracle 21c, Multitenant database is the only supported architecture. Let's install and create Oracle 21c database on Oracle Linux 7. Oracle 21c Pre-requisites Download and Install Oracle 21c Create 21c Multi-tenant Database Oracle 21c Pre-requisites Install the Oracle 21c pre-install package from YUM repository yum install -y oracle-database-preinstall-21c Set password for Oracle user passwd oracle Create 21c home directory and give ownership to Oracle user mkdir -p /u01/app/oracle/product/21.0/db_home chown -R oracle:oinstall /u01 chmod -R 775 /u01 Setup Oracle user bash_profile su - oracle vi .bash_profile Add the ORACLE_SID, ORACLE_BASE, ORACLE_HOME and update PATH variable. Your .bash_profile must look like below # .bash_profile # Get the aliases and functions if [ -f ~/.bashrc ]; then . ~/.bashrc fi # User specific environment and startup programs export ORACLE_SID=CDB export ORACLE_BASE=/u01/app/oracle export ORACLE_HOME=/u01/app/oracle/product/21.0/db_home PATH=$PATH:$HOME/.local/bin:$ORACLE_HOME/bin export PATH Export bash profile . .bash_profile Download and Install Oracle 21c Download the Oracle Database 21c (21.3) for Linux x86-64 software and place the download zip file under $ORACLE_HOME location cd $ORACLE_HOME unzip LINUX.X64_213000_db_home.zip Start the runInstaller in silent mode to install 21c ./runInstaller -ignorePrereq -waitforcompletion -silent \ -responseFile ${ORACLE_HOME}/install/response/db_install.rsp \ oracle.install.option=INSTALL_DB_SWONLY \ ORACLE_HOSTNAME=${HOSTNAME} \ UNIX_GROUP_NAME=oinstall \ INVENTORY_LOCATION=/u01/app/oraInventory \ SELECTED_LANGUAGES=en,en_GB \ ORACLE_HOME=${ORACLE_HOME} \ ORACLE_BASE=${ORACLE_BASE} \ oracle.install.db.InstallEdition=EE \ oracle.install.db.OSDBA_GROUP=dba \ oracle.install.db.OSBACKUPDBA_GROUP=dba \ oracle.install.db.OSDGDBA_GROUP=dba \ oracle.install.db.OSKMDBA_GROUP=dba \ oracle.install.db.OSRACDBA_GROUP=dba \ SECURITY_UPDATES_VIA_MYORACLESUPPORT=false \ DECLINE_SECURITY_UPDATES=true Run the root scripts post installation /u01/app/oraInventory/orainstRoot.sh /u01/app/oracle/product/21.0/db_home/root.sh Create 21c Multi-tenant Database Let's create a multi-tenant database with two PDBs by running DBCA in silent mode dbca -silent -createDatabase \ -templateName General_Purpose.dbc \ -gdbname ${ORACLE_SID} -sid ${ORACLE_SID} \ -characterSet AL32UTF8 \ -sysPassword enterCDB#123 \ -systemPassword enterCDB#123 \ -createAsContainerDatabase true \ -totalMemory 2000 \ -storageType FS \ -datafileDestination /u01/${ORACLE_SID} \ -emConfiguration NONE \ -numberOfPDBs 2 \ -pdbName PDB \ -pdbAdminPassword enterPDB#123 \ -ignorePreReqs Once the database is created, check the status of all the containers sqlplus / as sysdba SELECT NAME, OPEN_MODE, CDB FROM V$DATABASE; SELECT CON_ID, NAME, OPEN_MODE FROM V$CONTAINERS; Related Posts Heading 2 Add paragraph text. Click “Edit Text” to customize this theme across your site. You can update and reuse text themes.

  • OEM 13c Software Library

    OEM 13c Software Library Oracle Software Library (Software Library) is one of the core features offered by Enterprise Manager Cloud Control. It is a repository that stores software entities such as software patches, virtual appliance images, reference gold images, application software, and their associated directive scripts. It also enables you to maintain versions, maturity levels, and states of these software entities. To access the Software Library console page, from the Enterprise menu, select Provisioning and Patching, then click Software Library. On the Software Library home page, there are two types of folders: Oracle-owned folders (marked by a lock symbol) and User-owned folders. Software Library supports patching and provisioning in Online mode and Offline mode. Software Library allows you to organize the entities, which basically refer to the software binaries or directive scripts in your enterprise, into logical folders for efficient management. To start using the Software Library, you must add at least one upload file storage location (OMS Shared File System, or OMS Agent File System) on the host where the OMS is running. A storage location in Software Library represents a repository of files that are either uploaded to Software Library or referenced by it. To access the administration console, log in to Enterprise Manager Cloud Control with Administration access, and from the Setup menu, select Provisioning and Patching, and then click Software Library. There are three types of storage locations are available Upload File Locations Referenced File Location Cache Nodes For our demonstration, we are using Upload File Location. We have to create the folder first, to create the folder we have to click on Action and select create a folder. Now, we have to enter the name of the folder ( it must be a unique name) and select the parent folder from below and click on ok. After creating the folder we have to add the software, so in order to do this, we will select the relevant option from the list. Click on the Action button and click on the Create Entity and then select Component. After clicking on the Component the drop-down will come and we can select any option according to our requirement. Here I am proceeding with Generic Component. After selecting Generic Component, we have to fill in the details related to our software. and click on NEXT. Note: The component name must be unique to the parent folder that it resides in. Sometimes even when you enter a unique name, it may report a conflict, this is because there could be an entity with the same name in the folder that is not visible to you, as you do not have view privilege on it. Click +Add to attach files that describe the entity better such as readme, collateral, licensing, and so on. Ensure that the file size is less than 2 MB. In the Notes field, including information related to the entity like changes being made to the entity or modification history that you want to track. On this page, you can customize the generic component that you are creating by adding some new properties or updating the existing properties of the component. On the Select Files page, you can select one or more files to be associated with the entity. Select upload file and click on the icon on Upload Location and select the desired location. Now select the Agent Machine from the Specify Source option and select the Agent Machine. In my case, I am using the OEM as I have the software there. and click on the select button. Now, we will click on Add button and provide the Login Details of the Agent Machine. As in my case, I selected the OEM as agent my named credentials are already saved, click on OK. Now we have to navigate to the location where we have the software and select the software and click on the +Add button, after a click on Add it will reflect on Selected Files Or Folders section, click on OK. Here we will see at the bottom of the page out the software with their size is refecting. Now we are good to click on NEXT. On the Set Directives page, click Choose Directives to associate a component with one or more directives. Click Next. On the Review page, review all the details, and click Save and Upload to create the component, and upload it on the Software Library. Stage the Software to the Agent We have to click on Action and select stage Entity Then select the Add and the on the pop enter the entity name to search and from the search list select the entity and click on select. Now, we have to click on ADD button in the stage destination section. and select the Agent, In my case, I am selecting clone and then click on select. After that, we have to provide the location and the named credential of the Agent Machine. and click on OK After reviewing click on submit button. Now it will verify our inputs And it will process the transfer to the Agent location. 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 Fast Recovery Area (FRA)

    Oracle Fast Recovery Area (FRA) Oracle FRA location is to simplify the backup and recovery management for the Oracle database. Flash Recovery Area can be defined as a single, centralized, unified storage area that keep all the database backup & recovery related files and performs those activities in Oracle databases. Note: previous to 11gR2, FRA was known as Flash Recovery Area. From 11gR2 onwards, its known as Fast Recovery Area. You can (must) define the FRA location during the database creation using DBCA. This is recommended by Oracle and it helps in simplifying database administration. Reasons to configure FRA Reduces and simplifies database administration tasks Oracle takes care of managing files inside the FRA All backups are placed in one central location Automatically cleanup of old backups under FRA location Manages Oracle flashback logs Stores RMAN backups, control file, redo logs and archive logs The fast recovery area is an Oracle-managed directory, file system, or Oracle Automatic Storage Management disk group that provides a centralized storage location for backup and recovery files. Oracle creates archived logs and flashback logs in the fast recovery area. Oracle Recovery Manager (RMAN) can store its backup sets and image copies in the fast recovery area, and it uses it when restoring files during media recovery. Oracle Database automatically manages this storage, deleting files that are no longer needed. When you issue the RMAN BACKUP command without specifying a backup destination, RMAN automatically backs up to the fast recovery area if it is configured. Configure FRA If you did not specify a location for the fast recovery area during installation, the installation process automatically configures a fast recovery area in the Oracle base directory. Oracle recommends, however, that the fast recovery area be located on a separate storage device from the database files. Assume that you want to want to place the fast recovery area in the directory /u02/oracle/fra and you want its size to have an upper limit of 10 GB. Specify the size of the fast recovery area using the following command ALTER SYSTEM SET DB_RECOVERY_FILE_DEST_SIZE = 10G; Specify the location of the fast recovery area using the following command: ALTER SYSTEM SET DB_RECOVERY_FILE_DEST = '/u02/oracle/fra'; Note: The DB_RECOVERY_FILE_DEST_SIZE parameter must be set before the DB_RECOVERY_FILE_DEST parameter. Related Posts Heading 2 Add paragraph text. Click “Edit Text” to customize this theme across your site. You can update and reuse text themes.

  • Check FRA location utilization

    Check FRA location utilization Fast Recovery Area must be monitored regularly. Sometimes FRA runs our of space and a DBA must be able to gather FRA space utilization. It is very important to monitor space usage in the fast recovery area to ensure that it is large enough to contain backups and other recovery-related files. Below script gives you Fast Recovery Area utilization details set linesize 500 col NAME for a50 select name, ROUND(SPACE_LIMIT/1024/1024/1024,2) "Allocated Space(GB)", round(SPACE_USED/1024/1024/1024,2) "Used Space(GB)", round(SPACE_RECLAIMABLE/1024/1024/1024,2) "SPACE_RECLAIMABLE (GB)" , (select round(ESTIMATED_FLASHBACK_SIZE/1024/1024/1024,2) from V$FLASHBACK_DATABASE_LOG) "Estimated Space (GB)" from V$RECOVERY_FILE_DEST; 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 Schema Replication

    Oracle Golden Gate Schema Replication Oracle Golden Gate Schema Replication means we will be replicating all the tables of a schema. Here we will be looking at two scenarios: replication for new schema and replication for an existing schema Golden Gate New Schema Replication Golden Gate Existing Schema Replication Golden Gate New Schema Replication Imagine there is a new blank schema which is being created on source database. Client wants to setup replication for same schema on target server. Below are the client requirements: All DML replications from source to target All DDL replications from source to target The source schema is E6P and target schema is also E6P As per the client requirement, it is a new schema which is being created on source. Hence, no initial load involved here. We will be directly setting up the change sync between source and target database . Setup Schema Replication Create E6P schema on both source and target server with below permissions On proddb: ========== Create user e6p identified by e6p; Grant connect, resource to e6p; Alter user e6p quota unlimited on users; On devdb: ========= Create user e6p identified by e6p; Grant connect, resource to e6p; Alter user e6p quota unlimited on users; Till now we have always added table level supplemental logging. Now it’s time to add schema level supplemental logging On proddb: ========== GGSCI (ggprod) 2> add schematrandata e6p INFO OGG-01788 SCHEMATRANDATA has been added on schema e6p. INFO OGG-01976 SCHEMATRANDATA for scheduling columns has been added on schema e6p. Add extract on source with below details On proddb: ========== GGSCI> ADD EXTRACT e6pex1, INTEGRATED TRANLOG, BEGIN NOW EXTRACT (Integrated) added. GGSCI> register extract e6pex1 database  only needed in integrated capture mode GGSCI> add exttrail /u01/app/oracle/product/gg/dirdat/e6, extract e6pex1 GGSCI> edit param e6pex1 EXTRACT e6pex1 USERID ogg, PASSWORD ogg EXTTRAIL /u01/app/oracle/product/gg/dirdat/e6 DDL INCLUDE ALL TABLE E6P.*; Add data pump on source On proddb: ========== GGSCI> Add extract e6pdp1, EXTTRAILSOURCE /u01/app/oracle/product/gg/dirdat/e6 GGSCI> Add rmttrail /u01/app/oracle/product/gg/dirdat/r6, extract e6pdp1 GGSCI> edit parame6pdp1 EXTRACT e6pdp1 USERID ogg, PASSWORD ogg RMTHOST ggdev, MGRPORT 7809 RMTTRAIL /u01/app/oracle/product/gg/dirdat/r6 TABLE E6P.*; Add replicat on target On GGDEV: ========= GGSCI>dbloginuseridogg, password ogg GGSCI> add replicat e6drep1, integrated exttrail /u01/app/oracle/product/gg/dirdat/r6 GGSCI> edit param e6drep1 REPLICAT e6drep1 USERID ogg, PASSWORD ogg ASSUMETARGETDEFS MAP E6P.* TARGET E6P.*; Start Extract, Pump and replicat GGSCI> start e6pex1 GGSCI> start e6pdp1 GGSCI> start e6drep1 Test Schema Replication Let us create table on source and check if table is replicated to E6P on target CREATE TABLE profiles ( pname VARCHAR2(32) PRIMARY KEY, pemail VARCHAR2(64), paddr VARCHAR2(128) ); Let us insert some rows on source and you should see same row replicated on target INSERT INTO profilesVALUES ('Scott','scott@gmail.com','USA'); INSERT INTO profilesVALUES ('James','james@gmail.com','UK'); commit; Let us create an index on source and you should see same replicated on target SQL> CREATE INDEX pemail_idx ON profiles(pemail); Let us create a cluster on source and you should see same replicated on target SQL> CREATE CLUSTER dept_no(deptno NUMBER); Let us create a sample procedure on source database SQL> CREATE OR REPLACE PROCEDURE getemail(Ppname IN VARCHAR2, Ppemail OUT VARCHAR2) IS vpemail VARCHAR2(64); BEGIN SELECT pemail into vpemail FROM profiles WHERE pname =ppname; END; / Procedure created. Connect to the target database to verify it has been created successfully. Let us grant permissions on PROFILES table to FOX user SQL> GRANT SELECT ON PROFILES TO FOX; Connect to target and verify if the permissions are granted or not. IMPORTANT NOTES ON SCHEMA LEVEL DDL REPLICATION Make sure the permissions that source schema has, the same are given to target schema as well otherwise most of the DDL will fail. In our example, both source and target schema names are same. If you have different source and target schema names, add below line in replicat to allow DDL mapping from source to target DDLOPTIONS MAPSESSIONSCHEMA source_schema TARGET target_schema; DDLOPTIONS MAPSESSIONSCHEMA FOX TARGET TOM; Golden Gate Existing Schema Replication There is an existing schema on source database. Client wants to setup replication for same schema on target server. Below are the client requirements: All DML replications from source to target All DDL replications from source to target The source schema is HR and target schema is HRD Let us analyze the scenario: As per the client requirement, it is an existing schema on source. First, we must perform an initial load and then continue with change sync . Below would be further approaches to resolve this: Scenario 1: source schema of any size and you can stop changes to data on source Stop changes on source schema Perform schema export / import using expdp/impdp Configure golden gate change sync Start changes on source schema Scenario 2: source schema of small size < 100 GB and you cannot stop changes to data on source Configure golden gate change sync but do not start replicat Perform schema export / import using expdp/impdp (we are using database utility instead of GG Initial load) Start replicat with HANDLECOLLISIONS parameter Scenario 3: source schema of big size > 100 GB and you cannot stop changes to data on source Create target schema with source metadata only (DATAPUMP impdpover DBLINK content=METADATA_ONLY) Disable constraints, triggers (if any) on target tables Drop indexes on target tables Configure Golden Gate change sync but do not start replicat Configure Golden Gate initial load and start schema replication Rebuild indexes on target tables Enable constraints on target tables Start replicat with HANDLECOLLISIONS parameter Assignment Perform schema replication using all the three scenarios mentioned above. You can create new schemas or try to replicate existing schemas. Realtime Assignment There is a new blank schema which is being created on source database. Client wants to setup replication for same schema on target server. Below are the client requirements: All DML replications from source to target All DDL replications from source to target The source schema is IQP and target schema is IQD All the target table names must prefix “P_”. For example, If source table is EMP, on target it must be P_EMP No changes in the names of all other objects from source to target. 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 Convert Temperature

    Shell Script to Convert Temperature Linux shell script allows the user to enter the temperature and provides the option to convert in Fahrenheit or Celsius. To achieve this we will use for loop. A ‘for loop’ is a bash programming language statement that allows code to be repeatedly executed. vi temperature_convert.sh #!/bin/bash echo "*** Converting between the different temperature scales ***" echo "1. Convert Celsius temperature into Fahrenheit" echo "2. Convert Fahrenheit temperatures into Celsius" echo -n "Select your choice (1-2) : " read choice if [ $choice -eq 1 ] then echo -n "Enter temperature (C) : " read tc # formula Tf=(9/5)*Tc+32 tf=$(echo "scale=2;((9/5) * $tc) + 32" |bc) echo "$tc C = $tf F" elif [ $choice -eq 2 ] then echo -n "Enter temperature (F) : " read tf # formula Tc=(5/9)*(Tf-32) tc=$(echo "scale=2;(5/9)*($tf-32)"|bc) echo "$tf = $tc" else echo "Please select 1 or 2 only" exit 1 fi Here is the sample output of the above script More ways to use for loop You can use 'for' like the C programming language. You can use the Range of numbers after the “in” keyword. Related Posts Heading 2 Add paragraph text. Click “Edit Text” to customize this theme across your site. You can update and reuse text themes.

  • AWS | DBA Genesis Support

    AWS Mount EFS on EC2 Instance You might have created an Elastic File System (EFS) and now would like to mount it on EC2 instance. Get EFS link Mount EFS on EC2 Add to... Oracle Database Migration to AWS Cloud Find different ways to migrate an Oracle database from on-premises to AWS cloud using different tools available for migration. You could... Convert pem to ppk file Using PuTTygen When you create an EC2 instance, aws provides you with .pem file. This .pem file is used to connect EC2 instance from a Linux host. In...

  • Connect Linux Instance with SSH Keys

    Connect Linux Instance with SSH Keys Using passwords for remote Linux access is outdated and risky, leaving systems vulnerable to hacking. The SSH Key based login is the most secure and safest way to connect a remote Linux server. If you have the key, you are in! Connect to Linux Server from Mac Invoke Linux GUI on Mac Connect From Windows Using OpenSSH Connect From Windows Using PuTTY Invoke Linux GUI on Windows Transfer Files Using WinSCP Share SSH Key with other user Connect to Linux Server from Mac You must have SSH Key with .pem extension to connect from Mac Download the .pem key file, open Terminal app and navigate to Downloads folder cd ~/Downloads Set the permissions of your PEM file chmod 600 .pem Connect to the remote server using the SSH command ssh -i .pem cloud-user@ Type yes and enter the passphrase (if asked) for the SSH key. Enjoy! Invoke Linux GUI on Mac Download and install XQuartz package which will invoke Linux GUI on Mac. Start XQuartz, open terminal and connect to your Linux instance using SSH with -Y parameter ssh -i .pem -Y cloud-user@ Connect From Windows Using OpenSSH You must have SSH Key with .pem extension to connect from Windows using OpenSSH. You must be running at least Windows Server 2019 or Windows 10 (build 1809) and PowerShell 5.1 or later Install OpenSSH on Windows machine to SSH into any remote Linux server: Open Settings , select System , then select Optional Features Check if OpenSSH is already installed, else click Add a feature Find OpenSSH Client and Install Open cmd and navigate to the folder which contains .pem key cd downloads Connect to the remote server using the SSH command ssh -i .pem username@ Type yes and enter the passphrase (if asked) for the SSH key. Enjoy! Connect From Windows Using PuTTY You cannot use .pem file with PuTTY to connect remote Linux server. You will have to first convert the .pem file into .ppk (Putty Private Key) format and this is done through PuTTYgen software. Convert .pem to .ppk Install PuTTYgen from the SSH website and Start PuTTYgen. Under Actions section, click Load By default, PuTTYgen displays only files with .ppk extension. To locate your .pem file, choose the option to display files of all types PuTTYgen displays a notice that the .pem file was successfully imported. Choose OK. To save the key in .ppk format, choose Save private key. PuTTYgen displays a warning about saving the key without a passphrase. Choose Yes Connect via PuTTY Open PuTTY and enter the IP address or hostname On Left hand menu, navigate to Connection > SSH > Auth > Select Credentials . Under Private key file for authentication, click Browse and select the .ppk file Select Session in the left hand menu, under Saved Session give a meaningful name and click on Save You can now double click the Saved Session and connect to remote Linux server! Invoke Linux GUI on Windows Download and Install Xming which will allow you to invoke Linux GUI on Windows. Start the installer and Choose Multiple Windows , enter Display number 0 , and click Next Just click Next, Next and Finish the installation. Open PuTTY and enter the IP address If you have an SSH key, On Left hand menu, navigate to Connection > SSH > Auth > Select Credentials . Under Private key file for authentication, click Browse and select the .ppk file To enable X11 forwarding, navigate to SSH >> X11 >> Check Enable X11 forwarding Select Session in the left hand menu, under Saved Session give a meaningful name and click on Save Double-click the saved session , connect to Linux server and initiate any GUI application like firefox (if installed) or Oracle runInstaller Transfer Files Using WinSCP WinSCP allows you to transfer files from your windows system to remote Linux server. Install WinSCP and the default installation option are OK. Start WinSCP, give Host name (or IP Address) and User name of your remote Linux instance If you are using DBA Genesis cloud, the default username is root or cloud-user Click Advance button, under SSH and choose Authentication. Specify the path for your .ppk key file, or choose the ... button to browse to the key pair file and Choose OK Choose Login . To add the host fingerprint to the host cache, choose Yes . After the connection is established, in the connection window your Linux instance is on the right and your local machine is on the left. You can drag and drop files between the remote file system and your local machine. Share SSH Key with other user The cloud servers are generally configured to allow a specific super user like root or cloud-user to connect using SSH key. If you would like to connect to a different user example oracle to invoke GUI, then you will have to share the SSH key with oracle user. If you are connecting to cloud server with a super user apart from root , like cloud-user then you can simply switch to root user with sudo su - Connect to the cloud server with super user root or cloud-user and open authorized_keys file cat .ssh/authorized_keys Copy the keys that you would like to share with oracle user Switch to oracle user su - oracle Open the authorized_keys file and paste the keys vi ~/.ssh/authorized_keys If the authorized_keys file or .ssh folder does not exists then mkdir -m 700 -p ~/.ssh vi ~/.ssh/authorized_keys Paste the keys, save and give 600 permissions chmod 600 ~/.ssh/authorized_keys Now you can login to the server with Oracle user and same SSH Key . Related Posts Heading 2 Add paragraph text. Click “Edit Text” to customize this theme across your site. You can update and reuse text themes.

Search Results

bottom of page