'오라클'에 해당되는 글 499건

  1. 2015.12.30 sshUserSetup.sh 사용
  2. 2015.12.30 db 수동생성
  3. 2015.12.29 oracle 12R2 Beta 리뷰중 1
  4. 2015.12.09 linux bonding 작업
  5. 2015.12.08 12c data type 확장지원
  6. 2015.12.04 Oracle CLR services
  7. 2015.12.04 ASM 정보보기 Query 정리
  8. 2015.12.04 12c silent mode 설치
  9. 2015.12.04 12.1.0.2 pingtarget 기능
  10. 2015.12.03 oracle flashback 설정

2015. 12. 30. 11:24 오라클

sshUserSetup.sh 사용


rac 설치 사전사업으로 양 노드의 passwordless ssh 설정을 반드시 해 주어야 한다.

 

귀찮다. 설치 미디어내에 제공하는 쉘로 ssh 한방에 설정~~

 

sshUserSetup.sh

 

./sshUserSetup.sh -user root -hosts "rac1 rac2" -noPromptPassphrase -advanced

./sshUserSetup.sh -user oracle -hosts "rac1 rac2" -noPromptPassphrase -advanced

 

sshUserSetup.sh 쉘 내용

 

#!/bin/sh
# Nitin Jerath - Aug 2005
#Usage sshUserSetup.sh  -user <user name> [ -hosts \"<space separated hostlist>\" | -hostfile <absolute path of cluster configuration file> ] [ -advanced ]  [ -verify] [ -exverify ] [ -logfile <desired absolute path of logfile> ] [-confirm] [-shared] [-help] [-usePassphrase] [-noPromptPassphrase]
#eg. sshUserSetup.sh -hosts "host1 host2" -user njerath -advanced
#This script is used to setup SSH connectivity from the host on which it is
# run to the specified remote hosts. After this script is run, the user can use # SSH to run commands on the remote hosts or copy files between the local host
# and the remote hosts without being prompted for passwords or confirmations.
# The list of remote hosts and the user name on the remote host is specified as
# a command line parameter to the script. Note that in case the user on the
# remote host has its home directory NFS mounted or shared across the remote
# hosts, this script should be used with -shared option.
#Specifying the -advanced option on the command line would result in SSH
# connectivity being setup among the remote hosts which means that SSH can be
# used to run commands on one remote host from the other remote host or copy
# files between the remote hosts without being prompted for passwords or
# confirmations.
#Please note that the script would remove write permissions on the remote hosts
#for the user home directory and ~/.ssh directory for "group" and "others". This
# is an SSH requirement. The user would be explicitly informed about this by teh script and prompted to continue. In case the user presses no, the script would exit. In case the user does not want to be prompted, he can use -confirm option.
# As a part of the setup, the script would use SSH to create files within ~/.ssh
# directory of the remote node and to setup the requisite permissions. The
#script also uses SCP to copy the local host public key to the remote hosts so
# that the remote hosts trust the local host for SSH. At the time, the script
#performs these steps, SSH connectivity has not been completely setup  hence
# the script would prompt the user for the remote host password.
#For each remote host, for remote users with non-shared homes this would be
# done once for SSH and  once for SCP. If the number of remote hosts are x, the
# user would be prompted  2x times for passwords. For remote users with shared
# homes, the user would be prompted only twice, once each for SCP and SSH.
#For security reasons, the script does not save passwords and reuse it. Also,
# for security reasons, the script does not accept passwords redirected from a
#file. The user has to key in the confirmations and passwords at the prompts.
#The -verify option means that the user just wants to verify whether SSH has
#been set up. In this case, the script would not setup SSH but would only check
# whether SSH connectivity has been setup from the local host to the remote
# hosts. The script would run the date command on each remote host using SSH. In
# case the user is prompted for a password or sees a warning message for a
#particular host, it means SSH connectivity has not been setup correctly for
# that host.
#In case the -verify option is not specified, the script would setup SSH and
#then do the verification as well.
#In case the user speciies the -exverify option, an exhaustive verification would be done. In that case, the following would be checked:
# 1. SSH connectivity from local host to all remote hosts.
# 2. SSH connectivity from each remote host to itself and other remote hosts.

#echo Parsing command line arguments
numargs=$#

ADVANCED=false
HOSTNAME=`hostname`
CONFIRM=no
SHARED=false
i=1
USR=$USER

if  test -z "$TEMP"
then
  TEMP=/tmp
fi

IDENTITY=id_rsa
LOGFILE=$TEMP/sshUserSetup_`date +%F-%H-%M-%S`.log
VERIFY=false
EXHAUSTIVE_VERIFY=false
HELP=false
PASSPHRASE=no
RERUN_SSHKEYGEN=no
NO_PROMPT_PASSPHRASE=no

while [ $i -le $numargs ]
do
  j=$1
  if [ $j = "-hosts" ]
  then
     HOSTS=$2
     shift 1
     i=`expr $i + 1`
  fi
  if [ $j = "-user" ]
  then
     USR=$2
     shift 1
     i=`expr $i + 1`
   fi
  if [ $j = "-logfile" ]
  then
     LOGFILE=$2
     shift 1
     i=`expr $i + 1`
   fi
  if [ $j = "-confirm" ]
  then
     CONFIRM=yes
   fi
  if [ $j = "-hostfile" ]
  then
     CLUSTER_CONFIGURATION_FILE=$2
     shift 1
     i=`expr $i + 1`
   fi
  if [ $j = "-usePassphrase" ]
  then
     PASSPHRASE=yes
   fi
  if [ $j = "-noPromptPassphrase" ]
  then
     NO_PROMPT_PASSPHRASE=yes
   fi
  if [ $j = "-shared" ]
  then
     SHARED=true
   fi
  if [ $j = "-exverify" ]
  then
     EXHAUSTIVE_VERIFY=true
   fi
  if [ $j = "-verify" ]
  then
     VERIFY=true
   fi
  if [ $j = "-advanced" ]
  then
     ADVANCED=true
   fi
  if [ $j = "-help" ]
  then
     HELP=true
   fi
  i=`expr $i + 1`
  shift 1
done


if [ $HELP = "true" ]
then
  echo "Usage $0 -user <user name> [ -hosts \"<space separated hostlist>\" | -hostfile <absolute path of cluster configuration file> ] [ -advanced ]  [ -verify] [ -exverify ] [ -logfile <desired absolute path of logfile> ] [-confirm] [-shared] [-help] [-usePassphrase] [-noPromptPassphrase]"
