When running a Level 1 or Level 0 backup via OEM 13c, the following RMAN error causes the run to fail. (The RMAN script is being ran with proper sysdba credentials from OEM 13c.) RMAN-06169: could not read file header for datafile 2 error reason 5 Oracle documentation suggests reason 5 indicates 'Unable to open file'. However, if I execute the exact same RMAN statement directly on the server, the backup runs fine. We have removed the database from OEM and decommissioned the agent, the redeployed the agent as well as reinstated the database in OEM, but the error still occurred.

I'm attempting to do an impdp on RDS, Oracle 12c. I'm importing only one table for this particular impdp job but every time I try to import it, UNDO usage gets to about 50% and then the logs just say Resumable error: ORA-01628: max # extents (32765) reached for rollback segment. Since this is RDS I cannot manually manage undo. I created a fresh RDS instance with a new 4TB UNDO tablespace to perform the import of just this table. I've read about creating one giant rollback segment and also about creating lots of small rollback segments to solve this problem. I've also read I can split the import into multiple parts, but I'd rather not do that if possible. Is there anything more I can do here to maybe stop the UNDO tablespace from running out of extents?

i'm trying to create backup restore script . in short my backup script creating new directory every day from the operating system level and then backup the database using the directory created i tried the below script but it seems RMAN doesn't recognize the controlfiles here is my script **#RMAN RESTORE SCRIPT** #!/bin/bash #Environment Variables ORACLE_HOME=/u01/app/oracle/product/12.2.0.1/dbhome_1 ORACLE_SID=cdb11 PATH=$PATH:$ORACLE_HOME/bin DD=date +%Y-%m-%d-%H-%M export ORACLE_HOME ORACLE_SID PATH DD controlfile backupsets controlfile=/ArchiveDBBackup/DAILY_RMAN/${DD}/CDB1_CONTROLFILE backupsets=/ArchiveDBBackup/DAILY_RMAN/${DD}/ export controlfile backupsets LOGFILE=/RMAN/log/DAILY_FULLBKP_RESTORE_date +%Y%m%d.log rman target / LOG $LOGFILE RMAN-03002: failure of restore command at 07/04/2021 09:35:57 RMAN-06172: no AUTOBACKUP found or specified handle is not a valid copy or piece any idea how can RMAN recognize linux variables ? or anyway to achieve the restore with those variables ?

I downloaded **Pre-Built Developer VMs for Oracle VM VirtualBox** , which contains Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 I want to **BACKUP the Database using Rman**, but it is in NOARCHIVELOG mode, so it is necessary to shutdown and change the database to ARCHIVELOG mode, but when I try to alter database to archive log , it gives an error saying operation not allowed from within a pluggable database. An operation was attempted that can only be performed in the root container.

Database: Oracle 12 C I have a MESSAGE table with BODY VARCHAR2(32000), METADATA VARCHAR2(32000). Initially I had a problem when both columns have 32k+32k chars of data. I had fixed it by applying the TO_CLOB function and I am able to write the output to the file without truncating the result. (This is working only when 1. All are English characters(Eg: SQL query result record contains around 64 K characters) 2. Combination of French & English characters when the SQL query result record 32K characters, In this case, my query execution is failing with error code : 9. Please help me on how to fix this issue without truncating the data. Below is my Unix script code

export PATH

export LD_LIBRARY_PATH

export ORACLE_ACCESS

#export NLS_LANG=.AL32UTF8

export NLS_LANG=AMERICAN_AMERICA.AL32UTF8

output=$(sqlplus -S "$ORACLE_ACCESS" = TO_DATE('01-Jan-2025','DD-MON-YYYY') and CREATE_TS < TO_DATE('01-Jan-2025','DD-MON-YYYY')+1) ORDER BY ROWID;

    SPOOL OFF

    exit;

EOF

)

I have a table with a column storing JSON data as a CLOB. The JSON has a 'miscData' field that is made up of an array of JSON objects. Each object has 2 keys which are always the same, but the values are different.

