Oracle Application DBA Information: January 2024

Monday, 29 January 2024

ORA-12754: Feature 'Per-PDB TDE keystore' is disabled due to missing capability 'Engineered Systems'.

[oracle@dbs19c pfile]$ sqlplus / as sysdba

SQL> alter session set container=pdb1;

Session altered.

SQL> show con_name

CON_NAME

------------------------------

PDB1

SQL> set lines 250 pages 250

SQL> col name format a20

SQL> SELECT name, open_mode FROM v$pdbs;

NAME OPEN_MODE

-------------------- ----------

PDB1 READ WRITE

SQL> show parameter tde_configuration

NAME TYPE VALUE

------------------------------------ ----------- ------------------------------

tde_configuration string

SQL> alter system set TDE_CONFIGURATION="KEYSTORE_CONFIGURATION=FILE";

alter system set TDE_CONFIGURATION="KEYSTORE_CONFIGURATION=FILE"

ERROR at line 1:

ORA-32017: failure in updating SPFILE

ORA-12754: Feature 'Per-PDB TDE keystore' is disabled due to missing capability

'Runtime Environment'.

Note: This feature Per-PDB TDE keystore is only for cloud database & System Engineered Machine.

SQL> show parameter _exadata

SQL> alter session set container=cdb$root;

Session altered.

SQL> alter system set "_exadata_feature_on"=true scope=spfile;

System altered.

SQL> shut immediate;

SQL> startup;

SQL> alter session set container=pdb1;

Session altered.

SQL> alter system set TDE_CONFIGURATION="KEYSTORE_CONFIGURATION=FILE";

System altered.

SQL> show parameter TDE_CONFIGURATION

NAME TYPE VALUE

------------------------------------ ----------- ------------------------------

tde_configuration string KEYSTORE_CONFIGURATION=FILE

Sunday, 28 January 2024

How to Create & Drop Pluggable Database in 19c Manually (Command Line)

EXAMPLES OF PLUGGABLE DATABASE CREATION & DROP

=================================

## CREATION PLUGGABLE DATABASE ##

=================================

1) The first method uses the CREATE_FILE_DEST clause in the CREATE PLUGGABLE DATABASE statement.

CREATE PLUGGABLE DATABASE pdb2 ADMIN USER pdb_adm IDENTIFIED BY Password1

CREATE_FILE_DEST='/u01/app/oracle/oradata';

2) The second method uses the FILE_NAME_CONVERT clause in the CREATE PLUGGABLE DATABASE statement.

CONN / AS SYSDBA

CREATE PLUGGABLE DATABASE pdb2 ADMIN USER pdb_adm IDENTIFIED BY Password1

FILE_NAME_CONVERT=('/u01/app/oracle/oradata/cdb1/pdbseed/','/u01/app/oracle/oradata/cdb1/pdb2/');

3) Alternatively, we can specify the PDB_FILE_NAME_CONVERT initialization parameter before calling the command without using the FILE_NAME_CONVERT clause.

CONN / AS SYSDBA

ALTER SESSION SET PDB_FILE_NAME_CONVERT='/u01/app/oracle/oradata/cdb1/pdbseed/','/u01/app/oracle/oradata/cdb1/pdb3/';

CREATE PLUGGABLE DATABASE pdb3 ADMIN USER pdb_adm IDENTIFIED BY Password1;

=============================

## DROP PLUGGABLE DATABASE ##

=============================

Connect as ROOT

---------------

sqlplus / as sysdba

SHOW PDBs;

ALTER PLUGGABLE DATABASE FURQAN CLOSE;

KEEP DATAFILES

--------------

The following statement drops the PDB pdb_name and it will remain its associated data files:

DROP PLUGGABLE DATABASE FURQAN KEEP DATAFILES;

INCLUDING DATAFILES

-------------------

The following statement drops the PDB pdb_name and its associated data files:

DROP PLUGGABLE DATABASE FURQAN INCLUDING DATAFILES;

Source : Internet / R&D

Thursday, 25 January 2024

HOW TO SET NEWNAME CLAUSE 11G R2 AND ONWORD

Important Note: Oracle Introduce New Feature "SET NEWNAME" in Oracle 11g Release 2 for RMAN Utility to Database or Datafiles. Now a days DBAs are using this feature for restoration in Non ASM to ASM Databases and RAC Databases.

