DBA Blogs

Execute Immediate with forall

Tom Kyte - Tue, 2016-09-13 06:26
Hi Tom, I've below code where i need to update different table with status to null. declare type v_table_name is table of varchar2(100); p_tab v_table_name; pv_tab v_table_name; v_sql varchar2(100); v_var varchar2(100); ...
Categories: DBA Blogs

Standby from hot backup and 2 identical files

Tom Kyte - Tue, 2016-09-13 06:26
Hi, friends! We have a primary server, standby and the second standby. The 2nd stndby is a new server where i want to migrate database. I made a hot backup. 2nd stndby have a 1 directory for datafiles, but primary have 3 directories. In init file...
Categories: DBA Blogs

Clob size

Tom Kyte - Tue, 2016-09-13 06:26
Hi We have table t and it has clob segment c . We can measure the size of the clob segment from dba_segments. Say on day 1 : size is 0 gb On day 2: size is 4 gb on day 3 : we have pre allocated the extents of size 100gb on day 4: we ...
Categories: DBA Blogs

Does the Data Guard Broker populate and manage the FAL_SERVER and FAL_CLIENT parameters?

Tom Kyte - Tue, 2016-09-13 06:26
I found in the 9.2 documentation that these two parameters were removed but everywhere I look it seems people are populating them manually still and I see no mention of them being managed by the broker in 11 and 12 documentation. Are these still mana...
Categories: DBA Blogs

Insert Direct path load

Tom Kyte - Tue, 2016-09-13 06:26
Hi tom, While inserting record in a table when we use append hints, it going for direct path load but when use parallel hints it is going for conventional path load.Is it correct? While insert use should always use Append hints not parallel hi...
Categories: DBA Blogs

Oracle Service Secrets: Migrate Transparently

Pythian Group - Mon, 2016-09-12 15:02

Databases or schemas tend to get moved around between different servers or even datacenters for hardware upgrades, consolidations or other migrations. And while the work that needs to be done is pretty straight forward for DBAs, I find the most annoying aspect of that is updating all client connect strings and tns entries used with new IP addresses and – if not using services – also the SID as the instance name might have changed.

That process can be simplified a lot when following a simple good practice of creating an extra service for each application or schema and along with that service also a DNS name for that IP. With that in place, a database can be migrated without the need to touch client connection parameters or tns aliases. All that is needed will be to migrate the database or schema, create the service name on the new instance and update the DNS record to the new machine.

Demo

Here is an example. I am migrating a schema from an 11g single instance on my laptop to a RAC database in the oracle public cloud. I am connecting to that database with blog_demo.pythian.com both as the hostname (faked through /etc/hosts instead of proper DNS for this demo) and the service name. As an application I am connecting to the database with sqlcl and a static connection string. Just remember that the whole, and only point of this demo is to migrate the schema without having to change that connect string.

brost$ ping -c 1 blog_demo.pythian.com
PING blog_demo.pythian.com (192.168.78.101): 56 data bytes
64 bytes from 192.168.78.101: icmp_seq=0 ttl=64 time=0.790 ms

