Oracle 12.1.0.2.0 - Applying Patch 20228093: MATERIALIZED VIEW DEFERRED MODE ORA-00918: COLUMN AMBIGUOUSLY DEFINED

Oracle 12.1.0.2.0 - Applying Patch 20228093: MATERIALIZED VIEW DEFERRED MODE ORA-00918: COLUMN AMBIGUOUSLY DEFINED

- Download Patch 20228093 (p20228093_121020_Linux-x86-64.zip)

- Ensure environment variables are set, latest opatch version is used 12.1.0.1.4 or higher and Oracle home has Database 12.1.0.2.0 installed.

- Unzip the patch

$ unzip p20228093_121020_Linux-x86-64.zip

- Check for conflicts

$ cd 20228093

$ opatch prereq CheckConflictAgainstOHWithDetail -ph ./


Applying Patch 20228093

- Shutdown all services

- Run opatch apply

$ cd 20228093

$ opatch apply

- Verify patch applied

$ opatch lsinventory |grep 20228093

- Start all services

Patch rollback steps

If any issues after applying the patch and rollback is required, follow below steps

- Stop all services

- Run opatch rollback

$ opatch rollback -id 20228093


- Restart all services

Related Posts

Oracle 12.1.0.2.0 - Applying Patch 18201352: ORA-7445 [QERTBSTART()+3105] CRASHES QUERY WITH DISTINCT AND PJE TRANSFORMATION

Oracle 12.1.0.2.0 - Applying Patch 21948354: DATABASE PATCH SET UPDATE 12.1.0.2.160119 (INCLUDES CPUJAN2016)

Oracle 12.1.0.2.0 - Applying Patch 22139226: OJVM PATCH SET UPDATE 12.1.0.2.160119

Oracle 12.1.0.2.0 - Applying Patch 18201352: ORA-7445 [QERTBSTART()+3105] CRASHES QUERY WITH DISTINCT AND PJE TRANSFORMATION

Oracle 12.1.0.2.0 - Applying Patch 18201352: ORA-7445 [QERTBSTART()+3105] CRASHES QUERY WITH DISTINCT AND PJE TRANSFORMATION

- Download Patch 18201352 (p18201352_121020_Linux-x86-64.zip)

- Ensure Oracle home has Oracle Database 12.1.0.2.0 installed

- Set environment variables, ensure latest opatch version available for 12c release is used, 

- Unzip the patch

$ unzip p18201352_121020_Linux-x86-64.zip

- Check for patch conflicts

$ cd 18201352

$ opatch prereq CheckConflictAgainstOHWithDetail -ph ./


Applying Patch 18201352 (Offline Patching)

- Shutdown all services (for offline patching)

- Run opatch apply

$ cd 18201352

$ opatch apply

- Verify patch

$ opatch lsinventory |grep 18201352

- Restart all services

Patch rollback steps (Offline)

If any issues after applying the patch and rollback is required, follow below steps:

- Stop all services

- Run opatch rollback

$ opatch rollback -id 18201352


- Restart all services

Related Posts

Oracle 12.1.0.2.0 - Applying Patch 20228093: MATERIALIZED VIEW DEFERRED MODE ORA-00918: COLUMN AMBIGUOUSLY DEFINED

Oracle 12.1.0.2.0 - Applying Patch 21260431: GETTING ORA-4031 AFTER 12C UPGRADE

Oracle 12.1.0.2.0 - Applying Patch 21260431: GETTING ORA-4031 AFTER 12C UPGRADE

- Download Patch 21260431 (p21260431_121020_Linux-x86-64.zip)

- Ensure that Oracle home is Oracle Database 12c Release 12.1.0.2.0

Applying Patch 21260431

- Set environment variables

- Shutdown all services

- Unzip the patch

$ unzip p21260431_121020_Linux-x86-64.zip

$ cd <PATCH_TOP_DIR>/21260431

- Run opatch apply

$ opatch apply

- Verify patch

$ opatch lsinventory |grep 21260431

- Start all services

Rollback Patch 21260431

If any issues after applying the patch and rollback is required

- Stop the services

- Run opatch rollback

$ opatch rollback -id 21260431


- Restart all services

Related Posts:

Oracle Database 12.1.0.2.5 - Applying Patch 19509982: DISABLE FIX FOR RAISING ORA-1792 BY DEFAULT.

Unable to see data for Oracle Database Monthly Space Usage report for all Databases in Oracle Enterprise Manager 12c

Unable to see data for Oracle Database Monthly Space Usage report for all Databases in Oracle Enterprise Manager 12c

Trying to get a report on Oracle Database Space Usage but databases listed in "Outstanding Tablespace Full Alerts by Databases" were not appearing in "Allocated Space Usage by Database.

Digging deep found out that the Tablespace Allocation metric was disabled for some databases!

So tried below in Enterprise Manager:

Target > Databases > Choose Database with Metrics issue > Menu > Monitoring > All Metrics > Click "Tablespace Allocation"

Click Enable

• For single database

