Sujeet APPS DBA

How to open the PDBS automatically when CDB restarts

In oracle 12c , when we startup the CDB, the PDBS will be in MOUNTED stage.
We need to open them manually using alter pluggable PDB open.

To make the PDBS open automatically, we can use the saved state option .

This feature is available from Oracle 12.1.0.2 onwards

1. Restart the container database:

SQL> show con_name

CON_NAME
------------------------------
CDB$ROOT

SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.

SQL> startup
ORACLE instance started.

Total System Global Area 1.4529E+10 bytes
Fixed Size 7649368 bytes
Variable Size 8489273256 bytes
Database Buffers 5939134464 bytes
Redo Buffers 93011968 bytes
Database mounted.
Database opened.

2. Check the status of PDBS:

SQL> show pdbs

CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
2 PDB$SEED READ ONLY NO
3 PDB1 MOUNTED
4 PDB2 MOUNTED
5 NONCDB MOUNTED

We can see the PDBS are in MOUNTED stage after starting CDB. Lets open them

3. Open the PDBS:

SQL> alter pluggable database NONCDB open;

Pluggable database altered.

SQL> alter pluggable database PDB1 open;

Pluggable database altered.

SQL> alter pluggable database PDB2 open;

Pluggable database altered.

SQL> show pdbs

CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
2 PDB$SEED READ ONLY NO
3 PDB1 READ WRITE NO
4 PDB2 READ WRITE NO
5 NONCDB READ WRITE NO

Now use save state command to save the states, so that next time ,when container db restarts,
PDBs will in READ WRITE MODE automatically.

4. Save the PDB state:

SQL> alter pluggable database NONCDB save state;

Pluggable database altered.

SQL> alter pluggable database PDB1 save state;

Pluggable database altered.

SQL> alter pluggable database PDB2 save state;

Pluggable database altered.

5. check the saved state in dba_pdb_saved_states

SQL> select con_name, state from dba_pdb_saved_states;

CON_NAME STATE
----------------------- --------------
NONCDB OPEN
PDB1 OPEN
PDB2 OPEN

6.Bounce the container database, to check the PDB state:

SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.

SQL> startup
ORACLE instance started.

Total System Global Area 1.4529E+10 bytes
Fixed Size 7649368 bytes
Variable Size 8489273256 bytes
Database Buffers 5939134464 bytes
Redo Buffers 93011968 bytes
Database mounted.
Database opened.

SQL> show pdbs

CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
2 PDB$SEED READ ONLY NO
3 PDB1 READ WRITE NO
4 PDB2 READ WRITE NO
5 NONCDB READ WRITE NO

We can observed that PDBs are in READ WRITE mode automatically.

DISCARD STATE OPTION:

We can discard the saved state, so that next time CDB restarts, PDBs will start with MOUNT STATE only.

Lets discard state for PDB2 .

SQL> select con_name, state from dba_pdb_saved_states;

CON_NAME STATE
----------------------- --------------
NONCDB OPEN
PDB1 OPEN
PDB2 OPEN

SQL> alter pluggable database PDB2 discard state;

Pluggable database altered.

SQL> select con_name, state from dba_pdb_saved_states;

CON_NAME STATE
----------------------- --------------
NONCDB OPEN
PDB1 OPEN

1. Bounce the CDB to check the PDB State:

SQL> show pdbs

CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
2 PDB$SEED READ ONLY NO
3 PDB1 READ WRITE NO
4 PDB2 READ WRITE NO
5 NONCDB READ WRITE NO

SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.

SQL> startup
ORACLE instance started.

Total System Global Area 1.4529E+10 bytes
Fixed Size 7649368 bytes
Variable Size 8489273256 bytes
Database Buffers 5939134464 bytes
Redo Buffers 93011968 bytes
Database mounted.
Database opened.

SQL> show pdbs

CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
2 PDB$SEED READ ONLY NO
3 PDB1 READ WRITE NO
4 PDB2 MOUNTED
5 NONCDB READ WRITE NO

We can observe that, only PDB2 started in MOUNTED stage, because we have discarded the saved state for PDB2 .

