Problem description: Oracle automatic stats collection job did not collect stats on a high modified table Cause: The monitoring data did not get flushed. "PL/SQL Packages and Types Reference": =================================== "GATHER STALE: Gathers statistics on stale objects as determined by looking at the *_tab_modifications views. Also, return a list of objects found to be stale." "Oracle Database Reference" ============================== "DBA_TAB_MODIFICATIONS describes modifications to all tables in the database that have been modified since the last time statistics were gathered on the tables." "Note: This view is populated only for tables with the MONITORING attribute. It is intended for statistics collection over a long period of time. For performance reasons, the Oracle Database does not populate this view immediately when the actual modifications occur. Run the FLUSH_DATABASE_MONITORING_INFO procedure in the DBMS_ STATS PL/SQL package to populate this view with the latest information. The ANALYZE_ANY system privilege is required to run this procedure." Solution: Schedule a job to execute FLUSH_DATABASE_MONITORING_INFO procedure before automatic stats collection job start.
Monday, October 14, 2013
Use DBMS_STATS.FLUSH_DATABASE_MONITORING_INFO to flush monitoring data
Friday, August 30, 2013
How to check database PSU version number
You can check database PSU version number using following 2 ways :
1, SQL > select comments, action_time from sys.registry$history; COMMENTS ACTION_TIME -------------------------------------------------- --------------------------------------------------------------------------- PSU 11.1.0.7.6 10-SEP-11 09.15.23.962385 PM view recompilation 10-SEP-11 09.21.53.526892 PM PSU 11.1.0.7.11 22-SEP-12 11.59.38.688006 PM 2, $ opatch lsinventory -bugs_fixed | grep -i -E "(DATABASE PSU|DATABASE PATCH SET UPDATE)" 8833297 13621679 Sat Sep 22 22:52:48 EDT 2012 DATABASE PSU 11.1.0.7.1 (INCLUDES CPUOCT2009) 9209238 13621679 Sat Sep 22 22:52:48 EDT 2012 DATABASE PSU 11.1.0.7.2 (INCLUDES CPUJAN2010) 9352179 13621679 Sat Sep 22 22:52:48 EDT 2012 DATABASE PSU 11.1.0.7.3 (INCLUDES CPUAPR2010) 9654987 13621679 Sat Sep 22 22:52:48 EDT 2012 DATABASE PSU 11.1.0.7.4 (INCLUDES CPUJUL2010) 9952228 13621679 Sat Sep 22 22:52:48 EDT 2012 DATABASE PSU 11.1.0.7.5 (INCLUDES CPUOCT2010) 10248531 13621679 Sat Sep 22 22:52:48 EDT 2012 DATABASE PSU 11.1.0.7.6 (INCLUDES CPUJAN2011) 11724936 13621679 Sat Sep 22 22:52:48 EDT 2012 DATABASE PSU 11.1.0.7.7 (INCLUDES CPUAPR2011) 12419384 13621679 Sat Sep 22 22:52:48 EDT 2012 DATABASE PSU 11.1.0.7.8 (INCLUDES CPUJUL2011) 12827740 13621679 Sat Sep 22 22:52:48 EDT 2012 DATABASE PSU 11.1.0.7.9 (INCLUDES CPUOCT2011) 13343461 13621679 Sat Sep 22 22:52:48 EDT 2012 DATABASE PATCH SET UPDATE 11.1.0.7.10 (INCLUDES 13621679 13621679 Sat Sep 22 22:52:48 EDT 2012 DATABASE PATCH SET UPDATE 11.1.0.7.11 (INCLUDES
Wednesday, August 21, 2013
Datapump export failed with ORA-01555 on table with LOB column.
Problem description:
Datapump export failed with ORA-01555 on table with LOB column. and there is no LOB corruption.
ORA-31693: Table data object "OWNER"."MY_LOB_TABLE" failed to load/unload and is being skipped due to error:
ORA-02354: error in exporting/importing data
ORA-01555: snapshot too old: rollback segment number 6 with name "_SYSSMU6_1307891672$"
Cause:
The LOB Retention is not defined properly.
This is confirmed by the queries:
SQL> show parameter undo;
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
undo_management string AUTO
undo_retention integer 900
undo_tablespace string UNDOTBS1
SQL> select max(maxquerylen) from gv$undostat;
MAX(MAXQUERYLEN)
----------------
2279
SQL> select retention from dba_lobs where table_name='OWNER.MY_LOB_TABLE';
RETENTION
----------
900
Solution:
SQL> alter system set undo_retention = 2400 scope = BOTH;
System altered.
SQL> alter table OWNER.MY_LOB_TABLE modify lob(LOB_COLUMN) (pctversion 5);
Table altered.
SQL> alter table OWNER.MY_LOB_TABLE modify lob(LOB_COLUMN) (retention);
Table altered.
SQL> select retention from dba_lobs where table_name='OWNER.MY_LOB_TABLE';
RETENTION
----------
2400
Tuesday, August 20, 2013
12C: Convert non-cdb into pdb and plug into cdb
1. After upgarde from 11.2.0.3 to 12.1.0.1, database(TESTRAC) is NON-CDB:
SQL> show parameter db_name;
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_name string TESTRAC
SQL> select name, cdb from v$database;
NAME CDB
--------- ---
TESTRAC NO
SQL> show con_name;
CON_NAME
------------------------------
Non Consolidated
2. Verify all datafile locations
SQL> select name from v$datafile;
NAME
--------------------------------------------------------------------------------
+DATA/testrac/datafile/system.313.822996543
+DATA/testrac/datafile/sysaux.314.822996543
+DATA/testrac/datafile/undotbs1.315.822996543
+DATA/testrac/datafile/users.316.822996543
+DATA/testrac/datafile/example.327.822996619
+DATA/testrac/datafile/undotbs2.328.822996729
3.Shutdown and Open read only, generate XML pdb description file on source non-cdb(TESTRAC)
srvctl stop database -d TESTRAC
sqlplus "/as sysdba"
SQL> startup mount;
ORACLE instance started.
Total System Global Area 1068937216 bytes
Fixed Size 2296576 bytes
Variable Size 968885504 bytes
Database Buffers 92274688 bytes
Redo Buffers 5480448 bytes
Database mounted.
SQL> alter database open read only;
Database altered.
SQL> exec dbms_pdb.describe(PDB_DESCR_FILE=>'/dba/testrac.xml'); ---> make sure created in shared directory
PL/SQL procedure successfully completed.
4.Shutdown the source non-CDB (TESTRAC)
SQL> shutdown immediate;
exit
5. Connect to target CDB(TEST1CDB) and check whether non-cdb (TESTRAC) can be plugged into it.
SQL> select name, cdb from v$database;
NAME CDB
--------- ---
TEST1CDB YES
SQL>
SET SERVEROUTPUT ON
DECLARE
compatible CONSTANT VARCHAR2(3) :=
CASE DBMS_PDB.CHECK_PLUG_COMPATIBILITY(
pdb_descr_file => '/dba/testrac.xml',
pdb_name => 'TESTRAC')
WHEN TRUE THEN 'YES'
ELSE 'NO'
END;
BEGIN
DBMS_OUTPUT.PUT_LINE(compatible);
END;
/
NO
6. If the scripts returns NO, then check PDB_PLUG_IN_VIOLATIONS view
SQL> col cause for a10
SQL> col name for a10
SQL> col message for a35 word_wrapped
SQL> select name,cause,type,message,status from PDB_PLUG_IN_VIOLATIONS where name='TESTRAC';
NAME CAUSE TYPE MESSAGE STATUS
---------- ---------- --------- ----------------------------------- ---------
TESTRAC OPTION WARNING Database option DV mismatch: PDB PENDING
installed version NULL. CDB
installed version 12.1.0.1.0.
TESTRAC OPTION WARNING Database option OLS mismatch: PDB PENDING
installed version NULL. CDB
installed version 12.1.0.1.0.
TESTRAC Non-CDB to WARNING PDB plugged in is a non-CDB, PENDING
PDB requires noncdb_to_pdb.sql be run.
NAME CAUSE TYPE MESSAGE STATUS
---------- ---------- --------- ----------------------------------- ---------
TESTRAC Parameter WARNING CDB parameter memory_target PENDING
mismatch: Previous 1073741824
Current 0
TESTRAC Parameter WARNING CDB parameter compatible mismatch: PENDING
Previous 11.2.0.0.0 Current
12.1.0.0.0
There are WARNING’s only. We can continue.
7. Plug-in Non-CDB (TESTRAC) as PDB (TESTRAC) into CDB(TEST1CDB):
SQL> CREATE PLUGGABLE DATABASE TESTRAC USING '/dba/testrac.xml' NOCOPY;
Pluggable database created.
8. Check the newly create pdb(TESTRAC)
SQL> select name, open_mode from v$pdbs;
NAME OPEN_MODE
------------------------------ ----------
PDB$SEED READ ONLY
TEST1PDB READ WRITE
TESTRAC MOUNTED
SQL> col pdb_name for a15
SQL> select pdb_name, status from dba_pdbs where pdb_name = 'TESTRAC';
PDB_NAME STATUS
--------------- -------------
TESTRAC NEW
SQL> alter session set container=TESTRAC;
Session altered.
SQL> show con_name;
CON_NAME
------------------------------
TESTRAC
SQL> show con_id;
CON_ID
------------------------------
4
SQL> select name from v$datafile;
NAME
--------------------------------------------------------------------------------
+DATA/TEST1CDB/DATAFILE/undotbs2.344.823881825
+DATA/testrac/datafile/system.313.822996543
+DATA/testrac/datafile/sysaux.314.822996543
+DATA/testrac/datafile/users.316.822996543
+DATA/testrac/datafile/example.327.822996619
9. Run the noncdb_to_pdb.sql script:
SQL>@$ORACLE_HOME/rdbms/admin/noncdb_to_pdb.sql
10. Open PDB(TESTRAC) verify that warnings was resolved.
SQL> alter session set container= CDB$ROOT;
Session altered.
SQL> col cause for a10
SQL> col name for a10
SQL> col message for a35 word_wrapped
SQL> select name,cause,type,message,status from PDB_PLUG_IN_VIOLATIONS where name='TESTRAC';
NAME CAUSE TYPE MESSAGE STATUS
---------- ---------- --------- ----------------------------------- ---------
TESTRAC Parameter WARNING CDB parameter memory_target RESOLVED
mismatch: Previous 1073741824
Current 0
TESTRAC Parameter WARNING CDB parameter compatible mismatch: RESOLVED
Previous 11.2.0.0.0 Current
12.1.0.0.0
TESTRAC OPTION WARNING Database option DV mismatch: PDB PENDING
installed version NULL. CDB
installed version 12.1.0.1.0.
TESTRAC OPTION WARNING Database option OLS mismatch: PDB PENDING
installed version NULL. CDB
installed version 12.1.0.1.0.
TESTRAC Non-CDB to ERROR PDB plugged in is a non-CDB, PENDING
PDB requires noncdb_to_pdb.sql be run.
SQL> alter pluggable database TESTRAC open;
Pluggable database altered.
SQL> select pdb_name, status from dba_pdbs;
PDB_NAME STATUS
--------------- -------------
TEST1PDB NORMAL
PDB$SEED NORMAL
TESTRAC NORMAL
3 rows selected.
SQL> col cause for a10
SQL> col name for a10
SQL> col message for a35 word_wrapped
SQL> select name,cause,type,message,status from PDB_PLUG_IN_VIOLATIONS where name='TESTRAC';
NAME CAUSE TYPE MESSAGE STATUS
---------- ---------- --------- ----------------------------------- ---------
TESTRAC Parameter WARNING CDB parameter memory_target RESOLVED
mismatch: Previous 1073741824
Current 0
TESTRAC Parameter WARNING CDB parameter compatible mismatch: RESOLVED
Previous 11.2.0.0.0 Current
12.1.0.0.0
TESTRAC Non-CDB to ERROR PDB plugged in is a non-CDB, RESOLVED
PDB requires noncdb_to_pdb.sql be run.
TESTRAC OPTION WARNING Database option DV mismatch: PDB PENDING
installed version NULL. CDB
installed version 12.1.0.1.0.
TESTRAC OPTION WARNING Database option OLS mismatch: PDB PENDING
installed version NULL. CDB
installed version 12.1.0.1.0.
Thursday, August 1, 2013
Using DBCA to add 3rd instance on standby database failed
Problem description: Try to use DBCA to add 3rd instance on a standby database, but it faile Cause of problem: While adding 3rd instance on primary database, it created undo,redo, thread information for 3rd instance and the change is propagated to standby. select inst_id, thread#, status, enabled, instance from gv$thread; select group#, thread#, sequence#,status from gv$log; Solution: 1. copy init*** and orapw*** to 3rd node 2. Modify the spfile: alter system set thread=3 scope=spfile sid='xxxx'; (sid is for third instance) alter system set instance_number=3 scope=spfile sid='xxxx'; alter system set undo_tablespace=xxxx scope=spfile sid='xxxx'; alter system set cluster_database_instances=3 scope=spfile 3. Bounce the standy database: 4. Use Sevctl to add 3rd instance and start it.
Monday, July 29, 2013
Upgrade Oracle Database to 11.2.0.3 with a Physical Standby Database in place
1, On Primary database run preupgrade check: SQL>@/u01/app/oracle/product/11.2.0.3/db_1/rdbms/admin/utlu112i.sql; 2, On Standby database, copy following file from 11.2.0.2 home to 11.2.0.3 home on all nodes $cp /u01/app/oracle/product/11.2.0.2/db_1/dbs/orapwRAC1 /u01/app/oracle/product/11.2.0.2/db_1/dbs/ $cp /u01/app/oracle/product/11.2.0.2/db_1/dbs/initRAC1.ora /u01/app/oracle/product/11.2.0.2/db_1/dbs/ $cp /u01/app/oracle/product/11.2.0.2/db_1/network/admin/tnsnames.ora /u01/app/oracle/product/11.2.0.2/db_1/ntwork/admin 3, Disable data guard broker: DGMGRL> disable configuration; 4, Stop broker on both Primary and standby: SQL> alter system set dg_broker_start=false scope=Both; 5, Stop primary database 6, Stop standby database 7, Mount standby database in new 11.2.0.3 home and start redo apply SQL> startup mount SQL> alter database recover managed standby database using current logfile disconnect from session; 8, Edit /etc/oratab to point to new oracle home 9, Run DBUA to upgarde primary database. 10, Start broker on both Primary and standby: SQL> alter system set dg_broker_start=true scope=Both; 11, Enable data guard broker: DGMGRL> enable configuration; 12, Enable clusterware configuration for standby database $ srvctl config database -d RACDR PRCD-1027 : Failed to retrieve database RACDR PRCD-1229 : An attempt to access configuration of database WLOANDR was rejected because its version 11.2.0.2.0 differs from the program version 11.2.0.3.0. Instead run the program from /u01/app/oracle/product/11.2.0.2/db_2. Run the following command srvctl upgrade database -d RACDR -o /u01/app/oracle/product/11.2.0.3/db_2
Friday, July 12, 2013
impdp failed with ORA-39002 ORA-39070 ORA-39070 ORA-06512 ORA-29283
Problem description: $ impdp system/*****@RACDB schemas=TEST directory=DP_TEST_DIR dumpfile=TEST_%U.dmp Import: Release 11.2.0.3.0 - Production on Fri Jul 12 14:59:24 2013 Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved. Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP, Data Mining and Real Application Testing options ORA-39002: invalid operation ORA-39070: Unable to open the log file. ORA-29283: invalid file operation ORA-06512: at "SYS.UTL_FILE", line 536 ORA-29283: invalid file operation Cause: impdp is connect to RAC database, but DP_TEST_DIR is point to directory on node1, Solution: connect node1 for impdp: impdp system/*****@RACDB1 schemas=TEST directory=DP_TEST_DIR dumpfile=TEST_%U.dmp
Wednesday, July 10, 2013
Install 11.2.0.3 ACFS on redhat linux 6
On redhat linux 6 ACFS is not supported, Here are the manual steps to enable ACFS functionality after GI installation: 1. After installation of the Grid Home, ensure the appropriate patch or PSU for the ACFS platform support is installed. 2. Run “/bin/acfsdriverstate supported” to ensure the patch is correctly installed. The result should be “True”. 3. Run “ /bin/acfsdriverstate installed” to ensure that ACFS is correctly installed. If the result is “False”, run “ /bin/acfsroot install” as root. 4. Run “ /bin/acfsroot enable” to enable the ACFS resources again.
Tuesday, July 2, 2013
Upgrade to Oracle Grid Infrastructure 12c
Here are the steps to upgrade Grid Infrastucture from 11.2.0.3 to 12.1.0.1 on Redhat linux 5
1. Unset Oracle Environment Variables $ unset ORACLE_BASE $ unset ORACLE_HOME $ unset ORACLE_SID 2. Using CVU to Validate Readiness for Oracle Clusterware Upgrades navigate to the staging area for the upgrade, where the runcluvfy.sh command is located: $ runcluvfy.sh stage -pre crsinst -upgrade -n ractest1-lnx,ractest2-lnx -rolling -src_crshome /u01/app/grid/11.2.0.3 -dest_crshome /u01/app/grid/12.1.0.1 -dest_version 12.1.0.1.0 -fixup -verbose Run fixup as root on all nodes: $ /tmp/CVU_12.1.0.1.0_oracle/runfixup.sh 3. Performing Rolling Upgrade of Oracle Grid Infrastructure Start the installer, and select the option to upgrade an existing Oracle Clusterware and Oracle ASM installation Run rootupgrade.sh as root on all nodes: $ /u01/app/grid/12.1.0.1/rootupgrade.sh 4. Verification $ crsctl check cluster CRS-4537: Cluster Ready Services is online CRS-4529: Cluster Synchronization Services is online CRS-4533: Event Manager is online $ crsctl query crs activeversion Oracle Clusterware active version on the cluster is [12.1.0.1.0]
Thursday, June 20, 2013
ORA-19602: cannot backup or copy active file in NOARCHIVELOG mode
Problem Description Using Rman to backup a NOARCHIVELOG failed with error : channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01 RMAN-00571: =========================================================== RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS =============== RMAN-00571: =========================================================== RMAN-03009: failure of backup command on ORA_DISK_1 channel at 06/20/2013 16:48:31 ORA-19602: cannot backup or copy active file in NOARCHIVELOG mode Cause Startup mount force cause datafile was not closed cleanly Solution Following commands will guarantee that the database is in a consistent state for a backup: RMAN> shutdown immediate; RMAN> STARTUP FORCE DBA; RMAN> shutdown immediate; RMAN> STARTUP MOUNT; RMAN> backup database;
Friday, June 14, 2013
migrate oracle database to ASM using RMAN
1. Create pfile from spfile;
2. Back up the data files to the Oracle ASM disk group.
RUN
{
ALLOCATE CHANNEL dev1 DEVICE TYPE DISK;
ALLOCATE CHANNEL dev2 DEVICE TYPE DISK;
ALLOCATE CHANNEL dev3 DEVICE TYPE DISK;
ALLOCATE CHANNEL dev4 DEVICE TYPE DISK;
BACKUP AS COPY
INCREMENTAL LEVEL 0
DATABASE
FORMAT '+DATA'
TAG 'ORA_ASM_MIGRATION';
}
3. Archive online log
RMAN> SQL "ALTER SYSTEM ARCHIVE LOG CURRENT";
4. Backup spfile
RMAN> BACKUP AS BACKUPSET SPFILE;
5. Restore spfile in ASM
RMAN> SHUTDOWN IMMEDIATE;
RMAN> STARTUP MOUNT;
RMAN> RESTORE SPFILE TO '+DATA/spfilesid.ora';
RMAN> SHUTDOWN IMMEDIATE;
6. Set Oracle Managed Files initialization parameters to Oracle ASM locations.
SQL> STARTUP FORCE NOMOUNT;
SQL> ALTER SYSTEM SET DB_CREATE_FILE_DEST='+DATA' SID='*';
SQL> ALTER SYSTEM SET DB_RECOVERY_FILE_DEST_SIZE=100G SID='*';
SQL> ALTER SYSTEM SET DB_RECOVERY_FILE_DEST='+FLASH' SID='*';
7. Set the CONTROL_FILES initialization parameter to Oracle ASM locations.
SQL> STARTUP FORCE NOMOUNT;
SQL> ALTER SYSTEM SET CONTROL_FILES='+DATA','+FLASH' SCOPE=SPFILE SID='*';
8. Migrate the control file to Oracle ASM and mount the control file.
RMAN> STARTUP FORCE NOMOUNT;
RMAN> RESTORE CONTROLFILE FROM 'original_cf_name';
RMAN> ALTER DATABASE MOUNT;
9. Migrate the data files to Oracle ASM.
SWITCH DATABASE TO COPY;
RUN
{
ALLOCATE CHANNEL dev1 DEVICE TYPE DISK;
ALLOCATE CHANNEL dev2 DEVICE TYPE DISK;
ALLOCATE CHANNEL dev3 DEVICE TYPE DISK;
ALLOCATE CHANNEL dev4 DEVICE TYPE DISK;
RECOVER DATABASE;
}
10. Drop the tempfiles and re-create them in Oracle ASM.
If you get following error, bounce the database and redo it.
ORA-25152: TEMPFILE cannot be dropped at this time
SQL> ALTER DATABASE TEMPFILE 'tempfile_name' DROP;
SQL> ALTER TABLESPACE temp_tbs_name ADD TEMPFILE;
11. Migrate the online redo log files.
SET SERVEROUTPUT ON;
DECLARE
CURSOR rlc IS
SELECT GROUP# GRP, THREAD# THR, BYTES, 'NO' SRL
FROM V$LOG
UNION
SELECT GROUP# GRP, THREAD# THR, BYTES, 'YES' SRL
FROM V$STANDBY_LOG
ORDER BY 1;
stmt VARCHAR2(2048);
BEGIN
FOR rlcRec IN rlc LOOP
IF (rlcRec.srl = 'YES') THEN
stmt := 'ALTER DATABASE ADD STANDBY LOGFILE THREAD ' ||
rlcRec.thr || ' SIZE ' || rlcRec.bytes;
EXECUTE IMMEDIATE stmt;
stmt := 'ALTER DATABASE DROP STANDBY LOGFILE GROUP ' || rlcRec.grp;
EXECUTE IMMEDIATE stmt;
ELSE
stmt := 'ALTER DATABASE ADD LOGFILE THREAD ' ||
rlcRec.thr || ' SIZE ' || rlcRec.bytes;
EXECUTE IMMEDIATE stmt;
BEGIN
stmt := 'ALTER DATABASE DROP LOGFILE GROUP ' || rlcRec.grp;
DBMS_OUTPUT.PUT_LINE(stmt);
EXECUTE IMMEDIATE stmt;
EXCEPTION
WHEN OTHERS THEN
EXECUTE IMMEDIATE 'ALTER SYSTEM SWITCH LOGFILE';
EXECUTE IMMEDIATE 'ALTER SYSTEM CHECKPOINT GLOBAL';
EXECUTE IMMEDIATE stmt;
END;
END IF;
END LOOP;
END;
/
Reference : http://docs.oracle.com/cd/E11882_01/server.112/e18951/asm_rman.htm#BABJAFIF
Using Rman to Migrate database from windows(11.2.0.1) to linux(11.2.0.3)
1 Verify following information on Winodow platform:
SQL> select * from v$version;
SQL> select platform_id, platform_name from v$database;
SQL> show parameter compatible;
SQL> select name from v$datafile;
SQL> select name from v$controlfile;
SQL> select member from v$logfile;
2 Check platform compatibility between source and target OS(Make sure ENDIAN_FORMAT are same):
SQL> col platform_name format a40
SQL> select * from v$transportable_platform where platform_name = 'Linux x86 64-bit' or platform_name = 'Microsoft Windows x86 64-bit' order by 2;
PLATFORM_ID PLATFORM_NAME ENDIAN_FORMAT
----------- ---------------------------------------- --------------
13 Linux x86 64-bit Little
12 Microsoft Windows x86 64-bit Little
3 Start the source database in read only mode:
SQL> shutdown immediate;
SQL> startup mount;
SQL> alter database open read only;
4. Check database readiness for transport from Windows to Linux:
SQL> set serveroutput on
SQL> declare
2 db_ready boolean;
3 begin
4 db_ready := dbms_tdb.check_db('Linux x86 64-bit');
5 end;
6 /
5. Check if there are any external objects:
PL/SQL procedure successfully completed.
SQL>
SQL> set serveroutput on
SQL> declare
2 external boolean;
3 begin
4 external := dbms_tdb.check_external;
5 end;
6 /
The following external tables exist in the database:
SH.SALES_TRANSACTIONS_EXT
The following directories exist in the database:
SYS.ORACLE_OCM_CONFIG_DIR, SYS.DATA_PUMP_DIR, SYS.XMLDIR, SYS.DATA_FILE_DIR,
SYS.LOG_FILE_DIR, SYS.MEDIA_DIR, SYS.SS_OE_XMLDIR, SYS.SUBDIR
The following BFILEs exist in the database:
PM.PRINT_MEDIA
PL/SQL procedure successfully completed.
6. Create a directory(make sure have enough space) and save the pfile there
Mkdir C:\to_linux
SQL> create pfile='c:\to_linux\initLINXORCL.ora' from spfile;
7. Use rman convert database command:
rman target /
RMAN> CONVERT DATABASE NEW DATABASE 'LINXORCL'
2> transport script 'C:\to_linux\script.sql'
3> to platform 'Linux x86 64-bit'
4> db_file_name_convert 'C:\APP\YCHEN\ORADATA\WINORCL\' 'C:\to_linux\';
File created
8. Transfer C:\to_linux to your target system and create directory for target database
9. Modify the pfile(initLINXORCL and script.sql to reflect the correct structure in target system.
10. Don't run script.sql directly manaully execute following commands from script.sql and upgrade to 11.2.0.3
STARTUP NOMOUNT PFILE='/u01/app/oracle/admin/LINXORCL/initLINXORCL.ora'
CREATE spfile from pfile='/u01/app/oracle/admin/LINXORCL/initLINXORCL.ora';
CREATE CONTROLFILE REUSE SET DATABASE "LINXORCL" RESETLOGS NOARCHIVELOG
MAXLOGFILES 16
MAXLOGMEMBERS 3
MAXDATAFILES 100
MAXINSTANCES 8
MAXLOGHISTORY 292
LOGFILE
GROUP 1 '/u01/app/oracle/admin/LINXORCL/REDO/REDO01.LOG' SIZE 50M BLOCKSIZE 512,
GROUP 2 '/u01/app/oracle/admin/LINXORCL/REDO/REDO02.LOG' SIZE 50M BLOCKSIZE 512,
GROUP 3 '/u01/app/oracle/admin/LINXORCL/REDO/REDO03.LOG' SIZE 50M BLOCKSIZE 512
DATAFILE
'/u01/app/oracle/admin/LINXORCL/DATA/SYSTEM01.DBF',
'/u01/app/oracle/admin/LINXORCL/DATA/SYSAUX01.DBF',
'/u01/app/oracle/admin/LINXORCL/DATA/UNDOTBS01.DBF',
'/u01/app/oracle/admin/LINXORCL/DATA/USERS01.DBF',
'/u01/app/oracle/admin/LINXORCL/DATA/EXAMPLE01.DBF'
CHARACTER SET WE8MSWIN1252
;
ALTER DATABASE OPEN RESETLOGS UPGRADE; (make sure to open database in upgrade mode)
ALTER TABLESPACE TEMP ADD TEMPFILE '/u01/app/oracle/admin/LINXORCL/DATA/TEMP01.DBF'
SIZE 20971520 AUTOEXTEND ON NEXT 655360 MAXSIZE 32767M;
SHUTDOWN IMMEDIATE
STARTUP UPGRADE
@catupgrd.sql
SQL> STARTUP
SQL> @utlu112s.sql
SQL> @catuppst.sql
SQL> @utlrp.sql
Verify that all expected packages and classes are valid:
SQL> SELECT count(*) FROM dba_invalid_objects;
SQL> SELECT distinct object_name FROM dba_invalid_objects;
Thursday, June 13, 2013
Why Execution Plan changes
Execution plans can differ due to the following: 1. Schema change(like new index... etc) 2. Data volume and statistic change. 3. Bind variable types and values change. 4. Initialization parameters change, following are examples: shared_pool_size —The memory region allocated for the library cache and internal control structures db_block_size —The size of each data block db_cache_size —The number of data buffers to allocate for the instance sort_area_size —The amount of RAM reserved for each user to perform sorting operations optimizer_mode —The default mode in which all SQL statements will be optimized for an execution plan(all_row, first_rows, first_rows_n) db_file_multiblock_read_count —The parameter that controls the number of blocks that are read asynchronously in full-table scan operations
Tuesday, June 11, 2013
RAC One Node database online relocation
1. Create RAC One Node database using DBCA 2. Verify the configuration and status of RAC One Node database: $ srvctl status database -d MYRONE Instance MYRONE_2 is running on node oratest2-lnx Online relocation: INACTIVE $ srvctl config database -d MYRONE Database unique name: MYRONE Database name: MYRONE Oracle home: /u01/app/oracle/product/11.2.0.3/db_1 Oracle user: oracle Spfile: +DATA/MYRONE/spfileMYRONE.ora Domain: intdata.com Start options: open Stop options: immediate Database role: PRIMARY Management policy: AUTOMATIC Server pools: MYRONE Database instances: Disk Groups: DATA,FLASH Mount point paths: Services: MYRONE_S Type: RACOneNode Online relocation timeout: 30 Instance name prefix: MYRONE Candidate servers: oratest1-lnx,oratest2-lnx Database is administrator managed 3. Online database relocation: $ srvctl relocate database -d MYRONE -n oratest1-lnx 4. Verify database stauts: $ srvctl status database -d MYRONE Instance MYRONE_2 is running on node oratest2-lnx Online relocation: ACTIVE Source instance: MYRONE_2 on oratest2-lnx Destination instance: MYRONE_1 on oratest1-lnx $ srvctl status database -d MYRONE Instance MYRONE_1 is running on node oratest1-lnx Online relocation: INACTIVE
Monday, June 10, 2013
Add another ASM diskgroup for OCR and voting disks.
During the installation of 11gr2 Grid Infrastructure. Oracle will ask for a ASM diskgroup to store OCR and voting disk
$ ocrcheck
Status of Oracle Cluster Registry is as follows :
Version : 3
Total space (kbytes) : 262120
Used space (kbytes) : 2760
Available space (kbytes) : 259360
ID : 2003410762
Device/File Name : +OCR_VOTING1
Device/File integrity check succeeded
Device/File not configured
Device/File not configured
Device/File not configured
Device/File not configured
Cluster registry integrity check succeeded
Logical corruption check bypassed due to non-privileged user
$ crsctl query css votedisk
## STATE File Universal Id File Name Disk group
-- ----- ----------------- --------- ---------
1. ONLINE 8a1dd0786a194fefbf40b91e857093d4 (ORCL:OCRVOTED01) [OCR_VOTING1]
2. ONLINE 1f00a1ae71424fb7bfa53b99c07c4e17 (ORCL:OCRVOTED02) [OCR_VOTING1]
3. ONLINE 94336ab660584f87bf66e4abbca6f537 (ORCL:OCRVOTED03) [OCR_VOTING1]
It is recommended by Oracle to add another loation for OCR and voting disks
1, Create a new asm diskgroup named as OCR_VOTING2
2, Add an OCR location to the new asm diskgroup
/u01/app/grid/11.2.0.3/bin/ocrconfig -add +OCR_VOTING2
3, Verify
$ ocrcheck
Status of Oracle Cluster Registry is as follows :
Version : 3
Total space (kbytes) : 262120
Used space (kbytes) : 2776
Available space (kbytes) : 259344
ID : 2003410762
Device/File Name : +OCR_VOTING1
Device/File integrity check succeeded
Device/File Name : +OCR_VOTING2
Device/File integrity check succeeded
Device/File not configured
Device/File not configured
Device/File not configured
Cluster registry integrity check succeeded
Logical corruption check bypassed due to non-privileged user
Saturday, June 8, 2013
How to located and dump a data block in oracle
SQL> create table mytest(cl1 number, cl2 varchar2(32));
Table created.
SQL> insert into mytest values (1, 'AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA');
1 row created.
SQL> insert into mytest values (2, 'BBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBB');
1 row created.
SQL> commit;
Commit complete.
SQL> select ora_rowscn scn, dbms_rowid.rowid_block_number(rowid) block from mytest;
SCN BLOCK
---------- ----------
325082188 175177
325082188 175177
SQL> select header_file, header_block, blocks from dba_segments where segment_name = 'MYTEST';
HEADER_FILE HEADER_BLOCK BLOCKS
----------- ------------ ----------
1 175176 8
SQL> alter session set tracefile_identifier=mytest;
Session altered.
SQL> alter system dump datafile 1 block 175177;
System altered.
Below are the dump from cache, you should be see scn(last change SCN), csc(last clean SCN), itl... etc information in block header
==================================================================
Block dump from cache:
Dump of buffer cache at level 4 for tsn=0, rdba=4369481
BH (0x7ebc98c0) file#: 1 rdba: 0x0042ac49 (1/175177) class: 1 ba: 0x7ea1a000
set: 11 bsz: 8192 bsi: 0 sflg: 0 pwc: 0, 25 lid: 0x00000000,0x00000000
dbwrid: 0 obj: 139983 objn: 139983 tsn: 0 afn: 1
hash: [0x9de2e2d0,0x9de2e2d0] lru: [0x7dbc7428,0x823cdd38]
obj-flags: object_ckpt_list
ckptq: [0x9e78f9a8,0x7dbc73d8] fileq: [0x9e78f9c8,0x9e78f9c8] objq: [0x9a7d9080,0x9a7d9080]
st: XCURRENT md: NULL tch: 3 le: 0x777e7d58
flags: buffer_dirty redo_since_read gotten_in_current_mode
LRBA: [0x49a.237.0] LSCN: [0x0.13605c21] HSCN: [0x0.13605c4c] HSUB: [1]
cr pin refcnt: 0 sh pin refcnt: 0
buffer tsn: 0 rdba: 0x0042ac49 (1/175177)
scn: 0x0000.13605c4c seq: 0x01 flg: 0x02 tail: 0x5c4c0601
frmt: 0x02 chkval: 0x0000 type: 0x06=trans data
Hex dump of block: st=0, typ_found=1
Dump of memory from 0x000000007EA1A000 to 0x000000007EA1C000
07EA1A000 0000A206 0042AC49 13605C4C 02010000 [....I.B.L\`.....]
07EA1A010 00000000 00000001 000222CF 13605C21 [........."..!\`.]
07EA1A020 00000000 00030002 00000000 0008000A [................]
07EA1A030 00029F08 00C006F0 00128556 00002002 [........V.... ..]
07EA1A040 13605C4C 00000000 00000000 00000000 [L\`.............]
07EA1A050 00000000 00000000 00000000 00020100 [................]
07EA1A060 0016FFFF 1F3C1F52 00001F3C 1F790002 [....R.<.<.....y.]
07EA1A070 00001F52 00000000 00000000 00000000 [R...............]
07EA1A080 00000000 00000000 00000000 00000000 [................]
Repeat 497 times
07EA1BFA0 00000000 00000000 00000000 012C0000 [..............,.]
07EA1BFB0 03C10202 42424220 42424242 42424242 [.... BBBBBBBBBBB]
07EA1BFC0 42424242 42424242 42424242 42424242 [BBBBBBBBBBBBBBBB]
07EA1BFD0 42424242 02012C42 2002C102 41414141 [BBBBB,..... AAAA]
07EA1BFE0 41414141 41414141 41414141 41414141 [AAAAAAAAAAAAAAAA]
07EA1BFF0 41414141 41414141 41414141 5C4C0601 [AAAAAAAAAAAA..L\]
Block header dump: 0x0042ac49
Object id on Block? Y
seg/obj: 0x222cf csc: 0x00.13605c21 itc: 2 flg: O typ: 1 - DATA
fsl: 0 fnx: 0x0 ver: 0x01
Trace file /u02/oracle/diag/DBAPRD/diag/rdbms/dbaprd/DBAPRD1/trace/DBAPRD1_ora_8207_MYTEST.trc
Oracle Database 11g Enterprise Edition Release 11.1.0.7.0 - 64bit Production
With the Partitioning, Real Application Clusters, OLAP, Data Mining
and Real Application Testing options
ORACLE_HOME = /u01/app/oracle/product/11.1.0/db_1
System name: Linux
Node name: racprod1-lnx
Release: 2.6.18-53.el5
Version: #1 SMP Wed Oct 10 16:34:19 EDT 2007
Machine: x86_64
Instance name: DBAPRD1
Redo thread mounted by this instance: 1
Oracle process number: 62
Unix process pid: 8207, image: oracle@racprod1-lnx (TNS V1-V3)
*** 2013-06-08 22:19:47.221
*** SESSION ID:(214.14265) 2013-06-08 22:19:47.221
*** CLIENT ID:() 2013-06-08 22:19:47.221
*** SERVICE NAME:(SYS$USERS) 2013-06-08 22:19:47.221
*** MODULE NAME:(sqlplus@racprod1-lnx (TNS V1-V3)) 2013-06-08 22:19:47.221
*** ACTION NAME:() 2013-06-08 22:19:47.221
Start dump data blocks tsn: 0 file#:1 minblk 175177 maxblk 175177
Block dump from cache:
Dump of buffer cache at level 4 for tsn=0, rdba=4369481
BH (0x7ebc98c0) file#: 1 rdba: 0x0042ac49 (1/175177) class: 1 ba: 0x7ea1a000
set: 11 bsz: 8192 bsi: 0 sflg: 0 pwc: 0, 25 lid: 0x00000000,0x00000000
dbwrid: 0 obj: 139983 objn: 139983 tsn: 0 afn: 1
hash: [0x9de2e2d0,0x9de2e2d0] lru: [0x7dbc7428,0x823cdd38]
obj-flags: object_ckpt_list
ckptq: [0x9e78f9a8,0x7dbc73d8] fileq: [0x9e78f9c8,0x9e78f9c8] objq: [0x9a7d9080,0x9a7d9080]
st: XCURRENT md: NULL tch: 3 le: 0x777e7d58
flags: buffer_dirty redo_since_read gotten_in_current_mode
LRBA: [0x49a.237.0] LSCN: [0x0.13605c21] HSCN: [0x0.13605c4c] HSUB: [1]
cr pin refcnt: 0 sh pin refcnt: 0
buffer tsn: 0 rdba: 0x0042ac49 (1/175177)
scn: 0x0000.13605c4c seq: 0x01 flg: 0x02 tail: 0x5c4c0601
frmt: 0x02 chkval: 0x0000 type: 0x06=trans data
Hex dump of block: st=0, typ_found=1
Dump of memory from 0x000000007EA1A000 to 0x000000007EA1C000
07EA1A000 0000A206 0042AC49 13605C4C 02010000 [....I.B.L\`.....]
07EA1A010 00000000 00000001 000222CF 13605C21 [........."..!\`.]
07EA1A020 00000000 00030002 00000000 0008000A [................]
07EA1A030 00029F08 00C006F0 00128556 00002002 [........V.... ..]
07EA1A040 13605C4C 00000000 00000000 00000000 [L\`.............]
07EA1A050 00000000 00000000 00000000 00020100 [................]
07EA1A060 0016FFFF 1F3C1F52 00001F3C 1F790002 [....R.<.<.....y.]
07EA1A070 00001F52 00000000 00000000 00000000 [R...............]
07EA1A080 00000000 00000000 00000000 00000000 [................]
Repeat 497 times
07EA1BFA0 00000000 00000000 00000000 012C0000 [..............,.]
07EA1BFB0 03C10202 42424220 42424242 42424242 [.... BBBBBBBBBBB]
07EA1BFC0 42424242 42424242 42424242 42424242 [BBBBBBBBBBBBBBBB]
07EA1BFD0 42424242 02012C42 2002C102 41414141 [BBBBB,..... AAAA]
07EA1BFE0 41414141 41414141 41414141 41414141 [AAAAAAAAAAAAAAAA]
07EA1BFF0 41414141 41414141 41414141 5C4C0601 [AAAAAAAAAAAA..L\]
Block header dump: 0x0042ac49
Object id on Block? Y
seg/obj: 0x222cf csc: 0x00.13605c21 itc: 2 flg: O typ: 1 - DATA
fsl: 0 fnx: 0x0 ver: 0x01
Itl Xid Uba Flag Lck Scn/Fsc
0x01 0x000a.008.00029f08 0x00c006f0.8556.12 --U- 2 fsc 0x0000.13605c4c
0x02 0x0000.000.00000000 0x00000000.0000.00 ---- 0 fsc 0x0000.00000000
bdba: 0x0042ac49
data_block_dump,data header at 0x7ea1a05c
===============
tsiz: 0x1fa0
hsiz: 0x16
pbl: 0x7ea1a05c
76543210
flag=--------
ntab=1
nrow=2
frre=-1
fsbo=0x16
fseo=0x1f52
avsp=0x1f3c
tosp=0x1f3c
0xe:pti[0] nrow=2 offs=0
0x12:pri[0] offs=0x1f79
0x14:pri[1] offs=0x1f52
block_row_dump:
tab 0, row 0, @0x1f79
tl: 39 fb: --H-FL-- lb: 0x1 cc: 2
col 0: [ 2] c1 02
col 1: [32]
41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41
41 41 41 41 41 41 41
tab 0, row 1, @0x1f52
tl: 39 fb: --H-FL-- lb: 0x1 cc: 2
col 0: [ 2] c1 03
col 1: [32]
42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42
42 42 42 42 42 42 42
end_of_block_dump
switchover failed with ORA-16775: target standby database in broker operation has potential data loss
Problem Description
I switchover from primary to standby successfully but when I swithback to primary I got following error:
DGMGRL> show configuration
Configuration
Name: DBAPRD.intdata.com
Enabled: YES
Protection Mode: MaxPerformance
Databases:
DBALC - Primary database
DBAPRD - Physical standby database
DBADR - Physical standby database
Fast-Start Failover: DISABLED
Current status for "DBAPRD.intdata.com":
SUCCESS
DGMGRL> switchover to 'DBAPRD'
DGMGRL> Performing switchover NOW, please wait...
Error: ORA-16775: target standby database in broker operation has potential data loss
Failed.
Unable to switchover, primary database is still "DBALC"
DGMGRL> show configuration
Configuration
Name: DBAPRD.intdata.com
Enabled: YES
Protection Mode: MaxPerformance
Databases:
DBALC - Primary database
DBAPRD - Physical standby database
DBADBDR - Physical standby database
Fast-Start Failover: DISABLED
Current status for "DBAPRD.intdata.com":
Warning: ORA-16607: one or more databases have failed
Following error was found in alertlog of primary database:
Errors in file /u02/oracle/diag/dbaPRD/diag/rdbms/dbalc/dbaLC/trace/dbaLC_lns1_21171.trc:
ORA-03135: connection lost contact
LGWR: Error 3135 closing archivelog file '(DESCRIPTION=(ADDRESS_LIST = (ADDRESS=(PROTOCOL=tcp)(HOST=racprod1-v)(PORT=1540)))(CONNECT_DATA=(SERVICE_NAME=dbaPRD.intdata.com)(SERVER=DEDICATED)))'
Sat Jun 08 19:20:51 2013
Deleted Oracle managed file +dba_FLASH/dbalc/archivelog/2013_04_14/thread_1_seq_130382.8527.812711313
Waiting for all non-current ORLs to be archived...
Waiting for the ORL for thread 1 sequence 136062 to be archived...
Sat Jun 08 19:20:59 2013
ARC3: Archiving not possible: failed standby destinations
ARCH: Archival stopped, error occurred. Will continue retrying
ORACLE Instance dbaLC - Archival Error
ORA-16014: log 2 sequence# 136062 not archived, no available destinations
ORA-00312: online log 2 thread 1: '+dba_DATA/dbalc/onlinelog/group_2.311.794395241'
ORA-00312: online log 2 thread 1: '+dba_FLASH/dbalc/onlinelog/group_2.303.794395255'
Errors in file /u02/oracle/diag/dbaPRD/diag/rdbms/dbalc/dbaLC/trace/dbaLC_arc3_14518.trc:
ORA-16014: log 2 sequence# 136062 not archived, no available destinations
ORA-00312: online log 2 thread 1: '+dba_DATA/dbalc/onlinelog/group_2.311.794395241'
ORA-00312: online log 2 thread 1: '+dba_FLASH/dbalc/onlinelog/group_2.303.794395255'
......
ORA-16416 signalled during: ALTER DATABASE COMMIT TO SWITCHOVER TO PHYSICAL STANDBY WITH SESSION SHUTDOWN...
Cause:
The target standby database in the broker operation did not have all the redo logs from the primary database.
Solution:
I tried "alter system swith logfile" but it didnt' work.
Restart primary database solve the problem.
Conclusion
Before swithover , run a few log switchover to make sure log can ship to standby.
Understand SCN movement during online user managed backup;
Before online backup, check current scn, system and datafile scn in controlfile and scn in datafile header. As expected checkpoint scn are same in controlfile and datafile headers, and it is behind current scn. SQL> select current_scn, checkpoint_change# from v$database; CURRENT_SCN CHECKPOINT_CHANGE# ----------- ------------------ 325011036 325009912 SQL> select name,checkpoint_change# from v$datafile; NAME CHECKPOINT_CHANGE# -------------------------------------------------- ------------------ +DBA_DATA/dbaprd/datafile/system.270.675355175 325009912 +DBA_DATA/dbaprd/datafile/sysaux.269.675355177 325009912 +DBA_DATA/dbaprd/datafile/undotbs1.266.675355179 325009912 +DBA_DATA/dbaprd/datafile/undotbs2.264.675355187 325009912 +DBA_DATA/dbaprd/datafile/users.263.675355189 325009912 +DBA_DATA/dbaprd/datafile/xml_data.262.675355189 325009912 SQL> select name,checkpoint_change# from v$datafile_header where name like '%users%'; NAME CHECKPOINT_CHANGE# -------------------------------------------------- ------------------ +DBA_DATA/dbaprd/datafile/users.263.675355189 325009912 Start online tablespace backup Oracle did a checkpoint on USERS tablespace and datafile only, and freeze the checkpoint scn on datafile header. SQL> alter tablespace users begin backup; Tablespace altered. SQL> select current_scn, checkpoint_change# from v$database; CURRENT_SCN CHECKPOINT_CHANGE# ----------- ------------------ 325011196 325009912 SQL> select name,checkpoint_change# from v$datafile; NAME CHECKPOINT_CHANGE# -------------------------------------------------- ------------------ +DBA_DATA/dbaprd/datafile/system.270.675355175 325009912 +DBA_DATA/dbaprd/datafile/sysaux.269.675355177 325009912 +DBA_DATA/dbaprd/datafile/undotbs1.266.675355179 325009912 +DBA_DATA/dbaprd/datafile/undotbs2.264.675355187 325009912 +DBA_DATA/dbaprd/datafile/users.263.675355189 325011168 +DBA_DATA/dbaprd/datafile/xml_data.262.675355189 325009912 SQL> select name,checkpoint_change# from v$datafile_header where name like '%users%'; NAME CHECKPOINT_CHANGE# -------------------------------------------------- ------------------ +DBA_DATA/dbaprd/datafile/users.263.675355189 325011168 during the online backup checkpoint scn is freeze on datafile belongs to USERS tablespace SQL> alter system checkpoint; System altered. SQL> select current_scn, checkpoint_change# from v$database; CURRENT_SCN CHECKPOINT_CHANGE# ----------- ------------------ 325011272 325011243 SQL> select name,checkpoint_change# from v$datafile; NAME CHECKPOINT_CHANGE# -------------------------------------------------- ------------------ +DBA_DATA/dbaprd/datafile/system.270.675355175 325011243 +DBA_DATA/dbaprd/datafile/sysaux.269.675355177 325011243 +DBA_DATA/dbaprd/datafile/undotbs1.266.675355179 325011243 +DBA_DATA/dbaprd/datafile/undotbs2.264.675355187 325011243 +DBA_DATA/dbaprd/datafile/users.263.675355189 325011168 +DBA_DATA/dbaprd/datafile/xml_data.262.675355189 325011243 SQL> select name,checkpoint_change# from v$datafile_header where name like '%users%'; NAME CHECKPOINT_CHANGE# -------------------------------------------------- ------------------ +DBA_DATA/dbaprd/datafile/users.263.675355189 325011168 END online tablespace backup; Oracle advanced checkpoint scn on USERS tablespace and datafile only to be same as system checkpoint scn SQL> alter tablespace users end backup; Tablespace altered. SQL> select current_scn, checkpoint_change# from v$database; CURRENT_SCN CHECKPOINT_CHANGE# ----------- ------------------ 325011488 325011243 SQL> select name,checkpoint_change# from v$datafile; NAME CHECKPOINT_CHANGE# -------------------------------------------------- ------------------ +DBA_DATA/dbaprd/datafile/system.270.675355175 325011243 +DBA_DATA/dbaprd/datafile/sysaux.269.675355177 325011243 +DBA_DATA/dbaprd/datafile/undotbs1.266.675355179 325011243 +DBA_DATA/dbaprd/datafile/undotbs2.264.675355187 325011243 +DBA_DATA/dbaprd/datafile/users.263.675355189 325011243 +DBA_DATA/dbaprd/datafile/xml_data.262.675355189 325011243 9 rows selected. SQL> select name,checkpoint_change# from v$datafile_header where name like '%users%'; NAME CHECKPOINT_CHANGE# -------------------------------------------------- ------------------ +DBA_DATA/dbaprd/datafile/users.263.675355189 325011243
Friday, June 7, 2013
Use rman increamental backup to re-sync a physical standby database
Problem description: Got following error in standby alert log: Completed: ALTER DATABASE RECOVER MANAGED STANDBY DATABASE THROUGH ALL SWITCHOVER DISCONNECT USING CURRENT LOGFILE Media Recovery Log +DBA_FLASH/dbalc/archivelog/2013_06_06/thread_1_seq_952.3228.817406977 Errors with log +DBA_FLASH/dbalc/archivelog/2013_06_06/thread_1_seq_952.3228.817406977 MRP0: Background Media Recovery terminated with error 328 Errors in file /u02/oracle/diag/DBAPRD/diag/rdbms/dbalc/DBALC/trace/DBALC_mrp0_2283.trc: ORA-00328: archived log ends at change 324705843, need later change 324707097 ORA-00334: archived log: '+DBA_FLASH/dbalc/archivelog/2013_06_06/thread_1_seq_952.3228.817406977' Managed Standby Recovery not using Real Time Apply Shutting down recovery slaves due to error 328 Recovery interrupted! Errors in file /u02/oracle/diag/DBAPRD/diag/rdbms/dbalc/DBALC/trace/DBALC_mrp0_2283.trc: ORA-00328: archived log ends at change 324705843, need later change 324707097 ORA-00334: archived log: '+DBA_FLASH/dbalc/archivelog/2013_06_06/thread_1_seq_952.3228.817406977' MRP0: Background Media Recovery process shutdown (DBALC) Cause: The issue can be due to many reasons related to archived logs and SCN having wrong meta data. Standby Oracle control file is looking for a specific Archivelog specific SCN which it thinks is required for Recovery is not available in the archived log. Action Plan: Using RMAN Incremental Backups to Roll Forward a Physical Standby Database http://docs.oracle.com/cd/E11882_01/server.112/e25608/rman.htm#CIHIAADC 1 Stop Redo apply and get the SCN on standby database SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL; SQL> SELECT CURRENT_SCN FROM V$DATABASE; CURRENT_SCN ----------- 324714843 2 Backup incremental from SCN on primary database RMAN> BACKUP INCREMENTAL FROM SCN 324714843 DATABASE FORMAT '/datapump/ForStandby_%U' tag 'FORSTANDBY'; 3 Copy backup from primary to standby 4 Catalog the backup on standby database RMAN> CATALOG START WITH '/datapump/ForStandby'; 5 Recover the standby database with the cataloged incremental backup RMAN> RECOVER DATABASE NOREDO; you may need to restore standby control first if above command failed. RMAN> STARTUP FORCE NOMOUNT; RMAN> RESTORE STANDBY CONTROLFILE FROM TAG 'FORSTANDBY'; RMAN> ALTER DATABASE MOUNT; RMAN> RECOVER DATABASE NOREDO; 6 Restart Standby database and start redo apply.
Thursday, June 6, 2013
Re-create corrupted ASM disks
Problem description: Following 3 disks can see via OS but not ASM: OCRVOTED01 OCRVOTED02 OCRVOTED03 # multipath -l|grep OCR shared_asm_OCRVOTED01 (1HITACHI_770140123364) dm-24 HITACHI,DF600F shared_asm_OCRVOTED02 (1HITACHI_770140123365) dm-25 HITACHI,DF600F shared_asm_OCRVOTED03 (1HITACHI_770140123366) dm-26 HITACHI,DF600F shared_asm_OCRVOTED04 (1HITACHI_770140123367) dm-27 HITACHI,DF600F shared_asm_OCRVOTED05 (1HITACHI_770140123368) dm-28 HITACHI,DF600F # /etc/init.d/oracleasm listdisks |grep OCR OCRVOTED04 OCRVOTED05 Cause: Disks are corrupted Action Plan: 1. Try to recreate asm disk /etc/init.d/oracleasm createdisk OCRVOTED01 /dev/mapper/shared_asm_OCRVOTED01 Marking disk "OCRVOTED01" as an ASM disk: [FAILED] found following message from /var/log/oracleasm Device "/dev/mapper/shared_asm_OCRVOTED01" is already labeled for ASM disk "" 2. Overwrite the ASM header information on the disk. This can be achieved with the UNIX command dd. The following command will write 100x1024b blocks to the specified raw device: dd if=/dev/zero of=/dev/mapper/shared_asm_OCRVOTED01 bs=1024 count=100 dd if=/dev/zero of=/dev/mapper/shared_asm_OCRVOTED02 bs=1024 count=100 dd if=/dev/zero of=/dev/mapper/shared_asm_OCRVOTED03 bs=1024 count=100 3. Recreate asm disks /etc/init.d/oracleasm createdisk OCRVOTED01 /dev/mapper/shared_asm_OCRVOTED01 /etc/init.d/oracleasm createdisk OCRVOTED02 /dev/mapper/shared_asm_OCRVOTED02 /etc/init.d/oracleasm createdisk OCRVOTED03 /dev/mapper/shared_asm_OCRVOTED03 4. Run oracleasm scandisks on both nodes /etc/init.d/oracleasm scandisks 5. Verify # /etc/init.d/oracleasm listdisks |grep OCR OCRVOTED01 OCRVOTED02 OCRVOTED03 OCRVOTED04 OCRVOTED05
Find mapping of ASM disks to Physical Devices
Query asmdisk to get the major - minor numbers $ /etc/init.d/oracleasm querydisk -d OCRVOTED04 Disk "OCRVOTED04" is a valid ASM disk on device [253, 27] Match the number to physical device $ ls -l /dev|grep 253|grep 27 brw-rw---- 1 root root 253, 27 Jun 2 00:50 dm-27
Tuesday, June 4, 2013
Prerequest check before switchover to physical standby
Verify standby has received all redo from primary
If you are running in Maximum Protection or Maximum Availability mode, running following query on primary database to check if the target of you switchover is synchronized.
SQL> select db_unique_name, protection_mode, synchronization_status, synchronized from v$archive_dest_status;
If Synchronized does not say YES or you are running Maximum Performance mode, running following query on target standby database to get current log sequence that the primary is sending.
SQL> select client_process, process, thread#, sequence#, status from v$managed_standby where client_process = 'LGWR';
CLIENT_P PROCESS THREAD# SEQUENCE# STATUS
-------- --------- ---------- ---------- ------------
LGWR RFS 1 718 IDLE
LGWR RFS 2 11 IDLE
Running following query on primary database to get the current redo sequence#
SQL> select thread#, sequence#, status from v$log where status = 'CURRENT';
THREAD# SEQUENCE# STATUS
---------- ---------- ----------------
1 718 CURRENT
2 11 CURRENT
You can not switchover if standby is not receiving the current redo.
Check the apply is caught up.
Running following query on target standby database, make sure status fo MRP0 is "APPLYING_LOG"
SQL> select inst_id, client_process, process, thread#, sequence#, status from gv$managed_standby where process like 'MRP%';
INST_ID CLIENT_P PROCESS THREAD# SEQUENCE# STATUS
---------- -------- --------- ---------- ---------- ------------
1 N/A MRP0 1 718 APPLYING_LOG
Stop Rman backup and cancel jobs
Thursday, May 30, 2013
How to setup ACFS manually
You can use ASMCA to setup ACFS, below are the steps to setup it manually: Create Diskgroups 1 CREATE DISKGROUP ACFS_DATA EXTERNAL REDUNDANCY 2 DISK 'ORCL:ACFSVOL1' NAME ACFSVOL1 3 ATTRIBUTE 'au_size'='1M', 4 'compatible.asm' = '11.2', 5 'compatible.rdbms' = '11.2', 6 'compatible.advm' = '11.2'; Load ACFS Drivers Validate ACFS drivers, #lsmod |grep oracle oracleacfs 1734520 0 oracleadvm 242048 0 oracleoks 295248 2 oracleacfs,oracleadvm oracleasm 84136 1 If you don't see oracleacfs, oracleadvm and oracleoks, load the ACFS Driver: $GRID_HOME/bin/acfsload start –s Create the Dynamic volume on ACFS 1 sqlplus / as sysasm 2 alter diskgroup ACFS_DATA add volume acfsvol_01 size 100G; 3 4 select VOLUME_NAME, VOLUME_DEVICE from v$asm_volume; 5 6 VOLUME_NAME VOLUME_DEVICE 7 ------------------------------ ------------------------------ 8 ACFSVOL_01 /dev/asm/acfsvol_01-335 Create Filesystem for ACFS Volume Device As root user, to create a filesystem on linux for the ACFS volume device mkfs -t acfs -b 4k /dev/asm/acfsvol_01-335 mkfs.acfs: version = 11.2.0.1.0.0 mkfs.acfs: on-disk version = 39.0 mkfs.acfs: volume = /dev/asm/acfsvol_01-335 mkfs.acfs: volume size = 268435456 Mount ACFS Filesystem mkdir -p /datapump mount -t acfs /dev/asm/acfsvol_01-335 /datapump chown oracle:oinstall /datapump df -h to verify. Oracle ACFS Mount Registry Register ACFS mount point to automatic startup ACFS mount devices acfsutil registry -a /dev/asm/acfsvol_01-335 /datapump
Wednesday, May 29, 2013
Change MTU size for a Network Interface in Redhat linux
During the 11.2.0.3 GI installation, Oracle recommend to change MTU of private network interface from 9000 to 1500. Here are the steps I followed: 1. Verify current mtu setting, bond1 is priivate interface. $ /bin/netstat -in Kernel Interface table Iface MTU Met RX-OK RX-ERR RX-DRP RX-OVR TX-OK TX-ERR TX-DRP TX-OVR Flg bond1 9000 0 2556 0 0 0 1629 0 0 0 BMmRU eth0 1500 0 4246139 0 0 0 4779910 0 0 0 BMRU eth2 9000 0 1269 0 0 0 816 0 0 0 BMsRU eth3 9000 0 1287 0 0 0 813 0 0 0 BMsRU lo 16436 0 99006 0 0 0 99006 0 0 0 LRU 2. Change mtu in ifcfg-bond1 $ vi /etc/sysconfig/network-scripts/ifcfg-bond1 DEVICE=bond1 BOOTPROTO=none ONBOOT=yes TYPE=Ethernet USERCTL=no NETMASK=255.255.0.0 IPADDR=10.10.10.170 MTU=9000 PEERDNS=yes IPV6INIT=no BONDING_OPTS="mode=0 miimon=100 primary=eth2" Chagne MTU to 1500 3. Restart private network interface. service network restart bond1 4 Verify that mtu size changed. $ /bin/netstat -in Kernel Interface table Iface MTU Met RX-OK RX-ERR RX-DRP RX-OVR TX-OK TX-ERR TX-DRP TX-OVR Flg bond1 1500 0 0 0 0 0 9 0 0 0 BMmRU eth0 1500 0 100 0 0 0 73 0 0 0 BMRU eth2 1500 0 0 0 0 0 6 0 0 0 BMsRU eth3 1500 0 0 0 0 0 3 0 0 0 BMsRU lo 16436 0 118694 0 0 0 118694 0 0
Tuesday, May 28, 2013
Define Transparent Application Failover(TAF) Policy with service
Services simplify the deployment of TAF. You can define a TAF policy for a service, and all connections using this service will automatically have TAF enabled. This does not require any client-side changes. The TAF setting on a service overrides any TAF setting in the client connection definition. To define a TAF policy for a service, use SRVCTL as in the following example, where the service name is RAC_S and the database name is RAC: $ srvctl modify service -d RAC -s RAC_S -P BASIC -e SELECT -z 5 -w 120 $ srvctl config service -d RAC -s RAC_S Service name: RAC_S Service is enabled Server pool: RAC_RAC_S Cardinality: 1 Disconnect: false Service role: PRIMARY Management policy: AUTOMATIC DTP transaction: false AQ HA notifications: false Failover type: SELECT Failover method: NONE TAF failover retries: 5 TAF failover delay: 120 Connection Load Balancing Goal: LONG Runtime Load Balancing Goal: NONE TAF policy specification: BASIC Edition: Preferred instances: RAC3 Available instances: RAC2,RAC1
Dynamical sql to drop all disks from an ASM disk group
set pagesize 0 select 'ALTER DISKGROUP '|| dg.name || ' DROP DISK '|| d.name || ';' from v$asm_disk d join v$asm_diskgroup dg on (d.group_number = dg.group_number) where dg.name = '&diskgroup_name';
Saturday, May 25, 2013
recover copy of database failed with ORA-15012
Problem description: During the incrementally Updated Backup, I got following error message while executing command -- "recover copy of database with tag '$TAGAME';" recovering datafile copy file number=00016 name=+RAC_FLASH/RACprd/datafile/index_medium.1854.813296975 channel oem_disk_backup: reading from backup piece +RAC_FLASH/RACprd/backupset/2013_05_25/nnndn1_RACprd_lvl0_0.2907.816333595 released channel: oem_disk_backup RMAN-00571: =========================================================== RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS =============== RMAN-00571: =========================================================== RMAN-03002: failure of recover command at 05/25/2013 21:40:57 ORA-19870: error while restoring backup piece +RAC_FLASH/RACprd/backupset/2013_05_25/nnndn1_RACprd_lvl0_0.2907.816333595 ORA-19625: error identifying file +RAC_FLASH/RACprd/datafile/index_medium.1854.813296975 ORA-17503: ksfdopn:2 Failed to open file +RAC_FLASH/RACprd/datafile/index_medium.1854.813296975 ORA-15012: ASM file '+RAC_FLASH/RACprd/datafile/index_medium.1854.813296975' does not exist Cause: The copy of the data files are marked to be Available in the RMAN repository. So during an incremental merge, RMAN expects to merge the incremental 1 backup to the datafile copies. Solution: RMAN> crosscheck copy of database;
Friday, May 24, 2013
Shutdown abort in a rac instance
Here is how instance recovery happened in rac environment: After you issue the SHUTDOWN ABORT command or after an instance terminates abnormally. An instance that is still running performs instance recovery for the instance that shut down. If no other instances are running, the next instance to open the database performs instance recovery for any instances needing it.
How to re-configure dataguard broker
Sometimes there is some issue with dataguard broker configuration, you may want to re-configure it. Here are the steps 1, Stop dataguard brokers on both primary and standby database alter system set dg_broker_start = false; 2, Remove the dataguard configure files on both primary and standby database from ASM or filesystem 3, Start dataguard brokers on both primary and standby database alter system set dg_broker_start = true; 4, Re-create dataguard broker configuraation DGMGRL> CONNECT sys; Password: password Connected. DGMGRL> CREATE CONFIGURATION 'PRIMARY' AS > PRIMARY DATABASE IS 'PRIMARY' > CONNECT IDENTIFIER IS PRIMARY; DGMGRL> ADD DATABASE 'STANDBY' AS > CONNECT IDENTIFIER IS STANDBY; DGMGRL> ENABLE CONFIGURATION; Enabled. DGMGRL> SHOW CONFIGURATION;
RMAN-04014: startup failed: ORA-00439: feature not enabled: Real Application Clusters
Problem description: Use Rman duplicate to create standby database, if failed with following error message: RMAN-00571: =========================================================== RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS =============== RMAN-00571: =========================================================== RMAN-03002: failure of Duplicate Db command at 05/24/2013 11:50:18 RMAN-05501: aborting duplication of target database RMAN-03015: error occurred in stored script Memory Script RMAN-04014: startup failed: ORA-00439: feature not enabled: Real Application Clusters Cause: This error happens when the primary database is in RAC and the standby database running on single instance. During the duplicate the RMAN will read the source control file, its RAC parameters and try to duplicate RAC as well which is not possible as the standby is running on single instance. Solution: Add SET CLUSTER_DATABASE=’FALSE’ in RMAN duplicate command.
Tuesday, May 21, 2013
Config VIP on a newly added cluster node
After I added a new node to the cluster, somehow VIP is not configured automatically. $ srvctl status nodeapps -n rac3-lnx Network is enabled Network is running on node: rac3-lnx GSD is enabled GSD is not running on node: rac3-lnx ONS is enabled ONS daemon is running on node: rac3-lnx PRKO-2165 : VIP does not exist on node(s) : rac3-lnx executed as root to add vip on node3 srvctl add vip -n rac3-lnx -A rac3-v/255.255.255.0 -k 1 -v Start VIP on node3 $ srvctl start vip -n rac3-lnx $ srvctl config vip -n rac3-lnx VIP exists: /rac3-v/172.24.194.203/172.24.194.0/255.255.255.0/bond0, hosting node rac3-lnx $ srvctl status nodeapps -n rac3-lnx VIP rac3-v is enabled VIP rac3-v is running on node: rac3-lnx Network is enabled Network is running on node: rac3-lnx GSD is enabled GSD is not running on node: rac3-lnx ONS is enabled ONS daemon is running on node: rac3-lnx rac3-lnx (oracle)
find all trace file cover certain time period and copy to another directory.
Following is the command I used to copy all trace file cover periods between 1pm to 2pm on 2013-05-18 to a tmp directory
cp $(grep '2013-05-18 13:' *trc|awk -F: '{print $1}'|uniq) /tmp
Deleted trace files still holding by Oracle process
Problem description
delete an big trace file under bdump , but storage was not released
Symptoms
$ du -sh /u02/oracle/rac
31M
$ df -h /u02/oracle/rac
Filesystem Size Used Avail Use% Mounted on
/dev/mapper/rac3--vg01-u02_rac
16G 15G 316M 98% /u02/oracle/rac
$/usr/sbin/lsof /u02/oracle/niids
COMMAND PID USER FD TYPE DEVICE SIZE NODE NAME
COMMAND PID USER FD TYPE DEVICE SIZE NODE NAME
oracle 1177 oracle 2w REG 253,239 798 81929 /u02/oracle/rac/logs/bdump/RAC3_ora_1177.trc
oracle 1177 oracle 5w REG 253,239 893 17213 /u02/oracle/rac/logs/udump/RAC3_ora_25913.trc (deleted)
oracle 1177 oracle 6w REG 253,239 2565 81922 /u02/oracle/rac/logs/bdump/alert_RAC3.log
oracle 1177 oracle 8w REG 253,239 2565 81922 /u02/oracle/rac/logs/bdump/alert_RAC3.log
oracle 1949 oracle 5w REG 253,239 893 17213 /u02/oracle/rac/logs/udump/RAC3_ora_25913.trc (deleted)
oracle 1949 oracle 6w REG 253,239 2565 81922 /u02/oracle/rac/logs/bdump/alert_RAC3.log
oracle 1949 oracle 8w REG 253,239 2565 81922 /u02/oracle/rac/logs/bdump/alert_RAC3.log
oracle 7147 oracle 2w REG 253,239 743 89415 /u02/oracle/rac/logs/bdump/RAC3_nsv0_7147.trc (deleted)
oracle 7147 oracle 5w REG 253,239 893 17213 /u02/oracle/rac/logs/udump/RAC3_ora_25913.trc (deleted)
oracle 7147 oracle 6w REG 253,239 2565 81922 /u02/oracle/rac/logs/bdump/alert_RAC3.log
oracle 7147 oracle 8w REG 253,239 2565 81922 /u02/oracle/rac/logs/bdump/alert_RAC3.log
oracle 7147 oracle 9w REG 253,239 2565 81922 /u02/oracle/rac/logs/bdump/alert_RAC3.log
oracle 9497 oracle 2w REG 253,239 229376 89437 /u02/oracle/rac/logs/bdump/RAC3_lns1_9497.trc
oracle 9497 oracle 5w REG 253,239 893 17213 /u02/oracle/rac/logs/udump/RAC3_ora_25913.trc (deleted)
oracle 9497 oracle 6w REG 253,239 2565 81922 /u02/oracle/rac/logs/bdump/alert_RAC3.log
oracle 9497 oracle 8w REG 253,239 2565 81922 /u02/oracle/rac/logs/bdump/alert_RAC3.log
oracle 9497 oracle 9w REG 253,239 2565 81922 /u02/oracle/rac/logs/bdump/alert_RAC3.log
ksh 13250 oracle cwd DIR 253,239 4096 2 /u02/oracle/rac
oracle 14578 oracle 5w REG 253,239 893 17213 /u02/oracle/rac/logs/udump/RAC3_ora_25913.trc (deleted)
oracle 14578 oracle 6w REG 253,239 2565 81922 /u02/oracle/rac/logs/bdump/alert_RAC3.log
oracle 14578 oracle 8w REG 253,239 2565 81922 /u02/oracle/rac/logs/bdump/alert_RAC3.log
oracle 17116 oracle 2w REG 253,239 15650070528 89291 /u02/oracle/rac/logs/bdump/RAC3_j000_17116.trc (deleted)
oracle 17116 oracle 5w REG 253,239 893 17213 /u02/oracle/rac/logs/udump/RAC3_ora_25913.trc (deleted)
Cause:
There is still oracle process holding deleted files
Solution:
restart the instance
srvctl stop instance -d RAC -i RAC3
srvctl start instance -d RAC -i RAC3
Monday, May 20, 2013
Add a new node, Run root.sh failed with " Configuration of ASM ... failed "
problem description
Adding 3rd nodes on a 2 node cluster, execute root.sh on 3rd nodes.
$ sudo /u01/app/crs/11.2.0.3/root.sh
Performing root user operation for Oracle 11g
The following environment variables are set as:
ORACLE_OWNER= oracle
ORACLE_HOME= /u01/app/crs/11.2.0.3
Enter the full pathname of the local bin directory: [/usr/local/bin]:
Copying dbhome to /usr/local/bin ...
Copying oraenv to /usr/local/bin ...
Copying coraenv to /usr/local/bin ...
Entries will be added to the /etc/oratab file as needed by
Database Configuration Assistant when a database is created
Finished running generic part of root script.
Now product-specific root actions will be performed.
Using configuration parameter file: /u01/app/crs/11.2.0.3/crs/install/crsconfig_params
Creating trace directory
User ignored Prerequisites during installation
OLR initialization - successful
Adding Clusterware entries to inittab
CRS-2672: Attempting to start 'ora.mdnsd' on 'rac3-lnx'
CRS-2676: Start of 'ora.mdnsd' on 'rac3-lnx' succeeded
CRS-2672: Attempting to start 'ora.gpnpd' on 'rac3-lnx'
CRS-2676: Start of 'ora.gpnpd' on 'rac3-lnx' succeeded
CRS-2672: Attempting to start 'ora.cssdmonitor' on 'rac3-lnx'
CRS-2672: Attempting to start 'ora.gipcd' on 'rac3-lnx'
CRS-2676: Start of 'ora.cssdmonitor' on 'rac3-lnx' succeeded
CRS-2676: Start of 'ora.gipcd' on 'rac3-lnx' succeeded
CRS-2672: Attempting to start 'ora.cssd' on 'rac3-lnx'
CRS-2672: Attempting to start 'ora.diskmon' on 'rac3-lnx'
CRS-2676: Start of 'ora.diskmon' on 'rac3-lnx' succeeded
CRS-2676: Start of 'ora.cssd' on 'rac3-lnx' succeeded
Symptoms
found following error in ocssd.log
2013-05-20 18:28:17.654: [ SKGFD][1098938688]ERROR: -15(asmlib ASM:/opt/oracle/extapi/64/asm/orcl/1/libasm.so op asm_open error Operation not permitted
)
2013-05-20 18:28:17.654: [ SKGFD][1098938688]ERROR: -15(asmlib ASM:/opt/oracle/extapi/64/asm/orcl/1/libasm.so op asm_open error Operation not permitted
)
2013-05-20 18:28:17.654: [ SKGFD][1098938688]ERROR: -15(asmlib ASM:/opt/oracle/extapi/64/asm/orcl/1/libasm.so op asm_open error Operation not permitted
)
2013-05-20 18:28:17.654: [ SKGFD][1098938688]ERROR: -15(asmlib ASM:/opt/oracle/extapi/64/asm/orcl/1/libasm.so op asm_open error Operation not permitted
)
2013-05-20 18:28:17.654: [ CSSD][1098938688]clssnmvDiskVerify: Successful discovery of 0 disks
2013-05-20 18:28:17.655: [ CSSD][1098938688]clssnmCompleteInitVFDiscovery: Completing initial voting file discovery
2013-05-20 18:28:17.655: [ CSSD][1098938688]clssnmvFindInitialConfigs: No voting files found
2013-05-20 18:28:17.655: [ CSSD][1098938688](:CSSNM00070:)clssnmCompleteInitVFDiscovery: Voting file not found. Retrying discovery in 15 seconds
Cause
By default, Oracle Database ASM instance parameter 'ASM_DISKSTRING' is unset and therefore uses a default value of '*'. This means that the ASM instance scans both/dev/raw/and/dev/oracleasm/disks/directories when searching for oracle ASM disks.
When using a combination of oracleasm disks (/dev/oracleasm/disks/*) with raw devices (/dev/raw/*), in particular where some raw devices are not owned by or accessible to the oracle user) as well as ASM instance parameter 'ASM_DISKSTRING' with an unspecified value, error ORA-15186 is likely to occur.
Solution
1. either edit the file /etc/sysconfig/oracleasm-_dev_oracleasm and change the lines:
ORACLEASM_SCANORDER=""
ORACLEASM_SCANEXCLUDE=""
to
ORACLEASM_SCANORDER="dm"
ORACLEASM_SCANEXCLUDE="sd"
or alternatively run the following command (as user root)
/usr/sbin/oracleasm configure -i -e -u user -g group -o "dm" -x "sd"
2. stop & restart ASMLIB as user root using:
/usr/sbin/oracleasm exit
/usr/sbin/oracleasm init
3. restart CRS or reboot node
The above steps need to be executed on all nodes
Relocate service in RAC environment
TEMPORARILY RELOCATE SERVICE FROM ONE NODE TO ANOTHER NODE IN CLUSTER $ srvctl status service -d RAC Service RAC_S is running on instance(s) RAC1 $ srvctl relocate service -d RAC -s RAC_S -i RAC1 -t RAC2 $ srvctl status service -d RAC Service RAC_S is running on instance(s) RAC2 PERMANENT RELOCATE SERVICE FROM ONE NODE OF THE CLUSTER TO ANOTHER $ srvctl config service -d RAC Service name: RAC_S Service is enabled Server pool: RAC_RAC_S Cardinality: 1 Disconnect: false Service role: PRIMARY Management policy: AUTOMATIC DTP transaction: false AQ HA notifications: false Failover type: NONE Failover method: NONE TAF failover retries: 0 TAF failover delay: 0 Connection Load Balancing Goal: LONG Runtime Load Balancing Goal: NONE TAF policy specification: NONE Edition: servPreferred instances: RAC1 Available instances: RAC2 $ srvctl modify service -d RAC -s RAC_S -n -i RAC2 -a RAC1 $ srvctl config service -d RAC Service name: RAC_S Service is enabled Server pool: RAC_RAC_S Cardinality: 1 Disconnect: false Service role: PRIMARY Management policy: AUTOMATIC DTP transaction: false AQ HA notifications: false Failover type: NONE Failover method: NONE TAF failover retries: 0 TAF failover delay: 0 Connection Load Balancing Goal: LONG Runtime Load Balancing Goal: NONE TAF policy specification: NONE Edition: Preferred instances: RAC2 Available instances: RAC1
Friday, May 17, 2013
Prerequisite check "CheckActiveFilesAndExecutables" failed while applying opatch
Problem description: applying opatch failled: $ /u01/app/crs/11.2.0.2/OPatch/opatch napply -oh /u01/app/oracle/product/11.2.0.2/db_2 -local /dba/staging/patches/16056267/ Oracle Interim Patch Installer version 11.2.0.3.4 Copyright (c) 2012, Oracle Corporation. All rights reserved. Oracle Home : /u01/app/oracle/product/11.2.0.2/db_2 Central Inventory : /u01/app/oraInventory from : /u01/app/oracle/product/11.2.0.2/db_2/oraInst.loc OPatch version : 11.2.0.3.4 OUI version : 11.2.0.2.0 Log file location : /u01/app/oracle/product/11.2.0.2/db_2/cfgtoollogs/opatch/opatch2013-05-16_12-23-56PM_1.log Verifying environment and performing prerequisite checks... Prerequisite check "CheckActiveFilesAndExecutables" failed. The details are: Following executables are active : /u01/app/oracle/product/11.2.0.2/db_2/lib/libclntsh.so.11.1 UtilSession failed: Prerequisite check "CheckActiveFilesAndExecutables" failed. Log file location: /u01/app/oracle/product/11.2.0.2/db_2/cfgtoollogs/opatch/opatch2013-05-16_12-23-56PM_1.log OPatch failed with error code 73 Cause of problem: Something is using oracle library. Solution of problem: Using fuser to find which process is using that library and kill it. /sbin/fuser /u01/app/oracle/product/11.2.0.2/db_2/lib/libclntsh.so.11.1 /u01/app/oracle/product/11.2.0/db_2/lib/libclntsh.so.11.1: 12195m ps -ef|grep 12195 kill -9 12195
Monday, May 13, 2013
RMAN configure archivelog deletion policy in Data Guard
In Primary Database RMAN>configure archivelog deletion policy to applied on all standby; In Physical Standby Database where backup is not taken RMAN>configure archivelog deletion policy to applied on standby; In Physical Standby Database where backup is taken RMAN>configure archivelog deletion policy to NONE;
ACFS not mounted after restart on standalone server
Problem description:
After reboot, ACFS is not mounted.
Cause of problem:
Oracle Restart does not support root-based Oracle ACFS resources. As a result, the following operations are not automatically performed:
Loading Oracle ACFS drivers
Mounting Oracle ACFS file systems listed in the Oracle ACFS mount registry
Mounting resource-based Oracle ACFS database home file systems
Solution of problem:
1. Manually load the modules required for ACFS, with the command:
# lsmod |grep oracle
oracleasm 84136 1
#/u01/app/crs/11.2.0.2/bin/acfsload start –s
ACFS-9327: Verifying ADVM/ACFS devices.
ACFS-9322: completed
#lsmod |grep oracle
oracleacfs 1734520 0
oracleadvm 242048 0
oracleoks 295248 2 oracleacfs,oracleadvm
oracleasm 84136 1
2. Enable ACFS Volume:
ASMCMD> volinfo -a
Diskgroup Name: DATAPUMP_DATA
Volume Name: DATAPUMPVOL
Volume Device: /dev/asm/datapumpvol-438
State: DISABLED
Size (MB): 613376
Resize Unit (MB): 256
Redundancy: UNPROT
Stripe Columns: 4
Stripe Width (K): 128
Usage: ACFS
Mountpath: /datapump
ASMCMD> volenable -G DATAPUMP_DATA DATAPUMPVOL
ASMCMD> volinfo -a
Diskgroup Name: DATAPUMP_DATA
Volume Name: DATAPUMPVOL
Volume Device: /dev/asm/datapumpvol-438
State: ENABLED
Size (MB): 613376
Resize Unit (MB): 256
Redundancy: UNPROT
Stripe Columns: 4
Stripe Width (K): 128
Usage: ACFS
Mountpath: /datapump
ASMCMD> volstat -G DATAPUMP_DATA
3. Mount ACFS and change owner to oracle
/bin/mount -t acfs /dev/asm/datapumpvol-438 /datapump
chown oracle:oinstall /datapump
create a new filesystem on linux
Having the device ready
[oracle@rac1 ~]$ ls -al /dev/sd*
brw-r----- 1 root disk 8, 0 May 13 08:42 /dev/sda
brw-r----- 1 root disk 8, 1 May 13 08:42 /dev/sda1
brw-r----- 1 root disk 8, 2 May 13 08:42 /dev/sda2
brw-r----- 1 root disk 8, 16 May 13 08:42 /dev/sdb
brw-r----- 1 root disk 8, 17 May 13 08:44 /dev/sdb1
brw-r----- 1 root disk 8, 32 May 13 08:42 /dev/sdc
brw-r----- 1 root disk 8, 33 May 13 08:44 /dev/sdc1
brw-r----- 1 root disk 8, 48 May 13 08:42 /dev/sdd
brw-r----- 1 root disk 8, 49 May 13 08:44 /dev/sdd1
brw-r----- 1 root disk 8, 64 May 13 08:42 /dev/sde
brw-r----- 1 root disk 8, 65 May 13 08:44 /dev/sde1
brw-r----- 1 root disk 8, 80 May 13 08:42 /dev/sdf
brw-r----- 1 root disk 8, 81 May 13 08:44 /dev/sdf1
brw-r----- 1 root disk 8, 96 May 13 08:42 /dev/sdg
brw-r----- 1 root disk 8, 97 May 13 08:42 /dev/sdg1
brw-r----- 1 root disk 8, 112 May 13 08:42 /dev/sdh
Partitioning with fdisk
[root@rac1 ~]# fdisk /dev/sdh
Device contains neither a valid DOS partition table, nor Sun, SGI or OSF disklabel
Building a new DOS disklabel. Changes will remain in memory only,
until you decide to write them. After that, of course, the previous
content won't be recoverable.
The number of cylinders for this disk is set to 6527.
There is nothing wrong with that, but this is larger than 1024,
and could in certain setups cause problems with:
1) software that runs at boot time (e.g., old versions of LILO)
2) booting and partitioning software from other OSs
(e.g., DOS FDISK, OS/2 FDISK)
Warning: invalid flag 0x0000 of partition table 4 will be corrected by w(rite)
Command (m for help): n
Command action
e extended
p primary partition (1-4)
p
Partition number (1-4): 1
First cylinder (1-6527, default 1):
Using default value 1
Last cylinder or +size or +sizeM or +sizeK (1-6527, default 6527):
Using default value 6527
Command (m for help): p
Disk /dev/sdh: 53.6 GB, 53687091200 bytes
255 heads, 63 sectors/track, 6527 cylinders
Units = cylinders of 16065 * 512 = 8225280 bytes
Device Boot Start End Blocks Id System
/dev/sdh1 1 6527 52428096 83 Linux
Command (m for help): w
The partition table has been altered!
Calling ioctl() to re-read partition table.
Syncing disks.
Creating an ext3 filesystem
[root@rac1 ~]# mkfs -t ext3 /dev/sdh1
mke2fs 1.39 (29-May-2006)
Filesystem label=
OS type: Linux
Block size=4096 (log=2)
Fragment size=4096 (log=2)
6553600 inodes, 13107024 blocks
655351 blocks (5.00%) reserved for the super user
First data block=0
Maximum filesystem blocks=4294967296
400 block groups
32768 blocks per group, 32768 fragments per group
16384 inodes per group
Superblock backups stored on blocks:
32768, 98304, 163840, 229376, 294912, 819200, 884736, 1605632, 2654208,
4096000, 7962624, 11239424
Writing inode tables: done
Creating journal (32768 blocks):
done
Writing superblocks and filesystem accounting information:
done
This filesystem will be automatically checked every 20 mounts or
180 days, whichever comes first. Use tune2fs -c or -i to override.
Create a mount point:
[root@rac1 u01]# mkdir /u01
Edit the /etc/fstab to include the new partition
# vi /etc/fstab
Append the new line as follows:
/dev/sdh1 /u01 ext3 defaults 1 2
Mount the filesystem
[root@rac1 u01]# mkdir /u01
Friday, May 10, 2013
crsctl stop crs failed if there is ACFS mount point
Problem description:
# crsctl stop crs
CRS-2791: Starting shutdown of Oracle High Availability Services-managed resources on 'oratest1-lnx'
CRS-2673: Attempting to stop 'ora.crsd' on 'oratest1-lnx'
CRS-2790: Starting shutdown of Cluster Ready Services-managed resources on 'oratest1-lnx'
CRS-2673: Attempting to stop 'ora.DATAPUMP_DATA.dg' on 'oratest1-lnx'
CRS-2675: Stop of 'ora.DATAPUMP_DATA.dg' on 'oratest1-lnx' failed
CRS-2799: Failed to shut down resource 'ora.DATAPUMP_DATA.dg' on 'oratest1-lnx'
CRS-2794: Shutdown of Cluster Ready Services-managed resources on 'oratest1-lnx' has failed
CRS-2675: Stop of 'ora.crsd' on 'oratest1-lnx' failed
CRS-2799: Failed to shut down resource 'ora.crsd' on 'oratest1-lnx'
CRS-2795: Shutdown of Oracle High Availability Services-managed resources on 'oratest1-lnx' has failed
CRS-4687: Shutdown command has completed with error(s).
CRS-4000: Command Stop failed, or completed with errors.
Cause of problem:
There is an ACFS mount point
df -h
/dev/asm/datapumpvol-371
167772160 398368 167373792 1% /datapump
Solution of problem:
# umount /datapump
# crsctl stop crs
cp -rf asking for "cp: overwrite ... ?"
Problem description: $ cp -rf OPatch/* OPatch_OLD/ cp: overwrite `OPatch_OLD/crs/auto_patch.pl'? Cause of problem: $ alias |grep cp cp='cp -i' Solution of problem: $ 'cp' -rf OPatch/* OPatch_OLD/
Tuesday, May 7, 2013
Merge statement performance issue on RAC enviornment
Problem description: Merge statement has fluctuating performance issue in RAC database. Cause of the Problem: Merge statment request blocks from different nodes Solution of the Problem: It is recommended to run merge in one singe node, and it is doable in RAC to make sure the connection to one node only. 1. add service RAC_S srvctl add service -d RAC -s RAC_S -r RAC1 -a RAC2 2. start service RAC_S srvctl start service -d RAC -s RAC_S 3. check service status srvctl status service -d RAC -s RAC_S 4. Verify service is added to RAC database SQL> show parameter servcie
Monday, May 6, 2013
PL/SQL package PRICE_RMAN.DBMS_RCVCAT version 11.02.00.02 in RCVCAT database is not current
Problem Description While starting Oracle RMAN backup whenever we use catalog database as repository it fails with following errors: $ rman target / catalog catowner@rman_catdb Recovery Manager: Release 11.2.0.3.0 - Production on Mon May 6 10:06:37 2013 Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved. connected to target database: METHDEV (DBID=2414256617) connected to recovery catalog database PL/SQL package PRICE_RMAN.DBMS_RCVCAT version 11.02.00.02 in RCVCAT database is not current PL/SQL package PRICE_RMAN.DBMS_RCVMAN version 11.02.00.02 in RCVCAT database is not current Try to resync catalog, it fails with forllowing errors: RMAN> resync catalog; RMAN-00571: =========================================================== RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS =============== RMAN-00571: =========================================================== RMAN-03009: failure of resync command on default channel at 05/06/2013 10:07:05 RMAN-10015: error compiling PL/SQL program RMAN-10014: PL/SQL error 0 on line 1655 column 12: Statement ignored RMAN-10014: PL/SQL error 306 on line 1655 column 12: wrong number or types of arguments in call to 'CHECKTABLESPACE' RMAN> exit Recent Changes The target database has been recently upgrade to 11.2.0.3 and so RMAN executable is upgraded but catalog database is not aware of this upgrade. Cause of the Problem The problem happened due to version mismatch between the RMAN-executable and the RMAN-catalog SCHEMA. Solution of the Problem Solution 01: Upgrade the RMAN catalog SCHEMA. Start the RMAN-executable from the ORACLE_HOME which has been upgraded. There is only a connection to the CATALOG required. A connection to the TARGET is optional. For example issue, $ rman catalog $RMAN_USERID/$RMAN_PASSWD@$RMAN_CONN_STR RMAN> upgrade catalog; Solution 02: Don't use catalog for backup information. So run backup without connecting catalog database. $ rman target / $ backup database;
Friday, May 3, 2013
Upgrade to 11.2.0.3 - OCM: ORA-12012 and ORA-29280
Symptoms:
After updated database to 11.2.0.3, getting following error in alert log:
Errors in file /opt/oracle/diag/rdbms/db/trace/db_j001_26027.trc:
ORA-12012: error on auto execute of job "ORACLE_OCM"."MGMT_CONFIG_JOB_2_2"
ORA-29280: invalid directory path
ORA-06512: at "ORACLE_OCM.MGMT_DB_LL_METRICS", line 2436
ORA-06512: at line 1
Cause:
OCM is the Oracle Configuration Manager, a tool to proactively monitor your Oracle environment to provide this information to Oracle Software Support.
OCM is trying to write to a local directory which does not exist. Besides that the OCM version delivered with Oracle Database Patch Set 11.2.0.3 is older than the newest available OCM Collector 10.3.7 - the one which has that issue fixed.
Solutions:
you'll either drop OCM completely if you won't use it:
SQL> drop user ORACLE_OCM cascade;
you'll disable the collector jobs:
SQL> exec dbms_scheduler.disable('ORACLE_OCM.MGMT_CONFIG_JOB');
SQL> exec dbms_scheduler.disable('ORACLE_OCM.MGMT_STATS_CONFIG_JOB');
Refer to MOS Note: [ID 1453959.1]
Wednesday, May 1, 2013
ora.diskmon offline on 11.2.0.3
As Grid Infrastructure daemon diskmon.bin is used for Exadata fencing, started from 11.2.0.3, resource ora.diskmon will be offline in non-Exadata environment. This is expected behaviour change.
$ crsctl stat res -t -init
--------------------------------------------------------------------------------
NAME TARGET STATE SERVER STATE_DETAILS
--------------------------------------------------------------------------------
Cluster Resources
--------------------------------------------------------------------------------
ora.asm
1 ONLINE ONLINE pricedev1-lnx Started
ora.cluster_interconnect.haip
1 ONLINE ONLINE pricedev1-lnx
ora.crf
1 ONLINE ONLINE pricedev1-lnx
ora.crsd
1 ONLINE ONLINE pricedev1-lnx
ora.cssd
1 ONLINE ONLINE pricedev1-lnx
ora.cssdmonitor
1 ONLINE ONLINE pricedev1-lnx
ora.ctssd
1 ONLINE ONLINE pricedev1-lnx OBSERVER
ora.diskmon
1 OFFLINE OFFLINE
ora.drivers.acfs
1 ONLINE ONLINE pricedev1-lnx
ora.evmd
1 ONLINE ONLINE pricedev1-lnx
ora.gipcd
1 ONLINE ONLINE pricedev1-lnx
ora.gpnpd
1 ONLINE ONLINE pricedev1-lnx
ora.mdnsd
1 ONLINE ONLINE pricedev1-lnx
Modifying AWR Automatic Snapshot Settings
Verify Current setting:
SQL> set linesize 100
SQL> col snap_interval format a20
SQL> col retention format a20
SQL> col topnsql format a20
SQL> select * from dba_hist_wr_control;
DBID SNAP_INTERVAL RETENTION TOPNSQL
---------- -------------------- -------------------- --------------------
2684842560 +00000 01:00:00.0 +00008 00:00:00.0 DEFAULT
Change the interval to 10 minutes and retention to 10 days:
SQL> execute dbms_workload_repository.modify_snapshot_settings( interval => 10,retention => 14400);
Verify change:
SQL> select * from dba_hist_wr_control;
DBID SNAP_INTERVAL RETENTION TOPNSQL
---------- -------------------- -------------------- --------------------
2684842560 +00000 00:10:00.0 +00010 00:00:00.0 DEFAULT
Tuesday, April 30, 2013
High 'direct path read' waits in 11g
Symptoms: See high 'direct path read' wait when query a large table. cause: In 10g, serial table scans for "large" tables used to go through cache (by default) which is not the case anymore. In 11g, this decision to read via direct path or through cache is based on the size of the table, buffer cache size and various other stats. Direct path reads are faster than scattered reads and have less impact on other processes because they avoid latches. Solution: When using Automatic Shared Memory Management (ASMM) and with buffer cache low limit set at a low end compared to the normal workload requirements and usually after startup, 11g might choose to do serial direct path read scans for large tables that do not fit in the SGA. When ASMM increases the buffer cache due to increased demand, 11g might not again do serial direct path read scans for these same large tables. If you like to avoid this from happening, you should note the buffer cache and share pool requirements for a normal workload and set the low limits of buffer cache and shared pool in spfile/pfile close to these normal workload values. db_cache_size shared_pool_size From MOS Note: [ID 793845.1]
Friday, April 26, 2013
DBUA failed in bringing up the database. Either the database is not running from Oracle Home or the correct pfile was not found
Symptoms: When using dbua to upgrade a database, you might get a pop-up asking for the location of the init.ora, with a message like this: "Either the database is not running from Oracle Home or the correct pfile was not found" Cause: An earlier upgrade attempt failed, and a dbua file got left "in limbo" Solution: cd /u01/app/oracle/cfgtoollogs/dbua/logs ls PreUpgradeResults.html sqls.log trace.log Welcome_mydb.txt rm Welcome_mydb.txt From MOS Note: [ID 1303325.1]
Wednesday, April 24, 2013
Setup active dataguard using dgmgrl
1. Set apply off
DGMGRL> edit database 'DBAPLC' set state=apply-off;
Succeeded.
DGMGRL> show database 'DBAPLC'
Database - DBAPLC
Role: PHYSICAL STANDBY
Intended State: APPLY-OFF
Transport Lag: 0 seconds
Apply Lag: 0 seconds
Real Time Query: OFF
Instance(s):
DBAPLC
Database Status:
SUCCESS
2. Open Standby database
SQL> alter database open; Database altered.3. Set apply on
DGMGRL> edit database 'DBAPLC' set state=apply-on;
Succeeded.
DGMGRL> show database 'DBAPLC'
Database - DBAPLC
Role: PHYSICAL STANDBY
Intended State: APPLY-ON
Transport Lag: 0 seconds
Apply Lag: 0 seconds
Real Time Query: ON
Instance(s):
DBAPLC
Database Status:
SUCCESS
Determining CPU/core counts on Linux Server
Here are the commands that can be used to determine the physical CPU/core and logical CPU counts on a Linux server. 1. Determining the number of logical processors seen by the OS: cat /proc/cpuinfo | grep ^processor | wc -l 2. Determining the number of physical CPUs on a machine: cat /proc/cpuinfo | grep "physical id" | uniq | wc -l 3. Determining the number of cores per CPU: cat /proc/cpuinfo | grep "cores" | head -1 4. Determining if Hyperthreading is turned on: a. First determine the number of siblings per CPU. Siblings are the number of logical processors sharing the same physical processor: cat /proc/cpuinfo | grep "siblings" | head -1 b. If the number of CPU cores per processor (from #2 above) is the same as the number of siblings, then Hyperthreading is off. If siblings is 2 times the number of cores, Hyperthreading is turned on.
Tuesday, April 23, 2013
ORA-39095: Dump file space has been exhausted
Problem Description: datapump export got following error: ORA-39095: Dump file space has been exhausted: Unable to allocate 4096 bytes Cause of the Error: Note that %U specification for the dump file can expand up to 99 files. If 99 files have been generated before the export has completed, it will again return the ORA-39095 error. Solution of the Error: Use a bigger filesize value.
Add a 3rd node to Oracle RAC 11gr2
1 Verify the prerequisites with cluvfy
Login as a grid user on one of the existing cluster nodes, for example raclinux1, and run the following commands.
cluvfy stage -post hwos -n rac3
cluvfy comp peer -refnode rac1 -n rac3
cluvfy stage -pre nodeadd -n rac3
cluvfy stage -pre crsinst -n rac3
2 Ignore error due to Bug #10310848
If your shared storage is ASM using asmlib you may get an error – similar to the following
ERROR:
PRVF-5449 : Check of Voting Disk location "ORCL:CRS1(ORCL:CRS1)" failed on the following nodes:
rac3:No such file or directory
PRVF-5431 : Oracle Cluster Voting Disk configuration check failed
To ignore it:
IGNORE_PREADDNODE_CHECKS=Y
export IGNORE_PREADDNODE_CHECKS
3 Extend Clusterware
addNode.sh "CLUSTER_NEW_NODES={rac3}" "CLUSTER_NEW_VIRTUAL_HOSTNAMES={rac3-vip}"
execute following as root on new node:
/u01/app/oraInventory/orainstRoot.sh
/u01/app/crs/11.2.0,2/root.sh
verify new node:
cluvfy stage -post crsinst -n rac3-lnx
cluvfy stage -post nodeadd -n rac3-lnx
4 Extend Oracle Database Software
$ORACLE_HOME/oui/bin/addNode.sh -silent "CLUSTER_NEW_NODES={rac3}"
5 Use DBCA to add database instance on new nodes
Subscribe to:
Posts (Atom)