Targets > Databases > Choose Database with Metrics issue > Menu > Monitoring > All Metrics > Click "Tablespace Allocation"

Click Enable

• For Multiple Databases

-- Create Template with this metric enabled and push the template to multiple databases

UI > Enterprise > Monitoring > Monitoring Templates > Click Create and add the metric

Wait at-least 72 hours and check the status again. It should show data now.

Oracle Database 12c R1 New Features - Multiple Indexes on the same column or set of columns

Oracle Database 12c R1 New Features - Multiple Indexes on the same column or set of columns

In DB releases prior to Oracle DB 12c, you could not create multiple indexes either on the same column or set of columns in any form. 

But now in DB 12c, you can have multiple indexes on the same column or set of columns as long as the index type is different. However, only one type of index is usable/visible at a given time. 

In order to test the invisible indexes, you need to set the optimizer_use_use_invisible_indexes=true.

Example:


SQL> CREATE TABLE EMP_RECORDS
  2  (EMP_NO number (5),
  3  EMP_NAME varchar2 (30),
  4  EMP_SAL number (6) INVISIBLE)
  5  /

SQL> alter session set optimizer_use_invisible_indexes = true;

Session altered.

SQL> CREATE INDEX EMP_IND1 ON EMP_RECORDS (EMP_NO, EMP_NAME);

Index created.

SQL> CREATE BITMAP INDEX EMP_IND2 ON EMP_RECORDS (EMP_NO, EMP_NAME) INVISIBLE;

Index created.

Related:

Oracle Database 12c R1 New Features - Invisible columns

Installing Oracle Database 12c R1 (12.1.0.1.0)

Oracle Database 12c R1 New Features - Invisible columns

Oracle Database 12c R1 New Features - Invisible columns

In Oracle Database 12c R1, you can now have an invisible column in a table. When a column is defined as invisible, the column does not appear in generic queries, unless the column is explicitly referred to in the SQL statement or condition, or DESCRIBED in the table definition. You can add or modify a column to be invisible and vice versa.

You must explicitly refer to the invisible column name with the INSERT statement to insert the database into invisible columns. A virtual column or partition column can be defined as invisible too. However, temporary tables, external tables and cluster tables won’t support invisible columns.


Example:

Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing opt
ions

SQL> CREATE TABLE EMP_RECORDS
  2  (EMP_NO number (5),
  3  EMP_NAME varchar2 (30),
  4  EMP_SAL number (6) INVISIBLE)
  5  /

Table created.

SQL> desc EMP_RECORDS;
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------

 EMP_NO                                             NUMBER(5)
 EMP_NAME                                       VARCHAR2(30)


SQL> alter table emp_records
  2  modify (emp_sal visible)
  3  /

Table altered.

SQL> desc emp_records
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------

 EMP_NO                                             NUMBER(5)
 EMP_NAME                                       VARCHAR2(30)
 EMP_SAL                                            NUMBER(6)

Related:

Installing Oracle Database 12c R1 (12.1.0.1.0)

Oracle Database 12c R1 New Features - Multiple Indexes on the same column or set of columns

Installation of Oracle Database 12c R1 (12.1.0.1.0)

Installation of Oracle Database 12c R1 (12.1.0.1.0)

Run setup.exe from installer directory (This is on windows, similarly invoke installer on Linux environment)

Oracle Database 12c Release 1 Installer - Installing Database

Step 1: Configure security updates

Enter My Oracle Support Username / Password to receive security updates via My Oracle Support (optional)

Step 2: Download Software updates

Enter My Oracle Support credentials to download software updates for this installation (optional)

Step 3: Select Installation Option

i)  Create and configure a database (Select this option)
ii) Install database software only
iii)Upgrade an existing database 

Step 4: System Class

i) Desktop class (Choose this option)
ii)Server class

Step 5: Specify Oracle Home User

i) Use Existing Windows User
ii) Create New Windows USer 
iii)Use Windows Built-in account (Option chosen - No need to specify username or password)


Step 6: Typical Install Configuration

Enter Administrative password: 

Step 7: Prerequisite checks

Step 8: Summary

Step 9: Install

At the end of installation, you will see on screen 

The installation of Oracle Database was successful.

Your database configuration files have been installed in C:\app\ while other components selected for installation have been installed in C:\app\product\12.1.0\dbhome_1.  Be cautious not to accidentally delete these configuration files.

SQL> select instance_name, status, version, database_status from v$instance;

INSTANCE_NAME    STATUS       VERSION           DATABASE_STATUS
---------------- ------------ ----------------- -------------------------------------------------------

newdb12c         OPEN         12.1.0.1.0        ACTIVE


Deinstalling Oracle Database 12c:

Go to $DB_HOME/deinstall and execute deinstall script.

For eg:

C:\app\db12c\product\12.1.0\dbhome_1\deinstall>deinstall

Related:

Oracle Database 12c R1 New Features - Invisible columns

Oracle Database 12c R1 New Features - Multiple Indexes on the same column or set of columns