Author: Osama Mustafa

Osama considered as one of the leaders in Cloud technology, DevOps and database in the Middle-East. I have more than ten years of experience within the industry. moreover, certfied 4x AWS , 4x Azure and 6x OCI, have also obtained database certifications for multiple providers. In addition to having experience with Oracle database and Oracle products, such as middle-ware, OID, OAM and OIM, I have gained substantial knowledge with different databases. Currently, I am architecting and implementing Cloud and DevOps. On top of that, I'm providing solutions for companies that allow them to implement the solutions and to follow the best practices.

DDL With the WAIT Option

Posted on by Osama Mustafa in Database
The DDL_LOCK_TIMEOUT parameter indicates the number of seconds a DDL command should wait for the locks to become available before throwing the resource busy error message

For Example : 

CREATE TABLE lock_tab (id  NUMBER);
INSERT INTO lock_tab VALUES (1);
 
ALTER SESSION SET ddl_lock_timeout=30;
 
ALTER TABLE lock_tab ADD (description  VARCHAR2(50)); 

ALTER TABLE lock_tab ADD (
            *
ERROR at line 1:
ORA-00054: resource busy and acquire with NOWAIT specified or timeout expired

 Ref :
Oracle_base

Thank you
Osama mustafa

Create Backup to another Location

Posted on by Osama Mustafa in Database

run {
ALLOCATE CHANNEL disk1 DEVICE TYPE DISK FORMAT ‘/u01/Rman/%U’;
ALLOCATE CHANNEL disk2 DEVICE TYPE DISK FORMAT ‘/u02/Rman/%U’;
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO ‘u02/rman/%F’;
backup incremental level 0 database;
release channel disk1;
release channel disk2;

sql ‘alter system archive log current’;
ALLOCATE CHANNEL disk1 DEVICE TYPE DISK FORMAT ‘/u01/Rman/LOG_%t_%s_%p_%U’;
ALLOCATE CHANNEL disk2 DEVICE TYPE DISK FORMAT ‘/u02/Rman/LOG_%t_%s_%p_%U’;
backup archivelog all DELETE INPUT;
release channel disk1;
release channel disk2;

CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK CLEAR;
}

Thank you
Osama mustafa

manual record to the Alert.log

Posted on by Osama Mustafa in Database
Oracle Give us an Option to record our own error in alertlog.log But How can we Do that :

The procedure kdswrt in dbms_system package allows us to write own messages in the alert log / trace files or both.It receives two parameters:

  1. –   A number that indicates where do we want to write our message:

  •      Writing to a TRACE file.
  •      Writing to the Alert.log file.
  •      Writing to both of them.

       2.   A text string (the message itself).

How to Use it  :

exec dbms_system.ksdwrt(2, ‘ORA-10200: Error in Database.);

 Sun Sep 29 10:00:57 2010
Thread 1 advanced to log sequence 7615 (LGWR switch)
Current log# 1 seq# 7615 mem# 0: u01/app/oracle/product/11.2.0/dbhome_1/REDO01.LOG
Sun Sep 29 11:10:15 2010
ORA-10200: Error in Database.
Sun Sep 29 11:10:15 2010
Thread 1 advanced to log sequence 7616 (LGWR switch)
Current log# 2 seq# 7616 mem# 0: u01/app/oracle/product/11.2.0/dbhome_1/REDO02.LOG
Sun Sep 29 10:00:57 2010
Thread 1 advanced to log sequence 7615 (LGWR switch)
Current log# 1 seq# 7615 mem# 0: u01/app/oracle/product/11.2.0/dbhome_1/REDO01.LOG

 It is a very useful feature, as we can use it in our PL/SQL procedures inside the exception handling section or to indicate the procedure’s progression.

As i mention before my blog you can use X$DBGALERTEXT to check alert log via sqlplus (11g).

Thank you
Osama Mustafa

Check Rman Backup/Validate

Posted on by Osama Mustafa in Database
There’s More than One Way to Do it :

Check One :

To check Datavase Backup

RMAN > Restore Validate Database ;

Check Two : 

Check Spfile

RMAN > restore validate spfile to ‘c:\temp\spfile.ora’;

Check Three :

Test Control File

RMAN> restore validate controlfile to ‘c:\temp\control01.ctl’;

Check Four :

Test Archive log

RMAN> list backup of archivelog all;

 or

RMAN> list backup of archivelog all completed after ‘sysdate -1’;

Then

RMAN> restore validate archivelog from sequence XXX until sequence XXX; 

Thank you
Osama Mustafa

12c new features

Posted on by Osama Mustafa in Database
Tom Kyte , Talk about 12c new features LETS START :

1) “With” clause can define PL/SQL functions

