Copy Command in Sqlplus

Today as any Database administrator i wake up on new task , they want import some tables from Developer environment to Production , So I used Copy Command .

its usual used for copy tables from database to Another  , but you have to know this way is very useful specially if you have long Data type in your tables , i will explain it , and Define Two Database in your TNS.

create table osama as select * from long_table ;

You will Get this error :

ERROR at line 1:

ORA-00997: illegal use of LONG datatype

copy from user1/password@orcl  to user2/password@orcl2 create

using ;

The Output :

Array fetch/bind size is 30. (arraysize is 30)
Will commit when done. (copycommit is 0)
Maximum long size is 40. (long is 40)

Table osama created.

Simple !!!

Useful Link : 
1-Oracle Document .
2-Examples. 

Thank you 
Osama Mustafa 

Oracle Databases from 11.2.0.1 to 11.2.0.2

Basic Steps : 

1. Install 11.2.0.2 into a separate ORACLE_HOME.
2. Take a full backup of the database.
3. Always shutdown the database cleanly

After Installing 11.2.0.2 , Go to 11.2.0.1 database and run :

Connect as sys
SQL> spool pre_upgrade.log
SQL> @ORACLE_HOME/rdbms/admin/utlu112i.sql
SQL> spool off 

 To check Data Dictionary 

Connect as sys
SQL> EXECUTE dbms_stats.gather_dictionary_stats;
Note: Ignore if dbms_stats.gather_dictionary_stats was run once.

To Fix invalid Object in the database :

connect as sys
SQL> @$ORACLE_HOME/rdbms/admin/utlrp.sql

Run pre-upgrade diagnostic utility dbupgradiag.sql

$ cd $ORACLE_HOME/rdbms/admin
$ sqlplus “/ as sysdba”
SQL> spool pre_dbupgdiag.log
SQL>@dbupgdiag.sql
SQL>spool off 

 if the dbupgdiag.sql script reports any invalid objects, run $ORACLE_HOME/rdbms/admin/utlrp.sql 

Configure the 11.2.0.1 database environment for 11.2.0.2 Upgrade 

Copy the following Files to New Home 11.2.0.2 :

  1. Spfile/Pfile/init.ora
  2. orapwd
  3. tnsname.ora
  4. Listener.ora
  5. OC4J Home (ORACLE_HOME/oc4j/j2ee/OC4J_DBConsole_) Only if you have enterprise manager

Set The environment to 11.2.0.2

export ORACLE_HOME=/u01/app/oracle/prouct/11.2.0.2
export ORACLE_SID=ORCL

Note : Replace the Path , LD_Library_Path to new Home .

Upgrade 11.2.0.1 database to 11.2.0.2

sqlplus  / as sysdba
 spool upgrade.log
startup upgrade
set echo on
@?/rdbms/admin/catupgrd.sql;
spool off
shutdown immediate

Restart the database in normal mode

sqlplus  / as sysdba
 @/opt/app/oracle/product/11.2.0.2/rdbms/admin/catuppst.sql;
@/opt/app/oracle/product/11.2.0.2/rdbms/admin/utlrp.sql;
select comp_name,version,status from dba_registry;
select owner,count(*) from dba_objects where status != ‘VALID’ group by owner;

Change the compatibility parameter

Change the compatible parameter and restart the database.

SQL> alter system set compatible=’11.2.0.2.0′ scope=spfile;
SQL> shutdown immediate;
SQL> startup;

And Please Make Sure the listener has been started from the new home not the old one .

I will Post Upgrade to 11.2.0.3 “Enshalla”

Thank you
Osama Mustafa

ORA-02021: DDL operations are not allowed on a remote database

*Cause: An attempt was made to use a DDL operation on a remote database.
 For example, “CREATE TABLE tablename@remotedbname …”.
*Action: To alter the remote database structure, you must connect to the
remote database with the appropriate privileges.

But you can avoide this  Using :

exec dbms_utility.exec_ddl_statement@db_link(‘your statment’);


Thank you
Osama Mustafa

Instance Caging

Instance Caging

Sometimes When you are doing some testing on one machine and have more than one instance with limited hardware resource , Oracle let control that resource by caging its new feature in 11g its method to cage or bound the instance to use a certain number of cpu instead to take all available CPU simple way :

Alter system set CPU_Count = 2 

Just as note this method work with Resource Manager so you need to enable it , and create resource manager plan first before doing Instance Caging .

Instance Caging Benefits :

  1. Useful when you are using multiple instance .
  2. Allowing CPU , Resource allocation be done effectively .
  3. Control CPU Consumption of each Instance .

Thank you
Osama Mustafa

