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_DISK_1: fi

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 =     (ADDRESS = (PROTOCOL = TCP)(HOST = scan_prd.server2.com)(PORT = 1521))     (CONNECT_DATA =       (SERVER = DEDICATED)       (SERVICE_NAME = prmrydb)     )   ) 3.   Pre-create your directory structure for the standby database u

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 cp $FRA/$F

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;                                                                                           4. Start Redefinition. EXEC DBMS_REDEFINITION.start_redef_table('OCS_ETL', 'TEMPORARY_OCS_RTE_CUST', 'TEMPORARY_OCS_

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/password@prm

Completing full backup/restore from RMAN backup or cold backup should now be a "thing of the past" due to flashback technology.  It is highly suggested to create a guaranteed restore point (GRP) in 10g/11g Oracle database without enabling flashback logging; the reason this would be beneficial is if you are not concerned with being able to flashback the database to a particular point-in-time AFTER the GRP was setup in the database.  If you would like to be able to flashback the database to any point in time AFTER the GRP was setup in the database, you WOULD need to enable flashback logging. Both GRP without flashback logging and flashback logging will generate flashback logs; however, without flashback logging enabled, the amount of flashback logs will be much less and much smaller than if flashback logging were enabled. Starting with Oracle 11gR2, you can now enable/disable flashback logging and create/drop GRPs at will WITHOUT requiring a database outage. I will go thr

I found a great blog entry around cloning a database from an 11g physical standby.  I decided to complete the steps myself. The method basically entails turning your physical standby into a snapshot standby database via dgmgrl (11g only). For 11gR2, flashback logging is not a requirement.  Otherwise, flashback logging being enabled is a requirement as converting physical standby into snapshot standby requires a guaranteed restore point to be created; this allows the snapshot standby to be converted back into physical standby. Setting db_recovery_file_dest and db_recovery_file_dest_size are configured is the only caveat. 1. Startup clone instance with startup nomount. SQL*Plus: Release 11.2.0.1.0 Production on Wed May 11 13:06:12 2011 Copyright (c) 1982, 2009, Oracle.  All rights reserved. Connected to an idle instance. SQL> startup nomount ORACLE instance started. Total System Global Area  217157632 bytes Fixed Size                  2211928 bytes Variable Size