As this feature is not available in 12.1.0.1 , we can create a trigger to open the PDBs automatically, with container database startup.

CREATE TRIGGER open_all_pdbs
AFTER STARTUP ON DATABASE
BEGIN
EXECUTE IMMEDIATE ‘ALTER PLUGGABLE DATABASE ALL OPEN’;
END ;
/

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

Reference :-

https://dbaclass.com/article/how-to-open-the-pdbs-automatically-when-cdb-restarts/

https://community.oracle.com/thread/4086167?start=0&tstart=0

ORA-20106

When i was run sqltxtract.sql script with SQL_id facing below error.

# cd sqlt/run
# sqlplus apps
SQL> START sqltxtract.sql SQL_ID [HASH_VALUE] [sqltxplain_password]

SQL> START sqltxtract.sql 223a9p72spa100 *****

Error :-

DECLARE
*
ERROR at line 1:
ORA-20106: SQLT parameter connect_identifier must be set when running SQLT from a remote client
ORA-06512: at line 10

Solution :-

The only solution to avoid such error is to set connect_identifier using below command:

=================
EXEC sqltxadmin.sqlt$a.set_sess_param(‘connect_identifier’, ‘@SID’);
=================

In case if you are getting above error in RDS Orale then please use below command to set Connect Identifier.

=================
EXEC sqltxadmin.sqlt$a.set_param(‘connect_identifier’, ‘@<hostname>:<port>/<sid>’);

# sqlplus / as sysdba

SQL> EXEC sqltxadmin.sqlt$a.set_param('connect_identifier', '@sonu.oracle.com:1521/PROD');

PL/SQL procedure successfully completed.

SQL>

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

ORA-00600: internal error code, arguments

Facing below issue when database server down due to power outage.

SQL > startup

Error :-

ORA-00600: internal error code, arguments: [kcratr_scan_lastbwr], [], [], [],

[], [], [], [], [], [], [], []

Cause:-

This is the generic internal error number for Oracle program exceptions. It indicates that a process has encountered a low-level, unexpected condition. Causes of this message include:

1. Timeouts
2. File corruption
3. Failed data checks in memory
4. Hardware, memory, or I/O errors
5. Incorrectly restored files

Solution :-

SQL> shutdown immediate
SQL> startup mount
SQL>alter database recover database;
SQL> alter database open

SYSTEM-UNABLE TO SET NLS SORT

After clone i am not able to run active user concurrent program. facing below issue. Please find log file.
Application Object Library: Version : 12.1

Copyright (c) 1998, 2013, Oracle and/or its affiliates. All rights reserved.

FNDSCURS: Active Users
+---------------------------------------------------------------------------+

Current system time is 13-JAN-2020 12:36:09
+---------------------------------------------------------------------------+
+-----------------------------
| Starting concurrent program execution...
+-----------------------------

SYSTEM-UNABLE TO SET NLS SORT
Concurrent Manager encountered an error while running Oracle*Report for your concurrent request 23849699.

Review your concurrent request log and/or report output file for more detailed information.
+---------------------------------------------------------------------------+
Executing request completion options...

Output file size:

Finished executing request completion options.
+---------------------------------------------------------------------------+
Concurrent request completed
Current system time is 13-JAN-2019 12:36:10

Solution :-

Solution is to apply Patch 24675262:R12.FND.B: SCHEDULED REQUEST ERROR SYSTEM-UNABLE TO SET NLS SORT DUE TO NEW NLS_SORT COLUMN, but this patch contains a version of afpcrm.oc that can cause potential data corruption - see bug 23021248.

Ref: BUG 23021248 - CONC REQUEST RUNNING TWICE CREATING BALANCE CORRUPTION

Apply Patch 23021248:R12.FND.B: CONC REQUEST RUNNING TWICE CREATING BALANCE CORRUPTION

ACTION PLAN
=============

On a test environment where the issue occurs, download and apply patch 23021248.

1. Download patch 23021248.
2. Enable 'Maintenance Mode.
3. execute adpatch.
4. Disable 'Maintenance Mode'

