MISTECH 技術手抄本

C:\>sqlplus /nolog

SQL> connect / as sysdba

C:\>rman catalog RMAN_USER/RMAN_PWD@rmandb

RMAN> create catalog;

recovery catalog created

C:\>rman catalog RMAN_USER/RMAN_PWD@rmandb

RMAN> connect target sys/oracle@orcl

己連線到目標資料庫: ORCL (DBID=1357434876)

[oracle@oradb12clinux ~]$ echo $ORACLE_SID
orcl
[oracle@oradb12clinux ~]$ rman catalog RMAN_USER/RMAN_PWD@rmandb

RMAN> connect target;
connected to target database: ORCL (DBID=1357434876)

$ sqlplus / as sysdba

#查看現在資料庫的備份狀況
SQL> archive log list;
Database log mode           No Archive Mode
Automatic archival           Disabled
Archive destination           USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence     30
Current log sequence           32

#查看 archive log 預設備份的目錄
SQL> show parameter DB_RECOVERY_FILE_DEST

NAME                                       TYPE           VALUE
------------------------------------ -----------     ------------------------------
db_recovery_file_dest            string           /u01/app/oracle/fast_recovery_area
db_recovery_file_dest_size    big integer  4800M

#為了方便觀察, 改變備份目錄, 並關閉資料庫

SQL> alter system set db_recovery_file_dest='/u01/arclog' scope=both;
SQL> shutdown immediate;

# 設定資料庫的模式為 archive log mode
SQL> startup mount;
SQL> alter database archivelog;
SQL> alter database open;

SQL> archive log list;
Database log mode           Archive Mode
Automatic archival             Enabled
Archive destination           USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence     30
Next log sequence to archive   32
Current log sequence           32

#強制產生 archive log file
SQL> alter system switch logfile;
System altered.

#到 OS 去查看是否有 archive log file 產生
$ pwd
/u01/arclog/ORCL/archivelog/2013_11_07
$ ls -l
-rw-r-----. 1 oracle oinstall 25397248 Nov  7 13:44 o1_mf_1_32_97pb5qnt_.arc

$ rman catalog RMAN_USER/RMAN_PWD@rmandb
RMAN> connect target;
connected to target database: ORCL (DBID=1357434876)

# 先將之前的 script 刪除 (若有的話)
RMAN> delete script fullbackup;
deleted script: fullbackup

# 建立一個資料庫全備份的 script (含control file & spfile)
RMAN> create script fullbackup {
allocate channel d1 type disk;
backup
incremental level 0
format '/u01/backdb/%d_%s_%p'
filesperset 5
(database include current controlfile);
sql 'alter system archive log current';
release channel d1;}

RMAN> connect target;
connected to target database: ORCL (DBID=1357434876)

RMAN> print script fullbackup;

或

$ sqlplus RMAN_USER/RMAN_PWD@rmandb

SQL> select * from RC_STORED_SCRIPT;
SQL> SQL> select * from RC_STORED_SCRIPT_LINE;

在資料庫 ORACLE_SID：xe 是11g 看到的訊息：

database registered in recovery catalog
starting full resync of recovery catalog
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03008: error while performing automatic resync of recovery catalog
RMAN-20036: invalid record order

在資料庫 ORACLE_SID:orcl 是 12c 看到的訊息：

PL/SQL package RMAN_XE.DBMS_RCVCAT version 11.02.00.02 in RCVCAT database is too old
PL/SQL package RMAN_XE.DBMS_RCVCAT version 11.02.00.02 in RCVCAT database is too old
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of list command at 11/01/2013 14:38:44
RMAN-06429: RCVCAT database is not compatible with this version of RMAN

#下面的設定是連線到遠端：192.168.1.1 的資料庫

ORCL =
　　(DESCRIPTION =
　　(ADDRESS_LIST =　　
　　#下面是這個 TNSNAME 對應的資料庫主機，端口，協定　　
　 　(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.1)(PORT = 1521))　　
　　)　　
　　(CONNECT_DATA =

　　#使用專用服務器模式去連接需要跟服務器的模式匹配，如果沒有就根據服務器的模式　　

　　#自動調節
　　(SERVER = DEDICATED)　

　　#對應service_name，SQLPLUS>show parameter service_name;

　　#進行查看　　
　　(SERVICE_NAME = orcl)　　
　　 )　　
　　)