.
.
.
"otherKeys" : "otherValues",
"miscData": [
        {
            "miscType": "date",
            "miscText": "2020-07-09T10:01:10.450Z"
        },
        {
            "miscType": "Comment",
            "miscText": "Comment body"
        },
        {
            "miscType": "CORRECT_TYPE",
            "miscText": "SELECT_ME"
        }
    ],
"confirmationNumber" : "123456789qwerty",
.
.
.

I need to

SELECT 
    CLOB_COL.miscData.miscText 
WHERE
    CLOB_COL.miscData.miscType = 'CORRECT_TYPE' 
AND 
    CLOB_COL.confirmationNumber = 'xxxx';

But I am having trouble since miscData is an array of objects which all contain miscType and miscData keys and I can't guarantee the array will be the same size, or in the same order.

I need to produce a JSON document with several properties from a list of Oracle 12c1 production databases, and I was given read-only access in order to generate this data. The problem is these databases were created over time and may store VARCHAR2 values in different character sets. My naive attempt at producing JSON is: SELECT '{' || '"content":"' || content || '", "' || '"name":"' || name || '"}' FROM my_table WHERE ... However, since the columns content and name may have all kind of special characters and use non-trivial character sets, the query above may produce invalid JSON documents. Is there any function that can convert a VARCHAR2 into Unicode from any character set? I think if the string is in Unicode it could be easy to manage. Unfortunately, I don't really know each column's character set in advance.

There is a known issue with Oracle where the flashback logs are not reclaimable when a PDB is dropped and a restore point exists in the PDB. I can see a restore point in x$kccrsp but no restore points exist in v$restore_point. I believe I am experiencing the aforementioned issue but the Oracle support web site is "experiencing an unscheduled outage" and I was wondering if anyone knows of a solution for removing the errant restore point.

I'm working with Oracle Data Integrator 12.2.1.4.0, and I'm encountering a persistent repository-related issue. ❗ Problem: When I try to save changes to a mapping or generate a scenario, I receive the following errors:

ODI-26211: Mapping name is not unique  
ODI-20088: Error while generating the scenario  
ODI-10182: Uncategorized exception during repository access

This happens even though: It's a fresh repository. The mapping I created is the very first and only one. Tables such as SNP_POP, SNP_SCEN, and SNP_STEP are completely empty. 🔍 Additional context: Initially, I was able to save the mapping. Later, the error started appearing. I suspect this may have happened after I tried to run the mapping (it failed due to missing agent) — but I can't be sure. Renaming the mapping doesn't help. No agents are installed yet — I'm just using Standalone ODI Studio locally. 🔄 Environment: Oracle Data Integrator 12.2.1.4.0 (also tried 14.1.2.0.0 with same result) Oracle XE 21c, PDB: XEPDB1 Repositories freshly created Windows Server 2019 Standard No agent, just testing in local development 🛠️ Background: Originally I started in ODI 14c, encountered the same issue, then switched to 12c and recreated the repositories from scratch, but the issue persists in both versions. Any insight into what might be wrong with the repository setup? Could something be corrupted after running a mapping without agent? Any metadata cleanup suggestions?

**This question concerns Oracle PL/SQL (Oracle version 12c/19c) and Oracle SQL Developer (version 20.4).** I have a procedure which calls a dynamically generated SELECT statement and save results into sys_refcursor. I would like to print content of the refcursor within the procedure and show it in ouput window of Oracle SQL Developer. So far, I was able to only return the refcursor from procedure through OUT parameter and bind variable, and then print it. My current code of the procedure looks like this:

create or replace procedure cursor_show (rc_out OUT sys_refcursor) is
    v_sql           varchar2(1000);      
    v_rc            sys_refcursor;
begin           
    --other statements generating SELECT 
    v_sql := 'select ...'; --my SELECT
    
    open v_rc for v_sql; --get data 
    rc_out := v_rc; --return refcursor
end;

To print results, I need to call these statements:

var x refcursor;
execute cursor_show (:x);
print x;

I would like to encapsulate print into procedure cursor_show to get something like this:

create or replace procedure cursor_show is
    v_sql           varchar2(1000);      
    v_rc            sys_refcursor;
begin            
    --other statements generating SELECT 
    v_sql := 'select ...'; --my SELECT
    
    open v_rc for v_sql; --get data 

    print v_rc; --print data