echo "This script is used to setup SSH connectivity from the host on which it is run to the specified remote hosts. After this script is run, the user can use  SSH to run commands on the remote hosts or copy files between the local host and the remote hosts without being prompted for passwords or confirmations.  The list of remote hosts and the user name on the remote host is specified as a command line parameter to the script. "
echo "-user : User on remote hosts. "
echo "-hosts : Space separated remote hosts list. "
echo "-hostfile : The user can specify the host names either through the -hosts option or by specifying the absolute path of a cluster configuration file. A sample host file contents are below: "
echo
echo  "   stacg30 stacg30int 10.1.0.0 stacg30v  -"
echo  "   stacg34 stacg34int 10.1.0.1 stacg34v  -"
echo
echo " The first column in each row of the host file will be used as the host name."
echo
echo "-usePassphrase : The user wants to set up passphrase to encrypt the private key on the local host. "
echo "-noPromptPassphrase : The user does not want to be prompted for passphrase related questions. This is for users who want the default behavior to be followed."
echo "-shared : In case the user on the remote host has its home directory NFS mounted or shared across the remote hosts, this script should be used with -shared option. "
echo "  It is possible for the user to determine whether a user's home directory is shared or non-shared. Let us say we want to determine that user user1's home directory is shared across hosts A, B and C."
echo " Follow the following steps:"
echo "    1. On host A, touch ~user1/checkSharedHome.tmp"
echo "    2. On hosts B and C, ls -al ~user1/checkSharedHome.tmp"
echo "    3. If the file is present on hosts B and C in ~user1 directory and"
echo "       is identical on all hosts A, B, C, it means that the user's home "
echo "       directory is shared."
echo "    4. On host A, rm -f ~user1/checkSharedHome.tmp"
echo " In case the user accidentally passes -shared option for non-shared homes or viceversa,SSH connectivity would only be set up for a subset of the hosts. The user would have to re-run the setyp script with the correct option to rectify this problem."
echo "-advanced :  Specifying the -advanced option on the command line would result in SSH  connectivity being setup among the remote hosts which means that SSH can be used to run commands on one remote host from the other remote host or copy files between the remote hosts without being prompted for passwords or confirmations."
echo "-confirm: The script would remove write permissions on the remote hosts for the user home directory and ~/.ssh directory for "group" and "others". This is an SSH requirement. The user would be explicitly informed about this by the script and prompted to continue. In case the user presses no, the script would exit. In case the user does not want to be prompted, he can use -confirm option."
echo  "As a part of the setup, the script would use SSH to create files within ~/.ssh directory of the remote node and to setup the requisite permissions. The script also uses SCP to copy the local host public key to the remote hosts so that the remote hosts trust the local host for SSH. At the time, the script performs these steps, SSH connectivity has not been completely setup  hence the script would prompt the user for the remote host password.  "
echo "For each remote host, for remote users with non-shared homes this would be done once for SSH and  once for SCP. If the number of remote hosts are x, the user would be prompted  2x times for passwords. For remote users with shared homes, the user would be prompted only twice, once each for SCP and SSH.  For security reasons, the script does not save passwords and reuse it. Also, for security reasons, the script does not accept passwords redirected from a file. The user has to key in the confirmations and passwords at the prompts. "
echo "-verify : -verify option means that the user just wants to verify whether SSH has been set up. In this case, the script would not setup SSH but would only check whether SSH connectivity has been setup from the local host to the remote hosts. The script would run the date command on each remote host using SSH. In case the user is prompted for a password or sees a warning message for a particular host, it means SSH connectivity has not been setup correctly for that host.  In case the -verify option is not specified, the script would setup SSH and then do the verification as well. "
echo "-exverify : In case the user speciies the -exverify option, an exhaustive verification for all hosts would be done. In that case, the following would be checked: "
echo "   1. SSH connectivity from local host to all remote hosts. "
echo "   2. SSH connectivity from each remote host to itself and other remote hosts.  "
echo The -exverify option can be used in conjunction with the -verify option as well to do an exhaustive verification once the setup has been done. 
echo "Taking some examples: Let us say local host is Z, remote hosts are A,B and C. Local user is njerath. Remote users are racqa(non-shared), aime(shared)."
echo "$0 -user racqa -hosts "A B C" -advanced -exverify -confirm"
echo "Script would set up connectivity from Z -> A, Z -> B, Z -> C, A -> A, A -> B, A -> C, B -> A, B -> B, B -> C, C -> A, C -> B, C -> C."
echo "Since user has given -exverify option, all these scenario would be verified too."
echo
echo "Now the user runs : $0 -user racqa -hosts "A B C" -verify"
echo "Since -verify option is given, no SSH setup would be done, only verification of existing setup. Also, since -exverify or -advanced options are not given, script would only verify connectivity from Z -> A, Z -> B, Z -> C"

echo "Now the user runs : $0 -user racqa -hosts "A B C" -verify -advanced"
echo "Since -verify option is given, no SSH setup would be done, only verification of existing setup. Also, since  -advanced options is given, script would verify connectivity from Z -> A, Z -> B, Z -> C, A-> A, A->B, A->C, A->D"

echo "Now the user runs:"
echo "$0 -user aime -hosts "A B C" -confirm -shared"
echo "Script would set up connectivity between  Z->A, Z->B, Z->C only since advanced option is not given."
echo "All these scenarios would be verified too."

exit
fi

if test -z "$HOSTS"
then
   if test -n "$CLUSTER_CONFIGURATION_FILE" && test -f "$CLUSTER_CONFIGURATION_FILE"
   then
      HOSTS=`awk '$1 !~ /^#/ { str = str " " $1 } END { print str }' $CLUSTER_CONFIGURATION_FILE`
   elif ! test -f "$CLUSTER_CONFIGURATION_FILE"
   then
     echo "Please specify a valid and existing cluster configuration file."
   fi
fi

if  test -z "$HOSTS" || test -z $USR
then
echo "Either user name or host information is missing"
echo "Usage $0 -user <user name> [ -hosts \"<space separated hostlist>\" | -hostfile <absolute path of cluster configuration file> ] [ -advanced ]  [ -verify] [ -exverify ] [ -logfile <desired absolute path of logfile> ] [-confirm] [-shared] [-help] [-usePassphrase] [-noPromptPassphrase]"
exit 1
fi

if [ -d $LOGFILE ]; then
    echo $LOGFILE is a directory, setting logfile to $LOGFILE/ssh.log
    LOGFILE=$LOGFILE/ssh.log
fi

echo The output of this script is also logged into $LOGFILE | tee -a $LOGFILE

if [ `echo $?` != 0 ]; then
    echo Error writing to the logfile $LOGFILE, Exiting
    exit 1
fi

echo Hosts are $HOSTS | tee -a $LOGFILE
echo user is  $USR | tee -a $LOGFILE
SSH="/usr/bin/ssh"
SCP="/usr/bin/scp"
SSH_KEYGEN="/usr/bin/ssh-keygen"
calculateOS()
{
    platform=`uname -s`
    case "$platform"
    in
       "SunOS")  os=solaris;;
       "Linux")  os=linux;;
       "HP-UX")  os=hpunix;;
         "AIX")  os=aix;;
             *)  echo "Sorry, $platform is not currently supported." | tee -a $LOGFILE
                 exit 1;;
    esac

    echo "Platform:- $platform " | tee -a $LOGFILE
}
calculateOS
BITS=1024
ENCR="rsa"

deadhosts=""
alivehosts=""
if [ $platform = "Linux" ]
then
    PING="/bin/ping"
else
    PING="/usr/sbin/ping"
fi
#bug 9044791
if [ -n "$SSH_PATH" ]; then
    SSH=$SSH_PATH
fi
if [ -n "$SCP_PATH" ]; then
    SCP=$SCP_PATH
fi
if [ -n "$SSH_KEYGEN_PATH" ]; then
    SSH_KEYGEN=$SSH_KEYGEN_PATH
fi
if [ -n "$PING_PATH" ]; then
    PING=$PING_PATH
fi
PATH_ERROR=0
if test ! -x $SSH ; then
    echo "ssh not found at $SSH. Please set the variable SSH_PATH to the correct location of ssh and retry."
    PATH_ERROR=1
fi
if test ! -x $SCP ; then
    echo "scp not found at $SCP. Please set the variable SCP_PATH to the correct location of scp and retry."
    PATH_ERROR=1
fi
if test ! -x $SSH_KEYGEN ; then
    echo "ssh-keygen not found at $SSH_KEYGEN. Please set the variable SSH_KEYGEN_PATH to the correct location of ssh-keygen and retry."
    PATH_ERROR=1
