DBA Blogs

How to track deleted records data in a file

Tom Kyte - Sat, 2017-04-15 00:26
Hi Tom, we have some exercise to delete records in > 300 tables depend on few requirements. We are using dynamic sql delete statement iterating through tables list. Is there any chance can I track what data is deleting from back-end, I want inse...
Categories: DBA Blogs

Plan Hash Values Changed For the Same SQL ID || How to determine why ?

Tom Kyte - Fri, 2017-04-14 06:06
Hello Team, I have a particular sql query which runs on a daily basis for around 40 mins on an average. Today it has been running since 4:50 BST and has not completed at all. I analysed what went wrong today and have detailed out what I could :...
Categories: DBA Blogs

the confusion about db_domain

Tom Kyte - Fri, 2017-04-14 06:06
Hi: Today I read the link below about the db_domain https://docs.oracle.com/cd/B28359_01/server.111/b28320/initparams052.htm#REFRN10036 it is said that Range of values of this parameter can not be null IS that right? because my datab...
Categories: DBA Blogs

RMAN - "report unrecoverable" command not listing the expected tablespace

Tom Kyte - Fri, 2017-04-14 06:06
I have added some rows to a table TAB_1 with NOLOGGING option after taking an RMAN backup. The tablespace TBS_1 is used for the table. When I tried to find the unrecoverable tablespaces using the command "REPORT UNRECOVERABLE", the expected tablespac...
Categories: DBA Blogs

Constraints

Tom Kyte - Fri, 2017-04-14 06:06
Is there a way I can drop a table without cascade if I have foreign keys pointing to the primary key of the table? Any idea?
Categories: DBA Blogs

CAST TABLE type not working with EXECUTE IMMEDIATE

Tom Kyte - Fri, 2017-04-14 06:06
Hi, I have created below TABLE type of VARCHAR2 - create or replace TYPE VC_ARRAY is table of varchar2(4000); and tables create table tb_test_src as select object_name, object_id from user_objects where rownum <= 20; create table tb_test...
Categories: DBA Blogs

UNUSABLE status of indexes , index partitions and index subpartitions

Tom Kyte - Fri, 2017-04-14 06:06
How can i find the unusable date of indexes, index partitions and index subpartitions , only with sqlplus , not looking in the alert log. - How can i find the date that the status of an index has became unusable ? - How can i know the date that t...
Categories: DBA Blogs

Data Encryption And Decryption In Tables