end;

After that, I would be able to call the procedure and print the refcursor content with one-row statement execute cursor_show;. However, once I tried to compile such procedure, I received this error message:

Error(51,11): PLS-00103: Encountered the symbol "V_RC" when 
expecting one of the following: := . ( @ % ; 
The symbol ":=" was substituted for "V_RC" to continue.

Could you please advise how to call print statement within body of the procedure?

What are the possible ways to Clone a PDB from one CDB into another CDB (in a different host) with TDE enabled at both source and target. At present i am able to think of the following two a) Data pump export and import b) Unplugging in source and plugging into target Option a) requires creation of tablespaces manually before import at target pdb and is quite slow since the data we have is huge. Option b) is easy without TDE but with TDE their is a concept of exporting and importing encryption keys , and what ever i have used it till now it becomes quite complex during plugging and importing keys . Can someone suggest and alternative way to achieve the mentioned requirement or may be a way to do a) or b) with more ease and accuracy .

![enter image description here ][1] ps://i.sstatic.net/0DX0d.png I am trying to install the Oracle database 12c on the Windows 10 Laptop. Have tried disk C and disk E, got the same error. And I tried to change the file properties, change the security to allow the full control.. And still got the same error message. Anyone could help me with this? Thank you!!!!

I am new to Oracle Database Installation and I tried to install newer version but it does not work, and when I get a full documentation on how to install version 12c, somehow I got an error out of memory. When I check in this path: ***/u01/app/oracle/cfgtoollogs/dbca/orcl***

[ 2023-04-19 16:37:55.751 GMT ] Copying database files
DBCA_PROGRESS : 1%
DBCA_PROGRESS : 2%
[ 2023-04-19 16:38:01.236 GMT ] ORA-27102: out of memory

DBCA_PROGRESS : 3%
[ 2023-04-19 16:38:01.238 GMT ] ORA-01034: ORACLE not available

[ 2023-04-19 16:38:02.301 GMT ] ORA-01034: ORACLE not available

[ 2023-04-19 16:38:02.305 GMT ] ORA-01034: ORACLE not available

[ 2023-04-19 16:38:07.537 GMT ] DBCA_PROGRESS : DBCA Operation failed.

When I check in this path: ***/u01/app/oracle/diag/rdbms/orcl/orcl/trace***

2023-04-19T16:38:01.221832+00:00
Dump of system resources acquired for SHARED GLOBAL AREA (SGA) 

2023-04-19T16:38:01.221951+00:00
 Per process system memlock (soft) limit = 128G
2023-04-19T16:38:01.222002+00:00
 Expected per process system memlock (soft) limit to lock
 SHARED GLOBAL AREA (SGA) into memory: 820M
2023-04-19T16:38:01.222103+00:00
 Available system pagesizes:
  4K 
2023-04-19T16:38:01.222200+00:00
 Supported system pagesize(s):
2023-04-19T16:38:01.222254+00:00
  PAGESIZE  AVAILABLE_PAGES  EXPECTED_PAGES  ALLOCATED_PAGES  ERROR(s)
2023-04-19T16:38:01.222306+00:00
        4K       Configured          209924            2149   ORA-27102
2023-04-19T16:38:01.222355+00:00

My current config during installation is this:

fs.aio-max-nr = 1048576
fs.file-max = 6815744
kernel.shmall = 2097152
kernel.shmmax = 1987162112
kernel.shmmni = 4096
kernel.sem = 250 32000 100 128
net.ipv4.ip_local_port_range = 9000 65500
net.core.rmem_default = 262144
net.core.rmem_max = 4194304
net.core.wmem_default = 262144
net.core.wmem_max = 1048586

My current memory: ***[root@testing etc]# free -m***

total        used        free      shared  buff/cache   available
Mem:           2048         855         269         496         922         696
Swap:           512         191         320

After close the installer and check back my RAM

total        used        free      shared  buff/cache   available
Mem:           2048          76        1120         496         850        1474
Swap:           512          24         487

Can you help me to identify the correct config for my database?

