Ulfat`s DataBase Blog

If you did not set log_archive_dest_* or db_recovery_file_dest in stanby database`s parameter file during creation of stanbdy, primary`s archivelogs will be transferred to default location of standby database. This is $ORACLE_HOME/dbs (in linux/unix). SQL> select status,instance_name,database_role from v$database,v$instance; STATUS     INSTANCE_NAME    DATABASE_ROLE ------------ ---------------- ---------------- OPEN     STND      PHYSICAL STANDBY Disconnect recovery managed SQL> alter database recover managed standby database cancel; Database altered. Check current location of archivelogs [oracle@fcdbdb_stby trace]$ ls -lrt /u01/app/oracle/product/11.2.0.3/db_1/dbs/ total 133444 -rw-r--r--. 1 oracle oinstall     2851 May 15  2009 init.ora -rw-r-----. 1 oracle oinstall       24 Dec 19 14:43 lkSTND -rw-r--r--. 1 oracle oinstall     1520 Dec 19 14:55 initSTND.ora -rw-r---...

Today while installing Oracle 11g r2 (11.2.0.3) on OEL 6 I faced below error. Looking up something in the OraInstall Log. [oracle@fcdbdb database]$ cd /tmp/OraInstall2013-12-18_04-47-35PM [oracle@fcdbdb OraInstall2013-12-18_04-47-35PM]$ ls -ltr total 36 -rwxr-x---. 1 oracle oinstall 5578 Dec 18 16:47 command_output_15591 drwxr-x---. 2 oracle oinstall 4096 Dec 18 16:47 images drwxr-x---. 6 oracle oinstall 4096 Dec 18 16:47 jdk drwxr-x---. 3 oracle oinstall 4096 Dec 18 16:47 diagnostics drwxr-x---. 3 oracle oinstall 4096 Dec 18 16:47 srvm drwxr-x---. 8 oracle oinstall 4096 Dec 18 16:47 oui drwxrwx---. 5 oracle oinstall 4096 Dec 18 16:47 ext -rw-------. 1 oracle oinstall    0 Dec 18 16:47 oraInstall2013-12-18_04-47-35PM.out -rw-------. 1 oracle oinstall 1269 Dec 18 16:47 oraInstall2013-12-18_04-47-35PM.err Here it is oraInstall2013-12-18_04-47-35PM.err [oracle@fcdbdb OraInstall2013-12-18_04-47-35PM]$ more oraInstall2013-12-18_04-47-35PM.err ---# Begin Stack...

Sometimes we need to rename or move data files. My db version: 11.2.0.3 One of the easy way is below: 1. Identify which data file you will move. In my case I will move datafile which belong FCATDEV121 tablespace. select a.tablespace_name, a.status, file_name from dba_tablespaces a, dba_data_files b where a.tablespace_name=b.tablespace_name order by 1; TABLESPACE_NAME       STATUS FILE_NAME ========================================================================================================================== SYSAUX       ONLINE /u01/app/oracle/oradata/FCDBT/sysaux01.dbf SYSTEM       ONLINE /u01/app/oracle/oradata/FCDBT/system01.dbf UNDOTBS1       ONLINE /u01/app/oracle/oradata/FCDBT/undotbs01.dbf USERS       ONLINE /u01/app/oracle/oradata/FCDBT/users01.dbf FCATDEV121       ONLINE /home/oracle/FCDBT_EXPTPS/data_D-FCDBT_I-1987414639_TS-FCATDEV121_FN...

Today I requested moving 3 databases under Aix to Linux. I am going to demonstrate only one database in this tutorial. So, let`s start. Selected SID name is FCDBT. Source -bash-4.2$ uname -a AIX localhost 1 6 00F697214C00 Target [oracle@fc-db-tst1 FCDBT_EXPTPS]$ uname -a Linux fc-db-tst1 2.6.39-400.17.1.el6uek.x86_64 #1 SMP Fri Feb 22 18:16:18 PST 2013 x86_64 x86_64 x86_64 GNU/Linux 1. create same name DB on target. ex: dbca Note: nls_language should be same as source SQL> select * from nls_database_parameters; PARAMETER                      VALUE ------------------------------ ---------------------------------------------------------------------------------------------------------------------------------------------------------------- NLS_LANGUAGE                   AMERICAN NLS_TERRITORY                  AMER...

