RMAN Configuration for 2 nodes RAC Database

question is:

Is it the same procedure to create catalog and register database for RAC DB with 2 nodes as of Single Production DB.


If the archived redologs are on the clustered filesystems (and i strongly advise you to use a clustered file system) and both the nodes can see the filesystem and it is properly configured then yes

BACKUP DATABASE PLUS ARCHIVELOG DELETE INPUT;

This will work perfectly


Refercenes:

RMAN Configuration for 2 nodes RAC Database

RMAN delete commands

rosscheck backup of database
completed between 'SYSDATE' and 'SYSDATE-30';

When you are deleting backups ensure that you don't use OS commands to do so.

For deleting expired backups of previous week type the following

RMAN>DELETE EXPIRED BACKUP COMPLETED BEFORE SYSDATE-7

and for deleting backups completed before 7 days type

RMAN>DELETE BACKUP COMPLETED BEFORE SYSDATE-7

Don't use the OS utilites to maintain the backups.

set linux root password

具体方法：
1 重启。系统默认加载之前倒数5秒之前按方向键，当出现grub选择界面时，按“E”键
2 此时会进入grub编辑模式，选中kernel 那一行，再次按“e”
3 出现kernel引导命令的编辑界面，在末尾追加 "single"。注意，加single之前要加个空格。然后回车
4 按“b”，此时会引导你进入单用户模式,
5 当出现“sh-2.05#”时，就意味着已经进入单用户模式。现在，输入“passwd root”，就可以重新设置root密码了。
6 修改完成后，重启。正常进入系统即可


Ref:
http://www.itpub.net/thread-1752019-1-1.html

how to turn on autocomplete in AIX 6.1?

Under ksh, do the following:
set -o vi
Filename completion -   ESC + '\'    [ escape + backslash ]
Previous command -  ESC + 'k'

stripe unit size and DB_FILE_MULTIBLOCK_READ_COUNT

http://pic.dhe.ibm.com/infocenter/powersys/v3r1m5/index.jsp?topic=/p7ebj/sasstripeunitsize.htm

Stripe-unit size

With RAID technology, data is striped across an array of physical disks. This data distribution scheme complements the way that the operating system requests data.

The granularity at which data is stored on one disk of the array before subsequent data is stored on the next disk of the array is called the stripe-unit size. The collection of stripe units, from the first disk of the array to the last disk of the array, is called a stripe.

For PCI-X and PCIe controllers, you can set the stripe-unit size of an IBM® SAS Disk Array to 16 KB, 64 KB, 256 KB, or 512 KB. You might be able to maximize the performance of your disk array by setting the stripe-unit size to a value that is slightly larger than the size of the average system I/O request. For large system I/O requests, use a stripe-unit size of 256 KB or 512KB. The recommended stripe size will be identified on the screen when you create the disk array.

For PCIe2 controllers, you can only set a stripe-unit size of 256 KB. This stripe-unit size has been selected to provide the optimum performance when used with both HDDs and SSDs.

And similar link http://ibm.co/Re9h3D

From http://communities.vmware.com/thread/293789

3. RAID array stripe unit size - This is how much data is written to one disk before having

to jump to the next disk within a single stripe. evidently performance in general follows

some sort of bell curve. We used the default 64k of our H700 RAID controller as the control

and found that setting this to 256k gave us the best overall performance gains.One would

think 1024k would be best but that was not the case. Ok, so here are some raw

percentages we pulled out of our tests using bonnie+, here we are looking at the

advantage of going with 256K stripe units:

A.2.5 Using the DB_FILE_MULTIBLOCK_READ_COUNT Parameter

When using Direct I/O or Concurrent I/O with Oracle Database 10g, the AIX file system does not perform any read-ahead on sequential scans. For this reason the DB_FILE_MULTIBLOCK_READ_COUNT value in the server parameter file should be increased when Direct I/O or Concurrent I/O is enabled on Oracle data files. The read ahead is performed by Oracle Database as specified by the DB_FILE_MULTIBLOCK_READ_COUNT initialization parameter.

Setting a large value for the DB_FILE_MULTIBLOCK_READ_COUNT initialization parameter usually yields better I/O throughput on sequential scans. On AIX, this parameter ranges from 1 to 512, but using a value higher than 16 usually does not provide additional performance gain.

Set this parameter so that its value when multiplied by the value of the DB_BLOCK_SIZE parameter produces a number larger than the LVM stripe size. Such a setting causes more disks to be used.

how to set user password unexpire?

Actually, we can retain the current password by " alter user  identified by values ' xxxx' " , while changing the status from expired to open.  In 11g, the hashed password can be found in SYS.USER$.


Ref:
http://stackoverflow.com/questions/1766445/oracle-how-to-set-user-password-unexpire

How To Install VMware Player In Ubuntu 12.04

http://bit.ly/Nx46wx

Nice article, used the same patch fixed my installation of vmplayer 4.0.4

linux fstab

commands learned which related to UUID and label are:

Display current label

sudo e2label /dev/sda1

 

To get a list of all the UUIDs, use one of the following two commands:

sudo blkid
ls -l /dev/disk/by-uuid

More info can be found below.

AutomaticallyMountPartitions

Introduction to fstab

Understanding fstab 

Label a Linux Partition

 

 

 

X11 over firewall

did a quick study on X11 over firewall, and find two ways to do so .

