[ORACLE DB] Create a log on trigger database to trace all session from user

Login Sys:
- Create Table to store name of trace file.

SQL> CREATE TABLE login_audit_info
(
logon_time DATE         ,
username   VARCHAR2(100),
tracefile  VARCHAR2(100)
);

- Create a logon trigger Database


CREATE OR REPLACE TRIGGER audit_login_trigger
   AFTER LOGON
   ON DATABASE
DECLARE
   l_user        SYS.dba_users.username%TYPE := USER;
   l_sql         VARCHAR2 (500);
   l_tracefile   VARCHAR2 (100);
   l_time        DATE;
BEGIN
   l_sql :=
         'alter session set events '
      || CHR (39)
      || '10046 trace name context forever, level 12'
      || CHR (39);
   l_time := SYSDATE;

   IF (l_user = 'GOLDLIVE')
   THEN
      EXECUTE IMMEDIATE l_sql;

      SELECT    pa.VALUE
             || '/'
             || LOWER (SYS_CONTEXT ('userenv', 'instance_name'))
             || '_ora_'
             || p.spid
             || '.trc'
        INTO l_tracefile
        FROM v$session s, v$process p, v$parameter pa
       WHERE     pa.NAME = 'user_dump_dest'
             AND s.paddr = p.addr
             AND s.audsid = SYS_CONTEXT ('USERENV', 'SESSIONID');

      INSERT INTO login_audit_info (logon_time, username, tracefile)
           VALUES (l_time, l_user, l_tracefile);

      COMMIT;
   END IF;
END;
/

- When User login, will execute the trigger, Trace of session keep in login_audit_info.

SQL> select * from login_audit_info;

- To Disable Trigger.

SQL>alter trigger audit_login_trigger disable;

[ORACLE 11G - DATAGUARD] BACKUP & RESTORE STANDBY DB BY RMAN

run {
allocate channel st1 device type disk;
allocate channel st2 device type disk;
allocate channel st3 device type disk;
allocate channel st4 device type disk;
allocate channel st5 device type disk;
allocate channel st6 device type disk;
allocate channel st7 device type disk;
allocate channel st8 device type disk;
allocate channel st9 device type disk;
allocate channel st10 device type disk;
allocate channel st11 device type disk;
allocate channel st12 device type disk;
allocate channel st13 device type disk;
allocate channel st14 device type disk;
allocate channel st15 device type disk;
allocate channel st16 device type disk;
allocate channel st17 device type disk;
allocate channel st18 device type disk;
allocate channel st19 device type disk;
allocate channel st20 device type disk;
backup as compressed backupset format '/data1fs/RMAN/%d_%t_%U.rman' database plus archivelog format '/data1fs/RMAN/%d_archivelog_%t_%U.ARC';
release channel st1;
release channel st2;
release channel st3;
release channel st4;
release channel st5;
release channel st6;
release channel st7;
release channel st8;
release channel st9;
release channel st10;
release channel st11;
release channel st12;
release channel st13;
release channel st14;
release channel st15;
release channel st16;
release channel st17;
release channel st18;
release channel st19;
release channel st20;
}

run {
allocate channel st1 device type disk;
allocate channel st2 device type disk;
allocate channel st3 device type disk;
allocate channel st4 device type disk;
allocate channel st5 device type disk;
allocate channel st6 device type disk;
allocate channel st7 device type disk;
allocate channel st8 device type disk;
allocate channel st9 device type disk;
allocate channel st10 device type disk;
allocate channel st11 device type disk;
allocate channel st12 device type disk;
allocate channel st13 device type disk;
allocate channel st14 device type disk;
allocate channel st15 device type disk;
allocate channel st16 device type disk;
allocate channel st17 device type disk;
allocate channel st18 device type disk;
allocate channel st19 device type disk;
allocate channel st20 device type disk;
restore database;
release channel st1;
release channel st2;
release channel st3;
release channel st4;
release channel st5;
release channel st6;
release channel st7;
release channel st8;
release channel st9;
release channel st10;
release channel st11;
release channel st12;
release channel st13;
release channel st14;
release channel st15;
release channel st16;
release channel st17;
release channel st18;
release channel st19;
release channel st20;
}

[ORACLE 11G - RMAN] Delete RMAN Backup Fail

HƯỚNG DẪN XÓA RMAN BACKUP LỖI <TAPE>

I/ Mục đích.

-        Xóa backup database lỗi, giải phóng dung lượng Tape.

-        Xóa thông tin backup khỏi RMAN Repository (Controlfile), giúp quá trình backup restore nhanh hơn, do bỏ qua thông tin lỗi.

II/ Tiến Hành.

