SQL> create diskgroup FRA external redundancy disk '/dev/sdc1';
Diskgroup created.
SQL> desc v$asm_diskgroup
Name Null? Type
----------------------------------------- -------- ----------------------------
GROUP_NUMBER NUMBER
NAME VARCHAR2(30)
SECTOR_SIZE NUMBER
BLOCK_SIZE NUMBER
ALLOCATION_UNIT_SIZE NUMBER
STATE VARCHAR2(11)
TYPE VARCHAR2(6)
TOTAL_MB NUMBER
FREE_MB NUMBER
HOT_USED_MB NUMBER
COLD_USED_MB NUMBER
REQUIRED_MIRROR_FREE_MB NUMBER
USABLE_FILE_MB NUMBER
OFFLINE_DISKS NUMBER
COMPATIBILITY VARCHAR2(60)
DATABASE_COMPATIBILITY VARCHAR2(60)
VOTING_FILES VARCHAR2(1)
SQL> select * from v$asm_diskgroup;
GROUP_NUMBER NAME SECTOR_SIZE BLOCK_SIZE
------------ ------------------------------ ----------- ----------
ALLOCATION_UNIT_SIZE STATE TYPE TOTAL_MB FREE_MB HOT_USED_MB
-------------------- ----------- ------ ---------- ---------- -----------
COLD_USED_MB REQUIRED_MIRROR_FREE_MB USABLE_FILE_MB OFFLINE_DISKS
------------ ----------------------- -------------- -------------
COMPATIBILITY
------------------------------------------------------------
DATABASE_COMPATIBILITY V
------------------------------------------------------------ -
1 DATA 512 4096
1048576 MOUNTED EXTERN 20473 20414 0
59 0 20414 0
11.2.0.0.0
10.1.0.0.0 N
2 FRA 512 4096
1048576 MOUNTED EXTERN 10236 10186 0
50 0 10186 0
10.1.0.0.0
10.1.0.0.0 N
SQL> alter diskgroup data set attribute 'compatible.asm'='11.2.0.0.0';
Diskgroup altered.
SQL> alter diskgroup data set attribute 'compatible.rdbms'='11.2.0.0.0';
Diskgroup altered.
SQL> select * from v$asm_diskgroup;
GROUP_NUMBER NAME SECTOR_SIZE BLOCK_SIZE
------------ ------------------------------ ----------- ----------
ALLOCATION_UNIT_SIZE STATE TYPE TOTAL_MB FREE_MB HOT_USED_MB
-------------------- ----------- ------ ---------- ---------- -----------
COLD_USED_MB REQUIRED_MIRROR_FREE_MB USABLE_FILE_MB OFFLINE_DISKS
------------ ----------------------- -------------- -------------
COMPATIBILITY
------------------------------------------------------------
DATABASE_COMPATIBILITY V
------------------------------------------------------------ -
1 DATA 512 4096
1048576 MOUNTED EXTERN 20473 20414 0
59 0 20414 0
11.2.0.0.0
11.2.0.0.0 N
2 FRA 512 4096
1048576 MOUNTED EXTERN 10236 10186 0
50 0 10186 0
10.1.0.0.0
10.1.0.0.0 N
SQL> alter diskgroup fra set attribute 'compatible.asm'='11.2';
Diskgroup altered.
SQL> alter diskgroup fra set attribute 'compatible.rdbms'='11.2';
Diskgroup altered.
SQL> select * from v$asm_diskgroup;
GROUP_NUMBER NAME SECTOR_SIZE BLOCK_SIZE
------------ ------------------------------ ----------- ----------
ALLOCATION_UNIT_SIZE STATE TYPE TOTAL_MB FREE_MB HOT_USED_MB
-------------------- ----------- ------ ---------- ---------- -----------
COLD_USED_MB REQUIRED_MIRROR_FREE_MB USABLE_FILE_MB OFFLINE_DISKS
------------ ----------------------- -------------- -------------
COMPATIBILITY
------------------------------------------------------------
DATABASE_COMPATIBILITY V
------------------------------------------------------------ -
1 DATA 512 4096
1048576 MOUNTED EXTERN 20473 20414 0
59 0 20414 0
11.2.0.0.0
11.2.0.0.0 N
2 FRA 512 4096
1048576 MOUNTED EXTERN 10236 10184 0
52 0 10184 0
11.2.0.0.0
11.2.0.0.0 N
Saturday, April 7, 2012
Sunday, January 8, 2012
Create directory implictly grant permission to creator (owner is SYS)
[oracle@vmxdb05b ~]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.3.0 Production on Sun Jan 8 20:09:10 2012
Copyright (c) 1982, 2011, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, Automatic Storage Management, OLAP, Data Mining
and Real Application Testing options
SQL> create user donghua2 identified by donghua2;
User created.
SQL> grant create session to donghua2;
Grant succeeded.
SQL> grant create any directory to donghua2;
Grant succeeded.
SQL> conn donghua2/donghua2
Connected.
SQL> create directory HOMEDIR as '/home/oracle';
Directory created.
SQL> exit
SQL> select * from all_directories;
OWNER DIRECTORY_NAME
------------------------------ ------------------------------
DIRECTORY_PATH
--------------------------------------------------------------------------------
SYS SUBDIR
/u01/app/oracle/product/11.2.0/dbhome_1/demo/schema/order_entry//2002/Sep
SYS SS_OE_XMLDIR
/u01/app/oracle/product/11.2.0/dbhome_1/demo/schema/order_entry/
SYS HOMEDIR
/home/oracle
OWNER DIRECTORY_NAME
------------------------------ ------------------------------
DIRECTORY_PATH
--------------------------------------------------------------------------------
SYS LOG_FILE_DIR
/u01/app/oracle/product/11.2.0/dbhome_1/demo/schema/log/
SYS MEDIA_DIR
/u01/app/oracle/product/11.2.0/dbhome_1/demo/schema/product_media/
SYS XMLDIR
/u01/app/oracle/product/11.2.0/dbhome_1/rdbms/xml
OWNER DIRECTORY_NAME
------------------------------ ------------------------------
DIRECTORY_PATH
--------------------------------------------------------------------------------
SYS DATA_FILE_DIR
/u01/app/oracle/product/11.2.0/dbhome_1/demo/schema/sales_history/
SYS DATA_PUMP_DIR
/u01/app/oracle/admin/orcl05b/dpdump/
SYS ORACLE_OCM_CONFIG_DIR
/u01/app/oracle/product/11.2.0/dbhome_1/ccr/state
9 rows selected.
SQL> select * from all_tab_privs where grantee='DONGHUA2';
GRANTOR GRANTEE
------------------------------ ------------------------------
TABLE_SCHEMA TABLE_NAME
------------------------------ ------------------------------
PRIVILEGE GRA HIE
---------------------------------------- --- ---
SYS DONGHUA2
SYS HOMEDIR
WRITE YES NO
SYS DONGHUA2
SYS HOMEDIR
READ YES NO
GRANTOR GRANTEE
------------------------------ ------------------------------
TABLE_SCHEMA TABLE_NAME
------------------------------ ------------------------------
PRIVILEGE GRA HIE
---------------------------------------- --- ---
SYS DONGHUA2
SYS HOMEDIR
EXECUTE YES NO
[oracle@vmxdb05b ~]$ sqlplus donghua2/donghua2
SQL*Plus: Release 11.2.0.3.0 Production on Sun Jan 8 20:17:44 2012
Copyright (c) 1982, 2011, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, Automatic Storage Management, OLAP, Data Mining
and Real Application Testing options
SQL> set echo on
SQL> @utl_file.sql
SQL> DECLARE
2 V1 VARCHAR2(32767);
3 F1 UTL_FILE.FILE_TYPE;
4 BEGIN
5 F1 := UTL_FILE.FOPEN('HOMEDIR','MYFILE','W');
6 UTL_FILE.PUT_LINE(F1,'TEST LINE');
7 UTL_FILE.FCLOSE(F1);
8
9 F1 := UTL_FILE.FOPEN('HOMEDIR','MYFILE','R');
10 UTL_FILE.GET_LINE(F1,V1,32767);
11 UTL_FILE.FCLOSE(F1);
12 END;
13 /
PL/SQL procedure successfully completed.
SQL> ! cat ~/MYFILE
TEST LINE
SQL> connect donghua/donghua
Connected.
SQL> create directory homedir2 as '/home/oracle';
Directory created.
SQL> select * from all_tab_privs where grantee='DONGHUA';
GRANTOR GRANTEE
------------------------------ ------------------------------
TABLE_SCHEMA TABLE_NAME
------------------------------ ------------------------------
PRIVILEGE GRA HIE
---------------------------------------- --- ---
SYS DONGHUA
SYS HOMEDIR2
WRITE YES NO
SYS DONGHUA
SYS HOMEDIR2
READ YES NO
GRANTOR GRANTEE
------------------------------ ------------------------------
TABLE_SCHEMA TABLE_NAME
------------------------------ ------------------------------
PRIVILEGE GRA HIE
---------------------------------------- --- ---
SYS DONGHUA
SYS HOMEDIR2
EXECUTE YES NO
SQL*Plus: Release 11.2.0.3.0 Production on Sun Jan 8 20:09:10 2012
Copyright (c) 1982, 2011, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, Automatic Storage Management, OLAP, Data Mining
and Real Application Testing options
SQL> create user donghua2 identified by donghua2;
User created.
SQL> grant create session to donghua2;
Grant succeeded.
SQL> grant create any directory to donghua2;
Grant succeeded.
SQL> conn donghua2/donghua2
Connected.
SQL> create directory HOMEDIR as '/home/oracle';
Directory created.
SQL> exit
SQL> select * from all_directories;
OWNER DIRECTORY_NAME
------------------------------ ------------------------------
DIRECTORY_PATH
--------------------------------------------------------------------------------
SYS SUBDIR
/u01/app/oracle/product/11.2.0/dbhome_1/demo/schema/order_entry//2002/Sep
SYS SS_OE_XMLDIR
/u01/app/oracle/product/11.2.0/dbhome_1/demo/schema/order_entry/
SYS HOMEDIR
/home/oracle
OWNER DIRECTORY_NAME
------------------------------ ------------------------------
DIRECTORY_PATH
--------------------------------------------------------------------------------
SYS LOG_FILE_DIR
/u01/app/oracle/product/11.2.0/dbhome_1/demo/schema/log/
SYS MEDIA_DIR
/u01/app/oracle/product/11.2.0/dbhome_1/demo/schema/product_media/
SYS XMLDIR
/u01/app/oracle/product/11.2.0/dbhome_1/rdbms/xml
OWNER DIRECTORY_NAME
------------------------------ ------------------------------
DIRECTORY_PATH
--------------------------------------------------------------------------------
SYS DATA_FILE_DIR
/u01/app/oracle/product/11.2.0/dbhome_1/demo/schema/sales_history/
SYS DATA_PUMP_DIR
/u01/app/oracle/admin/orcl05b/dpdump/
SYS ORACLE_OCM_CONFIG_DIR
/u01/app/oracle/product/11.2.0/dbhome_1/ccr/state
9 rows selected.
SQL> select * from all_tab_privs where grantee='DONGHUA2';
GRANTOR GRANTEE
------------------------------ ------------------------------
TABLE_SCHEMA TABLE_NAME
------------------------------ ------------------------------
PRIVILEGE GRA HIE
---------------------------------------- --- ---
SYS DONGHUA2
SYS HOMEDIR
WRITE YES NO
SYS DONGHUA2
SYS HOMEDIR
READ YES NO
GRANTOR GRANTEE
------------------------------ ------------------------------
TABLE_SCHEMA TABLE_NAME
------------------------------ ------------------------------
PRIVILEGE GRA HIE
---------------------------------------- --- ---
SYS DONGHUA2
SYS HOMEDIR
EXECUTE YES NO
[oracle@vmxdb05b ~]$ sqlplus donghua2/donghua2
SQL*Plus: Release 11.2.0.3.0 Production on Sun Jan 8 20:17:44 2012
Copyright (c) 1982, 2011, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, Automatic Storage Management, OLAP, Data Mining
and Real Application Testing options
SQL> set echo on
SQL> @utl_file.sql
SQL> DECLARE
2 V1 VARCHAR2(32767);
3 F1 UTL_FILE.FILE_TYPE;
4 BEGIN
5 F1 := UTL_FILE.FOPEN('HOMEDIR','MYFILE','W');
6 UTL_FILE.PUT_LINE(F1,'TEST LINE');
7 UTL_FILE.FCLOSE(F1);
8
9 F1 := UTL_FILE.FOPEN('HOMEDIR','MYFILE','R');
10 UTL_FILE.GET_LINE(F1,V1,32767);
11 UTL_FILE.FCLOSE(F1);
12 END;
13 /
PL/SQL procedure successfully completed.
SQL> ! cat ~/MYFILE
TEST LINE
SQL> connect donghua/donghua
Connected.
SQL> create directory homedir2 as '/home/oracle';
Directory created.
SQL> select * from all_tab_privs where grantee='DONGHUA';
GRANTOR GRANTEE
------------------------------ ------------------------------
TABLE_SCHEMA TABLE_NAME
------------------------------ ------------------------------
PRIVILEGE GRA HIE
---------------------------------------- --- ---
SYS DONGHUA
SYS HOMEDIR2
WRITE YES NO
SYS DONGHUA
SYS HOMEDIR2
READ YES NO
GRANTOR GRANTEE
------------------------------ ------------------------------
TABLE_SCHEMA TABLE_NAME
------------------------------ ------------------------------
PRIVILEGE GRA HIE
---------------------------------------- --- ---
SYS DONGHUA
SYS HOMEDIR2
EXECUTE YES NO
No error detected to change OS time for oracle DB running on 11.2
[oracle@vmxdb05b ~]$ vi trigger_changes.sql
[oracle@vmxdb05b ~]$ sqlplus donghua/donghua
SQL*Plus: Release 11.2.0.3.0 Production on Sun Jan 8 12:42:07 2012
Copyright (c) 1982, 2011, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, Automatic Storage Management, OLAP, Data Mining
and Real Application Testing options
SQL> truncate table t;
Table truncated.
SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, Automatic Storage Management, OLAP, Data Mining
and Real Application Testing options
[oracle@vmxdb05b ~]$ vi trigger_changes.sql
[oracle@vmxdb05b ~]$ sqlplus donghua/donghua
SQL*Plus: Release 11.2.0.3.0 Production on Sun Jan 8 12:42:26 2012
Copyright (c) 1982, 2011, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, Automatic Storage Management, OLAP, Data Mining
and Real Application Testing options
SQL> @trigger_changes.sql
SQL> col CURR_TIME for a30
SQL> col next_time for a30
SQL> insert into t values (t_s1.nextval,systimestamp);
1 row created.
SQL> commit;
Commit complete.
SQL> alter system switch logfile;
System altered.
SQL> alter system switch logfile;
System altered.
SQL> select ora_rowscn,id,curr_time from t;
ORA_ROWSCN ID CURR_TIME
---------- ---------- ------------------------------
1228384 23 08-JAN-12 12.42.30.484001 PM
SQL> select * from (
2 select sequence#,to_char(next_time,'dd-mon-yy hh24.mi.ss') next_time,rownum rn from v$archived_log order by sequence#)
3 where rn<11;
SEQUENCE# NEXT_TIME RN
---------- ------------------------------ ----------
3 25-nov-11 08.09.55 1
4 27-nov-11 17.25.37 2
5 27-nov-11 21.00.08 3
6 07-dec-11 22.05.11 4
7 11-dec-11 10.41.06 5
8 11-dec-11 11.11.00 6
9 08-jan-12 09.46.15 7
10 08-jan-12 09.57.38 8
11 08-jan-12 11.08.41 9
12 08-jan-12 11.09.56 10
10 rows selected.
SQL>
[grid@vmxdb05b ~]$ crsctl stop has
[root@vmxdb05b ~]# date
Sun Jan 8 12:45:02 SGT 2012
You have new mail in /var/spool/mail/root
[root@vmxdb05b ~]# date 01081446
Sun Jan 8 14:46:00 SGT 2012
[root@vmxdb05b ~]# date
Sun Jan 8 14:46:01 SGT 2012
[root@vmxdb05b ~]#
[grid@vmxdb05b ~]$ crsctl start has
SQL> @trigger_changes.sql
SQL> col CURR_TIME for a30
SQL> col next_time for a30
SQL> insert into t values (t_s1.nextval,systimestamp);
1 row created.
SQL> commit;
Commit complete.
SQL> alter system switch logfile;
System altered.
SQL> alter system switch logfile;
System altered.
SQL> select ora_rowscn,id,curr_time from t;
ORA_ROWSCN ID CURR_TIME
---------- ---------- ------------------------------
1228384 23 08-JAN-12 12.42.30.484001 PM
1248897 24 08-JAN-12 02.48.55.368836 PM
SQL> select * from (
2 select sequence#,to_char(next_time,'dd-mon-yy hh24.mi.ss') next_time from v$archived_log order by sequence# desc )
3 where rownum<11
4 /
SEQUENCE# NEXT_TIME
---------- ------------------------------
19 08-jan-12 14.48.55
18 08-jan-12 14.48.55
17 08-jan-12 14.47.34
16 08-jan-12 12.42.30
15 08-jan-12 12.42.30
14 08-jan-12 12.17.48
13 08-jan-12 10.18.42
12 08-jan-12 11.09.56
11 08-jan-12 11.08.41
10 08-jan-12 09.57.38
10 rows selected.
[grid@vmxdb05b ~]$ crsctl stop has
[root@vmxdb05b ~]# date
Sun Jan 8 14:59:13 SGT 2012
[root@vmxdb05b ~]# date 01081259
Sun Jan 8 12:59:00 SGT 2012
[root@vmxdb05b ~]# date
Sun Jan 8 12:59:02 SGT 2012
[root@vmxdb05b ~]#
[grid@vmxdb05b ~]$ crsctl start has
SQL> @trigger_changes.sql
SQL> col CURR_TIME for a30
SQL> col next_time for a30
SQL> insert into t values (t_s1.nextval,systimestamp);
1 row created.
SQL> commit;
Commit complete.
SQL> alter system switch logfile;
System altered.
SQL> alter system switch logfile;
System altered.
SQL> select ora_rowscn,id,curr_time from t;
ORA_ROWSCN ID CURR_TIME
---------- ---------- ------------------------------
1228384 23 08-JAN-12 12.42.30.484001 PM
1269374 25 08-JAN-12 01.04.57.367069 PM
1248897 24 08-JAN-12 02.48.55.368836 PM
SQL> select * from (
2 select sequence#,to_char(next_time,'dd-mon-yy hh24.mi.ss') next_time from v$archived_log order by sequence# desc)
3 where rownum<11;
SEQUENCE# NEXT_TIME
---------- ------------------------------
20 08-jan-12 13.01.12
19 08-jan-12 14.48.55
18 08-jan-12 14.48.55
17 08-jan-12 14.47.34
16 08-jan-12 12.42.30
15 08-jan-12 12.42.30
14 08-jan-12 12.17.48
13 08-jan-12 10.18.42
12 08-jan-12 11.09.56
11 08-jan-12 11.08.41
10 rows selected.
[root@vmxdb05b ~]# date
Sun Jan 8 13:06:06 SGT 2012
[root@vmxdb05b ~]# date 01091306
Mon Jan 9 13:06:00 SGT 2012
[root@vmxdb05b ~]# date
Mon Jan 9 13:06:01 SGT 2012
SQL>
SQL> @trigger_changes.sql
SQL> set echo on
SQL> col CURR_TIME for a30
SQL> col next_time for a30
SQL> insert into t values (t_s1.nextval,systimestamp);
1 row created.
SQL> commit;
Commit complete.
SQL> alter system switch logfile;
System altered.
SQL> alter system switch logfile;
System altered.
SQL> select ora_rowscn,id,curr_time from t;
ORA_ROWSCN ID CURR_TIME
---------- ---------- ------------------------------
1228384 23 08-JAN-12 12.42.30.484001 PM
1269605 25 08-JAN-12 01.04.57.367069 PM
1269605 26 09-JAN-12 01.06.31.790207 PM
1248897 24 08-JAN-12 02.48.55.368836 PM
SQL> select * from (
2 select sequence#,to_char(next_time,'dd-mon-yy hh24.mi.ss') next_time from v$archived_log order by sequence# desc)
3 where rownum<11;
SEQUENCE# NEXT_TIME
---------- ------------------------------
22 08-jan-12 13.04.57
21 08-jan-12 13.04.57
20 08-jan-12 13.01.12
19 08-jan-12 14.48.55
18 08-jan-12 14.48.55
17 08-jan-12 14.47.34
16 08-jan-12 12.42.30
15 08-jan-12 12.42.30
14 08-jan-12 12.17.48
13 08-jan-12 10.18.42
10 rows selected.
SQL>
SQL> @trigger_changes.sql
SQL> set echo on
SQL> col CURR_TIME for a30
SQL> col next_time for a30
SQL> insert into t values (t_s1.nextval,systimestamp);
1 row created.
SQL> commit;
Commit complete.
SQL> alter system switch logfile;
System altered.
SQL> alter system switch logfile;
System altered.
SQL> select ora_rowscn,id,curr_time from t;
ORA_ROWSCN ID CURR_TIME
---------- ---------- ------------------------------
1228384 23 08-JAN-12 12.42.30.484001 PM
1269683 25 08-JAN-12 01.04.57.367069 PM
1269683 26 09-JAN-12 01.06.31.790207 PM
1269683 27 09-JAN-12 01.06.59.125860 PM
1248897 24 08-JAN-12 02.48.55.368836 PM
SQL> select * from (
2 select sequence#,to_char(next_time,'dd-mon-yy hh24.mi.ss') next_time from v$archived_log order by sequence# desc)
3 where rownum<11;
SEQUENCE# NEXT_TIME
---------- ------------------------------
25 09-jan-12 13.07.01
24 09-jan-12 13.06.34
23 09-jan-12 13.06.34
22 08-jan-12 13.04.57
21 08-jan-12 13.04.57
20 08-jan-12 13.01.12
19 08-jan-12 14.48.55
18 08-jan-12 14.48.55
17 08-jan-12 14.47.34
16 08-jan-12 12.42.30
10 rows selected.
SQL>
[root@vmxdb05b ~]# date
Mon Jan 9 13:09:48 SGT 2012
[root@vmxdb05b ~]# date 01081310
Sun Jan 8 13:10:00 SGT 2012
[root@vmxdb05b ~]# date
Sun Jan 8 13:10:00 SGT 2012
SQL> @@trigger_changes.sql
SQL> col CURR_TIME for a30
SQL> col next_time for a30
SQL> insert into t values (t_s1.nextval,systimestamp);
1 row created.
SQL> commit;
Commit complete.
SQL> alter system switch logfile;
System altered.
SQL> alter system switch logfile;
System altered.
SQL> select ora_rowscn,id,curr_time from t;
ORA_ROWSCN ID CURR_TIME
---------- ---------- ------------------------------
1228384 23 08-JAN-12 12.42.30.484001 PM
1269683 25 08-JAN-12 01.04.57.367069 PM
1269683 26 09-JAN-12 01.06.31.790207 PM
1269683 27 09-JAN-12 01.06.59.125860 PM
1248897 24 08-JAN-12 02.48.55.368836 PM
1270330 28 08-JAN-12 01.10.21.538665 PM
6 rows selected.
SQL> select * from (
2 select sequence#,to_char(next_time,'dd-mon-yy hh24.mi.ss') next_time from v$archived_log order by sequence# desc)
3 where rownum<11;
SEQUENCE# NEXT_TIME
---------- ------------------------------
26 09-jan-12 13.07.04
25 09-jan-12 13.07.01
24 09-jan-12 13.06.34
23 09-jan-12 13.06.34
22 08-jan-12 13.04.57
21 08-jan-12 13.04.57
20 08-jan-12 13.01.12
19 08-jan-12 14.48.55
18 08-jan-12 14.48.55
17 08-jan-12 14.47.34
10 rows selected.
SQL>
SQL>
SQL> @trigger_changes.sql
SQL> set echo on
SQL> col CURR_TIME for a30
SQL> col next_time for a30
SQL> insert into t values (t_s1.nextval,systimestamp);
1 row created.
SQL> commit;
Commit complete.
SQL> alter system switch logfile;
System altered.
SQL> alter system switch logfile;
System altered.
SQL> select ora_rowscn,id,curr_time from t;
ORA_ROWSCN ID CURR_TIME
---------- ---------- ------------------------------
1228384 23 08-JAN-12 12.42.30.484001 PM
1269683 25 08-JAN-12 01.04.57.367069 PM
1269683 26 09-JAN-12 01.06.31.790207 PM
1269683 27 09-JAN-12 01.06.59.125860 PM
1248897 24 08-JAN-12 02.48.55.368836 PM
1270344 28 08-JAN-12 01.10.21.538665 PM
1270344 29 08-JAN-12 01.10.24.453826 PM
7 rows selected.
SQL> select * from (
2 select sequence#,to_char(next_time,'dd-mon-yy hh24.mi.ss') next_time from v$archived_log order by sequence# desc)
3 where rownum<11;
SEQUENCE# NEXT_TIME
---------- ------------------------------
28 08-jan-12 13.10.21
27 08-jan-12 13.10.21
26 09-jan-12 13.07.04
25 09-jan-12 13.07.01
24 09-jan-12 13.06.34
23 09-jan-12 13.06.34
22 08-jan-12 13.04.57
21 08-jan-12 13.04.57
20 08-jan-12 13.01.12
19 08-jan-12 14.48.55
10 rows selected.
SQL>
SQL>
[oracle@vmxdb05b trace]$ cat orcl05b_vktm_9981.trc
Trace file /u01/app/oracle/diag/rdbms/orcl05b/orcl05b/trace/orcl05b_vktm_9981.trc
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
ORACLE_HOME = /u01/app/oracle/product/11.2.0/dbhome_1
System name: Linux
Node name: vmxdb05b.lab.dbaglobe.com
Release: 2.6.32-71.29.1.el6.x86_64
Version: #1 SMP Mon Jun 27 19:49:27 BST 2011
Machine: x86_64
VM name: VMWare Version: 6
Instance name: orcl05b
Redo thread mounted by this instance: 0
Oracle process number: 4
Unix process pid: 9981, image: oracle@vmxdb05b.lab.dbaglobe.com (VKTM)
*** 2012-01-08 13:01:04.109
*** SESSION ID:(4.1) 2012-01-08 13:01:04.109
*** CLIENT ID:() 2012-01-08 13:01:04.109
*** SERVICE NAME:() 2012-01-08 13:01:04.109
*** MODULE NAME:() 2012-01-08 13:01:04.109
*** ACTION NAME:() 2012-01-08 13:01:04.109
kstmmainvktm: succeeded in setting elevated priority
highres_enabled
*** 2012-01-08 13:01:04.109
VKTM running at (1)millisec precision with DBRM quantum (100)ms
[Start] HighResTick = 1325998864109366
kstmrmtickcnt = 0 : ksudbrmseccnt[0] = 1325998864
*** 2012-01-09 13:06:00.000
kstmchkdrift (kstmhighrestimecntkeeper:highres): Time jumped forward by (86384713465)usec at (1326085560000441) whereas (1000000) is allowed
[End] HighResTick = 1326085758255964
*** 2012-01-09 13:09:18.257
kstmrmtickcnt = 5066 : ksudbrmseccnt[510] = 1326085758
[oracle@vmxdb05b ~]$ sqlplus donghua/donghua
SQL*Plus: Release 11.2.0.3.0 Production on Sun Jan 8 12:42:07 2012
Copyright (c) 1982, 2011, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, Automatic Storage Management, OLAP, Data Mining
and Real Application Testing options
SQL> truncate table t;
Table truncated.
SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, Automatic Storage Management, OLAP, Data Mining
and Real Application Testing options
[oracle@vmxdb05b ~]$ vi trigger_changes.sql
[oracle@vmxdb05b ~]$ sqlplus donghua/donghua
SQL*Plus: Release 11.2.0.3.0 Production on Sun Jan 8 12:42:26 2012
Copyright (c) 1982, 2011, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, Automatic Storage Management, OLAP, Data Mining
and Real Application Testing options
SQL> @trigger_changes.sql
SQL> col CURR_TIME for a30
SQL> col next_time for a30
SQL> insert into t values (t_s1.nextval,systimestamp);
1 row created.
SQL> commit;
Commit complete.
SQL> alter system switch logfile;
System altered.
SQL> alter system switch logfile;
System altered.
SQL> select ora_rowscn,id,curr_time from t;
ORA_ROWSCN ID CURR_TIME
---------- ---------- ------------------------------
1228384 23 08-JAN-12 12.42.30.484001 PM
SQL> select * from (
2 select sequence#,to_char(next_time,'dd-mon-yy hh24.mi.ss') next_time,rownum rn from v$archived_log order by sequence#)
3 where rn<11;
SEQUENCE# NEXT_TIME RN
---------- ------------------------------ ----------
3 25-nov-11 08.09.55 1
4 27-nov-11 17.25.37 2
5 27-nov-11 21.00.08 3
6 07-dec-11 22.05.11 4
7 11-dec-11 10.41.06 5
8 11-dec-11 11.11.00 6
9 08-jan-12 09.46.15 7
10 08-jan-12 09.57.38 8
11 08-jan-12 11.08.41 9
12 08-jan-12 11.09.56 10
10 rows selected.
SQL>
[grid@vmxdb05b ~]$ crsctl stop has
[root@vmxdb05b ~]# date
Sun Jan 8 12:45:02 SGT 2012
You have new mail in /var/spool/mail/root
[root@vmxdb05b ~]# date 01081446
Sun Jan 8 14:46:00 SGT 2012
[root@vmxdb05b ~]# date
Sun Jan 8 14:46:01 SGT 2012
[root@vmxdb05b ~]#
[grid@vmxdb05b ~]$ crsctl start has
SQL> @trigger_changes.sql
SQL> col CURR_TIME for a30
SQL> col next_time for a30
SQL> insert into t values (t_s1.nextval,systimestamp);
1 row created.
SQL> commit;
Commit complete.
SQL> alter system switch logfile;
System altered.
SQL> alter system switch logfile;
System altered.
SQL> select ora_rowscn,id,curr_time from t;
ORA_ROWSCN ID CURR_TIME
---------- ---------- ------------------------------
1228384 23 08-JAN-12 12.42.30.484001 PM
1248897 24 08-JAN-12 02.48.55.368836 PM
SQL> select * from (
2 select sequence#,to_char(next_time,'dd-mon-yy hh24.mi.ss') next_time from v$archived_log order by sequence# desc )
3 where rownum<11
4 /
SEQUENCE# NEXT_TIME
---------- ------------------------------
19 08-jan-12 14.48.55
18 08-jan-12 14.48.55
17 08-jan-12 14.47.34
16 08-jan-12 12.42.30
15 08-jan-12 12.42.30
14 08-jan-12 12.17.48
13 08-jan-12 10.18.42
12 08-jan-12 11.09.56
11 08-jan-12 11.08.41
10 08-jan-12 09.57.38
10 rows selected.
[grid@vmxdb05b ~]$ crsctl stop has
[root@vmxdb05b ~]# date
Sun Jan 8 14:59:13 SGT 2012
[root@vmxdb05b ~]# date 01081259
Sun Jan 8 12:59:00 SGT 2012
[root@vmxdb05b ~]# date
Sun Jan 8 12:59:02 SGT 2012
[root@vmxdb05b ~]#
[grid@vmxdb05b ~]$ crsctl start has
SQL> @trigger_changes.sql
SQL> col CURR_TIME for a30
SQL> col next_time for a30
SQL> insert into t values (t_s1.nextval,systimestamp);
1 row created.
SQL> commit;
Commit complete.
SQL> alter system switch logfile;
System altered.
SQL> alter system switch logfile;
System altered.
SQL> select ora_rowscn,id,curr_time from t;
ORA_ROWSCN ID CURR_TIME
---------- ---------- ------------------------------
1228384 23 08-JAN-12 12.42.30.484001 PM
1269374 25 08-JAN-12 01.04.57.367069 PM
1248897 24 08-JAN-12 02.48.55.368836 PM
SQL> select * from (
2 select sequence#,to_char(next_time,'dd-mon-yy hh24.mi.ss') next_time from v$archived_log order by sequence# desc)
3 where rownum<11;
SEQUENCE# NEXT_TIME
---------- ------------------------------
20 08-jan-12 13.01.12
19 08-jan-12 14.48.55
18 08-jan-12 14.48.55
17 08-jan-12 14.47.34
16 08-jan-12 12.42.30
15 08-jan-12 12.42.30
14 08-jan-12 12.17.48
13 08-jan-12 10.18.42
12 08-jan-12 11.09.56
11 08-jan-12 11.08.41
10 rows selected.
[root@vmxdb05b ~]# date
Sun Jan 8 13:06:06 SGT 2012
[root@vmxdb05b ~]# date 01091306
Mon Jan 9 13:06:00 SGT 2012
[root@vmxdb05b ~]# date
Mon Jan 9 13:06:01 SGT 2012
SQL>
SQL> @trigger_changes.sql
SQL> set echo on
SQL> col CURR_TIME for a30
SQL> col next_time for a30
SQL> insert into t values (t_s1.nextval,systimestamp);
1 row created.
SQL> commit;
Commit complete.
SQL> alter system switch logfile;
System altered.
SQL> alter system switch logfile;
System altered.
SQL> select ora_rowscn,id,curr_time from t;
ORA_ROWSCN ID CURR_TIME
---------- ---------- ------------------------------
1228384 23 08-JAN-12 12.42.30.484001 PM
1269605 25 08-JAN-12 01.04.57.367069 PM
1269605 26 09-JAN-12 01.06.31.790207 PM
1248897 24 08-JAN-12 02.48.55.368836 PM
SQL> select * from (
2 select sequence#,to_char(next_time,'dd-mon-yy hh24.mi.ss') next_time from v$archived_log order by sequence# desc)
3 where rownum<11;
SEQUENCE# NEXT_TIME
---------- ------------------------------
22 08-jan-12 13.04.57
21 08-jan-12 13.04.57
20 08-jan-12 13.01.12
19 08-jan-12 14.48.55
18 08-jan-12 14.48.55
17 08-jan-12 14.47.34
16 08-jan-12 12.42.30
15 08-jan-12 12.42.30
14 08-jan-12 12.17.48
13 08-jan-12 10.18.42
10 rows selected.
SQL>
SQL> @trigger_changes.sql
SQL> set echo on
SQL> col CURR_TIME for a30
SQL> col next_time for a30
SQL> insert into t values (t_s1.nextval,systimestamp);
1 row created.
SQL> commit;
Commit complete.
SQL> alter system switch logfile;
System altered.
SQL> alter system switch logfile;
System altered.
SQL> select ora_rowscn,id,curr_time from t;
ORA_ROWSCN ID CURR_TIME
---------- ---------- ------------------------------
1228384 23 08-JAN-12 12.42.30.484001 PM
1269683 25 08-JAN-12 01.04.57.367069 PM
1269683 26 09-JAN-12 01.06.31.790207 PM
1269683 27 09-JAN-12 01.06.59.125860 PM
1248897 24 08-JAN-12 02.48.55.368836 PM
SQL> select * from (
2 select sequence#,to_char(next_time,'dd-mon-yy hh24.mi.ss') next_time from v$archived_log order by sequence# desc)
3 where rownum<11;
SEQUENCE# NEXT_TIME
---------- ------------------------------
25 09-jan-12 13.07.01
24 09-jan-12 13.06.34
23 09-jan-12 13.06.34
22 08-jan-12 13.04.57
21 08-jan-12 13.04.57
20 08-jan-12 13.01.12
19 08-jan-12 14.48.55
18 08-jan-12 14.48.55
17 08-jan-12 14.47.34
16 08-jan-12 12.42.30
10 rows selected.
SQL>
[root@vmxdb05b ~]# date
Mon Jan 9 13:09:48 SGT 2012
[root@vmxdb05b ~]# date 01081310
Sun Jan 8 13:10:00 SGT 2012
[root@vmxdb05b ~]# date
Sun Jan 8 13:10:00 SGT 2012
SQL> @@trigger_changes.sql
SQL> col CURR_TIME for a30
SQL> col next_time for a30
SQL> insert into t values (t_s1.nextval,systimestamp);
1 row created.
SQL> commit;
Commit complete.
SQL> alter system switch logfile;
System altered.
SQL> alter system switch logfile;
System altered.
SQL> select ora_rowscn,id,curr_time from t;
ORA_ROWSCN ID CURR_TIME
---------- ---------- ------------------------------
1228384 23 08-JAN-12 12.42.30.484001 PM
1269683 25 08-JAN-12 01.04.57.367069 PM
1269683 26 09-JAN-12 01.06.31.790207 PM
1269683 27 09-JAN-12 01.06.59.125860 PM
1248897 24 08-JAN-12 02.48.55.368836 PM
1270330 28 08-JAN-12 01.10.21.538665 PM
6 rows selected.
SQL> select * from (
2 select sequence#,to_char(next_time,'dd-mon-yy hh24.mi.ss') next_time from v$archived_log order by sequence# desc)
3 where rownum<11;
SEQUENCE# NEXT_TIME
---------- ------------------------------
26 09-jan-12 13.07.04
25 09-jan-12 13.07.01
24 09-jan-12 13.06.34
23 09-jan-12 13.06.34
22 08-jan-12 13.04.57
21 08-jan-12 13.04.57
20 08-jan-12 13.01.12
19 08-jan-12 14.48.55
18 08-jan-12 14.48.55
17 08-jan-12 14.47.34
10 rows selected.
SQL>
SQL>
SQL> @trigger_changes.sql
SQL> set echo on
SQL> col CURR_TIME for a30
SQL> col next_time for a30
SQL> insert into t values (t_s1.nextval,systimestamp);
1 row created.
SQL> commit;
Commit complete.
SQL> alter system switch logfile;
System altered.
SQL> alter system switch logfile;
System altered.
SQL> select ora_rowscn,id,curr_time from t;
ORA_ROWSCN ID CURR_TIME
---------- ---------- ------------------------------
1228384 23 08-JAN-12 12.42.30.484001 PM
1269683 25 08-JAN-12 01.04.57.367069 PM
1269683 26 09-JAN-12 01.06.31.790207 PM
1269683 27 09-JAN-12 01.06.59.125860 PM
1248897 24 08-JAN-12 02.48.55.368836 PM
1270344 28 08-JAN-12 01.10.21.538665 PM
1270344 29 08-JAN-12 01.10.24.453826 PM
7 rows selected.
SQL> select * from (
2 select sequence#,to_char(next_time,'dd-mon-yy hh24.mi.ss') next_time from v$archived_log order by sequence# desc)
3 where rownum<11;
SEQUENCE# NEXT_TIME
---------- ------------------------------
28 08-jan-12 13.10.21
27 08-jan-12 13.10.21
26 09-jan-12 13.07.04
25 09-jan-12 13.07.01
24 09-jan-12 13.06.34
23 09-jan-12 13.06.34
22 08-jan-12 13.04.57
21 08-jan-12 13.04.57
20 08-jan-12 13.01.12
19 08-jan-12 14.48.55
10 rows selected.
SQL>
SQL>
[oracle@vmxdb05b trace]$ cat orcl05b_vktm_9981.trc
Trace file /u01/app/oracle/diag/rdbms/orcl05b/orcl05b/trace/orcl05b_vktm_9981.trc
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
ORACLE_HOME = /u01/app/oracle/product/11.2.0/dbhome_1
System name: Linux
Node name: vmxdb05b.lab.dbaglobe.com
Release: 2.6.32-71.29.1.el6.x86_64
Version: #1 SMP Mon Jun 27 19:49:27 BST 2011
Machine: x86_64
VM name: VMWare Version: 6
Instance name: orcl05b
Redo thread mounted by this instance: 0
Oracle process number: 4
Unix process pid: 9981, image: oracle@vmxdb05b.lab.dbaglobe.com (VKTM)
*** 2012-01-08 13:01:04.109
*** SESSION ID:(4.1) 2012-01-08 13:01:04.109
*** CLIENT ID:() 2012-01-08 13:01:04.109
*** SERVICE NAME:() 2012-01-08 13:01:04.109
*** MODULE NAME:() 2012-01-08 13:01:04.109
*** ACTION NAME:() 2012-01-08 13:01:04.109
kstmmainvktm: succeeded in setting elevated priority
highres_enabled
*** 2012-01-08 13:01:04.109
VKTM running at (1)millisec precision with DBRM quantum (100)ms
[Start] HighResTick = 1325998864109366
kstmrmtickcnt = 0 : ksudbrmseccnt[0] = 1325998864
*** 2012-01-09 13:06:00.000
kstmchkdrift (kstmhighrestimecntkeeper:highres): Time jumped forward by (86384713465)usec at (1326085560000441) whereas (1000000) is allowed
[End] HighResTick = 1326085758255964
*** 2012-01-09 13:09:18.257
kstmrmtickcnt = 5066 : ksudbrmseccnt[510] = 1326085758
Monday, December 19, 2011
Sample dataguard configuration for 10g database (prorcl/drorcl)
-- DR
ALTER DATABASE ADD STANDBY LOGFILE THREAD 1 group 5
'/u01/app/oracle/oradata/prorcl/stdby05.log' size 50M;
ALTER DATABASE ADD STANDBY LOGFILE THREAD 1 group 6
'/u01/app/oracle/oradata/prorcl/stdby06.log' size 50M;
ALTER DATABASE ADD STANDBY LOGFILE THREAD 1 group 7
'/u01/app/oracle/oradata/prorcl/stdby07.log' size 50M;
ALTER DATABASE ADD STANDBY LOGFILE THREAD 1 group 8
'/u01/app/oracle/oradata/prorcl/stdby08.log' size 50M;
alter system set db_unique_name='drorcl' scope=spfile;
alter system set LOG_ARCHIVE_CONFIG='DG_CONFIG=(prorcl,drorcl)';
alter system set LOG_ARCHIVE_DEST_1='LOCATION=USE_DB_RECOVERY_FILE_DEST VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=drorcl';
alter system set LOG_ARCHIVE_DEST_2= 'SERVICE=prorcl LGWR ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=prorcl';
alter system set LOG_ARCHIVE_DEST_STATE_1=ENABLE;
alter system set LOG_ARCHIVE_DEST_STATE_2=ENABLE;
alter system set LOG_ARCHIVE_DEST_STATE_10=DEFER;
alter system set FAL_SERVER='prorcl';
alter system set FAL_CLIENT='drorcl';
alter system set DB_FILE_NAME_CONVERT='prorcl','prorcl' scope=spfile;
alter system set LOG_FILE_NAME_CONVERT='prorcl','prorcl' scope=spfile;
alter system set STANDBY_FILE_MANAGEMENT=AUTO;
-----------------------------------------------------------------------------------
-- PR
ALTER DATABASE ADD STANDBY LOGFILE THREAD 1 group 5
'/u01/app/oracle/oradata/prorcl/stdby05.log' size 50M;
ALTER DATABASE ADD STANDBY LOGFILE THREAD 1 group 6
'/u01/app/oracle/oradata/prorcl/stdby06.log' size 50M;
ALTER DATABASE ADD STANDBY LOGFILE THREAD 1 group 7
'/u01/app/oracle/oradata/prorcl/stdby07.log' size 50M;
ALTER DATABASE ADD STANDBY LOGFILE THREAD 1 group 8
'/u01/app/oracle/oradata/prorcl/stdby08.log' size 50M;
alter system set db_unique_name='prorcl' scope=spfile;
alter system set LOG_ARCHIVE_CONFIG='DG_CONFIG=(prorcl,drorcl)';
alter system set LOG_ARCHIVE_DEST_1='LOCATION=USE_DB_RECOVERY_FILE_DEST VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=prorcl';
alter system set LOG_ARCHIVE_DEST_2= 'SERVICE=drorcl LGWR ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=drorcl';
alter system set LOG_ARCHIVE_DEST_STATE_1=ENABLE;
alter system set LOG_ARCHIVE_DEST_STATE_2=ENABLE;
alter system set LOG_ARCHIVE_DEST_STATE_10=DEFER;
alter system set FAL_SERVER='drorcl';
alter system set FAL_CLIENT='prorcl';
alter system set DB_FILE_NAME_CONVERT='prorcl','prorcl' scope=spfile;
alter system set LOG_FILE_NAME_CONVERT='prorcl','prorcl' scope=spfile;
alter system set STANDBY_FILE_MANAGEMENT=AUTO;
ALTER DATABASE ADD STANDBY LOGFILE THREAD 1 group 5
'/u01/app/oracle/oradata/prorcl/stdby05.log' size 50M;
ALTER DATABASE ADD STANDBY LOGFILE THREAD 1 group 6
'/u01/app/oracle/oradata/prorcl/stdby06.log' size 50M;
ALTER DATABASE ADD STANDBY LOGFILE THREAD 1 group 7
'/u01/app/oracle/oradata/prorcl/stdby07.log' size 50M;
ALTER DATABASE ADD STANDBY LOGFILE THREAD 1 group 8
'/u01/app/oracle/oradata/prorcl/stdby08.log' size 50M;
alter system set db_unique_name='drorcl' scope=spfile;
alter system set LOG_ARCHIVE_CONFIG='DG_CONFIG=(prorcl,drorcl)';
alter system set LOG_ARCHIVE_DEST_1='LOCATION=USE_DB_RECOVERY_FILE_DEST VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=drorcl';
alter system set LOG_ARCHIVE_DEST_2= 'SERVICE=prorcl LGWR ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=prorcl';
alter system set LOG_ARCHIVE_DEST_STATE_1=ENABLE;
alter system set LOG_ARCHIVE_DEST_STATE_2=ENABLE;
alter system set LOG_ARCHIVE_DEST_STATE_10=DEFER;
alter system set FAL_SERVER='prorcl';
alter system set FAL_CLIENT='drorcl';
alter system set DB_FILE_NAME_CONVERT='prorcl','prorcl' scope=spfile;
alter system set LOG_FILE_NAME_CONVERT='prorcl','prorcl' scope=spfile;
alter system set STANDBY_FILE_MANAGEMENT=AUTO;
-----------------------------------------------------------------------------------
-- PR
ALTER DATABASE ADD STANDBY LOGFILE THREAD 1 group 5
'/u01/app/oracle/oradata/prorcl/stdby05.log' size 50M;
ALTER DATABASE ADD STANDBY LOGFILE THREAD 1 group 6
'/u01/app/oracle/oradata/prorcl/stdby06.log' size 50M;
ALTER DATABASE ADD STANDBY LOGFILE THREAD 1 group 7
'/u01/app/oracle/oradata/prorcl/stdby07.log' size 50M;
ALTER DATABASE ADD STANDBY LOGFILE THREAD 1 group 8
'/u01/app/oracle/oradata/prorcl/stdby08.log' size 50M;
alter system set db_unique_name='prorcl' scope=spfile;
alter system set LOG_ARCHIVE_CONFIG='DG_CONFIG=(prorcl,drorcl)';
alter system set LOG_ARCHIVE_DEST_1='LOCATION=USE_DB_RECOVERY_FILE_DEST VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=prorcl';
alter system set LOG_ARCHIVE_DEST_2= 'SERVICE=drorcl LGWR ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=drorcl';
alter system set LOG_ARCHIVE_DEST_STATE_1=ENABLE;
alter system set LOG_ARCHIVE_DEST_STATE_2=ENABLE;
alter system set LOG_ARCHIVE_DEST_STATE_10=DEFER;
alter system set FAL_SERVER='drorcl';
alter system set FAL_CLIENT='prorcl';
alter system set DB_FILE_NAME_CONVERT='prorcl','prorcl' scope=spfile;
alter system set LOG_FILE_NAME_CONVERT='prorcl','prorcl' scope=spfile;
alter system set STANDBY_FILE_MANAGEMENT=AUTO;
Sunday, December 18, 2011
ORA-16047: DGID mismatch between destination setting and standby
[oracle@vmxdb01b ~]$ more /u01/app/oracle/admin/prorcl/bdump/prorcl_arc0_5809.trc
/u01/app/oracle/admin/prorcl/bdump/prorcl_arc0_5809.trc
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - 64bit Production
With the Partitioning, OLAP and Data Mining options
ORACLE_HOME = /u01/app/oracle/product/10.2.0/db_1
System name: Linux
Node name: vmxdb01b.lab.dbaglobe.com
Release: 2.6.18-274.12.1.el5
Version: #1 SMP Tue Nov 29 13:37:46 EST 2011
Machine: x86_64
Instance name: prorcl
Redo thread mounted by this instance: 1
Oracle process number: 16
Unix process pid: 5809, image: oracle@vmxdb01b.lab.dbaglobe.com (ARC0)
*** SERVICE NAME:() 2011-12-18 23:47:14.471
*** SESSION ID:(156.1) 2011-12-18 23:47:14.471
kcrrwkx: nothing to do (start)
Redo shipping client performing standby login
*** 2011-12-18 23:47:14.590 64561 kcrr.c
Logged on to standby successfully
Client logon and security negotiation successful!
Error 16047 attaching to destination LOG_ARCHIVE_DEST_2 standby host 'drorcl'
ORA-16047: DGID mismatch between destination setting and standby
*** 2011-12-18 23:47:14.591 58941 kcrr.c
kcrrfail: dest:2 err:16047 force:0 blast:1
kcrrwkx: unknown error:16047
ORA-16055: FAL request rejected
ARCH: Connecting to console port...
ARCH: Connecting to console port...
kcrrwkx: nothing to do (end)
Root Cause:
Case mis-match between db_unique_name and log_archive_config
alter system set LOG_ARCHIVE_CONFIG='DG_CONFIG=(prorcl,drorcl)';
db_name = prorcl
db_unique_name = PRORCL
/u01/app/oracle/admin/prorcl/bdump/prorcl_arc0_5809.trc
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - 64bit Production
With the Partitioning, OLAP and Data Mining options
ORACLE_HOME = /u01/app/oracle/product/10.2.0/db_1
System name: Linux
Node name: vmxdb01b.lab.dbaglobe.com
Release: 2.6.18-274.12.1.el5
Version: #1 SMP Tue Nov 29 13:37:46 EST 2011
Machine: x86_64
Instance name: prorcl
Redo thread mounted by this instance: 1
Oracle process number: 16
Unix process pid: 5809, image: oracle@vmxdb01b.lab.dbaglobe.com (ARC0)
*** SERVICE NAME:() 2011-12-18 23:47:14.471
*** SESSION ID:(156.1) 2011-12-18 23:47:14.471
kcrrwkx: nothing to do (start)
Redo shipping client performing standby login
*** 2011-12-18 23:47:14.590 64561 kcrr.c
Logged on to standby successfully
Client logon and security negotiation successful!
Error 16047 attaching to destination LOG_ARCHIVE_DEST_2 standby host 'drorcl'
ORA-16047: DGID mismatch between destination setting and standby
*** 2011-12-18 23:47:14.591 58941 kcrr.c
kcrrfail: dest:2 err:16047 force:0 blast:1
kcrrwkx: unknown error:16047
ORA-16055: FAL request rejected
ARCH: Connecting to console port...
ARCH: Connecting to console port...
kcrrwkx: nothing to do (end)
Root Cause:
Case mis-match between db_unique_name and log_archive_config
alter system set LOG_ARCHIVE_CONFIG='DG_CONFIG=(prorcl,drorcl)';
db_name = prorcl
db_unique_name = PRORCL
Sunday, December 11, 2011
TNS-12599: TNS:cryptographic checksum mismatch
Symptoms:
After Enterprise manager Grid Control 12c installed, following message appears in alert log of repository database.
Fri Dec 09 14:11:21 2011
***********************************************************************
NI cryptographic checksum mismatch error: 12599.
VERSION INFORMATION:
TNS for Linux: Version 11.2.0.2.0 - Production
Oracle Bequeath NT Protocol Adapter for Linux: Version 11.2.0.2.0 - Production
TCP/IP NT Protocol Adapter for Linux: Version 11.2.0.2.0 - Production
Time: 09-DEC-2011 14:11:21
Tracing not turned on.
Tns error struct:
ns main err code: 12599
TNS-12599: TNS:cryptographic checksum mismatch
ns secondary err code: 2526
nt main err code: 0
nt secondary err code: 0
nt OS err code: 0
Fri Dec 09 14:19:45 2011
Cause:
This is due to Bug 9871805. The Enterprise Manager OMS connects to the 11gR1 and 11gR2 Repository database
using 10.2 JDBC Thin connection and AES256 encryption algorithm.
The 11gR1 and 11gR2 database does not support client JDBC connections using AES encryption, hence the TNS-
12599: TNS:cryptographic checksum mismatch
Solution:
To Workaround the issue please do the following:
On Repository database side, set the parameter
SQLNET.ENCRYPTION_TYPES_SERVER= (3DES168)
in sqlnet.ora file
After Enterprise manager Grid Control 12c installed, following message appears in alert log of repository database.
Fri Dec 09 14:11:21 2011
***********************************************************************
NI cryptographic checksum mismatch error: 12599.
VERSION INFORMATION:
TNS for Linux: Version 11.2.0.2.0 - Production
Oracle Bequeath NT Protocol Adapter for Linux: Version 11.2.0.2.0 - Production
TCP/IP NT Protocol Adapter for Linux: Version 11.2.0.2.0 - Production
Time: 09-DEC-2011 14:11:21
Tracing not turned on.
Tns error struct:
ns main err code: 12599
TNS-12599: TNS:cryptographic checksum mismatch
ns secondary err code: 2526
nt main err code: 0
nt secondary err code: 0
nt OS err code: 0
Fri Dec 09 14:19:45 2011
Cause:
This is due to Bug 9871805. The Enterprise Manager OMS connects to the 11gR1 and 11gR2 Repository database
using 10.2 JDBC Thin connection and AES256 encryption algorithm.
The 11gR1 and 11gR2 database does not support client JDBC connections using AES encryption, hence the TNS-
12599: TNS:cryptographic checksum mismatch
Solution:
To Workaround the issue please do the following:
On Repository database side, set the parameter
SQLNET.ENCRYPTION_TYPES_SERVER= (3DES168)
in sqlnet.ora file
11g New feature: listagg
SQL> select * from v$version;
BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
PL/SQL Release 11.2.0.3.0 - Production
CORE 11.2.0.3.0 Production
TNS for Linux: Version 11.2.0.3.0 - Production
NLSRTL Version 11.2.0.3.0 - Production
SQL> select department_id dept,first_name
2 from employees
3 where department_id<50
4 order by department_id,first_name;
DEPT FIRST_NAME
---------- --------------------------------------------------
10 Jennifer
20 Michael
20 Pat
30 Alexander
30 Den
30 Guy
30 Karen
30 Shelli
30 Sigal
40 Susan
10 rows selected.
SQL> select department_id dept,listagg(first_name,';') within group (order by first_name) first_name
2 from employees
3 where department_id<50
4 group by department_id
5 order by department_id;
DEPT FIRST_NAME
---------- --------------------------------------------------
10 Jennifer
20 Michael;Pat
30 Alexander;Den;Guy;Karen;Shelli;Sigal
40 Susan
Subscribe to:
Posts (Atom)
