ORA-65500: could not modify DB_UNIQUE_NAME, resource exists

 Env:-

DB : 12.1.0.2

GI: 19.0.0(Oracle restart)

OS: OEL 7

DB Type: Standalone Database

 

During execution of  rman duplicate database  following error is observed.

RMAN> run{ allocate auxiliary channel C1 DEVICE TYPE DISK; allocate auxiliary channel C2 DEVICE TYPE DISK; allocate auxiliary channel C3 DEVICE TYPE DISK; allocate auxiliary channel C4 DEVICE TYPE DISK; duplicate database to 'E1THS' backup location '/u01/rman18062021/databkp' nofilenamecheck; }

 

Error details:-

RMAN-03015: error occurred in stored script Memory Script RMAN-03009: failure of sql command on clone_default channel at 11/07/2022 02:29:26 RMAN-20000: abnormal termination of job step RMAN-11003: failure during parse/execution of SQL statement: alter system reset  db_unique_name scope=spfile RMAN-11001: Oracle Error: ORA-32010: cannot find entry to delete in SPFILE RMAN-03015: error occurred in stored script Memory Script RMAN-03009: failure of sql command on clone_default channel at 11/07/2022 02:29:06 RMAN-20000: abnormal termination of job step RMAN-11003: failure during parse/execution of SQL statement: alter system set  db_unique_name =  'E1THS' comment= 'Modified by RMAN duplicate' scope=spfile RMAN-11001: Oracle Error: ORA-32017: failure in updating SPFILE ORA-65500: could not modify DB_UNIQUE_NAME, resource exists RMAN-00571: =========================================================== RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS =============== RMAN-00571: =========================================================== RMAN-03002: failure of Duplicate Db command at 11/07/2022 02:29:26 RMAN-05501: aborting duplication of target database

 

Rationale:-

This issue is reported as Bug 20977794 in MOS for Oracle 12.1.0.2 version.The error is observed during execution of rman duplicate database where any attempt to change DB_UNIQUE_NAME for database when a CRS resource for it exists. As RMAN tries to make a change to db_unique_name, this error is observed.

 

Solution :-

Remove the existing entry to database which is going to be cloned from CRS registry

[oracle@server004 dbs]$ srvctl config database E1KRS E1THS   [oracle@server004 dbs]$ srvctl remove database -db E1THS Remove the database E1THS? (y/[n]) y

 

Now rerun the Rman duplicate command , it should work fine.

 

 

CRS-4013: This command is not supported in a single-node configuration.

Issue:-

[grid@hkgdbs004 bin]$ ./crsctl stop crs

CRS-4013: This command is not supported in a single-node configuration.

CRS-4000: Command Stop failed, or completed with errors.

 

Solution:-

There is no crs in standalone grid installation. Hence try shutting down Has Component

 

[grid@hkgdbs004 bin]$ ./crsctl stop has CRS-2791: Starting shutdown of Oracle High Availability Services-managed resources on 'hkgdbs004' CRS-2673: Attempting to stop 'ora.DATAC1.dg' on 'hkgdbs004' CRS-2673: Attempting to stop 'ora.LISTENER.lsnr' on 'hkgdbs004' CRS-2677: Stop of 'ora.DATAC1.dg' on 'hkgdbs004' succeeded CRS-2673: Attempting to stop 'ora.evmd' on 'hkgdbs004' CRS-2673: Attempting to stop 'ora.RECOC1.dg' on 'hkgdbs004' CRS-2677: Stop of 'ora.RECOC1.dg' on 'hkgdbs004' succeeded CRS-2673: Attempting to stop 'ora.asm' on 'hkgdbs004' CRS-2677: Stop of 'ora.LISTENER.lsnr' on 'hkgdbs004' succeeded CRS-2677: Stop of 'ora.evmd' on 'hkgdbs004' succeeded CRS-2677: Stop of 'ora.asm' on 'hkgdbs004' succeeded CRS-2673: Attempting to stop 'ora.cssd' on 'hkgdbs004' CRS-2677: Stop of 'ora.cssd' on 'hkgdbs004' succeeded CRS-2793: Shutdown of Oracle High Availability Services-managed resources on 'hkgdbs004' has completed CRS-4133: Oracle High Availability Services has been stopped.

 

 

 

