Description: In this blog, we are going to see oracle data guard protection modes and how to change one mode to another mode.
What is protection mode: Production mode is based on the RTO(recovery time objective) and RPO(recovery point objective values and to select the most appropriate model for their data guard configuration. The primary database always syncs with the standby database, the primary database is not active for the end-users for some natural disasters, hardware errors occur the standby can be changed as primary.
Why it is Required: In that situation some of the applications required maximum database performance at all times, so we can configure the standby database with appropriate protection mode.
Types of protection modes:
MAXIMUM PERFORMANCEMAXIMUM PROTECTIONMAXIMUM AVAILABILITY
MAXIMUM AVAILABILITY: Maximum availability protection mode provides the highest level of protection, which is possible without compromising the availability of the primary database. Whatever changes happen in the primary database it can be reflected in the standby database, the standby database sends acknowledgment then it will send again the logs, otherwise, it ignores the availability mode move on to the maximum performance mode. This mode ensures ZERO data loss in the case of certain double faults.
MAXIMUM PERFORMANCE: Maximum performance is a default protection mode. It is a high-level protection mode without affecting the primary database. The primary database sends logs to the standby database it’s not waiting for an acknowledgment it will perform asynchronous behavior. Less amount of data loss happens in maximum performance mode.
MAXIMUM PROTECTION: Maximum protection mode ensures that no data loss occurs if the primary database fails. The primary database sends logs to standby the RFS acknowledges to LNS at least one redo log can be transferred or it will shut down the primary database.
SUMMARY OF PROTECTION MODES:
How to view and change protection mode: View protection mode: SQL> select protection_mode from v$database; Data sync and async configuration: SQL> ALTER SYSTEM SET log_archive_dest_2=’service=data sync valid_for=(online_logfiles,primary_role) db_unique_name=stand’ SCOPE=both; System altered.
How to change protection mode:
SQL> alter database set standby database to maximize PERFORMANCE; SQL> alter database set standby database to maximize PROTECTION; SQL> alter database set standby database to maximize AVAILABILITY; SQL> select name,protection_mode from v$database;
Thank you for giving your valuable time to read the above information.
If you want to be updated with all our articles send us the Invitation or Follow us:
Description : In this blog, we are going to see step-by-step oracle application express workspace creation and import the application from the workspace and run the application.
High-level steps:-
1. Pre-request checks.2. Export schema in database and application in apex.3. Drop or create exported schema before import with required privileges and tablespace.4. Import schema in database level.5. Create a new workspace in apex.6. Edit privileges and create a password for workspace.7. Login the workspace as admin user.8. Import application into the workspace.9. Install and run the application.
Step 1:- Pre-request check.
Check application schema in a database and map tablespace and size of the schema.
Step 2:- Export schema in database and application in apex.
After that export the schema and move it to the local machine using win SCP. Login to the apex workspace select the managed workspace and users in that mention the export option to export the workspace SQL format file.
Step 3:- Drop or create exported schema before import with required privileges and tablespace. Step 4:- Import schema at the database level. We need to import the schema in the database using the dump file. Step 5:- Create a New workspace in the apex. 1. log in as Admin user. 2. Select Create workspace option. 3. Provide workspace name and click next. 4. Already we create a schema at the database level so we select the reuse option as yes and select the schema. 5. Provide username and password to the workspace. 6. Select Create workspace option. 7. Change workspace details give edit workspace option. Step 6:- Edit privileges and create an application for the workspace. 1. Account privilege changes. 2. Create a password for the workspace. Step 7:- Create a new application in the workspace. 1. log in to the Workspace with workspace name, username, and password. 2. Select the application builder option in that we create an application or we can import an existing application. 3. Select create an application we can create the new application. 4. Run the application and Login as the workspace username and password. Step 8:- Importing Existing application to the workspace. 1. Select the import option in the application builder. 2. Drag the exported SQL file to that location and click next. 3. select reuse application id and install the application. after install run the application. Step 9:- Login and check the application working fine. ********************************************************************************
Thank you for giving your valuable time to read the above information.
If you want to be updated with all our articles send us the Invitation or Follow us:
Description: In this blog, we are going to see step-by-step installation steps in oracle apex 21.2 on Linux and database 19c.
Oracle Application Express:
Oracle Application Express (APEX) is a low-code development platform.
that enables you to build stunning, scalable, secure apps with world-class features that can be deployed anywhere.
Using APEX, developers can quickly develop and deploy compelling apps that solve real problems and provide immediate value.
Overall steps:1. Create a directory copy the software's.2. Unzip Apex 21.2 Zip File.3. Create APEX tablesapce.4. Execute apex installation scripts.5. Configure the database apex restful services.6. Alter a apex user in database level and set the password.7. Set the port number as 0.8. Unzip java and tomcat Zip files.9. Create Tomcat environment file, start tomcat and check the ip
its working or not.10. In tomcat-user.xml file entries.11. ORDS configuration process.12. apex.war file creation and Set Oracle REST Database Service ORDS configuration directory.13. ORDS installation process.14. Apex.xml file entries and create image directory and copy images.15. Start the tomcat and check web page front end working or not.
Required Software’s
Step 1:- Create a Directory and copy the softwares. Create a new directory name as apex_21.2. Either we can download software or we copy the software’s from available locations using winscp/scp. Step 2:- Unzip the Apex file in the created directory. Unzip the Apex software in our customized location after unzipping it will create a directory called apex. [oracle@primary apex21.2]$ unzip apex_21.2_en.zip Step 3:- Create a separate tablespace for Apex: SQL>create tablesapce APEX datafile ‘/u01/app/oracle/oradata/ORCL/apex02.dbf’ size 1g;
Tablesapce created. Step 4:- Execute apex installation scripts. Script Location – /home/oracle/apex21.2/apex [oracle@primary apex]$ cat apexins.sql SQL> @apexins.sql APEX APEX TEMP /i/ Step 5:- Configure the database apex restful services. SYS> @apex_rest_config.sql PL/SQL procedure successfully completed. After execute script provide password for public user and apex listener. Step 6:- Alter a apex user in database level and set the password. SYS> alter user APEX_LISTENER identified by Apex#321 account unlock; SYS> alter user APEX_PUBLIC_USER identified by Apex#321 account unlock; Step 7:-Set the port number as 0. (we need to set default port 8080 while using PLSQL gateway) SYS> EXEC dbms_xdb.sethttpport(0); PL/SQL procedure successfully completed.
Step 8:- Unzip java and tomcat Zip files. Unzip the java software file. [oracle@primary apex21.2]$ tar -xvf jdk-17_linux-x64_bin.tar.gz unzip tomcat software file. [oracle@primary apex21.2]$ tar -xvf apache-tomcat-9.0.27.tar.gz Step 9:- Create Tomcat environment file, start tomcat and check the ip its working or not. [oracle@primary ~]$vi tomcat.env export JAVA_HOME=/home/oracle/apex21.2/jdk-17.0.1 export CATALINA_HOME=/home/oracle/apex21.2/apache-tomcat-9.0.27 export CATALINA_BASE=$CATALINA_HOME Start the tomcat using start up script.
script location – cd$CATALINA_HOME/bin/startup.sh check the system local ip tomcat working or not if working shutdown the tomcat for apex configuration. System IP – http://192.168.44.128:8080 Step 10:- Tomcat user-xml file entries. script Location – /home/oracle/apex21.2/apache-tomcat-9.0.27/conf/tomcat-user.xml <role rolename=”manager-gui” /> <user username=”oracle” password=”oracle” roles=”manager-gui” />Step 11:- ORDS configuration process.
Make a directory as ords and unzip ords software in that location.
Make a conf directory in that ords location. ORDS configuration:
Add the dbname and domain entries in the “ords_params.properties” file [oracle@primary ords]$ cd params/ [oracle@primary params]$ ls ords_params.properties [oracle@primary params]$ cat ords_params.properties
#Thu Nov 25 07:19:06 IST 2021
db.hostname=primary.localdomain (mention the Hostname)
db.password=@0509B9AA45A97BE4E54E4C5E0A3BC3E45135BFB52D400C1A4C
db.port=1521
db.servicename=orcl.localdomain (specify the domain)
db.username=APEX_PUBLIC_USER
migrate.apex.rest=false
plsql.gateway.add=true
rest.services.apex.add=true
rest.services.ords.add=true
schema.tablespace.default=APEX (set the tablespace)
schema.tablespace.temp=TEMP
standalone.http.port=8080
standalone.mode=false
user.apex.listener.password=Apex#321
user.apex.restpublic.password=Apex#321
user.public.password=Apex#321
user.tablespace.default=APEX
user.tablespace.temp=TEMP Step 12:- apex.war file creation and Set Oracle REST Database Service ORDS configuration directory. Take a backup for ords war file. [oracle@primary ords]$cp ords.war ords.war_bkp_25_11_2021 Move the file ords.war to apex.war [oracle@primary ords]$ mv ords.war apex.war Set Oracle REST Database Service ORDS configuration directory [oracle@primary ords]$ /home/oracle/apex21.2/jdk-17.0.1/bin/java -jar apex.war configdir /home/oracle/apex21.2/ords/conf INFO: Set config.dir to /home/oracle/apex21.2/ords/conf in: /home/oracle/apex21.2/ords/apex.war Step 13:-ORDS installation process:- after create apex war file using that to install a java. [oracle@primary ords]$ /home/oracle/apex21.2/jdk-17.0.1/bin/java -jar apex.war install advanced Verify ORDS schema in Database Configuration apex with connection host: primary.localdomain port: 1521 service name: orcl.localdomain Requires to login with administrator privileges to verify Oracle REST Data Services schema. Enter the administrator username:sys Enter the database password for SYS AS SYSDBA: Confirm password: retrieving information. Enter the default tablespace for ORDS_METADATA [APEX]: Enter the temporary tablespace for ORDS_METADATA [TEMP]: Enter the default tablespace for ORDS_PUBLIC_USER [APEX]: Enter the temporary tablespace for ORDS_PUBLIC_USER [TEMP]: Installing Oracle REST Data Services version 19.1.0.r0921545 … Log file written to /home/oracle/ords_install_core_2021-11-25_014419_00456.log … Verified database prerequisites … Created Oracle REST Data Services proxy user … Created Oracle REST Data Services schema … Granted privileges to Oracle REST Data Services … Created Oracle REST Data Services database objects … Log file written to /home/oracle/ords_install_datamodel_2021-11-25_014452_00616.log … Log file written to /home/oracle/ords_install_apex_2021-11-25_014455_00239.log Completed installation for Oracle REST Data Services version 19.1.0.r0921545. Elapsed time: 00:00:39.481 Enter 1 if you want to use PL/SQL Gateway or 2 to skip this step. If using Oracle Application Express or migrating from mod_plsql then you must enter 1 [1]: Enter the PL/SQL Gateway database user name [APEX_PUBLIC_USER]: Enter the database password for APEX_PUBLIC_USER: Confirm password: Enter 1 to specify passwords for Application Express RESTful Services database users (APEX_LISTENER, APEX_REST_PUBLIC_USER) or 2 to skip this step [1]: Enter the database password for APEX_LISTENER: Confirm password: Enter the database password for APEX_REST_PUBLIC_USER: Confirm password: Nov 25, 2021 06:24:50 AM INFO: reloaded pools: [] Enter 1 if you wish to start in standalone mode or 2 to exit [1]: Step 14:- Apex.xml file entries and create image directory and copy images. Make the below entries in apex.xml file.
script location: [oracle@primary conf]$ pwd /home/oracle/apex21.2/ords/conf/apex/conf <entry key=”jdbc.InitialLimit”>15</entry> <entry key=”jdbc.MinLimit”>15</entry> <entry key=”jdbc.MaxLimit”>50</entry> Create a images directory and copy the oracle application express images. [oracle@primary ~]$ mkdir -p $CATALINA_HOME/webapps/i/ [oracle@primary apex]$ cp -R /home/oracle/apex21.2/apex/images/* $CATALINA_HOME/webapps/i/ Copy the apex.war file in webapps folder Step 15:- Start the tomcat and check apex front end working or not. start tomcat using a startup script. [oracle@primary ~]$ . tomcat.env
[oracle@primary ~]$ cd $CATALINA_HOME/bin
[oracle@primary bin]$ sh startup.sh check the local IP and apex application working or not http://192.168.44.128:8080/apex Reset Admin password:- Reset admin password connect the database and execute the script @apxchpwd.sql Login to new password:- log in the new password the apex application now working fine. ********************************************************************************
Thank you for giving your valuable time to read the above information.
If you want to be updated with all our articles send us the Invitation or Follow us:
Description:
In this Blog, we are going to see what is snapshot standby database and how to convert a physical standby database to a snapshot standby database with Demo.
Snapshot Standby Database:
Snapshot standby allows doing a read-write operation on the standby database.
We can convert the physical standby database to snapshot standby.
On that, we can do all types of testing or can be used as a development database.
Once the testing is over we can again convert the snapshot database to physical standby.
Once it is converted physical standby database, whatever changes were done to the snapshot standby will be reverted.
Data Guard Configuration Environment:
Overall Steps:
1. Check primary and standby database role and open mode.2. Check the archived sequence value in standby.3. Disable the MRP process.4. Check the flashback status if it not enabled, enable the flashback.5. Check the db_recovery_file_set location and size.6. Bounce the database and start the db as mount stage.7. Convert physical standby to snapshot standby database.8. Open standby database and check open mode READ/WRITE.9. Test the snapshot standby database now we can create user and tables
for testing purpose.10. verify the log sequence primary and standby.11. Bounce database again open in mount stage.12. Convert snapshot standby to physical standby.13. Bounce the database.14. Check the database role has been changed as physical standby and the
mode as read only.15. After bounce enable the MRP process and check table available or not.
Step 1- Check primary and standby database role and open mode. Primary database: [oracle@agent ~]$ sqlplus / as sysdba SQL*Plus: Release 19.0.0.0.0 – Production on Thu Nov 18 14:16:48 2021 Version 19.3.0.0.0 Copyright (c) 1982, 2019, Oracle. All rights reserved. Connected to: Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 – Production Version 19.3.0.0.0 SQL> select status,instance_name,database_role,open_mode from v$database,v$Instance; STATUS INSTANCE_NAME DATABASE_ROLE OPEN_MODE ———— —————- —————- ——————– OPEN agent PRIMARY READ WRITE SQL>
Standby database: [oracle@data ~]$ sqlplus / as sysdba SQL*Plus: Release 19.0.0.0.0 – Production on Thu Nov 18 14:18:41 2021 Version 19.3.0.0.0 Copyright (c) 1982, 2019, Oracle. All rights reserved. Connected to: Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 – Production Version 19.3.0.0.0
SQL> select status,instance_name,database_role,open_mode from v$database,v$Instance;
STATUS INSTANCE_NAME DATABASE_ROLE OPEN_MODE ———— —————- —————- ——————– OPEN data PHYSICAL STANDBY READ ONLY WITH APPLY SQL>
Step 2:- Check the archived sequence value in standby: SQL> select thread#,max(sequence#) from v$archived_log group by thread#; THREAD# MAX(SEQUENCE#) ———- ————– 1 14
Step 3:- Disable the MRP process in the standby database: SQL> alter database recover managed standby database cancel; Database altered.
Step 4:- Check the flashback status if it is not enabled enable the flashback. SQL> select flashback_on from v$database;
FLASHBACK_ON —————— NO SQL> alter database flashback on; Database altered. SQL> select flashback_on from v$database;
FLASHBACK_ON —————— YES
Step 5:- Check the db_recovery_file_set location and size. SQL> show parameter db_recovery_file_dest NAME TYPE VALUE ———————————— ———– —————————— db_recovery_file_dest string /u01/app/oracle/fast_recovery_ area db_recovery_file_dest_size big integer 8256M
Step 6:- Bounce the database and start the DB as mount stage: SQL> shut immediate ORA-01109: database not open Database dismounted. ORACLE instance shut down. SQL> startup mount ORACLE instance started.
Total System Global Area 1258290752 bytes Fixed Size 8896064 bytes Variable Size 738197504 bytes Database Buffers 503316480 bytes Redo Buffers 7880704 bytes Database mounted.
Step 7:- Convert physical standby to snapshot standby database: SQL> alter database convert to snapshot standby; Database altered.
Step 8:- Open standby database check read/write mode, and database role: SQL> alter database open; Database altered. SQL> select status, instance_name, database_role,open_mode from v$database, v$instance; STATUS INSTANCE_NAME DATABASE_ROLE OPEN_MODE ———— —————- —————- ——————– MOUNTED data SNAPSHOT STANDBY MOUNTED
Step 9:- Test the snapshot standby database now we can create user and tables for testing purposes. SQL> create user agent identified by vbt default tablespace users quota unlimited on users; User created SQL> grant connect,resource to agent; Grant succeeded. SQL> conn agent/vbt Connected. SQL> create table test(id number,name varchar2(20)); Table created. SQL> insert into test values(101,’ram’); 1 row created. SQL> insert into test values(102,’raj’); 1 row created. SQL> commit; Commit complete. SQL> select * from test; ID NAME ———- ——————– 101 ram 102 raj
Step 10:- verify the log sequence primary and standby. Primary side SQL> select thread#,max(sequence#) from v$archived_log group by thread#; THREAD# MAX(SEQUENCE#) ———- ————– 1 14 Standby side SQL> select thread#,max(sequence#) from v$archived_log group by thread#;
Step 11:- Bounce database again open in mount stage. SQL> shut immediate Database closed. Database dismounted. ORACLE instance shut down. SQL> startup mount ORACLE instance started.
Total System Global Area 1258290752 bytes Fixed Size 8896064 bytes Variable Size 754974720 bytes Database Buffers 486539264 bytes Redo Buffers 7880704 bytes Database mounted.
Step 12:- convert snapshot standby to physical standby: SQL> alter database convert to physical standby; Database altered.
Step 13- Bounce the database SQL> shut immediate ORA-01109: database not open Database dismounted. ORACLE instance shut down. SQL> startup ORACLE instance started. Total System Global Area 1258290752 bytes Fixed Size 8896064 bytes Variable Size 754974720 bytes Database Buffers 486539264 bytes Redo Buffers 7880704 bytes Database mounted. Database opened.
Step 14:- check the database role has been changed as physical standby and the mode as read-only SQL> select status,instance_name,database_role,open_mode from v$database,v$Instance;
STATUS INSTANCE_NAME DATABASE_ROLE OPEN_MODE ———— —————- —————- ——————– OPEN data PHYSICAL STANDBY READ ONLY
Step 15:- After bounce enable the MRP process and check table available or not. SQL> alter database recover managed standby database disconnect from session; Database altered.
SQL> select * from agent.test; select * from agent.test ERROR at line 1: ORA-00942: table or view does not exist SQL>
Thank you for giving your valuable time to read the above information.
If you want to be updated with all our articles send us the Invitation or Follow us:
Description: In this Blog we are going to see the step by step process of data Guard configuration, and primary to physical standby database demo.
what Is Data Guard:
Oracle data Guard ensures high availability, data protection, and disaster recovery for enterprise data.
Data Guard maintains these standby databases as transactional consistent copies of the production database.
if the production database becomes unavailable, Data Guard can switch any standby database to the production role.
Physical Standby
Physical Standby is the exact block-for-block copy of primary database.
REDO logs apply primary to physical standby, its always sync with primary database
Physical Standby database only opened as READ ONLY mode.
Most of the environment used the physical standby database.
Environment Details: Overall Steps:
1.Check Archive log mode enable and force logging enabled.2.Add Standby log file group.3.check listener and TNS entry both primary and standby severs.4.Change parameters in primary database.5.copy password file primary to standby.6.Create PFILE in standby database.7.Create directory Structure in Standby database.8.Connect RMAN in standby database and run the script9.Check database can be configured or not.10.Connect standby database execute MRP process.11.Switch Logs in primary database, Execute Defer Enable.12.Check standby database log files can be switched.13.Crosscheck Alert Log files.
Primary Server Configuration:
Step 1: Check Archive log mode enable and force logging enabled.
In the primary database check whether the archive log mode is enabled or not, also check the force logging option to enable if is not enabled please enable it.
[oracle@agent ~]$ export ORACLE_SID=agent [oracle@agent ~]$ sqlplus / as sysdba Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 – Production Version 19.3.0.0.0
Connect database as mount stage
SQL> alter database archivelog; Database altered. SQL> alter database force logging; Database altered. SQL> alter database open; Database altered. SQL> select FORCE_LOGGING,log_mode from v$database;
FORCE_LOGGING LOG_MODE
————————- ————–
YES ARCHIVELOG
Step 2: Add Standby logfile group.
Add logfile group in the primary database mentioned as that file creation as standby log group.
SQL> alter database add standby logfile group 4 ‘/u01/app/oracle/oradata/AGENT/redo04.log’ size 50m; Database altered.
SQL> alter database add standby logfile group 5 ‘/u01/app/oracle/oradata/AGENT/redo05.log’ size 50m; Database altered.
SQL> alter database add standby logfile group 6 ‘/u01/app/oracle/oradata/AGENT/redo06.log’ size 50m; Database altered.
SQL> SELECT GROUP#,THREAD#,SEQUENCE#,ARCHIVED,STATUS FROM V$STANDBY_LOG;
The output of tns ping statements connects both sides primary to standby and standby to primary.
[oracle@agent admin]$ tnsping data TNS Ping Utility for Linux: Version 19.0.0.0.0 – Production on 18-NOV-2021 07:19:44 Copyright (c) 1997, 2019, Oracle. All rights reserved. Used parameter files: Used TNSNAMES adapter to resolve the alias Attempting to contact (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.2)(PORT = 1521))) (CONNECT_DATA = (SERVICE_NAME = data) (UR=A))) OK (20 msec)
[oracle@agent admin]$ tnsping agent TNS Ping Utility for Linux: Version 19.0.0.0.0 – Production on 18-NOV-2021 07:19:55 Copyright (c) 1997, 2019, Oracle. All rights reserved. Used parameter files: Used TNSNAMES adapter to resolve the alias Attempting to contact (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.1)(PORT = 1521))) (CONNECT_DATA = (SERVICE_NAME = agent))) OK (0 msec) [oracle@agent admin]$
Step 4:Change parameters in primary database.
1. dg_config – Specify the DG_CONFIG attribute to identify the DB_UNIQUE_NAME for the primary database and standby database. SQL> ALTER SYSTEM SET log_archive_config=’dg_config=(agent,data)’ SCOPE=both; System altered.
2. log_archive_dest_1 – The LOG_ARCHIVE_DEST parameter is used to specify the directory to which Oracle archive logs are written. SQL> ALTER SYSTEM SET log_archive_dest_1=’location=use_db_recovery_file_dest valid_for=(all_logfiles,all_roles) db_unique_name=agent’ SCOPE=both; System altered.
3. log_archive_dest_2 – handle the transmission of the standby site’s archived redo logs back to the original primary database. SQL> ALTER SYSTEM SET log_archive_dest_2=’service=data async valid_for=(online_logfiles,primary_role) db_unique_name=data’ SCOPE=both; System altered.
4. FAL_SERVER – It is used to fetch an archive log server for a standby database. SQL> ALTER SYSTEM SET fal_server=’data’ SCOPE=both; System altered.
5. FAL_CLIENT – It is configured for point the FAL Client. SQL> ALTER SYSTEM SET fal_client=’agent’ SCOPE=both; System altered.
6. standby_file_management –Check the redo changes done in Primary and sync those changes in Standby by Stopping the recovery and making STANDBY_FILE_MANAGEMENT = MANUAL value. Then sync the primary changes with standby changes and again change the parameter to AUTO and start the recovery on Standby database. SQL> ALTER SYSTEM SET standby_file_management=’AUTO’ SCOPE=both; System altered.
SQL> show parameter log_archive_config;
NAME TYPE VALUE ———————————— ———– —————————— log_archive_config string dg_config=(agent,data) SQL>
Move the file in new name db_unique_name(standby) oracle@data ~]$ cd $ORACLE_HOME/dbs [oracle@data dbs]$ ls hc_data.dat init.ora lkDATA orapwdata orapwagent spfiledata.ora [oracle@data dbs]$ mv orapwagent orapwdata
Step 6 :- create PFILE in standby database
In the $ORACLE_HOME/dbs directory of the standby system, create an initialization parameter file named initdata.ora Containing a single parameter: DB_NAME=agent [oracle@data dbs]$ cat initdata.ora db_name=agent [oracle@data dbs]$ pwd /u01/app/oracle/product/19.0.0/dbhome_1/dbs
Step 7 :- Create directory Structure in Standby database.
[oracle@data dbs]$ cd $ORACLE_BASE/admin/ [oracle@data dbs]$ mkdir data [oracle@data dbs]$ cd data [oracle@data dbs]$ mkdir adump [oracle@data dbs]$ mkdir -p /u01/app/oracle/oradata/DATA
Step 8 :- Connect RMAN clone Database
method 1: copy pfile primary database to standby edit the parameters after that connect RMAN and clone the database.
parameter_value_convert ‘agent’,’data’ set db_name=’agent’ set db_unique_name=’data’ set db_file_name_convert=’/u01/app/oracle/oradata/AGENT/’,’/u01/app/oracle/oradata/DATA/’ set log_file_name_convert=’/u01/app/oracle/oradata/AGENT/’,’/u01/app/oracle/oradata/DATA/’ set control_files=’/u01/app/oracle/oradata/DATA/standby1.ctl’ set log_archive_max_processes=’5′ set fal_client=’data’ set fal_server=’agent’ set standby_file_management=’AUTO’ set log_archive_config=’dg_config=(agent,data)’ set compatible=’19.3.0.0′ set memory_target=’1200m’
method 2:
craete a RMAN script mention the parameters changes in spfile to execute the script.
Target database as primary – Agent Auxiliary database as a standby – Data
[oracle@data admin]$ export ORACLE_SID=agent [oracle@data admin]$ rman target sys/oracle@agent auxiliary sys/oracle@data Recovery Manager: Release 19.0.0.0.0 – Production on Thu Nov 18 08:00:22 2021 Version 19.3.0.0.0 Copyright (c) 1982, 2019, Oracle and/or its affiliates. All rights reserved.
connected to target database: AGENT (DBID=396457310) connected to auxiliary database: AGENT (not mounted)
RMAN> run { allocate channel p1 type disk; allocate channel p2 type disk; allocate channel p3 type disk; allocate channel p4 type disk; allocate auxiliary channel s1 type disk; duplicate target database for standby from active database spfile parameter_value_convert ‘agent’,’data’ set db_name=’agent’ set db_unique_name=’data’ set db_file_name_convert=’/u01/app/oracle/oradata/AGENT/’,’/u01/app/oracle/oradata/DATA/’ set log_file_name_convert=’/u01/app/oracle/oradata/AGENT/’,’/u01/app/oracle/oradata/DATA/’ set control_files=’/u01/app/oracle/oradata/DATA/standby1.ctl’ set log_archive_max_processes=’5′ set fal_client=’data’ set fal_server=’agent’ set standby_file_management=’AUTO’ set log_archive_config=’dg_config=(agent,data)’ set compatible=’19.3.0.0′ set memory_target=’1200m’ nofilenamecheck; }
the output of the script:
using target database control file instead of recovery catalog allocated channel: p1 channel p1: SID=82 device type=DISK
contents of Memory Script: { backup as copy reuse passwordfile auxiliary format ‘/u01/app/oracle/product/19.0.0/dbhome_1/dbs/orapwdata’ targetfile ‘/u01/app/oracle/product/19.0.0/dbhome_1/dbs/spfileagent.ora’ auxiliary format ‘/u01/app/oracle/product/19.0.0/dbhome_1/dbs/spfiledata.ora’ ; sql clone “alter system set spfile= ”/u01/app/oracle/product/19.0.0/dbhome_1/dbs/spfiledata.ora””; } executing Memory Script
Starting backup at 18-NOV-21 Finished backup at 18-NOV-21
sql statement: alter system set spfile= ”/u01/app/oracle/product/19.0.0/dbhome_1/dbs/spfiledata.ora”
contents of Memory Script: { sql clone “alter system set audit_file_dest = ”/u01/app/oracle/admin/data/adump” comment= ”” scope=spfile”; sql clone “alter system set dispatchers = ”(PROTOCOL=TCP) (SERVICE=dataXDB)” comment= ”” scope=spfile”; sql clone “alter system set db_name = ”agent” comment= ”” scope=spfile”; sql clone “alter system set db_unique_name = ”data” comment= ”” scope=spfile”; sql clone “alter system set db_file_name_convert = ”/u01/app/oracle/oradata/AGENT/”, ”/u01/app/oracle/oradata/DATA/” comment= ”” scope=spfile”; sql clone “alter system set log_file_name_convert = ”/u01/app/oracle/oradata/AGENT/”, ”/u01/app/oracle/oradata/DATA/” comment= ”” scope=spfile”; sql clone “alter system set control_files = ”/u01/app/oracle/oradata/DATA/standby1.ctl” comment= ”” scope=spfile”; sql clone “alter system set log_archive_max_processes = 5 comment= ”” scope=spfile”; sql clone “alter system set fal_client = ”data” comment= ”” scope=spfile”; sql clone “alter system set fal_server = ”agent” comment= ”” scope=spfile”; sql clone “alter system set standby_file_management = ”AUTO” comment= ”” scope=spfile”; sql clone “alter system set log_archive_config = ”dg_config=(agent,data)” comment= ”” scope=spfile”; sql clone “alter system set compatible = ”19.3.0.0” comment= ”” scope=spfile”; sql clone “alter system set memory_target = 1200m comment= ”” scope=spfile”; shutdown clone immediate; startup clone nomount; } executing Memory Script
sql statement: alter system set audit_file_dest = ”/u01/app/oracle/admin/data/adump” comment= ”” scope=spfile
sql statement: alter system set dispatchers = ”(PROTOCOL=TCP) (SERVICE=dataXDB)” comment= ”” scope=spfile
sql statement: alter system set db_name = ”agent” comment= ”” scope=spfile
sql statement: alter system set db_unique_name = ”data” comment= ”” scope=spfile
sql statement: alter system set db_file_name_convert = ”/u01/app/oracle/oradata/AGENT/”, ”/u01/app/oracle/oradata/DATA/” comment= ”” scope=spfile
sql statement: alter system set log_file_name_convert = ”/u01/app/oracle/oradata/AGENT/”, ”/u01/app/oracle/oradata/DATA/” comment= ”” scope=spfile
sql statement: alter system set control_files = ”/u01/app/oracle/oradata/DATA/standby1.ctl” comment= ”” scope=spfile
sql statement: alter system set log_archive_max_processes = 5 comment= ”” scope=spfile
sql statement: alter system set fal_client = ”data” comment= ”” scope=spfile
sql statement: alter system set fal_server = ”agent” comment= ”” scope=spfile
sql statement: alter system set standby_file_management = ”AUTO” comment= ”” scope=spfile
sql statement: alter system set log_archive_config = ”dg_config=(agent,data)” comment= ”” scope=spfile
sql statement: alter system set compatible = ”19.3.0.0” comment= ”” scope=spfile
sql statement: alter system set memory_target = 1200m comment= ”” scope=spfile
Oracle instance shut down
connected to auxiliary database (not started) Oracle instance started
contents of Memory Script: { backup as copy current controlfile for standby auxiliary format ‘/u01/app/oracle/oradata/DATA/standby1.ctl’; } executing Memory Script
Starting backup at 18-NOV-21 channel p1: starting datafile copy copying standby control file output file name=/u01/app/oracle/product/19.0.0/dbhome_1/dbs/snapcf_agent.f tag=TAG20211118T080552 channel p1: datafile copy complete, elapsed time: 00:00:01 Finished backup at 18-NOV-21
contents of Memory Script: { sql clone ‘alter database mount standby database’; } executing Memory Script
sql statement: alter database mount standby database
contents of Memory Script: { set newname for tempfile 1 to “/u01/app/oracle/oradata/DATA/datafile/o1_mf_temp_jsc8y37j_.tmp”; switch clone tempfile all; set newname for datafile 1 to “/u01/app/oracle/oradata/DATA/datafile/o1_mf_system_jsc8qv1o_.dbf”; set newname for datafile 3 to “/u01/app/oracle/oradata/DATA/datafile/o1_mf_sysaux_jsc8smrq_.dbf”; set newname for datafile 4 to “/u01/app/oracle/oradata/DATA/datafile/o1_mf_undotbs1_jsc8tq0b_.dbf”; set newname for datafile 7 to “/u01/app/oracle/oradata/DATA/datafile/o1_mf_users_jsc8tr78_.dbf”; backup as copy reuse datafile 1 auxiliary format “/u01/app/oracle/oradata/DATA/datafile/o1_mf_system_jsc8qv1o_.dbf” datafile 3 auxiliary format “/u01/app/oracle/oradata/DATA/datafile/o1_mf_sysaux_jsc8smrq_.dbf” datafile 4 auxiliary format “/u01/app/oracle/oradata/DATA/datafile/o1_mf_undotbs1_jsc8tq0b_.dbf” datafile 7 auxiliary format “/u01/app/oracle/oradata/DATA/datafile/o1_mf_users_jsc8tr78_.dbf” ; sql ‘alter system archive log current’; } executing Memory Script
executing command: SET NEWNAME
renamed tempfile 1 to /u01/app/oracle/oradata/DATA/datafile/o1_mf_temp_jsc8y37j_.tmp in control file
SQL> alter database recover managed standby database disconnect nodelay;
Database altered.
Step 11:- Switch Logs in the primary database, Execute Defer Enable.
SQL> archive log list; Database log mode Archive Mode Automatic archival Enabled Archive destination USE_DB_RECOVERY_FILE_DEST Oldest online log sequence 7 Next log sequence to archive 9 Current log sequence 9
Set log_archive_dest_state_2 as defer SQL> alter system set log_archive_dest_state_2=defer;
System altered.
Enable log_archive_dest_state_2 location SQL> alter system set log_archive_dest_state_2=enable;
System altered.
SQL> alter system switch logfile;
System altered.
SQL> /
System altered.
Step 12: – check standby database log files can be switched
SQL> select error, status from v$archive_dest;
SQL> SELECT ARCH.THREAD# “Thread”, ARCH.SEQUENCE# “Last Sequence Received”, APPL.SEQUENCE# “Last Sequence Applied”, (ARCH.SEQUENCE# – APPL.SEQUENCE#) “Difference” FROM (SELECT THREAD# , SEQUENCE# FROM V$ARCHIVED_LOG WHERE (THREAD#, FIRST_TIME ) IN (SELECT THREAD#, MAX(FIRST_TIME) FROM V$ARCHIVED_LOG GROUP BY THREAD#)) ARCH, (SELECT THREAD# , SEQUENCE# FROM V$LOG_HISTORY WHERE (THREAD#, FIRST_TIME ) IN (SELECT THREAD#, MAX(FIRST_TIME) FROM V$LOG_HISTORY GROUP BY THREAD#)) APPL WHERE ARCH.THREAD# = APPL.THREAD# ORDER BY 1;
Check MRP0 process status: SQL> SELECT STATUS FROM V$MANAGED_STANDBY WHERE PROCESS=’MRP0′;
STATUS ———— WAIT_FOR_LOG
Archived log applied time and status:
SQL> SELECT sequence#, first_time, next_time, applied FROM v$archived_log ORDER BY sequence#; SQL> select name,open_mode from v$database; NAME OPEN_MODE ——— ——————– AGENT MOUNTED
Step 13:- Monitor the process in the alert log The best method to monitor the data guard process in the alert log shows as exact data transfer reports and errors we can using the logs easily find out the mistakes.
oracle@data trace]$ pwd /u01/app/oracle/diag/rdbms/data/data/trace [oracle@data trace]$ tail -100f alert_data.log MRP0 (PID:26173): Media Recovery Log /u01/app/oracle/fast_recovery_area/DATA/archivelog/2021_11_18/o1_mf_1_11_jsclfmk7_.arc MRP0 (PID:26173): Media Recovery Waiting for T-1.S-12 (in transit) 2021-11-18T09:05:44.267442+05:30 rfs (PID:29555): Archived Log entry 7 added for B-1088922080.T-1.S-12 ID 0x17a1415e LAD:2 rfs (PID:29555): No SRLs created for T-1 2021-11-18T09:05:44.297904+05:30 rfs (PID:29555): Opened log for T-1.S-13 dbid 396457310 branch 1088922080 2021-11-18T09:05:44.346775+05:30 MRP0 (PID:26173): Media Recovery Log /u01/app/oracle/fast_recovery_area/DATA/archivelog/2021_11_18/o1_mf_1_12_jsclfnxx_.arc MRP0 (PID:26173): Media Recovery Waiting for T-1.S-13 (in transit) 2021-11-18T09:05:45.670215+05:30 rfs (PID:29555): Archived Log entry 8 added for B-1088922080.T-1.S-13 ID 0x17a1415e LAD:2 rfs (PID:29555): No SRLs created for T-1 2021-11-18T09:05:45.699219+05:30 rfs (PID:29555): Opened log for T-1.S-14 dbid 396457310 branch 1088922080 2021-11-18T09:05:46.482970+05:30 MRP0 (PID:26173): Media Recovery Log /u01/app/oracle/fast_recovery_area/DATA/archivelog/2021_11_18/o1_mf_1_13_jscljj95_.arc MRP0 (PID:26173): Media Recovery Waiting for T-1.S-14 (in transit) 2021-11-18T09:16:20.023804+05:30
Thank you for giving your valuable time to read the above information.
If you want to be updated with all our articles send us the Invitation or Follow us:
The Automatic Workload Repository (AWR) collects and maintains statistics of the database.
We can generate awr report for a particular time frame in the past using the script awrrpt.sql ( located under $ORACLE_HOME/rdbms/admin)
script – @$ORACLE_HOME/rdbms/admin/awrrpt.sql
step – 1
[oratest@oracle ~]$ export ORACLE_SID=test
[oratest@oracle ~]$ sqlplus / as sysdba
SQL*Plus: Release 19.0.0.0.0 - Production on Thu Oct 7 11:06:50 2021
Version 19.3.0.0.0
Copyright (c) 1982, 2019, Oracle. All rights reserved.
Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0
SQL> select name,open_mode from v$database;
NAME OPEN_MODE
--------- --------------------
TEST READ ONLY
SQL> select status from v$instance;
STATUS
------------
OPEN
SQL> show user;
USER is "SYS"
step – 2
SQL>@$ORACLE_HOME/rdbms/admin/awrrpt.sql
Specify the Report Type
~~~~~~~~~~~~~~~~~~~~~~~
AWR reports can be generated in the following formats. Please enter the
name of the format at the prompt. Default value is 'html'.
'html' HTML format (default)
'text' Text format
'active-html' Includes Performance Hub active report
Enter value for report_type:<strong> <span style="color:#ed0911" <br />class="has-inline-color">html</span></strong>
old 1: select 'Type Specified: ',lower(nvl('&&report_type','html')) <br />report_type from dual
new 1: select 'Type Specified: ',lower(nvl('html','html')) <br />report_type from dual
Type Specified: html
old 1: select '&&report_type' report_type_def from dual
new 1: select 'html' report_type_def from dual
old 1: select '&&view_loc' view_loc_def from dual
new 1: select 'AWR_PDB' view_loc_def from dual
Current Instance
~~~~~~~~~~~~~~~~
DB Id DB Name Inst Num Instance Container Name
-------------- -------------- -------------- -------------- --------------
2378581000 TEST 1 test test
Instances in this Workload Repository schema
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
DB Id Inst Num DB Name Instance Host
------------ ---------- --------- ---------- ------
* 2378581000 1 TEST test oracle.local
Using 2378581000 for database Id
Using 1 for instance number
Specify the number of days of snapshots to choose from
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Entering the number of days (n) will result in the most recent
(n) days of snapshots being listed. Pressing <return> without
specifying a number lists all completed snapshots.
Enter value for num_days: 2
Listing the last 2 days of Completed Snapshots
Instance DB Name Snap Id Snap Started Snap Level
------------ ------------ ---------- ------------------ ----------
test TEST 646 29 Sep 2021 00:30 1
647 29 Sep 2021 01:30 1
648 29 Sep 2021 02:30 1
649 29 Sep 2021 03:30 1
650 29 Sep 2021 04:30 1
651 29 Sep 2021 05:30 1
652 29 Sep 2021 06:30 1
653 30 Sep 2021 00:14 1
654 30 Sep 2021 01:30 1
655 30 Sep 2021 03:46 1
Specify the Begin and End Snapshot Ids
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Enter value for begin_snap:<strong><span style="color:#a3000d" <br />class="has-inline-color"> </span><span style="color:#ec0b1e" <br />class="has-inline-color">650</span></strong>
Begin Snapshot Id specified: 650
Enter value for end_snap: <strong><span style="color:#e90c17" <br />class="has-inline-color">651</span></strong>
</pre>
<!-- /wp:preformatted -->
<!-- wp:preformatted -->
<pre class="wp-block-preformatted">Specify the Report Name
~~~~~~~~~~~~~~~~~~~~~~~
The default report file name is awrrpt_1_650_651.html. To use this name,
press <return> to continue, otherwise enter an alternative.
Enter value for report_name: awrrpt_1_07_10_2021.html
Using the report name awrrpt_1_07_10_2021.html
--------------------------------------------
Analysis Period
---------------
AWR snapshot range from 650 to 651.
Time period starts at 29-SEP-21 04.30.25 AM
Time period ends at 29-SEP-21 05.30.33 AM
Analysis Target
---------------
Database 'TEST' with DB ID 2378581000.
Database version 19.0.0.0.0.
ADDM performed an analysis of instance test, numbered 1 and hosted at
oracle.localdomain.
Activity During the Analysis Period
-----------------------------------
Total database time was 0 seconds.
The average number of active sessions was 0.
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
There are no findings to report.
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Additional Information
----------------------
Miscellaneous Information
-------------------------
There was no significant database activity to run the ADDM.
</pre>
<br /><a class="awr" href="#top">Back to Top</a><p />
<p />
<p />
<p />
<p />
<p />
<p />
<p />
<p />
<p />
End of Report
</body></html>
Report written to awrrpt_1_07_10_2021.html
Thank you for giving your valuable time to read the above information.
If you want to be updated with all our articles send us the Invitation or Follow us:
AWR collects database statistics every 60 minutes (on the hour) out of the box (this is configurable), and this data is maintained for a week and then purged.
ü The Oracle database uses AWR for problem detection and analysis as well as for self-tuning.A number of different statistics are collected by the AWR, including wait events, time model statistics, active session history statistics, various system- and session-level statistics, object usage statistics, and information on the most resource-intensive SQL statements.
ü To properly collect database statistics, set the initialization parameter STATISTICS_LEVEL to TYPICAL (the default) or ALL.
ü The AWR consists of a number of tables owned by the SYS schema and typically stored in the SYSAUX tablespace (currently no method exists that I know of to move these objects to another tablespace).
Recommendations before getting an AWR Report :
Collect Multiple AWR Reports
It’s always good to have two AWR Reports, one for good time (when database was performing well), second when performance is poor. This way Remote DBA can easily compare good and bad report to find out the culprit.
Stick to Particular Time
“Database is performing slow” will not help anymore to resolve performace issues. We have to have a specific time like Database was slow yesterday at 1 Pm and continue till 4Pm. Here, DBA will get a report for these three hours.
Split Large AWR Report into Smaller Reports
Instead of having one report for long time like one report for 4hrs. it’s better to have four reports each for one hour. This will help to isolate the problem.
Thank you for giving your valuable time to read the above information.
If you want to be updated with all our articles send us the Invitation or Follow us:
A database links allows an Oracle client to access two or more databases as one logical database via a connection between the database servers.
Database links are actually entries in a data dictionary table that define a communication path from one Oracle database to another remote database.
In order to access the database link a user must be connected to the database that contains the data dictionary table that defines the database link
Types Of Database Links
Private and Public
Private Link
To create a private database link, you must have the CREATE DATABASE LINK system privilege.
To create a public database link, you must have the CREATBE PUBLIC DATAASE LINK system privilege.
Also, you must have the CREATE SESSION system privilege on the remote Oracle database.
Private database link to a user in a remote database
SQL> CREATE DATABASE LINK orcl_remote
CONNECT TO scott IDENTIFIED BY tiger
USING 'orcl';
Database link created.
Private database link to a user in a remote database, with the full connection string
SQL> CREATE DATABASE LINK scott_remote
CONNECT TO apple IDENTIFIED BY apple
USING '(DESCRIPTION=
(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.1.24)(PORT=1521))
(CONNECT_DATA=(SERVICE_NAME=orcl))
)';
Database link created.
Thank you for giving your valuable time to read the above information.
If you want to be updated with all our articles send us the Invitation or Follow us:
A materialized view in Oracle is a database object that contains the results of a query.They are local copies of data located remotely, or are used to create summary tables based on aggregations of a table’s data.
A materialized view can query tables, views, and other materialized views.
A materialized view, or snapshot as they were previously known, is a table segment whose contents are periodically refreshed based on a query, either against a local or remote table.
Syntax
CREATE MATERIALIZED VIEW view-name BUILD [IMMEDIATE | DEFERRED] REFRESH [FAST | COMPLETE | FORCE ] ON [COMMIT | DEMAND ][[ENABLE | DISABLE] QUERY REWRITE] AS SELECT …;
Methods
BUILD
IMMEDIATE : The materialized view is populated immediately.This option is default one.
DEFERRED : The materialized view is populated on the first requested refresh.
Refresh Types
FAST: A fast refresh is attempted only there is a change in the base table. If materialized view logs are not present against the source tables in advance, the creation fails. To maintain the history of change in the base table, it is known as materialized view log.It is named as MLOG$_<base_table>. Materialized view log will be located in the source database in the same schema as the master table. Refresh fast will perform refresh according to the changes that occurred in the master table.
COMPLETE: The table segment supporting the materialized view is truncated and repopulated completely using the associated query.
FORCE: A fast refresh is attempted. If one is not possible a complete refresh is performed.
ON COMMIT : The refresh is triggered by a committed data change in one of the dependent tables.
ON DEMAND : The refresh is initiated by a manual request or a scheduled task.
GRANT CREATE MATERIALIZED VIEW TO INDIA;
Grant succeeded.
GRANT CREATE DATABASE LINK TO INDIA;
Grant succeeded.
Creating Database link to point remote database
SQL> CREATE DATABASE LINK PROD_LINK CONNECT TO ph IDENTIFIED BY ph USING 'orcl';
Database link created.
COMPLETE REFRESH
Creating Materialized View
SQL> CREATE MATERIALIZED VIEW dept_mv BUILD IMMEDIATE REFRESH COMPLETE AS SELECT * FROM ph.emp@PROD_LINK;
Materialized view created.
SQL> select * from emp;
NO NAME SALARY
---------- -------------------- ----------
1 apple 20000
2 orange 23000
3 mango 32000
Table Data present in Remote database
SQL> insert into emp values (6,'ice',30000);
1 row created.
SQL> commit;
Commit complete.
SQL> select rowid,no,name,salary fromdept_mv;
ROWID NO NAME SALARY
------------------ ---------- -------------------- ----------
AAAR36AABAAAbURAAA 1 apple 20000
AAAR36AABAAAbURAAB 2 orange 23000
AAAR36AABAAAbURAAC 3 mango 32000
Manually using complete refresh using the DBMS_VIEW package
SQL> execute DBMS_MVIEW.REFRESH( LIST => 'dept_mv', METHOD => 'C' );
PL/SQL procedure successfully completed.
SQL> select rowid,no,name,salary from dept_mv;
ROWID NO NAME SALARY
------------------ ---------- -------------------- ----------
AAAR36AABAAAbURAAD 1 apple 20000
AAAR36AABAAAbURAAE 2 orange 23000
AAAR36AABAAAbURAAF 3 mango 32000
AAAR36AABAAAbURAAG 6 ice 30000
DEFERRED
Initially drop the existing materialized view
SQL> drop MATERIALIZED VIEW dept_mv;
Materialized view dropped.
