Installation of APEX 21.2 in Linux

Step by step installation of APEX 21.2  in Linux


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:

Ramkumar’s LinkedIn: https://www.linkedin.com/in/ramkumardba/
LinkedIn Group: https://www.linkedin.com/in/ramkumar-m-0061a0204/
Facebook Page: https://www.facebook.com/Oracleagent-344577549964301
Ramkumar’s Twitter: https://twitter.com/ramkuma02877110
Ramkumar’s Telegram: https://t.me/oracleageant
Ramkumar’s Facebook: https://www.facebook.com/ramkumarram8 

Physical Standby To Snapshot Standby

Physical Standby To Snapshot Standby

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#;

THREAD# MAX(SEQUENCE#)
———- ————–
1 14
SQL> select process,status,sequence# from v$managed_standby;

PROCESS STATUS SEQUENCE#
——— ———— ———-
ARCH CONNECTED 0
DGRD ALLOCATED 0
DGRD ALLOCATED 0
ARCH CONNECTED 0
ARCH CONNECTED 0
ARCH CONNECTED 0
ARCH CONNECTED 0
LNS CONNECTED 0
DGRD ALLOCATED 0

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:

Ramkumar’s LinkedIn: https://www.linkedin.com/in/ramkumardba/
LinkedIn Group: https://www.linkedin.com/in/ramkumar-m-0061a0204/
Facebook Page: https://www.facebook.com/Oracleagent-344577549964301
Ramkumar’s Twitter: https://twitter.com/ramkuma02877110
Ramkumar’s Telegram: https://t.me/oracleageant
Ramkumar’s Facebook: https://www.facebook.com/ramkumarram8

19c Physical standby configuration – Dataguard

Physical standby configuration:


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 script
9.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;

GROUP# THREAD# SEQUENCE# ARC STATUS
———- ———- ———- — ———-
4 0 0 YES UNASSIGNED
5 0 0 YES UNASSIGNED
6 0 0 YES UNASSIGNED

Step 3- check listener and TNS entry both primary and standby servers.

Primary side entries.
LISTENER:
listener =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.1)(PORT = 1521))
)
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC))
)
)
)
SID_LIST_LISTENER =
(SID_DESC =
(GLOBAL_DBNAME = agent)
(ORACLE_HOME =/u01/app/oracle/product/19.0.0/dbhome_1)
(SID_NAME = agent)
)
)
TNS names:
[oracle@agent admin]$ cat tnsnames.ora
agent=
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.1)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = agent)
)
)
data=
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.2)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = data)
(UR=A)
)
)
Standby side entries:
LISTENER:
[oracle@data admin]$ cat listener.ora
listener =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.2)(PORT = 1521))
)
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC))
)
)
)
SID_LIST_LISTENER =
(SID_DESC =
(GLOBAL_DBNAME = data)
(ORACLE_HOME =/u01/app/oracle/product/19.0.0/dbhome_1)
(SID_NAME = data)
)
)
TNS names:
[oracle@data admin]$
data=
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.2)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = data)
)
)
agent=
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.1)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = agent)
(UR=A)
)
)

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>

Step 5:Copy password file primary to standby

scp orapwagent [email protected]:$ORACLE_HOME/dbs
[oracle@agent dbs]$ scp orapwagent [email protected]:$ORACLE_HOME/dbs
[email protected]’s password:
orapwagent 100% 2048 2.1MB/s 00:00
[oracle@agent dbs]$

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

allocated channel: p2
channel p2: SID=81 device type=DISK

allocated channel: p3
channel p3: SID=85 device type=DISK

allocated channel: p4
channel p4: SID=86 device type=DISK

allocated channel: s1
channel s1: SID=35 device type=DISK

Starting Duplicate Db at 18-NOV-21

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

Total System Global Area 1258290752 bytes