fi
if test ! -x $PING ; then
    echo "ping not found at $PING. Please set the variable PING_PATH to the correct location of ping and retry."
    PATH_ERROR=1
fi
if [ $PATH_ERROR = 1 ]; then
    echo "ERROR: one or more of the required binaries not found, exiting"
    exit 1
fi
#9044791 end
echo Checking if the remote hosts are reachable | tee -a $LOGFILE
for host in $HOSTS
do
   if [ $platform = "SunOS" ]; then
       $PING -s $host 5 5
   elif [ $platform = "HP-UX" ]; then
       $PING $host -n 5 -m 5
   else
       $PING -c 5 -w 5 $host
   fi
  exitcode=`echo $?`
  if [ $exitcode = 0 ]
  then
     alivehosts="$alivehosts $host"
  else
     deadhosts="$deadhosts $host"
  fi
done

if test -z "$deadhosts"
then
   echo Remote host reachability check succeeded.  | tee -a $LOGFILE
   echo The following hosts are reachable: $alivehosts.  | tee -a $LOGFILE
   echo The following hosts are not reachable: $deadhosts.  | tee -a $LOGFILE
   echo All hosts are reachable. Proceeding further...  | tee -a $LOGFILE
else
   echo Remote host reachability check failed.  | tee -a $LOGFILE
   echo The following hosts are reachable: $alivehosts.  | tee -a $LOGFILE
   echo The following hosts are not reachable: $deadhosts.  | tee -a $LOGFILE
   echo Please ensure that all the hosts are up and re-run the script.  | tee -a $LOGFILE
   echo Exiting now...  | tee -a $LOGFILE
   exit 1
fi

firsthost=`echo $HOSTS | awk '{print $1}; END { }'`
echo firsthost $firsthost
numhosts=`echo $HOSTS | awk '{ }; END {print NF}'`
echo numhosts $numhosts

if [ $VERIFY = "true" ]
then
   echo Since user has specified -verify option, SSH setup would not be done. Only, existing SSH setup would be verified. | tee -a $LOGFILE
   continue
else
echo The script will setup SSH connectivity from the host ''`hostname`'' to all  | tee -a $LOGFILE
echo the remote hosts. After the script is executed, the user can use SSH to run  | tee -a $LOGFILE
echo commands on the remote hosts or copy files between this host ''`hostname`'' | tee -a $LOGFILE
echo and the remote hosts without being prompted for passwords or confirmations. | tee -a $LOGFILE
echo  | tee -a $LOGFILE
echo NOTE 1: | tee -a $LOGFILE
echo As part of the setup procedure, this script will use 'ssh' and 'scp' to copy | tee -a $LOGFILE
echo files between the local host and the remote hosts. Since the script does not  | tee -a $LOGFILE
echo store passwords, you may be prompted for the passwords during the execution of  | tee -a $LOGFILE
echo the script whenever 'ssh' or 'scp' is invoked. | tee -a $LOGFILE
echo  | tee -a $LOGFILE
echo NOTE 2: | tee -a $LOGFILE
echo "AS PER SSH REQUIREMENTS, THIS SCRIPT WILL SECURE THE USER HOME DIRECTORY" | tee -a $LOGFILE
echo AND THE .ssh DIRECTORY BY REVOKING GROUP AND WORLD WRITE PRIVILEDGES TO THESE  | tee -a $LOGFILE
echo "directories." | tee -a $LOGFILE
echo  | tee -a $LOGFILE
echo "Do you want to continue and let the script make the above mentioned changes (yes/no)?" | tee -a $LOGFILE

if [ "$CONFIRM" = "no" ]
then
  read CONFIRM
else
  echo "Confirmation provided on the command line" | tee -a $LOGFILE
fi
  
echo  | tee -a $LOGFILE
echo The user chose ''$CONFIRM'' | tee -a $LOGFILE
 
if [ "$CONFIRM" = "no" ]
then
  echo "SSH setup is not done." | tee -a $LOGFILE
  exit 1
else
  if [ $NO_PROMPT_PASSPHRASE = "yes" ]
  then
    echo "User chose to skip passphrase related questions."  | tee -a $LOGFILE
  else
    typeset -i PASSPHRASE_PROMPT
    if [ $SHARED = "true" ]
    then
   PASSPHRASE_PROMPT=2*${numhosts}+1
    else
   PASSPHRASE_PROMPT=2*${numhosts}
    fi
    echo "Please specify if you want to specify a passphrase for the private key this script will create for the local host. Passphrase is used to encrypt the private key and makes SSH much more secure. Type 'yes' or 'no' and then press enter. In case you press 'yes', you would need to enter the passphrase whenever the script executes ssh or scp. " | tee -a $LOGFILE
    echo "The estimated number of times the user would be prompted for a passphrase is $PASSPHRASE_PROMPT. In addition, if the private-public files are also newly created, the user would have to specify the passphrase on one additional occasion. " | tee -a $LOGFILE
    echo "Enter 'yes' or 'no'." | tee -a $LOGFILE
    if [ $PASSPHRASE = "no" ]
    then
      read PASSPHRASE
    else
      echo "Confirmation provided on the command line" | tee -a $LOGFILE
    fi

    echo  | tee -a $LOGFILE
    echo The user chose ''$PASSPHRASE'' | tee -a $LOGFILE

    if [ "$PASSPHRASE" = "yes" ]
    then
       RERUN_SSHKEYGEN="yes"
#Checking for existence of ${IDENTITY} file
       if test -f  $HOME/.ssh/${IDENTITY}.pub && test -f  $HOME/.ssh/${IDENTITY}
       then
      echo "The files containing the client public and private keys already exist on the local host. The current private key may or may not have a passphrase associated with it. In case you remember the passphrase and do not want to re-run ssh-keygen, press 'no' and enter. If you press 'no', the script will not attempt to create any new public/private key pairs. If you press 'yes', the script will remove the old private/public key files existing and create new ones prompting the user to enter the passphrase. If you enter 'yes', any previous SSH user setups would be reset. If you press 'change', the script will associate a new passphrase with the old keys." | tee -a $LOGFILE
      echo "Press 'yes', 'no' or 'change'" | tee -a $LOGFILE
             read RERUN_SSHKEYGEN
             echo The user chose ''$RERUN_SSHKEYGEN'' | tee -a $LOGFILE
       fi
     else
       if test -f  $HOME/.ssh/${IDENTITY}.pub && test -f  $HOME/.ssh/${IDENTITY}
       then
         echo "The files containing the client public and private keys already exist on the local host. The current private key may have a passphrase associated with it. In case you find using passphrase inconvenient(although it is more secure), you can change to it empty through this script. Press 'change' if you want the script to change the passphrase for you. Press 'no' if you want to use your old passphrase, if you had one."
         read RERUN_SSHKEYGEN
         echo The user chose ''$RERUN_SSHKEYGEN'' | tee -a $LOGFILE
       fi
     fi
  fi
  echo Creating .ssh directory on local host, if not present already | tee -a $LOGFILE
  mkdir -p $HOME/.ssh | tee -a $LOGFILE
echo Creating authorized_keys file on local host  | tee -a $LOGFILE
touch $HOME/.ssh/authorized_keys  | tee -a $LOGFILE
echo Changing permissions on authorized_keys to 644 on local host  | tee -a $LOGFILE
chmod 644 $HOME/.ssh/authorized_keys  | tee -a $LOGFILE
mv -f $HOME/.ssh/authorized_keys  $HOME/.ssh/authorized_keys.tmp | tee -a $LOGFILE
echo Creating known_hosts file on local host  | tee -a $LOGFILE
touch $HOME/.ssh/known_hosts  | tee -a $LOGFILE
echo Changing permissions on known_hosts to 644 on local host  | tee -a $LOGFILE
chmod 644 $HOME/.ssh/known_hosts  | tee -a $LOGFILE
mv -f $HOME/.ssh/known_hosts $HOME/.ssh/known_hosts.tmp | tee -a $LOGFILE


