Upgrade PDB 12.1 to 12.2 PDB(Pluggable database)

Source Version - 12.1.0.2.0 
Upgrade Version - 12.2.0.1.0 
Pluggable Database Name - PDB 

Steps:

1.Check the version of the pluggable database

2.Check pdb name using show pdbs command

3.Run pre-upgrade script

[oracle@localhost ~]$ /u01/oracle/product/12.1.0/db_1/jdk/bin/java -jar /u01/oracle/product/12.2.0/db_1/rdbms/admin/preupgrade.jar FILE DIR /u01/upgrade

Preupgrade generated files:

    /u01/upgrade/preupgrade.log

    /u01/upgrade/preupgrade_fixups.sql

    /u01/upgrade/postupgrade_fixups.sql

[oracle@localhost ~]$

4.Login to pdb and run the preupgrade_fixup script

SQL> @/u01/upgrade/preupgrade_fixups_PDB.sql

Executing Oracle PRE-Upgrade Fixup Script

Auto-Generated by:       Oracle Preupgrade Script

                         Version: 12.2.0.1.0 Build: 1

Generated on:            2018-08-29 07:33:31

For Source Database:     CDB1

Source Database Version: 12.1.0.2.0

For Upgrade to Version:  12.2.0.1.0

Executing in container:  PDB

                          Fixup

Check Name                Status  Further DBA Action

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

dictionary_stats          Passed  None

apex_upgrade_msg          Failed  Manual fixup recommended.

PL/SQL procedure successfully completed.

5.run preupgrade_fixups_PDB.sql

SQL> @/u01/upgrade/preupgrade_fixups_PDB.sql

Executing Oracle PRE-Upgrade Fixup Script

Auto-Generated by:       Oracle Preupgrade Script

                         Version: 12.2.0.1.0 Build: 1

Generated on:            2018-08-29 07:33:31

 

For Source Database:     CDB1

Source Database Version: 12.1.0.2.0

For Upgrade to Version:  12.2.0.1.0

 

Executing in container:  PDB

                           Fixup

Check Name                Status  Further DBA Action

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

dictionary_stats          Passed  None

apex_upgrade_msg          Failed  Manual fixup recommended.

Note: As I am upgrading pdb using migrating pdb to empty container I need to remove apex from Source pdb.

6.Remove apex 

cd $ORACLE_HOME/apex

SQL> spool apexremove

SQL> @apxremov_con.sql

7.Check after removing apex is there any invalid component exists

SQL> select COMP_ID, STATUS from DBA_REGISTRY where COMP_ID='APEX';

Compile them using utlrp.sql

SQL> @?/rdbms/admin/utlrp.sql

8.Close pdb

SQL> alter pluggable database pdb close immediate;

9.Unplug pdb

SQL> alter pluggable database pdb unplug into '/u02/pdb_121.xml';

10.Connect to an empty container of 12.2 and create pdb using xml file

SQL> CREATE PLUGGABLE DATABASE PDB USING '/u02/pdb_121.xml';

11.Check the pluggable database

SQL> show pdbs

    CON_ID CON_NAME OPEN MODE  RESTRICTED

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

2 PDB$SEED   READ ONLY  NO

3 PDB   MOUNTED

12.Open pdb in upgrade mode

SQL>alter pluggable database pdb open upgrade;

13.Run catupgrd.sql

[oracle@localhost admin]$ $ORACLE_HOME/perl/bin/perl catctl.pl -c 'PDB' catupgrd.sql

14.Monitor catupgrd.sql

15.Open pdb in normal mode and Check pdb state 

SQL> alter pluggable database pdb open;

SQL> show pdbs

    CON_ID CON_NAME   OPEN MODE  RESTRICTED

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

2 PDB$SEED   READ ONLY  NO

3 PDB   READ WRITE NO

Oracle Dataguard:

1. What are different protection modes in dataguard?

There are three modes.

a. MAXIMUM PROTECTION

This mode provides maximum protection. It guarantees zero data loss. In this mode the redo/transaction data must be written to both primary redo log and standby redo log. For any reason(mostly N/W issue) if it is unable to write to standby, Then primary will get shutdown.

b. MAXIMUM AVAILABILITY:

It provides the highest level of data protection that is possible without affecting the availability of the primary database.Transactions are not allowed to commit until all redo data are written to the online redo logs and propagated to at least one synchronized secondary database. If for any reason, the primary database cannot propagate its redo stream to one secondary database, the primary will NOT shutdown and operates as it it were in maximum performance mode until issues are fixed.