1/ Xác định backup lỗi.

-        Thực hiện câu select sau để xác định backup rman là lỗi. Lấy thông tin SESSION_RECID, SESSION_STAMP

select * from V$RMAN_BACKUP_JOB_DETAILS order by start_time desc;

-        Thực hiện câu lệnh Select sau để lấy tên backup tag lỗi.

select output from GV$RMAN_OUTPUT where session_recid = &SESSION_RECID   and session_stamp = &SESSION_STAMP order by recid;

2/ Thực hiện xóa Backup lỗi từ RMAN

-        Vào RMAN, Check thông tin Backup Tag, rồi xóa đi

rman target /

LIST BACKUP TAG NAME_OF_TAG;

 Xóa Backup Tag Fail.

RUN {

ALLOCATE CHANNEL ch00 TYPE 'SBT_TAPE' parms 'ENV=(TDPO_OPTFILE=/usr/tivoli/tsm/client/oracle/bin64/tdpo.opt)';

DELETE BACKUP TAG='TAG20161016T154244';

RELEASE CHANNEL ch00;

}

Make by HieuPV

[Oracle 12c] Nghiên cứu, ứng dụng, triển khai Oracle 12c

[ORACLE 11G - DATAGUARD] ARCHIVELOG DELETION POLICY ON STANDBY

Using this configuration on physical standby databases will enable automatic deletion of archived logs on the standby database. Using this policy requires that the database uses a FRA.

RMAN> CONFIGURE ARCHIVELOG DELETION POLICY TO APPLIED ON STANDBY;

new RMAN configuration parameters:
CONFIGURE ARCHIVELOG DELETION POLICY TO APPLIED ON STANDBY;
new RMAN configuration parameters are successfully stored

[ORACLE DB 11G-DATAGUARD] Clone Oracle Database Standby Node

We have an Oracle DB using Dataguard configuration with one node DC - running as Primary mode, one node DR - running as Standby mode. Now we need to create one more other node in standby mode - read only, running as report DB server. Let's using these steps to build new standby by clone standby node.

On Standby:
- Stop recovery mode on Standby node
- shutdown standby mode.
- create pfile from spfile.
- copy pfile,orapwSID file, controlfile,datafile,redologfile,standbylogfile.
- startup mount -> start recovery mode.
On Primary:
- Add tnsnames.ora -> point to new standby.
- Add log_archive_config -> add New Standby service
- Add log_archive_dest_xxx -> Point to New Standby service
- Add log_archive_dest_state_xxx -> enable;
On New Standby:
- Modify pfile.
- startup pfile nomount;
- create spfile from pfile.
- alter database mount; (Rename datafile, if path on standby #)

- start recovery mode.

Check Oracle Dataguard status.

[ORACLE-11G] Dataguard Switchover Manual

primary

SQL> select open_mode, database_role from v$database;

Check achivelog sq between pri=dg

select THREAD#, SEQUENCE#,RESETLOGS_TIME,RESETLOGS_ID,APPLIED,COMPLETION_TIME from v$archived_log order by COMPLETION_TIME asc;

Check Jobs

SQL> select * from dba_jobs_running; --> stop job or set alter system set job_queue_processes=0;

Check Session

SQL> select username,sid,serial# from v$session where type='USER';

- kill user session; SQL>alter system kill session 'sid,serial';
- Stop App
- Restart Primary DB to clear session is best choice.

Check switchover status

SQL> select switchover_status from v$database;

SWITCHOVER_STATUS
--------------------
SESSIONS ACTIVE (Or To Standby)

Switchover to Physical Standby

SQL> ALTER DATABASE COMMIT TO SWITCHOVER TO PHYSICAL STANDBY WITH SESSION SHUTDOWN; -> Instance will be shutdown


physical

SQL> select switchover_status from v$database;

SWITCHOVER_STATUS
--------------------
SESSIONS ACTIVE (Or To Primary)

SQL> alter database commit to switchover to primary with session shutdown;
SQL> alter database open;


former primary: Start Mount, applying Log

sqlplus / as sysdba
sql>startup mount;
sql>alter database recover managed standby database using current logfile disconnect from session;

New Primary: Check Switch logfile between New Primary and New Standby

SQL> alter system switch logfile;

[ORACLE-DATABASE-11G] Using nid to rename an oracle database

1/ Check Database Status before rename

-          Check tnsping

[oracle@oeldg01 ~]$ tnsping oldname

TNS Ping Utility for Linux: Version 11.2.0.4.0 - Production on 29-APR-2016 16:39:59

Copyright (c) 1997, 2013, Oracle.  All rights reserved.

Used parameter files:

Used TNSNAMES adapter to resolve the alias

Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = oeldg01)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = oldname)))