#下面設定是連到本機的資料庫

XE =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = localhost )(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = XE)
    )
  )

[oracle@oradb12clinux ~]$ echo $ORACLE_SID
orcl
[oracle@oradb12clinux ~]$ sqlplus / as sysdba

SQL> show con_name

CON_NAME
------------------------------
CDB$ROOT

SQL> create user abcd identified by abcd;
create user abcd identified by abcd
            *
ERROR at line 1:
ORA-65096: invalid common user or role name

SQL> create user test identified by test container=current;
create user test identified by test container=current
                               *
ERROR at line 1:
ORA-65049: creation of local user or role is not allowed in CDB$ROOT

SQL> create user c##abc identified by abc;

User created.

SQL> drop user c##abc;

User dropped

SQL> alter session set container=PDBORCL;

Session altered.

SQL> show con_name;

CON_NAME
------------------------------
PDBORCL

SQL> CREATE USER RMAN_XE IDENTIFIED BY RMAN_PWD
ACCOUNT UNLOCK;
CREATE USER RMAN_XE IDENTIFIED BY RMAN_PWD
                                  *
ERROR at line 1:
ORA-01109: database not open

SQL> SHOW PDBS;

SQL> show pdbs;

 CON_ID CON_NAME              OPEN MODE  RESTRICTED
---------- ------------------------ ----------------- ----------
     2        PDB$SEED              READ ONLY      NO
     3        PDBORCL                MOUNTED

SQL> ALTER PLUGGABLE DATABASE ALL OPEN;

Pluggable database altered.

SQL> show pdbs;

CON_ID CON_NAME             OPEN MODE  RESTRICTED
---------- ------------------------ ---------------- ----------
     2       PDB$SEED              READ ONLY    NO
     3       PDBORCL                READ WRITE NO

[oracle@oradb12clinux ~]$ sqlplus / as sysdba

SQL> ALTER PLUGGABLE DATABASE ALL OPEN;

SQL> alter session set container=PDBORCL;

SQL> show pdbs;

CON_ID CON_NAME             OPEN MODE  RESTRICTED
---------- ------------------------ ---------------- ----------
     3       PDBORCL                READ WRITE NO

SQL> CREATE USER RMAN_XE IDENTIFIED BY RMAN_PWD;

User created.

SQL> alter session set container=PDBORCL;

Session altered.

SQL> select username from dba_users where username like '%RMAN%';

USERNAME
--------------------------------------------------------------------------------
RMAN_XE

#!/bin/sh
# chkconfig: 345 99 10
# description: Oracle auto start-stop script.
#
# Set ORA_OWNER to the user id of the owner of the 
# Oracle database software.

ORA_OWNER=oracle

case "$1" in
    'start')
        # Start the Oracle databases:
        # The following command assumes that the oracle login 
        # will not prompt the user for any values
        su $ORA_OWNER -c "/home/oracle/scripts/startup.sh >> /home/oracle/scripts/startup_shutdown.log 2>&1"
        touch /var/lock/subsys/dbora
        ;;
    'stop')
        # Stop the Oracle databases:
        # The following command assumes that the oracle login 
        # will not prompt the user for any values
        su $ORA_OWNER -c "/home/oracle/scripts/shutdown.sh >> /home/oracle/scripts/startup_shutdown.log 2>&1"
        rm -f /var/lock/subsys/dbora
        ;;
esac

# service dbora start
# service dbora stop

groupadd -g 54321 oinstall
groupadd -g 54322 dba
groupadd -g 54323 oper
#groupadd -g 54324 backupdba
#groupadd -g 54325 dgdba
#groupadd -g 54326 kmdba
#groupadd -g 54327 asmdba
#groupadd -g 54328 asmoper
#groupadd -g 54329 asmadmin

useradd -u 54321 -g oinstall -G dba,oper oracle

再設定 oracle 帳號的密碼：

passwd oracle

127.0.0.1       localhost.localdomain  localhost
192.168.0.210   oradb12clinux.globeunion.com    oradb12clinux