Fixed Size 8896064 bytes
Variable Size 738197504 bytes
Database Buffers 503316480 bytes
Redo Buffers 7880704 bytes
allocated channel: s1
channel s1: SID=35 device type=DISK

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

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

Starting backup at 18-NOV-21
channel p1: starting datafile copy
input datafile file number=00001 name=/u01/app/oracle/oradata/AGENT/datafile/o1_mf_system_jsc8qv1o_.dbf
channel p2: starting datafile copy
input datafile file number=00003 name=/u01/app/oracle/oradata/AGENT/datafile/o1_mf_sysaux_jsc8smrq_.dbf
channel p3: starting datafile copy
input datafile file number=00004 name=/u01/app/oracle/oradata/AGENT/datafile/o1_mf_undotbs1_jsc8tq0b_.dbf
channel p4: starting datafile copy
input datafile file number=00007 name=/u01/app/oracle/oradata/AGENT/datafile/o1_mf_users_jsc8tr78_.dbf
output file name=/u01/app/oracle/oradata/DATA/datafile/o1_mf_users_jsc8tr78_.dbf tag=TAG20211118T080558
channel p4: datafile copy complete, elapsed time: 00:00:08
output file name=/u01/app/oracle/oradata/DATA/datafile/o1_mf_sysaux_jsc8smrq_.dbf tag=TAG20211118T080558
channel p2: datafile copy complete, elapsed time: 00:01:00
output file name=/u01/app/oracle/oradata/DATA/datafile/o1_mf_undotbs1_jsc8tq0b_.dbf tag=TAG20211118T080558
channel p3: datafile copy complete, elapsed time: 00:00:59
output file name=/u01/app/oracle/oradata/DATA/datafile/o1_mf_system_jsc8qv1o_.dbf tag=TAG20211118T080558
channel p1: datafile copy complete, elapsed time: 00:01:22
Finished backup at 18-NOV-21

sql statement: alter system archive log current

contents of Memory Script:
{
switch clone datafile all;
}
executing Memory Script

datafile 1 switched to datafile copy
input datafile copy RECID=1 STAMP=1088928441 file name=/u01/app/oracle/oradata/DATA/datafile/o1_mf_system_jsc8qv1o_.dbf
datafile 3 switched to datafile copy
input datafile copy RECID=2 STAMP=1088928441 file name=/u01/app/oracle/oradata/DATA/datafile/o1_mf_sysaux_jsc8smrq_.dbf
datafile 4 switched to datafile copy
input datafile copy RECID=3 STAMP=1088928441 file name=/u01/app/oracle/oradata/DATA/datafile/o1_mf_undotbs1_jsc8tq0b_.dbf
datafile 7 switched to datafile copy
input datafile copy RECID=4 STAMP=1088928441 file name=/u01/app/oracle/oradata/DATA/datafile/o1_mf_users_jsc8tr78_.dbf
Finished Duplicate Db at 18-NOV-21
released channel: p1
released channel: p2
released channel: p3
released channel: p4
released channel: s1
RMAN> exit

Step 9: Check database can be configured or not:

SQL> select open_mode,name from v$database;

OPEN_MODE NAME
——————– ———
MOUNTED AGENT

SQL> select open_mode,name,database_role from v$database;

OPEN_MODE NAME DATABASE_ROLE
——————– ——— —————-
MOUNTED AGENT PHYSICAL STANDBY

Step 10:- Connect standby database execute MRP process:

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:

Ramkumar’s LinkedIn: https://www.linkedin.com/in/ramkumardba/
LinkedIn Group: https://www.linkedin.com/in/ramkumar-m-0061a0204/
Facebook Page: https://www.facebook.com/Oracleagent-344577549964301
Ramkumar’s Twitter: https://twitter.com/ramkuma02877110
Ramkumar’s Telegram: https://t.me/oracleageant
Ramkumar’s Facebook: https://www.facebook.com/ramkumarram8

Monitor Data Guard

