DBA Blogs

oracle form builder with pl/sql

Tom Kyte - Wed, 2016-11-16 08:46
Hello tom, I have a comprhensively large data in pl/sql for which i created a view for report purposes I am using oracle form builder for the first time. I wanted to show user data from my view based on the date range he/she selects Can you h...
Categories: DBA Blogs

difference between varchar2(10) and varchar2(10 char) in oracle

Tom Kyte - Tue, 2016-11-15 14:26
Hi team, Could you please explain the difference between the below two data types : difference between varchar2(10) and varchar2(10 char) in oracle asktom I know varcha2(10 char) , we can use in multibyte characters. So could you pleas eexp...
Categories: DBA Blogs

Backup and restore

Tom Kyte - Tue, 2016-11-15 14:26
Hi team, I have 11g Production database logical backup export and When development team required we import it into the reporting database. But now we have migrated 11g database to 12c with ASM and same logical backup export is every day. So, ...
Categories: DBA Blogs

Fix for Big Data Lite 4.6

If you are using Big Data Lite 4.6, you will need to make a change to the /etc/fstab file:sudo gedit /etc/fstab<replace line 1 with line 2> ...

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

How to insert the data using sql*loader by CSV file which contain comma as separator and comma present at column value

Tom Kyte - Mon, 2016-11-14 20:06
Hi Connor, I have an issue with sql*loader during loading below CSV file I have a csv file with below data:- Column names:- empid,empname,address,salary,deptn0 CSV file data:- 1123,Swarup,PO Box 42,1407 Graymalkin Lane,Salem Center, N...
Categories: DBA Blogs

Data Guard Log Apply method

Tom Kyte - Mon, 2016-11-14 20:06
I have a primary and a standby database which is running in maximum performance mode and LGWR ASYNC has been set for the same in Primary. Platform - Linux and Version - 12c This is regarding the apply process in Standby Database 1. I do not ...
Categories: DBA Blogs

Sql statistics per execution

Tom Kyte - Mon, 2016-11-14 20:06
Hi Tom, Is there a way to find cpu_time, db_time, physical_read_requests, physical_write_requests...etc per execution basis ? Say I run a particular SQL multiple times with different bind values. I'm interested in seeing sql with bind variables ...
Categories: DBA Blogs

Oracle Tracing with Bind Variables

Tom Kyte - Mon, 2016-11-14 20:06
Hi , I enabled tracing on the particular session in oracle database by using "dbms_system.set_sql_trace_in_session" and i am not enabled to trace back the binding variables associated with insert statements . Below is the sample statement: i...
Categories: DBA Blogs

Is it safe to move/recreate alertlog while the database is up and running

Learn DB Concepts with me... - Mon, 2016-11-14 19:00


 Is it safe to move/recreate alertlog while the database is up and running??


It is totally safe to "mv" or rename it while we are running. Since chopping part of it out would be lengthly process, there is a good chance we would write to it while you are editing it so I would not advise trying to "chop" part off -- just mv the whole thing and we'll start anew in another file.

If you want to keep the last N lines "online", after you mv the file, tail the last 100 lines to "alert_also.log" or something before you archive off the rest.



[oracle@Linux03 trace]$ ls -ll alert_*
-rw-r-----. 1 oracle oracle   
488012 Nov 14 10:23 alert_orcl.log

I will rename the existing alertlog file to something
 
[oracle@Linux03 trace]$ mv alert_orcl.log alert_orcl_Pre_14Nov2016.log

[oracle@Linux03 trace]$ ls -ll alert_*
-rw-r-----. 1 oracle oracle 488012 Nov 14 15:42 alert_orcl_Pre_14Nov2016.log
[oracle@Linux03 trace]$ ls -ll alert_*


Now lets create some activity that will need to update the alertlog.

[oracle@Linux03 bin]$ sqlplus / as sysdba

SQL*Plus: Release 12.1.0.2.0 Production on Mon Nov 14 16:23:02 2016

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


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

SQL> alter system switch logfile;

System altered.

SQL> /

