Knowledge Base Change the User's password Life Time

When Changing the users password Lifetime.this can be done with the profile alteration. Else you need to create a new profile only for that particular user.

This is done with the changing user profile values.

 

Check the relevant profile for the particular user.

Select profile from DBA_USERS where username = 'SYS';

You can alter the profile for the relevant user.

Normally default  profile name is DEFAULT.

If profile name is DEFAULT you can change it to unlimited or give numbers of days of the password life time

Alter profile DEFAULT limit password_life_time UNLIMITED;

Or mention the number of days

Alter profile DEFAULT limit password_life_time 200;

Oracle Physical Data guard.

Here I have successfully implemented Oracle Data guard.Followings are the steps which  carried out.

Primary  Server  (Server1) is  OEL6.5.swt.lk
Stand By  Server (Server2 )is OEL6.5-CLONE.swt.lk

Primary  and Stand By Servers  Both  have installed  Oracle 11.2.0.4  and Primary Server only has the DB only.Other Server does not have  a DB.  

Primary Server  DB name is  TEST                       Stand By Server DB name is TEST
Primary Server  DB_UNIQUE NAME  TEST      Stand By Server DB_UNIQUE_NAMETEST_STBY


Primary Server Configuration

Step 1

Check the log mode.It should be in ARCHIVELOG Mode.


SELECT log_mode FROM v$database;

If not in ARCHIVELOG mode  follow this steps.

SHUTDOWN IMMEDIATE;
STARTUP MOUNT;
ALTER DATABASE ARCHIVELOG;
ALTER DATABASE OPEN;


Step 2 

Enable force logging  :- If any change in that database happen It will go into the redo log and be usable for recovery.

                ALTER DATABASE FORCE LOGGING;

Step 3

Check out db_name and db_unique_name in  Primary Server

show parameter db_name
show parameter db_unique_name

Step 4

log_archive_config enables or disables the sending of redo logs to remote destinations and the receipt of remote redo logs. Also specifies the service provider names (sp_name) for each database in the Data Guard configuration.

DG_CONFIG  values  could be the db_unique name.It could not be more than nine databases.

ALTER SYSTEM SET LOG_ARCHIVE_CONFIG='DG_CONFIG=(TEST,TEST_STBY)';

ALTER SYSTEM SET LOG_ARCHIVE_DEST_2='SERVICE=test_stby NOAFFIRM ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=TEST_STBY';


ALTER SYSTEM SET LOG_ARCHIVE_DEST_STATE_2=ENABLE;

%t:- Thread name
%s- Log Sequence Number
%r- Reset Log Id
ALTER SYSTEM SET LOG_ARCHIVE_FORMAT='%t_%s_%r.arc' SCOPE=SPFILE;

Set Maximum number of active ARCH processes.

ALTER SYSTEM SET LOG_ARCHIVE_MAX_PROCESSES=30;

Check weather oracle looks for a password file by setting REMOTE_LOGIN_PASSWORDFILE and value is EXCLUSIVE mean this password file can be used by only one database.and contains all SYS and Users password.

ALTER SYSTEM SET REMOTE_LOGIN_PASSWORDFILE=EXCLUSIVE SCOPE=SPFILE;

Setting Up Target DB Server
FAL-->Fetch Archive Log

ALTER SYSTEM SET FAL_SERVER=TEST_STBY;
--ALTER SYSTEM SET DB_FILE_NAME_CONVERT='DB11G_STBY','DB11G' SCOPE=SPFILE;
--ALTER SYSTEM SET LOG_FILE_NAME_CONVERT='DB11G_STBY','DB11G'  SCOPE=SPFILE;
ALTER SYSTEM SET STANDBY_FILE_MANAGEMENT=AUTO;

Create tnsnames.ora file in Both Primary and StandBy Servers.

$ORACLE_HOME/network/admin/tnsnames.ora

TEST =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = oel6.5.swt.lk)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVICE_NAME = TEST)
    )
  )