Set NEWNAME Flexibility :

Suppose you are restoring datafiles from the backup, either on the same server or a different one such as staging. If the filesystem (or diskgroup) names are identical, you won’t have to change anything. But that is hardly ever the case. In staging the filesystems may be different, or perhaps you are restoring a production database to an ASM diskgroup different from where it was originally created. In that case you have to let RMAN know the new name of the datafile. The way to do it is using the SET NEWNAME command. Here is an example, where your restored files are located on /u02 instead of /u01 where they were codeviously.

run

{

set newname for datafile 1 to '/u02/oradata/system_01.dbf';

set newname for datafile 2 to '/u02/oradata/sysaux_01.dbf';

restore database; …

}

Here there are just two datafiles, but what if you have hundreds or even thousands? It will not only be a herculean task to enter all that information but it will be error-prone as well. Instead of entering each datafile by name, now you can use a single set newname clause for a tablespace. Here is how you can do it:

run

{

set newname for tablespace examples to '/u02/examples%b.dbf';

…

… rest of the commands come here …

}

If the tablespace has more than one datafile, they will all be uniquely created. You can use this clause for the entire database as well:

run

{

set newname for database to '/u02/oradata/%b';

}

The term %b specifies the base filename without the path, e.g. /u01/oradata/file1.dbf will be recodesented as file1.dbf in %b. This is very useful for cases where you are moving the files to a different directory. You can also use it for creating image copies where you will create the backup in a different location with the same names as the parent file which will make it easy for identification.

One caveat: Oracle Managed Files don’t have a specific basename; so this can’t be used for those. Here are some more examples of the placeholders.

%f is the absolute file number

%U is a system generated unique name similar to the %U in backup formats

%I is the Database ID

%N is the tablespace name

Source : Internet

How to STOP and START processes in Oracle RAC and Log Directory Structure

Shutdown RAC Database

You need to Shutdown Database instances on each node. You can either use Oracle Enterprise Manager or SVRCTL to shutdown the instances. If you are using EM Grid control then set a blackout in Grid control for processes that you intend to shutdown. So that records for these processes indicate that the shutdown was planned.

Use below command to stop Enterprise Manager/Grid Control

$ORACLE_HOME/bin/emctl stop dbconsole

Use below command to shutdown all oracle RAC instances on all nodes.

$ ORACLE_HOME/bin/srvctl stop database -d db_name

Use below command to status all oracle RAC instances on all nodes.

$ ORACLE_HOME/bin/srvctl status database -d db_name

If you want to stop specific database instances use below command

$ ORACLE_HOME/bin/srvctl stop database -d db_name –i instance_name

Shutdown Oracle ASM Instance

Once the database is stopped, proceed with ASM Instance shutdown.

Use below command to shutdown ASM instances on all nodes

$ORACLE_HOME/bin/bin/srvctl stop asm -n node

Shutdown Node applications

Use below command to shutdown node apps on all RAC nodes

$ORACLE_HOME/bin/bin/srvctl stop nodeapps -n node

Shutdown Oracle Clusterware

You need to Shutdown oracle clusterware or CRS as root and run below command on each node in the cluster.

#crsctl stop crs

Please note that using above command will stop Oracle High availability services (OHAS) and Clustware stack in a single command

From 11g R2 to onward, you can do this in two stops

1. Stop Clustwerware stack on local node

#crsctl stop cluster

You can stop the clusterware stack on all nodes in the cluster

# Crsctl stop cluster –all

Where

-all Start clusterware on all nodes

-n Start clusterware on particular nodes

2. Stop Oracle High availability service demon on each node in the cluster.

# crsctl stop has

Check the Status of Cluster

Once all process stopped run the below command to check the status of CRSD,CSSD,EVMD process.

# crsctl check crs

If you see any process failed to stop then you can also use Force option to terminate the processes unconditionally.

$ crsctl stop crs –all –f

Start processes in Oracle RAC

Follow the reverse sequence to start all processes in oracle RAC

# crsctl start crs

$ORACLE_HOME/bin/bin/srvctl start nodeapps -n node

$ORACLE_HOME/bin/bin/srvctl start asm -n node

$ORACLE_HOME/bin/srvctl start database -d db_name

If you come across any issues during startup orshutdown, check the Oracle Clusterware Component Log Files.

Oracle Clusterware Log Directory Structure

