Notes on common DB/Linux commands

Notes for common DB/Linux commands 1 UserGroup/etc/passwd: username: passwd: uid: gid: homedir: shell password ing in/etc/shadow, A user can belong to multiple user groups. The fifth field is the full name of the user name (optional)/etc/group: groupname: passwd: gid: userlist. the user list is optional. "split 2 AddUser & Group ● Modify file more/etc/passwd | grep oracle cat/etc/group | grep uid modify passwd, add user records, do not repeat uid; execute pwconv, synchronize passwd and shadow. Modify the group and add the group to run the maid command to synchronize group and shadow. Create the user's home directory and copy the user's Startup File; in addition. * Copy the hidden file: cp-R/etc/skel/home/user; change the owner and permissions of the New user: chown-R user: group/home/user; -R: Apply all files in the directory? Set the New User Password: passwd username ● run the command 1) mkgroup groupname 2) groupadd-g uid groupname 3) mkuser mkduer pgrp = username home = diracw.shell =/usr/bin/ksh id = uid username 4) useradd-u 1002-g group-G group01-d/home/user-m user useradd-d/opt/oracle-m-g dba-G oinstall-s/bin/ bash oracle user's default working directory is/home/username, you can use-d to specify other directories as their working directories. the user's default uid is increased from 500 to the later order. You can use-u to specify the uid. the user's group is not specified, A job with the same name as the user name is generated. Group, you can also use-g to specify 5) ld, finge to view User information 6) ls-dl $ HOME to display the owner and group of the Directory 7) usermod: Modify Usermod-l newname oldname; Modify usermod-d newdir user; Add user to group usermod-g group user; differences between small G and large g 8) gpasswd changes the group members. Only the root and group administrators have the permission to add: Gpasswd-a user group Delete: Gpasswd-d user group 9) Groupmod modifies the group name: groupmod-n oldname newname 10) rmuser-r username 11) Userdel delete a user and delete the working directory userdel-rf user 12) Rmgroup-rgroupname 13) Gro Updel 3 common Linux commands: tar-cvf-file | gzip-cf> file.tar.gz gzip-dc file.tar.gz | tar-xvf-gunzip directly decompress the gz package unzip (zip) directly decompress the zip package mv origfile destfile to cut the content in the source file to the target folder find. -name "*. log "| xargs rm | awk '{print $2}' | xargs kill-9 or | awk '{print" kill-9 ", $2} 'df-h; fdisk-l shows the disk space du-lh du-sh * Java-version Version. check the Uname-a model or oslevel operating system version of the sh platform: # Check the kernel version of cat/etc/SuSE-release: # Uname-r4 environment variable setting # echo $ SHELL bash: # vi. bash_profile #.. bash_profile sh or ksh: # vi. profile #.. profile csh or tcsh: # vi. login # source. login. cshrc? Setenv var = xxx; export var xxx; # echo $ JAVA # env | the grep JAVA shell type does not match. The other ln shell types to the current -- ln-s src desc script executes bash: #. /script; ksh: # sh script; bash-> ksh? = JAVA = root user upload (. bin ,. rpm, compressed package), set to use the user's environment variable export JAVA_HOME =/opt/jdk1.6 export JRE_HOME =/opt/jdk1.6/jre export CLASSPATH = $ JAVA_HOME/lib: $ JRE_HOME/lib :. export PATH = $ JAVA_HOME/bin: $ JRE_HOME/bin: $ ORACLE_HOME/bin :.: $ PATH = ORACLE = patch installation-Ora software installation-configuration listening-upgrade Ora-create DB export ORACLE_BASE =/opt/oracle/app/oracle export ORACLE_HOME = $ ORACLE_BASE/product /11.2.0/dbhome_1 export ORACLE_SID = SID export PATH = $ ORACLE_HOME/bin: $ PATH 5 Oracle1) Dbca creates database (Instance) instances as "Memory" (SGA) and "background process" (server process, background process, and subordinate process) each process allocates internal memory (PGA memory) to complete its task RAC (Real Application Cluster) Cluster: Multiple instances drive a database at the same time. 2) $ TNS_ADMIN specifies the configuration file tnsnames. ora location: $ ORACLE_HOME/network/admin Tnsnames. ora storage network service name (set as SIDname, as the alias of the connection descriptor) file structure: Esbora = (DISCRIPTION = (ADDRESS = (PROTOCOL = TCP) (HOST = IP) (PORT = 1521) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = Esbora) (3) Client Connection database connection string Sqlplus user/pwd @ HOSTIP: 1521/SID network string Sqlplus user/pwd @ netservicename native login does not need to input @ nsn Sqlplus sys/sys as sysdba or sqlplus "/as sysdba" Sqlplus/holog no user connects to DB Conn user /pwd @ nsn @ execute the specified directory script Disc disconnect 6 Listener1) clinet -- Listener -- Server 2) Netca configuration listener on the Server side 3) Listener Configuration File $ TNS_ADMIN/listener. ora file structure: LISTENER = LISTENER name, default LISTENER (DISCRIPTION_LIST = (DISCRIPTION = (ADDRESS = (PROTOCOL = TCP) (HOST = IP) (PORT = 1521) (ADDRESS = (PROTOCOL = IPC) (KEY = EXTPROC1521 )))) SID_LIST_LISTENER = (SID_LIST = (SID_DESC = (SID_NAME = esbora) (ORACLE_HOME =/opt/oracle/app/oracle/OraHome_1) (PROGRAME = extproc) 4) lsnrctl start (ListenerName) without adding a name to start listening by default 5) Lsnrctl stop 6) Lsnrctl status 7 database installation patch installation-Ora software installation-configuration listening-upgrade Ora-create DB sequence? ● Set the Display variable Xmanager to log on to the Root: xhost localhost directly with the oracle user; xhost + open all Oracle: export DISPLAY = localhost: 1.0 setenv DISPLAY local_host: 0.0 echo $ DISPLAY xclock path/usr/X11R6/bin can be added to the environment variable ● install the patch and install the database, and then install the database in the patch directory oui/bin /. /runInstaller if the database installation directory is not in the patch directory, for example, if the patch directory is OraHome_1 and OraHome_2, you must enter the $ ORACLE_HOME directory as an oracle user, make the jdk directory mv jdk. run the following command to create a link: # ln-s/opatch_path/jdk # ls-al: jdk-> opt/ora Lce/app/oracle/OraHome_1/jdk 8 System Management 1) database Startup: log on to Startup nomount as sysdba. Only start the instance. Do not start the database. Starting the instance includes allocating memory and starting the process (reading the parameter file). Startup mount starts the database to the mount State (including nomount) open the control file, data file, and on-line log file, and associate the instance Alter database open to open the database ● Startup; Use the server-side parameter file (SPFILE) to start the instance. Do not add SPFILE; you can also use the initialization parameter file (PFILE) to start: startupPFILE = $ ORACLE_HOME/dbs/init. ora Startup open read only starts the database in read-only mode. ● Nomount-> alter database mount-> alter database open; ● Mount-> Alterdatabse open; Alter database open readonly; read-only startup in mount mode FAQ: database startup database suspended AlterFile: Flash Recovery Area) insufficient disk space PFILE is configured with the flash back area path and size; Db_recovery_file_dest = '<ORACLE_BASE>/flash_recovery_area 'db_recovery_file_dest_size = 2G 2) The database closes the database (write data files and connection logs) -> Unmount database (detach database and instance, disable control file)-> close instance (remove SGA from memory, terminate process) Shutdown immediate user cannot register login; uncommitted events are rolled back in north China. process management processes that do not wait for all users to exit 9 fall into two categories: User processes and oracle processes. 1) User process users run applications or When using the oracle client, two similar concepts are created: ● connection: a communication path between the user process and the instance, A dedicatedserver connection is usually established between a customer process and a dedicated server or a scheduler, A dedicated process for this connection will be obtained on the server. There is a one-to-one ing sharing server (shared server) connection between the database connection and a process or thread on the server, when a shared connection is used, multiple sessions can share a server's process pool. The processes are generated and managed by the Oracle instance, and you are connected to a Database Scheduler (dispatcher ), instead of creating a dedicated server process for the connection. ● Session: a special connection from a user to a database, starting from user login to exit. A session is a logical entity in the instance. The customer process can execute SQL statements on the session, commit transactions and run stored procedures. Multiple independent sessions can be associated with one connection. These session settings can exist independently of the connection. 2) Oracle process (instance process) the Oracle process runs on the server: ● server process (ServerProcess): the server process is a process that completes the work on behalf of the customer session to process user process requests connected to the database, perform Syntax Parsing and execution on SQL statements, read data, and return results: VPC process (Dedicated): service sharing server process (Shared) only for specific user processes ): when a server process is shared, multiple sessions can share a server process pool. The processes are generated and managed by the Oracle instance. ● BackgroundProcess: starts with the database, used to complete various maintenance tasks 10 Data Storage Structure ● logical structure (LogisticalStructure): data block (DataBlock) area (Extent) Segment (Segment) Table space (Tablespace) block is the most basic storage unit tablespace: stores database objects (tables, indexes, etc.) ● Physical Structure (PhysicalStr Ucture): Datafiles, Control Files, Online Redo Logs (strictly) Other Archive Log Files, Parameter Files, AlertFiles, Trace Files, Backup Files DataFiles: each data file is an operating system file consisting of multiple operating system blocks. The table and index data in the database are physically stored in the data file. A database has multiple data files, A data file only belongs to one database and one tablespace can be composed of multiple data files. A data file only belongs to one tablespace) create tablespace tibco datafile '/home/oracle/ora/oradata/VERIFY111/tibcodata1.dbf' SIZE 2048 m reuse autoextend on next 100 M MAXSI ZE20480M, '/home/oracle/ora/oradata/VERIFY111/TIBCODATA2.DBF' SIZE 2048 m reuse autoextend on next 100 M MAXSIZE20480M, logging extent management local segment space management auto; 2) create user tibapp Identified by tibapp Default tablespace TIBCODATA Temporary tablespace TEMP Profile DEFAULT; -- Grant/Revoke role privileges Grant connect to tibapp; Grant resource to tibapp; -- Grant/Revoke syst Em privileges Grant unlimited tablespace to tibapp; 3) Delete tablespace and user Spool. /log/delete_tbs_user.log Connect sys/sys@10.71.179.64/eplus11 as sysdba Drop user username cascade; -- cascade indicates to delete the object Drop tablespace TIBCODATA in this user mode before deleting the user; Spool off Exit; 4) Common SQL ● Select open_mode from v $ database Select status from v $ instance ● tablespace, data file Select * from dba_tablespaces; Select username, default_tablespace f Rom dba_users where username = ''; user's default tablespace --------- query table space size --------- selectb. file_id File ID, B. tablespace_name tablespace name, B. bytes/1204/1204 | 'M', (B. bytes-sum (nvl (. bytes, 0)/1024/1024 | 'M' used, sum (nvl (. bytes, 0)/1024/1024 | 'M' remaining space, 100-sum (nvl (. bytes, 0)/(B. bytes) * 100% occupied by fromdba_free_space a, dba_data_files B wherea. file_id = B. file_id groupby B. tablespace_name, B. file_id, B. bytes order by B. file _ Id --------- modify the tablespace size --------- alter database datafile 'datafile _ path/dbfname' resize1024M; alter tablespace tbsname add datafile '*** 'size 500 m autoextend off; ● user management select username from dba_users; select username, default_tablespace from dba_users where username = ''; alter user username identified by new password; ● process query the process ID (PID) associated with the VPC Select. spid dedicated_server, B. processclientpid From V $ process a, v $ session bwhere a. addr = B. paddr And B. sid = (select sid fromv $ mystat where rownum = 1); Sqlplus :! /Bin/ps-p pid view process ID corresponding information background process view Select * from v $ bgprocess where paddr <> '00 '; ps-ef | grep ora _ ● killing user processes (session sessions) Select sid, serial #, username, status, server, process, osuser, paddr fromv $ session where type = 'user'; Process: clientpid paddr: VPC Process address Alter system kill session 'sid, Serial #'; --------- tracing method --------- query the session information of oracle users: select username, machine, osuser, programe, status, count (machine) fromv $ session where username = ''group by username, machine, programe, status, osuserorder by username, machine; find the oracle Connection Client, find the process connecting to the oracle user source to kill ● Database Backup a) Exp user/pwd @ SID owner: user rows = y compress = n buffer = 65536 feedback = 100000 volsize = 0 file = user. dmp log = user. log; B) Expdp user/pwd @ SID directory = dir_name dumpfile = user. dmp schemas = user logfile = user. log; query the create db directory Select * from dba_directories; Createdirectory dir_name as 'dir _ path'; Grantread, write or directory dir_name to public; ● TIME Select to_char (B2C (req_message )) from table where instr (B2C (req_message), 'str')> 0; Selet cast (sysdate as timestamp) "DATE" from dual; Select to_char (sysdate, 'yyyymmdd24miss ') from dual; Select to_char (to_date ('2017-06-30 15:15:15 ', 'yyyy-mm-ddhh24: mi: ss'), 'yyyy-mm-dd am hh24: mi: ss ') from dual; Select to_date (to_char (sysdate, 'yyyy-mm-dd hh24: mi: ss'), 'yyyy-mm-ddhh24: mi: ss') from dual; select to_number (to_char (sysdate, 'yyyymmdd') from dual; Select to_date ('2017-1-1 ', 'yy-mm-dd') from dual; l Select to_date (sysdate) from dual; Select sysdate-1 from dual; Select add_months (sysdate,-1) from dual; Select sysdate-1/(24*60) from dual;