Tag: Oracle Blog .

Duplicate Database Using RMAN / Clone Database

Posted on by Osama Mustafa in Database
Step By Step : Cloning Database On the Same Server / Using Rman Duplicate From Active Database

The Below Link Contain Document Describe Step by Step how to Duplicate Database Using RMAN ,

Cloning Database / Duplicate Database Using RMAN

Enjoy

Thank you
Osama mustafa

Set Listener Password

Posted on by Osama Mustafa in Database
In Oracle 10g and above, the listener is secured by default, and only user who started the listener can run the commands.

In order to set password on listener, follow the steps:

1) LSNRCTL> set current_listener
2) LSNRCTL> change_password
Old password:
New password: yourpassword
Reenter new password: yourpassword
3) LSNRCTL> save_config
4) LSNRCTL> status

Now Any One Want Start Administrating Listener Will be Asked for Password , After This you can manager Your Listener .

As the another user, enter the password to administer the listener:

1) LSNRCTL> set password
Password: yourpassword
2) LSNRCTL> stop

Thank you
Osama mustafa

Step By Step To Migration ASM to File System

Posted on by Osama Mustafa in Articles, Database
There’s More than One Way For this Migration you can use what you want

Lets Get The Database Data Files :


1-See your Database Files :

SQL>select file_name from dba_data_files;

 +DATA/test/datafile/undotbs1.301.697649965
+DATA/test/datafile/sysaux.300.697649963
+DATA/test/datafile/system.299.697649963
+DATA/test/datafile/users.302.697649965
+DATA/test/datafile/users.309.697650601

select tablespace_name,count(file_name) from dba_data_files group by tablespace_name ORDER BY 2

Use the above query to see how many data files under tablespace .

Now After see the DataFile , we need to create new Directory to copy the data file to it .
  mkdir /app/oracle/TEST


2- We Will Create Our Own Script And Copy our Database File to our new location :

sqlplus / as sysdba

set lines 120 pages 0

select ‘copy datafile ‘||file_id||’ to ‘||”’/app/oracle/TEST/’||substr(file_name,21,instr(file_name,’.’)-21)||’_01.dbf”’||’;’ from dba_data_files order by 1;

copy datafile 1 to ‘/app/oracle/TEST/system_01.dbf’;

copy datafile 2 to ‘/app/oracle/TEST/sysaux_01.dbf’;

copy datafile 3 to ‘/app/oracle/TEST/undotbs1_01.dbf’;

copy datafile 4 to ‘/app/oracle/TEST/users_01.dbf’;

copy datafile 5 to ‘/app/oracle/TEST/users_02.dbf’;

shutdown immediate;

startup mount;

exit

rman target /

copy datafile 1 to ‘/app/oracle/TEST/system_01.dbf’;

copy datafile 2 to ‘/app/oracle/TEST/sysaux_01.dbf’;

copy datafile 3 to ‘/app/oracle/TEST/undotbs1_01.dbf’;

copy datafile 4 to ‘/app/oracle/TEST/users_01.dbf’;

copy datafile 5 to ‘/app/oracle/TEST/users_02.dbf’;

exit

sql / as sysdba

select ‘alter database rename file ”’||file_name||”’ to ‘||”’/app/oracle/TEST/’||substr(file_name,21,instr(file_name,’.’)-21)||’_01.dbf”’||’;’ from dba_data_files order by 1;

alter database rename file ‘+DATA/test/datafile/sysaux.300.697649963’ to ‘/app/oracle/TEST/sysaux_01.dbf’;

alter database rename file ‘+DATA/test/datafile/system.299.697649963’ to ‘/app/oracle/TEST/system_01.dbf’;
alter database rename file ‘+DATA/test/datafile/undotbs1.301.697649965’ to ‘/app/oracle/TEST/undotbs1_01.dbf’;
alter database rename file ‘+DATA/test/datafile/users.302.697649965’ to ‘/app/oracle/TEST/users_01.dbf’;

alter database rename file ‘+DATA/test/datafile/users.309.697650601’ to ‘/app/oracle/TEST/users_02.dbf’;

 
 3- This not the finish , there’s some file still On A, Check it by :

open database

select name from v$controlfile
union
select name from v$tempfile
union
select member from v$logfile
union
select filename from v$block_change_tracking;