2) Improved defaults, including Default col to a sequence or “default if (on) null”.  Or always use a generated as an identity (with optional sequence def info).  Or Metadata-only defaults (default on an added column). 

3) Bigger varchar2, nvarchar2, raw -up to 32K.  But follows rules like LOB, if over 4K will be out of line. (max_SQL_String_Size init param)

4) TopN and Pagenation queries using the ‘OFFSET’ clause + optional ‘FETCH next N rows’ in SELECT.  Eg: SELECT … FROM t ORDER BY y FETCH FIRST 5 ROWS

5) Row pattern matching using the “MATCH_RECOGNIZE” clause.  Gonna take a while to get this one.

6) Partitioning improvements including ASYNC Global Index maintenance (includes new jobs to do work ‘later’), cascade truncate & exchange, multi ops in a single DDL, online partition moves (no RDBMS_REDEFINITION), “interval + reference” partitioning.

7) Adaptive execution plans, which sets thresholds and allows execution plans to switch if threshold is exceeded.  (Also ‘gather_plan_statistics’ hint.)  Shown by ‘Statistics Collector’ steps in trace/tkprof.

8) Enhanced statistics. Dynamic sampling goes to ‘eleven’, turning it persistent.  New histograms: hybrid (for more than 254 distinct values, instead of height-balanced) and top.  Stats gathered on data loads automatically. (By the way, don’t regather stats if not needed.)  Session private statistics for GTTs. 

9) UNDO for temporary objects, managed in TEMP, which eliminates REDO on the permanent UNDO. (ALTER SESSION/SYSTEM SET TEMP_UNDO_ENABLED=TRUE/FALSE)

10) Data optimization, or Information Lifecycle Management, which detects block use – hot, medium, dormant – and allows policies in table defintion (new ILM clause) to compress or archive data after time.

11) “transaction Guard’ to preserve commit state, which includes TAF r/w transfer and restart for some types of transactions.

12) pluggable databases!  Implications too numerous to list right now.  Suffice it to say, huge resource improvements, huge consolidation possibilities.  Looking forward to reality.

Thank you
Osama mustafa

emagent : Memory 0x0 encountered

Posted on by Osama Mustafa in Application
When you try to login to EM the following screen appear :

-bash-3.00$ emctl stop dbconsole
Oracle Enterprise Manager 11g Database Control Release 11.1.0.7.0
Copyright (c) 1996, 2008 Oracle Corporation.  All rights reserved.

https://rgpdb1.rg.com:1158/em/console/aboutApplication

Stopping Oracle Enterprise Manager 11g Database Control ...
 ...  Stopped.
-bash-3.00$
-bash-3.00$
-bash-3.00$
-bash-3.00$ emctl start dbconsole
Oracle Enterprise Manager 11g Database Control Release 11.1.0.7.0
Copyright (c) 1996, 2008 Oracle Corporation.  All rights reserved.

https://rgpdb1.rg.com:1158/em/console/aboutApplication

Starting Oracle Enterprise Manager 11g Database Control ............................................................................................. failed.
------------------------------------------------------------------
Logs are generated in directory /pdb01/oraprod/db/tech_st/11.1.0/rgpdb1_rgprd1/sysman/log
 
 
Solution:

Check the following processes and kill them :

ps -ef | grep emagent
ps -ef | grep DEMS

-bash-3.00$ kill -9 PID
-bash-3.00$ kill -9 PID

 Then

-bash-3.00$ emctl stop dbconsole
-bash-3.00$ emctl start dbconsole 

 
 Thank you 
