Next
Oracle Exadata X5 Administration (1Z0-070) - 15-Jul-2018 09:09 - Skant Gupta

Hi Readers

Finally, I’ve successfully passed 1Z0-070, Oracle Exadata X5 Administration. It was almost more than 3 months that I’ve been preparing for this.

The Exam contains 90 questions related to Exadata X5 and X6. To earn this certification you need to get 60% marks.

This was the result of the journey: Oracle Certified Expert, Oracle Exadata X5 Administrator.

In this post you will find:

  • A path to achieve it
  • Study Materials
  • Exam Detail & Score

How to Achieve this Certification?

Before appearing to this exam you must have the vast knowledge of Exadata & it’s features and components.

List of exam topics

  • Exadata Database Machine Overview
  • Exadata Database Machine Architecture
  • Key Capabilities of Exadata Database Machine
  • Exadata Database Machine Initial Configuration
  • Configure Exadata Storage Server
  • I/O Resource Management
  • Recommendations for Optimizing Database Performance
  • Using Smart Scan
  • Consolidation Options and Recommendations
  • Migrating Databases to Exadata Database Machine
  • Bulk Data Loading
  • Exadata Database Machine Platform Monitoring
  • Configuring Enterprise Manager Cloud Control 12c  to Monitor Exadata Database Machine
  • Monitoring Exadata Storage Servers
  • Monitoring Exadata Database Machine Database Servers
  • Monitoring the InfiniBand Network
  • Monitoring other Exadata Database Machine Components
  • Monitoring Tools
  • Backup and Recovery for Exadata Database Machines
  • Database Machine Maintenance tasks
  • Patching Exadata Database Machine
  • Database Machine Automated Support Ecosystem

For more detail, you can follow the below link.

https://education.oracle.com/pls/web_prod-plq-dad/db_pages.getpage?page_id=5001&get_params=p_exam_id:1Z0-070

How to prepare for the exam?

There is only one way to study for this exam

  1. Exadata Database Machine: 12c Administration Workshop Ed 2

How to register for the exam?

If you want to enroll exam, please click on below link.

Exam Number: 1Z0-070

Exam Title: Oracle Infrastructure as a Service Cloud 2017 Implementation Essentials

https://education.oracle.com/pls/web_prod-plq-dad/db_pages.getpage?page_id=654&get_params=p_id:474

Below are the Exam Details related to scoring.

Special Thanks

Joel Perez is an Oracle OCM and ACED who gave an opportunity to write him and motivating me to learn new things.  I’m very thankful to him as this was the kick off for my studies.

Thank you for giving your valuable time to read the above information.

If you want to be updated with all our articles send us the Invitation or Follow us:

Skant Gupta’s LinkedIn: www.linkedin.com/in/skantali/

Joel Perez’s LinkedIn: Joel Perez’s Profile

LinkedIn Group: Oracle Cloud DBAAS

Facebook Page: OracleHelp

The post Oracle Exadata X5 Administration (1Z0-070) appeared first on ORACLE-HELP.

How to connect to ASM Instance Remotely - 15-Jul-2018 09:06 - Skant Gupta

Today we have looked over ASM Instance Remotely. We can see that the normal instance of RDBMS is different from ASM. Connect to ASM Instance Remotely Being DBA we are aware that By Default Oracle ASM don’t allow remote sysdba connection since ASM instance is in NOMOUNT state. You Might get ORA-12528: TNS:listener: all appropriate instances are blocking new connections error when you are trying to connect ASM instance remotely. To bypass this error you need to add UR=A in the tns files like the example below and connect as sysdba:

ASM_Remote =

(DESCRIPTION =

(ADDRESS = (PROTOCOL = TCP)(HOST = xxxxx)(PORT = 1521))

(CONNECT_DATA =

(SERVER = DEDICATED)

(SERVICE_NAME = +ASM)

(UR =A)

)

)

Today’s thought…..

The power of imagination makes us infinite. —John Muir

Stay tuned for More articles on Oracle DataGuard

Thank you for giving your valuable time to read the above information.