1. X11 forwarding . Need to ensure SSH server by default/configured to support X11 forwarding on various unix-like platform.

2. Open port number 6000 to our laptop (using static ip ) that running x-server. Network team has to open this port on all  servers to those laptops needs to run x-window.

The followings links may help .

http://forums.vandyke.com/showthread.php?t=322

Tested that in order to run X11 application from UNIX server to our PC, we need to open TCP port number 6000 only .

i.e. The source ip is the unix server where we invoke xclock.

The destination ip is our PC, e.g. 10.84.100.24

Port number: TCP 6000

 

"Yes, in a nut shell. If you can connect via SSH, and your SSH server on the Unix side allows X11 port forwarding, then you can tunnel the X display back to your PC. NAT does matter in this case, since you are already connected.

Be sure *not* to set your DISPLAY variable after logon to the target server, and allow SSH to do it for you."


http://www.cyberciti.biz/tips/iptables-block-remote-x-window-server-connection.html

http://www.linux-tip.net/cms/content/view/302/26/


Here is X11 forwarding setting in putty.

Similarly, using openssh SSH in cygwin, needs to export DISPALY before invoke ssh command. e.g.

$ export DISPLAY=localhost:0

liqy@MP04ISLLIQY ~
$ ssh -X -l oracle dev07

Otherwise, may encounter below error.


dev07:B.11:TESTDB:/software/oracle> xclock                                                             
connect /tmp/.X11-unix/X0: No such file or directory
X connection to 10.132.90.1:11.0 broken (explicit kill or server shutdown).


Here are two sessions logon to same server 10.132.90.1 , note that the value of DISPLAY set by X11 forwarding are different: 10.132.90.1:10.0 and  10.132.90.1:12.0 

Problematic erase key in Oracle datafile running on unix

In Unix like shell command line, to erase a letter before current cursor, ^H (CTRL+H) or ^? (BACKSPACE) you'd like to use?

1. Introduction

"stty -a" helps to see current keyboard mapping the BACKSPACE key.

dev07:B.11:TESTDB:/software/oracle> stty -a                            
speed 38400 baud; line = 0;
rows = 24; columns = 80
min = 4; time = 0;
intr = ^C; quit = ^\; erase = DEL; kill = ^U
eof = ^D; eol ; eol2 ; swtch
stop = ^S; start = ^Q; susp = ^Z; dsusp
werase = ^W; lnext = ^V
-parenb -parodd cs8 -cstopb hupcl cread -clocal -loblk -crts
-ignbrk brkint ignpar -parmrk -inpck -istrip -inlcr -igncr icrnl -iuclc
ixon -ixany ixoff imaxbel -rtsxoff -ctsxon -ienqak
isig icanon -iexten -xcase echo echoe echok -echonl -noflsh
echoctl -echoprt echoke -flusho -pendin
opost -olcuc onlcr -ocrnl -onocr -onlret -ofill -ofdel -tostop tab3
dev07:B.11:TESTDB:/software/oracle> stty erase (here pressed CTRL+h)


dev07:B.11:TESTDB:/software/oracle> stty -a
speed 38400 baud; line = 0;
rows = 24; columns = 80
min = 4; time = 0;
intr = ^C; quit = ^\; erase = ^H; kill = ^U
eof = ^D; eol ; eol2 ; swtch
stop = ^S; start = ^Q; susp = ^Z; dsusp
werase = ^W; lnext = ^V
-parenb -parodd cs8 -cstopb hupcl cread -clocal -loblk -crts
-ignbrk brkint ignpar -parmrk -inpck -istrip -inlcr -igncr icrnl -iuclc
ixon -ixany ixoff imaxbel -rtsxoff -ctsxon -ienqak
isig icanon -iexten -xcase echo echoe echok -echonl -noflsh
echoctl -echoprt echoke -flusho -pendin
opost -olcuc onlcr -ocrnl -onocr -onlret -ofill -ofdel -tostop tab3



dev07:B.11:TESTDB:/software/oracle> lss^?    #attemp to use Backspace to delete extra "s" , instead shows as ^?
ksh: lss^?:  not found
dev07:B.11:TESTDB:/software/oracle> lss (followd by pressed CTRL+h)
10g                adhoc              jre                oradata
References:

2. Create a problem file

dev07:B.11:TESTDB:/software/oracle> touch test1^?.txt   #pressed CTRL+h after "1"
dev07:B.11:TESTDB:/software/oracle> ls -l test*.txt
-rw-r--r--   1 oracle     dba              0 Jul 31 17:22 test1.txt  #^? is invisible to "ls -l" command

dev07:B.11:TESTDB:/software/oracle> ls -ll test1^?.txt    # press escape key after "1", can auto-complete the filename, but still invisible
-rw-r--r--   1 oracle     dba              0 Jul 31 17:22 test1.txt

dev07:B.11:TESTDB:/software/oracle> ls -lb test1^?.txt    #use "b" option to see its octal number is 177, which is DEL in ASCII code table
-rw-r--r--   1 oracle     dba              0 Jul 31 17:22 test1\177.txt

--now play with ^H to create another problematic file