OK (0 msec)

-          Check Processes

[oracle@oeldg01 ~]$ ps -ef | grep -i ora_* | grep -v grep

oracle    4968     1  0 Apr28 ?        00:00:36 /u01/app/oracle/product/11.2.0/dbhome_1/perl/bin/perl /u01/app/oracle/product/11.2.0/dbhome_1/bin/emwd.pl dbconsole /u01/app/oracle/product/11.2.0/dbhome_1/oeldg01_oeldg/sysman/log/emdb.nohup

oracle    5010  4968  0 Apr28 ?        00:13:44 /u01/app/oracle/product/11.2.0/dbhome_1/bin/emagent

root     33292 33248  0 16:20 pts/0    00:00:00 su - oracle

oracle   33293 33292  0 16:20 pts/0    00:00:00 -bash

oracle   34008  4968  4 16:22 ?        00:01:13 /u01/app/oracle/product/11.2.0/dbhome_1/jdk/bin/java -server -Xmx384M -XX:MaxPermSize=400M -XX:MinHeapFreeRatio=20 -XX:MaxHeapFreeRatio=40 -DORACLE_HOME=/u01/app/oracle/product/11.2.0/dbhome_1 -Doracle.home=/u01/app/oracle/product/11.2.0/dbhome_1/oc4j -Doracle.oc4j.localhome=/u01/app/oracle/product/11.2.0/dbhome_1/oeldg01_oeldg/sysman -DEMSTATE=/u01/app/oracle/product/11.2.0/dbhome_1/oeldg01_oeldg -Doracle.j2ee.dont.use.memory.archive=true -Djava.protocol.handler.pkgs=HTTPClient -Doracle.security.jazn.config=/u01/app/oracle/product/11.2.0/dbhome_1/oc4j/j2ee/OC4J_DBConsole_oeldg01_oeldg/config/jazn.xml -Djava.security.policy=/u01/app/oracle/product/11.2.0/dbhome_1/oc4j/j2ee/OC4J_DBConsole_oeldg01_oeldg/config/java2.policy -Djavax.net.ssl.KeyStore=/u01/app/oracle/product/11.2.0/dbhome_1/sysman/config/OCMTrustedCerts.txt-Djava.security.properties=/u01/app/oracle/product/11.2.0/dbhome_1/oc4j/j2ee/home/config/jazn.security.props -DEMDROOT=/u01/app/oracle/product/11.2.0/dbhome_1/oeldg01_oeldg -Dsysman.md5password=true -Drepapi.oracle.home=/u01/app/oracle/product/11.2.0/dbhome_1 -Ddisable.checkForUpdate=true -Doracle.sysman.ccr.ocmSDK.websvc.keystore=/u01/app/oracle/product/11.2.0/dbhome_1/jlib/emocmclnt.ks -Dice.pilots.html4.ignoreNonGenericFonts=true -Djava.awt.headless=true -jar /u01/app/oracle/product/11.2.0/dbhome_1/oc4j/j2ee/home/oc4j.jar -config /u01/app/oracle/product/11.2.0/dbhome_1/oc4j/j2ee/OC4J_DBConsole_oeldg01_oeldg/config/server.xml

root     34376 33652  0 16:23 pts/1    00:00:00 su - oracle

oracle   34377 34376  0 16:23 pts/1    00:00:00 -bash

oracle   37509     1  0 16:35 ?        00:00:00 ora_pmon_oldname

oracle   37514     1  0 16:35 ?        00:00:00 ora_psp0_oldname

oracle   37520     1  5 16:35 ?        00:00:38 ora_vktm_oldname

oracle   37530     1  0 16:35 ?        00:00:00 ora_gen0_oldname

oracle   37534     1  0 16:35 ?        00:00:00 ora_diag_oldname

oracle   37537     1  0 16:35 ?        00:00:00 ora_dbrm_oldname

oracle   37539     1  0 16:35 ?        00:00:00 ora_dia0_oldname

oracle   37541     1  0 16:35 ?        00:00:00 ora_mman_oldname

oracle   37543     1  0 16:35 ?        00:00:00 ora_dbw0_oldname

oracle   37545     1  0 16:35 ?        00:00:00 ora_lgwr_oldname

oracle   37547     1  0 16:35 ?        00:00:00 ora_ckpt_oldname

oracle   37549     1  0 16:35 ?        00:00:00 ora_smon_oldname

oracle   37551     1  0 16:35 ?        00:00:00 ora_reco_oldname

oracle   37553     1  0 16:35 ?        00:00:01 ora_mmon_oldname

oracle   37555     1  0 16:35 ?        00:00:00 ora_mmnl_oldname