TEST_STBY =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = oel6.5-clone.swt.lk)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVICE_NAME = TEST_STBY)
    )
  )


I planned to do this using moving primary data files to Stand By server. For that you needed to get an RMAN backup.

OS>  rman target=/

I backed up all archive log files plus  data files both using following command.

RMAN> BACKUP DATABASE PLUS ARCHIVELOG;

Create a control file for stand by database.

ALTER DATABASE CREATE STANDBY CONTROLFILE AS '/tmp/test_stby.ctl';

Create pfile for the stand by database.

CREATE PFILE='/tmp/initTEST_stby.ora' FROM SPFILE;

Amend the pfile entries  relevant to stand by database.

*.db_unique_name='TEST_STBY'
*.fal_server='TEST'
*.log_archive_dest_2='SERVICE=test ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=TEST'


Stand By Server Configuration

#Creating required folders
mkdir -p /u01/app/oracle/oradata/TEST
mkdir -p /u01/app/oracle/fast_recovery_area/TEST
mkdir -p /u01/app/oracle/admin/TEST/adump


#Copy  Standby controlfile to all locations.
 scp oracle@oel6.5.swt.lk:/tmp/test_stby.ctl /u01/app/oracle/oradata/TEST/control01.ctl
 cp /u01/app/oracle/oradata/TEST/control01.ctl /u01/app/oracle/fast_recovery_area/TEST/control02.ctl


# Copy Archivelogs and backups
 scp -r oracle@oel6.5.swt.lk:/u01/app/oracle/fast_recovery_area/TEST/archivelog /u01/app/oracle/fast_recovery_area/TEST
 scp -r oracle@oel6.5.swt.lk:/u01/app/oracle/fast_recovery_area/TEST/backupset /u01/app/oracle/fast_recovery_area/TEST


# Copy Parameter file.
scp oracle@oel6.5.swt.lk:/tmp/initTEST_stby.ora /tmp/initTEST_stby.ora

 # Remote login password file.
scp oracle@oel6.5.swt.lk:$ORACLE_HOME/dbs/orapwTEST $ORACLE_HOME/dbs

#Add Listener

SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
      (GLOBAL_DBNAME = TEST)
      (ORACLE_HOME = /u01/app/oracle/product/11.2.0/dbhome_1)
      (SID_NAME = TEST)
    )
  )

LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = dguard1)(PORT = 1521))
    )
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
    )
  )

ADR_BASE_LISTENER = /u01/app/oracle
--

SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
      (GLOBAL_DBNAME = DB11G)
      (ORACLE_HOME = /u01/app/oracle/product/11.2.0/dbhome_1)
      (SID_NAME = DB11G)
    )
  )

LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = dguard2)(PORT = 1521))
    )
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
    )
  )
ADR_BASE_LISTENER = /u01/app/oracle

#Start Listener
lsnrctl start

#Set the ORACLE_SID
export ORACLE_SID=TEST
sqlplus / as sysdba

#Create  the spfile from the location

SQL> CREATE SPFILE FROM PFILE='/tmp/initTEST_stby.ora';

#Set the ORACLE_SID
export ORACLE_SID=TEST
rman target=/

#Restore the Database back from
RMAN> STARTUP MOUNT;
RMAN> RESTORE DATABASE;

#Put  STANDBY_FILE_MANAGEMENT to MANUAL
ALTER SYSTEM SET STANDBY_FILE_MANAGEMENT=MANUAL;

#Add RedoLog Files groups on stand by server.
ALTER DATABASE ADD LOGFILE ('/u01/app/oracle/oradata/TEST/online_redo01.log') SIZE 10M;
ALTER DATABASE ADD LOGFILE ('/u01/app/oracle/oradata/TEST/online_redo02.log') SIZE 10M;
ALTER DATABASE ADD LOGFILE ('/u01/app/oracle/oradata/TEST/online_redo03.log') SIZE 10M;
ALTER SYSTEM SET STANDBY_FILE_MANAGEMENT=AUTO;

# Make sure on BOTH Nodes you need to create standby LOGFILES