Today I decided to increase memory but before that I wanted to change sga_max_size`s value. Because in my production database configured sga_max_size to 12Gb. It means independing on busy or idle of instance state, SGA could not be more than 12GB (it is worse). The SGA_MAX_SIZE specifies the maximum size of the SGA for the lifetime of the instance. Our instance configured AMM: select name, value from v$parameter where name like 'memory%'   memory_target    32212254720 memory_max_target    32212254720 --Checking init file -bash-3.2$ more initPROD.ora| grep -i sga PROD.__sga_target=12616466432 *.sga_max_size=25769803776 *.sga_target=0 -bash-3.2$ SGA_MAX_SIZE is not dynamic value, after changing those value, you should restart instance. SQL> show parameter sga NAME                                 TYPE        VALUE ------------------...

Today I requested that while dropping user request hanged after some time. Let`s to check: -bash-3.00$ sqlplus "/as sysdba" SQL*Plus: Release 11.1.0.7.0 - Production on Wed Oct 23 15:52:36 2013 Copyright (c) 1982, 2008, Oracle.  All rights reserved. Connected to: Oracle Database 11g Enterprise Edition Release 11.1.0.7.0 - 64bit Production With the Partitioning, OLAP, Data Mining and Real Application Testing options SQL> drop user fcjlive cascade; Checking alert log --checking alertlog -bash-3.00$ tail -50 alert_PROD.log ORA-12012: error on auto execute of job 3149 ORA-01435: user does not exist Wed Oct 23 16:02:19 2013 Errors in file /u01/app/oracle/diag/rdbms/prod/PROD/trace/PROD_j000_20447444.trc: ORA-12012: error on auto execute of job 3149 ORA-01435: user does not exist Wed Oct 23 16:04:19 2013 Errors in file /u01/app/oracle/diag/rdbms/prod/PROD/trace/PROD_j000_20447450.trc: ORA-12012: error on auto execute of job 3149 ORA-01435: user do...

Hi, today I will post interview with young beautiful woman, Oracle 10g and 11g OCM certified - Lucia Hustatyova. She is one of the seldom DBA woman which have had OCM 10g and 11g certified. After graduating from high school in Bardejov Stockel Leonard continued his studies at the University of Pavol Jozef Safarik in Kosice, the first scientific maths, later to study maths and science. Thanks opportunities in Kosice as a student she worked at various companies for part-time positions. The last six years working as a database consultant at famous company in Slovakia . Q: Lucia, when did you pass that certified exam and where did you do it? A: Oracle 10g OCM in Slovakia 2011, 11g also Slovakia but in 2012. Q: How many years have you had experience with Oracle? A: 6 years. Q: What is your job responsibility and where currently are you in charge? A: I am database admin at leader IT company - mainly practical stuff but sometimes my colleagues need consultations duri...

Example of recovery database until time. For checking after recovery I am going to create simple table with one row SQL> create user ulfet identified by ulfet; User created. SQL> grant dba to ulfet; Grant succeeded. SQL> conn ulfet/ulfet Connected. SSQL> create table test1 (id number); Table created. SQL> insert into test1 values(1); 1 row created. SQL> commit; Commit complete. Now taking backup. RMAN> backup database plus archivelog; --note date SQL> select systimestamp from dual; SYSTIMESTAMP --------------------------------------------------------------------------- 13-JAN-16 11.03.34.646076 AM +04:00 --dropping table SQL> drop table ulfet.test1; Table dropped. SQL> select systimestamp from dual; SYSTIMESTAMP --------------------------------------------------------------------------- 13-JAN-16 11.04.40.428868 AM +04:00 SQL> --start recovery process --firstly start...