oracle   37557     1  0 16:35 ?        00:00:00 ora_d000_oldname

oracle   37559     1  0 16:35 ?        00:00:00 ora_s000_oldname

oracle   37601     1  0 16:36 ?        00:00:00 ora_qmnc_oldname

oracle   37624     1  0 16:36 ?        00:00:00 ora_cjq0_oldname

oracle   37637     1  0 16:36 ?        00:00:00 ora_q000_oldname

oracle   37641     1  0 16:36 ?        00:00:00 ora_q001_oldname

oracle   38781     1  0 16:41 ?        00:00:00 ora_smco_oldname

oracle   38783     1  0 16:41 ?        00:00:00 ora_w000_oldname

-          Try to connect, check name

[oracle@oeldg01 ~]$ export ORACLE_SID=oldname

[oracle@oeldg01 ~]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.4.0 Production on Fri Apr 29 16:48:08 2016

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

Connected to:

Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> select name from v$database;    

NAME

---------

OLDNAME

2/ OK. Let’s start changing database name.

On tab 1

SQL> shutdown immediate;

Database closed.

Database dismounted.

ORACLE instance shut down.

SQL> startup mount;

ORACLE instance started.

Total System Global Area  413372416 bytes

Fixed Size                                2253784 bytes

Variable Size                        327158824 bytes

Database Buffers                79691776 bytes

Redo Buffers                         4268032 bytes

Database mounted.

SQL>

Open New Tab 2:

[oracle@oeldg01 ~]$ export ORACLE_SID=oldname

[oracle@oeldg01 ~]$ nid TARGET=sys/oracle@oldname DBNAME=newname

DBNEWID: Release 11.2.0.4.0 - Production on Fri Apr 29 16:54:40 2016

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

Connected to database OLDNAME (DBID=137874822)

Connected to server version 11.2.0

Control Files in database:

    /u01/app/oracle/oradata/oldname/control01.ctl

    /u01/app/oracle/oradata/oldname/control02.ctl

Change database ID and database name OLDNAME to NEWNAME? (Y/[N]) => Y

Proceeding with operation

Changing database ID from 137874822 to 2288653456

Changing database name from OLDNAME to NEWNAME

    Control File /u01/app/oracle/oradata/oldname/control01.ctl - modified

    Control File /u01/app/oracle/oradata/oldname/control02.ctl - modified

    Datafile /u01/app/oracle/oradata/oldname/system01.db - dbid changed, wrote new name

    Datafile /u01/app/oracle/oradata/oldname/sysaux01.db - dbid changed, wrote new name

    Datafile /u01/app/oracle/oradata/oldname/undotbs01.db - dbid changed, wrote new name

    Datafile /u01/app/oracle/oradata/oldname/users01.db - dbid changed, wrote new name

    Datafile /u01/app/oracle/oradata/oldname/temp01.db - dbid changed, wrote new name

    Control File /u01/app/oracle/oradata/oldname/control01.ctl - dbid changed, wrote new name

    Control File /u01/app/oracle/oradata/oldname/control02.ctl - dbid changed, wrote new name

    Instance shut down

Database name changed to NEWNAME.

Modify parameter file and generate a new password file before restarting.

Database ID for database NEWNAME changed to 2288653456.

All previous backups and archived redo logs for this database are unusable.

Database has been shutdown, open database with RESETLOGS option.

Succesfully changed database name and ID.

DBNEWID - Completed succesfully.

-          Copy Password file, Create new Parameter file.

[oracle@oeldg01 ~]$ cd $ORACLE_HOME/dbs

[oracle@oeldg01 dbs]$ cp orapwoldname orapwnewname

[oracle@oeldg01 ~]$ export ORACLE_SID=newname

[oracle@oeldg01 ~]$ sqlplus / as sysdba

SQL>  create pfile='/setup/pfile_for_newname.ora' from spfile;

Open new tab 3:

-          Modify Parameters for newname

[root@oeldg01 setup]# sed 's/oldname/newname/g' pfile_for_newname.ora > pfile_for_newname_run.ora

-          Move Trace file, Datafile to new location

[oracle@oeldg01 ~]$ mv /u01/app/oracle/admin/oldname /u01/app/oracle/admin/newname

[oracle@oeldg01 ~]$ mv /u01/app/oracle/oradata/oldname /u01/app/oracle/oradata/newname

Return tab 2:

-          Start mount, modify location of datafile to new.

SQL> startup mount pfile='/setup/pfile_for_newname_run.ora'

SQL> alter database rename file '/u01/app/oracle/oradata/oldname/system01.dbf' to '/u01/app/oracle/oradata/newname/system01.dbf';