echo Creating config file on local host | tee -a $LOGFILE
echo If a config file exists already at $HOME/.ssh/config, it would be backed up to $HOME/.ssh/config.backup.
echo "Host *" > $HOME/.ssh/config.tmp | tee -a $LOGFILE
echo "ForwardX11 no" >> $HOME/.ssh/config.tmp | tee -a $LOGFILE

if test -f $HOME/.ssh/config
then
  cp -f $HOME/.ssh/config $HOME/.ssh/config.backup
fi

mv -f $HOME/.ssh/config.tmp $HOME/.ssh/config  | tee -a $LOGFILE
chmod 644 $HOME/.ssh/config

if [ $RERUN_SSHKEYGEN = "yes" ]
then
  echo Removing old private/public keys on local host | tee -a $LOGFILE
  rm -f $HOME/.ssh/${IDENTITY} | tee -a $LOGFILE
  rm -f $HOME/.ssh/${IDENTITY}.pub | tee -a $LOGFILE
  echo Running SSH keygen on local host | tee -a $LOGFILE
  $SSH_KEYGEN -t $ENCR -b $BITS -f $HOME/.ssh/${IDENTITY}   | tee -a $LOGFILE

elif [ $RERUN_SSHKEYGEN = "change" ]
then
    echo Running SSH Keygen on local host to change the passphrase associated with the existing private key | tee -a $LOGFILE
    $SSH_KEYGEN -p -t $ENCR -b $BITS -f $HOME/.ssh/${IDENTITY} | tee -a $LOGFILE
elif test -f  $HOME/.ssh/${IDENTITY}.pub && test -f  $HOME/.ssh/${IDENTITY}
then
    continue
else
    echo Removing old private/public keys on local host | tee -a $LOGFILE
    rm -f $HOME/.ssh/${IDENTITY} | tee -a $LOGFILE
    rm -f $HOME/.ssh/${IDENTITY}.pub | tee -a $LOGFILE
    echo Running SSH keygen on local host with empty passphrase | tee -a $LOGFILE
    $SSH_KEYGEN -t $ENCR -b $BITS -f $HOME/.ssh/${IDENTITY} -N ''  | tee -a $LOGFILE
fi

if [ $SHARED = "true" ]
then
  if [ $USER = $USR ]
  then
#No remote operations required
    echo Remote user is same as local user | tee -a $LOGFILE
    REMOTEHOSTS=""
    chmod og-w $HOME $HOME/.ssh | tee -a $LOGFILE
  else   
    REMOTEHOSTS="${firsthost}"
  fi
else
  REMOTEHOSTS="$HOSTS"
fi

for host in $REMOTEHOSTS
do
     echo Creating .ssh directory and setting permissions on remote host $host | tee -a $LOGFILE
     echo "THE SCRIPT WOULD ALSO BE REVOKING WRITE PERMISSIONS FOR "group" AND "others" ON THE HOME DIRECTORY FOR $USR. THIS IS AN SSH REQUIREMENT." | tee -a $LOGFILE
     echo The script would create ~$USR/.ssh/config file on remote host $host. If a config file exists already at ~$USR/.ssh/config, it would be backed up to ~$USR/.ssh/config.backup. | tee -a $LOGFILE
     echo The user may be prompted for a password here since the script would be running SSH on host $host. | tee -a $LOGFILE
     $SSH -o StrictHostKeyChecking=no -x -l $USR $host "/bin/sh -c \"  mkdir -p .ssh ; chmod og-w . .ssh;   touch .ssh/authorized_keys .ssh/known_hosts;  chmod 644 .ssh/authorized_keys  .ssh/known_hosts; cp  .ssh/authorized_keys .ssh/authorized_keys.tmp ;  cp .ssh/known_hosts .ssh/known_hosts.tmp; echo \\"Host *\\" > .ssh/config.tmp; echo \\"ForwardX11 no\\" >> .ssh/config.tmp; if test -f  .ssh/config ; then cp -f .ssh/config .ssh/config.backup; fi ; mv -f .ssh/config.tmp .ssh/config\""  | tee -a $LOGFILE
     echo Done with creating .ssh directory and setting permissions on remote host $host. | tee -a $LOGFILE
done

for host in $REMOTEHOSTS
do
  echo Copying local host public key to the remote host $host | tee -a $LOGFILE
  echo The user may be prompted for a password or passphrase here since the script would be using SCP for host $host. | tee -a $LOGFILE

  $SCP $HOME/.ssh/${IDENTITY}.pub  $USR@$host:.ssh/authorized_keys | tee -a $LOGFILE
  echo Done copying local host public key to the remote host $host | tee -a $LOGFILE
done

cat $HOME/.ssh/${IDENTITY}.pub >> $HOME/.ssh/authorized_keys | tee -a $LOGFILE

for host in $HOSTS
do
  if [ $ADVANCED = "true" ]
  then
    echo Creating keys on remote host $host if they do not exist already. This is required to setup SSH on host $host. | tee -a $LOGFILE
    if [ $SHARED = "true" ]
    then
      IDENTITY_FILE_NAME=${IDENTITY}_$host
      COALESCE_IDENTITY_FILES_COMMAND="cat .ssh/${IDENTITY_FILE_NAME}.pub >> .ssh/authorized_keys"
    else
      IDENTITY_FILE_NAME=${IDENTITY}
    fi

   $SSH  -o StrictHostKeyChecking=no -x -l $USR $host " /bin/sh -c \"if test -f  .ssh/${IDENTITY_FILE_NAME}.pub && test -f  .ssh/${IDENTITY_FILE_NAME}; then echo; else rm -f .ssh/${IDENTITY_FILE_NAME} ;  rm -f .ssh/${IDENTITY_FILE_NAME}.pub ;  $SSH_KEYGEN -t $ENCR -b $BITS -f .ssh/${IDENTITY_FILE_NAME} -N '' ; fi; ${COALESCE_IDENTITY_FILES_COMMAND} \"" | tee -a $LOGFILE
  else
#At least get the host keys from all hosts for shared case - advanced option not set
    if test  $SHARED = "true" && test $ADVANCED = "false"
    then
      if [ $PASSPHRASE = "yes" ]
      then
  echo "The script will fetch the host keys from all hosts. The user may be prompted for a passphrase here in case the private key has been encrypted with a passphrase." | tee -a $LOGFILE
      fi
      $SSH  -o StrictHostKeyChecking=no -x -l $USR $host "/bin/sh -c true"
    fi
  fi
done

for host in $REMOTEHOSTS
do
  if test $ADVANCED = "true" && test $SHARED = "false" 
  then
      $SCP $USR@$host:.ssh/${IDENTITY}.pub $HOME/.ssh/${IDENTITY}.pub.$host | tee -a $LOGFILE
      cat $HOME/.ssh/${IDENTITY}.pub.$host >> $HOME/.ssh/authorized_keys | tee -a $LOGFILE
      rm -f $HOME/.ssh/${IDENTITY}.pub.$host | tee -a $LOGFILE
    fi
done