If you want to be updated with all our articles send us the Invitation or Follow us:

Telegram Channel : https://t.me/helporacle

Skant Gupta’s LinkedIn: www.linkedin.com/in/skantali/

Joel Perez’s LinkedIn: Joel Perez’s Profile

LinkedIn Group: Oracle Cloud DBAAS

Facebook Page: OracleHelp

 

 

The post How to connect to ASM Instance Remotely appeared first on ORACLE-HELP.

In the previous Article, we have seen understanding and managing user connectivity in Data Guard Environment.

Understanding and managing client connectivity

 

In this Article, we will see Creating After Startup Trigger which will manage service for different database roles.

Step 1: Create services in a Primary database with DBMS_SERVICE package.

Note: We need to create service and trigger in a primary database only that will be propagated to standby server with redo apply or SQL apply.

SQL> EXEC DBMS_SERVICE.CREATE_SERVICE(SERVICE_NAME=>'DB_PRIM',NETWORK_NAME=>'DB_PRIM');
EXEC DBMS_SERVICE.CREATE_SERVICE(SERVICE_NAME=>'DB_ADG',NETWORK_NAME=>'DB_ADG');
EXEC DBMS_SERVICE.CREATE_SERVICE(SERVICE_NAME=>'SNAP_DG',NETWORK_NAME=>'SNAP_DG');
EXEC DBMS_SERVICE.CREATE_SERVICE(SERVICE_NAME=>'LOGICAL_DG',NETWORK_NAME=>'LOGICAL_DG');
PL/SQL procedure successfully completed.

SQL> 
PL/SQL procedure successfully completed.

SQL> 
PL/SQL procedure successfully completed.

SQL> 

PL/SQL procedure successfully completed.

SQL>

Step 2: Create After startup trigger

CREATE OR REPLACE TRIGGER SER_AC_ROLE AFTER STARTUP ON DATABASE
DECLARE

DBROLE VARCHAR(30);
OPEN_MODE VARCHAR(30);

BEGIN

SELECT DATABASE_ROLE INTO DBROLE FROM V$DATABASE;
SELECT OPEN_MODE INTO OPEN_MODE FROM V$DATABASE;
IF DBROLE = 'PRIMARY' THEN

DBMS_SERVICE.START_SERVICE ('DB_PRIM');

ELSIF DBROLE = 'PHYSICAL STANDBY' THEN
IF OPEN_MODE LIKE 'READ ONLY%' THEN
DBMS_SERVICE.START_SERVICE ('DB_ADG');
END IF;

ELSIF DBROLE = 'LOGICAL STANDBY' THEN

DBMS_SERVICE.START_SERVICE ('LOGICAL_DG');

ELSIF DBROLE = 'SNAPSHOT STANDBY' THEN

DBMS_SERVICE.START_SERVICE ('SNAP_DG');

END IF;

END;
/

Step 3: Check trigger is propagated to standby or not

SQL> SELECT TRIGGER_NAME,OWNER,TRIGGER_TYPE FROM DBA_TRIGGERS WHERE TRIGGER_NAME='SER_AC_ROLE';

TRIGGER_NAME		       OWNER			      TRIGGER_TYPE
------------------------------ ------------------------------ ----------------
SER_AC_ROLE		       SYS			      AFTER EVENT

To check trigger works correctly I am restarting my physical standby with Active Data Guard enabled.

Check service before restart:

SQL> show parameter service;

NAME				     TYPE	 VALUE
------------------------------------ ----------- ------------------------------
service_names			     string	 STD_TESTDB
SQL>

Restart the standby database :

SQL> STARTUP FORCE
ORACLE instance started.

Total System Global Area  663908352 bytes
Fixed Size		    2256192 bytes
Variable Size		  578814656 bytes
Database Buffers	   79691776 bytes
Redo Buffers		    3145728 bytes
Database mounted.
Database opened.
SQL>

See service name

SQL> SHOW PARAMETER SERVICE     

NAME				     TYPE	 VALUE
------------------------------------ ----------- ------------------------------
service_names			     string	 DB_ADG
SQL>

