Oralce 数据库整理1

欢迎大家来到IT世界,在知识的湖畔探索吧!

/*关掉图形界面：

[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/oracle@192.168.1.164/nwtprd

Connected.

conn sys/Gyx@0926@192.168.1.164/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/oracle@192.168.1.164/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=”liusihan0608@gmail.com,514456762@qq.com”

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