Osama Mustafa

Oracle RAC 12c: New Features

Posted on by Osama Mustafa in Database
1. Application Continuity

2. Oracle Flex ASM
With this feature, database instances use remote ASM instances. 

3. Oracle ASM Disk Scrubbing
Checks for logical data corruptions and repair them automatically.

4. Enhancements to Policy-based Databases
Actively utilizes different sized servers

5. What – if analysis for server pool management

6. Standardized deployment and patching 
Introducing GHS, rapid home provisioning and gold images

7. A new “ghctl” command for better patching

8. Oracle Utility Cluster

9. Dynamic IP Management and name resolution made easy

10. IPv6 Based IP Addresses Support for client connectivity

11. Multi-purpose Installation

12. Oracle installer will run Fix-up scripts & “root.sh” scripts across nodes. You don’t have to run the scripts manually on RAC nodes.

Thank you Asif Momen .

Thank you 
Osama Mustafa 

ORA-02020: too many database links in use

Posted on by Osama Mustafa in Database
Error :

ORA-02020: too many database links in use

Solution :

Increase the open_links and open_links_instance parameter in the DB . Bounce Database

Or

SQL>alter session close database link “link name”;

Thank you
Osama mustafa

ORA-19527: physical standby redo log must be renamed

Posted on by Osama Mustafa in Database
In Standby Database Alert log i Found the following :

Attempt to start background Managed Standby Recovery process (neonprd)
MRP0 started with pid=31, OS id=5623962
Mon Oct 8 09:12:10 2012
MRP0: Background Managed Standby Recovery process started (neonprd)
Managed Standby Recovery not using Real Time Apply
parallel recovery setup failed: using serial mode
Mon Oct 8 09:12:17 2012
Waiting for all non-current ORLs to be archived…
Mon Oct 8 09:12:17 2012
Errors in file /oracle/admin/neonprd/bdump/neonprd_mrp0_5623962.trc:
ORA-00367: checksum error in log file header
ORA-00318: log 1 of thread 1, expected file size 512 doesn’t match 512
ORA-00312: online log 1 thread 1: ‘/oracle/redolog/neonprd/redo01a.log’
Clearing online redo logfile 1 /oracle/redolog/neonprd/redo01a.log
Clearing online log 1 of thread 1 sequence number 267655
Mon Oct 8 09:12:17 2012
Errors in file /oracle/admin/neonprd/bdump/neonprd_mrp0_5623962.trc:
ORA-19527: physical standby redo log must be renamed
ORA-00312: online log 1 thread 1: ‘/oracle/redolog/neonprd/redo01a.log’
Clearing online redo logfile 1 complete
Media Recovery Waiting for thread 1 sequence 268189
Mon Oct 8 09:12:17 2012

 Solution :

Solution for this Error is so Simple , This Problem Occur when Database parameter “log_file_name_convert” is not set.

Alter system set log_file_name_convert= Scope=Spfile ;

Also You can check :
ORA-19527 reported in Standby Database when starting Managed Recovery [ID 352879.1]

Thank you
Osama Mustafa

ORA-00313 ,ORA-00312 open failed for members of log group

Posted on by Osama Mustafa in Database
ORA-00313: open failed for members of log group 206 of thread 2
ORA-00312: online log 206 thread 2: ‘/u05/oradata/stdby/’
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory

 Solution :

1-Mount the database.

SQL>STARTUP MOUNT
Database mounted.

2-Check the status of the logile to see whether it is current.

SELECT STATUS FROM V$LOG WHERE GROUP#=2;
STATUS
—————-
CURRENT

 Note : If the status did not CURRENT then simply drop the log file by:

 

SQL>ALTER DATABASE DROP LOGFILE GROUP 2;

3-Add new Redo Log by :

SQL>ALTER DATABASE ADD LOGFILE GROUP 4 ‘u03/App/Oradata/redo3.log’ SIZE 50M;

4-Do Recover and Open Database Resetlog :


SQL>RECOVER DATABASE UNTIL CANCEL;

SQL>ALTER DATABASE OPEN RESETLOGS; 

Enjoy
Osama Mustafa