Oracle Data Guard concept make sure high availability,  disaster recovery and data protection of your enterprise database. In Case of disaster or data correction your database can easily run on data guard.

Data Guard can be of two types:

  1. Physical Stand By Database-

Physical Stand by Database provides physical identical copy of your database. It has similar schema and schema. Physical Stand by synchronized by applying redo logs from primary database.

  1. Logical Stand by Database-

Logical Stand by database provides logical information of the data files. It can be of different structure and physical org. Logical Stand by synchronizes by using sql statement and then execute sql statement in logical database.

Query to monitor the data guard status:

Run in Production to get the database role and thread and sequence of archived_log

a. select name, database_role from v$database;

b. select thread#,max(sequence#) from v$archived_log group by thread#;

Run in DR Database:

a. select thread#,max(sequence#) from v$log_history group by thread#;
b. select name,database_role from v$database;

Command to see MRP & RFS services are running or not

select process,status,client_process,thread#,sequence#,BLOCK# from v$managed_standby;

Take current sync status using below query:

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;

If there is any lag between the Production Database versus Standby Database then you have to check the following:

  1. Space in DR using df -h
  2. MRP and RFS status using below query
    select process,status,client_process,thread#,sequence#,BLOCK# from v$managed_standby;

  3. If MRP or RFS has issue, you may have to restart it
    ALTER database RECOVER MANAGED STANDBY DATABASE CANCEL;
    alter database mount standby database;
    alter database recover managed standby database disconnect;

  4. You may have to manually copy the Archive from Production to stand by.

In this way you can monitor the DR and incase of issue you can resolve the issue related to DR.

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:

Ramkumar’s LinkedIn: https://www.linkedin.com/in/ramkumardba/
LinkedIn Group: https://www.linkedin.com/in/ramkumar-m-0061a0204/
Facebook Page: https://www.facebook.com/Oracleagent-344577549964301
Ramkumar’s Twitter : https://twitter.com/ramkuma02877110
Ramkumar’s Telegram: https://t.me/oracleageant
Ramkumar’s Facebook: https://www.facebook.com/ramkumarram8

Monitor Data Guard

Oracle Data Guard concept make sure high availability,  disaster recovery and data protection of your enterprise database. In Case of disaster or data correction your database can easily run on data guard.

Data Guard can be of two types:

  1. Physical Stand By Database-

Physical Stand by Database provides physical identical copy of your database. It has similar schema and schema. Physical Stand by synchronized by applying redo logs from primary database.

  1. Logical Stand by Database-

Logical Stand by database provides logical information of the data files. It can be of different structure and physical org. Logical Stand by synchronizes by using sql statement and then execute sql statement in logical database.

Query to monitor the data guard status:

Run in Production to get the database role and thread and sequence of archived_log

a. select name, database_role from v$database;

b. select thread#,max(sequence#) from v$archived_log group by thread#;

Run in DR Database:

a. select thread#,max(sequence#) from v$log_history group by thread#;
b. select name,database_role from v$database;

Command to see MRP & RFS services are running or not

select process,status,client_process,thread#,sequence#,BLOCK# from v$managed_standby;

Take current sync status using below query:

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;

If there is any lag between the Production Database versus Standby Database then you have to check the following:

  1. Space in DR using df -h
  2. MRP and RFS status using below query
    select process,status,client_process,thread#,sequence#,BLOCK# from v$managed_standby;

  3. If MRP or RFS has issue, you may have to restart it
    ALTER database RECOVER MANAGED STANDBY DATABASE CANCEL;
    alter database mount standby database;
    alter database recover managed standby database disconnect;

  4. You may have to manually copy the Archive from Production to stand by.

In this way you can monitor the DR and incase of issue you can resolve the issue related to DR.

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:

