Questions tagged with Oracle

**Gist:** Cannot gain terminal access to then run Oracle or SQL Plus commands to import a database dump into Oracle RDS. Struggling to import an Oracle database dump as there is no way to do this via AWS's RDS web console, and I have not been able to gain terminal access. Indeed, it does not seem that AWS RDS really wants the owner to use SSH access, as the IP address of the server instance is not readily displayed, but instead the expectation is that all work is done via the DB instance, and not the host server (it is a managed service, after all!). **Situation** * An Oracle RDS instance was created a few months ago. At that time an 'impdp' import was done. * Now wish to redo this step, yet do not have means to gain terminal access. Alas, the knowledge of how the first import was done is no longer in the company... and we cannot gain terminal access using the Master username + password (known good credentials as these are used to log into the Oracle DB using DBeaver or Oracle SQL Developer tools). * Endpoint is of format oracle.clpf12ab34.us-east-1.rds.amazonaws.com * Master username: known (displayed on RDS console's configuration tab) * Password: known **Failed approaches to gain terminal access:** 1. When I attempt to ssh into the server, having stripped the DB Name "oracle" from the front, I observe error "ssh: Could not resolve hostname ... nodename nor servname provided, or not known" 2. Managed to ping the server, so attempted to ssh using masterusernam@ipaddress - yet this returns error "ssh: connect to host <<IPADDRESS>> port 22: Operation timed out" Note: on all my ECS instances access is done via PEM key files, and not using username/password pair. However, no PEM key-pair has been defined on AWS RDS. Therefore my only credential is the Oracle DB username/password. **What next?** I am aware that AWS services cannot reissue new credentials. Yet I would have anticipated the ability to use the information that I have to be able to do some administration of the Oracle DB server... and specifically to be able to run a database dump import (using Oracle's 'impdp' command). The next approach is to manually conduct import... table by table, and with care for foreign key or constraint dependencies. This shall be laborious. Another approach is to dump this existing RDS instance and to create another. Ideally I would need to make use of Oracle DB version 12.1, as this is what I am needing to use for a project -- and importantly I need to have a patch level that would allow me to conduct the import (as the source database was Oracle 12.1 with patch from July 2021). As always, your guidance and pointers are most welcome. Wish you great end-of-week & weekend!lg...

There are users who want to keep accessing tables created by a specific user in the future. So is it possible to give full access to a specific user's table to another specific user? And can you give the user admin privileges?lg...

Does anyone know if AWS is planning to provide newer versions of Oracle APEX? The latest version available per the product page https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/Appendix.Oracle.Options.APEX.html is 21.1 which is well over a year old now.lg...

What would be a good connection pooling strategy for Lambda connecting to on-prem Oracle? Assume the network connectivity has been established. Should I use provisioned concurrency?lg...

Are we allowed to remove/disable Oracle default components like "Oracle Text" on RDS oracle instances?lg...

When will APEX 21.2 or 22.1 be available in RDS Oracle? Are there now permanent hinderances to Apex on RDS Oracle that I should be looking at alternatives for Apex development? Is it a specific feature that is problematic? I understand the strengths of RDS Oracle, but if one of the weaknesses is now going to be that Apex versioning is so far behind then I will have to make other accommodations. Previously, for example, Apex 21.1 was available in RDS 5 months after it was available for download from Oracle. It's now 10 months since Apex 21.2 has been released and no word on when this will be available. Thank you,lg...

Error while when executing exec dbms_java.grant_permission('myschema','SYS:java.util.PropertyPermission','java.protocol.handler.pkgs','write'); Error Details : ORA-29532: Java call terminated by uncaught Java exception: java.lang.SecurityException: policy table update SYS:java.util.PropertyPermission, java.protocol.handler.pkgs ORA-06512: at "SYS.DBMS_JAVA", line 705 ORA-06512: at line 1 29532. 00000 - "Java call terminated by uncaught Java exception: %s" *Cause: A Java exception or error was signaled and could not be resolved by the Java code.lg...

Hello. We have come across a situation where we need to use the local timezone in the RDS Oracle database. We need to set it to Jamaica Timezone which is America/Jamaica. Jamaica's time zone is different from the rest of the region. It is currently not available in AWS as we checked here [https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/Appendix.Oracle.Options.Timezone.html#Appendix.Oracle.Options.Timezone.Zones] Using other nearby timezones or central or eastern timezones is not feasible as DST is observed there. There are some timezones e.g. Bogota and Lima which are the same as Jamaica and could be an option to use but there is a risk due to the following ; 1. The countries might all of a sudden start to observe DST. Although they have not done it in the past 15 to 20 years. 2. We cannot change the timezone in RDS more than once. One Workaround is to use America/Lima or Bogota timezone which is exactly the same as Jamaica, but in the worst case, there is a chance that they start observing DST someday. (may be next month or after 10 years). Please suggest, how we can request AWS to add America/Jamaica which has the below properties. ``` Time Zone: America/Jamaica Current Date and Time in America/Jamaica: Thu 11-Aug-2022 04:41 P.M. Country: Jamaica Daylight Saving Time (DST) in effect? : No Most Recent DST Transition: DST ended on Sun 30-Oct-1983 at 02:00:00 A.M. when local clocks were set backward 1 hour Next Scheduled DST Transition: There are no future Daylight Saving Time rules for this time zone ```lg...

How can I remove files from the Oracle RDS DATA_PUMP_DIR that fail when using the UTL_FILE.FREMOVE package? e.g. SQL> EXEC UTL_FILE.FREMOVE('DATA_PUMP_DIR','test.dmp'); BEGIN UTL_FILE.FREMOVE('DATA_PUMP_DIR','test.dmp'); END; ERROR at line 1: ORA-29283: invalid file operation: cannot open file [29435] ORA-06512: at "SYS.UTL_FILE", line 251 ORA-06512: at "SYS.UTL_FILE", line 1230 ORA-06512: at line 1 The dump file was copied to the DATA_PUMP_DIR using rdsadmin.rdsadmin_s3_tasks.download_from_s3 package with p_decompression_format => 'GZIP' parameter. The extracted dump file is readable by the Data Pump utilities but cannot be deleted by UTL_FILE.FREMOVE. This problem does not occur when using an uncompressed dump file in the S3 bucket. e.g. SELECT rdsadmin.rdsadmin_s3_tasks.download_from_s3( p_bucket_name => 'oracle-rds-s3-test', p_s3_prefix => 'test.dmp.gz', p_directory_name => 'DATA_PUMP_DIR', p_decompression_format => 'GZIP') AS TASK_ID FROM DUAL; Engine version: Oracle Standard Edition Two 19.0.0.0.ru-2022-01.rur-2022-01.r1 Any help is appreciated. Thank you.lg...

Hello. At this moment, i have an Oracle Database on-premisse (licensed; Enterprise Edition) and i´m planning a Disaster Recovery strategy on cloud using Data Guard. In this scenario, i´m gonna keep the **primay instance on-premise** and the **standby instance on AWS Cloud**. Considerations: 1- Using EC2 as my standby - I´m gonna have to license standby vCPUs on AWS (BYOL, Enterprise Edition) , according to Oracle policies (https://www.oracle.com/assets/cloud-licensing-070579.pdf). After this, i install Linux and Oracle and configure Data Guard between the two instances, right? 2- Using RDS as my standby- Is it possible ?? I mean, i know i´m gonna have to license my RDS instance as well (BYOL, Enterprise Edidion), but how does this work? AWS team would help me configuring the standby/RDS?lg...

I am migrating data from Oracle materialized view to PostgreSQL using DMS CDC. Initial load completed successfully. I do not see the changes are replicating. Is any one has performed this type of migration? If Yes please share the details/document.lg...

Hello Continuous_mine functionality from logminer is getting desupported. Does DMS requires this to continuous mine operation in Oracle 19 RDS servers? Im hoping it can handle the log switching process in the same way as debezium does, handling the log switching task as the same way as debezium does https://franckpachot.medium.com/ideas-for-event-sourcing-in-oracle-d4e016e90af6lg...