fs.file-max = 6815744
kernel.sem = 250 32000 100 128
kernel.shmmni = 4096
kernel.shmall = 1073741824
kernel.shmmax = 4398046511104
net.core.rmem_default = 262144
net.core.rmem_max = 4194304
net.core.wmem_default = 262144
net.core.wmem_max = 1048576
fs.aio-max-nr = 1048576
net.ipv4.ip_local_port_range = 9000 65500

oracle   soft   nofile    1024
oracle   hard   nofile    65536
oracle   soft   nproc    2047
oracle   hard   nproc    16384
oracle   soft   stack    10240
oracle   hard   stack    32768

# From Public Yum or ULN
yum install binutils -y
yum install compat-libcap1 -y
yum install compat-libstdc++-33 -y
yum install compat-libstdc++-33.i686 -y
yum install gcc -y
yum install gcc-c++ -y
yum install glibc -y
yum install glibc.i686 -y
yum install glibc-devel -y
yum install glibc-devel.i686 -y
yum install ksh -y
yum install libgcc -y
yum install libgcc.i686 -y
yum install libstdc++ -y
yum install libstdc++.i686 -y
yum install libstdc++-devel -y
yum install libstdc++-devel.i686 -y
yum install libaio -y
yum install libaio.i686 -y
yum install libaio-devel -y
yum install libaio-devel.i686 -y
yum install libXext -y
yum install libXext.i686 -y
yum install libXtst -y
yum install libXtst.i686 -y
yum install libX11 -y
yum install libX11.i686 -y
yum install libXau -y
yum install libXau.i686 -y
yum install libxcb -y
yum install libxcb.i686 -y
yum install libXi -y
yum install libXi.i686 -y
yum install make -y
yum install sysstat -y
yum install unixODBC -y
yum install unixODBC-devel -y

# Change this
*          soft    nproc    1024

# To this
* - nproc 16384

SELINUX=permissive

# service iptables stop 
# chkconfig iptables off

mkdir -p /u01/app/oracle/product/12.1.0/db_1
chown -R oracle:oinstall /u01
chmod -R 775 /u01

# Oracle Settings
export TMP=/tmp
export TMPDIR=$TMP

export ORACLE_HOSTNAME=oradb12clinux.globeunion.com
export ORACLE_UNQNAME=orcl
export ORACLE_BASE=/u01/app/oracle
export ORACLE_HOME=$ORACLE_BASE/product/12.1.0/db_1
export ORACLE_SID=orcl

export PATH=/usr/sbin:$PATH
export PATH=$ORACLE_HOME/bin:$PATH

export LD_LIBRARY_PATH=$ORACLE_HOME/lib:/lib:/usr/lib
export CLASSPATH=$ORACLE_HOME/jlib:$ORACLE_HOME/rdbms/jlib

[oracle@oradb12clinux database]$ su -
[root@oradb12clinux ~]# /u01/app/oraInventory/orainstRoot.sh
[root@oradb12clinux ~]# /u01/app/oracle/product/12.1.0/db_1/root.sh

SQL> SELECT dbms_xdb_config.gethttpsport FROM dual;

GETHTTPSPORT
------------
        5500

SQL> EXEC dbms_xdb_config.sethttpsport(5500);

PL/SQL procedure successfully completed.

SQL>

[oracle@oradb12clinux ~]$ sqlplus /nolog

SQL> connect /as sysdba
Connected to an idle instance.
SQL> startup
ORACLE instance started.

Total System Global Area 1653518336 bytes
Fixed Size            2289016 bytes
Variable Size         1073742472 bytes
Database Buffers      570425344 bytes
Redo Buffers            7061504 bytes
Database mounted.
Database opened.

SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.

[oracle@oradb12clinux ~]$ lsnrctl start

LSNRCTL for Linux: Version 12.1.0.1.0 - Production on 24-OCT-2013 13:18:14

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

Starting /u01/app/oracle/product/12.1.0/db_1/bin/tnslsnr: please wait...

TNSLSNR for Linux: Version 12.1.0.1.0 - Production
System parameter file is /u01/app/oracle/product/12.1.0/db_1/network/admin/listener.ora
Log messages written to /u01/app/oracle/diag/tnslsnr/oradb12clinux/listener/alert/log.xml
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=oradb12clinux)(PORT=1521)))

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC1521)))
STATUS of the LISTENER
------------------------
Alias                     LISTENER
Version                   TNSLSNR for Linux: Version 12.1.0.1.0 - Production
Start Date                24-OCT-2013 13:18:14
Uptime                    0 days 0 hr. 0 min. 0 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   /u01/app/oracle/product/12.1.0/db_1/network/admin/listener.ora
Listener Log File         /u01/app/oracle/diag/tnslsnr/oradb12clinux/listener/alert/log.xml
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=oradb12clinux)(PORT=1521)))
The listener supports no services
The command completed successfully