lets see if the new alertlog file has been created.[oracle@Linux03 trace]$ ls -ll alert_*
-rw-r-----. 1 oracle oracle    249 Nov 14 16:23 alert_orcl.log
-rw-r-----. 1 oracle oracle 488012 Nov 14 15:42 alert_orcl_Pre_14Nov2016.log
Categories: DBA Blogs

Flashback Database -- 2 : Flashback Requires Redo (ArchiveLog)

Hemant K Chitale - Mon, 2016-11-14 09:03
Although Flashback Logs support the ability to execute a FLASHBACK DATABASE command, the actual Flashback also requires Redo to be applied.  This is because the Flashback resets the images of blocks but doesn't guarantee that all transactions are reset to the same point in time (any one block can contain one or more active, uncommitted transactions, and there can be multiple blocks with active transactions at any point in time).  Therefore, since Oracle must revert the database to a consistent image, it needs to be able to apply redo as well (just as it would do for a roll-forward recovery from a backup).

Here's a quick demo of what happens if the redo is not available.

SQL> alter session set nls_date_format='DD-MON-RR HH24:MI:SS';

Session altered.

SQL> select sysdate, l.oldest_flashback_scn, l.oldest_flashback_time
2 from v$flashback_database_log l;

SYSDATE OLDEST_FLASHBACK_SCN OLDEST_FLASHBACK_T
------------------ -------------------- ------------------
14-NOV-16 22:51:37 7246633 14-NOV-16 22:39:43

SQL>

sh-4.1$ pwd
/u02/FRA/ORCL/archivelog/2016_11_14
sh-4.1$ date
Mon Nov 14 22:52:29 SGT 2016
sh-4.1$ rm *
sh-4.1$

SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount;
ORACLE instance started.

Total System Global Area 1068937216 bytes
Fixed Size 2260088 bytes
Variable Size 750781320 bytes
Database Buffers 310378496 bytes
Redo Buffers 5517312 bytes
Database mounted.

SQL> flashback database to timestamp to_date('14-NOV-16 22:45:00','DD-MON-RR HH24:MI:SS');
flashback database to timestamp to_date('14-NOV-16 22:45:00','DD-MON-RR HH24:MI:SS')
*
ERROR at line 1:
ORA-38754: FLASHBACK DATABASE not started; required redo log is not available
ORA-38762: redo logs needed for SCN 7246634 to SCN 7269074
ORA-38761: redo log sequence 70 in thread 1, incarnation 5 could not be
accessed


SQL>
SQL> l
1 select sequence#, first_change#, first_time
2 from v$archived_log
3 where resetlogs_time=(select resetlogs_time from v$database)
4 and sequence# between 60 and 81
5* order by 1
SQL> /

SEQUENCE# FIRST_CHANGE# FIRST_TIME
---------- ------------- ------------------
60 7245238 14-NOV-16 22:27:35
61 7248965 14-NOV-16 22:40:46
62 7250433 14-NOV-16 22:40:52
63 7251817 14-NOV-16 22:41:04
64 7253189 14-NOV-16 22:41:20
65 7254583 14-NOV-16 22:41:31
66 7255942 14-NOV-16 22:41:44
67 7257317 14-NOV-16 22:41:59
68 7258689 14-NOV-16 22:42:10
69 7260094 14-NOV-16 22:42:15
70 7261397 14-NOV-16 22:42:22
71 7262843 14-NOV-16 22:42:28
72 7264269 14-NOV-16 22:42:32
73 7265697 14-NOV-16 22:42:37
74 7267121 14-NOV-16 22:42:43
75 7269075 14-NOV-16 22:48:05
76 7270476 14-NOV-16 22:48:11
77 7271926 14-NOV-16 22:48:17
78 7273370 14-NOV-16 22:48:23
79 7274759 14-NOV-16 22:48:32
80 7276159 14-NOV-16 22:48:39
81 7277470 14-NOV-16 22:48:43

22 rows selected.

SQL>



Note how the error message states that Redo(Archive)Log Sequence#70 is required but provides a range of SCNs that span Sequence#60 to Sequence#74 !

Bottom Line : Flashback Logs alone aren't adequate to Flashback database.  You also need the corresponding Redo.

Just to confirm that I can continue with the current (non-Flashbacked Database) state (in spite of the failed Flashback)  :