Today while duplication database I faced strange error: RMAN-00571: =========================================================== RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS =============== RMAN-00571: =========================================================== RMAN-03002: failure of Duplicate Db command at 07/09/2013 16:02:16 RMAN-06136: ORACLE error from auxiliary database: ORA-01503: CREATE CONTROLFILE failed ORA-01130: database file version 11.1.0.0.0 incompatible with ORACLE version 11.0.0.0.0 ORA-01110: data file 1: '/u01/app/oracle/oradata/DUPLPROD/system01.dbf' I checked all actions and tried again, but result was same. Error happened creation control file. Let me note that I used to duplication on the same host and Oracle version 11.1.0.7.0 SQL> select * from v$version; BANNER -------------------------------------------------------------------------------- Oracle Database 11g Enterprise Edition Release 11.1.0.7.0 - 64bit Production PL/SQL R...

So, finally Oracle introduced Oracle database 12c (12.1.0.1). You can easily download it from oracle`s official site : http://www.oracle.com/technetwork/database/enterprise-edition/downloads/index.html For now only for Linux and Solaris platform`s download is available, soon for windows platform will have.

Today my friend asked me what is the different between kill session and disconnect session. After researching I found useful information and here I`m going to share briefly. Using Disconnect Session clause to disconnect the current session by destroying the dedicated server process. Using Kill Session clause let you mark a session as terminated, release locks. Example: SQL>ALTER SYSTEM DISCONNECT SESSION 'sid,serial#'; SQL>ALTER SYSTEM KILL SESSION 'sid,serial#'; For more details please refer to :  http://oracletempspace.wordpress.com/2011/04/20/example-alter-sytem-disconnect-vs-alter-system-kill-for-currently-connected-sessions/

Today I faced with new ORA error. After solving I want to share this experience with yours. So, today 5`th datafile of my database was corrupted (/u01/app/oracle/oradata/ulfet_db/example01.dbf). After recover via RMAN I saw strange error. RMAN> recover datafile 5 block 443; Starting recover at 24-MAR-13 using channel ORA_DISK_1 channel ORA_DISK_1: restoring block(s) channel ORA_DISK_1: specifying block(s) to restore from backup set restoring blocks of datafile 00005 channel ORA_DISK_1: reading from backup piece /u01/app/oracle/flash_recovery_area/ULFET_DB/backupset/2013_03_24/o1_mf_nnndf_TAG20130324T223233_8nykp220_.bkp channel ORA_DISK_1: piece handle=/u01/app/oracle/flash_recovery_area/ULFET_DB/backupset/2013_03_24/o1_mf_nnndf_TAG20130324T223233_8nykp220_.bkp tag=TAG20130324T223233 channel ORA_DISK_1: restored block(s) from backup piece 1 channel ORA_DISK_1: block restore complete, elapsed time: 00:00:03 starting media recovery media recovery complete, elapsed ti...

Today I will show you how to rename existing database name. Before it take backup of your database and also please note that this step will change only database name not dbid. My database name is prod2 and oracle version is 11g R2. I will change it to prod name. SQL> select name from v$database; NAME --------- PROD2 SQL> select * from v$version; BANNER -------------------------------------------------------------------------------- Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production PL/SQL Release 11.2.0.1.0 - Production CORE    11.2.0.1.0      Production TNS for Linux: Version 11.2.0.1.0 - Production NLSRTL Version 11.2.0.1.0 - Production SQL> [oracle@localhost ~]$ uname -n localhost.localdomain [oracle@localhost ~]$ more /etc/redhat-release  Red Hat Enterprise Linux Server release 5.5 (Tikanga) Firstly shutdown database and open it on mount mode. [oracle@localhost ~]$ sql SQL*Plus:...

If in your production database configured audit, DBA should maintain audit tables. Because audit records may grows up to undesired size. Today I will show you one of the easy way to care aud$ table. Scenario: Create new tablespace Create new archive table Create procedure Create Scheduler or Cron Job Execute and check result  Firstly I will create new tablespace for my achived aud table. SQL> create tablespace arch_tbs datafile '/u01/app/oracle/oradata/ulfet_db/arch_tbs01.dbf' size 500M; Tablespace created. -- you can set more space SQL> alter tablespace arch_tbs add datafile '/u01/app/oracle/oradata/ulfet_db/arch_tbs02.dbf' size 500M; Tablespace altered. Now I will create new table with aud$ table structure but new table will be range partitioned. --You can create daily/monthly/yearly partition also subpartition too. To get table structure I use dbms_metadata.get_ddl package`s procedure. SQL> set pagesize 1000 SQL...