Friday, April 27, 2012
Thursday, April 26, 2012
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;
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
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.NEXTVALTo 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
Friday, April 20, 2012
"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
Wednesday, April 18, 2012
Why Role is not enabled be default ?
Why Role is not enabled be default ?
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
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
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
Tuesday, April 17, 2012
opatch util cleanup
testdb01:B.31:ORCL:/software/oratest/product> bdf ./
Filesystem kbytes used avail %used Mounted on
/dev/vgedwsoft/lvedwsoft
31449088 13516848 17815672 43% /software/oratest
testdb01:B.31:ORCL:/software/oratest/product> ls -lrt
total 16
drwxr-xr-x 77 oratest oratest 8192 Apr 16 22:45 11.2.0
Filesystem kbytes used avail %used Mounted on
/dev/vgedwsoft/lvedwsoft
31449088 13516848 17815672 43% /software/oratest
testdb01:B.31:ORCL:/software/oratest/product> ls -lrt
total 16
drwxr-xr-x 77 oratest oratest 8192 Apr 16 22:45 11.2.0
testdb01:B.31:ORCL:/software/oratest/product> du -sk 11.2.0
12425088 11.2.0
12425088 11.2.0
cd 11.2.0
testdb01:B.31:ORCL:/software/oratest/product/11.2.0> du -sk .patch_storage5371176 .patch_storage
testdb01:B.31:ORCL:/software/oratest/product/11.2.0> cd ..
testdb01:B.31:ORCL:/software/oratest/product> opatch util cleanup
Invoking OPatch 11.2.0.1.5
Oracle Interim Patch Installer version 11.2.0.1.5
Copyright (c) 2010, Oracle Corporation. All rights reserved.
UTIL session
Oracle Home : /software/oratest/product/11.2.0
Central Inventory : /software/oratest/oraInventory11202
from : /var/opt/oracle/oraInst.loc
OPatch version : 11.2.0.1.5
OUI version : 11.2.0.2.0
OUI location : /software/oratest/product/11.2.0/oui
Log file location : /software/oratest/product/11.2.0/cfgtoollogs/opatch/opatch2012-04-17_10-41-05AM.log
Patch history file: /software/oratest/product/11.2.0/cfgtoollogs/opatch/opatch_history.txt
Invoking utility "cleanup"
OPatch will clean up 'restore.sh,make.txt' files and 'rac,scratch,backup' directories.
You will be still able to rollback patches after this cleanup.
Do you want to proceed? [y|n]
y
User Responded with: Y
Size of directory "/software/oratest/product/11.2.0/.patch_storage" before cleanup is 5491294372 bytes.
Size of directory "/software/oratest/product/11.2.0/.patch_storage" after cleanup is 348956171 bytes.
UtilSession: Backup area for restore has been cleaned up. For a complete list of files/directories
deleted, Please refer log file.
OPatch succeeded.
testdb01:B.31:ORCL:/software/oratest/product> du -sk 11.2.0
7399216 11.2.0
testdb01:B.31:ORCL:/software/oratest/product> bdf ./
Filesystem kbytes used avail %used Mounted on
/dev/vgedwsoft/lvedwsoft
31449088 8491040 22802520 27% /software/oratest
Filesystem kbytes used avail %used Mounted on
/dev/vgedwsoft/lvedwsoft
31449088 8491040 22802520 27% /software/oratest
sprepsql in 11g.
It works in 11g. I tested in VASP database. The hash value is still using old_hash_value.
SQL> select hash_value , old_hash_value,child_number from v$sql where old_hash_value=3222739199;
HASH_VALUE OLD_HASH_VALUE CHILD_NUMBER
---------- -------------- ------------
2541016949 3222739199 0
2541016949 3222739199 3
---------- -------------- ------------
2541016949 3222739199 0
2541016949 3222739199 3
11916 08 Mar 2012 15:00 6
11921 08 Mar 2012 16:00 6
11921 08 Mar 2012 16:00 6
--error when uses HASH_VALUE
Specify the Begin and End Snapshot Ids
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Enter value for begin_snap: 11916
Begin Snapshot Id specified: 11916
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Enter value for begin_snap: 11916
Begin Snapshot Id specified: 11916
Enter value for end_snap: 11921
End Snapshot Id specified: 11921
End Snapshot Id specified: 11921
Specify the old (i.e. pre-10g) Hash Value
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Enter value for hash_value: 2541016949
Hash Value specified is: 2541016949
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Enter value for hash_value: 2541016949
Hash Value specified is: 2541016949
declare
*
ERROR at line 1:
ORA-20200: Hash value 2541016949 does not exist in end snapshot
ORA-06512: at line 66
--Correct when uses OLD_HASH_VALUE
Specify the Begin and End Snapshot Ids
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Enter value for begin_snap: 11916
Begin Snapshot Id specified: 11916
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Enter value for begin_snap: 11916
Begin Snapshot Id specified: 11916
Enter value for end_snap: 11921
End Snapshot Id specified: 11921
End Snapshot Id specified: 11921
Specify the old (i.e. pre-10g) Hash Value
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Enter value for hash_value: 3222739199
Hash Value specified is: 3222739199
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Enter value for hash_value: 3222739199
Hash Value specified is: 3222739199
...
compress while backup
dev09:NFMST:/software/orafms/product> ll
total 16
drwxr-xr-x 77 orafms orafms 8192 Jan 20 11:25 11.2.0
dev09:NFMST:/software/orafms/product> mknod ./gpipe p
dev09:NFMST:/software/orafms/product> ll
total 16
drwxr-xr-x 77 orafms orafms 8192 Jan 20 11:25 11.2.0
prw-r--r-- 1 orafms orafms 0 Mar 8 14:16 gpipe
dev09:NFMST:/software/orafms/product> gzip <./gpipe >./11.2.0_bak20120308.tar.gz &
dev09:NFMST:/software/orafms/product> tar cvf ./gpipe ./11.2.0
dev09:NFMST:/software/orafms/product> ll
total 9675872
drwxr-xr-x 77 orafms orafms 8192 Jan 20 11:25 11.2.0
-rw-r--r-- 1 orafms orafms 4943770248 Mar 8 14:53 11.2.0_bak20120308.tar.gz
prw-r--r-- 1 orafms orafms 0 Mar 8 14:53 gpipe
[1] + Done gzip <./gpipe >./11.2.0_bak20120308.tar.gz &
dev09:NFMST:/software/orafms/product> ll
total 9655840
drwxr-xr-x 77 orafms orafms 8192 Jan 20 11:25 11.2.0
-rw-r--r-- 1 orafms orafms 4943770248 Mar 8 14:53 11.2.0_bak20120308.tar.gz
prw-r--r-- 1 orafms orafms 0 Mar 8 14:53 gpipe
dev09:NFMST:/software/orafms/product> du -sk *
11161880 11.2.0
4827912 11.2.0_bak20120308.tar.gz
0 gpipe
total 16
drwxr-xr-x 77 orafms orafms 8192 Jan 20 11:25 11.2.0
dev09:NFMST:/software/orafms/product> mknod ./gpipe p
dev09:NFMST:/software/orafms/product> ll
total 16
drwxr-xr-x 77 orafms orafms 8192 Jan 20 11:25 11.2.0
prw-r--r-- 1 orafms orafms 0 Mar 8 14:16 gpipe
dev09:NFMST:/software/orafms/product> gzip <./gpipe >./11.2.0_bak20120308.tar.gz &
dev09:NFMST:/software/orafms/product> tar cvf ./gpipe ./11.2.0
dev09:NFMST:/software/orafms/product> ll
total 9675872
drwxr-xr-x 77 orafms orafms 8192 Jan 20 11:25 11.2.0
-rw-r--r-- 1 orafms orafms 4943770248 Mar 8 14:53 11.2.0_bak20120308.tar.gz
prw-r--r-- 1 orafms orafms 0 Mar 8 14:53 gpipe
[1] + Done gzip <./gpipe >./11.2.0_bak20120308.tar.gz &
dev09:NFMST:/software/orafms/product> ll
total 9655840
drwxr-xr-x 77 orafms orafms 8192 Jan 20 11:25 11.2.0
-rw-r--r-- 1 orafms orafms 4943770248 Mar 8 14:53 11.2.0_bak20120308.tar.gz
prw-r--r-- 1 orafms orafms 0 Mar 8 14:53 gpipe
dev09:NFMST:/software/orafms/product> du -sk *
11161880 11.2.0
4827912 11.2.0_bak20120308.tar.gz
0 gpipe
Sunday, April 01, 2012
Quest Toad and Alternatives
Quest Toad and Alternatives
http://yong321.freeshell.org/oranotes/ToadAndAlternatives.html#ToadAlternatives
http://yong321.freeshell.org/oranotes/ToadAndAlternatives.html#ToadAlternatives
Subscribe to:
Posts (Atom)