Ramkumar’s LinkedIn: https://www.linkedin.com/in/ramkumardba/
LinkedIn Group: https://www.linkedin.com/in/ramkumar-m-0061a0204/
Facebook Page: https://www.facebook.com/Oracleagent-344577549964301
Ramkumar’s Twitter : https://twitter.com/ramkuma02877110
Ramkumar’s Telegram: https://t.me/oracleageant
Ramkumar’s Facebook: https://www.facebook.com/ramkumarram8

Configure Data guard physical Standby

Oracle Dataguard physical Standby configuration in 19c:

Overview steps:

Step:1 we have to enable the archive log mode ,flashback  on primary databases.

Step:2 Then enable the force logging option.By enabling the force logging option it helps us to capture all the changes made in the database and available for recovery in the redo logs.

Step:3 Add the redolog files for standby database,it is required when you are configuring the standby for maximum protection.

Step:4 Add the listener.ora and tnsnames.ora entries in both primary and standby sides.

Step:5 Change the some set of parameters in primary side,you can change these parameters either by sql or directly make changes in the pfile.

Step:6 Move the password file,pfile from primary to standby using scp.(we can also create the pfile in standby side also by having a single parameter db_name in it)

Step:7 create the directory structure in standby similar to the primary side.

Step:8 Start the standby side database in nomount using the pfile.

Step:9 Connect to the RMAN and duplicate the primary database using the command

duplicate target database for standby from active database dorecover nofilenamecheck;

Step:10 Now our standby database is ready for read only purpose.

Step:11 Then connect to the standby database and start the MRP process using alter database recover managed standby database using current logfile disconnect; This command is used to apply the log files from primary to standby.

Step:12 Verify the current log sequence in both primary and standby sides.

Do the following set of changes in primary side:

Oracle Active Data Guard:

Active Data Guard is a licensed option for Oracle Database Enterprise Edition. … Data Guard automatically synchronizes the primary database and all standby databases by transmitting primary database redo – the information used by every Oracle Database to protect transactions – and applying it to the standby database.

Difference between Oracle Data Guard and Active Data Guard?

Oracle Active Data Guard provides the best data protection and availability for Oracle Database. Active Data Guard enables the offloading of read-only operations, backups, and so on, to an up-to-date physical standby database while also providing disaster protection.

Step:1 Enable the archive log  mode in the primary database.

SQL> alter database archivelog;

Database altered
SQL> archive log list;
Database log mode Archive Mode
Automatic archival Enabled
Archive destination USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence 3
Next log sequence to archive 5
Current log sequence 5

Step:2 Enable the force logging.

SQL>alter database force logging;

Database altered

Step:3 Flashback should be enabled in primary db.

ALTER DATABASE FLASHBACK ON;

Database altered

Check the archive log,force_logging,flashback is enabled.

SQL> select name,force_logging,flashback_on,log_mode from v$database;

   NAME            FORCE_LOGGING       FLASHBACK_ON    LOG_MODE       
--------- ---------------------------  ------------   -------------
CANADA                  YES                  YES       ARCHIVELOG

Step:4 Add the Standby redo log files for standby side.

SQL> alter database add standby logfile thread 1 
group 4('/u01/app/oracle/oradata/redo04.log')size 50m;

Database altered.

SQL> alter database add standby logfile thread 1 
group 5('/u01/app/oracle/oradata/redo05.log')size 50m;

Database altered.

SQL> alter database add standby logfile thread 1 
group 6('/u01/app/oracle/oradata/redo06.log')size 50m;

Database altered.

SQL> alter database add standby logfile thread 1 
group 7('/u01/app/oracle/oradata/redo07.log')size 50m;

Database altered.

Step:5  set the log archive config parameter.

SQL> alter system set log_archive_config='DG_config=(canada,standby)';

System altered.
SQL> show parameter log_archive_config

  NAME                                TYPE                VALUE
--------------------------------- ----------- ------------------------------
log_archive_config                  string      DG_config=(canada,standby)
SQL> show parameter log_archive_dest_2

