DBA Blogs

Database administration of log buffer

Tom Kyte - Mon, 2019-02-11 14:46
2. The airline database is an active database. The admin of the database has configured the redo log buffer to 16M. Assume that, in every 1/2 second 1M of redo log entries are created. LGWR copies those entries from buffer to file when 1M full in 1 s...
Categories: DBA Blogs

Updating Oracle Opatch with AWS Systems Manager Run Command at Scale

Pakistan's First Oracle Blog - Sun, 2019-02-10 02:16
AWS Systems Manager (SSM) is a managed service by AWS, which is used to configure and manage EC2 instances, other AWS resources, and on-premise VMs/servers at scale. SSM frees you up from having ssh or bastion host access to the remote resources.


Pre-requisites of SSM:

The managed instances need to have SSM agent running.
The managed instances need to be assigned an IAM role with policy AmazonEC2RoleforSSM.
The managed insatnces need to have a meaningful tag assigned to them to make it possible to manage them in bulk.

Example:

This example assumes that above pre-requisites are already there. For step by step instructions as how to do that, check this resource (https://aws.amazon.com/getting-started/tutorials/remotely-run-commands-ec2-instance-systems-manager/). This also assumes that all the instances
have been assigned tags like Env=Prod or Env=Dev.

Following is the script update_opatch.sh which was already bootstrapped to the EC2 instance at time of creation in userdata,
so its already present at /u01/scripts/update_opatch.sh

#/usr/bin/env bash -x
ORACLE_SID=`ps -ef | grep pmon | grep -v asm | awk '{print $NF}' | sed s'/pmon_//' | egrep "^[+]"`
export ORAENV_ASK=NO
. oraenv > /dev/null 2>&1
mv $ORACLE_HOME/OPatch $ORACLE_HOME/OPatch.$(date)
curl -T /tmp/ -u test@test.com ftps://
mv /tmp/p6880880_101000_linux64.zip $ORACLE_HOME
cd $ORACLE_HOME
unzip p6880880_101000_SOLARIS64.zip


Now just running following command in Systems Manager will update opatch on all the managed instances with tag Prod.

aws ssm send-command --document-name "AWS-RunShellScript" --comment "update_opatch.sh" --parameters commands=update_opatch.sh Key=tag:Env,Values=Prod


Categories: DBA Blogs

Unlock user shell script for Oracle

Tom Kyte - Fri, 2019-02-08 13:26
Hello, We have so many user ID locks from the front-end users. We need to provide a shell script to the user which they can run on OS level and unlock the ID themselves when the DBA is not present. The sys password needs to be decrypted during ...
Categories: DBA Blogs

Join all_tables to a normal table

Tom Kyte - Fri, 2019-02-08 13:26
Hi Is it possible to join ALL_TABLES and normal table? I like make dynamic SQL and select columns that shown after selected from list. Something like this: <b>select * from table1 a where a.columns in (SELECT DISTINCT A...
Categories: DBA Blogs

Select from view with where clause

Tom Kyte - Fri, 2019-02-08 13:26
Hi, I cant find so I'd like to ask: I have view myView: <code>create view myView as select * from myTable;</code> and what is the difference between <code>select * from myTable where myTable.id = 12345;</code> and <code>select * from ...
Categories: DBA Blogs

Guidelines for chossing Database Vs Schema. Also One Schema on multiple schema on Oracle Exadata

Tom Kyte - Fri, 2019-02-08 13:26
Hi, This is my first experience with Oracle Exadata. Trying to provide solution for client. 1) Do you have any guidelines for when to go for database against schema. Pros and Cons 3) We have to load data from 100+ sources so any guidelines f...
Categories: DBA Blogs

Log Recovery in Dataguard alert log

Tom Kyte - Fri, 2019-02-08 13:26
Hi all, I am using Oracle 11g Active Dataguard in maximum performance mode. I notice 2 general pattern of log recovery in the database alert log. ===================== Pattern1 <code>RFS[7]: Selected log 11 for thread 2 sequence xxxxx Arc...
Categories: DBA Blogs

Database migration