[oracle@oradb12clinux ~]$ tnsping orcl

TNS Ping Utility for Linux: Version 12.1.0.1.0 - Production on 24-OCT-2013 13:20:19

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

Used parameter files:
/u01/app/oracle/product/12.1.0/db_1/network/admin/sqlnet.ora


Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = oradb12clinux.globeunion.com)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = orcl)))
OK (0 msec)

[root@oradb12clinux ~]# df -h
Filesystem                                             Size  Used Avail Use% Mounted on
/dev/mapper/vg0-lv_root                      9.9G  4.1G  5.4G  43%    /
tmpfs                                                    2.0G  176K  2.0G   1%    /dev/shm
/dev/sda1                                            194M   51M  134M  28%  /boot
/dev/sda2                                            811M   17M  753M   3%  /home
/dev/mapper/vg0-lv_opt                      7.9G  146M  7.4G   2%   /opt
/dev/mapper/vg0-lv_u01                       15G  166M   14G   2%  /u01

[root@oradb12clinux ~]# lvextend -L +100M '/dev/vg0/lv_opt'
  Extending logical volume lv_opt to 8.10 GiB
  Logical volume lv_opt successfully resized

ot@oradb12clinux oradb12c]# lvextend -L +4096 '/dev/vg0/lv_root'
  Extending logical volume lv_root to 14.00 GiB
  Logical volume lv_root successfully resized


[root@oradb12clinux oradb12c]# lvscan
  ACTIVE            '/dev/vg0/lv_u01' [15.00 GiB] inherit
  ACTIVE            '/dev/vg0/lv_root' [14.00 GiB] inherit
  ACTIVE            '/dev/vg0/lv_swap' [4.00 GiB] inherit
  ACTIVE            '/dev/vg0/lv_opt' [8.10 GiB] inherit

[root@oradb12clinux oradb12c]# df -h
Filesystem                                             Size  Used Avail Use% Mounted on
/dev/mapper/vg0-lv_root                      9.9G  8.8G  644M  94% /
tmpfs                                                     2.0G  264K  2.0G   1% /dev/shm
/dev/sda1                                             194M   51M  134M  28% /boot
/dev/sda2                                             811M   17M  753M   3% /home
/dev/mapper/vg0-lv_opt                       7.9G  146M  7.4G   2% /opt
/dev/mapper/vg0-lv_u01                      15G  166M   14G   2% /u01

[root@oradb12clinux oradb12c]# resize2fs /dev/vg0/lv_root
resize2fs 1.41.12 (17-May-2010)
Filesystem at /dev/vg0/lv_root is mounted on /; on-line resizing required
old desc_blocks = 1, new_desc_blocks = 1
Performing an on-line resize of /dev/vg0/lv_root to 3670016 (4k) blocks.
The filesystem on /dev/vg0/lv_root is now 3670016 blocks long.

[root@oradb12clinux oradb12c]# resize2fs /dev/vg0/lv_opt
resize2fs 1.41.12 (17-May-2010)
Filesystem at /dev/vg0/lv_opt is mounted on /opt; on-line resizing required
old desc_blocks = 1, new_desc_blocks = 1
Performing an on-line resize of /dev/vg0/lv_opt to 2122752 (4k) blocks.
The filesystem on /dev/vg0/lv_opt is now 2122752 blocks long.

[root@oradb12clinux oradb12c]# df -h
Filesystem                                               Size  Used Avail Use% Mounted on
/dev/mapper/vg0-lv_root                       14G  8.8G  4.4G  67% /
tmpfs                                                      2.0G  264K  2.0G   1% /dev/shm
/dev/sda1                                               194M   51M  134M  28% /boot
/dev/sda2                                               811M   17M  753M   3% /home
/dev/mapper/vg0-lv_opt                         8.0G  146M  7.5G   2% /opt
/dev/mapper/vg0-lv_u01                        15G  166M   14G   2% /u01