dev07:B.11:TESTDB:/software/oracle> stty -a
speed 38400 baud; line = 0;
rows = 24; columns = 80
min = 4; time = 0;
intr = ^C; quit = ^\; erase = DEL; kill = ^U
eof = ^D; eol ; eol2 ; swtch
stop = ^S; start = ^Q; susp = ^Z; dsusp
werase = ^W; lnext = ^V
-parenb -parodd cs8 -cstopb hupcl cread -clocal -loblk -crts
-ignbrk brkint ignpar -parmrk -inpck -istrip -inlcr -igncr icrnl -iuclc
ixon -ixany ixoff imaxbel -rtsxoff -ctsxon -ienqak
isig icanon -iexten -xcase echo echoe echok -echonl -noflsh
echoctl -echoprt echoke -flusho -pendin
opost -olcuc onlcr -ocrnl -onocr -onlret -ofill -ofdel -tostop tab3

dev07:B.11:TESTDB:/software/oracle> touch test.txt   #type test2^H.txt
dev07:B.11:TESTDB:/software/oracle> ls -lb test*.txt
-rw-r--r--   1 oracle     dba              0 Jul 31 17:22 test1\177.txt  #177 in octal in ASCII is "DEL"
-rw-r--r--   1 oracle     dba              0 Jul 31 17:40 test2\010.txt   #010 in octal in ASCII is "backspace"
dev07:B.11:TESTDB:/software/oracle>
ksh: ^H^H^H^H^H^H^H^H:  not found
dev07:B.11:TESTDB:/software/oracle> ll test*.txt
-rw-r--r--   1 oracle     dba              0 Jul 31 17:22 test1.txt
-rw-r--r--   1 oracle     dba              0 Jul 31 17:40 test.txt




3. Make it more troublesome in oracle datafile now.

3.1 first set stty erase as ^H
SQL>  create tablespace testbs datafile '/oracle/UAT/sapdata1/system_1/test1.dbf' size 20m;

Tablespace created.


SQL> alter tablespace testbs  add datafile '/oracle/UAT/sapdata1/system_1/test1^?2.dbf' size 20m;

Tablespace altered.
--  type test1+(Backspace key)+2.dbf in order to create the problem.
SQL> select * from dba_data_files where tablespace_name='TESTBS';

FILE_NAME
--------------------------------------------------------------------------------
   FILE_ID TABLESPACE_NAME                     BYTES     BLOCKS STATUS
---------- ------------------------------ ---------- ---------- ---------
RELATIVE_FNO AUT   MAXBYTES  MAXBLOCKS INCREMENT_BY USER_BYTES USER_BLOCKS
------------ --- ---------- ---------- ------------ ---------- -----------
ONLINE_
-------
/oracle/UAT/sapdata1/system_1/test1.dbf
        74 TESTBS                           20971520       2560 AVAILABLE
          74 NO           0          0            0   20905984        2552
ONLINE

/oracle/UAT/sapdata1/system_1/test1^?2.dbf
        75 TESTBS                           20971520       2560 AVAILABLE
          75 NO           0          0            0   20905984        2552
ONLINE
At OS, it shows :
m1sapuat:orauat 9> ls -lb
total 81952
-rw-r-----   1 orauat     dba        20979712 Jul 30 16:45 test1.dbf
-rw-r-----   1 orauat     dba        20979712 Jul 30 18:42 test1\1772.dbf

3.2 Next, we need to rename it correctly to avoid other impact may arise.

# for database running in ARCHIVELOG mode
alter database datafile 75 offline

 SQL> alter database datafile 75 offline;   
*
ERROR at line 1:
ORA-01145: offline immediate disallowed unless media recovery enabled


  # this testing database runs in NOARCHIVELOG mode.

SQL> alter database datafile 75 offline drop;

Database altered.
-- type the exactly datafile name : test1+(Backspace key)+2.dbf in order
SQL>  alter database rename file  '/oracle/UAT/sapdata1/system_1/test1^?2.dbf' to '/oracle/UAT/sapdata1/system_1/test2.dbf';

Database altered.

-- at OS level, back up the datafile and do the same way to rename it to test2.dbf

-- now recover and online it back

SQL> recover datafile '/oracle/UAT/sapdata1/system_1/test2.dbf';
Media recovery complete.
SQL>  alter database datafile  '/oracle/UAT/sapdata1/system_1/test2.dbf' online;

Database altered.

SQL>  select * from dba_data_files where tablespace_name='TESTBS';

FILE_NAME
--------------------------------------------------------------------------------
   FILE_ID TABLESPACE_NAME                     BYTES     BLOCKS STATUS
---------- ------------------------------ ---------- ---------- ---------
RELATIVE_FNO AUT   MAXBYTES  MAXBLOCKS INCREMENT_BY USER_BYTES USER_BLOCKS
------------ --- ---------- ---------- ------------ ---------- -----------
ONLINE_
-------
/oracle/UAT/sapdata1/system_1/test1.dbf
        74 TESTBS                           20971520       2560 AVAILABLE
          74 NO           0          0            0   20905984        2552
ONLINE

/oracle/UAT/sapdata1/system_1/test2.dbf
        75 TESTBS                           20971520       2560 AVAILABLE
          75 NO           0          0            0   20905984        2552
ONLINE


SQL> select * from v$recover_file;

no rows selected

-- Note that this RENAME approach is preferred than drop the datafile , as drop datafile requires it is a empty file.

References:

Setting terminal characteristics with stty

Unix Tip: Using stty to Your Advantage

ASCII Table and Description

How to Drop a Datafile from a Tablespace

 

 

drop a non-empty datafile

SQL>  select * from dba_data_files where tablespace_name='TESTBS';