We can see here DB_ADG service is enabled. So whenever a client needs to access this service it will always up with correct role regardless of a database server.

Stay tuned for More articles on Oracle DataGuard

Thank you for giving your valuable time to read the above information.

If you want to be updated with all our articles send us the Invitation or Follow us:

Telegram Channel : https://t.me/helporacle

Skant Gupta’s LinkedIn: www.linkedin.com/in/skantali/

Joel Perez’s LinkedIn: Joel Perez’s Profile

LinkedIn Group: Oracle Cloud DBAAS

Facebook Page: OracleHelp

The post Managing Client Connectivity using DBMS_SERVICE package and After Startup Trigger appeared first on ORACLE-HELP.

Understanding and managing client connectivity - 15-Jul-2018 08:57 - Skant Gupta

While managing Data Guard Standby databases in our environment one of the key responsibility of DBA is End-user connectivity to a proper database or we can say clients connect to the correct database even in case of failover.

To know Oracle DataGuard

Oracle Dataguard Architecture

 

Your client connectivity should be managed in such a way that if failover or switchover happens with your databases your client connect to the correct database.

Database role we can manage using Client Connectivity are the following :

  1. Primary database
  2. Physical Standby [Read only with apply]
  3. Logical standby
  4. Snapshot Standby

The client should be connected to the correct database. If you have offloaded your reporting task to Physical Standby [Active Dataguard] or testing is being performed on Snapshot Standby or if the user wants to connect to your logical standby database, Clients must be connected to their desired database.

If a client has sent the request to the wrong host it results in connection to wrong database or error may occur.

We can Manage Client connectivity with  :

  1. Local Naming
  2. Using Services

1.Understanding Client Connectivity with Local Naming :

Configure a PRIMARY role service on the Primary and Standby and modify the Client connect descriptor to include both Primary and the Standby.

Example :

PRIM = (DESCRIPTION =
          (ADDRESS=(PROTOCOL = TCP)
              (HOST = DBPRM)(PORT = 1521))
          (ADDRESS=(PROTOCOL = TCP)
              (HOST = DBSTD)(PORT = 1521))
          (CONNECT_DATA = (SERVICE_NAME = PRIM)))

This TNS entry will direct connection to the server which supports PRIM service.

Preventing Clients from Connecting to the Wrong Database :

We can create database services to prevent a client from connecting to the wrong database in data guard environment. When we use database services it works as the abstract layer between your client and database instances.

As with this, your clients will be connected to Database Services instead of directly connected to the specific database instance.

Services you have created will be registered to Listeners. A listener will determine which service supports which database at the particular time. It can check and can redirect service to particular instance otherwise particular error will be returned. But your client will not be connected to the wrong instance.

You may get the error like

ORA-01033: ORACLE initialization or shutdown in progress
You can prevent clients from connect

Managing Service :

When Oracle Restart is not enabled, we can manage database service using DBMS_SERVICE package. This package is used for the creation, deletion, starting, and stopping of services for a single database instance.

If you use Oracle Real Application Cluster or single instance database which includes Oracle Restart, you can manage service using SRVCTL, DBMS_SERVICE package is deprecated in 11.2.

Attributes for DBMS_SERVICE package :

  1. Service Name: For administration of service.
  2. Network Name: For external clients
  3. TAF Attributes: For TAF enabled clients

In the next Article, we will see creating After Startup trigger which manages Database Service for different roles of database

Stay tuned for More articles on Oracle DataGuard

Thank you for giving your valuable time to read the above information.

If you want to be updated with all our articles send us the Invitation or Follow us:

Telegram Channel: https://t.me/helporacle

Skant Gupta’s LinkedIn: www.linkedin.com/in/skantali/

Joel Perez’s LinkedIn: Joel Perez’s Profile

LinkedIn Group: Oracle Cloud DBAAS

Facebook Page: OracleHelp

 

The post Understanding and managing client connectivity appeared first on ORACLE-HELP.

As we are working on the series of listener errors. Now we are going to work on “ORA-12514“.