brost$ ./sqlcl/bin/sql brost/******@blog_demo.pythian.com/blog_demo.pythian.com

SQLcl: Release 4.2.0.16.175.1027 RC on Mon Sep 05 17:50:11 2016

Copyright (c) 1982, 2016, Oracle.  All rights reserved.

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options


SQL> select instance_name from v$instance;

INSTANCE_NAME   
----------------
ORCL        

Next I migrated my schema with datapump and imported to a PDB running on a 12c RAC database.

Then added the service name BLOG_DEMO to PDB1 on the database ORCL42.

$ srvctl add service -db orcl42 -pdb pdb1 -service blog_demo -preferred orcl421,orcl422
$ srvctl start service -db orcl42 -service blog_demo

Updated the DNS or as in this simplified demo my /etc/hosts and now I can connect with the same connection string. Note that the IP, the instance_name and the version have changed without the need to modify the connection string.

brost$ ping -c 1 blog_demo.pythian.com
PING blog_demo.pythian.com (140.86.42.42): 56 data bytes

brost$ ./sqlcl/bin/sql brost/******@blog_demo.pythian.com/blog_demo.pythian.com

SQLcl: Release 4.2.0.16.175.1027 RC on Mon Sep 05 18:05:11 2016

Copyright (c) 1982, 2016, Oracle.  All rights reserved.

Last Successful login time: Mon Sep 05 2016 18:04:50 +02:00

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

SQL> select instance_name from v$instance;
INSTANCE_NAME   
----------------
orcl421

Note that with a proper DNS and a RAC target you would want to create A-records for the 3 SCAN IPs.

Other posts in this series

You can watch me talk briefly about this and other things that you can do with properly configured services in the video below or follow the links to other parts in this series.

tl;dr

When creating services for your applications to connect to a schema, also create a DNS entry for that and use this DNS name and the service for all client and application connections instead of using the hostname and SID. This might initially look like overhead but allows for flexibility when migrating schemas or databases to other systems. Updating DNS and creating a new service on the target machine can be changed in central places and saves updating potentially hundreds of client connect strings or tnsnames across the enterprise.

Categories: DBA Blogs

Self Join with Aggregate function

Tom Kyte - Mon, 2016-09-12 12:06
Hi, I got this question in one interview using Self Join Table Employee Salary 1 1K 2 2K 3 3K 4 4K 5 5K Output is 1 1K 2 3K 3 6K 4 10K ...
Categories: DBA Blogs

Execute multiple DDL statements at once

Tom Kyte - Mon, 2016-09-12 12:06
Hi Tom, I need your expertise in this regard. I'm using a self programmed version control system to upgrade my customers systems. In it all the changeset with packages, functions, DDL commands, DML commands and all this are saved in BLOBs in the...
Categories: DBA Blogs

Stats Calculation privileges

Tom Kyte - Mon, 2016-09-12 12:06
Hi, We have a database with multiple schemas and we need a proactive gather stats. The idea would be to have one schema(CORE) to gather stats for all tables in all schemas, so everything is centralize. is there any possibility to do this without g...
Categories: DBA Blogs

ORA-00984: column not allowed here in 11g but not in 12c ?

Tom Kyte - Mon, 2016-09-12 12:06
Hi Team, Please check below case are we missing something in 12c ? or incorrect in syntactical wise ? SQL> select banner from v$version; BANNER -------------------------------------------------------------------------------- Oracle Dat...
Categories: DBA Blogs

Database Migration.

Tom Kyte - Mon, 2016-09-12 12:06
I have to migrate database from 11g to 12c. What are the important steps I need to take into consideration. DB is widely used by the user.
Categories: DBA Blogs

Hierarchical Queries

Tom Kyte - Mon, 2016-09-12 12:06
<code>Oracle Version 11G. I have a requirement to show the data in a Hierarchy manner. It will shown as a TREE structure in the UI. I have 3 tables I) T_MAIN,T_ITEMS_MAIN It's having the Static data. II) T_PURCH_ITEMS Having purchase...
Categories: DBA Blogs

Ms access to oracle database connectivity

Tom Kyte - Mon, 2016-09-12 12:06
How to configure Oracle to be able to query a MS Access database (.mdb) that is lcoated in the remote server? I tried using configuring odbc and HS, at last am getting error "ORA-02019: connection description for remote database not found" I...
Categories: DBA Blogs

RMAN restore issue on duplicating database

Tom Kyte - Mon, 2016-09-12 12:06
Hi Team, While trying to perform RMAN restore getting the below error: RMAN-00571: =========================================================== RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS =============== RMAN-00571: =============...
Categories: DBA Blogs

Working with dates

Tom Kyte - Sat, 2016-09-10 05:06
Hello Again Need your help.. How can convert a date to YYYY-MM-DD hh24:00:00 format I have date like 2016-09-01 09:33:23 i want to have the output like 2016-09-01 08:00:00 (want to subtract and hour from the time and this format) I want t...
Categories: DBA Blogs

Stop archieve log generatio for date menupulation inside a transaction.

Tom Kyte - Sat, 2016-09-10 05:06
Hi Tom, we are facing problem with this archieve log generation, there is a SP where a lot of insert and update is performed inside a loop and this causes a high amount of archieve log (>300gb one sp cycle) to be generated. Is there a we can stop/...
Categories: DBA Blogs

programmatically running imp through a dbms_scheduler job: the import is done, but the job ends as failed

Tom Kyte - Sat, 2016-09-10 05:06
Hi, I am trying to run classic dump import by calling imp from a dbms_scheduler job, in Oracle 11.2 on my Windows 10 machine. The import itself is completed successfully, but unfortunately the job is failed (The system cannot find the path specifi...
Categories: DBA Blogs

Performance Tuning

Tom Kyte - Sat, 2016-09-10 05:06
Hi, While tunning a SQL what all things we need to check, and how can we check and improve the performance of the query? In PL/SQL block, other than using BULCK COLLECT and caching result, are there other ways to tune it? Thanks, DIPU
Categories: DBA Blogs

Database Security

Tom Kyte - Sat, 2016-09-10 05:06
Hi , Developers who have read access on production,if given write access to their individual schemas. what are the advantages and disadvantages from oracle point of view. i am considering security -a)no encryption of users data. b)performanc...
Categories: DBA Blogs

Max of consecutive numbers when greater than 0

Tom Kyte - Sat, 2016-09-10 05:06
Hi Tom, I am looking for a solution to get MAX COUNT of CONSECUTIVE NUMBER of ROWS for each BID where DUE GREATER THAN 0, when 0 appear reset counter for that BID. In below example I have max 3 consecutive row of BID 111 for MAR/APR/MAY and have...
Categories: DBA Blogs

Pages

Subscribe to Oracle FAQ aggregator - DBA Blogs