FILE_NAME                                                                      
--------------------------------------------------------------------------------
   FILE_ID TABLESPACE_NAME                     BYTES     BLOCKS STATUS         
---------- ------------------------------ ---------- ---------- ---------      
RELATIVE_FNO AUT   MAXBYTES  MAXBLOCKS INCREMENT_BY USER_BYTES USER_BLOCKS     
------------ --- ---------- ---------- ------------ ---------- -----------     
ONLINE_                                                                        
-------                                                                        
/oracle/UAT/sapdata1/system_1/test1.dbf                                        
        74 TESTBS                           20971520       2560 AVAILABLE      
          74 NO           0          0            0   20905984        2552     
ONLINE                                                                         
                                                                               
/oracle/UAT/sapdata1/system_1/test2.dbf                                        
        75 TESTBS                           20971520       2560 AVAILABLE      
          75 NO           0          0            0   20905984        2552     
ONLINE                                                                         
                                                                               
-- This is an empty tablespace, now create a table in it.
SQL> create user user1 identified by user1 default tablespace testbs;

User created.

SQL> grant dba to user1;

Grant succeeded.

SQL> conn user1/user1
Connected.
SQL> create table t1 as select * from dba_objects;

Table created.

SQL> desc dba_extents;
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 OWNER                                              VARCHAR2(30)
 SEGMENT_NAME                                       VARCHAR2(81)
 PARTITION_NAME                                     VARCHAR2(30)
 SEGMENT_TYPE                                       VARCHAR2(18)
 TABLESPACE_NAME                                    VARCHAR2(30)
 EXTENT_ID                                          NUMBER
 FILE_ID                                            NUMBER
 BLOCK_ID                                           NUMBER
 BYTES                                              NUMBER
 BLOCKS                                             NUMBER
 RELATIVE_FNO                                       NUMBER

SQL> select distinct file_id from user_extents;
select distinct file_id from user_extents
                *
ERROR at line 1:
ORA-00904: "FILE_ID": invalid identifier


SQL> desc user_extents
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 SEGMENT_NAME                                       VARCHAR2(81)
 PARTITION_NAME                                     VARCHAR2(30)
 SEGMENT_TYPE                                       VARCHAR2(18)
 TABLESPACE_NAME                                    VARCHAR2(30)
 EXTENT_ID                                          NUMBER
 BYTES                                              NUMBER
 BLOCKS                                             NUMBER
-- Interesting no FILE_ID in user_extents.

SQL> select file_id,count(*)  from dba_extents where owner='USER1' group by file_id;

   FILE_ID   COUNT(*)                                                          
---------- ----------                                                          
        74         24                                                          
        75          9                                                          

--attempt to drop the 2nd datafile

SQL> alter tablespace testbs drop datafile '/oracle/UAT/sapdata1/system_1/test2.dbf';
alter tablespace testbs drop datafile '/oracle/UAT/sapdata1/system_1/test2.dbf'
*
ERROR at line 1:
ORA-03262: the file is non-empty


SQL> drop table t1;

Table dropped.

SQL> show parameter recyclebin

NAME                                 TYPE        VALUE                         
------------------------------------ ----------- ------------------------------
recyclebin                           string      off                           

-- repeat the test with recyclebin is on

SQL> alter system set recyclebin=on;

System altered.

SQL>  create table t1 as select * from dba_objects;

Table created.

SQL>
SQL> select file_id,count(*)  from dba_extents where owner='USER1' group by file_id;

   FILE_ID   COUNT(*)                                                          
---------- ----------                                                          
        74         24                                                          
        75          9                                                          

SQL> drop table t1;

Table dropped.

SQL> select file_id,count(*)  from dba_extents where owner='USER1' group by file_id;

no rows selected

SQL> alter tablespace testbs drop datafile '/oracle/UAT/sapdata1/system_1/test2.dbf';
alter tablespace testbs drop datafile '/oracle/UAT/sapdata1/system_1/test2.dbf'
*
ERROR at line 1:
ORA-03262: the file is non-empty


SQL> purge recyclebin;

Recyclebin purged.

SQL>  alter tablespace testbs drop datafile '/oracle/UAT/sapdata1/system_1/test2.dbf';

Tablespace altered.

SQL> select * from dba_data_files where tablespace_name='TESTBS';

FILE_NAME                                                                      
--------------------------------------------------------------------------------
   FILE_ID TABLESPACE_NAME                     BYTES     BLOCKS STATUS         
---------- ------------------------------ ---------- ---------- ---------      
RELATIVE_FNO AUT   MAXBYTES  MAXBLOCKS INCREMENT_BY USER_BYTES USER_BLOCKS     
------------ --- ---------- ---------- ------------ ---------- -----------     
ONLINE_                                                                        
-------                                                                        
/oracle/UAT/sapdata1/system_1/test1.dbf                                        
        74 TESTBS                           20971520       2560 AVAILABLE      
          74 NO           0          0            0   20905984        2552     
ONLINE                                                                         
                                                                               

-- continue to test what happens to DEFAULT_TABLESPACE in DBA_USERS when the tablespace is dropped.



SQL> prompt to see default tablespace change after the tablespace is dropped
to see default tablespace change after the tablespace is dropped
SQL> conn / as sysdba
Connected.
SQL> select default_tablespace from dba_users where username='USER1';

DEFAULT_TABLESPACE                                                             
------------------------------                                                 
TESTBS                                                                         