NAME
——————————————————————————–
+DATA/test/controlfile/current.303.697650047
+DATA/test/onlinelog/group_1.304.697650049
+DATA/test/onlinelog/group_2.305.697650049
+DATA/test/onlinelog/group_3.306.697650049
+DATA/test/tempfile/temp.307.697650095
+FRA/test/controlfile/current.260.697650047
+FRA/test/onlinelog/group_1.261.697650049
+FRA/test/onlinelog/group_2.262.697650049
+FRA/test/onlinelog/group_3.263.697650051

alter database rename file ‘+DATA/test/datafile/sysaux.300.697649963’ to ‘/app/oracle/TEST/sysaux_01.dbf’;

alter database rename file ‘+DATA/test/datafile/system.299.697649963’ to ‘/app/oracle/TEST/system_01.dbf’;

alter database rename file ‘+DATA/test/datafile/undotbs1.301.697649965’ to ‘/app/oracle/TEST/undotbs1_01.dbf’;

alter database rename file ‘+DATA/test/datafile/users.302.697649965’ to ‘/app/oracle/TEST/users_01.dbf’;

alter database rename file ‘+DATA/test/datafile/users.309.697650601’ to ‘/app/oracle/TEST/users_02.dbf’;

ALTER DATABASE ADD LOGFILE GROUP 4 (‘/app/oracle/TEST/redo_04a.dbf’,’/app/oracle/TEST/redo_04b.dbf’) size 50M;

ALTER DATABASE ADD LOGFILE GROUP 5 (‘/app/oracle/TEST/redo_05a.dbf’,’/app/oracle/TEST/redo_05b.dbf’) size 50M;

ALTER DATABASE ADD LOGFILE GROUP 6 (‘/app/oracle/TEST/redo_06a.dbf’,’/app/oracle/TEST/redo_06b.dbf’) size 50M; 

4-the Last Step for Control file and Spfile :


SQL>show parameter control
control_files 
————————–
+DATA/test/controlfile/current .303.697650047
SQL > Create Pfile=” from spfile ;

After Creating Pfile , Open it modify Control File Location to Our New Location /app/oracle/TEST/…

*.control_files=’/app/oracle/TEST/control01.ctl’,’/app/oracle/TEST/control02.ctl’

startup nomount

$rman target /

 restore controlfile from ‘+DATA/test/controlfile/current.303.697650047’;

 exit

create spfile=’/app/oracle/product/11.1.0/db_1/dbs/spfileTEST.ora’ from pfile=’/home/oracle/john.ora’;

File created.

shutdown immediate;
startup

show parameter control

control_files  /app/oracle/TEST/control01.ctl , /app/oracle/TEST/control02.ctl

This Steps Without Using RMAN , I will Post Another Steps Using RMAN

Thank you
Osama mustafa

Failed to unlock all EM related accounts

Posted on by Osama Mustafa in Database
This problem can occur if the default Profile settings have been modified and are no longer the “default” settings..

The following query will reveal the profile:

        SQL> select profile, resource_name, limit

        from dba_profiles

        where resource_type='PASSWORD' order by 1,2;
 

The default 10.2.0.1.0 profile for SYSMAN has the following parameters:

PROFILE………..RESOURCE_NAME…………….                LIMIT
———————————————————————————–
DEFAULT………..FAILED_LOGIN_ATTEMPTS……..    10
DEFAULT………..PASSWORD_GRACE_TIME……….    UNLIMITED
DEFAULT………..PASSWORD_LIFE_TIME………..         UNLIMITED
DEFAULT………..PASSWORD_LOCK_TIME………..      UNLIMITED
DEFAULT………..PASSWORD_REUSE_MAX………. .    UNLIMITED
DEFAULT………..PASSWORD_REUSE_TIME………       UNLIMITED
DEFAULT………..PASSWORD_VERIFY_FUNCTION…..NULL

DBSNMP uses the MONITORING_PROFILE, which is defined the same except for the FAILED_LOGIN_ATTEMPTS, which is set to UNLIMITED.
The failing instance, in one case, showed the following for the DEFAULT profile:

