Step by step setup ASM using os files in 11g

Environment: Windows
DB version: 11.1.0.6

–Creating the ASM Instance Manually, using dbca is also ok
–create CSS service if it doesn’t exist in services
WINNT> <ORACLE_HOME>\bin\localconfig add

–create 2 1000MB os files as asmdisk
asmtool -create D:\asm\asmdisk1 1000
asmtool -create D:\asm\asmdisk2 1000

–if get an error msg, using commnd “net helpmsg errornumber” to know why
–create spfile
file: <ORACLE_HOME>\database\init+ASM.ora
INSTANCE_TYPE=ASM
DB_UNIQUE_NAME=+ASM
ASM_DISKSTRING=’D:\asm\*’
LARGE_POOL_SIZE=16M
_ASM_ALLOW_ONLY_RAW_DISKS= FALSE

–Note: The undocumented parameter _ASM_ALLOW_ONLY_RAW_DISKS is used to
–   make the instance recognize virtual disks created in previous step.

–create ASM instance service
ORADIM -NEW -ASMSID +ASM -STARTMODE auto

–add diskgroup
SET ORACLE_SID=+ASM
sqlplus / as sysdba
startup nomount
create diskgroup dgroup1 external redundancy
disk ‘D:\asm\asmdisk1′,’D:\asm\asmdisk2′;

–note: using ‘D:\asm\asmdisk*’ to add all

–startup ASM, use NOMOUNT, MOUNT, RESTRICT and FORCE options
–cannot use the STARTUP OPEN syntax
SET ORACLE_SID=+ASM
sqlplus / as sysdba
SYS@+ASM>startup force
ASM instance started

Total System Global Area  535662592 bytes
Fixed Size                  1334380 bytes
Variable Size             509162388 bytes
ASM Cache                  25165824 bytes
ASM diskgroups mounted

–add a 2000M new disk and drop the old 2 disks
–create the new one using asmtool
asmtool -create D:\oracle\oradata\asm\asmdisk1 2000

–change the asm_diskstring to find the new disk
alter system set asm_diskstring=’D:\asm\*’,'D:\oracle\oradata\asm\*’

–add the new one
ALTER DISKGROUP dgroup1 ADD DISK ‘D:\oracle\oradata\asm\asmdisk1′;
–check the status
select name,path,state,mode_status,MOUNT_STATUS from V$asm_disk;
NAME
———————————————————–
PATH
———————————————————–
————
STATE                    MODE_STATUS           MOUNT_STATUS
———————— ——————— ————
DGROUP1_0000
D:\ASM\ASMDISK1
NORMAL                   ONLINE                CACHED

DGROUP1_0001
D:\ASM\ASMDISK2
NORMAL                   ONLINE                CACHED

DGROUP1_0002
D:\ORACLE\ORADATA\ASM\ASMDISK1
NORMAL                   ONLINE                CACHED

–drop the old 2
ALTER DISKGROUP dgroup1 DROP DISK DGROUP1_0000,DGROUP1_0001;

–check the status, it is dropping
select name,path,state,mode_status,MOUNT_STATUS from V$asm_disk;
NAME
———————————————————–
PATH
———————————————————–
————
STATE                    MODE_STATUS           MOUNT_STATUS
———————— ——————— ————
DGROUP1_0000
D:\ASM\ASMDISK1
DROPPING                 ONLINE                CACHED

DGROUP1_0001
D:\ASM\ASMDISK2
DROPPING                 ONLINE                CACHED

DGROUP1_0002
D:\ORACLE\ORADATA\ASM\ASMDISK1
NORMAL                   ONLINE                CACHED

–after a while,check status with group number
SYS@+ASM>select group_number,path,state,mode_status,MOUNT_STATUS from V$asm_disk

GROUP_NUMBER
————
PATH
——————————————————————————–
————
STATE                    MODE_STATUS           MOUNT_STATUS
———————— ——————— ———————
0
D:\ASM\ASMDISK1
NORMAL                   ONLINE                CLOSED

0
D:\ASM\ASMDISK2
NORMAL                   ONLINE                CLOSED

1
D:\ORACLE\ORADATA\ASM\ASMDISK1
NORMAL                   ONLINE                CACHED