SQL> drop tablespace testbs including contents and datafiles;

Tablespace dropped.

SQL> select default_tablespace from dba_users where username='USER1';

DEFAULT_TABLESPACE                                                             
------------------------------                                                 
TESTBS                                                                         

SQL> conn user1/user1
Connected.
SQL>  create table t1 as select * from dba_users;
 create table t1 as select * from dba_users
                                  *
ERROR at line 1:
ORA-00959: tablespace 'TESTBS' does not exist



SQL>  alter user user1 default tablespace system;

User altered.

SQL> create table t1 as select * from dba_users;

Table created.

SQL> show user;
USER is "USER1"
SQL> conn  / as sysdba
Connected.
SQL> drop user user1 cascade;

User dropped.

SQL> spool off

Oracle offline drop datafile

ALTER DATABASE DATAFILE  OFFLINE DROP

Below is my understanding and testing.

This command will not drop the datafile (from both OS and database ) , it will simply take the datafile offline and Oracle will no longer attempt to access it. Offlining the datafile is typically used when your intention is to drop the tablespace. This will also give you the ability to recover the datafile at a later date, e.g recover a datafile (within short period, assuming no REDO log group being recycled yet) in noarchivelog mode.

 SQL>  select * from dba_data_files where tablespace_name='TESTBS';

FILE_NAME                                                                     
--------------------------------------------------------------------------------
   FILE_ID TABLESPACE_NAME                     BYTES     BLOCKS STATUS        
---------- ------------------------------ ---------- ---------- ---------     
RELATIVE_FNO AUT   MAXBYTES  MAXBLOCKS INCREMENT_BY USER_BYTES USER_BLOCKS    
------------ --- ---------- ---------- ------------ ---------- -----------    
ONLINE_                                                                       
-------                                                                       
/oracle/UAT/sapdata1/system_1/test1.dbf                                       
        74 TESTBS                           20971520       2560 AVAILABLE     
          74 NO           0          0            0   20905984        2552    
ONLINE                                                                        
                                                                              
/oracle/UAT/sapdata1/system_1/test2.dbf                                       
        75 TESTBS                           20971520       2560 AVAILABLE     
          75 NO           0          0            0   20905984        2552    
ONLINE                                                                        
                                                                              
                                                 

SQL> alter database datafile 75 offline drop;

Database altered.

SQL> select count(*) from t1;
select count(*) from t1
                     *
ERROR at line 1:
ORA-00376: file 75 cannot be read at this time
ORA-01110: data file 75: '/oracle/UAT/sapdata1/system_1/test2.dbf'


SQL> recover datafile 75;
Media recovery complete.

SQL>  alter database datafile 75 online;

Database altered.




 References:

"How to drop a datafile ?" 

 Oracle Drop Datafile

some links about ROWNUM

http://www.dbforums.com/oracle/988716-rownum-order.html

http://www.orafaq.com/wiki/ROWNUM

http://docs.oracle.com/cd/B19306_01/server.102/b14200/pseudocolumns009.htm

http://www.oracle.com/technetwork/issue-archive/2006/06-sep/o56asktom-086197.html

one useful use I learned from Tom Kytes' article is pagination.  e.g the follow sql returns 2nd and 3rd rows.

 select *
  from ( select /*+ FIRST_ROWS(5) */
  a.*, ROWNUM rnum
      from ( select * from t order by b) a
      where ROWNUM <4 )
where rnum  >1 ;

ORA-12034 and Complete Materialized View Refresh Generates Lots Of Archive Logs and Rollback/Undo

1. Refresh group failed with  FAST REFRESH mode .

SQL> exec dbms_refresh.refresh('"ARBOR"."ARBOR_DAILY_REF"');
BEGIN dbms_refresh.refresh('"ARBOR"."ARBOR_DAILY_REF"'); END;

*
ERROR at line 1:
ORA-12048: error encountered while refreshing materialized view
"ARBOR"."EXTERNAL_ID_EQUIP_MAP"
ORA-12034: materialized view log on "ARBOR"."EXTERNAL_ID_EQUIP_MAP" younger
than last refresh
ORA-06512: at "SYS.DBMS_SNAPSHOT", line 2255
ORA-06512: at "SYS.DBMS_SNAPSHOT", line 2461
ORA-06512: at "SYS.DBMS_IREFRESH", line 683
ORA-06512: at "SYS.DBMS_REFRESH", line 195
ORA-06512: at line 1


2. So I refresh the mview directly,still failed.
 
09:08:41 SQL> exec dbms_mview.refresh('"ARBOR"."EXTERNAL_ID_EQUIP_MAP"',atomic_refresh=>false);
BEGIN dbms_mview.refresh('"ARBOR"."EXTERNAL_ID_EQUIP_MAP"',atomic_refresh=>false); END;

*
ERROR at line 1:
ORA-12034: materialized view log on "ARBOR"."EXTERNAL_ID_EQUIP_MAP" younger
than last refresh
ORA-06512: at "SYS.DBMS_SNAPSHOT", line 2255
ORA-06512: at "SYS.DBMS_SNAPSHOT", line 2461
ORA-06512: at "SYS.DBMS_SNAPSHOT", line 2430
ORA-06512: at line 1


Elapsed: 00:00:00.55

 3. Than I try complete refresh, 16GB UNDO was not sufficient.