c. MAXIMUM PERFORMANCE:(default one)

This is the default protection mode. With this protection mode, a transaction is committed as soon as the redo data needed to recover the transaction is written to the local (online) redo log.

2. What are different types of dataguard and their difference?

There are two types of dataguard setups. PHYSICAL and LOGICAL.

PHYSICAL STANDBY:

A physical standby database is an exact, block-for-block copy of a primary database. A physical standby is maintained as an exact copy through a process called REDO APPLY, in which redo data received from a primary database is continuously applied to a physical standby database using the database recovery mechanisms. So it will be always in sync with primary.

This Standby database can be opened in read only mode( knows as ACTIVE DATA GUARD), for reporting purpose. Most of the corporations use physical standby for dataguard configuration.

LOGICAL STANDBY:

The logical standby database is kept synchronized with the primary database through SQL APPLY, which transforms the data in the redo received  from the primary database into SQL statements and then executes the SQL statements on the standby database. So it contains same logical information as that of production , but physical structure of data can be different.

3. What is the process to apply a psu patch in dataguard setup.

• Make sure lag between primary and standby is zero.

• Cancel the recovery (MRP) on standby.

• Shutdown standby db and listener.

• Apply patch to binary using opatch apply command.

• Once patch applied to binary , startup the listener and standby in mount stage or OPEN(if active dataguard).

• Now shutdown primary db and listener.

• Apply patch to binary using opatch apply command.

• Once patch applied to binary , startup the listener and prim db in mount OPEN(if active dataguard).

• Start the MRP recovery process on standby.

4. What is active dataguard. Does it needs additional licensing?

Active dataguard means, the standby database is open with read only mode, when redo logs are getting applied in real time.
Below are the benefit of using active dataguard.
Reporting queries can be offloaded to standby database.
Physical block corruptions are repaired automatically either at primary or physical standby database.
RMAN backups can be initiated from standby , instead  of primary which will reduce cpu load from primary.

NOTE – To use active dataguard, you need additional license from oracle.

ORA - 01017:invalid username/password; logon denied , ORA-28040, ORA-03134

Issue:
When application schema was trying to connect the database from SQL Developer, they are facing intermittent issues with the below. out every 3 attempts only one connection is establishing successfully.

DB version: 12.1.0.2
2 node RAC
Status : Failure -Test failed ORA- 01017:invalid username/password; logon denied

This issue may also arrives when you face below errors.If the client release does not meet or exceed the value defined by this parameter, then authentication fails with one of these two errors:

ORA-28040: No matching authentication protocol error

ORA-03134: Connections to this server version are no longer supported

But in this case we faced below issue.

ORA- 01017:invalid username/password; logon denied

Solution:

In general issue will be with your password, so make sure to ask application team to use the valid credentials.

But here in this situation,

someone has modified the $GI_HOME/network/admin/sqlnet.ora file only node 1, 

which is has having sqlnet.allowed_logon_version_server=8 value but later it modified to sqlnet.allowed_logon_version_server=12 and  due to that a your application proctol is not able to authenticate the sqlnet.allowed_logon_version_server=12 version for your client connections.

So just revert back the changes sqlnet.allowed_logon_version_server of the working node in cluster (or) to the allowed authenicate protocal version.

Note: You can check in test Database and observe the changes.

For UNIX systems,

the sqlnet.ora can be located any of these locations $ORACLE_HOME/network/admin (or) $GI_HOME/network/admin.

By default,  $ORACLE_HOME/network/admin 

The Oracle documentation notes that a setting of 8 permits most password versions, and allows any combination of the dba_users.password_versions values 10g, and 11g.  

sqlnet.allowed_logon_version_server=8:  A setting of 8 permits most password versions, and allows any combination of the dba_users.password_versions values 10g, 11g, and 12c.  

sqlnet.allowed_logon_version_server=12a: A sqlnet.allowed_logon_version_server setting of 12a permits only the 12c password version.

A value than 12 means the server is less compatible in terms of the protocol that clients must understand in order to authenticate. the server is also more restrictive in terms of the password version that must exist to authenticate any specific account. the ability for a client to authenticate depends on the dba_users.password_versions value on the server for that account.