CRS_HOME/log/hostname/crsd/ - The log files for the CRS daemon CRS_HOME/log/hostname/cssd/ - The log files for the CSS daemon

CRS_HOME/log/hostname/evmd/ - The log files for the EVM daemon

CRS_HOME/log/hostname/client/ - The log files for the Oracle Cluster Registry (OCR)

CRS_HOME/log/hostname/racg/ - The log files for the Oracle RAC high availability component

CRS_HOME/log/hostanme/alert.log – The alert.log for Clusterware issues.

Please note that the CRS_HOME is the directory in which the Oracle Clusterware software was installed and hostname is the name of the node

uatdb1

srvctl start instance -d UAT -n uat-ebsdb1

uatdb2

srvctl start instance -d UAT -n uat-ebsdb2

Source : Internet

Tuesday, 23 January 2024

Vulnerability : Default or Guessable SNMP community names: public (snmp-read-0001)

#### To configure SNMP Follow below mentioned Command ####

1) Backup original snmpd.conf file

[root@ebstest snmp]# mv /etc/snmp/snmpd.conf /etc/snmp/snmpd.conf.bak

2) Create a new snmpd.conf file and populate it with a single line.

[root@ebstest snmp]# touch /etc/snmp/snmpd.conf

echo "rocommunity public" > /etc/snmp/snmpd.conf // Replace Public with any keyword like a strong Password and don't forget.

[root@ebstest snmp]# echo "rocommunity Fu#Q@n_12-cFk-cd" > /etc/snmp/snmpd.conf

3) Restart the SNMP service.

[root@ebstest snmp]# systemctl restart snmpd.service

Error: No space left on device

[root@ebstest snmp]# systemctl status snmpd.service

● snmpd.service - Simple Network Management Protocol (SNMP) Daemon.

Loaded: loaded (/usr/lib/systemd/system/snmpd.service; disabled; vendor preset: disabled)

Active: active (running) since Thu 2024-01-18 11:04:55 PKT; 1s ago

Main PID: 23311 (snmpd)

CGroup: /system.slice/snmpd.service

└─23311 /usr/sbin/snmpd -LS0-6d -f

Jan 18 11:04:55 ebstest.oracle.com systemd[1]: Starting Simple Network Management Protocol (SNMP) Daemon....

Jan 18 11:04:55 ebstest.oracle.com snmpd[23311]: NET-SNMP version 5.7.2

Jan 18 11:04:55 ebstest.oracle.com systemd[1]: Started Simple Network Management Protocol (SNMP) Daemon..

4) Test with snmpwalk command on local host

snmpwalk –v2c –c public localhost // Dont forgot to change public string with configured key

[root@ebstest snmp]# snmpget -v2c -c simbadb 192.168.25.165

Monday, 15 January 2024

Oracle Multitenant Architecture Database 19c (Useful Commands)

Note:

This Practical is containing all information about file architecture of CDB and PDB. How will start and stop database of CDB with PDB and how to connect with CDB & PDB etc... This practical will help you make understanding with 19c multitenant architecture how to work.

1- Root, named CDB$ROOT. This contains Oracle metadata and common users (sys,system etc..).

2- Seed PDB, named PDB$SEED. This is a template that can be used to create new PDBs. You can’t add or modify objects in this PDB (PDB$SEED).

3- PDB is a pluggable database it will contain collection of objects like schemas and others objects(tablespaces etc..).

4- Below diagrams will help you understand that difference between CDB & PDB.

[oracle@dbs19c ~]$ sqlplus / as sysdba

SQL*Plus: Release 19.0.0.0.0 - Production on Mon Jan 15 11:51:26 2024

Version 19.3.0.0.0

Connected to:

Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production

Version 19.3.0.0.0

SQL> set lines 133 pages 133

SQL> desc dba_data_files;

Name Null? Type

------------------------------------------------------------------------- -------- -------------------------------

FILE_NAME VARCHAR2(513)

FILE_ID NUMBER

TABLESPACE_NAME VARCHAR2(30)

BYTES NUMBER

BLOCKS NUMBER

STATUS VARCHAR2(9)

RELATIVE_FNO NUMBER

AUTOEXTENSIBLE VARCHAR2(3)

MAXBYTES NUMBER

MAXBLOCKS NUMBER

INCREMENT_BY NUMBER

USER_BYTES NUMBER