–it is ok now. let’s change the parameter asm_diskstring
alter system set asm_diskstring=’D:\oracle\oradata\asm\*’;
–views
V$ASM_DISKGROUP
V$ASM_CLIENT
V$ASM_DISK
V$ASM_FILE
V$ASM_TEMPLATE

–asm command
asmcmd
asmtool

–FTP and HTTP Access
–Using ORACLE_HOME/rdbms/admin/catxdbdbca to create file mapping
–the first parameter is ftp port, the second one is http port
sqlplus>@catxdbdbca 7777 8888

ftp localhost 7777
http://localhost:8888

–create ASM based database bye setting 2 initial parameters
DB_CREATE_FILE_DEST = ‘+dgroup1′
DB_RECOVERY_FILE_DEST = ‘+dgroup2′

Step by step test oracle 11g physical standby database

–Enviroment
db version: 11.1.0.6.0
Primary          orcl11g        D:\oracle\oradata\orcl11g\orcl11g\
Physical standby     standby11g    C:\oracle\oradata\standby11g\
in oracle 11g using adrci command to check alert log, cause alert log in xml format under diag home

–Physical standby top new features, ** is tested
**Open and Recover physical standby at same time
**Snapshot standby, updatable standby database that provides full data protection for a primary database.
**Compression of redo traffic over the network in a Data Guard configuration
Use of physical standby database for rolling upgrades, by ALTER DATABASE RECOVER TO LOGICAL STANDBY KEEP IDENTITY
Mix of Linux and Windows primary and standby databases in the same Data Guard configuration
The ARCH redo transport mode has been deprecated, using ASNYC instead
Lost-write detection using a physical standby, detect a primary or physical standby database

–Step by Step
–primary
SQL> ALTER DATABASE FORCE LOGGING;

–primary initial parameter
DB_NAME=orcl11g
DB_UNIQUE_NAME=orcl11g
LOG_ARCHIVE_CONFIG=’DG_CONFIG=(orcl11g,standby11g)’
LOG_ARCHIVE_DEST_1=’LOCATION=D:\oracle\oradata\orcl11g\orcl11g\arch\ VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=orcl11g’
LOG_ARCHIVE_DEST_2=’SERVICE=standby11g ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=standby11g’
LOG_ARCHIVE_DEST_STATE_1=ENABLE
LOG_ARCHIVE_DEST_STATE_2=ENABLE
REMOTE_LOGIN_PASSWORDFILE=EXCLUSIVE
LOG_ARCHIVE_FORMAT=’ora11g_%t_%s_%r.arc’
LOG_ARCHIVE_MAX_PROCESSES=4
FAL_SERVER=’standby11g’
FAL_CLIENT=’orcl11g’
DB_FILE_NAME_CONVERT=’C:\oracle\oradata\standby11g\’,'D:\oracle\oradata\orcl11g\orcl11g\’
LOG_FILE_NAME_CONVERT=’C:\oracle\oradata\standby11g\’,'D:\oracle\oradata\orcl11g\orcl11g\’
STANDBY_FILE_MANAGEMENT=’AUTO’

–to reduce memory usage
alter system set memory_target=300M scope=both;
alter system set processes=75 scope=spfile;

SQL> SHUTDOWN IMMEDIATE;
SQL> STARTUP MOUNT;
SQL> ALTER DATABASE ARCHIVELOG;
SQL> ALTER DATABASE OPEN;

-create a cold copy of primary db
copy *.dbf *.log ..

–create standby controlfile
SQL> ALTER DATABASE CREATE STANDBY CONTROLFILE AS ‘d:\standby11g.ctl’;
SQL> ALTER DATABASE OPEN;

–create standby pfile
–on primary db
SQL> CREATE PFILE=’d:\pfile.txt’ FROM SPFILE;
–copy it to standby and modify these parameters
DB_UNIQUE_NAME=standby11g
CONTROL_FILES=’C:\oracle\oradata\standby11g\control1.ctl’, ‘C:\oracle\oradata\standby11g\control2.ctl’
DB_FILE_NAME_CONVERT=’D:\oracle\oradata\orcl11g\orcl11g\’,'C:\oracle\oradata\standby11g\’
LOG_FILE_NAME_CONVERT=’D:\oracle\oradata\orcl11g\orcl11g\’,'C:\oracle\oradata\standby11g\’
LOG_ARCHIVE_DEST_1=’LOCATION=C:\oracle\oradata\standby11g\arch\ VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=standby11g’
LOG_ARCHIVE_DEST_2=’SERVICE=orcl11g ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=orcl11g’
FAL_SERVER=’orcl11g’
FAL_CLIENT=’standby11g’