Tom Kyte - Fri, 2017-04-14 06:06
Team, As part of my request please consider the below create and insert statements : Version of DB being used is : Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit create table bo_master(ssn number, bo_code number...
Categories: DBA Blogs

PLsql code to search and find pdf from folder and send in email as attachment

Tom Kyte - Fri, 2017-04-14 06:06
HI Oracle, i Have a requirement - search and find pdf from folder/directory and send via email to the particular receipient. here is more info about scenario: we have pdf files generated already in one folder/directory we need to send pdf f...
Categories: DBA Blogs

Merger the records with same Id

Tom Kyte - Fri, 2017-04-14 06:06
Hi Tom, I have a below scenario.This is not fixed and it is dynamic in nature APPLICANT_NAME,Renewal_NAME,NUMBER,TYPE,SUBTYPE,ETHNICITY, GENDER,ZIP_CODE,QUESTION_id,ANSWER_Id,Answer JAN,LR-001352966,LPN.039005.MEDS-IV,Licensed Practical,,White,Fem...
Categories: DBA Blogs

sub queries

Tom Kyte - Fri, 2017-04-14 06:06
Hi Tom, SQL> select * from customer_det; CID NAME DOP ---------- -------------------- --------- 1 Chandan 01-JAN-84 2 Ramu 01-MAR-84 3 Raghavendra 17...
Categories: DBA Blogs

Partner Webcast – Smarter Diagnostic and Insights for Enterprise IT Assets with Oracle Management Cloud

Across the globe, companies have embraced cloud computing and the agility it provides. By constantly recombining technology and cloud solutions, enterprises are able to provide an ongoing stream of...

We share our skills to maximize your revenue!
Categories: DBA Blogs

Deploying a SpringBoot Application to Oracle App Container - Part 1/2

In this post I am going to create a simple SpringBoot Application and deploy it to Oracle's Application Container Cloud Service. Oracle ACCS is a polyglot application hosting environment for a number...

We share our skills to maximize your revenue!
Categories: DBA Blogs

Unable to fast refresh after rebuild of master table.

Tom Kyte - Thu, 2017-04-13 11:46
Hi, I have: - master table and mlogs on 11g EE DB - fast refresh materialized view on 12g EE DB - those two databases are connected with dblink After rebuild was done on master table, I wasn't able to fast refresh my materialized view. ...
Categories: DBA Blogs

assign sqlplus value to shell script variable

Tom Kyte - Thu, 2017-04-13 11:46
Hi Tom I am trying to get the f_name value to FILE_VAR. My proc is having 2nd and 3rd parameter as OUT parameter. I want to take its "f_name" variable's value to Shell script variable "FILE_VAR" I tried below but its not working. <code> FILE_...
Categories: DBA Blogs

Adaptive execution plans inside Stored Procedure

Tom Kyte - Thu, 2017-04-13 11:46
I have a stored procedure that using a global temp table called ITEMS with delete on commit (session based stats). Inside the stored procedure, sometimes 1 row gets added to ITEMS, sometimes 200,000 rows to that table. ITEMS is then used in a few ot...
Categories: DBA Blogs

Regex for comma separated strong with predefined list of words that are allowed.

Tom Kyte - Thu, 2017-04-13 11:46
Hello, With this regex : not REGEXP_LIKE (categories, '[^(subcat1| subcat2 |subcat3|null; )]', 'i'), i can specified what kind of values i can be used and this are separated with ';'. but i can do that <code>insert into regexText Values(';') ...
Categories: DBA Blogs

Need to call Restful API using Oracle PL SQL

Tom Kyte - Thu, 2017-04-13 11:46
Hi, I am new to hitting Restful APIs from Oracle. I have this huge Xml (> 4000 characters) which I need to post to a remote restful api endpoint. Please let me know how to accomplish this. Below is my sample code that I am playing with right now....
Categories: DBA Blogs

ORA_HASH Value Collision.

Tom Kyte - Thu, 2017-04-13 11:46
Hi Tom, I am attempting to assign unique value to a an expression value which is distinct and derived from the concatenation of multiple fields. Here's the usage: Query1: create table Table2 parallel 16 as select /*+ parallel(a,16)*/ disti...
Categories: DBA Blogs

Error ORA-01033 After Doing a Switchover in a 12.1 RAC Environment

Pythian Group - Thu, 2017-04-13 08:29

The other day I did a switchover in a RAC environment , which went pretty smooth , but after doing the switchover in the primary, I kept getting the following error:

select dest_name,status,error from gv$archive_dest_status where dest_id=2;

DEST_NAME
--------------------------------------------------------------------------------
STATUS	  ERROR
--------- -----------------------------------------------------------------
LOG_ARCHIVE_DEST_2
ERROR	  ORA-01033: ORACLE initialization or shutdown in progress

LOG_ARCHIVE_DEST_2
ERROR	  ORA-01033: ORACLE initialization or shutdown in progress

I went and checked the standby, and saw the standby was in recover mode and waiting for the redo log

PROCESS STATUS	     CLIENT_P CLIENT_PID	  THREAD#	 SEQUENCE#	     BLOCK#    ACTIVE_AGENTS	 KNOWN_AGENTS
------- ------------ -------- ---------- ---------------- ---------------- ---------------- ---------------- ----------------
ARCH	CONNECTED    ARCH     44474			0		 0		  0		   0		    0
RFS	IDLE	     ARCH     133318			0		 0		  0		   0		    0
RFS	IDLE	     ARCH     50602			0		 0		  0		   0		    0
ARCH	CLOSING      ARCH     44470			1	     21623	      14336		   0		    0
ARCH	CLOSING      ARCH     44476			1	     21624		  1		   0		    0
ARCH	CLOSING      ARCH     44472			2	     19221	      96256		   0		    0
RFS	IDLE	     LGWR     133322			1	     21625	      17157		   0		    0
RFS	IDLE	     LGWR     50620			2	     19222	      36611		   0		    0
MRP0	WAIT_FOR_LOG N/A      N/A			2	     19222	      36617		  0		   0

My first train of thought was that the password file was incorrect, so I recreated them and copied them from the primary to the standby nodes, but I still kept getting the same error. I reviewed the environment with the scripts in DOC ID 1581388.1 and everything seemed alright. It really kept bugging me that the logs were not being applied even though the logs were being shipped to the standby (so it did have to do with the password file), but what really bothered me, was that I had just recreated the password file in $ORACLE_HOME/dbs and I still kept getting the same error.

So after a while of troubleshooting, I found that in the new primary the password file was residing in an ASM Diskgroup, and that was the main culprit. This meant that I had to copy the password file from the ASM diskgroup in the primary to the standby.
Primary

[oracle@localhost trace]$ srvctl config database -d renedb
Database unique name: renedb
Database name: 
Oracle home: /u01/app/oracle/product/12.1.0.2/db_1
Oracle user: oracle
Spfile: +DATA1/renedb/spfilerenedb.ora
Password file: +DATA1/renedb/PASSWORD/pwrenedb
Domain: 
Start options: open
Stop options: immediate
Database role: PHYSICAL_STANDBY
Management policy: AUTOMATIC
Server pools: 
Disk Groups: ARCH1,DATA1,REDO
Mount point paths: 
Services: 
Type: RAC
Start concurrency: 
Stop concurrency: 
OSDBA group: dba
OSOPER group: oper
Database instances: renedb1,renedb2
Configured nodes: localhost,localhost
Database is administrator managed
[oracle@localhost trace]$ exit
-bash-4.1$ sudo su - grid
[sudo] password for pythian: 
[grid@localhost ~]$ . oraenv
ORACLE_SID = [+ASM1] ? 
The Oracle base remains unchanged with value /u01/app/grid
[grid@localhost ~]$ asmcmd
ASMCMD> pwcopy +DATA1/renedb/PASSWORD/pwrenedb /tmp/pwrenedb
copying +DATA1/renedb/PASSWORD/pwrenedb -> /tmp/pwrenedb
ASMCMD> exit

Standby

[oracle@localhost dbs]$ scp 10.10.0.1:/tmp/pwrenedb /tmp/pwrenedb_stby
pwrenedb_stby_phdb                                                                                                                                                                                                    100% 7680     7.5KB/s   00:00    
[oracle@localhost dbs]$ exit
logout
[pythian@localhost ~]$ sudo su - grid
[sudo] password for pythian: 
Last login: Fri Mar 31 21:55:53 MST 2017
[grid@localhost ~]$ . oraenv
ORACLE_SID = [grid] ? +ASM1
The Oracle base has been set to /u01/app/grid
[grid@localhost ~]$ asmcmd
ASMCMD> mkdir DATA/RENEDB/PASSWORD
ASMCMD> pwcopy /tmp/pwrenedb_stby_phdb +DATA/RENEDB/PASSWORD/pwrenedb_stby
copying /tmp/pwrenedb_stby_phdb -> +DATA/RENEDB/PASSWORD/pwrenedb_stby
ASMCMD> exit
[grid@localhost ~]$ exit
logout
[pythian@localhost ~]$ sudo su - oracle
Last login: Sat Apr  1 01:35:46 MST 2017 on pts/4
The Oracle base has been set to /u01/app/oracle
[oracle@localhost dbs]$ srvctl modify database -d renedb_stby -pwfile +DATA/RENEDB/PASSWORD/pwrenedb_stby
[oracle@localhost dbs]$ srvctl config  database -d renedb_stby
Database unique name: renedb_stby
Database name: 
Oracle home: /u01/app/oracle/product/12.1.0/dbhome_1
Oracle user: oracle
Spfile: /u01/app/oracle/product/12.1.0/dbhome_1/dbs/spfilerenedb_stby.ora
Password file: +DATA/RENEDB/PASSWORD/pwrenedb_stby
Domain: 
Start options: open
Stop options: immediate
Database role: PRIMARY
Management policy: AUTOMATIC
Server pools: 
Disk Groups: ARCH,DATA,REDO
Mount point paths: 
Services: 
Type: RAC
Start concurrency: 
Stop concurrency: 
OSDBA group: dba
OSOPER group: oper
Database instances: renedb_stby1,renedb_stby2
Configured nodes: *******,***********
Database is administrator managed

Once I did this, the standby started applying the redo logs and after the gap was closed the Primary switchover status was “TO STANDBY”
Primary

Primary Site last generated SCN

*******************************

DB_UNIQUE_NAME	SWITCHOVER_STATUS	  CURRENT_SCN
--------------- -------------------- ----------------
renedb	TO STANDBY		 134480468945

Standby

Data Guard Apply Lag

********************

NAME	     LAG_TIME		  DATUM_TIME	       TIME_COMPUTED
------------ -------------------- -------------------- --------------------
apply lag    +00 00:00:00	  04/01/2017 04:05:51  04/01/2017 04:05:52

1 row selected.


Data Guard Gap Problems

***********************

no rows selected

PROCESS STATUS	     CLIENT_P CLIENT_PID	  THREAD#	 SEQUENCE#	     BLOCK#    ACTIVE_AGENTS	 KNOWN_AGENTS
------- ------------ -------- ---------- ---------------- ---------------- ---------------- ---------------- ----------------
ARCH	CONNECTED    ARCH     44474			0		 0		  0		   0		    0
RFS	IDLE	     ARCH     133318			0		 0		  0		   0		    0
RFS	IDLE	     ARCH     50602			0		 0		  0		   0		    0
ARCH	CLOSING      ARCH     44470			1	     21623	      14336		   0		    0
ARCH	CLOSING      ARCH     44476			1	     21624		  1		   0		    0
ARCH	CLOSING      ARCH     44472			2	     19221	      96256		   0		    0
RFS	IDLE	     LGWR     133322			1	     21625	      17157		   0		    0
RFS	IDLE	     LGWR     50620			2	     19222	      36611		   0		    0
MRP0	APPLYING_LOG N/A      N/A			2	     19222	      36617		  33		   33

9 rows selected.

Conclusion
In 12.1 it is recommended as per DOC ID 1984091.1, to have the password file in ASM diskgroups. So once I did this, I was able to workaround error ORA-01033 and able to sleep well!

Note.-  This was originally published in rene-ace.com

Categories: DBA Blogs

Pages

Subscribe to Oracle FAQ aggregator - DBA Blogs