SQL> select bug_number,creation_date from ad_bugs where bug_number='23021248';

BUG_NUMBER CREATION_
------------------------------ ---------

23021248 15-JAN-20

Retest issue. I hope issue got resolved.

PLS-00905: object APPS.APPS is invalid

Today development team has reported an issue that when they are trying to compile any package which
have apps.<> schema reference thrown an error:

SQL> alter package XX_XXXX_PKG compile body;

Warning: Package Body altered with compilation errors.

SQL> show error
Errors for PACKAGE BODY XX_XXXX_PKG:

LINE/COL ERROR
-------- -----------------------------------------------------------------
1130/16 PL/SQL: Statement ignored
1130/16 PLS-00905: object APPS.APPS is invalid
1379/16 PL/SQL: Statement ignored
1379/16 PLS-00905: object APPS.APPS is invalid
1563/16 PL/SQL: Statement ignored
1563/16 PLS-00905: object APPS.APPS is invalid
1791/16 PL/SQL: Statement ignored
1791/16 PLS-00905: object APPS.APPS is invalid
SQL>

Cause:
An invalid package specification or stored subprogram was referenced.
A package specification or stored subprogram is invalid if its source code or
any database object it references has been DROPped, REPLACEd, or ALTERed since it was last compiled.

Action:
Find out what invalidated the package specification or stored subprogram,
then make sure that Oracle can recompile it without errors.
Now we should make sure that we have created the package in the right place.

Steps-1.

Package code I have compiled successfully in other instances like QA,
however got the error in dev instance. If we remove schema reference (apps) then it’s compiling from dev also
but as per coding standard it has good practice to use apps.<>.

Steps-2.

Now we should make sure that we have created the package in the right place.

Connect with apps schema :-

[Dev@oracle ~]$ sqlplus apps/apps

SQL > select owner, object_name, object_type
from dba_objects
where object_name='XX_XXXX_PKG'
order by 1,2,3;

Output- Perfect no issue in place.
owner object_name object_type
APPS XX_XXXX_PKG PACKAGE
APPS XX_XXXX_PKG PACKAGE BODY

Steps-3.

I have checked the compiler log again and found APPS.APPS that means some developer mistakenly crated the apps
name package on apps schema. That may be our case.

Now check :

SQL > select owner, object_name, object_type
from dba_objects
where OBJECT_NAME='APPS'
order by 1,2,3;

found one object created with name apps.

Steps-4.

Now we should drop apps object

SQL > drop package apps;

Steps-5.

Try compiling the package in dev instance again.

I hope issue fix.

Inventory load failed

Inventory load failed... OPatch cannot load inventory for the given Oracle Home.
Possible causes are:

[ora@oracle OPatch]$ opatch lsinventory

Oracle Interim Patch Installer version 12.1.0.1.3
Copyright (c) 2019, Oracle Corporation. All rights reserved.

Oracle Home : /u01/app/db/tech_st/12.1.0.2
Central Inventory : /u01/app/db/oraInventory
from : /u01/app/db/tech_st/12.1.0.2/oraInst.loc
OPatch version : 12.1.0.1.3
OUI version : 12.1.0.2.0
Log file location : /u01/app/db/tech_st/12.1.0.2/cfgtoollogs/opatch/opatchAM_1.log

List of Homes on this system:

Inventory load failed... OPatch cannot load inventory for the given Oracle Home.
Possible causes are:
Oracle Home dir. path does not exist in Central Inventory
Oracle Home is a symbolic link
Oracle Home inventory is corrupted
LsInventorySession failed: OracleHomeInventory gets null oracleHomeInfo

OPatch failed with error code 73

Solution

[ora@oracle ~]$ cd $ORACLE_HOME/oui/bin
[ora@oracle bin]$ pwd
/u01/app/db/tech_st/12.1.0.2/oui/bin

[ora@oracle bin]$ vi attachHome.sh

[ora@oracle bin]$ ./attachHome.sh

Starting Oracle Universal Installer...