USER_BLOCKS NUMBER

ONLINE_STATUS VARCHAR2(7)

LOST_WRITE_PROTECT VARCHAR2(7)

SQL> col FILE_NAME format a60

SQL> select TABLESPACE_NAME,FILE_NAME from dba_data_files;

TABLESPACE_NAME FILE_NAME

------------------------------ ------------------------------------------------------------

SYSTEM /u01/app/oracle/oradata/DBS19C/system01.dbf

SYSAUX /u01/app/oracle/oradata/DBS19C/sysaux01.dbf

UNDOTBS1 /u01/app/oracle/oradata/DBS19C/undotbs01.dbf

USERS /u01/app/oracle/oradata/DBS19C/users01.dbf

SQL> create tablespace dbs19c_Tablespace_TDE datafile '/u01/app/oracle/oradata/DBS19C/tde_tbs1.dbf' size 100M;

Tablespace created.

SQL> select TABLESPACE_NAME,FILE_NAME from dba_data_files;

TABLESPACE_NAME FILE_NAME

------------------------------ ------------------------------------------------------------

SYSTEM /u01/app/oracle/oradata/DBS19C/system01.dbf

SYSAUX /u01/app/oracle/oradata/DBS19C/sysaux01.dbf

UNDOTBS1 /u01/app/oracle/oradata/DBS19C/undotbs01.dbf

USERS /u01/app/oracle/oradata/DBS19C/users01.dbf

DBS19C_TABLESPACE_TDE /u01/app/oracle/oradata/DBS19C/tde_tbs1.dbf

SQL> create user furqan identified by hafeez default tablespace DBS19C_TABLESPACE_TDE quota unlimited on DBS19C_

create user furqan identified by hafeez default tablespace DBS19C_TABLESPACE_TDE quota unlimited on DBS19C_TABLE

ERROR at line 1:

ORA-65096: invalid common user or role name

SQL> show con_name

CON_NAME

------------------------------

CDB$ROOT

SQL> alter session set container=pdb;

Session altered.

SQL> create user teddb identified by haf default tablespace DBS19C_TABLESPACE_TDE quota unlimited on DBS19C_TABL

create user teddb identified by haf default tablespace DBS19C_TABLESPACE_TDE quota unlimited on DBS19C_TABLESPAC

ERROR at line 1:

ORA-01109: database not open

SQL> show user

USER is "SYS"

SQL> show con_name

CON_NAME

------------------------------

PDB

SQL> alter pluggable database pdb open;

Pluggable database altered.

SQL> alter pluggable database pdb save state;

Pluggable database altered.

SQL> alter session set container=pdb;

Session altered.

SQL> create user furqan identified by hafeez default tablespace DBS19C_TABLESPACE_TDE quota unlimited on DBS19C_

create user furqan identified by hafeez default tablespace DBS19C_TABLESPACE_TDE quota unlimited on DBS19C_TABLE

ERROR at line 1:

ORA-00959: tablespace 'DBS19C_TABLESPACE_TDE' does not exist

SQL> select TABLESPACE_NAME,FILE_NAME from dba_data_files;

TABLESPACE_NAME FILE_NAME

------------------------------ ------------------------------------------------------------

UNDOTBS1 /u01/app/oracle/oradata/DBS19C/pdb/undotbs01.dbf

SYSAUX /u01/app/oracle/oradata/DBS19C/pdb/sysaux01.dbf

SYSTEM /u01/app/oracle/oradata/DBS19C/pdb/system01.dbf

USERS /u01/app/oracle/oradata/DBS19C/pdb/users01.dbf

SQL> col name format a10

SQL> SELECT NAME, CON_ID, DBID, CON_UID, GUID FROM V$CONTAINERS ORDER BY CON_ID;

NAME CON_ID DBID CON_UID GUID

---------- ---------- ---------- ---------- --------------------------------

PDB 3 962847719 962847719 0EA7FFC0D70E4082E0632B29018C49BE

SQL> COLUMN PDB_NAME FORMAT A15

SQL> SELECT PDB_ID, PDB_NAME, STATUS FROM DBA_PDBS ORDER BY PDB_ID;

PDB_ID PDB_NAME STATUS

---------- --------------- ----------

3 PDB NORMAL

SQL> shut immediate;

Pluggable Database closed.

