Feed aggregator

When does RMAN validate archived redo logs are available during a duplicate?

Tom Kyte - Mon, 2017-10-16 00:26
Does RMAN validate all archived redo logs are available at the beginning of a duplication or once it starts restoring the logs after the backup data files have been restored?
Categories: DBA Blogs

Keyword Rank Tracking Tools

Nilesh Jethwa - Sun, 2017-10-15 13:34

An important element of search engine optimization (SEO) is choosing the right keyword. With the right keywords, you can make your content rank on search engines. But the work doesn’t stop after ranking, you still need to track the position of your keyword during the search. That way, you can obtain helpful information that will guide you in keeping your SEO efforts successful.

Why Check Keyword Ranking Regularly

One of the main reasons why you need to check your keyword ranking is to identify target keywords. Any SEO professional or blogger should understand how important it is for their content marketing strategies. In fact, a common mistake committed by website administrators and bloggers is writing and publishing articles that don’t target any keywords. It’s like aiming your arrow at something that you are not sure of.

Here are some of the best tools you can take advantage of when tracking your keyword rank:

SEMRUSH. When using this keyword rank tracking tool, it will take 10 to 15 minutes in order to determine which keywords or key phrases to use. Whether you are a webmaster or SEO specialist, this tool will help you analyze data for your clients and website. It also offers useful features such as in-depth reports, keyword grouping, and competitor tracking.

Google Rank Checker. This is a premium online tool that you can use for free. It will help you in tracking keyword positioning while making sure that you appear in search results. To use Google Rank Checker, all you need to do is enter the keywords that you want to check as well as your domain name. After putting in the details, you will now view the keyword rank.


Read more at https://www.infocaptor.com/dashboard/best-tools-for-keyword-rank-tracking

Oracle SOA Suite developer IDE: Creating a BPEL process (101HelloWorld)

Dietrich Schroff - Sun, 2017-10-15 05:53
After downloading and installing jdeveloper SOA Suite edition here a short 101 for building a BPEL process. (Nearly 10 years ago i posted the lost collaxa tutorials: 101.HelloWorld. All of these tutorials can be found here.)

First you have to start your jdeveloper (Middleware/Oracle_Home/jdeveloper/jdev/bin/jdev) and choose "All Features":
Then create a new application:
and choose SOA application:
and walk through the application wizard:
application wizard 2/3
application wizard 3/3
application wizard 3.1/3 (create BPEL process)

and here we are:

a right click onto the blue BPELProcess1 icon and "edit" opens the BPEL process:

On the right hand to this process you can find the components palette:

Now drag and drop the assign icon between the receiveInput and replyOutput node:

A right click to the Assign node and choosing "edit" will open up this dialog:

You can do this mapping via drag and drop:

and finished!

A nice feature of jdeveloper is the "History" tab, where you can compare changes within the XML:

Next posting i will show, how you can run this BPEL process within the jdeveloper builtin weblogic server.

International Financial Reporting Standards (IFRS)

OracleApps Epicenter - Sun, 2017-10-15 00:59
International Financial Reporting Standards (IFRS) Oracle’s Financial Mgmt Solutions Segment reporting Specialized currency Intercompany reconciliations Inventory management Multi-GAAP management Flow, audit trails Custom dimensions Financial intelligence Simulations, impacts Reports, MS Office direct links   IFRS is coming to the US Oracle is designed to address the IFRS requirements Organizations likely didn’t originally implement Oracle with […]
Categories: APPS Blogs

Debugging bash scripts on remote hosts using Eclipse

Dietrich Schroff - Sat, 2017-10-14 14:05
After installing the two plugins
it is very easy to debug scripts inside your workspace on your computer. But i was wondering, if this can work on a remote host, too.

So what has to be done?

Only these steps:
  • copy your shell scripts AND _DEBUG.sh onto your remote host
  • change the first line of _DEBUG.sh from
    exec 33<>/dev/tcp/localhost/33333
    exec 33<>/dev/tcp/host_running_eclipse/33333
After that start the debugger in eclipse and then run the shell script on the remote host. Done.

If this does not work: Check for firewalls blocking port 33333

Submitting Questions

Tom Kyte - Sat, 2017-10-14 11:46
Hi Tom, I've been consulting your site for years, and its almost always a source of answers to just about all Oracle issues I encounter. On rare occasions I might encounter an issue not previously discussed, but there's an uncertainty how long I w...
Categories: DBA Blogs

Mmm, π

Oracle WTF - Sat, 2017-10-14 06:31