dim-00014

While You are trying to install Oracle Database On Windows 2008 R2 or any other Microsoft Os with user not administrator you will get

DIM-00014: Cannot open the Windows NT Service Control Manager.
O/S-Error: (OS 5) Access is denied.
 
Solution :

Run DBCA as administrator .

Click on start button -> All programs -> Accessories -> right click the command prompt icon > choose run as administrator -> 
invoke dbca in the commandline or oradim can also be used.
 
Thank you 
Osama mustafa 

ORA-00322 ORA-00312

The Above error Appear In My Alert Log , I have Single Test DB , Sometimes the same error appear In Standby Database you don’t follow the same Produce , i didn’t find any document related to this error for single database so hope this will be useful 

  ORA-00322: log 3 of thread 1 is not current copy
ORA-00312: online log 3 thread 1 :
‘/u01/app/oracle/flash_recovery_area/ORCL/onlinelog/o1_mf_3_79fz3gx8_.log’
ORA-00322: log 3 of thread 1 is not current copy
ORA-00312: online log 3 thread 1 :
‘/u01/app/oracle/oradata/ORCL/onlinelog/o1_mf_3_79fz3g1v_.log’

Solution

Step-One :
conn / as sysdba

shutdown immediate ;

startup mount ;

Step-Two :
Recover database using backup controlfile;
 Note :
Provide path where your redo log file locate , in My case “/u01/app/oracle/oradata/ORCL/”

Step-three:
alter database open resetlogs;
shutdown immediate;
startup;

Thank you
Osama Mustafa

Get Information About Executed Sql

Check The User who Run Sql :

select sid,
to_char(logon_time,'MMDDYYYY:HH24:MI') logon_time,
username,
type,
status,
process,
sql_address,
sql_hash_value
from v$session
where username is not null

 Active SQL:

select sesion.sid,
sesion.username,
optimizer_mode,
hash_value,
address,
cpu_time,
elapsed_time,
sql_text
from v$sqlarea sqlarea, v$session sesion
where sesion.sql_hash_value = sqlarea.hash_value
and sesion.sql_address = sqlarea.address
and sesion.username is not null

Show Full  SQL Executing For Active Session : 

select sesion.sid,
sql_text
from v$sqltext sqltext, v$session sesion
where sesion.sql_hash_value = sqltext.hash_value
and sesion.sql_address = sqltext.address
and sesion.username is not null
order by sqltext.piece

  

 

Show Last executed SQL :

select sesion.sid,
sql_text
from v$sqltext sqltext, v$session sesion
where sesion.sql_hash_value = sqltext.hash_value
and sesion.sql_address = sqltext.address
and sesion.username is not null
order by sqltext.piece


 

Flashback data Archive

What Is Flashback Data Archive ?

is has another name called Oracle total Recall , and its ability to track and store all transactional changes to a table over its lifetime.


How Can I Use it ?

Step One

create default flashback archive on an existing tablespace.

Suntax :

create flashback archive  default tablespace retention ;

Example :

 conn / a sysdba
SQL > create flashback archive default Osama tablespace User retention 1 year;

Step Two :

Create Table to Store Information inside it

Conn Osama/Osama
Sql > Create table Store as select * from dba_objects; — For example 

Step Three:

We Need to enable flash archive on same table by

Sql > alter table Store flashback archive;

-You need to check time by :
select systimestamp from dual;
– Check Created Point By
select timestamp_to_scn(systimestamp) from dual;

The Above is the main Steps To Enable FlashBack data Archive.

Thank you
Osama Mustafa

Example Links :
1-Recall Example 
2-Recall Example 
3-Recall Example
4-Using Flashback Data Archive  

Drop DB Control Repository

I know that i post this topic before , you use emca to drop dbcontrol but what if this command fails 

what should i do ? I post This Topic to show second way to drop dbcontrol let Start :
 
emca
should be used to drop DB Control repository as follows:
emca -deconfig dbcontrol db -repos drop

Steps :

1.Shutdown database

 SHUTDOWN IMMEDIATE;

2.Remove EM job

EXEC sysman.emd_maintenance.remove_em_dbms_jobs;

3.Revoke DBA privilages from SYSMAN user

REVOKE dba FROM sysman;

4.Run 

DECLARE
  CURSOR c1 IS
  SELECT owner, synonym_name name
  FROM dba_synonyms
  WHERE table_owner = 'SYSMAN';
BEGIN
  FOR r1 IN c1 LOOP
    IF r1.owner = 'PUBLIC' THEN
      EXECUTE IMMEDIATE 'DROP PUBLIC SYNONYM '||r1.name;
    ELSE
      EXECUTE IMMEDIATE 'DROP SYNONYM '||r1.owner||'.'||r1.name;
    END IF;
  END LOOP;