SQL> SELECT PDB_ID, PDB_NAME, STATUS FROM DBA_PDBS ORDER BY PDB_ID;

SELECT PDB_ID, PDB_NAME, STATUS FROM DBA_PDBS ORDER BY PDB_ID

ERROR at line 1:

ORA-01219: database or pluggable database not open: queries allowed on fixed tables or views only

SQL> select status from v$instance;

STATUS

------------

MOUNTED

SQL> alter pluggable database pdb open;

Pluggable database altered.

SQL> select status from v$instance;

STATUS

------------

OPEN

SQL> alter pluggable database pdb close;

Pluggable database altered.

SQL> select status from v$instance;

STATUS

------------

MOUNTED

SQL> conn / as sysdba

Connected.

SQL> show con_name

CON_NAME

------------------------------

CDB$ROOT

SQL> shut immediate;

Database closed.

Database dismounted.

ORACLE instance shut down.

SQL> startup;

ORACLE instance started.

Total System Global Area 2248145928 bytes

Fixed Size 9137160 bytes

Variable Size 503316480 bytes

Database Buffers 1728053248 bytes

Redo Buffers 7639040 bytes

Database mounted.

Database opened.

SQL> desc V$CONTAINERS

Name Null? Type

------------------------------------------------------------------------- -------- -------------------------------------------------

CON_ID NUMBER

DBID NUMBER

CON_UID NUMBER

GUID RAW(16)

NAME VARCHAR2(128)

OPEN_MODE VARCHAR2(10)

RESTRICTED VARCHAR2(3)

OPEN_TIME TIMESTAMP(3) WITH TIME ZONE

CREATE_SCN NUMBER

TOTAL_SIZE NUMBER

BLOCK_SIZE NUMBER

RECOVERY_STATUS VARCHAR2(8)

SNAPSHOT_PARENT_CON_ID NUMBER

APPLICATION_ROOT VARCHAR2(3)

APPLICATION_PDB VARCHAR2(3)

APPLICATION_SEED VARCHAR2(3)

APPLICATION_ROOT_CON_ID NUMBER

APPLICATION_ROOT_CLONE VARCHAR2(3)

PROXY_PDB VARCHAR2(3)

LOCAL_UNDO NUMBER

UNDO_SCN NUMBER

UNDO_TIMESTAMP DATE

CREATION_TIME DATE

PDB_COUNT NUMBER

AUDIT_FILES_SIZE NUMBER

MAX_SIZE NUMBER

MAX_DIAGNOSTICS_SIZE NUMBER

MAX_AUDIT_SIZE NUMBER

LAST_CHANGED_BY VARCHAR2(11)

MEMBER_CDB VARCHAR2(3)

TENANT_ID VARCHAR2(256)

UPGRADE_LEVEL NUMBER

GUID_BASE64 VARCHAR2(30)

SQL> desc DBA_PDBS

Name Null? Type

------------------------------------------------------------------------- -------- -------------------------------------------------

PDB_ID NOT NULL NUMBER

PDB_NAME NOT NULL VARCHAR2(128)

DBID NOT NULL NUMBER

CON_UID NOT NULL NUMBER

GUID RAW(16)

STATUS VARCHAR2(10)

CREATION_SCN NUMBER

VSN NUMBER

LOGGING VARCHAR2(9)

FORCE_LOGGING VARCHAR2(39)

FORCE_NOLOGGING VARCHAR2(3)

APPLICATION_ROOT VARCHAR2(3)

APPLICATION_PDB VARCHAR2(3)

APPLICATION_SEED VARCHAR2(3)

APPLICATION_ROOT_CON_ID NUMBER

IS_PROXY_PDB VARCHAR2(3)

CON_ID NOT NULL NUMBER

UPGRADE_PRIORITY NUMBER

APPLICATION_CLONE VARCHAR2(3)

FOREIGN_CDB_DBID NUMBER

UNPLUG_SCN NUMBER

FOREIGN_PDB_ID NUMBER

CREATION_TIME NOT NULL DATE

REFRESH_MODE VARCHAR2(6)

REFRESH_INTERVAL NUMBER

TEMPLATE VARCHAR2(3)

LAST_REFRESH_SCN NUMBER

TENANT_ID VARCHAR2(255)

SNAPSHOT_MODE VARCHAR2(6)

SNAPSHOT_INTERVAL NUMBER

