The Oracle DBA's

Pre-requisite: Completed successfully. [oracle@testing 30593149]$ opatch prereq CheckConflictAgainstOHWithDetail -ph ./ Oracle Interim Patch Installer version 12.2.0.1.21 Copyright (c) 2020, Oracle Corporation.  All rights reserved. PREREQ session Oracle Home       : /testing/app/oracle/product/12.2.0/testing_1 Central Inventory : /testing/app/oraInventory    from           : /testing/app/oracle/product/12.2.0/testing_1/oraInst.loc OPatch version    : 12.2.0.1.21 OUI version       : 12.2.0.1.4 Log file location : /testing/app/oracle/product/12.2.0/testing_1/cfgtoollogs/opatch/opatch2020-05-15_15-38-45PM_1.log Invoking prereq "checkconflictagainstohwithdetail" Prereq "checkConflictAgainstOHWithDetail" passed. OPatch succeeded. But Opatch apply fails: [oracle@testing 30593149]$ opatch apply Oracle Interim Patch Installer version 12.2.0.1.21 Copyright (c) 2020, Oracle Corpo...

ORA-39083: Object type TYPE:"AURTO"."NORES_DATA" failed to create with error: ORA-02304: invalid object identifier literal Command executed: impdp  SCHEMAS=PORTO remap_schema=PORTO:AURTO remap_tablespace=PORTO:AURTO directory=PRODIMP dumpfile=PROD_%U.dmp logfile=PORTO_imp.log parallel=5 Error received: ORA-39083: Object type TYPE:"AURTO"."NORES_DATA" failed to create with error: ORA-02304: invalid object identifier literal Failing sql is: CREATE EDITIONABLE TYPE "AURTO"."NORES_DATA"   OID '6EE61264420D2234E04400144FEDB040' AS OBJECT (   VAL              VARCHAR2(200) ) ORA-39083: Object type TYPE:"AURTO"."NORES_TABLE_TYPE" failed to create with error: ORA-02304: invalid object identifier literal Failing sql is: CREATE EDITIONABLE TYPE "AURTO"."NORES_TABLE_TYPE"   OID '6EE6126442112234E04400144FEDB930' AS TABLE OF NORES_data Cause: Trying to c...

While Opening the database with resetlogs option post incomplete media recovery (Migrated database to another server), receiving the following error: SQL> alter database open resetlogs; alter database open resetlogs * ERROR at line 1: ORA-00349: failure obtaining block size for '/oracle/app/oracle/oradata/ORCL/redo01.log' ORA-27041: unable to open file Linux-x86_64 Error: 2: No such file or directory Additional information: 9 SQL> alter database open resetlogs; alter database open resetlogs * ERROR at line 1: ORA-00600: internal error code, arguments: [ksvworkmsgalloc: bad reaper], [0x080010003], [], [], [], [], [], [], [], [], [], [] SQL> ALTER DATABASE CLEAR UNARCHIVED LOGFILE GROUP 2; ALTER DATABASE CLEAR UNARCHIVED LOGFILE GROUP 2 * ERROR at line 1: ORA-00349: failure obtaining block size for '/oracle/app/oracle/oradata/ ORCL /redo02.log' ORA-27041: unable to open file Linux-x86_64 Error: 2: No such file or directory Ad...

How to set the root password after creating instance on oracle cloud? since you have created instance on oracle cloud, it requires you private .ppk key file to login from putty you can login on your instance by directly providing that private key. and on Linux/Unix defult user is "opc". since during creation of instance it  will not ask you for  root password. You dont need any password to connect to root. to connect to root type below command as opc user. [opc@sts ~]$ sudo su [root@sts opc]# by this you will login as root. Now change your password [root@sts opc]# passwd Changing password for user root. New password: Retype new password: passwd: all authentication tokens updated successfully. [root@sts opc]# All done. Thanks & Regards Nikhil Kumar.

How to  rename a Pluggable database??? SQL> -- we will change the  name of container database STSPDB to WALPDB SQL> show pdbs     CON_ID CON_NAME                       OPEN MODE  RESTRICTED ---------- ------------------------------ ---------- ----------          3 STSPDB                         READ WRITE NO SQL> shutdown immediate; Pluggable Database closed. SQL> SQL> SQL> show pdbs     CON_ID CON_NAME                       OPEN MODE  RESTRICTED ---------- ------------------------------ ---------- ----------           3 STSPDB                         MOUNTED SQL> startup open restrict; Pluggable Database opened...

#################################FAIL OVER#################################### --Primary Database Crushes Due to several reason like Hardware failure. On Standby Database: #Stop Redo Apply:-   ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL; #Finish applying all received redo data:- ALTER DATABASE RECOVER MANAGED STANDBY DATABASE FINISH; #Verify that the target standby database is ready to become a primary database:- SELECT SWITCHOVER_STATUS FROM V$DATABASE; #A value of either TO PRIMARY or SESSIONS ACTIVE indicates that the standby database is ready to be switched to the primary role. #Perform failover: ALTER DATABASE COMMIT TO SWITCHOVER TO PRIMARY WITH SESSION SHUTDOWN; /* Session shutdown is optional if  switchover status returns TO_PRIMARY */ srvctl status database -d ORCL_PRODRAC2 -v Thanks & Regards Nikhil Kumar.

#################### AWR (automatic workload repository) of Oracle #################### Retention period of AWR report: select retention from dba_hist_wr_control; /*If we have more than 7 days,we can reduce it to 7 day which is 7*24*60 = 10080 minutes. the retention period is modified to 7 days (10080 minutes) and  the interval between each snapshot is 60 minutes through below query. */ execute dbms_workload_repository.modify_snapshot_settings (interval => 60,retention => 10080); In case while reducing the retention period if we face an  error then check the MOVING_WINDOW_SIZE value with below query SELECT moving_window_size FROM dba_hist_baseline WHERE baseline_type = 'MOVING_WINDOW'; exec DBMS_WORKLOAD_REPOSITORY.modify_baseline_window_size( window_size =>7); Then update it to correct value and then execute the above AWR retention query. execute dbms_workload_repository.modify_snapshot_settings (interval => 60,retention => 10080); Once the...