NAME                                TYPE               VALUE
------------------------------- ----------- ---------------------------
log_archive_dest_2                string    SERVICE=standby NOAFFIRM ASYNC
                                  VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) 
                                                 DB_UNIQUE_NAME=standby
log_archive_dest_20 string
log_archive_dest_21 string
log_archive_dest_22 string
log_archive_dest_23 string
log_archive_dest_24 string
log_archive_dest_25 string
log_archive_dest_26 string

NAME                                TYPE               VALUE
-------------------------------- ----------- -----------------------
log_archive_dest_27 string
log_archive_dest_28 string
log_archive_dest_29 string


Step:6 Set the log archive format = ‘%t_%s_%r.arc ,it determines the name of the archive log file it comes into role when archive log is enabled.

SQL> alter system set log_archive_format='%t_%s_%r.arc' scope=spfile;

System altered.

Step:7 configure the log_archive_process to 30 ,it prevents to avoid any runtime overhead of invoking additional ARCn process,you can set the LOG_ARCHIVE_MAX_PROCESSES

SQL> alter system set log_archive_max_processes=30;

System altered.

Step:8 register the fal_server and fal_client as standby(standby) and physical(primary) databases respectively.

SQL> alter system set fal_server=standby;

System altered.

SQL> alter system set fal_client=physical;

System altered.

Step:9 The standby_file_management initialization parameter plays an important role in the recovery process.

SQL> alter system set standby_file_management=auto;

System altered.

Step:10 change the remote_login_password file as exclusive.

SQL> show parameter remote_login_passwordfile

    NAME                                TYPE                 VALUE
------------------------------------ ----------- ------------------------
remote_login_passwordfile              string              EXCLUSIVE

Step:11 db_file_name_convert and log_file_name_convert, converts the filename of a new datafile and logfile respectively on the primary database to a filename on the standby database.

SQL> alter system set db_file_name_convert='/u01/app/oracle/oradata/canada/'
,'/u01/app/oracle/oradata/standby/' scope=spfile;

System altered.

SQL> alter system set log_file_name_convert='/u01/app/oracle/oradata/canada/'
,'/u01/app/oracle/oradata/standby/' scope=spfile;

System altered.

Step:12 Configure the tns and listener files on both primary and standby databases.

listener entry in the primary database.

tnsnames entry in the primary database.

listener entry in the standby database.

tnsnames entry in the standby database.

Step:13  ping your primary and standby databases from one to another.

Step:14 create the similar directory structures in standby side as of in primary.

/u01/app/oracle/admin/standby/adump

/u01/app/oracle/oradata/standby

/u01/app/oracle/fast_recovery_area/standby

Step:15 Transfer the password files,pfiles  to the standby side through scp.

Step:16 create the initcanada.ora file containing the single parameter db_name=canada in the standby side it is useful to start our database in no mount stage.

Step:17 start the db in nomount stage and connect to the rman to duplicate the target database.

Step:18 Duplicate the target database using the following command,

duplicate target database for standby from active database 
dorecover nofilenamecheck;
[oracle@localhost admin]$ 
rman target sys/Pass#1234@canada auxiliary sys/Pass#1234@standby

Recovery Manager: Release 19.0.0.0.0 - Production on Wed Feb 17 07:28:58 2021
Version 19.3.0.0.0

Copyright (c) 1982, 2019, Oracle and/or its affiliates. All rights reserved.

connected to target database: CANADA (DBID=215384084)
connected to auxiliary database: CANADA (not mounted)

RMAN> 
duplicate target database for standby from active database dorecover nofilenamecheck;

Starting Duplicate Db at 17-FEB-21
using target database control file instead of recovery catalog
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: SID=35 device type=DISK
current log archived

contents of Memory Script:
{
backup as copy reuse
passwordfile auxiliary format
'/u01/app/oracle/product/19.0.0/dbhome_1/dbs/orapwcanada';
}
executing Memory Script