CREDENTIAL_NAME VARCHAR2(262)

SQL> alter session set container=pdb;

Session altered.

SQL> show con_name

CON_NAME

------------------------------

PDB

SQL> ALTER SESSION SET container=CDB$ROOT;

Session altered.

SQL> ALTER SESSION SET CONTAINER=PDB$SEED;

Session altered.

SQL> SHOW CON_NAME

CON_NAME

------------------------------

PDB$SEED

SQL> SELECT name, pdb FROM v$services;

no rows selected

SQL> ALTER SESSION SET CONTAINER=PDB;

Session altered.

SQL> COL PDB FORMAT A15

SQL> SELECT name, pdb FROM v$services;

NAME PDB

---------- ---------------

pdb PDB

SQL> ALTER SESSION SET CONTAINER=PDB$SEED;

Session altered.

SQL> SELECT name, pdb FROM v$services;

no rows selected

SQL> ALTER SESSION SET CONTAINER=CDB$ROOT;

Session altered.

SQL> COL NAME FORMAT A25

SQL> SELECT name, pdb FROM v$services;

NAME PDB

------------------------- ---------------

dbs19cXDB CDB$ROOT

dbs19c.hinopak.com CDB$ROOT

pdb PDB

SYS$BACKGROUND CDB$ROOT

SYS$USERS CDB$ROOT

SQL> show con_name

CON_NAME

------------------------------

CDB$ROOT

SQL> alter session set container=pdb;

Session altered.

SQL> alter pluggable database close;

Pluggable database altered.

SQL> startup;

Pluggable Database opened.

SQL> shut immediate;

Pluggable Database closed.

SQL> alter pluggable database open;

Pluggable database altered.

SQL> alter pluggable database pdb save state;

Pluggable database altered.

SQL> alter session set container=CDB$ROOT;

Session altered.

SQL> shut immediate;

Database closed.

Database dismounted.

ORACLE instance shut down.

SQL> startup;

SQL> ORACLE instance started.

Total System Global Area 2248145928 bytes

Fixed Size 9137160 bytes

Variable Size 503316480 bytes

Database Buffers 1728053248 bytes

Redo Buffers 7639040 bytes

Database mounted.

Database opened.

SQL> alter session set container=pdb;

Session altered.

SQL> select status from v$instance;

STATUS

------------

OPEN

SQL> show con_name

CON_NAME

------------------------------

PDB

SQL> SELECT SYS_CONTEXT('USERENV', 'CON_NAME')FROM dual;

SYS_CONTEXT('USERENV','CON_NAME')

---------------------------------

PDB

SQL> COLUMN name FORMAT A30

SQL> SELECT name, open_mode, recovery_status FROM v$pdbs ORDER BY 1;

NAME OPEN_MODE RECOVERY

------------------------------ ---------- --------

PDB READ WRITE ENABLED

SQL> show con_name

CON_NAME

------------------------------

PDB

SQL> conn / as sysdba

Connected.

SQL> show con_name

CON_NAME

------------------------------

CDB$ROOT

SQL> COLUMN name FORMAT A30

SQL> SELECT name, open_mode, recovery_status FROM v$pdbs ORDER BY 1 ;

NAME OPEN_MODE RECOVERY

------------------------------ ---------- --------

PDB READ WRITE ENABLED

PDB$SEED READ ONLY ENABLED

SQL> show parameter control_files

NAME TYPE VALUE

------------------------------------ ----------- ------------------------------

control_files string /u01/app/oracle/oradata/DBS19C/control01.ctl, /u01/app/oracle/fast_recovery_area/DBS19C/control02.ctl

SQL> alter session set container=pdb;

Session altered.

SQL> show parameter control_files

NAME TYPE VALUE

------------------------------------ ----------- ------------------------------

control_files string /u01/app/oracle/oradata/DBS19C/control01.ctl, /u01/app/oracle/fast_recovery_area/DBS19C/control02.ctl

SQL> desc v$logfile;

Name Null? Type

------------------------------------------------------------------------- -------- -------------------------------------------------

GROUP# NUMBER

STATUS VARCHAR2(7)

TYPE VARCHAR2(7)

MEMBER VARCHAR2(513)

IS_RECOVERY_DEST_FILE VARCHAR2(3)

CON_ID NUMBER

SQL> show con_name

CON_NAME

------------------------------

PDB