for host in $REMOTEHOSTS
do
   if [ $ADVANCED = "true" ]
   then
      if [ $SHARED != "true" ]
      then
         echo Updating authorized_keys file on remote host $host | tee -a $LOGFILE
         $SCP $HOME/.ssh/authorized_keys  $USR@$host:.ssh/authorized_keys | tee -a $LOGFILE
      fi
     echo Updating known_hosts file on remote host $host | tee -a $LOGFILE
     $SCP $HOME/.ssh/known_hosts $USR@$host:.ssh/known_hosts | tee -a $LOGFILE
   fi
   if [ $PASSPHRASE = "yes" ]
   then
  echo "The script will run SSH on the remote machine $host. The user may be prompted for a passphrase here in case the private key has been encrypted with a passphrase." | tee -a $LOGFILE
   fi
     $SSH -x -l $USR $host "/bin/sh -c \"cat .ssh/authorized_keys.tmp >> .ssh/authorized_keys; cat .ssh/known_hosts.tmp >> .ssh/known_hosts; rm -f  .ssh/known_hosts.tmp  .ssh/authorized_keys.tmp\"" | tee -a $LOGFILE
done

cat  $HOME/.ssh/known_hosts.tmp >> $HOME/.ssh/known_hosts | tee -a $LOGFILE
cat  $HOME/.ssh/authorized_keys.tmp >> $HOME/.ssh/authorized_keys | tee -a $LOGFILE
#Added chmod to fix BUG NO 5238814
chmod 644 $HOME/.ssh/authorized_keys
#Fix for BUG NO 5157782
chmod 644 $HOME/.ssh/config
rm -f  $HOME/.ssh/known_hosts.tmp $HOME/.ssh/authorized_keys.tmp | tee -a $LOGFILE
echo SSH setup is complete. | tee -a $LOGFILE
fi
fi

echo                                                                          | tee -a $LOGFILE
echo ------------------------------------------------------------------------ | tee -a $LOGFILE
echo Verifying SSH setup | tee -a $LOGFILE
echo =================== | tee -a $LOGFILE
echo The script will now run the 'date' command on the remote nodes using ssh | tee -a $LOGFILE
echo to verify if ssh is setup correctly. IF THE SETUP IS CORRECTLY SETUP,  | tee -a $LOGFILE
echo THERE SHOULD BE NO OUTPUT OTHER THAN THE DATE AND SSH SHOULD NOT ASK FOR | tee -a $LOGFILE
echo PASSWORDS. If you see any output other than date or are prompted for the | tee -a $LOGFILE
echo password, ssh is not setup correctly and you will need to resolve the  | tee -a $LOGFILE
echo issue and set up ssh again. | tee -a $LOGFILE
echo The possible causes for failure could be:  | tee -a $LOGFILE
echo   1. The server settings in /etc/ssh/sshd_config file do not allow ssh | tee -a $LOGFILE
echo      for user $USR. | tee -a $LOGFILE
echo   2. The server may have disabled public key based authentication.
echo   3. The client public key on the server may be outdated.
echo   4. ~$USR or  ~$USR/.ssh on the remote host may not be owned by $USR.  | tee -a $LOGFILE
echo   5. User may not have passed -shared option for shared remote users or | tee -a $LOGFILE
echo     may be passing the -shared option for non-shared remote users.  | tee -a $LOGFILE
echo   6. If there is output in addition to the date, but no password is asked, | tee -a $LOGFILE
echo   it may be a security alert shown as part of company policy. Append the | tee -a $LOGFILE
echo   "additional text to the <OMS HOME>/sysman/prov/resources/ignoreMessages.txt file." | tee -a $LOGFILE
echo ------------------------------------------------------------------------ | tee -a $LOGFILE
#read -t 30 dummy
  for host in $HOSTS
  do
    echo --$host:-- | tee -a $LOGFILE

     echo Running $SSH -x -l $USR $host date to verify SSH connectivity has been setup from local host to $host.  | tee -a $LOGFILE
     echo "IF YOU SEE ANY OTHER OUTPUT BESIDES THE OUTPUT OF THE DATE COMMAND OR IF YOU ARE PROMPTED FOR A PASSWORD HERE, IT MEANS SSH SETUP HAS NOT BEEN SUCCESSFUL. Please note that being prompted for a passphrase may be OK but being prompted for a password is ERROR." | tee -a $LOGFILE
     if [ $PASSPHRASE = "yes" ]
     then
       echo "The script will run SSH on the remote machine $host. The user may be prompted for a passphrase here in case the private key has been encrypted with a passphrase." | tee -a $LOGFILE
     fi
     $SSH -l $USR $host "/bin/sh -c date"  | tee -a $LOGFILE
echo ------------------------------------------------------------------------ | tee -a $LOGFILE
  done


if [ $EXHAUSTIVE_VERIFY = "true" ]
then
   for clienthost in $HOSTS
   do

      if [ $SHARED = "true" ]
      then
         REMOTESSH="$SSH -i .ssh/${IDENTITY}_${clienthost}"
      else
         REMOTESSH=$SSH
      fi

      for serverhost in  $HOSTS
      do
         echo ------------------------------------------------------------------------ | tee -a $LOGFILE
         echo Verifying SSH connectivity has been setup from $clienthost to $serverhost  | tee -a $LOGFILE
         echo ------------------------------------------------------------------------ | tee -a $LOGFILE
         echo "IF YOU SEE ANY OTHER OUTPUT BESIDES THE OUTPUT OF THE DATE COMMAND OR IF YOU ARE PROMPTED FOR A PASSWORD HERE, IT MEANS SSH SETUP HAS NOT BEEN SUCCESSFUL."  | tee -a $LOGFILE
         $SSH -l $USR $clienthost "$REMOTESSH $serverhost \"/bin/sh -c date\""  | tee -a $LOGFILE
         echo ------------------------------------------------------------------------ | tee -a $LOGFILE
      done 
       echo -Verification from $clienthost complete- | tee -a $LOGFILE
   done
else
   if [ $ADVANCED = "true" ]
   then
      if [ $SHARED = "true" ]
      then
         REMOTESSH="$SSH -i .ssh/${IDENTITY}_${firsthost}"
      else
         REMOTESSH=$SSH
      fi
     for host in $HOSTS
     do
         echo ------------------------------------------------------------------------ | tee -a $LOGFILE
        echo Verifying SSH connectivity has been setup from $firsthost to $host  | tee -a $LOGFILE
        echo "IF YOU SEE ANY OTHER OUTPUT BESIDES THE OUTPUT OF THE DATE COMMAND OR IF YOU ARE PROMPTED FOR A PASSWORD HERE, IT MEANS SSH SETUP HAS NOT BEEN SUCCESSFUL." | tee -a $LOGFILE
       $SSH -l $USR $firsthost "/bin/sh -c \"$REMOTESSH $host \\"/bin/sh -c date\\"\"" | tee -a $LOGFILE
         echo ------------------------------------------------------------------------ | tee -a $LOGFILE
    done
    echo -Verification from $clienthost complete- | tee -a $LOGFILE
  fi
fi
echo "SSH verification complete." | tee -a $LOGFILE

Posted by pat98

2015. 12. 30. 10:55 오라클

db 수동생성



DB 수동생성 작업

 

1. mkdir -p /oracle/ora920/dbs 생성

 

2. vi /oracle/ora920/dbs/initORA9.ora 생성

 

*.background_dump_dest='/oracle/admin/ORA9/bdump'
*.compatible='9.2.0.0.0'
*.control_files='/oradata/ORA9/control01.ctl','/oradata/ORA9/control02.ctl','/oradata/ORA9/control03.ctl'
*.core_dump_dest='/oracle/admin/ORA/cdump'
*.db_block_size=8192
*.db_cache_size=629145600
*.db_file_multiblock_read_count=16
*.db_name='ORA9'
*.instance_name='ORA9'
*.large_pool_size=314572800
*.processes=150
*.sga_max_size=1572864000
*.shared_pool_size=209715200
*.sort_area_size=524288
*.undo_management='AUTO'
*.undo_tablespace='UNDOTBS01'
*.user_dump_dest='/oracle/admin/ORA9/udump'

 