Let’a have look on the steps which we use to resolve this issue.

Cause: “The listener received a request to establish a connection to a database or other service. The connect descriptor received by the listener specified a SID for an instance (usually a database instance) that either has not yet dynamically registered with the listener or has not been statically configured for the listener. This may be a temporary condition such as after the listener has started, but before the database instance has registered with the listener.”

Make sure you have a valid service name in your tnsnames.ora file. You can use the following command to find out valid service name.

select value from v$parameter where name='service_names';

Update your tnsnames.ora file using valid service name:

TEST =

(DESCRIPTION =

(ADDRESS_LIST =

(ADDRESS = (PROTOCOL = TCP)(HOST = <validhost>)(PORT = <validport>))

)

(CONNECT_DATA =

(SERVER = DEDICATED)

(SERVICE_NAME = <service_name>)

)

)

Check TNS:

# tnsping TEST

or

sqlplus user@TEST

Note: If you are using client and tnsping is not working then check your client have installed that features or not. Usually, you will get the tnsping.exe file under bin folder if this features installed.

Today’s thought

Work hard, be kind, and amazing things will happen.— Conan O’Brien

Thank you for giving your valuable time to read the above information.

If you want to be updated with all our articles send us the Invitation or Follow us:

Telegram Channel: https://t.me/helporacle

Skant Gupta’s LinkedIn: www.linkedin.com/in/skantali/

Joel Perez’s LinkedIn: Joel Perez’s Profile

LinkedIn Group: Oracle Cloud DBAAS

Facebook Page: OracleHelp

 

The post ORA-12514: TNS:listener does not currently know of service requested in connect descriptor appeared first on ORACLE-HELP.

Recovering Data File in Primary Database from the standby database.

You have two approaches for data file restoration when your data file gets corrupted or deleted in data guard environment.

  1. Restore from RMAN backup
  2. Recover it from Standby Database.

In our routine, we use the first approach where we simply restore it using RMAN restore command and recover it through incremental backup or archive logs.

In the second approach, we will restore data file with help of Physical Standby database.

In this article, we will see steps for recovering data file in Primary Database using Standby database approach.

Approach 1: Restore From RMAN backup

We can simply restore it using RMAN restore and recover command.

RESTORE DATAFILE;

RECOVER DATAFILE ;

Approach 2: Recover it from Standby Database : 

Let’s simulate this case :

Step 1: Create the tablespace

SQL> create tablespace test datafile '/u01/oracle/oradata/dbupgrade/test01.dbf' size 5m;

Tablespace created.

Step 2 : Create the table in test tablespace

SQL> create table a (no number) tablespace test;

Table created.

add some records to a table.

Step 3: Manually delete the physical file of this tablespace :