–other changes related to path
audit_file_dest=’C:\oracle\admin\orcl11g\adump’
db_recovery_file_dest=’C:\oracle\flash_recovery_area’
diagnostic_dest=’C:\oracle’
–delete>>dispatchers=’(PROTOCOL=TCP) (SERVICE=orcl11gXDB)’

–Setup standby environment
WINNT> oradim –NEW –SID standby11g –STARTMODE manual

cp D:\oracle\product\11.1.0\db_1\database\PWDorcl11g.ora to standby C:\oracle\product\11.1.0\db_1\database\PWDstandby11g.ora

–Configure listeners for the primary and standby databases and bounce listeners
–primary db, using netmgr to configure or edit listener.ora
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(GLOBAL_DBNAME = ORCL11G)
(ORACLE_HOME = D:\oracle\product\11.1.0\db_1)
(SID_NAME = ORCL11G)
)
)

LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1528))
)
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1528))
)
)

–standby db
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(GLOBAL_DBNAME = STANDBY11G)
(ORACLE_HOME = c:\oracle\product\11.1.0\db_1)
(SID_NAME = STANDBY11G)
)
)

LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 16.158.69.24)(PORT = 1528))
)
)

–start physical standby db
set ORACLE_SID=standby11g
SQL> create spfile from pfile=’C:\oracle\product\11.1.0\db_1\database\pfile.txt’;
SQL> STARTUP MOUNT;
–add standby logfile
ALTER DATABASE ADD STANDBY LOGFILE group 4 (‘C:\oracle\oradata\standby11g\redo04.log’) SIZE 50M;
ALTER DATABASE ADD STANDBY LOGFILE group 5 (‘C:\oracle\oradata\standby11g\redo05.log’) SIZE 50M;
ALTER DATABASE ADD STANDBY LOGFILE group 6 (‘C:\oracle\oradata\standby11g\redo06.log’) SIZE 50M;
ALTER DATABASE ADD STANDBY LOGFILE group 7 (‘C:\oracle\oradata\standby11g\redo07.log’) SIZE 50M;
–start apply log on standby
SQL> alter database recover managed standby database using current logfile disconnect from session;

–check and confirm
–primary db
SYS@orcl11g>SELECT SEQUENCE# FROM V$ARCHIVED_LOG where dest_id=1 ORDER BY SEQUENCE#;

SEQUENCE#
———-
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29

17 rows selected.

T@orcl11g>create table t as select * from dba_objects;

Table created.
T@orcl11g>insert into t  select * from t;

68863 rows created.

T@orcl11g>insert into t  select * from t;

137726 rows created.

T@orcl11g>commit;

Commit complete.
SYS@orcl11g>alter system archive log current;

System altered.

-standby db
SYS@standby11g>SELECT SEQUENCE#,APPLIED FROM V$ARCHIVED_LOG ORDER BY SEQUENCE#;

SEQUENCE# APP
———- —
13 YES
14 YES
15 YES
16 YES
17 YES
18 YES
19 YES
20 YES
21 YES
22 YES
23 YES
24 YES
25 YES
26 YES
27 YES
28 YES
29 YES
30 NO

18 rows selected.
–after a while
SYS@standby11g>SELECT SEQUENCE#,APPLIED FROM V$ARCHIVED_LOG ORDER BY SEQUENCE#;

SEQUENCE# APP
———- —
13 YES
14 YES
15 YES
16 YES
17 YES
18 YES
19 YES
20 YES
21 YES
22 YES
23 YES
24 YES
25 YES
26 YES
27 YES
28 YES
29 YES
30 YES

–
–**test Real-time query, a.k.a. open standby and recover at the same time ;
–
–first, stop redo apply
SYS@standby11g>alter database recover managed standby database cancel;

Database altered.

–second, open database
SYS@standby11g>alter database open;

Database altered.
–third, start redo apply again after db is open
SYS@standby11g>alter database recover managed standby database using current logfile disconnect;

Database altered.

SYS@standby11g>select count(*) from t.t;

COUNT(*)
———-
275452

–let’s do change on primary db
SYS@orcl11g>insert into t.t select * from dba_objects;