(11g 의 경우)

ORCL.__db_cache_size=213909504
ORCL.__java_pool_size=4194304
ORCL.__large_pool_size=4194304
ORCL.__oracle_base='/oracle/app/oracle '#ORACLE_BASE set from environment
ORCL.__pga_aggregate_target=251658240
ORCL.__sga_target=369098752
ORCL.__shared_io_pool_size=0
ORCL.__shared_pool_size=138412032
ORCL.__streams_pool_size=0
*.audit_file_dest='/oracle/app/oracle/admin/ORCL/adump'
*.audit_trail='db'
*.compatible='11.2.0.0.0'
*.control_files='/data/ORCL/control01.ctl','/data/ORCL/control02.ctl','/data/ORCL/control03.ctl'
*.db_block_size=8192
*.db_domain=''
*.db_name='ORCL'
*.diagnostic_dest='/oracle/app/oracle'
*.memory_target=620756992
*.open_cursors=300
*.processes=500
*.remote_login_passwordfile='EXCLUSIVE'
*.undo_tablespace='UNDOTBS1'

 

3. cd  /oracle/ora920/dbs

orapwd file=orapwORA9 password=manager entries=5

 

4.

mkdir -p /oracle/admin/ORA9/bdump
mkdir -p /oracle/admin/ORA9/cdump
mkdir -p /oracle/admin/ORA9/udump


5. cr_db.sql 화일 작성

SQL> startup nomount

@cr_db.sql


- 9i의 경우

CREATE DATABASE ORA9
MAXINSTANCES 1
MAXLOGHISTORY 1
MAXLOGFILES 10
MAXLOGMEMBERS 10
MAXDATAFILES 100
DATAFILE '/oradata/ORA9/system01.dbf' SIZE 500M REUSE AUTOEXTEND ON NEXT  10240K MAXSIZE UNLIMITED EXTENT MANAGEMENT LOCAL
DEFAULT TEMPORARY TABLESPACE TEMP TEMPFILE '/oradata/ORA9/temp01.dbf' SIZE 1000M REUSE AUTOEXTEND ON NEXT  640K MAXSIZE UNLIMITED
UNDO TABLESPACE "UNDOTBS01" DATAFILE '/oradata/ORA9/undotbs01.dbf' SIZE 1000M REUSE AUTOEXTEND ON NEXT  5120K MAXSIZE UNLIMITED
CHARACTER SET KO16KSC5601
NATIONAL CHARACTER SET AL16UTF16
LOGFILE GROUP 1 ('/oradata/ORA9/redo01.log') SIZE 102400K,
GROUP 2 ('/oradata/ORA9/redo02.log') SIZE 102400K,
GROUP 3 ('/oradata/ORA(/redo03.log') SIZE 102400K ;

 

- 10g의 경우

CREATE DATABASE ORCL
MAXINSTANCES 1
MAXLOGHISTORY 1
MAXLOGFILES 32
MAXLOGMEMBERS 5
MAXDATAFILES 2000
DATAFILE '/userc/oraprod/proddata/system01.dbf' SIZE 500M REUSE AUTOEXTEND ON NEXT  10240K MAXSIZE UNLIMITED EXTENT MANAGEMENT LOCAL
DEFAULT TEMPORARY TABLESPACE TEMP TEMPFILE '/oradata/temp01.dbf' SIZE 1000M REUSE AUTOEXTEND ON NEXT  640K MAXSIZE UNLIMITED
UNDO TABLESPACE "UNDOTBS01" DATAFILE '/oradata/undotbs01.dbf' SIZE 1000M REUSE AUTOEXTEND ON NEXT  5120K MAXSIZE UNLIMITED
SYSAUX DATAFILE '/oradata/sysauxo1.dbf' SIZE 1000M REUSE AUTOEXTEND ON NEXT  5120K MAXSIZE UNLIMITED
CHARACTER SET KO16KSC5601
NATIONAL CHARACTER SET AL16UTF16
LOGFILE GROUP 1 ('/oradata/redo01.log') SIZE 102400K,
GROUP 2 ('/oradata/redo02.log') SIZE 102400K,
GROUP 3 ('/oradata/redo03.log') SIZE 102400K ;

 

- 11g 의 경우

CREATE DATABASE ORCL
USER SYS IDENTIFIED BY manager
USER SYSTEM IDENTIFIED BY manager
LOGFILE GROUP 1 ('/data/ORCL/redo01.log') SIZE 100M,
GROUP 2 ('/data/ORCL/redo02.log') SIZE 100M,
GROUP 3 ('/data/ORCL/redo03.log') SIZE 100M
MAXLOGFILES 5
MAXLOGMEMBERS 5
MAXLOGHISTORY 1
MAXDATAFILES 1000
MAXINSTANCES 1
CHARACTER SET AL32UTF8
NATIONAL CHARACTER SET AL16UTF16
DATAFILE '/data/ORCL/system01.dbf' SIZE 1000M REUSE
EXTENT MANAGEMENT LOCAL
SYSAUX DATAFILE '/data/ORCL/sysaux01.dbf' SIZE 1000M REUSE
DEFAULT TEMPORARY TABLESPACE temp
TEMPFILE '/data/ORCL/temp01.dbf'
SIZE 1000M REUSE
UNDO TABLESPACE UNDOTBS1
DATAFILE '/data/ORCL/undotbs01.dbf'
SIZE 1000M REUSE AUTOEXTEND ON MAXSIZE UNLIMITED;

 

6.

sysdba 로 실행

@?/rdbms/admin/catalog.sql
@?/rdbms/admin/catproc.sql

system유저로 실행

@?/sqlplus/admin/pupbld.sql

 

Posted by pat98

 

12cR2 Beta 버전 맛보기를 하고 있습니다. Single 은 크게 달라진게 없는거 같고. RAC 쪽은 역시 구성이나 개념이 상당히 크게 가네요..

GDS, Domain Cluster, Sharding 등등 어려운게 많네요.

구경 좀 하고 기능등을 기록할 생각...

 

[oracle@12c:/home/oracle]# ss

SQL*Plus: Release 12.2.0.0.0 Beta on Tue Dec 29 10:18:08 2015

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


Connected to:
Oracle Database 12c Enterprise Edition Release 12.2.0.0.1 - 64bit Beta
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

SYS@TEST>

Posted by pat98

2015. 12. 9. 16:42 오라클

linux bonding 작업


Linux bonding 간단히 설정

 

1. service network stop

 

2. service NetworkManager stop

 

3. chkconfig NetworkManager off

 

4. vi /etc/modprobe.d/bonding.conf

alias bond0 bonding

 

5. cp /etc/sysconfig/network-scripts/ifcfg-eth0 를 ifcfg-bond0 로 복사하여 ifcfg-bond0을 수정한다.

 

6. ifcfg-eth0 정보를 복사한 후 HWADDR, UUID 정보를 지운다.

DEVICE=bond0
TYPE=Ethernet
ONBOOT=yes
NM_CONTROLLED=no
BOOTPROTO=static
IPADDR=XXX.XXX.XXX.XXX
NETMASK=255.255.255.XXX
GATEWAY=XXX.XXX.XXX.XXX
USERCTL=no
BONDING_OPTS="mode=1 miimon=100"

 