ALTER DATABASE ADD STANDBY LOGFILE ('/u01/app/oracle/oradata/TEST/standby_redo01.log') SIZE 10M;
ALTER DATABASE ADD STANDBY LOGFILE ('/u01/app/oracle/oradata/TEST/standby_redo02.log') SIZE 10M;
ALTER DATABASE ADD STANDBY LOGFILE ('/u01/app/oracle/oradata/TEST/standby_redo03.log') SIZE 10M;
ALTER DATABASE ADD STANDBY LOGFILE ('/u01/app/oracle/oradata/TEST/standby_redo04.log') SIZE 10M;


#Redo Log Apply process

ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION;

#Testing  Log Apply Process

Primary Server

ALTER SESSION SET nls_date_format='DD-MON-YYYY HH24:MI:SS';

SELECT sequence#, first_time, next_time FROM   v$archived_log ORDER BY sequence#;

ALTER SYSTEM SWITCH LOGFILE;

Standby Server

ALTER SESSION SET nls_date_format='DD-MON-YYYY HH24:MI:SS';

SELECT sequence#, first_time, next_time, applied FROM   v$archived_log ORDER BY sequence#;


#Ultimatly Enter for automatic one  on standby server

#cancel out of the current  managed recovery
#Alter database recover managed standby database cancel;

# Real time Log apply with no time delay.

Alter database recover managed standby database using  current logfile disconnect



---------------------
Switch Over to Stand By 

For current primary database
# Convert primary database to standby
CONNECT / AS SYSDBA
ALTER DATABASE COMMIT TO SWITCHOVER TO STANDBY;

SHUTDOWN IMMEDIATE;

# Mount old primary database as standby database
STARTUP NOMOUNT;
ALTER DATABASE MOUNT STANDBY DATABASE;
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION;

For current stand by

 #Convert standby database to primary
CONNECT / AS SYSDBA
ALTER DATABASE COMMIT TO SWITCHOVER TO PRIMARY;

#Shutdown standby database
SHUTDOWN IMMEDIATE;

#Open old standby database as primary
STARTUP;

Now you are done with the SwithOver

 #Check the switch over status
 select switchover_status from v$database ;

Loading data Using SQL Loader.

I just tried to  Load sample data using SQLLOADER.

Actual data resides on  a flap file  delimited by "," symbol.

First i created a sample table to hold data.

 CREATE TABLE Test ( 
  ARTICLE_ID   NUMBER PRIMARY KEY , 
  AUTHOR       VARCHAR2(30), 
  FORMAT       VARCHAR2(30), 
  PUB_DATE     DATE, 
  TITLE        VARCHAR2(256), 
 );

Then run the following command to load  the data.

Make sure you have to connect sqlloader with SYSDBA  user account and  you need to provide a control file and the log file.Control file consist with What steps to be carried out when loading and Log file is used for logging purposes.

Here you see loader1.dat file 

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

load data

INFILE 'loader2.dat'

INTO TABLE articles_formatted

APPEND

FIELDS TERMINATED BY ','

(article_id SEQUENCE (MAX,1),

 author CHAR(30),

 format,

 pub_date SYSDATE,

 title,

 ext_fname FILLER CHAR(80) )

 

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

There you could see the loader2.dat file.That is the actual flapfile file.

Ben Kanobi, plaintext,Kawasaki news article,

Joe Bloggs, plaintext,Java plug-in,

John Hancock, plaintext,Declaration of Independence,

M. S. Developer, Word7,Newsletter example,

M. S. Developer, Word7,Resume example,

X. L. Developer, Excel7,Common example,

X. L. Developer, Excel7,Complex example,

Pow R. Point, Powerpoint7,Generic presentation,

Pow R. Point, Powerpoint7,Meeting presentation,

Java Man, PDF,Java Beans paper,

Java Man, PDF,Java on the server paper,

Ora Webmaster, HTML,Oracle home page,

Ora Webmaster, HTML,Oracle Company Overview,