SQL> set time on timing on
11:05:16 SQL> exec dbms_mview.refresh('"ARBOR"."EXTERNAL_ID_EQUIP_MAP"','C');
BEGIN dbms_mview.refresh('"ARBOR"."EXTERNAL_ID_EQUIP_MAP"','C'); END;

*
ERROR at line 1:
ORA-12008: error in materialized view refresh path
ORA-30036: unable to extend segment by 8 in undo tablespace 'UNDO'
ORA-06512: at "SYS.DBMS_SNAPSHOT", line 2255
ORA-06512: at "SYS.DBMS_SNAPSHOT", line 2461
ORA-06512: at "SYS.DBMS_SNAPSHOT", line 2430
ORA-06512: at line 1


Elapsed: 03:26:13.56


4. It stops growing until 37GB after I enable autoextensible, but refresh job can't complete after running for 46 hours, and it took anther 6.5 hours for rollback.

14:54:35 SQL> exec dbms_mview.refresh('"ARBOR"."EXTERNAL_ID_EQUIP_MAP"','C');

c

BEGIN dbms_mview.refresh('"ARBOR"."EXTERNAL_ID_EQUIP_MAP"','C'); END;

*
ERROR at line 1:
ORA-12008: error in materialized view refresh path
ORA-01013: user requested cancel of current operation
ORA-06512: at "SYS.DBMS_SNAPSHOT", line 2255
ORA-06512: at "SYS.DBMS_SNAPSHOT", line 2461
ORA-06512: at "SYS.DBMS_SNAPSHOT", line 2430
ORA-06512: at line 1


Elapsed: 52:26:01.08

As the mview contains 6.3 millions rows.

10:05:38 SQL> select count(*) from "ARBOR"."EXTERNAL_ID_ACCT_MAP";

  COUNT(*)
----------
   6382386



5. Finally I found ATOMIC_REFRESH=>false, and gave it a try. This works in 26 minutes!

09:08:44 SQL> exec dbms_mview.refresh('"ARBOR"."EXTERNAL_ID_EQUIP_MAP"','C',atomic_refresh=>false);

PL/SQL procedure successfully completed.

Elapsed: 00:25:12.26
 
11:08:57 SQL> select * from user_mview_refresh_times;

OWNER          NAME                           MASTER_OWNER
-------------- ------------------------------ --------------
MASTER                         LAST_REFRESH
------------------------------ --------------------


ARBOR          EXTERNAL_ID_EQUIP_MAP          ARBOR
EXTERNAL_ID_EQUIP_MAP          29-jun-2012 09:09:06

...
6 rows selected.
 
6. The subsequent FAST REFRESH is also succeed.


11:09:01 SQL> exec dbms_mview.refresh('"ARBOR"."EXTERNAL_ID_EQUIP_MAP"');

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.99
11:09:28 SQL> exec dbms_refresh.refresh('"ARBOR"."ARBOR_DAILY_REF"');

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.75


7. Key timestamp in dictionary views


mview site: 


11:12:43 SQL> select last_refresh from dba_mview_refresh_times where name='EXTERNAL_ID_EQUIP_MAP';

LAST_REFRESH
--------------------
29-jun-2012 11:12:43

master site:

ORACLE1> select CURRENT_SNAPSHOTS from dba_snapshot_logs where MASTER='EXTERNAL_ID_EQUIP_MAP';

CURRENT_SNAPSHOTS
--------------------
29-jun-2012 11:12:43

ORACLE1> select LAST_PURGE_DATE from sys.mlog$ where MASTER='EXTERNAL_ID_EQUIP_MAP';

LAST_PURGE_DATE
--------------------
29-jun-2012 11:12:43
 


8. Findings:



The reason of ORA-12034 is due to both mview and master site are cloned database, and the date cut date of mview site is older than master site, caused the out-of-sync in between mview and mview log.


References:

Complete Materialized View Refresh Generates Lots Of Archive Logs and Rollback/Undo Activity [ID 413188.1]

 http://aprakash.wordpress.com/2010/11/05/mview-complete-refresh-is-it-slow-high-redo-generation-oracle-10g/

http://gavinsoorma.com/2009/07/10g-materialized-view-complete-refresh-using-atomic_refresh/

Deinstall 9.2.0.5 intermedia

Follow these steps to deinstall Oracle interMedia:

1) Start SQL*Plus and connect as SYSDBA.

2) Invoke the imdinst.sql script to render Oracle interMedia inactive:

-> SQL> @/ord/im/admin/imdinst.sql (on UNIX)
        @\ord\im\admin\imdinst.sql (on Windows NT)

3) Drop all tables and types that depend on interMedia object types.

4) Invoke the imdtyp.sql script to drop all the interMedia object types.

-> SQL> @/ord/im/admin/imdtyp.sql (on UNIX)
        @\ord\im\admin\imdtyp.sql (on Windows NT)


Encounter the following error message in 9.2.0.5 when running imdtyp.sql. Problem solved by re-run it.

More info about the dependencies example can be found here


SQL> @imdtyp

Session altered.


Type dropped.


Type dropped.


Type dropped.


Type dropped.


Type dropped.


Type dropped.


Indextype dropped.


Operator dropped.


Operator dropped.


Function dropped.


Function dropped.


Type dropped.

drop type ORDSYS.ORDImageSignature
*
ERROR at line 1:
ORA-02303: cannot drop or replace a type with type or table dependents



Type dropped.