END;
/

 5.Drop MGMT_VIEW user.

DROP USER MGMT_VIEW CASCADE;

6.Drop MGMT_VIEW role

DROP ROLE mgmt_user;

7.Drop SYSMAN user

DROP USER sysman CASCADE;

8.Disable restricted mode

ALTER SYSTEM DISABLE RESTRICTED SESSION;

 Thank you
Osama

    Database Vulnerabilities

    in this post you will find 10 ways make your database easy to hack ,Special thank for Team Shatter , I post online Article about Database security before and as reminder read it again it . LETS START


    1-default-blank-and-weak-username-and-passwords

    We All know What we mean by Defaults and weak password (which not complex)

    To create a strong password:

    • Don’t use words that can be easily guessed or found in the dictionary
    • Use a combination of letters, numbers and characters
    • Create a complex sentence instead of a word
    • Do not share your password with anyone or write it down and leave it in your desk drawer

    2-sql-injections-in-the-database

    sql injection : attack vector in the DBMS through Web applications because of a failure to sanitize user inputs.
    SQL Injection in the DBMS exploits passing SQL commands as a parameter of a function or stored procedure. This will then execute the malicious SQL commands in the context of the component that provides the called function. This is often done using components with system or admin privileges resulting in privilege escalation.

    3-excessive-user-and-group-privileges

    avoid extensive user and group privileges:

    • Map Job Functions to Privileges on IT assets
    • Never Assign Privileges Directly to Guest Accounts or Public
    • Untangle The Web of User Entitlements
    • Implement Compensating Controls for What You Can’t Fix

    4-Unnecessary Enabled Database Features.



    These features provide database application developers with a lot more power when working with a DBMS.  The flipside of the coin is, the more power you give a developer, the more attack vectors you potentially expose to the bad guys.

    5-check-your-database-configurations with your company Goal.

    6-Buffer Overflows 

    A buffer overflow is when the input to a function contains more data than the input buffer can hold. If the size of the input is not checked during the copy to that buffer, adjacent memory that is used for other purposes might get overwritten.

     In most cases, this will be more or less random and can lead to unpredictable behavior, like crashing the server. However, if an attacker is able to also change the code execution pointer to the location of the overwritten memory, it is possible to execute any kind of malicious code using the context of the DBMS process.

     This could lead to a potential total compromise of the system, resulting in loss of sensitive information and overall security.
    To protect against these types of attacks, it is important to always keep your DBMS updated with the latest security patches available from the vendor, as well as monitoring for known attack signatures.

     7-Privilege Escalation 

    privilege escalation attack is when the attacker is exploiting a known vulnerability in a DBMS that allows a user account with restricted privileges to execute instructions or query data that that typically requires higher privileges. Thus unlocking the locks in the candy store.

    There are different common vulnerabilities that allow for privilege escalation. Sometimes misusing a function that runs under a sysdba, sa or similar security context. In other cases it is done by exploiting vulnerabilities that allow a low-privileged account to grant itself more rights.

    To protect against these types of attacks, it is important to always keep your DBMS updated with the latest security patches available from the vendor, as well as continuously monitor for known attack signatures.

    8-Denial of Service Attack DoS

    Think of a washing machine. The more clothes you put in it, the more work it needs to do. If you overfill it with clothes, it gets overwhelmed and stops working.

    The same thing happens with a Denial of Service attack. It creates so much traffic on a site, a server or even sections of the internet that it cannot function and shuts down.

    The most infamous DoS involving database servers was in 2003 when a computer worm called the SQL Slammer compromised more than 75,000 servers and slowed internet traffic to a halt.

    9-Unpatched Databases

     Many organizations don’t implement patches right away – some even wait a year or more. The most common excuses are the downtime involved with implementing patches and the time involved with testing these patches to make sure they don’t affect the production software. Whatever the excuse may be, organizations should use database activity monitoring to manage the gap between patches on its databases containing critical information PII data.

    10-Unencrypted sensitive data


    Encryption is an important part of housing sensitive data. Network traffic should also be encrypted to ensure that the passwords used to access sensitive, critical data cannot be seen by traffic.

    Any information that goes over the network or stored in the database should be encrypted and kept from prying eyes. Some network configurations and database management systems might allow for critical information to be sent in clear text. To ensure this doesn’t occur, make sure you have the latest version of software and turn off text indexing.

    Remember to back to my online article .
    Thank you

    Useful Link :
    1-Team Shatter

    Osama Mustafa