The default is sqlnet.allowed_logon_version_server=11, but the following values are supported for sqlnet.allowed_logon_version_server, depending on the version of the authentication protocol.

sqlnet.allowed_logon_version_server=12a:  For Oracle Database 12c Release 1 (12.1) release 12.1.0.2 or later.

sqlnet.allowed_logon_version_server=12:  For the critical patch updates CPUOct2012 and later Oracle Database 11g authentication protocols (recommended).

sqlnet.allowed_logon_version_server=11:  For Oracle Database 11g authentication protocols (default).

sqlnet.allowed_logon_version_server=10:  For Oracle Database 10g authentication protocols.

sqlnet.allowed_logon_version_server=8:  For Oracle8i authentication protocol

Hope this is clear..!!!

Generate Automatic Workload Repository (AWR) report:

The Automatic Workload Repository (AWR) collects, processes, and maintains performance statistics for problem detection and self-tuning purposes. This data is both in memory and stored in the database. The gathered data can be displayed in both reports and views.

Features of AWR:

• Some of the system and session statistics collected from V$SYSSTAT and V$SESSTAT views
• Object statistics that determine both access and usage statistics of DB segments.
• ASH statistics.
• Time model statistics based on time usage for activities, displayed in the V$SYS_TIME_MODEL and V$SESS_TIME_MODEL views
• SQL statements that are producing the highest load on the system, based on elapsed time and CPU time.

Types of AWR reports:

@?/rdbms/admin/awrrpt.sql --> Generates an HTML or text report that displays statistics for a range of snapshot Ids.

@?/rdbms/admin/awrrpti.sql -->  Generates an HTML or text report that displays statistics for a range of snapshot
Ids on a specified database and instance.

@?/rdbms/admin/awrsqrpt.sql -->  Generates an HTML or text report that displays statistics of a particular SQL statement for a range of snapshot Ids.
Run this report to inspect or debug the performance of a SQL statement.

@?/rdbms/admin/awrsqrpi.sql -->  Generates an HTML or text report that displays statistics of a particular SQL statement for a range of snapshot Ids on a specified database and instance.
Run this report to inspect or debug the performance of a SQL statement on a specific database and instance

@?/rdbms/admin/awrddrpt.sql SQL script generates an HTML or text report that compares detailed performance attributes and
configuration settings between two selected time periods.

@?/rdbms/admin/awrddrpi.sql SQL script generates an HTML or text report that compares
detailed performance attributes and configuration settings between two selected
time periods on a specific database and instance.

In order to run this reports you should have DBA role granted and you need to choose the snap id's interval based on your issue time.

awrrpt.sql is the most key common report and that is very helpful when you start investigating your journey on performance issues.

Access Control List (ACL)

Access control lists are manipulated using the DBMS_NETWORK_ACL_ADMIN package. The CREATE_ACL procedure uses the following parameters to create a new ACL:

In  11g and beyond, Oracle has introduced the ability to restrict connections to specific hosts (or IP addresses).

The access control lists (ACL) are used to restrict the hosts that are allowed to connect to the Oracle database.

ACL's are created using the dbms_network_acl_admin and dbms_network_acl_utility packages.  Either package can be used to create and manage ACLs.

• acl - The name of the access control list XML file, generated relative to the "/sys/acls" directory in the XML DB Repository.
• description - A description of the ACL.
• principal - The first user account or role being granted or denied permissions. The text is case sensitive.
• is_grant - TRUE to grant, FALSE to deny the privilege.
• privilege - Use 'connect' for UTL_TCP, UTL_SMTP, UTL_MAIL and UTL_HTTP access. Use 'resolve' for UTL_INADDR name/IP resolution. The text is case sensitive.
• start_date - Default value NULL. When specified, the ACL will only be active on or after the specified date.
• end_date - An optional end date for the ACL.

Note:  It is not possible that one server can have multiple services configured. If you try to do so then first configured service will be overwritten.

Creation of ACL Example:

BEGIN

  DBMS_NETWORK_ACL_ADMIN.CREATE_ACL(acl         => 'www.xml',

                                    description => 'WWW ACL',

                                    principal   => 'SCOTT',

                                    is_grant    => true,

                                    privilege   => 'connect');

  DBMS_NETWORK_ACL_ADMIN.ADD_PRIVILEGE(acl       => 'www.xml',

                                       principal => 'SCOTT',

                                       is_grant  => true,

                                       privilege => 'resolve');

  DBMS_NETWORK_ACL_ADMIN.ASSIGN_ACL(acl  => 'www.xml',

                                    host => 'www.us.oracle.com');