68863 rows created.

SYS@orcl11g>commit;

Commit complete.

SYS@orcl11g>select count(*) from t.t;

COUNT(*)
———-
344315

–check standby db immediately
SYS@standby11g>select count(*) from t.t;

COUNT(*)
———-
344315 –good, it is alreay the same as primary db,cool!

–insert more data
SYS@orcl11g>insert into t.t select * from t.t;

344315 rows created.

SYS@orcl11g>commit;

Commit complete.

SYS@orcl11g>select count(*) from t.t;

COUNT(*)
———-
688630

–check standby db
SYS@standby11g>select count(*) from t.t;

COUNT(*)
———-
688630  –good, it is quiet quick.

–
–**test Snapshot Standby Database
–
–stop redo apply
SYS@standby11g>alter database recover managed standby database cancel;

Database altered.

–ensure standby database is mount state
SYS@standby11g>select status from v$instance;

STATUS
————
MOUNTED

–convert to snapshot standby db
SYS@standby11g>alter database convert to snapshot standby;

Database altered.
–The database is dismounted after conversion and must be restarted.
SYS@standby11g>shutdown immediate
ORA-01507: database not mounted
ORACLE instance shut down.
ORACLE instance started.

Total System Global Area  213422080 bytes
Fixed Size                  1332216 bytes
Variable Size             184552456 bytes
Database Buffers           20971520 bytes
Redo Buffers                6565888 bytes
Database mounted.

–snapshot standby is based on flashback db feature when convert back
–falshback should be on
SYS@standby11g>alter database flashback on;

Database altered.
SYS@standby11g>alter database open;

Database altered.

–it is a snapshot standby db now
–let’s test update
–delete on snapshot standby
SYS@standby11g>delete from t.t where object_id<1000;

9400 rows deleted.

SYS@standby11g>commit;

Commit complete.

SYS@standby11g>select count(*) from t.t;

COUNT(*)
———-
679230

–insert on primary db
SYS@orcl11g>insert into t.t select * from t.t;

688630 rows created.

SYS@orcl11g>commit;

Commit complete.

SYS@orcl11g>select count(*) from t.t;

COUNT(*)
———-
1377260

SYS@orcl11g>alter system archive log current;

