Tag: osama oracle

Moving Schema to another Tablespace using "move" command.

Posted on by Osama Mustafa in Database
Step One: Create New  tablespace

SQL>Create tablespace datafile ‘\osama.dbf’ size 1G;

Step Two : Check User On which Tablespace


SQL> select owner, tablespace_name from dba_tables where owner=”
 
Step three:Create Script 

sqlplus / as sysdba
spool osama.log

SQL> select ‘alter table ‘ || table_name || ‘ move tablespace mytab ;’ from dba_tables where owner=” ;
 
Spool off ;
 
Step Four : Move Index
SQL> select ‘Alter index ‘ || index_name || ‘ rebuild tablespace mytab ;’ from dba_indexes where owner=” ;
 
Step Five : Check Again

SQL> select owner, tablespace_name from dba_tables where owner=”;

 
 
Thank you 
Osama mustafa

DBID without Select OR RMAN

Posted on by Osama Mustafa in Database
You can retive database id without Using v$view this way is useful when you losing your control file or data file and you need to know your DBID :

connect / as sysdba
SQL> startup nomount;
 
 
SQL> alter system dump datafile '/home/oracle/app/oracle/oradata/orcl/system01.dbf' block min 1 block max 2;
 
System altered.
 
tail -f /home/oracle/app/oracle/diag/rdbms/orcl/orcl/trace/orcl_ora_10680.trc
...
Start dump data block from file /home/oracle/app/oracle/oradata/orcl/system01.dbf minblk 1 maxblk 2
 V10 STYLE FILE HEADER:
        Compatibility Vsn = 186646528=0xb200000
        Db ID=1229390655=0x4947033f, Db Name='ORCL'
... 
SQL>  alter system dump datafile '/home/oracle/app/oracle/oradata/orcl/undotbs01.dbf'  block min 1 block max 2;
 
System altered.
 
tail -f /home/oracle/app/oracle/diag/rdbms/orcl/orcl/trace/orcl_ora_10680.trc
...
Start dump data block from file /home/oracle/app/oracle/oradata/orcl/undotbs01.dbf minblk 1 maxblk 2
 V10 STYLE FILE HEADER:
        Compatibility Vsn = 186646528=0xb200000
        Db ID=1229390655=0x4947033f, Db Name='ORCL'

Thank you
Osama Mustafa

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