Young rz.He over on Stack Overflow has a question about why SQL*Plus doesn't parse every damn line of a PL/SQL block in case it's a comment in order to avoid unnecessary prompts for substitution variables. Here's his mcve:

SQL> begin
  2      null; -- &commented_out
  3  end;
  4  /
Enter value for commented_out: wtf

PL/SQL procedure successfully completed.

Oh, wait - it's not that. It's actually this:


  pi              CONSTANT NUMBER := 3.1415926;
  radius          NUMBER := 3;

  -- to make it more dynamic I can set 
  -- radius NUMBER := &enter_value;

  circumference   DECIMAL(4,2) := radius * pi * 2;
  area            DECIMAL(4,2) := pi * radius ** 2;


  -- DBMS_OUTPUT.PUT_LINE('Enter a valur of radius: '|| radius);
  dbms_output.put_line('For a circle with radius '
   || radius
   || ',the circumference is '
   || circumference
   || ' and the area is '
   || area
   || '.');

But anyway.

This excellent answer explains all about clients and servers and procedure parameters and why it's really not SQL*Plus's job to pre-parse everything before sending it to the database and it wouldn't be such a great idea if it did. I voted up. But what really baked my noodle was the opening comment (my emphasis):

You can use a parameter instead of a substitution variable to allow different users to call the procedure with different values of pi.

...with a fixed version of the code posted as an example...

  AREA          DECIMAL(4, 2) := P_PI * P_RADIUS ** 2;

  DBMS_OUTPUT.put_line('For a circle with radius '
                       || P_RADIUS
                       || ', the circumference is '
                       || CIRCUMFERENCE
                       || ' and the area is '
                       || AREA
                       || '. ' || 'Calculated with Pi = ' || P_PI);

You will agree that is much more flexible. Now we can call it for a conventional π, like this:

SQL> call cal_circle(3, 3.1416);
For a circle with radius 3, the circumference is 18.85 and the area is 28.27. Calculated with Pi = 3.1416

Or like this:

SQL> call cal_circle(3, acos(-1));
For a circle with radius 3, the circumference is 18.85 and the area is 28.27. Calculated with Pi = 3.1415926535897932384626433832795028842

But what if we need to switch to the imperial π following our exit from the EU, or the chancellor increases the UK's π in the next budget in order to stimulate economic growth, or if we simply want to use the same procedure in an alternative universe with a fundamentally different geometry than our own? No problem:

SQL> call cal_circle(3, 2.71828);
For a circle with radius 3, the circumference is 16.31 and the area is 24.46. Calculated with Pi = 2.71828

Now that is reusability. (The radius can't be more than 9.99, but come on, you can't have everything.)

As usual, of course, the easy way is no fun:

create or replace function circumference
    ( p_radius in number )
    return number
    return p_radius * 6.2831853071795864769252867666;
end circumference;

Study: ASC 606 Might Slow IPOs in 2018 ??

OracleApps Epicenter - Fri, 2017-10-13 20:33
A new study by Deloitte reveals that initial public offerings (IPOs) may suffer as a result of New Accounting Standard (ASC606) Here are excerpts from an article by Terry Sheridan in accountingWEB.com Here’s a snapshot of the survey’s findings: 60 percent of the respondents said they have not begun implementation of the standard or have […]
Categories: APPS Blogs

After install of 12,2 on Windows 10, I can only connect as SYS

Tom Kyte - Fri, 2017-10-13 17:26
I have installed Oracle 12c Release 2 twice as a non-container database. The issue I have run into both times is that I keep getting "ORA-01017: invalid username/password; logon denied" every time I try connecting as any user other than SYS. See exam...
Categories: DBA Blogs

How to Create backup table from one schema to another schema every time the live schema table is inserted or deleted or updated

Tom Kyte - Fri, 2017-10-13 17:26
Hi, I want to know How to create a backup table that will backup data from one schema to another schema table every time the live table is inserted or updated or deleted. Awaiting for your response. Thanks, Ershad
Categories: DBA Blogs

Hierarchical query with count of leave attributes

Tom Kyte - Fri, 2017-10-13 17:26
Hello Experts. I want to calculate the sum of the count of the leaves' attributes in a hierarchical query <code> create table hq_test (parent_id NUMBER, child_id NUMBER); INSERT INTO hq_test (parent_id, child_id) VALUES (25,26); INSERT INTO hq...
Categories: DBA Blogs

bank Statement reconciliation

