/*關掉圖形介面：
[root@ora19c ~]# cat /etc/inittab
[root@ora19c ~]# systemctl set-default multi-user.target
*/
/*關閉防火牆
[root@ora19c ~]# systemctl stop firewalld
[root@ora19c ~]# systemctl disable firewalld
*/
/*切換到grid 環境
[root@ora19c ~]# . ./g.env
*/
/*連線sqlplus
[oracle@ora19c ~]$ sqlplus / as sysdba
*/
SQL> alter pluggable database nwtprd open;
SQL> alter session set container=NWTPRD;
SQL> show pdbs
CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
3 NWTPRD READ WRITE NO
SQL> !ps -ef | grep tns
root 22 2 0 Feb01 ? 00:00:00 [netns]
oracle 5876 1 0 09:26 ? 00:00:00 /u01/app/oracle/grid/bin/tnslsnr LISTENER -no_crs_notify -inherit
oracle 22714 22244 0 10:31 pts/0 00:00:00 /bin/bash -c ps -ef | grep tns
oracle 22716 22714 0 10:31 pts/0 00:00:00 grep tns
SQL> conn /as sysdba
Connected.
SQL> alter user system identified by oracle;
SQL> conn system/[email protected]/nwtprd
Connected.
conn sys/Gyx@[email protected]/NWTPRD
/*關閉資料庫
[oracle@ora19c ~]$ srvctl stop database -d NWT
*/
/*檢測是否還在
[oracle@ora19c ~]$ ps -ef | grep ora_
oracle 26706 7193 0 11:26 pts/0 00:00:00 grep --color=auto ora_
*/
/*檢視環境變數
[oracle@ora19c ~]$ env | grep ORA
ORACLE_SID=NWT
ORACLE_BASE=/u01/app/oracle
ORACLE_HOME=/u01/app/oracle/product/19.3/db1
*/
/*開啟資料庫
[oracle@ora19c ~]$ srvctl start database -d nwt
*/
/*檢視資料庫狀態
[oracle@ora19c ~]$ srvctl status database -d nwt
Database is running.
*/
/*
Process Monitor Process (PMON)
[oracle@ora19c ~]$ ps -ef | grep pmon
oracle 6424 1 0 09:26 ? 00:00:00 asm_pmon_+ASM
oracle 27506 1 0 11:38 ? 00:00:00 ora_pmon_NWT
oracle 28228 7193 0 11:44 pts/0 00:00:00 grep --color=auto pmon
*/
/* 開機後把nwtprd 變成pluggable
SQL> show pdbs
CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
2 PDB$SEED READ ONLY NO
3 NWTPRD MOUNTED
SQL> alter pluggable database nwtprd open;
Pluggable database altered.
SQL> alter session set container = NWTPRD;
SQL> show pdbs
CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
2 PDB$SEED READ ONLY NO
3 NWTPRD READ WRITE NO
*/
SGA: system global aera;共享記憶體（group of shared memory），可以被多個後臺程序所訪問
/* 檢視所有pmon：
[oracle@ora19c ~]$ ps -ef | grep pmon
-e and -f are options to the ps command, and pipes take the output of one command and pass it as the input to another. Here is a full breakdown of this command:
ps - list processes
-e - show all processes, not just those belonging to the user
-f - show processes in full format (more detailed than default)
command 1 | command 2 - pass output of command 1 as input to command 2
grep find lines containing a pattern
processname - the pattern for grep to search for in the output of ps -ef
*/
/* 檢視proc下的某個程序的子目錄（對應磁碟上的可執行檔案） 只能在root下操作
[root@ora19c ~]# ls -l /proc/4130/exe
lrwxrwxrwx. 1 oracle dba 0 Feb 3 21:31 /proc/4130/exe -> /u01/app/oracle/product/19.3/db1/bin/oracle
*/
用sqlplus登入資料庫：
[oracle@ora19c ~]$ sqlplus / as sysdba
SQL> show parameter control
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
control_file_record_keep_time integer 7
control_files string +DATA/NWT/CONTROLFILE/current.
262.1063533389, +DATA/NWT/CONT
ROLFILE/current.261.1063533389
control_management_pack_access string DIAGNOSTIC+TUNING
描述command：
SQL> desc dba_tablespaces
Name Null? Type
----------------------------------------- -------- ----------------------------
TABLESPACE_NAME NOT NULL VARCHAR2(30)
BLOCK_SIZE NOT NULL NUMBER
INITIAL_EXTENT NUMBER
NEXT_EXTENT NUMBER
MIN_EXTENTS NOT NULL NUMBER
MAX_EXTENTS NUMBER
SQL> show user
USER is "SYS"
SQL> show pdbs
CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
2 PDB$SEED READ ONLY NO
3 NWTPRD MOUNTED
進入指定pdb：
SQL> alter session set container = NWTPRD;
Session altered.
SQL> show pdbs
CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
3 NWTPRD MOUNTED
出來就重新連：
SQL> conn /as sysdba
Connected.
multitenant container database (CDB) that includes zero, one, or many customer-created pluggable databases (PDBs).
從 spfile轉換到pfile：直接create
SQL> create pfile='/tmp/o1.ora' from spfile='+DATA/NWT/PARAMETERFILE/spfile.274.1063533997';
File created.
檢視檔案：
SQL> !cat /tmp/o1.ora The cat (short for “concatenate“)
NWT.__data_transfer_cache_size=0
NWT.__db_cache_size=1795162112
NWT.__inmemory_ext_roarea=0
NWT.__inmemory_ext_rwarea=0
NWT.__java_pool_size=0
[root@ora19c ~]# su – oracle
[oracle@ora19c ~]$ sqlplus / as sysdba
SQL> conn system/[email protected]/nwtprd
[oracle@ora19c ~]$ srvctl start database -d NWT
PWD Linux command
In Unix-like and some other operating systems, the pwd command (print working directory) writes the full pathname of the current working directory to the standard output.
SQL> desc v$fixed_table
Name Null? Type
----------------------------------------- -------- ----------------------------
NAME VARCHAR2(128)
OBJECT_ID NUMBER
TYPE VARCHAR2(5)
TABLE_NUM NUMBER
CON_ID NUMBER
SQL> set pages 50000;
The su (short for substitute or switch user) command allows you to run commands with another user's privileges, by default the root user.
賬號、密碼 、host name、port id、instance name
檢視程序
[oracle@ora19c ~]$ ps -ef
UID PID PPID C STIME TTY TIME CMD
root 1 0 0 Feb03 ? 00:00:13 /usr/lib/systemd/systemd --switched-root --s
root 2 0 0 Feb03 ? 00:00:00 [kthreadd]
[oracle@ora19c ~]$ cd ..
[oracle@ora19c home]$ ls -l
total 8
drwx------. 8 oracle oinstall 4096 Feb 3 20:44 oracle
drwx------. 15 peter peter 4096 Feb 1 17:38 peter
[oracle@ora19c home]$ cd
檢視記憶體：
[oracle@ora19c ~]$ ipcs
------ Message Queues --------
key msqid owner perms used-bytes messages
------ Shared Memory Segments --------
key shmid owner perms bytes nattch status
0x1c8e0e8c 0 oracle 600 28672 26
0x00000000 163841 oracle 600 9138176 130
0x00000000 196610 oracle 600 2466250752 65
0x00000000 229379 oracle 600 7639040 65
0x38c752c0 262148 oracle 600 16384 65
進入shell模式：
SQL> !
[oracle@ora19c ~]$
從shell模式出來：
[oracle@ora19c ~]$ exit
exit
SQL>
Sql下檢視sga：
SQL> show sga;
Total System Global Area 2483027624 bytes
Fixed Size 9137832 bytes
Variable Size 536870912 bytes
Database Buffers 1929379840 bytes
Redo Buffers 7639040 bytes
SQL> select component, granule_size from v$sga_dynamic_components;
COMPONENT GRANULE_SIZE
---------------------------------------------------------------- ------------
shared pool 16777216
large pool 16777216
java pool 16777216
streams pool 16777216
[oracle@ora19c ~]$ /sbin/ifcongif
-bash: /sbin/ifcongif: No such file or directory
[oracle@ora19c ~]$ /sbin/ifconfig
ens192: flags=4163<UP,BROADCAST,RUNNING,MULTICAST> mtu 1500
inet 192.168.1.39 netmask 255.255.255.0 broadcast 192.168.1.255
SQL> col name format a20
SQL> col value format a30
SQL> /
SQL> select name, value from v$parameter where name ='default_sharing';
NAME VALUE
-------------------- ------------------------------
default_sharing metadata
查引數
SQL> show parameter pdb_lockdown;
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
pdb_lockdown string
連線五要素：
Conn user/password@server:port/database
檢視file：
[root@ora19c ~]# strings spfilenwt.ora | more
[root@ora19c ~]# man su
SU(1) User Commands SU(1)
NAME
su - run a command with substitute user and group ID
SYNOPSIS
su [options...] [-] [user [args...]]
兩種引數檔案：pfile和spfile
只改記憶體，不改磁碟：
檢視記憶體檔案：
所有引數都儲存在v$parameter 下面：
SQL> desc v$parameter;
Name Null? Type
----------------------------------------- -------- ----------------------------
NUM NUMBER
NAME VARCHAR2(80)
第九課：
第十課：
殺掉遠端連線的使用者：
檢視登陸進來的使用者：
select sid,serial#,username from v$session;
換到grid 環境：
Cat g.env
Set permissions for cronjob:
chomd 775 job1.sh
建立一個mail定時傳送cron job
#!/bin/bash
MAILFILE=/tmp/job1.txt
MAIL_RECIPIENT="[email protected],[email protected]"
cat $MAILFILE | mail -s "This is a test mail" $MAIL_RECIPIENT
echo "Send Mail"
~
set serverout on
DECLARE
CURSOR find_sql_text_table
IS
WITH x AS (SELECT
st.sql_id SQL_ID,
st.sql_text SQL_TEXT,
(SYSDATE - ss.sql_exec_start)*24*60*60 AS DURING
FROM v$session ss JOIN v$sqltext st ON (st.sql_id=ss.sql_id))
SELECT sql_id, sql_text, during from x where during >= 0;
BEGIN
DBMS_OUTPUT.DISABLE;
DBMS_OUTPUT.ENABLE(100000);
FOR cur_rec IN find_sql_text_table
LOOP
BEGIN
DBMS_OUTPUT.PUT_LINE('SQL ID: ' || cur_rec.SQL_ID);
DBMS_OUTPUT.PUT_LINE('SQL TEXT: ' || cur_rec.SQL_TEXT);
DBMS_OUTPUT.PUT_LINE('SQL ID: ' || cur_rec.SQL_ID || ' seconds');
DBMS_OUTPUT.PUT_LINE('--------------------------')
END;
END LOOP;
END;
/
再root下copy檔案到Oracle下面：
[root@odidb ~]# cp fmw_12.2.1.2.6_odi.jar /home/oracle
解壓ipm檔案：
[root@odidb ~]# rpm -ivh jre-8u131-linux-x64.rpm
檢視java version並選擇：
sudo update-alternatives --config java
讓介面跳出來需要export display
[oracle@odidb ~]$ export DISPLAY=192.168.1.3:0.0
akkad1an#87
show parameter service_name;
https://www.stechies.com/difference-between-oracle-sids-and-oracle-service-names/
在root環境下改Oracle user的密碼：
[root@odiapp ~]# passwd oracle
Changing password for user oracle.
[root@odiapp ~]# rm -f *.rpm