Starting backup at 17-FEB-21
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=112 device type=DISK
Finished backup at 17-FEB-21

contents of Memory Script:
{
restore clone from service 'canada' standby controlfile;
}
executing Memory Script

Starting restore at 17-FEB-21
using channel ORA_AUX_DISK_1

channel ORA_AUX_DISK_1: starting datafile backup set restore
channel ORA_AUX_DISK_1: using network backup set from service canada
channel ORA_AUX_DISK_1: restoring control file
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:09
output file name=/u01/app/oracle/product/19.0.0/dbhome_1/dbs/cntrlcanada.dbf
Finished restore at 17-FEB-21

contents of Memory Script:
{
sql clone 'alter database mount standby database';
}
executing Memory Script

sql statement: alter database mount standby database
RMAN-05158: WARNING: auxiliary (datafile) file name 
/u01/app/oracle/oradata/CANADA/datafile/o1_mf_system_j2qt47mc_.dbf 
conflicts with a file used by the target database
RMAN-05158: WARNING: auxiliary (datafile) file name 
/u01/app/oracle/oradata/CANADA/datafile/o1_mf_sysaux_j2qt5yyz_.dbf 
conflicts with a file used by the target database
RMAN-05158: WARNING: auxiliary (datafile) file name 
/u01/app/oracle/oradata/CANADA/datafile/o1_mf_undotbs1_j2qt6r4g_.dbf 
conflicts with a file used by the target database
RMAN-05158: WARNING: auxiliary (datafile) file name 
/u01/app/oracle/oradata/CANADA/datafile/o1_mf_users_j2qt6s6x_.dbf 
conflicts with a file used by the target database
RMAN-05158: WARNING: auxiliary (tempfile) file name 
/u01/app/oracle/oradata/CANADA/datafile/o1_mf_temp_j2qt8zx2_.tmp 
conflicts with a file used by the target database

contents of Memory Script:
{
set newname for tempfile 1 to 
"/u01/app/oracle/oradata/CANADA/datafile/o1_mf_temp_j2qt8zx2_.tmp";
switch clone tempfile all;
set newname for datafile 1 to 
"/u01/app/oracle/oradata/CANADA/datafile/o1_mf_system_j2qt47mc_.dbf";
set newname for datafile 3 to 
"/u01/app/oracle/oradata/CANADA/datafile/o1_mf_sysaux_j2qt5yyz_.dbf";
set newname for datafile 4 to 
"/u01/app/oracle/oradata/CANADA/datafile/o1_mf_undotbs1_j2qt6r4g_.dbf";
set newname for datafile 7 to 
"/u01/app/oracle/oradata/CANADA/datafile/o1_mf_users_j2qt6s6x_.dbf";
restore
from nonsparse from service 
'canada' clone database
;
sql 'alter system archive log current';
}
executing Memory Script

executing command: SET NEWNAME

renamed tempfile 1 to 
/u01/app/oracle/oradata/CANADA/datafile/o1_mf_temp_j2qt8zx2_.tmp in control file

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

Starting restore at 17-FEB-21
using channel ORA_AUX_DISK_1

channel ORA_AUX_DISK_1: starting datafile backup set restore
channel ORA_AUX_DISK_1: using network backup set from service canada
channel ORA_AUX_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_AUX_DISK_1: restoring datafile 00001 to 
/u01/app/oracle/oradata/CANADA/datafile/o1_mf_system_j2qt47mc_.dbf
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:04:09
channel ORA_AUX_DISK_1: starting datafile backup set restore
channel ORA_AUX_DISK_1: using network backup set from service canada
channel ORA_AUX_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_AUX_DISK_1: restoring datafile 00003 to 
/u01/app/oracle/oradata/CANADA/datafile/o1_mf_sysaux_j2qt5yyz_.dbf
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:01:37
channel ORA_AUX_DISK_1: starting datafile backup set restore
channel ORA_AUX_DISK_1: using network backup set from service canada
channel ORA_AUX_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_AUX_DISK_1: restoring datafile 00004 to 
/u01/app/oracle/oradata/CANADA/datafile/o1_mf_undotbs1_j2qt6r4g_.dbf
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:26
channel ORA_AUX_DISK_1: starting datafile backup set restore
channel ORA_AUX_DISK_1: using network backup set from service canada
channel ORA_AUX_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_AUX_DISK_1: restoring datafile 00007 to 
/u01/app/oracle/oradata/CANADA/datafile/o1_mf_users_j2qt6s6x_.dbf
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:01
Finished restore at 17-FEB-21