SQL> shutdown;
ORA-01109: database not open


Database dismounted.
ORACLE instance shut down.
SQL> startup
ORACLE instance started.

Total System Global Area 1068937216 bytes
Fixed Size 2260088 bytes
Variable Size 750781320 bytes
Database Buffers 310378496 bytes
Redo Buffers 5517312 bytes
Database mounted.
Database opened.
SQL> archive log list
Database log mode Archive Mode
Automatic archival Enabled
Archive destination USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence 89
Next log sequence to archive 90
Current log sequence 90
SQL> select current_scn from v$database;

CURRENT_SCN
-----------
7289329

SQL>


.Bottom Line : *Before* you attempt a FLASHBACK DATABASE to the OLDEST_FLASHBACK_TIME (or SCN) from V$FLASHBACK_DATABASE_LOG, ensure that you *do* have the "nearby"  Archive/Redo Logs. !
.
.
.
Categories: DBA Blogs

Links for 2016-11-13 [del.icio.us]

Categories: DBA Blogs

Validation procedure

Tom Kyte - Mon, 2016-11-14 01:46
How to write a validation procedure for three conditions which are dependent on each other . first condition will be for country zone in that there will be condition for branch for specific date range .
Categories: DBA Blogs

Chapter 12-user management

Tom Kyte - Mon, 2016-11-14 01:46
I am trying to create a role, but every time i type it in on sql plus, i get an error saying insufficient privileges. I was wondering what am i doing wrong. I have tried typing in CREATE ROLE PURCH_ROLE_1; and CREATE ROLE PURCH_ROLE_1 IDENTIFIED BY c...
Categories: DBA Blogs

Multi Table Insert with Differing Exists on Destination Tables

Tom Kyte - Mon, 2016-11-14 01:46
Is it possible to write a "Insert all into <table_1> ( col_1,col_2,col_3 ) values ( col_a,col_b,col_c) into <table_2> ( col_1,col_2,col_3 ) values ( col_a,col_b,col_c) into...
Categories: DBA Blogs

Data Loading with APEX - How to avoid upload of null values

Tom Kyte - Mon, 2016-11-14 01:46
Hi guys, I'm developing an application wich has a data loading wizard section. Users can upload several CSV files where some of the columns values are checked against a reference (I used Table Lookup for that). Problem is that I just realized tha...
Categories: DBA Blogs

Log File Sync Wait

Tom Kyte - Mon, 2016-11-14 01:46
Hi Tom, 1 ) As per my understanding, Log-File-Sync wait is the duration, after which I receive an acknowledgement that my commit is successful. In my database the only prominent issue I see is huge log-file-sync waits. 2 ) There are 32 cpu and ea...
Categories: DBA Blogs

Populate varchar2 collection from object type

Tom Kyte - Sat, 2016-11-12 13:06
I get the following error when I execute the code listed below - <b>ORA-21700: object does not exist or is marked for delete</b> The example is trivial the sake of illustration, but in real-life, I would be handing sets of several thousands...
Categories: DBA Blogs

Connection fail with differente permissions on a network folder.

Tom Kyte - Sat, 2016-11-12 13:06
Hi, Tom We have a .NET application in a network path and when the user only have read & execute permission on the folder the connection to the Data Base can't be made, but when the user has also the modify permission the connection can be done. ...
Categories: DBA Blogs

historic undo usage by day( last 90 days)

Tom Kyte - Fri, 2016-11-11 18:46
I am trying to size my undo tablespace properly( requirement is 24 hours retention for flashback query), So I need historic undo usage per day for last 90 days. and I am using the query below but I suspect that it is giving me wrong answer. Autoexten...
Categories: DBA Blogs

LAST and COUNT values after VARRAY trimming?

Tom Kyte - Fri, 2016-11-11 18:46
I would like to ask about the values of LAST and COUNT of the varray after operation TRIM. In the documentation is written, that " For varrays, COUNT always equals LAST." [https://docs.oracle.com/cd/B19306_01/appdev.102/b14261/collection_method.htm...
Categories: DBA Blogs

Pages

Subscribe to Oracle FAQ aggregator - DBA Blogs