#!/bin/sh
echo_red(){
  echo -e "\033[31;5;1m${1}\033[0m"
}
echo_yellow(){
  echo -e "\033[33;5;1m${1}\033[0m"
}
echo_blue(){
  echo -e "\033[34;5;1m${1}\033[0m"
}
echo_green(){
  echo -e "\033[32;5;1m${1}\033[0m"
}


sub_0_check_evar() {
cat /home/oracle/.bash_profile
}

sub_1_copy_datafile() {
mkdir -p /u01/app/oracle/oradata/orcl
export v_dir=/u01/app/oracle/oradata/orcl

cd /root/Desktop/Transportation
cp *EXAMPLE*  $v_dir/example01.dbf
cp *SYSAUX*   $v_dir/sysaux01.dbf
cp *SYSTEM*   $v_dir/system01.dbf
cp *UNDOTBS1* $v_dir/undotbs01.dbf
cp *USERS*    $v_dir/users01.dbf
chown -R oracle:oinstall /u01/app/oracle/oradata/orcl
}

sub_2_create_initorcl_admin() {
export v_dbs=/u01/app/oracle/product/10.2.0/db_1/dbs
export v_orcl=/u01/app/oracle/oradata/orcl
export v_base=/u01/app/oracle
export v_admin=/u01/app/oracle/admin/orcl
cat > initorcl.ora <<EOF
control_files              = "$v_orcl/control01.ctl","$v_orcl/control02.ctl","$v_orcl/control03.ctl"
db_recovery_file_dest      = "$v_base/flash_recovery_area"
db_recovery_file_dest_size = 2147483648
audit_file_dest            = "$v_admin/adump"
background_dump_dest       = "$v_admin/bdump"
user_dump_dest             = "$v_admin/udump"
core_dump_dest             = "$v_admin/cdump"
db_name                    = "orcl"

__shared_pool_size       = 163577856
__large_pool_size        = 4194304
__java_pool_size         = 4194304
__streams_pool_size      = 0
__db_cache_size          = 436207616
remote_login_passwordfile= "EXCLUSIVE"
db_domain                = ""
dispatchers              = "(PROTOCOL=TCP) (SERVICE=orclXDB)"

processes                = 150
sga_target               = 612368384
db_block_size            = 8192
compatible               = "10.2.0.1.0"
db_file_multiblock_read_count= 16
undo_management          = "AUTO"
undo_tablespace          = "UNDOTBS1"
job_queue_processes      = 10
open_cursors             = 300
pga_aggregate_target     = 203423744
EOF
mv initorcl.ora $v_dbs/
chown oracle:oinstall $v_dbs/*

mkdir -p $v_admin/adump
mkdir -p $v_admin/bdump
mkdir -p $v_admin/udump
mkdir -p $v_admin/cdump
chown -R oracle:oinstall $v_base/*
}

sub_3_mount_open_db() {
cat > ctl_migration.sql << EOF
SHUTDOWN ABORT
STARTUP NOMOUNT PFILE='/u01/app/oracle/product/10.2.0/db_1/dbs/initorcl.ora'
CREATE SPFILE FROM PFILE = '/u01/app/oracle/product/10.2.0/db_1/dbs/initorcl.ora';
STARTUP FORCE NOMOUNT
CREATE CONTROLFILE REUSE SET DATABASE "orcl" RESETLOGS  NOARCHIVELOG
    MAXLOGFILES 16
    MAXLOGMEMBERS 3
    MAXDATAFILES 100
    MAXINSTANCES 8
    MAXLOGHISTORY 292
LOGFILE
  GROUP 1 '/u01/app/oracle/oradata/orcl/redo01.log'  SIZE 50M,
  GROUP 2 '/u01/app/oracle/oradata/orcl/redo02.log'  SIZE 50M,
  GROUP 3 '/u01/app/oracle/oradata/orcl/redo03.log'  SIZE 50M
DATAFILE
  '/u01/app/oracle/oradata/orcl/system01.dbf',
  '/u01/app/oracle/oradata/orcl/sysaux01.dbf',
  '/u01/app/oracle/oradata/orcl/undotbs01.dbf',
  '/u01/app/oracle/oradata/orcl/example01.dbf',
  '/u01/app/oracle/oradata/orcl/users01.dbf'
CHARACTER SET ZHS16GBK;
EOF
mv ctl_migration.sql /home/oracle/
chmod +x /home/oracle/ctl_migration.sql
chown oracle:oinstall /home/oracle/*

su - oracle -c "sqlplus /nolog"<<EOF
conn /as sysdba;
@/home/oracle/ctl_migration.sql
host sleep 3
alter database open resetlogs;
select open_mode from v\$database;
quit
EOF
}

sub_4_recomplie_dictionary(){
su - oracle -c "sqlplus /nolog"<<EOF
conn /as sysdba
SHUTDOWN IMMEDIATE
STARTUP UPGRADE
@?/rdbms/admin/utlirp.sql
SHUTDOWN IMMEDIATE
STARTUP
@?/rdbms/admin/utlrp.sql
select count(*) "errors during recomplilation" from utl_recomp_errors;
alter tablespace temp add tempfile '/u01/app/oracle/oradata/orcl/temp01.dbf' size 50M;
quit
EOF
}


sub_5_detect() {
echo ""
echo ""
echo "Now detecting your database as sysdba"
echo "Assure you database is health."
su - oracle -c "sqlplus /nolog" <<!
conn / as sysdba;
set echo off
set feedback off
set serveroutput on
set linesize 110
------------------------------------------------------------
exec dbms_output.put_line(chr(10));
exec dbms_output.put_line('@#@#@#@#@#@#@#@#@#@#@#@#@#@#@#@#@#@#@#@#@#@#@#');
exec dbms_output.put_line('Database status is here:');
exec dbms_output.put_line('@#@#@#@#@#@#@#@#@#@#@#@#@#@#@#@#@#@#@#@#@#@#@#');
select open_mode from v\$database;
-------------------------------------------------------------
exec dbms_output.put_line(chr(10));
exec dbms_output.put_line('@#@#@#@#@#@#@#@#@#@#@#@#@#@#@#@#@#@#@#@#@#@#@#');
exec dbms_output.put_line('Archive info is here:');
exec dbms_output.put_line('@#@#@#@#@#@#@#@#@#@#@#@#@#@#@#@#@#@#@#@#@#@#@#');
archive log list
-------------------------------------------------------------
exec dbms_output.put_line(chr(10));
exec dbms_output.put_line('@#@#@#@#@#@#@#@#@#@#@#@#@#@#@#@#@#@#@#@#@#@#@#');
exec dbms_output.put_line('Logical and Physical structure info is here:');
exec dbms_output.put_line('Tablespace');
exec dbms_output.put_line('Datafile');
exec dbms_output.put_line('Controlfile');
exec dbms_output.put_line('Logfile');
exec dbms_output.put_line('Tempfile');
exec dbms_output.put_line('@#@#@#@#@#@#@#@#@#@#@#@#@#@#@#@#@#@#@#@#@#@#@#');
col name format a65 trunc
col status format a15 trunc
col tablespace_name format a30 trunc
select tablespace_name,status from dba_tablespaces;
select FILE#,CHECKPOINT_CHANGE# from v\$datafile;
select FILE#,name,status from v\$datafile;
select name from v\$controlfile;
col memeber format a30 trunc
select member from v\$logfile;
select name from v\$tempfile;
exec dbms_output.put_line(chr(10));
set heading off
show parameter cluster_database;
show parameter control_files;
show parameter spfile;
exec dbms_output.put_line(chr(10));
alter system flush buffer_cache;
exit
!
su - oracle -c "rman target /"<<!
list incarnation of database;
list backup of database;
list copy;
quit
!
}

 


v_check=` id -u `
if [ $v_check == 0 ]
then
  echo_red "login by root, to be continue...... "
else
  echo_red "please login by root to run this shell!!"
  exit
fi

case $1 in
0) sub_0_check_evar
;;
1) sub_1_copy_datafile
;;
2) sub_2_create_initorcl_admin
;;
3) sub_3_mount_open_db
;;
4) sub_4_recomplie_dictionary
;;
5) sub_5_detect > /tmp/tmp01
   firefox        /tmp/tmp01
;;
*) echo_red "please input {0|1|2|3|4}"
   echo_red "0=> sub_0_check_evar"
   echo_red "1=> sub_1_copy_datafile"
   echo_red "2=> sub_2_create_initorcl_admin"
   echo_red "3=> sub_3_mount_open_db"
   echo_red "4=> sub_4_recomplie_dictionary"
   echo_red "5=> sub_5_detect"
;;
esac