OracleApps Epicenter - Fri, 2017-10-13 11:41
Reconciliation provides a means of determining the balance on the bank accounts in Oracle Financials is equivalent to the balance shown on the bank statements. The High Point of the Reconciliation activity in Cash Managment includes: All transactions processed via the Oracle Accounts Payable, General Ledger and Oracle Accounts Receivable is reconciled against transactions on […]
Categories: APPS Blogs

Vertex and Oracle Cloud

OracleApps Epicenter - Fri, 2017-10-13 09:28
Oracle ERP Cloud Transaction Tax Automation is consist of Comprehensive Tax Partner Offerings Robust infrastructure for tax partner solutions Tax Content and Tax Reporting services providing fast ROI option for mid-market companies adopting Oracle ERP Cloud Integrated Cloud-to-Cloud tax calculation services for enterprise grade companies Infrastructure has been optimized for ERP Cloud customers using end-to-end […]
Categories: APPS Blogs

Switchover and Switchback simplified in Oracle 12c

Syed Jaffar - Fri, 2017-10-13 07:51

Business continuity (Disaster Recovery) has become a very critical factor for every business, especially in the financial sectors. Most of the banks are tending to have their regular DR test to meet the central bank regulation on DR testing capabilities.

Very recently, there was a request from one of the clients to perform a reverse replication and rollback (i.,e switchover & switchback) between the HO and DR for one of the business critical databases. Similar activities performed with easy on pre 12c databases. However, this was my first experience with Oracle 12c. After spending a bit of time to explore whats new in 12c Switchover, it was amazing to learn how 12c simplified the procedure. So, I decided to write a post on my experience.

This post demonstrates how Switchover and Switchback procedure is simplified in Oracle 12c.

The following is used in the scenario:

·        2 instances Oracle 12c RAC primary database (IMMPRD)
·        Single instance Oracle 12c RAC Standby database (IMMSDB)

Look at the current status of the both databases:
-- Primary
IMMPRD> select status,instance_name,database_role from v$database,v$instance;

------------ ---------------- ----------------
OPEN         IMMPRD1           PRIMARY

-- Standby
IMMSDB> select status,instance_name,database_role from v$database,v$instance;

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

Before getting into the real action, validate the following to avoid any failures during the course of role transition:

·        Ensure log_archive_dest_2 is configured on PRIMARY and STANDBY databases
·        Media Recovery Process (MRP) is active on STANDBY and in sync with PRIMARY database
·        Create STANDBY REDO logs on PRIMARY, if not exists
·        FAL_CLIENT & FAL_SERVER parameters set on both databases
·        Verify TEMP tablespaces on STANDBY, add them if required, as TEMPFFILES created after STANDBY creation won’t be propagated to STANDBY site.

Pre-Switchover in 12c

For a smooth role transition, it is important to have everything in-place and in sync. Pre-Oracle 12c, a set of commands used on PRIMARY and STANDBY to validate the readiness of the systems. However, with Oracle 12c, this is simplified with the ALTER DATABASE SWITCHOVER VERIFY command. The command performs the following set of actions:

·        Verifies minimum Oracle version, i.e, Oracle 12.1
·        Verify MRP status on Standby database

Let’s run the command on the primary database to validate if the environments are ready for the role transition.

IMMPRD>  alter database switchover to IMMSDB verify;
 alter database switchover to IMSDB verify
ERROR at line 1:
ORA-16475: succeeded with warnings, check alert log for more details

When the command is executed, an ORA-16475 error was encountered. For more details, lets walk through the PRIMARY and STANDBY databases alert.log file, and pay attention to the SWITCHOVER VERIFY WARNING.

--primary database alert.log

Fri Oct 13 11:16:00 2017
SWITCHOVER VERIFY: Send VERIFY request to switchover target IMSDB
SWITCHOVER VERIFY WARNING: switchover target has no standby database defined in LOG_ARCHIVE_DEST_n parameter. If the switchover target is converted to a primary database, the new primary database will not be protected.

ORA-16475 signalled during:  alter database switchover to IMSDB verify...

The LOG_ARCHIVE_DEST_2 parameter was not set on the STANDBY database and the VERIFY command produced the warning. After setting the parameter on the STANDBY, the verify command was re-ran, and it went well this time.

IMMPRD> alter database switchover to IMMSDB verify;

Database altered.

PRIMARY database alert.log confirms no WARINGS

alter database switchover to IMMSDB verify
Fri Oct 13 08:49:20 2017
SWITCHOVER VERIFY: Send VERIFY request to switchover target IMMSDB
Completed: alter database switchover to IMMSDB verify

Switchover in 12c 