Database altered.

SQL> alter database rename file '/u01/app/oracle/oradata/oldname/sysaux01.dbf' to '/u01/app/oracle/oradata/newname/sysaux01.dbf';

Database altered.

SQL> alter database rename file '/u01/app/oracle/oradata/oldname/undotbs01.dbf' to '/u01/app/oracle/oradata/newname/undotbs01.dbf';

Database altered.

SQL> alter database rename file '/u01/app/oracle/oradata/oldname/users01.dbf' to '/u01/app/oracle/oradata/newname/users01.dbf';

SQL> alter database rename file '/u01/app/oracle/oradata/oldname/redo01.log' to '/u01/app/oracle/oradata/newname/redo01.log';

SQL>  alter database rename file '/u01/app/oracle/oradata/oldname/redo02.log' to '/u01/app/oracle/oradata/newname/redo02.log';

SQL> alter database rename file '/u01/app/oracle/oradata/oldname/redo03.log' to '/u01/app/oracle/oradata/newname/redo03.log';

Open Resetlogs Database with new name of Database:

SQL> alter database open resetlogs;

SQL> create spfile='$ORACLE_HOME/dbs/spfilenewname.ora' from pfile='/setup/pfile_for_newname_run.ora';

SQL>exit;

3/ Check listener and Database status after rename

[oracle@oeldg01 ~]$ lsnrctl status

LSNRCTL for Linux: Version 11.2.0.4.0 - Production on 29-APR-2016 17:29:05

Copyright (c) 1991, 2013, Oracle.  All rights reserved.

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=oeldg01)(PORT=1521)))

STATUS of the LISTENER

------------------------

Alias                     LISTENER

Version                   TNSLSNR for Linux: Version 11.2.0.4.0 - Production

Start Date                28-APR-2016 06:44:28

Uptime                    1 days 10 hr. 44 min. 37 sec

Trace Level               off

Security                  ON: Local OS Authentication

SNMP                      OFF

Listener Parameter File   /u01/app/oracle/product/11.2.0/dbhome_1/network/admin/listener.ora

Listener Log File         /u01/app/oracle/diag/tnslsnr/oeldg01/listener/alert/log.xml

Listening Endpoints Summary...

  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=oeldg01)(PORT=1521)))

Services Summary...

Service "newname" has 1 instance(s).

  Instance "newname", status READY, has 1 handler(s) for this service...

Service "newnameXDB" has 1 instance(s).

  Instance "newname", status READY, has 1 handler(s) for this service...

 

  

[Oracle 11gr2-RAC-PCIDSS] Configure TDE Wallet on RAC to Encryption Tablespace, Table, Column Request by PCIDSS Certificate.

1/ Create Wallet

[oracle@oel01 ~]$ owm

Save to:

/u01/app/oracle/admin/$ORACLE_UNQNAME/wallet

2/ Configure ENV

-        Bash_profile:

[oracle@oel01 ~]$ cat .bash_profile

ORACLE_UNQNAME=RAC; export ORACLE_UNQNAME

-          Set srvctl env:

[oracle@oel01 ~]$ srvctl setenv database -d RAC -T ORACLE_UNQNAME=RAC

[oracle@oel01 ~]$ srvctl setenv database -d RAC -T ORACLE_BASE=/u01/app/oracle

[oracle@oel01 ~]$ srvctl setenv database -d RAC -T TNS_ADMIN=/u01/app/oracle/product/11.2.0/dbhome_1/network/admin

[oracle@oel01 ~]$ srvctl getenv database -d RAC

RAC: ORACLE_UNQNAME=RAC ORACLE_BASE=/u01/app/oracle TNS_ADMIN=/u01/app/oracle/product/11.2.0/dbhome_1/network/admin

3/ Configure sqlnet.ora

[oracle@oel01 ~]$ vi $ORACLE_HOME/network/admin/sqlnet.ora

ENCRYPTION_WALLET_LOCATION    =(source        =(method=file)         (method_data=(directory=/u01/app/oracle/admin/$ORACLE_UNQNAME/wallet))      )

4/ Set file permission for Wallet file:

Chmod 700 /u01/app/oracle/admin/$ORACLE_UNQNAME/wallet