On an Oracle Database 12c Standard Edition Release 12.2.0.1.0, I have a table that contains postal codes with corresponding names, which aren't normalized. We have a DETERMINISTIC function that returns a normalized text (e.g. 'Fernão Ferro' -> 'FERNAO FERRO') so we created an index for the description column for search purposes and gathered table statistics after.

CREATE INDEX IX_POSTAL_CODES_1 ON POSTAL_CODES (NORMALIZAR_TEXTO(DESCRICAO));

BEGIN
   DBMS_STATS.GATHER_TABLE_STATS(
      ownname => 'XXXXXXXX',
      tabname => 'POSTAL_CODES',
      cascade => TRUE
   );
END;
/

We have used this function successfully on other tables but, unless we apply an hint /*+ INDEX(POSTAL_CODES IX_POSTAL_CODES_1) */ it never uses the index on this one. A simple count takes ~5s, but with the hint it takes ~9ms.

SELECT
    /*+ INDEX(POSTAL_CODES IX_POSTAL_CODES_1) */
    COUNT(*)
FROM POSTAL_CODES
WHERE
    NORMALIZAR_TEXTO(DESCRICAO) LIKE '%FERNAO%FERRO%'

The execution plan without the hint:

Explained.


PLAN_TABLE_OUTPUT
-------------------------------------------
Plan hash value: 1234747403
 
-------------------------------------------
| Id  | Operation          | Name         |
-------------------------------------------
|   0 | SELECT STATEMENT   |              |
|   1 |  SORT AGGREGATE    |              |
|*  2 |   TABLE ACCESS FULL| POSTAL_CODES |
-------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   2 - filter("XXXXXXXX"."NORMALIZAR_TEXTO"("DESCRICAO") LIKE 
              '%FERNAO%FERRO%')
 
Note
-----
   - rule based optimizer used (consider using cbo)

19 rows selected.

The execution plan with the hint:

Explained.


PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------
Plan hash value: 1574285503
 
--------------------------------------------------------------------------------------
| Id  | Operation        | Name              | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT |                   |     1 |    12 |   682   (1)| 00:00:01 |
|   1 |  SORT AGGREGATE  |                   |     1 |    12 |            |          |
|*  2 |   INDEX FULL SCAN| IX_POSTAL_CODES_1 | 10979 |   128K|   682   (1)| 00:00:01 |
--------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   2 - filter("XXXXXXXX"."NORMALIZAR_TEXTO"("DESCRICAO") LIKE 
              '%FERNAO%FERRO%' AND "XXXXXXXX"."NORMALIZAR_TEXTO"("DESCRICAO") IS NOT NULL)

15 rows selected.

I already cloned the table to a new one, created all the same indexes and the behavior is always the same.

***Environment:*** - ***DB Version:*** Oracle Database 12cR2 EE on Exadata - ***RAC:*** True - ***Problem:*** ORA-06508 on procedure. > One of our users had a DBA role privilege, we don't want such a big > privilege to be defined anymore. When we revoke the DBA role privilege > from this user, all procedures, packages and triggers became invalid. > This was something we expected at that moment and we accepted it as > normal. When we compiled all of them, there was no problem and we > reassigned the DBA role privilege to this user in order to postpone > this work until later. > > The problem starts from this point. We get an error when one of the > procedures wants to run. But the error is not constantly getting, for > example it was working correctly for the last 26 hours, but then it > got the error. When we drop / create or compile, the problem is > solved. However, the strange part is that when this problem occurs, > the procedure and dependencies all seem to be valid. The error is as > follows: > > > > > > > > Error raised in: PROCEDURE_NAME at line xxxx - ORA-06508: PL/SQL: > could not find program unit being called ***Line-xxxx:*** EXCEPTION WHEN OTHERS THEN ROLLBACK; R_CODE:= 400; R_MESSAGE:='Error raised in: '|| $$plsql_unit ||' at line ' || $$plsql_line || ' - '||sqlerrm; I could not find a suitable solution for this scenario on Oracle Support or on dba.stackexchange and I am still researching. Anyone have a good idea? Best Regards,

