by Buda Consulting | Feb 10, 2022 | Cloud, Oracle
Oracle managed hosting offers some of the most powerful database technology available, but it comes with a lot of complex options and can be challenging to manage. Running Oracle-based applications efficiently and securely demands experienced technical staff with a wide range of specific skills.
To save operating costs, reduce the stress on in-house IT, and improve reliability, performance, and/or data protection, many small to midsized businesses (SMBs) are turning to Oracle-managed cloud services or other forms of hosting for their Oracle investments.
2 Paths to Oracle Database Hosting
When it comes to Oracle database hosting, there are basically two paths you can take:
- One is to migrate all your Oracle RDBMS environment with all your data to a public, private or hybrid cloud, where it will reside on virtualized servers and run on virtualized and containerized services. From there, you can either manage your databases in the cloud yourself or outsource some or all of your programs to an Oracle-managed cloud service provider.
- But what if you no longer want to maintain your own data center and manage your own Oracle environment, but prefer not to move your sensitive data to the cloud? You can leverage Oracle-managed hosting in an isolated and fully managed physical data center, including specialists to move and manage your databases and maintain your servers, operating systems, and applications. Some providers even specify that your data will be hosted in the US or North America, along with an ala carte managed services menu and a choice of service levels.
Which approach is right for your business? That depends on multiple factors, including your budget, data volume, and data growth rate, in-house skill sets, security and compliance risk tolerance, and business goals. Applicable regulatory or contract requirements, especially around security, are another major factor in the decision to move your Oracle databases to the cloud—one that can trump all other considerations for some organizations, especially in regulated industries like government, legal, healthcare, and financial services.
Leveraging Oracle Managed Cloud Services
The pressure to transition your Oracle environment to the cloud may be intense. Cloud is seen as “the future” and the only route to digital transformation, with a promise of cost savings and operational improvements. But the cloud presents unique security, reliability, and performance challenges, making it imperative that you carefully manage your cloud database solution. This is where Oracle cloud managed services come in.
With Oracle-managed cloud services, you get remote support to manage your Oracle environment in the cloud. This typically takes the form of a virtual team of certified Oracle practitioners who will monitor and manage your cloud-based Oracle environment (often 24×7), as well as help you with Oracle upgrades and security configurations and monitoring.
Oracle Managed Cloud Services Benefits
By engaging with an Oracle cloud managed services provider (MSP), you liberate your in-house resources to focus on strategic goals rather than ongoing, everyday tasks. You may also experience smoother database operations thanks to the specialist third-party’s broad expertise.
Other benefits that an Oracle cloud managed service provider can offer include:
- The reduced total cost of ownership (TCO) of your Oracle environment versus keeping your Oracle databases on your own physical servers and using internal resources to manage and maintain everything.
- The ability to scale up database infrastructure on-demand versus a more time- and labor-intensive purchasing and provisioning process.
- Reduced cybersecurity risk due to improved security expertise and controls, including stronger patch management and the availability of proactive cyber threat mitigation tools like database activity monitoring<SUGGEST LINK TO RECENT POST ON DAM (not yet posted to the blog)>.
- Improved business continuity and disaster recovery capabilities thanks to cloud-based services to deliver redundancy and high availability.
- Better overall IT agility and faster IT decision-making, since your team can now put more attention on keeping IT aligned with business needs.
When Cloud is Not the Best Option
But while moving your Oracle resources to the cloud has the potential to deliver significant benefits, this move is not for every organization. Every business needs a secure, resilient and cost-effective Oracle application infrastructure, but the cloud is not always the right place to host it.
For example, defense suppliers may be subject to the use of the “Not Releasable to Foreign Nationals” (NOFORN) caveat on Controlled Unclassified Information (CUI), classified data and even contract documents that they store and/or handle. NOFORN data cannot be stored in data centers outside the US, which rules out many cloud services because of how they move data and workloads across national geographies without the customer even being aware of it.
For other firms, such as in the financial services industry, the need to keep applications and data on isolated platforms for security and compliance reasons could be paramount.
Leveraging Oracle Managed Hosting
For businesses that need or want to keep certain Oracle data and workloads out of the cloud, Oracle managed hosting can be a great option. In this scenario, your Oracle environment is fully managed by expert staff in an isolated physical environment, giving you far more control.
With Oracle managed hosting, you always know where your data resides, so you can meet NOFORN and other contractual and regulatory mandates. You will also enjoy outstanding overall security, reliability and availability of your databases, along with many of the scalability and cost reduction benefits of cloud. Finally, as a managed service, Oracle managed hosting offers all the strategic and staffing benefits of a cloud-based outsourcing model.
How Oracle Managed Hosting Works
Here is how most Oracle managed hosting scenarios work:
- Expert database professionals handle the total process of migrating your Oracle databases to the hosted data center. This includes configuring the new Oracle environment, instantiating your databases and moving your data. Most Oracle-managed hosting providers will work alongside your in-house team and/or a third-party vendor(s) to ensure an optimal outcome with minimal downtime.
- Expert network administrators setup, configure and manage the network, firewalls, and connectivity associated with your hosted Oracle databases.
- Expert IT system administrators install, configure, manage and patch your database infrastructure, including servers, operating systems, and applications.
- Expert Oracle database administrators (DBAs) monitor and manage your databases, including maintaining disaster recovery and high availability capabilities.
Security Benefits of Oracle Managed Hosting
One of the top reasons to host your Oracle database in an outsourced physical data center is unsurpassed security. Besides giving you a dedicated environment with isolated hardware and software in a US-based Tier 3 (or potentially Tier 4) data center, most Oracle-managed hosting providers offer special features like Oracle Advanced Security and enhanced security monitoring.
Oracle managed hosting also offers disaster recovery and business continuity services, like:
- Replication across geographically separate (US-based) data centers to protect from natural disasters.
- Best-practice disaster recovery planning and processes. Many SMBs either never create or never test a disaster recovery plan, or test it infrequently, and thus have weak and unreliable disaster recovery capability. Moving to the cloud alone does not improve this situation.
Next Steps
If you are ready to make or are considering outsourcing your Oracle database hosting, Buda Consulting can help. We are Oracle managed hosting specialists with over 20 years’ experience. We offer professional, reliable and secure Oracle database hosting. Whether your database footprint is small or massive, let us help you free your business from the constraints of managing Oracle in-house, while cost-effectively addressing your unique security, compliance and other business requirements.
Contact us to talk about our services, custom hosting packages, hardware and Oracle licensing options, and more.
by Willie Gray | Mar 18, 2021 | Best Practices, Database Patch News, database patching, Database Security, Oracle, SQL Server
Welcome to Database Patch News, Buda Consulting’s newsletter of current patch information for Oracle and Microsoft SQL Server. Here you’ll find information recently made available on patches—including security patches—and desupported versions.
Why should you care about patching vulnerabilities and bugs? Two big reasons:
- Unpatched systems are a top cyber attack target. Patch releases literally advertise vulnerabilities to the hacker community. The longer you wait to patch, the greater your security risk.
- Along with running a supported database version, applying the latest patches ensures that you can get support from the vendor in case of an issue. Patching also helps eliminate downtime and lost productivity associated with bugs.
Here are the latest patch updates for Oracle and SQL Server:
Oracle Patches:
January 19, 2021 Quarterly Patch Updates:
21c – Released January 13, 2021, Version 21.1; no Quarterly patch yet
19c – Release Update 19.10 is available (32218494 and 321266828)
18c – Release Update 18.13 is available (32204699 and 32126855)
12cR2 – Release Update 210119 is available (32228578 and 32126871)
Regular support ends in Mar 2023 and extended support ends in Mar 2026.
12cR1 – Release Update 210119 is available (32132231 and 32126908)
Regular support ended in July 2019 and extended support ends in July 2021.
11gR4 – Patch Set Update 201020 is available (31720776)
Regular support ended in October 2018 and extended support ended December 31, 2020.
SQL Server Patches:
SQL Server 2019
Cumulative update 9 (Latest build) Released Feb 2, 2021
Mainstream support ends Jan 7, 2025
Extended support ends Jan 8, 2030
SQL Server 2017
Cumulative update 23 (Latest build) Released Feb 24, 2021
Mainstream support ends Oct 11, 2022|
Extended support ends Oct 12, 2027
SQL Server 2016 Service Pack 2
Cumulative update 16 Release date: Feb 11, 2021
Mainstream support ends Jul 13, 2021
Extended support ends Jul 14, 2026
SQL Server 2014 Service Pack 3
Cumulative update 4 Release date: Jan 12, 2021
Mainstream support ended Jul 9, 2019
Extended support ends Jul 9, 2024
SQL Server 2012 Service Pack 4
Release date: Oct 5, 2017
Mainstream support ended Jul 11, 2017
Extended support ends Jul 12, 2022
Note: All other SQL Server versions not mentioned are no longer supported.
by Willie Gray | Mar 15, 2021 | Best Practices, Database Security, Oracle, Oracle DBA, Uncategorized
It was very cold and early on a Monday morning when I received a call from one of my fellow system administrators. He reported that one of our production databases would not come back online after the server hosting the database was restarted.
Most DBAs would start investigating this issue by looking at database alert logs. But my experience led me to ask my fellow system admin the following question: “What changes did you make on the server prior to the reboot?”
It was his answer to that question that allowed me to quickly understand the issue and fix it in just a few minutes.
Apparently the system admin (not the DBA) was conducting vulnerability testing and, as a result, made a change to the main listener.ora file that disabled all databases from being able to dynamically register to Oracle database listeners.
By default, an Oracle database will try to dynamically register to an Oracle database listener on port 1521. This registration process allows connections to the database from outside of the server. The database was online and operational, but because the dynamic registration option was disabled it could no longer register to the listener. So no users could connect to the database.
The fix for this was adding a static listener to the listener.ora for the database hosted on the server, thus allowing it to receive connections. Once the static listener was added, all users were able to connect to the production database without error.
The Technical Problem\
Let’s break this incident down in more detail:
This is the original Listener file
LISTENER=
(DESCRIPTION=
(ADDRESS_LIST=
(ADDRESS=(PROTOCOL=tcp)(HOST=MyServer)(PORT=1521))
(ADDRESS=(PROTOCOL=ipc)(KEY=extproc))))
The administrator added one line (see below in red):
LISTENER=
(DESCRIPTION=
(ADDRESS_LIST=
(ADDRESS=(PROTOCOL=tcp)(HOST=MyServer)(PORT=1521))
(ADDRESS=(PROTOCOL=ipc)(KEY=extproc))))
DYNAMIC_REGISTRATION_LISTENER=OFF
This prevented any databases that do not have a static listener specified in the listener.ora file from accepting connections..
The Technical Solution
To correct the problem, I added a static listener to the listener.ora file (see below in red):
LISTENER=
(DESCRIPTION=
(ADDRESS_LIST=
(ADDRESS=(PROTOCOL=tcp)(HOST=MyServer)(PORT=1521))
(ADDRESS=(PROTOCOL=ipc)(KEY=extproc))))
DYNAMIC_REGISTRATION_LISTENER=OFF
SID_LIST_LISTENER=
(SID_LIST=
(SID_DESC=
(GLOBAL_DBNAME=MyDBName)
(ORACLE_HOME=/u01/app/oracle/product/12.1.0/dbhome_1)
(SID_NAME=MySID))
)
You can find detailed information about the listener file for Oracle version 19c here.
The Communication Problem
We have mentioned in this blog before that almost all problems with technology projects are the result of poor communication. This principle holds here as well. Because the system administrator did not keep any of the DBAs on our team “in the loop” about their vulnerability testing, or the resulting changes, those changes caused production downtime.
The Communication Solution
Any change to a server, database, or application must be communicated to all responsible parties beforehand. In fact, a better approach in this case would have been to ask the DBA to make the change to the listener file rather than the administrator making the change himself. This would have ensured that an experienced DBA had reviewed the change and understood the potential impact.
The moral of the story is: Keep your DBAs in the loop when you’re making system changes. It’s our job to proactively prevent database issues others might miss.
A Word on Database Security
While an action taken by the system administrator caused a problem in this situation, it should be applauded from a database security standpoint that vulnerability testing was conducted because it exposed a potential vulnerability (the dynamic registration). It is a best practice to disable dynamic registration unless it is necessary for the organization, and unless the associated risk is mitigated by other practices, such as changing the default listener port.
Database vulnerability testing is a crucial part of a comprehensive IT security plan and is often overlooked. For the reasons described above, the process should always include a member of the DBA team. See a few of our Database Security related blogs here.
by Scott Loudon | Feb 16, 2021 | Best Practices, Database Patch News, database patching, Database Security, Oracle, Oracle DBA, SQL Server
Welcome to Database Patch News, Buda Consulting’s newsletter of current patch information for Oracle and Microsoft SQL Server. Here you’ll find information recently made available on patches—including security patches—and desupported versions.
Why should you care about patching vulnerabilities and bugs? Two big reasons:
- Unpatched systems are a top cyber attack target. Patch releases literally advertise vulnerabilities to the hacker community. The longer you wait to patch, the greater your security risk.
- Along with running a supported database version, applying the latest patches ensures that you can get support from the vendor in case of an issue. Patching also helps eliminate downtime and lost productivity associated with bugs.
Here are the latest patch updates for Oracle and SQL Server:
Oracle Patches:
January 19, 2021 Quarterly Patch Updates:
21c – Released January 13, 2021, Version 21.1; no Quarterly patch yet
19c – Release Update 19.10 is available (32218494 and 321266828)
18c – Release Update 18.13 is available (32204699 and 32126855)
12cR2 – Release Update 210119 is available (32228578 and 32126871)
Regular support ends in Mar 2023 and extended support ends in Mar 2026.
12cR1 – Release Update 210119 is available (32132231 and 32126908)
Regular support ended in July 2019 and extended support ends in July 2021.
11gR4 – Patch Set Update 201020 is available (31720776)
Regular support ended in October 2018 and extended support ended December 31, 2020.
SQL Server Patches:
SQL Server 2019
Cumulative update 8 (Latest build) Released Oct 1, 2020
Mainstream support ends Jan 7, 2025
Extended support ends Jan 8, 2030
SQL Server 2017
Cumulative update 22 (Latest build) Released Sept 10, 2020
Mainstream support ends Oct 11, 2022
Extended support ends Oct 12, 2027
SQL Server 2016 Service Pack 2
Cumulative update 15 Release date: Sept 28, 2020
Mainstream support ends Jul 13, 2021
Extended support ends Jul 14, 2026
SQL Server 2014 Service Pack 3
Cumulative update 4 Release date: Feb 11, 2019
Mainstream support ended Jul 9, 2019
Extended support ends Jul 9, 2024
SQL Server 2012 Service Pack 4
Release date: Oct 5, 2017
Mainstream support ended Jul 11, 2017
Extended support ends Jul 12, 2022
Note: All other SQL Server versions not mentioned are no longer supported.
by Robert Buda | Dec 8, 2020 | Best Practices, Database Patch News, database patching, Database Security, Oracle, SQL Server, Uncategorized
Welcome to Database Patch News, Buda Consulting’s newsletter of current patch information for Oracle and Microsoft SQL Server. Here you’ll find information recently made available on patches—including security patches—and desupported versions.
Why should you care about patching vulnerabilities and bugs? Two big reasons:
- Unpatched systems are a top cyber attack target. Patch releases literally advertise vulnerabilities to the hacker community. The longer you wait to patch, the greater your security risk.
- Along with running a supported database version, applying the latest patches ensures that you can get support from the vendor in case of an issue. Patching also helps eliminate downtime and lost productivity associated with bugs.
Here are the latest patch updates for Oracle and SQL Server:
Oracle Patches:
October 20, 2020 Quarterly Patch Updates:
19c – Release Update 19.9 is available (31771877 & 31668882)
18c – Release Update 18.12 is available (31730250 & 31668892)
12cR2 – Release Update 201020 is available (31741641 & 31668898)
Regular support ends in Mar 2023 and extended support ends in Mar 2026.
12cR1 – Release Update 201020 is available (31550110 & 31668915)
Regular support ended in July 2019 and extended support ends in July 2021.
11gR4 – Patch Set Update 201020 is available (31720776)
Regular support ended in October 2018 and extended support ends in December 2020.
SQL Server Patches:
SQL Server 2019
Cumulative update 8 (Latest build) Released Oct 1, 2020
Mainstream support ends Jan 7, 2025
Extended support ends Jan 8, 2030
SQL Server 2017
Cumulative update 22 (Latest build) Released Sept 10, 2020
Mainstream support ends Oct 11, 2022
Extended support ends Oct 12, 2027
SQL Server 2016 Service Pack 2
Cumulative update 15 Release date: Sept 28, 2020
Mainstream support ends Jul 13, 2021
Extended support ends Jul 14, 2026
SQL Server 2014 Service Pack 3
Cumulative update 4 Release date: Feb 11, 2019
Mainstream support ended Jul 9, 2019
Extended support ends Jul 9, 2024
SQL Server 2012 Service Pack 4
Release date: Oct 5, 2017
Mainstream support ended Jul 11, 2017
Extended support ends Jul 12, 2022
Note: All other SQL Server versions not mentioned are no longer supported.
by KarlaS | Jul 15, 2020 | Best Practices, open source, Oracle, PostgreSQL
Many companies are looking for open source database solutions to help shrink the IT budget by minimizing or avoiding support costs. One such solution would be migrating an Oracle database to PostgreSQL. PostgreSQL is a widely used open source RDBMS, and the PostgreSQL database model is very similar to Oracle. All PostgreSQL features are available at no cost or minimal cost. Also PostgreSQL professionals are less expensive to hire than Oracle DBAs.
However, migrating an Oracle database to PostgreSQL is not a straightforward task in an on-premises data center. In particular, it is challenging to migrate an entire Oracle schema with partitioning tables. This technical blog shares a simple method to migrate the Oracle schema to a PostgreSQL database using open source tools. It does not cover installation and configuration of PostgreSQL.
Caution: Every environment is different, so test this in a non-production environment before working with production.
Versions used: Oracle 12.2.0.1, PostgreSQL 12.3
Operating System: CentOS Linux 8.
Tools and utilities used: SQLines Data, SQLine Converter, and PGAdmin 4.x
Pre-migration considerations:
- There are quite a few data type differences between Oracle and PostgreSQL. Commonly used types (Oracle –> PostgreSQL) include: VARCHAR2–>VARCHAR, CHAR –>CHAR, CLOB –>TEXT, BLOB RAW \–>BYTEA (1 GB limit) or Large object, DATE–>TIMESTAMP(0), TIMESTAMP WITH LOCAL TIME ZONE –> TIMESTAMPTZ, NUMBER–> NUMERIC, NUMBER(4) –>SMALLINT, NUMBER(9) –> INT, NUMBER(18) –> BIGINT.
- No package stored procedure is available in PostgreSQL. Consider converting packages into procedures and functions. There are several differences in built-in functions between Oracle and PostgreSQL. Please find the equivalent function in PostgreSQL user manual.
How To Migrate Oracle To PostgreSQL
I have used the SQLines Data open source tool to do this migration. Once you create all required users (roles) and tablespaces and privileges in PostgreSQL, do the initial load using SQLines Data and exclude all partition tables.
Synatax : sqldata -sd=oracle,<OracleUser>/<pwd>@<oralcleDBhost>:<OraclePort>/<OracleDBname> -td=pg,<postgresUser>/<pwd>@<hostname>:<PostgreSQLPort>,<PostgreSQLdbname> -t=<Oracleschema>.* -texcl=partitiontables -smap=<Oracleschema>:<PostgresSchema>
Example: $sqldata -sd=oracle,system/welcome1@orahost1:1521/ORATEST -td=pg,PGTEST/postpwd1@pghost1:5432,TESTDB -t=payroll.* –texcl=employee_pay_tab -smap=payroll:payroll
Truncate all PostgreSQL tables (* truncates only tables in PostgreSQL).
Get the partition table creation script without partitions from the table DDL and change the data types as appropriate to PostgreSQL.
Example:
CREATE TABLE payroll.employee_pay_tab (
Pay_period_id numeric(6),
Pay_period text COLLATE pg_catalog.”default”,
Emp_id numeric(6),
Base_sal numeric(10,2),
fed_tax numeric(10,2),
state_tax numeric(10,2),
medicare numeric(10,2),
ssn_ded numeric(10,2),
ret_ded numeric(10,2),
tot_pay numeric(10,2)
CONSTRAINT XPK_empployee_pay_tab PRIMARY KEY (Pay_period_id,Emp_id) USING INDEX TABLESPACE PAY_INDEX)
PARTITION BY LIST (Pay_period_id);
ALTER TABLE payroll.empployee_pay_tab OWNER to payroll;
ALTER TABLE payroll.empployee_pay_tab SET TABLESPACE PAY_DATA;
CREATE TABLE payroll.empployee_pay_tab_P_TEMP PARTITION OF payroll.empployee_pay_tab DEFAULT TABLESPACE PAY_DATA;
Get a partition creation script from the Oracle partition table from the database.
set linesize 150
set pages 0
set feedback on
set serveroutput on buffer 999999999;
BEGIN
FOR r IN (select ‘CREATE TABLE ‘||table_owner||’.’|| partition_name ||’ PARTITION OF ‘||table_owner||’.’||TABLE_NAME || ‘ FOR VALUES IN (‘ as col1 , HIGH_VALUE , ‘) TABLESPACE PAY_DATA;’ as col2 from dba_tab_partitions where table_owner = ‘PAYROLL’ and table_name=’EMPLOYEE_PAY_TAB’ order by 1) LOOP
IF r.HIGH_VALUE != ‘-1’ THEN
DBMS_OUTPUT.PUT_LINE(
r.col1|| r.HIGH_VALUE || r.col2
);
END IF;
END LOOP;
END;
/
Get a partition index creation script from the Oracle partition table from the database.
set pages 0
set linesize 150
select ‘CREATE INDEX ‘ ||index_name||’ ON payroll.employee_pay_tab (‘||column_name||’) TABLESPACE CE_INDEX;’ from (select index_name , listagg (column_name, ‘,’) WITHIN GROUP (ORDER BY column_name) column_name FROM dba_ind_columns where table_owner=‘PAYROLL’ and table_name=’EMPLOYEE_PAY_TAB’ GROUP BY index_name order by index_name)
;
Create the partition table, partitions and indexes from above scripts in PostgreSQL. Then load the Oracle schema into PostgreSQL, including partition tables, using the SQLine Data tool.
Syntax : sqldata -sd=oracle,<OracleUser>/<pwd>@<oralcleDBhost>:<OraclePort>/<OracleDBname> -td=pg,<postgresUser>/<pwd>@<hostname>:<PostgreSQLPort>,<PostgreSQLdbname> -t=<Oracleschema>.* -smap=<Oracleschema>:<PostgresSchema>
Example: $sqldata -sd=oracle,system/welcome1@orahost1:1521/ORATEST -td=pg,PGTEST/postpwd1@pghost1:5432,TESTDB -smap=payroll:payroll -topt=truncate
Verify data and create functions, procedures, views and sequences. Get the Sequences scripts from Oracle:
set linesize 150
select ‘CREATE SEQUENCE ‘||SEQUENCE_NAME||’ MINVALUE ‘||MIN_VALUE||’ MAXVALUE ‘||MAX_VALUE||’ INCREMENT BY ‘||INCREMENT_BY||’ START WITH ‘||LAST_NUMBER||’;’ from dba_sequences where SEQUENCE_OWNER=’PAYROLL’;
Get the views, procedure and packages using an Oracle DDL generation command. Use the SQLine converter to convert Oracle code into PostgreSQL. Then get table and column comments scripts from Oracle and run on PostgreSQL.
set heading off
set linesize 300
set feedback off
set verify off
set pages 100
col table_name format a30
col column_name format a30
spool tab_col_comments.sql
select ‘COMMENT ON COLUMN ‘||table_name||’.’||column_name||’ IS ”’||comments||”’;’ from DBA_COL_COMMENTS where owner=‘PAYROLL’ and comments is not null;
select ‘COMMENT ON TABLE ‘||table_name||’ IS ”’||comments||”’;’
from DBA_TAB_COMMENTS where owner=‘PAYROLL’ and comments is not null;
spool off
Now run tab_col_comments.sql in PostgreSQL.
Finally, test your application in a non-production environment.
If you’re thinking of migrating your existing database to an open source platform, contact Buda Consulting for a free consultation on best practices and “lessons learned.”