ERROR: The home is not clean. This home cannot be used since there was a failed OPatch execution in this home.

 So while applying patch on grid home(19c) we faced the above error

 

$ ./gridSetup.sh ApplyRU /u01/app/33803476

 

ERROR: The home is not clean. This home cannot be used since there was a failed OPatch execution in this home. Use a different home to proceed.

 

 

Solution:- In our last attempt to patch grid home , the opatch failed . So while retrying to after fixing the issue when retrying to apply the patch its throwing above error.

 

The only way to resolve this issue is to clean the contents of GRID HOME (e.g. rm -Rf <NEW_GRID_HOME>), then re-unzip the Grid Infrastructure binary on local node into the NEW_GRID_HOME path as GRID_HOME user.

 

$ rm -rf $GRID_HOME

$ unzip LINUX.X64_193000_grid_home.zip

 

And then retry to apply the patch again. It should work .

How to validate invalid objects in pdb$seed pluggable database

 

Env:- 2 node RAC Database

DB Version: 12.2.0.1

Platform:- Exadata x6

OS- OEL 7

 

Recently , while working on a crucial database upgrade project (12.2.0.1 to 19c), I came across with one observation .

For upgrade , I ran preupgrade.jar script which generally throws a list of recommendations along with fixes . After checking the recommendations I found out one of the recommendation, was related to validation of invalid objects that was  residing inside SYS or SYSTEM schema in PDB$SEED .

 

Now generally we run utlrp.sql to validate the invalid objects , so I did the same to fix the invalid objects

SQL> @?/rdbms/admin/utlrp SELECT dbms_registry_sys.time_stamp('utlrp_bgn') as timestamp from dual * ERROR at line 1: ORA-16000: database or pluggable database open for read-only access ORA-06512: at "SYS.DBMS_REGISTRY_SYS", line 834 DOC> The following PL/SQL block invokes UTL_RECOMP to recompile invalid DOC> objects in the database. Recompilation time is proportional to the DOC> number of invalid objects in the database, so this command may take DOC> a long time to execute on a database with a large number of invalid DOC> objects. DOC> DOC> Use the following queries to track recompilation progress: DOC> DOC> 1. Query returning the number of invalid objects remaining. This DOC> number should decrease with time. DOC> SELECT COUNT(*) FROM obj$ WHERE status IN (4, 5, 6); DOC> DOC> 2. Query returning the number of objects compiled so far. This number DOC> should increase with time. DOC> SELECT COUNT(*) FROM UTL_RECOMP_COMPILED; DOC> DOC> This script automatically chooses serial or parallel recompilation DOC> based on the number of CPUs available (parameter cpu_count) multiplied DOC> by the number of threads per CPU (parameter parallel_threads_per_cpu). DOC> On RAC, this number is added across all RAC nodes. DOC> DOC> UTL_RECOMP uses DBMS_SCHEDULER to create jobs for parallel DOC> recompilation. Jobs are created without instance affinity so that they DOC> can migrate across RAC nodes. Use the following queries to verify DOC> whether UTL_RECOMP jobs are being created and run correctly: DOC> DOC> 1. Query showing jobs created by UTL_RECOMP DOC> SELECT job_name FROM dba_scheduler_jobs DOC> WHERE job_name like 'UTL_RECOMP_SLAVE_%'; DOC> DOC> 2. Query showing UTL_RECOMP jobs that are running DOC> SELECT job_name FROM dba_scheduler_running_jobs DOC> WHERE job_name like 'UTL_RECOMP_SLAVE_%'; DOC># DECLARE * ERROR at line 1: ORA-16000: database or pluggable database open for read-only access ORA-06512: at "SYS.UTL_RECOMP", line 875 ORA-06512: at line 4 SELECT dbms_registry_sys.time_stamp('utlrp_end') as timestamp from dual * ERROR at line 1: ORA-16000: database or pluggable database open for read-only access ORA-06512: at "SYS.DBMS_REGISTRY_SYS", line 834 DOC> The following query reports the number of objects that have compiled DOC> with errors. DOC> DOC> If the number is higher than expected, please examine the error DOC> messages reported with each object (using SHOW ERRORS) to see if they DOC> point to system misconfiguration or resource constraints that must be DOC> fixed before attempting to recompile these objects. DOC># OBJECTS WITH ERRORS ------------------- 0 DOC> The following query reports the number of errors caught during DOC> recompilation. If this number is non-zero, please query the error DOC> messages in the table UTL_RECOMP_ERRORS to see if any of these errors DOC> are due to misconfiguration or resource constraints that must be DOC> fixed before objects can compile successfully. DOC># ERRORS DURING RECOMPILATION --------------------------- 0 CREATE OR REPLACE FUNCTION local_enquote_name (str varchar2) * ERROR at line 1: ORA-00604: error occurred at recursive SQL level 1 ORA-16000: database or pluggable database open for read-only access PL/SQL procedure successfully completed. DROP function local_enquote_name * ERROR at line 1: ORA-16000: database or pluggable database open for read-only access Warning: XDB now invalid, could not find xdbconfig ORDIM INVALID OBJECTS: ORDIMDPCALLOUTS - INVALID - PACKAGE BODY PL/SQL procedure successfully completed. SQL>

 