PROFILE………..RESOURCE_NAME……..LIMIT
—————————— ——————————————-
DEFAULT………..FAILED_LOGIN_ATTEMPTS………         4
DEFAULT………..PASSWORD_GRACE_TIME………..         1
DEFAULT………..PASSWORD_LIFE_TIME…………             60
DEFAULT………..PASSWORD_LOCK_TIME……….. .         0138
DEFAULT………..PASSWORD_REUSE_MAX…………        100
DEFAULT………..PASSWORD_REUSE_TIME………..         UNLIMITED
DEFAULT………..PASSWORD_VERIFY_FUNCTION……  NULL

Solution :

1.  In SQL*Plus, logon as SYS and change DBSNMP’s MONITORING_PROFILE, for example:

ALTER PROFILE MONITORING_PROFILE 

LIMIT FAILED_LOGIN_ATTEMPTS UNLIMITED 

PASSWORD_GRACE_TIME UNLIMITED

PASSWORD_LIFE_TIME UNLIMITED

PASSWORD_LOCK_TIME UNLIMITED

PASSWORD_REUSE_MAX UNLIMITED

PASSWORD_REUSE_TIME UNLIMITED

PASSWORD_VERIFY_FUNCTION NULL;

2.  Then modify SYSMAN’s DEFAULT:

ALTER PROFILE DEFAULT 

LIMIT FAILED_LOGIN_ATTEMPTS 10 

PASSWORD_GRACE_TIME UNLIMITED

PASSWORD_LIFE_TIME UNLIMITED

PASSWORD_LOCK_TIME UNLIMITED

PASSWORD_REUSE_MAX UNLIMITED

PASSWORD_REUSE_TIME UNLIMITED

PASSWORD_VERIFY_FUNCTION NULL;

After this Drop with following Steps : 

 SQL> drop role mgmt_user;
SQL> drop user sysman cascade;
SQL> drop user MGMT_VIEW cascade;
SQL> drop public synonym mgmt_target_blackouts;
SQL> drop public synonym setemviewusercontext;

 -Recreate EM 

 emca -dbcontrol db -repos create ;

Thank you
Osama mustafa 

 

DBSNMP User Dose not Exists

Posted on by Osama Mustafa in Database
This error appear while you are trying to create enterprise manager via DBCA 

Casue :

DBSNMP Not Found On dba_users

Solution:

So Simple run the following scripts :

@?/rdbms/admin/catnsnmp.sql    

@?/rdbms/admin/catsnmp.sql      

Thank you
Osama mustafa

Dealing with OCR Backup ( Oracle Cluster Registery)

Posted on by Osama Mustafa in Database
OCR calls Oracle Cluster Registry. It stores cluster configuration information. It is also shared disk component. It must be accessed by all nodes in cluster environment.It also keeps information of Which database instance run on which nodes and which service runs on which database.The process daemon OCSSd manages the configuration info in OCR and maintains the changes to cluster in the registry.

There’s Two Kind Of OCR Backup :

1-Automatic Backup By Oracle CRS , You can check 

$ORA_CRS_HOME/cdata 
Or
ocrconfig -showbackup 

 Want to change The Default Location for automatic backup :

ocrconfig -backuploc

2- Manual Backup 

$ocrconfig -export /u04/crs_backup/ocrbackup/exports/OCRFile_expBackup.dmp
$ocrconfig -export /u04/crs_backup/ocrbackup/exports/OCRFile_expBackup.dmp

 Recover OCR from Physical Backup(AUTOMATIC):

$ocrconfig -showbackup 
$srvctl -stop database -d ORCL (Shutdown all RAC instances and RAC database)
$crsctl stop crs (Shutdown Cluster)
#ocrconfig -restore /u02/apps/crs/cdata/crs/backup00.ocr
$crsctl start crs (After issuing start cluster check status of cluster using ‘crs_stat -t’)
$srvctl start database -d ORCL (Start Oracle RAC database and RAC instances)

  Recover OCR from Physical Backup (MANUAL): 

$srvctl -stop database -d ORCL (Shutdown all RAC instances and RAC database)
$crsctl stop crs (Shutdown Cluster)

SAME process should need to repeat for OCR mirror also.

ocrconfig -import /u04/crs_backup/ocrbackup/exports/OCRFile_exp_Backup.dmp (Import metadata of OCR using command)
$crsctl start crs (After issuing start cluster check status of cluster using ‘crs_stat -t’)
$srvctl start database -d ORCL (Start Oracle RAC database and RAC instances)

 Some Important Notes :

  • Oracle takes physical backup of OCR automatically.
  • No Cluster downtime or RAC database down time requires for PHYSICAL backup of OCR.
  • No Cluster downtime or RAC database down time requires for MANUAL export backup of OCR.
  • For recovery of OCR from any of above backup it should need to down ALL.
  • All procedure requires ROOT login. 