Chmod 600 /u01/app/oracle/admin/$ORACLE_UNQNAME/wallet/*

Chmod u+i /u01/app/oracle/admin/$ORACLE_UNQNAME/wallet/*

5/ Configure Database using Wallet:

-          Stop Database:

[oracle@oel01 ~]$ srvctl stop database -d RAC

-          Start one node, the others is shutdown.

[oracle@oel01 ~]$ srvctl start instance -d RAC –i RAC1

SQL>alter system set encryption wallet open identified by “Password”;

SQL >select * from v$encryption_wallet; -> Must be open, file_name

SQL >alter system set encryption key identified by “Master_Password”;

SQL >alter system set encryption wallet close identified by “Password”;

-          Copy Wallet file to the others host:

$scp /u01/app/oracle/admin/$ORACLE_UNQNAME/wallet/* oracle@othernode: /u01/app/oracle/admin/$ORACLE_UNQNAME/wallet

-          Start all of instance:

[oracle@oel01 ~]$ srvctl start instance -d RAC –i RAC2

-          Let’s open wallet:

SQL >alter system set encryption wallet open identified by “Password”;

-          Check status:

SQL >select * from gv$encryption_wallet; -> Must be open, file_name;

Next, We will using TDE Wallet to encrypt Data on Tablespace, Table, Column.

[ORACLE RAC 11GR2] Playing Game with SRVCTL command.

Enable & Disable DATABASE on CRS

[oracle@oel01 ~]$ srvctl status database -d RAC
Instance RAC1 is running on node oel01
Instance RAC2 is running on node oel02
[oracle@oel01 ~]$ srvctl stop database -d RAC
[oracle@oel01 ~]$ srvctl status database -d RAC
Instance RAC1 is not running on node oel01
Instance RAC2 is not running on node oel02
[oracle@oel01 ~]$ srvctl disable database -d RAC
[oracle@oel01 ~]$ srvctl status database -d RAC
Instance RAC1 is not running on node oel01
Instance RAC2 is not running on node oel02
[oracle@oel01 ~]$ srvctl start database -d RAC
PRCR-1079 : Failed to start resource ora.rac.db
CRS-2501: Resource 'ora.rac.db' is disabled
[oracle@oel01 ~]$ srvctl enable database -d RAC
[oracle@oel01 ~]$ srvctl start database -d RAC
[oracle@oel01 ~]$ srvctl status database -d RAC
Instance RAC1 is running on node oel01
Instance RAC2 is running on node oel02

Remove Database from CRS

[oracle@oel01 ~]$ srvctl status database -d RAC
Instance RAC1 is running on node oel01
Instance RAC2 is running on node oel02
[oracle@oel01 ~]$ srvctl stop database -d RAC
[oracle@oel01 ~]$ srvctl remove database -d RAC
Remove the database RAC? (y/[n]) y
[oracle@oel01 ~]$ srvctl status database -d RAC
PRCD-1120 : The resource for database RAC could not be found.
PRCR-1001 : Resource ora.rac.db does not exist
[grid@oel01 ~]$ crs_stat -t -> Don't see ora.rac.db Resource

Can start instance on each node through sqlplus:

[oracle@oel01 ~]$ sqlplus / as sysdba -> can start on both of nodes.
SQL> startup
ORACLE instance started.

Total System Global Area  726540288 bytes
Fixed Size    2256792 bytes
Variable Size  511705192 bytes
Database Buffers  209715200 bytes
Redo Buffers    2863104 bytes
Database mounted.
Database opened.

Add Database & Intance to CRS

[oracle@oel01 ~]$ srvctl add database -d RAC -o /u01/app/oracle/product/11.2.0/dbhome_1 -p +DATA01/RAC/spfileRAC.ora -r PRIMARY -s open -t normal -n RAC -y AUTOMATIC -a "DATA01,DATA02"
[oracle@oel01 ~]$ srvctl status database -d RAC
Database is not running.
[oracle@oel01 ~]$ srvctl add instance -d RAC -i RAC1 -n oel01
[oracle@oel01 ~]$ srvctl add instance -d RAC -i RAC2 -n oel02
[oracle@oel01 ~]$ srvctl status database -d RAC
Instance RAC1 is not running on node oel01
Instance RAC2 is not running on node oel02

[grid@oel01 ~]$ crsctl status res -t -> The resources were added to CRS

ora.rac.db
      1        OFFLINE OFFLINE                                                   
      2        OFFLINE OFFLINE


[oracle@oel01 ~]$ srvctl start database -d RAC
[oracle@oel01 ~]$ srvctl status database -d RAC
Instance RAC1 is running on node oel01
Instance RAC2 is running on node oel02


Nodeapps on CRS (Network(VIP),GSD,ONS)


[oracle@oel01 ~]$ srvctl status nodeapps -n oel01
VIP oel01-vip is enabled
VIP oel01-vip is running on node: oel01
Network is enabled
Network is running on node: oel01
GSD is disabled
GSD is not running on node: oel01
ONS is enabled
ONS daemon is running on node: oel01
[oracle@oel01 ~]$ srvctl status nodeapps -n oel02
VIP oel02-vip is enabled
VIP oel02-vip is running on node: oel02
Network is enabled
Network is running on node: oel02
GSD is disabled
GSD is not running on node: oel02
ONS is enabled
ONS daemon is running on node: oel02

[oracle@oel01 ~]$ srvctl config nodeapps
Network exists: 1/172.16.111.0/255.255.255.0/eth0, type static
VIP exists: /oel01-vip/172.16.111.202/172.16.111.0/255.255.255.0/eth0, hosting node oel01
VIP exists: /oel02-vip/172.16.111.203/172.16.111.0/255.255.255.0/eth0, hosting node oel02
GSD exists
ONS exists: Local port 6100, remote port 6200, EM port 2016

[root@oel01 bin]# export ORACLE_HOME=/u01/app/oracle/product/11.2.0/dbhome_1
[root@oel01 bin]# ./srvctl remove nodeapps

[root@oel01 bin]# ./srvctl add nodeapps -n oel01 -A 172.16.111.240/255.255.255.0/eth0
[root@oel01 bin]# ./srvctl add nodeapps -n oel02 -A 172.16.111.241/255.255.255.0/eth0
[root@oel01 bin]# ./srvctl config nodeapps
Network exists: 1/172.16.111.0/255.255.255.0/eth0, type static
VIP exists: /172.16.111.240/172.16.111.240/172.16.111.0/255.255.255.0/eth0, hosting node oel01
VIP exists: /172.16.111.241/172.16.111.241/172.16.111.0/255.255.255.0/eth0, hosting node oel02
GSD exists
ONS exists: Local port 6100, remote port 6200, EM port 2016

[oracle@oel01 ~]$ srvctl start nodeapps
[oracle@oel01 ~]$ srvctl status nodeapps
VIP 172.16.111.240 is enabled
VIP 172.16.111.240 is running on node: oel01
VIP 172.16.111.241 is enabled
VIP 172.16.111.241 is running on node: oel02
Network is enabled
Network is running on node: oel01
Network is running on node: oel02
GSD is disabled
GSD is not running on node: oel01
GSD is not running on node: oel02
ONS is enabled
ONS daemon is running on node: oel01
ONS daemon is running on node: oel02


[oracle@oel01 ~]$ ifconfig -a
eth0      Link encap:Ethernet  HWaddr 00:0C:29:F8:86:6C  
          inet addr:172.16.111.200  Bcast:172.16.111.255  Mask:255.255.255.0
          inet6 addr: fe80::20c:29ff:fef8:866c/64 Scope:Link
          UP BROADCAST RUNNING MULTICAST  MTU:1500  Metric:1
          RX packets:9273 errors:0 dropped:0 overruns:0 frame:0
          TX packets:8516 errors:0 dropped:0 overruns:0 carrier:0
          collisions:0 txqueuelen:1000 
          RX bytes:1005371 (981.8 KiB)  TX bytes:1129551 (1.0 MiB)

eth0:1    Link encap:Ethernet  HWaddr 00:0C:29:F8:86:6C  
          inet addr:172.16.111.240  Bcast:172.16.111.255  Mask:255.255.255.0
          UP BROADCAST RUNNING MULTICAST  MTU:1500  Metric:1


if the listeners are in INTERMEDIATE then do these steps to fix:

$crsctl stat res -t 

ora.LISTENER.lsnr

       ONLINE   ONLINE               racdb1 

       ONLINE   INTERMEDIATE         racdb2   Not All Endpoints Registered

ora.LISTENER_SCAN1.lsnr

   1   ONLINE   INTERMEDIATE         racdb2    Not All Endpoints Registered



$<RDBMS ORACLE_HOME>/bin/lsnrctl stop LISTENER
$<GRID_HOME>/bin/srvctl stop listener -n RAC1
$<GRID_HOME>/bin/srvctl stop scan_listener -i 1
$<GRID_HOME>/bin/srvctl start listener -n oel01
$<GRID_HOME>/bin/srvctl start scan_listener -i 1
- Modify /etc/hosts -> to new VIP ADDR
- Check local_listener on Database point to VIP LISTENER


Working with Service:

SQL> select service_id,name from v$services;

SERVICE_ID NAME
---------- ----------------------------------------------------------------
5 RACXDB
6 RAC
1 SYS$BACKGROUND
2 SYS$USERS

SQL> show parameter service;

NAME     TYPE VALUE
------------------------------------ ----------- ------------------------------
service_names     string RAC
SQL> show parameter unique;

NAME     TYPE VALUE
------------------------------------ ----------- ------------------------------
db_unique_name     string RAC


[oracle@oel01 ~]$ srvctl status service -d RAC -s GIRL
PRCR-1001 : Resource ora.rac.GIRL.svc does not exist
[oracle@oel01 ~]$  srvctl add service -d RAC -s GIRL -r RAC1,RAC2
[oracle@oel01 ~]$ srvctl status service -d RAC -s GIRL
Service GIRL is not running.
[oracle@oel01 ~]$ srvctl start service -d RAC -s GIRL
[oracle@oel01 ~]$ srvctl status service -d RAC -s GIRL
Service GIRL is running on instance(s) RAC1,RAC2


SQL> select service_id,name from v$services;

SERVICE_ID NAME
---------- ----------------------------------------------------------------
3 GIRL
5 RACXDB
6 RAC
1 SYS$BACKGROUND
2 SYS$USERS

SRVCTL with configuration Database



[oracle@oel01 ~]$ srvctl config database -d RAC
Database unique name: RAC
Database name: RAC
Oracle home: /u01/app/oracle/product/11.2.0/dbhome_1
Oracle user: oracle
Spfile: +DATA01/RAC/spfileRAC.ora
Domain:
Start options: open
Stop options: normal
Database role: PRIMARY
Management policy: AUTOMATIC
Server pools: RAC
Database instances: RAC1,RAC2
Disk Groups: DATA01,DATA02
Mount point paths:
Services: FLEXPROD
Type: RAC

Database is administrator managed


[oracle@oel01 ~]$ srvctl modify database -d RAC -h

Modifies the configuration for the database.
...
    -t <stop_options>        Stop options for the database. Examples of shutdown options are NORMAL, TRANSACTIONAL, IMMEDIATE, or ABORT.

[oracle@oel01 ~]$ srvctl modify database -d RAC -t IMMEDIATE
[oracle@oel01 ~]$ srvctl config database -d RAC
Database unique name: RAC
Database name: RAC
Oracle home: /u01/app/oracle/product/11.2.0/dbhome_1
Oracle user: oracle
Spfile: +DATA01/RAC/spfileRAC.ora
Domain:
Start options: open
Stop options: immediate
Database role: PRIMARY
Management policy: AUTOMATIC
Server pools: RAC
Database instances: RAC1,RAC2
Disk Groups: DATA01,DATA02
Mount point paths:
Services: FLEXPROD
Type: RAC
Database is administrator managed

[OEL] Get Public YUM Repository

Setting up Oracle Linux 5 with public-yum

cd /etc/yum.repos.d/
wget http://public-yum.oracle.com/public-yum-el5.repo

Setting up Oracle Linux 6 with public-yum

cd /etc/yum.repos.d/
wget https://public-yum.oracle.com/public-yum-ol6.repo

[ORACLE-DB] Modify spfile setting in case unable to startup instance when parameter in spfile is wrong

$sqlplus / as sysdba

sql> create pfile='pfile.ora' from spfile='spfileORCL.ora';

Modify wrong parameter in pfile.ora and start your instance using this new configuration:

sql> startup pfile='pfile.ora';

You can then save your settings back to an spfile with the following command:

sql> create spfile='spfileORCL.ora' from pfile='pfile.ora';

And shutdown/start your database as usual:

sql> shutdown immediate;

sql> startup;

[IBM AIX] - Create many users follow [PCIDSS - User Policy] in a script

#!/bin/bash
x() {
LIST=
SET_A=
for i in "$@"
do
        if [ "$i" = "admin=true" ]
        then
                SET_A="-a"
                continue
        elif [ "$i" = "admin=false" ]
        then
                continue
        fi
        LIST="$LIST \"$i\""
done
eval mkuser $SET_A $LIST
}
for tpbuser in u_xxx u_yyy u_zzz
do
x admin='false' pgrp='staff' rlogin='true' loginretries='5' pwdwarntime='7' histsize='4' histexpire='0' maxage='13' minage='4' minlen='7' minalpha='1' minother='1' maxrepeats='5' mindiff='1' $tpbuser;
echo $tpbuser:Callme#1234 | chpasswd;
pwdadm -c $tpbuser;
done;

[Oracle - Database] Find Last active DDL and DLL on Table

/* Formatted on 1/16/2016 11:17:22 AM (QP5 v5.149.1003.31008) */
SELECT (SELECT last_ddl_time
          FROM dba_objects
         WHERE object_name = 'TAB$' AND owner = 'SYS')
          "DDL Time",
       DECODE (maxscn, 0, 'N/A', SCN_TO_TIMESTAMP (maxscn)) "DML Time"
  FROM (SELECT NVL (MAX (ORA_ROWSCN), 0) maxscn FROM SYS.TAB$);