After successful validation and confirmation about the DBs readiness for the role transition, execute the actual switchover command on the primary database. (advised to view the alert.log files of PRIMARY and STANDBY instances).

IMMPRD> alter database switchover to IMMSDB;

Database altered.

Let’s walk through the PRIMARY and STANDBY database alert.log files to review what Oracle has internally done.

--primary database alert.log

alter database switchover to IMMSDB
Fri Oct 13 08:50:21 2017
Starting switchover [Process ID: 302592]
Fri Oct 13 08:50:21 2017
Waiting for target standby to receive all redo
Fri Oct 13 08:50:21 2017
Waiting for all non-current ORLs to be archived...
Fri Oct 13 08:50:21 2017
All non-current ORLs have been archived.
Fri Oct 13 08:50:21 2017
Waiting for all FAL entries to be archived...
Fri Oct 13 08:50:21 2017
All FAL entries have been archived.
Fri Oct 13 08:50:21 2017
Waiting for dest_id 2 to become synchronized...
Fri Oct 13 08:50:22 2017
Active, synchronized Physical Standby switchover target has been identified
Preventing updates and queries at the Primary
Generating and shipping final logs to target standby
Switchover End-Of-Redo Log thread 1 sequence 24469 has been fixed
Switchover End-Of-Redo Log thread 2 sequence 23801 has been fixed
Switchover: Primary highest seen SCN set to 0x960.0x8bcd0f48
ARCH: Noswitch archival of thread 2, sequence 23801
ARCH: End-Of-Redo Branch archival of thread 2 sequence 23801
ARCH: LGWR is scheduled to archive destination LOG_ARCHIVE_DEST_2 after log switch
ARCH: Standby redo logfile selected for thread 2 sequence 23801 for destination LOG_ARCHIVE_DEST_2
ARCH: Noswitch archival of thread 1, sequence 24469
ARCH: End-Of-Redo Branch archival of thread 1 sequence 24469
ARCH: LGWR is scheduled to archive destination LOG_ARCHIVE_DEST_2 after log switch
ARCH: Standby redo logfile selected for thread 1 sequence 24469 for destination LOG_ARCHIVE_DEST_2
ARCH: Archiving is disabled due to current logfile archival
Primary will check for some target standby to have received all redo
Waiting for target standby to apply all redo
Backup controlfile written to trace file /u01/app/oracle/diag/rdbms/imprd/IMPRD1/trace/IMPRD1_ora_302592.trc
Converting the primary database to a new standby database
Clearing standby activation ID 627850507 (0x256c3d0b)
The primary database controlfile was created using the
'MAXLOGFILES 192' clause.
There is space for up to 186 standby redo logfiles
Use the following SQL commands on the standby database to create
standby redo logfiles that match the primary database:
Archivelog for thread 1 sequence 24469 required for standby recovery
Archivelog for thread 2 sequence 23801 required for standby recovery
Switchover: Primary controlfile converted to standby controlfile succesfully.
Switchover complete. Database shutdown required
USER (ospid: 302592): terminating the instance
Fri Oct 13 08:50:44 2017
Instance terminated by USER, pid = 302592
Completed: alter database switchover to IMMSDB
Shutting down instance (abort)

--standby database alert.log

SWITCHOVER: received request 'ALTER DTABASE COMMIT TO SWITCHOVER  TO PRIMARY' from primary database.
Fri Oct 13 08:50:32 2017
Maximum wait for role transition is 15 minutes.
Switchover: Media recovery is still active
Role Change: Canceling MRP - no more redo to apply

SMON: disabling cache recovery
Fri Oct 13 08:50:41 2017
Backup controlfile written to trace file /u01/app/oracle/diag/rdbms/imsdb/IMMSDB1/trace/IMMSDB1_rmi_120912.trc
SwitchOver after complete recovery through change 10310266982216
Online logfile pre-clearing operation disabled by switchover
Online log +DATAC1/IMMSDB/ONLINELOG/group_1.3018.922980623: Thread 1 Group 1 was previously cleared
Standby became primary SCN: 10310266982214
Switchover: Complete - Database mounted as primary
SWITCHOVER: completed request from primary database.
Fri Oct 13 08:51:11 2017

At this point-in-time, the new PRIMARY database is in MOUNT state, so you need to OPEN the database.

IMMSDB> alter database open

And startup the STANDBY database and enable MRP: (below is the active standby database command)

IMMPRD> startup
IMMPRD> recover managed standby database using current logfile disconnect from session;

Post Switchover, run through the following:

IMMSDB> alter system switch logfile;

IMMSDB> select dest_id,error,status from v$archive_dest where dest_id=2;

