Skip to main content

Posts

Changing archivelog destination in STANBY side

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---...

What is ID: oracle.install.commons.util.exception.DefaultErrorAdvisor:37 and how to fix it

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...

Moving & renaming datafile

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...

Moving production databases across platforms

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...

How to reset sga_max_size

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 ------------------...

Dropping user hanging

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...

Interview with Lucia Hustatyova

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...