I have server under RHEL 6.3 installed on that server Oracle 11gr2 and 12c, I want to create user lets say Ahmad but I want this user to be sysdba on both servers, for single server I edit .bach_profile to be like the following: 11g: ORACLE_HOSTNAME=oracledev; export ORACLE_HOSTNAME ORACLE_UNQNAME=DB11G; export ORACLE_UNQNAME ORACLE_BASE=/oracle11gr2/u01/app/oracle; export ORACLE_BASE ORACLE_HOME=$ORACLE_BASE/product/11.2.0/db_1; export ORACLE_HOME ORACLE_SID=DB11G; export ORACLE_SID PATH=/usr/sbin:$PATH; export PATH PATH=$ORACLE_HOME/bin:$PATH; export PATH LD_LIBRARY_PATH=$ORACLE_HOME/lib:/lib:/usr/lib; export LD_LIBRARY_PATH CLASSPATH=$ORACLE_HOME/jlib:$ORACLE_HOME/rdbms/jlib; export CLASSPATH PATH=$PATH:$HOME/bin unset USERNAME cd $ORACLE_BASE export PATH and if I want to make him sysdba for 12c I use the following: export TMP=/tmp export TMPDIR=$TMP export ORACLE_HOSTNAME=oracledev export ORACLE_UNQNAME=cdb1 export ORACLE_BASE=/oracle12c/u01/app/oracle export ORACLE_HOME=$ORACLE_BASE/product/12.1.0/db_1 export ORACLE_SID=cdb1 export PATH=/usr/sbin:$PATH export PATH=$ORACLE_HOME/bin:$PATH export LD_LIBRARY_PATH=$ORACLE_HOME/lib:/lib:/usr/lib export CLASSPATH=$ORACLE_HOME/jlib:$ORACLE_HOME/rdbms/jlib My question is it possible to marge those parameters together without making a conflict, if yes please advise

I am using this command: impdp atif/atif@192.168.67.68:1521/serv1 DIRECTORY=DATA_PUMP_DIR DUMPFILE=mltpltblbkp.dmp LOGFILE=mltpltblbkp.log to import data from one server to another. this is my data_pump_dir path:/u01/app/oracle/admin/serv1/dpdump/ I have granted these privileges to the user: grant datapump_exp_full_database to atif; grant datapump_imp_full_database to atif; grant read, write on directory data_pump_dir to system; When I run the command, I get the following errors: ORA-39001: invalid argument value ORA-39000: bad dump file specification ORA-31640: unable to open dump file "/u01/app/oracle/admin/serv1/dpdump/mltpltblbkp.dmp" for read ORA-27037: unable to obtain file status Linux-x86_64 Error: 2: No such file or directory Additional information: 7 can someone help here?

I am new to creating an Oracle database using the command line. I am logged in as superuser; now I would like to create a new blank database, "mynewdatabase". $ . oraenv ORACLE_SID = [oracle] ? mynewdatabase Is there a step-by-step example of how I can create a new database instance. Most of the examples I have found on the Internet show examples with a Windows environment and not Linux. Once I know how to create the new database environment, I can use ... sqlplus / as sysdba and begin working some database magic. Any assistance would be greatly appreciated!

I have a view in my production database and I want to create same view in my test database. I tried this commmand: ‌‌select dbms_metadata.get_ddl('VIEW','VIEW_NAME','VIEW_OWNER') from dual; It generated a create script and I run it. But it didn't copy all of the dependencies, data types, etc. How can I do this? Regards,

since Oracle will increase the costs for running databases my employer is currently evaluating alternatives for oracle databases. In one application we currently use oracle db with no specific settings. To test everything we migrated the same database to mariadb and ran some tests. One test takes with mariadb 18 minutes and with oracle only 4-5 minutes. Our tests runs a few (hundred/thousand?) Select queries in the same table with different parameters. I know, that maybe oracle has some caching feature which might cache the selected table, but 4-5 times slower is something I can't believe for MariaDB. The server MariaDB is running on is a Oracle Linux 8.10; the same as the oracle db but different host. MariaDB Version is 10.5.27. Is there any setting we can tweak for MariaDB? Thanks a lot for your ideas and help.