IMMSDB> select max(sequence#),thread# from v$log_history group by thread#;
IMMSDB> select max(sequence#)  from v$archived_log where applied='YES' and

On Standby database

IMMPRD> select thread#,sequence#,process,status from gv$managed_standby;
-- in 12.2, use gv$dataguard_status instead of gv$managed_standby view

IMMPRD> select max(sequence#),thread# from v$archived_log group by thread#;

You can also enable the trace on primary and standby before performing the role transition to analyze any failures during the procedure. Use the below procedure on the PRIMARY database to enable the tracing:

SQL> alter system set log_archive_trace=8191;  -- enabling trace

SQL> alter system set log_archive_trace=0;      -- disabling trace


To revert (switch back) to the previous situation, perform the same action. Remember, now, your primary is your previous STANDBY and standby is previous PRIMARY.


12c Data guard Switchover Best Practices using SQLPLUS (Doc ID 1578787.1)

Execute immediate and passing clob as a parameter is not working.

Tom Kyte - Thu, 2017-10-12 23:06
I have the following procedure <code> create or replace PROCEDURE bscs_rateplan_sync ( tmcode_list IN CLOB, project_id IN VARCHAR2, requestname IN VARCHAR2, v_errm OUT VARCHAR2 ) ...
Categories: DBA Blogs

Differrent Character set and NLS

Tom Kyte - Thu, 2017-10-12 23:06
Hi TOM, Some question on Character set and NLS. 1) What is the different between US7ASCII and WE8ISO8859P1 character set and NLS? 2) Is there any problem for the DB with two diferrent character sets to interact, - trigger - dblink - e...
Categories: DBA Blogs

Auditing Operating system program name

Tom Kyte - Thu, 2017-10-12 23:06
Sir, i am developing a client/server program using VB & Oracle, it is in testing stage. i want to track whether the programmer is doing changes to the database using the application program(VB) or by logging into database(SQLPLUS) for this i us...
Categories: DBA Blogs

How you manage you're work-spaces!!

Tom Kyte - Thu, 2017-10-12 23:06
Greetings Team, I am very curious to know how you actually manage you're work spaces.I mean you always face different kind of questions all day along.How many laptops you use(of course you are using VM). But my question is if you guide someone fo...
Categories: DBA Blogs

SQL * Loader connection errors

Tom Kyte - Thu, 2017-10-12 23:06
To previous answer to MY SQL*Loader question I had asked, you said: Examples of SQL*Loader String <code>sqlldr.exe userid=my_user/my_password@some_string control=my_control.ctl sqlldr.exe userid=scott/tiger@//my_host:my_port/my_service_name con...
Categories: DBA Blogs

Oracle SOA Suite developer IDE: Jdeveloper 12c (download & installation)

Dietrich Schroff - Thu, 2017-10-12 15:40
After installing an Oracle 12c database onto an Oracle Linux i want to run Oracle's SOA Suite. But before the installation of SOA Suite 12c including the application server, i decided to download Jdeveloper to get a first impression of BPEL processes in version 12c (several years ago i did some projects with Oracel Fusion and Oracle SOA Suite 11g).

First try, to get Jdeveloper with SOA Suite extensions (skip these 4 steps - because this attempt misses the SOA Suite...):
  1. You have to download two files:
    -rw-rw-r-- 1   432104829 Okt  7 21:40 jdev_suite_122130_linux64-2.zip
    -rw-rw-r-- 1 2045438518 Okt  7 21:51 jdev_suite_122130_linux64.bin
  2. chmod u+x jdev_suite_122130_linux64.bin
  3. ./jdev_suite_122130_linux64.bin
  4. Start Jdeveloper:

But after all this steps, still the SOA Suite is missing.
At the download site of Jdeveloper you will even not find a word about SOA Suite. Hmmm. Let's take a look at the SOA Suite webpages:

 "... it includes ... Oracle JDeveloper with SOA IDE extensions ...."

OK. Let's go:

  1. Download the SOA Suite quickstart zip files
  2. Extract them:
    schroff@zerberus:~/Downloads$ unzip fmw_12.
    Archive:  fmw_12.
      inflating: fmw_12. 
    schroff@zerberus:~/Downloads$ unzip fmw_12.
    Archive:  fmw_12.
      inflating: fmw_12.  
  3. Start the installation:
    /home/data/opt/jdk1.8.0_131/bin/java -jar fmw_12.

  4. Start Jdeveloper:

That was not as easy as expected...


Subscribe to Oracle FAQ aggregator