sql statement: alter system archive log current
current log archived

contents of Memory Script:
{
restore clone force from service 'canada' 
archivelog from scn 2091702;
switch clone datafile all;
}
executing Memory Script

Starting restore at 17-FEB-21
using channel ORA_AUX_DISK_1

channel ORA_AUX_DISK_1: starting archived log restore to default destination
channel ORA_AUX_DISK_1: using network backup set from service canada
channel ORA_AUX_DISK_1: restoring archived log
archived log thread=1 sequence=10
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:04
channel ORA_AUX_DISK_1: starting archived log restore to default destination
channel ORA_AUX_DISK_1: using network backup set from service canada
channel ORA_AUX_DISK_1: restoring archived log
archived log thread=1 sequence=11
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:01
Finished restore at 17-FEB-21

datafile 1 switched to datafile copy
input datafile copy RECID=1 STAMP=1064734612 file name=
/u01/app/oracle/oradata/CANADA/datafile/o1_mf_system_j2qt47mc_.dbf
datafile 3 switched to datafile copy
input datafile copy RECID=2 STAMP=1064734613 file name=
/u01/app/oracle/oradata/CANADA/datafile/o1_mf_sysaux_j2qt5yyz_.dbf
datafile 4 switched to datafile copy
input datafile copy RECID=3 STAMP=1064734613 file name=
/u01/app/oracle/oradata/CANADA/datafile/o1_mf_undotbs1_j2qt6r4g_.dbf
datafile 7 switched to datafile copy
input datafile copy RECID=4 STAMP=1064734613 file name=
/u01/app/oracle/oradata/CANADA/datafile/o1_mf_users_j2qt6s6x_.dbf

contents of Memory Script:
{
set until scn 2092999;
recover
standby
clone database
delete archivelog
;
}
executing Memory Script

executing command: SET until clause

Starting recover at 17-FEB-21
using channel ORA_AUX_DISK_1

starting media recovery

archived log for thread 1 with sequence 10 is already on disk as file 
/u01/app/oracle/product/19.0.0/dbhome_1/dbs/arch1_10_1064697431.dbf
archived log for thread 1 with sequence 11 is already on disk as file 
/u01/app/oracle/product/19.0.0/dbhome_1/dbs/arch1_11_1064697431.dbf
archived log file name=
/u01/app/oracle/product/19.0.0/dbhome_1/dbs/arch1_10_1064697431.dbf 
thread=1 sequence=10
archived log file name=
/u01/app/oracle/product/19.0.0/dbhome_1/dbs/arch1_11_1064697431.dbf 
thread=1 sequence=11
media recovery complete, elapsed time: 00:00:03
Finished recover at 17-FEB-21

contents of Memory Script:
{
delete clone force archivelog all;
}
executing Memory Script

released channel: ORA_DISK_1
released channel: ORA_AUX_DISK_1
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=112 device type=DISK
deleted archived log
archived log file name=/u01/app/oracle/product/19.0.0/dbhome_1/
dbs/arch1_10_1064697431.dbf RECID=1 STAMP=1064734609
deleted archived log
archived log file name=/u01/app/oracle/product/19.0.0/dbhome_1/
dbs/arch1_11_1064697431.dbf RECID=2 STAMP=1064734611
Deleted 2 objects