Checking swap space: must be greater than 500 MB. Actual 8191 MB Passed
The inventory pointer is located at /etc/oraInst.loc

'AttachHome' was successful.

Retest issue.

[ora@oracle OPatch]$ opatch lsinventory

Issue has been resolved.

Recover a dropped table in Oracle 11g

How to recover a dropped table in Oracle 11g when flashback mode is off

After dropping table and before restoring it from the recycle bin, run the following query:

Login with table owner schema.

SQL > SELECT OBJECT_NAME, ORIGINAL_NAME, TYPE FROM RECYCLEBIN;

if table is there than use below statement to restore.

Restore the table with the following command:

SQL > FLASHBACK TABLE drop_table_NAME TO BEFORE DROP;

Recovering dropped table is easy in Oracle, provided that the table was not dropped with PURGE option. In case the table is dropped and space occupied by the table is released and the table does not get moved into the recycle bin. But if table is dropped without PURGE option, Oracle has this very neat feature - Recycle bin, similar to the recycle bin in Windows. There are two recyle bin views in Oracle: USER_RECYCLEBIN and DBA_RECYCLEBIN, Synonym RECYCLEBIN points to your USER_RECYCLEBIN.

The recycle bin can be turned on and off with RECYCLEBIN initialization parameter. When table is dropped, it get rename to system-generated name preceeded with BIN and stored in recycle bin. The important thing to know is that after table has been dropped, it's only been renamed, the table segmants are still in the tablespace, unchanged. the space occupied by the table will not be reclaimed until the table has been purged from the recycle bin.

While in the recycle bin, the table can even be queried using the newly generated name that starts qwith BIN$.

The table can easily be recovered from the recycle bin using flashback drop, which will rename the table to its original name.

You can check flashback mode by running

SELECT FLASHBACK_ON FROM V$DATABASE;

First check the parameter Recyclebin is set to true. Recycle bin is a data dictionary table that contains information about dropped objects. Dropped tables and any associated objects such as indexes, constraints, nested tables, and the likes are not removed and still occupy space. They continue to count against user space quotas, until specifically purged from the recycle bin or the unlikely situation where they must be purged by the database because of tablespace space constraints.

SHOW PARAMETER RECYCLEBIN;

if recyclebin is set to off, perform the following steps:

ALTER SYSTEM SET RECYCLEBIN=ON SCOPE=SPFILE;

verify that recyclebin parameter is now set to ON

shutdown the database
SHUTDOWN IMMEDIATE

Restart the database
STARTUP

then run
SELECT * FROM RECYCLEBIN;

and see if your table is in there. If it is, use the following quesry to restore it:
FLASHBACK TABLE TO BEFORE DROP;

Then check if the table is back:
SELECT * FROM USER_TABLES WHERE TABLE_NAME=;

**********************************************************

SQL > SELECT * FROM RECYCLEBIN;

SQL > SELECT object_name, original_name FROM dba_recyclebin WHERE owner = 'Schema_name';

SQL > SELECT object_name, original_name, createtime FROM recyclebin;

SQL > desc dba_recyclebin;

SQL > select OWNER,OBJECT_NAME,ORIGINAL_NAME,CREATETIME,DROPTIME from dba_recyclebin WHERE owner = 'Schema_name';

Login with drop table owner schema

SQL > show user;

HR

SQL > FLASHBACK TABLE DROP_TABLE_NAME TO BEFORE DROP;

Clear Recyclebin

To remove all dropped objects from the recyclebin (current user):

PURGE RECYCLEBIN;
To remove all dropped objects from the recyclebin (system wide, available to SYSDBA only or, starting with version 12c, to users having the PURGE DBA_RECYCLEBIN system privilege):

PURGE DBA_RECYCLEBIN;
Tables can also be dropped without sending them to the recyclebin. Example:

DROP TABLE tba PURGE;
Tables inside recycle bin can be purged individually. Example:

PURGE TABLE tba;

Drop a table:

SQL> DROP TABLE tba;
Undrop the table:

SQL> FLASHBACK TABLE tba TO BEFORE DROP;