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
Friday, August 30, 2013
How to check database PSU version number
You can check database PSU version number using following 2 ways :
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.
Subscribe to:
Posts (Atom)