Finished duplicate at 17-FEB-21

Yes,we have created the physical standby database successfully.

Step:19 Then mount the database and open the database in the read only mode.check the name,open_mode,database_role in the standby database,in the role it should have the value as physical_standby.

Step:20 Check for the max(sequence#) from the v$archived_log view in both primary and standby databases.

SQL> select max(sequence#) from v$archived_log;

MAX(SEQUENCE#)
--------------
       11

Step:21 Apply the log files using MRP process.

SQL> alter database recover managed standby database using current 
logfile disconnect;


Database altered.

Step:22 check for the sequence no which is applied in standby.

SQL> SELECT sequence#, first_time, next_time, applied FROM v$archived_log 
ORDER BY sequence#;

SEQUENCE#    FIRST_TIM  NEXT_TIME APPLIED
----------   --------- --------- ---------
     10      17-FEB-21 17-FEB-21    YES
     11      17-FEB-21 17-FEB-21    YES

Step:23 We can check the difference in sequence using the following query.

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; 
SQL> 

 Thread    Last Sequence Received   Last Sequence Applied  Difference
---------- ----------------------    --------------------- ----------
  1                 11                         11                 0

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:
Ramkumar’s LinkedIn: https://www.linkedin.com/in/ramkumardba/
LinkedIn Group: https://www.linkedin.com/in/ramkumar-m-0061a0204/
Facebook Page: https://www.facebook.com/Oracleagent-344577549964301
Ramkumar’s Twitter : https://twitter.com/ramkuma02877110
Ramkumar’s Telegram: https://t.me/oracleageant
Ramkumar’s Facebook: https://www.facebook.com/ramkumarram8

19C RMAN CONCEPTS

rman

19c RMAN CONCEPTS:

RECOVER DATAFILE WITHOUT RMAN BACKUP
RECOVER THROUGH RESETLOGS
BLOCK CHANGE TRACKING
RECOVER LOSS OF ALL CONTROL FILE USING AUTO BACKUP
RECOVER LOSS OF ALL ONLINE REDO LOGS 
POINT IN TIME RECOVERY 
RMAN-06183: datafile or datafile copy xyz.dbf larger than MAXSETSIZE
RMAN Database Restore ASM
DATABASE INCARNATION USING RMAN
Restore Tablespace using RMAN
RESTORE SPFILE USING RMAN
RMAN backup Full Database
RMAN Backup Tablespace
RMAN Backup Particular Datafile
RMAN Backup Spfile
RMAN Backup Current Control file
RMAN Backup Archive log Until Sequence
RMAN Backup Archive log Between Sequence
RMAN Backup Archive log Between SCN
RMAN Backup Archive log Until SCN
RMAN Backup Database Plus Archive log
RMAN Backup Database Includes A Control file
RMAN Backup Archive log and All Delete Input
RMAN Backup Archive log All and Skip Inaccessible
LEVEL 0 and LEVEL 1 Backup And Recovery using RMAN
CROSSCHECK BACKUPS Using RMAN
RESTORE CONTROL FILE USING RMAN
Backup-based Cloning of a database using RMAN
RECOVERY CATALOG DATABASE IN RMAN
RMAN ORA ERRORS
DBVERIFY
END-OF-FILE ERROR
LEVEL 0 INCREMENTAL BACKUP
point-in-time recovery using RMAN in 19c
Recover a loss of all online redo log files

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:

Ramkumar’s LinkedIn: https://www.linkedin.com/in/ramkumardba/
LinkedIn Group: https://www.linkedin.com/in/ramkumar-m-0061a0204/
Facebook Page: https://www.facebook.com/Oracleagent-344577549964301
Ramkumar’s Twitter: https://twitter.com/ramkuma02877110
Ramkumar’s Telegram: https://t.me/oracleageant
Ramkumar’s Facebook: https://www.facebook.com/ramkumarram8