Blocksize for Datafiles,Redo log files and Control files

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

Advertisements

ORA-600[2130], [2], [1], [2]

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 ūüôā

EXPDP – exclude parameter

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.

Session 1 –

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

Session 2 –

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

Session 1 –

10:43:53 SYS @ BRAVE >drop user anand cascade;

User dropped.

Session 2 –

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

Session 1 –

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;