Thank you
Osama mustafa

ORA-39002,ORA-39070,ORA-29283,ORA-06512

Posted on by Osama Mustafa in Database

ORA-39002: invalid operation
ORA-39070: Unable to open the log file.
ORA-29283: invalid file operation
ORA-06512: at “SYS.UTL_FILE”, line 475
ORA-29283: invalid file operation
This problem is encountered when you try to import a data-pump dump file.
Actually, it is somewhere related to permission issues and also due to mismatch of the steps performed during import operation.
You can Check you permission by below command :

select * from all_directories where directory_name = ‘DIRECTORY_NAME‘;

select * from user_tab_privs
where table_name = ‘DIRECTORY_NAME‘;

select * from user_sys_privs

where privilege = 'CREATE ANY DIRECTORY';

Check the Below Steps : 

 1) Create data-pump directory

CONN / AS SYSDBA

CREATE OR REPLACE DIRECTORY test_dir AS '';

2.) Grant permission now to this directory 

GRANT READ, WRITE ON DIRECTORY test_dir TO ;

3.)Copy the desired dump-file at directory Location :
4.)Start import now .

Good Luck

Thank you
Osama mustafa

OBE-15409: Error in loading constraint information

Posted on by Osama Mustafa in Database

This Error Starts Appear After Migration from 9i to 10gR2 , and using forms/reports 6i I read lot about this error but nothing been useful for me  ,  I Found Some Document On metalink talk about this error , you check this error on metalink under BUG Database , the Bug Number is 309292121.
Problem description:
——————–
QUERY BUILDER is not showing the table relation between master and detailed
tables when conneted to 8.1.7 OR 9.2.0.3 database. With 9.2.0.1 data base
there is no problem.
Testcase step-by-step instructions:
———————————–
1) In the report builder, open the query builder.
3) Select the table EMP from the list and press the include button.
4) Select the table DEPT from the list and press the include button.
After I follow the above steps and rebuild the constraint the same error appear.
So i post this article maybe it will be useful.
After did some research i found that the error appears Because 10gR2 its not certified with forms/reports 6i. 
so you could try the below solution :
1-Install Oracle 10gR1.
2-Upgrade forms/reports 6i to 10g.
Thank you
Osama Mustafa

Clone Oracle Database Steps

Posted on by Osama Mustafa in Database
First :  What is Oracle Database Clone :

complete and separate copy of a database system that includes the business data, the DBMS software and any other application tiers that make up the environment. Cloning is a different kind of operation to replication and backups in that the cloned environment is both fully functional and separate in its own right. Additionally the cloned environment may be modified at its inception due to configuration changes or data subsetting.

Second : Benefit Of Clone : 

1-useful for the DBA who wants to give his developers a full-sized TEST and DEV instance by cloning the PROD instance into the development server areas. 
2-quickly migrate a system from one server to another .
3-fastest way to copy a Oracle database .

How We do it :


Step-1 : On the old system, go into SQL*Plus, sign on as SYSDBA and issue: 

SQL>alter database backup controlfile to trace;

copy and paste the below lines from your user trace file and save it as dbclone_controlfile_creation.sql in any location in your system .


STARTUP NOMOUNT
CREATE CONTROLFILE REUSE DATABASE “OLDLSQ” NORESETLOGS
NOARCHIVELOG
MAXLOGFILES 16
MAXLOGMEMBERS 2
MAXDATAFILES 240
MAXINSTANCES 1
MAXLOGHISTORY 113
LOGFILE
GROUP 1 (‘/u01/oradata/oldlsq/log1a.dbf’,
‘/u01/oradata/olslsq/log1b.dbf’) SIZE 30M,
GROUP 2 (‘/u01/oradata/oldlsq/log2a.dbf’,
‘/u01/oradata/oldlsq/log2b.dbf’) SIZE 30M
DATAFILE
‘/u01/oradata/oldlsq/system01.dbf’,
‘/u01/oradata/oldlsq/mydatabase.dbf’
;
# Recovery is required if any of the datafiles are restored
# backups, or if the last shutdown was not normal or immediate.
RECOVER DATABASE
# Database can now be opened normally.
ALTER DATABASE OPEN;

 

 Step-2 : Shutdown Old Database