drop type ORDSYS.ORDImage
*
ERROR at line 1:
ORA-02303: cannot drop or replace a type with type or table dependents


drop type ORDSYS.ORDIMGB
*
ERROR at line 1:
ORA-02303: cannot drop or replace a type with type or table dependents


drop type ORDSYS.ORDIMGF
*
ERROR at line 1:
ORA-02303: cannot drop or replace a type with type or table dependents


drop type ORDSource
*
ERROR at line 1:
ORA-02303: cannot drop or replace a type with type or table dependents

DBUA complains database not in mounted state

"The Upgrade Assistant failes in bringing up the database XXX. Oracle Home /oracle/XXX/11202 obtained from file /etc/oratab was used to connect to the database. Either the database is not running from Oracle Home /oracle/XXX/11202 or the correct Initialization Parameter file (pfile) was not found" Then it asks me to locate the pfile.

My case is similar to http://scn.sap.com/thread/1938507

So cat the Welcome_.txt 

> cat  Welcome_TTTT.txt
The presence of this file indicates that the database is opened up with the new Oracle Home binaries.

/software/orattt/admin/cfgtoollogs/dbua/logs_bak> ls -lrt
total 188
-rw-r----- 1 orattt orattt    102 Oct 21  2010 Welcome_TTTT.txt
-rw-r----- 1 orattt orattt 140879 May 24 16:03 sqls.log
-rw-r----- 1 orattt orattt   2104 May 24 16:07 PreUpgradeResults.html
-rw-r----- 1 orattt orattt  20034 May 24 16:07 trace.log

That is the culprit.

After I rename the directory, everything goes smoothly.

set arraysize [SQL*Plus]

set arraysize [SQL*Plus]

How to spot duplicate datafiles

Oracle Expert » How to spot duplicate datafiles

 

select *  from (
    select  tablespace_name, fullpath, filename, count(filename) over (partition by filename)  freq
    from  ( select tablespace_name, file_name fullpath, substr(file_name, instr(file_name,'/',-1)+1)  filename from dba_data_files )
 )
where freq > 1;

 

 

set arraysize [SQL*Plus]

Increase parameter COMPATIBLE

Here are change info found in alert.log when bounce the database.

...

ALERT: Compatibility of the database is changed from 10.2.0.2.0 to 11.2.0.2.0.

Increased the record size of controlfile section 4 to 520 bytes
Control file expanded from 2274 blocks to 2320 blocks
Increased the record size of controlfile section 14 to 200 bytes
Control file expanded from 2320 blocks to 2342 blocks
Increased the record size of controlfile section 16 to 736 bytes
Control file expanded from 2342 blocks to 2362 blocks
Increased the record size of controlfile section 20 to 928 bytes
Control file expanded from 2362 blocks to 2382 blocks
Increased the record size of controlfile section 21 to 124 bytes
Control file expanded from 2382 blocks to 2388 blocks
Increased the record size of controlfile section 22 to 900 bytes
The number of logical blocks in section 22 remains the same

 

...

Public synonym for SEQUENCE ?

No, it is wrong to do so !

To refer to the current or next value of a sequence in the schema of another user, you must have been granted either SELECT object privilege on the sequence or SELECT ANY SEQUENCE system privilege, and you must qualify the sequence with the schema containing it:

schema.sequence.CURRVAL
schema.sequence.NEXTVAL

To refer to the value of a sequence on a remote database, you must qualify the sequence with a complete or partial name of a database link:

schema.sequence.CURRVAL@dblink
schema.sequence.NEXTVAL@dblink

Reference: http://docs.oracle.com/cd/B19306_01/server.102/b14200/pseudocolumns002.htm

 

"Strict Standards" err msg

http://bit.ly/HWE27I

For my case, problem solved after make the following changes in php.ini

;error_reporting = E_ALL | E_STRICT
error_reporting = E_ALL

Why Role is not enabled be default ?

Why Role is not enabled be default ?

SYS@NSMSP> select * from dba_role_privs where grantee='LIQY';

no rows selected

SYS@NSMSP> grant SMS_SEL_ROLE to liqy;

Grant succeeded.

SYS@NSMSP> grant SMS_FULL_ROLE to liqy;

Grant succeeded.

SYS@NSMSP> select * from dba_role_privs where grantee='LIQY';

GRANTEE                        GRANTED_ROLE                   ADM DEF
------------------------------ ------------------------------ --- ---
LIQY                           SMS_SEL_ROLE                   NO  YES
LIQY                           SMS_FULL_ROLE                  NO  YES

SYS@NSMSP> revoke SMS_SEL_ROLE from liqy;

Revoke succeeded.

SYS@NSMSP> revoke SMS_FULL_ROLE from liqy;

Revoke succeeded.

SYS@NSMSP> grant SMS_SEL_ROLE to liqy;

Grant succeeded.

SYS@NSMSP> alter user liqy default role SMS_SEL_ROLE;

User altered.

SYS@NSMSP> grant SMS_FULL_ROLE to liqy;

Grant succeeded.

SYS@NSMSP> select * from dba_role_privs where grantee='LIQY';

GRANTEE                        GRANTED_ROLE                   ADM DEF
------------------------------ ------------------------------ --- ---
LIQY                           SMS_SEL_ROLE                   NO  YES
LIQY                           SMS_FULL_ROLE                  NO  NO

# note that here SMS_FULL_ROLE   is not activated by default.


