Oracle Database Releases and Support Timelines
Currently Oracle 19c database version is stable and long term support
Monday, May 31, 2021
Oracle Database Releases and Support Timelines
I am Anurag Pandey, working as Cloud Database Architect | Migration & Modernization Expert .
Database Administration and Implementation. Provide technical guidance and work with global team.
Extensive Experience in Database Installation and Configuration, Real Application Cluster (RAC), ASM, Dataguard , Patching, Upgrade, RMAN, Performance Tuning, Backup and Recovery, Azure, Google, AWS Cloud, AWS RDS, MySQL, Database Migration Service(DMS),CloudEndure - MGN, Azure Site Recovery, Azure Migrate, Datapump, terraform automation, Ansible
Migrate Oracle Database workload to Cloud : Re-host (Lift and Shift),Re-platform, Refactor
Migrate database workload to Cloud
When Migrating database and application to cloud ,it is very important to keep in mind the business goals ,application and database capabilities and cost of migration .
There are different strategies for different application and database migrations goals .
- Re-host (Lift and Shift) - Oracle on EC2
Same administration experience as on-premise
Full Control over the environment
All feature is available
All version is supported
- Re-platform - Oracle on RDS
Optimized Architecture
Database Install and Maintenance
Automated Patching & Upgrade
Automated Backup
High Availability
OS Patching & Maintenance
Scaling
- Refactor (Rearchitect) - Adopt Cloud Native Services
Amazon Aurora
Amazon Redshift
PostgreSQL
Other database engine
Eliminate Oracle Licensing Cost
I am Anurag Pandey, working as Cloud Database Architect | Migration & Modernization Expert .
Database Administration and Implementation. Provide technical guidance and work with global team.
Extensive Experience in Database Installation and Configuration, Real Application Cluster (RAC), ASM, Dataguard , Patching, Upgrade, RMAN, Performance Tuning, Backup and Recovery, Azure, Google, AWS Cloud, AWS RDS, MySQL, Database Migration Service(DMS),CloudEndure - MGN, Azure Site Recovery, Azure Migrate, Datapump, terraform automation, Ansible
Sunday, May 30, 2021
Cloud Migration Lifecycle
Cloud Database Migration Lifecycle
Migration to cloud have two major phase :-
- Staging
- Cutover
Migration Lifecycle
========================
- Migration Assessment (3 to 5 Weeks )
Migration Readiness Assessment
Understand Business acceptation
Note down current know issue, pain area & challenge
Total Cost Of ownership (TCO)
Application Dependency Mapping
Compliance & Risk
- Migration Planning (6-8 Weeks)
Application Portfolio Assessment
Target Architecture
Operating Model
Build Cloud Landing Zone
Migration Plan
Migration Wave Sequencing
- Migration (9++ Weeks)
Migrate Workload
Validate Migrated Workload
Cutover
Decommission
- Optimization
Cost Optimization
Performance Optimization
Recommended Best Practice
Backup setup + Etc.
Documentation & Knowledge Sharing
High Level Migration Timeline
=================================
- Migration Assessment (3 to 5 Weeks )
- Migration Planning (6 to 8 Weeks)
- Migration + Optimization (9++ Weeks)
*Timeline will be change and depends upon inventory and complexity
I am Anurag Pandey, working as Cloud Database Architect | Migration & Modernization Expert .
Database Administration and Implementation. Provide technical guidance and work with global team.
Extensive Experience in Database Installation and Configuration, Real Application Cluster (RAC), ASM, Dataguard , Patching, Upgrade, RMAN, Performance Tuning, Backup and Recovery, Azure, Google, AWS Cloud, AWS RDS, MySQL, Database Migration Service(DMS),CloudEndure - MGN, Azure Site Recovery, Azure Migrate, Datapump, terraform automation, Ansible
Oracle Database Migration Tools & Techniques
Oracle Database Migration Tools & Techniques
Oracle Native Migrations Tools
====================================
• Data Pump
• Transportable Tablespaces
• Full Transportable Export/Import
• Data Guard
• Incremental Backups
• Oracle GoldenGate
• RMAN
AWS Native Migration Tools
=================================
• Database Migration Service (DMS)
• CloudEndure
• AWS Application Migration Service
I am Anurag Pandey, working as Cloud Database Architect | Migration & Modernization Expert .
Database Administration and Implementation. Provide technical guidance and work with global team.
Extensive Experience in Database Installation and Configuration, Real Application Cluster (RAC), ASM, Dataguard , Patching, Upgrade, RMAN, Performance Tuning, Backup and Recovery, Azure, Google, AWS Cloud, AWS RDS, MySQL, Database Migration Service(DMS),CloudEndure - MGN, Azure Site Recovery, Azure Migrate, Datapump, terraform automation, Ansible
Thursday, May 27, 2021
Oracle standby database Error ORA-10458: standby database requires recovery,ORA-01196,ORA-01110
Oracle database Error ORA-10458: standby database requires recovery
ORA-01196: file 1 is inconsistent due to a failed media recovery session
ORA-01110: data file 1
SQL> set numwidth 30;
SQL> set pagesize 50000;
SQL> alter session set nls_date_format = 'DD-MON-RRRR HH24:MI:SS';
select status,checkpoint_change#,checkpoint_time, resetlogs_change#, resetlogs_time, count(*), fuzzy from v$datafile_header group by status,checkpoint_change#,checkpoint_time, resetlogs_change#, resetlogs_time, fuzzy;
Session altered.
SQL>
STATUS CHECKPOINT_CHANGE# CHECKPOINT_TIME RESETLOGS_CHANGE# RESETLOGS_TIME COUNT(*) FUZ
------- ------------------------------ -------------------- ------------------------------ -------------------- ------------------------------ ---
ONLINE 2472666 26-MAY-2021 11:19:09 1408558 29-APR-2021 11:19:37 4 YES
SQL> select min(fhrba_Seq), max(fhrba_Seq) from X$KCVFH;
MIN(FHRBA_SEQ) MAX(FHRBA_SEQ)
------------------------------ ------------------------------
63 63
SQL>
SQL> SELECT SEQUENCE#, APPLIED FROM V$ARCHIVED_LOG;
SEQUENCE# APPLIED
---------- ---------
1 NO
2 NO
20 YES
21 YES
19 YES
22 YES
23 YES
24 YES
25 YES
26 YES
27 YES
28 YES
29 YES
30 YES
31 YES
32 YES
33 YES
34 YES
36 YES
35 YES
38 YES
40 YES
41 YES
39 YES
37 YES
42 YES
43 YES
44 YES
45 YES
46 YES
47 YES
47 YES
46 YES
48 YES
48 YES
49 YES
49 NO
50 NO
50 YES
52 YES
51 YES
53 YES
54 YES
55 YES
56 YES
57 YES
58 YES
59 YES
60 YES
61 YES
62 YES
SQL>
SQL> alter database open read only;
alter database open read only
*
ERROR at line 1:
ORA-10458: standby database requires recovery
ORA-01196: file 1 is inconsistent due to a failed media recovery session
ORA-01110: data file 1: '/u01/app/oracle/oradata/india_stby/system01.dbf'
SQL>
SQL>
SQL> recover standby database;
ORA-00279: change 2472666 generated at 05/26/2021 11:19:09 needed for thread 1
ORA-00289: suggestion :
/u01/app/oracle/fra/INDIA_STBY/archivelog/2021_05_27/o1_mf_1_63_%u_.arc
ORA-00280: change 2472666 for thread 1 is in sequence #63
Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
cancel
ORA-01547: warning: RECOVER succeeded but OPEN RESETLOGS would get error below
ORA-01196: file 1 is inconsistent due to a failed media recovery session
ORA-01110: data file 1: '/u01/app/oracle/oradata/india_stby/system01.dbf'
ORA-01112: media recovery not started
SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE FINISH;
Database altered.
SQL> alter database open read only;
Database altered.
SQL> select * from hr.abc;
ID NAME
---------- --------------------------------------------------
2 From standby after primary
1 A
3 from primary after convert
SQL> select name ,open_mode ,database_role from v$database;
NAME OPEN_MODE DATABASE_ROLE
--------- -------------------- ----------------
INDIA READ ONLY PHYSICAL STANDBY
SQL>
SQL>
SQL>
SQL> shut immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL>
SQL> startup
ORA-32004: obsolete or deprecated parameter(s) specified for RDBMS instance
ORACLE instance started.
Total System Global Area 1048576000 bytes
Fixed Size 8628640 bytes
Variable Size 369100384 bytes
Database Buffers 662700032 bytes
Redo Buffers 8146944 bytes
Database mounted.
Database opened.
SQL> select name ,open_mode ,database_role from v$database;
NAME OPEN_MODE DATABASE_ROLE
--------- -------------------- ----------------
INDIA READ ONLY PHYSICAL STANDBY
We can open database in read write mode using below command and this is also called manual failover
SQL> alter database activate standby database;
I am Anurag Pandey, working as Cloud Database Architect | Migration & Modernization Expert .
Database Administration and Implementation. Provide technical guidance and work with global team.
Extensive Experience in Database Installation and Configuration, Real Application Cluster (RAC), ASM, Dataguard , Patching, Upgrade, RMAN, Performance Tuning, Backup and Recovery, Azure, Google, AWS Cloud, AWS RDS, MySQL, Database Migration Service(DMS),CloudEndure - MGN, Azure Site Recovery, Azure Migrate, Datapump, terraform automation, Ansible
Wednesday, May 26, 2021
AWS Application Migration Service Error : Failed Installing the AWS Replication Agent
AWS Application Migration Service Error : Failed Installing the AWS Replication Agent
[root@ip ~]# python3 aws-replication-installer-init.py
The installation of the AWS Replication Agent has started.
AWS Region Name: XXXXXXXXXXXXXXX
AWS Access Key ID: XXXXXXXXXXXXXXX
AWS Secret Access Key:
Identifying volumes for replication.
Choose the disks you want to replicate. Your disks are: /dev/xvda
To replicate some of the disks, type the path of the disks, separated with a comma (for example, /dev/sda,/dev/sdb). To replicate all disks, press Enter:
Identified volume for replication: /dev/xvda of size 30 GiB
All volumes for replication were successfully identified.
Downloading the AWS Replication Agent onto the source server... Finished.
Installing the AWS Replication Agent onto the source server...
Error: Failed Installing the AWS Replication Agent
Installation failed.
Learn more about installation issues in our documentation at https://docs.aws.amazon.com/mgn/latest/ug/Error-Installtion-Failed.html
[root@ip ~]#
Solutions: Install package elfutils.* ,elfutils-libelf-devel
[root@ip ~]# python3 aws-replication-installer-init.py
The installation of the AWS Replication Agent has started.
AWS Region Name: XXXXXXXXXXXXXXX
AWS Access Key ID: XXXXXXXXXXXXXXX
AWS Secret Access Key:
Identifying volumes for replication.
Choose the disks you want to replicate. Your disks are: /dev/xvda
To replicate some of the disks, type the path of the disks, separated with a comma (for example, /dev/sda,/dev/sdb). To replicate all disks, press Enter:
Identified volume for replication: /dev/xvda of size 30 GiB
All volumes for replication were successfully identified.
Downloading the AWS Replication Agent onto the source server... Finished.
Installing the AWS Replication Agent onto the source server... Finished.
Syncing the source server with the Application Migration Service Console... Finished.
The following is the source server ID: XXXXXXXXXXXXXX
The AWS Replication Agent was successfully installed.
[root@ip ~]#
I am Anurag Pandey, working as Cloud Database Architect | Migration & Modernization Expert .
Database Administration and Implementation. Provide technical guidance and work with global team.
Extensive Experience in Database Installation and Configuration, Real Application Cluster (RAC), ASM, Dataguard , Patching, Upgrade, RMAN, Performance Tuning, Backup and Recovery, Azure, Google, AWS Cloud, AWS RDS, MySQL, Database Migration Service(DMS),CloudEndure - MGN, Azure Site Recovery, Azure Migrate, Datapump, terraform automation, Ansible
Convert a Snapshot Standby Back to a Physical Standby using DGMGRL
Converting a Snapshot Standby Back to a Physical Standby using DGMGRL
DGMGRL> convert database india_stby to physical standby;
Converting database "india_stby" to a Physical Standby database, please wait...
Operation requires shut down of instance "india_stby" on database "india_stby"
Shutting down instance "india_stby"...
Connected to "india_stby"
Database closed.
Database dismounted.
ORACLE instance shut down.
Operation requires start up of instance "india_stby" on database "india_stby"
Starting instance "india_stby"...
ORA-32004: obsolete or deprecated parameter(s) specified for RDBMS instance
ORACLE instance started.
Database mounted.
Connected to "india_stby"
Continuing to convert database "india_stby" ...
Database "india_stby" converted successfully
DGMGRL>
DGMGRL> sql 'alter system switch logfile';
Succeeded.
DGMGRL> show configuration
Configuration - india
Protection Mode: MaxPerformance
Members:
india - Primary database
india_stby - Physical standby database
Warning: ORA-16854: apply lag could not be determined
Fast-Start Failover: DISABLED
Configuration Status:
WARNING (status updated 45 seconds ago)
DGMGRL>
DGMGRL> show configuration;
Configuration - india
Protection Mode: MaxPerformance
Members:
india - Primary database
india_stby - Physical standby database
Fast-Start Failover: DISABLED
Configuration Status:
SUCCESS (status updated 21 seconds ago)
DGMGRL>
on standby database table hr.abc_snap table does not exist which we created when database role was snapshot standby
===================================================================
[oracle@ip-10-0-1-37 ~]$ sqlplus "/as sysdba"
SQL*Plus: Release 12.2.0.1.0 Production on Wed May 26 11:19:33 2021
Copyright (c) 1982, 2016, Oracle. All rights reserved.
Connected to:
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production
SQL> select * from hr.abc_snap;
select * from hr.abc_snap
*
ERROR at line 1:
ORA-00942: table or view does not exist
SQL> select name ,open_mode,database_role from v$database;
NAME OPEN_MODE DATABASE_ROLE
--------- -------------------- ----------------
INDIA READ ONLY WITH APPLY PHYSICAL STANDBY
SQL>
I am Anurag Pandey, working as Cloud Database Architect | Migration & Modernization Expert .
Database Administration and Implementation. Provide technical guidance and work with global team.
Extensive Experience in Database Installation and Configuration, Real Application Cluster (RAC), ASM, Dataguard , Patching, Upgrade, RMAN, Performance Tuning, Backup and Recovery, Azure, Google, AWS Cloud, AWS RDS, MySQL, Database Migration Service(DMS),CloudEndure - MGN, Azure Site Recovery, Azure Migrate, Datapump, terraform automation, Ansible
Convert a Physical Standby to a Snapshot Standby using DGMGRL
Convert a Physical Standby to a Snapshot Standby using DGMGRL
[oracle@ip-10-0-1-12 ~]$ dgmgrl
DGMGRL for Linux: Release 12.2.0.1.0 - Production on Wed May 26 11:07:46 2021
Copyright (c) 1982, 2017, Oracle and/or its affiliates. All rights reserved.
Welcome to DGMGRL, type "help" for information.
DGMGRL> connect sys/@india
Password:
Connected to "india"
Connected as SYSDBA.
DGMGRL>
DGMGRL>
DGMGRL>
DGMGRL> show configuration;
Configuration - india
Protection Mode: MaxPerformance
Members:
india - Primary database
india_stby - Physical standby database
Fast-Start Failover: DISABLED
Configuration Status:
SUCCESS (status updated 43 seconds ago)
DGMGRL>
DGMGRL>
DGMGRL> validate database india;
Database Role: Primary database
Ready for Switchover: Yes
Flashback Database Status:
india: Off
Managed by Clusterware:
india: NO
Warning: Ensure primary database's StaticConnectIdentifier property
is configured properly so that the primary database can be restarted
by DGMGRL after switchover
DGMGRL>
DGMGRL> validate database india_stby;
Database Role: Physical standby database
Primary Database: india
Ready for Switchover: Yes
Ready for Failover: Yes (Primary Running)
Flashback Database Status:
india : Off
india_stby: On
Managed by Clusterware:
india : NO
india_stby: NO
Warning: Ensure primary database's StaticConnectIdentifier property
is configured properly so that the primary database can be restarted
by DGMGRL after switchover
Current Log File Groups Configuration:
Thread # Online Redo Log Groups Standby Redo Log Groups Status
(india) (india_stby)
1 3 3 Insufficient SRLs
Future Log File Groups Configuration:
Thread # Online Redo Log Groups Standby Redo Log Groups Status
(india_stby) (india)
1 3 2 Insufficient SRLs
DGMGRL>
DGMGRL>
DGMGRL>
DGMGRL> convert database india_stby to snapshot standby;
Converting database "india_stby" to a Snapshot Standby database, please wait...
Database "india_stby" converted successfully
DGMGRL>
On Standby database
====================
SQL> select name ,open_mode,database_role from v$database;
NAME OPEN_MODE DATABASE_ROLE
--------- -------------------- ----------------
INDIA READ ONLY WITH APPLY PHYSICAL STANDBY
SQL> /
select name ,open_mode,database_role from v$database
*
ERROR at line 1:
ORA-03135: connection lost contact
Process ID: 5938
Session ID: 272 Serial number: 59433
SQL> exit
Disconnected from Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production
[oracle@ip-10-0-1-37 ~]$
[oracle@ip-10-0-1-37 ~]$ sqlplus "/as sysdba"
SQL*Plus: Release 12.2.0.1.0 Production on Wed May 26 11:11:02 2021
Copyright (c) 1982, 2016, Oracle. All rights reserved.
Connected to:
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production
SQL> select name ,open_mode,database_role from v$database;
NAME OPEN_MODE DATABASE_ROLE
--------- -------------------- ----------------
INDIA READ WRITE SNAPSHOT STANDBY
SQL>
SQL>
SQL>
SQL> create table hr.abc_snap as select * from hr.abc;
Table created.
SQL>
SQL> select * from hr.abc_snap;
ID NAME
---------- --------------------------------------------------
2 From standby after primary
1 A
3 from primary after convert
SQL>
I am Anurag Pandey, working as Cloud Database Architect | Migration & Modernization Expert .
Database Administration and Implementation. Provide technical guidance and work with global team.
Extensive Experience in Database Installation and Configuration, Real Application Cluster (RAC), ASM, Dataguard , Patching, Upgrade, RMAN, Performance Tuning, Backup and Recovery, Azure, Google, AWS Cloud, AWS RDS, MySQL, Database Migration Service(DMS),CloudEndure - MGN, Azure Site Recovery, Azure Migrate, Datapump, terraform automation, Ansible
Tuesday, May 25, 2021
Validate oracle database using dgmgrl utility before and after switchover
Validate primary/standby database using dgmgrl utility before and after switchover
[oracle@ip-10-0-1-12 ~]$ dgmgrl
DGMGRL for Linux: Release 12.2.0.1.0 - Production on Tue May 25 14:34:37 2021
Copyright (c) 1982, 2017, Oracle and/or its affiliates. All rights reserved.
Welcome to DGMGRL, type "help" for information.
DGMGRL> connect sys/@india
Password:
Connected to "india"
Connected as SYSDBA.
DGMGRL>
DGMGRL>
DGMGRL>
DGMGRL>
DGMGRL> show configuration;
Configuration - india
Protection Mode: MaxPerformance
Members:
india - Primary database
india_stby - Physical standby database
Fast-Start Failover: DISABLED
Configuration Status:
SUCCESS (status updated 47 seconds ago)
DGMGRL>
DGMGRL>
DGMGRL> validate database verbose india;
Database Role: Primary database
Ready for Switchover: Yes
Flashback Database Status:
india: Off
Capacity Information:
Database Instances Threads
india 1 1
Managed by Clusterware:
india: NO
Warning: Ensure primary database's StaticConnectIdentifier property
is configured properly so that the primary database can be restarted
by DGMGRL after switchover
Temporary Tablespace File Information:
india TEMP Files: 1
Data file Online Move in Progress:
india: No
Transport-Related Information:
Transport On: Yes
Log Files Cleared:
india Standby Redo Log Files: Cleared
Automatic Diagnostic Repository Errors:
Error india
No logging operation NO
Control file corruptions NO
System data file missing NO
System data file corrupted NO
System data file offline NO
User data file missing NO
User data file corrupted NO
User data file offline NO
Block Corruptions found NO
DGMGRL>
DGMGRL> validate database verbose india_stby;
Database Role: Physical standby database
Primary Database: india
Ready for Switchover: Yes
Ready for Failover: Yes (Primary Running)
Flashback Database Status:
india : Off
india_stby: On
Capacity Information:
Database Instances Threads
india 1 1
india_stby 1 1
Managed by Clusterware:
india : NO
india_stby: NO
Warning: Ensure primary database's StaticConnectIdentifier property
is configured properly so that the primary database can be restarted
by DGMGRL after switchover
Temporary Tablespace File Information:
india TEMP Files: 1
india_stby TEMP Files: 1
Data file Online Move in Progress:
india: No
india_stby: No
Standby Apply-Related Information:
Apply State: Running
Apply Lag: 0 seconds (computed 1 second ago)
Apply Delay: 0 minutes
Transport-Related Information:
Transport On: Yes
Gap Status: No Gap
Transport Lag: 0 seconds (computed 1 second ago)
Transport Status: Success
Log Files Cleared:
india Standby Redo Log Files: Cleared
india_stby Online Redo Log Files: Cleared
india_stby Standby Redo Log Files: Available
Current Log File Groups Configuration:
Thread # Online Redo Log Groups Standby Redo Log Groups Status
(india) (india_stby)
1 3 3 Insufficient SRLs
Future Log File Groups Configuration:
Thread # Online Redo Log Groups Standby Redo Log Groups Status
(india_stby) (india)
1 3 2 Insufficient SRLs
Current Configuration Log File Sizes:
Thread # Smallest Online Redo Smallest Standby Redo
Log File Size Log File Size
(india) (india_stby)
1 200 MBytes 200 MBytes
Future Configuration Log File Sizes:
Thread # Smallest Online Redo Smallest Standby Redo
Log File Size Log File Size
(india_stby) (india)
1 200 MBytes 200 MBytes
Apply-Related Property Settings:
Property india Value india_stby Value
DelayMins 0 0
ApplyParallel AUTO AUTO
ApplyInstances 0 0
Transport-Related Property Settings:
Property india Value india_stby Value
LogXptMode ASYNC ASYNC
Dependency <empty> <empty>
DelayMins 0 0
Binding optional optional
MaxFailure 0 0
MaxConnections 1 1
ReopenSecs 300 300
NetTimeout 30 30
RedoCompression DISABLE DISABLE
LogShipping ON ON
Automatic Diagnostic Repository Errors:
Error india india_stby
No logging operation NO NO
Control file corruptions NO NO
SRL Group Unavailable NO NO
System data file missing NO NO
System data file corrupted NO NO
System data file offline NO NO
User data file missing NO NO
User data file corrupted NO NO
User data file offline NO NO
Block Corruptions found NO NO
DGMGRL>
I am Anurag Pandey, working as Cloud Database Architect | Migration & Modernization Expert .
Database Administration and Implementation. Provide technical guidance and work with global team.
Extensive Experience in Database Installation and Configuration, Real Application Cluster (RAC), ASM, Dataguard , Patching, Upgrade, RMAN, Performance Tuning, Backup and Recovery, Azure, Google, AWS Cloud, AWS RDS, MySQL, Database Migration Service(DMS),CloudEndure - MGN, Azure Site Recovery, Azure Migrate, Datapump, terraform automation, Ansible
Oracle Dataguard Switchover using DGMGRL - Error : ORA-01017: invalid username/password; logon denied
DGMGRL Error : ORA-01017: invalid username/password; logon denied
DGMGRL> switchover to india_stby;
Performing switchover NOW, please wait...
Operation requires a connection to database "india_stby"
Connecting ...
ORA-01017: invalid username/password; logon denied
Warning: You are no longer connected to ORACLE.
connect to instance "india_stby" of database "india_stby"
we have to provide sys user credentials (username/password pair) to connect to database in DGMGRL also check sec_case_sensitive_logon database parameter .
In order to resolve ORA-01017 this error , we have to connect to dgmgrl utility using below method :
[oracle@ip-10-0-1-12 ~]$ dgmgrl
DGMGRL for Linux: Release 12.2.0.1.0 - Production on Tue May 25 14:02:07 2021
Copyright (c) 1982, 2017, Oracle and/or its affiliates. All rights reserved.
Welcome to DGMGRL, type "help" for information.
DGMGRL> connect sys@india
Password:
Connected to "india"
Connected as SYSDBA.
DGMGRL>
DGMGRL>
DGMGRL>
DGMGRL> show configuration;
Configuration - india
Protection Mode: MaxPerformance
Members:
india - Primary database
india_stby - Physical standby database
Fast-Start Failover: DISABLED
Configuration Status:
SUCCESS (status updated 48 seconds ago)
DGMGRL>
DGMGRL>
DGMGRL> switchover to india_stby
Performing switchover NOW, please wait...
Operation requires a connection to database "india_stby"
Connecting ...
Connected to "india_stby"
Connected as SYSDBA.
New primary database "india_stby" is opening...
Operation requires start up of instance "india" on database "india"
Starting instance "india"...
ORA-32004: obsolete or deprecated parameter(s) specified for RDBMS instance
ORACLE instance started.
Database mounted.
Database opened.
Connected to "india"
Switchover succeeded, new primary is "india_stby"
DGMGRL>
I am Anurag Pandey, working as Cloud Database Architect | Migration & Modernization Expert .
Database Administration and Implementation. Provide technical guidance and work with global team.
Extensive Experience in Database Installation and Configuration, Real Application Cluster (RAC), ASM, Dataguard , Patching, Upgrade, RMAN, Performance Tuning, Backup and Recovery, Azure, Google, AWS Cloud, AWS RDS, MySQL, Database Migration Service(DMS),CloudEndure - MGN, Azure Site Recovery, Azure Migrate, Datapump, terraform automation, Ansible
Oracle 12c- Perform Dataguard Switchover Using DGMGRL Utility
Oracle 12c- Perform Dataguard Switchover Using DGMGRL Utility
[oracle@ip-10-0-1-12 ~]$ dgmgrl
DGMGRL for Linux: Release 12.2.0.1.0 - Production on Tue May 25 14:02:07 2021
Copyright (c) 1982, 2017, Oracle and/or its affiliates. All rights reserved.
Welcome to DGMGRL, type "help" for information.
DGMGRL> connect sys@india
Password:
Connected to "india"
Connected as SYSDBA.
DGMGRL
DGMGRL> edit database india set property StaticConnectIdentifier='(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=10.0.1.12)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=india)(INSTANCE_NAME=india)(SERVER=DEDICATED)))';
DGMGRL> edit database india_stby set property StaticConnectIdentifier='(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=10.0.1.37)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=india_stby)(INSTANCE_NAME=india_stby)(SERVER=DEDICATED)))';
[oracle@ip-10-0-1-12 ~]$ dgmgrl
DGMGRL for Linux: Release 12.2.0.1.0 - Production on Tue May 25 14:02:07 2021
Copyright (c) 1982, 2017, Oracle and/or its affiliates. All rights reserved.
Welcome to DGMGRL, type "help" for information.
DGMGRL> connect sys@india
Password:
Connected to "india"
Connected as SYSDBA.
DGMGRL>
DGMGRL>
DGMGRL>
DGMGRL> show configuration;
Configuration - india
Protection Mode: MaxPerformance
Members:
india - Primary database
india_stby - Physical standby database
Fast-Start Failover: DISABLED
Configuration Status:
SUCCESS (status updated 48 seconds ago)
DGMGRL>
DGMGRL>
DGMGRL> switchover to india_stby
Performing switchover NOW, please wait...
Operation requires a connection to database "india_stby"
Connecting ...
Connected to "india_stby"
Connected as SYSDBA.
New primary database "india_stby" is opening...
Operation requires start up of instance "india" on database "india"
Starting instance "india"...
ORA-32004: obsolete or deprecated parameter(s) specified for RDBMS instance
ORACLE instance started.
Database mounted.
Database opened.
Connected to "india"
Switchover succeeded, new primary is "india_stby"
DGMGRL>
DGMGRL>
DGMGRL>
DGMGRL>
DGMGRL> show configuration;
Configuration - india
Protection Mode: MaxPerformance
Members:
india_stby - Primary database
india - Physical standby database
Fast-Start Failover: DISABLED
Configuration Status:
SUCCESS (status updated 28 seconds ago)
DGMGRL>
[oracle@ip-10-0-1-37 ~]$ !sq
sqlplus sys/sys@india_stby as sysdba
SQL*Plus: Release 12.2.0.1.0 Production on Tue May 25 14:05:53 2021
Copyright (c) 1982, 2016, Oracle. All rights reserved.
Connected to:
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production
SQL> select name ,open_mode from v$database;
NAME OPEN_MODE
--------- --------------------
INDIA READ WRITE
SQL>
SQL> insert into hr.abc values(2,'From standby after primary');
1 row created.
SQL> commit;
Commit complete.
SQL>
On new standby
=================
[oracle@ip-10-0-1-12 ~]$ sqlplus "/as sysdba"
SQL*Plus: Release 12.2.0.1.0 Production on Tue May 25 14:06:28 2021
Copyright (c) 1982, 2016, Oracle. All rights reserved.
Connected to:
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production
SQL> select name ,open_mode from v$database;
NAME OPEN_MODE
--------- --------------------
INDIA READ ONLY WITH APPLY
SQL>
SQL>
SQL> select * from hr.abc;
ID NAME
---------- --------------------------------------------------
1 A
SQL> /
ID NAME
---------- --------------------------------------------------
2 From standby after primary
1 A
SQL>
SQL>
alert log
=================
Initiating a healthcheck...
05/25/2021 14:02:50
Forwarding CTL_SWITCH operation to member india for processing
Updated broker configuration file available, loading from "/u01/app/oracle/product/12.2.0/dbhome_1/dbs/dr2india_stby.dat"
05/25/2021 14:02:51
Switchover processing to this database has started
Notifying Oracle Clusterware to prepare target standby database for switchover
05/25/2021 14:03:01
Updated broker configuration file available, loading from "/u01/app/oracle/product/12.2.0/dbhome_1/dbs/dr1india_stby.dat"
New primary database after switchover
Informing client: OPENING instance 'india_stby' of database 'india_stby'
Informing client: STARTUP instance 'india' of database 'india'
Switchover completed, notifying client
Proceeding with buildup after switchover...
Notifying Oracle Clusterware to buildup the primary database after switchover
Opening database after Switchover
05/25/2021 14:03:04
Evaluating archivelog destinations for switchover
SWITCHOVER TO india_stby completed successfully, new primary database is india_stby
I am Anurag Pandey, working as Cloud Database Architect | Migration & Modernization Expert .
Database Administration and Implementation. Provide technical guidance and work with global team.
Extensive Experience in Database Installation and Configuration, Real Application Cluster (RAC), ASM, Dataguard , Patching, Upgrade, RMAN, Performance Tuning, Backup and Recovery, Azure, Google, AWS Cloud, AWS RDS, MySQL, Database Migration Service(DMS),CloudEndure - MGN, Azure Site Recovery, Azure Migrate, Datapump, terraform automation, Ansible
Tuesday, May 18, 2021
Oracle Trace File Analyzer (TFA)
Oracle Trace File Analyzer (TFA)
Oracle Trace File Analyzer helps you perform real-time health monitoring, fault detection & diagnosis via a single interface. It will securely consolidate all distributed diagnostic data.
[oracle@ip-10.1.1.1.1 bin]$ ps -ef|grep tfa
root 687 1 0 05:50 ? 00:00:00 /bin/sh /etc/init.d/init.tfa run >/dev/null 2>&1 </dev/null
root 837 1 0 05:50 ? 00:00:16 /u01/app/oracle/tfa/ip-10.1.1.1.1/tfa_home/jre/bin/java -Xms128m -Xmx512m oracle.rat.tfa.TFAMain /u01/app/oracle/tfa/ip-10.1.1.1.1/tfa_home
oracle 22559 8133 0 07:05 pts/2 00:00:00 grep --color=auto tfa
[oracle@ip-10.1.1.1.1 bin]$ cd /u01/app/oracle/tfa
[root@ip-10.1.1.1.1 bin]#
[root@ip-10.1.1.1.1 bin]# ./tfactl status
.---------------------------------------------------------------------------------------------------.
| Host | Status of TFA | PID | Port | Version | Build ID | Inventory Status |
+--------------+---------------+-----+-------+------------+----------------------+------------------+
| ip-10.1.1.1.1 | RUNNING | 837 | 31183 | 12.2.1.0.0 | 12210020161122170355 | COMPLETE |
'--------------+---------------+-----+-------+------------+----------------------+------------------'
[root@ip-10.1.1.1.1 bin]#
[root@ip-10.1.1.1.1 bin]#
[root@ip-10.1.1.1.1 bin]#
[root@ip-10.1.1.1.1 bin]# ./tfactl toolstatus
.----------------------------------------.
| External Support Tools |
+--------------+--------------+----------+
| Host | Tool | Status |
+--------------+--------------+----------+
| ip-10.1.1.1.1 | events | DEPLOYED |
| ip-10.1.1.1.1 | orachk | DEPLOYED |
| ip-10.1.1.1.1 | vi | DEPLOYED |
| ip-10.1.1.1.1 | dbglevel | DEPLOYED |
| ip-10.1.1.1.1 | exachk | DEPLOYED |
| ip-10.1.1.1.1 | managelogs | DEPLOYED |
| ip-10.1.1.1.1 | menu | DEPLOYED |
| ip-10.1.1.1.1 | changes | DEPLOYED |
| ip-10.1.1.1.1 | param | DEPLOYED |
| ip-10.1.1.1.1 | history | DEPLOYED |
| ip-10.1.1.1.1 | triage | DEPLOYED |
| ip-10.1.1.1.1 | calog | DEPLOYED |
| ip-10.1.1.1.1 | grep | DEPLOYED |
| ip-10.1.1.1.1 | summary | DEPLOYED |
| ip-10.1.1.1.1 | ls | DEPLOYED |
| ip-10.1.1.1.1 | srdc | DEPLOYED |
| ip-10.1.1.1.1 | pstack | DEPLOYED |
| ip-10.1.1.1.1 | tail | DEPLOYED |
| ip-10.1.1.1.1 | alertsummary | DEPLOYED |
| ip-10.1.1.1.1 | ps | DEPLOYED |
'--------------+--------------+----------'
[root@ip-10.1.1.1.1 bin]#
[root@ip-10.1.1.1.1 bin]#
[root@ip-10.1.1.1.1 bin]# pwd
/u01/app/oracle/tfa/bin
[root@ip-10.1.1.1.1 bin]#
I am Anurag Pandey, working as Cloud Database Architect | Migration & Modernization Expert .
Database Administration and Implementation. Provide technical guidance and work with global team.
Extensive Experience in Database Installation and Configuration, Real Application Cluster (RAC), ASM, Dataguard , Patching, Upgrade, RMAN, Performance Tuning, Backup and Recovery, Azure, Google, AWS Cloud, AWS RDS, MySQL, Database Migration Service(DMS),CloudEndure - MGN, Azure Site Recovery, Azure Migrate, Datapump, terraform automation, Ansible
Saturday, May 8, 2021
Setup Data Guard Broker Configuration in Oracle Database
How to setup Data Guard Broker Configuration
=================================================
Primary database : india
Standby database : india_stby
Add static entry for the DGMGRL in the listener.ora on both the primary and standby servers
Standby listener.ora
================================
oracle@ip-10-0-1-37 admin]$ cat listener.ora
# listener.ora Network Configuration File: /u01/app/oracle/product/12.2.0/dbhome_1/network/admin/listener.ora
# Generated by Oracle configuration tools.
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(GLOBAL_DBNAME = india_stby)
(SID_NAME = india_stby)
(ORACLE_HOME = /u01/app/oracle/product/12.2.0/dbhome_1)
)
(SID_DESC =
(GLOBAL_DBNAME = india_dgmgrl)
(SID_NAME = india_stby)
(ORACLE_HOME = /u01/app/oracle/product/12.2.0/dbhome_1)
)
(SID_DESC =
(GLOBAL_DBNAME = india_stby_dgmgrl)
(SID_NAME = india_stby)
(ORACLE_HOME = /u01/app/oracle/product/12.2.0/dbhome_1)
)
)
[oracle@ip-10-0-1-37 admin]$
Primary listener.ora
==============================
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(GLOBAL_DBNAME = india)
(SID_NAME = india)
(ORACLE_HOME = /u01/app/oracle/product/12.2.0/dbhome_1)
)
(SID_DESC =
(GLOBAL_DBNAME = india_dgmgrl)
(SID_NAME = india_stby)
(ORACLE_HOME = /u01/app/oracle/product/12.2.0/dbhome_1)
)
(SID_DESC =
(GLOBAL_DBNAME = india_stby_dgmgrl)
(SID_NAME = india_stby)
(ORACLE_HOME = /u01/app/oracle/product/12.2.0/dbhome_1)
)
)
ALTER SYSTEM SET dg_broker_start=true scope=both;
SQL>
SQL> ALTER SYSTEM SET dg_broker_start=true scope=both;
System altered.
SQL>
SQL>
SQL> show parameter broker
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
connection_brokers string ((TYPE=DEDICATED)(BROKERS=1)),
((TYPE=EMON)(BROKERS=1))
dg_broker_config_file1 string /u01/app/oracle/product/12.2.0
/dbhome_1/dbs/dr1india_stby.da
t
dg_broker_config_file2 string /u01/app/oracle/product/12.2.0
/dbhome_1/dbs/dr2india_stby.da
t
dg_broker_start boolean TRUE
use_dedicated_broker boolean FALSE
SQL>
SQL>
SQL>
SQL>
on stanndby
==============
SQL> /
Thread Last Sequence Received Last Sequence Applied Difference
---------- ---------------------- --------------------- ----------
1 29 29 0
SQL>
SQL>
SQL> ALTER SYSTEM SET dg_broker_start=true scope=both;
System altered.
SQL>
SQL>
SQL> show parameter broker
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
connection_brokers string ((TYPE=DEDICATED)(BROKERS=1)),
((TYPE=EMON)(BROKERS=1))
dg_broker_config_file1 string /u01/app/oracle/product/12.2.0
/dbhome_1/dbs/dr1india_stby.da
t
dg_broker_config_file2 string /u01/app/oracle/product/12.2.0
/dbhome_1/dbs/dr2india_stby.da
t
dg_broker_start boolean TRUE
use_dedicated_broker boolean FALSE
SQL>
SQL>
SQL>
SQL>
DGMGRL>
DGMGRL>
DGMGRL> create configuration 'india' as primary database is 'india' connect identifier is india;
Configuration "india" created with primary database "india"
DGMGRL>
DGMGRL>
DGMGRL> show configuration;
Configuration - india
Protection Mode: MaxPerformance
Members:
india - Primary database
Fast-Start Failover: DISABLED
Configuration Status:
DISABLED
DGMGRL>
DGMGRL>
DGMGRL> add database 'india_stby' as connect identifier is india_stby maintained as physical;
Error: ORA-16698: member has a LOG_ARCHIVE_DEST_n parameter with SERVICE attribute set
Failed.
DGMGRL>
DGMGRL> add database 'india_stby' as connect identifier is india_stby maintained as physical;
Database "india_stby" added
DGMGRL>
DGMGRL>
DGMGRL> show configuration
Configuration - india
Protection Mode: MaxPerformance
Members:
india - Primary database
india_stby - Physical standby database
Fast-Start Failover: DISABLED
Configuration Status:
DISABLED
DGMGRL>
DGMGRL>
DGMGRL> enable configuration;
Enabled.
DGMGRL>
DGMGRL>
DGMGRL> show configuration
Configuration - india
Protection Mode: MaxPerformance
Members:
india - Primary database
india_stby - Physical standby database
Fast-Start Failover: DISABLED
Configuration Status:
SUCCESS (status updated 51 seconds ago)
DGMGRL>
DGMGRL> show database india InconsistentProperties
INCONSISTENT PROPERTIES
INSTANCE_NAME PROPERTY_NAME MEMORY_VALUE SPFILE_VALUE BROKER_VALUE
DGMGRL>
DGMGRL>
DGMGRL> show database india_stby InconsistentProperties
INCONSISTENT PROPERTIES
INSTANCE_NAME PROPERTY_NAME MEMORY_VALUE SPFILE_VALUE BROKER_VALUE
DGMGRL>
DGMGRL>
DGMGRL> show database india
Database - india
Role: PRIMARY
Intended State: TRANSPORT-ON
Instance(s):
india
Database Status:
SUCCESS
DGMGRL> show database india_stby
Database - india_stby
Role: PHYSICAL STANDBY
Intended State: APPLY-ON
Transport Lag: 0 seconds (computed 1 second ago)
Apply Lag: 0 seconds (computed 1 second ago)
Average Apply Rate: 1.00 KByte/s
Real Time Query: ON
Instance(s):
india_stby
Database Status:
SUCCESS
DGMGRL>
DGMGRL>
DGMGRL> show database india_stby statusreport
STATUS REPORT
INSTANCE_NAME SEVERITY ERROR_TEXT
DGMGRL>
DGMGRL>
DGMGRL> show database india statusreport
STATUS REPORT
INSTANCE_NAME SEVERITY ERROR_TEXT
DGMGRL>
I am Anurag Pandey, working as Cloud Database Architect | Migration & Modernization Expert .
Database Administration and Implementation. Provide technical guidance and work with global team.
Extensive Experience in Database Installation and Configuration, Real Application Cluster (RAC), ASM, Dataguard , Patching, Upgrade, RMAN, Performance Tuning, Backup and Recovery, Azure, Google, AWS Cloud, AWS RDS, MySQL, Database Migration Service(DMS),CloudEndure - MGN, Azure Site Recovery, Azure Migrate, Datapump, terraform automation, Ansible
Subscribe to:
Posts (Atom)