각각 /etc/sysconfig/network-scripts/ifcfg-eth0, ifcfg-eth1 실행후

 

7. 아래 세 줄을 다음과 같이 변경하고

ONBOOT=yes
NM_CONTROLLED=no
BOOTPROTO=none

 

아래 두줄 추가한다.

MASTER=bond0
SLAVE=yes

 

8. 재부팅이 여의치 않을 경우

# modprobe bonding 이후
# service network restart

 

9. 정상적으로 본딩이 걸렸는지 확인하는 방법은

# ifconfig | more //bonding이 올라오는지
# cat /proc/net/bonding/bond0 //master –slave 상태로 올라오는지 확인
#ifdown, #ifup 명 령 어 를 이 용하여 eth0과 eth1 장치를 내린 후에도 본딩 구성이 정상적으로 되는지 확인한다.

(ping을 Gateway로 던지 면서 , # cat /proc/net/bonding/bond0 상태 확인)

Posted by pat98

-12c 이전 varchar2 type 4000자 제한

 

SQL> create table tabela_teste(campo01
varchar2(4001));
*
ERROR at line 1:
ORA-00910 : specified length too long for its datatype

 

 

-12c 부터 32767 까지 지원함.

 

Extended Data Type

 

VARCHAR2  : 32767 bytes
NVARCHAR2 : 32767 bytes
RAW       : 32767 bytes

 

그냥 되지는 않고 아래와 같이 해 줘야 된다.

 

SQL> shutdown immediate

SQL> startup upgrade

SQL> alter system set max_string_size=extended;

SQL>@>/rdbms/admin/utl32k.sql

SQL> shutdown immediate

SQL> startup;

 

* 일단 한번 변경하면 switch back 은 안됨

 

기본은 STANDARD

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
max_string_size                      string      STANDARD

Posted by pat98

2015. 12. 4. 17:16 오라클

Oracle CLR services


 

Windows에 oracle 을 Default 로 설치하게 되면 listener.ora 화일에 다음과 같은 내용이 셋팅됨

 

Oracle Database Extensions for .NET 지원을 위해 환경변수가 위와 같이 지정됨

default SID 가 CLRExtProc 로 설정된다.

얘 때문에 Oracle CLR services  가 자동기동됨

 

CLR : Microsoft Common Language Runtime (CLR)

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

SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
      (SID_NAME = PLSExtProc)
      (ORACLE_HOME = C:\oracle\database_1)
      (PROGRAM = extproc)
    )
    (SID_DESC =
      (SID_NAME = CLRExtProc)
      (ORACLE_HOME = C:\oracle\database_1)
      (PROGRAM = extproc)
      (ENVS="EXTPROC_DLLS=ONLY:C:\oracle\database_1\bin\oraclr12.dll")
    )
  )
 
LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1))
    
    )
  )
-------------------------------------------------------------------------------------

 

그림을 보면 이해가 쉽다.

 

 

 

 

 

Posted by pat98

ASM에서 다음과 같은 view 들을 참조하여 정보를 확인할수 있다.

 

V$ASM_ALIAS
V$ASM_CLIENT
V$ASM_DISK
V$ASM_DISK_IOSTAT
V$ASM_DISK_STAT
V$ASM_DISKGROUP
V$ASM_DISKGROUP_STAT
V$ASM_FILE
V$ASM_TEMPLATE
V$ASM_USER
V$ASM_USERGROUP
V$ASM_USERGROUP_MEMBER

 

1. DISK GROUP 속성보기 (V$ASM_ATTRIBUTE)

 

SQL> SELECT dg.name AS diskgroup, SUBSTR(a.name,1,18) AS name,
     SUBSTR(a.value,1,24) AS value, read_only FROM V$ASM_DISKGROUP dg,
     V$ASM_ATTRIBUTE a WHERE dg.name = 'DATA'
     AND dg.group_number = a.group_number;

 

2. DISK GROUP 호환성 보기 (V$ASM_DISKGROUP)

 

SQL> SELECT name AS diskgroup, compatibility AS asm_compat,
     database_compatibility AS db_compat FROM V$ASM_DISKGROUP;

 

3. DISK GROUP 내 디스크 보기 (V$ASM_DISK)

 

SQL> SELECT SUBSTR(d.name,1,16) AS asmdisk, d.mount_status, d.state,
     dg.name AS diskgroup FROM V$ASM_DISKGROUP dg, V$ASM_DISK d
     WHERE dg.group_number = d.group_number;

 

4. DISK GROUP Client 보기 (V$ASM_CLIENT)

 

SQL> SELECT dg.name AS diskgroup, SUBSTR(c.instance_name,1,12) AS instance,
    SUBSTR(c.db_name,1,12) AS dbname, SUBSTR(c.SOFTWARE_VERSION,1,12) AS software,
    SUBSTR(c.COMPATIBLE_VERSION,1,12) AS compatible
    FROM V$ASM_DISKGROUP dg, V$ASM_CLIENT c 
    WHERE dg.group_number = c.group_number;

 

5. ASM FILE Access Control 정보 보기 (V$ASM_USER)

 

SQL> SELECT dg.name AS diskgroup, u.group_number, u.user_number, u.os_id, u.os_name
     FROM V$ASM_DISKGROUP dg, V$ASM_USER u
     WHERE dg.group_number = u.group_number AND dg.name = 'DATA';

 

6. ASM FILE Access Control 정보 보기 (V$ASM_USERGROUP)

 

SQL>  SELECT dg.name AS diskgroup, ug.group_number, ug.owner_number, u.os_name,
      ug.usergroup_number, ug.name FROM V$ASM_DISKGROUP dg, V$ASM_USER u, V$ASM_USERGROUP ug
      WHERE dg.group_number = ug.group_number AND dg.name = 'DATA'
      AND ug.owner_number = u.user_number;

 

7. ASM FILE Access Control 정보 보기 (V$ASM_USERGROUP_MEMBER)


SQL> SELECT dg.name AS diskgroup, um.group_number, um.member_number, u.os_name,
     um.usergroup_number, ug.name FROM V$ASM_DISKGROUP dg, V$ASM_USER u, V$ASM_USERGROUP_MEMBER um,
     V$ASM_USERGROUP ug WHERE dg.group_number = um.group_number AND
     dg.group_number = ug.group_number AND dg.group_number = u.group_number AND dg.name = 'DATA'
     AND um.member_number = u.user_number AND um.usergroup_number = ug.usergroup_number;

 

8. ASM FILE Access Control 정보 보기 (V$ASM_FILE)

 

SQL> SELECT dg.name AS diskgroup, a.name, f.permissions, f.user_number, u.os_name,
     f.usergroup_number, ug.NAME FROM V$ASM_DISKGROUP dg, V$ASM_USER u, V$ASM_USERGROUP ug,
     V$ASM_FILE f, V$ASM_ALIAS a WHERE dg.group_number = f.group_number AND
     dg.group_number = u.group_number AND dg.group_number = ug.group_number AND
     dg.name = 'FRA' AND f.usergroup_number = ug.usergroup_number AND f.user_number = u.user_number
     AND f.file_number = a.file_number;

 

9. Intelligent Data Placement 정보 (V$ASM_FILE)

 

SQL> SELECT dg.name AS diskgroup, f.file_number, f.primary_region, f.mirror_region, f.hot_reads,
       f.hot_writes,  f.cold_reads, f.cold_writes
       FROM V$ASM_DISKGROUP dg, V$ASM_FILE f
       WHERE dg.group_number = f.group_number and dg.name = 'DATA';

 

10. Intelligent Data Placement 정보 (V$ASM_TEMPLATE)

 