System altered.
SYS@orcl11g>select max(sequence#) from v$archived_log;

MAX(SEQUENCE#)
————–
35

–check on standby
SYS@standby11g>select sequence#,applied from v$archived_log where sequence#=35;

SEQUENCE# APP
———- —
35 NO –it is transfered but not applied

–conver back to physical standby db
SYS@standby11g>shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SYS@standby11g>startup mount
ORACLE instance started.

Total System Global Area  213422080 bytes
Fixed Size                  1332216 bytes
Variable Size             188746760 bytes
Database Buffers           16777216 bytes
Redo Buffers                6565888 bytes
Database mounted.
SYS@standby11g>alter database convert to physical standby;

Database altered.
SYS@standby11g>shutdown immediate
ORA-01507: database not mounted

ORACLE instance shut down.
SYS@standby11g>startup
ORACLE instance started.

Total System Global Area  213422080 bytes
Fixed Size                  1332216 bytes
Variable Size             188746760 bytes
Database Buffers           16777216 bytes
Redo Buffers                6565888 bytes
Database mounted.
Database opened.
SYS@standby11g>select count(*) from t.t;

COUNT(*)
———-
688630  –it is already discard the delete on standby

–let’s start recover
SYS@standby11g>alter database recover managed standby database using current logfile disconnect;

Database altered.
–check again
SYS@standby11g>select count(*) from t.t;

COUNT(*)
———-
688630  –still not applied yet

SYS@standby11g>select sequence#,applied from v$archived_log where sequence#=35;

SEQUENCE# APP
———- —
35 YES –it should applied

SYS@standby11g>select count(*) from t.t;

COUNT(*)
———-
1377260 –good, got it! It is synced with primary db again

–
–**test ARCHIVELOG_COMPRESSION
–
–primary db
alter system set log_archive_dest_2=’SERVICE=standby11g ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=standby11g COMPRESSION=ENABLE’;

-test data
SYS@orcl11g>delete from t.t;

1377260 rows deleted.

SYS@orcl11g>commit;

Commit complete.

–check standby db
SYS@standby11g>select count(*) from t.t;

COUNT(*)
———-
0 –it applied the same, don’t notice much difference on time

Step by step to test stream on 10gr2

db version: 10.2.0.4
test platform: windows xp
source db: orcl
dest db: orcl1

–default all execute on source database except specified

SQL> show parameter compati

NAME                                 TYPE        VALUE
———————————— ———– ————
compatible                           string      10.2.0.3.0
sga_max_size                         big integer 276M
sga_target                           big integer 276M
processes                            integer     150
timed_statistics                     boolean     TRUE
log_archive_dest_1                   string      LOCATION=D:\oracle\oradata\orcl\archive
log_archive_format                   string      ORCL_ARC%S_%R.%T
undo_retention                       integer     7200
remote_archive_enable                string      TRUE
global_names                         boolean     TRUE
open_links                           integer     10
job_queue_processes                  integer     10
parallel_max_servers                 integer     20
streams_pool_size                    big integer 44M

–enable database supplemental logging
SQL> ALTER DATABASE ADD SUPPLEMENTAL LOG DATA (PRIMARY KEY, UNIQUE) COLUMNS;

Database altered.

–create users for test
SQL> create user t identified by t default tablespace users temporary tablespace temp;

User created.

SQL> grant dba to t;

Grant succeeded.

conn t/t
SQL> create table mystream (id number, name varchar(100));

Table created.

SQL> insert into mystream values(1,’a');

1 row created.

SQL> commit;

Commit complete.

–dest db
create user t identified by t default tablespace users temporary tablespace temp;
grant dba to t;

–create database link
CREATE public DATABASE LINK orcl1 CONNECT TO tadmin identified by tadmin USING ‘ORCL1′;

–source and dest db
–tadmin is the stream administrator user, MUST be different from SYS and SYSTEM
CREATE USER tadmin IDENTIFIED BY tadmin DEFAULT TABLESPACE USERS TEMPORARY TABLESPACE TEMP;
GRANT CONNECT, RESOURCE, DBA TO tadmin;
exec DBMS_STREAMS_AUTH.GRANT_ADMIN_PRIVILEGE(grantee => ‘TADMIN’, grant_privileges => TRUE);
create directory REORG_DIR as ‘D:\oracle\reorg’; –souce db
create directory REORG_DIR as ‘C:\oracle\reorg’; –dest db

–source db
conn tadmin/tadmin

begin
DBMS_STREAMS_ADM.MAINTAIN_SCHEMAS(
schema_names  => ‘T’,
source_database => ‘ORCL’,
source_directory_object => ‘REORG_DIR’,
destination_directory_object => ‘REORG_DIR’,
destination_database => ‘ORCL1′,
perform_actions => TRUE
);
end;
/

–source db
T@orcl>select * from mystream;

ID NAME
———- ——————–
1 a

–check dest db
SQL> conn t/t
Connected.
SQL> select * from mystream;

ID NAME
———- ——————–
1 a

–add data to source table
T@orcl>insert into mystream values(2,’b');

1 row created.

T@orcl>insert into mystream values(3,’c');

1 row created.

T@orcl>commit;

Commit complete.

T@orcl>select * from mystream;

ID NAME
———- ——————–
1 a
2 b
3 c

–check dest db
SQL> select * from mystream;

ID NAME
———- ——————–
1 a
2 b
3 c

–add many data to source db
T@orcl>insert into mystream select object_id,object_name from dba_objects;

46767 rows created.

T@orcl>commit;

Commit complete.

T@orcl>select count(*) from mystream;

COUNT(*)
———-
46770

–check dest db
SQL> select count(*) from mystream;

COUNT(*)
———-
3  –it is still 3

–wait 3+ minutes and check again. It is slow.
SQL> select count(*) from mystream;

COUNT(*)
———-
46770

–let’s recreate the stream with ddl enable
–both source and dest db
conn / as sysdba
exec dbms_streams_adm.remove_streams_configuration();
drop user tadmin cascade;
CREATE USER tadmin IDENTIFIED BY tadmin DEFAULT TABLESPACE USERS TEMPORARY TABLESPACE TEMP;
GRANT CONNECT, RESOURCE, DBA TO tadmin;
exec DBMS_STREAMS_AUTH.GRANT_ADMIN_PRIVILEGE(grantee => ‘TADMIN’, grant_privileges => TRUE);

–dest db
drop user t cascade;
–source db
truncate table t.mystream;

–recreate with ddl enable, source db
CONN tadmin/tadmin
begin
DBMS_STREAMS_ADM.MAINTAIN_SCHEMAS(
schema_names  => ‘T’,
source_database => ‘ORCL’,
source_directory_object => ‘REORG_DIR’,
destination_directory_object => ‘REORG_DIR’,
destination_database => ‘ORCL1′,
perform_actions => TRUE,
include_ddl => TRUE);
end;
/

–add data
conn t/t
insert into mystream values (1,’ddl’);
commit;

–check dest
SQL> select * from mystream;

ID NAME
———- ——————–
1 ddl

–dml is ok, let’s do ddl test
–source db, create table
T@orcl>create table mystream2 as select * from dba_objects where 1=0;

Table created.

–check dest db
SQL> select table_name from user_tables;

TABLE_NAME
——————————
MYSTREAM
MYSTREAM2

–mystream2 is there. DDL is ok too.
–let’s test tons of data insert
T@orcl>insert into mystream2 select * from dba_objects;

46807 rows created.

T@orcl>insert into mystream2 select * from mystream2;

46807 rows created.

T@orcl>/

93614 rows created.

T@orcl>commit;

Commit complete.

T@orcl>insert into mystream2 select * from mystream2;

187228 rows created.

T@orcl>commit;

Commit complete.

–wait n minutes, check dest db
SQL> select count(*) from mystream2;

COUNT(*)
———-
0 –it still ZERO!

–check source db alert log, get this
Wed Oct 29 15:47:38 2008
Error 600 occured while spilling buffered messages
Wed Oct 29 15:47:38 2008
Errors in file d:\oracle\admin\orcl\bdump\orcl_q004_5340.trc:
ORA-00600: internal error code, arguments: [kwqbmcrcpts101], [], [], [], [], [], [], []
–according to metalink Note:435741.1, this bug is fixed in 11g

–also get low SGA error
Wed Oct 29 15:47:59 2008
Propagation Schedule for (TADMIN.ORCL$CAPQ, “TADMIN”.”ORCL$APPQ”@ORCL1) encountered following error:
ORA-23603: STREAMS enqueue aborted due to low SGA
this is my sga size:
sga_max_size                         big integer 276M
sga_target                           big integer 276M

My impress so far, stream is not stable or efficient enough in 10gr2.

oralce physical rowid structure

Since Oracle 8i, oracle physical rowid is in extended rowid format. Extended rowids show in 18 characters with base 64 encoding. The encoding characters are A-Z, a-z, 0-9, +, and /. It is stored in 10 bytes (80 bits).

An extended rowid has a four-piece format, OOOOOOFFFBBBBBBRRR:

The first 6 characters OOOOOO represent data object number, using 32bits

The next 3 characters FFF represent tablespace-relative datafile number, using 10bits.

The next 6 characters BBBBB represent block number, using 22bits.

The last 3 character RRR represent row number, using 16bit

18characters=6+3+6+3

80bits=32+10+22+16

It will easy to understand below.

32bit—object number——–The maximum number of OBJECT in one database is 4G (4294967296)
10bit—file number—–The maximum number of FILE in a tablespace is 1022 (keep 2 in remainder)
22bit—block number——–The maximum number of BLOCK in one file is : 4M (4194304)
16bit—row number——– The maximum number of ROW in one block is 64K(65536)

Using SQL*Plus Copy Command to move data

SQL*Plus Copy Command usage：
COPY {FROM database | TO database | FROM database TO database} {APPEND|CREATE|INSERT|REPLACE} destination_table [(column, column, column, ...)]
USING query

Example:

SQL> copy from scott/tiger@orcl to scott/tiger@orcl create dept1 using select * from dept;

–You can ommit the from or to statement if the destination is as as the source database.

SQL> copy from scott/tiger@orcl create dept2 using select * from dept;

SQL> copy to scott/tiger@orcl create dept3 using select * from dept;

Tips: You can set the arraysize to 5000, the maximum, to improve the performance

SQL> set arraysize 5000

Compare Redo log size between commit outside of loop and inside of loop

Here is my test on commit outside of loop and inside of loop. It will show the generated redo size will be much less when commit outside of loop.

SQL> select * from v$version;

BANNER
—————————————————————-
Oracle9i Enterprise Edition Release 9.2.0.6.0 – Production
PL/SQL Release 9.2.0.6.0 – Production
CORE 9.2.0.6.0 Production
TNS for Linux: Version 9.2.0.6.0 – Production
NLSRTL Version 9.2.0.6.0 – Production

SQL> select name,value from v$sysstat where name like ‘redo size’;

NAME VALUE
—————————————————————- ———-
redo size 88813560

SQL> desc t
Name Null? Type
—————————————– ——– —————————-
C1 NUMBER

SQL> begin
2 for i in 1..10000 loop
3 insert into t values(i);
4 end loop;
5 commit;
6 end;
7 /

PL/SQL procedure successfully completed.

SQL> select name,value from v$sysstat where name like ‘redo size’;

NAME VALUE
—————————————————————- ———-
redo size 91160824

SQL> begin
2 for i in 1..10000 loop
3 insert into t values(i);
4 commit;
5 end loop;
6 end;
7 /

PL/SQL procedure successfully completed.

SQL> select name,value from v$sysstat where name like ‘redo size’;

NAME VALUE
—————————————————————- ———-
redo size 96090180

SQL> select 91160824-88813560
2 commit1 from dual;

COMMIT1
———-
2347264 –redo size when commit outside of loop

SQL> select 96090180-91160824 commitN from dual;

COMMITN
———-
4929356 –redo size when commit inside of loop, more than doulbe of previous

So commit outside of loop whenever possible.

Scripts to simulate connections to oracle

UNIX, test.ksh
————————

#!/bin/ksh
i=0
while [ $i -lt 200 ]
do
i=`expr $i + 1`
sqlplus -S test/test@service @test.sql &
done

Windows, test.bat
————————

for /L %%G in (1,1,200) do start /b sqlplus username/password@service @test.sql

How to sorting and paging in oracle

1. Using the feature count(stopkey). Oracle will get rows in max rownum and then omit rows under min rownum.

select owner,object_name,object_id
from ( select rownum rno,a.*
from (select /*+first_rows*/ * from t where object_name < ‘Z’ order by object_id ) a
where rownum <= 30
)
where rno >= 20

Execution Plan
———————————————————-
0 SELECT STATEMENT Optimizer=HINT: FIRST_ROWS (Cost=53053 Card=30 Bytes=1800)
1 0 VIEW (Cost=53053 Card=30 Bytes=1800)
2 1 COUNT (STOPKEY) –this is an special internal count stopkey method
3 2 VIEW (Cost=53053 Card=52483 Bytes=2466701)
4 3 TABLE ACCESS (BY INDEX ROWID) OF ‘T’ (Cost=53053 Card=52483 Bytes=4513538)
5 4 INDEX (FULL SCAN) OF ‘T_OID_IND’ (NON-UNIQUE) (Cost=101 Card=52952)

2. Using rowid, use Fast Full scan index when possible

select owner,object_name,object_id
from t a,
( select rid from
( select rownum rno,rowid rid from
( select rowid from t
where object_name < ‘Z’
order by object_id  )
) where rNo between 20 and 30
) b
where a.rowid=b.rid
order by a.object_id

How to recover or delete corrupted blocks

For corrupted blocks, we can use block media recovery to recover corrupt datablocks with remain the datafile online. If we don’t have backup available, we can recreate the table by skip the corrupted blocks.

Here are steps for block media recover.

Step1. Find out the bad blocks file# and block#
SQL> select * from V$DATABASE_BLOCK_CORRUPTION;
will show all recorded corrupt blocks.
also you may get it in alert log or other error outputs.
ORA-01578: ORACLE data block corrupted (file # 7, block # 3)
ORA-01578: ORACLE data block corrupted (file # 7, block # 4)
ORA-01578: ORACLE data block corrupted (file # 9, block # 235)

Step2. Recover by rman
rman> blockrecover corruption list;
or
rman> blockrecover datafile 7 block 3,4 datafile 9 block 235;

Here are steps for no backup available scenario:

To skip these blocks and recreate it as the last resort.

Method 1:
Step1.set the 10231 trace for system
SQL> ALTER SYSTEM SET EVENTS=’10231 trace name context forever,level 10′ ;

step2.using CTAS to create new table. It will skip corrupt data and do not report error .
SQL> CREATE TABLE new_table AS SELECT * FROM bad_table;

step3.check the data in new_table.
If acceptable, drop the bad one and rename it.
SQL> DROP TABLE bad_table;
SQL> RENAME good_table TO bad_table;

step4.turn off the trace
ALTER SYSTEM SET EVENTS ‘10231 trace name context off’;

Method 2: To do it manually.
Step1.Find out the corrupted segment
SQL>  SELECT segment_name,segment_type,extent_id,block_id, blocks
FROM dba_extents t
WHERE file_id = :file_id> AND :block_id between block_id and (block_id + blocks – 1) ;

Step2.If the corrupted segmetn is index, just drop it and recreate it.
If it is a table, go to step3

Step3.create new table using CTAS but skip the bad blocks.
SQL> create table good_table  as
select from bad_table where rowid not in
(select rowid from bad_table where to_number(substr(rowid,10,6)) = :block_id );

How to install oracle9i on Linux(RHEL AS 3)

Here I show a simplified steps for installation oracle9i in Linux (Red Hat Enterprise Linux Advanced Server 3)

–set system environment
su – root
echo `expr 512 \* 1024 \* 1024` > /proc/sys/kernel/shmmax
groupadd dba
useradd -c “Oracle software owner” -g dba oracle
passwd oracle
oracle (input twice)
mkdir -p /opt/oracle/product/9.2.0
chown -R oracle.dba /opt/oracle
mkdir -p /var/opt/oracle
chown oracle.dba /var/opt/oracle
chmod 755 /var/opt/oracle

add content below to ~oracle/.bash_profile
————————————————————
#added for oracle
# Set the LD_ASSUME_KERNEL environment variable only for Red Hat 9 and
# for Red Hat Enterprise Linux Advanced Server 3 (RHEL AS 3) !!
# Use the “Linuxthreads with floating stacks” implementation instead of NPTL:
export LD_ASSUME_KERNEL=2.4.1

# Oracle Environment
export ORACLE_BASE=/opt/oracle
export ORACLE_HOME=/opt/oracle/product/9.2.0
export ORACLE_SID=test
export ORACLE_TERM=xterm
# export TNS_ADMIN= Set if sqlnet.ora, tnsnames.ora, etc. are not in $ORACLE_HOME/network/admin
export NLS_LANG=AMERICAN;
export ORA_NLS33=$ORACLE_HOME/ocommon/nls/admin/data
export LD_LIBRARY_PATH=$ORACLE_HOME/lib:$ORACLE_HOME/network/lib
export LIBPATH=$ORACLE_HOME/lib32:$ORACLE_HOME/lib

# Set shell search paths
export PATH=$PATH:$ORACLE_HOME/bin

————————————————————

–relink gcc g++
su – root
mv /usr/bin/gcc /usr/bin/gcc323
ln -s /usr/bin/gcc296 /usr/bin/gcc
mv /usr/bin/g++ /usr/bin/g++323      # if g++ doesn’t exist, then gcc-c++ was not installed
ln -s /usr/bin/g++296 /usr/bin/g++

–install system patch
download the patch p3006854_9204_LINUX.zip from http://metalink.oracle.com.
See bug 3006854 for more information.
su – root
# unzip p3006854_9204_LINUX.zip
Archive:  p3006854_9204_LINUX.zip
creating: 3006854/
inflating: 3006854/rhel3_pre_install.sh
inflating: 3006854/README.txt

# cd 3006854
# sh rhel3_pre_install.sh
Applying patch…
Patch successfully applied
#

–install oracle
su – oracle
Now runInstaller can be started from the CD:
….

–upgrade oracle
download the Oracle 9i Release 2 Patch Set Version 9.2.0.6.0 for Linux x86 from http://metalink.oracle.com.
su – oracle
$ cp p3948480_9206_LINUX.zip /tmp
$ cd /tmp
$ unzip p3948480_9206_LINUX.zip
…..
$ cd /tmp/Disk1/
$ ./runInstaller
install “Oracle Universial Installer 2.2.0.18.0 “
install “Oracle9iR2 Patch Set 9.2.0.6.0 !”"

–set system parameter
add content below to ~oracle/.bash_profile
————————————————————
#for oracle
kernel.shmmax=536870912
kernel.sem= 250 32000 100 128
fs.file-max=65536
————————————————————

–restart machine

–create db (pna)
su – oracle
dbca