SQL>Shutdown immediate ;

Step-3 : Copy and Edit the Control file – Using the output syntax from STEP 1, modify the controlfile creation script by changing the following: 


CREATE CONTROLFILE REUSE DATABASE “OLDLSQ” NORESETLOGS

CREATE CONTROLFILE SET DATABASE “NEWLSQ” RESETLOGS

 Step-4 :Remove the “recover database” and “alter database open” syntax 


Step-5:Re-names of the data files location to new clone location in a dbclone_controlfile_creation.sql.

Old:

DATAFILE
‘/u01/oradata/oldlsq/system01.dbf’,
‘/u01/oradata/oldlsq/mydatabase.dbf’

New:

DATAFILE
‘/u01/oradata/newlsq/system01.dbf’,
‘/u01/oradata/newlsq/mydatabase.dbf’

Step-6: Copy all data files into the new directories on the new server. You may change the file names if you want, but you must edit the controlfile to reflect the new data files names on the new server.

Step-7 : Create the bdump, udump and cdump directories
  

 cd /u01/admin/
mkdir newlsq
cd newlsq
mkdir bdump
mkdir udump
mkdir cdump
mkdir pfile

Step-8 : Copy-over the old init.ora file 


open the primary database pfile in a notepad or text editor and change the udump,bdump,pfile,controlfile destination and save it in new clone pfile location newlsq folder and save as newlsq.ora 
eg: primary database location /u01/admin/oldlsq/pfile/oldlsq.ora
open that oldlsq.ora file in a text editor or notepad and change the required destinations cdump,udump,bdump,controlfile destinations and save as newlsq.ora in the below destination
/u01/admin/newlsq/pfile/newlsq.ora

Step-9 : Start the new database

SQL>startup nomount pfile=’ /u01/admin/newlsq/pfile/newlsq.ora ‘ ;
SQL> @dbclone_controlfile_creation.sql
SQL>alter database open resetlogs;

SQL>create spfile from pfile;

 Step-10 : Place the new database in archivelog mode.




Thank you 
osama mustafa

delete a duplicate rows in a oracle table

Posted on by Osama Mustafa in Scripts 
 
Check the below Steps to delete duplicate row : 
 
 

SQL>select * from emp;


 EMPID ENAME                       SAL     DEPTNO
---------- -------------------- ---------- ----------
 10005 palani                             54544         10
 10001 Scott Tiger                     1000           40
 10002 Frank Naude                 500             20
 10003 rajesh                            21000         140
 10004 ramesh                           3500          220
 10005 suresh                            34567        123
 
 

delete duplicate rows in empid column in emp table 

 
 

 SQL>delete from emp where rowid not in (select max(rowid) from emp group by empid);


1 row deleted.

SQL>select * from emp;


 EMPID ENAME                       SAL     DEPTNO
---------- -------------------- ---------- ----------
 10001 Scott Tiger                       1000         40
 10002 Frank Naude                   500           20
 10003 rajesh                             21000        140
 10004 ramesh                           3500           220
 10005 suresh                            34567        123
 
 
to delete the old duplicate row from the table instead of max(rowid) replace min(rowid) 
for exampl
 

SQL>insert into emp values(10005,’Osama’,54544,10);


1 row created.

SQL>select * from emp;


   EMPID ENAME                       SAL     DEPTNO
---------- -------------------- ---------- ----------
   10005 Osama                           54544        10
   10001 Scott Tiger                     1000          40
   10002 Frank Naude                 500            20
   10003 rajesh                            21000        140
   10004 ramesh                          3500          220
   10005 suresh                           34567        123

6 rows selected.

SQL>delete from emp where rowid not in (select min(rowid) from emp group by empid);


1 row deleted.
 

SQL>select * from emp;


   EMPID ENAME                       SAL     DEPTNO
---------- -------------------- ---------- ----------
   10005 Osama                            54544      10
   10001 Scott Tiger                      1000        40
   10002 Frank Naude                  500          20
   10003 rajesh                             21000      140
   10004 ramesh                           3500        220 
 
 
thank you 
Osama mustafa