END;

/

COMMIT;

Please find below sample scenario.

Error:

we have faced below error on DB: 

ORA-24247: network access denied by access control list (ACL)

ORA-06512: at "SYS.UTL_INADDR", line 19

ORA-06512: at "SYS.UTL_INADDR", line 40

ORA-06512: at line 1

ORA-06512: at "XMLUSER.SEND_FLOWMON_MSG_ORDFAIL", line 33

ORA-06512: at "XMLUSER.XML_ORDER_PROCESSOR", line 816

ORA-06512: at "XMLUSER.XML_ORDER_PROCESSOR", line 793

The DBA_NETWORK_ACLS view displays information about network and ACL assignments:

SELECT host, lower_port, upper_port, acl

FROM   dba_network_acls;

HOST                           LOWER_PORT UPPER_PORT ACL

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

101.29.77.147                                         /sys/acls/utl_http.xml

The DBA_NETWORK_ACL_PRIVILEGES view displays information about privileges associated with the ACL:

COLUMN acl FORMAT A30

COLUMN principal FORMAT A30

col ACL for a40

col PRINCIPAL for a30

col PRIVILEGE for a30

SELECT acl,

       principal,

       privilege,

       is_grant,

       TO_CHAR(start_date, 'DD-MON-YYYY') AS start_date,

       TO_CHAR(end_date, 'DD-MON-YYYY') AS end_date

FROM   dba_network_acl_privileges;

ACL                            PRINCIPAL                      PRIVILE IS_GR START_DATE  END_DATE

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

/sys/acls/utl_http.xml         TESTUSER                        connect true

The USER_NETWORK_ACL_PRIVILEGES view displays the current users network ACL settings:

COLUMN host FORMAT A30

SELECT host, lower_port, upper_port, privilege, status

FROM   user_network_acl_privileges;

HOST                           LOWER_PORT UPPER_PORT PRIVILE STATUS

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

101.29.77.147                                         connect GRANTED

101.29.77.147                                         resolve GRANTED

Steps To Fix the above issue:

1. First check the privileges on the user whether connect privilege is granted or not using dba_network_acl_privileges view, in my case privilege is not there.
2. If not granted then follow below step.

begin

DBMS_NETWORK_ACL_ADMIN.ADD_PRIVILEGE(acl => '/sys/acls/utl_http.xml',

principal => 'TESTUSER',

is_grant => true,

privilege => 'connect');

end;

/

commit;

If you need to resolve a host name from a host IP you will need the resolve grant as well.

begin

DBMS_NETWORK_ACL_ADMIN.ADD_PRIVILEGE(acl => 'http_permissions.xml',

principal => 'TESTUSER',

is_grant => true,

privilege => 'resolve');

end;

/

commit;

    3.The final step is to assign the ACL to a specific target.

BEGIN

dbms_network_acl_admin.assign_acl (

acl => 'http_permissions.xml',

host => 'NETWORK ADDRESS', /*can be computer name or IP , wildcards are accepted as well for example - '*.us.oracle.com'*/

lower_port => 80,

upper_port => 80

);

END;

After above steps performed my issues has been resolved.

Hope this helps !!! Cheers !!!

Thank you 😊

Oracle 12c, 12.1.0.2.0 Basic commands & PDB save state feature

How to check the current container and set container database:

SQL> show con_name

CON_NAME

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

CDB$ROOT

SQL> show pdbs

    CON_ID CON_NAME  OPEN MODE  RESTRICTED

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

         2 PDB$SEED  READ ONLY  NO

         3 TEST1     READ WRITE NO

SQL> alter session set container='&CON_NAME';

SQL> show pdbs

How to create pluggable(PDB) database:

CREATE PLUGGABLE DATABASE  <PDB_NAME> ADMIN USER <Username> IDENTIFIED BY <Password>;

How to drop PDB database:

DROP PLUGGABLE DATABASE <PDB_NAME> INCLUDING DATAFILES;

How to open pluggable instance wise:

