· 8 years ago · Feb 18, 2018, 03:42 AM
1#############################
2# Day 3 - Jan 09 #
3#############################
4
5Boot partition for Oracle Linux
6
7(root) / 15/16 GB
8/boot 200MB
9/tmp 4096MB 4 GB
10/swap 4GB
11/u01 15/16GB
12
13LVM
14Logical Volume Manager
15means LOGICAL Partitions can be augmented as necessary for data storage
16in Oracle DB, the extra space for data storage ie Clusters added should be partitioned but not formatted to any FILE SYSTEM as Oracle sets its own FS
17
18Schema: incase of oracle
19refers to tables(objects), sequences created by a particular user and belongs to that very user.
20
21Null - arithmetic operations with Null always give Null. Null is not equal to another Null.
22Blank - unknown, N/A
23Zero - String or Numeric
24
25Unique key: distinct key.
26since, no null is same, infinite amount of Nulls can be associated as unique key in a table(object).
27
28Primary key: a unique key used in a object, so that no two rows of datas are confused with each other.
29
30"" only for Alias
31'' for concatenation and adding string inbetween
32
33RPM
34
35!RemindMe: SELECT statement in multiple lines
36
37#############################
38# Day 4 - Jan 10 #
39#############################
40
41DUAL: dummy object defined by Oracle to test out
42
43WHERE clause condition:
44=, >, <, in, between, like
45
46 =
47 SELECT * FROM object_name WHERE field_name = 'something';
48
49 >: greater than
50 <: less than
51 SELECT * FROM object_name WHERE field_name > < 'some numeric or even string';
52
53 IN: values should be among these
54 SELECT * FROM object_name WHERE field_name IN ('value_1', 'value_2', 'value_3');
55
56 BETWEEN: values should be between these parameters. however, includes both upper and lower limit. must be followed with 'AND' logic operator
57 SELECT * FROM object_name WHERE field_name BETWEEN 'value_1' AND 'value_2';
58
59 LIKE: values should be similar to this, using wildcard(% for *, _ for ?)
60 SELECT * FROM object_name WHERE field_name LIKE '_a%'; (this returns result with second letter as a and any length of character after that)
61
62Logical condition:
63AND, OR, NOT
64
65 AND: defines logic that result should obey ALL given parameters
66
67 OR: defines logic that result should fulfill either of the given parameters
68
69 NOT: similar to ! in other language, not this
70 SELECT * FROM object WHERE field_name NOT BETWEEN value_1 AND value_2;
71 SELECT * FROM object WHERE field_name NOT IN ('value_1', 'value_2', 'value_3');
72
73VARIABLES:
74 AMPERSAND & &&:
75 used to define a variable in Oracle
76 * &var_name:
77 when used in SQL Statement after WHERE, if the variable is not defined yet, prompts the user for input of the value, else just uses the already defined value
78 * &&var_name:
79 the &&var_name is done so that the value defined in &var_name prompt can be used again by Oracle; like:
80 SELECT address AS CONCAT("&&var_name", "'s address") FROM customer_detail WHERE customer_name = '&var_name';
81
82DEFINE
83 define a variable
84 DEFINE &var_name = 'value';
85
86UNDEFINE
87 undefine a variable
88 UNDEFINE &var_name;
89
90VERIFY
91 useful when multiple variables are already defined
92 when VERIFY is turned ON: if a statement contains variable - prints the value of the variable to the user before statement execution, for convinience
93 VERIFY ON;
94 VERIFY OFF;
95
96!RemindMe: ORDER BY 40 rows, but i want to view only 5 rows
97
98#############################
99# Day 5 - Jan 11 #
100#############################
101
102SQL function (types of):
1031. Single row function
104 1 input, 1 output
1052. Multiple row function
106 multiple input, 1 output
107
1081. SINGLE ROW FUNCTION
109
110 * CONVERSION function: this converts the string to the case as specified
111 UPPER(column_name) - convert all string to uppercase
112 LOWER(column_name) - convert all string to lowercase
113 INITCAP - convert first character to uppercase and rest to lowercase ie Sentence Case
114
115 useful in query when datas are in mixed cases:
116 SELECT * FROM customer_info WHERE UPPER(first_name) = 'JOHN';
117 this converts all first_names in table_obj into UPPERCASE and matches against 'JOHN', so that all instances of JOHN are returned regardless of their case.
118 however, the printed output is not UPPERCASED
119
120 * NUMBER function
121 ROUND - ROUND(number, place_from_decimal_to_round_off)
122 param_2: +ve, right from decimal; -ve, left from decimal
123 TRUNC - means permanent delete, to cut off
124 TRUNC(number, place_from_decimal_to_truncate)
125 param_2: +ve, cuts out the numbers after that place; -ve, makes the number from that place to decimal zero
126 MOD - Modulus ie gives Remainder after Division
127 MOD(number, divisor)
128
129 * TIMEDATE function
130 SYSDATE - gives sysdate in DD-MM-YY format, which is Oracle Default format
131 for time info TO_CHAR can be used in SYSDATE like:
132 TO_CHAR(SYSDATE, 'DD-MM-YYYY HH12:MM:SS')l;
133
134 * CHARACTER function
135 CONCAT - CONCAT('str_1', 'str_2') is same as 'str_1' || 'str_2'
136 LPAD - LPAD(num, no_to_replace_with, total_digits_inall) - adds no. from left_side
137 LPAD(321, 7, 5); --> 77321
138 RPAD - RPAD(num, no_to_replace_with, total_digits_inall) - adds no. from right_side
139 RPAD(987, 5, 6); --> 987666
140 TO_CHAR - TO_CHAR(1234, '$9999'); --> $1234. only $ works
141
142 * NULL function
143 NVL - Null Value Function
144 NVL(col_with_NULL_value, what_to_replace_null_with)
145 NVL2
146 NVL2(col_with_NULL_value, NOT_NULL_show_this, NULL_show_this)
147
148#############################
149# Day 6 - Jan 12 #
150#############################
151
152GROUP function
153 AVG
154
155 MIN
156 MAX
157 COUNT
158
159SELECT
160 FROM
161 WHERE
162ORDER BY
163
164!RemindMe: RANK() - Next class
165
166if start.sh and listen.sh exist
167run them
168./start.sh
169./listen.sh
170
171if not
172sqlplus '/as sysdba'
173startup
174exit
175
176lsnrctl start
177
178select employee_id, first_name, salary from employees;
179select employee_id, first_name, salary ,depatment_id, rank() over (partiton by department_id order by salary) as rank_salary
180from employees;
181
182
183select employee_id,first_name,salary from employees;
184select * from (
185select employee_id, first_name,salary, depatm_ID =D.DEPARTMENT_ID);ent_id rank () over (partition by department_id order by salary) as rank_salary
186from employess)
187where rank_salary<=5;
188
189SELECT E.EMPLOYEE_ID, E.LASTNAME,E.DEPARTMENT_ID,
190D.DEPARTMENT_ID,D.LOCATION
191FROM EMPLOYEES E JOIN DEPARTMENTS D
192ON(E.DEPARTMENT= D.DEPATMENT_ID);
193
194SELECT E.EMPLOYEE_ID, E.LASTNAME,E.DEPARTMENT_ID,
195D.DEPARTMENT_ID,D.LOCATION
196FROM EMPLOYEES E JOIN DEPARTMENTS D
197ON(E.DEPARTMENT= D.DEPATMENT_ID);
198WHERE UPPER (E.FIRST_NAME) = 'KAREN';
199
200TYPES OF JOIN
201 • NATURAL JOIN
202 USING CLAUSE
203 ON CLAUSE(IMP)
204 • SELF JOIN(IMP)
205 • NONEQUIJIONS
206 • OUTER JOIN
207 LEFT OUTER JOIN
208 RIGHT OUTER JOIN
209 FULL OUTER JOIN
210
211 SELECT E.EMPLOYEE_ID, E.LASTNAME, E.DEPARTMENT_ID, D.DEPARTMENT_ID, D.LOCATION
212 FROM EMPLOYEES E
213 LEFT OUTER JOIN DEPARTMENTS D
214 ON(E.DEPARTMENT= D.DEPATMENT_ID);
215
216 SELECT E.EMPLOYEE_ID, E.LASTNAME, E.DEPARTMENT_ID, D.DEPARTMENT_ID, D.LOCATION
217 FROM EMPLOYEES E
218 RIGHT OUTER JOIN DEPARTMENTS D
219 ON(E.DEPARTMENT= D.DEPATMENT_ID);
220
221IMP
222CP,MV,PWD,CD,LS,LS-LTR,CHOWN,CHMOD,CP,USER_ID,GROUPADD,-G,-g ,-D,PERMISSION
223
224#############################
225# Day 7 - Jan 14 #
226#############################
227
228sqlplus / as dba
229startup (this starts db server)
230exit (sqlplus)
231
232lsnrctl start (this starts listener control utility)
233
234export ORACLE_SID=orcl
235export ORACLE_BASE=/u01/app/oracle
236export ORACLE_HOME=/u01/app/oracle/product/11.2.0/db_1
237export PATH=$ORACLE_HOME/bin:$PATH
238
239env | grep ORA
240
241RANK() OVER( PARTITION BY )
242
243JOINs
244 NATURAL JOIN
245 unusual
246 must have all fields matching in all objects
247
248 USING clause
249 not all fields must be matching
250 but requires atleast 1 matching field
251
252 ON clause
253 SELECT emp.first_name, dept.name FROM employees emp
254 JOIN department dept
255 ON emp.department_id = dept.id
256 WHERE UPPER(emp.first_name) = 'JANE';
257
258 SELF JOIN
259 JOin same table eith the same table
260
261 OUTER JOIN
262 LEFT JOIN
263 RIGHT JOIN
264 FULL OUTER JOIN
265
266Linux commands basic:
267cd change directory
268mv move
269pwd present working directory
270ls -ltr list files
271ls
272chmod change mod change the permission of files and directories [U]ser [G]roup [O]ther [R]ead [W]rite [X]ecute
273chown change owner
274cp copy
275useradd create new user
276groupadd create new group
277-G
278-g
279-d
280permission [R]ead [W]rite [X]ecute [A]ll
281
282#################################################################
283# How to connect to sample schemas in Oracle DB 12C #
284#################################################################
285
286 ---------INCASE OF PLUGGABLE DATABASE---------
287
288• install ORACLE database 12c with pluggable database/connection enabled
289 during installation, be sure to enable the 'install the sample schemas AKA SEEDED schemas'
290• BACKGROUND:
291 Oracle stores datas in Containers which are inside Pluggable Databases (meaning can be plugged/unplugged)
292 the sample_Schemas which we direly need is in such Container
293 Oracle being the McDaddy, locked the Container and the User the Schema belongs to
294 thus, we find the name of sample_Schema containing Container;
295 not just Unlock it but make sure it can be accessed remotely by external Clients.
296• in command-line SQLPLUS,
297 login as SYSDBA
298 sqlplus / as sysdba
299 find which Container we-at
300 SHOW con_name;
301 list all Containers with info (name and id)
302 SELECT name, con_id FROM v$pdbs;
303 find the Service_name of the Container, required for creating connection later
304 SELECT name FROM v$active_services WHERE con_id = X;
305• now add the above Container to LISTEadfNER file(like host), for convienient connection from other Clients
306 goto {Oracle_base|Oracle_install_dir}\product\12.2.0\dbhome_1\network\admin
307 open `tnsnames.ora` with Administrative previleges
308 then create a new entry in the SAME-FILE, copying the existing(default) ORCL_connection: using it as template; then set the Container_name and Container_service_name in the new entry
309• reload the Listener_service to make changes, from admin-commandline
310 lsnrctl reload
311• back in SQLPLUS re-login as SYSDBA
312 change the Container to the one containing seeded-Schemas
313 ALTER SESSION SET container = (Container_name);
314 check the state/status(mode) of the Container
315 SELECT name, open_mode FROM v$pdbs;
316 if the Container is in MOUNTED-mode, change to READ-WRITE
317 ALTER PLUGGABLE DATABASE open;
318• with the DB_Container unlocked (still inside SQLPLUS_cmd)
319 unlock the User to whom the sample schema belongs i.e. hr
320 ALTER USER hr IDENTIFIED BY hr ACCOUNT UNLOCK;
321• with the Listener set to Container_DB and HR_user unlocked, connection to the sample-Schema HR can be made from remote Client
322 in SQL Developer, create a new connection
323 username & password hr
324 hostname localhost
325 port (listener_port - found in `tnsnames.ora`)
326 service_name (Container_service_name)
327• REJOICE
328
329#############################
330# Day 8 - Jan 16 #
331#############################
332
333Subquery
334 used when input is unknown
335 subquery is a query used to get value(s) which is used as input to another query
336
337 • MULTIPLE row subquery and SINGLE row subquery
338 as per the req of the main query, subquery must either be SINGLE or MULTIPLE query
339
340 SINGLE ROW : = > < BETWEEN
341 SELECT fields FROM obj WHERE field = < > != ( SINGLE ROW SUBQuery );
342 SELECT fields FROM obj WHERE field BETWEEN ( SINGLE ROW SUBQuery ) AND ( SINGLE ROW SUBQuery );
343
344 MULTIPLE ROW : ANY, IN
345 SELECT fields FROM obj WHERE field IN ( MULTIPLE ROW SUBQuery );
346 SELECT fields FROM obj WHERE field ANY ( MULTIPLE ROW SUBQuery );
347
348!RemindMe: CASE in SQL - IF_ELSE fn in Oracle
349
350NULL
351 Arithemetic operator dont work with NULL so we,
352 IS NULL
353 IS NOT NULL
354
355Not Equals to operators
356 != & <>
357
358HAVING clause in GROUP BY
359 to filter the output fetched by GROUP BY.
360 the heirarchy of parts of SQL_statement goes
361 SELECT fields FROM obj WHERE cond_1 AND|OR cond_2
362 GROUP BY filter HAVING filter
363 ORDER BY field(or just use numbers);
364
365ANY
366 ANY clause is the IN-equivalent for relational operators like > <
367 takes the value that fetches the most count(values)
368
369IN
370 fetches all results that match the ones given in parenthesis
371 is same as multiple-ANDs; but not ORs
372 also IN is faster than multiple-ANDs in terms of processing
373
374SQLPLUS
375 the defacto commandline tool(batch/interactive) for oracle database
376 can be invoked anywhere from OS as long as environment variables are set properly
377 supports 4 types of command types:
378 • SQL
379 • PL/SQL (Procedural Language / System Query Language)
380 • SQLPlus - special sqlplus commands
381 • host OS
382
383 * Starting SQLPlus (command line env)
384 > sqlplus
385 prompts for username then password
386 > sqlplus [user]
387 prompts for password
388 > sqlplus [user]/[password]@[db]
389 logs right into
390 > sqlplus / as [sysdba|sysoper]
391 here, we are using our OS-level user account to loginto SQLPlus as SYS[sysdba|sysoper]
392 and, only works if our account is member/part of SYSDBA Group of Oracle
393 so, we are using our OS-account authentication to loginto SQLPlus by bypassing the need for password;
394 and also we are invoking our SYSDBA previlege
395 > sqlplus /nolog
396 using SQLPlus without loggin in as a user
397 no connection to any DB
398
399 * SQLPlus commands
400 > list
401 lists the last SQL statement stored in buffer
402 > define
403 defines the environment variables the app is running in like USER, PREVILEGE, DATE, DB_VER
404 > define [var] = " "
405 change the env variables
406 > edit
407 edits the command stored in buffer, allows edit in editor set in define_env
408 > /
409 executes the command stored in buffer or the one edited
410 > column col_name format a*;
411 format the column to required a[lphabet]_length: * = integer
412 > clear columns
413 clears the saved formatting
414 formatting set lasts only for the session, resets after logout
415 > set pagesize *
416 divides the rows of results fetched into * amount of pages
417
418#############################
419# Day 9 - Jan 17 #
420#############################
421
422CREATE TABLE table_name ( constraints );
423INSERT INTO table_name ( field_name ) VALUES ( row_value );
424UPDATE table_name SET column_name = value (WHERE)
425
426create table from existing table with or without data
427datatypes definition in column name
428 int, varchar, unique, primary key, null, not null
429
430use of AND, OR in single statement
431and its precedence or heirarchy
432
433ROWNUM
434RANK
435
436commit
437rollback
438savepoint savepoint_name
439rollback to savepoint_name
440
441CHECK Constraint
442 ( CONSTRAINT check_col_name
443 CHECK ( col_name = rule(col_name) ) );
444
445#############################
446# Day 10 - Jan 18 #
447#############################
448
449Start SQLPlus and start DB Server
450 sqlplus / as sysdba
451 startup
452
453Exit SQLplus then start listener service
454 exit
455 lsnrctl start
456
457ROLLBACK
458 restores data back to last commit
459
460TRUNCATE
461 deletes data from table and autocommits so cannot be restored
462
463SAVEPOINT
464ROLLBACK TO savepoint_name
465
466DATATYPES
467 int
468 number
469 number (10)
470 number (10, 2)
471 VARCHAR
472 VARCHAR2
473 CHAR
474
475CHECK ( column_name rule_here )
476Check disable
477novalidate enable
478
479VIEW
480 is query, does not save data
481 helps create security
482 > CREATE VIEW view_name
483 > AS
484 > SELECT * FROM table_name;
485
486 > CREATE VIEW view_name
487 > AS
488 > SELECT col_1, col_2, col_3 FROM table_name;
489
490 > CREATE VIEW view_name
491 > AS
492 > SELECT col_1, col_2, col_3 FROM table_name WHERE row = ' ';
493
494 • Materialized view
495 actually saves data, normal view is unmaterialized
496
497#############################
498# Day 11 - Jan 19 #
499#############################
500
501Sequence
502 CREATE SEQUENCE sequence_name
503 start_with
504 interval
505 end with ##optional
506 nocycle
507 nocache
508
509INSERT INTO table_name (id, other_cols) VALUES (sequence.nextval, other_datas);
510
511READ ONLY TABLE
512 > ALTER TABLE [table_name] READ ONLY
513
514#############################
515# Day 12 - Jan 21 #
516#############################
517
518LINUX is FILESYSTEM based
519LINUX Commandline
520
521• ls
522 lists contents of a directory
523 -a all files, even hidden(files starting with .period) ones
524 -ltr (l)ong listing format, (t)ime sorting, (r)everse order
525
526• touch
527 for modification of file_time to current time; creates file if doesn't exist
528
529• vi
530 VIM commandline Text Editor
531 creates file if does not already exist
532 i allows insertion
533 Esc -> wq! save changes and exit
534 Esc -> q! quit without changes
535
536• more
537 displays portion of file so can be read at user's convenience
538
539• su [s]witch[u]ser
540 su [username] switches to the previlege of the user being in the same directory
541 su - [username] switches to the previlege of the user along to his home directory ie home/[username]
542
543• echo $HOME
544 echos the location of the user's directory in the filesystem
545
546• useradd -g [groupid] -G [group_name_1,group_name_2] -d [directory_for_base_folder] [username]
547 > useradd -g oinstall -G dba -d home/oracle_test oracletest
548 • id [username]
549 identity of the current user ie userid, groupid, groups the user belongs to
550 • /etc/passwd
551 location where user info is saved
552
553• passwd [username]
554 change password of the user
555
556• chmod [permission] file/dirname
557 permission in octalcode and should be 3_digit combination, respectively for: Owner/Self/User | Group | Others
558 [R]ead 4 rwx 7
559 [W]rite 2 r_x 5
560 e[Xecute] 1 rw_ 6
561
562• chown [owner] file/dirname
563 change the owner of a file/directory
564 -R recursive ie changes apply to subdirectories and files within. This attribute also applies to chmod
565
566• groupadd [groupname]
567 creates a new group for user to be part of
568 • /etc/group
569 the location where groups are stored
570
571• cat con[cat]enate
572 cat [filename] displays the file content
573 cat [file_1] [file_2] concatenates and displays the content of both files
574 cat [filename] | more
575 cat [filename] | less
576 cat > [filename] creates file, with content as typed, CTRL+D to save
577 [content goes on the next line]
578 cat [filename] -n [n]umber lines displayed at the left
579 cat [file_1] > [file_2] content of file_1 will be overwritten or created_then_saved to file_2
580 cat [file_1] >> [file_2] content of file_1 will be appended to the bottom of file_2
581
582• tail -f [filename]
583 shows the end part of a file and updates as the file updates, in realtime
584
585• RPM [Repository Package Manager]
586 > rpm -ivh package_name.rpm
587 -i [i]nformation/description, displays when installing
588 -v [v]erbose, gives verbose output
589 -h [h]ash, prints #, to show installation progress
590
591• YUM Repo
592 /etc/yum.repos.d/ *.repo
593
594#############################
595# Jan 22 - Jan #
596#############################
597
598ALT+CTRL+F[1-7] change TTY
599
600![char] last command starting with that character/alphabet
601!?[string] last command containing that string in between
602!$ passes the argument that was last passed into command
603CTRL+R reverse search
604history
605![number] execute the line of command from history
606dos2unix [filename]
607
608cat concatenate
609tac displays the content of a file in reverse order
610more displays in page view
611less more is less
612sort
613cut
614
615cp
616mv
617rm
618tar
619rsync
620dd
621find
622
623#############################
624# Day 12 - Jan 25 #
625#############################
626
627Connection to linux (VM)
628 • Bridged (automatic)
629 Linux connects with LAN, so all other devices in same network can connect to Linux
630 • VirtualBox host only Ethernet adapter
631 connection is made ONLY to VM's Windows
632 settings can be accessed: control panel > network adapter > virtualbox host only network
633
634> service network restart
635 restart network connectivity in Linux 6
636 > nmtui Linux 7
637
638DHCP VS Static IP
639 in both connection(above) type, Linux machine can be assigned be dynamic(DHCP) or static, IP address
640 for static IP:
641 • Bridged (LAN)
642 systemSettings > Network Connection > connectionName_Edit > IPv4 Settings > Manual_Method > enter IP address
643 • VB host only Ethernet
644 [WIN] control panel > network adapter > VB Host-Only Network > IPv4_Settings > IP address
645 [LINUX] systemSettings > Network Connection > connectionName_Edit > IPv4 Settings > Manual_Method > enter IP address
646 Address within "VB Host-Only Network" range
647 Netmask default
648 Geteway "VB Host-Only Network" IP, exact
649 only the IP within the range set in "VB Host-Only Network" can be used [0-255]
650 meaning, only the last_part of the IP set in "VB Host-Only Network" can be varied, starting 3 sequences must be the same
651
652> hostname check the hostname
653 /etc/sysconfig/network file where the hostname is set, making changes requires reboot
654 /etc/hosts host file
655> date check the server date_time
656> uptime check duration the server has stayed up
657
658##############################
659# Setup YUM #
660# Yellowdog Updater Modified #
661##############################
662
663• copy all the packages from packages_directory in Oracle_ISO onto Linux System
664 the required packages are inside Oracle_ISO, mount the ISO
665 shows the overview of the filesystem, with drives and ext_drives
666 > df -h
667 -h gives size in [h]uman readable format
668 in Linux, the mounted ISO can be accessed as External_Drive as
669 > cd /media/[ISO_name]/
670 copy the packages_dir(along with all content) from ISO into Root of Linux File System
671 > cp /media/[ISO_name]/Packages/* /[root_repo_directory]/
672
673• install the pre-requisite package(s) required for YUM
674 these 3 files:
675 createrepo
676 deltarpm
677 python-deltarpm
678 are preliminary to setup yum, install them
679 > rpm -ivh createrepo-0.9.9-18.0.1.el6.noarch.rpm deltarpm-3.5-0.5.20090913git.el6.x86_64.rpm python-deltarpm-3.5-0.5.20090913git.el6.x86_64.rpm
680
681• with YUM installed, bundle all the copied packages together
682 > createrepo [packages_location]
683 this creates xml(index) repository from the set of RPMs
684
685• set YUM to search for packages_dependencies to packages_directory inside Filesystem
686 all YUM requests lead to
687 > /etc/yum.repos.d/
688 so, we create a .repo file here which states the location of packages_directory, where YUM actually searches for dependencies
689 > vi [filename].repo
690 content inside the .repo file
691 [server_name] ## any servername goes except 'server_test', must be inside []
692 name=[name]
693 baseurl=file:///[repo_directory]/Packages/ ## url where packages are located, here pointed to FileSystem
694 enabled=1 ## enable or disable
695 gpgcheck=0 ## checks the GNU Privacy Guard encryption signature to ensure package authenticity
696 save and exit
697 packages in YUM repo can be checked
698 > yum repolist
699
700• all set, packages can now be installed as
701 > yum -y install [package_name]
702
703!READ: Network subnetting
704 samba server
705 httpd
706
707#############################
708# Day 13 - Jan 26 #
709#############################
710
711uname -a
712 LINUX ko meta
713
714ORACLE_BASE
715ORACLE_HOME
716
717ORACLE_SID for instance
718ORACLE_UNQNAME for DB
719 unique name or no. to identify particylar instance of oracle
720
721DB vs instance
722 in instance, BG provess and memory allocation
723
724ps -ef | grep smon
725
726memory
727 SGA
728 shared memory
729 • shared pool
730 library cache view and dictionart
731 shared sql area maintains SQL execution plan by saving in its own buffer cache
732 • large pool backup
733 • java pool "
734 • stream pool "
735 • buffer cache 3/4 or 3secs DML or DDL util commit redo.log checkpoint log to dbf
736 PGA
737 private
738
739in sqlplus
740 > show parameter memory_target
741 shows instance memory allocation
742 bg process is named as orc_[bgprocess]_[instance_name]
743
744env | grep ORA
745 see SID
746
747ls | wc -l
748
749session
750 session is made only when user is preforming some activity
751
752/u01 20GB
753/tmp 5GB
754/boot 200MB
755/swap > 4GB
756/ 20GB
757
758#############################
759# Day 14 - Jan 29 #
760#############################
761
762background processes
763 PMON Process Monitor
764 SMON System Managmetn Mpnotor
765 DBWr database writer
766 writes changes from • DB buffer cache to DB buffer cache when checkpoint is created
767 • online redolog to datafile
768 RECO
769 CKPT
770 updates control file about the changes made to datafile after checkpoint is created
771 LGWr
772 writes changes from online redo log
773
774 database buffer cache
775 logwriter
776 online redo log
777 DBWr
778 datafile
779 CKPT
780 controlfile
781
782DBWr
783 buffer cache --> datafile
784 online redo log --> datafile
785
786logwriter
787 buffer cache --> online redo log
788
789ORACLE_BASE=/u01/app/oracle
790ORACLE_HOME=/u01/app/oracle/product/11.2.0/db_1
791
792!Read: bash_profile
793
794#############################
795# Day 15 - Jan 30 #
796#############################
797
798Oracle Universal Installer
799 GUI tool to install Oracle DB and/or Oracle Binary
800 this is the same installer in all OS
801
802$ORACLE_HOME/bin
803 Oracle DB binary location
804
805##############################
806# Oracle DB Installation #
807# Linux - post YUM setup #
808##############################
809
810PART 1: as root
811• create user (not root) to install Oracle (any name works)
812 the user should belong to group 'oinstall'(primary) and 'dba'(secondary)
813 > groupadd -g [group_id] oinstall
814 > groupadd -g [group_id] dba
815 > useradd -g [primary_group] -G [secondary_group] [username]
816 also set password for the user
817 > passwd [username]
818
819• copy the binary files to the home directory of the said user, unzip if not
820 if these steps are done by the root_user(which they are), permissions and ownership of those binary files should be changed to the newly created user
821 > chown -R [username]:[primary_group] [file/directory]
822 > chmod -R [octal_permission_number] [file/directory]
823
824• grant the user, ownership of $ORACLE_HOME, $ORACLE_BASE
825 > chown -R [username]:[primary_group] /u01
826
827• setup localhost and hosts file
828 change the localhost of the machine, if needed
829 > vi /etc/sysconfig/network
830 change the host file, so the hostname is associated with the machine
831 meaning, the hostname can be used to identify and connect with the machine, provided the IP is static
832 > vi /etc/hosts
833 inside, add:
834 [ip_address of the machine] [hostname] [first_part of hostname]
835
836• change the bash_profile of the user
837 to setup environment, such as ORACLE_BASE, ORACLE_HOME, DB_UNQNAME, DB_SID
838 > vi /home/[username]/.bash_profile
839 inside, add:
840 #content_start [hostname, unqname, base_dir, home_dir, SID cna all be changed as required]
841 TMP=/tmp; export TMP
842 TMPDIR=$TMP; export TMPDIR
843
844 ORACLE_HOSTNAME=[hostname_here]; export ORACLE_HOSTNAME
845 ORACLE_UNQNAME=[oracle_UNQNAME_here: same_as_SID]; export ORACLE_UNQNAME
846 ORACLE_BASE=/u01/app/oracle; export ORACLE_BASE
847 ORACLE_HOME=$ORACLE_BASE/product/11.2.0/db_1; export ORACLE_HOME
848 ORACLE_SID=[oracle_SID_here: same_as_UNQNAME]; export ORACLE_SID
849
850 PATH=/usr/sbin:$PATH; export PATH
851 PATH=$ORACLE_HOME/bin:$PATH; export PATH
852
853 LD_LIBRARY_PATH=$ORACLE_HOME/lib:/lib:/usr/lib; export LD_LIBRARY_PATH
854 CLASSPATH=$ORACLE_HOME/jlib:$ORACLE_HOME/rdbms/jlib; export CLASSPATH
855 #content_end
856
857• change the system_control file and reload the changes made
858 > vi /etc/sysctl.conf
859 inside, add:
860 #content_start
861 fs.suid_dumpable = 1
862 fs.aio-max-nr = 1048576
863 fs.file-max = 6815744
864 kernel.shmall = 2097152
865 kernel.shmmax = 536870912
866 kernel.shmmni = 4096
867 # semaphores: semmsl, semmns, semopm, semmni
868 kernel.sem = 250 32000 100 128
869 net.ipv4.ip_local_port_range = 9000 65500
870 net.core.rmem_default=262144
871 net.core.rmem_max=4194304
872 net.core.wmem_default=262144
873 net.core.wmem_max=1048586
874 #content_end
875 > sysctl -p
876
877• define the limits for oracle_user in limits.conf
878 > vi /etc/security/limits.conf
879 inside, add:
880 #content_start
881 [domain_name set in hostname] soft nproc 16384
882 [domain_name set in hostname] hard nproc 16384
883 [domain_name set in hostname] soft nofile 4096
884 [domain_name set in hostname] hard nofile 65536
885 [domain_name set in hostname] soft stack 10240
886 #content_end
887 nproc max number of processes
888 nofile max number of open files
889 stack max stack size
890 hard/soft limits enforcement
891
892• disable linux firewall_status from file and from terminal
893 > vi /etc/sysconfig/selinux
894 inside, edit:
895 SELINUX=disabled
896 > service iptables stop
897 stops firewall
898 > chkconfig iptables off
899 turns off firewall for every sysboot
900
901• install prerequisite packages from Oracle with YUM
902 #packagelist_start
903 yum -y install binutils-2*x86_64*
904 yum -y install glibc-2*x86_64* nss-softokn-freebl-3*x86_64*
905 yum -y install glibc-2*i686* nss-softokn-freebl-3*i686*
906 yum -y install compat-libstdc++-33*x86_64*
907 yum -y install glibc-common-2*x86_64*
908 yum -y install glibc-devel-2*x86_64*
909 yum -y install glibc-devel-2*i686*
910 yum -y install glibc-headers-2*x86_64*
911 yum -y install elfutils-libelf-0*x86_64*
912 yum -y install elfutils-libelf-devel-0*x86_64*
913 yum -y install gcc-4*x86_64*
914 yum -y install gcc-c++-4*x86_64*
915 yum -y install ksh-*x86_64*
916 yum -y install libaio-0*x86_64*
917 yum -y install libaio-devel-0*x86_64*
918 yum -y install libaio-0*i686*
919 yum -y install libaio-devel-0*i686*
920 yum -y install libgcc-4*x86_64*
921 yum -y install libgcc-4*i686*
922 yum -y install libstdc++-4*x86_64*
923 yum -y install libstdc++-4*i686*
924 yum -y install libstdc++-devel-4*x86_64*
925 yum -y install make-3.81*x86_64*
926 yum -y install numactl-devel-2*x86_64*
927 yum -y install sysstat-9*x86_64*
928 yum -y install compat-libstdc++-33*i686*
929 yum -y install compat-libcap*
930 #packagelist_end
931
932PART2: as [user]
933• run the binary_installer from database directory
934 > ./runInstaller
935
936• skip the updates and email_specification
937
938• DB mgmt is done through DBCA (Database Configuration Assistant)
939 * install database software only
940
941• type of DB installation
942 * Single Instance (choose this)
943 single instance per DB
944 * Real Application Cluster
945 multiple instances per DB
946
947• path specification must be same as specified in bash_profile
948 Oracle Base = $ORACLE_BASE
949 Software Location = $ORACLE_HOME
950
951• Inventory directory: location where LOGFILES are saved
952
953• DBA(SYSDBA) and DB_operator(SYSOPER)
954 these are set to give the group of users the privileges to setup and administer DB
955 the users of the group set here as SYSDBA and SYSOPER, shall be considered as SYS users
956
957• Prerequisite Check: final housekeeping
958 the requirements mentioned here, if fixable, should be fixed
959 if not fixable, can be ignored with fingerscrossed
960
961 OS Kernel Parameter: shmmax
962 this value can be corrected inside 'sysctl' configuration file
963
964• logs during the changes are stored at
965 /u01/app/oraInventory/logs/
966
967• when prompted, run the scripts as necessary
968
969##############################
970# Oracle DBCA #
971# Linux - post ORACLE setup #
972##############################
973
974• Login as user of dba_group
975
976• locate the DBCA and run dbca
977 > which dbca
978 > dbca
979
980• create DB
981 --> General Purpose or Transaction Processing
982 --> specify Global DB Name and SID
983 must be same as specified in env file (bash_profile)
984
985• storage type
986 * File System
987 location can be changed from 'File Location Variables...'
988 * Automatic Storage Management (will elaborate later)
989 for RAC type DB
990 * Use Oracle-Managed Files
991 the location for the DB_storage is controlled by Oracle
992
993• specify FRA (Fast Recovery Area)
994 --> sample schemas, install if necessary
995
996• Memory
997 * Automatic Memory Management
998 * Automatic Shared Memory Management
999 * Manual Shared Memory Management
1000
1001• Sizing | Processes
1002 total combined BG and user processes, this number is divided among specified PGA
1003
1004• Connection Mode
1005 * Dedicated Server Mode
1006 non-sharing resource_dedication for individual external connection
1007 * Shared Server Mode
1008 resource dedicated for external connection is shared among all connection
1009
1010• Database Storage
1011 all the configurations for Datafiles, Controlfiles, Redo Logfiles, Log_groups and their size
1012
1013#############################
1014# Day 16 - Jan 31 #
1015#############################
1016
1017how to check BG process
1018 ps -ef | grep smon
1019
1020most used command
1021 hostname
1022 ifconfig
1023 date
1024 id
1025 uname -a
1026 df -h
1027
1028su - [username]
1029 loads bash_profile also switches to home directory
1030
1031 SELECT name, open_mode, database_role FROM v$database;
1032
1033shutdown
1034 waits for every instance to exit, then proceeds to shutdown
1035 transactions waits
1036 instances waits
1037shutdown transaction
1038 waits for running transactions by creating checkpoint or rollingback, then proceeds to shutdown without waiting for all other instances to end
1039 transactions checkpoint or rollback
1040 instances no wait
1041shutdown immediate
1042 halts running transactions by creating checkpoint or rollingback, then proceeds to shutdown without waiting for all other instances to end
1043 transactions no wait
1044 instances no wait
1045shutdown abort
1046 last resort if other methods dont work. chances of database crash if this is done
1047
1048startup nomount
1049 does not check for controlfile ie spfile(binary file which stores parameters regarding DB AKA initializtion parameter)
1050 pfile
1051startup mount
1052 checks for controlfile
1053startup open
1054
1055!Read: spfile, pfile, create pfile from spfile; then move spfile elsewhere
1056 dynamic view and static view; dynamc parameters and static parametes
1057 su "-"
1058
1059ALTER DATABASE mount;
1060ALTER DATABASE open;
1061
1062#############################
1063# Day 17 - Feb 01 #
1064#############################
1065
1066dynamic view
1067static view
1068
1069dynamic parameter
1070static parameter
1071
1072Views
1073 dba
1074 user
1075 all
1076
1077dynamic view
1078dictionary view
1079
1080 > select * from V$DATABASE where name = 'ORCL';
1081
1082listener in DB server
1083connect from cloent with TCP/IP ICP
1084
1085listener netmgr
1086check listener
1087 > ps -ef | grep tns
1088
1089listener files are located in
1090 $ORACLE_HOME/netwoek/admin
1091 files required are
1092 listener.ora
1093 tnsnames.ora
1094
1095see service name of dabatase
1096 > show parameter service
1097
1098listener creating GUI Tool
1099 netca
1100 netmgr
1101
1102tnsalias ie tnsnames.ora created by 'netca'
1103
1104'netmgr' creates listener.ora file
1105
1106lsnrctl status [listener_name]
1107 stop
1108 start
1109 reload
1110
1111create another as 'listener1'
1112
1113How to check:
1114 DB Global Name SELECT * FROM global_name;
1115 DB SID SELECT * FROM db_sid;
1116 service_name SHOW PARAMETER service_name;
1117 instance_name SHOW PARAMETER instance_name;
1118 SELECT instance_name FROM v$instance;
1119
1120> alter system register;
1121> alter system set LOCAL_LISTENER='(ADDRESS = (PROTOCOL=TCP)(HOST=192.168.7.69)(PORT=1522))' scope=both;
1122> alter system set LOCAL_LISTENER='(ADDRESS = (PROTOCOL=TCP)(HOST=192.168.7.69)(PORT=1521))' scope=both;
1123
1124Start Oracle Enterprise Manager Console
1125 > emctl status dbconsole
1126 > emctl stop dbconsole
1127
1128#############################
1129# Day 18 - Feb 02 #
1130#############################
1131
1132A SEGMENT is created when a table is created
1133Multiple tables create multiple SEGMENTS, which is collectively known as TABLESPACE
1134TABLESPACE is collection of SEGMENTS, which is logical storage structure
1135A single TABLESPACE can be used by multiple users
1136TABLESPACE exists inside DATAFILE(s)
1137 meaning 1 TABLESPACE can be divided among 2 DATAFILES
1138 BUT, no DATAFILE can have more than 1 TABLESPACE
1139
1140DATAFILE(s) --> TABLESPACE --> TABLE(s) ~ SEGMENT(s)
1141v$datafile v$tablespace dba_tables
1142 [contains]
1143system01.dbf SYSTEM DB dictionary
1144sysaux01.dbf SYSAUX snapshots
1145
1146DATAFILES, CONTROLFILES, LOGFILES
1147 DATAFILES
1148 contain TABLESPACES which contains TABLES
1149 CONTROLFILES
1150 contain DB PARAMETERS
1151 LOGFILES
1152 AKA ONLINE REDO LOGFILES; contain TRANSACTION LOGS
1153
1154DYNAMIC VIEWS [v$viewname]
1155 views regarding the info. and metadata of DB(some_aspects_of)
1156 • v$instance
1157 > DESC v$instance;
1158 > SELECT * FROM v$instance;
1159 > SELECT instance_name, host_name, status, database_status FROM v$instance;
1160
1161 • v$database
1162 > DESC v$database;
1163 > SELECT * FROM v$database;
1164 > SELECT dbid, name, open_mode, db_unique_name FROM v$database;
1165
1166 • v$datafile
1167 > DESC v$datafile;
1168 > SELECT * FROM v$datafile;
1169 > SELECT status, enabled, bytes/(1024*1024) MB, blocks, name FROM v$datafile;
1170
1171 • v$controlfile
1172 > DESC v$controlfile;
1173 > SELECT * FROM v$controlfile;
1174
1175 • v$logfile
1176 > DESC v$logfile;
1177 > SELECT * FROM v$logfile;
1178
1179 • v$tablespace
1180 > DESC v$tablespace;
1181 > SELECT * FROM v$tablespace;
1182
1183Useful tablenames
1184 user_tables tables owned by the user
1185 dba_tables tables owned by all users
1186 dba_tablespaces tablespaces info
1187 dba_data_files datafiles with their corresponding tablespace
1188 > SELECT file_name, tablespace_name, bytes/(1024*1024) MB, maxbytes/(1024*1024*1024) MaxGB, increment_by FROM dba_data_files;
1189
1190Tablespace creation
1191 > CREATE TABLESPACE test_tablespace DATAFILE '/u01/app/oracle/oradata/orcl/testdf01.dbf' SIZE 10M;
1192 > DROP TABLESPACE test_tablespace;
1193 > DROP TABLESPACE test_tablespace INCLUDING CONTENTS;
1194
1195User creation and tablespace allocation
1196 Syntax:
1197 > CREATE USER [username] IDENTIFIED BY [password] DEFAULT TABLESPACE [tablespace_name] TEMPORARY TABLESPACE [tablespace_name] QUOTA [size_in_M] ON [tablespace];
1198 > CREATE USER test_user IDENTIFIED BY admin DEFAULT TABLESPACE test_tablespace TEMPORARY TABLESPACE temp QUOTA 5M ON test_tablespace;
1199 > SELECT * FROM dba_users WHERE username = 'TEST_USER';
1200 when parameter not specified
1201 default tablespace USERS
1202 temp tablespace TEMP
1203 quota unlimited
1204
1205User privilege
1206 logon(both same)
1207 > GRANT CONNECT TO test_user;
1208 > GRANT CREATE SESSION TO test_user;
1209 > REVOKE CONNECT FROM test_user;
1210 > REVOKE CREATE SESSION FROM test_user;
1211 table creation
1212 > GRANT CREATE TABLE TO test_user;
1213 > REVOKE CREATE TABLE TO test_user;
1214
1215read printable characters (usually in encrypted files)
1216 > strings [filename]
1217
1218#############################
1219# Day 19 - Feb 07 #
1220#############################
1221
1222Grant connection privilege to one's own account
1223 > GRANT CONNECT TO [username];
1224
1225Grant table creation privilege
1226 > GRANT CREATE TABLE TO [username];
1227
1228Allow the user to grant others with the same privilege
1229 > GRANT [ the_privilege ] TO [grantee] WITH ADMIN OPTION;
1230
1231Alter user password or quota or tablespace
1232 > ALTER USER [username] IDENTIFIED BY [password] QUOTA [size_in_M] ON [tablespace];
1233
1234!Read: cluster index
1235 v$session
1236 shell script
1237
1238 #############################
1239 # Advanced VIm commands #
1240 #############################
1241
1242 i insert from cursor I insert from line_begin
1243 a insert before cursor A insert end of line
1244 o insert from newline below O insert from newline above the cursor
1245
1246 G goto last line gg goto first line
1247 u undo
1248 :x save and exit
1249 :q exit without save ! for forceful
1250 :w just save
1251
1252 :r [file_location] read from ext. file and append below cursor line
1253 :[x],[y]w [file_location] write to a new file from line number x to y
1254 /[keyword] search
1255 n next instance of search N previous instance of search
1256 :[x],[y]s/[w1]/[w2]/ search the w1 from line x to y and replace with w2
1257 :%s/[w1]/[w2]/ search the complete file for w1 and replace with w2
1258 [x]G goto line x
1259
1260 :set number show line number
1261 nonumber removes line number invnumber reverses the current line number setting
1262 :set nohlsearch does not highlight in search mode
1263 :set showmode shows insert when on insert mode
1264
1265 /etc/vimrc global configuration for VIM
1266 $HOME/.vimrc user_wise configuration
1267 direct commands can be placed here for always on preference
1268 nmap <[keystrokes]> :set [command]<CR> map macro keystroke to specific commands
1269 keystroke sample <[C]-[R]> [C]TRL + [R](button)
1270
1271#############################
1272# Day 20 - Feb 08 #
1273#############################
1274
1275dynamic query
1276dba_audit_trial
1277v$session
1278
1279#############################
1280# Day 21 - Feb 09 #
1281#############################
1282
1283Auditing
1284 hinders peformance
1285 diff server used in auditing, usually
1286 location: /u01/app/oracle/admin/orcl/adump/
1287
1288> SHOW PARAMETER AUDIT
1289
1290Mandatory auditing
1291 always created automatically for sys user
1292
1293Fine Grain Auditing
1294 audit on specific row and column
1295
1296dbms_fga.add_policy (
1297 object_schema => 'HR',
1298 object_name => 'EMPLOYEES',
1299 policy_name => 'audit_emps_salary',
1300 audit_condition=> 'department_id=10',
1301 audit_column => 'SALARY,COMMISSION_PCT',
1302 handler_schema => 'secure',
1303 handler_module => 'log_emps_salary',
1304 enable => TRUE,
1305 statement_types => 'SELECT,UPDATE');
1306
1307#############################
1308# Day 22 - Feb 11 #
1309#############################
1310
1311> show parameter background
1312
1313diagnostic directory
1314 /u01/app/oracle/diag/rdbms/orcl/orcl/
1315
1316/trace/alert_orcl.log
1317
1318!Read: DB start states
1319 mount, nomount, readonly
1320
1321AWR: Automatic Workload Repository
1322 snapshot of workload and DB status generated every 60mins
1323 maintained for 8 days
1324 this snapshot saved in sysaux.dbf
1325 > /u01/app/oracle/product/11.2.0/db_1/rdbms/admin/awrrpt.sql
1326
1327ADDM: Automatic DB Diagnostic and Maintenance
1328 > /u01/app/oracle/product/11.2.0/db_1/rdbms/admin/addmrpt.sql
1329
1330Memory structure
1331 Automatic Memory Management
1332 memory_target != 0
1333 sga and pga = 0
1334 Automatic Shared Memory Management
1335 memory_target = 0
1336 sga and pga != 0
1337
1338 > show parameter memory_target
1339 > show parameter sga_target
1340 > show parameter pga_aggregate_target
1341 > show parameter memory_max_target
1342 size upto which the DB can occupy system memory
1343
1344> ALTER SYSTEM SET pga_aggregate_target = 100M scope=spfile;
1345> ALTER SYSTEM SET sga_target = 300M scope=spfile;
1346> ALTER SYSTEM SET memory_target = 0 scope=spfile;
1347> SHOW PARAMETER spfile;
1348 /u01/app/oracle/product/11.2.0/db_1/dbs/spfileorcl.ora
1349
1350#############################
1351# DICTIONARY • VIEWS #
1352#############################
1353
1354dba_role_privs roles granted to users
1355dba_sys_privs sys privileges granted to users: more detailed
1356dba_views all views
1357dba_audit_session all sessions trail ie logons and logoffs
1358dba_roles all available roles: group of privilege
1359dba_triggers all triggers in DB
1360dba_jobs all jobs list
1361dba_free_space free space available in tablespace
1362dba_data_files datafile, tablespace_name, increment_by, current|max|used size
1363dba_tablespaces description of all tablespaces
1364dba_tablespace_usage_metrics total|used size and percentage used
1365 sizes shown in blocks: 128blocks = 1MB
1366dba_ts_quotas user associated with TS and size allocated for user
1367
1368 * see user, TSused, total_size_of_TS_used_by_user, total_TS_size, DATAFILE_location, next_size_increment_value
1369 > SELECT q.username, m.tablespace_name, m.used_space/(128) "totalTSSizeUsedMB",
1370 m.tablespace_size/(128) "totalTSSizeMB", q.max_bytes/(1024*1024) "totalUserAllocatedMaxSizeMB",
1371 df.file_name dataFileLocation, df.autoextensible, df.increment_by/(128) "incrementByMB"
1372 FROM dba_tablespace_usage_metrics m
1373 RIGHT OUTER JOIN dba_ts_quotas q ON m.tablespace_name = q.tablespace_name
1374 LEFT OUTER JOIN dba_data_files df ON q.tablespace_name = df.tablespace_name;
1375
1376dba_temp_files list of temporary datafiles
1377dba_temp_free_size TS_size, allocated_size, free_space in TEMP TS
1378dba_audit_policies audit policies in DB
1379dba_fga_audit_trails fine_grained_audit event logs
1380
1381user_tables tables owned
1382user_catalog tables, views, sequences, and synonyms owned
1383user_tab_privs privileges of self on others' tables | others on own tables
1384user_views user owned views
1385user_procedures fn/procedures/pkgs/triggers/types listing
1386user_recyclebin recyclebin
1387user_password_limits limites imposed on users password
1388user_users self info. default and temp TS, id, created_date, status
1389user_audit_session logon logoff session logs
1390user_sys_privs privileges of user
1391user_jobs jobs owned
1392user_free_space free space on TS
1393user_tablespaces TS user is associated with, including their info
1394user_ts_quotas quota available in TS
1395
1396all_users not_so_detailed list of all users
1397
1398v$log log info
1399v$logfile logfile associated and its location
1400 > SELECT lf.group#, lf.member, l.thread#, l.status FROM v$logfile lf
1401 JOIN v$log l ON lf.group# = l.group#;
1402
1403v$process processes, their tracefile, bg_status, memory info
1404v$dbfile quick list of all .dbf(s)
1405v$database DB info, status, mode, unique_name, archive_mode
1406v$instance hostname, instance name, status
1407v$pga PGA memory info
1408v$sgainfo SGA memory info
1409v$datafile comprehensive table of datafiles info
1410v$tablespace opentoall TS list
1411v$archive_dest shows destination where archive files are saved, save info as
1412 > ARCHIVE LOG LIST;
1413
1414#############################
1415# Day 23 - Feb 12 #
1416#############################
1417
1418DB state:
1419 NOMOUNT
1420 starting the instance without mounting the DB ie no connection
1421 done for DB_creation or recreation of Controlfiles
1422 does not require Controlfile
1423 MOUNT
1424 starting the indtance with DB mount but provides no access to it
1425 done for certain DBA activities, no general access though
1426 requires Controlfile but no Datafile
1427 OPEN
1428 starting the instance with DB mount and open, ready for access
1429 requires Controlfile and Datafile(all/every DFs must be present and accessible)
1430
1431BACKUP AND RECOVERY
1432 incase of DB crash
1433 > SHUTDOWN ABORT;
1434 which, when happens, doesnot create checkpoint and contents of database_buffer_cache is not written to datafile
1435 so the contents of onlineredolog, DBF, CKPT are not in sync
1436 incase no archive mechanism is set
1437
1438 Archive Mode
1439 > ARCHIVE LOG LIST;
1440 shows the pref of archive mode
1441 default archive location: /u01/app/oracle/product/11.2.0/db_1/dbs/arch
1442 every time CKPT runs,
1443 DBWn writes from cache to Datafiles
1444 online_redo_logfile switches and flushes the old one,so the log is lost
1445 archive mode simply creates an archive of the logfile_content before the CKPT process flushes the logfile
1446 if DB crashes, with last Backup taken few hours ago, and redo_logfiles are not insync with DFs
1447 then, Oracle can, with help of last Backup and Archive of redo_logfiles, restore the DB to the state moments before the crash
1448
1449RECOMMENDATIONs for setting up DB:
1450 • Controlfiles more than 2 and in different location(s) than default
1451 • make multiple logfiles in each online_redo_logs_group (MULTIPLEXing)
1452 • keep Datafiles in different directory than default /u01/ location
1453
1454Prerequisites to set DB to Archive mode
1455 > ARCHIVE LOG LIST
1456 check current preferences
1457 > ALTER SYSTEM SET DB_RECOVERY_FILE_DEST_SIZE=5G;
1458 > ALTER SYSTEM SET DB_RECOVERY_FILE_DEST='/u01/app/oracle/fast_recovery_area/orcl/';
1459 > SHOW PARAMETER db_recovery_file_dest
1460 check to assure
1461
1462Steps to enable Archive_mode in DB
1463 change DB to Mounted state
1464 > ALTER DATABASE CLOSE;
1465 > ALTER DATABASE ARCHIVELOG;
1466 inorder to confirm
1467 > ARCHIVE LOG LIST
1468 > ALTER SYSTEM SWITCH LOGFILE
1469 everytime a switch to online_redo_logfile is made, a new archive_file of the log is created at `DB_RECOVERY_FILE_DEST`
1470
1471Multiplexing Controlfile
1472 > ALTER SYSTEM SET control_files =
1473 '/u01/app/oracle/oradata/orcl/control01.ctl',
1474 '/u01/app/oracle/oradata/orcl/control02.ctl',
1475 '/u01/app/oracle/oradata/orcl/control03.ctl' SCOPE=spfile;
1476 after DB instance restart, instance will refuse to start because of missing controlfile control03.ctl
1477 create a copy in binary folder
1478 > cp control01.ctl control03.ctl
1479
1480Backup
1481 consistent offline
1482 consistent offline backup is simply copy and paste of required DB binary files
1483 inconsistent online
1484 must be in archive mode with auto_archival enabled
1485 done with Oracle RMAN
1486
1487 fullbackup
1488 incremental backup
1489 differential
1490 backup of particular duration
1491 cumulative
1492 backup from fullbackup hitherto
1493
1494RMAN (RECOVERY MANAGER)
1495 > backup database;
1496
1497!READ: backup and moving data
1498
1499#############################
1500# Day 24 - Feb 14 #
1501#############################
1502
1503Recommendation:
1504 CONFIGURE CONTROLFILE AUTOBACKUP OFF
1505 > CONFIGURE CONTROLFILE AUTOBACKUP ON;
1506 > CONFIGURE BACKUP OPTIMIZATION ON;
1507
1508!READ: SCP for linux
1509 create new DB by copying old sys DBF, CF, RedoLog and spfile
1510 matrix performance
1511
1512autobackup: has controlfiles backup
1513 > RESTORE CONTROLFILE FROM AUTOBACKUP;
1514
1515 > ALTER DATBASE OPEN RESETLOGS;
1516
1517 > RESTORE DATAFILE 1;
1518
1519 > RECOVER DATABASE;
1520
1521 > ALTER DATABASE ADD LOGFILE MEMBER '/u01/app/oracle/oradata/orcl/log01a.log' TO GROUP 1;
1522 > ALTER DATABASE ADD LOGFILE MEMBER '/u01/app/oracle/oradata/orcl/log02a.log' TO GROUP 2;
1523 > ALTER DATABASE ADD LOGFILE MEMBER '/u01/app/oracle/oradata/orcl/log03a.log' TO GROUP 3;
1524
1525Change size of the Redo Log File
1526 add new files of required size
1527 > ALTER DATABASE ADD LOGFILE GROUP [new_group_no.] '[file_location]' SIZE [size];
1528 `SWITCH LOGFILE` till all old group logfiles become 'INACTIVE'
1529 > ALTER SYSTEM SWITCH LOGFILE;
1530 > SELECT status, group# FROM v$logfile;
1531 remove the old ones
1532 > ALTER DATABASE DROP LOGFILE GROUP [old_group_no.];
1533
1534#############################
1535# MANUAL BACKUP RESTORE #
1536#############################
1537
15381. create VM with same structure as previous one
15392. configure pre-requisites for installation of oracle database binaries
15403. install the oracle database binaries
15414. copy all the datafile from previous one to create new one with same structure as previous
1542 ( to check all the datafile, select * from v$datafile)
15435. copy all the control files from previous one to create new one with same structure as previous
1544 ( select name from v$controlfile)
15456. copy all the online logfile from previous one to new vm
1546 (select member from v$logfile)
15477. copy spfile from previous one to new VM
1548 (show parameter spfile)
15498. once copy files has been completed, on new vm
1550 sqlplus / as sysdba
1551 startup
1552
1553#############################
1554# Day 25 - Feb 15 #
1555#############################
1556
1557DATA MOVEMENT
1558 SQL Loader external table
1559 RMAN physical backup
1560
1561 DUMP backup logical backup
1562 either full DB, schema, tables or other objects
1563
1564SET PAGES 0;
1565SET LINES 233;
1566COL [column_name] format a[no.];
1567COL [col_name] format 9999;
1568
1569spool [filename]
1570 [sqlcommands here]
1571spool off
1572
1573vi spool.sh
1574
1575/boot 500M ~ 1024M
1576/archive 20G
1577/u01 20G
1578/ 10G
1579/data 10G
1580/index 10G
1581/tmp 5G
1582swap 4G
1583 ------------
1584 80G[total]
1585
1586For full backup
1587 > create directory test_dir as '/u01/app/oracle/dump/';
1588 > expdp system/admin full=Y directory=TEST_DIR
1589 dumpfile=test.dmp logfile=test.log;
1590 > expdp hr/hr schemas=hr directory=TEST_DIR
1591 dumpfile=hr.dmp logfile=hr.log;
1592 > expdp hr/hr tables=employees, departments directory=TEST_DIR
1593 dumpfile=hr.dmp logfile=table.log;
1594
1595 Google: oracle database expdp
1596 https://oracle-base.com/articles/10g/oracle-data-pump-10g
1597 backup: table ending in 'ployee' and with id 170
1598
1599RMAN physical
1600expdp logical
1601
1602#############################
1603# Day 26 - Feb 16 #
1604#############################
1605
1606table in: "( SELECT DISTINCT table_name FROM dba_tables WHERE UPPER(table_name) LIKE '%LOYEES' WHERE employee_id = 170 )"
1607
1608CREATE TABLESPACE test_data DATAFILE '/data/oradata/test/test_data01.dbf' SIZE 10M;
1609CREATE TABLESPACE test_index DATAFILE '/index/oradata/test/test_index01.dbf' SIZE 10M;
1610
1611CREATE USER test identified by test DEFAULT TABLESPACE test_data TEMPORARY TABLESPACE temp QUOTA UNLIMITED ON test_data;
1612
1613ALTER USER test QUOTA UNLIMITED ON test_index;
1614
1615GRANT CONNECT, RESOURCE TO test;
1616
1617CREATE TABLE employees
1618 ( id int not null primary key, name varchar2(20) );
1619
1620#insert dummy PL/SQL
1621begin
1622for i in 2 .. 1000
1623loop
1624insert into employees values(i, concat('test', i));
1625commit;
1626end loop;
1627end;
1628
1629SELECT index_name FROM user_indexes WHERE table_name = 'EMPLOYEES';
1630
1631SELECT tablespace_name from user_indexes where index_name = '[indexno]';
1632
1633ALTER tablespace_name from user_indexes where
1634
1635alter index SYS_C0011093 rebuild tablespace test_index;
1636
1637#############################
1638# Day 27 - Feb 18 #
1639#############################
1640
1641> alter system set log_archive_dest_1 = 'LOCATION=/archive/oradata/test/';
1642
1643> show parameter log_archive_dest_state_1;
1644
1645> shut immediate
1646> startup mount
1647> alter database [no]archivelog
1648> archive log list
1649> alter database open
1650
1651> alter system switch logfile;
1652
1653rman target /
1654CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '/archive/oradata/rman_bk/AUTO_%F';
1655
1656BACKUP DATABASE FORMAT '/archive/oradata/rman_bk/DB_%U' PLUS ARCHIVELOG FORMAT '/archive/oradata/rman_bk/ARCH_%U';