[oracle@localhost dbupgrade]$ rm -rfv test01.dbf 
removed `test01.dbf'

Step 4: Connect with RMAN using the target as Standby and Auxiliary as Primary :

[oracle@localhost dbupgrade]$ rman target sys/oracle@std_dbupgrade auxiliary sys/oracle@dbupgrade

Recovery Manager: Release 12.2.0.1.0 - Production on Thu Jul 12 09:36:43 2018

Copyright (c) 1982, 2017, Oracle and/or its affiliates.  All rights reserved.

connected to target database: DBUPGRAD (DBID=1987568604)
connected to auxiliary database: DBUPGRAD (DBID=1987568604)

Step 5: Backup that datafile using the following command.

RMAN> BACKUP AS COPY DATAFILE 5 AUXILIARY FORMAT '/u01/test01.dbf';

Starting backup at 12-JUL-18
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=66 device type=DISK
channel ORA_DISK_1: starting datafile copy
input datafile file number=00005 name=/u01/oracle/oradata/dbupgrade/test01.dbf
output file name=/u01/test01.dbf tag=TAG20180712T093756
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:01
Finished backup at 12-JUL-18

Step 6: Now connect primary as target and catalog as a catalog database.

Note: You need catalog database for this approach.

[oracle@localhost admin]$ rman catalog rcatown/rcatown@std_upgdb

Recovery Manager: Release 12.2.0.1.0 - Production on Thu Jul 12 10:35:21 2018

Copyright (c) 1982, 2017, Oracle and/or its affiliates.  All rights reserved.

connected to recovery catalog database

RMAN> connect target sys/oracle@std_dbupgrade;

connected to target database: DBUPGRAD (DBID=1987568604, not open)

Step 7: Catalog datafile backup

RMAN> catalog datafilecopy '/u01/test01.dbf';

Step 8: Run the following script to set newname for datafile and then switch it to the new name and then recover datafile.

RMAN> run{
set newname for datafile 5 to '/u01/test01.dbf';
switch datafile 5;
}2> 3> 4> 

executing command: SET NEWNAME

datafile 5 switched to datafile copy
input datafile copy RECID=11 STAMP=981282541 file name=/u01/test01.dbf
starting full resync of recovery catalog
full resync complete

RMAN> recover datafile 5;

Starting recover at 12-JUL-18
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=44 device type=DISK

starting media recovery
media recovery complete, elapsed time: 00:00:02

Finished recover at 12-JUL-18

RMAN>

Stay tuned for More articles on Oracle DataGuard

Thank you for giving your valuable time to read the above information.

If you want to be updated with all our articles send us the Invitation or Follow us:

Telegram Channel: https://t.me/helporacle

Skant Gupta’s LinkedIn: www.linkedin.com/in/skantali/

Joel Perez’s LinkedIn: Joel Perez’s Profile

LinkedIn Group: Oracle Cloud DBAAS

Facebook Page: OracleHelp

 

The post Recover Data File in Primary Database in Data Guard Environment appeared first on ORACLE-HELP.

JDev/ADF sample - ADF Postback Payload Size Optimization - 15-Jul-2018 02:49 - Andrejus Baranovskis
  • ADF Postback Payload Size Optimization. Recently I came across property called oracle.adf.view.rich.POSTBACK_PAYLOAD_TYPE. This property helps to optimize postback payload size. It is described in ADF Faces configuration section - A.2.3.16 Postback Payload Size Optimization. ADF partial request is executing HTTP post with values from all fields included. When postback property is set to dirty, it will include into HTTP post only changed values. As result - server will get only changed attributes, potentially this can reduce server time processing and make HTTP request size smaller. This especially can be important for large forms, with many fields.
    Download - GitHub
Highly Available VIP (HAVIP) - 14-Jul-2018 18:49 - Skant Gupta

Highly Available IP (HAIP) comes from 11.2.0.2 on-words and maximum 4 define for Private network

Now in 12c version introduces Highly Available VIP (HAVIP) a Clusterware-monitored VIP that can be used for non-Database
applications. Typically, VIPs are created for VIP or SCAN listeners for Database connections. In some cases, the need
arises to create a VIP monitored by Clusterware designed to support other applications.

For more Detail : Click Here

The post Highly Available VIP (HAVIP) appeared first on ORACLE-HELP.

In My Case Cause of below the issue at storage level.

failing currently due to LVM inconsistency

Below screenshot where CRS services are running on another node.

iocp0 is now available on pdb02

 

Ref: AIX 12.1.0.2 Installation Fails with “rtld: 0712-001 Symbol CreateIoCompletionPort was referenced ” (Doc ID 1949184.1)

The post rtld: 0712-001 Symbol CreateIoCompletionPort was referenced appeared first on ORACLE-HELP.

Quiz Night - 14-Jul-2018 14:07 - Jonathan Lewis

Because it’s been a long time since the last quiz night.  Here’s a question prompted by a recent thread on the ODevCom database forum – how many rows will Oracle sorts (assuming you have enough rows to start with in all_objects) for the final query, and how many sort operations will that take ?


drop table t1 purge;

create table t1 nologging as select * from all_objects where rownum < 50000;

select owner, count(distinct object_type), count(distinct object_name) from t1 group by owner;

Try to resist the temptation of doing a cut-n-paste and running the code until after you’ve thought about the answer.

Next