ALTER PLUGGABLE DATABASE <PDB_NAME>  OPEN INSTANCES=('instance_name’); 

ALTER PLUGGABLE DATABASE <PDB_NAME>  CLOSE INSTANCES=('instance_name’); 

How to open PDB in all instances:

       ALTER PLUGGABLE DATABASE <PDB_NAME> close instances =all; 

To check status PDB status: 

SQL>  SELECT INST_ID,NAME,OPEN_MODE,RESTRICTED FROM GV$PDBS;  

    INST_ID NAME          OPEN_MODE  RESTRICTED

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

         2 PDB$SEED      READ ONLY  NO

         2 TEST1         READ WRITE NO

         1 PDB$SEED      READ ONLY  NO

         1 TEST1         READ WRITE NO

        

PDB save state feature: 

In earlier releases of 12c (before 12.1.0.2.0) whenever container database was restarted all the pluggable databases associated with container database remains in MOUNT state.

From 12c release 12.1.0.2.0 this functionality can be achieved with new PDB save state feature.

       

Note: By default all the PDB's would be in MOUNTED state.

Example scenario:  

In this case node 2 has rebooted and observed on second instance went to mounted state and also pluggable database was not register in DBA_PDB_SAVED_STATES View, so after staring PDB on 2nd instance, I have registered the second instance into save state.

set pages 1200 lines 120

col CON_NAME for a7

col STATE for a7

col INSTANCE_NAME for a11

col RESTRICTED for a4

select CON_ID,CON_NAME,INSTANCE_NAME,STATE,RESTRICTED from DBA_PDB_SAVED_STATES;

    CON_ID CON_NAM INSTANCE_NA STATE   REST

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

         3 TEST1   testa1     OPEN    NO

TEST1 Pluggable database was not present in DBA_PDB_SAVED_STATES view , so have added using below commands.

SQL> SELECT INST_ID,NAME,OPEN_MODE,RESTRICTED FROM GV$PDBS;

   INST_ID NAME                           OPEN_MODE  RES

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

         1 PDB$SEED                       READ ONLY  NO

         1 TEST1                          READ WRITE NO

         2 PDB$SEED                       READ ONLY  NO

         2 TEST1                          MOUNTED          >>>Issue one >>>

SQL> ALTER PLUGGABLE DATABASE TEST1 OPEN INSTANCES=('test1a2');

SQL> alter pluggable database TEST1 save state;

Pluggable database altered.

SQL> SELECT INST_ID,NAME,OPEN_MODE,RESTRICTED FROM GV$PDBS;

  INST_ID NAME                           OPEN_MODE  RES

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

         1 PDB$SEED                       READ ONLY  NO

         1 TEST1                          READ WRITE NO

         2 PDB$SEED                       READ ONLY  NO

         2 TEST1                          READ WRITE NO

SQL> select CON_ID,CON_NAME,INSTANCE_NAME,STATE,RESTRICTED from DBA_PDB_SAVED_STATES;

    CON_ID CON_NAM INSTANCE_NA STATE   REST

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

         3 TEST1   test1a1     OPEN    NO

         

!! Hope this help you !! Cheers😊

Pre-requisites for expdp and impdp:

• Check the database status details using below query.

SQL>COL DB_UNIQUE_NAME FOR A15

SQL>COL PLATFORM_NAME FOR A20 

SQL>SELECT DBID,NAME,DB_UNIQUE_NAME,DATABASE_ROLE,OPEN_MODE,PROTECTION_MODE,

PLATFORM_NAME,FLASHBACK_ON 

FROM gV$DATABASE;

• Based on your requirement you can check the query for entire database or schema size using below queries. 

SQL> select sum(bytes/1024/1024/1024) from dba_data_files;

(OR)

SQL>

select owner,sum(bytes)/1024/1024/1024 schema_size_gb

from dba_segments group by owner;

• Check the database version using below query.

SQL> select banner from v$version;

• Check character set for the current database using below query.

SQL>select * from v$NLS_PARAMETERS;

• Check invalid object count on database prior export using below query.

SQL>select count(*) from dba_objects where status='INVALID';

• Check the space requirement where you're going to take the export at OS level or disk group, wherever depends on your requirement.

• Useful commands if you are checking at OS level: 
• df -kh   in Linux/Sun Solaris
• bdf   in HP- UX

• Directory creation at database and OS level using below command.

SQL>create or replace directory <directory_name> as <directory_path>;

SQL>exit;

$mkdir <direcotory_name>

Note: All above steps are just to check the feasibility before export, i will update how to take export and import the database/schema in my next post.