In short, it failed as the pdb$seed pdb is in read only mode.

SQL> show pdbs       CON_ID CON_NAME                       OPEN MODE  RESTRICTED ---------- ------------------------------ ---------- ----------          2 PDB$SEED                       READ ONLY  NO

 

 

So to validate the invalid objects in PDB$SEED I followed the following steps :-

sqlplus / as sysdba   SQL> ALTER SESSION SET CONTAINER = PDB$SEED;   SQL> show con_name;   CON_NAME ------------------------------ PDB$SEED   -- I Set a hidden parameter at session level SQL> alter session set "_oracle_script" = true instances=all;   -- Now lets try to open the pdb$seed instance   SQL> alter pluggable database pdb$seed close immediate instances=all;   SQL> alter pluggable database pdb$seed open read write instances=all; SQL> show pdbs       CON_ID CON_NAME                       OPEN MODE  RESTRICTED ---------- ------------------------------ ---------- ----------          2 PDB$SEED                       READ WRITE NO   SQL> @?/rdbms/admin/utlrp.sql   SQL> select count(*) from dba_objects where status='INVALID' AND OWNER IN ('SYS','SYSTEM');     COUNT(*) ----------          0   SQL> alter session set "_oracle_script" = true instances=all;   SQL> alter pluggable database pdb$seed close instances=all; <Make sure there is no session active to pdb$seed database from any other terminal>   SQL> alter pluggable database pdb$seed OPEN READ ONLY instances=all;

 

So this way I validated all the invalid objects in pdb$seed  PDB.

How to extract DDL for a DBMS Scheduler Job

This is one handy script which helps how to extract DDL for a dbms scheduler job 

