Few times this question passed by my mind but don’t know why i never thought much over it – “Are the block sizes for the datafiles,redo log files and control files the same”.Thanks to Aman, for writing on it, in his blog.Simple and well explained.Definitely, read it 🙂
http://blog.aristadba.com/?p=124
I had an interesting case of ORA-600 [2130], [2], [1], [2] few days back, which i am going to share in this post 🙂
As part of Oracle streams setup , a downstream capture database was created by me manually. What is Downstream Capture Database ???With Oracle 10g,it is possible to configure the capture process on a database other than the source database. The database where the capture process is configured is called the “Downstream Database” and the capture process is referred to as “Downstream Capture”.
The source database was a 2-node RAC database, and capture database a single instance database.Little background about the capture part,is the archive log files generated at the source database would come to capture database where it would get logmined by the logminer process and LCRs will be created.For enabling the real time mining standby redo log files are required in the capture database.
After creating the standby redo log files on the capture database and enabling real time mining using DBMS_CAPTURE_ADM ,alert log was full with ORA-600.ORA-600 [2130] error was continuously reported by the archiver process on the Downstream database.The moment these standby redo log files were dropped, no more ORA-600.Hmmmm, sounds interesting, isn’t it!!!!!!
The reason being “When i manually created the Downstream database, i had set the MAXINSTANCES to 1 “.As per metalink
The Downstream’s database controlfile has been created with an incorrect MAXINSTANCES value. The MAXINSTANCES value needs to be equal to the maximum number of instances (redo threads) which are available on any source database.
Recreated the controlfile with MAXINTANCES 2 and no more ORA-600.
I think it was good that i had created it with value 1, i learned something new 🙂
There seems to be some confusion with the “EXCLUDE” clause in expdp.The confusion is,when a schema level expdp is taken with few table names(one or more) in the exclude parameter and imported, these excluded tables will be created (metadata only) but won’t have data.It means even if we exclude the tables while export, its metadata is present in the dumpfile, which is “WRONG”. Here’s a test to proof its WRONG !!!!!!
As per Oracle Document :-
EXCLUDE
Default: none
Purpose
Enables you to filter the metadata that is exported by specifying objects and object types that you want excluded from the export operation.
I have a schema “ANAND” with 4 tables.I will take a schema level export, excluding 1 table, drop the schema “ANAND” and import the dump.
10:29:49 SYS @ BRAVE >@users USERNAME ACCOUNT_STATUS DEFAULT_TABLESPACE TEMPORARY_TBS PROFILE ------------------------- -------------------------------- ------------------------------ --------------- -------- ANAND OPEN ANDY TEMP DEFAULT 10:29:52 SYS @ BRAVE >@schema_table_size Enter value for owner_name: anand OWNER SEGMENT_NAME SEGMENT_TYPE MB ------------------------------ ---------------------------------------- ------------------ ---------- ANAND LOCK_TEST TABLE .125 ANAND ORDERS TABLE .0625 ANAND PROCESS_STATE_BKP TABLE .0625 ANAND PROCESS_STATE TABLE .0625
C:\Documents and Settings\user>expdp directory=DATA_PUMP_DIR dumpfile=anand_test.dmp logfile=anand_test.log schemas=anand exclude=TABLE:\"IN(\'PROCESS_STATE\')\" job_name=anand_expdp Export: Release 10.2.0.4.0 - Production on Friday, 02 April, 2010 10:42:38 Copyright (c) 2003, 2007, Oracle. All rights reserved. Username: sys as sysdba Password: Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - Production With the Partitioning, OLAP, Data Mining and Real Application Testing options Starting "SYS"."ANAND_EXPDP": sys/******** AS SYSDBA directory=DATA_PUMP_DIR dumpfile=anand_test.dmp logfile=anand_test.log schemas=anand exclude=TABLE:"IN(\'PROCESS_STATE\')" job_name=anand_expdp Estimate in progress using BLOCKS method... Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA Total estimation using BLOCKS method: 256 KB Processing object type SCHEMA_EXPORT/USER Processing object type SCHEMA_EXPORT/SYSTEM_GRANT Processing object type SCHEMA_EXPORT/ROLE_GRANT Processing object type SCHEMA_EXPORT/DEFAULT_ROLE Processing object type SCHEMA_EXPORT/TABLESPACE_QUOTA Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA Processing object type SCHEMA_EXPORT/TABLE/TABLE Processing object type SCHEMA_EXPORT/TABLE/INDEX/INDEX Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS Processing object type SCHEMA_EXPORT/PROCEDURE/PROCEDURE Processing object type SCHEMA_EXPORT/PROCEDURE/ALTER_PROCEDURE Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS . . exported "ANAND"."LOCK_TEST" 51.25 KB 500 rows . . exported "ANAND"."ORDERS" 5.601 KB 4 rows . . exported "ANAND"."PROCESS_STATE_BKP" 5.593 KB 4 rows Master table "SYS"."ANAND_EXPDP" successfully loaded/unloaded ****************************************************************************** Dump file set for SYS.ANAND_EXPDP is: D:\ORACLE\PRODUCT\10.2.0\ADMIN\ORACLE\DPDUMP\ANAND_TEST.DMP Job "SYS"."ANAND_EXPDP" successfully completed at 10:43:44
10:43:53 SYS @ BRAVE >drop user anand cascade; User dropped.
C:\Documents and Settings\user>impdp directory=DATA_PUMP_DIR dumpfile=anand_test.dmp logfile=anand_test_imp.log full=y job_name=anand_impdp Import: Release 10.2.0.4.0 - Production on Friday, 02 April, 2010 10:45:17 Copyright (c) 2003, 2007, Oracle. All rights reserved. Username: sys as sysdba Password: Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - Production With the Partitioning, OLAP, Data Mining and Real Application Testing options Master table "SYS"."ANAND_IMPDP" successfully loaded/unloaded Starting "SYS"."ANAND_IMPDP": sys/******** AS SYSDBA directory=DATA_PUMP_DIR dumpfile=anand_test.dmp logfile=anand_test_imp.log full=y job_name=anand_impdp Processing object type SCHEMA_EXPORT/USER Processing object type SCHEMA_EXPORT/SYSTEM_GRANT Processing object type SCHEMA_EXPORT/ROLE_GRANT Processing object type SCHEMA_EXPORT/DEFAULT_ROLE Processing object type SCHEMA_EXPORT/TABLESPACE_QUOTA Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA Processing object type SCHEMA_EXPORT/TABLE/TABLE Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA . . imported "ANAND"."LOCK_TEST" 51.25 KB 500 rows . . imported "ANAND"."ORDERS" 5.601 KB 4 rows . . imported "ANAND"."PROCESS_STATE_BKP" 5.593 KB 4 rows Processing object type SCHEMA_EXPORT/TABLE/INDEX/INDEX Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS Processing object type SCHEMA_EXPORT/PROCEDURE/PROCEDURE Processing object type SCHEMA_EXPORT/PROCEDURE/ALTER_PROCEDURE Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS Job "SYS"."ANAND_IMPDP" successfully completed at 10:45:35
10:46:22 SYS @ BRAVE >@schema_table_size Enter value for owner_name: anand OWNER SEGMENT_NAME SEGMENT_TYPE MB ------------------------------ ---------------------------------------- ------------------ ---------- ANAND LOCK_TEST TABLE .125 ANAND PROCESS_STATE_BKP TABLE .0625 ANAND ORDERS TABLE .0625
The table “PROCESS_STATE” is not created after importing the dump.So, the exclude parameter filtered the “metadata” in the expdp.
The sqlfile output :-
-- new object type path is: SCHEMA_EXPORT/TABLE/TABLE
-- CONNECT SYS
CREATE TABLE "ANAND"."LOCK_TEST"
( "OWNER" VARCHAR2(30) NOT NULL ENABLE,
"OBJECT_NAME" VARCHAR2(30) NOT NULL ENABLE,
"SUBOBJECT_NAME" VARCHAR2(30),
"OBJECT_ID" NUMBER NOT NULL ENABLE,
"DATA_OBJECT_ID" NUMBER,
"OBJECT_TYPE" VARCHAR2(19),
"CREATED" DATE NOT NULL ENABLE,
"LAST_DDL_TIME" DATE NOT NULL ENABLE,
"TIMESTAMP" VARCHAR2(19),
"STATUS" VARCHAR2(7),
"TEMPORARY" VARCHAR2(1),
"GENERATED" VARCHAR2(1),
"SECONDARY" VARCHAR2(1)
) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
TABLESPACE "ANDY" ;
CREATE TABLE "ANAND"."ORDERS"
( "ORDER_ID" NUMBER,
"ORDER_DT" DATE,
"CUST_ID" NUMBER NOT NULL ENABLE
) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
TABLESPACE "ANDY" ;
CREATE TABLE "ANAND"."PROCESS_STATE_BKP"
( "JOB_ID" NUMBER,
"PROCESS_ID" NUMBER,
"PROCESS_CODE" VARCHAR2(3)
) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
TABLESPACE "ANDY" ;
-- new object type path is: SCHEMA_EXPORT/TABLE/INDEX/INDEX
-- CONNECT ANAND
CREATE INDEX "ANAND"."LOCK_TEST_IDX" ON "ANAND"."LOCK_TEST" ("OBJECT_NAME")
PCTFREE 10 INITRANS 2 MAXTRANS 255
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
TABLESPACE "ANDY" PARALLEL 1 ;
ALTER INDEX "ANAND"."LOCK_TEST_IDX" NOPARALLEL;
