DBA Maestro - Helpful DBA tips

When receiving this error, this is more than likely due to db_file_name_convert not being configured correctly in the standby database.  DB_FILE_NAME_CONVERT is used to convert a filename of a new datafile on the primary database to a filename on the standby database. Here is how I fixed the issue on standby: 1. Set standby_file_management to MANUAL on the standby database. ALTER SYSTEM SET STANDBY_FILE_MANAGEMENT=MANUAL; 2. Create the datafile manually in the standby database. ALTER DATABASE CREATE DATAFILE '/opt/oracle/app/db/11.2.0.1/dbs/UNNAMED00057' as '/opt/data/poc2adg/datafile/deal_data.257.767990655';   3. Set standby_file_management to AUTO on the standby database. ALTER SYSTEM SET STANDBY_FILE_MANAGEMENT=AUTO; 4. Configure DB_FILE_NAME_CONVERT with the correct settings for datafile conversion. ALTER SYSTEM SET DB_FILE_NAME_CONVERT=' primary_db_datafile_location ',' standby_db_datafile_location ' SCOPE=SPFILE;...

After reviewing this error in the alert log, I found that this was pertaining to one particular datafile.  What I did to resolve this issue was the following: 1. Go to the primary database and backup the datafile in question.  In this case, it was datafile #56. Recovery Manager: Release 11.2.0.1.0 - Production on Tue Dec 20 21:52:01 2011 Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved. connected to target database: POC2 (DBID=1234567890) RMAN> backup as compressed backupset datafile 56; Starting backup at 20-DEC-11 using target database control file instead of recovery catalog allocated channel: ORA_DISK_1 channel ORA_DISK_1: SID=1492 instance=poc22 device type=DISK channel ORA_DISK_1: starting compressed full datafile backup set channel ORA_DISK_1: specifying datafile(s) in backup set input datafile file number=00056 name=+DATA/poc2/datafile/tg_ods.256.767919427 channel ORA_DISK_1: starting piece 1 at 20-DEC-11 channel ORA_D...

Before building out your standby, ensure that the database has force logging enabled in your primary database.       ALTER DATABASE FORCE LOGGING; 1. Ensure that your TNS_ADMIN is pointing to the proper location (typically this will be the Oracle Home where your database will utilize).       echo $TNS_ADMIN       export TNS_ADMIN=$ORACLE_HOME/network/admin 2.  Modify tnsnames.ora in $TNS_ADMIN on both primary and standby locations to include both your standby database TNS entry and the primary database TNS entry.   (example)     stbydb =   (DESCRIPTION =     (ADDRESS_LIST =       (ADDRESS = (PROTOCOL = TCP)(HOST = server1)(PORT = 1525))     )     (CONNECT_DATA =       (SERVICE_NAME = stbydb)     )   ) prmrydb =   (DESCRIPTION =    ...

Below is a script to accomplish copying files from ASM to nonASM (on Linux).  Below example is for copying archivelogs from inside ASM to NAS mount. #!/bin/bash # # This script copies files from FRA on ASM to local disk # ORACLE_SID=+ASM1  ORACLE_HOME=/opt/grid/11.2.0.2  ##{Grid OH} ASMLS=/users/apps/oracle/asm_ls.txt ##{ASM files list} FRA=+FRA/CODREP/ARCHIVELOG/`date +%Y_%m_%d` ##{source location of files} LOCALBACKUPDIR=/opt/dbadmin/CODREP/ARCHIVE_COPY   ## {destination filesystem} LOG=/users/apps/oracle/asm_log.txt  ## {log file} # # Get the list of files # $ORACLE_HOME/bin/asmcmd   > $ASMLS <<EOF ls $FRA exit EOF # # Clean the list by removing "ASMCMD>" # sed -i 's/ASMCMD> //g' $ASMLS ##cat $ASMLS echo `date` > $LOG # # Copy files one by one # for FILENAME in `cat $ASMLS` do if [[ ! -f $LOCALBACKUPDIR/${FILENAME} ]] then $ORACLE_HOME/bin/asmcmd >> $LOG <<EOF ...

1. Check the status of the schema objects we can see that all of them are valid. COLUMN object_name FORMAT A40 SELECT object_name, object_type, status FROM dba_objects where owner='TABLE_OWNER'; 2. Check to see that the table can be redefined online using either rowid or primary key. By default, DBMS_REDEFINITION.CONS_USE_PK is the default method if not specified. EXEC DBMS_REDEFINITION.can_redef_table ('OCS_ETL','OCS_BLNCG_RSLT_ALT',DBMS_REDEFINITION.CONS_USE_PK);   EXEC DBMS_REDEFINITION.can_redef_table ('OCS_ETL','OCS_BLNCG_RSLT_ALT',DBMS_REDEFINITION.CONS_USE_ROWID);  3. Create interim (new) table. CREATE TABLE OCS_ETL.TEMPORARY_OCS_RTE_CUST_REDEF AS SELECT * FROM OCS_ETL.TEMPORARY_OCS_RTE_CUST WHERE 1=2;                                                                           ...

I recently came across situation where I needed to recreate a public database link in a non-prod database environment and application team didn't know the password required for this particular user being used for the db link. I did the following to obtain the DDL out of production: set long 999999 select dbms_metadata.get_ddl('DB_LINK', 'LINKNAME', 'PUBLIC') from dual; I had to do the same for a user that didn't get created that I wanted to quickly create myself without using exp/imp. select dbms_metadata.get_ddl('USER','&USERNAME') from dual;

I recently experienced some type of a network hiccup of some sort resulting in a failed duplicate database using active database methodology for Oracle 11g.  The below error was the one I received for several datafile copies which is the first steps in the duplicate with active database process.  Some were missed/didn't get copied over for some reason. RMAN-03009: failure of backup command on prm2 channel at 05/01/2011 04:50:02 ORA-17629: Cannot connect to the remote database server ORA-17629: Cannot connect to the remote database server continuing other job steps, job failed will not be re-run This resulted in the below error: RMAN-03009: failure of backup command on prm4 channel at 05/01/2011 23:20:53 ORA-17629: Cannot connect to the remote database server ORA-17629: Cannot connect to the remote database server Here is how I manually completed the clone after the failure occurred: 1. Copy over the missing datafiles from primary via RMAN. connect target sys/passwor...