SQL> set long 999999 SQL> set pagesize 222 SQL> set long 1000   Syntax:- select dbms_metadata.get_ddl('PROCOBJ','JOB_NAME','OWNER OF THE JOB') from dual;   SQL>select dbms_metadata.get_ddl('PROCOBJ','PROD_DB_BACKUP','SYSTEM') from dual;   DBMS_METADATA.GET_DDL('PROCOBJ','PROD_DB_BACKUP','SYSTEM') --------------------------------------------------------------------------------     BEGIN dbms_scheduler.create_job('"PROD_DB_BACKUP"', job_type=>'PLSQL_BLOCK', job_action=> 'BEGIN   jde_schema_backup(''(''''PRODDTA'''',''''PRODCTL'''',''''SY920'''',''''SVM920' ''',''''PD920'''')''                    ,''soumyad@wizertech.in''                    ); END;' , number_of_arguments=>0, start_date=>TO_TIMESTAMP_TZ('10-JAN-2019 09.48.32.526098000 AM ASIA/KOLKATA',' DD-MON-RRRR HH.MI.SSXFF AM TZR','NLS_DATE_LANGUAGE=english'), repeat_interval=>   'FREQ=WEEKLY;BYDAY=MON,TUE,WED,THU,FRI,SAT;BYHOUR=0;BYMINUTE=10' , end_date=>NULL, job_class=>'"DEFAULT_JOB_CLASS"', enabled=>FALSE, auto_drop=>TRUE,comments=> NULL ); COMMIT; END;

Gather all information related to dba scheduler using a single script

 In our daily life, sometimes we require to gather various information related to different jobs or jobs scheduled in dba_scheduler  and for many of us it becomes challenging to remember all the views to gather that information.

 

Using this script, we can gather various information related to undo tablespace in a single go.

 [db@server102 ~]$ cat scheduler.sql

Rem Please execute following script to generate html file   set markup html on spool on SPOOL Scheduler_Views.HTML set pagesize 200 set echo on select systimestamp from dual; select sessiontimezone from dual; select dbms_scheduler.stime from dual; select dbms_scheduler.get_sys_time_zone_name from dual; select instance_name,host_name,version,to_char(startup_time, 'DD-MON-YY HH:MI:SS AM') startup_time from v$instance; select * from dba_scheduler_global_attribute; show parameter job_queue /   -- ** Autotask ** select client_name, status from dba_autotask_client; select * from dba_autotask_client; select * from dba_autotask_client_history order by window_start_time desc;   -- ** Windows and Window Group ** select * from dba_scheduler_window_groups; select * from dba_scheduler_wingroup_members order by 1,2; select window_name,enabled,active,resource_plan,comments from dba_scheduler_windows; select window_name,enabled,active,resource_plan,repeat_interval,duration,last_start_date,next_start_date from dba_scheduler_windows; select * from dba_scheduler_windows;   -- ** Window history ** select log_date, window_name, operation,status, substr(additional_info,1,350) Info from dba_scheduler_window_log order by 1 desc / select log_date,window_name,req_start_date,actual_start_date,window_duration,actual_duration,additional_info from dba_scheduler_window_details where rownum<20 order by log_date desc /   -- ** DBA JOBS ** select owner,job_name,repeat_interval,enabled,state,run_count,last_start_date,next_run_date from dba_scheduler_jobs order by owner,enabled / select owner,job_name,enabled,state,job_style,job_creator,program_owner,program_name,job_type, schedule_owner,schedule_name,schedule_type,start_date,repeat_interval, job_class,job_priority,run_count,max_runs,failure_count,max_failures,retry_count,last_start_date, last_run_duration,next_run_date from dba_scheduler_jobs order by owner,enabled / select * from dba_scheduler_jobs;   -- ** Running Jobs ** select * from dba_scheduler_running_jobs;   -- ** Schedule ** select * from dba_scheduler_schedules;   -- ** JOB History ** select to_char(log_date, 'DD-MON-YY HH24:MI:SS') timestamp, owner,job_name, status,error#,req_start_date,actual_start_date,additional_info from dba_scheduler_job_run_details order by log_date desc ;   -- ** Resource Manager ** show parameter resource_manager_plan select plan,status from dba_rsrc_plans; select * from dba_rsrc_plan_directives ; select * from dba_rsrc_plans ; spool off set markup html off spool off exit

Execute the scheduler.sql

[db@server102 ~]$ sqlplus / as sysdba  SQL> @scheduler.sql