SQL> SELECT dg.name AS diskgroup, t.name, t.stripe, t.redundancy, t.primary_region, t.mirror_region
       FROM V$ASM_DISKGROUP dg, V$ASM_TEMPLATE t
       WHERE dg.group_number = t.group_number and dg.name = 'DATA' ORDER BY t.name;

Posted by pat98

2015. 12. 4. 15:02 오라클

12c silent mode 설치


설치이미치를 압축해제하고..한방에 끝

파란색은 12c에서 새로 추가된 항목

나머지 빨간색은 사용환경에 맞게 수정하여 설치하도록 하자.

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

 

Unix,Linux 인 경우

 

response file은 해당위치에 sample이 있다.

<11gR2 Disk>/database/response/db_install.rsp

 

but response file 별도로 없는 경우.

 

./runInstaller -silent -debug -force \
FROM_LOCATION=/tmp/12c/database/Disk1/stage/products.xml \
oracle.install.option=INSTALL_DB_SWONLY \
UNIX_GROUP_NAME=oinstall \
INVENTORY_LOCATION=/u01/app/oraInventory \
ORACLE_HOME=/u01/app/oracle/product/12.1.0/dbhome_1 \
ORACLE_HOME_NAME="OraDb12c_Home1" \
ORACLE_BASE=/u01/app/oracle \
oracle.install.db.InstallEdition=EE \
oracle.install.db.DBA_GROUP=dba \
oracle.install.db.OPER_GROUP=dba \
oracle.install.db.BACKUPDBA_GROUP=dba \
oracle.install.db.DGDBA_GROUP=dba \
oracle.install.db.KMDBA_GROUP=dba \

DECLINE_SECURITY_UPDATES=true

Posted by pat98

12.1.0.2 의 네트웍 기능 중 새로운 옵션 하나 소개

 

1.가상화 환경에서 네트웍 Component 도 가상화 되는 경우가 있음.

 

2.가끔식 네트웍 failure 가 guest 환경에 반영안되는 경우가 있음

(guest의 OS 명령어는 네트웍 failure를 탐지하는데 실패했음에도  virtual NIC 가 up 인 상태로 남아있음

 

 

3. 결과적으로 이에대한 수정작업이 이루어 지지 못함

 

4. 그래서 나온것이 ping target 임

 

5. ping target 은 public network에만 필요함 (prviate 은 heatbeat check 를 계속하기 때문에)

 

6. 예를 들어 client 와 서버사이에 switch 를 지정하여 pingtarget 을 이용하게 할수 있음.

 

 

사용 예)

 

[GRID]> srvctl modify network -k 1 -pingtarget “<UsefulTargetIP(s)>"
[GRID]> exit
exit

[GRID]> srvctl config network -k 1
Network 1 exists
Subnet IPv4: 10.1.1.0/255.255.255.0/eth0, static
Subnet IPv6:
Ping Targets: <UsefulTargetIP(s)>
Network is enabled
Network is individually enabled on nodes:
Network is individually disabled on nodes:

 

Posted by pat98

2015. 12. 3. 01:35 오라클

oracle flashback 설정


- flashback database 여부 확인

SQL> select flashback_on from v$database;

FLASHBACK_ON
------------------
NO  

 

-flashback 영역에 저장되는 화일

Current control file
Online redo logs
Archived redo logs
Flashback logs
Control file autobackups
Datafile 과 control file copies
Backup pieces
Foreign archived redo log ( LogMiner세션을 위해 logical standby 데이터베이스에 의해 수신된 archived redo log.)

 

LOG_ARCHIVE_DEST 와 LOG_ARCHIVE_DUPLEX_DEST 파라미터를 리두 로그 아카이브 위치로 지정하는 데 사용할 수는 없다. 플래쉬 복구 영역을 구성하는 경우라면 언제나 LOG_ARCHIVE_DEST_n 을 사용해야 한다.

만약 복구 영역을 생성하고 다른 로컬 아카이빙 위치를 설정하지 않았다면, LOG_ARCHIVE_DEST_10 는 묵시적으로 USE_DB_RECOVERY_FILE_DEST 로 설정된다.

 

- flashback database off

SQL> ALTER DATABASE FLASHBACK OFF;

ALTER SYSTEM SET DB_RECOVERY_FILE_DEST='';

ALTER SYSTEM SET DB_RECOVERY_FILE_DEST_SIZE='';

 

- flashback database on

SQL> shutdown immediate;
SQL> startup mount;
SQL> alter database flashback on;
SQL> alter database open;

 

- 위치지정 또는 변경

SQL> alter system set db_recovery_file_dest='+<FRA Diskgroup>' SCOPE=spfile;
SQL> ALTER SYSTEM SET DB_RECOVERY_FILE_DEST='+disk1' SCOPE=BOTH SID='*';

SQL> alter system set db_recovery_file_dest_size=100G SCOPE=spfile;

아카이브로그 백업을 위해 바람직한 방법

SQL> alter system set log_archive_dest_10='LOCATION=USE_DB_RECOVERY_FILE_DEST' scope=both;


- flashback log의 크기확인(archive & rman backup 제외)

SQL> select estimated_flashback_size/1024/1024/1024 "EST_FLASHBACK_SIZE(GB)" from v$flashback_database_log;

 

- fast recovery area 사용량 확인

SQL> select * from v$flash_recoveyr_area_usage;
SQL> Select file_type, percent_space_used as used,percent_space_reclaimable as reclaimable,number_of_files as "number" from v$flash_recovery_area_usage;
SQL> select name, space_limit as Total_size ,space_used as Used,SPACE_RECLAIMABLE as reclaimable ,NUMBER_OF_FILES as "number" from  V$RECOVERY_FILE_DEST;

 

버전별로 view가 틀려서 헷갈릴수 있다.

From >= 10gR2 and <= 11gR1: V$FLASH_RECOVERY_AREA_USAGE
From              >= 11gR2       : V$RECOVERY_AREA_USAGE

 

- reclaimable space 확인

SELECT substr(name, 1, 30) name,
            space_limit               AS quota,
            space_used                AS used,
            space_reclaimable         AS reclaimable,
            number_of_files           AS files
       FROM V$RECOVERY_FILE_DEST ;

 

 

- FRA가 Full 났을시 조치 정리

 

1. DB_RECOVERY_FILE_DEST_SIZE 의 크기 확장

SQL> alter system set db_recovery_file_dest_size=xG SCOPE=BOTH;

 

2. rman 으로 영역을 백업

RMAN>BACKUP RECOVERY AREA;

 

3. OS에서 지웠다면 RMAN에서 DELETE 수행

RMAN>CROSSCHECK BACKUP;
RMAN>CROSSCHECK ARCHIVELOG ALL;

RMAN>Delete expired backup;
RMAN>Delete expired archivelog all;
RMAN>Delete force obsolete;

 

4. 복원지점이 필요없다면 삭제
SQL> select * from v$restore_point;
SQL>Drop restore point <restore_point_name>;

 

5. 충분한 용량을 가지고 있지 않다면

SQL>Alter database FLASHBACK OFF;

 

6. 백업 retenton 정책을 변경

RMAN>CONFIGURE RETENTION POLICY TO RECOVERY WINDOW OF 7 DAYS;

 

Posted by pat98

01-25 18:32
Flag Counter
Yesterday
Today
Total

글 보관함

최근에 올라온 글

달력

 « |  » 2025.1
1 2 3 4
5 6 7 8 9 10 11
12 13 14 15 16 17 18
19 20 21 22 23 24 25
26 27 28 29 30 31

최근에 달린 댓글