SQL> col MEMBER format a60

SQL> select GROUP#,STATUS,MEMBER from v$logfile;

GROUP# STATUS MEMBER

---------- ------- ------------------------------------------------------------

3 /u01/app/oracle/oradata/DBS19C/redo03.log

2 /u01/app/oracle/oradata/DBS19C/redo02.log

1 /u01/app/oracle/oradata/DBS19C/redo01.log

SQL> show parameter undo

NAME TYPE VALUE

------------------------------------ ----------- ------------------------------

temp_undo_enabled boolean FALSE

undo_management string AUTO

undo_retention integer 900

undo_tablespace string UNDOTBS1

Sunday, 14 January 2024

HOW TO RESOLVE ORA-00031: session marked for kill

ERROR:

ERROR: ORA-00031: session marked for kill

SOLUTION:

Note: You may find out thread id for session kil actvity from Operating Sysyem (Linux OS)

select ab.sid, ab.username, ab.osuser, ab.process fg_pid,

cd.spid bg_pid from v$session ab, v$process cd

where ab.paddr = cd.addr;

(OR)

If you know the system user name (OS Naming Unser) you may easily find out SPID.

select ab.sid,ab.username,ab.osuser, ab.process,cd.spid

from v$session ab, v$process cd where ab.paddr = cd.addr

and ab.username=upper('&schema')

and ab.osuser='&system_hostname';

SID USERNAME OSUSER PROCESS SPID

---- --------------- -------------- --------- ---------

99 FURQAN furqan-hafeez 2194:2565 274438

[furqan@furqan-hafeez ~]$ ps -ef |grep 274438

[furqan@furqan-hafeez ~]$ kill -9 274438

Tuesday, 9 January 2024

How to Resolve Control file Restoration Error ORA-19913: unable to decrypt backup ORA-28365: wallet is not open

Important Note: If you are using oracle wallet you may stock in restoration of control file procedure.

### RESTORE CONTROLFILE ###

$ rman target /

RMAN > restore controlfile from '/backup/stage/bkp_ctl.ctl';

### ERROR ###

channel ORA_DISK_1: restoring control file

RMAN-00571: ===========================================================

RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============

RMAN-00571: ===========================================================

RMAN-03002: failure of restore command at 11/23/2023 15:22:44

ORA-19870: error while restoring backup piece /backup/stage/bkp_ctl.ctl

ORA-19913: unable to decrypt backup

ORA-28365: wallet is not open

### SOLUTION ###

$ cd /u01/app/oracle

$ cp -Rf wallet_for_12c wallet

### EDIT sqlnet_ifile.ora ###

$ cd /u01/app/oracle/product/12.1.0/db_1/network/admin/PROD_erpenv001

$ vi sqlnet_ifile.ora

