ORACLE DATABASE PROBLEM AND SOLUTIONS

Monday, May 31, 2021

Oracle Database Releases and Support Timelines

Oracle Database Releases and Support Timelines

Currently Oracle 19c database version is stable and long term support

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

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

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

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

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

Wednesday, May 26, 2021

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 ~]#

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

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>