Tom Kyte - Fri, 2019-02-08 13:26
Hi All, We have 70 TB Database sitting in Solaris Sparc . We are migrating oracle from Solaris to RHEL 7. i am looking for best approach for moving data from Solaris to rhel with minimal downtime. Could you please let me know what would be ...
Categories: DBA Blogs

“Oracle Indexing Internals and Best Practices” Seminar – Berlin 8-9 May: DOAG Website

Richard Foote - Fri, 2019-02-08 00:55
Just a short note to say that DOAG have now a registration page for my upcoming “Oracle Indexing Internals and Best Practices” seminar running in Berlin, Germany on 8-9 May 2019. For all the details regarding this acclaimed educational experience and how to book your place, please visit: https://www.doag.org/de/eventdetails?tx_doagevents_single[id]=577320 Please mention you heard this seminar […]
Categories: DBA Blogs

Pivot on a query with multiple data columns

Tom Kyte - Thu, 2019-02-07 19:06
I have an SQL query written against view sys.dba_audit_session to retrieve initial logon and final logoff times for users across a range of days. So the basic query is <code> SELECT DISTINCT username, action_name, ...
Categories: DBA Blogs

Parsing through a Long Character with 255 characters and Stripping out Words

Tom Kyte - Thu, 2019-02-07 19:06
Hi, I have a long character field for every one of my 10,000 rows and I need to write a PL/SQL that goes through every row a parses this long field for any WORD (Mix of Alpha and Numeric) up to 6 characters and spits it out and Save it to a differ...
Categories: DBA Blogs

Generating output file using spool is very slow when compared to UTL_FILE

Tom Kyte - Thu, 2019-02-07 19:06
Hello Team, We have to extract huge data from EBS tables. We have tried two approaches Approach1 (Using SQL and Spool command) --------- We have created a script to generate the output using SPOOL command but this is taking 12 hours of time ...
Categories: DBA Blogs

Identify when a function is executed in a SQL Query or in a PL/SQL procedure

Tom Kyte - Thu, 2019-02-07 19:06
Is there any way to identify when a pl/sql function is executed in SQL Query and when is executed in a procedure or PL/SQL anonymous block? (I don't want to pass any parameter for manual identification) The main reason I need that is when a functi...
Categories: DBA Blogs

How to convert ROWS to COLUMNS

Tom Kyte - Thu, 2019-02-07 19:06
Hi, I have data as given below in table <code>create table chart( SERIES varchar2(10), LABEL date, VALUE number(5,2) ); insert into chart values('A',to_date('13-DEC-18','DD-MON-YY'),83.55); insert into chart values('B',to_date('13...
Categories: DBA Blogs

Partner Webcast – Connect and Extend SaaS with Oracle Autonomous Integration Cloud Service

Oracle Autonomous Integration Cloud helps to simplify application integration and process automation to let you rapidly connect SaaS and on-premises application across, both Oracle and non-Oracle,...

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

Index creation slow - lots of direct path write/temp waits

Tom Kyte - Thu, 2019-02-07 00:46
I have a table on which I'm trying to index a column. This happens as part of an ETL process, however I have run this process directly to reproduce. The table in question has about 15M rows and I am trying to index a single column. create index x...
Categories: DBA Blogs

Created file using UTL_FILE. How to prevent file from being picked up before it has been fully created

Tom Kyte - Thu, 2019-02-07 00:46
We have an integration where the file is being created in oracle ebs using utl_file utility. The file is placed on the server where biztalk pings every 60 sec and picks it up. In one scenario the file was picked up before it was written out resulting...
Categories: DBA Blogs

When are partition statistics beneficial?

Tom Kyte - Thu, 2019-02-07 00:46
Tom, When are partition-level statistics used by the optimizer in 9iR2 and 10g? The only thing I've found in the docs so far (trying to avoid RTFM) is that they're used when a query fit the form SELECT ... FROM MYTABLE PARTITION (PTN_NAME). Specif...
Categories: DBA Blogs

oracle database parameters

Tom Kyte - Thu, 2019-02-07 00:46
Dear Tom, I'm trying to test the oracle 12C reporting system, in which i will dump the oracle database transaction database backup to report system. For better performance of completed reporting system, what are the parameters i need to set perfe...
Categories: DBA Blogs

Pages

Subscribe to Oracle FAQ aggregator - DBA Blogs