ENCRYPTION_WALLET_LOCATION = (SOURCE = (METHOD = FILE) (METHOD_DATA = (DIRECTORY = /u01/app/oracle/wallet/$ORACLE_SID))

$ sqlplus / as sysdba

SQL> alter system set encryption wallet open identified by "133#Rd2q#dW#dC4";

System altered.

### NOW RESTORE CONTROLFILE AGAIN ###

$ rman target /

RMAN> restore controlfile from '/backup/stage/bkp_ctl.ctl';

Starting restore at 23-NOV-23

using target database control file instead of recovery catalog

allocated channel: ORA_DISK_1

channel ORA_DISK_1: SID=3 device type=DISK

channel ORA_DISK_1: restoring control file

channel ORA_DISK_1: restore complete, elapsed time: 00:00:03

output file name=/u01/oracle/fur/apps_st/data/cntrl_01.dbf

output file name=/u01/oracle/fur/apps_st/data/cntrl_02.dbf

output file name=/u01/oracle/fur/apps_st/data/cntrl_03.dbf

Finished restore at 23-NOV-23

$ exit

$ sqlplus / as sysdba

SQL> alter database mount;

Note : After performed above mentioned step you able to catalog your backupset and restore of your datafile from backupset.

Wednesday, 3 January 2024

Authentication Failed When Opening pdf in Edge Browser from Application R12

"Authentication failed." Message is displayed in Output after Saving a Report from Edge Browser. (Doc ID 2870889.1)

Solution:

The solution is to increase the size of the cache in Edge browser.

Sample setup:

1. Create Microsoft Edge browser shortcut on the desktop.
2. Right-click the Edge icon and choose Properties.
3. Switch to the Shortcuts tab of Edge Properties Window.
4. In the target field, append the following text to the entry provided -disk-cache-size-<size in bytes>.
5. Click Apply Button.
6. Restart the Edge browser.
7. Retest the issue.

Source : Oracle Support

ODA DCS-10001: Internal error encountered: Not able to create new job at this time In Oracle Database Appliance (ODA)

ODA DCS-10001: Internal error encountered: Not able to create new job at this time. DCS-Agent is in state of ReadyToUpgrade state. (Doc ID 2727668.1)

SOLUTION

1. Run below on both nodes:

touch /opt/oracle/dcs/conf/.agent_upgraded

2. Restart agent for both nodes:

• systemctl stop initdcsagent

• systemctl start initdcsagent

• /opt/zookeeper/bin/zkServer.sh stop

• /opt/zookeeper/bin/zkServer.sh start

Source : Oracle Support.

How To Change Password of Naming User without login on Application R12

Note:

You may change the password of naming users in Oracle E-Business Suite R12 (Application) without Login on Application from the backend.

Connect Apps/Apps@PROD

DECLARE

l_ret_val BOOLEAN;

l_user_name varchar2(50) := '&USER_NAME';

l_new_pwd varchar2(20) := '&PASSWORD';

BEGIN

l_ret_val := fnd_user_pkg.changepassword(username => l_user_name, newpassword => l_new_pwd);

IF l_ret_val THEN

DBMS_OUTPUT.PUT_LINE('The password is successfully reset to ' || l_new_pwd);

COMMIT;

ELSE

DBMS_OUTPUT.PUT_LINE('The password reset has failed');

END IF;

END;

Tuesday, 2 January 2024

Exadata & Database Machine Administration Workshop

https://drive.google.com/file/d/1jOr4QJNS520515KD6H78iRaNwrM_wI1C/view?usp=sharing

Source : Internet (Oracle Corporation).

Monday, 1 January 2024

How To Increasing Output Post Process Values In Apps R12

Error (encounter in 325-ABCD Account Analysis Subledger 150) report

+---------------------------------------------------------------------------+

General Ledger: Version : 12.0.0

325 module: 325-Hinopak Account Analysis Subledger 150

+---------------------------------------------------------------------------+

Current system time is 15-DEC-2017 10:03:03

+---------------------------------------------------------------------------+

+-----------------------------

| Starting concurrent program execution...

+-----------------------------

Arguments

------------

p_datef='16-NOV-2017'

p_datet='30-NOV-2017'

p_facont='2900001'

p_tacont='3921102'

------------

Forcing NLS_NUMERIC_CHARACTERS to: '.,' for XDO processing

Current NLS_LANG and NLS_NUMERIC_CHARACTERS Environment Variables are :

American_America.US7ASCII

'.,'

Enter Password:

Report Builder: Release 10.1.2.3.0 - Production on Fri Dec 15 10:03:04 2017

+---------------------------------------------------------------------------+

Start of log messages from FND_FILE

+---------------------------------------------------------------------------+

End of log messages from FND_FILE

+---------------------------------------------------------------------------+

Executing request completion options...

Output file size:

99138382

+------------- 1) PUBLISH -------------+

Beginning post-processing of request 74442184 on node APPS at 15-DEC-2017 10:07:33.

Post-processing of request 74442184 failed at 15-DEC-2017 10:10:27 with the error message:

One or more post-processing actions failed. Consult the OPP service log for details.

+--------------------------------------+

+------------- 2) PRINT -------------+

Not printing the output of this request because post-processing failed.

+--------------------------------------+

Finished executing request completion options.

+---------------------------------------------------------------------------+

Concurrent request completed

Current system time is 15-DEC-2017 10:10:27

+---------------------------------------------------------------------------+

Solution

Step 01

1- Find value in profile option

Before Values

Concurrent: OPP Process Timeout 600

Concurrent: OPP Response Timeout 240

Step 02

After Value (replace with old values)

Concurrent: OPP Process Timeout 1800

Concurrent: OPP Response Timeout 600

Step 03

Go to the concurrent => Manager => Administer

Step 04

Restart the concurrent of Output Post Process

Step 05

Re-run => the 325-FURQAN Account Analysis Subledger 150

Issue Resolve.