donghua@rh6:~/Downloads$ cat wget.sh
#!/bin/sh
#
# Generated Sun, 1 May 2011 14:33:17 Coordinated Universal Time
# Start of user configurable variables
#
# SSO username and password
SSO_USERNAME=your_oracle_support@yourdomain.com
SSO_PASSWORD=your_password_here
# E-Delivery token
# The EPD_TOKEN will expire 48 hours after the following generation date
# Sun, 1 May 2011 14:33:17 Coordinated Universal Time
EPD_TOKEN=
# Path to wget command
WGET=/usr/bin/wget
# Location of cookie file
COOKIE_FILE=/tmp/$$.cookies
# Log directory and file
LOGDIR=.
LOGFILE=$LOGDIR/wgetlog-`date +%m-%d-%y-%H:%M`.log
# Output directory and file
OUTPUT_DIR=.
#
# End of user configurable variable
#
if [ "$SSO_PASSWORD " = " " ]
then
echo "Please edit script and set SSO_PASSWORD"
exit
fi
# Contact updates site so that we can get SSO Params for logging in
SSO_RESPONSE=`$WGET --user-agent="Mozilla/5.0" https://updates.oracle.com/Orion/Services/download 2>&1|grep Location`
# Extract request parameters for SSO
SSO_TOKEN=`echo $SSO_RESPONSE| cut -d '=' -f 2|cut -d ' ' -f 1`
SSO_SERVER=`echo $SSO_RESPONSE| cut -d ' ' -f 2|cut -d 'p' -f 1,2`
SSO_AUTH_URL=sso/auth
AUTH_DATA="ssousername=$SSO_USERNAME&password=$SSO_PASSWORD&site2pstoretoken=$SSO_TOKEN"
# The following command to authenticate uses HTTPS. This will work only if the wget in the environment
# where this script will be executed was compiled with OpenSSL. Remove the --secure-protocol option
# if wget was not compiled with OpenSSL
# Depending on the preference, the other options are --secure-protocol= auto|SSLv2|SSLv3|TLSv1
$WGET --user-agent="Mozilla/5.0" --secure-protocol=auto --post-data $AUTH_DATA --save-cookies=$COOKIE_FILE --keep-session-cookies $SSO_SERVER$SSO_AUTH_URL -O sso.out >> $LOGFILE 2>&1
rm -f sso.out
$WGET --user-agent="Mozilla/5.0" --load-cookies=$COOKIE_FILE --save-cookies=$COOKIE_FILE --keep-session-cookies "https://updates.oracle.com/Orion/Services/download/p11069614_112020_AIX64-5L.zip?aru=13752552&patch_file=p11069614_112020_AIX64-5L.zip" -O $OUTPUT_DIR/p11724916_112020_LINUX.zip >> $LOGFILE 2>&1
# Cleanup
rm -f $COOKIE_FILE
Tuesday, May 31, 2011
Thursday, May 26, 2011
Manual create database in 11gR2 (11.2.0.2)
export ORACLE_SID=prorcl
[oracle@vmxdb01 ~]$ cat /u01/app/oracle/product/11.2.0.2/db_1/dbs/initprorcl.ora
*.audit_file_dest='/u01/app/oracle/admin/prorcl/adump'
*.audit_trail='db'
*.compatible='11.2.0.0.0'
*.control_files='/u01/app/oracle/oradata/prorcl/control01.ctl','/u01/app/oracle/oradata/prorcl/control02.ctl','/u01/app/oracle/oradata/prorcl/control03.ctl'
*.db_block_size=8192
*.db_domain=''
*.db_name='prorcl'
*.db_recovery_file_dest='/u01/app/oracle/fast_recovery_area'
*.db_recovery_file_dest_size=4196401152
*.diagnostic_dest='/u01/app/oracle'
*.log_archive_format='%t_%s_%r.arc'
*.memory_target=400M
*.open_cursors=300
*.processes=150
*.remote_login_passwordfile='EXCLUSIVE'
*.undo_tablespace='UNDOTBS1'
[oracle@vmxdb01 ~]$
[oracle@vmxdb01 ~]$ orapwd file=$ORACLE_HOME/dbs/orapwprorcl password=ora123 ignorecase=n force=y
[oracle@vmxdb01 ~]$ mkdir -p /u01/app/oracle/admin/prorcl/adump
[oracle@vmxdb01 ~]$ mkdir -p /u01/app/oracle/oradata/prorcl
[oracle@vmxdb01 ~]$
[oracle@vmxdb01 ~]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.2.0 Production on Thu May 26 21:32:06 2011
Copyright (c) 1982, 2010, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> shutdown immediate
ORA-01507: database not mounted
ORACLE instance shut down.
SQL> startup nomount
ORACLE instance started.
Total System Global Area 418484224 bytes
Fixed Size 1344588 bytes
Variable Size 255855540 bytes
Database Buffers 155189248 bytes
Redo Buffers 6094848 bytes
SQL>
CREATE DATABASE prorcl
USER SYS IDENTIFIED BY ora123
USER SYSTEM IDENTIFIED BY ora123
LOGFILE GROUP 1 ('/u01/app/oracle/oradata/prorcl/redo01.log') SIZE 100M BLOCKSIZE 512,
GROUP 2 ('/u01/app/oracle/oradata/prorcl/redo02.log') SIZE 100M BLOCKSIZE 512,
GROUP 3 ('/u01/app/oracle/oradata/prorcl/redo03.log') SIZE 100M BLOCKSIZE 512
MAXLOGFILES 5
MAXLOGMEMBERS 5
MAXLOGHISTORY 1
MAXDATAFILES 100
CHARACTER SET US7ASCII
NATIONAL CHARACTER SET AL16UTF16
EXTENT MANAGEMENT LOCAL
DATAFILE '/u01/app/oracle/oradata/prorcl/system01.dbf' SIZE 300M AUTOEXTEND ON NEXT 10M
SYSAUX DATAFILE '/u01/app/oracle/oradata/prorcl/sysaux01.dbf' SIZE 300M AUTOEXTEND ON NEXT 10M
DEFAULT TABLESPACE users
DATAFILE '/u01/app/oracle/oradata/prorcl/users01.dbf'
SIZE 500M REUSE AUTOEXTEND ON MAXSIZE UNLIMITED
DEFAULT TEMPORARY TABLESPACE temp
TEMPFILE '/u01/app/oracle/oradata/prorcl/temp01.dbf'
SIZE 20M AUTOEXTEND ON NEXT 10M
UNDO TABLESPACE UNDOTBS1
DATAFILE '/u01/app/oracle/oradata/prorcl/undotbs01.dbf'
SIZE 200M REUSE AUTOEXTEND ON MAXSIZE UNLIMITED;
SQL> @createdb.sql
SQL> CREATE DATABASE prorcl
2 USER SYS IDENTIFIED BY ora123
3 USER SYSTEM IDENTIFIED BY ora123
4 LOGFILE GROUP 1 ('/u01/app/oracle/oradata/prorcl/redo01.log') SIZE 100M BLOCKSIZE 512,
5 GROUP 2 ('/u01/app/oracle/oradata/prorcl/redo02.log') SIZE 100M BLOCKSIZE 512,
6 GROUP 3 ('/u01/app/oracle/oradata/prorcl/redo03.log') SIZE 100M BLOCKSIZE 512
7 MAXLOGFILES 5
8 MAXLOGMEMBERS 5
9 MAXLOGHISTORY 1
10 MAXDATAFILES 100
11 CHARACTER SET US7ASCII
12 NATIONAL CHARACTER SET AL16UTF16
13 EXTENT MANAGEMENT LOCAL
14 DATAFILE '/u01/app/oracle/oradata/prorcl/system01.dbf' SIZE 300M AUTOEXTEND ON NEXT 10M
15 SYSAUX DATAFILE '/u01/app/oracle/oradata/prorcl/sysaux01.dbf' SIZE 300M AUTOEXTEND ON NEXT 10M
16 DEFAULT TABLESPACE users
17 DATAFILE '/u01/app/oracle/oradata/prorcl/users01.dbf'
18 SIZE 500M REUSE AUTOEXTEND ON MAXSIZE UNLIMITED
19 DEFAULT TEMPORARY TABLESPACE temp
20 TEMPFILE '/u01/app/oracle/oradata/prorcl/temp01.dbf'
21 SIZE 20M AUTOEXTEND ON NEXT 10M
22 UNDO TABLESPACE UNDOTBS1
23 DATAFILE '/u01/app/oracle/oradata/prorcl/undotbs01.dbf'
24 SIZE 200M REUSE AUTOEXTEND ON MAXSIZE UNLIMITED;
Database created.
SQL> @?/rdbms/admin/catalog.sql
SQL> @?/rdbms/admin/catproc.sql
SQL> conn system/ora123
SQL> @?/sqlplus/admin/pupbld.sql
SQL> EXIT
SQL> select COMP_NAME,VERSION,STATUS from dba_registry;
COMP_NAME
--------------------------------------------------------------------------------
VERSION STATUS
------------------------------ -----------
Oracle Database Catalog Views
11.2.0.2.0 VALID
Oracle Database Packages and Types
11.2.0.2.0 VALID
SQL> conn / as sysdba
Connected.
SQL> @?/rdbms/admin/catbundle.sql psu apply
[oracle@vmxdb01 ~]$ cat /u01/app/oracle/product/11.2.0.2/db_1/dbs/initprorcl.ora
*.audit_file_dest='/u01/app/oracle/admin/prorcl/adump'
*.audit_trail='db'
*.compatible='11.2.0.0.0'
*.control_files='/u01/app/oracle/oradata/prorcl/control01.ctl','/u01/app/oracle/oradata/prorcl/control02.ctl','/u01/app/oracle/oradata/prorcl/control03.ctl'
*.db_block_size=8192
*.db_domain=''
*.db_name='prorcl'
*.db_recovery_file_dest='/u01/app/oracle/fast_recovery_area'
*.db_recovery_file_dest_size=4196401152
*.diagnostic_dest='/u01/app/oracle'
*.log_archive_format='%t_%s_%r.arc'
*.memory_target=400M
*.open_cursors=300
*.processes=150
*.remote_login_passwordfile='EXCLUSIVE'
*.undo_tablespace='UNDOTBS1'
[oracle@vmxdb01 ~]$
[oracle@vmxdb01 ~]$ orapwd file=$ORACLE_HOME/dbs/orapwprorcl password=ora123 ignorecase=n force=y
[oracle@vmxdb01 ~]$ mkdir -p /u01/app/oracle/admin/prorcl/adump
[oracle@vmxdb01 ~]$ mkdir -p /u01/app/oracle/oradata/prorcl
[oracle@vmxdb01 ~]$
[oracle@vmxdb01 ~]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.2.0 Production on Thu May 26 21:32:06 2011
Copyright (c) 1982, 2010, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> shutdown immediate
ORA-01507: database not mounted
ORACLE instance shut down.
SQL> startup nomount
ORACLE instance started.
Total System Global Area 418484224 bytes
Fixed Size 1344588 bytes
Variable Size 255855540 bytes
Database Buffers 155189248 bytes
Redo Buffers 6094848 bytes
SQL>
CREATE DATABASE prorcl
USER SYS IDENTIFIED BY ora123
USER SYSTEM IDENTIFIED BY ora123
LOGFILE GROUP 1 ('/u01/app/oracle/oradata/prorcl/redo01.log') SIZE 100M BLOCKSIZE 512,
GROUP 2 ('/u01/app/oracle/oradata/prorcl/redo02.log') SIZE 100M BLOCKSIZE 512,
GROUP 3 ('/u01/app/oracle/oradata/prorcl/redo03.log') SIZE 100M BLOCKSIZE 512
MAXLOGFILES 5
MAXLOGMEMBERS 5
MAXLOGHISTORY 1
MAXDATAFILES 100
CHARACTER SET US7ASCII
NATIONAL CHARACTER SET AL16UTF16
EXTENT MANAGEMENT LOCAL
DATAFILE '/u01/app/oracle/oradata/prorcl/system01.dbf' SIZE 300M AUTOEXTEND ON NEXT 10M
SYSAUX DATAFILE '/u01/app/oracle/oradata/prorcl/sysaux01.dbf' SIZE 300M AUTOEXTEND ON NEXT 10M
DEFAULT TABLESPACE users
DATAFILE '/u01/app/oracle/oradata/prorcl/users01.dbf'
SIZE 500M REUSE AUTOEXTEND ON MAXSIZE UNLIMITED
DEFAULT TEMPORARY TABLESPACE temp
TEMPFILE '/u01/app/oracle/oradata/prorcl/temp01.dbf'
SIZE 20M AUTOEXTEND ON NEXT 10M
UNDO TABLESPACE UNDOTBS1
DATAFILE '/u01/app/oracle/oradata/prorcl/undotbs01.dbf'
SIZE 200M REUSE AUTOEXTEND ON MAXSIZE UNLIMITED;
SQL> @createdb.sql
SQL> CREATE DATABASE prorcl
2 USER SYS IDENTIFIED BY ora123
3 USER SYSTEM IDENTIFIED BY ora123
4 LOGFILE GROUP 1 ('/u01/app/oracle/oradata/prorcl/redo01.log') SIZE 100M BLOCKSIZE 512,
5 GROUP 2 ('/u01/app/oracle/oradata/prorcl/redo02.log') SIZE 100M BLOCKSIZE 512,
6 GROUP 3 ('/u01/app/oracle/oradata/prorcl/redo03.log') SIZE 100M BLOCKSIZE 512
7 MAXLOGFILES 5
8 MAXLOGMEMBERS 5
9 MAXLOGHISTORY 1
10 MAXDATAFILES 100
11 CHARACTER SET US7ASCII
12 NATIONAL CHARACTER SET AL16UTF16
13 EXTENT MANAGEMENT LOCAL
14 DATAFILE '/u01/app/oracle/oradata/prorcl/system01.dbf' SIZE 300M AUTOEXTEND ON NEXT 10M
15 SYSAUX DATAFILE '/u01/app/oracle/oradata/prorcl/sysaux01.dbf' SIZE 300M AUTOEXTEND ON NEXT 10M
16 DEFAULT TABLESPACE users
17 DATAFILE '/u01/app/oracle/oradata/prorcl/users01.dbf'
18 SIZE 500M REUSE AUTOEXTEND ON MAXSIZE UNLIMITED
19 DEFAULT TEMPORARY TABLESPACE temp
20 TEMPFILE '/u01/app/oracle/oradata/prorcl/temp01.dbf'
21 SIZE 20M AUTOEXTEND ON NEXT 10M
22 UNDO TABLESPACE UNDOTBS1
23 DATAFILE '/u01/app/oracle/oradata/prorcl/undotbs01.dbf'
24 SIZE 200M REUSE AUTOEXTEND ON MAXSIZE UNLIMITED;
Database created.
SQL> @?/rdbms/admin/catalog.sql
SQL> @?/rdbms/admin/catproc.sql
SQL> conn system/ora123
SQL> @?/sqlplus/admin/pupbld.sql
SQL> EXIT
SQL> select COMP_NAME,VERSION,STATUS from dba_registry;
COMP_NAME
--------------------------------------------------------------------------------
VERSION STATUS
------------------------------ -----------
Oracle Database Catalog Views
11.2.0.2.0 VALID
Oracle Database Packages and Types
11.2.0.2.0 VALID
SQL> conn / as sysdba
Connected.
SQL> @?/rdbms/admin/catbundle.sql psu apply
Manual create database in 11gR2 (11.2.0.2)
export ORACLE_SID=prorcl
[oracle@vmxdb01 ~]$ cat /u01/app/oracle/product/11.2.0.2/db_1/dbs/initprorcl.ora
*.audit_file_dest='/u01/app/oracle/admin/prorcl/adump'
*.audit_trail='db'
*.compatible='11.2.0.0.0'
*.control_files='/u01/app/oracle/oradata/prorcl/control01.ctl','/u01/app/oracle/oradata/prorcl/control02.ctl','/u01/app/oracle/oradata/prorcl/control03.ctl'
*.db_block_size=8192
*.db_domain=''
*.db_name='prorcl'
*.db_recovery_file_dest='/u01/app/oracle/fast_recovery_area'
*.db_recovery_file_dest_size=4196401152
*.diagnostic_dest='/u01/app/oracle'
*.log_archive_format='%t_%s_%r.arc'
*.memory_target=400M
*.open_cursors=300
*.processes=150
*.remote_login_passwordfile='EXCLUSIVE'
*.undo_tablespace='UNDOTBS1'
[oracle@vmxdb01 ~]$
[oracle@vmxdb01 ~]$ orapwd file=$ORACLE_HOME/dbs/orapwprorcl password=ora123 ignorecase=n force=y
[oracle@vmxdb01 ~]$ mkdir -p /u01/app/oracle/admin/prorcl/adump
[oracle@vmxdb01 ~]$ mkdir -p /u01/app/oracle/oradata/prorcl
[oracle@vmxdb01 ~]$
[oracle@vmxdb01 ~]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.2.0 Production on Thu May 26 21:32:06 2011
Copyright (c) 1982, 2010, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> shutdown immediate
ORA-01507: database not mounted
ORACLE instance shut down.
SQL> startup nomount
ORACLE instance started.
Total System Global Area 418484224 bytes
Fixed Size 1344588 bytes
Variable Size 255855540 bytes
Database Buffers 155189248 bytes
Redo Buffers 6094848 bytes
SQL>
CREATE DATABASE prorcl
USER SYS IDENTIFIED BY ora123
USER SYSTEM IDENTIFIED BY ora123
LOGFILE GROUP 1 ('/u01/app/oracle/oradata/prorcl/redo01.log') SIZE 100M BLOCKSIZE 512,
GROUP 2 ('/u01/app/oracle/oradata/prorcl/redo02.log') SIZE 100M BLOCKSIZE 512,
GROUP 3 ('/u01/app/oracle/oradata/prorcl/redo03.log') SIZE 100M BLOCKSIZE 512
MAXLOGFILES 5
MAXLOGMEMBERS 5
MAXLOGHISTORY 1
MAXDATAFILES 100
CHARACTER SET US7ASCII
NATIONAL CHARACTER SET AL16UTF16
EXTENT MANAGEMENT LOCAL
DATAFILE '/u01/app/oracle/oradata/prorcl/system01.dbf' SIZE 300M AUTOEXTEND ON NEXT 10M
SYSAUX DATAFILE '/u01/app/oracle/oradata/prorcl/sysaux01.dbf' SIZE 300M AUTOEXTEND ON NEXT 10M
DEFAULT TABLESPACE users
DATAFILE '/u01/app/oracle/oradata/prorcl/users01.dbf'
SIZE 500M REUSE AUTOEXTEND ON MAXSIZE UNLIMITED
DEFAULT TEMPORARY TABLESPACE temp
TEMPFILE '/u01/app/oracle/oradata/prorcl/temp01.dbf'
SIZE 20M AUTOEXTEND ON NEXT 10M
UNDO TABLESPACE UNDOTBS1
DATAFILE '/u01/app/oracle/oradata/prorcl/undotbs01.dbf'
SIZE 200M REUSE AUTOEXTEND ON MAXSIZE UNLIMITED;
SQL> @createdb.sql
SQL> CREATE DATABASE prorcl
2 USER SYS IDENTIFIED BY ora123
3 USER SYSTEM IDENTIFIED BY ora123
4 LOGFILE GROUP 1 ('/u01/app/oracle/oradata/prorcl/redo01.log') SIZE 100M BLOCKSIZE 512,
5 GROUP 2 ('/u01/app/oracle/oradata/prorcl/redo02.log') SIZE 100M BLOCKSIZE 512,
6 GROUP 3 ('/u01/app/oracle/oradata/prorcl/redo03.log') SIZE 100M BLOCKSIZE 512
7 MAXLOGFILES 5
8 MAXLOGMEMBERS 5
9 MAXLOGHISTORY 1
10 MAXDATAFILES 100
11 CHARACTER SET US7ASCII
12 NATIONAL CHARACTER SET AL16UTF16
13 EXTENT MANAGEMENT LOCAL
14 DATAFILE '/u01/app/oracle/oradata/prorcl/system01.dbf' SIZE 300M AUTOEXTEND ON NEXT 10M
15 SYSAUX DATAFILE '/u01/app/oracle/oradata/prorcl/sysaux01.dbf' SIZE 300M AUTOEXTEND ON NEXT 10M
16 DEFAULT TABLESPACE users
17 DATAFILE '/u01/app/oracle/oradata/prorcl/users01.dbf'
18 SIZE 500M REUSE AUTOEXTEND ON MAXSIZE UNLIMITED
19 DEFAULT TEMPORARY TABLESPACE temp
20 TEMPFILE '/u01/app/oracle/oradata/prorcl/temp01.dbf'
21 SIZE 20M AUTOEXTEND ON NEXT 10M
22 UNDO TABLESPACE UNDOTBS1
23 DATAFILE '/u01/app/oracle/oradata/prorcl/undotbs01.dbf'
24 SIZE 200M REUSE AUTOEXTEND ON MAXSIZE UNLIMITED;
Database created.
SQL> @?/rdbms/admin/catalog.sql
SQL> @?/rdbms/admin/catproc.sql
SQL> conn system/ora123
SQL> @?/sqlplus/admin/pupbld.sql
SQL> EXIT
SQL> select COMP_NAME,VERSION,STATUS from dba_registry;
COMP_NAME
--------------------------------------------------------------------------------
VERSION STATUS
------------------------------ -----------
Oracle Database Catalog Views
11.2.0.2.0 VALID
Oracle Database Packages and Types
11.2.0.2.0 VALID
SQL> conn / as sysdba
Connected.
SQL> @?/rdbms/admin/catbundle.sql psu apply
[oracle@vmxdb01 ~]$ cat /u01/app/oracle/product/11.2.0.2/db_1/dbs/initprorcl.ora
*.audit_file_dest='/u01/app/oracle/admin/prorcl/adump'
*.audit_trail='db'
*.compatible='11.2.0.0.0'
*.control_files='/u01/app/oracle/oradata/prorcl/control01.ctl','/u01/app/oracle/oradata/prorcl/control02.ctl','/u01/app/oracle/oradata/prorcl/control03.ctl'
*.db_block_size=8192
*.db_domain=''
*.db_name='prorcl'
*.db_recovery_file_dest='/u01/app/oracle/fast_recovery_area'
*.db_recovery_file_dest_size=4196401152
*.diagnostic_dest='/u01/app/oracle'
*.log_archive_format='%t_%s_%r.arc'
*.memory_target=400M
*.open_cursors=300
*.processes=150
*.remote_login_passwordfile='EXCLUSIVE'
*.undo_tablespace='UNDOTBS1'
[oracle@vmxdb01 ~]$
[oracle@vmxdb01 ~]$ orapwd file=$ORACLE_HOME/dbs/orapwprorcl password=ora123 ignorecase=n force=y
[oracle@vmxdb01 ~]$ mkdir -p /u01/app/oracle/admin/prorcl/adump
[oracle@vmxdb01 ~]$ mkdir -p /u01/app/oracle/oradata/prorcl
[oracle@vmxdb01 ~]$
[oracle@vmxdb01 ~]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.2.0 Production on Thu May 26 21:32:06 2011
Copyright (c) 1982, 2010, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> shutdown immediate
ORA-01507: database not mounted
ORACLE instance shut down.
SQL> startup nomount
ORACLE instance started.
Total System Global Area 418484224 bytes
Fixed Size 1344588 bytes
Variable Size 255855540 bytes
Database Buffers 155189248 bytes
Redo Buffers 6094848 bytes
SQL>
CREATE DATABASE prorcl
USER SYS IDENTIFIED BY ora123
USER SYSTEM IDENTIFIED BY ora123
LOGFILE GROUP 1 ('/u01/app/oracle/oradata/prorcl/redo01.log') SIZE 100M BLOCKSIZE 512,
GROUP 2 ('/u01/app/oracle/oradata/prorcl/redo02.log') SIZE 100M BLOCKSIZE 512,
GROUP 3 ('/u01/app/oracle/oradata/prorcl/redo03.log') SIZE 100M BLOCKSIZE 512
MAXLOGFILES 5
MAXLOGMEMBERS 5
MAXLOGHISTORY 1
MAXDATAFILES 100
CHARACTER SET US7ASCII
NATIONAL CHARACTER SET AL16UTF16
EXTENT MANAGEMENT LOCAL
DATAFILE '/u01/app/oracle/oradata/prorcl/system01.dbf' SIZE 300M AUTOEXTEND ON NEXT 10M
SYSAUX DATAFILE '/u01/app/oracle/oradata/prorcl/sysaux01.dbf' SIZE 300M AUTOEXTEND ON NEXT 10M
DEFAULT TABLESPACE users
DATAFILE '/u01/app/oracle/oradata/prorcl/users01.dbf'
SIZE 500M REUSE AUTOEXTEND ON MAXSIZE UNLIMITED
DEFAULT TEMPORARY TABLESPACE temp
TEMPFILE '/u01/app/oracle/oradata/prorcl/temp01.dbf'
SIZE 20M AUTOEXTEND ON NEXT 10M
UNDO TABLESPACE UNDOTBS1
DATAFILE '/u01/app/oracle/oradata/prorcl/undotbs01.dbf'
SIZE 200M REUSE AUTOEXTEND ON MAXSIZE UNLIMITED;
SQL> @createdb.sql
SQL> CREATE DATABASE prorcl
2 USER SYS IDENTIFIED BY ora123
3 USER SYSTEM IDENTIFIED BY ora123
4 LOGFILE GROUP 1 ('/u01/app/oracle/oradata/prorcl/redo01.log') SIZE 100M BLOCKSIZE 512,
5 GROUP 2 ('/u01/app/oracle/oradata/prorcl/redo02.log') SIZE 100M BLOCKSIZE 512,
6 GROUP 3 ('/u01/app/oracle/oradata/prorcl/redo03.log') SIZE 100M BLOCKSIZE 512
7 MAXLOGFILES 5
8 MAXLOGMEMBERS 5
9 MAXLOGHISTORY 1
10 MAXDATAFILES 100
11 CHARACTER SET US7ASCII
12 NATIONAL CHARACTER SET AL16UTF16
13 EXTENT MANAGEMENT LOCAL
14 DATAFILE '/u01/app/oracle/oradata/prorcl/system01.dbf' SIZE 300M AUTOEXTEND ON NEXT 10M
15 SYSAUX DATAFILE '/u01/app/oracle/oradata/prorcl/sysaux01.dbf' SIZE 300M AUTOEXTEND ON NEXT 10M
16 DEFAULT TABLESPACE users
17 DATAFILE '/u01/app/oracle/oradata/prorcl/users01.dbf'
18 SIZE 500M REUSE AUTOEXTEND ON MAXSIZE UNLIMITED
19 DEFAULT TEMPORARY TABLESPACE temp
20 TEMPFILE '/u01/app/oracle/oradata/prorcl/temp01.dbf'
21 SIZE 20M AUTOEXTEND ON NEXT 10M
22 UNDO TABLESPACE UNDOTBS1
23 DATAFILE '/u01/app/oracle/oradata/prorcl/undotbs01.dbf'
24 SIZE 200M REUSE AUTOEXTEND ON MAXSIZE UNLIMITED;
Database created.
SQL> @?/rdbms/admin/catalog.sql
SQL> @?/rdbms/admin/catproc.sql
SQL> conn system/ora123
SQL> @?/sqlplus/admin/pupbld.sql
SQL> EXIT
SQL> select COMP_NAME,VERSION,STATUS from dba_registry;
COMP_NAME
--------------------------------------------------------------------------------
VERSION STATUS
------------------------------ -----------
Oracle Database Catalog Views
11.2.0.2.0 VALID
Oracle Database Packages and Types
11.2.0.2.0 VALID
SQL> conn / as sysdba
Connected.
SQL> @?/rdbms/admin/catbundle.sql psu apply
How do I change (rotate, re-key) the encryption keys?
How do I change (rotate, re-key) the encryption keys?
Table C: Encryption re-key capabilities (Oracle Wallet or HSM) Database Release TDE Column Encryption TDE Tablespace Encryption Master encryption key Individual table keys Master encryption key Individual tablespace keys 10gR2 Yes Yes n/a n/a 11gR1 Yes Yes No(*) No(*) 11gR2 Yes Yes Yes No(*) (*): Content can be moved from one encrypted tablespace to a new encrypted tablespace, where it is encrypted with a new tablespace key.
TDE uses a two tier key mechanism. When TDE column encryption is applied to an existing application table column, a new table key is created and stored in the Oracle data dictionary. When TDE tablespace encryption is used, the individual tablespace keys are stored in the header of the underlying OS file(s). The table and tablespace keys are encrypted using the TDE master encryption key. The master encryption key is generated when TDE is initialized and stored outside the database in the Oracle Wallet or an HSM device (starting with Oracle 11gR1). Both the master key and table keys can be independently changed (rotated, re-keyed) based on company security policies. Tablespace keys cannot be re-keyed (rotated); work around is to move the data into a new encrypted tablespace. Oracle recommends backing up the wallet before and after each master key change.
Changing the wallet password does not re-key the TDE master encryption key.
[oracle@vmxdb01 ~]$
[oracle@vmxdb01 ~]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.2.0 Production on Thu May 26 08:18:08 2011
Copyright (c) 1982, 2010, Oracle. All rights reserved.
Connected to an idle instance.
SQL> startup
ORACLE instance started.
Total System Global Area 627732480 bytes
Fixed Size 1345992 bytes
Variable Size 452986424 bytes
Database Buffers 167772160 bytes
Redo Buffers 5627904 bytes
Database mounted.
Database opened.
SQL> select * from v$encryption_wallet;
WRL_TYPE
--------------------
WRL_PARAMETER
--------------------------------------------------------------------------------
STATUS
------------------
file
/u01/app/oracle/product/11.2.0.2/db_1/wallet
OPEN
SQL> select * from v$encrypted_tablespaces;
TS# ENCRYPT ENC
---------- ------- ---
ENCRYTPEDKEY
----------------------------------------------------------------
MASTERKEYID BLOCKS_ENCRYPTED BLOCKS_DECRYPTED
-------------------------------- ---------------- ----------------
8 AES256 YES
7CC0E110A3368FC76D4F80F5D3036E61C4CC16244E5B883E4AA167664D08C29C
0CBD900BD9E24FE7BFA2A120F1353E0A 0 0
SQL> alter system set encryption key identified by Never4get;
alter system set encryption key identified by Never4get
*
ERROR at line 1:
ORA-28353: failed to open wallet
SQL> alter system set encryption key identified by "Never4get";
System altered.
SQL> select * from v$encrypted_tablespaces;
TS# ENCRYPT ENC
---------- ------- ---
ENCRYTPEDKEY
----------------------------------------------------------------
MASTERKEYID BLOCKS_ENCRYPTED BLOCKS_DECRYPTED
-------------------------------- ---------------- ----------------
8 AES256 YES
305CE8652C4DC4FEC92A8C1C28E1F4925E40330B76C9A7E0F208CCEBA2256CD4
2270620BA7814F3FBFD0C31C42BABF03 0 0
SQL> select count(*) from e;
COUNT(*)
----------
107
Wednesday, May 25, 2011
Change wallet password using orapki
This task can be performed through OWM (Oracle Wallet Manager)
[oracle@vmxdb01 wallet]$ ls -ltr
total 12
-rw-------. 1 oracle oinstall 1838 May 25 20:21 ewallet.p12
-rw-------. 1 oracle oinstall 1915 May 25 20:21 cwallet.sso
-rw-r--r--. 1 oracle oinstall 9 May 25 20:22 password.txt
[oracle@vmxdb01 wallet]$ orapki wallet change_pwd -wallet $ORACLE_HOME/wallet
Oracle PKI Tool : Version 11.2.0.2.0 - Production
Copyright (c) 2004, 2010, Oracle and/or its affiliates. All rights reserved.
Enter wallet password: Easy2rem
New password:
Enter wallet password: Never4get
[oracle@vmxdb01 wallet]$
[oracle@vmxdb01 wallet]$ ls -ltr
total 12
-rw-r--r--. 1 oracle oinstall 9 May 25 20:22 password.txt
-rw-------. 1 oracle oinstall 1838 May 25 20:25 ewallet.p12
-rw-------. 1 oracle oinstall 1915 May 25 20:25 cwallet.sso
[oracle@vmxdb01 wallet]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.2.0 Production on Wed May 25 20:26:12 2011
Copyright (c) 1982, 2010, Oracle. All rights reserved.
Connected to an idle instance.
SQL> startup
ORACLE instance started.
Total System Global Area 627732480 bytes
Fixed Size 1345992 bytes
Variable Size 452986424 bytes
Database Buffers 167772160 bytes
Redo Buffers 5627904 bytes
Database mounted.
Database opened.
SQL> select count(*) from e;
COUNT(*)
----------
107
SQL> exit
Sunday, May 22, 2011
Background Processes in 11gR2 RAC
SQL> select instance_name from v$instance;
INSTANCE_NAME
----------------
+ASM1
SQL> select inst_id,name,description from gv$bgprocess where paddr<>'00' order by inst_id, name;
INST_ID NAME DESCRIPTION
---------- ----- ----------------------------------------------------------------
1 ASMB ASM Background
1 CKPT checkpoint
1 DBW0 db writer process 0
1 DIA0 diagnosibility process 0
1 DIAG diagnosibility process
1 GEN0 generic0
1 GMON diskgroup monitor
1 LCK0 Lock Process 0
1 LGWR Redo etc.
1 LMD0 global enqueue service daemon 0
1 LMHB lm heartbeat monitor
1 LMON global enqueue service monitor
1 LMS0 global cache service process 0
1 MMAN Memory Manager
1 MMNL Manageability Monitor Process 2
1 MMON Manageability Monitor Process
1 PING interconnect latency measurement
1 PMON process cleanup
1 PSP0 process spawner 0
1 RBAL ASM Rebalance master
1 SMON System Monitor Process
1 VKTM Virtual Keeper of TiMe process
3 ASMB ASM Background
3 CKPT checkpoint
3 DBW0 db writer process 0
3 DIA0 diagnosibility process 0
3 DIAG diagnosibility process
3 GEN0 generic0
3 GMON diskgroup monitor
3 LCK0 Lock Process 0
3 LGWR Redo etc.
3 LMD0 global enqueue service daemon 0
3 LMHB lm heartbeat monitor
3 LMON global enqueue service monitor
3 LMS0 global cache service process 0
3 MMAN Memory Manager
3 MMNL Manageability Monitor Process 2
3 MMON Manageability Monitor Process
3 PING interconnect latency measurement
3 PMON process cleanup
3 PSP0 process spawner 0
3 RBAL ASM Rebalance master
3 SMON System Monitor Process
3 VKTM Virtual Keeper of TiMe process
44 rows selected.
SQL> select instance_name from v$instance;
INSTANCE_NAME
----------------
orcl1
SQL> select inst_id,name,description from gv$bgprocess where paddr<>'00' order by inst_id, name
2 /
INST_ID NAME DESCRIPTION
---------- ----- ----------------------------------------------------------------
1 ACMS Atomic Controlfile to Memory Server
1 ARC0 Archival Process 0
1 ARC1 Archival Process 1
1 ARC2 Archival Process 2
1 ARC3 Archival Process 3
1 ASMB ASM Background
1 CJQ0 Job Queue Coordinator
1 CKPT checkpoint
1 DBRM DataBase Resource Manager
1 DBW0 db writer process 0
1 DIA0 diagnosibility process 0
1 DIAG diagnosibility process
1 GEN0 generic0
1 GTX0 Global Txn process 0
1 LCK0 Lock Process 0
1 LGWR Redo etc.
1 LMD0 global enqueue service daemon 0
1 LMHB lm heartbeat monitor
1 LMON global enqueue service monitor
1 LMS0 global cache service process 0
1 MARK mark AU for resync koordinator
1 MMAN Memory Manager
1 MMNL Manageability Monitor Process 2
1 MMON Manageability Monitor Process
1 PING interconnect latency measurement
1 PMON process cleanup
1 PSP0 process spawner 0
1 QMNC AQ Coordinator
1 RBAL ASM Rebalance master
1 RCBG Result Cache: Background
1 RECO distributed recovery
1 RMS0 rac management server
1 RSMN Remote Slave Monitor
1 SMCO Space Manager Process
1 SMON System Monitor Process
1 VKRM Virtual sKeduler for Resource Manager
1 VKTM Virtual Keeper of TiMe process
3 ACMS Atomic Controlfile to Memory Server
3 ARC0 Archival Process 0
3 ARC1 Archival Process 1
3 ARC2 Archival Process 2
3 ARC3 Archival Process 3
3 ASMB ASM Background
3 CJQ0 Job Queue Coordinator
3 CKPT checkpoint
3 DBRM DataBase Resource Manager
3 DBW0 db writer process 0
3 DIA0 diagnosibility process 0
3 DIAG diagnosibility process
3 GEN0 generic0
3 GTX0 Global Txn process 0
3 LCK0 Lock Process 0
3 LGWR Redo etc.
3 LMD0 global enqueue service daemon 0
3 LMHB lm heartbeat monitor
3 LMON global enqueue service monitor
3 LMS0 global cache service process 0
3 MARK mark AU for resync koordinator
3 MMAN Memory Manager
3 MMNL Manageability Monitor Process 2
3 MMON Manageability Monitor Process
3 PING interconnect latency measurement
3 PMON process cleanup
3 PSP0 process spawner 0
3 QMNC AQ Coordinator
3 RBAL ASM Rebalance master
3 RCBG Result Cache: Background
3 RECO distributed recovery
3 RMS0 rac management server
3 RSMN Remote Slave Monitor
3 SMCO Space Manager Process
3 SMON System Monitor Process
3 VKRM Virtual sKeduler for Resource Manager
3 VKTM Virtual Keeper of TiMe process
74 rows selected.
Find out MVIEWs stopping MVIEW LOG being purged
RUN as DBA User
RUN as SOURCE_USER
RUN as TARGET_USER
donghua@rh6:~$ sqlplus donghua/donghua@orcl
SQL*Plus: Release 11.2.0.2.0 Production on Sun May 22 11:27:15 2011
Copyright (c) 1982, 2010, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> create user source_user identified by source_user;
User created.
SQL> grant create session,create table to source_user;
Grant succeeded.
SQL> alter user source_user quota unlimited on users;
User altered.
SQL> create user target_user identified by target_user;
User created.
SQL> grant create session, create table, create materialized view to target_user;
Grant succeeded.
SQL> alter user target_user quota unlimited on users;
User altered.
SQL> grant create database link to target_user;
Grant succeeded.
SQL> create user snapshot_user identified by snapshot_user;
User created.
SQL> grant create session to snapshot_user;
Grant succeeded.
SQL> conn source_user/source_user@orcl
Connected.
SQL> create table tbl_large_table
2 (id number,
3 name char(100));
Table created.
SQL> alter table tbl_large_table
2 add constraint pk_tbl_large_table
3 primary key (id);
Table altered.
SQL> create materialized view log on tbl_large_table
2 tablespace users
3 with rowid, primary key
4 including new values;
Materialized view log created.
SQL> select * from tab;
TNAME TABTYPE CLUSTERID
------------------------------ ------- ----------
MLOG$_TBL_LARGE_TABLE TABLE
RUPD$_TBL_LARGE_TABLE TABLE
TBL_LARGE_TABLE TABLE
SQL> grant select on tbl_large_table to snapshot_user;
Grant succeeded.
SQL> grant select on mlog$_tbl_large_table to snapshot_user;
Grant succeeded.
SQL> grant select on rupd$_tbl_large_table to snapshot_user;
Grant succeeded.
SQL> desc user_registered_mviews;
Name Null? Type
----------------------------------------- -------- ----------------------------
OWNER NOT NULL VARCHAR2(30)
NAME NOT NULL VARCHAR2(30)
MVIEW_SITE NOT NULL VARCHAR2(128)
CAN_USE_LOG VARCHAR2(3)
UPDATABLE VARCHAR2(3)
REFRESH_METHOD VARCHAR2(11)
MVIEW_ID NUMBER(38)
VERSION VARCHAR2(26)
QUERY_TXT LONG
SQL> select count(*) from user_registered_mviews;
COUNT(*)
----------
0
SQL> set pages 999
SQL> select * from user_mview_logs;
LOG_OWNER MASTER
------------------------------ ------------------------------
LOG_TABLE LOG_TRIGGER ROW PRI OBJ FIL
------------------------------ ------------------------------ --- --- --- ---
SEQ INC PUR PUR PURGE_STA
--- --- --- --- ---------
PURGE_INTERVAL
--------------------------------------------------------------------------------
LAST_PURG LAST_PURGE_STATUS NUM_ROWS_PURGED COM
--------- ----------------- --------------- ---
SOURCE_USER TBL_LARGE_TABLE
MLOG$_TBL_LARGE_TABLE YES YES NO NO
NO YES NO NO
NO
donghua@rh6:~$ sqlplus target_user/target_user@orcl
SQL*Plus: Release 11.2.0.2.0 Production on Sun May 22 12:05:26 2011
Copyright (c) 1982, 2010, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> create database link orcl.local connect to snapshot_user identified by snapshot_user using 'orcl';
Database link created.
SQL> select systimestamp from dual@orcl.local;
SYSTIMESTAMP
---------------------------------------------------------------------------
22-MAY-11 12.11.15.187693 PM +08:00
SQL>
SQL> create materialized view tbl_large_table_sum
2 refresh fast on demand
3 as
4 select sum(id) from source_user.tbl_large_table@orcl.local;
Materialized view created.
SQL> select * from user_registered_mviews;
OWNER NAME
------------------------------ ------------------------------
MVIEW_SITE
--------------------------------------------------------------------------------
CAN UPD REFRESH_MET MVIEW_ID VERSION
--- --- ----------- ---------- --------------------------
QUERY_TXT
--------------------------------------------------------------------------------
TARGET_USER TBL_LARGE_TABLE_SUM
ORCL
YES NO ROWID 23 ORACLE 8 MATERIALIZED VIEW
select sum(id) from source_user.tbl_large_table@orcl.local
SQL> create materialized view tbl_large_table
2 refresh fast on demand
3 as
4 select * from source_user.tbl_large_table@orcl.local;
Materialized view created.
SQL> select * from user_registered_mviews;
OWNER NAME
------------------------------ ------------------------------
MVIEW_SITE
--------------------------------------------------------------------------------
CAN UPD REFRESH_MET MVIEW_ID VERSION
--- --- ----------- ---------- --------------------------
QUERY_TXT
--------------------------------------------------------------------------------
TARGET_USER TBL_LARGE_TABLE
ORCL
YES NO PRIMARY KEY 24 ORACLE 8 MATERIALIZED VIEW
SELECT "TBL_LARGE_TABLE"."ID" "ID","TBL_LARGE_TABLE"."NAME" "NAME" FROM "SOURCE_
TARGET_USER TBL_LARGE_TABLE_SUM
ORCL
YES NO ROWID 23 ORACLE 8 MATERIALIZED VIEW
select sum(id) from source_user.tbl_large_table@orcl.local
SQL> exec dbms_mview.refresh('tbl_large_table');
PL/SQL procedure successfully completed.
SQL> select * from tbl_large_table;
no rows selected
SQL> exec dbms_mview.refresh('tbl_large_table_sum');
PL/SQL procedure successfully completed.
SQL> select * from tbl_large_table_sum;
SUM(ID)
----------
SQL> select * from user_mview_refresh_times;
OWNER NAME
------------------------------ ------------------------------
MASTER_OWNER MASTER LAST_REFRESH
------------------------------ ------------------------------ ------------------
TARGET_USER TBL_LARGE_TABLE_SUM
SOURCE_USER TBL_LARGE_TABLE 22-MAY-11 12:30:18
TARGET_USER TBL_LARGE_TABLE
SOURCE_USER TBL_LARGE_TABLE 22-MAY-11 12:29:59
SQL> desc user_base_table_mviews
Name Null? Type
----------------------------------------- -------- ----------------------------
OWNER NOT NULL VARCHAR2(30)
MASTER NOT NULL VARCHAR2(30)
MVIEW_LAST_REFRESH_TIME NOT NULL DATE
MVIEW_ID NUMBER(38)
SQL> select * from user_base_table_mviews;
OWNER MASTER MVIEW_LAST_REFRESH
------------------------------ ------------------------------ ------------------
MVIEW_ID
----------
SOURCE_USER TBL_LARGE_TABLE 22-MAY-11 12:30:18
23
SOURCE_USER TBL_LARGE_TABLE 22-MAY-11 12:29:59
24
SQL> desc user_mview_logs
Name Null? Type
----------------------------------------- -------- ----------------------------
LOG_OWNER VARCHAR2(30)
MASTER VARCHAR2(30)
LOG_TABLE VARCHAR2(30)
LOG_TRIGGER VARCHAR2(30)
ROWIDS VARCHAR2(3)
PRIMARY_KEY VARCHAR2(3)
OBJECT_ID VARCHAR2(3)
FILTER_COLUMNS VARCHAR2(3)
SEQUENCE VARCHAR2(3)
INCLUDE_NEW_VALUES VARCHAR2(3)
PURGE_ASYNCHRONOUS VARCHAR2(3)
PURGE_DEFERRED VARCHAR2(3)
PURGE_START DATE
PURGE_INTERVAL VARCHAR2(200)
LAST_PURGE_DATE DATE
LAST_PURGE_STATUS NUMBER
NUM_ROWS_PURGED NUMBER
COMMIT_SCN_BASED VARCHAR2(3)
SQL> select * from user_mview_logs;
LOG_OWNER MASTER
------------------------------ ------------------------------
LOG_TABLE LOG_TRIGGER ROW PRI OBJ FIL
------------------------------ ------------------------------ --- --- --- ---
SEQ INC PUR PUR PURGE_START
--- --- --- --- ------------------
PURGE_INTERVAL
--------------------------------------------------------------------------------
LAST_PURGE_DATE LAST_PURGE_STATUS NUM_ROWS_PURGED COM
------------------ ----------------- --------------- ---
SOURCE_USER TBL_LARGE_TABLE
MLOG$_TBL_LARGE_TABLE YES YES NO NO
NO YES NO NO
22-MAY-11 12:30:18 0 0 NO
-- Generate Data in Source (source_user)
SQL> insert into tbl_large_table
2 select rownum, 'x' from dual connect by rownum < 100000;
99999 rows created.
SQL> commit;
Commit complete.
SQL> delete from tbl_large_table where mod(id,4)=0;
24999 rows deleted.
SQL> commit;
Commit complete.
SQL> select count(*) from tbl_large_table;
COUNT(*)
----------
75000
SQL> select last_purge_date,num_rows_purged from user_mview_logs;
LAST_PURGE_DATE NUM_ROWS_PURGED
------------------ ---------------
22-MAY-11 12:30:18 0
SQL> select mview_id,mview_last_refresh_time from user_base_table_mviews;
MVIEW_ID MVIEW_LAST_REFRESH
---------- ------------------
23 22-MAY-11 12:30:18
24 22-MAY-11 12:29:59
SQL> col segment_name for a30
SQL> select segment_name, blocks from user_segments;
SEGMENT_NAME BLOCKS
------------------------------ ----------
TBL_LARGE_TABLE 1664
MLOG$_TBL_LARGE_TABLE 1024
PK_TBL_LARGE_TABLE 256
SQL> select count(*) from mlog$_tbl_large_table;
COUNT(*)
----------
124998
SQL> exec dbms_mview.refresh('tbl_large_table_sum');
BEGIN dbms_mview.refresh('tbl_large_table_sum'); END;
*
ERROR at line 1:
ORA-32314: REFRESH FAST of "TARGET_USER"."TBL_LARGE_TABLE_SUM" unsupported
after deletes/updates
ORA-06512: at "SYS.DBMS_SNAPSHOT", line 2566
ORA-06512: at "SYS.DBMS_SNAPSHOT", line 2779
ORA-06512: at "SYS.DBMS_SNAPSHOT", line 2748
ORA-06512: at line 1
SQL> exec dbms_mview.refresh('tbl_large_table_sum',method=>'complete');
PL/SQL procedure successfully completed.
SQL> select * from user_mview_refresh_times;
OWNER NAME
------------------------------ ------------------------------
MASTER_OWNER MASTER LAST_REFRESH
------------------------------ ------------------------------ ------------------
TARGET_USER TBL_LARGE_TABLE_SUM
SOURCE_USER TBL_LARGE_TABLE 22-MAY-11 12:49:20
TARGET_USER TBL_LARGE_TABLE
SOURCE_USER TBL_LARGE_TABLE 22-MAY-11 12:29:59
SQL> select mview_id,mview_last_refresh_time from user_base_table_mviews;
MVIEW_ID MVIEW_LAST_REFRESH
---------- ------------------
23 22-MAY-11 12:49:20
24 22-MAY-11 12:29:59
SQL> select last_purge_date,num_rows_purged from user_mview_logs;
LAST_PURGE_DATE NUM_ROWS_PURGED
------------------ ---------------
22-MAY-11 12:49:20 0
SQL> select count(*) from mlog$_tbl_large_table;
COUNT(*)
----------
124998
SQL> select segment_name, blocks from user_segments;
SEGMENT_NAME BLOCKS
------------------------------ ----------
TBL_LARGE_TABLE 1664
MLOG$_TBL_LARGE_TABLE 1024
PK_TBL_LARGE_TABLE 256
SQL> exec dbms_mview.refresh('tbl_large_table');
PL/SQL procedure successfully completed.
SQL> select * from user_mview_refresh_times;
OWNER NAME
------------------------------ ------------------------------
MASTER_OWNER MASTER LAST_REFRESH
------------------------------ ------------------------------ ------------------
TARGET_USER TBL_LARGE_TABLE_SUM
SOURCE_USER TBL_LARGE_TABLE 22-MAY-11 12:49:20
TARGET_USER TBL_LARGE_TABLE
SOURCE_USER TBL_LARGE_TABLE 22-MAY-11 12:53:09
SQL> select mview_id,mview_last_refresh_time from user_base_table_mviews;
MVIEW_ID MVIEW_LAST_REFRESH
---------- ------------------
23 22-MAY-11 12:49:20
24 22-MAY-11 12:53:09
SQL> select last_purge_date,num_rows_purged from user_mview_logs;
LAST_PURGE_DATE NUM_ROWS_PURGED
------------------ ---------------
22-MAY-11 12:53:15 124998
SQL> select count(*) from mlog$_tbl_large_table;
COUNT(*)
----------
0
SQL> select segment_name, blocks from user_segments;
SEGMENT_NAME BLOCKS
------------------------------ ----------
TBL_LARGE_TABLE 1664
MLOG$_TBL_LARGE_TABLE 1024
PK_TBL_LARGE_TABLE 256
SQL> alter table MLOG$_TBL_LARGE_TABLE move;
Table altered.
SQL> select segment_name, blocks from user_segments;
SEGMENT_NAME BLOCKS
------------------------------ ----------
TBL_LARGE_TABLE 1664
MLOG$_TBL_LARGE_TABLE 8
PK_TBL_LARGE_TABLE 256
Wednesday, May 18, 2011
Learn C++ by examples (Pointers)
donghua@rh6:~/tmp$ cat pointer_example.cpp
#include
using namespace std;
int main ()
{
int x; // a normal integer
int *p; // a point to an integer
p = &x; // read it, "assign the address of x to p"
cin>>x; // put a value in x, we could also use *p here
cin.ignore();
cout<< *p <<"\n";// note the use of * to get/dereferencing the value
cin.get();
}
#include
using namespace std;
int main ()
{
int x; // a normal integer
int *p; // a point to an integer
p = &x; // read it, "assign the address of x to p"
cin>>x; // put a value in x, we could also use *p here
cin.ignore();
cout<< *p <<"\n";// note the use of * to get/dereferencing the value
cin.get();
}
Monday, May 16, 2011
Using opatch auto apply PSU in GI envrionment
[grid@vmxdb04 grid]$ crsctl stat res -t
--------------------------------------------------------------------------------
NAME TARGET STATE SERVER STATE_DETAILS
--------------------------------------------------------------------------------
Local Resources
--------------------------------------------------------------------------------
ora.DATA.dg
ONLINE ONLINE vmxdb03
ONLINE ONLINE vmxdb04
ora.FRA.dg
ONLINE ONLINE vmxdb03
ONLINE ONLINE vmxdb04
ora.LISTENER.lsnr
ONLINE ONLINE vmxdb03
ONLINE ONLINE vmxdb04
ora.OCRVOTE.dg
ONLINE ONLINE vmxdb03
ONLINE ONLINE vmxdb04
ora.asm
ONLINE ONLINE vmxdb03 Started
ONLINE ONLINE vmxdb04
ora.gsd
OFFLINE OFFLINE vmxdb03
OFFLINE OFFLINE vmxdb04
ora.net1.network
ONLINE ONLINE vmxdb03
ONLINE ONLINE vmxdb04
ora.ons
ONLINE ONLINE vmxdb03
ONLINE ONLINE vmxdb04
ora.registry.acfs
ONLINE ONLINE vmxdb03
ONLINE ONLINE vmxdb04
--------------------------------------------------------------------------------
Cluster Resources
--------------------------------------------------------------------------------
ora.LISTENER_SCAN1.lsnr
1 ONLINE ONLINE vmxdb04
ora.LISTENER_SCAN2.lsnr
1 ONLINE ONLINE vmxdb03
ora.LISTENER_SCAN3.lsnr
1 ONLINE ONLINE vmxdb03
ora.cvu
1 ONLINE ONLINE vmxdb03
ora.oc4j
1 ONLINE ONLINE vmxdb03
ora.orcl.db
1 ONLINE ONLINE vmxdb03 Open
2 ONLINE ONLINE vmxdb04 Open
ora.scan1.vip
1 ONLINE ONLINE vmxdb04
ora.scan2.vip
1 ONLINE ONLINE vmxdb03
ora.scan3.vip
1 ONLINE ONLINE vmxdb03
ora.vmxdb03.vip
1 ONLINE ONLINE vmxdb03
ora.vmxdb04.vip
1 ONLINE ONLINE vmxdb04
[grid@vmxdb03 grid]$ $ORACLE_HOME/OPatch/ocm/bin/emocmrsp
OCM Installation Response Generator 10.3.4.0.0 - Production
Copyright (c) 2005, 2010, Oracle and/or its affiliates. All rights reserved.
Provide your email address to be informed of security issues, install and
initiate Oracle Configuration Manager. Easier for you if you use your My
Oracle Support Email address/User Name.
Visit http://www.oracle.com/support/policies.html for details.
Email address/User Name:
You have not provided an email address for notification of security issues.
Do you wish to remain uninformed of security issues ([Y]es, [N]o) [N]: y
The OCM configuration response file (ocm.rsp) was successfully created.
[root@vmxdb03 patch]# opatch auto /u01/stage/patch/grid
Executing /usr/bin/perl /u01/app/11.2.0/grid/OPatch/crs/patch112.pl -patchdir /u01/stage/patch -patchn grid -paramfile /u01/app/11.2.0/grid/crs/install/crsconfig_params
opatch auto log file location is /u01/app/11.2.0/grid/OPatch/crs/../../cfgtoollogs/opatchauto2011-05-16_10-39-51.log
Detected Oracle Clusterware install
Using configuration parameter file: /u01/app/11.2.0/grid/crs/install/crsconfig_params
OPatch is bundled with OCM, Enter the absolute OCM response file path:
/u01/stage/patch/grid/ocm.rsp
patch /u01/stage/patch/grid/12311357/custom/server/12311357 apply successful for home /u01/app/oracle/product/11.2.0/dbhome_1
patch /u01/stage/patch/grid/11724916 apply successful for home /u01/app/oracle/product/11.2.0/dbhome_1
Successfully unlock /u01/app/11.2.0/grid
patch /u01/stage/patch/grid/12311357 apply successful for home /u01/app/11.2.0/grid
patch /u01/stage/patch/grid/11724916 apply successful for home /u01/app/11.2.0/grid
ACFS-9300: ADVM/ACFS distribution files found.
ACFS-9312: Existing ADVM/ACFS installation detected.
ACFS-9314: Removing previous ADVM/ACFS installation.
ACFS-9315: Previous ADVM/ACFS components successfully removed.
ACFS-9307: Installing requested ADVM/ACFS software.
ACFS-9308: Loading installed ADVM/ACFS drivers.
ACFS-9321: Creating udev for ADVM/ACFS.
ACFS-9323: Creating module dependencies - this may take some time.
ACFS-9327: Verifying ADVM/ACFS devices.
ACFS-9309: ADVM/ACFS installation correctness verified.
CRS-4123: Oracle High Availability Services has been started.
[root@vmxdb04 ~]# $ORACLE_HOME/OPatch/opatch auto /u01/stage/patch/grid
Executing /usr/bin/perl /u01/app/11.2.0/grid/OPatch/crs/patch112.pl -patchdir /u01/stage/patch -patchn grid -paramfile /u01/app/11.2.0/grid/crs/install/crsconfig_params
opatch auto log file location is /u01/app/11.2.0/grid/OPatch/crs/../../cfgtoollogs/opatchauto2011-05-16_11-07-49.log
Detected Oracle Clusterware install
Using configuration parameter file: /u01/app/11.2.0/grid/crs/install/crsconfig_params
OPatch is bundled with OCM, Enter the absolute OCM response file path:
/u01/stage/patch/grid/ocm.rsp
patch /u01/stage/patch/grid/12311357/custom/server/12311357 apply successful for home /u01/app/oracle/product/11.2.0/dbhome_1
patch /u01/stage/patch/grid/11724916 apply successful for home /u01/app/oracle/product/11.2.0/dbhome_1
Successfully unlock /u01/app/11.2.0/grid
patch /u01/stage/patch/grid/12311357 apply successful for home /u01/app/11.2.0/grid
patch /u01/stage/patch/grid/11724916 apply successful for home /u01/app/11.2.0/grid
ACFS-9300: ADVM/ACFS distribution files found.
ACFS-9312: Existing ADVM/ACFS installation detected.
ACFS-9314: Removing previous ADVM/ACFS installation.
ACFS-9315: Previous ADVM/ACFS components successfully removed.
ACFS-9307: Installing requested ADVM/ACFS software.
ACFS-9308: Loading installed ADVM/ACFS drivers.
ACFS-9321: Creating udev for ADVM/ACFS.
ACFS-9323: Creating module dependencies - this may take some time.
ACFS-9327: Verifying ADVM/ACFS devices.
ACFS-9309: ADVM/ACFS installation correctness verified.
CRS-4123: Oracle High Availability Services has been started.
[oracle@vmxdb03 ~]$ cd $ORACLE_HOME/rdbms/admin
[oracle@vmxdb03 admin]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.2.0 Production on Mon May 16 12:27:42 2011
Copyright (c) 1982, 2010, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Data Mining and Real Application Testing options
SQL> @catbundle.sql psu apply
PL/SQL procedure successfully completed.
PL/SQL procedure successfully completed.
Generating apply and rollback scripts...
Check the following file for errors:
/u01/app/oracle/cfgtoollogs/catbundle/catbundle_PSU_ORCL_GENERATE_2011May16_12_27_48.log
Apply script: /u01/app/oracle/product/11.2.0/dbhome_1/rdbms/admin/catbundle_PSU_ORCL_APPLY.sql
Rollback script: /u01/app/oracle/product/11.2.0/dbhome_1/rdbms/admin/catbundle_PSU_ORCL_ROLLBACK.sql
PL/SQL procedure successfully completed.
Executing script file...
SQL> COLUMN spool_file NEW_VALUE spool_file NOPRINT
SQL> SELECT '/u01/app/oracle/cfgtoollogs/catbundle/' || 'catbundle_PSU_' || name || '_APPLY_' || TO_CHAR(SYSDATE, 'YYYYMonDD_hh24_mi_ss', 'NLS_DATE_LANGUAGE=''AMERICAN''') || '.log' AS spool_file FROM v$database;
SQL> SPOOL &spool_file
SQL> exec dbms_registry.set_session_namespace('SERVER')
PL/SQL procedure successfully completed.
SQL> PROMPT Skipping Oracle Database Vault because it is not installed or versions mismatch...
Skipping Oracle Database Vault because it is not installed or versions mismatch...
SQL> ALTER SESSION SET current_schema = SYS;
Session altered.
SQL> PROMPT Updating registry...
Updating registry...
SQL> INSERT INTO registry$history
2 (action_time, action,
3 namespace, version, id,
4 bundle_series, comments)
5 VALUES
6 (SYSTIMESTAMP, 'APPLY',
7 SYS_CONTEXT('REGISTRY$CTX','NAMESPACE'),
8 '11.2.0.2',
9 2,
10 'PSU',
11 'PSU 11.2.0.2.2');
1 row created.
SQL> COMMIT;
Commit complete.
SQL> SPOOL off
SQL> SET echo off
Check the following log file for errors:
/u01/app/oracle/cfgtoollogs/catbundle/catbundle_PSU_ORCL_APPLY_2011May16_12_27_53.log
SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Data Mining and Real Application Testing options
[oracle@vmxdb03 admin]$
--------------------------------------------------------------------------------
NAME TARGET STATE SERVER STATE_DETAILS
--------------------------------------------------------------------------------
Local Resources
--------------------------------------------------------------------------------
ora.DATA.dg
ONLINE ONLINE vmxdb03
ONLINE ONLINE vmxdb04
ora.FRA.dg
ONLINE ONLINE vmxdb03
ONLINE ONLINE vmxdb04
ora.LISTENER.lsnr
ONLINE ONLINE vmxdb03
ONLINE ONLINE vmxdb04
ora.OCRVOTE.dg
ONLINE ONLINE vmxdb03
ONLINE ONLINE vmxdb04
ora.asm
ONLINE ONLINE vmxdb03 Started
ONLINE ONLINE vmxdb04
ora.gsd
OFFLINE OFFLINE vmxdb03
OFFLINE OFFLINE vmxdb04
ora.net1.network
ONLINE ONLINE vmxdb03
ONLINE ONLINE vmxdb04
ora.ons
ONLINE ONLINE vmxdb03
ONLINE ONLINE vmxdb04
ora.registry.acfs
ONLINE ONLINE vmxdb03
ONLINE ONLINE vmxdb04
--------------------------------------------------------------------------------
Cluster Resources
--------------------------------------------------------------------------------
ora.LISTENER_SCAN1.lsnr
1 ONLINE ONLINE vmxdb04
ora.LISTENER_SCAN2.lsnr
1 ONLINE ONLINE vmxdb03
ora.LISTENER_SCAN3.lsnr
1 ONLINE ONLINE vmxdb03
ora.cvu
1 ONLINE ONLINE vmxdb03
ora.oc4j
1 ONLINE ONLINE vmxdb03
ora.orcl.db
1 ONLINE ONLINE vmxdb03 Open
2 ONLINE ONLINE vmxdb04 Open
ora.scan1.vip
1 ONLINE ONLINE vmxdb04
ora.scan2.vip
1 ONLINE ONLINE vmxdb03
ora.scan3.vip
1 ONLINE ONLINE vmxdb03
ora.vmxdb03.vip
1 ONLINE ONLINE vmxdb03
ora.vmxdb04.vip
1 ONLINE ONLINE vmxdb04
[grid@vmxdb03 grid]$ $ORACLE_HOME/OPatch/ocm/bin/emocmrsp
OCM Installation Response Generator 10.3.4.0.0 - Production
Copyright (c) 2005, 2010, Oracle and/or its affiliates. All rights reserved.
Provide your email address to be informed of security issues, install and
initiate Oracle Configuration Manager. Easier for you if you use your My
Oracle Support Email address/User Name.
Visit http://www.oracle.com/support/policies.html for details.
Email address/User Name:
You have not provided an email address for notification of security issues.
Do you wish to remain uninformed of security issues ([Y]es, [N]o) [N]: y
The OCM configuration response file (ocm.rsp) was successfully created.
[root@vmxdb03 patch]# opatch auto /u01/stage/patch/grid
Executing /usr/bin/perl /u01/app/11.2.0/grid/OPatch/crs/patch112.pl -patchdir /u01/stage/patch -patchn grid -paramfile /u01/app/11.2.0/grid/crs/install/crsconfig_params
opatch auto log file location is /u01/app/11.2.0/grid/OPatch/crs/../../cfgtoollogs/opatchauto2011-05-16_10-39-51.log
Detected Oracle Clusterware install
Using configuration parameter file: /u01/app/11.2.0/grid/crs/install/crsconfig_params
OPatch is bundled with OCM, Enter the absolute OCM response file path:
/u01/stage/patch/grid/ocm.rsp
patch /u01/stage/patch/grid/12311357/custom/server/12311357 apply successful for home /u01/app/oracle/product/11.2.0/dbhome_1
patch /u01/stage/patch/grid/11724916 apply successful for home /u01/app/oracle/product/11.2.0/dbhome_1
Successfully unlock /u01/app/11.2.0/grid
patch /u01/stage/patch/grid/12311357 apply successful for home /u01/app/11.2.0/grid
patch /u01/stage/patch/grid/11724916 apply successful for home /u01/app/11.2.0/grid
ACFS-9300: ADVM/ACFS distribution files found.
ACFS-9312: Existing ADVM/ACFS installation detected.
ACFS-9314: Removing previous ADVM/ACFS installation.
ACFS-9315: Previous ADVM/ACFS components successfully removed.
ACFS-9307: Installing requested ADVM/ACFS software.
ACFS-9308: Loading installed ADVM/ACFS drivers.
ACFS-9321: Creating udev for ADVM/ACFS.
ACFS-9323: Creating module dependencies - this may take some time.
ACFS-9327: Verifying ADVM/ACFS devices.
ACFS-9309: ADVM/ACFS installation correctness verified.
CRS-4123: Oracle High Availability Services has been started.
[root@vmxdb04 ~]# $ORACLE_HOME/OPatch/opatch auto /u01/stage/patch/grid
Executing /usr/bin/perl /u01/app/11.2.0/grid/OPatch/crs/patch112.pl -patchdir /u01/stage/patch -patchn grid -paramfile /u01/app/11.2.0/grid/crs/install/crsconfig_params
opatch auto log file location is /u01/app/11.2.0/grid/OPatch/crs/../../cfgtoollogs/opatchauto2011-05-16_11-07-49.log
Detected Oracle Clusterware install
Using configuration parameter file: /u01/app/11.2.0/grid/crs/install/crsconfig_params
OPatch is bundled with OCM, Enter the absolute OCM response file path:
/u01/stage/patch/grid/ocm.rsp
patch /u01/stage/patch/grid/12311357/custom/server/12311357 apply successful for home /u01/app/oracle/product/11.2.0/dbhome_1
patch /u01/stage/patch/grid/11724916 apply successful for home /u01/app/oracle/product/11.2.0/dbhome_1
Successfully unlock /u01/app/11.2.0/grid
patch /u01/stage/patch/grid/12311357 apply successful for home /u01/app/11.2.0/grid
patch /u01/stage/patch/grid/11724916 apply successful for home /u01/app/11.2.0/grid
ACFS-9300: ADVM/ACFS distribution files found.
ACFS-9312: Existing ADVM/ACFS installation detected.
ACFS-9314: Removing previous ADVM/ACFS installation.
ACFS-9315: Previous ADVM/ACFS components successfully removed.
ACFS-9307: Installing requested ADVM/ACFS software.
ACFS-9308: Loading installed ADVM/ACFS drivers.
ACFS-9321: Creating udev for ADVM/ACFS.
ACFS-9323: Creating module dependencies - this may take some time.
ACFS-9327: Verifying ADVM/ACFS devices.
ACFS-9309: ADVM/ACFS installation correctness verified.
CRS-4123: Oracle High Availability Services has been started.
[oracle@vmxdb03 ~]$ cd $ORACLE_HOME/rdbms/admin
[oracle@vmxdb03 admin]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.2.0 Production on Mon May 16 12:27:42 2011
Copyright (c) 1982, 2010, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Data Mining and Real Application Testing options
SQL> @catbundle.sql psu apply
PL/SQL procedure successfully completed.
PL/SQL procedure successfully completed.
Generating apply and rollback scripts...
Check the following file for errors:
/u01/app/oracle/cfgtoollogs/catbundle/catbundle_PSU_ORCL_GENERATE_2011May16_12_27_48.log
Apply script: /u01/app/oracle/product/11.2.0/dbhome_1/rdbms/admin/catbundle_PSU_ORCL_APPLY.sql
Rollback script: /u01/app/oracle/product/11.2.0/dbhome_1/rdbms/admin/catbundle_PSU_ORCL_ROLLBACK.sql
PL/SQL procedure successfully completed.
Executing script file...
SQL> COLUMN spool_file NEW_VALUE spool_file NOPRINT
SQL> SELECT '/u01/app/oracle/cfgtoollogs/catbundle/' || 'catbundle_PSU_' || name || '_APPLY_' || TO_CHAR(SYSDATE, 'YYYYMonDD_hh24_mi_ss', 'NLS_DATE_LANGUAGE=''AMERICAN''') || '.log' AS spool_file FROM v$database;
SQL> SPOOL &spool_file
SQL> exec dbms_registry.set_session_namespace('SERVER')
PL/SQL procedure successfully completed.
SQL> PROMPT Skipping Oracle Database Vault because it is not installed or versions mismatch...
Skipping Oracle Database Vault because it is not installed or versions mismatch...
SQL> ALTER SESSION SET current_schema = SYS;
Session altered.
SQL> PROMPT Updating registry...
Updating registry...
SQL> INSERT INTO registry$history
2 (action_time, action,
3 namespace, version, id,
4 bundle_series, comments)
5 VALUES
6 (SYSTIMESTAMP, 'APPLY',
7 SYS_CONTEXT('REGISTRY$CTX','NAMESPACE'),
8 '11.2.0.2',
9 2,
10 'PSU',
11 'PSU 11.2.0.2.2');
1 row created.
SQL> COMMIT;
Commit complete.
SQL> SPOOL off
SQL> SET echo off
Check the following log file for errors:
/u01/app/oracle/cfgtoollogs/catbundle/catbundle_PSU_ORCL_APPLY_2011May16_12_27_53.log
SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Data Mining and Real Application Testing options
[oracle@vmxdb03 admin]$
Subscribe to:
Posts (Atom)