John Constable, GIF,Laurence J. Ellison : portrait,

Alan Greenspan, GIF,Oracle revenues : Graph,

Giorgio Armani, GIF,Oracle Revenues : Trend,

  

Pleas make sure above file end up with "," symbol.

Installing DB Visit Stand By

DBVisit is a software which can be used to implement a replication of database  with low cost .No Other Software are needed.

 

As  for  Oracle Stranded Edition  and Enterprise Edition  having different licensing cost which you could have to pay much more money value for Oracle Enterprise Edition Licensing.As a solution provider you could suggest more cheaper method for  customers to enhance the IT solution.From customer's perspective they will try to reduce the cost .In Oracle Enterprise Edition it will provide Data guard  as a feature where customers will have to pay much more money.
So to full fill this Database Replication concept (Data guard concept) in Production Environment we can provide solution.


The DbVisit will  carry out 3 things mainly.

               1.  Extraction
               2.  Transport
               3.  Log Apply


Oracle Standard Edition with DbVisit.

 Prerequisites 

You need to have  Primary Server and a Stand by Server.Both Servers must have installed Oracle Binary  and created database. Both databases should be in Archivelog mode.

Dbvisit must be installed with the same user account that is running the oracle database software.
This user should be in oracle dba group.

**Now you can install dbvisitstandby.exe**

After Installing you need create Dbvisit Database Configuration (DDC) file. I have created the DDC using Command Line   --DDC can be created with GUI --

To create a Dbvisit Database Configuration (DDC) file from the command line you need to run the “dbvisit_setup” command.Go to Place where you installed dbvisitstandby
My Case                          C:\ProgrameFiles\dbvisit\Standby\
                            and run dbvisit_setup.exe  

There you could ask whether your db environment status.ex : Single Instance Database or RAC environment database. As my database is standalone I pressed 1 and moved on.
Then
This will ask for common details for Primary Server  make sure you give the correct details and fill almost all every thing.

After this configuration it will show the summary of values for primary server which you entered.

After that you need to configure the standby database. you can do this by pressing 7. Enter the correct values for asked questions.
This step will do the replica of the the primary db.This will send datafiles ,spfile,pfile and other files to the standby database.

There  you need to license the dbvisit software even you installing the trial version.

dbvisit.exe  -lic ORCL License_Key

Now you are done with configuring  DbVisitStandby.

There you could see some manual things you could do.

Manually send from primary server
dbvisit ORCL
Manually apply to standby server
dbvisit ORCL
Manually resend logs from primary server
dbvisit -R  ORCL
Apply manually  to Stand by server
dbvisit  ORCL
Check the log gap report   // this is on primary server
dbvisit  -i ORCL 

Perform Graceful switchover  // this has to be performed on both primary and standby servers.

dbv_oraStartStop switchover ORCL 123  // here 123 is a unique number  // this ha to be applyied on both side.

Source:https://dbvisit.atlassian.net/wiki/display/UGDS7/Introduction

Day today used Linux Command

When I  used to worked in LINUX environment,I had to use following commands in Day today Life :)