--rectify the issue 

dbsvr21:NSMSP:/software/oranSMS/admin/NSMSP/create/setup/role> sqlplus / as sysdba

SQL*Plus: Release 11.2.0.2.0 Production on Tue Apr 3 15:53:29 2012

Copyright (c) 1982, 2010, Oracle.  All rights reserved.


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production
With the Partitioning option

SYS@NSMSP> spool alter_default_role_mnaccess.log
SYS@NSMSP> select * from dba_role_privs where grantee='MNACCESS';

GRANTEE                        GRANTED_ROLE                   ADM DEF
------------------------------ ------------------------------ --- ---
MNACCESS                       MNACCESS_ROLE                  NO  YES
MNACCESS                       SMS_FULL_ROLE                  NO  NO

SYS@NSMSP> prompt in DEF column SMS_FULL_ROLE is not activated by default
in DEF column SMS_FULL_ROLE is not activated by default
SYS@NSMSP> alter user MNACCESS default role none;

User altered.

SYS@NSMSP> select * from dba_role_privs where grantee='MNACCESS';

GRANTEE                        GRANTED_ROLE                   ADM DEF
------------------------------ ------------------------------ --- ---
MNACCESS                       MNACCESS_ROLE                  NO  NO
MNACCESS                       SMS_FULL_ROLE                  NO  NO

SYS@NSMSP> revoke MNACCESS_ROLE from  MNACCESS;

Revoke succeeded.

SYS@NSMSP> grant MNACCESS_ROLE to  MNACCESS;

Grant succeeded.

SYS@NSMSP> revoke SMS_FULL_ROLE from  MNACCESS;

Revoke succeeded.

SYS@NSMSP> grant SMS_FULL_ROLE to  MNACCESS;

Grant succeeded.

SYS@NSMSP> select * from dba_role_privs where grantee='MNACCESS';

GRANTEE                        GRANTED_ROLE                   ADM DEF
------------------------------ ------------------------------ --- ---
MNACCESS                       MNACCESS_ROLE                  NO  NO
MNACCESS                       SMS_FULL_ROLE                  NO  NO

SYS@NSMSP> alter user MNACCESS default role none;

User altered.

SYS@NSMSP> select * from dba_role_privs where grantee='MNACCESS';

GRANTEE                        GRANTED_ROLE                   ADM DEF
------------------------------ ------------------------------ --- ---
MNACCESS                       MNACCESS_ROLE                  NO  NO
MNACCESS                       SMS_FULL_ROLE                  NO  NO

SYS@NSMSP> revoke SMS_FULL_ROLE, MNACCESS_ROLE from MNACCESS;

Revoke succeeded.

SYS@NSMSP> select * from dba_role_privs where grantee='MNACCESS';

no rows selected

SYS@NSMSP> grant SMS_FULL_ROLE, MNACCESS_ROLE to  MNACCESS;

Grant succeeded.

SYS@NSMSP> select * from dba_role_privs where grantee='MNACCESS';

GRANTEE                        GRANTED_ROLE                   ADM DEF
------------------------------ ------------------------------ --- ---
MNACCESS                       MNACCESS_ROLE                  NO  NO
MNACCESS                       SMS_FULL_ROLE                  NO  NO


MNACCESS@NSMSP> conn / as sysdba
Connected.
SYS@NSMSP> select * from dba_role_privs where grantee='MNACCESS';

GRANTEE                        GRANTED_ROLE                   ADM DEF
------------------------------ ------------------------------ --- ---
MNACCESS                       MNACCESS_ROLE                  NO  NO
MNACCESS                       SMS_FULL_ROLE                  NO  NO

SYS@NSMSP> conn mnaccess/mnaccess
Connected.
MNACCESS@NSMSP> insert into super_conf.cust_roam_cap(mobileno, accountid, starttime, indicator)values(97485258,51494367,20120403121541,'IN');
insert into super_conf.cust_roam_cap(mobileno, accountid, starttime, indicator)values(97485258,51494367,20120403121541,'IN')
                       *
ERROR at line 1:
ORA-01031: insufficient privileges


MNACCESS@NSMSP> set role all
  2  ;

Role set.

MNACCESS@NSMSP> insert into super_conf.cust_roam_cap(mobileno, accountid, starttime, indicator)values(97485258,51494367,20120403121541,'IN');

1 row created.

MNACCESS@NSMSP> rollback;

Rollback complete.

MNACCESS@NSMSP> conn / as sysdba
Connected.

SYS@NSMSP> ALTER USER mnaccess DEFAULT ROLE mnaccess_role, SMS_FULL_ROLE;

User altered.

SYS@NSMSP> select * from dba_role_privs where grantee='MNACCESS';

GRANTEE                        GRANTED_ROLE                   ADM DEF
------------------------------ ------------------------------ --- ---
MNACCESS                       MNACCESS_ROLE                  NO  YES
MNACCESS                       SMS_FULL_ROLE                  NO  YES

SYS@NSMSP> revoke create session from MNACCESS;

Revoke succeeded.

SYS@NSMSP> conn mnaccess/mnaccess
Connected.
MNACCESS@NSMSP> insert into super_conf.cust_roam_cap(mobileno, accountid, starttime, indicator)values(97485258,51494367,20120403121541,'IN');

1 row created.

MNACCESS@NSMSP> rollback;

Rollback complete.


  COUNT(*)
----------
         0