cd         Change Directory    ex : cd /tmp
mkdir   Make Directory        ex : mkdir -p /u01/app
which      Show the full path  ex :Which java
cp        Copy a file         ex :cp /tmp/init.ora . 
mv      Move file             ex :mv  /tmp/init.ora  /u01/app/oracle/product/10.2.0/dbhome_1/dbs
scp     Secure Copy         ex :scp oracle@192.9.220.110:/tmp/spfileORCL.ora   /u01/app/oracle/product    /10.2.0/dbhome_1/dbs
ll  /ls    list down all files and direcotries in the current location        ex : ll -a /ls -s /ll -la
man        list down all the possible parameters                              ex : man cp   
id        listdown the group,sub groups and user id will be displayed.    ex :id oracle                   
exit    exit from the terminal
find    find a file from specific location          ex :-find / -name "init.ora"
useradd     add an user      ex :  useradd -m -u 501 -g dba -G software  -c "Oracle Software Owner" oracle
groupadd       add a group       ex :groupadd -g 502 dba
vi /vim   text editors which can be used to format plain text files       ex  :  vi abc.txt
cat     concatinate and print the output of the file                               ex  : cat abc.txt
fsck    check and repai a Linux file system                                       ex :  fsck
df -h     storage(disk space) usage will be seen                                ex :  df -kh
ps    -ef list down the process                                                         ex :  ps -ef
fdisk  partition table  details shown                                                 ex : fdisk /dev/sda1
mount  Check mounted device to the server                                    ex :mount 
umount  unmount the
touch     create an empty file                                                          ex : touch abcd.txt
 tail / head   read a file  from  end of file or top of the file               ex :tail -f
 chmod  change the mod  of the file (read/write/execute for user/group/other  ex : chmod -R 777 /oracle/app
 chown     change the owner of the file                                           ex :chown -R oracle:dba  /u01/app/
 chkconfig  update and queries for runlevels                                  ex :- chkconfig --list iptables 
 date    Print/Set the date and time of the server
 echo     Display line of text                                                           ex :echo $ORACLE_HOME
 export export the values                                                              ex : export ORACLE_HOME
 kill      kill a process with process id                                             ex :  kill 1512
 gzip     zip a file
 tar      unzip a file                                                                         ex :-  tar -xvf abc
 rpm      redhat package manager                                                  ex :-  rpm -i gcc4.1.1.rpm
 netstat    shows the port details of the server                               ex :- netstat -a     
 nslookup    Query Internet domain service
 passwd        set/change password for users                                  ex :- passwd oracle
 pwd        print present working directory
 init0/shutdown power off the system
 init6/reboot    reboot  the system      
 sleep             delaying the system
 sudo             execute the command as another user   
 uname            show the kernal version                                       ex :- uname -a    
 userdel        delete a user account
 who am I        show who is logged in
 wc             Word Count
 ln            create links (softlinks)  ex :- ln -s  /media/RHEL6   /tmp       
 clear        clear the terminal Window
 dmesg  print kernel and driver message
 eject        eject the CDROM
 hostname    show the hostname
 history    show the history of the command you executed.
 ifconfig     Configure Network Interface

Upgrade OVM Manager 3.2.8 and Server 3.2.8

**Make sure  you upgrade OVM Manager before Upgrading OV Server. **
** Make sure you get a back up from existing  Manager  **
** Do all stuff as root user**

First of all you need to back up the existing Oracle Virtual Manager  configuration file and Database schema
In my case the meta data db was MySQL Which is deployed in the same server.schema name as OVS.

The Oracle VM Manger configuration file location is

/u01/app/oracle/ovm-manager-3/.config


 then perform backup.

Backups can be performed manually or automatic

Automatically performed backs up are located in

/u01/app/oracle/mysql/dbbackup

Back Up  is performed automatically every 24 hours. 
 there you  could see last 21 backup.


Manually backing Up

run the following script.
/u01/app/oracle/ovm-manager-3/bin/createBackup.sh








Please enter the Oracle VM manager user password:
INFO: Succesfully backed up database as AutoFullBackup-20140506_112035


Now  you can see this folder





Now you Can Upgrade the Oracle VM Manager.

Upgrade Oracle VM Manager

First you need to go into binary folder and run

./runUpgrader.sh



















Post Upgrade steps

After finishing upgrade,Please run the following command  on the server where oracle VM installed.

# cd /u01/app/oracle/ovm-manager-3/weblogic
# sh configureIdentityTrust.sh
# service ovmm stop
# service ovmm start
























Now you are done with Oracle VM Upgrade

Upgrade Oracle VM Server

Thing are to be done when Upgrading
Choose Update existing boot loader.
Then It will upgrade the boot loader.  as I remember it will upgrade 17 packages only.

Now you are done with  the stuff.. :))

Haffy stuff ...!!

Reference Installation and Upgrade Guide for Release 3.2  (E35330-06)